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.

CREATE TABLE [dbo].[ProdcutsSold] (
    [ProductId] [int] IDENTITY(1,1) NOT NULL,
      NULL,
      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 as follows.

Design view

  1. "Start" - "All Programs" - "Microsoft Visual Studio 2010".
  2. "File" - "New WebSite" - "C#" - "Empty WebSite" (to avoid adding a master page).
  3. Provide the website 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 text boxes onto the <form> section of the Default.aspx page.

Now the default.aspx Page source code will look 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>Warranty</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="Warranty" DataField="Warranty" />
                    <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.

Design mode

Now switch to the default.aspx.cs code behind the file and write the following code to create and save the data table 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 and 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()
{
    // Storing 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 columns  
    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 and click as.

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

Now the entire code of the default.aspx.cs file will look 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()
    {
        // Storing 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();
                }

                // Adding 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 as follows.

Application

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.

Add Product

Now add another Product detail, it will be added to the existing one.

Product details

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.

Database

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