Datagrid(Paging,Sorting,Deleting)

In this second and last article we will see some of the most commonly used features of the datagrid web server control. This article will be focused on the Paging, Sorting and deleting functions. Read the Previous Article for some more details about the sample code and the Stored Procedures used.

Datagrid most commonly used Operations

Paging, Sorting, Deleting

Introduction:

In this second and last article we will see some of the most commonly used features of the datagrid web server control. This article will be focused on the Paging, Sorting and deleting functions. Read the Previous Article for some more details about the sample code and the Stored Procedures used.

Deleting in the Datagrid:

Lets first set up our Datagrid. As we know from the previous article that we can add some bound columns to the Datagrid. These bound columns can also be link buttons. Delete link button is also available and can be added to the Datagrid using the property builder. Here are the steps that you can use to add the delete link button to the Datagrid.

  1. Right click on the Datagrid and select property builder.
  2. Now select the columns tab and add a Delete Column. 
  3. You can set the name of the column using the header text field. 

The Delete Event in the datagrid:

The delete event in the Datagrid is known as DeleteCommand event. As I explained in the previous article that you can view all the events associated with the control by right clicking on the control and selecting the "flash" sign. Lets see the delete event code below: 

private void myDataGrid_DeleteCommand(object source, System.Web.UI.WebControls.DataGridCommandEventArgs e) 
{

string s = e.Item.Cells[0].Text; 
SqlCommand myCommand = new SqlCommand("DELETE FROM tblPerson WHERE UserName="+s,myConnection); 
myCommand.CommandType = CommandType.Text; 
myConnection.Open();
myCommand.ExecuteNonQuery();
myConnection.Close(); 

// Binds the grid so that change can be seen 
BindData();

} 

Explanation of the Code:

  1. The first line which is string s = e.Item.Cells[0].Text; gets the value of the first column in the Datagrid. The first column in our datagrid is username. So, in the string s we will get the username of the row whose delete link button has been clicked. 
  2. Next we used the PL/SQL query which just deletes the row whose UserName is in the column which was clicked. 
  3. Finally we execute the query and binds the grid on the page. 
  4. Please note that you should never use the Ad-hoc query like I did in the delete event. Always use Stored procedures to access the database. Ad-hoc queries make the database vulnerable to SQL Injections. I will leave the stored procedure for you to code. 

  So as you see the that deleting in Datagrid is pretty straight forward and follows the same procedure as editing and updating the Datagrid. 

Paging in the Datagrid:

   Paging is one of the most important and advanced features that have been included in the Datagrid server control. Consider a situation that you have 5000 rows that you need to display to the user and if you display all the 5000 rows on the same page, your user will have to scroll down his way to eternity. Microsoft exposes a paging property in the Datagrid control which lets the developer to display number of rows in the Datagrid and more rows can be viewed using the new pages in the Datagrid. Due to this feature its called paging. Lets see how paging works. 

   Datagrid controls lets you customize your paging features. In order to add the paging do the following steps. 

  1. Right click on the Datagrid control and select property builder. 
  2. Select the paging tab and you will see a whole new menu that is only dedicated to paging. 
  3. Check mark the checkbox to ensure that paging is enabled. 
  4. You can select how many rows to be displayed in the Datagrid. 
  5. You can also how you want the paging to appear. You can select the "Next", "Previous" buttons or you can also select the page numbers. Not only that but you can also specify your own paging link. 

Lets see the code behind the paging feature: 

private void Paging_DataGrid(object source, System.Web.UI.WebControls.DataGridPageChangedEventArgs e)
{
     myDataGrid.CurrentPageIndex = e.NewPageIndex; BindData();
}

Explanation of the Code: As you can see the paging code is quite simple.

  1. The Datagrid current page is assigned a new value which will be the new page of the Datagrid. The number of pages will depend on the size of the rows that are retrieved from the database.
  2. Finally as we doing for centuries we bind the Datagrid. This is what the Datagrid will look like when the paging is enabled.

Problem with Datagrid paging with deletion:

   As you sit back to relax you find out there is some problem with the Datagrid paging. If you have many rows and you trigger the deletion operation on Datagrid each row will start to be delete one by one depending which row you clicked. The deletion will work fine until it reaches the last row to be deleted. When you delete the last row the whole hell breaks loose and you will be facing many errors. Since the paging does not know that all the rows have been deleted.

Good news is that we can fix the error easily by editing the BindData method. Here is what your BindData will look like :

public void BindData()
{

SqlCommand myCommand = new SqlCommand("SP_SELECT_PERSON",myConnection);
myCommand.CommandType = CommandType.StoredProcedure;
SqlDataAdapter myAdapter = new SqlDataAdapter(myCommand);
DataSet ds = new DataSet(); 
myAdapter.Fill(ds,"tblPerson"); 
myConnection.Open(); 
myCommand.ExecuteReader(); 
myDataGrid.DataSource = ds; myConnection.Close(); 
try 
{ 

myDataGrid.DataBind();

}
catch
{ 

myDataGrid.CurrentPageIndex = 0; 
BindData();

}

} 

   As you see we made a small change in the BindData method. Now whenever we bind the grid we and an exception is thrown we just make the page index to "0" which indicates that all the rows have been deleted.

Sorting in datagrid:

  Okay! Now lets discuss the sorting functionality available in the Datagrid control. You can sort the columns of the Datagrid by just clicking on the header of the column. I will explain you how you can make two way sorting in the Datagrid. First make the AllowSorting property of the datagrid control to "true". Lets see the sorting code:

private void Sort_DataGrid(object source, System.Web.UI.WebControls.DataGridSortCommandEventArgs e)
{

SqlCommand myCommand = new SqlCommand("SELECT * FROM tblPerson",myConnection); 
myCommand.CommandType = CommandType.Text; 
SqlDataAdapter myAdapter = new SqlDataAdapter(myCommand);
DataSet ds = new DataSet(); 
myAdapter.Fill(ds,"tblPerson"); 
DataView dv = new DataView(ds.Tables["tblPerson"]); 
// Two way sorting LOGIC 
if( (numberDiv%2) == 0 )
 dv.Sort = e.SortExpression + " " + "ASC"; 
else dv.Sort = e.SortExpression + " " + "DESC";
  numberDiv++; 
myDataGrid.DataSource = dv;
myDataGrid.DataBind();

} 

Explanation of the code: 

  1. Lets start with DataView code since this is where the new stuff begins. DataView represents the view of the table in the database. We used Dataview since it has the sort expression property. 
  2. The numberDiv is a public variable. Actually I recommend you to use ViewState instead of static variables. 
  3. The sorting is based on the even odd number sequence. 
  4. Later we bind the Datagrid to the Dataview.