如何让 Linq to SQL 识别动态存储过程的结果集?

问题描述

我将 Linq-to-SQL 与 SQL Server 后端(当然)一起用作项目的 ORM.我需要从从动态创建的表返回的存储过程中获取结果集.这是 proc 的样子:

I'm using Linq-to-SQL with a SQL Server backend (of course) as an ORM for a project. I need to get the result set from a stored procedure that returns from a dynamically-created table. Here's what the proc looks like:

CREATE procedure [RetailAdmin].[TitleSearch] (
@isbn varchar(50), @author varchar(50),
 @title varchar(50))
as

declare @L_isbn varchar(50)
declare @l_author varchar(50)
declare @l_title varchar(50)
declare @sql nvarchar(4000)

set  @L_isbn = rtrim(ltrim(@isbn))
set @l_author = rtrim(ltrim(@author))
set @l_title = rtrim(ltrim(@title))

CREATE TABLE #mytemp(
    [storeid] int not NULL,
    [Author] [varchar](100) NULL,
    [Title] [varchar](400) NULL,
    [ISBN] [varchar](50) NULL,
    [Imprint] [varchar](255) NULL,
    [Edition] [varchar](255) NULL,
    [Copyright] [varchar](100) NULL,
    [stockonhand] [int] NULL
)

set @sql  = 'select  a.storeid, Author,Title, thirteendigitisbn ISBN,  
Imprint,Edition,Copyright ,b.stockonhand from ods.items a join ods.inventory b on     
a.itemkey = b.itemkey where b.stockonhand <> 0  ' 

if len(@l_author) > 0
set @sql =  @sql + ' and author like ''%'+@L_author+'%'''

if len(@l_title) > 0
set @sql =  @sql + ' and title like ''%'+@l_title+'%'''

if len(@L_isbn) > 0
set @sql =  @sql + ' and thirteendigitisbn like ''%'+@L_isbn+'%'''

print @sql

if len(@l_author) <> 0 or len(@l_title) <>  0 or len(@L_isbn) <> 0 

begin
    insert into #mytemp
    EXECUTE sp_executesql  @sql
end 


select * from #mytemp
drop table #mytemp

这个程序不是我写的，但如果出现非常严重的问题，可能会影响更改.

I didn't write this procedure, but may be able to influence a change if there's a really serious problem.

我目前的问题是，当我将这个过程添加到我的模型中时，设计者会生成这个函数:

My present problem is that when I add this procedure to my model, the designer generates this function:

[Function(Name="RetailAdmin.TitleSearch")]
public int TitleSearch([Parameter(DbType="VarChar(50)")] string isbn,  
  [Parameter(DbType="VarChar(50)")] string author, 
  [Parameter(DbType="VarChar(50)")] string title)
{
    IExecuteResult result = this.ExecuteMethodCall(this, 
        ((MethodInfo)(MethodInfo.GetCurrentMethod())), isbn, author, title);

    return ((int)(result.ReturnValue));
}

这看起来不像我手动运行 proc 时得到的结果集:

which doesn't look anything like the result set I get when I run the proc manually:

谁能告诉我这里出了什么问题?

Can anybody tell me what's going wrong here?

这与这个问题基本相同 但由于 OP 措辞不当，从未真正得到答复.

This is basically the same problem as this question but due to the poor phrasing from the OP it was never really answered.

感谢马克的回复.我会看看如何进行您建议的更改.

Thanks Marc for your reply. I will see about making the changes you suggested.

问题是临时表.Linq to Sql 只是不知道如何处理它们.这尤其难以诊断，因为 Visual Studio 缓存了有关存储过程的信息，因此当它最初找不到结果集时，它会将返回值设置为默认整数类型，并且在我对存储过程进行更改时不会更新.让 VS 识别更改需要您:

The problem was the temp table. Linq to Sql just doesn't know what to do with them. This was particularly difficult to diagnose, because Visual Studio caches information about stored procs, so when it initially failed to find a result set it set the return as a default integer type and didn't update when I made changes to the stored proc. Getting VS to recognize a change requires you to:

• 从 dbml 中删除 proc
• 从服务器资源管理器中删除服务器连接
• 保存 dbml 以强制重新编译
• 关闭项目并重启VS
• 重新创建服务器连接并导入过程

您可能不必执行所有这些步骤，但这对我有用.如果您必须使用临时表，您需要做的是创建一个简单地返回正确架构的准系统 proc，然后在将其导入 OR 设计器后对其进行更改以执行您想要的操作.

You might not have to do every one of those steps, but that's what worked for me. What you need to do, if you must use a temp table, is to create a barebones proc that simply returns the correct schema, and then alter it to do what you want after you've imported it into the OR Designer.

推荐答案

首先 - 重要 - 您的 SQL 容易受到注入；内部命令应该被参数化:

First - IMPORTANT - your SQL is vulnerable to injection; the inner command should be parameterized:

if len(@l_author) > 0
set @sql =  @sql + ' and author like ''%''+@author+''%'''

EXECUTE sp_executesql  @sql, N'@author varchar(100)', @L_author

这会将@L_author 的值作为动态命令中的@author 参数传入 - 防止注入攻击.

This passes the value of @L_author in as the @author parameter in the dynamic command - preventing injection attacks.

第二 - 你真的不需要临时表.它对你没有任何作用……你只是插入和选择.也许只是 EXEC 并让结果自然流向调用者?

Second - you don't really need the temp table. It isn't doing anything for you... you just INSERT and SELECT. Perhaps just EXEC and let the results flow to the caller naturally?

在其他情况下，表变量更合适，但这不适用于 INSERT/EXEC.

In other circumstances a table-variable would be more appropriate, but this doesn't work with INSERT/EXEC.

每次调用的列是否相同?如果是这样，要么手动编写 dbml，要么使用临时 SP(仅使用WHERE 1=0"或其他内容)，以便 SET FMT_ONLY ON 可以工作.

Are the columns the same for every call? If so, either write the dbml manually, or use a temp SP (just with "WHERE 1=0" or something) so that the SET FMT_ONLY ON can work.

如果不是(每次使用不同的列)，则没有简单的答案.也许在这种情况下使用常规 ADO.NET(ExecuteReader/IDataReader - 甚至可能是 DataTable.Fill).

If not (different columns per usage), then there isn't an easy answer. Perhaps use regular ADO.NET in this case (ExecuteReader/IDataReader - and perhaps even DataTable.Fill).

当然，你可以让 LINQ 承受压力......(C#):

Of course, you could let LINQ take the strain... (C#):

...
if(!string.IsNullOrEmpty(author)) {
    query = query.Where(row => row.Author.Contains(author));
}
...

等

这篇关于如何让 Linq to SQL 识别动态存储过程的结果集?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持html5模板网！