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 be set for UDT parameters." being thrown