[.NET] Dapper - Query samples

 .NET    Dapper  

▌Introduction

---

Dapper: a simple object mapper for .Net

Okay, I think everyone knows it and I will just keep some sample codes for practicing and diary.

▌Environment

---

▋Dapper 1.50.2

▌Implement

---

▋Sql Connection factory

First, I created a SqlConnection factory.

public static class SqlConnectionFactory {         private static string _connStr = "Data Source=.;Initial Catalog=XXX;Integrated Security=True";         public static SqlConnection Create()         {             return new SqlConnection(_connStr);         } }

▋Query with Strongly typed result and anonymous parameter

public static City GetCity(string id) {             using (var cn = SqlConnectionFactory.Create())             {                 var city = cn.QueryFirstOrDefault<City>(                     "SELECT * FROM Citys WHERE Id = @Id", new { Id = id });                 return city;             } }

▋Query with Store procedure and dynamic parameters

public static IEnumerable<City> GetCitiesSp(string cityName) {             using (var cn = SqlConnectionFactory.Create())             {                 var parameter = new DynamicParameters();                 parameter.Add("@CityName", cityName, DbType.String, ParameterDirection.Input);                 var cities = cn.Query<City>("usp_getCitys",                     parameter,                     commandType: CommandType.StoredProcedure);                 return cities;             } }

▋Query with list

public static IEnumerable<City> GetCities(string[] ids) {             using (var cn = SqlConnectionFactory.Create())             {                 var cities = cn.Query<City>(                     "SELECT * FROM Citys WHERE Id IN @Ids", new { Ids = ids });                 return cities;             } }

▋Query with list

public static IEnumerable<City> GetCities(string[] ids) {             using (var cn = SqlConnectionFactory.Create())             {                 var cities = cn.Query<City>(                     "SELECT * FROM Citys WHERE Id IN @Ids", new { Ids = ids });                 return cities;             } }

▋Query multiple result

public static void GetCityCustomer(string cityId,int customerId, out City city, out Customer customer) {     using (var cn = SqlConnectionFactory.Create())     {        string sql = @"                        SELECT * FROM Citys WHERE Id = @CityId;                        SELECT * FROM Customers WHERE Id = @CustomerId";         using (var multi = cn.QueryMultiple(sql, new { CityId = cityId, CustomerId = customerId }))         {              city = multi.Read<City>().First();              customer = multi.Read<Customer>().First();         }    } }

▌Reference

---

▋Dapper Tutorials & Examples - C# Micro ORM