Insert Multiple record into DataBase from GridView using DataTable and Type concept in Sql Server

   In this article I have done a new concept . It accepts inputs from user and show it in a GridView then insert all records into table using the "Type" concept of Sql server

Step:1

Design the Table (Student Details)

 

Step:2

Create a Type  for Student Details in your Sql Server query Window

create type  Type_Studentdetails       
as  table
(
Name varchar(50),
FatherName varchar(50),
Address varchar(50),
Age int
)

Nb: here i have not taken ID because is is auto identity

Step:3

Create a Stored Procedure  for Student Details

CREATE PROCEDURE SP_AddStudentDetails
(
@P_DataTableStudentDetails AS Type_Studentdetails  READONLY
)
 as
 begin
   insert into StudentDetails ( Name ,FatherName,Address ,Age )
   select Name ,FatherName,Address ,Age from @P_DataTableStudentDetails

   WHERE 
    Name NOT IN (select Name from StudentDetails )

 end

Step:4

Design the page as per requirement

<table class="style1">
        <tr>
            <td style="text-align: right" width="40%">
                <asp:Label ID="Label1" runat="server" Text="Name"></asp:Label>
            </td>
            <td width="60%">
                <asp:TextBox ID="TxtName" runat="server" Width="210px"></asp:TextBox>
            </td>
        </tr>
        <tr>
            <td style="text-align: right">
                <asp:Label ID="Label2" runat="server" Text="Father's Name"></asp:Label>
            </td>
            <td>
                <asp:TextBox ID="TxtFartherName" runat="server" Width="210px"></asp:TextBox>
            </td>
        </tr>
        <tr>
            <td style="text-align: right">
                <asp:Label ID="Label3" runat="server" Text="Address"></asp:Label>
            </td>
            <td>
                <asp:TextBox ID="TxtAddress" runat="server" Width="210px"></asp:TextBox>
            </td>
        </tr>
        <tr>
            <td style="text-align: right">
                <asp:Label ID="Label4" runat="server" Text="Age" Width="210px"></asp:Label>
            </td>
            <td>
                <asp:TextBox ID="TxtAge" runat="server" Width="210px"></asp:TextBox>
            </td>
        </tr>
        <tr>
            <td>
                &nbsp;
            </td>
            <td>
                <asp:Button ID="BtnAddtoList" runat="server" Text="Add to List" OnClick="BtnAddtoList_Click" />
                <asp:Button ID="BtnSave" runat="server" OnClick="BtnSave_Click" Text="Save" Width="61px" />
                <asp:Button ID="BtnCancel" runat="server" OnClick="BtnCancel_Click" Text="Cancel" />
            </td>
        </tr>
        <tr>
            <td colspan="2" align="center">
                <asp:GridView ID="GvDetails" runat="server" Width="60%" OnRowCommand="GvDetails_RowCommand"
                    OnRowDeleting="GvDetails_RowDeleting" OnRowEditing="GvDetails_RowEditing">
                    <Columns>
                        <asp:TemplateField HeaderText="Edit">
                            <ItemTemplate>
                                <asp:LinkButton ID="LnkEdit" runat="server" CommandName="Edit">Edit</asp:LinkButton>
                            </ItemTemplate>
                        </asp:TemplateField>
                        <asp:TemplateField HeaderText="Delete">
                            <ItemTemplate>
                                <asp:LinkButton ID="LnkDelete" runat="server" CommandName="Delete">Delete</asp:LinkButton>
                            </ItemTemplate>
                        </asp:TemplateField>
                    </Columns>
                </asp:GridView>
            </td>
        </tr>

       <tr>
            <td>
                <asp:Label ID="Label5" runat="server" Text=""></asp:Label>
            </td>
        </tr>
    </table>

Step:5

Declare connection in web.config file

<connectionStrings>
            <add name="MyDBConnection" connectionString="Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\MyDatabase.mdf;Integrated Security=True;User Instance=True" providerName="System.Data.SqlClient"/>
    </connectionStrings>

  

Step:6

C# Code

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Configuration;
using System.Data.SqlClient;

