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?

Answer 1

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);
            }
        }

Answer 2

ExecuteNonQuery - returns the number of rows affected.

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

Answer 3

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.

Answer 4

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

Answer 5

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