Gridview [Insert,Update and Insert]

App_Code[Folder] : CustomersCls.cs[File]

using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient;

/// <summary>
/// Summary description for ClsCustomer
/// </summary>
public class CustomersCls
{

private string cnstr = ConfigurationManager.ConnectionStrings[“ConnectionString”].ConnectionString;
private string sql;

public CustomersCls()
{
//
// TODO: Add constructor logic here
//
}

public void Insert(string CustomerName, string Gender, string City, string State, string CustomerType)
{
string sql = “Insert Into SampleCustomer (Name, Gender, City, State, Type) Values (‘” + CustomerName
+ “‘ , ‘” + Gender + “‘, ‘” + City + “‘, ‘” + State + “‘, ‘” + CustomerType + “‘)”;
SqlConnection conn = new SqlConnection(cnstr);
conn.Open();
SqlCommand cmd = new SqlCommand(sql, conn);
cmd.ExecuteNonQuery();
conn.Close();
conn.Dispose();
}

public DataTable Fetch()
{
string sql = “Select * From SampleCustomer”;
SqlDataAdapter da = new SqlDataAdapter(sql, cnstr);
DataTable dt = new DataTable();
da.Fill(dt);
return dt;
}

public void Update(int CustomerCode, string CustomerName, string Gender, string City, string CustomerType)
{
string sql = “UPDATE SampleCustomer SET Name='” + CustomerName + “‘, Gender = ‘” + Gender
+ “‘, City='” + City + “‘, Type = ‘” + CustomerType + “‘ Where Code=” + CustomerCode;
SqlConnection conn = new SqlConnection(cnstr);
conn.Open();
SqlCommand cmd = new SqlCommand(sql, conn);
cmd.ExecuteNonQuery();
conn.Close();
conn.Dispose();
}

public void Delete(int CustomerCode)
{
string sql = “Delete SampleCustomer Where Code=” + CustomerCode;
SqlConnection conn = new SqlConnection(cnstr);
conn.Open();
SqlCommand cmd = new SqlCommand(sql, conn);
cmd.ExecuteNonQuery();
conn.Close();
conn.Dispose();
}

public DataTable FetchCustomerType()
{
string sql = “Select Distinct Type From SampleCustomer”;
SqlDataAdapter da = new SqlDataAdapter(sql, cnstr);
DataTable dt = new DataTable();
da.Fill(dt);
return dt;
}

}
//CREATE TABLE [dbo].[SampleCustomer](
//    [Code] [int] IDENTITY(1,1) NOT NULL,
//    [Name] [varchar](50) NULL,
//    [Gender] [varchar](10) NULL,
//    [City] [varchar](50) NULL,
//    [State] [varchar](50) NULL,
//    [Type] [varchar](50) NULL
//) ON [PRIMARY]

GridViewInsertEditUpdateDelete.aspx.cs  File:

using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;

public partial class GridViewInsertEditUpdateDelete : System.Web.UI.Page
{

CustomersCls customer = new CustomersCls();
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
FillCustomerInGrid();
}

