Dapper学习(二)之Query相关

 

0. FIrst , Single & Default

使用这个方法时要小心,First 和 Single 是不同的。

 这里,对这个表做下说明:

如果使用 First , 当没有查到元素时,会报错;如果查到一个元素,会返回这个元素;如果查到多个元素,会返回第一个元素;

如果使用 Single , 没有查到元素会报错;查到一个元素,则返回这个元素;查到多个元素,则报错;

如果使用 FirstOrDefault , 没有查到元素会返回默认值;如果查到一个元素,则返回这个元素;如果查到多个元素,则返回第一个元素;

如果使用 SingleOrDefault , 没有查到元素返回默认值;如果查到一个元素,则返回这个元素;如果查到多个元素,则报错;

1. QueryFirst

可以用来查询和映射第一个结果

结果可以被映射到:

  • Anonymous
  • Strongly Typed

参数

1.1 Query Anonymous

执行一个查询,并且映射第一个结果到一个动态集合

string sql = "SELECT * FROM OrderDetails WHERE OrderDetailID = @OrderDetailID;";

using (var connection = new SqlConnection(FiddleHelper.GetConnectionStringSqlServerW3Schools()))
{
    var orderDetail = connection.QueryFirst(sql, new {OrderDetailID = 1});

    FiddleHelper.WriteTable(orderDetail);
}

1.2 Query Strongly Typed

执行一个查询,并且映射第一个结果到一个强类型集合

string sql = "SELECT * FROM OrderDetails WHERE OrderDetailID = @OrderDetailID;";

using (var connection = new SqlConnection(FiddleHelper.GetConnectionStringSqlServerW3Schools()))
{
    var orderDetail = connection.QueryFirst<OrderDetail>(sql, new {OrderDetailID = 1});

    FiddleHelper.WriteTable(orderDetail);
}

2. QueryFirstOrDefault

执行一个查询,并且映射第一个结果,或者如果没查到元素时,返回一个默认值

结果可以被映射到:

  • Anonymous
  • Strongly Typed

参数

 2.1 Query Anonymous

执行一个查询,并且映射第一个结果到一个动态集合,或者如果没查到元素,则返回一个默认值

string sql = "SELECT * FROM OrderDetails WHERE OrderDetailID = @OrderDetailID;";

using (var connection = new SqlConnection(FiddleHelper.GetConnectionStringSqlServerW3Schools()))
{            
    var orderDetail = connection.QueryFirstOrDefault(sql, new {OrderDetailID = 1});

    FiddleHelper.WriteTable(orderDetail);
}

2.2 Query Strongly Typed

执行一个查询,并且映射第一个结果到一个强类型集合,或者如果没查到元素,则返回一个默认值

string sql = "SELECT * FROM OrderDetails WHERE OrderDetailID = @OrderDetailID;";

using (var connection = new SqlConnection(FiddleHelper.GetConnectionStringSqlServerW3Schools()))
{
    var orderDetail = connection.QueryFirstOrDefault<OrderDetail>(sql, new {OrderDetailID = 1});

    FiddleHelper.WriteTable(new List<OrderDetail>() { orderDetail });
}

3. QuerySingle

执行查询,并且映射第一个结果,如果查到不止一个元素,则会抛出异常

结果可以映射到:

  • Anonymous
  • Strongly Typed

参数

 3.1 Query Anonymous

执行查询,映射第一个结果到一个动态集合,如果查到不止一个元素,抛出异常

string sql = "SELECT * FROM OrderDetails WHERE OrderDetailID = @OrderDetailID;";

using (var connection = new SqlConnection(FiddleHelper.GetConnectionStringSqlServerW3Schools()))
{    
    var orderDetail = connection.QuerySingle(sql, new {OrderDetailID = 1});

    FiddleHelper.WriteTable(orderDetail);
}

3.2 Query Strongly Typed

执行查询,并且映射第一个结果到一个强类型集合,如果查到不止一个元素,则抛出异常

string sql = "SELECT * FROM OrderDetails WHERE OrderDetailID = @OrderDetailID;";

using (var connection = new SqlConnection(FiddleHelper.GetConnectionStringSqlServerW3Schools()))
{            
    var orderDetail = connection.QuerySingle<OrderDetail>(sql, new {OrderDetailID = 1});

    FiddleHelper.WriteTable(new List<OrderDetail>() { orderDetail });
}

4. QuerySingleOrDefault

执行查询,并且映射第一个结果,或者如果查询结果为空,则返回默认值;如果查到不止一个元素,则抛出异常

结果映射到:

  • Anonymous
  • Strongly Typed

参数

 4.1 Query Anonymous

执行查询,并且映射第一个结果到一个动态集合,或者如果查询结果为空,则返回一个默认值;如果查到不止一个元素,则抛出异常。

string sql = "SELECT * FROM OrderDetails WHERE OrderDetailID = @OrderDetailID;";

using (var connection = new SqlConnection(FiddleHelper.GetConnectionStringSqlServerW3Schools()))
{
    var orderDetail = connection.QuerySingleOrDefault(sql, new {OrderDetailID = 1});

    FiddleHelper.WriteTable(orderDetail);
}

4.2 Query Strongly Typed

执行查询,并且映射第一个结果到一个强类型集合,或者如果查询结果为空,则返回一个默认值;如果查到不止一个元素,则抛出异常

string sql = "SELECT * FROM OrderDetails WHERE OrderDetailID = @OrderDetailID;";

using (var connection = new SqlConnection(FiddleHelper.GetConnectionStringSqlServerW3Schools()))
{            
    var orderDetail = connection.QuerySingleOrDefault<OrderDetail>(sql, new {OrderDetailID = 1});

    FiddleHelper.WriteTable(new List<OrderDetail>() { orderDetail });
}

5. QueryMultiple

它可以在同一个命令中执行多个查询,并且映射到结果中

string sql = "SELECT * FROM Invoice WHERE InvoiceID = @InvoiceID; SELECT * FROM InvoiceItem WHERE InvoiceID = @InvoiceID;";

using (var connection = My.ConnectionFactory())
{
    connection.Open();

    using (var multi = connection.QueryMultiple(sql, new {InvoiceID = 1}))
    {
        var invoice = multi.Read<Invoice>().First();
        var invoiceItems = multi.Read<InvoiceItem>().ToList();
    }
}

参数

 

posted @ 2019-09-12 20:58 Vincent-yuan 阅读(...) 评论(...) 编辑 收藏