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).

Sql Insert and Sql Edit in one common method

Comments

About dotnetspider.com

Quick Links

General

Services