Return value of ExecuteNonQuery() in the MySQL .NET Provider

As support for stored procedures was added in MySQL 5, I thought I'd blog about a difference in behavior I noticed between the SQL Server .NET Provider and the MySQL .NET Provider when executing stored procedures using ExecuteNonQuery().

You know that when you use ExecuteNonQuery() to execute a stored procedure (or any other SQL statements but we're going to talk about stored procedures here) it returns the number of records affected by the stored procedure. Now, what exactly is returned by the SQL Server Provider? I assume most developers who use the SQL Server Provider know that but in case you didn't know, the SQL Server Provider returns the total number of records affected by executing all statements in the stored procedure.

Let's take this example to make things clearer. For example, say that you have a stored procedure in which you first insert some records in a table then in the same procedure you update some records in another table. Let's assume that the procedure was executed and it inserted 5 records in the first table and updated 3 records in the second table, now what the SQL Server Provider returns exactly is the total number of records affected by executing this procedure which is 5 + 3 = 8.

On the other hand, the MySQL .NET Provider only returns the number of records affected by executing the last statement in the procedure, so referring to the preceding example, ExecuteNonQuery() will only return 3.

The point in this post is that if you're using the value returned from ExecuteNonQuery() for anything in your project, you just should be aware of the difference in behavior between the SQL Server Provider and the MySQL .NET Provider.

Happy programming!