views:

26

answers:

2

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

+1  A: 

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

Perpetualcoder
I'm using an ObjectDataSource as this is being used by the ReportViewer control to display a report on the page
David Gardiner
Plain old ADO.net code and selects returning result sets will suffice your report viewer needs. I am wondering why u need SQL UDT.
Perpetualcoder
Oh ok. I want to pass in a varying number of parameters to filter the report.. eg. Just display Orders 1,2,4,6,4
David Gardiner
That's an interesting article, maybe I'll end up using that instead. Thanks (will leave question unanswered for now in case someone can answer the original problem)
David Gardiner
A: 

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!

David Gardiner