DataGridView setup header text using SQL-Server

아티클
1/17/2024

Introduction

A common method for setting header text property of a DataGridView is done one of two ways when not working with TableAdapter method to populate a DataGridView. The first is to assign the DataSource of the DataGridView to a DataTable or a List then use something like a dictionary where the key is the underlying field name and the value is the header text, iterate the dictionary to assign header text. The second method is to create columns for the DataGridView in the designer, assign header text and a field to the DataPropery.

This article examines an alternate method for providing header text to column in a DataGridView using a single property for a column in a table within a SQL-Server database.

Implementation

Open SSMS (SQL-Server Management Studio), select the database, select the table.
In the Object Explorer right click on the table and select design.
Select a column.
Select the property Description and enter a title for what should be seen in a DataGridView. Do not set this property for columns which will not be displayed such as primary or foreign key columns.
Save your changes.

The next step will be to query the descriptions for the table above. THe proper method to write this query is in SSMS as shown below. In the query below a parameter is used so not to hard code the table name. In our code this translates to a parameter for the SqlClientCommand.

DECLARE @TableName NVARCHAR(50) = 'Customers';
 
SELECT  COLUMN_NAME AS ColumnName ,
        ORDINAL_POSITION AS  Position ,
        prop.value AS  [Description]
FROM    INFORMATION_SCHEMA.TABLES AS tbl
        INNER JOIN INFORMATION_SCHEMA.COLUMNS AS  col ON  col.TABLE_NAME = tbl.TABLE_NAME
        INNER JOIN sys.columns  AS  sc ON  sc.object_id = OBJECT_ID(tbl.TABLE_SCHEMA
                                                              + '.'
                                                              + tbl.TABLE_NAME)
                                        AND sc.name = col.COLUMN_NAME
        LEFT JOIN sys.extended_properties prop ON  prop.major_id = sc.object_id
                                                  AND prop.minor_id = sc.column_id
                                                  AND prop.name = 'MS_Description'
WHERE   tbl.TABLE_NAME = @TableName AND prop.value  IS NOT NULL
ORDER BY  col.ORDINAL_POSITION

To translate this to VB.NET a return type is needed as per below where Name property is the name of the column, Ordinal is the ordinal position in the physical table, Description if the description set in SQL-Server table/column and Visible is read only property which if there is no description this will be used to hide a column in a DataGridView.

