How to Update bulk data (multiple rows) to a SQL Server database using ASP.NET

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. 

In this example, I have used table as below

Step-4: Create a Stored Procedure for Update bulk data.

Right Click on Stored Procedure (in your database) > Add new Stoted procedure > Write following SQL Code And Execute.

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();
    }
}

and here is the function

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.