Can ObjectDataSource use table-valued parameters

Question

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

To clarify, this is being used by a ReportViewer control. The rdlc files like to use DataSets to bind data to the report - that's why I'm using an ObjectDataSource — David Gardiner, Oct 15 '10 at 04:20

Perpetualcoder · Answer 1 · 2010-10-15T04:48:20.157

1

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

edited Oct 15 '10 at 04:48

answered Oct 15 '10 at 03:52

Perpetualcoder

13,501
9
64
99

I'm using an ObjectDataSource as this is being used by the ReportViewer control to display a report on the page – David Gardiner Oct 15 '10 at 04:16
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 Oct 15 '10 at 04:42
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 Oct 15 '10 at 05:03
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 Oct 15 '10 at 05:10

score 0 · Answer 2 · answered Oct 15 '10 at 05:51

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!

Can ObjectDataSource use table-valued parameters

2 Answers2