现在有没有办法将TVP传递给.Net Core上的dapper？ [英] Is there a way to pass a TVP to dapper on .Net Core right now?
问题描述
我正在使用.net core和dapper，第一个没有DataTables，第二个没有将它们用于TVP。
I am using .net core and dapper, the first one doesn't have DataTables and the second one use them for TVP.
我试图转换一个 List< T> 到 List< SqlDataRecord> ，使用此列表创建一个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);
如您所见，该模型去除了用于发送电子邮件的字符串数组，因为我没有对其进行编码以嵌套电视。 （ 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扩展名方法，但.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屋！
