By: Ray Barley | Updated: 2013-07-25 | Comments (20) | Related: > Application Development
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.
About the author
Ray Barley is a Principal Architect at IT Resource Partners and a MSSQLTips.com BI Expert.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
Article Last Updated: 2013-07-25
