Displaying 2 Field Names in 1 Column of DataGrid

In the previous articles, I used the Authors table from Pubs database to display data in the datagrid. As you can see the screenshots, the author's firstname (au_fname) and author's lastname (au_lname) are displayed in the different columns. Perhaps, you would be wondering how to combine both au_fname and au_lname fields into 1 column. Now, I will demonstrate two ways to do that. First method using new combined DataColumn, and second method using Data Binding in the ItemDataBound event. To avoid this article from becoming lengthy, I combine both methods into 1 solution. [As you will notice that there are two same columns, which have author's full names.]


In the aspx page,

<asp:datagrid id="dgrdAuthors" onItemDataBound="dgrdAuthors_ItemDataBound" .......>
<Columns>
     <asp:BoundColumn DataField="au_id" HeaderText="Author ID"/>
     <asp:BoundColumn DataField="FullName" HeaderText="Name (DataColumn)"/>;      <-- First method

      <asp:TemplateColumn HeaderText="Name(DataItem)">      <-- Second method
          <ItemTemplate>
               <asp:label id="lblFullName" runat="server"/>
          </ItemTemplate>
     </asp:TemplateColumn>

     <asp:BoundColumn DataField="phone" HeaderText="Phone"/>
     <asp:BoundColumn DataField="city" HeaderText="City"/>
     <asp:BoundColumn DataField="state" HeaderText="State"/>
</Columns>
</asp:datagrid>

In my DataGrid, there are total number of 6 columns, which 2 of columns are the same (Name(DataColumn) and Name(DataItem) columns). As you can notice, one of the columns used is TemplateColumn. This column is customizable and provide more flexibility in formatting the appearance of the displayed data. The purpose of using this TemplateColumn here is I want to customize the data (combining both au_fname and au_lname) at run time.


Definitions : TemplateColumn class | BoundColumn Class | ItemDataBound Event

See Also : Customizing DataList Items at Run Time [MSDN]


In the <script> tag or code behind ,

<script>
protected void Page_Load(Object sender, EventArgs e)
{
     if(!IsPostBack)
     {


          SqlConnection conPubs = new SqlConnection("Server=DOTNET;uid=sa;database=pubs");
          SqlDataAdapter daSelectAuthors = new SqlDataAdapter("SELECT au_id, au_fname, au_lname, phone,city,state FROM Authors",conPubs);
               DataSet dsAuthors = new DataSet();

               conPubs.Open();
               daSelectAuthors.Fill(dsAuthors,"Authors");
               conPubs.Close();

               DataColumn dcolFullName = new DataColumn();
               dcolFullName.ColumnName = "FullName";
               dcolFullName.DataType = System.Type.GetType("System.String");
               dcolFullName.Expression = "au_fname + ' ' + au_lname";

               dsAuthors.Tables["Authors"].Columns.Add(dcolFullName); // add new column to the datatable

               dgrdAuthors.DataSource = dsAuthors.Tables["Authors"].DefaultView;
               dgrdAuthors.DataBind();
          }
     }
}


private void dgrdAuthors_ItemDataBound(Object sender, DataGridItemEventArgs e)
{
     if(e.Item.ItemType == ListItemType.Item || e.Item.ItemType == ListItemType.AlternatingItem)
     {
          Label lblFullName = (Label)e.Item.FindControl("lblFullName");

          lblFullName.Text = (((DataRowView)e.Item.DataItem)["au_fname"]).ToString() + " " +
                                          (((DataRowView)e.Item.DataItem)["au_lname"]).ToString();
     }


}
</script>

For the first method, I have created a new datacolumn, which has FullName column name and string data type. The Expression property of DataColumn can be used for calculating values from different data fields, combining columns and so on. At here, I'm combining the au_fname and au_lname fields as 1 field (column). After that, I add this new column to the DataColumnCollection of "Authors" DataTable, and then bind this DataSet to DataGrid. Eventually, the DataSet has 7 fields.


In the ItemDataBound event, I check the data rows using the If..... Statement, and for each data row, I use the FindControl() to search and locate the Label server control that is placed in the TemplateColumn there by its ID. Of course, I have to cast the returned result of the FindControl() to Label type. Let us proceed to next line, which will be a bit complicated.


The e.Item.DataItem is an object that represents the data item in the DataGrid. It can only be placed in the data rows like Item, AlternatingItem, SelectedItem and EditedItem. Whereas the DataRowView represents each displayed data row. The ((DataRowView)e.Item.DataItem)["au_fname"] is an explicit casting. Alternatively, you can change to the DataBinder.Eval(e.Item.DataItem,"au_fname"), which is the equivalent to the ((DataRowView)e.Item.DataItem)["au_fname"]. The reason I use the explicit casting is because it offers better performance than DataBinder.Eval() in avoiding the cost of reflection.

TRY it and GET it !


Definitions : DataColumn.ColumnName Property | DataColumn.DataType Property | DataColumn.Expression Property | Type Class | DataColumnCollection.Add() | DataGridItem.DataItem | DataRowView Class

See Also : Improving ASP.NET Performance [MSDN]

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 -

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

Merging Cells in DataGrid

Merging Cells in DataGrid 

By default, all the data bound to the DataGrid are displayed in the tabular form, which is like an ordinary table. But, however, sometimes we neeed to adjust or change the appearance of the grid so that the displaying data would be presented nicely and clearly. And here is the reason this article comes about. I would like to show you how to merge the cells (columns in a row) in the DataGrid.

