Pass table parameter to stored procedure with Simple.Data

Question

I want to pass a list of Ids to a stored procedure using Simple.Data.

My stored procedure is...

CREATE TYPE IntValue AS TABLE (Value INT)
GO

CREATE PROCEDURE testTableSproc
    @items IntValue READONLY
AS
SELECT * FROM @items

So far I've tried...

public class IntValue
{
    public int Value { get; set; }
}

var db = Database.OpenNamedConnection("MyConnection")
var array = new int[] {1, 2, 3};
List<IntValue> results = db.testTableSproc(items: array);

also

List<IntValue> results = db.testTableSproc(
    items: array.Select(x => new IntValue{ Value = x}).ToArray());

Any solutions or confirmation that this is not supported?

Answer 1

Figured it out - Simple.Data accepts DataTable as a type.

        var db = ConnectionFactory.GetConnection();

        var tbl = new DataTable("items");
        tbl.Columns.Add("Value");

        var row1 = tbl.NewRow();
        row1["Value"] = 1;
        tbl.Rows.Add(row1);
        var row2 = tbl.NewRow();
        row2["Value"] = 2;
        tbl.Rows.Add(row2);
        var row3 = tbl.NewRow();
        row3["Value"] = 3;
        tbl.Rows.Add(row3);

        List<IntValue> results = db.testTableSproc(items: tbl);

The syntax for working with DataTables is quite clunky but it can be refactored out of sight.