There are different ways to access data access functions in your applications. But it is always better to arrange these common set of functions into a Data Access Layer. This layer is the most important layer in a multi-tier application. You can scale up this layer to make it more generic to handle multiple databases. This article only focuses on SQL Server Database.
Here is the code for creating the Data Access Layer (DAL), I will be referring it as DAL in the rest of my article. There are 2 basic functions in this DAL namely ExecuteProcedureReader & ExecuteNonQueryProcedure. Create a Class Library type project and include replace this source code in the code-behind page.
using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Text;
namespace MyApp.DAL
{
///
/// SqlServerDAL class is the interface to the SQL Server database. All required methods for accessing the
/// SQL database will be provided in this class and hence acts as the Data Access Layer (DAL) between
/// front-end application and the SQL Server database.
///
public class SqlServerDAL
{
///
/// objSqlServerDAL is the single instance variable for this class
///
private static SqlServerDAL objSqlServerDAL = null;
///
/// The object which holds the sql connection
///
private SqlConnection objSqlConnection = null;
///
/// The constructor should be a private method for a singleton class. This constructor
/// will initialize the sql connection
///
///connection string variable
private SqlServerDAL(string connString)
{
objSqlConnection = new SqlConnection(connString);
}
///
/// GetInstance method will return the current active instance of the connection class. If there is no
/// active instance, it will create one and will return the new instance
///
///connection string
///
/// Open method will open the current instance of the connection object if the object is in closed state
///
public void Open()
{
try
{
if (objSqlConnection.State == ConnectionState.Closed)
{
objSqlConnection.Open();
}
}
catch (Exception e)
{
throw e;
}
}
///
/// Dispose method will close and dispose the connection object that was created earlier.
///
public void Dispose()
{
try
{
if (objSqlConnection.State != ConnectionState.Closed)
{
this.objSqlConnection.Close();
//this.objSqlConnection.Dispose();
}
}
catch (Exception e)
{
throw e;
}
}
///
/// ExecuteProcedureReader is the method by which a Stored Procedure is called with the list of
/// parameters and its values. This method will execute the stored proc and returns the resultset
/// on successfull completion, else throws the error.
///
///Name of the Stored Proc that to be executed
///All required parameters in the Hashtable format with the format
/// - @OUT_ prefix for output parameters
/// - @IN_ prefix for input parameters
///
///The resultant dataset
public DataSet ExecuteProcedureReader(string StoredProcName, Hashtable parameterList)
{
DataSet objresultSet = new DataSet();
SqlDataAdapter objSqlDataAdapter = null;
int counter = 0;
try
{
SqlCommand objSqlCommand = new SqlCommand(StoredProcName, objSqlConnection);
objSqlCommand.CommandType = CommandType.StoredProcedure;
if (parameterList != null)
{
foreach (string parametername in parameterList.Keys)
{
objSqlCommand.Parameters.AddWithValue(parametername, parameterList[parametername]);
if (parametername.StartsWith("@OUT_"))
{
objSqlCommand.Parameters[counter].Direction = ParameterDirection.Output;
}
else
{
objSqlCommand.Parameters[counter].Direction = ParameterDirection.Input;
}
counter++;
}
}
objSqlDataAdapter = new SqlDataAdapter(objSqlCommand);
objSqlDataAdapter.Fill(objresultSet);
}
catch (Exception e)
{
throw e;
}
return objresultSet;
}
///
/// ExecuteNonQueryProcedure is the method by which a Stored Procedure is called with the list of
/// parameters and its values. This method will execute the stored proc and returns the status of
/// on completion. Any exceptions during the process will throws the error.
///
///Name of the Stored Proc that to be executed
///All required parameters in the Hashtable format with the format
/// - @OUT_ prefix for output parameters
/// - @IN_ prefix for input parameters
///
///The result of the Stored Proc execution
public int ExecuteNonQueryProcedure(string StoredProcName, Hashtable parameterList)
{
int returnValue = 0;
int counter = 0;
try
{
SqlCommand objSqlCommand = new SqlCommand(StoredProcName, objSqlConnection);
objSqlCommand.CommandType = CommandType.StoredProcedure;
if (parameterList != null)
{
foreach (string parametername in parameterList.Keys)
{
objSqlCommand.Parameters.AddWithValue(parametername, parameterList[parametername]);
if (parametername.StartsWith("@OUT_"))
{
objSqlCommand.Parameters[counter].Direction = ParameterDirection.Output;
}
else
{
objSqlCommand.Parameters[counter].Direction = ParameterDirection.Input;
}
counter++;
}
}
returnValue = objSqlCommand.ExecuteNonQuery();
}
catch (Exception e)
{
throw e;
}
return returnValue;
}
}
}
How to use this DAL Class:
using MyAPP.DAL;
///
/// GetApplicationList method will retrieve all applications and will return a Dataset will this list.
///
///Dataset
///
public DataSet GetApplicationList(int appID)
{
DataSet resultant = null;
Hashtable parameterlist = new Hashtable();
parameterlist.Add("@IN_CAT_ID", Convert.ToInt32(appID));
try
{
SqlServerDAL objSqlServerDAL = SqlServerDAL.GetInstance(ConfigurationManager.ConnectionStrings["SQLConnStr"].ToString().Trim());
objSqlServerDAL.Open();
resultant = objSqlServerDAL.ExecuteProcedureReader("USP_APP_GET", parameterlist);
objSqlServerDAL.Dispose();
}
catch (Exception e)
{
throw e;
}
finally
{
}
return resultant;
}