private void FillCustomerInGrid()
{
DataTable dtCustomer = customer.Fetch();

if (dtCustomer.Rows.Count > 0)
{
GridView1.DataSource = dtCustomer;
GridView1.DataBind();
}
else
{
dtCustomer.Rows.Add(dtCustomer.NewRow());
GridView1.DataSource = dtCustomer;
GridView1.DataBind();

int TotalColumns = GridView1.Rows[0].Cells.Count;
GridView1.Rows[0].Cells.Clear();
GridView1.Rows[0].Cells.Add(new TableCell());
GridView1.Rows[0].Cells[0].ColumnSpan = TotalColumns;
GridView1.Rows[0].Cells[0].Text = “No Record Found”;
}
}
protected void GridView1_RowCommand(object sender, GridViewCommandEventArgs e)
{
if (e.CommandName.Equals(“AddNew”))
{
TextBox txtNewName = (TextBox)GridView1.FooterRow.FindControl(“txtNewName”);
DropDownList cmbNewGender = (DropDownList)GridView1.FooterRow.FindControl(“cmbNewGender”);
TextBox txtNewCity = (TextBox)GridView1.FooterRow.FindControl(“txtNewCity”);
TextBox txtNewState = (TextBox)GridView1.FooterRow.FindControl(“txtNewState”);
TextBox cmbNewType = (TextBox)GridView1.FooterRow.FindControl(“cmbNewType”);

customer.Insert(txtNewName.Text, cmbNewGender.SelectedValue, txtNewCity.Text, txtNewState.Text, cmbNewType.Text);
FillCustomerInGrid();
}
}
protected void GridView1_RowDataBound(object sender, GridViewRowEventArgs e)
{
if (e.Row.RowType == DataControlRowType.DataRow)
{
//DropDownList cmbType = (DropDownList)e.Row.FindControl(“cmbType”);

//if (cmbType != null)
//{
//    cmbType.DataSource = customer.FetchCustomerType();
//    cmbType.DataBind();
//    cmbType.SelectedValue = GridView1.DataKeys[e.Row.RowIndex].Values[1].ToString();
//}
}

if (e.Row.RowType == DataControlRowType.Footer)
{
//DropDownList cmbNewType = (DropDownList)e.Row.FindControl(“cmbNewType”);
//cmbNewType.DataSource = customer.FetchCustomerType();
//cmbNewType.DataBind();
}
}
protected void GridView1_RowCancelingEdit(object sender, GridViewCancelEditEventArgs e)
{
GridView1.EditIndex = -1;
FillCustomerInGrid();
}
protected void GridView1_RowDeleting(object sender, GridViewDeleteEventArgs e)
{
customer.Delete(Convert.ToInt32(GridView1.DataKeys[e.RowIndex].Values[0].ToString()));
FillCustomerInGrid();
}
protected void GridView1_RowEditing(object sender, GridViewEditEventArgs e)
{
GridView1.EditIndex = e.NewEditIndex;
FillCustomerInGrid();
}
protected void GridView1_RowUpdating(object sender, GridViewUpdateEventArgs e)
{
TextBox txtName = (TextBox)GridView1.Rows[e.RowIndex].FindControl(“txtName”);
DropDownList cmbGender = (DropDownList)GridView1.Rows[e.RowIndex].FindControl(“cmbGender”);
TextBox txtCity = (TextBox)GridView1.Rows[e.RowIndex].FindControl(“txtCity”);
TextBox cmbType = (TextBox)GridView1.Rows[e.RowIndex].FindControl(“cmbType”);

customer.Update(Convert.ToInt32(GridView1.DataKeys[e.RowIndex].Values[0].ToString()), txtName.Text, cmbGender.SelectedValue, txtCity.Text, cmbType.Text);
GridView1.EditIndex = -1;
FillCustomerInGrid();
}
}

GridViewInsertEditUpdateDelete.aspx :

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

<!DOCTYPE html PUBLIC “-//W3C//DTD XHTML 1.0 Transitional//EN” “http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd”&gt;

<html xmlns=”http://www.w3.org/1999/xhtml&#8221; >
<head runat=”server”>
<title>Untitled Page</title>
</head>
<body>
<form id=”form1″ runat=”server”>
<div>
<asp:GridView ID=”GridView1″ runat=”server” AutoGenerateColumns=”False”
DataKeyNames=”Code,Type” OnRowCancelingEdit=”GridView1_RowCancelingEdit”
OnRowDataBound=”GridView1_RowDataBound” OnRowEditing=”GridView1_RowEditing”
OnRowUpdating=”GridView1_RowUpdating” OnRowCommand=”GridView1_RowCommand”
ShowFooter=”True” OnRowDeleting=”GridView1_RowDeleting” CellPadding=”4″
ForeColor=”#333333″ GridLines=”None” Font-Bold=”True”
Font-Names=”Microsoft Sans Serif” Font-Size=”10pt” Font-Strikeout=”False”>
<FooterStyle BackColor=”#507CD1″ ForeColor=”White” Font-Bold=”True” />
<RowStyle BackColor=”#EFF3FB” />
<Columns>

<asp:TemplateField HeaderText=”Name” SortExpression=”Name”> <EditItemTemplate>
<asp:TextBox ID=”txtName” runat=”server” Text='<%# Eval(“Name”) %>’></asp:TextBox>
</EditItemTemplate>
<FooterTemplate>
<asp:TextBox ID=”txtNewName” runat=”server”></asp:TextBox> </FooterTemplate>
<ItemTemplate>
<asp:Label ID=”Label2″ runat=”server” Text='<%# Bind(“Name”) %>’></asp:Label>
</ItemTemplate>
</asp:TemplateField>

