Multiple Resultsets - RecordsAffected

[Paul]

Hello,

Im trying to send a number of insert statements to SQL Server 2000 at
one time, using ADO.NET.

The code im using is pretty standard (see below) and the insert is
performed all fine and dandy.
Dim objCmd As SqlCommand
Dim dr As IDataReader
objCmd = SqlConnection1.CreateCommand()
objCmd.Transaction = SqlConnection1.BeginTransaction()
objCmd.CommandType = CommandType.Text
objCmd.CommandText = v_sSQL
dr = objCmd.ExecuteReader(CommandBehavior.Default)

the v_sSQL parameter contains SQL much like
insert aa (aaa) values('aa')
insert aa (aaa) values('aa')
insert aa (aaa) values('aa')
(obviously these are dummy inserts - i dont usually create tables
named aa with a singular column aaa)

I'd like to know how many records each of the statements in my batch
affected (or created in the insert case). I was expecting in the above
case to have to call NextResult 3 times and get the .RecordsAffected
property value of 1 3 times as well. However this doesnt seem to be
the case. The first time i call .RecordsAffected I get a value of 3
(i.e. 3 records have been inserted) and the next call to NextResult
returns false i.e. no more resultsets. Whats going on here? How do i
distinguish between one statements results and another (much like
Query Analyser does) when posting a batch of statements to the server?


Another side issue I am having is with the InfoMessage handler which I
have implemented thus:

Private Sub SqlConnection1_InfoMessage(ByVal sender As Object, ByVal e
As System.Data.SqlClient.SqlInfoMessageEventArgs) Handles
SqlConnection1.InfoMessage
Dim objError As SqlError
For Each objError In e.Errors
m_sMessages = m_sMessages & objError.Message & vbCrLf
Next
End Sub

Again, im getting the messages all fine and dandy, however in this
case
insert aa (aaa) values('aa')
insert aa (aaa) values('aa')
PRINT 'Hello World'
I get the hello world message before i have a chance to interrogate
the DataReader for the results of the first two statements. Anybody
know a way of getting things to happen in the correct order when
working with multiple resultsets and server messages?

Thanks in advance.
Paul.

 

[William Ryan eMVP]

I think your problem in a nutshell stems from the fact that you are using a
dataReader to do all of your udpates and while that's totally legal, if you
want the behavior your describing, there's more effective ways of getting
there, namely executeNonQuery

See comments below

Hello,

Im trying to send a number of insert statements to SQL Server 2000 at
one time, using ADO.NET.

The code im using is pretty standard (see below) and the insert is
performed all fine and dandy.
Dim objCmd As SqlCommand
Dim dr As IDataReader
objCmd = SqlConnection1.CreateCommand()
objCmd.Transaction = SqlConnection1.BeginTransaction()
objCmd.CommandType = CommandType.Text
objCmd.CommandText = v_sSQL
dr = objCmd.ExecuteReader(CommandBehavior.Default)

the v_sSQL parameter contains SQL much like
insert aa (aaa) values('aa')
insert aa (aaa) values('aa')
insert aa (aaa) values('aa')
(obviously these are dummy inserts - i dont usually create tables
named aa with a singular column aaa)

I'd like to know how many records each of the statements in my batch
affected (or created in the insert case). I was expecting in the above
case to have to call NextResult 3 times and get the .RecordsAffected
property value of 1 3 times as well. However this doesnt seem to be
the case. The first time i call .RecordsAffected I get a value of 3
(i.e. 3 records have been inserted) and the next call to NextResult
returns false i.e. no more resultsets. Whats going on here? How do i
distinguish between one statements results and another (much like
Query Analyser does) when posting a batch of statements to the server?

How many calls to ExecuteNonQuery are you dealing with here? I'm guessing
3? Each time you call the ExecuteNonQuery, it'll return an int value
indicating the number of rows affected if applicable. I think part of the
problem is that you're using a Reader. .NextResult is a method of the
reader and although it works, I'd use ExcecuteNonQuery for this. Granted a
dataReader is used under the hood to accomplish this, but the model of
iterating through and checking next result is an uncomfortable fit here.
http://msdn.microsoft.com/library/d...clientsqlcommandclassexecutenonquerytopic.asp
I think this will fix part fo the problem b/c it returns an integer. (or -1
if the transaction fails).

Another thing you may want to do (although not necessary in this situation
but a good way to do things in other cases ) is use OutPut parameters.
Similarly, if at all possible, I'd consider moving those transactions to the
back end.

Another side issue I am having is with the InfoMessage handler which I
have implemented thus:

