In this article we discuss how to use SQL Bulk Copy in ASP.Net MVC C#. In this example we see how a multiple list of data is push into SQL Server database using SQL Bulkcopy.
The Student table with the script are following like below.
CREATE TABLE [dbo].[StudentInfo](
[Id] [int] IDENTITY(1,1) NOT NULL,
[Name] [varchar](50) NULL,
[Phone] [varchar](50) NULL,
[Address] [varchar](50) NULL,
[Class] [varchar](50) NULL,
CONSTRAINT [PK_StudentInfo] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GOThe Student XML data that we want to push into database using SQL Bulk copy.
<?xml version="1.0" encoding="utf-8" ?>
<data>
<Student Id="1">
<Name>Ajit Mishra</Name>
<Phone>9999999999</Phone>
<Address>Delhi</Address>
<Class>MCA</Class>
</Student>
<Student Id="2">
<Name>Aakash Mishra</Name>
<Phone>9123456789</Phone>
<Address>Delhi</Address>
<Class>BTech</Class>
</Student>
<Student Id="3">
<Name>Sachin Sen</Name>
<Phone>9123499999</Phone>
<Address>Mumbai</Address>
<Class>Bsc</Class>
</Student>
<Student Id="4">
<Name>James Wisdom</Name>
<Phone>9123499007</Phone>
<Address>London</Address>
<Class>Ms</Class>
</Student>
</data>
Create a ASP.Net MVC Project
File -> New Project -> Create ASP.NET Web Application then choose like below template
Then create a Controller and view page with following Code.
Controller
public class BulkInsertController : Controller
{
// GET: BulkInsert
public ActionResult Index()
{
return View();
}
[HttpPost]
[ValidateAntiForgeryToken]
public ActionResult Index(string x) {
try
{
string cs = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
using (SqlConnection sqlConn = new SqlConnection(cs))
{
DataSet ds = new DataSet();
ds.ReadXml(Server.MapPath("~/StudentFile.xml"));
DataTable dtStudentMaster = ds.Tables["Student"];
sqlConn.Open();
using (SqlBulkCopy sqlbc = new SqlBulkCopy(sqlConn))
{
sqlbc.DestinationTableName = "StudentInfo";
sqlbc.ColumnMappings.Add("Name", "Name");
sqlbc.ColumnMappings.Add("Phone", "Phone");
sqlbc.ColumnMappings.Add("Address", "Address");
sqlbc.ColumnMappings.Add("Class", "Class");
sqlbc.WriteToServer(dtStudentMaster);
Response.Write("Bulk data stored successfully");
}
}
}
catch (Exception ex)
{
throw ex;
}
return View();
}
}Action View page
@{
ViewBag.Title = "Index";
}
<html lang="en">
<head>
<title>SQL Bulk Copy in ASP .NET MVC</title>
<meta charset="utf-8">
<meta name="viewport" content="width=device-width, initial-scale=1">
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.4.1/css/bootstrap.min.css">
<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.5.1/jquery.min.js"></script>
<script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.4.1/js/bootstrap.min.js"></script>
</head>
<body>
<div class="container">
<h2>SQL Bulk Copy in ASP .NET MVC C#</h2>
@using (Html.BeginForm("Index", "BulkInsert", FormMethod.Post, new { @class = "form-horizontal", role = "form", enctype = "multipart/form-data" }))
{
@Html.AntiForgeryToken()
<div class="panel panel-default">
<div class="panel-heading">Panel Heading</div>
<div class="panel-body">
<input type="submit" name="Push Bulk Data" class="btn btn-info" />
</div>
</div>
}
</div>
</body>
</html>Code Explanation
DataSet ds = new DataSet();
ds.ReadXml(Server.MapPath("~/StudentFile.xml"));
DataTable dtStudentMaster = ds.Tables["Student"];- The above code is responsible for to read the XML file and convert it into datatable.
using (SqlBulkCopy sqlbc = new SqlBulkCopy(sqlConn))
{
sqlbc.DestinationTableName = "StudentInfo";
sqlbc.ColumnMappings.Add("Name", "Name");
sqlbc.ColumnMappings.Add("Phone", "Phone");
sqlbc.ColumnMappings.Add("Address", "Address");
sqlbc.ColumnMappings.Add("Class", "Class");
sqlbc.WriteToServer(dtStudentMaster);
Response.Write("Bulk data stored successfully");
}- The above code is the SQL bulk copy data that target to the table StudentInfo and according to the table filed it match with the data table column then sqlbc.WriteToServer(dtStudentMaster) is write all the data at a time into SQL server.
@using (Html.BeginForm("Index", "BulkInsert", FormMethod.Post, new { @class = "form-horizontal", role = "form", enctype = "multipart/form-data" }))
{
@Html.AntiForgeryToken()
<div class="panel panel-default">
<div class="panel-heading">Panel Heading</div>
<div class="panel-body">
<input type="submit" name="Push Bulk Data" class="btn btn-info" />
</div>
</div>
}- @using (Html.BeginForm("Index", "BulkInsert" is target to controller BulkInsert and action Index.
- When the submit button is hit it call the controller action method and push the data into database using SQL Bulk copy.
Final output
The final output should like below;
</> Find the Source Code in Github
Post a Comment