Call stored procedure from dapper which accept list of user defined table type
Solution 1
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 ?
You need to convert your generic list Car to a datatable and 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 type in database. Otherwise data will not save properly. And it must have same number of columns as well.
I use this method to convert List to DataTable. You can call it like 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;
}
Solution 2
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:
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.0 Still in its early stages so may not work with everything!
Please read the README and feel free to contribute on GitHub: https://github.com/RasicN/Dapper-Parameters
Solution 3
You can use Query methods, too, as well as Execute. The parameter has to be of type DataTable and it can be provided as a part of an anonymous parameter collection. And if your need is simple and you don't need a generic solution to building the DataTable, a small non-generic function is the easiest:
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;
}
Then just call it like this:
var result = await connection.QuerySingleAsync<AlertMetadata>(
"[dbo].[InsertAlert]",
new
{
eventId,
deviceId,
timestamp,
alertThresholds = JsonConvert.SerializeObject( rules ),
data = CreateDataTable( alertData )
},
commandType: CommandType.StoredProcedure );
Solution 4
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.
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
{
}
imodin
Updated on June 03, 2021Comments
-
imodin almost 3 years
I have a stored procedure
InsertCarswhich accepts list of user defined table typeCarType.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 ENDI need call this stored procedure from Dapper. I googled it and found some solutions.
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
Also stored procedure
InsertCarsreturns the count of inserted rows; I need get this value.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 ?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.
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 715What is wrong?
FIXED:
Call
con.Executeinsteadcon.Query