Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

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


By:   |   Last Updated: 2013-07-25   |   Comments (20)   |   Related Tips: 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.


Last Updated: 2013-07-25


next webcast button


next tip button



About the author
MSSQLTips author Ray Barley Ray Barley is a Principal Architect at IT Resource Partners and a MSSQLTips.com BI Expert.

View all my tips




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Thursday, April 17, 2014 - 12:38:40 PM - Raymond Barley Back To Top

Try running the SQL Server Profiler while you run your code.  You can see whether the stored procedure returns any rows.


Thursday, April 17, 2014 - 12:25:49 PM - Rajendran Back To Top

ExecuteQuery not returning any rows. But I am having 6 rows in database. When I am executing the stored procedure GetCustomerList in the SQLEXPRESS, it executes successfully.

Any idea ?


Tuesday, March 11, 2014 - 8:58:57 AM - Raymond Barley Back To Top

I think Dictionary is the easiest but there are many collection classes in the .NET framework.  Take a look at these namespaces: System.Collections: http://msdn.microsoft.com/en-us/library/system.collections(v=vs.110).aspx and System.Collections.Generic: http://msdn.microsoft.com/en-us/library/system.collections.generic(v=vs.110).aspx


Tuesday, March 11, 2014 - 7:01:00 AM - ashish Back To Top

how can we do it without using dictionary and using params to pass parameters,as dictionary takes larger space 


Tuesday, February 11, 2014 - 1:27:36 PM - Mani Back To Top

Thank you sir. That part was somewhat confusing.

Nice Article about adding parameters. One of the best I have found on the net till date.

 


Monday, February 10, 2014 - 1:11:30 PM - Raymond Barley Back To Top

I went back to my original code for this tip.  The declaration for the SQL command parameters should look like this:

Dictionary<string, SqlParameter> cmdParameters = new Dictionary<string, SqlParameter>();

The above code is saying I want to create an instance of a .NET Dictionary object where the key type is a string and the key value is an instance of the SqlParameter class.  

The code in the tip Dictionary<string, sqlparameter=""> cmdParameters = new Dictionary<string, sqlparameter="">(); IS WRONG.  I don't know where it came from.

 


Monday, February 10, 2014 - 11:04:21 AM - Mani Back To Top

Sorry Sir.My bad.

 

Dictionary<string, sqlparameter=""> cmdParameters = new Dictionary<string, sqlparameter="">();
the underlined part creating a problem. When I write this it says: expected'>'...i.e the closing tag
by the way if I remove the equal and the double quotes sign...it works flawlessly.
So I want to know the purpose of that eaual and quotes sign.
 

Monday, February 10, 2014 - 10:07:26 AM - Raymond Barley Back To Top

Re: Monday, February 10, 2014 - 12:31:32 AM - Mani

You haven't provided enough code for anyone to figure out what's wrong

 

As I look through the code samples in the tip I find an error at "Here is the code to call the AddCustomer stored procedure:"

The last line should not be there; i.e. </string,></string,> is NOT correct and should be deleted; maybe this is your problem?

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);
</string,></string,>
 
 

