Post
Ask Question

Insert Multiple Bulk Data Rows Into a Database Using XML in ASP.Net

For this example we will create a .aspx page GridView, TextBox and button control and create an XML parameter Stored Procedure in the database.

So I will explain step-by-step how to insert bulk data into a database.

Step 1: SQL Database

First create a table in a database store to insert a data value into a table as follows.

  1. Create Table EmployeeData.
    1. Create Table EmployeeData  
    2. (  
    3.    EmpID int identity (1,1) Primary Key,  
    4.    EmpName varchar(30),  
    5.    Contact nchar(15),  
    6.    EmailId nvarchar(50)  
    7. ) 
    In this new table the EmpID column is an auto-increment field for the Employee Identity.

  2. Create XML Parameter Stored Procedure

    Now create an insert data procedure in the SQL database as in the following.
    1. Create PROCEDURE sp_BulkEmployeeDataInsert  
    2.    @EmpData xml  
    3. AS  
    4. Create table #tempEmployeeData(  
    5.    EmpName varchar(30) not null,  
    6.    Contact nchar(15) not null,  
    7.    EmailId nvarchar(50) not null  
    8. );  
    9.   
    10. Insert into EmployeeData(EmpName,Contact,EmailId)  
    11. Select  
    12.    Employee.query('EmpName').value('.', 'varchar(30)') as EmpName,  
    13.    Employee.query('Contact').value('.', 'nchar(15)') as Contact,  
    14.    Employee.query('EmailId').value('.', 'nvarchar(50)') as EmailId  
    15. FROM  
    16.    @EmpData.nodes('/EmployeeData/Employee')AS EmpData(Employee)  
    17. RETURN 
    Now create a Select procedure for the inserted record display in the Grid.

  3. Select Data Procedure
    1. Create procedure [dbo].[sp_FillData]  
    2. As  
    3.    Begin  
    4.    set nocount on;  
    5.    select EmpID, EmpName, Contact, EmailID from EmployeeData  
    6. End 
Step 2: Visual Studio

Create a UI Design inside Visual Studio using the following procedure:
  • Go to Solution Explorer.
  • Right-click on the project and click the Add tab
  • Click the Add New Item as in the following:

Add Web Form
Figure 1: Add Web Form

