Showing Number of Records at Footer In DataGrid (Pt. 1)

Showing number of records in DataGrid 

When displaying the data in the DataGrid, you likely want to show the total records that are listed in the grid. When there is no data available, the ASP.NET will generate a blank datagrid that has column names but does not have any data inside, which isn't what we wanted.

In this article, I will show you how to display the "XX records found" at the footer, and show "No record found" when there is no data in the datagrid. Same thing, I use the Pub database and Author table in my following example. In this example, I will use SqlDataReader and DataSet to accomplish this job.

SqlDataReader

First, let me talk about the introduction of SqlDataReader. The SqlDataReader, which implements the IDataReader interface, is connected, read-only and forward-only (also called firehose cursor of ADO.NET). It is a stream of data that is returned from query of database. It can read one row at a time from database and can only move forward. Those rows retrieved from database by SqlDataReader can be read, but they cannot be edited. However, the SqlDataReader has to maintain its connection to the data source (stay connected) and it is partially bindable. (It is bindable for only one time, unless you resetting the cursor to the beginning)

Back to the matter, here is my code, which can be placed in the script tag or in the code-behind of the ASP.NET page. For me, I put it in the script tag.

<script>
protected void Page_Load(Object sender, EventArgs e)
{
     if(!IsPostBack)
     {
          using(SqlConnection conPubs = new SqlConnection())
          {
               conPubs.ConnectionString = "Server=DOTNET;uid=sa;database=pubs";
               SqlCommand cmdSelectAuthors = new SqlCommand("SELECT au_id, au_fname, au_lname, phone FROM Authors",conPubs);

               try
               {
                    conPubs.Open();
                    dgrdAuthors.DataSource = cmdSelectAuthors.ExecuteReader();
                    dgrdAuthors.DataBind();
               }
               catch(SqlException ex)
               {
                    Response.Write(ex.Message);
               }
               finally
               {
                    if(conPubs.State == ConnectionState.Open)
                         conPubs.Close();
               }
          }
     }
}
</script>

At the very first line of the Page_Load event, I use the if statement the check whether it is IsPostBack. Normally, we only need to bind the data from data source to DataGrid once - first time the page is loaded, but not everytime the page is requested. It could reduce the roundtrip to the database server. In the following line, I use the using block, that is, when the the code exits the using block, the SqlConnection object will be disposed automatically. [However, some saying that it is inefficient to wait until the GC to dispose the SqlConnection object. Erm.. it depends on you, if you dont use this method, then call the Close() method manually. * Close the connection as soon as possible. ]

Then, I assign the ConnectionString property with the correct value. Next, I retrieve 4 fields in the Author table, open the connection and run the bind the a data reader which is returned by result of ExecuteReader() to the datagrid.

Definitions : The "Using" Statement in C# | using Statement [MSDN] | SqlDataReader class



In the ItemDataBound event,

int iCount = 0;      // count the number of items
protected void dgrdAuthors_ItemDataBound(Object sender, DataGridItemEventArgs e)
{
     if(e.Item.ItemType == ListItemType.Item || e.Item.ItemType == ListItemType.AlternatingItem)
     {
           // .... do other things here
          iCount++;
     }
     else if(e.Item.ItemType == ListItemType.Footer)
     {
          e.Item.Cells.RemoveAt(0);
          e.Item.Cells.RemoveAt(0);
          e.Item.Cells.RemoveAt(0);
          e.Item.Cells[0].ColumnsSpan= 4;

          if(iCount!=0)
          {
               e.Item.Cells[0].HorizontalAlign = HorizontalAlign.Right;
               e.Item.Cells[0].Text = String.Format("{0} records found",iCount.ToString());
          }
          else
          {
               e.Item.Cells[0].HorizontalAlign = HorizontalAlign.Center;
               e.Item.Cells[0].Text = "No record found";
          }
     }
}

Repeating what the ItemDataBound event is, the ItemDataBound event is fired when the data is bound to each row of the datagrid. Therefore, this event is the best place for us to count the number of data in the grid. In the code above, I declare an integer-type variable with value of 0. Its value increments to 1 whenever a data is bound to the row, so that we could know how many data are actually in the datagrid. Remember, I exclude the header and footer, which are not counted in.

At the footer, I merge the 4 cells in the row by calling the removing the cells 3 times before merging it. For more information about merging cells, you can read my previous article Merging Cells in DataGrid. Then, I check whether there is any incrementation of value of iCount. If yes, that means there is data in the DataGrid. I set to the HorizontalAlign to Right, which will display the "XX records found" on the right hand side. Otherwise, it will be showing the "No record found" at the center of the footer row.

Since this article is quite lengthy, I will separate the one using DataSet in part 2 of this article.


Definitions : OnItemDataBound event | DataGrid's ItemTypes