Manage Your CRUD Operation
4.90/5 (16 votes)
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 IConnection Command . 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 string GetConnectionString() method is giving the connection string depending on web.config Connectionstring LockItem
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