How to configure ASP.NET SQL Datasource to accept TVP

Question

In the code, you will add TVP as SqlDbType.Structured for the stored procedure But this does not exist in the ASP.NET SqlDataSource control.

I saved my Datatables in session variables (don't worry, they are small!) And I need to pass them as parameters to SqlDataSource (which has several data objects)

I pointed the data source to a session variable, but it does not work when converting to a table type.

EDIT: Let's say I take the Session variable from the equation (because, indeed, it is completely tangential)

There should be a way that I can attach DBType.Structured to SQLDataSource. My lists have the appropriate data binding, but the storage procedures to which they are attached must accept TVP

I can’t believe that there was no way to send the TVP parameter to SQLDataSource? What are my alternatives?

EDIT2: I am creating a custom parameter for SqlDataSource, but it still seems to me that its "eval" method will not be satisfied with the structured data type

EDIT3: It seems like my only option is to do all the work in CodeBehind for my control of data binding. I added generosity in case anyone else has an elegant solution.

EDIT4: Is it possible that I can transfer the table as an object to a stored procedure, and then SQL Server converts it to TVP?

Answer 1

I know that you edited to say that the session does not matter, however I was able to get this working with SessionParameter. I have a feeling that it will also work with ControlParameter.

So you have a custom 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 the GridView is bound to a SqlDataSource, which selects from your sproc by 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 the DataTable in the session, although you say that you already have it:

(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> 

(FROM#)

 <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 precisely related GridView:

and the following generated request 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 it should work even lower.

Answer 2

Make an intermediate class or adapter that will serve as the source of any automatic data that you already have. Then you have complete control over the preparation of arguments for sproc exactly as they need them.