LINQ的ORM功能中对使用sp_executesql语句的存储过程的支持

 

在实际的项目中，我们经常会碰到存储过程中需要使用sp_executesql语句的情形，如下：

    1 IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'GetEmployeesByFilter')

    2     BEGIN

    3         DROP Procedure [GetEmployeesByFilter]

    4     END

    5 

    6 GO

    7 

    8 CREATE Procedure [GetEmployeesByFilter]

    9     (

   10         @EmployeeNo NVarChar(50) = NULL,

   11         @EmployeeName NVarChar(50) = NULL,

   12         @DepartmentId Int = NULL,

   13         @PositionId Int = NULL,

   14         @EmployeeManager Int = NULL,

   15         @BeginEmployeeEntryDate DateTime = NULL,

   16         @EndEmployeeEntryDate DateTime = NULL,

   17         @EmployeeStatus Int = NULL,

   18         @PageSize Int = NULL,

   19         @PageIndex Int = NULL,

   20         @RecordCount Int = NULL OUTPUT

   21     )

   22 

   23 AS

   24 

   25     BEGIN

   26         DECLARE @MinIndex Int

   27         DECLARE @MaxIndex Int

   28         SET @MinIndex = (@PageIndex - 1) * @PageSize + 1

   29         SET @MaxIndex = @MinIndex + @PageSize - 1

   30 

   31         DECLARE @Where NVarChar(MAX)

   32         SET @Where = '0 = 0'

   33         IF @EmployeeNo IS NOT NULL

   34             SET @Where = @Where + ' AND [EmployeeNo] LIKE ''%' + @EmployeeNo + '%'''

   35         IF @EmployeeName IS NOT NULL

   36             SET @Where = @Where + ' AND [EmployeeName] LIKE ''%' + @EmployeeName + '%'''

   37         IF @DepartmentId IS NOT NULL

   38             SET @Where = @Where + ' AND [DepartmentId] = ''' + CONVERT(NVarChar, @DepartmentId) + ''''

   39         IF @PositionId IS NOT NULL

   40             SET @Where = @Where + ' AND [PositionId] = ''' + CONVERT(NVarChar, @PositionId) + ''''

   41         IF @EmployeeManager IS NOT NULL

   42             SET @Where = @Where + ' AND [EmployeeManager] = ''' + CONVERT(NVarChar, @EmployeeManager) + ''''

   43         IF @BeginEmployeeEntryDate IS NOT NULL

   44             SET @Where = @Where + ' AND [EmployeeEntryDate] >= ''' + CONVERT(NVarChar, @BeginEmployeeEntryDate, 101) + ' ' + '00:00:00' + ''''

   45         IF @EndEmployeeEntryDate IS NOT NULL

   46             SET @Where = @Where + ' AND [EmployeeEntryDate] <= ''' + CONVERT(NVarChar, @EndEmployeeEntryDate, 101) + ' ' + '23:59:59' + ''''

   47         IF @EmployeeStatus IS NOT NULL

   48             SET @Where = @Where + ' AND [EmployeeStatus] = ''' + CONVERT(NVarChar, @EmployeeStatus) + ''''

   49 

   50         DECLARE @Record NVarChar(MAX)

   51         SET @Record = 'SELECT ROW_NUMBER() OVER(ORDER BY [EmployeeId]) AS [Index],

   52                               [EmployeeId],

   53                               [EmployeeNo],

   54                               [EmployeeName],

   55                               [DepartmentId],

   56                               [PositionId],

   57                               [EmployeeManager],

   58                               [EmployeeGender],

   59                               [EmployeeEntryDate],

   60                               [EmoplyeeBirthday],

   61                               [EmployeePhone],

   62                               [EmployeeEmail],

   63                               [EmployeeStatus]

   64                        FROM [Employee]

   65                        WHERE' + ' ' + @Where

   66 

   67         DECLARE @Sql NVarChar(MAX)

   68         SET @Sql = 'SELECT @RecordCount = COUNT(*)

   69                     FROM (' + @Record + ') DERIVEDTBL

   70 

   71                     SELECT [EmployeeId],

   72                            [EmployeeNo],

   73                            [EmployeeName],

   74                            [DepartmentId],

   75                            [PositionId],

   76                            [EmployeeManager],

   77                            [EmployeeGender],

   78                            [EmployeeEntryDate],

   79                            [EmoplyeeBirthday],

   80                            [EmployeePhone],

   81                            [EmployeeEmail],

   82                            [EmployeeStatus]

   83                     FROM (' + @Record + ') DERIVEDTBL

   84                     WHERE [Index] >= @MinIndex AND [Index] <= @MaxIndex'

   85 

   86         DECLARE @Parameter NVarChar(MAX)

   87         SET @Parameter = '@MinIndex Int, @MaxIndex Int, @RecordCount Int OUTPUT'

   88 

   89         EXEC sp_executesql @Sql, @Parameter, @MinIndex, @MaxIndex, @RecordCount OUTPUT

   90     END

   91 

   92 GO

　　但在这种情况下，LINQ往往不能正确识别：

    1     <Function Name="dbo.GetEmployeesByFilter" Method="GetEmployeesByFilter">

    2         <Parameter Name="EmployeeNo" Parameter="employeeNo" Type="System.String" DbType="NVarChar(50)" />

    3         <Parameter Name="EmployeeName" Parameter="employeeName" Type="System.String" DbType="NVarChar(50)" />

    4         <Parameter Name="DepartmentId" Parameter="departmentId" Type="System.Int32" DbType="Int" />

    5         <Parameter Name="PositionId" Parameter="positionId" Type="System.Int32" DbType="Int" />

    6         <Parameter Name="EmployeeManager" Parameter="employeeManager" Type="System.Int32" DbType="Int" />

    7         <Parameter Name="BeginEmployeeEntryDate" Parameter="beginEmployeeEntryDate" Type="System.DateTime" DbType="DateTime" />

    8         <Parameter Name="EndEmployeeEntryDate" Parameter="endEmployeeEntryDate" Type="System.DateTime" DbType="DateTime" />

    9         <Parameter Name="EmployeeStatus" Parameter="employeeStatus" Type="System.Int32" DbType="Int" />

   10         <Parameter Name="PageSize" Parameter="pageSize" Type="System.Int32" DbType="Int" />

   11         <Parameter Name="PageIndex" Parameter="pageIndex" Type="System.Int32" DbType="Int" />

   12         <Parameter Name="RecordCount" Parameter="recordCount" Type="System.Int32" DbType="Int" Direction="InOut" />

   13         <Return Type="System.Int32" DbType="Int" />

   14     </Function>

　　这就是说，原本这个存储过程会返回一个结果集的，但LINQ会误认为返回的是一个Int32的值。
　　遇到这种情况，我们该怎么处理呢？这里提供两种解决方案：
　　1、直接更改DBML文件，将返回值改成一个空的结果集。

    1   <Function Name="dbo.GetEmployeesByFilter" Method="GetEmployeesByFilter">

    2     <Parameter Name="EmployeeNo" Parameter="employeeNo" Type="System.String" DbType="NVarChar(50)" />

    3     <Parameter Name="EmployeeName" Parameter="employeeName" Type="System.String" DbType="NVarChar(50)" />

    4     <Parameter Name="DepartmentId" Parameter="departmentId" Type="System.Int32" DbType="Int" />

    5     <Parameter Name="PositionId" Parameter="positionId" Type="System.Int32" DbType="Int" />

    6     <Parameter Name="EmployeeManager" Parameter="employeeManager" Type="System.Int32" DbType="Int" />

    7     <Parameter Name="BeginEmployeeEntryDate" Parameter="beginEmployeeEntryDate" Type="System.DateTime" DbType="DateTime" />

    8     <Parameter Name="EndEmployeeEntryDate" Parameter="endEmployeeEntryDate" Type="System.DateTime" DbType="DateTime" />

    9     <Parameter Name="EmployeeStatus" Parameter="employeeStatus" Type="System.Int32&gt;" DbType="Int" />

   10     <Parameter Name="PageSize" Parameter="pageSize" Type="System.Int32" DbType="Int" />

   11     <Parameter Name="PageIndex" Parameter="pageIndex" Type="System.Int32" DbType="Int" />

   12     <Parameter Name="RecordCount" Parameter="recordCount" Type="System.Int32" DbType="Int" Direction="InOut" />

   13     <ElementType Name="GetEmployeesByFilterResult" />

   14   </Function>

　　然后再按照上一篇文章所说的方法处理即可。
　　2、保持存储过程GetEmployeesByFilter不变，再创建另外一个存储过程GetEmployeesByFilterCalling，然后在存储过程GetEmployeesByFilterCalling中调用存储过程GetEmployeesByFilter（EXEC GetEmployeesByFilter）。
　　（感谢Microsoft公司的Brandon Wang提供的这个方案。不过对不住的是，在我的系统中第一种方案用起来似乎更为方便些。）

本文出自 “Denny_Zhang” 博客，出处http://eallies.blog.51cto.com/375118/79030

posted on 2009-05-07 11:44 瞌睡