Post
Ask Question

Bulk Insert From ASP.NET Web Form Using C#

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's start creating an application so beginners can also understand.

First create the table named ProductsSold using the following script:

  1. CREATE TABLE [dbo].[ProdcutsSold](    
  2.     [ProductId] [int] IDENTITY(1,1) NOT NULL,    
  3.     [ProductName] [varchar](50) NULL,    
  4.     [BrandName] [varchar](50) NULL,    
  5.     [Warrenty] [int] NULL,    
  6.     [Price] [numeric](18, 2) NULL,    
  7.  CONSTRAINT [PK_ProdcutsSold] PRIMARY KEY CLUSTERED     
  8. (    
  9.     [ProductId] ASC    
  10. )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]    
  11. ) ON [PRIMARY]   
Then the design view of the table will look such as follows:
 
 
  1. "Start" - "All Programs" - "Microsoft Visual Studio 2010".
  2. "File" - "New WebSite" - "C#" - "Empty WebSite" (to avoid adding a master page).
  3. Provide the web site a name such as "InsertbulkRecordsIntoDataBase" or another as you wish and specify the location.
  4. Then right-click on Solution Explorer - "Add New Item" - Add Web Form.
  5. 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.

  1. <%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="_Default" %>  
  2.   
  3. <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">  
  4. <html xmlns="http://www.w3.org/1999/xhtml">  
  5. <head runat="server">  
  6.     <title></title>  
  7. </head>  
  8. <body style="background-color: #0000FF">  
  9.     <form id="form1" runat="server">  
  10. <table style="color:White;" >  
  11.      
  12.     <tr>  
  13.         <td>  
  14.             Product Name  
  15.         </td>  
  16.         <td>  
  17.             Brand Name  
  18.         </td>  
  19.         <td>  
  20.             Warrenty  
  21.         </td>  
  22.         <td>  
  23.             Price  
  24.         </td>  
  25.     </tr>  
  26.     <tr>  
  27.     <td>   
  28.       <asp:TextBox ID="TextBox1" runat="server"></asp:TextBox>   
  29.       </td>  
  30.      <td>   
  31.      <asp:TextBox ID="TextBox2" runat="server"></asp:TextBox>  
  32.      </td>  
  33.       <td>  
  34.       <asp:TextBox ID="TextBox3" runat="server"></asp:TextBox>  
  35.       </td>  
  36.        <td>  
  37.        <asp:TextBox ID="TextBox4" runat="server"></asp:TextBox>  
  38.          
  39.          
  40.        </td>  
  41.     </tr>  
  42.     <tr>  
  43.     <td></td>  
  44.     <td></td>   
  45.     <td>   
  46.      
  47.     </td>  
  48.     <td>   
  49.         <asp:Button ID="AddProduct" runat="server" style="color:White" Text="Add Product"   
  50.             onclick="AddProduct_Click" BackColor="#999966" /></td>  
  51.     </tr>  
  52.    </table>    
  53.    <div style="margin-top:20px;margin-left:10px;">  
  54.      
  55.     
  56.     <asp:GridView ID="GridView1" AutoGenerateColumns="false" runat="server" CellPadding="4" ForeColor="#333333"   
  57.         GridLines="None">  
  58.         <AlternatingRowStyle BackColor="White" />  
  59.     <Columns>  
  60.     <asp:BoundField  HeaderStyle-Width="120px" HeaderText="Product Name" DataField="ProductName"/>  
  61.      <asp:BoundField HeaderStyle-Width="120px" HeaderText="Brand Name" DataField="BrandName"/>  
  62.      <asp:BoundField HeaderStyle-Width="120px" HeaderText="Warrenty" DataField="Warrenty"/>  
  63.      <asp:BoundField  HeaderStyle-Width="120px" HeaderText="Price" DataField="Price"/>  
  64.     </Columns>  
  65.         <EditRowStyle BackColor="#2461BF" />  
  66.         <FooterStyle BackColor="#507CD1" Font-Bold="True" ForeColor="White" />  
  67.         <HeaderStyle BackColor="#507CD1" Font-Bold="True" ForeColor="White" />  
  68.         <PagerStyle BackColor="#2461BF" ForeColor="White" HorizontalAlign="Center" />  
  69.         <RowStyle BackColor="#EFF3FB" />  
  70.         <SelectedRowStyle BackColor="#D1DDF1" Font-Bold="True" ForeColor="#333333" />  
  71.         <SortedAscendingCellStyle BackColor="#F5F7FB" />  
  72.         <SortedAscendingHeaderStyle BackColor="#6D95E1" />  
  73.         <SortedDescendingCellStyle BackColor="#E9EBEF" />  
  74.         <SortedDescendingHeaderStyle BackColor="#4870BE" />  
  75.     </asp:GridView>  
  76.      </div>  
  77.     <div  style="margin-top:10px;margin-left:350px">  
  78.     <asp:Button ID="btnsubmitProducts" runat="server" style="color:White"   
  79.             Text="Save Products" BackColor="#999966" onclick="btnsubmitProducts_Click" />  
  80.     </div>  
  81.     </form>  
  82. </body>  
  83. </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,
  1. private void AddDefaultFirstRecord()  
  2. {  
  3.      //creating DataTable  
  4.      DataTable dt = new DataTable();  
  5.      DataRow dr;  
  6.      dt.TableName = "ProductsSold";  
  7.      //creating columns for DataTable  
  8.      dt.Columns.Add(new DataColumn("ProductName", typeof(string)));  
  9.      dt.Columns.Add(new DataColumn("BrandName", typeof(string)));  
  10.      dt.Columns.Add(new DataColumn("Warrenty", typeof(int)));  
  11.      dt.Columns.Add(new DataColumn("Price", typeof(double)));  
  12.      dr = dt.NewRow();  
  13.      dt.Rows.Add(dr);  
  14.   
  15.      ViewState["ProductsSold"] = dt;  
  16.      GridView1.DataSource = dt;  
  17.      GridView1.DataBind();  
  18. } 
