Dapper支持SQL 2008表值参数吗?

本文关键字：参数值参支持 SQL 2008 Dapper | 更新日期: 2023-09-27 18:04:21

我知道dapper可以支持TVF，但是你如何发送额外的参数与TVF(不添加到IntDynamicParam类)?下面的例子来自Tests.cs，我已经修改为添加了额外的参数:

connection.Execute("CREATE TYPE int_list_type AS TABLE (n int NOT NULL PRIMARY KEY)");
connection.Execute("CREATE PROC get_ints @x int, @ints int_list_type READONLY AS select * from @ints");

我尝试了以下操作，但得到了错误(没有映射存在从对象类型SqlMapper。测试+IntDynamicParam到已知托管提供程序本机类型):

var p = new DynamicParameters();
p.Add("x", 4);
p.Add("ints",new IntDynamicParam(new int[] { 1, 2, 3 }));
var nums = connection.Query<int>("get_ints", p).ToList();

谢谢你的回复，Sam，但是问题有点不同。我想知道如何将另一个变量与元组一起传入。见下面修改后的SP:

CREATE TYPE int_tuple_list_type AS TABLE (n int NOT NULL PRIMARY KEY, n2 int)
CREATE PROC get_int_tuples 
  @someVar varchar(10),
  @ints int_tuple_list_type READONLY
AS select * from @ints

Dapper支持SQL 2008表值参数吗?

IDynamicParameters几乎没有什么神奇之处，您需要担心的是在准备运行的IDbCommand上实现AddParameters。

假设你想要一个int型元组，你可以实现如下:

CREATE TYPE int_tuple_list_type 
     AS TABLE (n int NOT NULL PRIMARY KEY, n2 int)
CREATE PROC get_int_tuples @ints 
     int_tuple_list_type READONLY AS select * from @ints

后接:

class TupleIntDynamicParam : Dapper.SqlMapper.IDynamicParameters
{
    IEnumerable<int> tuples;
    public IntDynamicParam(IEnumerable<Tuple<int,int>> tuples)
    {
        this.tuples= tuples;
    }
    public void AddParameters(IDbCommand command)
    {
        var sqlCommand = (SqlCommand)command;
        sqlCommand.CommandType = CommandType.StoredProcedure;
        List<Microsoft.SqlServer.Server.SqlDataRecord> number_list = 
           new List<Microsoft.SqlServer.Server.SqlDataRecord>();
        // Create an SqlMetaData object that describes our table type.
        Microsoft.SqlServer.Server.SqlMetaData[] tvp_definition = { 
          new Microsoft.SqlServer.Server.SqlMetaData("n", SqlDbType.Int), 
          new Microsoft.SqlServer.Server.SqlMetaData("n2", SqlDbType.Int) };
        foreach (int n in tuples)
        {
            // Create a new record, using the metadata array above.
            Microsoft.SqlServer.Server.SqlDataRecord rec = 
                new Microsoft.SqlServer.Server.SqlDataRecord(tvp_definition);
            rec.SetInt32(0, n.Item1);
            rec.SetInt32(1, n.Item2);
            number_list.Add(rec);      // Add it to the list.
        }
        // Add the table parameter.
        var p = sqlCommand.Parameters.Add("ints", SqlDbType.Structured);
        p.Direction = ParameterDirection.Input;
        p.TypeName = "int_tuple_list_type";
        p.Value = number_list;
    }
}

那么你可以传入元组:

var nums = connection.Query("get_int_tuples", 
      new TupleIntDynamicParam (new Tuple<int,int>[] 
      { 
           Tuple.Create(1,2), Tuple.Create(2,3) 
      })).ToList();

这是一个完整的解决方案，使用ICustomQueryParameter接口和我写的一些代码(在附件页的底部)，让你发送任何IEnumerable到一个存储过程使用dapper，即使T有多个属性:

http://code.google.com/p/dapper-dot-net/issues/detail?can=2& = 0开始,num = 100, q = ICustomQueryParameter& colspec = ID % 20类型% 20地位% 20优先% 20里程碑% 20所有者% 20 summary& groupby =, =排序,ID = 69

