Click here to Skip to main content
15,565,601 members
Articles / Programming Languages / C#
Article
Posted 19 Feb 2010

Tagged as

Stats

26.7K views
173 downloads
7 bookmarked

Optimize Your Data Layer for Quicker Code Development

Rate me:
Please Sign up or sign in to vote.
2.00/5 (8 votes)
19 Feb 2010CPOL5 min read
Create re-useable code for your data layer

Introduction

If you are already using stored procedures and have your own data layer, skip the overview 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 your 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 manipulations in a stored procedure gives 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.

Developing and maintaining a good data layer allows the developer to focus his/her 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.

Using the Code

I decided I needed to optimize my data layer so that 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 where 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 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:

C#
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) 
   {
      util.logEntry(ex.Message + " - " + sqlErr.ToString());
      return false;
   }
}

Calling the Method

The storedProc argument will of course take the name of the stored procedure.

The spParms argument takes a comma separated list of stored procedure parameter names, i.e., (refer to stored procedure below).

SQL
"@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.

SQL
USE [Northwind]
GO
/****** Object:  StoredProcedure [dbo].[GetCustomer]  ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:		<joe>
-- 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] ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:		<joe>
-- 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 you configure the DataLayer application, create your stored procedures and make sure you can access the Northwind database. We can now discuss how we access our datalayer methods.

Follow along with the code in the DataLayer application.

Reference the code in the frmDatalayer.cs file.

Notice on the Form design of the frmDatalayer we have two push buttons and one DataGridView. Take a look at the code for the btnTitle_Click() event.

SQL
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 must then 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 can now 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.

Points of Interest

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 keyword 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.

History

  • Initial release - 02/18/2010

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
Software Developer Objetek Systems
United States United States
I am currently consulting for Denso Wireless writing manufacturing traceability systems in C# and ASP.net. Denso Wireless manufactures navigation systems for some of the major automotive manufactures.

The manufacturing systems that I develop interface directly to assembly line hardware that in real time collects manufacturing metrics and data
and stores this data in a SQL Server database instance.

The Traceability system also provides engineering and management real time
access to the gathered metrics and data from an internationally
accessible web site that is developed in ASP.net.

Comments and Discussions

 
GeneralMy vote of 5 Pin
Jonathan Canta30-Dec-10 4:47
Jonathan Canta30-Dec-10 4:47 
GeneralMy vote of 1 Pin
Karasb23-Feb-10 10:58
Karasb23-Feb-10 10:58 
GeneralMy vote of 1 Pin
arepetti20-Feb-10 0:02
arepetti20-Feb-10 0:02 
GeneralRe: My vote of 1 Pin
Joe Pitz20-Feb-10 7:09
Joe Pitz20-Feb-10 7:09 
GeneralMy vote of 1 Pin
paragme19-Feb-10 19:08
paragme19-Feb-10 19:08 
GeneralRe: My vote of 1 [modified] Pin
Joe Pitz20-Feb-10 7:08
Joe Pitz20-Feb-10 7:08 
GeneralSorry, not such a good plan Pin
paul.vencill19-Feb-10 8:11
paul.vencill19-Feb-10 8:11 
GeneralRe: Sorry, not such a good plan Pin
Joe Pitz19-Feb-10 8:31
Joe Pitz19-Feb-10 8:31 
GeneralRe: Sorry, not such a good plan Pin
Joe Pitz19-Feb-10 8:55
Joe Pitz19-Feb-10 8:55 
GeneralRe: Sorry, not such a good plan Pin
paul.vencill11-Mar-10 12:06
paul.vencill11-Mar-10 12:06 
GeneralThoughts Pin
PIEBALDconsult19-Feb-10 7:38
professionalPIEBALDconsult19-Feb-10 7:38 
GeneralMy vote of 2 Pin
BigJim6119-Feb-10 7:32
BigJim6119-Feb-10 7:32 
Hasn't this already been done in the Data Access Application Blocks?
GeneralRe: My vote of 2 Pin
Joe Pitz19-Feb-10 8:04
Joe Pitz19-Feb-10 8:04 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.