[RESOLVED] [2008] SQLcommand Async methods worth using?

[chris128]

I'm working on a project that needs quite a bit of interaction with an SQL database, mainly loading and saving data to and from DGVs on my forms.
I noticed that instead of running the standard synchronous methods of the SQLCommand class you can also run some Async ones too such as BeginExecuteNonQuery() and BeginExecuteReader(). So I did a quick bit of reading up on the MSDN site and to be honest it looks like quite a bit of work learning how exactly to implement this along with the EndExecuteReader() etc methods to actually return the results. Then once I had figured out how to use them I would obviously have to replace all the existing synchronous methods I have already written. So I was wondering if anyone here has used these methods much in the past? I'm not dragging huge amounts of data from the SQL tables so load times in my application are pretty small as it is but as I'm sure you all know even a small delay between screens (the UI blocking I mean) can give users cause to label your software as "slow" and "unresponsive"...

So basically, my question is has anyone used these methods previously and if so is it a lot of work implementing them or should I give it a go just to cut out all possible blocks of my UI thread?

Ta

[GaryMazzone]

Did you consider useing background worker tasks instead?

[chris128]

Yes but I thought if there are methods specifically for this on the SQLcommand class then why use a backgroundworker (which I assume uses more resources and is a bit overkill for running a few simple SQL commands)

[chris128]

Anyone else?

[MaximilianMayrhofer]

Well, for one thing, a background worker is a much simpler option for people who aren't too familiar/good with the concepts of multithreading.

One thing about synchronous sql commands is, even if the actual request isn't large, if for any reason you have a connection problem and you haven't manually adjusted your timeout property to a lower value, your UI thread could freeze for up to 20 seconds while it attempts to open the connection.

[chris128]

Sorry i didnt realise there had been another reply on this.

OK so is the general consensus that I should just use a background worker then instead of these methods? If so, is it a good/bad idea to just have one backgroundworker and write out a load of functions & methods in there, then just pass a different argument to RunWorkerAsync depending on which SQL method I want to run? Or would it be a better idea to have seperate background workers for different SQL queries?

[jmcilhinney]

I'd use the SqlClient methods. It's very little work at all. I've never done it before and I just wrote the following code in a few minutes. I simply applied my existing knowledge of the .NET asynchronous programming model (which you may not have) and consulted the MSDN documentation for the BeginExecuteReader method for the specifics.

Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
    Dim connection As New SqlConnection("connection string here")
    Dim command As New SqlCommand("SQL query here", connection)
 
    connection.Open()
    command.BeginExecuteReader(AddressOf LoadData, _
                               command, _
                               CommandBehavior.CloseConnection)
End Sub
 
Private Sub LoadData(ByVal ar As IAsyncResult)
    Dim command As SqlCommand = DirectCast(ar.AsyncState, SqlCommand)
    Dim reader As SqlDataReader = command.EndExecuteReader(ar)
    Dim table As New DataTable
 
    table.Load(reader)
    reader.Close()
    command.Connection.Dispose()
    command.Dispose()
 
    'Bind table here.
End Sub

There's no asynchronous methods for a SqlDataAdapter, so if you need to use one of those then you'd need to look elsewhere.

[chris128]

Nah I dont use the SQLDataAdapter class at all so that shouldnt be a problem. I thought the SQL async methods would probably be the way to go because MS must have made them for a reason... I mean they dont make an async method for everything. Thanks yet again jmc, I'll try convert all my existing methods to the async ones and see how it goes

Cheers
Chris