下面是链接中的代码:

  /// <summary>
    /// Send DataTable as dapper parameter
    /// </summary>
    public class DapperTableParameter : ICustomQueryParameter
    {
        protected DataTable _table = null;
        public DapperTableParameter(DataTable table)
        {
            _table = table;
        }
        public void AddParameter(System.Data.IDbCommand command, string name)
        {
            // This is SqlConnection specific
            ((SqlCommand)command).Parameters.Add("@" + name, SqlDbType.Structured).Value = _table;
        }
    }

public class DapperTVP<T> : DapperTableParameter
    {
        public DapperTVP(IEnumerable<T> list) : base(new System.Data.DataTable())
        {
            var t = typeof(T);
            var propertyByName = new Dictionary<string, PropertyInfo>();
            foreach (var p in t.GetProperties())
            {
                propertyByName.Add(p.Name, p);
                _table.Columns.Add(p.Name, p.PropertyType);
            }
            foreach (var i in list)
            {
                var row = _table.NewRow();
                foreach (var p in propertyByName)
                {
                    row[p.Key] = p.Value.GetValue(i, null);
                }
                _table.Rows.Add(row);
            }
        }
    }

你仍然需要像这样合并ICustomQueryParameter:http://code.google.com/p/dapper-dot-net/issues/attachmentText?id=69&援助= 690000000,name = SqlMapper.patch&令牌= wFLdWLM4LPamcAwcDaGqcITaAmg % 3 a1392913796708

这就是我使用泛型类的方法。这还不完美。我会更新这篇文章，因为我做了更改。

