Get affected rows on ExecuteNonQuery

Question

I am currently working on a C# project and I am running an insert query which also does a select at the same time, e.g.:

INSERT INTO table (SELECT * FROM table WHERE column=date)

Is there a way I can see how many rows were inserted during this query?

Valerievalerio

Answer 1

ExecuteNonQuery - returns the number of rows affected.

SqlCommand comm;
// other codes
int numberOfRecords = comm.ExecuteNonQuery();

Mae

I'm doing this, and for some reason even though a new row was created in my table, this methods returns -1. Should I also have something in my sql statement? – Tahsildar

SqlCommand.ExecuteNonQuery() returns -1 when doing Insert / Update / Delete See blogs.msdn.microsoft.com/spike/2009/01/27/… – Clavicytherium

This does not always work as one might expect. For an UPDATE the number of returned rows is the number of rows that could have been changed rather than the number of rows that were actually changed. Let's say you're doing an UPDATE where up to 50 rows might be affected. However for 35 of those rows the UPDATE doesn't cause any data to be changed. The data changes for only 15 rows of the 50 possible rows. In this case one would hope that "15" would be the returned value but instead the returned value is 50, the total number of rows. – Everglades

@Ian: so is there a way to get the 15? – Piecrust

@serv-inc, I don't think there is a way to get "15" for an UPDATE with a single line of code. You would need to execute a SELECT count() statement separately *before the update is run to find out how many rows are going to be updated. As long as your tables are properly indexed this won't add any noticeable additional overhead. – Everglades

@serv-inc: Another option is to make sure that the number of potentially affected rows is the same as the number of actually affected rows. So rather than doing "UPDATE table SET val1 = 5;" use "UPDATE table SET val1 = 5 WHERE val1 <> 5;". – Everglades

@Ian It should be pointed out that that is how any SQL RDBMS behaves, not some behavior unique to the SqlClient class or SQL Server. If you tell an RDBMS to update a value to the value already present, the RDBMS will still do all the relevant work, obtaining locks on the rows, filling your transaction log, modifying relevant indices, firing relevant triggers, etc. It's entirely up to the user (aka, the application developer) to not specify redundant updates if they don't want to perform redundant updates. – Settlement

Answer 2

If you run the SQL from your question in a SqlCommand and check the return value of ExecuteNonQuery it should tell you how many records were affected.

From the documentation:

Return Value
Type: System.Int32
The number of rows affected.

Rosalvarosalyn

Answer 3

Be sure of one thing also You need to add a statement in the connection string For example:

string const "Server=localhost; PORT=3306; Database=db; User id=root; password='';UseAffectedRows=True";
MySqlConnection con = new MySqlConnection(const);
con.Open();
MySqlCommand cmd = new MySqlCommand(con);
cmd.CommandText = "Update db set table = value where Column = value";
int numberOfRecords = cmd.ExecuteNonQuery();

Be sure of:

UseAffectedRows=True

so it will return a right value of rows affected

Devin

I'm getting ArgumentException: Keyword not supported: 'useaffectedrows'. exception. – Guck

Answer 4

ExecuteNonQuery return the affected rows ONLY WHEN Use Affected Rows in the connections properties is set, if not (default) returns matched rows.

Nedranedrah

I'm getting ArgumentException: Keyword not supported: 'useaffectedrows'. exception. – Guck

The right word is "UseAffectedRows" – Nedranedrah

"UseAffectedRows" still doesn't work. This is not the correct answer. – Predator

Answer 5

If you run a bulk of ExecuteNonQuery(), and commit them all in once, you can get the number of total changes after connection by read the return value from "SELECT total_changes();"

The function to get the total changes:

public static long GetTotalChanges(SQLiteConnection m_dbConnection)
        {
            string sql = "SELECT total_changes();";
            using (SQLiteCommand command = new SQLiteCommand(sql, m_dbConnection))
            {
                using (SQLiteDataReader reader = command.ExecuteReader())
                {
                    reader.Read();
                    return (long)reader[0];
                }
            }
        }

Use it in another function:

public static long MyBulkInserts()
        {
            using (SQLiteConnection m_dbConnection = new SQLiteConnection())
            {
                m_dbConnection.Open();
                using (var cmd = new SQLiteCommand(m_dbConnection))
                {
                    using (var transaction = m_dbConnection.BeginTransaction())
                    {
                        //loop of bulk inserts
                        {
                            cmd.ExecuteNonQuery();
                        }
                        transaction.Commit();
                    }
                }
                return GetTotalChanges(m_dbConnection);
            }
        }

Messing

Answer 6

I realize you are trying to do this with the ExecuteNonquery, but what about ExecuteScalar and using the OUTPUT directive in your query?

For Insert:

declare @resulttable
(
  rowid int
)
insert yourtable
output inserted.rowid
  into @resulttable
select *
from someothertable

select count(1) affectedrows
from @resulttable

or for Update, if you only want to know the rows that changed

declare @resulttable
(
  beforefield1 varchar(255),
  afterfield1 varchar(255)
)
update tbl1
set field1 = replace(field1, 'oldstring', 'newstring')
output deleted.field1,
    inserted.field1
  into @resulttable
from someothertable

select count(1) affectedrows
from @resulttable
where beforefield1 != afterfield1;

Brink