Sql 存储过程和实体框架 6 [英] Sql Stored proc and Entity framework 6

问题描述

假设我有一个单参数存储过程:

Suppose i have a one parameter stored procedure :

procedure dbo.MyStoredProc @param VARCHAR(50)
as 
SELECT *
FROM whatever
WHERE column = @param

在我的 Web 应用程序(带有 entity 6 的 mvc)中，我希望调用我的存储过程，并从它的 SELECT 语句中检索所有行.

In my web application (mvc with entity 6) i wish to call my stored procedure, and retreive all the line from it's SELECT statement.

 var DBLines = MyStoredProc(parameterValue);

我看到了一些令人失望但非常合乎逻辑的东西；我的 var 包含一个整数.那个整数是 1.它告诉我在我的存储过程中一切正常...

I saw something disappointing but very logic; my var was containing an integer. That integer was 1. It was telling me that everything went ok in my stored procedure...

我希望 var 包含我的存储过程经过的每条 SQL 行.我想这是可能的，但如何?必须有一个共同的逻辑.我在互联网上看到的只是解决方法.

I want that var to contains every SQL lines my stored proc went through. I suppose it is possible but how ? There must be a commun logic. All i saw on internet... was workarounds.

推荐答案

在经历了很多挫折之后，我找到了一种方法，但这是另一种解决方法.不过，这个对我来说效果很好，它不需要额外的 C# 代码，不需要模型，也不需要 public class FunctionsContext : BaseContext<FunctionsContext>{} 之类的东西......

After lots of frustrations, i found a way, but it's another work around. This one works pretty well for me though, and it does not require extra C# code, no model, and no such things as public class FunctionsContext : BaseContext<FunctionsContext>{} whatever that is...

在 SQL Server 中，我创建了一个看起来就像我的存储过程返回的空表.假设我的存储过程要返回:

In SQL Server, i created an empty table that looks just like the return of my stored procedure. Suppose my stored procedure was to return :

SELECT nvarchar(2), int, datetime FROM whatever WHERE <condition>

然后我的 Empty_Table 使用相同的 var 类型创建.在使用实体框架映射向导映射我的 Empty_Table 之后.我可以将其用作此类型:

Then my Empty_Table is created with the same var types. After mapping my Empty_Table with Entity framework mapping wizard. I could use it as a type to to this :

databaseEntities db = new databaseEntities();

var query = db.Database.SqlQuery<Empty_Table>("EXEC [dbo].[My_Stored_Proc] @p1", new SqlParameter("p1", parameterValue));

foreach (var line in query)
{
    int i = line.ID
}

而且效果很好.这将是我在 Entity 中使用存储过程的主要(唯一)技术.

And it worked perfectly. This is going to be my main (only) technique for using stored procedure with Entity.

编辑(已正确解决):

问题已在最新版本的实体中完全解决，如果您将 EDMX 文件放在 Model 文件夹中，则可以在上下文菜单中导入存储过程.EF 会自动做 3 件事:

Issu as been completely solved in the latest version of entity, if you put your EDMX file inside the Model folder, then you can import the stored procedure in the contextual menu. EF will automatically do 3 things :

1. 创建一个新的复杂对象，表示名为 result_NameOfStoredProc 的存储过程的结果.

将过程的定义放在模型中.

Place the procedure's definition in the model.

创建一个函数来使用存储过程.

Create a function to use the stored procedure.

所以没有更多的解决方法.这就是要走的路.

So no more work-around. That's the way to go.

编辑 2(沼泽)

由于某种原因，我在存储过程中引入临时表后，我的第一次编辑(好的解决方案)就被破坏了.

For some reason, my first edit (good solution) got broken as soon as I introduce a temporary table in my stored procedure.

只有当它不包含临时表时，我才能正确导入我的存储过程，我测试了它

I am able to import my stored procedure correctly only if it doesn't contain a temporary table, I tested it by

1. 从模型文件夹中的 .edmx 文件中删除我的复合体类型、函数导入和过程.

1. deleting my complexes types, fonctions importations, and Procedure from my .edmx file in the model folder.

在存储过程中加入这两行:

Adding these two lines in the stored procedure :

创建表#TableVariable (cmpt nvarchar(18))

insert into #TableVariable select cmpt from TA where NomReseau = @user

使用 .edmx 文件的上下文菜单重新导入程序

Re-import the procedure using .edmx file's contextual menu

然后繁荣.不再创建 result_NameOfStoredProc 对象...取而代之的是一个烦人的 int，就像以前一样.我不得不修改整个查询以替换对这个临时表的所有调用(逻辑上，一个变量表)，然后保存它，然后导入，然后将过程改回来......很有趣......这是一场灾难.

And boom. No more result_NameOfStoredProc object created... An annoying int instead, JUST LIKE BEFORE. I had to modify the whole query to replace all call to this temporary table to something else (logically, a variable table), then save it, then import, then change the procedure back... Much fun... This is a disaster.

编辑 3(沼泽)

嗯...我有另一个沼泽实体无法创建我需要的结果对象，我认为这是因为我使用的数据库比我的其他数据库旧.所以我创建了一个类来补偿，我使用了我的第一个解决方案的代码.有时没有其他办法.我希望 Entity 始终像我的第一次编辑一样工作，但我不能指望这一点.

Well... I have another bog where entity isn't able to create the result object I need, I think it's because the database I use is older than my others. So I created a class to compensate and I used the code of my first solution. Sometimes there is no other way. I would like Entity to always work like in my first edit, but I just CAN'T count on that.

这篇关于Sql 存储过程和实体框架 6的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！