现在有没有办法将 TVP 传递给 .Net Core 上的 dapper? [英] Is there a way to pass a TVP to dapper on .Net Core right now?
问题描述
我正在使用 .net core 和 dapper，第一个没有数据表，第二个将它们用于 TVP.
I am using .net core and dapper, the first one doesn't have DataTables and the second one use them for TVP.
我试图将 List 转换为 List，使用此列表创建一个 SqlParameter，然后将其转换为 DynamicParameter 但遗憾的是我得到一个:Microsoft.SqlServer.Server.SqlDataRecord 类型的成员不能用作参数值
I was trying to convert a List<T> to a List<SqlDataRecord>, create a SqlParameter with this list and then convert it to a DynamicParameter but sadly I got an: The member of type Microsoft.SqlServer.Server.SqlDataRecord cannot be used as a parameter value
在使用 IDynamicParameters 玩了一下之后，我让它工作了.
After playing a bit with IDynamicParameters, I made it work.
IEnumerable的扩展方法
public static DynamicWrapper toTVP<T>(this IEnumerable<T> enumerable, string tableName, string typeName)
{
List<SqlDataRecord> records = new List<SqlDataRecord>();
var properties = typeof(T).GetProperties().Where(p => Mapper.TypeToSQLMap.ContainsKey(p.PropertyType));
var definitions = properties.Select(p => Mapper.TypeToMetaData(p.Name, p.PropertyType)).ToArray();
foreach (var item in enumerable)
{
var values = properties.Select(p => p.GetValue(item, null)).ToArray();
var schema = new SqlDataRecord(definitions);
schema.SetValues(values);
records.Add(schema);
}
SqlParameter result = new SqlParameter(tableName, SqlDbType.Structured);
result.Direction = ParameterDirection.Input;
result.TypeName = typeName;
result.Value = records;
return new DynamicWrapper(result);
}
实现IDynamicParameters
public class DynamicWrapper : IDynamicParameters
{
private readonly SqlParameter _Parameter;
public DynamicWrapper(SqlParameter param)
{
_Parameter = param;
}
public void AddParameters(IDbCommand command, Identity identity)
{
command.Parameters.Add(_Parameter);
}
}
Mapper(未完全测试，仅托管字符串到 NVARCHAR 因为它在没有 maxLength 的情况下抛出异常)
Mapper (not fully tested, only managed string to NVARCHAR because it throws an exception without maxLength)
public class Mapper
{
public static Dictionary<Type, SqlDbType> TypeToSQLMap = new Dictionary<Type, SqlDbType>()
{
{typeof (long),SqlDbType.BigInt},
{typeof (long?),SqlDbType.BigInt},
{typeof (byte[]),SqlDbType.Image},
{typeof (bool),SqlDbType.Bit},
{typeof (bool?),SqlDbType.Bit},
{typeof (string),SqlDbType.NVarChar},
{typeof (DateTime),SqlDbType.DateTime2},
{typeof (DateTime?),SqlDbType.DateTime2},
{typeof (decimal),SqlDbType.Money},
{typeof (decimal?),SqlDbType.Money},
{typeof (double),SqlDbType.Float},
{typeof (double?),SqlDbType.Float},
{typeof (int),SqlDbType.Int},
{typeof (int?),SqlDbType.Int},
{typeof (float),SqlDbType.Real},
{typeof (float?),SqlDbType.Real},
{typeof (Guid),SqlDbType.UniqueIdentifier},
{typeof (Guid?),SqlDbType.UniqueIdentifier},
{typeof (short),SqlDbType.SmallInt},
{typeof (short?),SqlDbType.SmallInt},
{typeof (byte),SqlDbType.TinyInt},
{typeof (byte?),SqlDbType.TinyInt},
{typeof (object),SqlDbType.Variant},
{typeof (DataTable),SqlDbType.Structured},
{typeof (DateTimeOffset),SqlDbType.DateTimeOffset}
};
public static SqlMetaData TypeToMetaData(string name, Type type)
{
SqlMetaData data = null;
if (type == typeof(string))
{
data = new SqlMetaData(name, SqlDbType.NVarChar, -1);
}
else
{
data = new SqlMetaData(name, TypeToSQLMap[type]);
}
return data;
}
}
我的示例的 SQL 类型:
SQL Type for my example:
CREATE TYPE TestType AS TABLE (
FirstName NVARCHAR(255)
, GamerID INT
, LastName NVARCHAR(255)
, Salt UNIQUEIDENTIFIER);
GO
使用:
List<Gamer> gamers = new List<Gamer>();
gamers.Add(new Gamer {
Email = new string[] { "dsadsdsa@dasddas.com" },
FirstName = "Test_F0",
LastName = "Test_L0",
GamerID = 0,
Salt = Guid.NewGuid()});
gamers.Add(new Gamer {
Email = new string[] { "11111@11111.com" },
FirstName = "Test_F1",
LastName = "Test_L1",
GamerID = 1,
Salt = Guid.NewGuid()});
var structured = gamers.toTVP("GamerTable", "dbo.TestType");
using (var con = new SqlConnection(TestConnectionString))
{
con.Open();
string query = @"
SELECT *
FROM @GamerTable t
WHERE t.GamerID = 1
";
var result = con.Query(query, structured);
//var result = con.Query("dbo.DapperTest", structured, commandType: CommandType.StoredProcedure);
如您所见，该模型去除了电子邮件的字符串数组，因为我没有将其编码为嵌套 tvp.(TypeToSQLMap.ContainsKey 部分)，但可以编码，更改包装器以接受可枚举的参数和 AddParameters 以 foreach 并添加它们.更多的是关于类型名称等的问题.我正在考虑创建一些基于属性类型命名的泛型类型.目前，这就足够了，如果我不这样做，请随时升级.
As you can see, the model stripped out the array of strings for emails, coz I didn't code it to have nested tvp. (TypeToSQLMap.ContainsKey part), but could be coded, changing the wrapper to accept an enumerable of parameters and AddParameters to foreach and add them. Is more about a problem with the types names, etc. I was thinking to create some generic types named based on the property types. For now, this is enough, feel free to upgrade it if i dont do it.
今天晚些时候我会尝试改进它.
I Will try to improve it a bit more later today.
推荐答案
是的，这是可能的.在 .NET Framework 中，您可以使用 .AsTableValuedParameter extension 方法，但在 .NET Core 中没有此选项(从 Dapper v 1.5 开始)要解决该问题，您必须创建一个实现ICustomQueryMapper:
Yes, it is possible. In .NET Framework you can use the .AsTableValuedParameter extension methods but you don't have this option in .NET Core (as of Dapper v 1.5) To solve the problem you have to create a class that implements ICustomQueryMapper:
public class TabledValuedParameter: ICustomQueryMapper
{
public void AddParameter() {...}
}
然后你可以用它来包装你的 IEnumerable.我在这里写了一篇关于这个主题的文章:
And then you can use it to wrap your IEnumerable. I've written and article on the subject here:
https://medium.com/dapper-net/sql-server-specific-features-2773d894a6ae
示例代码可在 GitHub 上获取:
And sample code is available hon GitHub:
https://github.com/yorek/dapper-samples/blob/master/Dapper.Samples.Advanced/SQLServerFeatures.cs
现在不再需要变通方法，因为 Dapper 2.0 TVP 是本机支持的.我刚刚更新了我的示例，现在一切正常.
Now the workaround is not needed anymore, as with Dapper 2.0 TVPs are natively supported. I just updated my sample, and now everything works nicely.
这篇关于现在有没有办法将 TVP 传递给 .Net Core 上的 dapper?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
