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
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
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.
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
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
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 ?
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.
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. ?
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.
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 ?
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.
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
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/
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