How to set up ASP.NET SQL Datasource to accept TVP
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?
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.
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.
链接地址: http://www.djcxy.com/p/46914.html