Monday, February 10, 2014 - 12:31:32 AM - Mani Back To Top
 public DataSet ExecuteQuery(
  string connectionName,
  string storedProcName,
  Dictionary procParameters
)
{
sqlparameter=""   provides an error(requres '>')
Please Help

Thursday, December 05, 2013 - 8:18:37 AM - Raymond Barley Back To Top

I don't know what to do with respect to entity framework.

If you want to drop a database and it may be in use you can execute this command before tryiing to drop the database:

ALTER DATABASE databasename

SET SINGLE_USER WITH ROLLBACK IMMEDIATE 

 

See this for full details on set single_user: http://technet.microsoft.com/en-us/library/ms345598.aspx

 


Thursday, December 05, 2013 - 5:38:35 AM - Alex Back To Top

I am trying to Restore database using the C# code. Database is controlled by Entity Framework. The logic used:

void Restore()

{

  try

  {

    db.Connection.Close();

    using (cn = new SqlConnection(cnMaster))

    {

      cn.Open();

      SqlCommand cmd = new SqlCommand("Restore Database " + dbName + " from disk='" 

   + dataDir + dbName + ".bak' with replace", cn);

      cmd.ExecuteNonQuery();

      UpdateControls();

      cn.Close();

      new FormMsg("Restore success", 13000);

    }

  }

  catch (Exception ex) { new FormMsg(MyKit.SetErrorMsg(ex), 4000); }

}

I receive Exception saying that database is in use. Would you please explain how to temporarily close connectin and mayby disable ObjectContext while doing the Restore from backup.

Thank you.


Saturday, July 27, 2013 - 9:12:14 PM - Mike Bishop Back To Top

BTW Ray, great article.


Saturday, July 27, 2013 - 9:11:02 PM - Mike Bishop Back To Top

@Mark I believe Tobias is correct - kind of.  The MSDN Best Practices for Using ADO.NET says "automatically calls Dispose". My understanding is that System.Data.SqlClient is one of the very few .NET classes where .Close and .Dispose actually have different behaviours. And I believe the difference is that you can call close more than once (with no exception), but if you call .Dispose more than once .... Boom!  Anybody out there ever test this Or did you all just call .Close like I do??


Thursday, July 25, 2013 - 4:10:46 PM - Raymond Barley Back To Top

I use a Dictionary for the SqlParameters because in the event you have output parameters, it makes it a little easier to retrieve the SqlParameter in the calling code.

The SqlConnection in the ExecuteQuery() is not in a using because it has to remain open for the caller to iterate through the result set.  I pass the CommandBehavior.CloseConnection parameter to ExecuteReader; this will close the connection after the caller iterates through the result set.

 


Thursday, July 25, 2013 - 4:09:40 PM - Mark Harr Back To Top

@Tobias: Certainly some conflicting information, as it also says on that same page: "If the SqlConnection goes out of scope, it won't be closed. Therefore, you must explicitly close the connection".  I can say from testing that if not explicitly closed, then the next open connection will open a new connection, not reuse the previous one.

 

Note that ADO.Net does keep closed connections around for awhile, to reuse for connection pooling.  But it will generally not reuse a connection that is not explicitly closed. The exception to that is there is a timeout if the connection is not used or closed, it will then be closed.  But it is best to explicitly close the connection so that it can be reused by the next query.


Thursday, July 25, 2013 - 2:38:51 PM - Tobias Back To Top

From MSDN: The connection is automatically closed at the end of the using block.


Thursday, July 25, 2013 - 2:28:40 PM - Gregg Drennan Back To Top

Hi Ray,

I'm not sure why you are using a Dictionary to pass the SqloParameters since only the SqlParameter part is being used.  Wouldn't a Listbe more suitable?

Also I noticed that you wrapped the SqlConnection in the ExecuteCommand() with a Using() but not in the ExecuteQuery()... was there a reason for doing it this way?

I tend to wrap the SqlCommand with a Using() also but noticed you hadn't...

Thanks


Thursday, July 25, 2013 - 1:22:00 PM - Raymond Barley Back To Top

Good catch on the missing close.  For this tip I modified some code from a project where the SqlDatabaseUtility class implemented IDisposable and the close happened in the Dispose() method.

I will update the code in this tip to implement IDisposable.

Thanks


Thursday, July 25, 2013 - 12:28:45 PM - Victor E. Diaz Back To Top

Una instruccion using si cierra la conexion, de hecho segun tengo entendido es una forma limpia de cerrarla, sin preocuparse por hacer un Close al comando.


Thursday, July 25, 2013 - 11:02:22 AM - Mark Harr Back To Top

Nice article, Ray.  However, you have left out the dbConnection Close() command.  Just having the connection object in a Using statement does not close the connection.  And with ADO.Net connection pooling, calling another instance of your ExecuteCommand or ExecuteQuery will not reuse a recent connection unless the connection is explicitly closed.  To save on connections and resources in your app, and reuse recent connections, you need to explicitly close the connection at the end of your functions.


Learn more about SQL Server tools