Click here to Skip to main content
15,867,686 members
Articles / Programming Languages / C#
Tip/Trick

SQL Helper Class Microsoft .NET Utility

Rate me:
Please Sign up or sign in to vote.
4.77/5 (23 votes)
4 Mar 2013CPOL7 min read 176.5K   120   33   10
SQL helper utility for Data Access Layer.

Introduction

The Microsoft .NET Framework consists of ADO.NET which enables developers to interact with the database. ADO.NET provides many rich features that can be used to retrieve and display data in a number of ways. Apart from the flexibility provided by ADO.NET, sometimes we find ourselves repeating the same code again and again. Consider that at some point in our application we need to pass some parameters and retrieve some information from the database. We can perform this task by writing 5-6 lines of code which is cool. But when later we need to pass parameters again we have to write those 5-6 lines again, which is not cool.

For this reason Microsoft introduced the Data Access Application Block which can be used to perform common tasks with less code. Another good reason to use the Microsoft .NET Data Access Application Block is that it makes the application consistent, meaning that if different companies are using the Data Access Block then you will find the code more easier to understand. You can download the Microsoft .NET Data Access Application Block from http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnpag2/html/daab.asp. Download the Application Block and simply install it. Let's see how we can use it.

Using the code

Using the Microsoft .NET Data Access Application Block:

In this article I have used the Microsoft .NET Data Access Application Block Version 2.0. Once you download and install the application block you need to refer it in your application. For this first you need to create the .dll file for the Microsoft .NET Data Access Application Block project. You can simply add the Data Access Application Block project in your current project and build the solution (this can also be performed using the command line tools). Once the solution is built, the .dll file is created under the bin directory. Delete the Data Access Project from your current project as you only need it to create the .dll. And now add a reference in your project which will refer to Microsoft.ApplicationBlock.Data. Once you have made the reference you are ready to use the Application Block in your project.

Accessing data without Data Access Application Block:

Let's first see how we can access data without using the Application Block so that we can compare the flexibility of both approaches. Below is a simple example that inserts two parameters into the database. You can note that as the number of parameters increases the lines of code also increases.

C#
string connectionString = (string) 
ConfigurationSettings.AppSettings["ConnectionString"]; 
SqlConnection connection = new SqlConnection(connectionString);
SqlCommand command = new SqlCommand("INSERT_PERSON",connection);
command.CommandType = CommandType.StoredProcedure;
command.Parameters.Add(new SqlParameter("@Name",SqlDbType.NVarChar,50));
command.Parameters["@Name"].Value = txtName.Text;
command.Parameters.Add(new SqlParameter("@Age",SqlDbType.NVarChar,10));
command.Parameters["@Age"].Value = txtAge.Text;
connection.Open();
command.ExecuteNonQuery();
connection.Close();

As you can see we had to write a lot of code to insert the parameters into the database. Now suppose that you have 10 parameters instead of two, it would take you an hour to write a simple Insert module. Let's see how you can accomplish this using the Microsoft .NET Data Access Application Block.

C#
using Microsoft.ApplicationBlocks.Data;
SqlHelper.ExecuteNonQuery(connection,"INSERT_PERSON", 
   new SqlParameter("@Name",txtName.Text) ,new SqlParameter("@Age",txtAge.Text) );

As you see in the above code we perform the whole operation in a single line instead of writing 5-6 lines. First of all you should always add the namespace Microsoft.ApplicationBlocks.Data without using the namespace you will not be able to use the Application Block. The next interesting thing that you might note is the SqlHelper class. The SqlHelper class is developed by Microsoft developers which contains the static methods to access the database. You can view the SqlHelper class by opening it in any word editor.

Let's see some more features of the Microsoft Data Access Application Block.

Retrieving Multiple Rows using SqlDataReader and Data Access Application Block

Consider a situation that you need to retrieve multiple rows from the database. This retrieval can be for only displaying purposes and you want this task to be completed very fast. Since you only need to display the rows and you need it very fast your best bet is to use SqlDataReader since its a forward only reader. Lets see how you can use SqlDataReader to get the rows you wanted in an efficient and quick manner.

C#
using Microsoft.ApplicationBlocks.Data;
SqlDataReader dr = SqlHelper.ExecuteReader(connection,CommandType.StoredProcedure,"SELECT_PERSON");

STORED PROCEDURE SELECT_PERSON:

SQL
SELECT * FROM tblPerson;

As you can see executing the reader is pretty simple. All you have to do is pass few parameters and that's it and it will return the datareader object which you can use to bind to the datagrid. Also remember that Execute Reader method of the SqlHelper class has several overloads which you can use according to your needs. You can pass parameters or simple execute a simple procedure like I did.

I have also shown the stored procedure which simple selects all the rows from the tblPerson and returns them.

Retrieving Multiple Rows using DataSet

You can also use a dataset to retrieve multiple rows. The question that comes to your mind right now should be that when should you use DataReader and when you should use DataSet. You should use DataReader when your sole purpose is to display the data to the end user. Since datareader is a forward only reader its very fast in reading the records. SqlDataAdapter also uses SqlDataReader when reading records from the database.

If on the other hand you want are in a distributed environment and want your data to travel between different layers than using a DataSet will be a better choice.

C#
// Selects all the rows from the database 
DataSet ds = SqlHelper.ExecuteDataset(connection,CommandType.StoredProcedure,"SELECT_DATA"); 
// Sends the parameters and returns the dataset 
DataSet ds = SqlHelper.ExecuteDataset(connection,CommandType.StoredProcedure,
  new SqlParameter("@Name",txtName.Text), new SqlParameter("@Age",txtAge.Text));

