CRUD operations in AspNet GridView


CRUD operations in AspNet GridView

gridview

Image credit:commons.wikimedia.org

As far CRUD operations in AspNet GridView are concerned, unfortunately the GridView has not yet been design to support CRUD operations completely because the GridView does not conveniently allow the addition of records inside its cells.But we can get the functionality of adding the records by using the footer row of the control as this row is an extra row that does not contain any data by default.
Now lets begin the tutorial regarding CRUD operations in AspNet GridView control.
To populate ASP.NET GridView control and how to execute Add, Alter, Upgrade, Erase, Paging and Sorting capacities in ASP.NET GridView control, we have to do some work around about this well known web control, so this specimen gives a complete example to demonstrating to actualize these fundamental capacities of this control. The specimen exhibits information source from both database and memory.

For this example to work, you must introduce the SqlServer 2008 R2 Express. This example contains a SqlServer database record. More data about SqlServer 2008 R2 Express and download connections can be found here:

SqlServer 2008 R2 details

Download Sql Server 2008 R2 Express

Here’s our Default.aspx page:

<asp:GridView ID=”GridView1″ runat=”server” AutoGenerateColumns=”False” BackColor=”White” AllowPaging=”true” PageSize=”10″
BorderColor=”Navy” onpageindexchanging=”GridView1_PageIndexChanging”
onrowcancelingedit=”GridView1_RowCancelingEdit”
onrowdatabound=”GridView1_RowDataBound” onrowdeleting=”GridView1_RowDeleting”
onrowediting=”GridView1_RowEditing” onrowupdating=”GridView1_RowUpdating”
>
<RowStyle BackColor=”White” ForeColor=”Navy” />
<Columns>
<asp:CommandField ShowEditButton=”True” />
<asp:CommandField ShowDeleteButton=”True” />
<asp:BoundField DataField=”PersonID” HeaderText=”PersonID” ReadOnly=”True”
/>

<asp:TemplateField HeaderText=”FirstName” SortExpression=”FirstName”>
<EditItemTemplate>
<asp:TextBox ID=”txtFirstName” runat=”server” Text='<%# Bind(“FirstName”) %>’></asp:TextBox>
</EditItemTemplate>
<ItemTemplate>
<asp:Label ID=”lblFirstName” runat=”server” Text='<%# Bind(“FirstName”) %>’></asp:Label>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText=”LastName” SortExpression=”LastName”>
<EditItemTemplate>
<asp:TextBox ID=”txtLastName” runat=”server” Text='<%# Bind(“LastName”) %>’></asp:TextBox>
</EditItemTemplate>
<ItemTemplate>
<asp:Label ID=”lblLastName” runat=”server” Text='<%# Bind(“LastName”) %>’></asp:Label>
</ItemTemplate>
</asp:TemplateField>
</Columns>
<FooterStyle BackColor=”Blue” ForeColor=”White” />
<PagerStyle BackColor=”Navy” ForeColor=”Yellow” HorizontalAlign=”Center” />
<SelectedRowStyle BackColor=”Red” Font-Bold=”True” ForeColor=”Yellow” />
<HeaderStyle BackColor=”Brown” Font-Bold=”True” ForeColor=”White” />
</asp:GridView>

<asp:LinkButton ID=”lnAdd” runat=”server” onclick=”lnAdd_Click”>AddNew</asp:LinkButton>

<asp:Panel ID=”addPanel” runat=”server” Visible=”False”>
Last name:
<asp:TextBox ID=”txtLastName” runat=”server”></asp:TextBox>

First name:
<asp:TextBox ID=”txtFirstName” runat=”server”></asp:TextBox>

<asp:LinkButton ID=”lnSubmit” runat=”server” onclick=”lnSubmit_Click”>Submit</asp:LinkButton>

<asp:LinkButton ID=”lnCancel” runat=”server” onclick=”lnCancel_Click”>Cancel</asp:LinkButton>

</asp:Panel>

Here’s our Default.aspx.cs page:

using System;
using System.Data;
using System.Data.SqlClient;

protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{

BindData();
}
}

private void BindData()
{
using (SqlConection con = new SqlConection(ConfigurationManager.ConectionStrings[“myConection”].ToString()))
{
DataSet ds = new DataSet();

string cmdSelect = “SELECT PersonID ,LastName,FirstName FROM Person”;

SqlDataAdapter da = new SqlDataAdapter(cmdSelect, con);

con.Open();

da.Fill(ds);

GridView1.DataSource = ds;
GridView1.DataBind();
}
}

protected void GridView1_RowDataBound(object sender, GridViewRowEventArgs e)
{
if (e.Row.RowType == DataControlRowType.DataRow)
{
if (e.Row.RowState == DataControlRowState.Normal || e.Row.RowState == DataControlRowState.Alternate)
{ ((LinkButton)e.Row.Cells[1].Controls[0]).Attributes[“onclick”] = “if(!confirm(‘Are you sure to delete this record?’)) return false;”;
}
}
}

protected void GridView1_PageIndexChanging(object sender, GridViewPageEventArgs e)
{
GridView1.PageIndex = e.NewPageIndex;
BindData();
}

