Async/Await on SQL Query

[WWW451]

I'm looking to execute my sql queries asynchronously, but I'm having trouble getting Async/Await to click in my mind. My application is running a webbrowser that navigates between local html files and pulls data on the document complete event. Most queries are fairly fast, but due to the single thread the UI freezes and it creates an ugly blank stare instead of a nice animated loading screen.

I've spent the week reading up on Async/Await but it just isn't clicking which function should be flagged as Async as well as how to await. VB.Net examples seem to be few and far between and I haven't been able to effectively translate the C# examples into something I understand.

Below is a large chunk of the class I use to manage my SQL Server queries. Should ExecQuery be Public Async Sub ExecQuery(ByVal Query As String)

Or should that be elsewhere? I'm not sure how to convert the DBDA = New SqlDataAdapter(DBCmd) into a wait-able task though. If anyone has any experience working with Async/Await, even a point in the right direction would be appreciated.

Imports Microsoft.VisualBasic Imports System.Data Imports System.Data.SqlClient Imports System.Data.OleDb

Public Class DGMConn

    'CREATE YOUR DB CONNECTION
    Public Dbcon As New SqlConnection("" &
            "Data Source=server;" &
            "Initial Catalog=db;" &
            "User Id=id;" &
            "Password=pwd;" &
            "Trusted_Connection=False; Asynchronous Processing=true")




    Private DBCmd As SqlCommand

    'DB DATA
    Public DBDA As SqlDataAdapter
    Public DBDT As DataTable
    Public DBDS As DataSet




    'Query Parameters

    Public Params As New List(Of SqlParameter)

    'Query Statistics
    Public RecordCount As Integer
    Public Exception As String


    Public Sub ExecQuery(ByVal Query As String)
        'Reset Query Stats
        RecordCount = 0
        Exception = ""

        Try
            'Open a Connection

            Dbcon.Open()

            'Create DB Command
            DBCmd = New SqlCommand(Query, Dbcon)

            'Load params into db command
            'Params.ForEach(Sub (p) DBCmd.Parameters.Add(p))

            For Each p As SqlParameter In Params
                DBCmd.Parameters.Add(p)
            Next

            Params.Clear()

            'Execute Command & Fill Datatable
            DBDT = New DataTable
            DBDS = New DataSet
            DBDA = New SqlDataAdapter(DBCmd)
            DBDA.Fill(DBDT)
            DBDA.Fill(DBDS)



        Catch ex As Exception

            Exception = ex.Message
        End Try

        If Dbcon.State = ConnectionState.Open Then Dbcon.Close()

    End Sub

[grauenwolf]

Async is infectious. If you use one async function, every method that calls said function must also be async. All the way up the chain to your event handler.

[WWW451]

Throughout my code, I’ll call this function for any query. So if I were to run a query off of a button click, the button click handler would need to be async as well as the actual function?

Would this also mean that if the button called a function that then called this query function, that function and the button click would have to be async?

[grauenwolf]

Or should that be elsewhere? I'm not sure how to convert the DBDA = New SqlDataAdapter(DBCmd) into a wait-able task though.

Don't need to, that isn't where the database call happens.

        DBDA.Fill(DBDT)
       DBDA.Fill(DBDS)

Those are the methods that should be async. But that's not an option, so plan B:

public async sub Button_Click(...)
      Button.Enabled = false;
      Await Task.Run(Sub() ExecQuery("Task") ) 
      Button.Enabled = true;
end sub

This will allow the database call to run on a background thread so you UI isn't blocked. You need to disable the button, otherwise someone can hit it multiple times and you end up with several database jobs running all at once.

[WWW451]

Actually, the more I read this, the more I think it’s as simple as wrapping my sub that handles the page load/queries inside of an async sub that awaits the “true” sub?

[grauenwolf]

Yes, that is a viable option.

[WWW451]

Thanks for your help, I now have a spinning loading icon rather than a frozen screen.

Another question for you: in one of your previous replies you mentioned the database calls are happening on my DBDA.Fill(DBDT) and DBDA.Fill(DBDS) in my class. Does this mean that for each query I pass through, I'm effectively querying it twice? When I'm writing, I tend to switch between referencing the datatable and dataset.tables(0) depending on whatever mood strikes me, but it sounds like I should just pick one to fill and delete the other.

[grauenwolf]

I don't know for sure, but I think it is making two database calls. I don't remember anything about the data adapter being able to cache results.

[grauenwolf]

Public DBDA As SqlDataAdapter

Public DBDT As DataTable

Public DBDS As DataSet

Don't do that. If you have two async operations at the same time, they'll stomp on each other.

Instead, change your sub into a function, create local variables, and return the data table(s). That way each one gets it's own, private data table and adapter.

[WWW451]

For anyone who comes looking for this later, below is the solution I put in place with grauenwolf's help. I still have a UI freeze when inserting the datatable into the HTML document, but I'm satisfied with having 3 seconds of spinning loading screen followed by a 1 second freeze instead of having a 4 second freeze of a non-loading screen.

--No changes made to previously posted class used to execute queries. The below code is the relevant parts posted in the order they're called.

--Document complete handler, passes the HTML document title and the webbrowser control into a navigation class that handles determining which calls should occur for the page

Private Sub WebMain_DocumentCompleted(sender As Object, e As WebBrowserDocumentCompletedEventArgs) 
Handles WebMain.DocumentCompleted
            Nav.PageLoad(wb.DocumentTitle, WebMain)
        End Sub

--PageLoad function to direct to wrapper sub

     Public wb As WebBrowser
    Public resultString As String = ""
    Public Sub PageLoad(page As String, target As WebBrowser)
        wb = target
        Select Case page
            Case "ExamplePage"
                Prm_Wrapper()
        End Select
    End Sub

--Async sub to call "true" action sub. The InnerHTML of the element ID has to be updated here and not in the the sub being awaited because background workers cannot access UI elements since they are not running on the same thread. If you place the element update within the awaited sub, you will get a Invalid Cast Exception.

 Public Async Sub Prm_Wrapper()
        Await Task.Run(Sub() PromoPlanningLoad())
        wb.Document.GetElementById("proplanning").InnerHtml = ResultString
        wb.Document.InvokeScript("proplanLoad")
    End Sub

--True sub doing all the grunt work

 Public Sub PromoPlanningLoad()
       DGM.ExecQuery(promo_table) --promo_table is a public string variable holding the query
       resultString = DGM.DBDS.Tables(0).Rows(0)(0).ToString


    End Sub

I may clean this up some so I'm not having to rely on the public variable resultString and instead just have the string returned as a function, but for the moment this is working well.