No columns were selected" when there is no result returned by a query in when using QueryMultiple

[KamranShahid]

I am using .net core 3.1 with dapper 2.0.35 I am getting error "No columns were selected" when using QueryMultiple

My code looks like following

public static List<List<dynamic>> GetEntityObjectFromClientId(string clientId, int? entityId)
        {
            List<List<dynamic>> lst = null;
            var _params = new DynamicParameters();
            _params.Add("@_client_id", clientId, DbType.String, direction: ParameterDirection.Input);
            _params.Add("@_entity_id", entityId, DbType.Int32, direction: ParameterDirection.Input);

            using (var db = new MySqlConnection(ConnectionString))
            {
                var resultSet = db.QueryMultiple(StoredProceduresName.mysqp, _params, commandType: CommandType.StoredProcedure);

                if (resultSet != null && HasRows(resultSet))//just added HasRows hack
                {
                    var result1 = resultSet.Read();
                    if (result1 != null && result1.AsList().Count > 0)
                    {
                        lst = new List<List<dynamic>>();
                        lst.Add(result1.AsList());
                        lst.Add(resultSet.Read().AsList());
                        lst.Add(resultSet.Read().AsList());
                    }
                }
            }
            return lst;
        }

I have found a hack

   private static bool HasRows(SqlMapper.GridReader reader)//https://github.com/StackExchange/Dapper/issues/327
        {
            MySqlDataReader internalReader = (MySqlDataReader)typeof(SqlMapper.GridReader).
                GetField
                ("reader",
                System.Reflection.BindingFlags.NonPublic | System.Reflection.BindingFlags.Instance)
                .GetValue(reader);
            return internalReader.HasRows;
        }

Ideally HasRows method should be built into dapper

[bgrainger]

Your code looks unnecessarily complicated to me (and the HasRows hack should not be needed).

Does your stored procedure return multiple result sets? Using this sample sproc from the tests for MySqlConnector, I can execute the following code just fine:

var _params = new DynamicParameters();
_params.Add("@pivot", 1, DbType.String, direction: ParameterDirection.Input);

using var resultSet = connection.QueryMultiple("multiple_result_sets", _params, commandType: CommandType.StoredProcedure);
resultSet.Read<string>(); // first result set
resultSet.Read<string>(); // second result set

(The choice of pivot changes how many rows are in each result set, and a result set with 0 rows is read just fine.)

This code works with both MySqlConnector and Oracle's MySQL Connector/NET.

[KamranShahid]

Yes. My stored procedure return three different type of result sets with multiple records. this issue is in mysql on linux.

[NickCraver]

@KamranShahid did you try the approach with .QueryMultiple()? Using the generic .Read<T> should do what you want here. If the stored procedure is returning a varying number of result sets (instead of empty ones), that's another problem - and a critical detail here. Is that the case?

[chernikov]

Here is a problem with call .QueryMultiple():

I call SP and if parameters is ok, SP returns me a bunch of tables, seven or even more. If parameters isn't ok (userId wrong, or haven't the access), SP return me any tables.

So, I can't use grid.IsConsumed because it initially false in second case.

Right now I use @KamranShahid hack, but how I should use properly?

Sample code:

public (int, List<dynamic>) AuthorizeResourcePage(int userID, string resoursePath)
{
  using IDbConnection db = new SqlConnection(connectionString);
  var parameters = new DynamicParameters();
  AddAuthenticationUser(parameters);
  parameters.Add("intUserID", userID == 0 ? Convert.DBNull : userID, DbType.Int32);
  parameters.Add("ResourceLocation", resoursePath, DbType.String, ParameterDirection.Input, 300);
  parameters.Add("bitIsPostBack", false, DbType.Boolean);
  parameters.Add("@returnValue", dbType: DbType.Int32, direction: ParameterDirection.ReturnValue);

  GridReader grid = db.QueryMultiple("AUTHORIZE_S_AuthorizeResourcePage", parameters, commandType: CommandType.StoredProcedure);

  var hasRows = HasRows(grid);

  var resultsTables = new List<dynamic>();
  while (hasRows && !grid.IsConsumed)
  {
      resultsTables.Add(grid.Read());
  }
  var returnValue = parameters.Get<int>("returnValue");
  return (returnValue, resultsTables);
}

[vpekarek]

Have the same problem here. We had to return SELECT NULL for each collection, that we expected to be returned. But for SP with more tables it is a pain.

I will expect that null value will be returned from QueryMultipleAsync method.

[jonagh]

Yup, this is a blocking issue for me. I have been trying to update a semi-large legacy app to use Dapper, but there are many stored-procs that return no results in a "failure" case (and one or more result sets in the "success" case).

Ignoring the HasRows reflection hack mentioned above - it seems as though I am unable to use Dapper for this situation! Can't we have the underlying Reader.HasRows exposed?

I must be missing something? So many people using Dapper, did everyone in this situation change their procs to accomodate Dapper?

Related: https://github.com/DapperLib/Dapper/issues/327 https://github.com/DapperLib/Dapper/issues/751

[jsreynolds]

I have to concur; this just burned a few hours for us for something that would seem to be very unexpected behavior.