Editing, Deleting, Sorting and Paging in DataGrid Web Control

Description

The following article is about techniques for editing, deleting, sorting and paging using web Datagrid control. For this purpose, I used NortWind database in MSDE. The program has two parts :

ASPX file holds html code
Code Behind C# class file, holds all the logic and methods.

Source Code:

ASPX file starts here: Here is the design of DataGrid object, I commented out what properties and events do, so it becomes easier to follow

<asp:DataGrid id="MyDataGrid" style="Z-INDEX: 101; LEFT: 16px; POSITION:

absolute; TOP: 104px" runat="server" BorderStyle="Ridge" GridLines="None"

BorderWidth="2px" BorderColor="White" BackColor="White" CellPadding="3"

CellSpacing="1" AllowPaging="True" AllowSorting="True" PageSize="25"

PagerStyle-Mode="NextPrev" PagerStyle-NextPageText="Next" PagerStyle-

PrevPageText="Previous" PagerStyle-HorizontalAlign="Center" PagerStyle-

Position="TopAndBottom" DataKeyField="ProductID"

OnPageIndexChanged="MyDataGrid_PageIndexChanged"

OnSortCommand="Sort_Grid" OnDeleteCommand="MyDataGrid_Delete"

OnUpdateCommand="MyDataGrid_Update"

OnCancelCommand="MyDataGrid_Cancel" OnEditCommand="MyDataGrid_Edit"

AutoGenerateColumns="False" HorizontalAlign="Left">

<HeaderStyle Font-Bold="True" ForeColor="#E7E7FF"

BackColor="#4A3C8C"></HeaderStyle>

<PagerStyle NextPageText="Next" PrevPageText="Previous"

HorizontalAlign="Right" ForeColor="Black" Position="TopAndBottom"

BackColor="#C6C3C6"></PagerStyle>

<SelectedItemStyle Font-Bold="True" ForeColor="White"

BackColor="#9471DE"></SelectedItemStyle>

<asp:EditCommandColumn ButtonType="LinkButton" UpdateText="<img

border=0 src=ok.gif>" CancelText="<img border=0 src=cancel.gif>"

EditText="<img border=0 src=edit.gif>"></asp:EditCommandColumn>

<asp:ButtonColumn Text="<img border=0 src=delete.gif>"

CommandName="Delete"></asp:ButtonColumn>

<asp:BoundColumn DataField="ProductID" SortExpression="ProductID"

ReadOnly="True" HeaderText="Product ID"></asp:BoundColumn>

<asp:BoundColumn DataField="ProductName" SortExpression="ProductName"

HeaderText="Product Name"></asp:BoundColumn>

<asp:BoundColumn DataField="QuantityPerUnit"

SortExpression="QuantityPerUnit" HeaderText="Quantity Per Unit">

</asp:BoundColumn>

<asp:BoundColumn DataField="UnitPrice" SortExpression="UnitPrice"

HeaderText="Unit Price" DataFormatString="{0:c}"></asp:BoundColumn>

<asp:BoundColumn DataField="UnitsInStock" SortExpression="UnitsInStock"

HeaderText="Units In Stock"></asp:BoundColumn>

<asp:BoundColumn DataField="UnitsOnOrder" SortExpression="UnitsOnOrder"

HeaderText="Units On Order"></asp:BoundColumn>

<asp:BoundColumn DataField="ReorderLevel" SortExpression="ReorderLevel"

HeaderText="Reorder Level"></asp:BoundColumn>

<asp:TemplateColumn HeaderText="Discontinued"

SortExpression="Discontinued">

<asp:CheckBox id="Discontinued" runat="server" Checked='<%#

DataBinder.Eval(Container.DataItem, "Discontinued") %>' />

</ItemTemplate>

</asp:TemplateColumn>

</Columns>

</asp:DataGrid>

C# Code Behind starts here:

It is a good technique that you can bind data when the page is not postback,what that means that there will be one time data binding when the page is requested once.

private void Page_Load(object sender, System.EventArgs e)

