Dapper 查询

查询

获取查询结果的单个值：

[HttpGet("Count")]
        public async Task<ActionResult> GetCount()
        {
            using IDbConnection con = new SqlConnection(MyConfig.ConnectionString);
            string sql = "select count(*) from T_Book ";
            var count = await con.ExecuteScalarAsync(sql);// 返回dynamic类型
            return Ok(count);
        }
        [HttpGet("CountInt")]
        public async Task<ActionResult> GetCountInt()
        {
            using IDbConnection con = new SqlConnection(MyConfig.ConnectionString);
            string sql = "select count(*) from T_Book";
            int count = await con.ExecuteScalarAsync<int>(sql);//返回整形
            return Ok(count);
        }

选择一行数据：

[HttpGet("Product")]
       public async Task<ActionResult> Query()
       {
           string sql = "select * from products where productid=1";
           using (IDbConnection con = new SqlConnection(MyConfig.ConnectionString))
           {
               var product = await con.QuerySingleAsync(sql);
               return Ok(product);
           }
       }
       [HttpGet("ProductType")]
       public async Task<ActionResult> QueryType()
       {
           string sql = "select * from products where productid=1";
           using (IDbConnection con = new SqlConnection(MyConfig.ConnectionString))
           {
               var product = await con.QuerySingleAsync<Product>(sql);
               return Ok(product);
           }
       }

选择多行：

[HttpGet("customers")]
       public async Task<ActionResult> Query()
       {
           string sql = "select * from customers";
           using (IDbConnection con = new SqlConnection(MyConfig.ConnectionString))
           {
               var customers = await con.QueryAsync(sql);
               return Ok(customers);
           }
       }
       [HttpGet("customersType")]
       public async Task<ActionResult> QueryType()
       {
           var sql = "select * from customers";
           using (IDbConnection con = new SqlConnection(MyConfig.ConnectionString))
           {
               var customers = await con.QueryAsync<Customer>(sql);
               return Ok(customers);
           }
       }

　　INSERT UPDATE DELETE 插入、修改、删除，返回影响的行数

[HttpPost]
        public async Task<ActionResult> Insert()
        {
            var sql = "insert into categories (categoryName) values ('New Category')";
            using (IDbConnection con = new SqlConnection(MyConfig.ConnectionString))
            {
                var affectedRows = await con.ExecuteAsync(sql);
                return Ok(affectedRows);
            }
        }
        [HttpPut]
        public async Task<ActionResult> Update()
        {
            var sql = @"update products set unitprice=unitprice* 0.1 where categoryid=2";
            using (IDbConnection con = new SqlConnection(MyConfig.ConnectionString))
            {
                var rows = await con.ExecuteAsync(sql);
                return Ok(rows);
            }
        }
        [HttpDelete]
        public async Task<ActionResult> Delete()
        {
            var sql = "delete from categories where categoryName='New Category'";
            using (IDbConnection conn = new SqlConnection(MyConfig.ConnectionString))
            {
                var rows = await conn.ExecuteAsync(sql);
                return Ok(rows);
            }
        }

　　where in查詢

  public static string Libshp_PonoCid = "Select  * from Libshp_PonoCid Where cid in @cid ";
 
private async Task<List<LibshpPonoCid>> GetListSource(string cids)
  {
    if (string.IsNullOrEmpty(cids))
    {
      return new List<LibshpPonoCid>();
    }
    using IDbConnection conn = new SqlConnection(DbHelper.XFMesConnection);
    conn.Open();
    var mycids = cids.Split(',').ToArray();
    var list = await conn.QueryAsync<LibshpPonoCid>(SqlHelper.Libshp_PonoCid, new { cid = mycids });
    list ??= new List<LibshpPonoCid>();
    return list.ToList();
  }

同時查詢兩段SQL，查詢回來一樣可以選擇是否要用強型別去接值。

//QueryMultiple
using (SqlConnection conn = new SqlConnection(strConnection))
{
    string strSql ="Select * from Users; Select * from Account;" ;
    using( var results = conn. QueryMultiple(strSql))
    {
        //第一段SQL
        var users = results.Read().ToList();
        //第二段SQL 強型別
        var accounts = results.Read<MyModel>().ToList();
    }
}