public class TableTypeDynamicParam<T> : Dapper.SqlMapper.IDynamicParameters
{
    IEnumerable<T> MyList;
    Dictionary<string, int> ordinals;
    Dictionary<Type, SqlDbType> typeMap;
    string ParameterName, TableTypeName;
    public TableTypeDynamicParam(IEnumerable<T> listValue, string parameterName, string tableTypeName)
    {
        this.MyList = listValue;
        this.ParameterName = parameterName;
        this.TableTypeName = tableTypeName;
        ordinals = new Dictionary<string, int>();
        SetTypeMap();
    }
    void SetTypeMap()
    {
        typeMap = new Dictionary<Type, SqlDbType>();
        typeMap[typeof(byte)] = SqlDbType.TinyInt;
        typeMap[typeof(sbyte)] = SqlDbType.TinyInt;
        typeMap[typeof(short)] = SqlDbType.SmallInt;
        typeMap[typeof(ushort)] = SqlDbType.SmallInt;
        typeMap[typeof(int)] = SqlDbType.Int;
        typeMap[typeof(uint)] = SqlDbType.Int;
        typeMap[typeof(long)] = SqlDbType.Int;
        typeMap[typeof(ulong)] = SqlDbType.BigInt;
        typeMap[typeof(float)] = SqlDbType.Float;
        typeMap[typeof(double)] = SqlDbType.Float;
        typeMap[typeof(decimal)] = SqlDbType.Decimal;
        typeMap[typeof(bool)] = SqlDbType.Bit;
        typeMap[typeof(string)] = SqlDbType.Text;
        typeMap[typeof(char)] = SqlDbType.Char;
        typeMap[typeof(Guid)] = SqlDbType.UniqueIdentifier;
        typeMap[typeof(DateTime)] = SqlDbType.DateTime;
        typeMap[typeof(DateTimeOffset)] = SqlDbType.DateTimeOffset;
        typeMap[typeof(byte[])] = SqlDbType.Binary;
        typeMap[typeof(byte?)] = SqlDbType.TinyInt;
        typeMap[typeof(sbyte?)] = SqlDbType.TinyInt;
        typeMap[typeof(short?)] = SqlDbType.SmallInt;
        typeMap[typeof(ushort?)] = SqlDbType.SmallInt;
        typeMap[typeof(int?)] = SqlDbType.Int;
        typeMap[typeof(uint?)] = SqlDbType.Int;
        typeMap[typeof(long?)] = SqlDbType.BigInt;
        typeMap[typeof(ulong?)] = SqlDbType.BigInt;
        typeMap[typeof(float?)] = SqlDbType.Float;
        typeMap[typeof(double?)] = SqlDbType.Float;
        typeMap[typeof(decimal?)] = SqlDbType.Decimal;
        typeMap[typeof(bool?)] = SqlDbType.Bit;
        typeMap[typeof(char?)] = SqlDbType.Char;
        typeMap[typeof(Guid?)] = SqlDbType.UniqueIdentifier;
        typeMap[typeof(DateTime?)] = SqlDbType.DateTime;
        typeMap[typeof(DateTimeOffset?)] = SqlDbType.DateTimeOffset;
        //  typeMap[typeof(System.Data.Linq.Binary)] = SqlDbType.Binary;
    }
    SqlDbType GetSqlDBType(Type t)
    {
        # region other tries
        /*  SqlParameter p1 = new SqlParameter();
             TypeConverter tc = TypeDescriptor.GetConverter(p1.DbType);
             if (tc.CanConvertFrom(theType))
                 p1.DbType = (DbType)tc.ConvertFrom(theType.Name);
             else
             {
                 //Try brute force
                 try
                 {
                     p1.DbType = (DbType)tc.ConvertFrom(theType.Name);
                 }
                 catch {} //Do Nothing
             }
             return p1.SqlDbType; 
             Microsoft.SqlServer.Server.SqlMetaData sm = Microsoft.SqlServer.Server.SqlMetaData.InferFromValue(new byte[] { 1, 2 }, "someName");
sm = Microsoft.SqlServer.Server.SqlMetaData.InferFromValue(2.3, "someName");
sm = Microsoft.SqlServer.Server.SqlMetaData.InferFromValue(11, "someName");
sm =     Microsoft.SqlServer.Server.SqlMetaData.InferFromValue(System.Web.HttpValidationStatus.Valid, "someName");
sm = Microsoft.SqlServer.Server.SqlMetaData.InferFromValue("hello", "someName");
Console.WriteLine(sm.SqlDbType);
Console.WriteLine(sm.TypeName);
Console.WriteLine(sm.MaxLength);
             */
        # endregion
        return typeMap[t];
    }
    void SetRecordValue(ref Microsoft.SqlServer.Server.SqlDataRecord rec, string propertyName, object value, Type propertyType)
    {
        switch (propertyType.ToString())
        {
            //need to cover all type case
            case "System.Int32":
                rec.SetInt32(ordinals[propertyName], (int)value);
                break;
            case "System.Int64":
                rec.SetInt64(ordinals[propertyName], (long)value);
                break;
            case "System.Boolean":
                rec.SetBoolean(ordinals[propertyName], (bool)value);
                break;
            case "System.String":
            default:
                rec.SetString(ordinals[propertyName], Convert.ToString(value));
                break;
        }
    }
    public void AddParameters(IDbCommand command)
    {
        var sqlCommand = (SqlCommand)command;
        sqlCommand.CommandType = CommandType.StoredProcedure;
        List<Microsoft.SqlServer.Server.SqlDataRecord> tableType_list = new List<Microsoft.SqlServer.Server.SqlDataRecord>();
        var first = MyList.FirstOrDefault();
        if (null != first)
        {
            var lstDefinition = new List<Microsoft.SqlServer.Server.SqlMetaData>();
            int i = 0;
            foreach (var prop in first.GetType().GetProperties())
            {
                lstDefinition.Add(new Microsoft.SqlServer.Server.SqlMetaData(prop.Name, GetSqlDBType(prop.PropertyType)));
                ordinals.Add(prop.Name, i++);
            }
            // Create an SqlMetaData object that describes our table type.
            // Microsoft.SqlServer.Server.SqlMetaData[] tvp_definition = { new Microsoft.SqlServer.Server.SqlMetaData("n", SqlDbType.Int) };
            foreach (var l in MyList)
            {
                // Create a new record, using the metadata array above.
                Microsoft.SqlServer.Server.SqlDataRecord rec = new Microsoft.SqlServer.Server.SqlDataRecord(lstDefinition.ToArray());
                foreach (var prop in first.GetType().GetProperties())
                {
                    // rec.SetInt32(ordinals[prop.Name], prop.GetValue(l));    // Set the value.
                    SetRecordValue(ref rec, prop.Name, prop.GetValue(l), prop.GetType());
                }
                tableType_list.Add(rec);      // Add it to the list.
            }
        }
        // Add the table parameter.
        var p = sqlCommand.Parameters.Add("@" + ParameterName.TrimStart('@'), SqlDbType.Structured);
        p.Direction = ParameterDirection.Input;
        p.TypeName = TableTypeName;
        if (null != first)
            p.Value = tableType_list;
        else
            p.Value = DBNull.Value;
    }
    public void AddParameters(IDbCommand command, SqlMapper.Identity identity)
    {
        AddParameters(command);
    }
}

