Dapper how to pass object with list of objects into stored procedure

Question

I have a problem with executing an existing stored procedure using Dapper. The stored procedure takes some parameters and list of user-defined type object. I mimicked the required parameters in C#, but some conversion doesn't go well, that causes an exception(listed below) and I can't figure out how to solve it.

SQL Server stored procedure:

ALTER PROCEDURE [dbo].[MySp]
    @Name VARCHAR(60),
    @Size BIGINT,
    @Photos MyPhoto READONLY
AS
BEGIN
    -- ... logic

where MyPhoto is a user-defined table type with:

Name VARCHAR(25),
Model VARCHAR(25)

On the C# side, I have:

public class MyClassForSP
{
    public string Name { get; set; }
    public Int64 Size { get; set; }
    public List<Photos> Photos { get; set; }
}

public class Photos
{
    public string Name { get; set }
    public string Model { get; set; }
}

So far everything looks logical for me... so I tried to execute a SQL request:

public void ExecuteSp(MyClassForSP classForSp)
{
    using IDbConnection connection = new SqlConnection(_settings.ConnectionString);

    var result = await connection.QuerySingleAsync<MyResult>("dbo.MySp", classForSp, 
    commandType: CommandType.StoredProcedure);
}

As result I get a Dapper error:

System.NotSupportedException: 'The member of type MyNameSpace.Photos cannot be used as a parameter value'

Answer 1

You need to use Table-Valued Parameter by providing the DataTable.

DataTable dt = new DataTable();
dt.Columns.Add("Name");
dt.Columns.Add("Model");

foreach (var photo in classForSp.Photos) 
{
    dt.Rows.Add(photo.Name, photo.Model);
}

using IDbConnection connection = new SqlConnection(_settings.ConnectionString);
var result = await connection.QuerySingleAsync<MyResult>("dbo.MySp", 
    new 
    { 
        Name = classForSp.Name, 
        Size = classForSp.Size, 
        Photos = dt.AsTableValuedParameter("MyPhoto") 
    }, 
    commandType: CommandType.StoredProcedure);

If you are looking for a generic way to convert the list into DataTable for Table-Valued parameter, you can implement an extension method as below:

public static class IListExtensions
{
    public static DataTable ToDataTable<T>(this IList<T> list)
    {
        PropertyDescriptorCollection properties = TypeDescriptor.GetProperties(typeof(T));
        DataTable dataTable = new DataTable();

        foreach (PropertyDescriptor property in properties)
            dataTable.Columns.Add(property.Name, Nullable.GetUnderlyingType(property.PropertyType) ?? property.PropertyType);

        foreach (T item in list)
        {
            DataRow row = dataTable.NewRow();
            foreach (PropertyDescriptor property in properties)
                row[property.Name] = property.GetValue(item) ?? DBNull.Value;

            dataTable.Rows.Add(row);
        }

        return dataTable;
    }
}

Caller:

DataTable dt = classForSp.Photos.ToDataTable();