views:

241

answers:

3

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?

A: 

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.

ZXX
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.
Matthew PK
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.
ZXX
A: 

Hey Matthew,

myself i cant give you best answer, but have a look here

Take care, Ragims

Ragims
+1  A: 

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:

alt text

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.

langsamu
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 youI have reactivated the bounty and marked yours as the answer
Matthew PK
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
Matthew PK