December 19, 2008 at 11:14 pm
Perhaps I am posting in the wrong forum, and I apologize, but I am running into a situation that I cannot solve. I have posted a similar request in a VB programming forum, but hopefully someone here can give me ideas.
First, being fairly new to SQL programming, I use RAISERROR in my t-sql code to set messages and numbers (indicating a % complete) so my VB app can handle the events and update the progress bar (and set label messages). As far as I know, the only way to trap this is with the System.Data.SqlClient.SqlConnection.InfoMessages
In VB, it appears you can only use WithEvents on an object instance (no arrays or the class itself).
So, the dilemma. If I use ONE connection object, I can trap the RAISERROR and update my UI controls. If I use new SqlConnection objects each time, I can probably execute them asynchronously (I have not tried), but will not be able to get the RAISERRROR feedback. Or can I? Please advise.
If I use .ExecuteNonQuery, the long process appears to hang my application until it returns (could be 5 minutes on a particular .sql file). How can I run the query and still have my application usable?
I tried .BeginExecuteNonQuery, and had problems.
Dim acb As System.AsyncCallback = New AsyncCallback(AddressOf DoUpdate)
Dim res As IAsyncResult
res = Command.BeginExecuteNonQuery(acb, Command)
res.AsyncWaitHandle.WaitOne()
Private Sub DoUpdate(ByVal asResult As System.IAsyncResult)
Dim rowsAffected As Long
While asResult.IsCompleted = False
rowsAffected = Command.EndExecuteNonQuery(asResult)
End While
End Sub
I also tried it without parameters to the BeginExecuteNonQuery()
Ideally, what I am trying to accomplish, is load my .sql files from disk (all the info about how to process them is in a .XML file) and process them accordingly, so that .sql stored procedures can be loaded (not run) asynchronously and multi-threaded, while others that depend on these stored procedures would have to wait until that group of files has completed, then run. Various scripts take time and depend on other scripts.
I could be going about this all wrong. Perhaps all I need to do is run the scripts in series (synchronously), but still give control back to my application. ExecuteNonQuery seems to block all processes, so how do I go about freeing up the application?
Would it be much faster running asynchronous and multi-threaded sql or would the computer compensate for running multiple processes and still finish in about the same time?
Any help/advice would be appreciated.
December 20, 2008 at 7:30 pm
adammenkes (12/19/2008)
In VB, it appears you can only use WithEvents on an object instance (no arrays or the class itself).
This is true, but only to the extent that it is confusing syntax and semantics. It's complicated to describe but let's see if I can explain it:
"WithEvents" is a syntax element that is an optional clause of the VB variable declarations statements (DIM, PRIVATE, PUBLIC, etc.). And you are correct in that this clause can only be used on singular (non-aggregate) declarations of object instance variables.
However, that is syntax. The semantics work somewhat differently:
A declaration creates an object instance variable, but does not necessarily put anything in it. And an object instance variable does not hold an object instance, but rather holds a pointer to an object instance. Now this difference is usually just a nit that most .net programmers already know, but in this case the difference is critically important because an object instance is a unique entity, but pointers to a single object instance can exist in many variables, array entries, list entries, collection entries, etc. at the same time.
And this means that your specific connection object "in" an array somewhere can also be "in" a singular variable at the same time.
A variable, on the other hand may point to nothing, or may point to a single object instance throughout its lifetime, or may may point to a different object instance at different times, as you assign them to this variable. The "WithEvents" clasue works on whatever object instance is being pointed to by the the variable at that time.
Hopefully by now you will see where this is going...
If you want to trap the events from an object instance contained in an array, then just assign it to your WithEvents variable:
EventsConnection = AllMyConnections(i)
Now the EventsConnection variable will trap the events from this connection object instance, even though it is also still "in" the array.
This whole RAISERROR trick on the other hand, I am not so sure about...
I'll let someone else who knows better comment on that (Matt? Jonathan? Gail?...)
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply