如何在.NET Core中向Dapper传递表值参数？

Question

我正在使用.NET Core和Dapper。我的问题是.NET Core没有DataTable，而这是Dapper用于表值参数（TVP）的东西。

我试图将List转换为List，创建一个SqlParameter与此列表一起使用，然后将其转换为DynamicParameter：

public static SqlParameter toTVP<T>(this IList<T> enumerable, string name)
{
    List<SqlDataRecord> records = new List<SqlDataRecord>();
    // filter allowed types
    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(name, SqlDbType.Structured);
    result.Direction = ParameterDirection.Input;
    result.TypeName = $"{name}Type";
    result.Value = records;
    return result;
}

然后：

var structured = MyList.toTVP("Test");
var p = new DynamicParameters(new { });
p.Add(structured.ParameterName,structured.Value);

var result = con.Query(query, p);

但我遇到了错误：

类型为 Microsoft.SqlServer.Server.SqlDataRecord 的成员不能用作参数值。

有没有办法让这个工作起来？

- Nauzet

---

无论如何都不可能起作用，因为TVP需要“TypeName”，而这并没有从“structured”中读取。根据此实现“IDynamicParameters”应该可以解决问题，参见此链接。 - Jeroen Mostert

自从这个问题被提出以来，.NET Core 2.0的预览版已经发布，并包括DataTables。我知道迁移是一件大事，尤其是到预览版本，但如果您有可能，现在可以使用.NET Core 2.0。 - AdrienTorris

@AdrienTorris 抱歉，我知道你的评论有点旧了，但我想补充一下，是的，DataTables在.Net Core 2.0中得到支持，但是 AsTableValuedParameter() 方法没有实现，因此据我所知仍需要解决方法。 - pcdev

实际上，这个解决方法对我也不起作用：当查询被执行时，我会收到错误信息*System.ArgumentException: '结构化类型中没有足够的字段。结构化类型必须至少有一个字段。'*。噢，看来原始的DataTable方法很快就会得到完全支持。 - pcdev

@pcdev 我相信 AsTableValuedParameter() 是 Dapper 自身实现的扩展方法。你可能缺少了一些引用？ - Ángela

Answer 1

从 Dapper 2.0 开始，TVPs 得到了本地支持。GitHub 上提供了一些示例代码：https://github.com/yorek/dapper-samples/blob/master/Dapper.Samples.Advanced/SQLServerFeatures.cs。

对于版本低于 2.0 的 TVPs，则需要使用 .NET Framework，在其中可以使用 .AsTableValuedParameter extension 方法。 但是，在 .NET Core 中（截至 Dapper v1.5），您没有此选项。为解决此问题，您需要创建一个实现 ICustomQueryMapper 接口的类。

public class TabledValuedParameter: ICustomQueryMapper
{
    public void AddParameter() {...}
}

然后，您可以使用它来包装您的IEnumerable。我在这里写了一篇有关该主题的文章：https://medium.com/dapper-net/sql-server-specific-features-2773d894a6ae。

- mauridb

---

请在您的帖子中包含示例。 - Braiam

这个回答似乎不太有用，因为 OP 正在使用 .NET Core 而不是 .NET Framework。如果现在在 Dapper 2.0 中可用，只需删除答案中的 .NET Framework 部分即可。 - TylerH

Answer 2

提问者的解决方案已移至答案区

After playing a bit with IDynamicParameters, I made it work.

Extension method for 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);
}

Wrapper to implement 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 (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 Type for my example:

CREATE TYPE TestType AS TABLE ( 
  FirstName NVARCHAR(255)  
    , GamerID INT 
  , LastName NVARCHAR(255)
  , Salt UNIQUEIDENTIFIER);  
GO  

Using it:

List<Gamer> gamers = new List<Gamer>();

gamers.Add(new Gamer {
                Email = new string[] { "[e-mail]" },
                FirstName = "Test_F0",
                LastName = "Test_L0",
                GamerID = 0,
                Salt = Guid.NewGuid()});

            gamers.Add(new Gamer {
                Email = new string[] { "[e-mail]" },
                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);

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.

- Braiam