Inserting single or bulk record(s) in Database using asp.net C# is not a big deal but considering performance with the operation is a topic to think on. Less db trips and including use of client side coding would really increase performance of the application.
Let us consider one simple example for bulk insert. We will insert Id, Name, Age of students in bulk in DB using javascript code and server side code as well. We have taken one button “Add new record” which will create html elements (Its widely known that using html controls rather than server controls will improve the performance of the web page) using javascript and then we will be able to insert all those records at once by clicking “Save” button and passing all data in form of xml to store procedure for inserting. Xml format would be like-
<Persons> <Person> <Id></Id> <Name></Name> <Age></Age> </Person> </Persons>
Below is aspx page code-
<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="Registration.aspx.cs" Inherits="InsertionWithPerformance.Registration" ValidateRequest="false" %>
<!DOCTYPE html>
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title>Registration</title>
<style>
.HideContent {
display: none;
}
.BlockDiv {
margin: 10px;
}
table {
border-collapse: collapse;
}
td {
padding: 6px;
}
</style>
<script type="text/javascript">
function BindAge(id) {
var i;
var ddlAge = document.getElementById(id);
for (i = 17; i <= 30; i++) {
var optn = document.createElement("OPTION");
optn.text = i;
optn.value = i;
ddlAge.options.add(optn);
}
}
function AddRow() {
var table = document.getElementById("<%=register.ClientID%>");
var rowCount = table.rows.length;
var row = table.insertRow(rowCount);
var cell1 = row.insertCell(0);
var element1 = document.createElement("input");
element1.type = "text";
element1.id = "ID" + rowCount;
cell1.appendChild(element1);
var cell2 = row.insertCell(1);
var element2 = document.createElement("input");
element2.type = "text";
element2.id = "Name" + rowCount;
cell2.appendChild(element2);
var cell3 = row.insertCell(2);
var element3 = document.createElement("select");
element3.id = "Age" + rowCount;
cell3.appendChild(element3);
BindAge(element3.id);
return false;
}
function MakeString() {
var table = document.getElementById("<%=register.ClientID%>");
var rowCount = table.rows.length;
var StringData = "";
if (table.rows.length > 1) {
StringData = "<Persons>";
for (var i = 1; i <= rowCount - 1; i++) {
if (table.rows[i].children[0].children[0].value.trim() != "") {
StringData += "<Person>";
StringData += "<Id>" + table.rows[i].children[0].children[0].value + "</Id>";
StringData += "<Name>" + table.rows[i].children[1].children[0].value + "</Name>";
StringData += "<Age>" + table.rows[i].children[2].children[0].value + "</Age>";
StringData += "</Person>";
}
}
StringData += "</Persons>";
}
document.getElementById("<%= tempdata.ClientID %>").value = StringData;
}
</script>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:TextBox ID="tempdata" runat="server" TextMode="MultiLine" CssClass="HideContent"></asp:TextBox>
<asp:Button ID="btnAddnew" runat="server" OnClientClick="javascript:return AddRow();" Text="Add new Record" />
<div id="registration" class="BlockDiv" runat="server">
<table id="register" runat="server" border="1">
<tr id="Tr1" runat="server">
<td>Id</td>
<td>Name</td>
<td>Age</td>
</tr>
<tr>
<td>
<input type="text" id="ID1" /></td>
<td>
<input type="text" id="Name1" /></td>
<td>
<select id="Age1"></select></td>
</tr>
</table>
</div>
<asp:Button ID="btnSave" runat="server" OnClientClick="javascript:MakeString();" Text="Save" OnClick="btnSave_Click" />
</div>
<div class="BlockDiv">
<asp:GridView ID="gvRecords" runat="server"></asp:GridView>
</div>
</form>
<script>
BindAge('Age1');
</script>
</body>
</html>
Below is aspx.cs code-
protected void btnSave_Click(object sender, EventArgs e)
{
try
{
DataSet Ds = new DataSet();
if (!string.IsNullOrEmpty(tempdata.Text))
{
SqlXml RegistrationXml = new System.Data.SqlTypes.SqlXml(new XmlTextReader(tempdata.Text.Replace("&", "&"), XmlNodeType.Document, null));
strConnection = ConfigurationManager.ConnectionStrings["ConnectionString"].ToString();
SqlConnection SqlConn = new SqlConnection(strConnection);
SqlCommand cmdToExecute = new SqlCommand();
cmdToExecute.CommandText = "dbo.InsertRegistrationDetails";
cmdToExecute.CommandType = CommandType.StoredProcedure;
cmdToExecute.Connection = SqlConn;
cmdToExecute.Parameters.Add(new SqlParameter("@RegistrationXml", SqlDbType.Xml, 8000, ParameterDirection.Input, true, 18, 2, "", DataRowVersion.Proposed, RegistrationXml));
SqlConn.Open();
cmdToExecute.ExecuteNonQuery();
SqlConn.Close();
ScriptManager.RegisterClientScriptBlock(Page, GetType(), "SaveStudentsSuccess", "alert('Record(s) saved successfully!');", true);
BindGrid();
}
}
catch (Exception ex)
{
throw ex;
}
}
private void BindGrid()
{
try
{
DataTable dt = new DataTable();
strConnection = ConfigurationManager.ConnectionStrings["ConnectionString"].ToString();
SqlConnection SqlConn = new SqlConnection(strConnection);
SqlConn.Open();
string sqlStatement = "SELECT * FROM Student";
SqlCommand cmd = new SqlCommand(sqlStatement, SqlConn);
SqlDataAdapter sqlDa = new SqlDataAdapter(cmd);
sqlDa.Fill(dt);
if (dt.Rows.Count > 0)
{
gvRecords.DataSource = dt;
gvRecords.DataBind();
}
}
catch (Exception ex)
{
throw ex;
}
}
Below is Store Procedure-
CREATE PROCEDURE [dbo].[InsertRegistrationDetails]
@RegistrationXml XML
AS
BEGIN
SET NOCOUNT ON;
INSERT INTO Student
(
[ID],
[Name],
[Age]
)
SELECT
Stud.StudDetails.value('Id[1]','int'),
Stud.StudDetails.value('Name[1]','varchar(50)'),
Stud.StudDetails.value('Age[1]','int')
FROM
@RegistrationXml.nodes('/Persons/Person') AS Stud(StudDetails)
END
Please consider that this is just a basic example for inserting records in bulk with considering performance of the application. It would be really beneficial for real world application enviornment. Source code is attached here, which contains application solution and DB script.
Download Source code: Inserting bulk records using JavaScript, asp.net
Uchit….Nice article. Thanks.. Special for Javascript code. It is very helpful….