Insert more that 1 row at a time

Hi Suma.. just check this may be helpful to you
its inserts data(more than one Row at a time(In Single post back) data(All rows of the gridview) is inserted into sql type table(Tbl_LeaveType1) at once first and than to original(Tbl_Leave1)

create table Tbl_Leave1
(
LType varchar(5),
JAN varchar(20),
FEB varchar(20)
)

create type Tbl_LeaveType1 as table
(
TLType varchar(5),
TJAN varchar(20),
TFEB varchar(20)
)



create procedure Sp_Leave
(
@Leave Tbl_LeaveType1 readonly
)
as
insert into Tbl_Leave1(LType,JAN,FEB)select TLType,TJAN,TFEB from @Leave;


Source Code:

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Test1.aspx.cs" Inherits="Test1" %>




 


 

 

 
 
 
 
 
 
 
 
 
  '>
 
 
 
 
 
 
 
 
 
 
 

 

.Cs(C#)

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.Data.SqlClient;
using System.Configuration;

public partial class Test1 : System.Web.UI.Page
{
  protected void Page_Load(object sender, EventArgs e)
  {
  if (!IsPostBack)
  {
  TempTbl();
 
  }
  }
 
  public void TempTbl()
  {
  //Here i have load a gridview with empty data dynamically with Temprary table in .cs file.
  DataTable Dt = new DataTable();
  Dt.Columns.Add(new DataColumn("Leave", typeof(String)));
  Dt.Columns.Add(new DataColumn("JAN", typeof(String)));
  Dt.Columns.Add(new DataColumn("FEB", typeof(String)));

  Dt.Rows.Add("CL");
  Dt.Rows.Add("EL");
  Dt.Rows.Add("SL");
  Dt.Rows.Add("ML");
  Dt.Rows.Add("OL");
  Gv1.DataSource = Dt;
  Gv1.DataBind();


  }

  public DataTable GetGv()
  {
  DataTable Dt1 = new DataTable();
  Dt1.Columns.Add(new DataColumn("Leave", typeof(String)));
  Dt1.Columns.Add(new DataColumn("JAN", typeof(String)));
  Dt1.Columns.Add(new DataColumn("FEB", typeof(String)));
  DataRow Dr;
  foreach (GridViewRow RW in Gv1.Rows)
  {
  Label Lv = (Label)RW.FindControl("L");
  string Leave = Lv.Text;
  TextBox J = (TextBox)RW.FindControl("t1");
  string JAN = J.Text;
  TextBox F = (TextBox)RW.FindControl("t2");
  string FEB = F.Text;
  Dr = Dt1.NewRow();
  Dr[0] = Lv.Text;
  Dr[1] = J.Text;
  Dr[2] = F.Text;
  Dt1.Rows.Add(Dr);
  }
  return Dt1;
  }

  public void SaveData()
  {
  DataTable Dt2 = new DataTable();

  Dt2 = GetGv();
  //using web.config file
  string strcon = ConfigurationManager.ConnectionStrings["conn"].ConnectionString;
      //or you can write query string//
  using (SqlConnection con = new SqlConnection(strcon))
  {
  SqlCommand cmd = con.CreateCommand();
  cmd.CommandType = CommandType.StoredProcedure;
  cmd.CommandText = "Sp_Leave";
  SqlParameter P1 = cmd.Parameters.AddWithValue("@Leave", Dt2);
  con.Open();
  cmd.ExecuteNonQuery();


  }

  }
  protected void Btn1_Click(object sender, EventArgs e)
  {
  SaveData();

  }
}