Saturday, September 19, 2015

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:

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

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

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:
ASP.Net: Insert multiple records (rows) into sql server database

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

5 comments:

  1. nice one but when we enter data on first row and than after adding another row the first row also empty. what to do preventing this.

    ReplyDelete
  2. This is useful one.But i need help related to this one.
    The Problem is ,in my web page contain some controls in gridview(dropdown,textboxes)as well as in outside of gridview.The thing is i have given input from Both controls in a page.The Problem is I have select one value from dropdown which is outside of grid,then automatically loaded values(database value) to dropdown in gridview.We can add multiple rows to gridview both of the rows perform above same function.

    ReplyDelete
  3. is this working with footer textbox also?

    ReplyDelete