Results 1 to 14 of 14

Thread: Forms, Data sources and not running on the UI

  1. #1

    Thread Starter
    Hyperactive Member
    Join Date
    Apr 2010
    Posts
    402

    Forms, Data sources and not running on the UI

    I currently have a programme that has a SQL database which contains a table called 'ControlRecords', which when the option is selected from the main form, loads a new form, which contains a data Grid, & displays the records. The sub form is displayed by the option .show, so does not have to be closed for the rest of the application to continue functioning.

    Now the first question, is should the code that requests the data from the table be on the UI or in a separate thread, if so how?

    Second, am I retrieving the data and displaying it in the most efficient / correct way by filling a dataset and then displaying the dataset


    Code:
         Dim dbConnection As New SqlCeConnection(My.Settings.CurrentDatasource)
            Dim strSQL As String = "SELECT * FROM ControlRecords"
            Dim cmd As New SqlCeCommand(strSQL, dbConnection)
            Dim da As New SqlCeDataAdapter(cmd)
            Dim dsControlRecords As New DataSet()
    
            Try
                da.Fill(dsControlRecords, "ControlRecords")
                Me.Text = "Control Records "
                With Me.DataGridView1
                    .Visible = True
                    .AutoGenerateColumns = False
                    '.AutoGenerateColumns = True
                    .AlternatingRowsDefaultCellStyle.BackColor = Color.Lavender
                    .BackColor = Color.WhiteSmoke
                    .ForeColor = Color.MidnightBlue
                    '.CellBorderStyle = DataGridViewCellBorderStyle.None
                    .ColumnHeadersDefaultCellStyle.Font = New Font("Tahoma", 8.0!, FontStyle.Bold)
                    .ColumnHeadersDefaultCellStyle.BackColor = Color.MidnightBlue
                    .ColumnHeadersDefaultCellStyle.ForeColor = Color.WhiteSmoke
                    .DefaultCellStyle.ForeColor = Color.MidnightBlue
                    .DefaultCellStyle.BackColor = Color.WhiteSmoke
                    .ReadOnly = True
                    .RowHeadersVisible = False
    
                    .AllowUserToAddRows = False
                    .AllowUserToDeleteRows = False
                    .AllowUserToOrderColumns = True
                    .AllowUserToResizeColumns = True
    
    
                End With
    
    
    
                Me.DataGridView1.DataSource = dsControlRecords.Tables(0)
    
    
                Me.DataGridView1.Columns.Clear()
                Dim newColumn As Integer = Me.DataGridView1.Columns.Add("RecNo", "Record No")
                Me.DataGridView1.Columns(newColumn).DataPropertyName = "RecNo"
                Me.DataGridView1.Columns(newColumn).AutoSizeMode = DataGridViewAutoSizeColumnMode.DisplayedCells
    
                newColumn = Me.DataGridView1.Columns.Add("recid", "Record ID")
                Me.DataGridView1.Columns(newColumn).DataPropertyName = "Recid"
                Me.DataGridView1.Columns(newColumn).AutoSizeMode = DataGridViewAutoSizeColumnMode.DisplayedCells
    
                newColumn = Me.DataGridView1.Columns.Add("Version", "Version")
                Me.DataGridView1.Columns(newColumn).DataPropertyName = "Version"
                Me.DataGridView1.Columns(newColumn).AutoSizeMode = DataGridViewAutoSizeColumnMode.DisplayedCells
    
                newColumn = Me.DataGridView1.Columns.Add("date", " Date")
                Me.DataGridView1.Columns(newColumn).DataPropertyName = "date"
                Me.DataGridView1.Columns(newColumn).AutoSizeMode = DataGridViewAutoSizeColumnMode.DisplayedCells
    
            Catch sqlExc As Exception
    
                MessageBox.Show(sqlExc.ToString, "SQL Exception Error!",
                    MessageBoxButtons.OK, MessageBoxIcon.Error)
            End Try
        End Sub
    The form also has a refresh button on it, and when pressed, runs the above code again
    Last edited by Signalman; Jan 17th, 2021 at 03:11 PM.

  2. #2
    Karen Payne MVP kareninstructor's Avatar
    Join Date
    Jun 2008
    Location
    Oregon
    Posts
    6,684

    Re: Forms, Data sources and not running on the UI

    Here is a simple asynchronous method to load a DataTable from SQL-Server, no frill. Make sure to read the readme.md file.

    https://github.com/karenpayneoregon/...ter/SqlSamples

  3. #3
    PowerPoster
    Join Date
    Sep 2005
    Location
    Modesto, Ca.
    Posts
    5,196

    Re: Forms, Data sources and not running on the UI

    There is no need for a dataset when your only using one datatable, a dataset if for groups of datatables.

    Where you should put the code will depend on how much data your retrieving, a few hundred records wont have any noticeable affect on the UI. Some people also think that the data should be in a different layer, separate from the UI, if your interested check out nTier programming.
    Last edited by wes4dbt; Jan 21st, 2021 at 10:10 PM.

  4. #4
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    34,532

    Re: Forms, Data sources and not running on the UI

    Not that it will have any impact on anything, but just for brrevity and clarity and simplicity, for formatting and things things that will never change, I would set at design time. So things like htis:
    Code:
                Me.Text = "Control Records "
                With Me.DataGridView1
                    .Visible = True
                    .AutoGenerateColumns = False
                    '.AutoGenerateColumns = True
                    .AlternatingRowsDefaultCellStyle.BackColor = Color.Lavender
                    .BackColor = Color.WhiteSmoke
                    .ForeColor = Color.MidnightBlue
                    '.CellBorderStyle = DataGridViewCellBorderStyle.None
                    .ColumnHeadersDefaultCellStyle.Font = New Font("Tahoma", 8.0!, FontStyle.Bold)
                    .ColumnHeadersDefaultCellStyle.BackColor = Color.MidnightBlue
                    .ColumnHeadersDefaultCellStyle.ForeColor = Color.WhiteSmoke
                    .DefaultCellStyle.ForeColor = Color.MidnightBlue
                    .DefaultCellStyle.BackColor = Color.WhiteSmoke
                    .ReadOnly = True
                    .RowHeadersVisible = False
    
                    .AllowUserToAddRows = False
                    .AllowUserToDeleteRows = False
                    .AllowUserToOrderColumns = True
                    .AllowUserToResizeColumns = True
    Would be set at design time. Those are things that never change, so there's no reason to keep setting it at run-time. Set it at design time and be done with it.

    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  5. #5
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    34,532

    Re: Forms, Data sources and not running on the UI

    Ooof.... I just noticed you're using a Select * there too.... don't. Just don't. That's rarely ever a good idea. You should always list out your fields. 1) It's rare to ever need all the fields in a table. Just select the ones you need. 2) There's no guarantee that the order of the fields will remain the same. There are forces that can and will change them down the road. It will come back to haunt you at some point. Trust me. I have the scars. 3) By only selecting the fields you do want/use it'll perform better as you'll only be pulling back just the data you'll actually be using and not throwing away data you won't. Example, let's say your table has 10 fields, but you only use 4... what's the point of pulling those otehr 6 fields if you're not going to use them? That takes time and bandwidth. You're better off if you explicitly pull just the 4 you are going to use.

    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  6. #6

    Thread Starter
    Hyperactive Member
    Join Date
    Apr 2010
    Posts
    402

    Re: Forms, Data sources and not running on the UI

    The example Karen has provide is most interesting (thankyou Karen), albeit I understand what it should be doing, but not the code.

    in the form, it has the following


    Code:
    Public Class Form1
        Private Async Sub LoadDataTableButton_Click(sender As Object, e As EventArgs) _
            Handles LoadDataTableButton.Click
    
            Dim dt = Await Operations.LoadCustomersAsync()
    
            DataGridView1.InvokeIfRequired(
                Sub(dgv)
                    dgv.DataSource = dt
                End Sub)
        End Sub
    End Class
    Now normally I coudl have coded this as

    Code:
    Public Class Form1
        Private  Sub LoadDataTableButton_Click(sender As Object, e As EventArgs) _
            Handles LoadDataTableButton.Click
    
            Dim dt = Operations.LoadCustomersAsync()
    
            DataGridView1.InvokeIfRequired(
                Sub(dgv)
                    dgv.DataSource = dt
                End Sub)
        End Sub
    End Class

    Now my questions are, what does the Private Async Sub..... and the Dim dt = Await Operations.... actually do. Is suspect that it stops it running on the UI thread, but cant see how ?

  7. #7
    King of sapila
    Join Date
    Oct 2006
    Location
    Greece
    Posts
    6,597

    Re: Forms, Data sources and not running on the UI

    But that's what you wanted be able to manipulate the GUI when doing a data load.
    So that's what it does.
    Async sets the function to be able to manipulate asynchronous calls and await is the way the asynchronous function is called.
    Depending on what you want to do, many asynchronous calls can be made so many non GUI thread would be created and run ,well, asynchronous.
    It's worth noting that by Karen's example, you will be using Task.Run(Function() da.Fill(dt)) , that is fine because da.fill accepts async operations, however there are cases (like a resent Filesystem.CopyDirectory) that the example will not work as the function is not async . You can get away with a similar call but in reality the function is not asynchronous, it's just simulated, meaning, here the CopyDirectory is not made async but simulated async.
    So this:
    Code:
      Await Task.Run(Function()
                                   My.Computer.FileSystem.CopyDirectory("dir1", "dir2")
                                   Return 1
                               End Function)
    return is used as I'm showing a Function call, I suppose it could work with:
    Code:
      Await Task.Run(Sub()
                                   My.Computer.FileSystem.CopyDirectory("dir1", "dir2")                          
                               End Sub)
    While it seems async, runs async, it's not async.

    Also note that I'm into the async deeper for a month or so, so I might have said something incorrect but... Also note that here we just have one ready to go da.Fill, that is not that case always as we might have to manipulate a lot of async calls. That is where all hell breaks loose as the code flow is not canonical and the async functions might and will be called at non serial line . Unless you use a tread pool or something that I'm not deep into that yet. So what I want to say is that , yes async can do some thing to take the load of the GUI but it should be used with caution and not all over the place if not needed. But that is another huuuugeee conversation.
    ἄνδρα μοι ἔννεπε, μοῦσα, πολύτροπον, ὃς μάλα πολλὰ
    πλάγχθη, ἐπεὶ Τροίης ἱερὸν πτολίεθρον ἔπερσεν·

  8. #8

    Thread Starter
    Hyperactive Member
    Join Date
    Apr 2010
    Posts
    402

    Re: Forms, Data sources and not running on the UI

    Now I am new to all this, so forgive me if your explanation does make sense to me.
    What happens when the Line of code that contains async is executed as likewise the line containing await. ?

  9. #9
    King of sapila
    Join Date
    Oct 2006
    Location
    Greece
    Posts
    6,597

    Re: Forms, Data sources and not running on the UI

    If you don't use await and async then the code will be executed synchronously, so you will possibly get a blocked GUI. Else why bother creating an async function?
    Don't bother so much on async and await, that is the way to do the declaration, it could be called anything.
    ἄνδρα μοι ἔννεπε, μοῦσα, πολύτροπον, ὃς μάλα πολλὰ
    πλάγχθη, ἐπεὶ Τροίης ἱερὸν πτολίεθρον ἔπερσεν·

  10. #10

    Thread Starter
    Hyperactive Member
    Join Date
    Apr 2010
    Posts
    402

    Re: Forms, Data sources and not running on the UI

    I understand that you have to use async and wait so that the code will be executed and not get a blocked GUI. What I need to know is how to code this and understand what effect the commands have on the actual execution of the code.

    Hence what does async and await do in the code ?

  11. #11
    Super Moderator Shaggy Hiker's Avatar
    Join Date
    Aug 2002
    Location
    Idaho
    Posts
    38,989

    Re: Forms, Data sources and not running on the UI

    The question of whether you should do the query in the UI thread or some other thread generally comes down to performance. If the query is quick, you might not even notice the time it takes, in which case it probably doesn't matter which thread you run it on. If things slow down, then you should run it earlier, which means that a different thread would work, but starting it at some earlier time would also be helpful. Since this doesn't sound like the startup form, if you are seeing any delay due to filling the datatable, then fill the datatable as soon as you can such that it will be ready by the time you want to display it.
    My usual boring signature: Nothing

  12. #12

    Thread Starter
    Hyperactive Member
    Join Date
    Apr 2010
    Posts
    402

    Re: Forms, Data sources and not running on the UI

    Thanks, i understand the principal and reason, what I don't understand is how to code it. It looks like you add async & wait into the code, but what this actual statement does in isolation is what I don't understand.

    Whilst I understand what this statement does

    Code:
    Private Sub LoadDataTableButton_Click(sender As Object, e As EventArgs)   Handles LoadDataTableButton.Click
    What I don't understand is what effect on the individual line of code adding async into it does

    Code:
    Private Async Sub LoadDataTableButton_Click(sender As Object, e As EventArgs) Handles LoadDataTableButton.Click

    Likewise what effect on the execution of the individual line of code does adding await and load customers async does

    Code:
            Dim dt = Await Operations.LoadCustomersAsync()
    If I can understand this then I can understand how to apply the code / principals to my other code in other applications

  13. #13
    King of sapila
    Join Date
    Oct 2006
    Location
    Greece
    Posts
    6,597

    Re: Forms, Data sources and not running on the UI

    I don't know what else to say to help here.
    This has a full explanation, have a thoroughly read and if you have questions ask the members here again.
    https://docs.microsoft.com/en-us/dot...oncepts/async/
    ἄνδρα μοι ἔννεπε, μοῦσα, πολύτροπον, ὃς μάλα πολλὰ
    πλάγχθη, ἐπεὶ Τροίης ἱερὸν πτολίεθρον ἔπερσεν·

  14. #14

    Thread Starter
    Hyperactive Member
    Join Date
    Apr 2010
    Posts
    402

    Re: Forms, Data sources and not running on the UI

    can someone talk me thro LINE BY LINE what the code does and the FLOW it takes, as i suspect that on some lines the code continues to process the next line before the current one is executed.

    Code:
    Public Class Form1
        Private Async Sub LoadDataTableButton_Click(sender As Object, e As EventArgs) _
            Handles LoadDataTableButton.Click
    
            Dim dt = Await Operations.LoadCustomersAsync()
    
            DataGridView1.InvokeIfRequired(
                Sub(dgv)
                    dgv.DataSource = dt
                End Sub)
        End Sub
    End Class

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