Data Access Helper Class






1.24/5 (16 votes)
Oct 10, 2003

76972
This class helps with connecting to DB engine and with returning of resultsets.
Introduction
This is database access helper class with easy usage. For more information about designing data access helper classes see section Designing data layers in Application Architecture for .NET: Designing Applications and Services
Requirements
Only one thing you have to do before using this class, is to store your ADO connection string into the web.config file.
Usage
public class DBAudit
{
public static DataSet TextInfo(string IdCat, string IdTopic, string Lang, Guid IdObject)
{
return DB.FillDataset("STORED_PROC_NAME",
new SqlParameter[] {
new SqlParameter("@ID_CAT",SqlDbType.VarChar, 8),
new SqlParameter("@ID_TOPIC",SqlDbType.VarChar, 8),
new SqlParameter("@LANG",SqlDbType.VarChar, 8),
new SqlParameter("@ID_OBJEct",SqlDbType.UniqueIdentifier, 16)
},
new object[] {IdCat, IdTopic, Lang, IdObject});
}
}
Source code
namespace Infinity.DB
{
/// <summary>
/// Common methods for DB connection
/// </summary>
public class DB
{
const string DBConnStr = "DBSTR";
/// <summary>
/// Returns ADO connection string
/// </summary>
public static string GetConnStr()
{
return ConfigurationSettings.AppSettings[DBConnStr].ToString();
}
/// <summary>
/// Returns filled dataset from stored procedure name and its parameters
/// </summary>
public static DataSet FillDataset(string SProc, SqlParameter[] Params, object[] Values)
{
return FillDataset(SProc, Params, Values, DB.GetConnStr());
}
/// <summary>
/// Returns filled dataset from stored procedure name and its parameters
/// </summary>
public static DataSet FillDataset(string SProc, SqlParameter[] Params, object[] Values, string ConStr)
{
SqlConnection myConnection = new SqlConnection(ConStr);
SqlDataAdapter myAdapter = new SqlDataAdapter();
myAdapter.SelectCommand = new SqlCommand(SProc, myConnection);
myAdapter.SelectCommand.CommandType = CommandType.StoredProcedure;
// assign all parameters with its values
for(int i = 0; i < Params.Length; i++)
{
myAdapter.SelectCommand.Parameters.Add(Params[i]).Value = Values[i];
}
DataSet myDataSet = new DataSet();
myAdapter.Fill(myDataSet);
return myDataSet;
}
/// <summary>
/// Executes stored procedure with its parameters
/// </summary>
public static void ExecSQL(string SProc, SqlParameter[] Params, object[] Values)
{
SqlConnection myConnection = new SqlConnection(DB.GetConnStr());
SqlCommand myCmd = new SqlCommand(SProc, myConnection);
myCmd.CommandType = CommandType.StoredProcedure;
// assign all parameters with its values
for(int i = 0; i < Params.Length; i++)
{
myCmd.Parameters.Add(Params[i]).Value = Values[i];
}
myConnection.Open();
myCmd.ExecuteNonQuery();
myConnection.Close();
}
}
}