A generic data access class for .net apps.
public class DataAccessClient
{
public DataSet ExecuteStoredProc(string StoredPorc,string ConString,SqlParameter[] Parameters)
{
DataSet dsResult = new DataSet();
using (SqlConnection sqlCon = new SqlConnection(ConString))
{
try
{
sqlCon.Open();
SqlCommand sqlCmd = sqlCon.CreateCommand();
sqlCmd.CommandType = CommandType.StoredProcedure;
sqlCmd.CommandText = StoredPorc;
sqlCmd.CommandTimeout = 1200;
foreach (SqlParameter Param in Parameters)
{
sqlCmd.Parameters.Add(Param);
}
SqlDataAdapter sqlDa = new SqlDataAdapter(sqlCmd);
sqlDa.Fill(dsResult);
return dsResult;
}
catch (Exception ex)
{
Logger.TextLogger log = new Logger.TextLogger();
log.WriteLog(ex.ToString());
//return null;
throw;
}
finally
{
dsResult.Dispose();
sqlCon.Close();
sqlCon.Dispose();
}
}
}
public DataSet ExecuteQuery(string ConString, string Query)
{
DataSet dsResult = new DataSet();
using (SqlConnection sqlCon = new SqlConnection(ConString))
{
try
{
sqlCon.Open();
using (SqlCommand sqlCmd = sqlCon.CreateCommand())
{
sqlCmd.CommandType = CommandType.Text;
sqlCmd.CommandText = Query;
SqlDataAdapter sqlDa = new SqlDataAdapter(sqlCmd);
sqlDa.Fill(dsResult);
}
return dsResult;
}
catch (Exception ex)
{
Logger.TextLogger log = new Logger.TextLogger();
log.WriteLog(ex.ToString());
// return null;
throw;
}
finally
{
dsResult.Dispose();
sqlCon.Close();
sqlCon.Dispose();
}
}
}
public bool ExecuteInsertQuery(string Query,string ConnectionString)
{
bool isDataInserted = false;
int insertedRows = 0;
using (SqlConnection sqlCon = new SqlConnection(ConnectionString))
{
try
{
sqlCon.Open();
using (SqlCommand sqlCmd = sqlCon.CreateCommand())
{
sqlCmd.CommandType = CommandType.Text;
sqlCmd.CommandText = Query;
sqlCmd.CommandTimeout = 1200;
insertedRows = sqlCmd.ExecuteNonQuery();
}
if (insertedRows > 0)
{
isDataInserted = true;
}
return isDataInserted;
}
catch (Exception ex)
{
Logger.TextLogger log = new Logger.TextLogger();
log.WriteLog(ex.ToString());
// return false;
throw;
}
finally
{
sqlCon.Close();
sqlCon.Dispose();
}
}
}
public List<T> ConvertDtToList<T>(DataSet ds)
{
DataTable dt = ds.Tables[0];
List<T> CustomerListData = new List<T>();
foreach (DataRow row in dt.Rows)
{
T item = GetItem<T>(row);
CustomerListData.Add(item);
}
return CustomerListData;
}
public T GetItem<T>(DataRow dr)
{
Type temp = typeof(T);
T obj = Activator.CreateInstance<T>();
foreach (DataColumn column in dr.Table.Columns)
{
foreach (PropertyInfo pro in temp.GetProperties())
{
if (pro.Name == column.ColumnName)
pro.SetValue(obj, dr[column.ColumnName], null);
else
continue;
}
}
return obj;
}
}
To call any method
DataAccessClient client = new DataAccessClient();
SqlParameter[] Parameters = new SqlParameter[2];
SqlParameter param1 = new SqlParameter("@Param1", SqlDbType.VarChar, 50);
param1.Value = value1;
SqlParameter param2 = new SqlParameter("@Param2", SqlDbType.VarChar, 50);
ParentHierUniqueCode.Value = value2;
Parameters[0] = hierType;
Parameters[1] = ParentHierUniqueCode;
string spname = "Stored Proc Name";
ds = client.ExecuteStoredProc(spname, connectionstring, Parameters);
