AspBucket offers ASP.NET, C#, VB, Jquery, CSS, Ajax, SQL tutorials. It is the best place for programmers to learn

Tuesday, 27 October 2015

Separate class for database operations in ASP.NET


Here I am going to create a new class DBLayer where all database related methods implemented. This class will interact directly with the database. You can say its Data layer for your project. Here all database operations is handled by stored procedure.
public class DBLayer
  {
      /// 
      /// Database Operations
      ///   
 
      private SqlConnection conn = null;
 
      private void OpenConn()
      {         
         conn.ConnectionString = ConfigurationManager.ConnectionStrings["Your_Connection_String"].ToString();   
         conn.Open();
      }
 
      private void CloseConn()
      {
          if (conn.State == ConnectionState.Open)
          {
              conn.Close();
          }
      }
 
      public void ExecuteNonQuery(string sp_name, SqlParameter[] param)
      {
          try
          {
              OpenConn();
              SqlCommand cmd = new SqlCommand(sp_name, conn);
              cmd.CommandType = CommandType.StoredProcedure;
              for (int i = 0; i <= param.Length - 1; i++)
              {
                  cmd.Parameters.Add(param[i]);
              }
 
              cmd.ExecuteNonQuery();
          }
          catch (Exception ex)
          {
              throw ex;
          }
          finally
          {
              CloseConn();
          }
      }
 
      public DataSet ExecuteDataSet(string sp_name, SqlParameter[] param)
      {
          DataSet ds = new DataSet();
          try
          {
              OpenConn();
              SqlCommand cmd = new SqlCommand(sp_name, conn);
              cmd.CommandType = CommandType.StoredProcedure;
              for (int i = 0; i <= param.Length - 1; i++)
              {
                  cmd.Parameters.Add(param[i]);
              }
              SqlDataAdapter adp = new SqlDataAdapter(cmd);
 
 
              adp.Fill(ds);
          }
          catch (Exception ex)
          {
              throw ex;
          }
          finally
          {
              CloseConn();
          }
          return ds;
      }
 
      public DataTable ExecuteDataTable(string sp_name, SqlParameter[] param)
      {
          DataSet ds = new DataSet();
          DataTable dt = new DataTable();
          try
          {
              OpenConn();
              SqlCommand cmd = new SqlCommand(sp_name, conn);
              cmd.CommandType = CommandType.StoredProcedure;
              for (int i = 0; i <= param.Length - 1; i++)
              {
                  cmd.Parameters.Add(param[i]);
              }
              SqlDataAdapter adp = new SqlDataAdapter(cmd);
              adp.Fill(ds);
              if ((ds != null) & ds.Tables.Count > 0)
              {
                  dt = ds.Tables[0];
              }
          }
          catch (Exception ex)
          {
              throw ex;
          }
          finally
          {
              CloseConn();
          }
          return dt;
      }
 
      public string ExecuteScaler(string sp_name, SqlParameter[] param)
      {
          string _result = string.Empty;
          try
          {
              OpenConn();
              SqlCommand cmd = new SqlCommand(sp_name, conn);
              cmd.CommandType = CommandType.StoredProcedure;
              for (int i = 0; i <= param.Length - 1; i++)
              {
                  cmd.Parameters.Add(param[i]);
              }
              _result = Convert.ToString(cmd.ExecuteScalar());
          }
          catch (Exception ex)
          {
              throw ex;
          }
          finally
          {
              CloseConn();
          }
          return _result;
      }       
  }
Methods:
1- ExecuteNonQuery(string sp_name, SqlParameter[] param)
Method has two parameters sp_name(Stored Procedure Name) & param(Stored procedure Parameters). This method will not return any record & used for Insert & Updated Commands.

2- ExecuteDataSet(string sp_name, SqlParameter[] param) 
Method has two parameters sp_name(Stored Procedure Name) & param(Stored procedure Parameters) & it will return Dataset.

3- ExecuteDataTable(string sp_name, SqlParameter[] param)
Method has two parameters sp_name(Stored Procedure Name) & param(Stored procedure Parameters) & it will return DataTable.

4- ExecuteScaler(string sp_name, SqlParameter[] param)
Method has two parameters sp_name(Stored Procedure Name) & param(Stored procedure Parameters) & it will return single record.

0 comments :

Post a comment

  • Popular Posts
  • Comments