Dapper - Result Strongly Typed

Description

Extension methods can be used to execute a query and map the result using strongly typed.

The anonymous result can be mapped from following methods:

These extension methods can be called from any object of type IDbConnection.

Example - Query

Query method can execute a query and map the result to a strongly typed list.

  1. string sql = "SELECT TOP 10 * FROM OrderDetails";
  2. using (var connection = new SqlConnection(FiddleHelper.GetConnectionStringSqlServerW3Schools()))
  3. {
  4. var orderDetails = connection.Query<OrderDetail>(sql).ToList();
  5. Console.WriteLine(orderDetails.Count);
  6. FiddleHelper.WriteTable(orderDetails);
  7. }

Try it: .NET Core | .NET Framework

Example - QueryFirst

QueryFirst method can execute a query and map the first result to a strongly typed list.

  1. string sql = "SELECT * FROM OrderDetails WHERE OrderDetailID = @OrderDetailID;";
  2. using (var connection = new SqlConnection(FiddleHelper.GetConnectionStringSqlServerW3Schools()))
  3. {
  4. var orderDetail = connection.QueryFirst<OrderDetail>(sql, new {OrderDetailID = 1});
  5. FiddleHelper.WriteTable( new List<OrderDetail>() { orderDetail });
  6. }

Try it: .NET Core | .NET Framework

Example - QueryFirstOrDefault

QueryFirstOrDefault method can execute a query and map the first result to a strongly typed list, or a default value if the sequence contains no elements.

  1. string sql = "SELECT * FROM OrderDetails WHERE OrderDetailID = @OrderDetailID;";
  2. using (var connection = new SqlConnection(FiddleHelper.GetConnectionStringSqlServerW3Schools()))
  3. {
  4. var orderDetail = connection.QueryFirstOrDefault<OrderDetail>(sql, new {OrderDetailID = 1});
  5. FiddleHelper.WriteTable(new List<OrderDetail>() { orderDetail });
  6. }

Try it: .NET Core | .NET Framework

Example - QuerySingle

QuerySingle method can execute a query and map the first result to a strongly typed list and throws an exception if there is not exactly one element in the sequence.

  1. string sql = "SELECT * FROM OrderDetails WHERE OrderDetailID = @OrderDetailID;";
  2. using (var connection = new SqlConnection(FiddleHelper.GetConnectionStringSqlServerW3Schools()))
  3. {
  4. var orderDetail = connection.QuerySingle<OrderDetail>(sql, new {OrderDetailID = 1});
  5. FiddleHelper.WriteTable(new List<OrderDetail>() { orderDetail });
  6. }

Try it: .NET Core | .NET Framework

Example - QuerySingleOrDefault

QuerySingleOrDefault method can execute a query and map the first result to a strongly typed list, or a default value if the sequence is empty; this method throws an exception if there is more than one element in the sequence.

  1. string sql = "SELECT * FROM OrderDetails WHERE OrderDetailID = @OrderDetailID;";
  2. using (var connection = new SqlConnection(FiddleHelper.GetConnectionStringSqlServerW3Schools()))
  3. {
  4. var orderDetail = connection.QuerySingleOrDefault<OrderDetail>(sql, new {OrderDetailID = 1});
  5. FiddleHelper.WriteTable(new List<OrderDetail>() { orderDetail });
  6. }

Try it: .NET Core | .NET Framework