Dapper动态参数与表值参数

Question

Dapper动态参数与表值参数

c#sql-serverstored-proceduresdatatabledapper

18

我试图创建一个通用的方法，它可以在运行时从类中读取参数名称和值，并创建用于Dapper查询执行的参数集合。意识到只要所有参数都是输入类型，它就可以很好地工作，但如果我必须添加输出/返回值类型参数，那么我需要使用DynamicParameters，否则我无法获取输出/返回值参数的值。

存储过程具有以下参数：

PersonList - TableValued - Input
TestOutput - Int - Output

我无法让以下代码正常工作：

var dynamicParameters = new DynamicParameters();
dynamicParameters.Add("PersonList", <DataTable PersonList>);
dynamicParameters.Add("TestOutput", 0, Dbtype.Int32, ParameterDirection.Output);

异常信息为：

System.Data.SqlClient.SqlException: 输入的表格数据流(TDS)远程过程调用(RPC)协议流不正确。参数1("@PersonList")：数据类型0x62(sql_variant)对于特定类型的元数据无效。

我的理解是，在动态参数中添加表值参数(TVP)时没有有效的DbType可用，因为我没有使用SqlDbType,所以DbType中也没有SqlDbType.Structured的替代方法。

请提供任何指针或解决此问题的方法。

- Mrinal Kamboj

显然，在Dapper中，默认情况下无法使用IDbConnection访问参数集合，因此使用标准机制时，我无法访问输出/返回值参数。 - Mrinal Kamboj

1

尝试使用这个解决方案。 https://dev59.com/kG025IYBdhLWcg3wEhnr - Salman Syed

@SalmanSyed，感谢您提供的详细信息，但是为每个相关类型创建助手将会很繁琐。我的自定义解决方案更加通用，可以完成相同的任务。 - Mrinal Kamboj

6个回答

3

在参数对象的add函数中传递DbType.Object作为参数。

例如：DynamicParameters parameterObject = new DynamicParameters(); parameterObject.Add("@table", dtTable, DbType.Object);

- Kasun

1

哈哈哈，这个答案没有投票也没有被突出显示，但是对我有用。 - Haider Ali Wajihi

也为我们工作了，谢谢。 - trevorgk

2

我理解这个需求不能直接满足，可能需要编写特定的帮助程序。我使用自定义基础抽象类TypeMap来解决这个问题，可以通过所有类型的提供程序进行扩展，以实现API，这些API不能使用Dapper直接实现。下面是我针对SQL-Server的实现，其他符合ADO.Net标准的提供程序也可以采取类似方法：

namespace Dapper
{
    #region NameSpaces

    using System;
    using System.Collections.Generic;
    using System.Data;
    using System.Data.SqlClient;
    using System.Linq;

    #endregion


        /// <summary>
        ///     Type Map class for database provider specific code
        /// </summary>
        internal abstract class TypeMap
        {
            /// <summary>
            /// Only Non Input Parameters collection
            /// </summary>
            public abstract Dictionary<string, object> NonInputParameterCollection { get; set; } 

            /// <summary>
            /// Method to execute the DML via TypeMap
            /// </summary>
            /// <param name="connection"></param>
            /// <param name="sql"></param>
            /// <param name="commandType"></param>
            /// <param name="dapperParams"></param>
            /// <returns></returns>
            public abstract int Execute(IDbConnection connection, 
                                        string sql, 
                                        CommandType commandType,
                                        IEnumerable<DapperParam> dapperParams );

            /// <summary>
            /// Method to execute the Select to fetch IEnumerable via TypeMap
            /// </summary>
            /// <typeparam name="T"></typeparam>
            /// <param name="connection"></param>
            /// <param name="sql"></param>
            /// <param name="commandType"></param>
            /// <param name="dapperParams"></param>
            /// <returns></returns>
            public abstract IEnumerable<T> Query<T>(IDbConnection connection,
                                                    string sql,
                                                    CommandType commandType,
                                                    IEnumerable<DapperParam> dapperParams) where T : new();

