In this article I am going to show how we can insert
bulk data into the SQL Server.
Here I am uploading and saving XML file in a directory
after this I am showing all XML files from the directory in a Grid View. From
this Grid View user can download, delete and read data by clicking appropriate
button. After reading data from XML user can select rows and click on Bulk
Insert button. Flow of my application is:
Step 1: Upload XML file.
Step 2: Showing All XML files from directory to Grid
View.
Step 3: User Can Download, Delete & Read XML File.
Step 4: User can select Rows to insert.
Step 5: Click Bulk Insert button to insert data.
Step 6: All Data from Data base is showing in Grid
View.
Below is my SQL Server Data Table is design
mode:
Image 1.
Data in My Table :
Image 2.
Now Below is my XML File format for this application
and Data table.
Image 3.
Now my aspx code is:
<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="ManageBulkCopy.aspx.cs" Inherits="SQLBulkInsert.ManageBulkCopy" %>
<!DOCTYPE html>
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title>SQL SERVER Bulk Copy</title>
</head>
<body>
<form id="form1" runat="server" style="width: 100%; text-align: center;">
<div>
<table style="width: 100%; border: solid 2px red;">
<tr style="background-color: green; color: white; font-size: 15pt; font-weight: bold; height: 30px; text-align: center;">
<td>Upload XML Files
</td>
</tr>
<tr>
<td>
<table style="width: 100%; border: solid 2px red; height: 60px; background-color: skyblue;">
<tr>
<td>Select File To Upload:
<asp:FileUpload ID="FileUploadControl" runat="server" />
<asp:Button runat="server" ID="UploadButton" Text="Upload" OnClick="UploadButton_Click" />
<asp:Label runat="server" ID="StatusLabel" /></td>
</tr>
</table>
</td>
</tr>
</table>
<table>
<tr>
<td style="height: 5px;"></td>
</tr>
</table>
<table style="width: 100%; border: solid 2px red;">
<tr style="background-color: green; color: white; font-size: 15pt; font-weight: bold; height: 30px; text-align: center;">
<td>All Uploaded XML Files
</td>
</tr>
<tr>
<td>
<asp:GridView ID="GridViewAllXMLFiles" runat="server" AutoGenerateColumns="False" EmptyDataRowStyle-ForeColor="Red" EmptyDataText="There is no
File." Width="70%" BackColor="#DEBA84" BorderColor="#DEBA84" BorderStyle="None" BorderWidth="1px" CellPadding="3" CellSpacing="2">
<Columns>
<asp:BoundField DataField="Text" HeaderText="File
Name" />
<asp:TemplateField>
<ItemTemplate>
<asp:LinkButton ID="lnkDownload" Text="Download File" CommandArgument='<%# Eval("Value") %>' runat="server"
OnClick="DownloadFile"></asp:LinkButton>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField>
<ItemTemplate>
<asp:LinkButton ID="lnkDelete" Text="Delete File" CommandArgument='<%# Eval("Value") %>' runat="server"
OnClick="DeleteFile" />
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField>
<ItemTemplate>
<asp:LinkButton ID="lnkReadXML" Text="Read XML File" CommandArgument='<%# Eval("Value") %>' runat="server"
OnClick="ReadFile" />
</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>
</td>
</tr>
<tr>
<td>
<asp:GridView ID="GridViewXMLRecords" runat="server" AutoGenerateColumns="False" CellPadding="4" ForeColor="#333333"
GridLines="None" Width="100%">
<AlternatingRowStyle BackColor="White" />
<Columns>
<asp:TemplateField>
<ItemTemplate>
<asp:CheckBox ID="CheckBox1" runat="server" />
</ItemTemplate>
</asp:TemplateField>
<asp:BoundField DataField="Name" HeaderText="Name" />
<asp:BoundField DataField="Email" HeaderText="Email" />
<asp:BoundField DataField="Class" HeaderText="Class" />
<asp:BoundField DataField="EnrollYear" HeaderText="EnrollYear" />
<asp:BoundField DataField="City" HeaderText="City" />
<asp:BoundField DataField="Country" HeaderText="Country" />
</Columns>
<EditRowStyle BackColor="#2461BF" />
<FooterStyle BackColor="#507CD1" Font-Bold="True" ForeColor="White" />
<HeaderStyle BackColor="#507CD1" Font-Bold="True" ForeColor="White" />
<PagerStyle BackColor="#2461BF" ForeColor="White" HorizontalAlign="Center" />
<RowStyle BackColor="#EFF3FB" />
<SelectedRowStyle BackColor="#D1DDF1" Font-Bold="True" ForeColor="#333333" />
<SortedAscendingCellStyle BackColor="#F5F7FB" />
<SortedAscendingHeaderStyle BackColor="#6D95E1" />
<SortedDescendingCellStyle BackColor="#E9EBEF" />
<SortedDescendingHeaderStyle BackColor="#4870BE" />
</asp:GridView>
</td>
</tr>
<tr>
<td>
<asp:Button ID="Button1" Text="Click To Bulk
Insert" OnClick="Bulk_Insert" runat="server" /></td>
</tr>
</table>
<table>
<tr>
<td style="height: 5px;"></td>
</tr>
</table>
<table style="width: 100%; border: solid 2px red;">
<tr style="background-color: green; color: white; font-size: 15pt; font-weight: bold; height: 30px; text-align: center;">
<td>All Records From DATA BASE
</td>
</tr>
<tr>
<td>
<asp:GridView ID="GridViewAllDataFromDB" runat="server" AutoGenerateColumns="False" EmptyDataRowStyle-ForeColor="Red"
EmptyDataText="There is no
record." CellPadding="4" ForeColor="#333333" GridLines="None" Width="100%">
<AlternatingRowStyle BackColor="White" ForeColor="#284775" />
<Columns>
<asp:BoundField DataField="StudentID" HeaderText="Student ID" />
<asp:BoundField DataField="Name" HeaderText="Name" />
<asp:BoundField DataField="Email" HeaderText="Email" />
<asp:BoundField DataField="Class" HeaderText="Class" />
<asp:BoundField DataField="EnrollYear" HeaderText="EnrollYear" />
<asp:BoundField DataField="City" HeaderText="City" />
<asp:BoundField DataField="Country" HeaderText="Country" />
</Columns>
<EditRowStyle BackColor="#999999" />
<FooterStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />
<HeaderStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />
<PagerStyle BackColor="#284775" ForeColor="White" HorizontalAlign="Center" />
<RowStyle BackColor="#F7F6F3" ForeColor="#333333" />
<SelectedRowStyle BackColor="#E2DED6" Font-Bold="True" ForeColor="#333333" />
<SortedAscendingCellStyle BackColor="#E9E7E2" />
<SortedAscendingHeaderStyle BackColor="#506C8C" />
<SortedDescendingCellStyle BackColor="#FFFDF8" />
<SortedDescendingHeaderStyle BackColor="#6F8DAE" />
</asp:GridView>
</td>
</tr>
</table>
</div>
</form>
</body>
</html>
My aspx.cs code is:
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.IO;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
amespace SQLBulkInsert
{
public partial class ManageBulkCopy : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
if (!Page.IsPostBack)
{
GetAllXMLFiles();
GetAllRecordFromDB();
}
}
protected void UploadButton_Click(object sender, EventArgs e)
{
if (FileUploadControl.HasFile)
{
try
{
string FileName = Path.GetFileName(FileUploadControl.FileName);
string FileType = string.Empty;
//Saving File Physically.
Directory.CreateDirectory(Server.MapPath("~/XMLFiles/"));
FileUploadControl.SaveAs(Server.MapPath("~/XMLFiles/") + FileName);
StatusLabel.Text = "Upload status: File uploaded!";
GetAllXMLFiles();
}
catch (Exception ex)
{
StatusLabel.Text = "Upload status: The file could not be uploaded. The following error
occured: " + ex.Message;
}
}
}
public void GetAllXMLFiles()
{
string[] filePaths = Directory.GetFiles(Server.MapPath("~/XMLFiles/"));
List<ListItem> files = new List<ListItem>();
foreach (string filePath in filePaths)
{
files.Add(new ListItem(Path.GetFileName(filePath), filePath));
}
GridViewAllXMLFiles.DataSource =
files;
GridViewAllXMLFiles.DataBind();
}
protected void DownloadFile(object sender, EventArgs e)
{
string filePath = (sender as LinkButton).CommandArgument;
Response.ContentType = ContentType;
Response.AppendHeader("Content-Disposition", "attachment; filename=" + Path.GetFileName(filePath));
Response.WriteFile(filePath);
Response.End();
}
protected void DeleteFile(object sender, EventArgs e)
{
string filePath = (sender as LinkButton).CommandArgument;
File.Delete(filePath);
Response.Redirect(Request.Url.AbsoluteUri);
}
protected void ReadFile(object sender, EventArgs e)
{
string filePath = (sender as LinkButton).CommandArgument;
DataSet ds = new DataSet();
ds.ReadXml(filePath);
GridViewXMLRecords.DataSource =
ds.Tables[0];
GridViewXMLRecords.DataBind();
}
protected void Bulk_Insert(object sender, EventArgs e)
{
DataTable dt = new DataTable();
dt.Columns.AddRange(new DataColumn[6] {
new DataColumn("Name", typeof(string)),
new DataColumn("Email", typeof(string)),
new DataColumn("Class",typeof(string)),
new DataColumn("EnrollYear", typeof(string)),
new DataColumn("City", typeof(string)),
new DataColumn("Country",typeof(string))
});
foreach (GridViewRow row in GridViewXMLRecords.Rows)
{
if ((row.FindControl("CheckBox1") as CheckBox).Checked)
{
string name = row.Cells[1].Text;
string email = row.Cells[2].Text;
string S_class = row.Cells[3].Text;
string enrollYear = row.Cells[4].Text;
string city = row.Cells[5].Text;
string country = row.Cells[6].Text;
dt.Rows.Add(name, email,
S_class, enrollYear, city, country);
}
}
if (dt.Rows.Count > 0)
{
string consString = @"Data Source=MyPC\SqlServer2k8; Initial
Catalog=SchoolManagement; Integrated Security=true;";
using (SqlConnection con = new SqlConnection(consString))
{
using (SqlBulkCopy sqlBulkCopy = new SqlBulkCopy(con)) {
sqlBulkCopy.DestinationTableName = "dbo.Student";
sqlBulkCopy.ColumnMappings.Add("Name", "Name");
sqlBulkCopy.ColumnMappings.Add("Email", "Email");
sqlBulkCopy.ColumnMappings.Add("Class", "Class");
sqlBulkCopy.ColumnMappings.Add("EnrollYear", "EnrollYear");
sqlBulkCopy.ColumnMappings.Add("City", "City");
sqlBulkCopy.ColumnMappings.Add("Country", "Country");
con.Open();
sqlBulkCopy.WriteToServer(dt);
con.Close();
}
}
}
GetAllRecordFromDB();
}
SqlDataAdapter da;
DataSet ds = new DataSet();
public void GetAllRecordFromDB()
{
SqlConnection con = new SqlConnection();
con.ConnectionString = @"Data Source=MyPC\SqlServer2k8; Initial Catalog=SchoolManagement;
Integrated Security=true;";
SqlCommand cmd = new SqlCommand("SELECT * FROM Student ORDER BY
StudentID DESC", con);
da = new SqlDataAdapter(cmd);
da.Fill(ds);
con.Open();
cmd.ExecuteNonQuery();
con.Close();
if (!object.Equals(ds.Tables[0], null))
{
if (ds.Tables[0].Rows.Count > 0)
{
GridViewAllDataFromDB.DataSource = ds;
GridViewAllDataFromDB.DataBind();
}
else
{
GridViewAllDataFromDB.DataSource = null;
GridViewAllDataFromDB.DataBind();
}
}
}
}
}
Now run the application:
Image 4.
Upload XML file and see all uploaded xml files.
Image 5.
Image 6.
From here you can read your any uploaded XML file. Here
select data row and click on Bulk Insert button.
Image 7.
Now see Data Table.
Image 8.