Know Dot Net Article

Formatting DataGrid Columns with the CGrid Class

by Les Smith

With just a cursory look at the Windows Forms DataGrid control, it is not obvious how to create such simple controls as a Checkbox column, or even how to format, and set the width property, etc.  Even after studying the TableStyles collection, it is not straightforward to perform these tasks, and once accomplished, they should be done in such a manner as to make the code reusable.

I have written a class, named CGrid that accomplishes this task.  This article will not discuss the code, but will rather demonstrate the use of this class and show the results of using the class in several examples.

As you will note, the class deals with DataTables, but obviously could be enhanced to use DataSets, etc.  The purpose of the class is to automatically format data columns, set their width, alignment, and read only properties.  Additionally, it can create a Checkbox column and provides a method to call to check or uncheck the column and select the checked row.  It accomplishes its polymorphic behavior through the use of several Overloaded methods, which are called based on your requirements.

Example 1, simple grid formatting:

This code gets a DataTable filled by calling a data access class, which is not part of this article.  The following code fills the DataTable.

         SQL = ""
         SQL &= "SELECT DISTINCTROW Equipment.Equip_ID, "
         SQL &= "Equipment.Equip_Name, "
         SQL &= "Equipment.Available as Availability, "
         SQL &= "Equipment.Rented_Date, "
         SQL &= "Equipment.Available_Date as [Due Back], "
         SQL &= "Customers.CompanyName, Customers.ContactName, "
         SQL &= "Customers.PhoneNumber "
         SQL &= "FROM Customers RIGHT JOIN Equipment ON "
         SQL &= "Customers.CustomerID = Equipment.Renting_Company_ID "
         SQL &= "where equipment.available <> 'YES' "

         oDA.SendQuery(SQL, dt, ConnectString)

Once the DataTable is filled, the following code calls the SetTableStyle method of the CGrid class, which has previously been instantiated in the object "oCG".  The Overloaded method called below accepts three arguments.  It accepts the DataTable, the DataGrid, and a String array of formatting arguments.  The array is composed of sets of 4 fields that specify the following:
1)    Format string, acceptable to the Format function in VB.NET.
2)  String integer value specifying the width of the column.  This value corresponds to the value used in the DataGrid designer to specify the width of the column.
3)  A "T" or "F" to set the read only property of the column to true or false.  Leaving a column's read only property set to false allows the user to edit the value of the grid cell.
4)  The alignment specifier can be "L", "C", or "R" to denote Left, Center, or Right alignment of the column.

Finally, the class will make the header text look "pretty" by removing any "_" in the field names and capitalizing the first syllable of each word of the field name.

         If dt.Rows.Count > 0 Then
            Dim Formats() As String = _
               {"", "60", "T", "L", _
               "", "150", "T", "L", _
               "", "60", "T", "L", _
               "MM/dd/yyyy hh:mm tt", "130", "T", "L", _
               "MM/dd/yyyy hh:mm tt", "130", "T", "L", _
               "", "100", "T", "L", _
               "", "100", "T", "L", _
               "", "100", "T", "L"}
            oCG.SetTablesStyle(dt, Me.dbgWhoHasIt, Formats)
            oCG.BindDataTableToGrid(dt, Me.dbgWhoHasIt)
         End If

Once the table styles have been set in the grid, the call to BindDataTableToGrid will bind the DataTable to the grid.  The form shown below is the result of the code described above.

Example 2, creating a Checkbox column with formatting:

This example will not only format the data, using different format strings than the previous example, but it will also create a checkbox column. The following code creates the table styles as shown before, but calls a different overloaded method to create the checkbox column.

Since this grid is constantly refreshed, I reset the DataTable and reset the grid with the following two lines of code.

dtEquip = New DataTable("dt")
Me.dbgEquipment.SetDataBinding(dtEquip, "")

Next, I generate the format string array to specify the formatting of the respective columns.

         Dim Formats() As String =
            {"", "60", "T", "L", _
             "", "150", "T", "L", _
             "", "50", "T", "L", _
             "$#,##0.00", "60", "T", "R", _
             "$#,##0.00", "60", "T", "R", _
             "$#,##0.00", "60", "T", "R", _
             "$#,##0.00", "60", "T", "R", _
             "$#,##0.00", "60", "T", "R", _
             "", "60", "T", "R", _
             "$#,##0.00", "60", "T", "R", _
             "0", "60", "T", "R", _
             "", "60", "T", "R"}

Finally, I call three methods of the CGrid class, after successfully filling the DataTable.

         If oDA.SendQuery(SQL, dtEquip, ConnectString) > 0 Then
            oCG.SetTablesStyle("RentMe", dtEquip, _
            Me.dbgEquipment, Formats)
            oCG.BindDataTableToGrid(dtEquip, Me.dbgEquipment)
            oCG.DisableAddNew(Me.dbgEquipment, Me)
         End If