有一个nuget包，Dapper TVP，其中包括一些类，使使用表值参数更容易。

需要注意的是，该包依赖于Dapper 1.12.1或更高版本。

使用这个包将tvp传递给存储过程看起来像这样:

        var p = new Dapper.Tvp.DynamicParametersTvp();
        SqlMetaData[] tableValuedParameterDefinition = 
        { 
            new SqlMetaData("columnOne", SqlDbType.UniqueIdentifier), 
            new SqlMetaData("columnTwo", SqlDbType.VarBinary, 64)
        };
        var collectionRows = new List<SqlDataRecord>();
        for (int i = 0; i < collection.Count; i++)
        {
            var row = new SqlDataRecord(tableValuedParameterDefinition);
            row.SetGuid(0, collection[i].Item1);
            row.SetBytes(1, 0, collection[i].Item2, 0, sessionsAndPins[i].Item2.Length);
            collectionRows.Add(row);
        }
        p.Add(new Dapper.Tvp.TableValueParameter("@tvpName", "tvpTypeName", collectionRows));
        using (SqlConnection connection = new SqlConnection(ConnectionManager.GetConnectionString("Database")))
        {
            try
            {
                connection.Open();
                result = connection.Query<SomeObject>("SomeStoredProcedureThatExpectsTVP", p, commandType: CommandType.StoredProcedure).SingleOrDefault();
            }
            finally
            {
                connection.Close();
            }
        }

我需要能够传递表值参数和常规参数。结合@Esen和@Roger-Joys的答案，我得出了下面的结论。这个类专门用于使用stringlist_to_table类型，但是您可以根据自己的需要更改它。对我来说，对于你将要处理的每种TVP类型都有一个特定的方法似乎是有意义的。

CREATE TYPE stringlist_tbltype AS TABLE (s VARCHAR(256))

用法:

var p = new DynamicParametersWithTVP();
p.Add( "@someVar", "abc", DbType.AnsiString );
p.AddTVPString( "@stringlist", new string[] { "abc", "def" } );
using (var conn = new SqlConnection( _cs )) {
    conn.Open();
    return conn.Query( "dbo.stored_proc_name", p, commandType: CommandType.StoredProcedure );
}

下面是DynamicParametersWithTVP的实现。它基本上保留了一个DynamicParameters类来处理非TVP的东西，以及TVP的构建。

