.NET SQL Server Stored Procedure Data Access Layer

• Download source - 1.5 KB

Introduction

Many developers do all of their data access through stored procedures, either by choice or by mandate. This is a clean, simple alternative to using the Entity Framework for stored procedure data access in a .NET/SQL Server environment.

Background

The SqlServerStoredProcedureDataAccessLayer namespace contains one public class called SqlDatabase.

The methods in the SqlDatabase class are very transparently built upon standard SqlData methods. If you get an exception, it is very easy to debug because these methods do not obfuscate the underlying SqlCommand methods.

The SqlDatabase methods and their underlying SqlData methods are:

ExecuteScalar	SqlCommand.ExecuteScalar
ExecuteNonquery	SqlCommand.ExecuteNonquery
ExecuteReader	SqlCommand.ExecuteReader
GetDataTable	SqlDataAdapter.Fill
GetDataSet	SqlDataAdapter.Fill

These methods are used by GetDataTable and GetDataSet but can be called directly.

FillDataTable	SqlDataAdapter.Fill
FillDataSet	SqlDataAdapter.Fill

The above methods use supporting methods to get connection strings and SqlCommand objects. The supporting methods are also public and can be useful in situations such as getting a different connection string from web.config.

This class and its methods can be precompiled and added to a project as an assembly reference, or the class can be added to a project as source code. The class and its methods are static and reference the SQL Server specific SqlData objects. It is a fairly trivial task to update this code to use ADO.NET or to not be static, depending on your needs.

Using the Code

After adding a using statement to reference the SqlServerStoredProcedureDataAccessLayer namespace, the SqlDatabase object can be referenced.

using SqlServerStoredProcedureDataAccessLayer;

Database Connection String

Each method has overloads that look in web.config for a connection string named "SqlDatabase". You have the option of using this pre-named connection string or passing in your own database connection string. There is a method called GetConnectionString for retrieving a different connection string that you can leverage as well.

Connection string examples. The examples call a stored procedure named "spGetSomeInt":

int iSomething = (Int32)SqlDatabase.ExecuteScalar("SelectSomeInt");
//This example looks for a default connection string called ""SqlDatabase"" in your web.config file.
//This is the easy path. Name your connection string "SqlDatabase" 
//and you never have to think about it again.

or:

int iSomething = (Int32)SqlDatabase.ExecuteScalar
("SelectSomeInt",SqlDatabase.GetConnectionString("SecondDatabase"));
//This example looks for a connection string named 
//"SecondDatabase  " in the ConnecctionStrings section of web.config.

or:

int iSomething = (Int32)SqlDatabase.ExecuteScalar("SelectSomeInt", sConnString);
//Where sConnString is a string variable containing your connection string.

ExecuteScalar example

iTransNumber = (Int32)SqlDatabase.ExecuteScalar("SelectNextTransNumber");

ExecuteNonquery example

SqlDatabase.ExecuteNonquery("SaveMessage", new SqlParameter("@Message", sMessage));
//Executes a stored procedure called "spSaveMessage", 
//passing one SqlParameter named "@Message".

GetDataTable example

DataTable myDataTable = SqlDatabase.GetDataTable
("SelectOrders", new SqlParameter("@CustomerId", iCustomerId ));

Passing in multiple SqlParameters example

//create and populate a list of type SqlParameter
List<SqlParameter> dbParameters = new List<SqlParameter >();
dbParameters.Add(new SqlParameter("@FirstName", sFirstName));
dbParameters.Add(new SqlParameter("@LastName" , sLastName ));

//Pass the stored procedure name and the List into any SqlDatabase data access method.
Guid newRecordGuid = (Guid)SqlDatabase.ExecuteScalar("SaveNewRecord", dbParameters);

ExecuteReader example

SqlDataReader myDataReader = SqlDatabase.ExecuteReader("SelectMyData");