            /// <summary>
            /// Fetch the relevant TypeMap
            /// </summary>
            /// <param name="provider"></param>
            /// <returns></returns>
            public static TypeMap GetTypeMap(string provider)
            {
                TypeMap typeMap = null;

                switch (provider)
                {
                    case "System.Data.SqlClient":
                        typeMap = new SqlTypeMap();
                        break;
                    default:
                        // SQl Server TypeMap
                        typeMap = new SqlTypeMap();
                        break;
                }

                return (typeMap);
            }
        }

        /// <summary>
        ///     SQL Server provider type map
        /// </summary>
        internal class SqlTypeMap : TypeMap
        {
            public SqlTypeMap()
            {
                NonInputParameterCollection = new Dictionary<string, object>();
            }

            public override sealed Dictionary<string, object> NonInputParameterCollection { get; set; } 

            public override int Execute(IDbConnection connection,
                                        string sql,
                                        CommandType commandType,
                                        IEnumerable<DapperParam> dapperParams)
            {
                int returnValue = -1;

                var sqlConnection = (connection as SqlConnection) ?? new SqlConnection();

                using (sqlConnection)
                {
                    SqlCommand sqlCommand = null;

                    sqlCommand = sqlConnection.CreateCommand();

                    using (sqlCommand)
                    {
                        // public SqlParameter(string parameterName, SqlDbType dbType, int size, ParameterDirection direction, byte precision, byte scale, string sourceColumn, DataRowVersion sourceVersion, bool sourceColumnNullMapping, object value, string xmlSchemaCollectionDatabase, string xmlSchemaCollectionOwningSchema, string xmlSchemaCollectionName);
                        foreach (var param in dapperParams)
                        {
                            sqlCommand.Parameters.Add(new SqlParameter
                            {
                                ParameterName = param.ParamName,
                                SqlValue = param.ParamValue ?? DBNull.Value,
                                SqlDbType = TypeToSqlDbType[param.ParamType],
                                Direction = Map.DirectionMap[param.ParamDirection]
                            });
                        }

                        sqlCommand.CommandText = sql; // Assign Sql Text
                        sqlCommand.CommandType = commandType; // Assign CommandType
                        sqlCommand.Connection.Open(); // Explicitly open connection to use it with SqlCommand object
                        returnValue = sqlCommand.ExecuteNonQuery(); // Execute Query

                        foreach (SqlParameter param in sqlCommand.Parameters.Cast<SqlParameter>().Where(param => param.Direction != ParameterDirection.Input))
                            NonInputParameterCollection.Add(param.ParameterName, param.Value);
                    }
                }

                return (returnValue);
            }

            public override IEnumerable<T> Query<T>(IDbConnection connection,
                                      string sql,
                                      CommandType commandType,
                                      IEnumerable<DapperParam> dapperParams)
            {
                IEnumerable<T> returnEnumerable = null;

                var sqlConnection = (connection as SqlConnection) ?? new SqlConnection();

                using (sqlConnection)
                {
                    var sqlCommand = sqlConnection.CreateCommand();

                    using (sqlCommand)
                    {
                        foreach (var param in dapperParams)
                        {
                            sqlCommand.Parameters.Add(new SqlParameter
                            {
                                ParameterName = param.ParamName,
                                SqlValue = param.ParamValue ?? DBNull.Value,
                                SqlDbType = TypeToSqlDbType[param.ParamType],
                                Direction = Map.DirectionMap[param.ParamDirection]
                            });
                        }

                        sqlCommand.CommandText = sql; // Assign Sql Text
                        sqlCommand.CommandType = commandType; // Assign CommandType

                        var sqlDataAdapter = new SqlDataAdapter(sqlCommand);

                        var returnDataTable = new DataTable();

                        sqlDataAdapter.Fill(returnDataTable);

                        returnEnumerable = Common.ToList<T>(returnDataTable);

                        foreach (SqlParameter param in sqlCommand.Parameters.Cast<SqlParameter>()
                                                                 .Where(param => param.Direction != ParameterDirection.Input))
                            NonInputParameterCollection.Add(param.ParameterName, param.Value);
                    }
                }

                return (returnEnumerable);
            }

