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>
</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
}
