I am using .net core and dapper, the first one doesn't have DataTables and the second one use them for TVP...
I was trying to convert a List<T>
to a List<SqlDataRecord>
, create a SqlParameter with this list and then convert it to a DynamicParameter but I had no luck:
public static SqlParameter toTVP<T>(this IList<T> enumerable, string name)
{
List<SqlDataRecord> records = new List<SqlDataRecord>();
// filter allowed types
var properties = typeof(T).GetProperties().Where(p => Mapper.TypeToSQLMap.ContainsKey(p.PropertyType));
var definitions = properties.Select(p => Mapper.TypeToMetaData(p.Name,p.PropertyType)).ToArray();
foreach(var item in enumerable)
{
var values = properties.Select(p => p.GetValue(item, null)).ToArray();
var schema = new SqlDataRecord(definitions);
schema.SetValues(values);
records.Add(schema);
}
SqlParameter result = new SqlParameter(name, SqlDbType.Structured);
result.Direction = ParameterDirection.Input;
result.TypeName = $"{name}Type";
result.Value = records;
return result;
}
and then:
var structured = MyList.toTVP("Test");
var p = new DynamicParameters(new { });
p.Add(structured.ParameterName,structured.Value);
var result = con.Query(query, p);
but sadly i got an: The member of type Microsoft.SqlServer.Server.SqlDataRecord cannot be used as a parameter value
Do someone knows a way to do it or a way to improve this code to make it work?
Aucun commentaire:
Enregistrer un commentaire