Results 1 to 12 of 12

Thread: Binding a Data Reader in Windows Forms

  1. #1

    Thread Starter
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,299

    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:
    1. 'Create a new DataTable
    2. Dim table As New DataTable
    3.  
    4. Using connection As New SqlConnection("connection string here"),
    5.       command As New SqlCommand("SELECT * FROM MyTable", connection)
    6.     connection.Open()
    7.  
    8.     Using reader = command.ExecuteReader()
    9.         'Populate the DataTable from the data reader
    10.         table.Load(reader)
    11.     End Using
    12. End Using
    13.  
    14. 'Bind the DataTable
    15. BindingSource1.DataSource = table
    16.  
    17. ComboBox1.DisplayMember = "Name"
    18. ComboBox1.ValueMember = "Id"
    19. ComboBox1.DataSource = BindingSource1
    20.  
    21. TextBox1.DataBindings.Add("Text", BindingSource1, "Description")
    22.  
    23. DataGridView1.DataSource = BindingSource1
    and here's how I might do it now:
    vb.net Code:
    1. Using connection As New SqlConnection("connection string here"),
    2.       command As New SqlCommand("SELECT * FROM MyTable", connection)
    3.     connection.Open()
    4.  
    5.     Using reader = command.ExecuteReader()
    6.         'Bind the data reader and generate an IBindingList(Of DataRecordInternal)
    7.         BindingSource1.DataSource = reader
    8.     End Using
    9. End Using
    10.  
    11. ComboBox1.DisplayMember = "Name"
    12. ComboBox1.ValueMember = "Id"
    13. ComboBox1.DataSource = BindingSource1
    14.  
    15. TextBox1.DataBindings.Add("Text", BindingSource1, "Description")
    16.  
    17. 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.

  2. #2
    Fanatic Member Delaney's Avatar
    Join Date
    Nov 2019
    Location
    Paris, France
    Posts
    845

    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
    The best friend of any programmer is a search engine
    "Don't wish it was easier, wish you were better. Don't wish for less problems, wish for more skills. Don't wish for less challenges, wish for more wisdom" (J. Rohn)
    “They did not know it was impossible so they did it” (Mark Twain)

  3. #3

    Thread Starter
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,299

    Re: Binding a Data Reader in Windows Forms

    Quote Originally Posted by Delaney View Post
    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.

  4. #4
    Fanatic Member Delaney's Avatar
    Join Date
    Nov 2019
    Location
    Paris, France
    Posts
    845

    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
    The best friend of any programmer is a search engine
    "Don't wish it was easier, wish you were better. Don't wish for less problems, wish for more skills. Don't wish for less challenges, wish for more wisdom" (J. Rohn)
    “They did not know it was impossible so they did it” (Mark Twain)

  5. #5

    Thread Starter
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,299

    Re: Binding a Data Reader in Windows Forms

    Quote Originally Posted by Delaney View Post
    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.

  6. #6
    Fanatic Member Delaney's Avatar
    Join Date
    Nov 2019
    Location
    Paris, France
    Posts
    845

    Re: Binding a Data Reader in Windows Forms

    Thank you for the explanation.

    Regards

    Cyril
    The best friend of any programmer is a search engine
    "Don't wish it was easier, wish you were better. Don't wish for less problems, wish for more skills. Don't wish for less challenges, wish for more wisdom" (J. Rohn)
    “They did not know it was impossible so they did it” (Mark Twain)

  7. #7
    PowerPoster ChrisE's Avatar
    Join Date
    Jun 2017
    Location
    Frankfurt
    Posts
    3,045

    Re: Binding a Data Reader in Windows Forms

    Quote Originally Posted by jmcilhinney View Post
    C# version here.


    and here's how I might do it now:
    vb.net Code:
    1. Using connection As New SqlConnection("connection string here"),
    2.       command As New SqlCommand("SELECT * FROM MyTable", connection)
    3.     connection.Open()
    4.  
    5.     Using reader = command.ExecuteReader()
    6.         'Bind the data reader and generate an IBindingList(Of DataRecordInternal)
    7.         BindingSource1.DataSource = reader
    8.     End Using
    9. End Using
    10.  
    11. ComboBox1.DisplayMember = "Name"
    12. ComboBox1.ValueMember = "Id"
    13. ComboBox1.DataSource = BindingSource1
    14.  
    15. TextBox1.DataBindings.Add("Text", BindingSource1, "Description")
    16.  
    17. 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.....

    Name:  errorJMC.jpg
Views: 1404
Size:  73.8 KB


    I'm using VB2010
    Last edited by ChrisE; Jun 26th, 2020 at 10:33 AM.
    to hunt a species to extinction is not logical !
    since 2010 the number of Tigers are rising again in 2016 - 3900 were counted. with Baby Callas it's 3901, my wife and I had 2-3 months the privilege of raising a Baby Tiger.

  8. #8

    Thread Starter
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,299

    Re: Binding a Data Reader in Windows Forms

    Quote Originally Posted by ChrisE View Post
    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.....

    Name:  errorJMC.jpg
Views: 1404
Size:  73.8 KB


    I'm using VB2010
    It sounds like you have executed that code twice, thus attempting to bind to the Text property of TextBox1 twice.

  9. #9
    PowerPoster ChrisE's Avatar
    Join Date
    Jun 2017
    Location
    Frankfurt
    Posts
    3,045

    Re: Binding a Data Reader in Windows Forms

    Quote Originally Posted by jmcilhinney View Post
    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....
    to hunt a species to extinction is not logical !
    since 2010 the number of Tigers are rising again in 2016 - 3900 were counted. with Baby Callas it's 3901, my wife and I had 2-3 months the privilege of raising a Baby Tiger.

  10. #10

    Thread Starter
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,299

    Re: Binding a Data Reader in Windows Forms

    Quote Originally Posted by ChrisE View Post
    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.

  11. #11

    Thread Starter
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,299

    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.

  12. #12
    PowerPoster ChrisE's Avatar
    Join Date
    Jun 2017
    Location
    Frankfurt
    Posts
    3,045

    Re: Binding a Data Reader in Windows Forms

    Quote Originally Posted by jmcilhinney View Post
    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
    Last edited by ChrisE; Jun 26th, 2020 at 01:32 PM.
    to hunt a species to extinction is not logical !
    since 2010 the number of Tigers are rising again in 2016 - 3900 were counted. with Baby Callas it's 3901, my wife and I had 2-3 months the privilege of raising a Baby Tiger.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width