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?

Solution 1

ExecuteNonQuery - returns the number of rows affected.

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

Solution 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.

Solution 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

Solution 4

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

View more solutions