Showing number of records at footer in DataGrid (Pt. 2)

This is my second part of the "Showing number of records at footer in DataGrid". In the first part, I showed how to do it when you using SqlDataReader. And now, I show how can it be done if you are using DataSet. Compared to the SqlDataReader, it is much easier for you using DataSet.

DataSet

DataSet object is always a disconnected recordset. It is the core of the ADO.NET disconnected architecture. Unlike DataReader, it is not connected to the database. It is filled by the SqlDataAdapter (a bridge between the connected and disconnected objects), which associates with the Database. Behind the scenes, a SqlDataReader is created implicitly and the rowset is retrieved one row at a time in succession and sent to the DataSet. Once all the data is in the DataSet, the implicit SqlDataReader is destroyed and the SqlConnection is closed.

DataSet is fully navigable. It can traverse forward and backward, which unlike the SqlDataReader that moves forward only. Also, the DataSet is flexible since the DataTables in the it can be sorted, filtered and searched. Also, it is fully bindable. it can be bound to the data source of data controls every time. The DataSet can reduce the roundtrip to the database server, but, however, it increases the memory footprint in where it is stored. If you are retrieving 1 million of records, these records will be stored in the memory, which occupies the system resources.

Therefore, you have to be careful which one you are using. If you just merely want to view or display the data, always stick with the SqlDataReader ; if you would like to sort, filter , search the retrieved data, you can consider the DataSet. Nevertheless, there are some situations where you must use DataSet. For instance, when doing paging in DataGrid, the DataSet is needed because it requires your data source implements ICollection interface.


See Also : DataSet Vs. DataReader [sitepoint] | Contrasting the ADO.NET DataReader and DataSet [MSDN Magazine]

Definition : ICollection interface [MSDN]

<script>
DataSet dsAuthors = new DataSet();

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


               conPubs.Open();
               daSelectAuthors.Fill(dsAuthors,"Authors");
               dgrdAuthors.DataSource = dsAuthors.Tables["Authors"].DefaultView;
               dgrdAuthors.DataBind();
          }
     }
}
</script>

Here, I am not going to explain the code line by line since I explained it in my previous article. Instead of using the SqlCommand, I use the SqlDataAdapter, which in turn will fill the DataSet with the DataTable named "Authors". After that, I bind the DataTable to the data source of the DataGrid. Note that I declare the DataSet outside of the Page_Load() event because it can be used in the ItemDataBound event later. Simple ?


In the ItemDataBound event,

protected void dgrdAuthors_ItemDataBound(Object sender, DataGridItemEventArgs e)
{
     if(e.Item.ItemType == ListItemType.Footer)
     {
          TableCellCollection cells = e.Item.Cells;
          int iCount = dsAuthors.Tables["Authors"].Rows.Count; // obtaining total rows in the data table.

          cells.RemoveAt(0);
          cells.RemoveAt(0);
          cells.RemoveAt(0);
          cells[0].ColumnsSpan= 4;

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

Is this code looked simpler? Yes, we do not need to count the number of data in the datagrid one by one because we are able to the get the total rows in the rows.count property of the "Authors" DataTable of the DataSet. Instead of calling the e.Item.Cells everytime, I assign it to the variable cells with type of TableCellCollection. Therefore, it is more readable with less typing errors. For the subsequent codes, it is similar to what the previous article does.



Definitions : TableCellCollection [MSDN]


- The End -