LINQ到SQL：存储过程返回单个标量值？ [英] LINQ-to-SQL: Stored Procedure that returns a single scalar value?

问题描述

我使用LINQ到SQL为查询遗留数据库的应用程序。我需要调用存储过程，即选择一个整数值。 。改变存储过程是不是一种选择

I am using LINQ-to-SQL for an application that queries a legacy database. I need to call a stored procedure, that selects a single integer value. Changing the stored procedure is not an option.

设计者创建了这个签名的方法：

The designer creates a method with this signature:

private ISingleResult<sp_xal_seqnoResult> NextRowNumber([Parameter(DbType="Int")] System.Nullable<int> increment, [Parameter(DbType="Char(3)")] string dataset)

我想返回类型为int。我如何做到这一点使用LINQ到SQL？

I would like the return type to be int. How do I do this using LINQ-to-SQL ?

推荐答案

这将是微不足道的一个标量函数（UDF），而不是SP。但是，它应该工作很轻松了 - 尽管如果SP是复杂的（即FMT_ONLY不能检查它100％），那么你可能需要帮助它... ...

This would be trivial with a scalar function (UDF) rather than an SP. However, it should work easily enough - although if the SP is complex (i.e. FMT_ONLY can't inspect it 100%) then you might need to "help" it...

下面是我从返回一个整数一个简化的SP产生了一些的dbml;您可以编辑通过dbml的打开方式... XML编辑器）：

Here's some dbml that I generated from a simplfied SP that returns an integer; you can edit the dbml via "open with... xml editor):

<Function Name="dbo.foo" Method="foo">
    <Parameter Name="inc" Type="System.Int32" DbType="Int" />
    <Parameter Name="dataset" Type="System.String" DbType="VarChar(20)" />
    <Return Type="System.Int32" />
</Function>

（注意你显然需要调整的名称和数据类型）。

(note you obviously need to tweak the names and data-types).

和这里是生成的C＃：

[Function(Name="dbo.foo")]
public int foo([Parameter(DbType="Int")] System.Nullable<int> inc, [Parameter(DbType="VarChar(20)")] string dataset)
{
    IExecuteResult result = this.ExecuteMethodCall(this, ((MethodInfo)(MethodInfo.GetCurrentMethod())), inc, dataset);
    return ((int)(result.ReturnValue));
}

如果您当前的SP使用SELECT（而不是返回），那么DBML将需要反映这一点。你可以通过隐藏实现细节解决这个问题，并提供在部分类中的公共包装;例如：

If your current SP uses SELECT (instead of RETURN), then the DBML will need to reflect this. You can fix this by hiding the implementation details, and providing a public wrapper in a partial class; for example:

<Function Name="dbo.foo" Method="FooPrivate" AccessModifier="Private">
    <Parameter Name="inc" Type="System.Int32" DbType="Int" />
    <Parameter Name="dataset" Type="System.String" DbType="VarChar(20)" />
    <ElementType Name="fooResult" AccessModifier="Internal">
      <Column Name="value" Type="System.Int32" DbType="Int NOT NULL" CanBeNull="false" />
    </ElementType>
</Function>

上面的描述，它返回与单个列单个表的SP;但我所做的SP私有的数据上下文，结果型内部的组件（隐藏它）：

The above describes an SP that returns a single table with a single column; but I've made the SP "private" to the data-context, and the result-type "internal" to the assembly (hiding it):

[Function(Name="dbo.foo")]
private ISingleResult<fooResult> FooPrivate(
    [Parameter(DbType="Int")] System.Nullable<int> inc,
    [Parameter(DbType="VarChar(20)")] string dataset)
{
    IExecuteResult result = this.ExecuteMethodCall(this, ((MethodInfo)(MethodInfo.GetCurrentMethod())), inc, dataset);
    return ((ISingleResult<fooResult>)(result.ReturnValue));
}

现在的在我自己的类文件的我可以添加新的部分类（一个新的.cs文件）在正确的命名空间，这更方便公开方式：

Now in my own class file I can add a new partial class (a new .cs file) in the correct namespace, that exposes the method more conveniently:

namespace MyNamespace {
    partial class MyDataContext
    {
        public int Foo(int? inc, string dataSet)
        {
            return FooPrivate(inc, dataSet).Single().value;
        }
    }
}

（命名空间和上下文名需要是相同的实际数据上下文）。这增加了隐藏来自来电的蹩脚细节的公共方法

(the namespace and context names need to be the same as the actual data-context). This adds a public method that hides the grungy details from the caller.

不要编辑designer.cs文件直接;您的更改将丢失。只有编辑这dbml的或部分的类。

Don't edit the designer.cs file directly; your changes will be lost. Only edit either the dbml or partial classes.

这篇关于LINQ到SQL：存储过程返回单个标量值？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！