ASP.Net: Insert multiple records (rows) into sql server database

In this article I am going to explain how to insert multiple records (rows) into sql server database using Asp.net

Description:

In the previous article I have explained Asp.net add multiple rows to Gridview dynamically and Fill Country, State and City dropdownlist.

I have created a table Tb_Movies and want to insert multiple records into it. 

User click on add new row button (user can add multiple rows to Gridview) it will add row to Gridview data control.

Implementation:

Create store procedure to insert data

Create PROCEDURE Sp_InsertMoviesRecord

(

@name varchar(50),

@genre varchar(50)

)

AS

BEGIN

            SET NOCOUNT ON;

Insert into Tb_Movies(Name,Genre) values(@name,@genre)

END

GO

Add a webform to project/website. Drag and drop the required control toolbox to webform.

HTML Markup:

   <asp:Button ID="btnAddNewRow" runat="server" Text="Add New Row" OnClick="btnAddNewRow_Click" />

        <br />

        <br />

        <asp:GridView ID="gvbook" runat="server" AutoGenerateColumns="false">

    <Columns>

         <asp:TemplateField HeaderText="S. No.">

                <ItemTemplate>

                    <%#Container.DataItemIndex +1 %>

                </ItemTemplate>

            </asp:TemplateField>

        <asp:TemplateField HeaderText="Movie Name">

            <ItemTemplate>

                <asp:TextBox ID="txtname" runat="server">

                </asp:TextBox>

            </ItemTemplate>

        </asp:TemplateField>

        <asp:TemplateField HeaderText="Genre">

            <ItemTemplate>

                <asp:TextBox ID="txtgenre" runat="server">

                </asp:TextBox>

            </ItemTemplate>

        </asp:TemplateField>

    </Columns>

</asp:GridView>

        <br />

        <br />

        <asp:Button ID="btnsave" runat="server" Text="Save" OnClick="btnsave_Click1" />

Add the namespace

C#:

using System.Data;

using System.Data.SqlClient;

using System.Configuration;

VB:

Imports System.Data

Imports System.Data.SqlClient

Imports System.Configuration

Create sqlConnection:

C#:

SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["connection"].ToString());

VB:

Dim con As New SqlConnection(ConfigurationManager.ConnectionStrings("connection").ToString())

Add the rows to gridview

To add single row or multiple rows to Gridview write the below given code on button click

C#:

  protected void btnAddNewRow_Click(object sender, EventArgs e)

    {

        try

        {

            var rows = gvbook.Rows.Cast<GridViewRow>().Select(a => new

            {

                Name = ((TextBox)a.FindControl("txtname")).Text,

                genre = ((TextBox)a.FindControl("txtgenre")).Text,

            }).ToList();

            rows.Add(new

            {

                Name = "",

                genre = ""

            });

            gvbook.DataSource = rows;

            gvbook.DataBind();

            btnsave.Visible = true;

        }

        catch (Exception ex)

        { }

    }

VB:

Protected Sub btnAddNewRow_Click(sender As Object, e As EventArgs) Handles btnAddNewRow.Click

        Try

             Dim rows = gvbook.Rows.Cast(Of GridViewRow)().[Select](Function(a) New With {

       Key .Name = DirectCast(a.FindControl("txtname"), TextBox).Text,

       Key .genre = DirectCast(a.FindControl("txtgenre"), TextBox).Text

   }).ToList()

            rows.Add(New With {

                Key .Name = "",

                Key .genre = ""

            })

            gvbook.DataSource = rows

            gvbook.DataBind()

            btnsave.Visible = True

        Catch ex As Exception

        End Try

    End Sub

Insert the records into database

After adding row/rows to Gridview save button is visible and on button click write the given code.

C#:

protected void btnsave_Click1(object sender, EventArgs e)

    {

        foreach (GridViewRow gvrow in gvbook.Rows)

        {

            string name = ((TextBox)gvrow.FindControl("txtname")).Text;

            string genre = ((TextBox)gvrow.FindControl("txtgenre")).Text;

            SqlCommand cmd = new SqlCommand("Sp_InsertMoviesRecord", con);

                cmd.CommandType = CommandType.StoredProcedure;

                con.Open();

                cmd.Parameters.AddWithValue("@name", name);

                cmd.Parameters.AddWithValue("@genre", genre);

                cmd.ExecuteNonQuery();

                con.Close();           

        }

        Response.Write("<script>alert('Records Inserted Successfully');</script>");

    }

VB:

Protected Sub btnsave_Click(sender As Object, e As EventArgs) Handles btnsave.Click

        Try

            For Each gvrow As GridViewRow In gvbook.Rows

                Dim name As String = DirectCast(gvrow.FindControl("txtname"), TextBox).Text

                Dim genre As String = DirectCast(gvrow.FindControl("txtgenre"), TextBox).Text

                Dim cmd As New SqlCommand("Sp_InsertMoviesRecord", con)

                cmd.CommandType = CommandType.StoredProcedure

                con.Open()

                cmd.Parameters.AddWithValue("@name", name)

                cmd.Parameters.AddWithValue("@genre", genre)

                cmd.ExecuteNonQuery()

                con.Close()

            Next

            Response.Write("<script>alert('Records Inserted Successfully');</script>")

        Catch ex As Exception

        End Try

    End Sub

Build and run the project. Hope this article helps you.

 Result:

  In this article we have learn to how to add multiple rows to Gridview dynamically  in asp.net using C# and VB.net. I hope you enjoyed this article.