class DynamicParametersWithTVP : SqlMapper.IDynamicParameters
{
    private Dictionary<string, TVPData> _tvpParams;
    private DynamicParameters _params;
    public DynamicParametersWithTVP()
    {
        _tvpParams = new Dictionary<string, TVPData>();
        _params = new DynamicParameters();
    }
    public void Add( string name, object value = null, DbType? dbType = null, ParameterDirection? direction = null, int? size = null )
    {
        _params.Add( name, value, dbType, direction, size );
    }
    public void AddTVPString( string name, IEnumerable<string> values )
    {
        var metadata = new[] {
            new SqlMetaData( "one", SqlDbType.VarChar, 256)
        };
        var data = new TVPData
        {
            Data = values,
            MetaData = metadata,
            Name = name,
            Type = "stringlist_tbltype",
            SetData = ( rec, s ) => rec.SetString( 0, (string)s ),
        };
        _tvpParams.Add( name, data );
    }
    public void AddParameters( IDbCommand command, SqlMapper.Identity identity )
    {
        //Adds the regular parameters, then adds any tvp parameters
        ((SqlMapper.IDynamicParameters)_params).AddParameters( command, identity );
        AddTVPCommands( command );
    }
    private void AddTVPCommands( IDbCommand command )
    {
        foreach (var o in _tvpParams) {
            AddTVPParam( command, o.Key, o.Value );
        }
    }
    private void AddTVPParam( IDbCommand command, string name, TVPData value )
    {
        var dataList = new List<SqlDataRecord>();
        if (value.Data != null) {
            foreach (var d in value.Data) {
                var rec = new SqlDataRecord( value.MetaData );
                value.SetData( rec, d ); //note: this only works for a one-column TVP
                dataList.Add( rec );
            }
        }
        if (dataList.Count == 0) { //gotta make the value null if there isn't any data.
            dataList = null;
        }
        var p = ((SqlCommand)command).Parameters.Add( name, SqlDbType.Structured );
        p.Direction = ParameterDirection.Input;
        p.TypeName = value.Type;
        p.Value = dataList;
    }
    private class TVPData
    {
        public string Name { get; set; }
        public string Type { get; set; }
        public SqlMetaData[] MetaData { get; set; }
        public IEnumerable Data { get; set; }
        public Action<SqlDataRecord, object> SetData { get; set; }
    }
}

我更喜欢这样做:

