How To Create an ADO.NET Data Access Utility Class for SQL Server

By: Ray Barley   |   Comments (20)   |   Related: More > 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.

View all my tips