public partial class Projects_GridView_GridViewInsert : System.Web.UI.Page
{
    # region Methods
    DataTable dt;
    static int flag;
    static int dtflag = 0;
    static int rowno = 0;
    SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["MyDBConnection"].ConnectionString);
    SqlCommand cmd= new SqlCommand();
    # endregion

    # region Methods
    public DataTable CreateDataTable()
    {
        dt = new DataTable();
        dt.Columns.Add("Name", typeof(string));
        dt.Columns.Add("Father's Name", typeof(string));
        dt.Columns.Add("Address", typeof(string));
        dt.Columns.Add("Age", typeof(string));
        //............ we can add more columns as per our requirement
        ViewState["datatable"] = dt;
        return dt;

    }
    private void FillGridView()
    {
        dt = new DataTable();
        dt = (DataTable)ViewState["datatable"];
        if (dt.Rows.Count > 0)
        {
            GvDetails.DataSource = dt;
            GvDetails.DataBind();
        }
        else
        {
            GvDetails.Dispose();
            GvDetails.DataBind();
        }
    }
    public DataTable InnerData()
    {
        CreateDataTable();
        DataRow dr;
        foreach (GridViewRow row in GvDetails.Rows)
        {
            dr = dt.NewRow();
            dr[0] = row.Cells[2].Text;
            dr[1] = row.Cells[3].Text;
            dr[2] = row.Cells[4].Text;
            dr[3] = row.Cells[5].Text;           
            dt.Rows.Add(dr);
        }
        ViewState["datatable"] = dt;
        FillGridView();
        return dt;
    }
    # endregion

    # region Events
    protected void Page_Load(object sender, EventArgs e)
    { 
        con = new SqlConnection(ConfigurationManager.ConnectionStrings["MyDBConnection"].ConnectionString);  
        if (IsPostBack == false)
        {                 
            CreateDataTable();
        }
    }  
    protected void BtnAddtoList_Click(object sender, EventArgs e)
    {
        dt = new DataTable();
        dt = (DataTable)ViewState["datatable"];
        DataRow dr;
        if (dtflag == 0)
        {

            dr = dt.NewRow();
            dr[0] = TxtName.Text;
            dr[1] = TxtFartherName.Text;
            dr[2] = TxtAddress.Text;
            dr[3] = TxtAge.Text;
            dt.Rows.Add(dr);
          
        }
        else if (dtflag == 1)
        {
            dt.Rows[rowno][0] = TxtName.Text;
            dt.Rows[rowno][1] = TxtFartherName.Text;
            dt.Rows[rowno][2] = TxtAddress.Text;
            dt.Rows[rowno][3] = TxtAge.Text;
            dtflag = 0;
        }
        ViewState["datatable"] = dt;
        FillGridView();
    }
    protected void GvDetails_RowCommand(object sender, GridViewCommandEventArgs e)
    {
        LinkButton lk = (LinkButton)e.CommandSource;
        GridViewRow gr = (GridViewRow)lk.NamingContainer;
        dt = new DataTable();
        dt = (DataTable)ViewState["datatable"];
        if (e.CommandName == "Edit")
        {
            rowno = gr.RowIndex;
            TxtName.Text = GvDetails.Rows[rowno].Cells[2].Text;
            TxtFartherName.Text = GvDetails.Rows[rowno].Cells[3].Text;
            TxtAddress.Text = GvDetails.Rows[rowno].Cells[4].Text;
            TxtAge.Text = GvDetails.Rows[rowno].Cells[5].Text;          
            dtflag = 1;
        }
        if (e.CommandName == "Delete")
        {
            rowno = gr.RowIndex;
            dt.Rows.RemoveAt(rowno);
            ViewState["datatable"] = dt;
            FillGridView();
        }
    }
    protected void GvDetails_RowEditing(object sender, GridViewEditEventArgs e)
    {

    }
    protected void GvDetails_RowDeleting(object sender, GridViewDeleteEventArgs e)
    {

    }
    protected void BtnSave_Click(object sender, EventArgs e)
    {
        try
        {
            dt = new DataTable();
            dt = (DataTable) ViewState["datatable"];
            cmd.Connection = con;
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = "SP_AddStudentDetails";         
            cmd.Parameters.AddWithValue("@P_DataTableStudentDetails",dt);
            con.Open();
            cmd.ExecuteNonQuery();
            con.Close();
            Label5.Text = "Insert Successfull...........";
        }
        catch (Exception ex)
        {
             Label5.Text=ex.Message.ToString();
        }

    }
    protected void BtnCancel_Click(object sender, EventArgs e)
    {
        TxtName.Text = "";
        TxtFartherName.Text = "";
        TxtAddress.Text = "";
        TxtAge.Text = "";
        ViewState["datatable"] = null;
        GvDetails.DataSource = null;
        GvDetails.DataBind();
        CreateDataTable();
    }
    # endregion
}