2

First, just to be clear, I recognize that ExecuteNonQuery should only be used for UPDATE, INSERT, or DELETE statements, and that for all other types of statements, such as SELECT, the return value is -1.

My question is, why does the following stored procedure:

CREATE PROCEDURE `ExampleProc`(IN Name varchar(60), OUT ID bigint(20), OUT SP varchar(255))
BEGIN
    SELECT id, sp INTO ID, SP FROM users WHERE username = Name;
END

When executed using ExecuteNonQuery:

using (var conn = new MySqlConnection("Secret"))
{
    conn.Open();
    using (var cmd = new MySqlCommand("ExampleProc", conn) { CommandType = CommandType.StoredProcedure })
    {
        cmd.Parameters.AddWithValue("Name", request.Name).MySqlDbType = MySqlDbType.VarChar;
        cmd.Parameters.Add("ID", MySqlDbType.Int64).Direction = ParameterDirection.Output;
        cmd.Parameters.Add("SP", MySqlDbType.VarChar).Direction = ParameterDirection.Output;
        var returnVal = cmd.ExecuteNonQuery();
    }
}

Yield a 0 in returnVal when a row with Name is not found, and a 1 if it is found? Based on all the documentation I have read, since the stored procedure contains a single SELECT statement, I should be seeing -1 being returned no matter what. Instead, it's returning the number of rows affected/found, which doesn't make sense according to the documentation.

Lastly, I've also tried using just "SELECT *" instead of "SELECT id, sp INTO ID, SP". That seems to always return 0. Still not the -1 that I am expecting.

Mrinal Kamboj
  • 11,300
  • 5
  • 40
  • 74
Eaton
  • 1,310
  • 2
  • 15
  • 31
  • Check the link https://msdn.microsoft.com/en-in/library/ms188774.aspx for Select statement try setting `Set RowCount ` and test. Also please note behavior you have mentioned is specific to Sql Server, may behave different for different databases – Mrinal Kamboj Aug 17 '16 at 04:38

1 Answers1

1

Let's understand the working of the ExecuteNonQuery API in the ADO.Net, as you have understood from the documentation that it shall provide the number of Rows impacted as part of DML query and -1 for the Select query.

However the internal working is such that in case of Sql Server, default setting is Set NoCount Off helps in writing to the TDS protocol, the number of modified rows and same is read by the Ado.Net APIs, thus the correct result, this is the result of native binary integration of ado.net with Sql Server. more details at the following links:

Ado.Net TDS Protocol

SET NOCOUNT ON usage

When it comes to other database, like MySql, Oracle, there's no guarantee of the same behavior, as their integration is not at same level as Sql Server. This is the main reason for discrepancy, posted in the question, to make it consistent across the database, you may want to rely of a Output Parameter to automatically fill the value internally in the procedure, since every database has mechanism to figure out the number of rows updated

Community
  • 1
  • 1
Mrinal Kamboj
  • 11,300
  • 5
  • 40
  • 74
  • Thank you for the answer. It seems strange that there would be a discrepancy, especially since the same behavior is described right in the MySQL docs: https://dev.mysql.com/doc/dev/connector-net/html/M_MySql_Data_MySqlClient_MySqlCommand_ExecuteNonQuery.htm. Additionally, as a test, instead of a stored procedure, I set up MySqlCommand with "SELECT * FROM users" and that returns -1, so it appears there is something wrong with how the stored procedure works. Any tips? – Eaton Aug 17 '16 at 17:00
  • @Eaton need to get little deeper in the finer details, though the link posted by you provide the same details, but I am certain there's some aspect or setting like `NoCountON` of the `Sql Server` for `My Sql`, which need to be explored to understand the deviation in the behavior – Mrinal Kamboj Aug 17 '16 at 17:16
  • I did some research and could not find anything. NOCOUNT doesn't appear to be used in any form in MySQL. – Eaton Aug 18 '16 at 03:46
  • `NOCOUNT` is `Sql Server` specific, there has to be another option, main point remains the result to be correct need understanding of the implementation as done in `Sql Server`, which only data connector team can explain. I remember `Oracle` doesn't work correctly with `ExecuteNonQuery`, as `ODP.Net` don't have option like `Sql Server` – Mrinal Kamboj Aug 18 '16 at 04:38
  • This is looking like a bug to me. I tried using ExecuteReader and its returned MySqlDataReader's "HasRows" property is true, even though it should be false since I pass in a bogus username. Interestingly, using the returned MySqlDataReader's "Read" method returns false the first time. At this point I think I will just use Read to determine whether the SELECT in my stored procedure finds anything, as these other properties and return values appear to be unreliable. – Eaton Aug 18 '16 at 06:09
  • May or may not be a bug, since it is not mandatory for the provider to implement everything provided by abstract class like `DbDataReader`, which will be the base class for the `MySqlDataReader`, its important that you go as per documentation and if it is not inline then a bug. Not necessary for everyone to follow Microsoft documentation – Mrinal Kamboj Aug 18 '16 at 06:21