从 LINQ To SQL ExecuteQuery 中的存储过程捕获输出参数

发布于 2024-09-14 13:00:02 字数 1111 浏览 11 评论 0 原文

执行存储过程时是否可以从 LINQ To SQL DataContext 获取输出参数?

IEnumerable<Address> result = 
    ExecuteQuery<Address>(((MethodInfo)(MethodInfo.GetCurrentMethod())), 
       address, pageIndex, pageSize, totalCount);

其中,addresspageIndexpageSize 是输入参数,TotalCount 是输出参数。

如何捕获输出参数?

这是另一种尝试,但再次无法获取参数值:


    [Function(Name = "Telecom.AddressSearch")]
        private IEnumerable SearchAddress([Parameter(Name = "address", DbType = "varchar")] string address,
                                             [Parameter(Name = "pageIndex", DbType = "int")] int pageIndex,
                                             [Parameter(Name = "pageSize", DbType = "int")] int pageSize,
                                             [Parameter(Name = "totalCount", DbType = "int")] ref int totalCount)
        {            
            IEnumerable result = ExecuteQuery(((MethodInfo)(MethodInfo.GetCurrentMethod())), address, pageIndex, pageSize, totalCount);

            return result;
        }

Is it possible to get an output parameter back from the LINQ To SQL DataContext when you execute a stored procedure?

IEnumerable<Address> result = 
    ExecuteQuery<Address>(((MethodInfo)(MethodInfo.GetCurrentMethod())), 
       address, pageIndex, pageSize, totalCount);

where address, pageIndex and pageSize are input parameters, and TotalCount is an output parameter.

How can you capture the output param?

here is another attempt at doing it but again cannot get the parameter value:


    [Function(Name = "Telecom.AddressSearch")]
        private IEnumerable SearchAddress([Parameter(Name = "address", DbType = "varchar")] string address,
                                             [Parameter(Name = "pageIndex", DbType = "int")] int pageIndex,
                                             [Parameter(Name = "pageSize", DbType = "int")] int pageSize,
                                             [Parameter(Name = "totalCount", DbType = "int")] ref int totalCount)
        {            
            IEnumerable result = ExecuteQuery(((MethodInfo)(MethodInfo.GetCurrentMethod())), address, pageIndex, pageSize, totalCount);

            return result;
        }

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

扫码二维码加入Web技术交流群

发布评论

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

评论(1

天赋异禀 2024-09-21 13:00:02

斯科特·格思里(Scott Guthrie)有一个 博客文章,介绍如何让 LINQ to SQL 与存储过程配合使用。虽然他的帖子没有直接回答您的问题,但它提供了一些可能有效的线索。在 .dbml 类中定义方法时,“LINQ to SQL 将 SPROC 中的‘out’参数映射为引用参数(ref 关键字)。”您可以尝试使用 ref 关键字并查看 TotalCount 是否已更新。

   IEnumerable r = ExecuteQuery(((MethodInfo)(MethodInfo.GetCurrentMethod())),
   address, pageIndex, pageSize, ref totalCount);

更新:

我做了一些更多的研究,并找到了一种适合您的方法。这是来自 MSDN 文章。您需要扩展您的 DataContext,但这不应该是太大的问题(看来您可能已经在这样做了)。查看该文章以获取更多信息,但基本内容是:

[Function(Name="dbo.CustOrderTotal")]
[return: Parameter(DbType="Int")]
public int CustOrderTotal([Parameter(Name="CustomerID", DbType="NChar(5)")] string customerID, [Parameter(Name="TotalSales", DbType="Money")] ref System.Nullable<decimal> totalSales)
{
    IExecuteResult result = this.ExecuteMethodCall(this, ((MethodInfo)(MethodInfo.GetCurrentMethod())), customerID, totalSales);
    totalSales = ((System.Nullable<decimal>)(result.GetParameterValue(1)));
    return ((int)(result.ReturnValue));
}

其中“this”是您的 DataContext。

更新 2:

IExecuteResult 有一个 ReturnValue 属性。它是 Object 类型,因此您必须对其进行转换才能获得结果,但它应该允许您获得结果的 Enumerable。在你的情况下,这样的事情应该有效:

IExecuteResult result = this.ExecuteMethodCall(this, ((MethodInfo)(MethodInfo.GetCurrentMethod())), address, pageIndex, pageSize, totalCount);
totalCount = ((System.Nullable<decimal>)(result.GetParameterValue(3)));
return (IEnumerable<Address>)result.ReturnValue;

Scott Guthrie has a blog post that describes how to get LINQ to SQL to work with stored procedures. While his post does not directly answer your question, it provides a clue to something that may work. When defining a method in a .dbml class, "LINQ to SQL maps 'out' parameters in SPROCs as reference parameters (ref keyword)." You might try and use the ref keyword and see if the totalCount gets updated.

   IEnumerable r = ExecuteQuery(((MethodInfo)(MethodInfo.GetCurrentMethod())),
   address, pageIndex, pageSize, ref totalCount);

Update:

I did some more research, and have found a way that should work for you. This is from an MSDN article. You would need to extend your DataContext, but that shouldn't be too big of an issue (it appears you may already be doing this). Check out the article for more information, but the basic piece is this:

[Function(Name="dbo.CustOrderTotal")]
[return: Parameter(DbType="Int")]
public int CustOrderTotal([Parameter(Name="CustomerID", DbType="NChar(5)")] string customerID, [Parameter(Name="TotalSales", DbType="Money")] ref System.Nullable<decimal> totalSales)
{
    IExecuteResult result = this.ExecuteMethodCall(this, ((MethodInfo)(MethodInfo.GetCurrentMethod())), customerID, totalSales);
    totalSales = ((System.Nullable<decimal>)(result.GetParameterValue(1)));
    return ((int)(result.ReturnValue));
}

Where 'this' is your DataContext.

Update 2:

The IExecuteResult has a ReturnValue property. It is of type Object, so you will have to cast it to get the results, but it should allow you to get the Enumerable of the results. In your case, something like this should work:

IExecuteResult result = this.ExecuteMethodCall(this, ((MethodInfo)(MethodInfo.GetCurrentMethod())), address, pageIndex, pageSize, totalCount);
totalCount = ((System.Nullable<decimal>)(result.GetParameterValue(3)));
return (IEnumerable<Address>)result.ReturnValue;
~没有更多了~
我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站,即表示您同意使用 Cookies 和您的相关数据。
原文