protected void GridView1_RowEditing(object sender, GridViewEditEventArgs e)
{

GridView1.EditIndex = e.NewEditIndex;
BindData();
btnAdd.Visible = false;
}

// GridView.RowCancelingEdit Event
protected void GridView1_RowCancelingEdit(object sender, GridViewCancelEditEventArgs e)
{
GridView1.EditIndex = -1;

BindData();

btnAdd.Visible = true;
}

protected void GridView1_RowUpdating(object sender, GridViewUpdateEventArgs e)
{
using (SqlConection con = new SqlConection(ConfigurationManager.ConectionStrings[“MyConnection”].ToString()))
{

SqlCommand cmd = new SqlCommand();

cmd.Conection = con;

cmd.CommandText = “UPDATE Person SET LastName = @LastName, FirstName = @FirstName WHERE PersonID = @PersonID”;

cmd.CommandType = CommandType.Text;

string strPersonID = GridView1.Rows[e.RowIndex].Cells[2].Text;
string strFirstName = ((TextBox)GridView1.Rows[e.RowIndex].FindControl(“txFirstName”)).Text;

string strLastName = ((TextBox)GridView1.Rows[e.RowIndex].FindControl(“txtLastName”)).Text;

cmd.Parameters.Add(“@PersonID”, SqlDbType.Int).Value = strPersonID;

con.Open();

cmd.ExecuteNonQuery();
}

GridView1.EditIndex = -1;

BindData();

btnAdd.Visible = true;
}

protected void GridView1_RowDeleting(object sender, GridViewDeleteEventArgs e)
{
using (SqlConection con = new SqlConection(ConfigurationManager.ConectionStrings[“MyConnection”].ToString()))
{
SqlCommand cmd = new SqlCommand();

cmd.Conection = con;

cmd.CommandText = “DELETE FROM Person WHERE PersonID = @PersonID”;

cmd.CommandType = CommandType.Text;

string strPersonID = GridView1.Rows[e.RowIndex].Cells[2].Text;

cmd.Parameters.Add(“@PersonID”, SqlDbType.Int).Value = strID;

con.Open();

cmd.ExecuteNonQuery();
}

BindData();
}

protected void btnAdd_Click(object sender, EventArgs e)
{
btnAdd.Visible = false;
pnlAdd.Visible = true;
}

protected void btnSubmit_Click(object sender, EventArgs e)
{
using (SqlConection con = new SqlConection(ConfigurationManager.ConectionStrings[“MyConnection”].ToString()))
{
SqlCommand cmd = new SqlCommand();

cmd.Conection = con;

cmd.CommandText = “INSERT INTO Person( LastName, FirstName ) VALUES ( @LastName, @FirstName )”;

cmd.CommandType = CommandType.Text;
cmd.Parameters.Add(“@LastName”, SqlDbType.NVarChar, 50).Value = txtLastName.Text;
cmd.Parameters.Add(“@FirstName”, SqlDbType.NVarChar, 50).Value = txtFirstName.Text;

con.Open();

cmd.ExecuteNonQuery();
}

BindData();

txtLastName.Text = “”;
txtFirstName.Text = “”;

btnAdd.Visible = true;
pnlAdd.Visible = false;
}

protected void btnCancel_Click(object sender, EventArgs e)
{

txtLastName.Text = “”;
txtFirstName.Text = “”;

btnAdd.Visible = true;
pnlAdd.Visible = false;
}

private void InitializeDataSource()
{

DataTable dt = new DataTable();

dt.Columns.Add(“PersonID”);
dt.Columns.Add(“LastName”);
dt.Columns.Add(“FirstName”);

dt.Columns[“PersonID”].AutoIncrement = true;
dt.Columns[“PersonID”].AutoIncrementSeed = 1;
dt.Columns[“PersonID”].AutoIncrementStep = 1;

DataColumn[] DataKeys = new DataColumn[1];
DataKeys[0] = dt.Columns[“PersonID”];
dt.PrimaryKey = DataKeys;

dt.Rows.Add(null, “Adeel”, “Ahmed”);
dt.Rows.Add(null, “Wajahat”, “Ali”);
dt.Rows.Add(null, “Mark”, “Callous”);
dt.Rows.Add(null, “Shawn”, “Michaels”);
dt.Rows.Add(null, “Bret”, “Hart”);
dt.Rows.Add(null, “Van”, “Dam”);
dt.Rows.Add(null, “Paul”, “Bearer”);

ViewState[“dt”] = dtPerson;
}

private void BindGridView()
{
if (ViewState[“dt”] != null)
{
DataTable dtPerson = (DataTable)ViewState[“dt”];

GridView1.DataSource = dvPerson;
GridView1.DataBind();
}
}

Syed Adeel Ahmed
Syed Adeel Ahmed
Analyst, Programmer, Educationist and Blogger at Technofranchise
Computer Systems Engineer from Sir Syed University Of Engineering & Technology.I am passionate about all types of programming.

Published by

Syed Adeel Ahmed

Computer Systems Engineer from Sir Syed University Of Engineering & Technology.I am passionate about all types of programming.

Leave a Reply