Re: Using ADO.NET to prcoess T-SQL batches

Tech-Archive recommends: Fix windows errors by optimizing your registry

From: Greg Low \(MVP\) (greglow_at_lowell.com.au)
Date: 05/01/04 

Date: Sat, 1 May 2004 13:16:51 +1000

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, 

-- 
Greg Low (MVP)
MSDE Manager SQL Tools
www.whitebearconsulting.com
"Jeff Davis" <jeff.davis@tamtron.nospam.com.invalid> wrote in message
news:uufysEvLEHA.1484@tk2msftngp13.phx.gbl...
> 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
>
> "Graeme Malcolm" <graemem_cm@hotmail.com> wrote in message
> news:OnMSVCtLEHA.3696@TK2MSFTNGP09.phx.gbl...
> > 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 Han***" <anonymous@discussions.microsoft.com> wrote in message
> > news:6FE316FC-F849-4B9C-9B6A-189A05FF99C4@microsoft.com...
> > > 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.
> >
> >
>
>
Quantcast