Public Class  DataColumn
    ''' <summary>
    ''' Name of column
    ''' </summary>
    ''' <returns></returns>
    Public Property  Name() As  String
    ''' <summary>
    ''' Ordinal position of column
    ''' </summary>
    ''' <returns></returns>
    Public Property  Ordinal() As  Integer
    ''' <summary>
    ''' Description of column
    ''' </summary>
    ''' <returns></returns>
    ''' <remarks>
    ''' May be NULL
    ''' </remarks>
    Public Property  Description() As  String
    ''' <summary>
    ''' Determines if this column should be visible in the user interface.
    ''' </summary>
    ''' <returns></returns>
    Public ReadOnly  Property Visible() As Boolean
        Get
            Return Description IsNot Nothing
        End Get
    End Property
End Class

Using the SQL presented above here is how it's implemented, pass in an existing table name and return a list of DataColumn.

Public Function  ColumnDetails(pTableName As String) As  List(Of DataColumn)
    mHasException = False
 
    Dim columnData = New List(Of DataColumn)
    Dim selectStatement =
    "SELECT  COLUMN_NAME AS ColumnName, ORDINAL_POSITION AS Postion,prop.value AS [Description] " &
    "FROM INFORMATION_SCHEMA.TABLES AS tbl " &
    "INNER JOIN INFORMATION_SCHEMA.COLUMNS AS col ON col.TABLE_NAME = tbl.TABLE_NAME " &
    "INNER JOIN sys.columns AS sc ON sc.object_id = OBJECT_ID(tbl.TABLE_SCHEMA + '.' + tbl.TABLE_NAME) AND sc.name = col.COLUMN_NAME " &
    "LEFT JOIN sys.extended_properties prop ON prop.major_id = sc.object_id AND prop.minor_id = sc.column_id AND prop.name = 'MS_Description' " &
    "WHERE tbl.TABLE_NAME = @TableName " &
    "ORDER BY col.ORDINAL_POSITION"
 
    Try
        Using cn As  New SqlConnection With {.ConnectionString = ConnectionString}
            Using cmd As  New SqlCommand With {.Connection = cn}
 
                cmd.CommandText = selectStatement
                cmd.Parameters.AddWithValue("@TableName", pTableName)
 
                cn.Open()
 
                Dim reader = cmd.ExecuteReader()
                While reader.Read()
                    columnData.Add(New DataColumn() With {.Name = reader.GetString(0), .Ordinal = reader.GetInt32(1), .Description = reader.GetStringSafe("Description")})
                End While
 
            End Using
        End Using
    Catch ex As Exception
        mHasException = True
        mLastException = ex
    End Try
 
    Return columnData
 
End Function

Once the above method is called a call to return data as shown below where in this case it's a customer table.

Public Function  GetCustomers() As  DataTable
    mHasException = False
 
    Dim dtCustomers = New DataTable
    Dim selectStatement = "SELECT CustomerIdentifier, CompanyName, ContactName ,Address," &
                            "City,Region,PostalCode,Country,Phone,Fax,ContactTypeIdentifier,ModifiedDate " &
                            "FROM dbo.Customers"
 
    Try
        Using cn As  New SqlConnection With {.ConnectionString = ConnectionString}
            Using cmd As  New SqlCommand With {.Connection = cn}
                cmd.CommandText = selectStatement
                cn.Open()
                dtCustomers.Load(cmd.ExecuteReader())
            End Using
        End Using
    Catch ex As Exception
        mHasException = True
        mLastException = ex
    End Try
 
    Return dtCustomers
 
End Function

Pulling this together, a DataGridView is placed on a form at design time, no properties are set. In form shown event load the DataTable, in this case from GetCustomers then iterate the column descriptions to set column header text and for columns in the List(Of DataColumn) which the Visible property is false hide those columns in the DataGridView. The code in the sole button permits accessing the primary key in tangent with the company name so it can be validated e.g. with SSMS open visually inspect the record.

Public Class  Form1
    Private ReadOnly  _bsCustomers As  New BindingSource
    Private ReadOnly  _operations As  DataOperations = New  DataOperations
    ''' <summary>
    ''' - Populate a DataGridView with a single table.
    ''' - Set header text using information returned from parsing each columns description property
    ''' * Note in this code there is zero check for if the column exists in the DataGridView while
    '''   in form3 a check is done to see if the column exists in the underlying DataTable. 
    ''' </summary>
    ''' <param name="sender"></param>
    ''' <param name="e"></param>
    Private Sub  Form1_Shown(sender As Object, e As  EventArgs) Handles  Me.Shown
 
        ' get customer data into DataTable
        _bsCustomers.DataSource = _operations.GetCustomers()
        DataGridView1.DataSource = _bsCustomers
 
 
        ' get column details for customers table
        Dim results = _operations.ColumnDetails("Customers")
 
        If _operations.IsSuccessFul Then
            ' set column header text and hide column with no description
            For Each  dataColumn As  DataColumn In  results
                If dataColumn.Visible Then
                    DataGridView1.Columns(dataColumn.Name).HeaderText = dataColumn.Description
                Else
                    DataGridView1.Columns(dataColumn.Name).Visible = False
                End If
            Next
 
            DataGridView1.ExpandColumns
        Else
            MessageBox.Show(_operations.LastExceptionMessage)
        End If
    End Sub
    ''' <summary>
    ''' Show ability to get hidden and visible field values
    ''' </summary>
    ''' <param name="sender"></param>
    ''' <param name="e"></param>
    Private Sub  cmdCurrentRow_Click(sender As Object, e As  EventArgs) Handles  cmdCurrentRow.Click
        '
        ' Cast Current property to a DataRow which provides access to properties of current row
        ' in the DataGridView.
        '
        If _bsCustomers.Current IsNot Nothing Then
            Dim row = CType(_bsCustomers.Current, DataRowView).Row
            MessageBox.Show($"Primary key: {row.Field(Of Integer)("CustomerIdentifier")} Company: {row.Field(Of String)("CompanyName")}")
        End If
    End Sub
End Class

If you decided to place a DataGridView on a form, in the DataGridView designer add columns and set the DataProperty and do nothing else with a slight modification to the latter code the header text can be set.

For each column in the DataGridView prefix the column name with col (or your choice of a prefix) followed by the field name. For example, for the field CustomerIdentifier the DataGridView column name would be colCustomerIdentifier. In the For-Each add the prefix as shown below, the rest of the code remains as is from the code block above.

For Each  dataColumn As  DataColumn In  results
    If dataColumn.Visible Then
        DataGridView1.Columns($"col{dataColumn.Name}").HeaderText = dataColumn.Description
    Else
        DataGridView1.Columns($"col{dataColumn.Name}").Visible = False
    End If
