Sql Insert and Sql Edit in one common method


While writing code for real time applications it is important that we have to reduce the code as much as possible. I.e., we should avoid the code repetetion. When writing database operations 'Insert' and 'Edit' ,if you write two seperate methods for them ,you can most of the code is repeated(most of the code is same in both the methods). So, here am going to explain how to reduce the code in this scenario by writing one common method for both 'Insert' and 'Edit' operations.

Consider you have a table called 'Contacts' in your database. Then you can perform operations like Add contact, Fetch contact, Edit contact and Delete Contact etc etc.. Here am only explaining about Insert and Edit contact.

Let us consider you have fields like Id, FirstName, LastName, Email and MobileNumber which you have to insert into your database table.

Your aspx page has some code like this..


First Name:<asp:TextBox runat="server" ID="tbFirstName"></asp:TextBox>

Last Name:<asp:TextBox runat="server" ID="tbLastName"></asp:TextBox>

Email:<asp:TextBox runat="server" ID="tbEmail"></asp:TextBox>

MobileNumber:<asp:TextBox runat="server" ID="tbMobile"></asp:TextBox>

<asp:Button runat="server" ID="btnAddContact" Text="ADD" OnClick="btnAddContact_Click" />


This code produces 4 TextBoxes to enter contact details and one Submit button to submit the data.

Code for Inserting contact to DataBase table

protected void AddContact_Click(object sender, EventArgs e)
{
// define your connection string first
SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["MyConnectionString"].ConnectionString);
SqlCommand command = new SqlCommand("Insert into Contacts (FirstName,LastName,Email,Mobile) Values (@FirstName, @LastName, @Email, @Mobile)", connection);
command.Parameters.AddWithValue("@FirstName",tbFirstName.Text );
command.Parameters.AddWithValue("@LastName", tbLastName.Text);
command.Parameters.AddWithValue("@Email", tbEmail.Text);
command.Parameters.AddWithValue("@Mobile",tbMobile.Text);

try
{
connection.Open();
command.ExecuteNonQuery();
}
finally
{
connection.Close();
}
}

Code for Editing existing Contact

consider you have a Edit button which is having OnClick='EditContact_Click'

protected void EditContact_Click(object sender, EventArgs e)
{
// define your connection string first
SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["MyConnectionString"].ConnectionString);
SqlCommand command = new SqlCommand("Update Contacts Set (FirstName=@FirstName,LastName= @LastName,Email= @Email, MobileNumber=@Mobile) Where Id=@Id", connection);
command.Parameters.AddWithValue("@FirstName",tbFirstName.Text );
command.Parameters.AddWithValue("@LastName", tbLastName.Text);
command.Parameters.AddWithValue("@Email", tbEmail.Text);
command.Parameters.AddWithValue("@Mobile",tbMobile.Text);
command.Parameters.AddWithValue("@Id",someId);

try
{
connection.Open();
command.ExecuteNonQuery();
}
finally
{
connection.Close();
}
}


If you observe both Insert and Edit methods they are almost same. The only difference is in Command Text. So, we can write them both as a single method to which we can pass Command Text as a parameter depending on the operation we are performing.

Make a call to the common method from previously defined Insert and Edit events and pass connection string as argument

Insert:

protected void AddContact_Click(object sender, EventArgs e)
{
string CommandText="Insert into Contacts (FirstName,LastName,Email,Mobile) Values (@FirstName, @LastName, @Email, @Mobile)";
//now call the common method
Add_Edit(CommandText);
}

Edit

protected void EditContact_Click(object sender, EventArgs e)
{
string CommandText=Update Contacts Set (FirstName=@FirstName,LastName= @LastName,Email= @Email, MobileNumber=@Mobile) Where Id=@Id";
//now call the common method
Add_Edit(CommandText);
}

The common method (Add-Edit) :

public void Add-Edit(string CommandText)
{
SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["MyConnectionString"].ConnectionString);
SqlCommand command = new SqlCommand(CommandText, connection);
command.Parameters.AddWithValue("@FirstName",tbFirstName.Text );
command.Parameters.AddWithValue("@LastName", tbLastName.Text);
command.Parameters.AddWithValue("@Email", tbEmail.Text);
command.Parameters.AddWithValue("@Mobile",tbMobile.Text);
command.Parameters.AddWithValue("@Id",someId); //InCase of Add contact no issues with Id

try
{
connection.Open();
command.ExecuteNonQuery();
}
finally
{
connection.Close();
}
}

In this way we can reduce the code by writing a common method for Insert(Add) and Edit(Update).


Comments

Author: BHARAT BHUSHAN SOMA26 Jul 2013 Member Level: Bronze   Points : 0

Thank ..helped a lot for me!!!! :-)

Guest Author: KrishnaMoorthi S29 Jul 2013

The common method (Add-Edit) :

public void Add-Edit(string CommandText)
{
SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["MyConnectionString"].ConnectionString);
SqlCommand command = new SqlCommand("CommandText", connection);-->*** I think this is Wrong ***
*** we should apply like this ***'--> SqlCommand command = new SqlCommand(CommandText, connection);command.Parameters.AddWithValue("@FirstName",tbFirstName.Text );
command.Parameters.AddWithValue("@LastName", tbLastName.Text);
command.Parameters.AddWithValue("@Email", tbEmail.Text);
command.Parameters.AddWithValue("@Mobile",tbMobile.Text);
command.Parameters.AddWithValue("@Id",someId); //InCase of Add contact no issues with Id

try
{
connection.Open();
command.ExecuteNonQuery();
}
finally
{
connection.Close();
}
}

Author: ranadheer15 Oct 2013 Member Level: Silver   Points : 1

Hi krishnamoorthi,

Thank you very much for correcting it. I did not notice it. I will change it now. Happy coding.



  • Do not include your name, "with regards" etc in the comment. Write detailed comment, relevant to the topic.
  • No HTML formatting and links to other web sites are allowed.
  • This is a strictly moderated site. Absolutely no spam allowed.
  • Name:
    Email: