Insert Bulk Records Into DataBase Using ASP.NET C#

Vithal Wadje
7y
197.2k
0
4

Article

Background

Sometimes there is a need to insert bulk records into a database from a Web Form. Suppose there is the requirement of a retail store to add multiple products into a product catalog and then insert these multiple products into the database.

So let us start creating an application so beginners can also understand.

First create the table named ProductsSold using the following script:

CREATE TABLE [dbo].[ProdcutsSold](
[ProductId] [int] IDENTITY(1,1) NOT NULL,
[ProductName] [varchar](50) NULL,
[BrandName] [varchar](50) NULL,
[Warrenty] [int] NULL,
[Price] [numeric](18, 2) NULL,
CONSTRAINT [PK_ProdcutsSold] PRIMARY KEY CLUSTERED
(
[ProductId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

Then the design view of the table will look such as follows:

"Start" - "All Programs" - "Microsoft Visual Studio 2010".
"File" - "New WebSite" - "C#" - "Empty WebSite" (to avoid adding a master page).
Provide the web site a name such as "InsertbulkRecordsIntoDataBase" or another as you wish and specify the location.
Then right-click on Solution Explorer - "Add New Item" - Add Web Form.
Drag and drop one GridView, two buttons and four textBoxes onto the <form> section of the Default.aspx page.

Now the default.aspx Page source code will look such as follows.

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="_Default" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title></title>
</head>
<body style="background-color: #0000FF">
<form id="form1" runat="server">
<table style="color:White;" >
<tr>
<td>
Product Name
</td>
<td>
Brand Name
</td>
<td>
Warrenty
</td>
<td>
Price
</td>
</tr>
<tr>
<td>
<asp:TextBox ID="TextBox1" runat="server"></asp:TextBox>
</td>
<td>
<asp:TextBox ID="TextBox2" runat="server"></asp:TextBox>
</td>
<td>
<asp:TextBox ID="TextBox3" runat="server"></asp:TextBox>
</td>
<td>
<asp:TextBox ID="TextBox4" runat="server"></asp:TextBox>
</td>
</tr>
<tr>
<td></td>
<td></td>
<td>
</td>
<td>
<asp:Button ID="AddProduct" runat="server" style="color:White" Text="Add Product"
onclick="AddProduct_Click" BackColor="#999966" /></td>
</tr>
</table>
<div style="margin-top:20px;margin-left:10px;">
<asp:GridView ID="GridView1" AutoGenerateColumns="false" runat="server" CellPadding="4" ForeColor="#333333"
GridLines="None">
<AlternatingRowStyle BackColor="White" />
<Columns>
<asp:BoundField HeaderStyle-Width="120px" HeaderText="Product Name" DataField="ProductName"/>
<asp:BoundField HeaderStyle-Width="120px" HeaderText="Brand Name" DataField="BrandName"/>
<asp:BoundField HeaderStyle-Width="120px" HeaderText="Warrenty" DataField="Warrenty"/>
<asp:BoundField HeaderStyle-Width="120px" HeaderText="Price" DataField="Price"/>
</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>
</div>
<div style="margin-top:10px;margin-left:350px">
<asp:Button ID="btnsubmitProducts" runat="server" style="color:White"
Text="Save Products" BackColor="#999966" onclick="btnsubmitProducts_Click" />
</div>
</form>
</body>
</html>

Now switch to design mode and it will look as follows,

Now switch to the default.aspx.cs code behind file and write the following code to create and save the datatable into viewstate and bind the GridView as,

private void AddDefaultFirstRecord()
{
//creating DataTable
DataTable dt = new DataTable();
DataRow dr;
dt.TableName = "ProductsSold";
//creating columns for DataTable
dt.Columns.Add(new DataColumn("ProductName", typeof(string)));
dt.Columns.Add(new DataColumn("BrandName", typeof(string)));
dt.Columns.Add(new DataColumn("Warrenty", typeof(int)));
dt.Columns.Add(new DataColumn("Price", typeof(double)));
dr = dt.NewRow();
dt.Rows.Add(dr);
ViewState["ProductsSold"] = dt;
GridView1.DataSource = dt;
GridView1.DataBind();
}

Now call the function above at Page Load so that the initial records will be added into the view state and Grid View as,

protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
AddDefaultFirstRecord();
}
}

Now create a function with the following code that will save the records into the data table using view state and will be available to be bound to the Grid View as,

private void AddNewRecordRowToGrid()
{
if (ViewState["ProductsSold"] != null)
{
DataTable dtCurrentTable = (DataTable)ViewState["ProductsSold"];
DataRow drCurrentRow = null;
if (dtCurrentTable.Rows.Count > 0)
{
for (int i = 1; i <= dtCurrentTable.Rows.Count; i++)
{
//Creating new row and assigning values
drCurrentRow = dtCurrentTable.NewRow();
drCurrentRow["ProductName"] = TextBox1.Text;
drCurrentRow["BrandName"] = TextBox2.Text;
drCurrentRow["Warrenty"] =Convert.ToInt32( TextBox3.Text);
drCurrentRow["Price"] = Convert.ToDouble(TextBox4.Text);
}
//Removing initial blank row
if (dtCurrentTable.Rows[0][0].ToString() == "")
{
dtCurrentTable.Rows[0].Delete();
dtCurrentTable.AcceptChanges();
}
//Added New Record to the DataTable
dtCurrentTable.Rows.Add(drCurrentRow);
//storing DataTable to ViewState
ViewState["ProductsSold"] = dtCurrentTable;
//binding Gridview with New Row
GridView1.DataSource = dtCurrentTable;
GridView1.DataBind();
}
}
}

Call the preceding function from the Add Product button click as,

protected void AddProduct_Click(object sender, EventArgs e)
{
AddNewRecordRowToGrid();
}

Add the database connection string details to the web.config file as,

<connectionStrings>
<add name ="dbconn" connectionString ="Data Source=VITHAL;Initial Catalog=C#corner;User Id=sa;word=swift" providerName ="System.Data.Sqlclient"/>
</connectionStrings>

Now create a function for the connection as,

public void connection()
{
//Stoting connection string
constr = ConfigurationManager.ConnectionStrings["dbconn"].ConnectionString;
con = new SqlConnection(constr);
con.Open();
}

Now create the function to save the bulk records into the database using SqlBulkCopy as,

private void BulkInsertToDataBase()
{
DataTable dtProductSold = (DataTable)ViewState["ProductsSold"];
connection();
//creating object of SqlBulkCopy
SqlBulkCopy objbulk = new SqlBulkCopy(con);
//assigning Destination table name
objbulk.DestinationTableName = "ProdcutsSold";
//Mapping Table column
objbulk.ColumnMappings.Add("ProductName", "ProductName");
objbulk.ColumnMappings.Add("BrandName", "BrandName");
objbulk.ColumnMappings.Add("Warrenty", "Warrenty");
objbulk.ColumnMappings.Add("Price", "Price");
//inserting bulk Records into DataBase
objbulk.WriteToServer(dtProductSold);
}

Call the preceding function on the save product button click as,

protected void btnsubmitProducts_Click(object sender, EventArgs e)
{
BulkInsertToDataBase();
}

Now the entire code of the default.aspx.cs file will look such as follows,

using System;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
public partial class _Default : System.Web.UI.Page
{
public string constr;
public SqlConnection con;
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
AddDefaultFirstRecord();
}
}
public void connection()
{
//Stoting connection string
constr = ConfigurationManager.ConnectionStrings["dbconn"].ConnectionString;
con = new SqlConnection(constr);
con.Open();
}
protected void AddProduct_Click(object sender, EventArgs e)
{
AddNewRecordRowToGrid();
}
private void AddDefaultFirstRecord()
{
//creating DataTable
DataTable dt = new DataTable();
DataRow dr;
dt.TableName = "ProductsSold";
//creating columns for DataTable
dt.Columns.Add(new DataColumn("ProductName", typeof(string)));
dt.Columns.Add(new DataColumn("BrandName", typeof(string)));
dt.Columns.Add(new DataColumn("Warrenty", typeof(int)));
dt.Columns.Add(new DataColumn("Price", typeof(double)));
dr = dt.NewRow();
dt.Rows.Add(dr);
ViewState["ProductsSold"] = dt;
GridView1.DataSource = dt;
GridView1.DataBind();
}
private void AddNewRecordRowToGrid()
{
if (ViewState["ProductsSold"] != null)
{
DataTable dtCurrentTable = (DataTable)ViewState["ProductsSold"];
DataRow drCurrentRow = null;
if (dtCurrentTable.Rows.Count > 0)
{
for (int i = 1; i <= dtCurrentTable.Rows.Count; i++)
{
//Creating new row and assigning values
drCurrentRow = dtCurrentTable.NewRow();
drCurrentRow["ProductName"] = TextBox1.Text;
drCurrentRow["BrandName"] = TextBox2.Text;
drCurrentRow["Warrenty"] = Convert.ToInt32(TextBox3.Text);
drCurrentRow["Price"] = Convert.ToDouble(TextBox4.Text);
}
//Removing initial blank row
if (dtCurrentTable.Rows[0][0].ToString() == "")
{
dtCurrentTable.Rows[0].Delete();
dtCurrentTable.AcceptChanges();
}
//Added New Record to the DataTable
dtCurrentTable.Rows.Add(drCurrentRow);
//storing DataTable to ViewState
ViewState["ProductsSold"] = dtCurrentTable;
//binding Gridview with New Row
GridView1.DataSource = dtCurrentTable;
GridView1.DataBind();
}
}
}
protected void btnsubmitProducts_Click(object sender, EventArgs e)
{
BulkInsertToDataBase();
}
private void BulkInsertToDataBase()
{
DataTable dtProductSold = (DataTable)ViewState["ProductsSold"];
connection();
//creating object of SqlBulkCopy
SqlBulkCopy objbulk = new SqlBulkCopy(con);
//assigning Destination table name
objbulk.DestinationTableName = "ProdcutsSold";
//Mapping Table column
objbulk.ColumnMappings.Add("ProductName", "ProductName");
objbulk.ColumnMappings.Add("BrandName", "BrandName");
objbulk.ColumnMappings.Add("Warrenty", "Warrenty");
objbulk.ColumnMappings.Add("Price", "Price");
//inserting bulk Records into DataBase
objbulk.WriteToServer(dtProductSold);
}
}

Now run the application. The page will look such as follows,

Now add some details into the preceding textboxes and click on the Add Product button. The records that are saved into the DataTable will then be displayed in the GridView as,

Now add another Product detail, it will be added into the existing one as,

As in the preceding, you can add n number of records as the data table capacity, now click on the save Products Details button and the records will be saved into the database as follows,

Note

For detailed code please download the sample Zip file.
Do proper validation such as date input values when implementing.
Make the changes in the web.config file depending on your server details for the connection string

Summary

From all the above examples we have learned how to save Bulk Records into a database. I hope this article is useful for all readers, if you have a suggestion then please contact me.

Up Next

Ebook Download

View all

Programming ASP.NET MVC 5

Read by 122.1k people

Download Now!

Learn

View all