mardi 13 décembre 2016

Is there a way to pass a TVP to dapper on .Net Core right now?

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