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

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);

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...

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.

回答1:

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...

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>

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
}

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

EDIT (SOLVED CORRECTLY) :

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. Create a new complexe object representing the result of the stored procedure named result_NameOfStoredProc.

2. Place the procedure's definition in the model.

3. Create a function to use the stored procedure.

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

EDIT 2 (bog)

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. deleting my complexes types, fonctions importations, and Procedure from my .edmx file in the model folder.

2. Adding these two lines in the stored procedure :

   CREATE TABLE #TableVariable (cmpt nvarchar(18))

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

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

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.

Edit 3 (bog)

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.

回答2:

The solution to this issue is trough creating a temptable->inserting result into temp table -> select from temp table

then delete all objects related to this stored procedure on your project and update from database checking again this procedure

also use the SET NOCOUNT ON; on your sp