            /// <summary>
            ///     Data Type to Db Type mapping dictionary for SQL Server
            /// https://msdn.microsoft.com/en-us/library/cc716729(v=vs.110).aspx
            /// </summary>

            public static readonly Dictionary<Type, SqlDbType> TypeToSqlDbType = new Dictionary<Type, SqlDbType>
            {
              // Mapping C# types to Ado.net SqlDbType enumeration
                {typeof (byte), SqlDbType.TinyInt},
                {typeof (sbyte), SqlDbType.TinyInt},
                {typeof (short), SqlDbType.SmallInt},
                {typeof (ushort), SqlDbType.SmallInt},
                {typeof (int), SqlDbType.Int},
                {typeof (uint), SqlDbType.Int},
                {typeof (long), SqlDbType.BigInt},
                {typeof (ulong), SqlDbType.BigInt},
                {typeof (float), SqlDbType.Float},
                {typeof (double), SqlDbType.Float},
                {typeof (decimal), SqlDbType.Decimal},
                {typeof (bool), SqlDbType.Bit},
                {typeof (string), SqlDbType.VarChar},
                {typeof (char), SqlDbType.Char},
                {typeof (Guid), SqlDbType.UniqueIdentifier},
                {typeof (DateTime), SqlDbType.DateTime},
                {typeof (DateTimeOffset), SqlDbType.DateTimeOffset},
                {typeof (byte[]), SqlDbType.VarBinary},
                {typeof (byte?), SqlDbType.TinyInt},
                {typeof (sbyte?), SqlDbType.TinyInt},
                {typeof (short?), SqlDbType.SmallInt},
                {typeof (ushort?), SqlDbType.SmallInt},
                {typeof (int?), SqlDbType.Int},
                {typeof (uint?), SqlDbType.Int},
                {typeof (long?), SqlDbType.BigInt},
                {typeof (ulong?), SqlDbType.BigInt},
                {typeof (float?), SqlDbType.Float},
                {typeof (double?), SqlDbType.Float},
                {typeof (decimal?), SqlDbType.Decimal},
                {typeof (bool?), SqlDbType.Bit},
                {typeof (char?), SqlDbType.Char},
                {typeof (Guid?), SqlDbType.UniqueIdentifier},
                {typeof (DateTime?), SqlDbType.DateTime},
                {typeof (DateTimeOffset?), SqlDbType.DateTimeOffset},
                {typeof (System.Data.Linq.Binary), SqlDbType.Binary},
                {typeof (IEnumerable<>), SqlDbType.Structured},
                {typeof (List<>), SqlDbType.Structured},
                {typeof (DataTable), SqlDbType.Structured},

            };



        }

        /// <summary>
        /// 
        /// </summary>
        public static class Map
    {
        /// <summary>
        /// 
        /// </summary>
        public static Dictionary<Type, DbType> TypeToDbType = new Dictionary<Type, DbType>()
        {
            {typeof (byte), DbType.Byte},
            {typeof (sbyte), DbType.Byte},
            {typeof (short), DbType.Int16},
            {typeof (ushort), DbType.Int16},
            {typeof (int), DbType.Int32},
            {typeof (uint), DbType.Int32},
            {typeof (long), DbType.Int64},
            {typeof (ulong), DbType.Int64},
            {typeof (float), DbType.Single},
            {typeof (double), DbType.Double},
            {typeof (decimal), DbType.Decimal},
            {typeof (bool), DbType.Boolean},
            {typeof (string), DbType.String},
            {typeof (char), DbType.StringFixedLength},
            {typeof (Guid), DbType.Guid},
            {typeof (DateTime), DbType.DateTime},
            {typeof (DateTimeOffset), DbType.DateTimeOffset},
            {typeof (byte[]), DbType.Binary},
            {typeof (byte?), DbType.Byte},
            {typeof (sbyte?), DbType.Byte},
            {typeof (short?), DbType.Int16},
            {typeof (ushort?), DbType.Int16},
            {typeof (int?), DbType.Int32},
            {typeof (uint?), DbType.Int32},
            {typeof (long?), DbType.Int64},
            {typeof (ulong?), DbType.Int64},
            {typeof (float?), DbType.Single},
            {typeof (double?), DbType.Double},
            {typeof (decimal?), DbType.Decimal},
            {typeof (bool?), DbType.Boolean},
            {typeof (char?), DbType.StringFixedLength},
            {typeof (Guid?), DbType.Guid},
            {typeof (DateTime?), DbType.DateTime},
            {typeof (DateTimeOffset?), DbType.DateTimeOffset},
            {typeof (System.Data.Linq.Binary), DbType.Binary}
        };

