如何获取LINQ to SQL的识别结果集动态存储过程的？ [英] How do I get Linq to SQL to recognize the result set of a dynamic Stored Procedure?

问题描述

我使用LINQ到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

• Delete proc from the dbml
• delete the server connection from Server Explorer
• save the dbml to force a recompile
• close the project and restart VS
• recreate the server connection and import the proc

您可能没有做这些步骤的每一个，但是这对我工作。你需要做的，如果你必须使用一个临时表是什么，是创建一个准系统PROC简单地返回正确的架构，然后修改它做你想要什么，你已经导入到的人或者设计人后。

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?

在其他情况下的表变量会更合适，但是这并不与插入/ EXEC工作

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

---

是列同样为每一个电话？如果是这样，无论是手动编写的dbml，或者使用一个临时的SP（只是与WHERE 1 = 0或某事），使设置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 - 甚至数据表。.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));
}
...

等

etc

这篇关于如何获取LINQ to SQL的识别结果集动态存储过程的？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！