Header add

 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]

GO

The 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



Create a New XML file in the project let's say name as "StudentFile.xml" and add the above XML into it.




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

Previous Post Next Post