通过C#使用linq服务调用SQL Server存储过程 [英] Calling a SQL Server stored procedure with linq service through c#

问题描述

我是Linq的新手，正在尝试将此SQL Server存储过程转换为Linq，我正在构建Silverlight业务应用程序，需要调用此过程以返回结果网格.

I am new to Linq and trying to convert this SQL Server stored procedure into Linq, I am building a Silverlight business app and need to call on this procedure to return a grid of results.

我有多个参数，用户可以使用这些参数来搜索特定的作品.他们缩小了通过UI的搜索范围，当他们按下搜索按钮时，后面的代码将获取所有参数，并将其发送给我的Linq服务，该服务随后需要调用存储过程.

I have multiple parameters that the users can use to search for particular pieces. They narrow down their search through the UI and when they hit the search button, the code behind takes all the arguments and sends it to my Linq service, which then needs to call on the stored procedure.

这是存储过程.

ALTER PROCEDURE dbo.spSearchResults
@PieceType      nvarchar(6) =  '',
@FileType       nvarchar(3) = '',
@Market     nvarchar(6) = '',
@PieceNumber        nvarchar(6) = '',
@Header1    nvarchar(50) = '',
@Header2    nvarchar(50) = '',
@Header3    nvarchar(50) = '',
@Header4    nvarchar(50) = '',
@JobNumber      nvarchar(50)=' ',
@bShowInActive  BIT = 0,
@UDAC1      nvarchar(50) = '',
@UDAC2      nvarchar(50) = '',
@UDAC3      nvarchar(50) = '',
@UDAC4      nvarchar(50) = ''   
AS
BEGIN
SET NOCOUNT ON

SELECT J.* 
FROM Job J
  LEFT JOIN JobHeading H1 (NOLOCK) ON J.[JobNumber] =  H1.[JobID]
  LEFT JOIN JobHeading H2 (NOLOCK) ON J.[JobNumber] =  H2.[JobID]
  LEFT JOIN JobHeading H3 (NOLOCK) ON J.[JobNumber] =  H3.[JobID]
  LEFT JOIN JobHeading H4 (NOLOCK) ON J.[JobNumber] =  H4.[JobID]
  LEFT JOIN JobUDAC udac1 (NOLOCK) ON J.[JobNumber] = udac1.[JobID]
  LEFT JOIN JobUDAC udac2 (NOLOCK) ON J.[JobNumber] = udac2.[JobID]
  LEFT JOIN JobUDAC udac3 (NOLOCK) ON J.[JobNumber] = udac3.[JobID]
  LEFT JOIN JobUDAC udac4 (NOLOCK) ON J.[JobNumber] = udac4.[JobID]
WHERE ((@PieceType = '') OR (PieceType = @PieceType))
  AND ((@FileType = '') OR (FileType = @FileType))
  AND ((@Market = '') OR (Market = @Market))
  AND ((@PieceNumber = '') OR (PieceNumber = @PieceNumber))
  AND ((@JobNumber = '') OR (JobNumber = @JobNumber))
  AND (J.IsActive=1 OR @bShowInActive = 1)
  AND (((@Header1 = '' AND @Header2 = '' AND @Header3 = '' AND @Header4 = '') OR 
        H1.HeadingRowID = @Header1)
    OR (--@Header2=0 OR 
        H2.HeadingRowID = @Header2 )
    OR (--@Header3=0 OR 
        H3.HeadingRowID = @Header3)
    OR (--@Header4=0 OR 
        H4.HeadingRowID = @Header4))
  AND (((@UDAC1 = '' AND @UDAC2 = '' AND @UDAC3 = '' AND @UDAC4 = '') OR 
        udac1.UDACRowID = @UDAC1)
    OR (--@Header2=0 OR 
        udac2.UDACRowID = @UDAC2 )
    OR (--@Header3=0 OR 
        udac3.UDACRowID = @UDAC3)
    OR (--@Header4=0 OR 
        udac4.UDACRowID = @UDAC4))

在Linq中，我发现需要进行某些转换，这是我的尝试.

In Linq I found that there are certain conversions to do and this is my attempt.

