P r o g r a m a t i c a l l y    M a p p i n g    D a t a T a b l e s    t o    D a t a G r i d T a b l e S t y l e s

By Michael McIntyre

mikemc@getdotnetcode.com

How can you map a DataTable from a DataSet created at runtime to a DataGridTableStyle?  This article provides solution code and an explanation of how it works.

To map a DataTable to a DataGridTableStyle you must have the name of the DataTable and the name of the DataTable’s DataColumns.  Examine the code below.  Can you see explain how the DataTable name and its DataColumns are initialized at runtime?

Private Sub LoadCustomers()

        ' Create an OleDbConnection object.

        Dim NwConnection As OleDbConnection = _

        New OleDbConnection("Provider=SQLOLEDB;Data Source=localhost;" & _

        "Integrated Security=SSPI;Initial Catalog=northwind")

        ' REFERENCE 1

        ' Create an OleDbCommand object.

        Dim customersSelectCommand As OleDbCommand = New OleDbCommand( _

        "SELECT CustomerID, CompanyName, Address, Phone FROM Customers", NwConnection)

        customersSelectCommand.CommandTimeout = 30

        ' Create an OleDbDataAdatper object.

        Dim customersDataAdapter As OleDbDataAdapter = New OleDbDataAdapter()

        customersDataAdapter.SelectCommand = customersSelectCommand

        ' Create a DataSet.

        Dim customersDataSet As DataSet = New DataSet()

        ' REFERENCE 2

        ' Add a DataTable named Customers to the customerDataSet and

        ' fill the DataTable with rows from the Northwind database's

        ' Customers table.

        customersDataAdapter.Fill(customersDataSet, "Customers")

        ' Set the DataSource for CustomersDataGrid

        ' to the customersDataSet.

        Me.CustomersDataGrid.DataSource = customersDataSet

        ' Set the DataMember for CustomersDataGrid

        ' to the Customers DataTable.

        Me.CustomersDataGrid.DataMember = "Customers"

        ' Add a DataGridTableStyle to CustomersDataGrid.

        StyleCustomersTable()

End Sub

Private Sub StyleCustomersTable()

        ' Style the CustomersDataGrid to show

        ' only the CustomerID and CustomerName

        ' columns from the customersDataSet.

        ' Set the CustomersDataGrid caption.

        Me.CustomersDataGrid.CaptionText = "Customers"

        'REFERENCE 3

        ' Create a DataGridTableStyle.

        Dim dgTableStyle As New DataGridTableStyle()

        With dgTableStyle

            ' Map the DataGridTableStyle to

            ' a DataTable named Customers.

            .MappingName = "Customers"

        End With

        ' REFERENCE 4

        ' Create the CustomerID column.

        Dim columnCustomerID As New DataGridTextBoxColumn()

        With columnCustomerID

            ' Map the DataGridTextBoxColumn

            ' to a DataColumn named CustomerID.

            .MappingName = "CustomerID"

            .HeaderText = "Customer ID"

        End With

        dgTableStyle.GridColumnStyles.Add(columnCustomerID)

        ' REFERENCE 5

        ' Create the CustomerName column.

        Dim columnCustomerName As New DataGridTextBoxColumn()

        With columnCustomerName

            ' Map the DataGridTextBoxColumn

            ' to a DataColumn named CustomerName.

            .MappingName = "CustomerName"

            .HeaderText = "Customer Name"

        End With

        dgTableStyle.GridColumnStyles.Add(columnCustomerName)

        ' Clear existing TableStyle(s) from

        ' the CustomersDataGrid.

        Me.CustomersDataGrid.TableStyles.Clear()

        ' Add the new DataGridTableStyle to the

        ' CustomersDataGrid.

        ' REFERENCE 6

        Me.CustomersDataGrid.TableStyles.Add(dgTableStyle)

End Sub

Find Reference 1 in the code above.  This is where the DataColumns are initialized. The SELECT statement is what determines the DataColumns that will be created at runtime.  In the example above the DataColumns CustomerID, CustomerName, Address, and Phone will be created at runtime when the SELECT statement is used to fill a DataSet.

Find Reference 2 in the code above.  This is where the DataTable is initialized. By passing in the string “Customers” to the DataTable Fill method a DataTable named Customers is created and filled in the customersDataSet.

With the names of the DataTable and its DataColumns initialized it is possible to map the DataTable and some or all of its DataColumns to a DataGridTableStyle. Examine the StyleCustomersTable sub in the code above.  Can you explain how the DataTable and some of its columns are mapped to a DataGridTableStyle?

Find Reference 3 in the code above.  This is where the DataTable is mapped by name to a DataGridTableStyle.

Find Reference 4 and Reference 5 in the code above.  This is where two DataColumns from the DataTable are mapped to two DataGridTextBoxColumns.

Find Reference 6 in the code above.  This is where everything comes together.  The code has created a DataTable named Customers with DataColumns named CustomerID, CustomerName, and Address. The DataTable and its DataColumns have been mapped into a DataGridTableStyle. Finally, the DataGridTableStyle is added to a DataGrid.

It is important to observe that the DataGridTableStyle created by the StyleCustomersTable sub will accept any DataTable named Customers that has at least two DataColumns – one named CustomerId and one named CustomerName.  This fact makes it very important that you know the name of the DataTable and the DataColumns you wish to map to a DataGridTableSyle and that you do not misspell the names when creating the code.

Copyright © 2001-2003 aZ Software Developers. All rights reserved.