none
How to use progressbar while dataadapter is filling a datatable RRS feed

  • Question

  • Let say, I have thousands of records in my back end database table. I want to bring that table into vb.net.

    Using:

    myAdapter.fill(mydata)

    Above command works perfectly, but while loading records from database ( while executing above command), the application is like hanging for seconds depending on how many records count to be retrieved. I want to set progress bar until it finished loading data to datatable. Where should I put the code where progress bar will increment value?

    Friday, June 1, 2018 8:47 AM

All replies

  • Hello,

    The true way of handling this is to only load data that one user can work with at a time or provide a method for the user to search for data from the database, display it rather than load a great deal of data at one time.

    One of the many downsides to working with TableAdapter objects is that there are no events to subscribe to for dealing with your task.

    A simple approach would be to have a progressbar hidden on the form, make it visible right before performing the Fill operation have the style set to Marquee, the next line after fill would hide the progress bar b) display a form right before the Fill then dispose the form after the fill.

    Keeping with simple, the line after showing the Progressbar or the form, use Application.DoEvents (something that is generally frowned upon but okay here).

    Another approach would be to use a BackGroundWorker component, start the component then start the progress bar operation or displaying of the child form.

    In closing, the base issue here is the Fill method is synchronous with no events to tie into for doing a proper show progress while other forms of working with data e.g. using a managed data provider to take control then you would have control over the responsiveness of the load but with a slower load time. 

     


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Friday, June 1, 2018 10:16 AM
    Moderator
  • If you need a progressbar with a dataadapter, you are sure that there is something wrong with your approach. 

    It can be that you are on the biggest school in the world then there are about 1600 teachers. In those cases you can use a "where" clause to make that the end user can handle the data. 

    If it is then still slow there are probably bugs in your program. 

    Be aware, a progressbar has only the effect that the result is much slower retrieved.

    (I've no experience with a MySQL dataBase, but I cannot  believe that is so much slower than SQL server that this is needed).


    Success
    Cor

    Friday, June 1, 2018 11:10 AM
  • Since you can't possibly display a thousands rows at one time, why would you query for all of them? For any type of implementation involving a UI you should only bring back those rows necessary for display and use paging to display additional rows as required. Since you are using the DataAdapter this should be relatively easy to accomplish using paging:

    https://msdn.microsoft.com/en-us/library/eeyya6y4(v=vs.110).aspx


    Paul ~~~~ Microsoft MVP (Visual Basic)

    Friday, June 1, 2018 1:46 PM
  • Paul,

    We have also a sample therefore on our page. I did not want to show it because it would put another focus on my message. 

    http://www.vb-tips.com/PageDataGridView.aspx


    Success
    Cor

    Friday, June 1, 2018 4:39 PM
  • Cor, I was actually going to post that example but then I forgot about it and posted a link to the doc instead. ;-)

    Paul ~~~~ Microsoft MVP (Visual Basic)

    Friday, June 1, 2018 7:45 PM
  • You can improve performance (by perhaps an unnoticeable amount) by selecting columns instead of *

    Also you dont need to open the connection when using a dataadapter, and on that note I dont see where you closed it. It looks like your connection is hanging open. Look around for some "Using" block examples so things are disposing properly.

    I've never had a MySQL table bog down like you're referring. Are there any BLOB columns in your table? omit them from the select command if you can 


    Live as if you were going to die today, learn as if you were going to live forever -Mahatma Gandhi

    Friday, June 1, 2018 8:12 PM
  • Another option is to ditch the DataAdatper, go straight to reading into a DataTable using asynchronous methods as shown in my MSDN code sample (uses ms-access but will work with MySql by changing the data provider) which allows the user to cancel the operation. The progressbar can be shown in a child form or directly on the form loading the data.

    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Friday, June 1, 2018 8:33 PM
    Moderator
  • Hi zirjohn,

    Yes, I agree with karen's post, you can load some data used at this stage, and use a BackGroundWorker component, here is the simple sample that you can take a look:

    https://www.c-sharpcorner.com/UploadFile/deepak.sharma00/how-to-show-progress-of-fetching-database-records-using-a-pr/

    Best Regards,

    Cherry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, June 4, 2018 6:35 AM
    Moderator
  • Hello,

    The true way of handling this is to only load data that one user can work with at a time or provide a method for the user to search for data from the database, display it rather than load a great deal of data at one time.

    One of the many downsides to working with TableAdapter objects is that there are no events to subscribe to for dealing with your task.

    A simple approach would be to have a progressbar hidden on the form, make it visible right before performing the Fill operation have the style set to Marquee, the next line after fill would hide the progress bar b) display a form right before the Fill then dispose the form after the fill.

    Keeping with simple, the line after showing the Progressbar or the form, use Application.DoEvents (something that is generally frowned upon but okay here).

    Another approach would be to use a BackGroundWorker component, start the component then start the progress bar operation or displaying of the child form.

    In closing, the base issue here is the Fill method is synchronous with no events to tie into for doing a proper show progress while other forms of working with data e.g. using a managed data provider to take control then you would have control over the responsiveness of the load but with a slower load time. 

     


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    I tried to create an instance of my progressbar before the "adapter.fill(table)" in my class:

    but it's not working, It was loading and loading and didn't show the data and close the instantiated form.

    When I tried to close the progressbar, that was the time it loads the data and still have delay.

    Note: progressbar has been set to marquee.

    Monday, June 4, 2018 7:55 AM
  • Hi zirj0hn,

    You said that you have some issue at adapter.fill(table), but from your code, I think you can fill mysql datatable into table, your mean that the table has no data?

    Best Regards,

    Cherry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, June 7, 2018 7:00 AM
    Moderator