CODEWITHSUNDEEP
c#sqlexceptionexecutenonquery
Rutuja
Rutuja

Reputation: 7

How to get the number of rows affected, if it hits sql exception while running the sql script in c#

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.

Upvotes: -2

Views: 358

Answers (1)

Rutuja
Rutuja

Reputation: 7

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.

Upvotes: 0

Related Questions