The third call, to DisableAddNew, is a method, which removes the AddNew (bottom row preceded by *) row of the grid and thus prevents the user from attempting to add a new row to the grid manually. The result of executing the calls to CGrid, described above, fills the grid as shown below.

Formatted Grid Image.

The code for CGrid is too lengthy to be shown in its entirety in this article, but it can be downloaded for free by clicking here.

I will take the time to show you the most powerful SetTableStyle method. This one creates a checkbox column and formats the columns. The code is commented and straightforward, so I will not take time to discuss it further.

   Public Overloads Sub SetTablesStyle(ByVal AddCkBox As String, _
      ByRef dt As DataTable, _
      ByRef dg As DataGrid, _
      ByVal ParamArray Formats() As String)
      ' This method allows a checkbox and optional formatting
      ' param array.  If AddCkBox.Length >0 it will add
      ' a new first column as a check box
      ' if paramarray is supplied it is an array of
      ' four items of "format,width,ReadOnly,alignment,format,width,..."
      ' where format="$#,##0.00"|"MM/dd/yyyy"|etc
      '       width = "90", readonly =T|F
      ' with every column having at least a pair of placeholders,
      ' including optional checkbox column
      Dim i As Integer
      Dim ts As New DataGridTableStyle()
      Dim myDataCol As New DataGridBoolColumn() ' checkbox column

      Try
         ' This call creates the checkbox column and adds to dt
         If AddCkBox.Length > 0 Then
            AddCheckBoxColumn(dt, AddCkBox)
         End If

         dg.TableStyles.Clear()
         ts.GridColumnStyles.Clear()

         ' map the table style to the dt
         ts.MappingName = "dt"

         ' set the header and mapping for the ckbox column
         If AddCkBox.Length > 0 Then
            With myDataCol
               .HeaderText = dt.Columns.Item(AddCkBox).ColumnName
               .MappingName = dt.Columns.Item(AddCkBox).ColumnName
               .FalseValue = "false"
               .TrueValue = "true"
               .AllowNull = False
               .ReadOnly = True
            End With
            ' add the column style for the ckbox col
            ts.GridColumnStyles.Add(myDataCol)
         End If

         ' for the rest of the rows, make text box columns
         Dim j As Integer

         For i = 0 To dt.Columns.Count - 1
            If Not dt.Columns(i).ColumnName = AddCkBox Then
               Dim dgtbc As New DataGridTextBoxColumn()
               dgtbc.HeaderText = _
                  FixGridColumnCaption(dt.Columns.Item(i).ColumnName)
               dgtbc.MappingName = dt.Columns.Item(i).ColumnName
               If UBound(Formats) > 0 Then
                  j = i * 4
                  dgtbc.Format = Formats(j)
                  dgtbc.Width = Val(Formats(j + 1))
                  dgtbc.ReadOnly = IIf(Formats(j + 2) = "T", _
                    True, False)
                  If Formats(j + 3) = "C" Then
                     dgtbc.Alignment = HorizontalAlignment.Center
                  ElseIf Formats(j + 3) = "L" Then
                     dgtbc.Alignment = HorizontalAlignment.Left
                  ElseIf Formats(j + 3) = "R" Then
                     dgtbc.Alignment = HorizontalAlignment.Right
                  End If
                  ts.GridColumnStyles.Add(dgtbc)
               End If
            End If
         Next

         ' add the table style to the grid
         dg.TableStyles.Add(ts)

      Catch ex As System.Exception
         StructuredErrorHandler(ex)
      End Try
   End Sub

The following method is called by SetTableStyle to create the textbox.

   Public Sub AddCheckBoxColumn(ByRef dt As DataTable, _
      ByVal CName As String)
      ' adds the column for the checkbox to the dt and set to false
      ' the column will be placed at the end of the datatable,
      ' but it will appear in the grid in the first column
      Try
         dt.Columns.Add(CName)
         Dim i As Integer
         For i = 0 To dt.Rows.Count - 1
            dt.Rows(i).Item(CName) = "false"
         Next
      Catch ex As System.Exception
         StructuredErrorHandler(ex)
      End Try
   End Sub

In conclusion, I have used this class dozens of times and always know that my grids will have a finished, professional look and feel, if I take the time to create the format string array. It is constantly being enhanced, and although it may not have all of the functionality of an expensive third party control; it works, and you have the code for the cost of a free download. Hope it saves you some time!

Back to Top

Download Code for this article(Includes VB and C# Classes)