Interface to work with ADO.Net sample and it will work like DB helper class
using System;
using System.Data;
using System.Configuration;
using System.Web.Security;
using System.Data.SqlClient;
using System.Collections;
using System.Xml;
using System.Web.UI;
using System.Web;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Web.Caching;
using System.Text;
/// <summary>
/// Summary description for DataBaseInterface
/// </summary>
///
namespace Ramosh.Support.DataLayer
{
public class DBInterface : BasePage<DBInterface>
{
/// <summary>
/// Enum of DataTables in DataSet
/// </summary>
public enum Tables
{
Statuses = 0,
Types,
Categories
}
public enum eGetValueDropDown
{
STATE ,
CITY ,
LOCATION
}
#region Properties & Fields
/// <summary>
/// Property to manage the DataSet of static data
/// </summary>
private static DataSet StaticDataSet
{
get
{
// Get DataSet from Cache
Cache oCache = HttpContext.Current.Cache;
DataSet ds = (DataSet)oCache[StaticDataSetCache];
// If the DataSet is not in Cache then generate it and cache it
if (ds == null)
{
ds = CacheStaticData();
}
return ds;
}
}
private const string StaticDataSetCache = "StaticDataSetCache";
#endregion
/// <summary>
/// Bind static data to a ListControl (DropDownList / ListBox / RadioButtonList / CheckBoxList) with a header value
/// </summary>
/// <param name="listControl"></param>
/// <param name="tableIndex"></param>
/// <param name="header"></param>
public static void Bind(ListControl listControl, Tables tableIndex, string header)
{
// Get the DataTable from the cached DataSet
DataTable dt = GetData(tableIndex);
// Set the DataValueField and DataTextField of the LsitControl
listControl.DataValueField = dt.Columns[0].ToString();
listControl.DataTextField = dt.Columns[1].ToString();
// Bind the data to the LsitControl
listControl.DataSource = dt;
listControl.DataBind();
// Add the header row if required
if (header.Length > 0)
{
listControl.Items.Insert(0, new ListItem("- " + header + " -", "0"));
}
}
/// <summary>
/// Clear the Static Data from the Cache
/// </summary>
public static void ClearCache()
{
Cache oCache = HttpContext.Current.Cache;
oCache.Remove("StaticDataSetCache");
}
/// <summary>
/// Cache Static Data
/// </summary>
public static DataSet CacheStaticData()
{
Cache oCache = System.Web.HttpContext.Current.Cache;
// Get Static DataSet
DataSet ds = GetDataSet(Convert.ToString(eGetValueDropDown.STATE));
// Insert into cache
oCache.Insert(StaticDataSetCache, ds);
return ds;
}
/// <summary>
/// Get a DataTable from the cached DataSet
/// </summary>
/// <param name="tableIndex"></param>
/// <returns></returns>
public static DataTable GetData(Tables tableIndex)
{
return StaticDataSet.Tables[(int)tableIndex];
}
public SqlConnection con;
private static int SQL_DEADLOCK_ERROR_NUM = 1205;
#region database connection stuffs and parmeters
/// <summary>
/// Returns a new SqlConnection.
/// </summary>
/// <returns>SqlConnection object</returns>
public static SqlConnection GetConnection()
{
SqlConnection conn = new SqlConnection();
conn.ConnectionString = ConfigurationManager.ConnectionStrings["ASPNETDB"].ConnectionString; ;
return conn;
}
/// <summary>
/// Helper method to set properties of command object and its parameters
/// </summary>
/// <param name="spParams"></param>
/// <param name="command"></param>
private static void InflateCommandObject(SpParamCollection spParams, SqlCommand command)
{
command.CommandTimeout = 0;//Config.ExecuteTimeout;
command.CommandType = CommandType.StoredProcedure;
if (null == spParams) return;
foreach (SpParam param in spParams)
{
if (true == param.ParamDirectionOut)
command.Parameters.Add(param.Name, param.Value).Direction = ParameterDirection.Output;
else
command.Parameters.Add(param.Name, param.Value);
}
}
#endregion
# region Exceute non query command and insert and update the vale in databas
// <summary>
/// Overloaded method for ExecProcNonQuery that uses default sql connection,and it return integer which is getting from DB
/// </summary>
/// <param name="procName"></param>
/// <param name="spParams"></param>
public static Int16 ExecProcNonQueryText(string procName, SpParamCollection spParams)
{
Int16 ret = 0;
ExecProcNonQueryText(procName, spParams, GetConnection(), out ret);
return ret;
}
/// <summary>
/// Execute the given stored procedure. No results are expected from the proc.
/// </summary>
/// <param name="procName"></param>
/// <param name="spParams"></param>
public static void ExecProcNonQueryText(string procName, SpParamCollection spParams, SqlConnection conn, out Int16 ret)
{
SqlCommand command = new SqlCommand();
try
{
conn.Open();
command.Connection = conn;
command.CommandType = CommandType.Text;
command.CommandText = procName;
//command.ExecuteNonQuery();
// InflateCommandObject(spParams, command);
// command.CommandTimeout = 0;
ret = (Int16)command.ExecuteNonQuery();
}
catch (SqlException sqlEx)
{
if (SQL_DEADLOCK_ERROR_NUM == sqlEx.Number)
{
try
{
command.CommandTimeout = 60;
ret = (Int16)command.ExecuteNonQuery();
}
catch (Exception ex)
{
throw ex; ;
}
}
else
{
throw sqlEx;
}
}
catch (Exception ex)
{
throw ex; ;
}
finally
{
command = null;
if (null != conn)
{
conn.Close();
conn.Dispose();
}
}
}
// <summary>
/// Overloaded method for ExecProcNonQuery that uses default sql connection,and it return integer which is getting from DB
/// </summary>
/// <param name="procName"></param>
/// <param name="spParams"></param>
public static Int16 ExecProcNonQuery(string procName, SpParamCollection spParams)
{
Int16 ret = 0;
ExecProcNonQuery(procName, spParams, GetConnection(), out ret);
return ret;
}
/// <summary>
/// Execute the given stored procedure. No results are expected from the proc.
/// </summary>
/// <param name="procName"></param>
/// <param name="spParams"></param>
public static void ExecProcNonQuery(string procName, SpParamCollection spParams, SqlConnection conn, out Int16 ret)
{
SqlCommand command = new SqlCommand(procName, conn);
try
{
InflateCommandObject(spParams, command);
conn.Open();
command.CommandTimeout = 0;
ret = (Int16)command.ExecuteNonQuery();
}
catch (SqlException sqlEx)
{
if (SQL_DEADLOCK_ERROR_NUM == sqlEx.Number)
{
try
{
command.CommandTimeout = 60;
ret = (Int16)command.ExecuteNonQuery();
}
catch (Exception ex)
{
throw ex; ;
}
}
else
{
throw sqlEx;
}
}
catch (Exception ex)
{
throw ex; ;
}
finally
{
command = null;
if (null != conn)
{
conn.Close();
conn.Dispose();
}
}
}
# endregion
#region To populate the data into the datareader from database
/// Overloaded method for ExecProcNonQuery that uses default sql connection,and it return datareader which is getting from DB
/// </summary>
/// <param name="procName"></param>
/// <param name="spParams"></param>
public static SqlDataReader ExecProcAndPopulateDataReader(string procName, SpParamCollection spParams)
{
SqlDataReader dr;
ExecProcAndPopulateDataReader(procName, spParams, out dr, GetConnection());
return dr;
}
/// Overloaded method for ExecProcNonQuery that uses default sql connection,and it return datareader which is getting from DB
/// </summary>
/// <param name="procName"></param>
/// <param name="spParams"></param>
public static SqlDataReader ExecProcAndPopulateDataReaderText(string QueryText,SpParamCollection spParams)
{
SqlDataReader dr;
ExecProcAndPopulateDataReaderTextType(QueryText, out dr, GetConnection());
return dr;
}
public static void ExecProcAndPopulateDataReaderTextType(string QueryText, out SqlDataReader dr, SqlConnection conn)
{
SqlCommand command = new SqlCommand(QueryText, conn);
conn.Open();
try
{
command.CommandType = CommandType.Text;
// InflateCommandObject(spParams, command);
dr = command.ExecuteReader();
}
catch (SqlException sqlEx)
{
if (SQL_DEADLOCK_ERROR_NUM == sqlEx.Number)
{
try
{
dr = command.ExecuteReader();
}
catch (Exception ex)
{
throw ex; ;
}
}
else
{
throw sqlEx;
}
}
catch (Exception ex)
{
throw ex; ;
}
}
/// <summary>
/// Execute the given stored procedure and then populate the DataReader with the data.
/// </summary>
/// <param name="procName"></param>
/// <param name="spParams"></param>
/// <param name="dr"></param>
public static void ExecProcAndPopulateDataReader(string procName, SpParamCollection spParams, out SqlDataReader dr, SqlConnection conn)
{
SqlCommand command = new SqlCommand(procName, conn);
conn.Open();
try
{
InflateCommandObject(spParams, command);
dr = command.ExecuteReader();
}
catch (SqlException sqlEx)
{
if (SQL_DEADLOCK_ERROR_NUM == sqlEx.Number)
{
try
{
dr = command.ExecuteReader();
}
catch (Exception ex)
{
throw ex; ;
}
}
else
{
throw sqlEx;
}
}
catch (Exception ex)
{
throw ex; ;
}
}
#endregion
#region Use of dataset to populate the balue from database and put into dataset
/// <summary>
/// Overloaded method for ExecProcAndFillDataSet that uses default sql connection
/// </summary>
/// <param name="procName"></param>
/// <param name="spParams"></param>
/// <param name="ds"></param>
public static void ExecProcAndFillDataSetText(string procName, SpParamCollection spParams, DataSet ds)
{
ExecProcAndFillDataSetText(procName, spParams, ds, GetConnection());
}
/// <summary>
/// Execute the given stored procedure and then populate the DataSet with the returned data.
/// </summary>
/// <param name="procName"></param>
/// <param name="spParams"></param>
/// <param name="ds"></param>
public static void ExecProcAndFillDataSetText(string procName, SpParamCollection spParams, DataSet ds, SqlConnection conn)
{
SqlCommand command = new SqlCommand(procName, conn);
command.CommandType = CommandType.Text;
SqlDataAdapter dataAdapter = new SqlDataAdapter(command);
try
{
InflateCommandObject(spParams, command);
//Fill DataSet
dataAdapter.Fill(ds);
}
catch (SqlException sqlEx)
{
if (SQL_DEADLOCK_ERROR_NUM == sqlEx.Number)
{
try
{
dataAdapter.Fill(ds);
}
catch (Exception ex)
{
throw ex; ;
}
}
else
{
throw sqlEx;
}
}
catch (Exception ex)
{
throw ex; ;
}
finally
{
command = null;
if (null != conn)
{
conn.Close();
conn.Dispose();
}
}
}
/// <summary>
/// Overloaded method for ExecProcAndFillDataSet that uses default sql connection
/// </summary>
/// <param name="procName"></param>
/// <param name="spParams"></param>
/// <param name="ds"></param>
public static void ExecProcAndFillDataSet(string procName, SpParamCollection spParams, DataSet ds)
{
ExecProcAndFillDataSet(procName, spParams, ds, GetConnection());
}
/// <summary>
/// Execute the given stored procedure and then populate the DataSet with the returned data.
/// </summary>
/// <param name="procName"></param>
/// <param name="spParams"></param>
/// <param name="ds"></param>
public static void ExecProcAndFillDataSet(string procName, SpParamCollection spParams, DataSet ds, SqlConnection conn)
{
SqlCommand command = new SqlCommand(procName, conn);
SqlDataAdapter dataAdapter = new SqlDataAdapter(command);
try
{
InflateCommandObject(spParams, command);
//Fill DataSet
dataAdapter.Fill(ds);
}
catch (SqlException sqlEx)
{
if (SQL_DEADLOCK_ERROR_NUM == sqlEx.Number)
{
try
{
dataAdapter.Fill(ds);
}
catch (Exception ex)
{
throw ex; ;
}
}
else
{
throw sqlEx;
}
}
catch (Exception ex)
{
throw ex; ;
}
finally
{
command = null;
if (null != conn)
{
conn.Close();
conn.Dispose();
}
}
}
#endregion
#region Read data from databes and return it into XML File
// <summary>
/// Overloaded method for ExecProcAndReturnXmlDocument that uses default sql connection
/// </summary>
/// <param name="procName"></param>
/// <param name="spParams"></param>
public static XmlDocument ExecProcAndReturnXmlDocument(string procName, SpParamCollection spParams)
{
XmlDocument xmlDoc = null;
xmlDoc = ExecProcAndReturnXmlDocument(procName, spParams, GetConnection());
return xmlDoc;
}
/// <summary>
/// Execute the given stored procedure which is expected to return an xml document
/// (using sql FOR XML clause), and then return that xml document.
/// </summary>
/// <param name="procName"></param>
/// <param name="spParams"></param>
public static XmlDocument ExecProcAndReturnXmlDocument(string procName, SpParamCollection spParams, SqlConnection conn)
{
SqlCommand command = new SqlCommand(procName, conn);
XmlDocument xmlDoc = null;
try
{
InflateCommandObject(spParams, command);
conn.Open();
//Get XmlDocument
XmlTextReader reader = (XmlTextReader)command.ExecuteXmlReader();
xmlDoc = new XmlDocument();
xmlDoc.Load(reader);
reader.Close();
}
catch (SqlException sqlEx)
{
if (SQL_DEADLOCK_ERROR_NUM == sqlEx.Number)
{
try
{
//Get XmlDocument
XmlTextReader reader = (XmlTextReader)command.ExecuteXmlReader();
xmlDoc = new XmlDocument();
xmlDoc.Load(reader);
reader.Close();
}
catch (Exception ex)
{
throw ex; ;
}
}
else
{
throw sqlEx;
}
}
catch (Exception ex)
{
throw ex; ;
}
finally
{
command = null;
if (null != conn)
{
conn.Close();
conn.Dispose();
}
}
return xmlDoc;
}
#endregion
#region Private Methods to get the value in data set
/// <summary>
/// Get Static data from database
/// </summary>
/// <returns></returns>
public static DataSet GetDataSet(string SprocName)
{
SqlDataReader dr = null;
DataSet ds = new DataSet();
try
{
//SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["StaticDataHelperConnectionString"].ToString());
// SqlCommand cmd = new SqlCommand("proc_StaticDataSelect", GetConnection());
SqlCommand cmd = new SqlCommand(SprocName, GetConnection());
dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
// Load DataSet with multiple resultsets from the SqlDataReader
ds.Load(dr, LoadOption.OverwriteChanges, GetDataTablesValuesArray());
return ds;
}
finally
{
if (dr != null && !dr.IsClosed)
{
dr.Close();
}
}
}
/// <summary>
/// Helper method to return an string array of the values in the Tables enum
/// </summary>
/// <returns></returns>
private static string[] GetDataTablesValuesArray()
{
string[] s = new string[0];
foreach (Tables value in Enum.GetValues(typeof(Tables)))
{
Array.Resize(ref s, s.Length + 1);
s.SetValue(value.ToString(), s.Length - 1);
}
return s;
}
#endregion
}
#region Structs Make a parmetariged straucture
/// <summary>
/// Represents a stored procedure's command paramater
/// name/value pair and the direction of the parameter
/// </summary>
public struct SpParam
{
private string m_paramName;
private object m_paramValue;
private bool m_paramDirectionOut;
public string Name
{
get { return m_paramName; }
}
public object Value
{
get { return m_paramValue; }
}
public bool ParamDirectionOut
{
get { return m_paramDirectionOut; }
}
/// <summary>
/// Overridden constructor that sets the paramDirectionOut parameter to false
/// </summary>
public SpParam(string paramName, object paramValue) : this(paramName, paramValue, false) { }
/// <summary>
/// Constructor that sets paramDirectionOut parameter specifically
/// </summary>
public SpParam(string paramName, object paramValue, bool paramDirectionOut)
{
m_paramName = paramName;
m_paramValue = paramValue;
m_paramDirectionOut = paramDirectionOut;
}
}
#endregion
#region Parameters
/// <summary>
/// Represents a collection of parameters (SpParams').
/// </summary>
///
public class SpParamCollection : ArrayList
{
public SpParamCollection()
{
}
public void Add(SpParam param)
{
base.Add(param);
}
public void clear()
{
base.Clear();
}
}
#endregion
}
==================================================
Using of the Method which is created there as
using System;
using System.Configuration;
using System.Data;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
/// <summary>
/// Summary description for WIN_CgPearl
/// </summary>
///
namespace Ramosh.Support.DataLayer.DAL
{
public class WIN_CgPearlDAL : DBInterface
{
public WIN_CgPearlDAL()
{
}
internal static Int16 Insert_CgPearl(WIN_CgPearl objCgPearl)
{
Int16 ret = 0;
DataSet dts = new DataSet();
try
{
SpParamCollection spParams = new SpParamCollection();
spParams.Add(new SpParam("@CgPearlName", objCgPearl.CgPearlName));
spParams.Add(new SpParam("@CreatedDate", objCgPearl.CreatedDate));
spParams.Add(new SpParam("@ModifiedDate", objCgPearl.ModifiedDate));
spParams.Add(new SpParam("@IsActive", objCgPearl.IsActive));
ret = ExecProcNonQuery("WIN_Insert_CgPearl", spParams);
}
catch (Exception ex)
{
ex.Message.ToString();
}
return ret;
}
internal static Int16 Delete_CgPearl(WIN_CgPearl objCgPearl)
{
Int16 ret = 0;
DataSet dts = new DataSet();
try
{
SpParamCollection spParams = new SpParamCollection();
spParams.Add(new SpParam("@CgPearl_Id", objCgPearl.CgPearl_Id));
ret = ExecProcNonQuery("WIN_Delete_CgPearl", spParams);
}
catch (Exception ex)
{
ex.Message.ToString();
}
return ret;
}
internal static Int16 Update_CgPearl(WIN_CgPearl objCgPearl)
{
Int16 ret = 0;
DataSet dts = new DataSet();
try
{
SpParamCollection spParams = new SpParamCollection();
spParams.Add(new SpParam("@CgPearl_Id", objCgPearl.CgPearl_Id));
spParams.Add(new SpParam("@CgPearlName", objCgPearl.CgPearlName));
spParams.Add(new SpParam("@ModifiedDate", objCgPearl.ModifiedDate));
spParams.Add(new SpParam("@IsActive", objCgPearl.IsActive));
ret = ExecProcNonQuery("WIN_Update_CGPearl", spParams);
}
catch (Exception ex)
{
ex.Message.ToString();
}
return ret;
}
internal static BasePage<WIN_CgPearl> Select_CgPearl()
{
IDataReader oReader = null;
BasePage<WIN_CgPearl> objCgPearl = new BasePage<WIN_CgPearl>();
SpParamCollection spParams = new SpParamCollection();
oReader = ExecProcAndPopulateDataReader("WIN_Select_CgPearl", spParams);
while (oReader.Read())
{
objCgPearl.Add(new WIN_CgPearl(oReader));
}
return objCgPearl;
}
internal static BasePage<WIN_CgPearl> Select_CgPearl_Id(WIN_CgPearl objCgPearl)
{
IDataReader oReader = null;
BasePage<WIN_CgPearl> objBCgPearl = new BasePage<WIN_CgPearl>();
try
{
SpParamCollection spParams = new SpParamCollection();
spParams.Add(new SpParam("@CgPearl_Id", objCgPearl.CgPearl_Id));
oReader = ExecProcAndPopulateDataReader("WIN_Select_CgPearl_Id", spParams);
while (oReader.Read())
{
objBCgPearl.Add(new WIN_CgPearl(oReader));
}
}
catch (Exception ex)
{
ex.Message.ToString();
}
return objBCgPearl;
}
}
}