var query = from j in Job
                    join JobHeading H1 in Job on headingRowID1 equals H1
                    join JobHeading H2 in Job on headingRowID2 equals H2
                    join JobHeading H3 in Job on headingRowID3 equals H3
                    join JobHeading H4 in Job on headingRowID4 equals H4
                    join JobUDAC udac1 in Job on udacRowID1 equals udac1
                    join JobUDAC udac2 in Job on udacRowID2 equals udac2
                    join JobUDAC udac3 in Job on udacRowID3 equals udac3
                    join JobUDAC udac4 in Job on udacRowID4 equals udac4
                    join PieceType in db on piece equals PieceType
                    join JobFileType in db on filetype equals JobFileType
                    join Book in db on market equals Book
                    join PieceNumber in db on pieceNumber equals PieceNumber
                    join JobNumber in db on jobNumber equals JobNumber
                    join Job in db on FindJobs equals db
                    where ((piece = string.Empty) || (PieceType = piece))
                      && ((filetype = string.Empty) || (JobFileType = filetype))
                      && ((market = string.Empty) || (Book = market))
                      && ((pieceNumber = string.Empty) || (PieceNumber = pieceNumber))
                      && ((jobNumber = string.Empty) || (JobNumber = jobNumber))
                      && (showInActive = true)
                      && ((((headingRowID1 = string.Empty) + (headingRowID2 = string.Empty) + (headingRowID3 = string.Empty) + (headingRowID4 = string.Empty)) ||
                            H1.HeadingRowID = headingRowID1)
                        || (H2.HeadingRowID = headingRowID2)
                        || (H3.HeadingRowID = headingRowID3)
                        || (H4.HeadingRowID = headingRowID4))
                      && ((((udacRowID1 = string.Empty) + (udacRowID2 = string.Empty) + (udacRowID3 = string.Empty) + (udacRowID4 = string.Empty)) ||
                            udac1.UDACRowID = udacRowID1)
                        || (udac2.UDACRowID = udacRowID2)
                        || (udac3.UDACRowID = udacRowID3)
                        || (udac4.UDACRowID = udacRowID4))
                    select j.Job;
        return query;

但是，开头的"Job"有一个错误，并说找不到实现...找不到'join'"有人可以帮忙翻译吗?还是提供一种更好的方法来调用带有后面代码的存储过程?谢谢

However, the beginning 'Job' has an error, and says 'could not find an implementation ... 'join' not found' Can anyone help translate? Or offer a better way to call the stored procedure with the code behind? Thanks

推荐答案

您可以将存储过程添加到实体上下文中，并作为一种方法来调用它.

You can add your store procedure to the entity context and call it as it were a method.

为此，您必须将存储过程添加到实体模型.您没有说如何建立模型或使用的是什么版本的EF，但我想您可以继续从数据库中更新模型并添加存储过程.

To accomplish this, you have to add the store procedure to the entity model. You don't say how are you building the model or what version of EF you are using, but I assume you can just go ahead and update the model from the DB and add the store procedure.

一旦模型中有SP，就必须将SP转换为功能导入.您可以通过在模型浏览器中导航到存储过程，右键单击SP图标并选择添加功能导入"来执行此操作.因为您要返回复杂的结果集，所以建议您构建一个复杂类型，将其用作SP的结果类型.要在添加函数导入"对话框中创建复杂类型，请单击获取列信息"按钮.这将显示SP的返回值，因此您可以通过单击创建新的复杂类型"按钮来创建新的复杂类型.给它起一个名字，然后单击确定".现在，您的函数导入位于模型浏览器的函数导入"下.

Once you have the SP in the model you have to turn the SP into a Function Import. The way you do this is by navigating to the store procedure in the Model Browser, right click the SP icon and select "Add function import". Because you are returning a complex result set, I suggest you build a Complex Type, which you will use as the result type of the SP. To create a Complex Type in the Add Function Import dialog click the "Get Column Information" button. That will present the return values of your SP, so you can create a new Complex Type by clicking the "Create New Complex Type" button. Give it a name and click OK. Your function import is now under "Function Imports" in the Model Browser.

函数导入方法的调用方式为:

The way you call you function import method is:

var result = context.spSearchResults(Your list of parameters...);

这将执行SP，并且结果"应为您提供复杂类型的对象列表.

This executes the SP, and "result" should give you a list of objects of the type of your complex type.

一些参考文献:

http://msdn.microsoft.com/en-us/library/bb896231.aspx

http://msdn.microsoft.com/en-us/library/ee534438.aspx

仅需完成:添加复杂类型的另一种方法是:在模型浏览器"中，导航到复杂类型"，右键单击并选择创建复杂类型".在这里，您必须定义一个复杂类型，该类型具有与SP结果集相同的属性(名称和类型).在对话框中，选择返回的集合"中的复杂"，

Just to be complete: other way you can add a complex type is: in Model Browser navigate to "Complex Types", right click and select "Create Complex Type". There you have to define a complex type that has the same properties (names and types) of the result set of your SP. In the dialog box select Complex in the "Returns a Collection of",

希望这会有所帮助.

这篇关于通过C#使用linq服务调用SQL Server存储过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！