Dapper: QueryAsync throws "No columns were selected" when stored proc doesn't perform a select statement

[toannguyen83]

Tested on Dapper 1.42 and 1.50.2

The following sql statement will simulate creating a temp stored proc and execute it with dapper

create procedure #TestProcedure
as
begin

    -- basically a failed if statement, so the select is not happening and the stored proc return nothing
    if 1=0
    begin
        select 
            'Password' as ColumnName,
            'Cannot be the same as your username' as [Message]
    end

end

exec #TestProcedure

When using Query version, it works fine and no results return. When using QueryAsync version, it throws "No columns were selected" error.

I attached a code sample using microsoft localdb. The sample project is using Dapper 1.50.2 and structure similar to the one I submitted in #501

DapperBugReplication20160827.zip

[adamhartford]

I ran into this problem too. I also noticed that it works correctly in Mono, but not .NET.

[nickInMN]

I'm having this same problem. Is there any plan to get this fixed, it is a pretty serious issue. Having to query the database to get the object to update is cumbersome.

[andreujuanc]

I can confirm that the issue affects async version of query, but it works fine otherwise.

[joaopgrassi]

Nothing on this? This is a real problem.. We have a bunch of procs that throw errors if the sensitive data you queried is not there:

IF @@ROWCOUNT = 0 RAISERROR ('User does not exists', 11, 1)

The database is old, and I'm not going to ask them to change 1000 procs to adjust to my code. multireader should expose some safe reader so we don't get these errors.

[alexbudisan]

The same thing happened to me. When I ran a stored procedure using QueryAsync I receive the "No columns were selected" exception. If I use Query everything works ok and I can retrieve the return value. Is there any workaround apart from simply not using the Async method?

This is using the latest version of Dapper, 1.50.4.0.

[peterdeme]

Or can we have a command parameter something like SuppressNoColumnError?
@mgravell

[NickCraver]

We mixed up issues here a while ago and I just realized that reading back through here. This is fixed for the next release and will be up on MyGet shortly after the next build.

[DatBluBat]

Same here using latest version :(

[peterdeme]

@DatBluBat the latest version is 4 months old, @NickCraver when can this get released?

[nickInMN]

Since replying to this issue I've used Dapper quite a bit more. One workaround for this that I've found is to use .ExecuteAsync. It seems to work just fine for queries that don't return anything. The return value appears to be the number of rows the query affected.

[peterdeme]

Why are the release cycles so slow? This was fixed 2 months ago and we still can't use this.

[NickCraver]

@peterdeme It's simply a matter of limited time, but I see now I never added the MyGet feeds to the README on Dapper. I've just fixed that. I've added a section here: https://github.com/StackExchange/Dapper#packages

Every build is immediately available for use on MyGet, the problem was this not being common knowledge after the effort to set it up, that's my fault. So while we have had very limited time lately to do proper releases, it's very much the intent that fixes and changes to immediately available to the community. Sorry for not communicating this better - hopefully the updated README does a much better job (and suggestions welcome!).

I'm going to close this issue out because I have tested (on Stack Overflow) and released 1.50.5 this week (including a fix for this issue). It's available on NuGet for all.

[toannguyen83]

Thanks for fixing the issue 👍

[PudottaPommin]

I have same problem in WITH RECURSION (...) SELECT on current version of package (1.50.5)

Gist to sql, query

[managerger]

Unfortunately, the issue is still exits. I perform simple QueryAsync against 1.50.5

[MikeDev96]

I got around this issue by doing the following:

var rawReader = (SqlDataReader)reader.GetType().GetField("reader", BindingFlags.NonPublic | BindingFlags.Instance).GetValue(reader);
if (rawReader.HasRows)
    // Do stuff

Yes it's a bit hacky as it uses reflection. Would be nice if the SqlMapper.GridReader exposed the SqlDataReader's HasRows property.

[CleveSteve]

@nickInMN 's suggestion of using ExecuteAsync is the right answer, especially if you are intending for no values to be returned (no select). Thank you.