Custom functions recieving StoredProcedure Name and Command Parameters to reduce code duplicacy, by redundant open() and close() sql Connection calls.
Here is the sample code for 3 static functions to be written in your SqlHelper or DAL or any other Data Related class.
public static void DBExecuteNonQuery(string storedProcedure,SqlCommand command)
{
using(SqlConnection connection = new SqlConnection(ConnectionString))
{
connection.Open();
command.CommandText = storedProcedure;
command.Connection = connection;
command.CommandType = CommandType.StoredProcedure;
command.ExecuteNonQuery();
}
}
1.DBExecuteNonQuery is receiving two parameters one is the name of the stored procedure and other is the prebuild command having all the parameter names and values, later in the function we open the connection and provide the CommandText as the recieved stored Procedure name, command connection as connection,command type as commandtype.storedprocedure and then we execute the command.ExeuteNonQuery statement.
2.Similarly we do things in other functions the only difference is of the return type and the execute function.
public static Int32 DBExecuteScalar(string storedProcedure, SqlCommand command)
{
Int32 value = 0;
using (SqlConnection connection = new SqlConnection(ConnectionString))
{
connection.Open();
command.CommandText = storedProcedure;
command.Connection = connection;
command.CommandType = CommandType.StoredProcedure;
value = (Int32)command.ExecuteScalar();
}
return value;
}
3.DBExecuteScalar takes the same arguments but returns an Int32 type value and Executes the stored procedure using command.ExecuteScalar() function
public static DataSet DBExecuteReader(string storedProcedure, SqlCommand command)
{
SqlDataReader reader = null;
using (SqlConnection connection = new SqlConnection(ConnectionString))
{
connection.Open();
command.CommandText = storedProcedure;
command.Connection = connection;
command.CommandType = CommandType.StoredProcedure;
reader = command.ExecuteReader();
//Here we can write any custom code to convert the reader to a dataset because when the connection will be closed if we return reader it will complain as it is connected type.
}
return dataSet;
}
4.DBExecuteReader also takes similar arguments but return a SqlReader which you can iterate through to get the desired output.
5.These functions might come handy when you are using multiple opening and closing connection calls it might not help in performance but it make the code look clean and reduce the duplicacy.
6.ConnectionString used above is a static property in the SqlHelper Class which can be used to retreive the connection string from web.config in web applications and app.config in Winforms applications.
public static string ConnectionString
{
get{return System.Configuration.ConfiguraitonManager.ConnectionStrings["connectionsstringname"].ConnectionString;}
}
7.Now we can directly use these functions as following code snippet shows:-
SqlCommand command = new SqlCommand();
command.Parameters.Add("@firstName", SqlDbType.VarChar).Value = person.FirstName;
command.Parameters.Add("@middleName", SqlDbType.VarChar).Value = person.MiddleName;
command.Parameters.Add("@lastName", SqlDbType.VarChar).Value = person.LastName;
command.Parameters.Add("@gender", SqlDbType.Bit).Value = person.Gender;
command.Parameters.Add("@email", SqlDbType.VarChar).Value = person.Email;
command.Parameters.Add("@address", SqlDbType.VarChar).Value = person.Address;
command.Parameters.Add("@qualification", SqlDbType.VarChar).Value = person.Qualification;
command.Parameters.Add("@password", SqlDbType.VarChar).Value = person.Password;
SqlHelper.DBExecuteNonQuery("usp_InsertPerson", command);
If you want more modularity and donot want to pass command from your business logic layer and want to make this command only in your Data Access Layer than you can add the following methods to your SqlHelper.cs file
public static DataSet ConvertToSqlCommand(string storedProcedure,SortedList values)
{
SqlCommand command = new SqlCommand();
foreach (KeyValuePair value in values)
{
Type type = value.Value.GetType();
SqlDbType sqlDBType = ConverToSqlDBType(type);
command.Parameters.Add(value.Key, sqlDBType).Value = value.Value;
//SqlDbType value.Value.GetType()
}
DataSet dataSet = DBExecuteReader(storedProcedure, command);
return dataSet;
}
public static void ConvertToSqlCommandForNonQuery(string storedProcedure, SortedList values)
{
SqlCommand command = new SqlCommand();
foreach (KeyValuePair value in values)
{
Type type = value.Value.GetType();
SqlDbType sqlDBType = ConverToSqlDBType(type);
command.Parameters.Add(value.Key, sqlDBType).Value = value.Value;
}
DBExecuteNonQuery(storedProcedure, command);
}
These two overloaded methods take storedprocedure name and sortedlist ie key value pair as parameters and convert them to sqlcommands and execute the related DBExecute……() function and return the output.
The problem with this approach is that we have to convert System.Type to System.Data.Type ie SqlDBType which is an enumeration thus a probable limitation is that it cannot understand which string is to be converted to char,varchar and nvarchar for rest it works fine.Given below is the method which achieves this you can add another entries if you want.
public static SqlDbType ConverToSqlDBType(Type systemType)
{
if (systemType.Name == "Int32")
return SqlDbType.Int;
else if (systemType.Name == "String")
return SqlDbType.VarChar;
else if (systemType.Name == "DateTime")
return SqlDbType.DateTime;
else
return SqlDbType.VarChar;
}
