ObjectDataSource 可以使用表值参数吗

发布于 2024-09-27 17:06:06 字数 1408 浏览 3 评论 0原文

如果 ASP.NET 网页使用 ObjectDataSource，您可以配置它吗使用使用表值参数的存储过程？

用户定义类型：

CREATE TYPE [dbo].[integer_list_tbltype] AS TABLE
(
 [n] [int] NOT NULL,
 PRIMARY KEY CLUSTERED 
)

存储过程：

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>

我尝试像这样挂钩到 ObjectDataSource 的 Selecting 事件：

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;        
}

但这只会导致“System.ArgumentException：必须为 UDT 参数设置 UdtTypeName 属性”。被扔

原文

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

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

苍暮颜 2024-10-04 17:06:06

我不知道你为什么要这样做。请参阅此 Lenni Lobel 的博客文章，看看这是否适合您。

*更新：*对于报告，我使用此技术，如 Codebetter.com 上所示

回复收藏 0 原文

心如荒岛 2024-10-04 17:06:06

为了完整起见，这是一个可能的解决方案（尽管它可能确实属于“可怕”类别！）

覆盖由强类型数据集生成的表适配器。例如。

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 );
        }
    }
}

然后更新 ObjectDataSource 控件上的 Type 属性以引用这个新类。例如。 “MyProject.GeneralDataSetTableAdapters.GeneralViewTableAdapter2”

希望看到比这更“干净”的答案！

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!

回复收藏 0 原文

~没有更多了~