批量查询数据:
/// <summary>
/// 批量查询数据(单表查询)
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="sql"></param>
/// <param name="t"></param>
/// <returns></returns>
public static List<T> TestDapperListQuery<T>(string sql)
{
List<T> list = null;
try
{
using (SqlConnection connect = new SqlConnection(ConnectStr))
{
list= connect.Query<T>(sql).ToList();//调用Dapper的扩展方法QueryMultiple()执行查询操作,提交到数据库
}
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
return list;
}
测试代码:
//批量查询数据 --单表查询
string sqlQuery2 = @"SELECT * FROM ProductType";
List<ProductType> productTypeQueryReturnedList= TestDapperListQuery<ProductType>(sqlQuery2);
Result:
使用匿名函数参数化赋值查询:
/// <summary>
/// 匿名函数参数化赋值:Query()方法支持单表不查询所有字段
/// </summary>
public static void TestDapperQueryParams()
{
//string sql = @"SELECT * FROM Product
//WHERE ProductNo>@ProductNo AND TypeId=@TypeId";
string sql = @"SELECT ProductName,Price,Description FROM Product
WHERE ProductNo>@ProductNo AND TypeId=@TypeId";
try
{
using (SqlConnection connect = new SqlConnection(ConnectStr))
{
List<Product> productTypeList = connect.Query<Product>(sql, new { ProductNo= 1007,TypeId=1}).ToList();
}
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
}
/// <summary>
/// 使用匿名函数的方式,传入参数 --批量查询(in查询)
/// </summary>
public static void TestDapperQueryIn() {
string sql = @"SELECT * FROM ProductType WHERE TypeId IN @typeIds ";
try {
using (SqlConnection connect = new SqlConnection(ConnectStr))
{
List<ProductType> productTypeList=connect.Query<ProductType>(sql, new { typeIds = new int[] {2,3,4}}).ToList();
}
}
catch (Exception ex) {
Console.WriteLine(ex.Message);
}
}
跨表查询:
public static void TestDapperAcrossQuery01()
{
string sql = @"SELECT * FROM Product P
INNER JOIN ProductType PT
ON P.TypeId=PT.TypeId
";
try
{
using (SqlConnection connect = new SqlConnection(ConnectStr))
{
//连接查询P->PT,且返回类型为P,把查询出的相关的PT封装到P中
//适用于查询所有字段
List<Product> products=connect.Query<Product,ProductType,Product>(sql, (p, pt) =>
{
p.ProductType = pt;//关联ProductType
return p;
}, splitOn: "TypeId").ToList();//TypeId:连接字段
}
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
}
返回Product记录,并且相关的ProductType记录也被查询出来了。
在跨表连接查询时,只想获取需要的字段数据,这应该如何处理?
自定义中间实体类,将需要的字段以属性的方式存储在此对象中。
添加中间实体类:
eg:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
namespace DapperORMDemo.Models
{
/// <summary>
/// 中间实体类:用来封装跨表查询的字段数据
/// </summary>
public class ViewProductModel
{
public string ProductName { get; set;}
public decimal Price { get; set;}
public string Description { get; set;}
public string TypeName { get; set; }
}
}
/// <summary>
/// 跨表查询:将查询出的数据封装到自定义对象中
/// </summary>
public static void TestDapperAcrossQuery02()
{
string sql = @"SELECT ProductName,Price,Description,PT.TypeName FROM Product P
INNER JOIN ProductType PT
ON P.TypeId=PT.TypeId
WHERE P.ProductNo>@ProductNo AND P.TypeId=@TypeId";
try
{
using (SqlConnection connect = new SqlConnection(ConnectStr))
{
//封装到自定义的对象中
List<ViewProductModel> vpmList = connect.Query<ViewProductModel>(sql, new { ProductNo = 1016, TypeId = 1 }).ToList();
}
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
}
需要注意: 属性名称必须要和数据表中字段的名称一致。
Result:
跨表连接+匿名函数参数化查询:
/// <summary>
/// 跨表查询:将查询出的数据封装到自定义对象中
/// </summary>
public static void TestDapperAcrossQuery03()
{
string sql = @"SELECT * FROM Product P
INNER JOIN ProductType PT
ON P.TypeId=PT.TypeId
WHERE P.ProductNo>@ProductNo AND P.TypeId=@TypeId";
try
{
using (SqlConnection connect = new SqlConnection(ConnectStr))
{
//适用于参数化查询
List<Product> products = connect.Query<Product, ProductType, Product>(sql, (p, pt) =>
{
p.ProductType = pt;//关联ProductType
return p;
}, new //参数
{
ProductNo = 1016,
TypeId = 1
}, splitOn: "TypeId").ToList();//TypeId:连接字段
}
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
}
这里需要注意:匿名函数赋值:键值对赋值(key=value)[key是Sql语句中所需要赋值的字段名称]
Result: