Get affected rows on ExecuteNonQuery

Transactions and handling bulk operations

When it comes to bulk operations or when operations are innitiated within a transaction, consider using BeginTransaction(). This ensures an accurate count of affected rows. Here's an example of how you'd handle transactions:

using (var con = new SqlConnection(connectionString))
{
    con.Open();
    using (var tran = con.BeginTransaction())
    {
        try
        {
            var cmd = new SqlCommand("YOUR SQL COMMAND", con, tran);
            int affectedRows = cmd.ExecuteNonQuery(); // Get your row count here
            tran.Commit(); // Don't forget to commit, or your database will get angry
            return affectedRows;
        }
        catch
        {
            tran.Rollback(); // In case of tantrums
            throw;
        }
    }
}


MySQL specifics and how to handle them

In the case of MySQL, make sure to append UseAffectedRows=True to your connection string. MySqlCommand.ExecuteNonQuery() may not provide an accurate count in the absence of this line, especially if triggers are involved.

SQLite and the total_changes() method

For those working with SQLite, another tool available for you is SELECT total_changes();. By executing this after your primary operation, you will get the overall number of row changes, including those that came about because of triggers:

int totalChanges;
using(var command = new SQLiteCommand("SELECT total_changes();", new SQLiteConnection(connectionString)))
{
    totalChanges = Convert.ToInt32(command.ExecuteScalar()); // SQLite being extra with its total_changes()
}


Mind your exceptions: A note on resource management

When executing SQL operations, bear in mind the significant role of resource management. Always enclose your connection code with using statements to prevent potential leaks and exceptions. Additionally, this ensures that connections are closed and properly disposed.

Understanding return values from ExecuteNonQuery

Resistance to confusion is futile when dealing with ExecuteNonQuery(). The method returns -1, not because it's an emo teen, but it wants you to understand that the number of rows affected is not reliable or simply no rows were affected—most often during a failed operation or a ROLLBACK. Positive numbers signal the successful creation or alteration (INSERT, UPDATE, or DELETE) of data.

Further reading and expanding knowledge

Ready to take a deeper dive into ExecuteNonQuery()? I highly recommend giving the SqlCommand documentation a peruse. It is an expanse of detailed descriptions on parameters, user instructions, and exceptions- a true haven for those gluttons of knowledge.

Tying it together: SQL and .NET

Understanding the role of ExecuteNonQuery in SQL operations with .NET is crucial for developing resilient and accurate data-access layers. Transactions, exception handling, and engine-specific nuances tie together into a complex, yet beautiful dance that is SQL operation management.

Remember to commit your transactions after bulk inserts. Failing to do so could lead to a database equivalent of a temper-tantrum, discarding all operations executed within the transaction.