Private Sub SqlConnection1_InfoMessage(ByVal sender As Object, ByVal e
As System.Data.SqlClient.SqlInfoMessageEventArgs) Handles
SqlConnection1.InfoMessage
Dim objError As SqlError
For Each objError In e.Errors
m_sMessages = m_sMessages & objError.Message & vbCrLf
Next
End Sub

Again, im getting the messages all fine and dandy, however in this
case
insert aa (aaa) values('aa')
insert aa (aaa) values('aa')
PRINT 'Hello World'
I get the hello world message before i have a chance to interrogate
the DataReader for the results of the first two statements. Anybody
know a way of getting things to happen in the correct order when
working with multiple resultsets and server messages?

This isn't a problem. because HelloWorld is sent before the reader is
processed Remember that you still have to walk a reader to get anything
out of it, and you don't need to ever call while dr.Read() for instance
(although it'd be pretty useless if you didn't read from it). InfoMessage
has already been processed at this point so the timing is actually correct.
Again I think if you use ExecuteNonQuery instead, you can find those values.
You don't have to walk a datareader to find out what happened on updates if
you don't use one

Thanks in advance.
Paul.

--

W.G. Ryan, eMVP

http://forums.devbuzz.com/
http://www.knowdotnet.com/williamryan.html
http://www.msmvps.com/WilliamRyan/

 

[William \(Bill\) Vaughn]

Mr. Ryan is correct (as usual), but I would never use ADO to do bulk
operations. Build a delimited file and call DTS. It's far faster, more
robust and it works...

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________

 

[Paul]

William and William,

I very much appreciate both of your comments, however, the application
that I am working on requires integration of a "query analyser style"
tool, that will allow users to execute ad-hoc queries (they are all
well trained in T-SQL). In fact, if I stop beating around the bush, Im
trying to very much mimic Query Analyser because this is the
application that our Data Analysts are used to working with. I know
that the usual initial reaction to this sort of thing is 'Oh my god,
users building ad-hoc queries, performing updates etc etc', but I'm an
experienced developer, Ive done the analysis and in this case it is
necessary.

So, getting down to the real problem:

How do I take a string which contains one to many T-SQL statements,
execute it as a batch and display the results in a style if not
exactly the same as Query Analyser then very close to it. This should
include the resultsets from sql statement, information relating to the
number of records inserted / updated / deleted + any "information
messages" (i.e InfoMessages) that are produced by either the server or
the script (e.g. a PRINT statement).

If for instance you know of a way of parsing a series of statements
into seperate statements so that I could execute them one by one then
that would be great, but as far as I can see this itself is a non
trivial problem, especially considering that the users really do need
the use of the full T-SQL language, including things like ALTER, DROP
etc etc.

I really have been all the place for this one, including ADO, RDO even
to underlying ODBC API calls (I actually got this working very well,
except that the retrieval of resultsets was "very slow" and I dont
have the experience with ODBC to know where to start with performance
tuning.) Finally, I have tried using ADO.NET and this would be perfect
if I could resolve the two problems I highlighted in my previous post
i.e. the summing of RecordsAffected and the incorrect order of the
InfoMessages.

If you think you know of anyway I can achieve my goals then please let
me know, Im currently tearing my hair out with this one and have been
for a good few days now.

Thanks in advance, Paul Gamblen. MCSD.

 

[William \(Bill\) Vaughn]

Strangely enough, what you're asking to do was a lab in one of my MSU
courses (years ago). It's not hard to do. You simply define a "separator"
like "GO". Use string parsing to separate out the scripts (one or more
statements) and use ExecuteReader on each script in the batch. You'll have
to process the resultsets in code and step to secondary resultsets (if there
are any). Another approach is to use the DataAdapter fill, but in this case
you'll get N DataTables--one for each rowset. You might want the
intermediate row-less resultsets (for rowcounts etc.) so the DataReader is
the route usually taken. I might have some QuickBasic or C code somewhere
that does this--with DBLib.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________

 

[Greg Low \(MVP\)]

Hi Paul & Bill,

One of the problems with the Execute methods in ADO.NET is that if you send
a statement that causes multiple updates:

eg UPDATE blah WHERE somecondition UPDATE somethingelse WHERE
someothercondition

all you get is the total rows affected for both statements. Where you have a
separator like GO, it's no problem but you can't get the QA behaviour
without it in the current version. It's one of the things we had to live
with in the managed version of our MSDE Manager product.

Not a big help for now but in ADO.NET v2, there is a StatementCompleted
event that passes a RowsAffected value in the EventArgs that will allow
this. They also have better options for firing the InfoMessage event that
will help you get more QA-like behaviour too.

HTH,