Now call the function above at Page Load so that the initial records will be added into the view state and Grid View as,
  1. protected void Page_Load(object sender, EventArgs e)  
  2. {  
  3.      if (!IsPostBack)  
  4.      {  
  5.          AddDefaultFirstRecord();  
  6.      }  
  7. } 
 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,
  1. private void AddNewRecordRowToGrid()  
  2. {  
  3.      if (ViewState["ProductsSold"] != null)  
  4.      {  
  5.          DataTable dtCurrentTable = (DataTable)ViewState["ProductsSold"];  
  6.          DataRow drCurrentRow = null;  
  7.   
  8.          if (dtCurrentTable.Rows.Count > 0)  
  9.          {  
  10.   
  11.              for (int i = 1; i <= dtCurrentTable.Rows.Count; i++)  
  12.              {  
  13.   
  14.                //Creating new row and assigning values  
  15.                  drCurrentRow = dtCurrentTable.NewRow();  
  16.                  drCurrentRow["ProductName"] = TextBox1.Text;  
  17.                  drCurrentRow["BrandName"] = TextBox2.Text;  
  18.                  drCurrentRow["Warrenty"] =Convert.ToInt32( TextBox3.Text);  
  19.                  drCurrentRow["Price"] = Convert.ToDouble(TextBox4.Text);  
  20.                  
  21.              }  
  22.              //Removing initial blank row  
  23.              if (dtCurrentTable.Rows[0][0].ToString() == "")  
  24.              {  
  25.                  dtCurrentTable.Rows[0].Delete();  
  26.                  dtCurrentTable.AcceptChanges();  
  27.   
  28.              }  
  29.   
  30.              //Added New Record to the DataTable  
  31.              dtCurrentTable.Rows.Add(drCurrentRow);  
  32.              //storing DataTable to ViewState  
  33.              ViewState["ProductsSold"] = dtCurrentTable;  
  34.              //binding Gridview with New Row  
  35.              GridView1.DataSource = dtCurrentTable;  
  36.              GridView1.DataBind();  
  37.          }  
  38.      }  
  39. } 
Call the preceding function from the Add Product button click as,
  1. protected void AddProduct_Click(object sender, EventArgs e)  
  2. {  
  3.    AddNewRecordRowToGrid();  
  4. } 
Add the database connection string details to the web.config file as,
  1. <connectionStrings>  
  2.   <add name ="dbconn" connectionString ="Data Source=VITHAL;Initial Catalog=C#corner;User Id=sa;word=swift" providerName ="System.Data.Sqlclient"/>  
  3. </connectionStrings> 
Now create a function for the connection as,
  1. public void connection()  
  2. {  
  3.     //Stoting connection string   
  4.     constr = ConfigurationManager.ConnectionStrings["dbconn"].ConnectionString;  
  5.     con = new SqlConnection(constr);  
  6.     con.Open();  
  7.       
  8. } 
Now create the function to save the bulk records into the database using SqlBulkCopy as,
  1. private void BulkInsertToDataBase()  
  2. {  
  3.     DataTable dtProductSold = (DataTable)ViewState["ProductsSold"];  
  4.     connection();  
  5.     //creating object of SqlBulkCopy  
  6.     SqlBulkCopy objbulk = new SqlBulkCopy(con);  
  7.     //assigning Destination table name  
  8.     objbulk.DestinationTableName = "ProdcutsSold";  
  9.     //Mapping Table column  
  10.     objbulk.ColumnMappings.Add("ProductName", "ProductName");  
  11.     objbulk.ColumnMappings.Add("BrandName", "BrandName");  
  12.     objbulk.ColumnMappings.Add("Warrenty", "Warrenty");  
  13.     objbulk.ColumnMappings.Add("Price", "Price");  
  14.     //inserting bulk Records into DataBase   
  15.     objbulk.WriteToServer(dtProductSold);  
  16. } 