As what I said in my first article before, the onItemCreated event is the great place for us to edit and change the appearance of the datagrid. ( not to forget about the ItemDataBound event, too).

In the example below, I use the Pubs as my database, retrieving au_id, au_fname and au_lname fields frome the Authors table. Then, I would like to merge the 2nd and 3rd cells at the header, and all cells at the footer there.

protected void dgrdAuthors_ItemCreated(Object sender, DataGridItemEventArgs e)
{
     // if it is header
     if(e.Item.ItemType == ListItemType.Header)
     {
          e.Item.Cells.RemoveAt(2);      // Removing 3rd cell
          e.Item.Cells[1].ColumnSpan=2;      // Merging 2nd and 3rd cells
          e.Item.Cells[0].Text = "Author ID";
          e.Item.Cells[1].Text = "Author Full Names";
     }
     else if(e.Item.ItemType == ListItemType.Footer)
     {
          e.Item.Cells.RemoveAt(0);      // Removing the first cell
          e.Item.Cells.RemoveAt(0);      // Removing the second cell
          e.Item.Cells[0].HorizontalAlign = HorizontalAlign.Center;
          e.Item.Cells[0].Text = " End of Records";
     }
}

Header part

In the code above, first, I check whether the current row created is Header of the DataGrid. If it is header, then I remove the 3rd cell (which is au_lname field) of the row by using the RemoveAt(). This method will remove the particular cell at the specified position. I call RemoveAt(2), which removes the 3rd cell (always start with 0). Then, I merge the column 2 and 3 (au_fname and au_lname) by assigning the ColumnSpan property to 2, which takes up 2 cells in the row.

As a result, the header eventually has 2 cells. 1 is cell for Author ID field, and another is merged cell for the Author Full Name. Then, I assign the text to the appropriate cells.


Footer part

At the footer, I would like merge all the 3 cells into 1 cell. Hence, I remove the first cell by calling the RemoveAt(0). And you would wondering why I call this method twice. The reason is, when I remove the first cell of the 3 cells, the second cell becomes the first cell. Therefore, I have to call the RemoveAt(0) once again to remove the second cell, which is already become the first cell. By now, I can merge all the cells by assigning 3 to ColumnSpan property. Also, I set the HorizontalAlign to center so that the text would be displayed at the center.



Definitions: ColumnSpan | RemoveAt()



~~ Happy Programming ~~~

Changing the Row's Background Color in DataGrid When Hovering

Binding data source to the DataGrid data control is simple, but adding the visual-effect on the DataGrid isn't hard too. Here, I show you the code that changing background color of the particular row when the users move the mouse pointer over the row. Since the DataGrid data control does not have the properties to let us specify the rows' backgroundcolor, thus, we have add the onmouseover and onmouseout attributes when it (DataGrid) is created (OnItemCreated event) or its data are bound to the rows.(OnDataBound). First, add the onItemCreated attribute to the DataGrid data control.

<asp:datagrid id="dgrdAuthors" onItemCreated="dgrdAuthors_ItemCreated" ........ />

then, in the <script> tag or in the code-behind, add this event (I put it in the script tag in my example). As what I have said, it can be done by putting the code in the ItemDataBound event, so just change the event name instead.

<script>
private void dgrdAuthors_ItemCreated(Object sender, DataGridItemEventArgs e)
{
     if(e.Item.ItemType == ListItemType.Item || e.Item.ItemType == ListItemType.AlternatingItem)
     {
          e.Item.Attributes.Add("onmouseover",
                              "this.style.backgroundColor='#f7f7f7';this.style.cursor='hand'");
          e.Item.Attributes.Add("onmouseout","this.style.backgroundColor='#ffffff'");
     }
}
</script>

How does this code work? The ItemCreated event is fired when each row is created in the datagrid, just before the data is bound to the row. Therefore, it is commonly used to set the appearance of the datagrid's rows here. Whereas, the ItemDataBound event is fired when each data from the data source bound to the row.

The e.Item simply represents the current data row of the datagrid. And, a datagrid consists of header, data items and footer, which are under the ListItemType enumerations. The Item ItemType represents the odd-number rows in the datagrid, and the AlternatingItem ItemType represents the even-number rows.

In the code above, the ItemCreated event will be fired when each row is created. Since we do not want add the visual effect on the header and footer, we check the ItemType whether it is Item or AlternatingItem. If it is, then we add the current row with the onmouseover and onmouseout attributes, which will apply the specified styles to it.


Definitions : OnItemCreated event | OnItemDataBound event | DataGrid's ItemTypes



Alternative way :

Apart from the doing it in the ItemCreated and ItemDataBound events, there is another way to accomplish the job above. In the Page_Load() event, add this

protected void Page_Load()
{
     if(!IsPostBack)
     {
          foreach(DataGridItem item in dgrdAuthors.Items)
          {
               item.Attributes["onmouseover"] = "this.style.backgroundColor='#f7f7f7'";
               item.Attributes["onmouseout"] = "this.style.backgroundColor='#ffffff'";
          }
     }
}

Simple, The DataGridItem represents the data items in the datagrid. Therefore, I add the onmouseover and onmouseout attributes to every data item in the grid.


Definition: DataGridItem


Okay, I have done my first article regarding to the DataGrid Data Control. And soon, I will write more articles, basically more on the data controls in asp.net. :)

Welcome to My Blog

Hello, in very first place, thanks for visiting this blog. Basically, I will write my own articles regarding to the ASP.NET ( more on Data Controls). Of course, if my information given incorrect, feel free to drop comments here.