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>
- 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
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
- 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
public SqlConnection GetConnection(string connectionName)
{
string cnstr = ConfigurationManager.ConnectionStrings[connectionName].ConnectionString;
SqlConnection cn = new SqlConnection(cnstr);
cn.Open();
return cn;
}
- 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
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,>
- 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
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;
}
- 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
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
- 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
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);
- 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
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()));
}
- 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
No comments :
Post a Comment