Now I will write the design code for the web page containing the TextBox inside the GridView, and a Button control to save the bulk data. The following is the UI design code.

  1. <%@ Page Title="" Language="C#" MasterPageFile="~/Master/Master.master" AutoEventWireup="true" CodeFile="BulkData.aspx.cs" Inherits="UI_BulkData" %>  
  2.   
  3. <asp:Content ID="Content1" ContentPlaceHolderID="head" Runat="Server">  
  4. </asp:Content>  
  5. <asp:Content ID="Content2" ContentPlaceHolderID="ContentPlaceHolder1" Runat="Server">  
  6.   
  7.     <h3 style="background-color: #FFFFFF; font-weight: bold; font-style: italic; font-variant: small-caps; text-transform: uppercase; color: #000000; width: 1216px; times: ;, ">Insert Multiple BulkData Row Record in a SQL Database Table </h3>  
  8.     <div>  
  9.          Insert No. of Row Record   
  10.         <asp:TextBox ID="txtAddNoOfRecord" runat="server" Width="30px" style="margin-left: 5px"></asp:TextBox>  
  11.            
  12.         <asp:Button ID="btnAddRow" runat="server" Text="Add Rows" OnClick="btnAddRow_Click"  />  
  13.         <br />  
  14.         <br />  
  15.     </div>  
  16.     <asp:GridView ID="GridAddEmp" runat="server" AutoGenerateColumns="False" CellPadding="4" BackColor="White" BorderColor="#CC9966" BorderStyle="None" BorderWidth="1px">  
  17.         <Columns>  
  18.             <asp:TemplateField HeaderText="No.">  
  19.                 <ItemTemplate>  
  20.                     <%#Container.DataItemIndex +1 %>  
  21.                 </ItemTemplate>  
  22.             </asp:TemplateField>  
  23.             <asp:TemplateField HeaderText="Employee Name">  
  24.                 <ItemTemplate>  
  25.                     <asp:TextBox ID="txtName" runat="server"></asp:TextBox>  
  26.                 </ItemTemplate>  
  27.             </asp:TemplateField>  
  28.             <asp:TemplateField HeaderText="Contact">  
  29.                 <ItemTemplate>  
  30.                     <asp:TextBox ID="txtContact" runat="server"></asp:TextBox>  
  31.                 </ItemTemplate>  
  32.             </asp:TemplateField>  
  33.             <asp:TemplateField HeaderText="Email Id">  
  34.                 <ItemTemplate>  
  35.                     <asp:TextBox ID="txtEmail" runat="server"></asp:TextBox>  
  36.                 </ItemTemplate>  
  37.             </asp:TemplateField>  
  38.         </Columns>  
  39.         <FooterStyle BackColor="#FFFFCC" ForeColor="#330099" />  
  40.         <HeaderStyle BackColor="#990000" Font-Bold="True" ForeColor="#FFFFCC" />  
  41.         <PagerStyle BackColor="#FFFFCC" ForeColor="#330099" HorizontalAlign="Center" />  
  42.         <RowStyle BackColor="White" ForeColor="#330099" />  
  43.         <SelectedRowStyle BackColor="#FFCC66" Font-Bold="True" ForeColor="#663399" />  
  44.         <SortedAscendingCellStyle BackColor="#FEFCEB" />  
  45.         <SortedAscendingHeaderStyle BackColor="#AF0101" />  
  46.         <SortedDescendingCellStyle BackColor="#F6F0C0" />  
  47.         <SortedDescendingHeaderStyle BackColor="#7E0000" />  
  48.     </asp:GridView>  
  49.     <div style="padding:10px 0px;">  
  50.         <asp:Panel ID="PanelData" runat="server" Visible="false">  
  51.             <asp:Button ID="btnSave" runat="server" Text="Save" OnClick="btnSave_Click" /><br />  
  52.              <asp:Label ID="lblMsg" runat="server" ></asp:Label>  
  53.         </asp:Panel>  
  54.     </div>  
  55.     <div>  
  56.         <b>Database Records</b>  
  57.         <div>  
  58.             <asp:GridView ID="GridEmpData" runat="server" AutoGenerateColumns="False" CellPadding="3" BackColor="#DEBA84" BorderColor="#DEBA84" BorderStyle="None" BorderWidth="1px" CellSpacing="2">  
  59.                 <Columns>  
  60.                     <asp:TemplateField HeaderText="No.">  
  61.                         <ItemTemplate>  
  62.                             <%#Eval("EmpID") %>  
  63.                         </ItemTemplate>  
  64.                     </asp:TemplateField>  
  65.                     <asp:TemplateField HeaderText="Employee Name">  
  66.                         <ItemTemplate>  
  67.                             <%#Eval("EmpName") %>  
  68.                         </ItemTemplate>  
  69.                     </asp:TemplateField>  
  70.                     <asp:TemplateField HeaderText="Contact">  
  71.                         <ItemTemplate>  
  72.                            <%#Eval("Contact") %>  
  73.                         </ItemTemplate>  
  74.                     </asp:TemplateField>  
  75.                     <asp:TemplateField HeaderText="Email Id">  
  76.                         <ItemTemplate>  
  77.                             <%#Eval("EmailId") %>  
  78.                         </ItemTemplate>  
  79.                     </asp:TemplateField>  
  80.                 </Columns>  
  81.                 <FooterStyle BackColor="#F7DFB5" ForeColor="#8C4510" />  
  82.                 <HeaderStyle BackColor="#A55129" Font-Bold="True" ForeColor="White" />  
  83.                 <PagerStyle ForeColor="#8C4510" HorizontalAlign="Center" />  
  84.                 <RowStyle BackColor="#FFF7E7" ForeColor="#8C4510" />  
  85.                 <SelectedRowStyle BackColor="#738A9C" Font-Bold="True" ForeColor="White" />  
  86.                 <SortedAscendingCellStyle BackColor="#FFF1D4" />  
  87.                 <SortedAscendingHeaderStyle BackColor="#B95C30" />  
  88.                 <SortedDescendingCellStyle BackColor="#F1E5CE" />  
  89.                 <SortedDescendingHeaderStyle BackColor="#93451F" />  
  90.             </asp:GridView>  
  91.         </div>  
  92.     </div>    
  93. </asp:Content> 


Step 3: UI CODE

Now go to the UI code side section. In this first write the code for the TextBox value insertion in the SQL table by the preceding Insert using the preceding create XML parameter procedure. Then write text record save code is in the save button control event.

