综合应用WPF/WCF/WF/LINQ之十三：LINQ的ORM功能中对使用sp_executesql语句的存储过程的支持...-白红宇

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

IF EXISTS (

SELECT *

FROM sysobjects

WHERE type =

'P'

AND name =

'GetEmployeesByFilter')

BEGIN

DROP Procedure [GetEmployeesByFilter]

END

6 GO

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 )

BEGIN

DECLARE @MinIndex

Int

DECLARE @MaxIndex

Int

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

SET @MaxIndex = @MinIndex + @PageSize - 1

DECLARE @Where

NVarChar(

MAX)

SET @Where =

'0 = 0'

IF @EmployeeNo

IS NOT NULL

SET @Where = @Where +

' AND [EmployeeNo] LIKE ''%' + @EmployeeNo +

'%'''

IF @EmployeeName

IS NOT NULL

SET @Where = @Where +

' AND [EmployeeName] LIKE ''%' + @EmployeeName +

'%'''

IF @DepartmentId

IS NOT NULL

SET @Where = @Where +

' AND [DepartmentId] = ''' +

CONVERT(

NVarChar, @DepartmentId) +

''''

IF @PositionId

IS NOT NULL

SET @Where = @Where +

' AND [PositionId] = ''' +

CONVERT(

NVarChar, @PositionId) +

''''

IF @EmployeeManager

IS NOT NULL

SET @Where = @Where +

' AND [EmployeeManager] = ''' +

CONVERT(

NVarChar, @EmployeeManager) +

''''

IF @BeginEmployeeEntryDate

IS NOT NULL

SET @Where = @Where +

' AND [EmployeeEntryDate] >= ''' +

CONVERT(

NVarChar, @BeginEmployeeEntryDate, 101) +

' ' +

'00:00:00' +

''''

IF @EndEmployeeEntryDate

IS NOT NULL

SET @Where = @Where +

' AND [EmployeeEntryDate] <= ''' +

CONVERT(

NVarChar, @EndEmployeeEntryDate, 101) +

' ' +

'23:59:59' +

''''

IF @EmployeeStatus

IS NOT NULL

SET @Where = @Where +

' AND [EmployeeStatus] = ''' +

CONVERT(

NVarChar, @EmployeeStatus) +

''''

DECLARE @Record

NVarChar(

MAX)

SET @Record =

'SELECT ROW_NUMBER() OVER(ORDER BY [EmployeeId]) AS [Index],

[EmployeeId],

[EmployeeNo],

[EmployeeName],

[DepartmentId],

[PositionId],

[EmployeeManager],

[EmployeeGender],

[EmployeeEntryDate],

[EmoplyeeBirthday],

[EmployeePhone],

[EmployeeEmail],

[EmployeeStatus]

FROM [Employee]

WHERE' +

' ' + @Where

DECLARE @Sql

NVarChar(

MAX)

SET @Sql =

'SELECT @RecordCount = COUNT(*)

FROM (' + @Record +

') DERIVEDTBL

SELECT [EmployeeId],

[EmployeeNo],

[EmployeeName],

[DepartmentId],

[PositionId],

[EmployeeManager],

[EmployeeGender],

[EmployeeEntryDate],

[EmoplyeeBirthday],

[EmployeePhone],

[EmployeeEmail],

[EmployeeStatus]

FROM (' + @Record +

') DERIVEDTBL

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

DECLARE @Parameter

NVarChar(

MAX)

SET @Parameter =

'@MinIndex Int, @MaxIndex Int, @RecordCount Int OUTPUT'

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

OUTPUT

END

92 GO

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

Function

Name

dbo.GetEmployeesByFilter"

Method

GetEmployeesByFilter"

Parameter

Name

EmployeeNo"

Parameter

employeeNo"

Type

System.String"

DbType

NVarChar(50)"

Parameter

Name

EmployeeName"

Parameter

employeeName"

Type

System.String"

DbType

NVarChar(50)"

Parameter

Name

DepartmentId"

Parameter

departmentId"

Type

System.Int32"

DbType

Int"

Parameter

Name

PositionId"

Parameter

positionId"

Type

System.Int32"

DbType

Int"

Parameter

Name

EmployeeManager"

Parameter

employeeManager"

Type

System.Int32"

DbType

Int"

Parameter

Name

BeginEmployeeEntryDate"

Parameter

beginEmployeeEntryDate"

Type

System.DateTime"

DbType

DateTime"

Parameter

Name

EndEmployeeEntryDate"

Parameter

endEmployeeEntryDate"

Type

System.DateTime"

DbType

DateTime"

Parameter

Name

EmployeeStatus"

Parameter

employeeStatus"

Type

System.Int32"

DbType

Int"

Parameter

Name

PageSize"

Parameter

pageSize"

Type

System.Int32"

DbType

Int"

Parameter

Name

PageIndex"

Parameter

pageIndex"

Type

System.Int32"

DbType

Int"

Parameter

Name

RecordCount"

Parameter

recordCount"

Type

System.Int32"

DbType

Int"

Direction

InOut"

Return

Type

System.Int32"

DbType

Int"

Function

这就是说，原本这个存储过程会返回一个结果集的，但LINQ会误认为返回的是一个Int32的值。

遇到这种情况，我们该怎么处理呢？这里提供两种解决方案：

1、直接更改DBML文件，将返回值改成一个空的结果集。

Function

Name

dbo.GetEmployeesByFilter"

Method

GetEmployeesByFilter"

Parameter

Name

EmployeeNo"

Parameter

employeeNo"

Type

System.String"

DbType

NVarChar(50)"

Parameter

Name

EmployeeName"

Parameter

employeeName"

Type

System.String"

DbType

NVarChar(50)"

Parameter

Name

DepartmentId"

Parameter

departmentId"

Type

System.Int32"

DbType

Int"

Parameter

Name

PositionId"

Parameter

positionId"

Type

System.Int32"

DbType

Int"

Parameter

Name

EmployeeManager"

Parameter

employeeManager"

Type

System.Int32"

DbType

Int"

Parameter

Name

BeginEmployeeEntryDate"

Parameter

beginEmployeeEntryDate"

Type

System.DateTime"

DbType

DateTime"

Parameter

Name

EndEmployeeEntryDate"

Parameter

endEmployeeEntryDate"

Type

System.DateTime"

DbType

DateTime"

Parameter

Name

EmployeeStatus"

Parameter

employeeStatus"

Type

System.Int32

DbType

Int"

Parameter

Name

PageSize"

Parameter

pageSize"

Type

System.Int32"

DbType

Int"

Parameter

Name

PageIndex"

Parameter

pageIndex"

Type

System.Int32"

DbType

Int"

Parameter

Name

RecordCount"

Parameter

recordCount"

Type

System.Int32"

DbType

Int"

Direction

InOut"

ElementType

Name

GetEmployeesByFilterResult"

Function

然后再按照上一篇文章所说的方法处理即可。

2、保持存储过程GetEmployeesByFilter不变，再创建另外一个存储过程GetEmployeesByFilterCalling，然后在存储过程GetEmployeesByFilterCalling中调用存储过程GetEmployeesByFilter（EXEC GetEmployeesByFilter）。

（感谢Microsoft公司的Brandon Wang提供的这个方案。不过对不住的是，在我的系统中第一种方案用起来似乎更为方便些。）

本文转自 Eallies 51CTO博客，原文链接：http://blog.51cto.com/eallies/79030，如需转载请自行联系原作者