By , 15 Sep 2013 on codeproject
| Introduction |
Monday, September 16, 2013
Thursday, August 1, 2013
How To Create an ADO.NET Data Access Utility Class for SQL Server
 | By: Ray Barley |
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 atSqlConnection.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, DictionaryprocParameters ) { 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; }
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 cmdParameters = new Dictionary();
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.
Subscribe to:
Posts (Atom)