Binding a Data Reader in Windows Forms

[jmcilhinney]

VB 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<T> class. The DataTable class implements IListSource and DataView implements IList. The BindingList<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<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<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
var table = new DataTable();
 
using (var connection = new SqlConnection("connection string here"))
using (var command = new SqlCommand("SELECT * FROM MyTable", connection))
{
    connection.Open();
 
    using (var reader = command.ExecuteReader())
    {
        // Populate the DataTable from the data reader
        table.Load(reader);
    }
}
 
// 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 (var connection = new SqlConnection("connection string here"))
using (var command = new SqlCommand("SELECT * FROM MyTable", connection))
{
    connection.Open();
 
    using (var reader = command.ExecuteReader())
    {
        // Bind the data reader and generate an IBindingList<DataRecordInternal>
        bindingSource1.DataSource = reader;
    }
}
 
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<T>. The user will see no difference and you see little difference as the developer but your app is a little bit more efficient.