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?

Aquiline

There are a number of reasons why very few people still use those controls, and why people don't store data tables in session. It might be time to move on to the next step in your developmental education. – Revolutionize

I understand the reasons against storing datatables in sessions; this is a controlled environment where my use is appropriate. As for your claim that "very few people still use those controls" I have never heard that. Are you suggesting there's an inherent problem with using the SqlDataSource control? Considering it works quite well in almost all situations for which it was designed I fail to see your point against it. On the other hand, I'm open to your suggestions for how to best bind multiple controls to data as elegantly as the SqlDataSource – Aquiline

The SqlDataSource predates table valued parameters by a significant number of years, I wouldn't be surprised that the capability doesn't exist and hasn't been added. – Petree

Understood; and I was aware of that. I was just hoping there would have been some sort of workable method considering the SDS can accept objects. – Aquiline

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.

Butterflies

This works! So it appears that the issue is that the datasource by default wants a datatype for parameters. If it is removed and deliberately blanked then the sds functions work properly. Thank you I have reactivated the bounty and marked yours as the answer – Aquiline

As an important note: This only seems to work with the session field option and with no type specified. Furthermore, the session fields need to be set to a datatable of the correct dimensions on form load. In order to account for this (in cases where session fields are not appropriate) I temporarily set a session field, rebind the controls, then remove the session field – Aquiline

This strikes me as a very convoluted way to avoid implementing a custom Parameter. Pay special attention to the Microsoft StaticParameter example, which can probably do the same thing as a SessionParameter. – Siderosis

Having tested this myself, a simple Samples.AspNet.StaticParameter has the same behavior as the SessionParameter demonstrated in this solution. Switching to a StaticParameter consists of replacing Session["MyDataTable"] = MyDataTable; with SqlDataSource1.SelectParameters.Add(new StaticParameter("TVP", MyDataTable)); (and then removing <asp:SessionParameter ... /> from the markup). The rest of the solution (i.e., constructing the DataTable for the parameter itself) remains the same. Admittedly, it's a bit irksome to introduce a new class, but that avoids polluting the Session. – Siderosis

Answer 2

Make intermediary class or adapter that will serve as a source to whatever automatic data bounding you already have. Then you are in full control to prepare args for the sproc exactly the way it needs them.

Marlinemarlinespike

This evades the use of the SDS with the TVP though, doesn't it? I already know I can write classes to source my databound objects... the issue is feeding the datatables into the existing datasource object. – Aquiline

When faced with bloated layers, the art of evasion is a great virtue :-) Technically you can derive from SqlDataSourceView and override ExecuteSelect etc. so that it still looks and quacks like SqlDataSourceView. You can read current implementation in Reflector and look for a place to do the mod. – Marlinemarlinespike