internal class TableTypeDynamicParam : SqlMapper.IDynamicParameters {
    protected readonly List<object> Lst = new List<object>();
    protected SqlCommand Cmd;
    public void Add(DataTable dt) {
        if(dt != null)
            Lst.Add(dt);
    }
    public void Add(DataSet ds) {
        if(ds != null)
            foreach(DataTable dt in ds.Tables) {
                Lst.Add(dt);
            }
    }
    public void Add(string name, object value = null, SqlDbType? dbType = null, ParameterDirection direction = ParameterDirection.Input, int? size = null) {
        var par = value != null ? new SqlParameter(name, value) : new SqlParameter(name, dbType);
        par.Direction = direction;
        if(size.HasValue)
            par.Size = size.Value;
        Lst.Add(par);
    }
    public void Add(SqlParameter par) {
        Lst.Add(par);
    }
    public void AddRange(IEnumerable<SqlParameter> pars) {
        if(pars != null)
            Lst.AddRange(pars);
    }
    public void AddParameters(IDbCommand command) {
        Cmd = (SqlCommand)command;
        foreach(var o in Lst) {
            var dt = o as DataTable;
            if(dt != null) {
                var parameter = Cmd.Parameters.AddWithValue(dt.ExtendedProperties["parameterName"].ToString(), dt);
                parameter.SqlDbType = SqlDbType.Structured;
                parameter.TypeName = dt.TableName;
            } else {
                var par = o as SqlParameter;
                if(par != null)
                    Cmd.Parameters.Add(par);
            }
        }
    }
    public void AddParameters(IDbCommand command, SqlMapper.Identity identity) {
        AddParameters(command);
    }
    public T Get<T>(string name) {
        return (T)Cmd.Parameters[name].Value;
    }
    public ERPDictionay GetLst(ERPLst<SqlParameter> pars) {
        var dic = new ERPDictionay();
        if(pars != null) {
            dic.QtdItemsPageParameterName = pars.QtdItemsPageParameterName;
            dic.QtdItemsPage = pars.QtdItemsPage;
            dic.CurrentPageParameterName = pars.CurrentPageParameterName;
            dic.CurrentPage = pars.CurrentPage;
        };
        for(var i = 0; i < Cmd.Parameters.Count; i++) {
            var a = Cmd.Parameters[i];
            if (a.Direction != ParameterDirection.InputOutput && a.Direction != ParameterDirection.Output) continue;
            if(pars != null && a.ParameterName == pars.TotalItensDBParameterName) {
                dic.TotalItensDB = Convert.ToInt32(Cmd.Parameters[i].Value);
            } else {
                dic.Add(a.ParameterName, Cmd.Parameters[i].Value);
            }
        }
        return dic;
    }
}
internal class TableTypeDynamicParam<T> : TableTypeDynamicParam
{
    private ERPLstInput<T> _lstItems;
    public void Add(ERPLstInput<T> lstItems)
    {
        if (lstItems != null)
            _lstItems = lstItems;
    }
    private static Dictionary<Type, SqlDbType> _typeMap;
    private static Dictionary<Type, SqlDbType> TypeMap
    {
        get
        {
            if (_typeMap != null)
                return _typeMap;
            _typeMap = new Dictionary<Type, SqlDbType>();
            _typeMap[typeof(byte)] = SqlDbType.TinyInt;
            _typeMap[typeof(sbyte)] = SqlDbType.TinyInt;
            _typeMap[typeof(short)] = SqlDbType.SmallInt;
            _typeMap[typeof(ushort)] = SqlDbType.SmallInt;
            _typeMap[typeof(int)] = SqlDbType.Int;
            _typeMap[typeof(uint)] = SqlDbType.Int;
            _typeMap[typeof(long)] = SqlDbType.Int;
            _typeMap[typeof(ulong)] = SqlDbType.BigInt;
            _typeMap[typeof(float)] = SqlDbType.Float;
            _typeMap[typeof(double)] = SqlDbType.Float;
            _typeMap[typeof(decimal)] = SqlDbType.Decimal;
            _typeMap[typeof(bool)] = SqlDbType.Bit;
            _typeMap[typeof(string)] = SqlDbType.Text;
            _typeMap[typeof(char)] = SqlDbType.Char;
            _typeMap[typeof(Guid)] = SqlDbType.UniqueIdentifier;
            _typeMap[typeof(DateTime)] = SqlDbType.DateTime;
            _typeMap[typeof(DateTimeOffset)] = SqlDbType.DateTimeOffset;
            _typeMap[typeof(byte[])] = SqlDbType.Binary;
            _typeMap[typeof(byte?)] = SqlDbType.TinyInt;
            _typeMap[typeof(sbyte?)] = SqlDbType.TinyInt;
            _typeMap[typeof(short?)] = SqlDbType.SmallInt;
            _typeMap[typeof(ushort?)] = SqlDbType.SmallInt;
            _typeMap[typeof(int?)] = SqlDbType.Int;
            _typeMap[typeof(uint?)] = SqlDbType.Int;
            _typeMap[typeof(long?)] = SqlDbType.BigInt;
            _typeMap[typeof(ulong?)] = SqlDbType.BigInt;
            _typeMap[typeof(float?)] = SqlDbType.Float;
            _typeMap[typeof(double?)] = SqlDbType.Float;
            _typeMap[typeof(decimal?)] = SqlDbType.Decimal;
            _typeMap[typeof(bool?)] = SqlDbType.Bit;
            _typeMap[typeof(char?)] = SqlDbType.Char;
            _typeMap[typeof(Guid?)] = SqlDbType.UniqueIdentifier;
            _typeMap[typeof(DateTime?)] = SqlDbType.DateTime;
            _typeMap[typeof(DateTimeOffset?)] = SqlDbType.DateTimeOffset;
            return _typeMap;
        }
    }
    static SqlDbType GetSqlDBType(Type t) {
        return TypeMap[t];
    }
    void SetRecordValue(ref SqlDataRecord rec, string propertyName, object value, Type propertyType) {
        switch(propertyType.ToString()) {
            case "System.Int32":
                rec.SetInt32(def.IndexOf(a=>a.Name == propertyName), (int)value);
                break;
            case "System.Int64":
                rec.SetInt64(def.IndexOf(a => a.Name == propertyName), (long)value);
                break;
            case "System.Boolean":
                rec.SetBoolean(def.IndexOf(a => a.Name == propertyName), (bool)value);
                break;
            case "System.String":
            default:
                rec.SetString(def.IndexOf(a => a.Name == propertyName), Convert.ToString(value));
                break;
        }
    }
    private List<SqlMetaData> def;
    public new void AddParameters(IDbCommand command) {
        Cmd = (SqlCommand)command;
        foreach(var o in Lst) {
            var dt = o as DataTable;
            if(dt != null) {
                var parameter = Cmd.Parameters.AddWithValue(dt.ExtendedProperties["parameterName"].ToString(), dt);
                parameter.SqlDbType = SqlDbType.Structured;
                parameter.TypeName = dt.TableName;
            } else {
                var par = o as SqlParameter;
                if(par != null)
                    Cmd.Parameters.Add(par);
            }
        }
        if (_lstItems != null)
        {
            var tableTypeList = new List<SqlDataRecord>();
            var first = _lstItems.FirstOrDefault();
            if (first != null)
            {
                def = new List<SqlMetaData>();
                int i = 0;
                foreach (var prop in first.GetType().GetProperties())
                {
                    def.Add(new SqlMetaData(prop.Name, GetSqlDBType(prop.PropertyType)));
                }
                foreach (var l in _lstItems)
                {
                    var rec = new SqlDataRecord(def.ToArray());
                    foreach (var prop in first.GetType().GetProperties())
                    {
                        SetRecordValue(ref rec, prop.Name, prop.GetValue(l), prop.GetType());
                    }
                    tableTypeList.Add(rec);
                }
            }
            var p = Cmd.Parameters.Add("@" + _lstItems.ParameterName.TrimStart('@'), SqlDbType.Structured);
            p.Direction = ParameterDirection.Input;
            p.TypeName = _lstItems.TableTypeName;
            if (null != first)
                p.Value = tableTypeList;
            else
                p.Value = DBNull.Value;
        }
    }
}