Call the preceding function on the save product button click as,
  1. protected void btnsubmitProducts_Click(object sender, EventArgs e)    
  2. {    
  3.     BulkInsertToDataBase();  
  4. } 
Now the entire code of the default.aspx.cs file will look such as follows, 
  1. using System;  
  2. using System.Data;  
  3. using System.Data.SqlClient;  
  4. using System.Configuration;  
  5.   
  6. public partial class _Default : System.Web.UI.Page  
  7. {  
  8.     public string constr;  
  9.     public SqlConnection con;  
  10.     protected void Page_Load(object sender, EventArgs e)  
  11.     {  
  12.         if (!IsPostBack)  
  13.         {  
  14.             AddDefaultFirstRecord();  
  15.         }  
  16.     }  
  17.     public void connection()  
  18.     {  
  19.         //Stoting connection string   
  20.         constr = ConfigurationManager.ConnectionStrings["dbconn"].ConnectionString;  
  21.         con = new SqlConnection(constr);  
  22.         con.Open();  
  23.   
  24.     }  
  25.     protected void AddProduct_Click(object sender, EventArgs e)  
  26.     {  
  27.         AddNewRecordRowToGrid();  
  28.     }  
  29.   
  30.     private void AddDefaultFirstRecord()  
  31.     {  
  32.         //creating DataTable  
  33.         DataTable dt = new DataTable();  
  34.         DataRow dr;  
  35.         dt.TableName = "ProductsSold";  
  36.         //creating columns for DataTable  
  37.         dt.Columns.Add(new DataColumn("ProductName", typeof(string)));  
  38.         dt.Columns.Add(new DataColumn("BrandName", typeof(string)));  
  39.         dt.Columns.Add(new DataColumn("Warrenty", typeof(int)));  
  40.         dt.Columns.Add(new DataColumn("Price", typeof(double)));  
  41.         dr = dt.NewRow();  
  42.         dt.Rows.Add(dr);  
  43.   
  44.         ViewState["ProductsSold"] = dt;  
  45.         GridView1.DataSource = dt;  
  46.         GridView1.DataBind();  
  47.     }  
  48.     private void AddNewRecordRowToGrid()  
  49.     {  
  50.         if (ViewState["ProductsSold"] != null)  
  51.         {  
  52.             DataTable dtCurrentTable = (DataTable)ViewState["ProductsSold"];  
  53.             DataRow drCurrentRow = null;  
  54.   
  55.             if (dtCurrentTable.Rows.Count > 0)  
  56.             {  
  57.   
  58.                 for (int i = 1; i <= dtCurrentTable.Rows.Count; i++)  
  59.                 {  
  60.   
  61.                     //Creating new row and assigning values  
  62.                     drCurrentRow = dtCurrentTable.NewRow();  
  63.                     drCurrentRow["ProductName"] = TextBox1.Text;  
  64.                     drCurrentRow["BrandName"] = TextBox2.Text;  
  65.                     drCurrentRow["Warrenty"] = Convert.ToInt32(TextBox3.Text);  
  66.                     drCurrentRow["Price"] = Convert.ToDouble(TextBox4.Text);  
  67.  
  68.                 }  
  69.                 //Removing initial blank row  
  70.                 if (dtCurrentTable.Rows[0][0].ToString() == "")  
  71.                 {  
  72.                     dtCurrentTable.Rows[0].Delete();  
  73.                     dtCurrentTable.AcceptChanges();  
  74.   
  75.                 }  
  76.   
  77.                 //Added New Record to the DataTable  
  78.                 dtCurrentTable.Rows.Add(drCurrentRow);  
  79.                 //storing DataTable to ViewState  
  80.                 ViewState["ProductsSold"] = dtCurrentTable;  
  81.                 //binding Gridview with New Row  
  82.                 GridView1.DataSource = dtCurrentTable;  
  83.                 GridView1.DataBind();  
  84.             }  
  85.         }  
  86.     }  
  87.   
  88.     protected void btnsubmitProducts_Click(object sender, EventArgs e)  
  89.     { 
  90.         BulkInsertToDataBase();
  91.     }  
  92.   
  93.     private void BulkInsertToDataBase()  
  94.     {  
  95.         DataTable dtProductSold = (DataTable)ViewState["ProductsSold"];  
  96.         connection();  
  97.         //creating object of SqlBulkCopy  
  98.         SqlBulkCopy objbulk = new SqlBulkCopy(con);  
  99.         //assigning Destination table name  
  100.         objbulk.DestinationTableName = "ProdcutsSold";  
  101.         //Mapping Table column  
  102.         objbulk.ColumnMappings.Add("ProductName", "ProductName");  
  103.         objbulk.ColumnMappings.Add("BrandName", "BrandName");  
  104.         objbulk.ColumnMappings.Add("Warrenty", "Warrenty");  
  105.         objbulk.ColumnMappings.Add("Price", "Price");  
  106.         //inserting bulk Records into DataBase   
  107.         objbulk.WriteToServer(dtProductSold);  
  108.     }  
  109. } 
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.


Similar Articles