Next

Caveats

In the above examples all columns were used and matched againsts the List(Of DataColumn), most likely this is not always the case, suppose one or two fields are left out and the above logic is used, what will happen? A runtime exception will be thrown as there is no assertion to determine if a column in the DataGridView exists.

To circumvent this a check is needed to assert a column in the List(Of DataColumn) exists in the DataGridView. In the following example an assertion is performed to determine if a column exists in the DataGridView from in the List(Of DataColumn).

For Each  dataColumn As  DataColumn In  customerResults
    If DataGridView1.Columns.Contains(dataColumn.Name)  Then
        If dataColumn.Visible Then
 
            DataGridView1.Columns(dataColumn.Name).HeaderText = dataColumn.Description
        Else
            DataGridView1.Columns(dataColumn.Name).Visible = False
        End If
    End If
Next

The next thing to consider is joining one or more tables to the primary table. In this case there are two tables, Customers and ContactType.

Public Function  GetCustomersAndContactTypes() As DataTable
    mHasException = False
 
    Dim dtCustomers = New DataTable
 
    Dim selectStatement = "SELECT  C.CustomerIdentifier , C.CompanyName , CT.ContactTitle , C.ContactName , C.[Address] , " &
                            "C.City , C.Region , C.PostalCode , C.Country , C.ContactTypeIdentifier , C.ModifiedDate " &
                            "FROM Customers AS C " &
                            "INNER JOIN ContactType AS CT ON C.ContactTypeIdentifier = CT.ContactTypeIdentifier; "
    Try
        Using cn As  New SqlConnection With {.ConnectionString = ConnectionString}
            Using cmd As  New SqlCommand With {.Connection = cn}
                cmd.CommandText = selectStatement
                cn.Open()
                dtCustomers.Load(cmd.ExecuteReader())
            End Using
        End Using
    Catch ex As Exception
        mHasException = True
        mLastException = ex
    End Try
 
    Return dtCustomers
 
End Function

The solution is to get column details for both tables and iterate them as done with a single table as shown below.

' get customer data into DataTable
_bsCustomers.DataSource = _operations.GetCustomersAndContactTypes()
DataGridView1.DataSource = _bsCustomers
 
 
' get column details for customers table
Dim customerResults = _operations.ColumnDetails("Customers")
Dim contactResults = _operations.ColumnDetails("ContactType")
 
If _operations.IsSuccessFul Then
    ' set column header text and hide column with no description
    For Each  dataColumn As  DataColumn In  customerResults
        If DataGridView1.Columns.Contains(dataColumn.Name)  Then
            If dataColumn.Visible Then
 
                DataGridView1.Columns(dataColumn.Name).HeaderText = dataColumn.Description
            Else
                DataGridView1.Columns(dataColumn.Name).Visible = False
            End If
        End If
    Next
 
    For Each  dataColumn As  DataColumn In  contactResults
        If DataGridView1.Columns.Contains(dataColumn.Name)  Then
            If dataColumn.Visible Then
                DataGridView1.Columns(dataColumn.Name).HeaderText = dataColumn.Description
            Else
                DataGridView1.Columns(dataColumn.Name).Visible = False
            End If
        End If
    Next
 
    DataGridView1.ExpandColumns
Else
    MessageBox.Show(_operations.LastExceptionMessage)
End If

Summary

What has been presented, a property of a column in a SQL-Server database table that if not used for other purposes allows header text to be set from the database. The downside is this is that the property is being used for something it was not intended for. Doing so means if you decide to document columns to create reports you can’t do so. This means the methods presented will not work for everyone yet if you never intend to document columns this is an alternate to setting header text and be consistent on all applications that use these tables setup via the Description property.

Running the code samples

There is one class project for data operations and a Windows Form project. The Windows form project has three forms where Form3 is set as default. Form3 has all the proper assertions while Form1 and Form2 do not. Form2 difference from Form1 in that Form1 has no predefined columns in the DataGridView while Form2 does have predefined columns in the DataGridView,

Requires

Microsoft Visual Studio 2015 or higher.
Microsoft SQL-Server, at minimum the Express edition
SSMS (SQL-Server Management Studio).
Before running the code samples, execute the SQL script in the class project script.sql to create the database, table and populate the tables.

Source code

https://github.com/karenpayneoregon/DataGridViewColumnHeaderFromSqlServer

다음을 통해 공유