Click here to Skip to main content
13,004,099 members (111,789 online)
Click here to Skip to main content
Add your own
alternative version

Stats

22K views
40 bookmarked
Posted 15 May 2013

Manage Your CRUD Operation

, 16 Aug 2013
Rate this:
Please Sign up or sign in to vote.
This Article will help you to manage our CRUD operation more efficient way

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.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Faisal(mfrony)
Bangladesh Bangladesh
I am a Sr.Software Engineer at Brain Station -23. I have 5+ years of work experience in .Net technology. I strongly believe that before software can be reusable it first has to be usable.

My contact info :

mfrony2003@yahoo.com
mfrony2003@hotmail.com

LinkedIn
http://www.linkedin.com/profile/view?id=106671466&trk=tab_pro

You may also be interested in...

Comments and Discussions

 
Questiongood one Pin
Castro steffi23-May-16 21:31
memberCastro steffi23-May-16 21:31 
QuestionManage Your CRUD Operation Pin
cocis4820-Aug-13 5:05
membercocis4820-Aug-13 5:05 
AnswerRe: Manage Your CRUD Operation Pin
Faisal(mfrony)20-Aug-13 22:09
memberFaisal(mfrony)20-Aug-13 22:09 
GeneralMy vote of 2 Pin
Paulo Zemek16-Aug-13 11:26
professionalPaulo Zemek16-Aug-13 11:26 
GeneralMany small problems Pin
Paulo Zemek16-Aug-13 11:26
professionalPaulo Zemek16-Aug-13 11:26 
GeneralRe: Many small problems Pin
$andesh M Patil19-Aug-13 0:35
member$andesh M Patil19-Aug-13 0:35 
GeneralMy vote of 5 Pin
$andesh M Patil16-Aug-13 7:45
member$andesh M Patil16-Aug-13 7:45 
GeneralMy vote of 5 Pin
Hasibul Haque15-Aug-13 6:00
professionalHasibul Haque15-Aug-13 6:00 
GeneralRe: My vote of 5 Pin
Faisal(mfrony)16-Aug-13 2:17
memberFaisal(mfrony)16-Aug-13 2:17 
GeneralMy vote of 5 Pin
Mihai MOGA13-Jun-13 20:47
memberMihai MOGA13-Jun-13 20:47 
QuestionGood article Pin
dbrenth22-May-13 8:32
memberdbrenth22-May-13 8:32 
AnswerRe: Good article Pin
somnathdgaikwad6-Aug-13 19:27
professionalsomnathdgaikwad6-Aug-13 19:27 
GeneralMy vote of 5 Pin
Mohammed Hameed16-May-13 2:53
professionalMohammed Hameed16-May-13 2:53 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

Permalink | Advertise | Privacy | Terms of Use | Mobile
Web02 | 2.8.170627.1 | Last Updated 16 Aug 2013
Article Copyright 2013 by Faisal(mfrony)
Everything else Copyright © CodeProject, 1999-2017
Layout: fixed | fluid