Posted in C#.Net

DataAccessLayer.cs for MS SQL Server in .Net

   1:   
   2:  using System;
   3:  using System.Data;
   4:  using System.Configuration;
   5:  using System.Data.SqlClient;
   6:   
   7:  ///
 8: /// DataAccessLayer class is sealed class(which cannot be inherited). 
 9: /// It contains two public static methods. 
 10: /// First one is ExecuteDMLCommand and another one is ExecuteDQLCommand. 
 11: /// 
  12:  public sealed class DataAccessLayer
  13:  {
  14:   
  15:      #region Fields
  16:   
  17:      static SqlConnection con;
  18:      static SqlCommand cmd;
  19:      static DataSet ds;
  20:      static SqlDataAdapter da;
  21:      //static SqlTransaction trans;
  22:   
  23:      #endregion
  24:   
  25:      #region StaticConstructor
  26:   
  27:      ///
 28: /// Constructor. 29: /// 

  30:      static DataAccessLayer()
  31:      {
  32:          con = null;
  33:          cmd = null;
  34:          ds = null;
  35:          da = null;
  36:          //trans = null;
  37:      }
  38:   
  39:      #endregion
  40:   
  41:      #region StaticPrivateMethods
  42:   
  43:      ///
 44: /// Returns SQL Sever connection string. 
 45: /// 
  46:      /// Connection String.
  47:      static string GetSqlConnectionString()
  48:      {
  49:          return
  50:              ConfigurationManager.ConnectionStrings["SqlConnectionString"].
  51:              ConnectionString.ToString();
  52:      }
  53:   
  54:      ///
 55: /// Sets SqlCommand object. 
 56: /// 
  57:      ///cmd">SqlCommand field.
  58:      ///commandText">SQL command text.
  59:      ///SqlConnection object.
  60:      ///commandType">CommandText or StoredProcedure.
  61:      ///storedProcedureParameters">Parameters of stored procedure.
  62:      /// SqlCommand object.
  63:      static SqlCommand SetSqlCommand
  64:          (SqlCommand cmd, string commandText, SqlConnection con,
  65:          CommandType commandType, SqlParameter[] storedProcedureParameters)
  66:      {
  67:          try
  68:          {
  69:              cmd = new SqlCommand(commandText, con);
  70:              cmd.CommandType = commandType;
  71:              if (storedProcedureParameters != null &&
  72:                            storedProcedureParameters.Length != 0)
  73:              {
  74:                  foreach (SqlParameter param in storedProcedureParameters)
  75:                  {
  76:                      cmd.Parameters.Add(param);
  77:                  }
  78:              }
  79:              return cmd;
  80:          }
  81:          catch (Exception ex)
  82:          {
  83:              throw new ArgumentException(ex.Message);
  84:          }
  85:      }
  86:   
  87:      #endregion
  88:   
  89:      #region StaticPublicMethods
  90:   
  91:      ///
 92: /// Executes Data Manipulation Language commands(Insert, Update, Delete). 
 93: /// 
  94:      ///nonQueryCommandText">Insert/Update/Delete command statement.
  95:      ///commandType">CommandText or StoredProcedure.
  96:      ///storedProcedureParameters">Parameters of stored procedure.
  97:      /// Number of rows affected.
  98:      public static int ExecuteDMLCommand
  99:          (string nonQueryCommandText, CommandType commandType,
 100:          SqlParameter[] storedProcedureParameters)
 101:      {
 102:          try
 103:          {
 104:              con = new SqlConnection(GetSqlConnectionString());
 105:              cmd = SetSqlCommand(cmd, nonQueryCommandText, con,
 106:                             commandType, storedProcedureParameters);
 107:              con.Open();
 108:              //trans = con.BeginTransaction();
 109:              //cmd.Transaction = trans;
 110:              int numberOfRowsAffected = cmd.ExecuteNonQuery();
 111:              //trans.Commit();
 112:              return numberOfRowsAffected;
 113:          }
 114:          catch (SqlException ex)
 115:          {
 116:              //trans.Rollback();
 117:              throw new ArgumentException(ex.Message);
 118:          }
 119:          catch (Exception ex)
 120:          {
 121:              //trans.Rollback();
 122:              throw new ArgumentException(ex.Message);
 123:          }
 124:          finally
 125:          {
 126:              if (con != null)
 127:              {
 128:                  if (con.State == ConnectionState.Open)
 129:                  {
 130:                      con.Close();
 131:                  }
 132:              }
 133:          }
 134:      }
 135:   
 136:      ///
 137: /// Executes Data Query Language command(Select). 
 138: /// 
 139:      ///selectCommandText">Select command statement.
 140:      ///CommandText or StoredProcedure.
 141:      ///Parameters of stored procedure.
 142:      /// Data in the form of DataSet.
 143:      public static DataSet ExecuteDQLCommand
 144:          (string selectCommandText, CommandType commandType,
 145:          SqlParameter[] storedProcedureParameters)
 146:      {
 147:          try
 148:          {
 149:              con = new SqlConnection(GetSqlConnectionString());
 150:              cmd = SetSqlCommand(cmd, selectCommandText, con,
 151:                     commandType, storedProcedureParameters);
 152:              using (da = new SqlDataAdapter(cmd))
 153:              {
 154:                  ds = new DataSet();
 155:                  da.Fill(ds);
 156:              }
 157:              return ds;
 158:          }
 159:          catch (SqlException ex)
 160:          {
 161:              throw new ArgumentException(ex.Message);
 162:          }
 163:          catch (Exception ex)
 164:          {
 165:              throw new ArgumentException(ex.Message);
 166:          }
 167:      }
 168:   
 169:      #endregion
 170:   
 171:  }
 172:
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s