Problem
I am a .NET developer and I typically write applications that use a SQL Server database. I’m looking for a really simple, reusable class that encapsulates my ADO.NET database access code for create, read, update and delete (CRUD). As I see it I need two methods in the class: one that executes a stored procedure that returns a result set and another that executes a stored procedure that does an insert, update or a delete. Can you provide an example of how to do it?
Solution
While there are many code samples readily available to encapsulate ADO.NET database access, I prefer the simple, bare-bones approach that satisfies your requirements of a method that executes a query and another that executes a command. In this tip I will review a solution that has a class library for the database utility and a console application that uses the class library.
I will assume that the reader is familiar with creating .NET applications using Visual Studio.
Connection Strings
When you write ADO.NET code to access a database, you need a connection string to specify the database that you want to access. The connection string can be stored in your application’s app.config file or web.config file (for a web application). The following is an example of an app.config file:
<?xml version=”1.0″ encoding=”utf-8″ ?>
<configuration>
<startup>
<supportedRuntime version=”v4.0″ sku=”.NETFramework,Version=v4.5″ />
</startup>
<connectionStrings>
<add name=”mssqltips”
connectionString=”data source=localhost;initial catalog=mssqltips;Integrated Security=SSPI;”
providerName=”System.Data.SqlClient” />
</connectionStrings>
</configuration>
The following are the main points about the app.config file:
mssqltips is the name of the connection string; we will use the name mssqltips to access the connection string
Data source is the server name of the SQL Server database instance
Initial catalog is the database name
Integrated Security=SSPI means we are using windows authentication to connect to the database
Provider name is the ADO.NET data provider for SQL Server
You can specify many more settings in the connection string than I have shown here. Take a look at SqlConnection.ConnectionString Property for the details.
Database Utility Class
I will use a class library project named DataAccessUtility to implement the database access utility class. When a class library is compiled it generates a dynamic link library (.DLL) which can then be referenced from any .NET application. The class library will have a single class named SqlDatabaseUtility with the following methods:
GetConnection() opens a database connection
ExecuteQuery() executes a stored procedure that performs a query
ExecuteCommand() executes a stored procedure that performs an insert, update or delete
Before I get to reviewing the code in the methods, here are a couple of things that need to be done:
Add a reference to System.Configuration to the class library project; I need this to access the connection string in the app.config file
Add using statements to the SqlDatabaseUtility class for the namespaces System.Configuration, System.Data and System.Data.SqlClient; I am using classes from these namespaces
The GetConnection() method has the following code:
public SqlConnection GetConnection(string connectionName)
{
string cnstr = ConfigurationManager.ConnectionStrings[connectionName].ConnectionString;
SqlConnection cn = new SqlConnection(cnstr);
cn.Open();
return cn;
}
The main points about the GetConnection() method are:
Reads the connection string from the app.config (or web.config) file
Creates an instance of a SqlConnection object passing the connection string into the constructor
Calls the Open() method on the SqlConnection object which “opens” a database connection
Returns the SqlConnection object to the caller
The ExecuteQuery() method has the following code:
public DataSet ExecuteQuery(
string connectionName,
string storedProcName,
Dictionary<string, sqlparameter=””> procParameters
)
{
DataSet ds = new DataSet();
using(SqlConnection cn = GetConnection(connectionName))
{
using(SqlCommand cmd = cn.CreateCommand())
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = storedProcName;
// assign parameters passed in to the command
foreach (var procParameter in procParameters)
{
cmd.Parameters.Add(procParameter.Value);
}
using (SqlDataAdapter da = new SqlDataAdapter(cmd))
{
da.Fill(ds);
}
}
}
return ds;
}
</string,>
The main points about the ExecuteQuery() method are:
Creates a Dataset that will be used to return the query results to the caller
Calls the GetConnection() method to open a database connection
Creates a SqlCommand object from the Connection, and sets the CommandType and CommandText properties
Adds any parameters passed in to the SqlCommand parameter collection
Creates a SqlDataAdapter for the SqlCommand, and calls the Fill method to execute the query and populate a dataset
Returns the Dataset to the caller
The SqlConnection, SqlCommand, and SqlDataAdapter objects are wrapped with a “using” statement which ensures that the objects are disposed; the caller is not responsible for “freeing” these objects
The ExeuteCommand() method has the following code:
public int ExecuteCommand(
string connectionName,
string storedProcName,
Dictionary<string, SqlParameter> procParameters
)
{
int rc;
using (SqlConnection cn = GetConnection(connectionName))
{
// create a SQL command to execute the stored procedure
using (SqlCommand cmd = cn.CreateCommand())
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = storedProcName;
// assign parameters passed in to the command
foreach (var procParameter in procParameters)
{
cmd.Parameters.Add(procParameter.Value);
}
rc = cmd.ExecuteNonQuery();
}
}
return rc;
}
The main points about the ExecuteCommand() method are:
Calls the GetConnection() method to open a database connection; the using construct is used to close the database connection automatically
Creates a SqlCommand object from the Connection and sets the CommandType and CommandText properties
Adds any parameters passed in to the SqlCommand parameter collection
Calls the SqlCommand ExecuteNonQuery method to call the stored procedure; the return value is the number of rows affected; e.g. the number of rows inserted, update or deleted by the command
The SqlConnection and SqlCommand objects are wrapped with a “using” statement which ensures that the objects are disposed; the caller is not responsible for “freeing” these objects
Console Application
In this section I will review a .NET console application that will access a SQL Server database by using the SqlDatabaseUtility class. Here is a T-SQL script that creates a table, and two stored procedures – one that inserts a row and another that performs a query:
use mssqltips
go
create table [dbo].[customer] (
[id] [int] identity(1,1) NOT NULL,
[name] [varchar](50) NOT NULL,
[state] [varchar](2) NOT NULL,
constraint [pk_customer] primary key clustered ([id] asc)
)
go
create procedure dbo.AddCustomer
@name varchar(50)
,@state char(2)
as
begin
insert into dbo.customer
([name], [state])
values
(@name, @state)
end
go
create procedure dbo.GetCustomerList
as
begin
select [id], [name], [state]
from dbo.customer
end
go
Before I get to reviewing the code in the console application, here are a couple of things that need to be done:
Add a reference to the DataAccessUtility class library to the console application; I need this to call the methods in the SqlDatabaseUtility class
Add a using statement for the DataAccessUtility, System.Data and System.Data.SqlClient namespaces
Create an mssqltips database and run the above T-SQL script in it
Put the connectionStrings element (shown in the Connection Strings section above) into the app.config file in the console application project
Here is the code to call the AddCustomer stored procedure:
SqlDatabaseUtility dbutility = new SqlDatabaseUtility();// add a customer
Dictionary<string, SqlParameter> cmdParameters = new Dictionary<string, SqlParameter>();
cmdParameters[“name”] = new SqlParameter(“name”, “Smith”);
cmdParameters[“state”] = new SqlParameter(“state”, “MD”);
dbutility.ExecuteCommand(“mssqltips”, “dbo.AddCustomer”, cmdParameters);
The main points about the above code are:
Create an instance of the SqlDatabaseUtility class
Create a Dictionary collection for parameters; it’s like a name-value pair
Add parameters to the collection; parameter names must match the stored procedure parameters
Call the SqlDatabaseUtility ExecuteCommand method passing in the connection name, stored procedure name, and the parameter collection
Here is the code to call the GetCustomerList stored procedure:
Dictionary<string, SqlParameter> queryParameters = new Dictionary<string, SqlParameter>();
DataSet ds = dbutility.ExecuteQuery(“mssqltips”, “dbo.GetCustomerList”, queryParameters);
DataTable t = ds.Tables[0];
foreach(DataRow r in t.Rows)
{
Console.WriteLine(string.Format(“{0}\t{1}\t{2}”,
r[0].ToString(),
r[1].ToString(),
r[2].ToString()));
}
The main points about the above code are:
Create a Dictionary collection for parameters; even though the GetCustomerList does not take any parameters, you still have to pass an empty collection
Call the SqlDatabaseUtility ExecuteQuery method passing the connection name, stored procedure name, and empty parameter collection
ExecuteQuery returns a Dataset which is a collection of DataTables
Get the first Datatable from the Dataset, iterate through the rows and print the column values to the console
Next Steps
- The above code is an example of a very simple approach to calling stored procedures from .NET code.
- To keep the code as simple as possible, there is no exception handling shown. You do need try/catch blocks around your database calls. Take a look at the SqlCommand methods to see the kinds of exceptions that you need to catch.
- Download the sample code here to experiment on your own.
Ray is a Principal Architect at IT Resource Partners focused on SQL Server and Business Intelligence. He is a Microsoft Certified Solutions Expert (MCSE) in Business Intelligence, a MSSQLTips.com BI Expert and Ambassador. He is also a co-author of the book: SharePoint 2010 Business Intelligence Unleashed.
- MSSQLTips Awards: Champion (100+ tips)
