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.
- Create Table EmployeeData.
- Create Table EmployeeData
- (
- EmpID int identity (1,1) Primary Key,
- EmpName varchar(30),
- Contact nchar(15),
- EmailId nvarchar(50)
- )
In this new table the EmpID column is an auto-increment field for the Employee Identity.
- Create XML Parameter Stored Procedure
Now create an insert data procedure in the SQL database as in the following.
- Create PROCEDURE sp_BulkEmployeeDataInsert
- @EmpData xml
- AS
- Create table #tempEmployeeData(
- EmpName varchar(30) not null,
- Contact nchar(15) not null,
- EmailId nvarchar(50) not null
- );
-
- Insert into EmployeeData(EmpName,Contact,EmailId)
- Select
- Employee.query('EmpName').value('.', 'varchar(30)') as EmpName,
- Employee.query('Contact').value('.', 'nchar(15)') as Contact,
- Employee.query('EmailId').value('.', 'nvarchar(50)') as EmailId
- FROM
- @EmpData.nodes('/EmployeeData/Employee')AS EmpData(Employee)
- RETURN
Now create a Select procedure for the inserted record display in the Grid.
- Select Data Procedure
- Create procedure [dbo].[sp_FillData]
- As
- Begin
- set nocount on;
- select EmpID, EmpName, Contact, EmailID from EmployeeData
- End
Step 2: Visual StudioCreate 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:
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.
- <%@ Page Title="" Language="C#" MasterPageFile="~/Master/Master.master" AutoEventWireup="true" CodeFile="BulkData.aspx.cs" Inherits="UI_BulkData" %>
-
- <asp:Content ID="Content1" ContentPlaceHolderID="head" Runat="Server">
- </asp:Content>
- <asp:Content ID="Content2" ContentPlaceHolderID="ContentPlaceHolder1" Runat="Server">
-
- <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>
- <div>
- Insert No. of Row Record
- <asp:TextBox ID="txtAddNoOfRecord" runat="server" Width="30px" style="margin-left: 5px"></asp:TextBox>
-
- <asp:Button ID="btnAddRow" runat="server" Text="Add Rows" OnClick="btnAddRow_Click" />
- <br />
- <br />
- </div>
- <asp:GridView ID="GridAddEmp" runat="server" AutoGenerateColumns="False" CellPadding="4" BackColor="White" BorderColor="#CC9966" BorderStyle="None" BorderWidth="1px">
- <Columns>
- <asp:TemplateField HeaderText="No.">
- <ItemTemplate>
- <%#Container.DataItemIndex +1 %>
- </ItemTemplate>
- </asp:TemplateField>
- <asp:TemplateField HeaderText="Employee Name">
- <ItemTemplate>
- <asp:TextBox ID="txtName" runat="server"></asp:TextBox>
- </ItemTemplate>
- </asp:TemplateField>
- <asp:TemplateField HeaderText="Contact">
- <ItemTemplate>
- <asp:TextBox ID="txtContact" runat="server"></asp:TextBox>
- </ItemTemplate>
- </asp:TemplateField>
- <asp:TemplateField HeaderText="Email Id">
- <ItemTemplate>
- <asp:TextBox ID="txtEmail" runat="server"></asp:TextBox>
- </ItemTemplate>
- </asp:TemplateField>
- </Columns>
- <FooterStyle BackColor="#FFFFCC" ForeColor="#330099" />
- <HeaderStyle BackColor="#990000" Font-Bold="True" ForeColor="#FFFFCC" />
- <PagerStyle BackColor="#FFFFCC" ForeColor="#330099" HorizontalAlign="Center" />
- <RowStyle BackColor="White" ForeColor="#330099" />
- <SelectedRowStyle BackColor="#FFCC66" Font-Bold="True" ForeColor="#663399" />
- <SortedAscendingCellStyle BackColor="#FEFCEB" />
- <SortedAscendingHeaderStyle BackColor="#AF0101" />
- <SortedDescendingCellStyle BackColor="#F6F0C0" />
- <SortedDescendingHeaderStyle BackColor="#7E0000" />
- </asp:GridView>
- <div style="padding:10px 0px;">
- <asp:Panel ID="PanelData" runat="server" Visible="false">
- <asp:Button ID="btnSave" runat="server" Text="Save" OnClick="btnSave_Click" /><br />
- <asp:Label ID="lblMsg" runat="server" ></asp:Label>
- </asp:Panel>
- </div>
- <div>
- <b>Database Records</b>
- <div>
- <asp:GridView ID="GridEmpData" runat="server" AutoGenerateColumns="False" CellPadding="3" BackColor="#DEBA84" BorderColor="#DEBA84" BorderStyle="None" BorderWidth="1px" CellSpacing="2">
- <Columns>
- <asp:TemplateField HeaderText="No.">
- <ItemTemplate>
- <%#Eval("EmpID") %>
- </ItemTemplate>
- </asp:TemplateField>
- <asp:TemplateField HeaderText="Employee Name">
- <ItemTemplate>
- <%#Eval("EmpName") %>
- </ItemTemplate>
- </asp:TemplateField>
- <asp:TemplateField HeaderText="Contact">
- <ItemTemplate>
- <%#Eval("Contact") %>
- </ItemTemplate>
- </asp:TemplateField>
- <asp:TemplateField HeaderText="Email Id">
- <ItemTemplate>
- <%#Eval("EmailId") %>
- </ItemTemplate>
- </asp:TemplateField>
- </Columns>
- <FooterStyle BackColor="#F7DFB5" ForeColor="#8C4510" />
- <HeaderStyle BackColor="#A55129" Font-Bold="True" ForeColor="White" />
- <PagerStyle ForeColor="#8C4510" HorizontalAlign="Center" />
- <RowStyle BackColor="#FFF7E7" ForeColor="#8C4510" />
- <SelectedRowStyle BackColor="#738A9C" Font-Bold="True" ForeColor="White" />
- <SortedAscendingCellStyle BackColor="#FFF1D4" />
- <SortedAscendingHeaderStyle BackColor="#B95C30" />
- <SortedDescendingCellStyle BackColor="#F1E5CE" />
- <SortedDescendingHeaderStyle BackColor="#93451F" />
- </asp:GridView>
- </div>
- </div>
- </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.
Step 4: Browser Side
Now run your new page in the browser.
Figure 2: Add No of Row
Then after pressing the Add Rows button see it as in the following web form.
Figure 3: Fill Record in Added Row
After saving the filled-in record in the database using the Save button:
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:
- <connectionStrings>
- <add name="connstr" connectionString="Data Source=RAKESH-PC;Initial Catalog=SqlServerTech;User ID=sa;Password=password" providerName="System.Data.SqlClient"/>
- </connectionStrings>
I hope you understood how to save bulk data into a database with a SQL XML parameter procedure using ASP.Net.