        /// <summary>
        ///     Parameter Direction for Stored Procedure
        /// </summary>
        public static readonly Dictionary<string, ParameterDirection> DirectionMap =
               new Dictionary<string, ParameterDirection>(StringComparer.InvariantCultureIgnoreCase)
            {
                {ParamDirectionConstants.Input, ParameterDirection.Input},
                {ParamDirectionConstants.Output, ParameterDirection.Output},
                {ParamDirectionConstants.InputOutput, ParameterDirection.InputOutput},
                {ParamDirectionConstants.ReturnValue, ParameterDirection.ReturnValue}
            };
    }
}

支持上述代码运行的类和API如下：

using System;
using System.Collections.Generic;

namespace Dapper
{
    public class DapperParam
    {
        /// <summary>
        ///     Parameter Type Constructor
        /// </summary>
        /// <param name="paramName"></param>
        /// <param name="paramType"></param>
        /// <param name="paramDirection"></param>
        /// <param name="paramValue"></param>
        public DapperParam(string paramName,
                        Type paramType,
                        string paramDirection,
                        object paramValue)
        {
            ParamName = paramName;
            ParamType = paramType;
            ParamDirection = paramDirection;
            ParamValue = paramValue;
        }

        /// <summary>
        ///     Parameter name
        /// </summary>
        public string ParamName { get; set; }

        /// <summary>
        ///     Parameter Type
        /// </summary>
        public Type ParamType { get; set; }

        /// <summary>
        ///     Parameter Direction
        /// </summary>
        public string ParamDirection { get; set; }

        /// <summary>
        ///     Parameter Value
        /// </summary>
        public object ParamValue { get; set; }

    }

    internal static class DataConversionMap
    {
        /// <summary>
        ///     Type conversion, handles null
        /// </summary>
        /// <param name="obj"></param>
        /// <param name="func"></param>
        /// <returns></returns>
        private static object ConvertDbData(object obj, Func<object> func)
        {
            return (!Convert.IsDBNull(obj)) ? func() : null;
        }

        /// <summary>
        ///     Dictionary map to convert to a given DataType. Returns a Func of object,object.
        ///     Internally calls ConvertDbData for Data Type conversion
        /// </summary>
        public static readonly Dictionary<Type, Func<object, object>> Map =
            new Dictionary<Type, Func<object, object>>
            {
                {
                    typeof(Int16),
                    objectValue => ConvertDbData(objectValue, () => Convert.ToInt16(objectValue))
                },

                {
                    typeof(Int32),
                    objectValue => ConvertDbData(objectValue, () => Convert.ToInt32(objectValue))
                },

                {
                    typeof(Int64),
                    objectValue => ConvertDbData(objectValue, () => Convert.ToInt64(objectValue))
                },

                {
                    typeof(Boolean),
                    objectValue => ConvertDbData(objectValue, () => Convert.ToBoolean(objectValue))
                },

                {
                    typeof(string),
                    objectValue => ConvertDbData(objectValue, () => Convert.ToString(objectValue))
                },

                {
                   typeof(DateTime), objectValue =>

                        ConvertDbData(objectValue, () =>
                        {
                            DateTime dateTime = Convert.ToDateTime(objectValue);

                            if (dateTime.TimeOfDay.Equals(TimeSpan.Zero))
                                return dateTime.ToShortDateString();

                            return dateTime.ToString("MM/dd/yyyy HH:mm");
                        })

                },

                {
                    typeof(Byte),
                    objectValue => ConvertDbData(objectValue, () => Convert.ToByte(objectValue))
                },

                {
                    typeof(Double),
                    objectValue => ConvertDbData(objectValue, () => Convert.ToDouble(objectValue))
                },

                {
                    typeof(Decimal),
                    objectValue => ConvertDbData(objectValue, () => Convert.ToDecimal(objectValue))
                },

                {
                    typeof(TimeSpan),
                    objectValue => ConvertDbData(objectValue, () => TimeSpan.Parse(objectValue.ToString()))
                },

                {
                    typeof(Guid),
                    objectValue => ConvertDbData(objectValue, () => new Guid(objectValue.ToString()))
                },

                {
                    typeof(Byte[]),
                    objectValue => ConvertDbData(objectValue, () => (Byte[])(objectValue))
                }
            };
    }
}

