Introduction:
It is a known fact that, most of the time we create applications for Create, Select, Update, Delete. And every time, we end up with writing similar set of code for every execution of our stored procedures.
Our approach is constantly repeated like creating connection, creating command object, adding parameters etc and finally to execute.
I am just trying to write a wrapper class for the data application block.
Pre-assumptions:
We assume that, the database is MS SQL Server (widely used) we take advantage of Microsoft Data Application Block. I am attaching the dll file.
Why do I need it?
Mainly Re-Usability of code.
Once you come up with a generic class for your data access, you can use it anywhere. Once you are finished with it, you can achieve the task with very less amount of coding.
Limitations:
For now, the code is only for stored procedures. Extend it as you wish. Please overload the methods if you wish to have. Likewise, you can also have extended methods as we have in our SqlHelper class. (Data application block).
Code:
using System;
using System.Data;
using System.Collections;
using System.Collections.Generic;
using System.Data.SqlClient;
using Microsoft.ApplicationBlocks.Data;
/// <summary>
/// This is a generic DAC layer.
/// </summary>
namespace Ellaar.DAC
{
public class DAC
{
// Execute the stored procedure
public static DataSet Execute(string StoredProcName)
{
// Generic collection of my DacParameter object
List<Parameters.DacParameter> ParameterPairs = Parameters.MyParameters;
DataSet ds;
// Validation for Parameter collection
try
{
// if you want to force the user to give parameters, use this code
if (ParameterPairs.Count <= 0)
{
//throw new Exception("Invalid parameter supply. Check your BL Method");
}
}
catch (Exception Ex)
{
throw (Ex);
}
// Get connection String - I've created this for web app. Hence it is reading from web.config.
// You can modify this as you wish.
System.Configuration.Configuration rootWebConfig = System.Web.Configuration.WebConfigurationManager.OpenWebConfiguration(System.Web.Hosting.HostingEnvironment.ApplicationVirtualPath);
string ConnString = "";
if (0 < rootWebConfig.ConnectionStrings.ConnectionStrings.Count)
{
ConnString = rootWebConfig.ConnectionStrings.ConnectionStrings["DfltConnection"].ToString();
}
// Using a Transaction Here: To ensure data integrity
SqlTransaction Trans;
using (SqlConnection Conn = new SqlConnection(ConnString))
{
Conn.Open();
Trans = Conn.BeginTransaction();
try
{
// If there is any parameters then iterate through the collection
// and bind it
if (ParameterPairs.Count > 0)
{
SqlParameter[] arparams = new SqlParameter[ParameterPairs.Count];
int Count = 0;
foreach (Parameters.DacParameter pObject in ParameterPairs)
{
arparams[Count] = new SqlParameter(pObject.Name, pObject.Type);
arparams[Count].Value = pObject.Value;
//Increment counter
Count++;
}
// Execute stored procedure
ds = SqlHelper.ExecuteDataset(ConnString, CommandType.StoredProcedure, StoredProcName, arparams);
}
// else I just need to execute it Eg: Procedures that have only SELECT queries
else
{
ds = SqlHelper.ExecuteDataset(ConnString, CommandType.StoredProcedure, StoredProcName);
}
// Yes, go ahead with Committing the transaction
Trans.Commit();
}
catch (Exception Ex)
{
Trans.Rollback();
throw (Ex);
}
finally
{
if (Conn.State == ConnectionState.Open)
{
Conn.Close();
Conn.Dispose();
}
Parameters.MyParameters.Clear();
}
return ds;
}
}
// My Parameters class
public class Parameters
{
protected internal static List<DacParameter> MyParameters = new List<DacParameter>();
public static void Add(string Name, SqlDbType dbtype, object value)
{
DacParameter KeyValuePair = new DacParameter();
KeyValuePair.Name = Name;
KeyValuePair.Type = dbtype;
KeyValuePair.Value = value;
MyParameters.Add(KeyValuePair);
}
// class param, type , value class : Aggregation
public class DacParameter
{
private string _ParameterName;
public string Name
{
get
{
return _ParameterName;
}
set
{
_ParameterName = value;
}
}
private System.Data.SqlDbType _Type;
public System.Data.SqlDbType Type
{
get
{
return _Type;
}
set
{
_Type = value;
}
}
private object _value;
public object Value
{
get
{
return _value;
}
set
{
_value = value;
}
}
}
}
}
}
How do I use it? (Some examples):
- To create a State
DAC.Parameters.Add("@StateName", SqlDbType.VarChar, StateName);
DAC.Parameters.Add("@StateAbbr", SqlDbType.VarChar, StateAbbr);
DAC.Parameters.Add("@MapImagePath", SqlDbType.VarChar, MapImagePath);
DAC.Execute("spInsertState");
- To create a City
DAC.Parameters.Add("@CityName", SqlDbType.VarChar, CityName);
DAC.Parameters.Add("@MetroId", SqlDbType.Int, MetroId);
DAC.Parameters.Add("@MapImagePath", SqlDbType.VarChar, MapImagePath);
DAC.Execute("spInsertCity");
- SPs without any parameters
DataSet ds = DAC.Execute("spGetStates");
Conclusion:
I hope this could of helpful for you. Please try this and extend the class as you wish. Please do post your suggestions and feedback.