字典是这样的:

[Serializable]
public class ERPDictionay : Dictionary<string,object> {
    private string _currentPageParameterName = "@currentPage";
    public string CurrentPageParameterName {
        get { return _currentPageParameterName; }
        set { _currentPageParameterName = value; }
    }
    private string _qtdItemsPageParameterName = "@qtdItemsPage";
    public string QtdItemsPageParameterName {
        get { return _qtdItemsPageParameterName; }
        set { _qtdItemsPageParameterName = value; }
    }
    private string _totalItensDBParameterName = "@totalItensDB";
    public string TotalItensDBParameterName {
        get { return _totalItensDBParameterName; }
        set { _totalItensDBParameterName = value; }
    }
    public int CurrentPage { get; set; }
    public int QtdItemsPage { get; set; }
    public int TotalItensDB { get; set; }
    public ERPDictionay() { }
    public ERPDictionay(
        int qtdItemsPage,
        int currentPage = 0,
        string currentPageParameterName = null,
        string qtdItemsPageParameterName = null,
        string totalItensDBParameterName = null)
    {
        QtdItemsPage = qtdItemsPage;
        CurrentPage = currentPage;
        if (!string.IsNullOrEmpty(currentPageParameterName))
            _currentPageParameterName = currentPageParameterName;
        if(!string.IsNullOrEmpty(qtdItemsPageParameterName))
            _qtdItemsPageParameterName = qtdItemsPageParameterName;
        if(!string.IsNullOrEmpty(totalItensDBParameterName))
            _totalItensDBParameterName = totalItensDBParameterName;
    }
}

如果您从DynamicParameters派生自定义类并实现iddynamicparameters，则可以解决上述问题。

public class DynamicParametersWithTVP : Dapper.DynamicParameters, Dapper.SqlMapper.IDynamicParameters
{

…

我写了一个通用类，它允许你像这样调用一个进程:

        var procParams = new DynamicParametersWithTVP();
        procParams.Add("@Param1", someValue);
        procParams.Add("@Param2", someOtherValue);
        procParams.AddTVP("@TableParam1", "dbo.someTableType", someEnumberableListOfObject);