使用实体框架4.1执行更新/插入存储过程吗? [英] Executing a update/insert stored procedure with entity framework 4.1?
本文介绍了使用实体框架4.1执行更新/插入存储过程吗?的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我试图从实体框架4.1(MVC 3 Web应用程序)调用存储过程.它不会引发异常,但是不会发生插入/更新吗?当我手动执行存储的proc时,它会起作用.
I tried to call a stored procedure from entity framework 4.1 (mvc 3 web application). Its not throwing an exception but the insert/update is not happening? When I manually executed the stored proc it works.
CREATE PROCEDURE [dbo].[SP_AddUpdateResponse]
@QuestionID int,
@UserID int,
@AnswerValue nvarchar(2000),
@ReviewID int
AS
MERGE [dbo].[Responses] AS [Target]
USING (SELECT @QuestionID, @UserID, @AnswerValue, @ReviewID)
AS [Source] ([QuestionID], [UserID], [AnswerValue], [ReviewID] )
ON [Target].[QuestionID] = [Source].[QuestionID]
AND [Target].[ReviewID] = [Source].[ReviewID]
WHEN MATCHED THEN
UPDATE SET [AnswerValue] = [Source].[AnswerValue]
WHEN NOT MATCHED THEN
INSERT ( [QuestionID], [UserID], [AnswerValue], [ReviewID] )
VALUES ( [Source].[QuestionID], [Source].[UserID],
[Source].[AnswerValue], [Source].[ReviewID] );
调用该过程的代码:
using System.Data.SqlClient;
using System.Data.Metadata.Edm;
using (var db = new NexGenContext())
{
foreach (var key in formCollection.AllKeys)
{
var answer = formCollection[key];
int questionId = Convert.ToInt32(key);
db.Database.SqlQuery<EntityType>(
"EXEC SP_AddUpdateResponse @QuestionID, @UserID, @AnswerValue, @ReviewID",
new SqlParameter("@QuestionID", questionId),
new SqlParameter("@UserID", 9999),
new SqlParameter("@AnswerValue", answer),
new SqlParameter("@ReviewID", id)
);
}
}
推荐答案
尝试使用ExecuteSqlCommand
而不是SqlQuery
db.Database.ExecuteSqlCommand(
"EXEC SP_AddUpdateResponse @QuestionID, @UserID, @AnswerValue, @ReviewID",
new SqlParameter("@QuestionID", questionId),
new SqlParameter("@UserID", 9999),
new SqlParameter("@AnswerValue", answer),
new SqlParameter("@ReviewID", id)
);
在SqlQuery
方法的描述中说:使用方法返回上下文跟踪的实体",因此您只能将其用于SELECT
查询.
In description of the SqlQuery
method said: "Use method to return entities that are tracked by the context", so you can use it only for SELECT
queries.
这篇关于使用实体框架4.1执行更新/插入存储过程吗?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文