Re: How to get RecordsAffected when an exception occurs

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

From: Sean Nolan (seann_at_imgno%spaminc.com)
Date: 03/18/05 

Date: Fri, 18 Mar 2005 16:33:01 -0600

Thanks Sushil,

I did see that I can do that in the 2.0 Framework, and I'm glad that new
event is there. I have sent in a feature suggestion for a RecordsAffected
property too, because it seems like that would be so easy compared to all
that code you have to write to do it with the StatementCompleted event.
Basically you write all that code just to duplicate what the SqlCommand
class has already done internally.

Sean

"Sushil Chordia" <sushilc@online.microsoft.com> wrote in message
news:eXsjPdALFHA.1284@TK2MSFTNGP14.phx.gbl...
> Sean, you are right. If you are using Whiudbey you can use the
> StatementCompleted event to get the RecordsCount after each statement is
> completed. This is a much neater solution. Here is code snippet:
> <Code Snippet>
> public static int Main(string[] args) {
> SqlConnection sqlconnection1 = new
> SqlConnection("ConnectionString");
> sqlconnection1.Open();
> SqlCommand sqlcommand1 = sqlconnection1.CreateCommand();
> sqlcommand1.StatementCompleted += new
> StatementCompletedEventHandler(SqlCommand_OnStatementCompleted);
> sqlcommand1.CommandText = "delete from t where i =1;insert into
> t values (1)";
> Int32 int321 = sqlcommand1.ExecuteNonQuery(); // -1
> return 1;
> }
>
> public static void SqlCommand_OnStatementCompleted(Object sender,
> StatementCompletedEventArgs e) {
>
> Console.WriteLine("StatementCompleted Event: " + sender + ", "
> + e);
> Console.WriteLine("RecordCount:"+e.RecordCount);
> }
> </Code Snippet>
> <Output>
> StatementCompleted Event: System.Data.SqlClient.SqlCommand,
> System.Data.StatementCompletedEventArgs
> RecordCount:4
> StatementCompleted Event: System.Data.SqlClient.SqlCommand,
> System.Data.StatementCompletedEventArgs
> RecordCount:1
> </Output>
>
> One more coarse way would be to SELECT @@Rowcount in between each
> statement.
>
> HTH,
> Sushil.
>
> "Sean Nolan" <seann@imgno%spaminc.com> wrote in message
> news:ubquLS$KFHA.3104@TK2MSFTNGP14.phx.gbl...
>> Thanks, but that won't work if the last statement fails - for example, if
>> I have 4 INSERTS in the query text and the first three succeed and last
>> one fails. I will get an exception and @@ROWCOUNT will be zero because
>> the last INSERT before the @@ROWCOUNT generated an error - also, in this
>> case even if they all succeed, @@ROWCOUNT would only return 1 because it
>> only looks at the last statement.
>>
>> In this case I don't want to roll back the first three inserts that
>> succeeded, but I do want to know that they succeeded. I could execute the
>> INSERTs individually but that is much slower, so I'd like to just execute
>> the whole thing in one call and at the end know that 3 rows were
>> inserted.
>>
>> Sean
>>
>> "Sushil Chordia" <sushilc@online.microsoft.com> wrote in message
>> news:Oakb0F%23KFHA.3380@TK2MSFTNGP15.phx.gbl...
>>>A simple work-around would be to use the SELECT @@ROWCOUNT to get the
>>>right information.
>>> HTH,
>>> Sushil.
>>> "Sean Nolan" <seann@imgno%spaminc.com> wrote in message
>>> news:eDjx%23t9KFHA.2716@TK2MSFTNGP15.phx.gbl...
>>>> If I have this code
>>>>
>>>> SqlCommand cmd = new SqlCommand("<text with multiple inserts>", conn);
>>>> int recs = 0;
>>>> try
>>>> {
>>>> recs = cmd.ExecuteNonQuery();
>>>> }
>>>> catch (SqlException exc)
>>>> {
>>>> //whatever
>>>> }
>>>>
>>>> If an exception occurs the value obviously isn't assigned to my
>>>> variable recs, however records may well have been affected even when an
>>>> exception occurs. The problem is that I cannot find any way to find out
>>>> how many records were affected. When I debug this, I can see that the
>>>> SqlCommand class has an internal field that correctly shows how many
>>>> records were affected, but for some reason the SqlCommand class does
>>>> not have an accessible RecordsAffected property that shows that value,
>>>> so you can't get to it in regular code.
>>>>
>>>> This seems like a design flaw in the SqlCommand class?
>>>>
>>>> Sean
>>>>
>>>
>>>
>>
>>
>
>