<asp:TemplateField HeaderText=”Gender”>
<EditItemTemplate>
<asp:DropDownList ID=”cmbGender” runat=”server” SelectedValue='<%# Eval(“Gender”) %>’>
<asp:ListItem Value=”M” Text=”Male”></asp:ListItem>
<asp:ListItem Value=”F” Text=”Female”></asp:ListItem>
</asp:DropDownList>
</EditItemTemplate>
<ItemTemplate>
<asp:Label ID=”lbGender” runat=”server” Text='<%# Eval(“Gender”) %>’></asp:Label>
</ItemTemplate>
<FooterTemplate>
<asp:DropDownList ID=”cmbNewGender” runat=”server” >
<asp:ListItem Selected=”True” Text=”Male” Value=”M”></asp:ListItem>
<asp:ListItem Text=”Female” Value=”F”></asp:ListItem> </asp:DropDownList>
</FooterTemplate>
</asp:TemplateField>

<asp:TemplateField HeaderText=”City”>
<EditItemTemplate>
<asp:TextBox ID=”txtCity” runat=”server” Text='<%# Bind(“City”) %>’></asp:TextBox>
</EditItemTemplate>
<FooterTemplate>
<asp:TextBox ID=”txtNewCity” runat=”server” ></asp:TextBox>
</FooterTemplate>
<ItemTemplate>
<asp:Label ID=”Label3″ runat=”server” Text='<%# Bind(“City”) %>’></asp:Label>
</ItemTemplate>
</asp:TemplateField>

<asp:TemplateField HeaderText=”State” SortExpression=”State”>
<EditItemTemplate>
<asp:Label ID=”Label1″ runat=”server” Text='<%# Eval(“State”) %>’></asp:Label>
</EditItemTemplate>
<FooterTemplate>
<asp:TextBox ID=”txtNewState” runat=”server” ></asp:TextBox>
</FooterTemplate>
<ItemTemplate>
<asp:Label ID=”Label4″ runat=”server” Text='<%# Bind(“State”) %>’></asp:Label>
</ItemTemplate>
</asp:TemplateField>

<asp:TemplateField HeaderText=”Type”>
<EditItemTemplate>
<asp:TextBox ID=”cmbType” runat=”server” > </asp:TextBox>
</EditItemTemplate>
<ItemTemplate>
<asp:Label ID=”Label5″ runat=”server” Text='<%# Eval(“Type”) %>’></asp:Label>
</ItemTemplate>
<FooterTemplate>
<asp:TextBox ID=”cmbNewType” runat=”server” > </asp:TextBox>
</FooterTemplate>
</asp:TemplateField>

<asp:TemplateField HeaderText=”Edit” ShowHeader=”False”>
<EditItemTemplate>
<asp:ImageButton ID=”LinkButton1″ runat=”server” CausesValidation=”True” CommandName=”Update”  Text=”Update” ImageUrl=”~/Save.jpeg” ></asp:ImageButton>
<asp:ImageButton ID=”LinkButton2″ runat=”server” CausesValidation=”False” CommandName=”Cancel” Text=”Cancel” ImageUrl=”~/cancel.jpeg” ></asp:ImageButton>
</EditItemTemplate>
<FooterTemplate>
<asp:ImageButton ID=”LinkButton2″ runat=”server” CausesValidation=”False” CommandName=”AddNew” Text=”Add New” ImageUrl=”~/new.jpeg” ></asp:ImageButton>
</FooterTemplate>
<ItemTemplate>
<asp:ImageButton ID=”LinkButton1″ runat=”server” CausesValidation=”False” CommandName=”Edit” Text=”Edit” ImageUrl=”~/pencil icon.jpg” ></asp:ImageButton>
</ItemTemplate>
</asp:TemplateField>
<asp:CommandField HeaderText=”Delete” ShowDeleteButton=”true” ShowHeader=”true”   />

</Columns>
<PagerStyle BackColor=”#2461BF” ForeColor=”White” HorizontalAlign=”Center” />
<SelectedRowStyle BackColor=”#D1DDF1″ Font-Bold=”True” ForeColor=”#333333″ />
<HeaderStyle BackColor=”#507CD1″ Font-Bold=”True” ForeColor=”White” />
<EditRowStyle BackColor=”#2461BF” />
<AlternatingRowStyle BackColor=”White” />
</asp:GridView>
<br />
<br />
<br />

</div>
</form>
</body>
</html>

Web.config

<connectionStrings>
<add name=”ConnectionString” connectionString=”Data Source=.;Initial Catalog=LMDB;Integrated Security=True”/>
</connectionStrings>

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s

%d bloggers like this: