Passing a user defined table type to SQL function in Ormlite

Question

I've to pass a table to a SQL function (till now I've passed to stored procedures and everything was fine)

Consider the following snippet

        var dataTable = new DataTable();
        dataTable.Columns.Add(new DataColumn("ID", typeof(Guid)));
        foreach (var o in orders)
        {
            var r = dataTable.NewRow();
            r["ID"] = o;
            dataTable.Rows.Add(r);
        }
        var res = db.Exec(cmd =>
        {
            cmd.CommandType = CommandType.Text;
            cmd.Parameters.Add(new SqlParameter("INPUT", dataTable));
            cmd.CommandText = "SELECT * FROM FUNCTION";
            return cmd.ConvertToList<MyObj>();
        });

I'm not aware if parameters are considered when specifing CommandType as Text, I've tried on SQLServer and it works... What am I doing wrong? is this a limitation of ServiceStack's OrmLite? Thanks

Answer 1

When manually populating cmd as in your example you're using ADO.NET (i.e. not OrmLite), so you need to find out the correct way to call a SQL Server function from ADO.NET.

It seems you can only pass a datatable to a UDF from SQL Server 2008+ and requires that your TableType parameter is READONLY.