While developing applications, we may come across the situations where we need to insert/update multiple records in a database table at the same time. Inserting (or updating) records one by one isn't that much of good idea. In such a case, we can send the data in XML format and use some built-in functions and stored procedures in SQL server to save the records. Let’s create a sample web application demonstrating this using VS 2015 Express edition for Web.
Suppose there is a zoo and one of the staff members at the entry gate is required to make entries of visitors by providing their basic information (first name, last name,
mobile number, age and ticket amount). User can save details of multiple visitors (one row for each visitor) if required; on single click of 'Save' button.
- Open Visual Studio and create a new empty MVC application.
(File → New Project... → Select “Installed” >> “Templates” >> “Visual C#” >> “Web” in the left pane in the opened window → Enter name for your web application(“InsertMultipleRecordsUsingXMLDemo”) → Click “OK” to navigate to next window → Select “Empty” as a template type with check marked checkbox for "MVC" → Click "OK")
- You can find a folder named "scripts" under the root folder of the application. Delete the JS files (if any) that are already present in it.
Download the jQuery library from-
https://ajax.googleapis.com/ajax/libs/jquery/3.2.1/jquery.min.js
Get Bootstrap's CSS and JavaScript from-
https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.min.css
and
https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/js/bootstrap.min.js
As you can see, I am using v3.2.1 of jQuery and v3.3.7 of Bootstrap in this project.
[You can also get these JS and CSS files from http://jquery.com/ (for jQuery) and http://getbootstrap.com/ (for Bootstrap).]
Copy both the JS files (jQuery and Bootstrap) into "scripts" folder.
Create a new folder with name "Content" under the root folder of the application and copy Bootstrap's CSS file into it.
Add a new stylesheet file named "Style.css" in the same folder. Replace the original content of this file with the one given below-
.bottom-pad-10 {
padding-bottom: 10px;
}
.top-pad-10 {
padding-top: 10px;
}
#title {
padding: 5px;
font-weight: bold;
font-size: 30px;
color: #809fff;
background-color: #eeeeee;
border-radius: 4px;
}
#dvInsertInfo th {
text-align: center;
color: white;
background-color: #4CAF50;
}
#dvInsertInfo tbody tr {
text-align: center;
padding-top: 5px;
padding-bottom: 5px;
}
#dvInsertInfo tbody tr:nth-child(odd) {
background-color: #eaeffa;
}
- Inside "Views" folder, create a new folder named "Visitor". Add a new view with an empty template and without using any layout page in this newly created "Visitor" folder and name it as "InsertVisitors".
Put the content given below in this view to replace its default content.(Make sure that names and paths of CSS and JS files included in the <head> tag are correct.)-
@{
Layout = null;
}
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="utf-8" />
<meta name="viewport" content="width=device-width, initial-scale=1" />
<title>Visitor Register</title>
<link href="~/Content/bootstrap-3.3.7.min.css" rel="stylesheet" />
<link href="~/Content/Style.css" rel="stylesheet" />
<script src="~/scripts/jquery-3.2.1.min.js"></script>
<script src="~/scripts/bootstrap-3.3.7.min.js"></script>
</head>
<body>
<div class="container" id="dvContainer">
<div class="text-center top-pad-10">
<span id="title">Visitor Register</span>
</div>
<div class="table-responsive top-pad-10 bottom-pad-10" id="dvInsertInfo">
<table class="table">
<thead>
<tr>
<th>First Name</th>
<th>Last Name</th>
<th>Mobile No.</th>
<th>Age</th>
<th>Amount</th>
</tr>
</thead>
<tbody>
<tr>
<td>
<input type="text" class="form-control info-field" />
</td>
<td>
<input type="text" class="form-control info-field" />
</td>
<td>
<input type="text" class="form-control info-field" />
</td>
<td>
<input type="text" class="form-control info-field" />
</td>
<td>
<input type="text" class="form-control info-field" />
</td>
</tr>
</tbody>
</table>
</div>
<div class="top-pad-10 bottom-pad-10" id="dvAddDeleteRowButtons">
<button type="button" id="btnAddRow" class="btn btn-primary"
onclick="addNewRow()">Add New Row</button>
<button type="button" id="btnDeleteRow" class="btn btn-warning disabled"
onclick="deleteRow()">Delete Last Row</button>
</div>
<div class="top-pad-10 text-center">
<button type="button" id="btnSave" class="btn btn-success"onclick="saveAllVisitorInfo()">Save All</button>
<button type="button" id="btnClear" class="btn btn-primary"
onclick="clearAllVisitorInfo()">Clear All</button>
</div>
</div>
<script>
function addNewRow() {
var lastRow = $("#dvInsertInfo tbody tr").last();
var newRow = lastRow.clone();
newRow.find(".info-field").val("");
lastRow.after(newRow);
$("#btnDeleteRow").removeClass("disabled");
}
function deleteRow() {
var allRows = $("#dvInsertInfo").find("tbody tr");
var countRows = allRows.length;
if (countRows > 1) {
allRows.last().remove();
}
if (countRows == 2) {
$("#btnDeleteRow").addClass("disabled");
}
}
function saveAllVisitorInfo() {
var propNames = ["FirstName", "LastName", "MobileNo", "Age",
"TicketAmount"];
var arrVisitor = [];
$.each($("#dvInsertInfo tbody tr"), function () {
var visitor = {};
$.each($(this).find(".info-field"), function (index) {
currentValue = $.trim($(this).val());
visitor[propNames[index]] = currentValue;
});
arrVisitor.push(visitor);
});
$.ajax({
url: "@Url.Action("SaveVisitors", "Visitor")",
method: "POST",
data: { lstVisitor: arrVisitor },
success: function (response) {
if (response == 1) {
alert("Record(s) inserted successfully!");
if ($("#dvInsertInfo tbody tr").length > 1) {
$("#dvInsertInfo tbody tr").slice(1).remove();
}
clearAllVisitorInfo();
$("#btnDeleteRow").addClass("disabled");
}
else {
alert("Some error has occurred while saving the records!");
}
},
error: function (xhrObj, strStatus, strError) {
alert(strError);
}
});
}
function clearAllVisitorInfo() {
$(".info-field").val("");
}
</script>
</body>
</html>
- We need to create a model class that will map information related to one entity (one visitor) to corresponding row in the database table. For that, add a class ("Visitor.cs") in the "Models" folder.
Delete the auto-generated code and put the one given below-
using System;
namespace InsertMultipleRecordsUsingXMLDemo.Models
{
public class Visitor
{
public string FirstName { get; set; }
public string LastName { get; set; }
public int MobileNo { get; set; }
public int Age { get; set; }
public int TicketAmount { get; set; }
}
}
Create a new empty controller with name "VisitorController" in "Controllers" folder. Replace its content with this one-
using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Text;
using System.Web.Mvc;
using System.Xml;
using InsertMultipleRecordsUsingXMLDemo.Models;
namespace InsertMultipleRecordsUsingXMLDemo.Controllers
{
public class VisitorController : Controller
{
public ActionResult InsertVisitors()
{
return View();
}
[HttpPost]
public int SaveVisitors(List<Visitor> lstVisitor)
{
int result = 0;
StringBuilder lSb = new StringBuilder();
XmlWriterSettings lXmlSettings = new XmlWriterSettings { OmitXmlDeclaration = true, ConformanceLevel = ConformanceLevel.Fragment };
XmlWriter lXmlWriter = XmlWriter.Create(lSb, lXmlSettings);
lXmlWriter.WriteStartElement("rows");
foreach (var visitor in lstVisitor)
{
lXmlWriter.WriteStartElement("row");
lXmlWriter.WriteElementString("FirstName", visitor.FirstName);
lXmlWriter.WriteElementString("LastName", visitor.LastName);
lXmlWriter.WriteElementString("MobileNo", Convert.ToString(visitor.MobileNo));
lXmlWriter.WriteElementString("Age", Convert.ToString(visitor.Age));
lXmlWriter.WriteElementString("TicketAmount", Convert.ToString(visitor.TicketAmount));
lXmlWriter.WriteEndElement();
}
lXmlWriter.WriteEndElement();
lXmlWriter.Flush();
lXmlWriter.Close();
string sqlConString = ConfigurationManager.ConnectionStrings["ConnString"].ConnectionString;
try
{
using (SqlConnection con = new SqlConnection(sqlConString))
{
using (SqlCommand cmd = new SqlCommand("InsertVisitors_prc",con))
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@MultipleRecordsData",
lSb.ToString());
con.Open();
cmd.ExecuteNonQuery();
con.Close();
}
}
result = 1;
}
catch (Exception ex)
{
System.Diagnostics.Trace.WriteLine(ex.Message); //You can see the exception message in Output window (Go to "View" in menu bar and select "Output").
result = 0;
}
return result;
}
}
}
If you see, there is a statement-
string sqlConString = ConfigurationManager.ConnectionStrings["ConnString"].ConnectionString;
For this, you need to have <connectionStrings> tag as a child node of <configuration> in the application's main Web.config file (This file is present directly under the root folder).
The file should look something like this-
<?xml version="1.0" encoding="utf-8"?>
<configuration>
<connectionStrings>
<add name="ConnString" connectionString="Data Source=SERVER_NAME; InitialCatalog=DATABASE_NAME; User ID=YOUR_USER_ID; Password=YOUR_PASSWORD;"
providerName="System.Data.SqlClient" />
</connectionStrings>
<!-- Other part has been removed for brevity. -->
</configuration>
- Create a table named "Visitor" in database to save the records.
USE [DATABASE_NAME] --Put your database name here.
GO
CREATE TABLE Visitor
( Id INT IDENTITY(1,1) PRIMARY KEY,
[FirstName] [VARCHAR](50) NULL,
[LastName] [VARCHAR](50) NULL,
[MobileNo] [VARCHAR](20) NULL,
[Age] [SMALLINT] NULL,
[TicketAmount] [SMALLINT] NULL )
GO
Now create the stored procedure "InsertVisitors_prc" that is the heart of this blog.
CREATE PROCEDURE [dbo].[InsertVisitors_prc]
@MultipleRecordsData VARCHAR(MAX)
AS
BEGIN
DECLARE @idoc INT
EXEC sp_xml_preparedocument @idoc OUTPUT, @MultipleRecordsData
SELECT * INTO #Data FROM
OPENXML(@idoc, '/rows/row', 2) WITH
(
[FirstName] VARCHAR(50),
[LastName] VARCHAR(50),
[MobileNo] VARCHAR(20),
[Age] SMALLINT,
[TicketAmount] SMALLINT
)
EXEC sp_xml_removedocument @idoc
INSERT INTO Visitor ([FirstName], [LastName], [MobileNo], [Age], [TicketAmount])
SELECT [FirstName], [LastName], [MobileNo], [Age], [TicketAmount] FROM #Data
END
GO
The operations that we perform in this stored procedure are-
a) Create a tree representation of nodes from input XML text into internal cache of SQL Server using system-defined stored procedure "sp_xml_preparedocument".
b) Get rowset from it using element-centric mapping (Note that 3rd argument for OPENXML is '2' that represents element-centric mapping) into a temporary table[#Data].
c) Free up the memory used by the rowset in step b) (We don't need it because now we have the data in the temporary table.) using another system-defined stored procedure "sp_xml_removedocument".
d) Insert the records from [#Data] into table [Visitor].
Now build and then run the application. Before running it, just make sure that you have set default controller and action names in "RouteConfig.cs" file properly as given below-
(You can find the file in the folder "App_Start" that is under the root folder of the application.)
using System.Web.Mvc;
using System.Web.Routing;
namespace InsertMultipleRecordsUsingXMLDemo
{
public class RouteConfig
{
public static void RegisterRoutes(RouteCollection routes)
{
routes.IgnoreRoute("{resource}.axd/{*pathInfo}");
routes.MapRoute(
name: "Default",
url: "{controller}/{action}/{id}",
defaults: new { controller = "Visitor", action = "InsertVisitors", id = UrlParameter.Optional }
);
}
}
}
On 'Visitor Register' page, you can add information about more than one visitors by creating new row(s) for each of them by clicking 'Add New Row' button.
After clicking on 'Save All' button, you can check for the records inserted in the database by executing following query in SQL server-
SELECT * FROM Visitor
(You may have observed that I haven't put any attention to validating user inputs, simply because the purpose of the blog is "how" to insert multiple recordssimultaneously rather than considering "what" type of data is being inserted. But in real world, there must be validations for the content before insertion.)
If you want to dive deep into the code, please read more on the web about different classes like XmlWriter() and XmlWriterSettings() that are present in System.Xml namespaces. Also, it is worth to have a look at sp_xml_preparedocument, OPENXML and sp_xml_removedocument.
As a further study, you also can read about MERGE statement in SQL server. It can be used in "update if record is already present and insert if it is not" situations.
