Introduction

In one of my previous article, we have seen how to insert bulk data to a SQL server database in asp.net webform application. Today I am going to explain how to update bulk data (multiple rows) to a SQL Server database in asp.net webform.

In  asp.net webform application, we used Gridview control to allow user perform CRUD (Create, Read, Update and Delete) operations in many occasions. You can see here how to implement CRUD operation in ASP.NET webforms application where user clicks on Edit button of a particular row, modify data and clicks update to save the changes in database.

Now suppose user wants to modify multiple records at a time. Today I will show how how to update multiple rows at once in asp.net webforms application. Here in our scenario, Gridview loads on editable mode and once modifications are done, user will click on update button for update modified data in the database.

If you have asp.net mvc project, please visit how to update multiple rows at once in mvc application.

Steps :

Step - 1 : Create New Project.

Go to File > New > Project > Select asp.net web forms application > Entry Application Name > Click OK.

Step-2: Add a Database.

Go to Solution Explorer > Right Click on App_Data folder > Add > New item > Select SQL Server Database Under Data > Enter Database name > Add.

Step-3: Create a table for Save Records.

Open Database > Right Click on Table > Add New Table > Add Columns > Save > Enter table name > Ok.

CREATE PROCEDURE dbo.ContactBulkUpdate
 @XMLData xml
AS
 Create table #tempContact
 (
   ID int not null,
   FirstName varchar(50) not null,
   LastName varchar(50) not null,
   ContactNo varchar(50) not null
 )

 Insert into #tempContact (ID, FirstName, LastName, ContactNo)
  Select 
   contact.query('ID').value('.','int') as ID,
   contact.query('FirstName').value('.','varchar(50)') as FirstName,
   contact.query('LastName').value('.','varchar(50)') as LastName,
   contact.query('ContactNo').value('.','varchar(50)') as ContactNo
  From
   @XMLData.nodes('/contacts/contact') as xmlData(contact)

  Update Contacts 
   Set 
    FirstName = b.FirstName,
    LastName = b.LastName,
    ContactNo = b.ContactNo
   from
    #tempContact as b
   Where
    Contacts.ID = b.ID
RETURN

Step-5: Add a Webpage and Design for Show/Update Multiple Records from/to SQL Server Database.

Go to Solution Explorer > Right Click on Project name form Solution Explorer > Add > New item > Select web form/ web form using master page under Web > Enter page name > Add.

HTML Code

  
   <h3>Bulk update in ASP.NET</h3>
    <div style="padding:20px">

        <asp:GridView ID="gvContacts" runat="server" AutoGenerateColumns="false" CellPadding="5" DataKeyNames="ID">
            <Columns>
                <asp:TemplateField HeaderText="ID">
                    <ItemTemplate>
                        <asp:Label ID="lblID" runat="server" Text='<%# Bind("ID") %>'></asp:Label>
                    </ItemTemplate>
                </asp:TemplateField>
                <asp:TemplateField HeaderText="First Name">
                    <ItemTemplate>
                        <asp:TextBox ID="txtFirstName" runat="server" Text='<%# Bind("FirstName") %>'></asp:TextBox>
                    </ItemTemplate>
                </asp:TemplateField>
                <asp:TemplateField HeaderText="Last Name">
                    <ItemTemplate>
                        <asp:TextBox ID="txtLastName" runat="server" Text='<%# Bind("LastName") %>'></asp:TextBox>
                    </ItemTemplate>
                </asp:TemplateField>
                <asp:TemplateField HeaderText="Contact No">
                    <ItemTemplate>
                        <asp:TextBox ID="txtContactNo" runat="server" Text='<%# Bind("ContactNo") %>'></asp:TextBox>
                    </ItemTemplate>
                </asp:TemplateField>
            </Columns>
        </asp:GridView>
        <div style="padding:20px;">
            <asp:Button ID="btnUpdate" runat="server" Text="Update" OnClick="btnUpdate_Click" />
            <asp:Label ID="lblMsg" runat="server" ></asp:Label>
        </div>
    </div>

Step-6: Write code in page_load event for fetch and show existing data from database.

protected void Page_Load(object sender, EventArgs e)
{
    if (!IsPostBack)
    {
        PopulateData();
    }
}

private void PopulateData()
{
    using (SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["con"].ConnectionString))
    {
        DataTable dt = new DataTable();
        SqlCommand cmd = new SqlCommand("Select * from Contacts", con);
        if (con.State !=  ConnectionState.Open)
        {
            con.Open();
        }

        dt.Load(cmd.ExecuteReader());

        gvContacts.DataSource = dt;
        gvContacts.DataBind();
    }
}

Step-7: Write code in button (btnUpdate) click event for Update multiple records to SQL Server database.

protected void btnUpdate_Click(object sender, EventArgs e)
{
    StringBuilder sb = new StringBuilder();
    sb.AppendLine("<?xml version=\"1.0\" ?>");
    sb.AppendLine("     <contacts>");
    TextBox txtFirstName;
    TextBox txtLastName;
    TextBox txtContactNo;
    foreach (GridViewRow row in gvContacts.Rows)
    {
        int ID = Convert.ToInt32(gvContacts.DataKeys[row.RowIndex].Values["ID"].ToString());
        txtFirstName = (TextBox)row.FindControl("txtFirstName");
        txtLastName = (TextBox)row.FindControl("txtLastName");
        txtContactNo = (TextBox)row.FindControl("txtContactNo");

        if (txtFirstName == null || txtLastName == null || txtContactNo == null)
        {
            return;
        }
        if (String.IsNullOrEmpty(txtFirstName.Text.Trim()) || String.IsNullOrEmpty(txtLastName.Text.Trim())||
            String.IsNullOrEmpty(txtContactNo.Text.Trim()))
        {
            lblMsg.Text = "All fields are required!";
            return;
        }
        else
        {
            sb.AppendLine("         <contact>");
            sb.AppendLine("             <ID>" + ID + "</ID>");
            sb.AppendLine("             <FirstName>" + txtFirstName.Text.Trim() + "</FirstName>");
            sb.AppendLine("             <LastName>" + txtLastName.Text.Trim() + "</LastName>");
            sb.AppendLine("             <ContactNo>" + txtContactNo.Text.Trim() + "</ContactNo>");
            sb.AppendLine("         </contact>");
        }
    }
    sb.AppendLine("         </contacts>");
    //Update query here
    using (SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["con"].ConnectionString))
    {
        SqlCommand cmd = new SqlCommand("ContactBulkUpdate", con);
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.AddWithValue("@XMLData", sb.ToString());
        if (con.State != ConnectionState.Open)
        {
            con.Open();
        }
        int aff = cmd.ExecuteNonQuery();
        if (aff > 0)
        {
            lblMsg.Text = "Successfully Updated!";
            //pOPULATE Updated data again.
            PopulateData();
        }
        else
        {
            lblMsg.Text = "Failed! Please try again.";
        }
    }
}

Step-8: Run Application.