从接受用户定义表类型列表的 dapper 调用存储过程
本文关键字：列表 dapper 调用 存储过程 类型 用户 定义 | 更新日期: 2023-09-27 18:27:58
我有一个存储过程InsertCars它接受用户定义的表类型列表CarType .
CREATE TYPE dbo.CarType
AS TABLE
(
CARID int null,
CARNAME varchar(800) not null,
);
CREATE PROCEDURE dbo.InsertCars
@Cars AS CarType READONLY
AS
-- RETURN COUNT OF INSERTED ROWS
END
我需要从 Dapper 调用此存储过程。我用谷歌搜索了一下，找到了一些解决方案。
var param = new DynamicParameters(new{CARID= 66, CARNAME= "Volvo"});
var result = con.Query("InsertCars", param, commandType: CommandType.StoredProcedure);
但是我得到一个错误：
过程或函数 InsertCars 指定的参数过多
存储过程InsertCars还返回插入的行数;我需要获取此值。
问题的根源在哪里？
我的问题是我在通用列表List<Car> Cars中有汽车，我想通过此列表来存储程序。它存在优雅的方式怎么做？
public class Car
{
public CarId { get; set; }
public CarName { get; set; }
}
谢谢你的帮助
编辑
我找到了解决方案
Dapper 是否支持 SQL 2008 表值参数？
或
Dapper 是否支持 SQL 2008 表值参数 2？
所以我尝试制作自己的愚蠢的助手类
class CarDynamicParam : Dapper.SqlMapper.IDynamicParameters
{
private Car car;
public CarDynamicParam(Car car)
{
this.car = car;
}
public void AddParameters(IDbCommand command, SqlMapper.Identity identity)
{
var sqlCommand = (SqlCommand)command;
sqlCommand.CommandType = CommandType.StoredProcedure;
var carList = new List<Microsoft.SqlServer.Server.SqlDataRecord>();
Microsoft.SqlServer.Server.SqlMetaData[] tvpDefinition =
{
new Microsoft.SqlServer.Server.SqlMetaData("CARID", SqlDbType.Int),
new Microsoft.SqlServer.Server.SqlMetaData("CARNAME", SqlDbType.NVarChar, 100),
};
var rec = new Microsoft.SqlServer.Server.SqlDataRecord(tvpDefinition);
rec.SetInt32(0, car.CarId);
rec.SetString(1, car.CarName);
carList.Add(rec);
var p = sqlCommand.Parameters.Add("Cars", SqlDbType.Structured);
p.Direction = ParameterDirection.Input;
p.TypeName = "CarType";
p.Value = carList;
}
}
用
var result = con.Query("InsertCars", new CarDynamicParam(car), commandType: CommandType.StoredProcedure);
我得到例外
使用多映射 API 时，请确保在具有 Id 以外的键时设置 splitOn 参数。
堆栈跟踪：
at Dapper.SqlMapper.GetDynamicDeserializer(IDataRecord reader, Int32 startBound, Int32 length, Boolean returnNullIfFirstMissing) in c:'Dev'Dapper'Dapper'SqlMapper.cs:line 1308
at Dapper.SqlMapper.GetDeserializer(Type type, IDataReader reader, Int32 startBound, Int32 length, Boolean returnNullIfFirstMissing) in c:'Dev'Dapper'Dapper'SqlMapper.cs:line 1141
at Dapper.SqlMapper.<QueryInternal>d__d`1.MoveNext() in c:'Dev'Dapper'Dapper'SqlMapper.cs:line 819
at System.Collections.Generic.List`1..ctor(IEnumerable`1 collection)
at System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source)
at Dapper.SqlMapper.Query[T](IDbConnection cnn, String sql, Object param, IDbTransaction transaction, Boolean buffered, Nullable`1 commandTimeout, Nullable`1 commandType) in c:'Dev'Dapper'Dapper'SqlMapper.cs:line 770
at Dapper.SqlMapper.Query(IDbConnection cnn, String sql, Object param, IDbTransaction transaction, Boolean buffered, Nullable`1 commandTimeout, Nullable`1 commandType) in c:'Dev'Dapper'Dapper'SqlMapper.cs:line 715
怎么了？
固定：
呼叫con.Execute而不是con.Query
我的问题是我在通用列表列表汽车中有汽车，我想将此列表传递给存储过程。它存在优雅的方式怎么做？
您需要将泛型列表 Car 转换为数据表，然后将其传递给存储过程。 需要注意的一点是，字段的顺序必须与数据库中用户定义的表类型中定义的顺序相同。否则，数据将无法正确保存。并且它还必须具有相同数量的列。
我使用此方法将列表转换为数据表。你可以像你的List.ToDataTable((一样称呼它
public static DataTable ToDataTable<T>(this List<T> iList)
{
DataTable dataTable = new DataTable();
PropertyDescriptorCollection propertyDescriptorCollection =
TypeDescriptor.GetProperties(typeof(T));
for (int i = 0; i < propertyDescriptorCollection.Count; i++)
{
PropertyDescriptor propertyDescriptor = propertyDescriptorCollection[i];
Type type = propertyDescriptor.PropertyType;
if (type.IsGenericType && type.GetGenericTypeDefinition() == typeof(Nullable<>))
type = Nullable.GetUnderlyingType(type);
dataTable.Columns.Add(propertyDescriptor.Name, type);
}
object[] values = new object[propertyDescriptorCollection.Count];
foreach (T iListItem in iList)
{
for (int i = 0; i < values.Length; i++)
{
values[i] = propertyDescriptorCollection[i].GetValue(iListItem);
}
dataTable.Rows.Add(values);
}
return dataTable;
}
我知道这有点旧了，但我想无论如何我都会发布这个，因为我试图让它更容易一点。 我希望我已经使用我创建的 NuGet 包来做到这一点，该包将允许以下代码：
public class CarType
{
public int CARID { get; set; }
public string CARNAME{ get; set; }
}
var cars = new List<CarType>{new CarType { CARID = 1, CARNAME = "Volvo"}};
var parameters = new DynamicParameters();
parameters.AddTable("@Cars", "CarType", cars)
var result = con.Query("InsertCars", parameters, commandType: CommandType.StoredProcedure);
NuGet 包：https://www.nuget.org/packages/Dapper.ParameterExtensions/0.2.0仍处于早期阶段，因此可能无法处理所有内容！
请阅读自述文件，并随时在 GitHub 上做出贡献：https://github.com/RasicN/Dapper-Parameters
您也可以使用查询方法以及执行方法。参数的类型必须是 DataTable，并且可以作为匿名参数集合的一部分提供。如果你的需求很简单，并且不需要构建 DataTable 的通用解决方案，那么一个小的非泛型函数是最简单的：
private DataTable CreateDataTable( IEnumerable<AlertChannelContainer> alertData )
{
DataTable table = new DataTable();
table.Columns.Add( "ChannelOrdinal", typeof( int ) );
table.Columns.Add( "Value", typeof( decimal ) );
foreach ( var alertChannel in alertData )
{
var dataRow = table.NewRow();
dataRow["ChannelOrdinal"] = alertChannel.ChannelOrdinal;
dataRow["Value"] = alertChannel.Value;
table.Rows.Add( dataRow );
}
return table;
}
然后就这样称呼它：
var result = await connection.QuerySingleAsync<AlertMetadata>(
"[dbo].[InsertAlert]",
new
{
eventId,
deviceId,
timestamp,
alertThresholds = JsonConvert.SerializeObject( rules ),
data = CreateDataTable( alertData )
},
commandType: CommandType.StoredProcedure );
使用反射将对象属性映射到数据表列的成本很高。进一步采用 Ehsan 的解决方案，在性能是一个问题的地方，您可以缓存类型属性映射。正如 Ehsan 还指出的那样，类中的顺序必须与数据库中的顺序相同，并且必须有相等数量的列。这可以通过根据类型定义对列重新排序来克服。
public static class DataTableExtensions
{
private static readonly EntityPropertyTypeMap PropertyTypeMap = new EntityPropertyTypeMap();
public static DataTable ToDataTable<T>(this ICollection<T> values)
{
if (values is null)
{
throw new ArgumentNullException(nameof(values));
}
var table = new DataTable();
var properties = PropertyTypeMap.GetPropertiesForType<T>().Properties;
foreach (var prop in properties)
{
table.Columns.Add(prop.Name, Nullable.GetUnderlyingType(prop.PropertyType) ?? prop.PropertyType);
}
foreach (var value in values)
{
var propertyCount = properties.Count();
var propertyValues = new object[propertyCount];
if (value != null)
{
for (var i = 0; i < propertyCount; i++)
{
propertyValues[i] = properties[i].GetValue(value);
}
}
table.Rows.Add(propertyValues);
}
return table;
}
}
public static class DapperExtensions
{
private static readonly SqlSchemaInfo SqlSchemaInfo = new SqlSchemaInfo();
public static DataTable ConvertCollectionToUserDefinedTypeDataTable<T>(this SqlConnection connection, ICollection<T> values, string dataTableType = null)
{
if (dataTableType == null)
{
dataTableType = typeof(T).Name;
}
var data = values.ToDataTable();
data.TableName = dataTableType;
var typeColumns = SqlSchemaInfo.GetUserDefinedTypeColumns(connection, dataTableType);
data.SetColumnsOrder(typeColumns);
return data;
}
public static DynamicParameters AddTableValuedParameter(this DynamicParameters source, string parameterName, DataTable dataTable, string dataTableType = null)
{
if (dataTableType == null)
{
dataTableType = dataTable.TableName;
}
if (dataTableType == null)
{
throw new NullReferenceException(nameof(dataTableType));
}
source.Add(parameterName, dataTable.AsTableValuedParameter(dataTableType));
return source;
}
private static void SetColumnsOrder(this DataTable table, params string[] columnNames)
{
int columnIndex = 0;
foreach (var columnName in columnNames)
{
table.Columns[columnName].SetOrdinal(columnIndex);
columnIndex++;
}
}
}
class EntityPropertyTypeMap
{
private readonly ConcurrentDictionary<Type, TypePropertyInfo> _mappings;
public EntityPropertyTypeMap()
{
_mappings = new ConcurrentDictionary<Type, TypePropertyInfo>();
}
public TypePropertyInfo GetPropertiesForType<T>()
{
var type = typeof(T);
return GetPropertiesForType(type);
}
private TypePropertyInfo GetPropertiesForType(Type type)
{
return _mappings.GetOrAdd(type, (key) => new TypePropertyInfo(type));
}
}
class TypePropertyInfo
{
private readonly Lazy<PropertyInfo[]> _properties;
public PropertyInfo[] Properties => _properties.Value;
public TypePropertyInfo(Type objectType)
{
_properties = new Lazy<PropertyInfo[]>(() => CreateMap(objectType), true);
}
private PropertyInfo[] CreateMap(Type objectType)
{
var typeProperties = objectType
.GetProperties(BindingFlags.DeclaredOnly |
BindingFlags.Public |
BindingFlags.Instance)
.ToArray();
return typeProperties.Where(property => !IgnoreProperty(property)).ToArray();
}
private static bool IgnoreProperty(PropertyInfo property)
{
return property.SetMethod == null || property.GetMethod.IsPrivate || HasAttributeOfType<IgnorePropertyAttribute>(property);
}
private static bool HasAttributeOfType<T>(MemberInfo propInfo)
{
return propInfo.GetCustomAttributes().Any(a => a is T);
}
}
public class SqlSchemaInfo
{
private readonly ConcurrentDictionary<string, string[]> _udtColumns = new ConcurrentDictionary<string, string[]>();
public string[] GetUserDefinedTypeColumns(SqlConnection connection, string dataTableType)
{
return _udtColumns.GetOrAdd(dataTableType, (x) =>
connection.Query<string>($@"
SELECT name FROM
(
SELECT column_id, name
FROM sys.columns
WHERE object_id IN (
SELECT type_table_object_id
FROM sys.table_types
WHERE name = '{dataTableType}'
)
) Result
ORDER BY column_id").ToArray());
}
}
[AttributeUsage(AttributeTargets.Property)]
public sealed class IgnorePropertyAttribute : Attribute
{
}
除了上面的Ehsan和Pawan尼泊尔答案之外，我想出了一个适用于.NET Core和.NET的解决方案。以下是执行此操作的步骤。
1.创建扩展方法以将模型转换为数据表(感谢Ehsan提供上面的扩展方法(。
public static DataTable ToDataTable<T>(this List<T> iList)
{
DataTable dataTable = new DataTable();
PropertyDescriptorCollection propertyDescriptorCollection =
TypeDescriptor.GetProperties(typeof(T));
for (int i = 0; i < propertyDescriptorCollection.Count; i++)
{
PropertyDescriptor propertyDescriptor = propertyDescriptorCollection[i];
Type type = propertyDescriptor.PropertyType;
if (type.IsGenericType && type.GetGenericTypeDefinition() == typeof(Nullable<>))
type = Nullable.GetUnderlyingType(type);
dataTable.Columns.Add(propertyDescriptor.Name, type);
}
object[] values = new object[propertyDescriptorCollection.Count];
foreach (T iListItem in iList)
{
for (int i = 0; i < values.Length; i++)
{
values[i] = propertyDescriptorCollection[i].GetValue(iListItem);
}
dataTable.Rows.Add(values);
}
return dataTable;
}
2.将模型转换为数据表。
var cars = new List<CarType>{new CarType { CARID = 1, CARNAME = "Volvo"}};
DataTable dt = cars.ToDataTable();
3.最后添加UDT作为动态参数并将其传递给查询
var parameters = new DynamicParameters();
parameters.Add("@paramFromStoredProcedure",
dt.AsTableValuedParameter("YourUDTNameInDataBase"));
//Pass it to query
var result = con.Query<dynamic>("InsertCars", parameters , commandType: CommandType.StoredProcedure);
另一种解决方案是这样称呼它
var param = new DynamicParameters(new{CARID= 66, CARNAME= "Volvo"});
var result = con.Query<dynamic>("InsertCars", param);
删除：new CarDynamicParam(car(，命令类型：CommandType.StorageProcedure
直接使用表类型的参数，它将起作用。
如果你可以使用Datatable(.net core不支持它(，那么它很容易。
创建数据表 -> 添加与表类型匹配的必需列 -> 添加所需行。最后就用这样的dapper来称呼它。
var result = con.Query<dynamic>("InsertCars", new{paramFromStoredProcedure=yourDataTableInstance}, commandType: CommandType.StoredProcedure);
添加到上面的 JCisar 结果中，您可以创建一个通用函数，该函数既可用于单个参数，也可用于此处。您可以将返回类型设置为预期结果。
要调用它，你可以做
var cars = new List<CarType>{new CarType { CARID = 1, CARNAME = "Volvo"}};
var parameters = new DynamicParameters();
parameters.AddTable("@Cars", "CarType", cars)
调用函数
ExecuteStordProcedureDynamic("ProcedureName", parameters)
public IEnumerable<T> ExecuteStordProcedureDynamic<T>(String ProcedureName, DynamicParameters Param) where T : class
{
using (SqlConnection conn = new SqlConnection(ConnectionString))
{
conn.Open();
var result= conn.Query<T>(ProcedureName, Param, commandType: CommandType.StoredProcedure);
return result;
}
}