If you already are using stored procedures and have you own data layer, skip the introduction and take a look at the Coding Example.
Stored Procedure Overview
Recently I have really begun to appreciate my decision in using stored procedures in my client server, n-tier and web based application development. Besides the really obvious benefits of compiled stored procedures running faster there are many other benefits to not embedding SQL into your client application.
Just like using code behind pages in Visual Studio asp.net applications, separating your SQL away from your code goes a long way towards better management of you application. If modifications or enhancements have to be made to the database side of the equation then the developer does not have to modify, re-compile and migrate the entire application.
Performing all of your database manipulation in a stored procedure give you tremendous flexibility by allowing the SQL code to be written using programming constructs, such as cursors, variables, if statements and while loops. There are also performance gains by offloading this programming logic to the database server instead of the web server or client workstation.
In order to take advantage of the SQL code encapsulation you have to have a clean interface between the application and the database. Most developers write a database layer which handles this interface. In the .NET environment it is ADO.NET that provides the bridge between the application code and the database.
ADO.NET provides the necessary tools to open and close connections to the database. It also provides methods to pass arguments to your stored procedures and ways to gather the returned data sets or communications from the database and store these results in data readers, data adapters or memory variables.
By developing and maintaining a good data layer allows the developer to focus their time on writing the application code rapidly without having to worry about interfacing with the database all of the time.
Recently, where I work, management has been requesting, enhancements, modifications and the addition of new products to our web site. They wanted these changes to be made very rapidly.
Coding Example
I decided I needed to optimize my data layer so I could call just about any select based stored procedure without having to write custom code for every instance.
In this case C#, method overloading and ADO.NET came to the rescue.
I needed a method that I could call any stored procedure with any number of arguments and return any number of different types of data.
So the requirements for my data layer method are the following:
My method should take the following arguments:
- Name of stored procedure to call
- List of stored procedure arguments to pass (could be a different each time)
- List of values to pass for each argument (could be different each time)
C# has the capability of defining methods with the params keyword. The params keyword lets you specify a method parameter that takes an argument where the number of arguments is variable.
Below is my data layer method that meets the above requirements.
public bool GetLookUpData(string storedProc, string spParms, ref SqlDataReader rdr, SqlConnection SqlConnect, params string[] strParms)
{
string[] Parms = spParms.Split(',');
StringBuilder sqlErr = new StringBuilder();
try
{
if (Parms.Length != strParms.Length)
{
throw new System.ArgumentOutOfRangeException("Arguments do not match");
}
if (SqlConnect.State == ConnectionState.Closed)
{
return false;
}
SqlCommand sqlQuery = new SqlCommand(storedProc, SqlConnect);
sqlQuery.CommandType = CommandType.StoredProcedure;
for (int i = 0; i < strParms.Length; i++)
{
sqlQuery.Parameters.AddWithValue(Parms[i], strParms[i]);
}
sqlErr.Append(sqlQuery.CommandText + " '");
for (int i = 0; i < strParms.Length - 1; i++)
{
sqlErr.Append(strParms[i] + "','");
}
sqlErr.Append(strParms[strParms.Length - 1] + "'");
rdr = sqlQuery.ExecuteReader();
if (rdr == null)
{
util.logEntry("Null - " + sqlErr.ToString());
return false;
}
sqlErr.Remove(0, strParms.Length);
return true;
}
catch (Exception ex) // catches without assigning to a variable
{
util.logEntry(ex.Message + " - " + sqlErr.ToString());
return false;
}
}
The storedProc argument will of course take the name of the stored procedure.
The spParms argument takes a comma seprated list of stored procedure parameter names i.e. (refer to stored procedure below)
"@Country,@ContactTitle"
The rdr argument is a local SqlDataReader passed by reference.
The SqlConnect argument is a local SqlConnection object.
(See the enclosed C# project for a variation on SqlConnection)
The strParms array argument is used to pass a variable amount of stored procedure parameter values.
The first for loop associates the stored procedure parameters with the parameter values.
The second for loop is used to build a string, sqlErr, which contains the stored procedure call that is saved to a log file in the event of an error. In this way if there are errors I can copy and paste these stored procedure calls and run them in the SQL Server Management studio for debugging purposes or update the database with missed transactions.
I have included a sample C# project called DataLayer. The project was created in Visual Studio 2008. This project included a mini data layer that uses the below stored procedures and the Northwind database that comes with Microsoft Sql Server.
To get the project working create the below stored procedures in the Northwind database. You will also have to modify the connection string referenced in the app.config in the project to match your own server environment.
You will need to change the Data Source, the User ID and the Password.
USE [Northwind]
GO
/****** Object: StoredProcedure [dbo].[GetCustomer] Script Date: 02/17/2010 12:49:18 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Joe Pitz>
-- Create date: <02/17/2010>
-- Description: Stored procedure to get customer by country and contact title
--
-- =============================================
CREATE PROCEDURE [dbo].[GetCustomer]
-- Add the parameters for the stored procedure here
@Country nvarchar(15),
@ContactTitle nvarchar(30)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
select * from Customers where Country = @Country and ContactTitle
= @ContactTitle
END
USE [Northwind]
GO
/****** Object: StoredProcedure [dbo].[GetCustomer] Script Date: 02/17/2010 12:49:18 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Joe Pitz>
-- Create date: <02/17/2010>
-- Description: Stored procedure to get customer by country and contact title
--
-- =============================================
CREATE PROCEDURE [dbo].[GetCustomerCountry]
-- Add the parameters for the stored procedure here
@Country nvarchar(15)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
select * from Customers where Country = @Country
END
Running the Code Example
Once configure the DataLayer application, create your stored procedures and make sure you can access theNorthwind database we can now discuss how we access our datalayer methods.
Follow along with the code in the DataLayer applicaton.
Reference the code in the frmDatalayer.cs file.
Notice on the Form desgin of the frmDatalayer we have two push buttons and one DataGridView.
Take a look at the code for the btnTitle_Click() event.
DataLayer db = new DataLayer();
db.Connect(ConstSettings.ConnStr);
DataTable dataTable = new DataTable();
SqlDataReader sdr = null;
string[] strParms = new string[2];
strParms[0] = "USA";
strParms[1] = "Sales Manager";
db.GetLookUpData(ConstSettings.CustSP,
ConstSettings.CustSPPara, ref sdr, strParms);
dataTable.Load(sdr);
grdCustomers.DataSource = dataTable;
sdr.Close();
db.Disconnect();
In order to access the database we first have to create an instance of our datalayer. We than must open a connection to the database.
We call the db.Connect(ConstSettings.ConnStr) method and pass our static connection string.
When binding a SQLDataReader to a DataGridView you must use a dataTable.
Notice how we create the string array, populate the elements with the stored procedure parameter values and then call the datalayer db.GetLookUpData() method.
We now can populate the datatable and bind the table to the DataGridView.
Make sure when you are done you close the SqlDataReader and call the db.Disconnect() method.
On your own take a look at the btnCountry_Click() event code to see the same call to the GetLookUpData method with a different stored procedure and a different number of parameters and parameter values.
The nice thing about creating your own datalayer is that you can add as many new methods as you need. For example we can use the same params key work and array to create data layer methods to perform database updates and inserts using the ADO.NET ExecuteNonQuery() method or we can develop methods to perform counts using the ADO.NET ExecuteScalar() method.
We could also use generics instead of method overloading to create our data layer methods which will allow our code to specify argument types when the methods are called.
Stay tuned for more information.