-2

I need to get the number of rows affected if ExecuteNonQuery() hits the SqlException. I'm able to see the Number of affected rows while debugging in c#, but unable to get that, like-

cmd.InternalRecordsAffected

cmd._rowsAffected

I have already tried using below code along with PRINT statement, but it didn't work with my case:

conn.InfoMessage += delegate (object sender, SqlInfoMessageEventArgs e)
{
     sqlMessage += "\n" + e.Message;
};

Our application is running the sql scripts using c#. Below is the sample code:

int rowsAffected = -1;

using (SqlConnection conn = new SqlConnection(connectionString))
{
    if (conn != null && conn.State != ConnectionState.Open)
    {
        conn.Open();
    }
    using (SqlCommand cmd = new SqlCommand())
    {
        cmd.Connection = conn;
        StreamReader reader = new StreamReader(strFilePath);
        string sqlQuery = reader.ReadToEnd();
        cmd.CommandText = sqlQuery;
        try
        {
            rowsAffected = cmd.ExecuteNonQuery();
        }
        catch (SqlException ex)
        {
            //How to get the number of rows affected here?
        }
        if (conn.State != ConnectionState.Closed)
        {
            conn.Close();
        }
    }
}

In exception, getting rowsAffected as -1, but needs the actual count.

Community
  • 1
  • 1
Rutuja
  • 7
  • 3
  • 3
    If you use transactions then it's 0 affected rows. – Sinatr Apr 11 '19 at 11:29
  • Also, when a query fails, how should it know how many rows it yielded? – Patrick Hofman Apr 11 '19 at 11:34
  • 1
    What you want to do with the rowsAffected when the code enters the exception? Is this an [XY Problem](https://meta.stackexchange.com/questions/66377/what-is-the-xy-problem)? – Steve Apr 11 '19 at 11:39
  • As I mentioned in the description, we are running sql scripts via c#, I need to log how many records affected. – Rutuja Apr 11 '19 at 12:03
  • So your file contains an undefined number of sql statements separated by a semicolons and you want to know at which point your script contains a error? – Steve Apr 11 '19 at 12:06
  • If I execute same script on database which consists suppose 3 queries and for first query it fails, it still executes remaining two queries which is fine. So I want to know the number of records affected by the 2nd and 3rd queries, even if it hits exception for 1st one in c#. Hope that makes sense. – Rutuja Apr 11 '19 at 12:07
  • That is not how it works. Split your statements and execute them separately. – Patrick Hofman Apr 11 '19 at 12:11
  • Did you try if, after a failing statement, the following ones are still executed when you use C# code and the ADO.NET libraries? It is not the same thing, in code you don't have an IDE like Sql Server Management Studio that handles the exceptions and continue – Steve Apr 11 '19 at 12:11
  • @Patrick: That's not possible as we are having too many script files containing multiple sql statements which need to be run via c# application. – Rutuja Apr 11 '19 at 12:19
  • You can tell as much as you want that "it is not possible", but it is not possible to do that using ADO.NET. So you have two options: you abandon ADO.NET or your split the statements. – Patrick Hofman Apr 11 '19 at 12:21
  • @Steve: Yes, I have just confirmed again and it's executing the 2nd and 3rd insert queries, even if it fails for 1st one. – Rutuja Apr 11 '19 at 12:21
  • No, it does not. Really. @Rutuja – Patrick Hofman Apr 11 '19 at 12:21
  • I ended up with the solution of adding 'GO' after each sql statement and splitting by it while running the script file in c#, so, I will get the number of affected rows till the exception occurs and rest of the queries will not get execute. Thank you guys. – Rutuja Apr 11 '19 at 13:09

1 Answers1

0

I ended up with the solution of adding 'GO' after each sql statement and splitting by it while running the script file in c#, so, I will get the number of affected rows till the exception occurs and rest of the queries will not get execute.

Rutuja
  • 7
  • 3