Binding a Data Reader in Windows Forms
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:
vb.net Code:
'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:
vb.net Code:
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.
Re: Binding a Data Reader in Windows Forms
Hello
I have a stupid question : why do you not declare "BindingSource1" ?
you directly write
Code:
BindingSource1.DataSource = reader
or maybe it is declared somewhere else ? (like for the combobox and text box)
Regards
Re: Binding a Data Reader in Windows Forms
Quote:
Originally Posted by
Delaney
maybe it is declared somewhere else ? (like for the combobox and text box)
Indeed. It was/would be added to the form in the designer, just as you would for a Timer, NotifyIcon, BackgorundWorker or any other component.
Re: Binding a Data Reader in Windows Forms
Ok, thank you
If I understand correctly the way : you have some data in a data base, you open it (connection), you read it (reader), you place what you read in the datasource of the databinding object (BindingSource1.DataSource = reader) then you use it (ComboBox1.DataSource = BindingSource1) in you application.
So the datatable was just an intermediary with also methods to play with the data
I suppose this BindingSource1.DataSource = reader format somehow the data else you would do directly ComboBox1.DataSource = reader or it is to read only one time the data to use them with several object (maybe both in fact). I have read the microsoft doc (link) but I must confess I didn't understand very well. It seems also that the BindingSource object has several methods to play with the data before using them.
I need to jump seriously into database vocabulary, structure, philosophy and tutorials else I will be quickly lost.
Regards
Re: Binding a Data Reader in Windows Forms
Quote:
Originally Posted by
Delaney
Ok, thank you
If I understand correctly the way : you have some data in a data base, you open it (
connection), you read it (reader), you place what you read in the
datasource of the
databinding object (
BindingSource1.DataSource = reader) then you use it (
ComboBox1.DataSource = BindingSource1) in you application.
So the
datatable was just an intermediary with also methods to play with the data
I suppose this
BindingSource1.DataSource = reader format somehow the data else you would do directly
ComboBox1.DataSource = reader or it is to read only one time the data to use them with several object (maybe both in fact). I have read the microsoft doc (
link) but I must confess I didn't understand very well. It seems also that the
BindingSource object has several methods to play with the data before using them.
I need to jump seriously into database vocabulary, structure, philosophy and tutorials else I will be quickly lost.
Regards
ADO.NET works generally in a disconnected state. Unlike ADO Recordsets, which maintain an open cursor on a database, ADO.NET generally works by opening a connection, retrieving data into your application, closing the connection, then using the local data cache in your application. If you make changes to the data, you then open a connection again and save those changes in a batch. A DataTable is that local data cache.
Where a DataTable gives you random access to the data to get and set values, a data reader is used when you need read-only, forward-only access to the data. A data adapter actually uses a data reader under the hood to populate a DataTable when you call Fill. Calling Load on the DataTable yourself is like doing that without the additional overhead of the data adapetr. You might use a data reader directly if you want to use and discard each record as you read it. Processing each row as you read it means not using the memory required to store the data locally.
When you bind data to your UI in WinForms, you need something to bind. It's generally a good idea to use a BindingSource as it provides one place to access all aspects of your bound data, e.g. navigation and currency. The BindingSource is just a way to access the data though. It still needs data to access and that data can be stored in various forms. You can create a DataTable and bind that, in which case your control will communicate with the BindingSource and the BindingSource will communicate with the DefaultView of the DataTable. As a data reader is read-only, forward-only, a BindingSource can't use it as a data source directly. If you assign a data reader to the DataSource property of a BindingSource, the data will be read from that data reader and used to populate an object that implements the IBindingList(Of T) interface (I haven't checked but that's probably a BindingList(Of T)). That object can then be navigated and accessed much as the DataTable/DataView can. If you're using the BindingSource in code though, the actual type of the underlying list is irrelevant.
Note that, just like the data reader, this IBindingList(Of T) is not intended for editing; just reading. There is less overhead to using a data reader and an IBindingList(Of DataRecordInternal) than there is to using a data adapter and a DataTable so, if you only need to read the data, e.g. a list of items to select from in a ComboBox, binding a data reader is a better option.
Re: Binding a Data Reader in Windows Forms
Thank you for the explanation.
Regards
Cyril
1 Attachment(s)
Re: Binding a Data Reader in Windows Forms
Quote:
Originally Posted by
jmcilhinney
C# version
here.
and here's how I might do it now:
vb.net Code:
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.
Hi,
I tried this today and at first it worked fine, then I got this error (Translated with google)
.......As a result, two bindings in the collection bind to the same property.
Parameter name: binding.....
Attachment 177686
I'm using VB2010
Re: Binding a Data Reader in Windows Forms
Quote:
Originally Posted by
ChrisE
Hi,
I tried this today and at first it worked fine, then I got this error (Translated with google)
.......As a result, two bindings in the collection bind to the same property.
Parameter name: binding.....
Attachment 177686
I'm using VB2010
It sounds like you have executed that code twice, thus attempting to bind to the Text property of TextBox1 twice.
Re: Binding a Data Reader in Windows Forms
Quote:
Originally Posted by
jmcilhinney
It sounds like you have executed that code twice, thus attempting to bind to the Text property of TextBox1 twice.
nope
there is Textbox1 and Textbox2
but I think I found the fault, it seems that Access 2000 can't handle it (only 2 Clients left with that antique)
it works with every other Access 2003 and up
SqLite etc....
Re: Binding a Data Reader in Windows Forms
Quote:
Originally Posted by
ChrisE
nope
there is Textbox1 and Textbox2
but I think I found the fault, it seems that Access 2000 can't handle it (only 2 Clients left with that antique)
it works with every other Access 2003 and up
SqLite etc....
That seems kinda weird. I would think that any issues that are related to the database would occur when you set the DataSource of the BindingSource, because that's when the data gets read. If what you're saying is true then there must be some difference between the underlying list in the BindingSource when using Access 2000 and other databases. In that case, you should be able to examine that list for two different databases and compare them to see what that difference is. I can't think what difference would account for that exception though, assuming it is saying what I think it's saying.
Re: Binding a Data Reader in Windows Forms
Ah, I think I may have spotted the issue. You are trying to bind to the Position column of the data source but the BindingSource class has its own Position property. If you use a different column name then I suspect that it will work. I'm not sure what the workaround would be if you can't change that column name though. I might have to play around with it to see, assuming that what I've suggested actually is the problem.
Re: Binding a Data Reader in Windows Forms
Quote:
Originally Posted by
jmcilhinney
Ah, I think I may have spotted the issue. You are trying to bind to the Position column of the data source but the BindingSource class has its own Position property. If you use a different column name then I suspect that it will work. I'm not sure what the workaround would be if you can't change that column name though. I might have to play around with it to see, assuming that what I've suggested actually is the problem.
you Hit the Nail on the head, I missed that completly
that's the German word for 'Title' in the Northwind Database
it works also correct with Access 2000
sorry ! hope I didn't cause a headache
EDIT:
I did just check the word 'Position', it is known to cause problems in Access
I always take a look here
http://allenbrowne.com/AppIssueBadWord.html