常用API

public static class Common
    {
        /// <summary>
        ///  Convert IEnumerable<T> to DataTable
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="collection"></param>
        /// <returns></returns>
        public static DataTable CreateTable<T>(this IEnumerable<T> collection)
        {
            // Fetch the type of List contained in the ParamValue
            var tableType = typeof(T);

            // Create DataTable which will contain data from List<T>
            var dataTable = new DataTable();

            // Fetch the Type fields count
            int columnCount = tableType.GetProperties().Count();

            var columnNameMappingDictionary = new Dictionary<string, string>();

            // Create DataTable Columns using table type field name and their types
            // Traversing through Column Collection
            for (int counter = 0; counter < columnCount; counter++)
            {
                var propertyInfo = tableType.GetProperties()[counter];

                var parameterAttribute = propertyInfo.GetParameterAttribute();

                string columnName = (parameterAttribute != null) ? parameterAttribute.Name : propertyInfo.Name;

                columnNameMappingDictionary.Add(propertyInfo.Name,
                    (parameterAttribute != null) ? parameterAttribute.Name : propertyInfo.Name);

                dataTable.Columns.Add(columnName, tableType.GetProperties()[counter].PropertyType);
            }

            // Return parameter with null value
            if (collection == null)
                return dataTable;

            // Traverse through number of entries / rows in the List
            foreach (var item in collection)
            {
                // Create a new DataRow
                DataRow dataRow = dataTable.NewRow();

                // Traverse through type fields or column names
                for (int counter = 0; counter < columnCount; counter++)
                {
                    // Fetch Column Name
                    string columnName = columnNameMappingDictionary[tableType.GetProperties()[counter].Name];

                    //Fetch Value for each column for each element in the List<T>
                    dataRow[columnName] = item
                        .GetType().GetProperties()[counter]
                        .GetValue(item);
                }
                // Add Row to Table
                dataTable.Rows.Add(dataRow);
            }

            return (dataTable);
        }

        /// <summary>
        /// Convert IEnumerable<T> to DataTable
        /// </summary>
        /// <param name="paramValue"></param>
        /// <returns></returns>
        public static DataTable CreateTable(object paramValue)
        {
            // Fetch the type of List contained in the ParamValue
            Type tableType = paramValue.GetType().GetGenericArguments()[0];

            // Create DataTable which will contain data from List<T>
            var genericDataTable = new DataTable();

            // Fetch the Type fields count
            int fieldCount = tableType.GetProperties().Count();

            // Create DataTable Columns using table type field name and their types
            // Traversing through Column Collection
            for (int counter = 0; counter < fieldCount; counter++)
            {
                genericDataTable.Columns.Add(tableType.GetProperties()[counter].Name,
                    tableType.GetProperties()[counter].PropertyType);
            }

            // Traverse through number of entries / rows in the List
            foreach (var item in (IEnumerable)paramValue)
            {
                // Create a new DataRow
                DataRow dataRow = genericDataTable.NewRow();

                // Traverse through type fields or column names
                for (int counter = 0; counter < fieldCount; counter++)
                {
                    // Fetch Column Name
                    string columnName = tableType.GetProperties()[counter].Name;

                    //Fetch Value for each column for each element in the List<T>
                    dataRow[columnName] = item
                        .GetType().GetProperties()[counter]
                        .GetValue(item);
                }
                // Add Row to Table
                genericDataTable.Rows.Add(dataRow);
            }
            return genericDataTable;
        }

        /// <summary>
        /// Convert DataTable to List<T>
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="dataTable"></param>
        /// <returns></returns>
        public static List<T> ToList<T>(DataTable dataTable) where T : new()
        {
            // Final result List (Converted from DataTable)
            var convertedList = new List<T>();

            // Traverse through Rows in the DataTable
            foreach (DataRow row in dataTable.Rows)
            {
                // Type T of generic list object
                var dataObject = new T();

                // Traverse through Columns in the DataTable
                foreach (DataColumn column in dataTable.Columns)
                {
                    // Fetch column name
                    string fieldName = column.ColumnName;

                    // Fetch type PropertyInfo using reflection
                    var propertyInfo = dataObject.GetType()
                        .GetProperty(fieldName,
                            BindingFlags.IgnoreCase | BindingFlags.Public | BindingFlags.Instance);

                    // For Null PropertyInfo, check whether ViewrColumn attribute is applied
                    propertyInfo = propertyInfo ?? Parameter.GetColumnAttribute(dataObject.GetType(), fieldName);

                    // Set the value for not null property Info
                    // Continue the loop for a null PropertyInfo (needs correction either in type description or DataTable selection)
                    if (propertyInfo == null) continue;

                    // Property value
                    var value = row[column];

                    // New - Work for Nullable Types
                    propertyInfo.SetValue(dataObject,
                        DataConversionMap.Map[propertyInfo.PropertyType](value), null);
                }

                // Add type object to the List
                convertedList.Add(dataObject);
            }

            return (convertedList);
        }
    }

