CODEWITHSUNDEEP
c#.netsql-serverasp.net-mvcdapper
Nic
Nic

Reputation: 12855

How to retrieve a single value from the database using Dapper

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?

Upvotes: 23

Views: 29464

Answers (3)

Aftab Ahmed
Aftab Ahmed

Reputation: 1737

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();
}

Upvotes: 15

David Mohundro
David Mohundro

Reputation: 12412

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);

Upvotes: 31

Zein Makki
Zein Makki

Reputation: 30032

You should use ExecuteScalar:

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

Upvotes: 2

Related Questions