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

I explain how to insert bulk data (multiple rows) to a SQL Server database using ASP.NET.
One of the common problems I have seen is to bulk data insert to a SQL Server database at a time using ASP.Net Application. Here in this post I have done this easily following these simple steps.

Here I have created a page containing a Gridview with no of blank rows(on demand) for take inputs in application end, and created a stored procedure with a parameter (xml type) for accept bulk data at a time in sql server end.

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 Save bulk data.

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

   
     CREATE PROCEDURE [dbo].[ContactBulkInsert]
 @XMLData xml
AS
 Create table #tempContact(
  FirstName varchar(50) not null,
  LastName varchar(50) not null,
  ContactNo varchar(50) not null
 ); 
 
 Insert into Contacts(FirstName,LastName,ContactNo)
  Select  
  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)
RETURN

Step-5: Add a Webpage and Design for Save/Show Multiple Records to/from 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>Insert Multiple Record in a SQL Database using ASP.Net application.</h3>
    <div style="padding:10px 0px">
        No of record you want to insert : 
        <asp:TextBox ID="txtNoOfRecord" runat="server"></asp:TextBox>
        &nbsp;
        <asp:Button ID="btnAddRow" runat="server" Text="Add Rows" OnClick="btnAddRow_Click" />
 
    </div>
    <asp:GridView ID="gvContacts" runat="server" AutoGenerateColumns="false" CellPadding="5">
        <Columns>
            <asp:TemplateField HeaderText="SL No.">
                <ItemTemplate>
                    <%#Container.DataItemIndex +1 %>
                </ItemTemplate>
            </asp:TemplateField>
            <asp:TemplateField HeaderText="First Name">
                <ItemTemplate>
                    <asp:TextBox ID="txtFirstName" runat="server"></asp:TextBox>
                </ItemTemplate>
            </asp:TemplateField>
            <asp:TemplateField HeaderText="Last Name">
                <ItemTemplate>
                    <asp:TextBox ID="txtLastName" runat="server"></asp:TextBox>
                </ItemTemplate>
            </asp:TemplateField>
            <asp:TemplateField HeaderText="Contact No">
                <ItemTemplate>
                    <asp:TextBox ID="txtContactNo" runat="server"></asp:TextBox>
                </ItemTemplate>
            </asp:TemplateField>
        </Columns>
    </asp:GridView>
    <div style="padding:10px 0px;">
        <asp:Panel ID="Panel1" runat="server" Visible="false">
            <asp:Button ID="btnSave" runat="server" Text="Save" OnClick="btnSave_Click" />
            &nbsp;<asp:Label ID="lblMsg" runat="server" ></asp:Label>
        </asp:Panel>
    </div>
    <div>
        <b>Database Records</b>
        <div>
            <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="false" CellPadding="5">
                <Columns>
                    <asp:TemplateField HeaderText="SL No.">
                        <ItemTemplate>
                            <%#Eval("ID") %>
                        </ItemTemplate>
                    </asp:TemplateField>
                    <asp:TemplateField HeaderText="First Name">
                        <ItemTemplate>
                            <%#Eval("FirstName") %>
                        </ItemTemplate>
                    </asp:TemplateField>
                    <asp:TemplateField HeaderText="Last Name">
                        <ItemTemplate>
                           <%#Eval("LastName") %>
                        </ItemTemplate>
                    </asp:TemplateField>
                    <asp:TemplateField HeaderText="Contact No">
                        <ItemTemplate>
                            <%#Eval("ContactNo") %>
                        </ItemTemplate>
                    </asp:TemplateField>
                </Columns>
            </asp:GridView>
        </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))
            {
                SqlCommand cmd = new SqlCommand("Select * from Contacts", con);
                if (con.State != System.Data.ConnectionState.Open)
                {
                    con.Open();
                }
                DataTable dt = new DataTable();
                dt.Load(cmd.ExecuteReader());
                GridView1.DataSource = dt;
                GridView1.DataBind();
            }
        }
        

Step-7: Write code in button (btnAddRow) click event for add blank row in gridview for take inputs.

            
        protected void btnAddRow_Click(object sender, EventArgs e)
        {
            AddRowsToGrid();
        }          

and here is the function

            
        private void AddRowsToGrid()
        {
            List<int> noofRows = new List<int>();
            int rows = 0;
            int.TryParse(txtNoOfRecord.Text.Trim(), out rows);
 
            for (int i = 0; i < rows; i++)
            {
                noofRows.Add(i);
            }
 
            gvContacts.DataSource = noofRows;
            gvContacts.DataBind();
            if (gvContacts.Rows.Count > 0)
            {
                Panel1.Visible = true;
            }
            else
            {
                Panel1.Visible = false;
            }
        }

Step-8: Write code in button (btnSave) click event for Save multiple records to SQL Server database.

            
        protected void btnSave_Click(object sender, EventArgs e)
        {
            // Save Here
            StringBuilder sb = new StringBuilder();
            sb.AppendLine("<?xml version=\"1.0\" ?>");
            sb.AppendLine("     <contacts>");
 
            // Check Validity
            TextBox txtFirstName;
            TextBox txtLastName;
            TextBox txtContactNo;
 
            foreach (GridViewRow row in gvContacts.Rows)
            {
                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("         <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>");
 
            using (SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["con"].ConnectionString))
            {
                SqlCommand cmd = new SqlCommand("ContactBulkInsert", con);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("@XMLData", sb.ToString());
                if (con.State != ConnectionState.Open)
                {
                    con.Open();
                }
 
                int affRow = cmd.ExecuteNonQuery();
                if (affRow > 0)
                {
                    lblMsg.Text = "Successfully " + affRow + " record inserted.";
                    PopulateData();
                    AddRowsToGrid();
                }
            }
        }

Step-9: Run Application.