Dapper Use cases

 

1. var sql = "select * from products";
2. var products = new List<Product>();
3. using (var connection = new SqlConnection(connString))
4. {
5. connection.Open();
6. using (var command = new SqlCommand(sql, connection))
7. {
8. using (var reader = command.ExecuteReader())
9. {
10. var product = new Product
11. {
12. ProductId = reader.GetInt32(reader.GetOrdinal("ProductId")),
13. ProductName = reader.GetString(reader.GetOrdinal("ProductName")),
14. SupplierId = reader.GetInt32(reader.GetOrdinal("SupplierId")),
15. CategoryId = reader.GetInt32(reader.GetOrdinal("CategoryId")),
16. QuantityPerUnit = reader.GetString(reader.GetOrdinal("QuantityPerUnit")),
17. UnitPrice = reader.GetDecimal(reader.GetOrdinal("UnitPrice")),
18. UnitsInStock = reader.GetInt16(reader.GetOrdinal("UnitsInStock")),
19. UnitsOnOrder = reader.GetInt16(reader.GetOrdinal("UnitsOnOrder")),
20. ReorderLevel = reader.GetInt16(reader.GetOrdinal("ReorderLevel")),
21. Discontinued = reader.GetBoolean(reader.GetOrdinal("Discontinued")),
22. DiscontinuedDate = reader.GetDateTime(reader.GetOrdinal("DiscontinuedDate"))
23. };
24. products.Add(product);
25. }
26. }
27. }

At its most basic level, Dapper replaces the highlighted block of assignment code in the example above with the following:

1. products = connection.Query<Product>(sql).ToList();

Excute Stored Procedure

1. using(var connection = new SqlConnection(connectionString))
2. {
3. // Execute the stored procedure
4. var result = connection.Query<Customer>(
5. "MyStoredProcedure",
6. commandType: CommandType.StoredProcedure
7. ).ToList();
8. }