{

if(!IsPostBack)

{

BindGrid();

}

The following code is for accessing database, executing command based on given SQL statement. This function return DataSet which holds returned records from database.

/// <summary>

/// this function returns dataset of products details

/// </summary>

/// <returns></returns>

private DataSet GetProductData()

{

///this is sql statement which returns records

String SQLStatement="SELECT Products.ProductID, Products.ProductName, Products.QuantityPerUnit,

Products.UnitPrice, "+ "Products.UnitsInStock, Products.UnitsOnOrder, Products.ReorderLevel,

Products.Discontinued "+ "FROM Products";

///instantiate sql connection and command object

SqlConnection myConnection= new SqlConnection(@"server=(local)

\NetSDK;database=NorthWind;uid=northwind;pwd=northwind;");

SqlDataAdapter myCommand = new SqlDataAdapter(SQLStatement,myConnection);

///declare myDataSet as DataSet

DataSet myDataSet;

// mark the Command as s Text

myCommand.SelectCommand.CommandType=CommandType.Text;

///create an instance of dataset

myDataSet = new DataSet();

///fill the dataset with returned records

myCommand.Fill(myDataSet, "Products");

///finally return dataset

return myDataSet;

}

This function is invoked only when the user clicks edit button. The code finds the index of item which will be edited, then assigns to DataGrid's EditItemIndex property.

/// <summary>

/// this function is invoked only when the user clicks edit button

/// </summary>

/// <param name="sender"></param>

/// <param name="E"></param>

protected void MyDataGrid_Edit(Object sender, DataGridCommandEventArgs E)

{

///findout the selected item index and bindgrid again

MyDataGrid.EditItemIndex = (int)E.Item.ItemIndex;

BindGrid();

}

When the user clicks on cancel button, this function is invoked. The code as you see assigns -1 to DataGrid's EditItemIndex.

-1 means "not selected".

/// <summary>

/// when the user clicks cancel button, this function is invoked

/// </summary>

/// <param name="sender"></param>

/// <param name="E"></param>

protected void MyDataGrid_Cancel(Object sender, DataGridCommandEventArgs E)

{

MyDataGrid.EditItemIndex = -1;

BindGrid();

}

The following code deletes a record based on selected item in DataGrid. DataGrid web control has a DataKeyField property. in my case it holds ProductID. If you want to figure out, what is the ProductID of selected item in the datagrid? The following code answers your question.

/// <summary>

/// this function deletes a record in the dataset

/// </summary>

/// <param name="sender"></param>

/// <param name="E"></param>

protected void MyDataGrid_Delete(Object sender, DataGridCommandEventArgs E)

{

int ProductID =(int)MyDataGrid.DataKeys[(int)E.Item.ItemIndex];

String SQLStatement="Delete Products WHERE ProductID="+ProductID;

SqlConnection myConnection= new SqlConnection(@"server=(local)

\NetSDK;database=NorthWind;uid=northwind;pwd=northwind;");

SqlCommand myCommand = new SqlCommand(SQLStatement,myConnection);

myCommand.CommandType=CommandType.Text;

myConnection.Open();

myCommand.ExecuteNonQuery();

myConnection.Close();

MyDataGrid.EditItemIndex = -1;

BindGrid();

}

The following code updates Products table in NorthWind Database. there are two techniques I am aware of.

First : If the field is bound as BoundColumn.

<asp:BoundColumn DataField="ProductID" SortExpression="ProductID" ReadOnly="True" HeaderText="Product ID"></asp:BoundColumn>

How you can retrive the edited value, you have to know the cell number in my case it is 3 ( like Cells[3]), be careful, index is zero based. Here is the answer :

string ProductName = ((TextBox)E.Item.Cells[3].Controls[0]).Text;

First we have to cast it to TextBox because when you edit datagrid, you are editing values in TextBox. What I do is that cast it to Textbox and then get the Text value of this TextBox and assign to string value.

Second : If the field is in TemplateColumn.

<asp:TemplateColumn HeaderText="Discontinued" SortExpression="Discontinued">

<asp:CheckBox id="Discontinued" runat="server" Checked='<%# DataBinder.Eval(Container.DataItem,

"Discontinued") %>' />

</ItemTemplate>

</asp:TemplateColumn>

You can use the following techniques to retrive value.

bool Discon = ((CheckBox)E.Item.FindControl("Discontinued")).Checked;

This time I used FindControl() method to get the value of Discontinued CheckBox.

/// <summary>

/// this function updates a record

/// </summary>

/// <param name="sender"></param>

/// <param name="E"></param>

protected void MyDataGrid_Update(Object sender, DataGridCommandEventArgs E)

{

int ProductID =(int)MyDataGrid.DataKeys[(int)E.Item.ItemIndex];

string ProductName = ((TextBox)E.Item.Cells[3].Controls[0]).Text;

string QuantityPerUnit=((TextBox)E.Item.Cells[4].Controls[0]).Text;

string UnitPrice = ((TextBox)E.Item.Cells[5].Controls[0]).Text;

Int16 UnitsInStock=Int16.Parse(((TextBox)E.Item.Cells[6].Controls[0]).Text);

Int16 UnitsOnOrder=Int16.Parse(((TextBox)E.Item.Cells[7].Controls[0]).Text);

Int16 ReorderLevel=Int16.Parse(((TextBox)E.Item.Cells[8].Controls[0]).Text);

bool Discon=((CheckBox)E.Item.FindControl("Discontinued")).Checked;

int result;

///there might be easier way to implement this

if(!Discon)

{

result=0;

}

else

{

result=1;

}

String SQLStatement="UPDATE Products "+ "SET ProductName='"+ProductName+"', "+

"QuantityPerUnit='"+QuantityPerUnit+"', "+"UnitPrice ="+UnitPrice.Substring(UnitPrice.IndexOf("$")+1)+", "+

"UnitsInStock ="+UnitsInStock+", "+"UnitsOnOrder ="+UnitsOnOrder+", "+

"ReorderLevel ="+ReorderLevel+", "+"Discontinued ="+result+

" WHERE ProductID ="+ProductID;

SqlConnection myConnection= new SqlConnection(@"server=(local)

\NetSDK;database=NorthWind;uid=northwind;pwd=northwind;");

SqlCommand myCommand = new SqlCommand(SQLStatement,myConnection);

myCommand.CommandType=CommandType.Text;

myConnection.Open();

myCommand.ExecuteNonQuery();

myConnection.Close();

MyDataGrid.EditItemIndex = -1;

BindGrid();

}

The following code binds the datagrid by getting DataSet from private function GetProductData.

/// <summary>

/// this function retrives the data from database and bind datagrid again

/// </summary>

protected void BindGrid()

{

MyDataGrid.DataSource=GetProductData().Tables["Products"].DefaultView;

MyDataGrid.DataBind();

}

The following event is fired when the user moves back or forward in the datagrid. DataGrid does not automatically get the new page index. You have to do manually.

/// <summary>

/// this function is for finding the page index whenever datagrid changes the page index

/// </summary>

/// <param name="source"></param>

/// <param name="e"></param>

protected void MyDataGrid_PageIndexChanged(object source, System.Web.UI.WebControls.DataGridPageChangedEventArgs e)

{

MyDataGrid.CurrentPageIndex=e.NewPageIndex;

BindGrid();

}

The following event is fired, whenever user wants to sort fields. for example, if user clicks field headers, this function will be fired and sort data.What do we need to sort. We need a DataView Object to sort e.SortExpression.ToString(); means that this expression returns which field header is clicked.

/// <summary>

/// this function sorts the dataset based on given criteria

/// </summary>

/// <param name="sender"></param>

/// <param name="e"></param>

protected void Sort_Grid(Object sender, DataGridSortCommandEventArgs e)

{

DataView dv= new DataView(GetProductData().Tables["Products"]);

dv.Sort= e.SortExpression.ToString();

MyDataGrid.DataSource=dv;

MyDataGrid.DataBind();

}