Insert multiple GridView Rows into Database using C# and VB.Net in ASP.Net

Hi akhter,

Refer below sample.

HTML

<asp:GridView ID="GVWrk" runat="server" AutoGenerateColumns="False" ShowFooter="True">
    <Columns>
        <asp:TemplateField HeaderText="ID">
            <ItemTemplate>
                <asp:Label ID="SECID" runat="server" Text='<%# Bind("SECID") %>'> </asp:Label></ItemTemplate>
        </asp:TemplateField>
        <asp:TemplateField HeaderText="Section">
            <ItemTemplate>
                <asp:Label ID="txtsecnam" runat="server" Text='<%# Bind("Secnam") %>'> </asp:Label></ItemTemplate>
        </asp:TemplateField>
        <asp:TemplateField HeaderText="Date">
            <ItemTemplate>
                <asp:Label ID="txtDate" runat="server" Text='<%# DateTime.Now.ToString("dd/MM/yyyy") %>'> </asp:Label>
            </ItemTemplate>
        </asp:TemplateField>
        <asp:TemplateField HeaderText="QTY">
            <ItemTemplate>
                <asp:TextBox ID="QTY" runat="server" Width="50" Text='<%# Bind("emp_QTY") %>'></asp:TextBox>
            </ItemTemplate>
        </asp:TemplateField>
    </Columns>
</asp:GridView>
<br />
<asp:Button ID="ADD" runat="server" Text="Add" OnClick="ADD_Click" />
<br />
<asp:GridView runat="server" AutoGenerateColumns="false" ID="GridView1">
    <Columns>
        <asp:BoundField DataField="EID" HeaderText="EID" />
        <asp:BoundField DataField="SecID" HeaderText="SecID" />
        <asp:BoundField DataField="QTY" HeaderText="QTY" />
        <asp:BoundField DataField="EntryDate" HeaderText="EntryDate" DataFormatString="{0:dd/MM/yyyy}" />
    </Columns>
</asp:GridView>

Namespaces

C#

using System.Data;
using System.Data.SqlClient;
using System.Configuration;

VB.Net

Imports System.Data
Imports System.Data.SqlClient

Code

C#

protected void Page_Load(object sender, EventArgs e)
{
    if (!this.IsPostBack)
    {
        string query = "SELECT SecID SECID, Secnam,emp_QTY FROM Sections";
        DataTable dt = GetData(query);
        GVWrk.DataSource = dt;
        GVWrk.DataBind();
    }
}

private DataTable GetData(string query)
{

    using (SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["constr"].ConnectionString))
    {
        using (SqlCommand cmd = new SqlCommand(query, con))
        {
            using (SqlDataAdapter da = new SqlDataAdapter(cmd))
            {
                DataTable dt = new DataTable();
                da.Fill(dt);
                return dt;
            }
        }
    }
}

protected void ADD_Click(object sender, EventArgs e)
{
    DataTable dt = new DataTable();
    dt.Columns.AddRange(new DataColumn[] { new DataColumn("SecID", typeof(int)), new DataColumn("QTY", typeof(string)), new DataColumn("EntryDate", typeof(DateTime)) });
    foreach (GridViewRow row in GVWrk.Rows)
    {
        dt.Rows.Add((row.FindControl("SECID") as Label).Text, (row.FindControl("QTY") as TextBox).Text, (row.FindControl("txtDate") as Label).Text);
    }
    using (SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["constr"].ConnectionString))
    {
        using (SqlBulkCopy sqlBulkCopy = new SqlBulkCopy(con))
        {
            sqlBulkCopy.DestinationTableName = "dbo.Emp_Strength";
            sqlBulkCopy.ColumnMappings.Add("SecID", "SecID");
            sqlBulkCopy.ColumnMappings.Add("QTY", "QTY");
            sqlBulkCopy.ColumnMappings.Add("EntryDate", "EntryDate");
            con.Open();
            sqlBulkCopy.WriteToServer(dt);
            con.Close();
        }
    }
    string query = "SELECT EID, SecID,QTY,EntryDate FROM Emp_Strength";
    DataTable dtinsert = GetData(query);
    GridView1.DataSource = dtinsert;
    GridView1.DataBind();
}

VB.Net

Protected Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs) Handles Me.Load
    If Not Me.IsPostBack Then
        Dim query As String = "SELECT SecID SECID, Secnam,emp_QTY FROM Sections"
        Dim dt As DataTable = GetData(query)
        GVWrk.DataSource = dt
        GVWrk.DataBind()
    End If
End Sub

Private Function GetData(ByVal query As String) As DataTable
    Using con As SqlConnection = New SqlConnection(ConfigurationManager.ConnectionStrings("constr").ConnectionString)
        Using cmd As SqlCommand = New SqlCommand(query, con)
            Using da As SqlDataAdapter = New SqlDataAdapter(cmd)
                Dim dt As DataTable = New DataTable()
                da.Fill(dt)
                Return dt
            End Using
        End Using
    End Using
End Function

Protected Sub ADD_Click(ByVal sender As Object, ByVal e As EventArgs)
    Dim dt As DataTable = New DataTable()
    dt.Columns.AddRange(New DataColumn() {New DataColumn("SecID", GetType(Integer)), New DataColumn("QTY", GetType(String)), New DataColumn("EntryDate", GetType(DateTime))})
    For Each row As GridViewRow In GVWrk.Rows
        dt.Rows.Add((TryCast(row.FindControl("SECID"), Label)).Text, (TryCast(row.FindControl("QTY"), TextBox)).Text, (TryCast(row.FindControl("txtDate"), Label)).Text)
    Next
    Using con As SqlConnection = New SqlConnection(ConfigurationManager.ConnectionStrings("constr").ConnectionString)
        Using sqlBulkCopy As SqlBulkCopy = New SqlBulkCopy(con)
            sqlBulkCopy.DestinationTableName = "dbo.Emp_Strength"
            sqlBulkCopy.ColumnMappings.Add("SecID", "SecID")
            sqlBulkCopy.ColumnMappings.Add("QTY", "QTY")
            sqlBulkCopy.ColumnMappings.Add("EntryDate", "EntryDate")
            con.Open()
            sqlBulkCopy.WriteToServer(dt)
            con.Close()
        End Using
    End Using
    Dim query As String = "SELECT EID, SecID,QTY,EntryDate FROM Emp_Strength"
    Dim dtinsert As DataTable = GetData(query)
    GridView1.DataSource = dtinsert
    GridView1.DataBind()
End Sub

Screenshot