Manage Your CRUD Operation

Introduction  

Here i am going to show you how we can manage our CRUD operation more efficient way. We regularly use this in our daily work. But some codes may be written unnecessary . its Redundant code. My approach is to point out those redundant code and eliminate the redundancy.

Using the code

Typical code to make Select operation on my own class.

public class Employee
  {
      DataSet ds = new DataSet();

      public DataSet SelectEmployee()
      {
          string mySelectQuery = "SELECT ID,Name,Address FROM Employee ";
          var myConnString = ConfigurationManager.ConnectionStrings["conProduction"].ToString();
          SqlConnection myConnection = new SqlConnection(myConnString);
          SqlCommand myCommand = new SqlCommand(mySelectQuery, myConnection);
          myConnection.Open();
          var adapter = new SqlDataAdapter(myCommand);
          adapter.Fill(ds);
          myConnection.Close();
          return ds;
      }
  }

 I think most of the peoples are familiar with this type. Here my class Employee has to do all the steps to make the query execute successfully,  Then again for insert update or delete it will go through all the steps like getting Connectionstring,SqlCommand , ExecuteQuery etc . if another  new class added then it will also follow the overhead of creating all those objects . It will be just wasting time as well as making code redundant Right . Ok Now I will show you some different mechanism  to handle this situation.

Now I am going to separate the duty of connection and command.  

public interface IConnection
   {
       SqlConnection Connection { get;}
   }

public interface ICommand
  {
      string CommandText  // For Inline Sql
      { get; set; }
      string SpName
      { get; set; }
      List<SqlParameter> SqlParams { get; set; }
      bool Execute();
      DataSet SelectData();
  }

If you take a look you will notice that  those are the common things that we need to execute a command. Now we have the  common structure , so now we need some one who implement that and take the responsibility. For doing that we have to create classes who implements <code>I<code>Connection and ICommand .

public class ConnectionManager : IConnection
   {
       private string GetConnectionString()
       {
           string connectionString = string.Empty;
           var connection = ConfigurationManager
                         .ConnectionStrings
                         .Cast<ConnectionStringSettings>()
                         .Where(p => p.LockItem == false).FirstOrDefault();

           connectionString = connection != null ? connection.ConnectionString : string.Empty;
           return connectionString;
       }


       private SqlConnection GetConnection()
       {
           return new SqlConnection(GetConnectionString());
       }

       public SqlConnection Connection
       {
           get
           {
               return GetConnection();
           }

       }

This is the connection manager class. Here the private <code>string GetConnectionString() method is giving the connection string depending on web.config Connectionstring <code>LockItem Attribute . Look at the below picture .    

Fig 1.1 

So The connection string that has lockItem=false will be used as current connection. So after deploying it on the  respective server we need to set the property false and rest of them must be true (Locked). Our readonly property Connection getting the data based on the web.config configuration. For IConnection its ConnectionManager class so what about ICommand ?. 

Now for ICommand its CommandBuilder.  

public class CommandBuilder : ICommand
    {
        private IConnection connection = null;
        private SqlConnection sqlConnection = null;
        public CommandBuilder()
        {
            connection = new ConnectionManager();
            sqlConnection = connection.Connection;
        }
        public string CommandText
        { get; set; }
        public string SpName
        { get; set; }
        public List<SqlParameter> SqlParams { get; set; }
        /// <summary>
        /// For Insert Update and Delete Queries
        /// </summary>
        /// <returns></returns>
        public bool Execute()
        {
            bool IsExecuted = false;
            using (sqlConnection)
            {
                try
                {
                    sqlConnection.Open();
                    using (var sqlCommand = new SqlCommand(CommandText, sqlConnection))
                    {
                        sqlCommand.CommandType = !string.IsNullOrWhiteSpace(CommandText) ? 
                                        CommandType.Text : CommandType.StoredProcedure;
                        sqlCommand.CommandText = !string.IsNullOrEmpty(CommandText) ?
                                        CommandText : SpName;
                        if (SqlParams != null)
                        {
                            SqlParams.ForEach(p => sqlCommand.Parameters.Add(p));
                        }
                        IsExecuted = sqlCommand.ExecuteNonQuery() > 0;
                    }
                }
                catch (Exception)
                {
                    //Handle Exception Here
                }

            }
            return IsExecuted;
        }

        /// <summary>
        /// /// For Select Queries
        /// </summary>
        /// <returns></returns>
        public DataSet SelectData()
        {
            var ds = new DataSet();
            using (sqlConnection)
            {
                try
                {
                    sqlConnection.Open();
                    using (var sqlCommand = new SqlCommand(CommandText, sqlConnection))
                    {
                        sqlCommand.CommandType = !string.IsNullOrWhiteSpace(CommandText) ? 
                            CommandType.Text : CommandType.StoredProcedure;
                        sqlCommand.CommandText = !string.IsNullOrEmpty(CommandText) ? 
                            CommandText : SpName;
                        SqlParams.ForEach(p => sqlCommand.Parameters.Add(p));
                        var adapter = new SqlDataAdapter(sqlCommand);
                        adapter.Fill(ds);
                    }
                }
                catch (Exception)
                {
                    //Handle Exception Here
                }

            }
            return ds;
        }
        
    }

Here the constructor is giving you the connection from ConnectionManager class  . 

public CommandBuilder()
     {
         connection =  new ConnectionManager();
         sqlConnection = connection.Connection;
     }

Now the properties  

If you will execute Inline Sql then  put sql in CommandText property.

public string CommandText // For Inline Sql
       { get; set; }

If you will execute Stored Procedure   put the  Stored Procedure  name on SpName property. 

public string SpName // For Stored procedure
{ get; set; }

If you want to pass SqlPatameter then use this SqlParams

public List<SqlParameter> SqlParams { get; set; }

Execute method is for Insert Update Delete  srtatement

public bool Execute()

 SelectData method  is for Select statement

public DataSet SelectData()

Now the final one . I have to impose some  specific rule to my entity classes for operating the CRUD (Execute and SelectData method). This is an arrangement under the same umbrella. Below is umbrella or rules for my classes.  

interface ICrud<T>
{
    bool Insert(T obj);
    bool Update(T obj);
    bool Delete(T obj);
    DataSet Select(T obj);
}

 

Points of Interest

 Now what happen if i implement that interface to my Employee class.  Lets See

public class Employee:ICrud<ICommand>
    {
        public bool Insert(ICommand obj)
        {
            return obj.Execute();
        }
 
        public bool Update(ICommand obj)
        {
            return obj.Execute();
        }
 
        public bool Delete(ICommand obj)
        {
            return obj.Execute();
        }
 
        public DataSet Select(ICommand obj)
        {
          return  obj.SelectData();
        }
    }

Wow my classes don't need to know about SqlCommand,SqlConnection,SqlAdapter. Great. I will fill the ICommand type and  pass , it will do the rest .

How we call those method from my page(client) ? Very Easy  

// FOR INLINE SQL
var objEmployee = new Employee();
var cmd = new CommandBuilder();
cmd.CommandText = "SELECT ID,Name,Address FROM Employee";
var dset = objEmployee.Select(cmd);

// FOR STORED PROCEDURE
var cmd = new CommandBuilder();
cmd.SpName = "sp_InsertEmployee";
cmd.SqlParams = new List<SqlParameter>()
{
    new SqlParameter("@ID",123),
    new SqlParameter("@Name","Mark"),
    new SqlParameter("@Address","123 Park Street"),
};
var isInserted = objEmployee.Insert(cmd); 

Client code don't need to know about SqlConnection <code>SQlCommand.