This is a UI back side.

  1. using System;  
  2. using System.Collections.Generic;  
  3. using System.Linq;  
  4. using System.Web;  
  5. using System.Web.UI;  
  6. using System.Web.UI.WebControls;  
  7. using System.Configuration;  
  8. using System.Data;  
  9. using System.Data.SqlClient;  
  10. using System.Text;  
  11.   
  12. public partial class UI_BulkData : System.Web.UI.Page  
  13. {  
  14.     string connection = ConfigurationManager.ConnectionStrings["connstring"].ConnectionString;  
  15.     protected void Page_Load(object sender, EventArgs e)  
  16.     {  
  17.   
  18.     }  
  19.     private void FillEmpData()  
  20.     {  
  21.         using (SqlConnection con = new SqlConnection(connection))  
  22.         {  
  23.             using (SqlCommand cmd = new SqlCommand("sp_FillData",con))  
  24.             {  
  25.                 cmd.CommandType = CommandType.StoredProcedure;  
  26.                 if (con.State != System.Data.ConnectionState.Open)  
  27.                 {  
  28.                     con.Open();  
  29.                 }  
  30.                 DataTable dt = new DataTable();  
  31.                 dt.Load(cmd.ExecuteReader());  
  32.                 GridEmpData.DataSource = dt;  
  33.                 GridEmpData.DataBind();  
  34.             }  
  35.         }  
  36.     }  
  37.     protected void btnAddRow_Click(object sender, EventArgs e)  
  38.     {  
  39.         AddRowsToGrid();  
  40.     }  
  41.     private void AddRowsToGrid()  
  42.     {  
  43.         List<int> Row_Number = new List<int>();  
  44.         int rows = 0;  
  45.         int.TryParse(txtAddNoOfRecord.Text.Trim(), out rows);  
  46.   
  47.         for (int R = 0; R < rows; R++)  
  48.         {  
  49.             Row_Number.Add(R);  
  50.         }  
  51.         GridAddEmp.DataSource = Row_Number;  
  52.         GridAddEmp.DataBind();  
  53.         if (GridAddEmp.Rows.Count > 0)  
  54.         {  
  55.             PanelData.Visible = true;  
  56.         }  
  57.         else  
  58.         {  
  59.             PanelData.Visible = false;  
  60.         }  
  61.   
  62.     }  
  63.   
  64.   
  65.     protected void btnSave_Click(object sender, EventArgs e)  
  66.     {  
  67.         StringBuilder sb = new StringBuilder();  
  68.         sb.AppendLine("<?xml version=\"1.0\" ?>");  
  69.   
  70.         foreach (GridViewRow row in GridAddEmp.Rows)  
  71.         {  
  72.          TextBox txtName = (TextBox)row.FindControl("txtName");  
  73.          TextBox txtContact = (TextBox)row.FindControl("txtContact");  
  74.          TextBox txtEmail = (TextBox)row.FindControl("txtEmail");  
  75.   
  76.             if (txtName == null || txtName == null || txtEmail == null)  
  77.             {  
  78.                 return;  
  79.             }  
  80.   
  81.             if (string.IsNullOrEmpty(txtName.Text.Trim()) || string.IsNullOrEmpty(txtContact.Text.Trim()) || string.IsNullOrEmpty(txtEmail.Text.Trim()))  
  82.             {  
  83.                 lblMsg.Text = "You Can not Empty Any Text Field";  
  84.                 return;  
  85.             }  
  86.             else  
  87.             {  
  88.                 sb.AppendLine("<EmployeeData>");  
  89.                 sb.AppendLine(" <Employee>");  
  90.                 sb.AppendLine("  <EmpName>" + txtName.Text.Trim() + "</EmpName>");  
  91.                 sb.AppendLine("  <Contact>" + txtContact.Text.Trim() + "</Contact>");  
  92.                 sb.AppendLine("  <EmailId>" + txtEmail.Text.Trim() + "</EmailId>");  
  93.                 sb.AppendLine(" </Employee>");  
  94.                 sb.AppendLine("</EmployeeData>");  
  95.             }  
  96.   
  97.         }  
  98.         // data insert into table   
  99.         {  
  100.            using(SqlConnection con = new SqlConnection(connection))  
  101.            {  
  102.                using (SqlCommand cmd = new SqlCommand("sp_BulkEmployeeDataInsert", con))  
  103.                {  
  104.                    cmd.CommandType = CommandType.StoredProcedure;  
  105.                    cmd.Parameters.AddWithValue("@EmpData", sb.ToString());  
  106.   
  107.                    if (con.State != ConnectionState.Open)  
  108.                    {  
  109.                        con.Open();  
  110.                    }  
  111.   
  112.                    int AddRow = cmd.ExecuteNonQuery();  
  113.                    if (AddRow > 0)  
  114.                    {  
  115.                        lblMsg.Text = "No. of " + AddRow + " Row record inserted";  
  116.                        FillEmpData();  
  117.                        AddRowsToGrid();  
  118.                    }  
  119.                }  
  120.                  
  121.            }  
  122.         }  
  123.     }  
  124. }

Step 4: Browser Side

Now run your new page in the browser.

Add No of Row
Figure 2: Add No of Row

Then after pressing the Add Rows button see it as in the following web form.

Fill Record in Added Row
Figure 3: Fill Record in Added Row

After saving the filled-in record in the database using the Save button:

Record Save
Figure 4: Record Save

Also check in the database table that the record was inserted.

Note: Configure a connection sting in the project's config file as in the following:

  1. <connectionStrings>  
  2.    <add name="connstr" connectionString="Data Source=RAKESH-PC;Initial Catalog=SqlServerTech;User ID=sa;Password=password" providerName="System.Data.SqlClient"/>  
  3. </connectionStrings> 

I hope you understood how to save bulk data into a database with a SQL XML parameter procedure using ASP.Net.


Similar Articles