Data Access Helper Class

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();
  }
 }
}