C# 从接受用户定义表类型列表的 dapper 调用存储过程
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/18269886/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Call stored procedure from dapper which accept list of user defined table type
提问by imodin
I have a stored procedure InsertCarswhich accepts list of user defined table type CarType.
我有一个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
I need call this stored procedure from Dapper. I googled it and found some solutions.
我需要从 Dapper 调用这个存储过程。我用谷歌搜索并找到了一些解决方案。
var param = new DynamicParameters(new{CARID= 66, CARNAME= "Volvo"});
var result = con.Query("InsertCars", param, commandType: CommandType.StoredProcedure);
But I get an error:
但我收到一个错误：
Procedure or function InsertCars has too many arguments specified
过程或函数 InsertCars 指定的参数过多
Also stored procedure InsertCarsreturns the count of inserted rows; I need get this value.
存储过程还InsertCars返回插入的行数；我需要得到这个值。
Where is the root of problem?
问题的根源在哪里？
My problem is also that I have cars in generic list List<Car> Carsand I want pass this list to store procedure. It exist elegant way how to do it ?
我的问题也是我在通用列表中有汽车List<Car> Cars，我想将此列表传递给存储过程。它存在优雅的方式怎么办呢？
public class Car
{
public CarId { get; set; }
public CarName { get; set; }
}
Thank you for help
谢谢你的帮助
EDITED
已编辑
I found solutions
我找到了解决方案
Does Dapper support SQL 2008 Table-Valued Parameters?
or
或者
Does Dapper support SQL 2008 Table-Valued Parameters 2?
So I try make own stupid helper class
所以我尝试制作自己的愚蠢助手类
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;
}
}
Use
用
var result = con.Query("InsertCars", new CarDynamicParam(car), commandType: CommandType.StoredProcedure);
I get exception
我得到例外
When using the multi-mapping APIs ensure you set the splitOn param if you have keys other than Id.
使用多映射 API 时，如果您有 Id 以外的键，请确保设置 splitOn 参数。
StackTrace:
堆栈跟踪：
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
What is wrong?
怎么了？
FIXED:
固定的：
Call con.Executeinstead con.Query
con.Execute改为调用con.Query
采纳答案by Ehsan
My problem is also that I have cars in generic list List Cars and I want pass this list to stored procedure. It exist elegant way how to do it ?
我的问题也是我在通用列表 List Cars 中有汽车，我想将此列表传递给存储过程。它存在优雅的方式怎么办呢？
You need to convert your generic list Car to a datatableand then pass it to storedprocedure. A point to note is that the order of your fields must be same as defined in the user defined table typein database. Otherwise data will not save properly. And it must have same number of columnsas well.
您需要将通用列表 Car 转换为数据表，然后将其传递给存储过程。需要注意的一点是，您的字段顺序必须与数据库中用户定义的表类型中定义的相同。否则数据将无法正确保存。并且它也必须具有相同的列数。
I use this method to convert List to DataTable. You can call it like yourList.ToDataTable()
我使用这种方法将 List 转换为 DataTable。你可以像 yourList.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;
}
回答by pawan nepal
The other solution would be to call it like this
另一种解决方案是这样称呼它
var param = new DynamicParameters(new{CARID= 66, CARNAME= "Volvo"});
var result = con.Query<dynamic>("InsertCars", param);
Remove : new CarDynamicParam(car), commandType: CommandType.StoredProcedure
删除：新的 CarDynamicParam(car)，commandType：CommandType.StoredProcedure
Use the parameter of table type directly, it will work.
直接使用表格类型的参数，它会起作用。
If you can use Datatable(.net core does not support it), then its very easy.
如果你可以使用Datatable（.net core不支持），那么就很简单了。
Create DataTable -> Add required columns to match with your table type -> Add required rows. Finally just call it using dapper like this.
创建 DataTable -> 添加所需的列以匹配您的表类型 -> 添加所需的行。最后只需像这样使用 dapper 调用它。
var result = con.Query<dynamic>("InsertCars", new{paramFromStoredProcedure=yourDataTableInstance}, commandType: CommandType.StoredProcedure);
回答by JCisar
I know this is a little old, but I thought I would post on this anyway since I sought out to make this a little easier. I hope I have done so with a NuGet package I create that will allow for code like:
我知道这有点旧，但我想无论如何我都会发布这个，因为我试图让这更容易一些。我希望我已经使用我创建的 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 package: https://www.nuget.org/packages/Dapper.ParameterExtensions/0.2.0Still in its early stages so may not work with everything!
NuGet 包：https: //www.nuget.org/packages/Dapper.ParameterExtensions/0.2.0仍处于早期阶段，因此可能无法处理所有内容！
Please read the README and feel free to contribute on GitHub: https://github.com/RasicN/Dapper-Parameters
请阅读自述文件并随时在 GitHub 上做出贡献：https: //github.com/RasicN/Dapper-Parameters
回答by Jamie Pearcey
Using reflection to map object properties to datatable columns is expensive. Taking Ehsan's solution further, where performance is a concern you can cache the type property mappings. As Ehsan also pointed out, the order in the class must be the same as in the database and there must be an equal number of columns. This can be overcome by reordering the columns according to the type definition.
使用反射将对象属性映射到数据表列的成本很高。进一步考虑 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
{
}
回答by M Hanif
Adding to JCisar result above, you can create a generic function which can be used both for single parameters and list here it is. You can set return type to your expected result.
添加到上面的 JCisar 结果，您可以创建一个通用函数，该函数既可用于单个参数，也可在此处列出。您可以将返回类型设置为您的预期结果。
to call it you can do
称之为你可以做
var cars = new List<CarType>{new CarType { CARID = 1, CARNAME = "Volvo"}};
var parameters = new DynamicParameters();
parameters.AddTable("@Cars", "CarType", cars)
//Call Function
//调用函数
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;
}
}
