How to retrieve a single value from the database using Dapper

Question

I'm using Dapper and trying to retrieve a short from the database and would like to do this without grabbing it from a collection.

I've got the following code, which doesn't work because QueryAsync<short> returns IEnumerable<short>.

short status;
using (var sqlConnection = new SqlConnection(connectionString))
{
    var parameters = new DynamicParameters();
    parameters.Add("@ID", ID, DbType.Int32, ParameterDirection.Input);

    await sqlConnection.OpenAsync();
    status = await sqlConnection.QueryAsync<short>("SELECT [StatusID] FROM [MyTable] WHERE [ID] = @ID", parameters, commandTimeout: _sqlCommandTimeoutInSeconds);
}

Can I get Dapper to return a single value, instead of a collection?

David Mohundro · Accepted Answer · 2020-06-09T20:30:30.130

25

FYI, Dapper has now added both QuerySingle and QuerySingleAsync as well as their corresponding OrDefault variants... usage for QuerySingleOrDefaultAsync is:

await connection.QuerySingleOrDefaultAsync<short>(sql);

edited Jun 09 '20 at 20:30

answered Jun 05 '20 at 14:27

David Mohundro

11,922
5
40
44

This is absolutely the right answer, and keeps your code much cleaner than the currently accepted answer. – VSO Jun 07 '20 at 21:27
Nice one, I've changed the accepted answer to yours. – Nic Jun 09 '20 at 13:48

score 15 · Answer 2 · edited Jun 10 '20 at 13:42

Either you can use ExecuteScalarAsync or Single() with ExecuteScalarAsync you can retrieve a single value from database using Dapper.

short status;
using (var sqlConnection = new SqlConnection(connectionString))
{
  var parameters = new DynamicParameters();
  parameters.Add("@ID", ID, DbType.Int32, ParameterDirection.Input);

  await sqlConnection.OpenAsync();
  status = await sqlConnection.ExecuteScalarAsync<short>("SELECT [StatusID] FROM     [MyTable] WHERE [ID] = @ID", parameters, commandTimeout: _sqlCommandTimeoutInSeconds);
}

Single() can be used in this way

short status;
using (var sqlConnection = new SqlConnection(connectionString))
{
  var parameters = new DynamicParameters();
  parameters.Add("@ID", ID, DbType.Int32, ParameterDirection.Input);

  await sqlConnection.OpenAsync();
  status = await sqlConnection.QueryAsync<short>("SELECT [StatusID] FROM [MyTable] WHERE [ID] = @ID", parameters, commandTimeout: _sqlCommandTimeoutInSeconds).Single();
}

@DavidMohundro Please add that as an answer - it's the correct answer to this question. — VSO, Jun 04 '20 at 17:01

score 2 · Answer 3 · edited Aug 16 '19 at 12:24

2

You should use ExecuteScalar:

status = await sqlConnection.ExecuteScalarAsync<short>("SELECT [StatusID] FROM [MyTable] WHERE [ID] = @ID", parameters, commandTimeout: _sqlCommandTimeoutInSeconds)

edited Aug 16 '19 at 12:24

Uwe Keim

39,551
56
175
291

answered Jul 18 '16 at 07:08

Zein Makki

29,485
6
52
63

How to retrieve a single value from the database using Dapper

3 Answers3