Tuesday, December 22, 2009

Data Access Layer

· 0 comments

SqlProvider static class

using System;
using System.Data;
using System.Web;
using System.Data.SqlClient;
using System.Collections;
///


/// Data Access Layer
///

public static class SqlProvider
{
// retrive connectio string from clsConnectionString class
public static string connectionString = clsConnectionString.conString;

// return parameter array
public static object[] returnParams = null;

// private static method
private static void AttachParameters(SqlCommand command, SqlParameter[] commandParameters)
{
foreach (SqlParameter p in commandParameters)
{

if ((p.Direction == ParameterDirection.InputOutput) && (p.Value == null))
{
p.Value = DBNull.Value;
}

command.Parameters.Add(p);
}
}
private static void AssignParameterValues(SqlParameter[] commandParameters, object[] parameterValues)
{
if ((commandParameters == null) || (parameterValues == null))
{
return;
}

//if (commandParameters.Length != parameterValues.Length)
//{
// throw new ArgumentException("Parameter count does not match Parameter Value count.");
//}

for (int i = 0, j = parameterValues.Length; i < j; i++)
{
if (commandParameters[i].Direction == ParameterDirection.InputOutput)
{
commandParameters[i].Value = "0";
}
else
{
commandParameters[i].Value = parameterValues[i];
}
}
}
private static void PrepareCommand(SqlCommand command, SqlConnection connection, SqlTransaction transaction, CommandType commandType, string commandText, SqlParameter[] commandParameters)
{

if (connection.State != ConnectionState.Open)
{
connection.Open();
}
command.Connection = connection;
command.CommandText = commandText;
if (transaction != null)
{
command.Transaction = transaction;
}
command.CommandType = commandType;
if (commandParameters != null)
{
AttachParameters(command, commandParameters);
}
return;
}

///
/// Call Execute NonQuery Method :
/// Parameter : spName = Stored Procedure Name, Object[] = Array of stored procedure parameter
/// Return Parameter : (int)Nuber of affected row
///

///
///
///
public static int ExecuteNonQuery(string spName, params object[] parameterValues)
{
if ((parameterValues != null) && (parameterValues.Length > 0))
{
SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(spName);
AssignParameterValues(commandParameters, parameterValues);
return ExecuteNonQuery(CommandType.StoredProcedure, spName, commandParameters);
}
else
{
return ExecuteNonQuery(CommandType.StoredProcedure, spName);
}
}
private static int ExecuteNonQuery(CommandType commandType, string commandText, params SqlParameter[] commandParameters)
{
using (SqlConnection cn = new SqlConnection(connectionString))
{
cn.Open();
return ExecuteNonQuery(cn, commandType, commandText, commandParameters);
}
}
private static int ExecuteNonQuery(SqlConnection connection, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
{

SqlCommand cmd = new SqlCommand();
PrepareCommand(cmd, connection, (SqlTransaction)null, commandType, commandText, commandParameters);
int retval = cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
return retval;
}

///
/// Call Execute NonQuery Method :
/// Parameter : spName = Stored Procedure Name, outputparameterNames = Return parameter array, Object[] = Array of stored procedure parameter
/// Return Parameter : (object[])Return Parameter
///

///
///
///
public static object[] ExecuteNonQuery(string spName, string[] outputparameterNames, params object[] parameterValues)
{
if ((parameterValues != null) && (parameterValues.Length > 0))
{
SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(spName);
AssignParameterValues(commandParameters, parameterValues);
return ExecuteNonQuery(CommandType.StoredProcedure, spName, outputparameterNames, commandParameters);
}
else
{
return ExecuteNonQuery(CommandType.StoredProcedure, spName, outputparameterNames);
}
}
private static object[] ExecuteNonQuery(CommandType commandType, string commandText, string[] outputparameterNames, params SqlParameter[] commandParameters)
{
using (SqlConnection cn = new SqlConnection(connectionString))
{
cn.Open();
return ExecuteNonQuery(cn, commandType, commandText, outputparameterNames, commandParameters);
}
}
private static object[] ExecuteNonQuery(SqlConnection connection, CommandType commandType, string commandText, string[] outputparameterNames, params SqlParameter[] commandParameters)
{

SqlCommand cmd = new SqlCommand();
PrepareCommand(cmd, connection, (SqlTransaction)null, commandType, commandText, commandParameters);
cmd.ExecuteNonQuery();
int i = 0;
returnParams = new object[outputparameterNames.Length];
foreach (string upParam in outputparameterNames)
{
returnParams[i] = cmd.Parameters[upParam].Value;
i++;
}
cmd.Parameters.Clear();
return returnParams;
}

///
/// Call Execute Dataset Method :
/// Parameter : spName = Stored Procedure Name, Object[] = Array of stored procedure parameter
/// Return Parameter : (dataset)Operational Dataset
///

///
///
///
public static DataSet ExecuteDataset(string spName, params object[] parameterValues)
{
if ((parameterValues != null) && (parameterValues.Length > 0))
{
SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(spName);
AssignParameterValues(commandParameters, parameterValues);
return ExecuteDataset(CommandType.StoredProcedure, spName, commandParameters);
}

else
{
return ExecuteDataset(CommandType.StoredProcedure, spName);
}
}
private static DataSet ExecuteDataset(CommandType commandType, string commandText, params SqlParameter[] commandParameters)
{
using (SqlConnection cn = new SqlConnection(connectionString))
{
cn.Open();
return ExecuteDataset(cn, commandType, commandText, commandParameters);
}
}
private static DataSet ExecuteDataset(SqlConnection connection, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
{
SqlCommand cmd = new SqlCommand();
PrepareCommand(cmd, connection, (SqlTransaction)null, commandType, commandText, commandParameters);

SqlDataAdapter da = new SqlDataAdapter(cmd);
DataSet ds = new DataSet();
da.Fill(ds);
cmd.Parameters.Clear();
return ds;
}

///
/// Call Execute Scalar Method:
/// Parameter : spName = Stored Procedure Name, Object[] = Array of stored procedure parameter
/// Return Parameter : (object)Signle row value
///

///
///
///
public static object ExecuteScalar(string spName, params object[] parameterValues)
{
if ((parameterValues != null) && (parameterValues.Length > 0))
{
SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(spName);
AssignParameterValues(commandParameters, parameterValues);
return ExecuteScalar(CommandType.StoredProcedure, spName, commandParameters);
}
else
{
return ExecuteScalar(CommandType.StoredProcedure, spName);
}
}
private static object ExecuteScalar(CommandType commandType, string commandText, params SqlParameter[] commandParameters)
{
using (SqlConnection cn = new SqlConnection(connectionString))
{
cn.Open();

return ExecuteScalar(cn, commandType, commandText, commandParameters);
}
}
private static object ExecuteScalar(SqlConnection connection, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
{
SqlCommand cmd = new SqlCommand();
PrepareCommand(cmd, connection, (SqlTransaction)null, commandType, commandText, commandParameters);

object retval = cmd.ExecuteScalar();

cmd.Parameters.Clear();
return retval;
}

///
/// Call Execute Reader Method:
/// Parameter : spName = Stored Procedure Name, Object[] = Array of stored procedure parameter
/// Return Parameter : (datareader)Operational Data Reader
///

///
///
///
public static SqlDataReader ExecuteReader(string spName, params object[] parameterValues)
{
if ((parameterValues != null) && (parameterValues.Length > 0))
{
SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(spName);
AssignParameterValues(commandParameters, parameterValues);
return ExecuteReader(CommandType.StoredProcedure, spName, commandParameters);
}

else
{
return ExecuteReader(CommandType.StoredProcedure, spName);
}
}
private static SqlDataReader ExecuteReader(CommandType commandType, string commandText, params SqlParameter[] commandParameters)
{
SqlConnection cn = new SqlConnection(connectionString);

cn.Open();
return ExecuteReader(cn, commandType, commandText, commandParameters);

}
private static SqlDataReader ExecuteReader(SqlConnection connection, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
{
SqlCommand cmd = new SqlCommand();
try
{
PrepareCommand(cmd, connection, (SqlTransaction)null, commandType, commandText, commandParameters);
SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
return reader;
}
finally { cmd.Parameters.Clear(); }
}
}

public sealed class SqlHelperParameterCache
{
private SqlHelperParameterCache() { }
private static Hashtable paramCache = Hashtable.Synchronized(new Hashtable());
private static string connectionString = clsConnectionString.conString;
private static SqlParameter[] DiscoverSpParameterSet(string spName, bool includeReturnValueParameter)
{
using (SqlConnection cn = new SqlConnection(connectionString))
using (SqlCommand cmd = new SqlCommand(spName, cn))
{
cn.Open();
cmd.CommandType = CommandType.StoredProcedure;

SqlCommandBuilder.DeriveParameters(cmd);

//int c = cmd.Parameters.Count;
//for (int i = 0; i < c; i++)
//{
// if (cmd.Parameters[i].Direction == ParameterDirection.InputOutput)
// cmd.Parameters.Remove(cmd.Parameters[i]);
//}
if (!includeReturnValueParameter)
{
cmd.Parameters.RemoveAt(0);
}

SqlParameter[] discoveredParameters = new SqlParameter[cmd.Parameters.Count];
cmd.Parameters.CopyTo(discoveredParameters, 0);

return discoveredParameters;
}
}

private static SqlParameter[] CloneParameters(SqlParameter[] originalParameters)
{

SqlParameter[] clonedParameters = new SqlParameter[originalParameters.Length];
for (int i = 0, j = originalParameters.Length; i < j; i++)
{
clonedParameters[i] = (SqlParameter)((ICloneable)originalParameters[i]).Clone();
}

return clonedParameters;
}

public static void CacheParameterSet(string commandText, params SqlParameter[] commandParameters)
{
string hashKey = connectionString + ":" + commandText;
paramCache[hashKey] = commandParameters;
}

public static SqlParameter[] GetCachedParameterSet(string commandText)
{
string hashKey = connectionString + ":" + commandText;
SqlParameter[] cachedParameters = (SqlParameter[])paramCache[hashKey];
if (cachedParameters == null)
{
return null;
}
else
{
return CloneParameters(cachedParameters);
}
}
public static SqlParameter[] GetSpParameterSet(string spName)
{
return GetSpParameterSet(spName, false);
}
public static SqlParameter[] GetSpParameterSet(string spName, bool includeReturnValueParameter)
{
string hashKey = connectionString + ":" + spName + (includeReturnValueParameter ? ":include ReturnValue Parameter" : "");
SqlParameter[] cachedParameters;
paramCache[hashKey] = null;
cachedParameters = (SqlParameter[])paramCache[hashKey];
if (cachedParameters == null)
{
cachedParameters = (SqlParameter[])(paramCache[hashKey] = DiscoverSpParameterSet(spName, includeReturnValueParameter));
}
return CloneParameters(cachedParameters);
}
}

Connection String class.
using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;


///
/// clsConnectionString class returns the connection string
///

public static class clsConnectionString
{
public static string conString
{
get { return ConfigurationManager.AppSettings["conString"].ToString(); }
}

}

How to call method

1. Execute Non Query without input parameter and return parameter.
public void Insert()
{
SqlProvider.ExecuteNonQuery("USP_ADMIN_LOG_INSERT");
}

2. Execute Non Query with input parameter and without return parameter.
public void Insert()
{
SqlProvider.ExecuteNonQuery("USP_ADMIN_LOG_INSERT", this.ID,this.Name,......);
//Input parameter is Property of the class.
}


3. Execute Non Query with input paramter and return parameter.
private Boolean Insert()
{
string[] OutParams = new string[1];
OutParams[0] = "@OutID"; -- this is out id variable that is define in stored procedure.
object[] outParam = SqlProvider.ExecuteNonQuery("USP_ADMIN_INSERTUPDATE",OutParams, this.ID);
if (outParam != null && outParam.Length == 1)
{
this.OutID = Convert.ToInt32(outParam[0].ToString());
return true;
// this.OutID is property and outPram[0] returns return value from SP
}
else
return false;
}


You can call Execute Dataset, Execute Scaler and Execute Reader same way.