- Mrinal Kamboj

1

我发现Rohit Shetty的回答有帮助，但是仍然遇到了问题，因为它没有提供完整的示例。下面是一个包含更多样本代码的示例。

在SQL中，定义您的用户定义表类型：

CREATE TYPE [dbo].[IntListTableType]
AS TABLE
(
    [Value] INT NOT NULL
);

然后在C#中，你可以这样做：

List<int> employeeIds = GetEmployeeIds();
...
// Create and populate the table type for the stored procedure.
DataTable employeeIdsTable = new DataTable();
employeeIdsTable.Columns.Add("Value", typeof(int));
foreach (var employeeId in employeeIds)
{
    employeeIdsTable.Rows.Add(employeeId);
}

var sproc = "[dbo].[GetEmployeesById]";
var sprocParameters = new DynamicParameters();
sprocParameters.Add("@EmployeeIds", employeeIdsTable.AsTableValuedParameter());

using (var connection = new SqlConnection(databaseConnectionString))
{
    var results = await connection.QueryAsync<IEnumerable<Employee>>(sproc, sprocParameters, commandType: System.Data.CommandType.StoredProcedure, commandTimeout: 60);
    return results;
}

您可以选择在AsTableValuedParameter方法中提供用户定义表类型的名称（例如[dbo].[IntListTableType]），但根据我的情况，我发现即使没有提供它，也能正常工作，所以我不确定什么时候会需要它。

- deadlydog

0

CREATE TYPE [Common].[IDList] AS TABLE([ID] [int] NULL)
GO

using Microsoft.Data.SqlClient;
using Microsoft.Data.SqlClient.Server;
using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using static Dapper.SqlMapper;

namespace Test
{
    public class TableOfIntegersTypeHandler : ITypeHandler
    {
        public object Parse(Type destinationType, object value)
        {
            throw new NotImplementedException();
        }

        public void SetValue(IDbDataParameter parameter, object value)
        {
            var p = (SqlParameter)parameter;
            p.TypeName = "Common.IDList";
            p.SqlDbType = SqlDbType.Structured;
            p.Value = AsSqlDataRecords((IEnumerable)value);
        }

