How to set up ASP.NET SQL Datasource to accept TVP

Question

In the codebehind you would add the TVP as a SqlDbType.Structured for a stored procedure But this doesn't exist in an ASP.NET SqlDataSource control.

I have stored my Datatables in session variables (don't worry they are small!) and I need to pass those as parameters to the SqlDataSource (which has a number of databound objects)

I pointed the Datasource to the session variable but it fails on the conversion to the table type.

EDIT: Let's say I take the Session variable out of the equation (because, really, it's completely tangential)

There must be a way I can attach a DBType.Structured to a SQLDataSource. My Listviews are appropriately databound but the store procedures to which they are attached must take TVP's

I cannot believe that there would be no way to send a TVP paramater for a SQLDataSource? What are my alternatives?

EDIT2: I've been looking into creating a custom parameter for the SqlDataSource but it still seems to me like its "eval" method won't be happy with the structured data type

EDIT3: It's beginning to appear that my only option is to do all the work in codebehind for my databound controls. I added a bounty in case anybody else has an elegant solution.

EDIT4: Is there, perhaps, a way that I can pass the table as an object to a stored procedure, then have SQL Server convert it to the TVP?

Answer 1

I know you've edited to say session is of no importance, however I was able to get this working using a SessionParameter. I have a feeling it would also work with a ControlParameter.

So you have a user-defined table type:

CREATE TYPE TVPType AS TABLE(
    Col1 int,
    Col2 int)
GO

and a stored procedure that uses it:

CREATE PROC TVPProc(@TVP AS TVPType READONLY) AS
    SELECT * FROM @TVP

then a GridView bound to a SqlDataSource that selects from your sproc, passing a SessionParameter:

<asp:GridView ID="GridView1" runat="server" DataSourceID="SqlDataSource1" />
<asp:SqlDataSource ID="SqlDataSource1" SelectCommand="TVPProc" runat="server" SelectCommandType="StoredProcedure" ConnectionString="Server=(local)\sqlexpress;Database=Graph;Integrated Security=True">
    <SelectParameters>
        <asp:SessionParameter SessionField="MyDataTable" Name="TVP" />
    </SelectParameters>
</asp:SqlDataSource>

and finally a little something to put a DataTable into the session, although you say you already have it there anyway:

(VB)

<script runat="server">
    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs)
        Dim MyDataTable As New System.Data.DataTable

        MyDataTable.Columns.AddRange({
            New System.Data.DataColumn("Col1", GetType(integer)),
            New System.Data.DataColumn("Col2", GetType(integer))})

        MyDataTable.Rows.Add(22, 33)
        MyDataTable.Rows.Add(44, 55)
        MyDataTable.Rows.Add(66, 77)

        Session("MyDataTable") = MyDataTable
    End Sub
</script>

(C#)

<script runat="server">
    protected void Page_Load(object sender, EventArgs e)
    {
        System.Data.DataTable MyDataTable = new System.Data.DataTable();
        MyDataTable.Columns.AddRange(
            new System.Data.DataColumn[] {
                new System.Data.DataColumn("Col1", typeof (int)),
                new System.Data.DataColumn("Col2", typeof (int))});

        MyDataTable.Rows.Add(22, 33);
        MyDataTable.Rows.Add(44, 55);
        MyDataTable.Rows.Add(66, 77);

        Session["MyDataTable"] = MyDataTable;
    }
</script>

which results in a finely bound GridView:

and the following generated query from Profiler:

declare @p1 dbo.TVPType
insert into @p1 values(22,33)
insert into @p1 values(44,55)
insert into @p1 values(66,77)

exec TVPProc @TVP=@p1

This is .NET 4, MSSQL Express 2010, but should work lower as well.