Retrieve record counts from multiple statements

Question

I wrote my own SQL client because I was tired of SSMS. I want to capture the affected row counts just like SSMS but SqlCommand.StatementCompleted is raised multiple times when ExecuteReader finishes (or EndExecuteReader is called). Even though the statements in the batch complete with a regular interval, it seems like the DONE_IN_PROC messages are queued up on the client, and then dumped all at once, instead of being raised continuously. No InfoMessage events through-out the execution from SqlConnection, either.

Update above in italics.

Say, you have a SQL statement that updates rows in a loop, or something like that:

WHILE 1=1 
BEGIN 
  UPDATE tbl SET .... WHERE Id BETWEEN i AND i+10; 
  IF @@ROWCOUNT =0 BREAK; 
  SET i = i + 10; 
END

SSMS properly shows "(10 rows affected)" every X number of seconds or w/e. There are no records coming from the reader, since this is a UPDATE only statement, so can't use SqlDataReader to count the rows.

Is that possible, using the SqlClient library?

The simplified code in question is like this:

class ExecuteWorker
{
  public void Start(string query)
  {
    this._query = query;
    this._thread.Start(this.Work);
  }

  void Work()
  {
    using(var conn = new SqlConnection(this._connStr))
    {
      conn.Open();
      using(var command = conn.CreateCommand())
      {
        command.CommandText = this._query;
        using(var reader = command.ExecuteReader())
        {
          while(reader.Read())
          {
            this._control.BeginInvoke(new Action(()=>{
              // update UI
            }));
          }
        }
      }
    }
  }
}

A similar question is found here, though the description is less clear.

Answer 1

Why dont you move the sql into a stored procedure. Execute the stored procedure using a command object with a parameters collection including an output parameter. Within the stored procedure have a total count variable to sum each of the individual update rows affected. Return this as an output parameter.

Answer 2

When you update the db using ExecuteNonQuery method, it returns number of rows affected. This method is used when you use insert, update or delete sql command. Hope this is what you are looking for.

Answer 3

There are two events that you want to handle. The SqlConnection InfoMessage and the SqlCommand StatementCompleted.

Sorry for the VB code but you should be able to convert it.

Private Sub OnInfoMessage(sender As Object, args As SqlClient.SqlInfoMessageEventArgs)
    For Each sqlEvent As System.Data.SqlClient.SqlError In args.Errors
        Dim msg As String = String.Format("Msg {0}, Level {1}, State {2}, Line {3}{4}", sqlEvent.Number, sqlEvent.Class, sqlEvent.State, sqlEvent.LineNumber, Environment.NewLine)
        'Write msg to output
    Next
End Sub

Private Sub OnStatementCompleted(sender As Object, args As StatementCompletedEventArgs)
    Dim msg As String = String.Format("({0} row(s) affected)", args.RecordCount)
    'Write msg to output
End Sub

Public Sub Work()
    Using dbc As New SqlConnection(Me.ConnectionString),
          dbcmd As New SqlCommand()

        dbcmd.Connection = dbc
        dbcmd.CommandTimeout = Convert.ToInt32(timeout) ' you will want to set this otherwise it will timeout after 30 seconds
        'set other dbcmd properties

        AddHandler dbc.InfoMessage, New SqlInfoMessageEventHandler(AddressOf OnInfoMessage)
        AddHandler dbcmd.StatementCompleted, AddressOf OnStatementCompleted

        dbc.Open()

        'dbcmd.ExecuteNonQuery() or Using dataReader As SqlDataReader = dbcmd.ExecuteReader()

    End Using
End Sub