Binding a Data Reader in Windows Forms

[jmcilhinney]

C# version here.

I learned something new today so I felt the need to share it. It has always been the case that complex data-binding in Windows Forms requires an object that implements the IList or IListSource interface. Arrays implement IList, as does the List(Of T) class. The DataTable class implements IListSource and DataView implements IList. The BindingList(Of T) and BindingSource classes both implement IList. These are the most common types used as data sources for ComboBoxes, DataGridViews and the like.

I generally recommend that people use a BindingSource when binding, so bind their data to the BindingSource and bind that to the control(s). In some cases it makes little difference but in others it can help and I have found such a way that it can help that I wasn't previously aware of. When binding read-only data from a database, I have generally recommended creating a data reader, loading the data into a DataTable and then binding that to a BindingSource. That's because I assumed that a BindingSource also required an IList or IListSource, but that turns out not to be the case. In various cases, the BindingSource will generate its own IBindingList(Of T) and binding a data reader is one such case.

If you bind a data reader (SqlDataReader, OleDbDataReader, etc) to a BindingSource then it will automatically generate an IBindingList(Of System.Data.Common.DataRecordInternal). That list is similar to the DataRowCollection from the Rows property of a DataTable or the DataView from the DefaultView property. You can index a DataRecordInteral by column name or ordinal in the same way you can a DataRow or DataRowView but, unlike those other two, it only stores one version of the data, which makes it more efficient for read-only data. It also supports all the same PropertyDescriptor functionality that a DataGridView needs to automatically generate columns.

Here's an example of how I may have bound read-only data from a database before:

'Create a new DataTable
Dim table As New DataTable
 
Using connection As New SqlConnection("connection string here"),
      command As New SqlCommand("SELECT * FROM MyTable", connection)
    connection.Open()
 
    Using reader = command.ExecuteReader()
        'Populate the DataTable from the data reader
        table.Load(reader)
    End Using
End Using
 
'Bind the DataTable
BindingSource1.DataSource = table
 
ComboBox1.DisplayMember = "Name"
ComboBox1.ValueMember = "Id"
ComboBox1.DataSource = BindingSource1
 
TextBox1.DataBindings.Add("Text", BindingSource1, "Description")
 
DataGridView1.DataSource = BindingSource1

and here's how I might do it now:

Using connection As New SqlConnection("connection string here"),
      command As New SqlCommand("SELECT * FROM MyTable", connection)
    connection.Open()
 
    Using reader = command.ExecuteReader()
        'Bind the data reader and generate an IBindingList(Of DataRecordInternal)
        BindingSource1.DataSource = reader
    End Using
End Using
 
ComboBox1.DisplayMember = "Name"
ComboBox1.ValueMember = "Id"
ComboBox1.DataSource = BindingSource1
 
TextBox1.DataBindings.Add("Text", BindingSource1, "Description")
 
DataGridView1.DataSource = BindingSource1

As you can see, the code changes very little but there is slightly less code and the data structures underneath will be less resource-intensive. In the first case, each item is a DataRowView from the DefaultView of the DataTable while, in the second case, each item is DataRecordInternal from an IBindingList(Of T). The user will see no difference and you see little difference as the developer but your app is a little bit more efficient.