        private static List<SqlDataRecord> AsSqlDataRecords(IEnumerable collection, string columnName = "ID")
        {
            var records = new List<SqlDataRecord>();
            var meta = new SqlMetaData[] { new SqlMetaData(columnName, SqlDbType.Int) };
            foreach (var num in collection)
            {
                var record = new SqlDataRecord(meta);
                if (num is null)
                {
                    record.SetDBNull(0);
                }
                else
                {
                    record.SetInt32(0, (int)num);
                }

                records.Add(record);
            }

            return records;
        }

        private static DataTable AsDataTable(IEnumerable collection, string columnName = "ID")
        {
            var tvp = new DataTable();
            var enumerator = collection.GetEnumerator();
            if (enumerator.MoveNext())
            {
                tvp.Columns.Add(new DataColumn(columnName, enumerator.Current.GetType()));

                do
                {
                    tvp.Rows.Add(enumerator.Current);
                }
                while (enumerator.MoveNext());
            }

            return tvp;
        }
    }
}

using Microsoft.Data.SqlClient;
using System;
using System.Collections.Generic;
using Dapper;

namespace Test
{
    public static class Program
    {
        public static void Main(string[] args)
        {
            var typeHandler = new TableOfIntegersTypeHandler();
            SqlMapper.AddTypeHandler(typeof(int[]), typeHandler);
            SqlMapper.AddTypeHandler(typeof(int?[]), typeHandler);
            SqlMapper.AddTypeHandler(typeof(List<int>), typeHandler);
            SqlMapper.AddTypeHandler(typeof(List<int?>), typeHandler);
            SqlMapper.AddTypeHandler(typeof(IEnumerable<int>), typeHandler);
            SqlMapper.AddTypeHandler(typeof(IEnumerable<int?>), typeHandler);

            try
            {
                using var con = new SqlConnection(...);
                con.Open();

                var ps = new DynamicParameters();
                ps.Add("@Ids", new List<int>(new[] { 1, 2, 3, 4, 5 }));

                var ids = con.Query<int>("select * from @Ids", ps);

                ids = con.Query<int>("select * from @Ids", new { Ids = new[] { 1, 2 } });
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex);
            }

            Console.WriteLine("Hello, World!");
            Console.ReadLine();
        }
    }
}

- LyubomirL

0

我到达这里是因为我遇到了同样的问题 - 想要调用一个存储过程，将TABLE TYPE参数传递进去以实现批量插入

解决方案使用了Dapper.ParameterExtensions NuGet。

对我来说，步骤如下：

创建用户定义的表类型

CREATE TYPE [core].[DataErrorType] AS TABLE (
 [Id] [int] NULL,
 [SubmissionResponseErrorId] [int] NOT NULL,
 [ErrorCode] [nvarchar](30) NOT NULL,
 [Severity] [int] NULL,
 [RecordReferenceId] [int] NOT NULL)

创建存储过程

CREATE OR ALTER PROCEDURE [core].[spBulkInsertDataErrors]
 (
     @dataErrors [core].[DataErrorType] READONLY
 )
 AS
 INSERT INTO [core].[DataError] ([SubmissionResponseErrorId], [ErrorCode], [Severity], [RecordReferenceId])
 SELECT [SubmissionResponseErrorId], [ErrorCode], [Severity], [RecordReferenceId]
 FROM @dataErrors

在 C# 代码中，使用 DynamicParameters 的 AddTable() 扩展方法。该方法可以在 DapperParameters NuGet 包中找到：Dapper.ParameterExtensions

var parameters = new DynamicParameters();
parameters.AddTable("@dataErrors", "core.DataErrorType", dataErrors);
await sql.ExecuteAsync("[core].[spBulkInsertDataErrors]", parameters, transaction, commandType: CommandType.StoredProcedure);

- gpanagopoulos

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Rohit Shetty · Accepted Answer

首先在数据库中创建一个用户定义的表类型

CREATE TYPE udtt_PersonList AS TABLE 
(
    ...
)
GO

在你的代码中

var dynamicParameters = new DynamicParameters();
dynamicParameters.Add("@PersonList", PersonList.AsTableValuedParameter("[dbo].[udtt_PersonList]"));
dynamicParameters.Add("TestOutput", 0, Dbtype.Int32, ParameterDirection.Output);