If an ASP.NET web page uses an ObjectDataSource, can you configure it to use a stored procedure that uses table-value parameters?
User-defined type:
CREATE TYPE [dbo].[integer_list_tbltype] AS TABLE
(
[n] [int] NOT NULL,
PRIMARY KEY CLUSTERED
)
Stored procedure:
CREATE PROCEDURE [dbo].[GeneralReport]
@intList integer_list_tbltype READONLY
AS
BEGIN
SELECT * FROM ...
END
ASP.NET
<asp:ObjectDataSource ID="GeneralDataSource" runat="server"
SelectMethod="GetDataByRange"
TypeName="MyProject.GeneralDataSetTableAdapters.GeneralViewTableAdapter"
>
<SelectParameters>
<asp:Parameter Name="intList" />
</SelectParameters>
</asp:ObjectDataSource>
I've tried hooking into the ObjectDataSource's Selecting event like this:
protected void GeneralDataSource_Selecting( object sender, System.Web.UI.WebControls.ObjectDataSourceSelectingEventArgs e )
{
var zeroList = new List<SqlDataRecord>();
var tvp_definition = new[] {new SqlMetaData( "n", SqlDbType.Int )};
var sqlDataRecord = new SqlDataRecord( tvp_definition );
sqlDataRecord.SetInt32( 0, 0 );
zeroList.Add( sqlDataRecord );
e.InputParameters[ "intList" ] = zeroList;
}
But that just results in a "System.ArgumentException: UdtTypeName property must b开发者_C百科e set for UDT parameters." being thrown
I do not know why you wish to do it this way. See this blog post by Lenni Lobel and see if that works for you.
*UPDATE:*For reporting I use this technique as shown on Codebetter.com
For the sake of completeness, this is a possible solution (though it probably does fall into the 'horrible' category!)
Override the table adapter that was generated by the strongly-typed dataset. eg.
public class GeneralViewTableAdapter2 : GeneralViewTableAdapter
{
public override GeneralDataSet.GeneralViewDataTable GetDataByRange( object intList )
{
try
{
return base.GetDataByRange( intList );
}
catch ( ArgumentException e )
{
foreach ( SqlParameter parameter in this.Adapter.SelectCommand.Parameters )
{
if ( parameter.SqlDbType == SqlDbType.Structured )
parameter.TypeName = "integer_list_tbltype";
}
return base.GetDataByRange( intList );
}
}
}
Then update the Type attribute on the ObjectDataSource control to refer to this new class. eg. "MyProject.GeneralDataSetTableAdapters.GeneralViewTableAdapter2"
Would love to see a 'cleaner' answer than this!
精彩评论