SQL Server Dapper
用于简化Dapper与SQL Server数据库交互的辅助类
- 下载 Dapper 1.42.0 演示 - 54 KB
- 下载 Dapper 1.50.2 演示 - 545 KB
- 下载 Dapper 1.50.2 .NET Core 演示 - 41 KB
- 下载 SqlHelper (所有版本) - 59 KB
引言
Dapper 已成为功能强大的微型 ORM 之一。它的主要重点是将数据库表映射到 POCO（纯粹的 CLR 对象），并且据称其映射速度非常快。它的优点之一是对数据库的“通用性”。其内部实现仅使用 DB 接口，如 IDbConnection、IDbCommand、IDbTransaction。没有特定于数据库的实现（如 SQL Server 特有的 SqlConnection），因此任何实现这些接口的数据提供程序都可以与 Dapper 配合使用。这种优势也可能成为一种弱点。每个数据库都有其自身的一些细微差别和不同的处理方式，通常需要用户预先编写这些代码。SQL Server 表值参数（简称 TVP）是一个很好的数据库特定示例。此外，在处理数据提供程序时，还有一些常见的任务需要封装到帮助类中。所有这些原因以及更多原因促使我编写了这个帮助类，即无处不在的 SqlHelper，它专门用于缓解 Dapper 与 SQL Server 数据库的交互问题。
连接字符串和超时
在使用此帮助类项目之前，需要对其进行一些“调整”。您需要编辑 GetConnectionString 和 GetTimeout 方法的默认实现，以反映 **您的** 特定项目需求，因此您需要对其进行调整以满足您的喜好。
仍然可以选择将不同的连接字符串和连接超时传递给查询方法，但通常情况下，您不希望每次都这样做。您希望将此任务封装到所有数据库调用中，而这些方法正是为此而存在的。我使用的默认连接超时为 30 秒。也可以随意更改。
public static class SqlHelper
{
public static string GetConnectionString(string connectionString = null)
{
// this is where you write your implementation
if (!string.IsNullOrEmpty(connectionString))
return connectionString;
return ConfigurationManager.ConnectionStrings["connection_string_name"].ConnectionString;
}
public static int ConnectionTimeout { get; set; }
public static int GetTimeout(int? commandTimeout = null)
{
if (commandTimeout.HasValue)
return commandTimeout.Value;
return ConnectionTimeout;
}
}
DynamicParameters - 使用 SQL Server 类型
Dapper 使用 DbType 枚举来定义传递给 SQL 或存储过程的参数类型。大多数时候，您不会显式地执行此操作。您只需将匿名对象作为参数值传递，Dapper 会自行完成值到类型的映射。但是，有时您需要显式设置参数的类型。这通常发生在将输出参数传递给存储过程时。Dapper 使用 DynamicParameters 类来定义参数集合。
// Dapper
partial class DynamicParameters
{
public void Add(string name, object value = null, DbType? dbType = null,
ParameterDirection? direction = null, int? size = null) { }
}
// Adding output parameters
DynamicParameters params = new DynamicParameters();
params.Add("ResultCode", dbType: DbType.Int32, direction: ParameterDirection.Output);
params.Add("ResultDesc", dbType: DbType.String, direction: ParameterDirection.Output, size: 4000);
问题在于，您不希望每次都弄清楚 SQL Server 类型与 DbType 枚举之间的映射关系。您只想使用 SQL Server 类型，而该枚举是 SqlDbType。借助 MSDN 页面 SQL Server 数据类型映射，我在两个枚举之间添加了内部映射，并为 Dapper 的 DynamicParameters 类添加了一个方法重载。为了方便起见，我还为 DynamicParameters 添加了一个非默认构造函数，我觉得这是缺失的。
partial class DynamicParameters
{
static readonly Dictionary<SqlDbType, DbType?> sqlDbTypeMap = new Dictionary<SqlDbType, DbType?>
{
{SqlDbType.BigInt, DbType.Int64},
{SqlDbType.Binary, DbType.Binary},
{SqlDbType.Bit, DbType.Boolean},
{SqlDbType.Char, DbType.AnsiStringFixedLength},
{SqlDbType.DateTime, DbType.DateTime},
{SqlDbType.Decimal, DbType.Decimal},
{SqlDbType.Float, DbType.Double},
{SqlDbType.Image, DbType.Binary},
{SqlDbType.Int, DbType.Int32},
{SqlDbType.Money, DbType.Decimal},
{SqlDbType.NChar, DbType.StringFixedLength},
{SqlDbType.NText, DbType.String},
{SqlDbType.NVarChar, DbType.String},
{SqlDbType.Real, DbType.Single},
{SqlDbType.UniqueIdentifier, DbType.Guid},
{SqlDbType.SmallDateTime, DbType.DateTime},
{SqlDbType.SmallInt, DbType.Int16},
{SqlDbType.SmallMoney, DbType.Decimal},
{SqlDbType.Text, DbType.String},
{SqlDbType.Timestamp, DbType.Binary},
{SqlDbType.TinyInt, DbType.Byte},
{SqlDbType.VarBinary, DbType.Binary},
{SqlDbType.VarChar, DbType.AnsiString},
{SqlDbType.Variant, DbType.Object},
{SqlDbType.Xml, DbType.Xml},
{SqlDbType.Udt,(DbType?)null}, // Dapper will take care of it
{SqlDbType.Structured,(DbType?)null}, // Dapper will take care of it
{SqlDbType.Date, DbType.Date},
{SqlDbType.Time, DbType.Time},
{SqlDbType.DateTime2, DbType.DateTime2},
{SqlDbType.DateTimeOffset, DbType.DateTimeOffset}
};
// non-default constructor with DbType
public DynamicParameters(string name, object value = null, DbType? dbType = null,
ParameterDirection? direction = null, int? size = null) : this()
{
Add(name, value, dbType, direction, size);
}
// overload constructor with SqlDbType
public DynamicParameters(string name, object value = null, SqlDbType? sqlDbType = null,
ParameterDirection? direction = null, int? size = null) : this()
{
Add(name, value, sqlDbType, direction, size);
}
// add parameter with SQL Server type
public void Add(string name, object value = null, SqlDbType? sqlDbType = null,
ParameterDirection? direction = null, int? size = null)
{
Add(name, value, (sqlDbType != null ?
sqlDbTypeMap[sqlDbType.Value] : (DbType?)null), direction, size);
}
}
从映射中可以看到，我没有将 SqlDbType.Udt 和 SqlDbType.Structured 这两种类型映射到任何特定的 DbType。显然，这没有直接的映射关系。
SqlDbType.Udt 用于用户定义类型等，以及更高级的类型，如 Geography、Geometry 和 HierarchyId。
SqlDbType.Structured 用于表值参数，当您将 DataTable 作为值传递时。
我只是将它们映射到一个 null DbType，然后让 Dapper 来处理。
DynamicParameters - 获取所有参数值
DynamicParameters 有一个 Get 方法，该方法根据参数名称返回参数的值。它的用途通常是在检索输出参数的值时。但为了清楚起见，这里对参数的方向没有限制。
您可以获取输入参数的值，但这显然没有太大意义。
// Dapper
partial class DynamicParameters
{
public T Get<T>(string name) { }
}
// Adding output parameters
DynamicParameters params = new DynamicParameters();
params.Add("ResultCode", dbType: DbType.Int32, direction: ParameterDirection.Output);
params.Add("ResultDesc", dbType: DbType.String, direction: ParameterDirection.Output, size: 4000);
....
int resultCode = params.Get<int>("ResultCode");
string resultDesc = params.Get<string>("ResultDesc");
使用多个输出参数时，通常希望一次性获取它们的所有值，然后与它们一起工作。
我为此添加了几个 Get 方法，以便一次性将所有值拉取到一个 Dictionary 实例中。它们使用底层 DynamicParameters 的 Get 方法来检索每个参数值。第一个 Get 方法返回一个 Dictionary<string, object>，它将参数名称映射到其值。如果您知道所有参数都是同一类型，则可以使用泛型版本的 Get<T> 来检索类型安全的 Dictionary<string, T>。
partial class DynamicParameters
{
public Dictionary<string, object> Get()
{
return Get<object>();
}
// all the parameters are of the same type T
public Dictionary<string, T> Get<T>()
{
Dictionary<string, T> values = new Dictionary<string, T>();
foreach (string parameterName in ParameterNames)
values.Add(parameterName, Get<T>(parameterName));
return values;
}
}
这段代码的问题在于，它还会获取输入参数的值，而这通常不是您想要的。
稍后，我将展示如何解决这个问题，以及如何区分输入参数和非输入参数。
查询
Query 方法封装了 Dapper 自带的查询方法。内部，它们只是调用 Dapper 的查询方法并返回其返回的任何内容。
概念上，它们镜像了它们调用的 Dapper 方法，主要是通过返回值的类型，但是，它们的结构与 Dapper 的方法略有不同。
// stored procedure - dynamic
IEnumerable<dynamic> QuerySP(string storedProcedure, dynamic param, dynamic outParam,
SqlTransaction transaction, bool buffered, int? commandTimeout, string connectionString) { }
// sql - dynamic
IEnumerable<dynamic> QuerySQL(string sql, dynamic param, dynamic outParam,
SqlTransaction transaction, bool buffered, int? commandTimeout, string connectionString) { }
// stored procedure - T
IEnumerable<T> QuerySP<T>(string storedProcedure, dynamic param, dynamic outParam,
SqlTransaction transaction, bool buffered, int? commandTimeout, string connectionString) { }
// sql - T
IEnumerable<T> QuerySQL<T>(string sql, dynamic param, dynamic outParam,
SqlTransaction transaction, bool buffered, int? commandTimeout, string connectionString) { }
首先要注意的是，方法分为存储过程方法和纯 SQL 方法。存储过程查询方法以 "SP" 结尾，纯 SQL 方法以 "SQL" 结尾，因此查询方法将是 "QuerySP" 和 "QuerySQL"。
这种命名模式将在帮助类的其余部分重复出现。原因如下。首先，它将两种不同的操作类型——存储过程和纯 SQL——分到语义上两种类型的方法中，帮助用户区分它们。虽然您可以将存储过程作为纯 SQL 执行（"exec sp_do_something"），但您真的不希望这样做，以防止 SQL 注入。
其次，用户不必将命令类型（CommandType.Text、CommandType.StoredProcedure）作为参数传递，因为这些方法已经处理了。
另一个主要区别在于它们接受两组参数：param 和 outParam。param 接受一组输入参数，outParam 接受一组非输入参数（输出、输入-输出、返回值的参数）。
这种区别仅是语义上的，取决于用户如何保持。没有什么可以阻止您通过另一种方法参数传递一种类型的 SQL 参数。那么为什么要这样做呢？如果您直接通过 Dapper 执行查询，您会发现输入参数几乎总是作为匿名类型传递，即 new { Name = value, .... }，而非输入参数是通过声明 DynamicParameters 的实例来传递的。DynamicParameters 是 Dapper 对一组参数的实现。
在内部，Dapper 会将您作为输入参数集传递的匿名类型转换为 DynamicParameters 的实例。我并不是说您不能直接通过 DynamicParameters 定义输入参数，但这并不是输入参数的常用做法。
我们采用两种看似独立的方式定义参数的原因是我们想要读取非输入参数的结果，而不关心输入参数。这就是为什么这些方法有两组参数：输入和非输入。在您想要传递输入参数和非输入参数混合体的情况下，您仍然可以通过 param 传递匿名参数集，通过 outParam 传递 DynamicParameters 的实例。查询结束后，您可以调用 Get<T>()（如前所述）来获取 **仅** 非输入参数的所有值。
最后一个区别是事务的类型。Query 方法要求您传递 SqlTransaction，它是 IDbTransaction 的 SQL Server 提供程序实现。Dapper 只要求 IDbTransaction。
查询示例
示例查询 AdventureWorks 数据库中的 Production.Product 表。
首先，我们声明 POCO。
// Product POCO
public class Product
{
public int ProductID { get; set; }
public string Name { get; set; }
}
使用纯 SQL 和存储过程的查询。
// pure SQL
var products = SqlHelper.QuerySQL<Product>("select * from Production.Product");
var product = SqlHelper.QuerySQL<Product>("select * from Production.Product _
where ProductID = @ProductID", new { ProductID = 1 }).FirstOrDefault();
// stored procedure
var products = SqlHelper.QuerySP<Product>("sp_get_products");
var product = SqlHelper.QuerySP<Product>("sp_get_products", new { ProductID = 1 }).FirstOrDefault();
带输出参数的查询。
// output parameters
// the parameter types are defines by SqlDbType enumeration, not by DbType enumeration
var outParam = new DynamicParameters("ProductsCount", sqlDbType: SqlDbType.Int,
direction: ParameterDirection.Output);
outParam.Add("DummyInOutParam", 10, SqlDbType.Int, ParameterDirection.InputOutput);
// pure SQL and output parameters
var products = SqlHelper.QuerySQL<Product>(@"
select @ProductsCount = count(*) from Production.Product;
set @DummyInOutParam = @DummyInOutParam * 2;
select * from Production.Product",
outParam: outParam);
var product = SqlHelper.QuerySQL<Product>(@"
select @ProductsCount = count(*) from Production.Product;
set @DummyInOutParam = @DummyInOutParam * 2;
select * from Production.Product where ProductID = @ProductID",
new { ProductID = 1 }, outParam).FirstOrDefault();
// get specific output parameter value
int productsCount = outParam.Get<int>("ProductsCount");
int dummyInOutParam = outParam.Get<int>("DummyInOutParam");
// get all output parameter values
Dictionary<string, object> outValues = outParam.Get();
int productsCount = (int)outValues["ProductsCount"];
int dummyInOutParam = (int)outValues["DummyInOutParam"];
// both output parameters are of the same type int
Dictionary<string, int> outValues = outParam.Get<int>();
int productsCount = outValues["ProductsCount"];
int dummyInOutParam = outValues["DummyInOutParam"];
TVP - 表值参数 - 快速回顾
TVP 是在 SQL Server 2008 中引入的。它们提供了一种方便的方式将多行数据传递给 SQL Server。TVP “作用”类似于表（但不是），这意味着您可以像从表中选择一样从它选择。
TVP 必须在存储过程的参数列表中声明为只读，因此您不能更改其值，只能读取它们。TVP 值是 DataTable。DataTable 的列必须与 TVP 声明的名称、类型和顺序完全匹配。
在 SQL Server 2008 之前，您需要诉诸各种技巧来提供 TVP 的相同功能。一个常见的问题是将数字列表（例如索引）传递给 SQL Server。在这种情况下，一个常见的技巧是将值列表作为字符串传递，用逗号分隔，然后在 SQL Server 端拆分字符串（dbo.fnSplit）并将值转换为数字。
这是一个 TVP 解决该问题的示例。首先，创建 TVP。
CREATE TYPE IntArray AS TABLE
(
IntValue int
)
然后，在存储过程中声明一个 TVP
CREATE PROCEDURE [dbo].[sp_get_products]
@ProductIDs IntArray readonly
AS
BEGIN
SET NOCOUNT ON;
select *
from Production.Product
where ProductID in (select IntValue from @ProductIDs)
END
最后，创建 DataTable 作为 TVP 值并创建 TVP。
DataTable intArray = new DataTable();
intArray.Columns.Add("IntValue", typeof(int));
intArray.Rows.Add(1); // product 1
intArray.Rows.Add(2); // product 2
intArray.Rows.Add(3); // product 3
// SqlParameter with Structured sql type
SqlParameter sqlParameter = new SqlParameter();
sqlParameter.ParameterName = "ProductIDs";
sqlParameter.SqlDbType = SqlDbType.Structured;
sqlParameter.Value = intArray;
TVP
从 1.26 版本开始，Dapper 支持 TVP。您只需将 DataTable 作为值传递，Dapper 就会创建适当的参数。
继续上一个示例...
var products = SqlHelper.QuerySP("sp_get_products", new { ProductIDs = intArray });
您也可以选择传递 TVP 的名称。AsTableValuedParameter 是 DataTable 的一个扩展，在 Dapper 中定义，它使用 DataTable 的 ExtendedProperties 来定义 TVP 类型的名称。在这种情况下，TVP 类型的名称是“IntArray”。
var products = SqlHelper.QuerySP("sp_get_products",
new { ProductIDs = intArray.AsTableValuedParameter("IntArray") });
IEnumerable 转 DataTable
TVP 的问题在于它的值是一个 DataTable。您需要为这种参数专门构建一个 DataTable。当您在一个面向 POCO 的环境中工作时，这项任务会变得很繁琐。这种工作环境还有使用 LINQ to Objects 的好处。在一个主要用 IEnumerable（如 Array、List<>）而不是 DataTable 存储数据的环境中，每次想要使用 TVP 时构建 DataTable 都会变得单调乏味。
这就是 SQLHelper 的作用。SQLHelper 拥有 IEnumerable 和 Type 的方法扩展，可以返回 DataTable。这为您提供了一种无缝的方式来快速创建 TVP 值，而无需自行编写代码。当您想要创建一个包含某些行的 TVP 时，使用 IEnumerable 方法扩展。当您想要创建一个空的 TVP 时，使用 Type 方法扩展。方法扩展声明如下：
DataTable ToDataTable<T>(this IEnumerable<T> instances, string typeName,
MemberTypes memberTypes = MemberTypes.Field | MemberTypes.Property, DataTable table) { }
DataTable ToDataTable(this Type type, string typeName,
MemberTypes memberTypes = MemberTypes.Field | MemberTypes.Property) { }
在深入研究实现和各种选项之前，让我们看一个简单的示例。与之前一样，我们使用 Product POCO。其 TVP 声明如下：
CREATE TYPE ProductTVP AS TABLE
(
ProductID int,
Name nvarchar(50)
)
以及它的 POCO 类：
public class Product
{
public int ProductID { get; set; }
public string Name { get; set; }
}
将 products 列表作为参数传递给存储过程
var products = new List<Product>() {
new Product() { ProductID = 1, Name = "Product 1" },
new Product() { ProductID = 2, Name = "Product 2" },
new Product() { ProductID = 3, Name = "Product 3" }
};
// convert a list of products to a DataTable of products
// and pass it as a TVP value
DataTable productsDT = products.ToDataTable<Product>();
var results = SqlHelper.QuerySP("sp_get_products", new { Products = productsDT });
// in one line
var results = SqlHelper.QuerySP("sp_get_products", new { Products = products.ToDataTable<Product>() });
// and pass the name of the TVP type
var results = SqlHelper.QuerySP("sp_get_products",
new { Products = products.ToDataTable<Product>("ProductTVP") });
当您想要创建一个包含某些行的 TVP 时，使用 IEnumerable 方法扩展。当您想要创建一个空的 TVP 时，使用 Type 方法扩展。
DataTable ToDataTable<T>(this IEnumerable instances, string typeName,
MemberTypes memberTypes = MemberTypes.Field | MemberTypes.Property, DataTable table) { }
DataTable ToDataTable(this Type type, string typeName,
MemberTypes memberTypes = MemberTypes.Field | MemberTypes.Property) { }
typeName 参数是 SQL Server 中 TVP 类型的名称。
第一个方法扩展中的 table 参数用于将行追加到传递给方法的现有 DataTable。
memberTypes 参数指示我们要将 POCO 类中的哪种类型的成员映射到 DataTable。默认行为是映射 POCO 中的字段和属性。通常，POCO 只包含属性，但该方法提供了额外的灵活性来处理字段。方法扩展只会接受 public 字段和 public get 属性（无论 set 属性是否公开）。
无法保证映射到 DataTable 的字段或属性的顺序。
意图显然是它们在代码中声明的顺序。直观上，我们希望顺序与我们编写 POCO 类的顺序相同。但这是不可能的，因为 System.Reflection 中没有“声明顺序”。
话虽如此，根据扩展方法的实现方式，顺序并非您可能认为的随机。System.Reflection 中有一个东西可以给人一种“声明顺序”的印象，尽管它并非如此。数据成员按 MemberInfo.MetadataToken 属性 排序，该属性唯一标识元数据元素。分配给 MemberInfo.MetadataToken 的 int 值通常（但绝不保证）按以下顺序排列：首先是字段的声明顺序，然后是属性的声明顺序。
以下 POCO 数据成员的顺序：
public class Product
{
public int Width;
public int ProductID { get; set; }
public int Height;
public string Name { get; set; }
}
将是：Width、Height、ProductID、Name。先是字段，然后是属性。但是，这个示例是人为设计的。通常，POCO 将仅用属性编写，并且由于 MemberInfo.MetadataToken（通常）按声明顺序排序，因此 POCO 数据成员将按正确顺序排列为 DataTable 中的列。
这是实现此处讨论的所有内容的 Snippet 代码。
private static DataTable ToDataTable (IEnumerable instances, Type type,
string typeName, MemberTypes memberTypes, DataTable table)
{
bool isField = ((memberTypes & MemberTypes.Field) == MemberTypes.Field);
bool isProperty = ((memberTypes & MemberTypes.Property) == MemberTypes.Property);
var columns =
type.GetFields(BindingFlags.Public | BindingFlags.Instance)
.Where(f => isField)
.Select(f => new
{
ColumnName = f.Name,
ColumnType = f.FieldType,
IsField = true,
MemberInfo = (MemberInfo)f
})
.Union(
type.GetProperties(BindingFlags.Public | BindingFlags.Instance)
.Where(p => isProperty)
.Where(p => p.CanRead) // has get property
.Where(p => p.GetGetMethod(true).IsPublic) // is public property
.Where(p => p.GetIndexParameters().Length == 0) // not an indexer
.Select(p => new
{
ColumnName = p.Name,
ColumnType = p.PropertyType,
IsField = false,
MemberInfo = (MemberInfo)p
})
)
.OrderBy(c => c.MemberInfo.MetadataToken);
....
}
如果您不想依赖 MemberInfo.MetadataToken 并想确保列的顺序正确，SetOrdinal 是 DataTable 的一个扩展方法，它根据您将列名传递给方法的方式来设置列的序号。
// set the columns ordinal numbers
public static void SetOrdinal(this DataTable table, params string[] columnNames) { }
// usage
table.SetOrdinal("Width", "ProductID", "Height", "Name");
DataTable 转 IEnumerable
严格来说，这里没有必要从 DataTable 转换为任何类型的 IEnumerable。Dapper 返回值是 IEnumerable。然而，这是一个帮助类，而且我已经实现了 IEnumerable 到 DataTable 的转换，为了完整起见，我也添加了反向转换。根据我的经验，在一个同时使用 POCO 和 DataTable 作为业务对象来传递数据的开发环境中，能够轻松地将 POCO 转换为 DataTable 以及从 DataTable 转换过来是非常有帮助的。
前 3 个方法——Cast<T>()、ToArray<T>()、ToList<T>()——是具有默认构造函数的泛型方法，类型为 T。如果 T 没有默认构造函数，则下面的 3 个方法会派上用场。您可以传递一个返回 T 类型实例的委托作为参数。
还有 6 个方法，模板相同，只是用 DataView 代替了 DataTable。
// T has a default constructor
IEnumerable<T> Cast<T>(this DataTable table) where T : new() { }
T[] ToArray<T>(this DataTable table) where T : new() { }
List<T> ToList<T>(this DataTable table) where T : new() { }
// T is instantiated with a delegate
IEnumerable<T> Cast<T>(this DataTable table, Func<T> instanceHandler) { }
T[] ToArray<T>(this DataTable table, Func<T> instanceHandler) { }
List<T> ToList<T>(this DataTable table, Func<T> instanceHandler) { }
类型不一致时会发生什么？表列可能是某种类型，而 POCO 属性可能是另一种类型。默认情况下，方法使用 Convert.ChangeType() 来强制执行 POCO 属性类型。例如，表列是 decimal，POCO 属性是 int。Convert.ChangeType() 会将十进制值转换为整数。当类型可以相互转换时，这是一个合理的解决方案。如果类型无法转换（Convert.ChangeType() 引发异常），或者您希望在值分配给 POCO 属性之前进行自己的操作，您可以将一个委托（作为上面任何方法的参数）传递给它，该委托接受原始值并返回另一个值。
IEnumerable<T> Cast<T>(this DataTable table, ValueHandler getValue) { }
T[] ToArray<T>(this DataTable table, ValueHandler getValue) { }
List<T> ToList<T>(this DataTable table, ValueHandler getValue) { }
此示例将 decimal 值转换为字符串，但它们的 string 表示形式在小数点后会有 4 位数字。
public class POCO
{
public string Number { get; set; } // string type
}
DataTable table = new DataTable();
table.Columns.Add("Number", typeof(decimal)); // decimal type
table.Rows.Add(1);
table.Rows.Add(2.3);
table.Rows.Add(4.56);
table.Rows.Add(7.891);
IEnumerable<POCO> list = table.Cast<POCO>(delegate(string name, Type type, object value)
{
if (name == "Number")
return ((decimal)value).ToString("N4");
return Convert.ChangeType(value, type);
});
QueryMultiple
那么 Dapper 如何处理返回多个数据集呢？它使用一个内部类 GridReader，您可以从中读取下一个可用的数据集，直到 GridReader 被消耗。QueryMultiple 方法只是包装了这个 GridReader，并返回一个列表的列表或列表的元组。就这样。
如果从 SQL Server 返回的多个数据集具有相同的结构，即它们映射到同一个 POCO，那么 QueryMultiple 方法将返回 IEnumerable<IEnumerable<T>>。如果多个数据集具有相互不同的结构，QueryMultiple 方法将返回一个元组或列表。对于 2 个类型为 T1 和 T2 的数据集，它将返回 Tuple<IEnumerable<T1>, IEnumerable<T2>>。
存在从 2-Tuple 到 14-Tuple 的 QueryMultiple 方法（8-Tuple 中的第 8 个项本身就是一个 Tuple，从而将 Tuple 扩展到 8 项及更多）。
// all the enumerables are of the same type T
IEnumerable<IEnumerable<T>> QueryMultipleSQL<T>(string sql, dynamic param, dynamic outParam,
SqlTransaction transaction, int? commandTimeout, string connectionString) { }
// 2 enumerables, T1 and T2. can be different types
// there are method overloads from 2-Tuple up to 14-Tuple
Tuple<IEnumerable<T1>, IEnumerable<T2>> QueryMultipleSQL<T1, T2>(
string sql, dynamic param,
dynamic outParam, SqlTransaction transaction,
int? commandTimeout, string connectionString
) { }
QueryMultiple 转 DataSet
我们已经看到如何将 IEnumerable 转换为 DataTable。因此，如果我们有多个 IEnumerable 实例，我们可以将每个实例转换为 DataTable，并将它们全部打包到一个 Dataset 中。
ToDataSet 扩展方法正是这样做的。
// all the enumerables are of the same type T
DataSet ToDataSet<T>(this IEnumerable<IEnumerable<T>> instances,
string[] typeNames, MemberTypes memberTypes = MemberTypes.Field | MemberTypes.Property,
DataSet dataSet) { }
// 2 enumerables T1 and T2. can be different types
// there are method overloads from 2-Tuple up to 14-Tuple
DataSet ToDataSet<T1, T2>(this Tuple<IEnumerable<T1>, IEnumerable<T2>> instances,
string[] typeNames, MemberTypes memberTypes = MemberTypes.Field | MemberTypes.Property,
DataSet dataSet) { }
QueryMultiple 示例
第一个示例返回 2 个结果，都是 Production.Product。
// Product POCO
public class Product
{
public int ProductID { get; set; }
public string Name { get; set; }
}
使用 QueryMultipleSQL 返回多个结果。
var products = SqlHelper.QueryMultipleSQL<Product>(@"
select * from Production.Product where Product_Code <= 10;
select * from Production.Product where Product_Code > 10;
");
并将结果转换为 DataSet。
DataSet productsDS = products.ToDataSet<Product>();
第二个示例返回 2 个结果，一个结果是 Production.Product，另一个是 Person.Person，并将结果转换为 DataSet。
// Person POCO
public class Person
{
public int BusinessEntityID { get; set; }
public string FirstName { get; set; }
public string LastName { get; set; }
}
DataSet resultsDS = SqlHelper.QueryMultipleSQL<Product, Person>(@"
select * from Production.Product;
select * from Person.Person;
").ToDataSet<Product, Person>();
ExecuteScalar
ExecuteScalar 方法选择单个值。参数列表与 Query 方法类似。
object ExecuteScalarSQL(string sql, dynamic param, dynamic outParam,
SqlTransaction transaction, int? commandTimeout, string connectionString) { }
T ExecuteScalarSQL<T>(string sql, dynamic param, dynamic outParam,
SqlTransaction transaction, int? commandTimeout, string connectionString) { }
第一个示例从 Production.Product 表返回最大的产品 ID。第二个示例返回产品名称的平均长度。
// get max product id
int maxProductID = SqlHelper.ExecuteScalarSQL<int>("select max(ProductID) from Production.Product");
int maxProductID = SqlHelper.ExecuteScalarSP<int>("sp_get_max_product_id");
// get the average length of product names for products between id 500 and 600
int maxProductID = SqlHelper.ExecuteScalarSQL<int>(@"
select avg(len(Name))
from Production.Product
where ProductID between @FromProductID and @ToProductID
", new { FromProductID = 500, ToProductID = 600 });
Execute
Execute 方法运行参数化 SQL 并返回受影响的行数。参数列表与 Query 方法类似。
int ExecuteSQL(string sql, dynamic param, dynamic outParam,
SqlTransaction transaction, int? commandTimeout, string connectionString) { }
此示例在 Production.Product 表上执行更新代码，并返回一个表示成功与否的代码。
// update specific product name
var outParam = new DynamicParameters
("ResultCode", sqlDbType: SqlDbType.Int, direction: ParameterDirection.ReturnValue);
int rowsAffected = SqlHelper.ExecuteSQL(@"
begin try
begin transaction
update Production.Product
set Name = @Name
where ProductID = @ProductID
commit transaction
return 0
end try
begin catch
rollback transaction
return -1
end catch
", new { ProductID = 1, Name = "Another Product Name" }, outParam: outParam);
bool succeeded = (outParam.Get<int>("ResultCode") == 0);
处理 IEnumerable<dynamic>
有时您不想使用 POCO。也许您需要即时使用的东西。也许数据库是别人的职责，您不确定会返回什么。也许结果集返回的类型会因您传递的参数而异（列数不同、列名不同、类型不同）。无论原因如何，您都不会将结果映射到 POCO。在这种情况下，Dapper 会返回 IEnumerable<dynamic>。底层类型实际上是 DapperRow。它是一个内部类，无法从外部访问，这意味着您无法将其转换为 IEnumerable<DapperRow>。但是，您一开始就不需要那个类。dynamic 类型可以（也应该）转换为 IDictionary<string, object>，反之亦然。SQLHelper 为您完成了这些工作，并且做得更多。
在此示例中，如果您向参数 @Option 传递不同的值，结果集会返回不同的内容。如果 @Option = 1，则您会获得 2 列，类型为 string 和 int。如果 @Option = 2，则您会获得 3 列，类型为 datetime、decimal 和一种未定义类型。为这个查询创建一个 POCO 会很麻烦，而且代码很丑陋，POCO 必须包含所有可能的列。更糟糕的是，如果不同的结果集共享一个同名的列，但每个 @Option 的类型不同，您就需要为每个 @Option 的可能值编写多个 POCO 版本。
if @Option = 1
begin
select StringColumn = 'A', IntColumn = 1 union all
select StringColumn = 'B', IntColumn = 2
end
else if @Option = 2
begin
select DateColumn = getdate(), DecimalColumn = 1.1, NullColumn = null union all
select DateColumn = getdate() + 1, DecimalColumn = 2.2, NullColumn = null
end
ToProperties
SQLHelper 提供扩展方法 ToProperties，它可以接受 IEnumerable<dynamic> 并返回 IEnumerable<IDictionary<string, object>>。如果您需要，这些扩展方法还可以接受非动态类型——IEnumerable<MyPOCO>——并仍然返回适当的 IEnumerable<IDictionary<string, object>>。扩展方法可以接受一个列名列表，并在返回的结果中只返回它们。如果您 select * 了所有内容，但实际上只处理了部分列，这会很有用。
IEnumerable<IDictionary<string, object>> ToProperties(
this IEnumerable<object> objs, params string[] columnNames) { }
IEnumerable<IDictionary<string, object>> ToProperties<T>(
this IEnumerable<T> objs, params string[] columnNames) { }
IEnumerable<IDictionary<string, object>> ToProperties(
this IEnumerable<IDictionary<string, object>> objs, params string[] columnNames) { }
IDictionary<string, object> ToProperties(
this IDictionary<string, object> obj, params string[] columnNames) { }
IDictionary<string, object> ToProperties(object obj, params string[] columnNames) { }
遵循之前的示例。
string sql = "if @Option = 1 ... ";
IEnumerable<dynamic> dynamicResults = SqlHelper.QuerySQL(sql, new { Option = 1 });
IEnumerable<IDictionary<string, object>> results = dynamicResults.ToProperties();
// one line
var results = SqlHelper.QuerySQL(sql, new { Option = 1 }).ToProperties();
ToDataTable
处理 IEnumerable<IDictionary<string, object>> 可能不是存储数据的最便捷方式。您可能更习惯于使用 DataTable。SQLHelper 提供了一个扩展方法，可以直接将 IEnumerable<dynamic> 转换为 DataTable。
内部，扩展方法会调用 ToProperties 并检索 IEnumerable<IDictionary<string, object>>。根据结果集，它确定 DataTable 的列（名称和类型）应该是什么，并创建该 DataTable。然后，它将具有结果集适当数据的新行添加到 DataTable 中。您可能会注意到，构建此 DataTable 需要对结果集进行 2 次迭代，一次是为了嗅探列，另一次是为了填充 DataTable。扩展方法不对各种 IDictionary<string, object> 的结构做任何假设，这意味着它不假设它们都有相同的列名、相同的类型、相同的列数。这就是为什么它需要对结果集进行 2 次传递。如果您只对结果集的结构感兴趣，可以将参数 toEmptyDataTable = true 设置为 true，扩展方法将不会填充 DataTable。
DataTable ToDataTable(
this IEnumerable<IDictionary<string, object>> objs,
bool toEmptyDataTable = false,
string typeName = null,
DataTable table = null,
ValueHandler getValue = null,
params string[] columnNames
) { }
DataTable 的构造有一些规则。（1）如果结果集为空，则返回一个空的 DataTable。（2）默认列数据类型为 object。因此，对于给定的列，如果所有值都为 null，意味着无法确定类型，则该列数据类型将默认为 object。（3）如果值类型不同，则列数据类型设置为 object。例如，对于给定的列，存在 DateTime 和 decimal 类型的值，因此公共数据类型必须是 object。（4）DataTable 列是结果集中所有可能列的累积。结果集行的结构是相互匹配的（列名、类型、列数），如果它们都来自单个 SQL 查询。但是，如果它们不连续，意味着行来自不同的结果集，例如合并两个不相关 SQL 查询的结果，扩展方法会将所有行规范化为单个 DataTable，通过合并所有可能的列。
IEnumerable<dynamic> dynamicResults = SqlHelper.QuerySQL(
"select ProductID, Name, ProductNumber from Production.Product"
);
DataTable results = dynamicResults.ToDataTable();
// one line
DataTable products = SqlHelper.QuerySQL(
"select ProductID, Name, ProductNumber from Production.Product"
).ToDataTable();
此示例展示了两个结果集的组合如何协同工作。
// two queries. share only one column ProductID.
IEnumerable<IDictionary<string, object>> products1 = SqlHelper.QuerySQL(
"select ProductID, Name from Production.Product"
).ToProperties();
IEnumerable<IDictionary<string, object>> products2 = SqlHelper.QuerySQL(
"select ProductID, ProductNumber from Production.Product"
).ToProperties();
// add them together
List<IDictionary<string, object>> products = new List<IDictionary<string, object>>();
products.AddRange(products1);
products.AddRange(products2);
// get the DataTable
DataTable productsDT = products.ToDataTable();
// three columns
string column0 = productsDT.Columns[0].ColumnName; // ProductID
string column1 = productsDT.Columns[1].ColumnName; // Name
string column2 = productsDT.Columns[2].ColumnName; // ProductNumber
历史
- 2016/07/25：升级到 Dapper 1.50.2（.NET 4.5 和 .NET Core）。
- 2016/05/28：增加了对处理
IEnumerable<dynamic>的支持（ToProperties 和 ToDataTable）。 - 2015/08/04：升级到 Dapper 1.42。此版本可以处理十进制参数的精度和小数位数。
- 2015/08/26：错误修复
QueryMultiple在返回超过 7 个结果集时。现在QueryMultiple支持最多 14 个结果集。 - 2015/07/10：错误修复
DataTable到IEnumerable的值分配，当表列类型和 POCO 属性类型不相同时。感谢 SteveX9。 - 2015/12/21：小的错误修复
DataTable到IEnumerable。 - 2016/01/15：错误修复
DataTable到IEnumerable的值分配到没有 setter 的 POCO 属性。