CODEWITHSUNDEEP
c#sql.netsql-servert-sql
João Martins
João Martins

Reputation: 1046

SQL Transaction Report/Status

I've been searching for a way to get information about a completed SQL transaction. Since I'm using C# to connect to a DB I want to know how many records were updated, inserted or deleted during a specific transaction. Is there any way to do this?

Thanks in advance

Upvotes: 4

Views: 352

Answers (2)

Ted Cohen
Ted Cohen

Reputation: 1041

Note that in sql server rows affected for an update statement tells you how many rows meet the selection criteria, not the count of rows that were actually changed in contrast to mysql which returns the number of rows that were actually changed. I prefer to know the number of rows actually changed. If there is a way to do that in sql server, I would like to know how.

Upvotes: 1

Darren
Darren

Reputation: 70796

ExecuteNonQuery() returns the number of rows affected. 

    SqlCommand command = new SqlCommand(queryString, connection);
    command.Connection.Open();
    int rowsAffected = command.ExecuteNonQuery();

If you want multiple records, i.e. the total number of records deleted, inserted, updated etc. You would have to use an OUTPUT parameter. 

        command.Parameters.Add("@DeletedRecords", SqlDbType.Int);
        command.Parameters["@DeletedRecords"].Direction = ParameterDirection.Output;

Then in your transactional stored procedure:

CREATE PROCEDURE [dbo].[TransactionReportStatus]
      @DeletedRecords INT OUTPUT
AS
BEGIN
     -- Your transaction with delete statements
     SET @DeletedRecords = @@ROWCOUNT         
END

@@ROWCOUNT is SQL Server's equivalent of ExecuteNonQuery()

Upvotes: 1

Related Questions