Using ADO.NET to prcoess T-SQL batches

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

In our development process we have to create T-SQL Scripts to be run against the "Live" databases (there are at least a dozen) so that any changes can be verified and/or tracked

Reading the text from the file is straight forward enough. Using SqlConnection/SqlCommand to actually execute the code is also easy.

My problem is that a lot of these scripts use PRINT statements to indicate which logic has taken place and I cannot seem to obtain these in any form to display to the user (an authorized member of the dev. team). Other than success or failure I can't help him to report back to the script creator where he went wrong.

Does any one have any helpful suggestions.
 
Print statements return their data to an ADO.NET client as InfoMessages. You
need to create handler for the SqlConnection's InfoMessage event, as shown
in the following example

Dim conn As New SqlConnection("SERVER=localhost;DATABASE=Test;INTEGRATED
SECURITY=true")

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles Button1.Click
Try
AddHandler conn.InfoMessage, New
SqlInfoMessageEventHandler(AddressOf OnInfoMessage)

Dim cmd As New SqlCommand
cmd.CommandText = "INSERT INTO TestData Values('Test'); PRINT
'Data Inserted'"
cmd.Connection = conn
conn.Open()
cmd.ExecuteNonQuery()
Catch Ex As Exception
MessageBox.Show(Ex.Message)
Finally
conn.Close()
End Try
End Sub

Private Shared Sub OnInfoMessage(ByVal sender As Object, ByVal args As
SqlInfoMessageEventArgs)
Dim err As SqlError
For Each err In args.Errors
MessageBox.Show(err.Message)
Next
End Sub

Don't worry that it looks like you're looking for "errors", in this case the
errors are just messages (the output from your PRINT statements).

Cheers,
Graeme
--
----
Graeme Malcolm
Principal Technologist
Content Master Ltd.
www.contentmaster.com
www.microsoft.com/mspress/books/6137.asp


David R Hancock said:
In our development process we have to create T-SQL Scripts to be run
Click to expand...
against the "Live" databases (there are at least a dozen) so that any
changes can be verified and/or tracked.
Reading the text from the file is straight forward enough. Using
Click to expand...
SqlConnection/SqlCommand to actually execute the code is also easy.
My problem is that a lot of these scripts use PRINT statements to indicate
Click to expand...
which logic has taken place and I cannot seem to obtain these in any form to
display to the user (an authorized member of the dev. team). Other than
success or failure I can't help him to report back to the script creator
where he went wrong.
 
FYI, I successfully use this sort of approach for the same type of thing,
but there are a couple caveats (at least on current VS versions; not sure
about Whidbey):

1) The SqlCommand uses "sp_executesql" to execute SQL statements, and it can
only execute batches under a certain size, either 40K or 80K, if I remember
right. Because of this limitation I had to switch to use OleDB in my
application.

2) OleDbCommands work fine for executing SQL statments, but if you execute a
"SET NOCOUNT ON" statement, subsequent print statements issued inside a
stored procedure will come out in the wrong order. Extremely odd, but I can
reproduce this. My solution is to remove or comment out SET NOCOUNT ON
statements, since they're not useful for my application.


Jeff Davis
 
The way cool additional thing Whidbey ADO.NET v2 provides for this is the
"StatementCompleted" event. As well as knowing when each statement
completes, you also get a row count from each statement.

HTH,
 
Back
Top