In the above code you can see that I showed two ways of using ExecuteDataSet method. The method has many overloads that you can use to satisfy your needs. The ExecuteDataSet method will be the most commonly used method you will use in your application.

Retrieving a Single Row

Sometimes you have a need to retrieve a single row instead of group of rows. Whenever you need to retrieve a single row you will have to change your stored procedure. I am not saying that this is the only way to referring to a single row since you can retrieve all the rows into a dataset and than pick the row you like. I am talking about retrieving a single row from the database. Lets see what you need to do in your stored procedure to get one row out of it.

Stored Proc:

SQL
CREATE PROCEDURE getPersonDetails 
@PersonID int, 
@Name nvarchar(40) OUTPUT,
@Age nvarchar(40) OUTPUT 
AS 
SELECT @Name = Name,
@Age = Age
FROM tblPerson
WHERE PersonID = @PersonID

As you see in the stored procedure that we have marked the parameters with OUTPUT which means that when the execution of stored procedure is completed those parameters with OUTPUT keyword will be returned to the caller. Let's now see the C# code of how we can use to retrieve a single row.

C#
SqlParameter [] arParms = new SqlParameter[3];
// @PersonID Input Parameter
arParms[0] = new SqlParameter("@PersonID", SqlDbType.Int );
arParms[0].Value = personID;
// @ProductName Output Parameter
arParms[1] = new SqlParameter("@Name", SqlDbType.NVarChar, 40);
arParms[1].Direction = ParameterDirection.Output;
// @UnitPrice Output Parameter
arParms[2] = new SqlParameter("@Age", SqlDbType.NVarChar,40);
arParms[2].Direction = ParameterDirection.Output;
// Execute the stored procedure
SqlHelper.ExecuteNonQuery( connectionString, CommandType.StoredProcedure, "getPersonDetails", arParms);
// create a string array of return values and assign values returned from stored procedure
string [] arReturnParms = new string[2];
arReturnParms[0] = arParms[1].Value.ToString();
arReturnParms[1] = arParms[2].Value.ToString();

Explanation of the code:

  1. First we made the array of type SqlParameters. The reason that we made the array is because we need to pass several parameters.
  2. Then we assign parameter whose index is '0' to the parameter that we like to send which, in this case is PersonID.
  3. We did not specify the direction of the Parameter. If the direction is not specified the parameter is considered to be an input parameter by default.
  4. Later we defined parameter Age which is on index '1' in the parameter array.
  5. This time we specified the parameter direction since we want the parameter value to be returned when the SQL query completes.
  6. Next we use the ExecuteNonQuery method of the SqlHelper class to execute the query.
  7. Finally, we retrieved the values from the parameters. Values are retrieved since parameters are marked with direction output and also that in the stored procedure we have marked the variables as OUTPUT variables which will be returned to the caller program.

You can perform the same operations in a number of ways. I like it this way since its more clear. As you can see you first created an array of Parameters. After the array is created you simply assigns the value and also informs the C# compiler that which one of the parameters are OUTPUT.

Retrieving XML Data

You can not only retrieve data from the database but also from any XML File. Lets see a small code sample which shows this operation.

C#
SqlConnection conn = new SqlConnection(connectionString); conn.Open();
// Call ExecuteXmlReader static method of SqlHelper class that returns an XmlReader
// We pass in an open database connection object, command type, and command text
XmlReader xreader = SqlHelper.ExecuteXmlReader(conn, CommandType.Text, "SELECT * FROM Products FOR XML AUTO" );
return xreader;

Explanation of the code:

  1. First we made the simple SQL connection
  2. The ExecuteXmlReader method is responsible for fetching the xml from the table. FOR XML is a keyword that is used to retrieve XML fragments.
  3. And finally we returned the XML reader which is populated with the result that came from the ExecuteXmlReader method of the SqlHelper class.

Points of Interest

The SqlHelper class just makes the Data Access Layer more compact and reusable across various applications.

License

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


Written By
India India
I am a Software Developer with 2 years of working Experience on C#,ASP.NET,SQL SERVER,Javascript,Jquery

Comments and Discussions

 
GeneralI Vote 5 for this Excellent Article Pin
DivyaNaidu48620-Oct-14 2:54
DivyaNaidu48620-Oct-14 2:54 
GeneralSource Code Pin
Ricardo Ribani13-Sep-14 6:08
Ricardo Ribani13-Sep-14 6:08 
GeneralMy vote of 3 Pin
Ranisz9-Jan-14 15:30
Ranisz9-Jan-14 15:30 
QuestionCan i use oraclesqldeveloper instead of sqlserver Pin
kingsa21-Nov-13 1:42
kingsa21-Nov-13 1:42 
GeneralMy vote of 5 Pin
Er Atul Sharma24-Jul-13 23:16
Er Atul Sharma24-Jul-13 23:16 
Questionnull values Pin
Mujtaba Faizan22-Jul-13 0:21
Mujtaba Faizan22-Jul-13 0:21 
GeneralMy vote of 4 Pin
nevergiveupc5-Mar-13 1:47
nevergiveupc5-Mar-13 1:47 
GeneralRe: My vote of 4 Pin
Rakesh S S15-Mar-13 5:58
Rakesh S S15-Mar-13 5:58 
GeneralMy vote of 5 Pin
fecaloid monster4-Mar-13 22:53
fecaloid monster4-Mar-13 22:53 
GeneralRe: My vote of 5 Pin
Rakesh S S15-Mar-13 6:00
Rakesh S S15-Mar-13 6:00 

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.