ASP.NET MVC通过ExecuteSqlCommand获取插入项的ID(ASP.NET MVC Get the ID of Inserted Item by ExecuteSqlCommand)

我在ASP.NET MVC3项目中插入以下内容：

var query = "INSERT INTO MyTable VALUES(//some values)"; db.Database.ExecuteSqlCommand(query);

有没有一种简单的方法来获取插入行的ID？

编辑：

我的存储过程：

CREATE PROCEDURE addGetID ( @PersonnelID INT, @ShiftWorkID BIGINT, @EntranceDateTime DATETIME, @WorkflowStateRelationID BIGINT ) AS INSERT INTO DynamicDataEntrances VALUES (@PersonnelID, @ShiftWorkID, @EntranceDateTime, @WorkflowStateRelationID) SELECT @@IDENTITY

我的代码行：

string dt = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"); SqlParameter PersonnelID = new SqlParameter("@PersonnelID", 1); SqlParameter ShiftWorkID = new SqlParameter("@ShiftWorkID", 2); SqlParameter EntranceDateTime = new SqlParameter("@EntranceDateTime", dt); SqlParameter WorkflowStateRelationID = new SqlParameter("@WorkflowStateRelationID ", db.WorkflowStateRelations.Where(wsr => wsr.WorkflowID == dp.WorkflowID).Select(x => x.ID).FirstOrDefault()); SqlParameter returnValue = new SqlParameter(); returnValue.Direction = ParameterDirection.ReturnValue; // try 1 db.Database.ExecuteSqlCommand("addGetID(@PersonnelID, @ShiftWorkID, @EntranceDateTime, @WorkflowStateRelationID )", PersonnelID, ShiftWorkID, EntranceDateTime, WorkflowStateRelationID, returnValue); // try 2 db.Database.ExecuteSqlCommand("addGetID @PersonnelID @ShiftWorkID @EntranceDateTime @WorkflowStateRelationID ", PersonnelID, ShiftWorkID, EntranceDateTime, WorkflowStateRelationID, returnValue); // try 3 db.Database.ExecuteSqlCommand("exec addGetID @PersonnelID={0} @ShiftWorkID={1} @EntranceDateTime={2} @WorkflowStateRelationID={3} ", PersonnelID, ShiftWorkID, EntranceDateTime, WorkflowStateRelationID, returnValue); var id = returnValue.Value;

I do insertion with following in my ASP.NET MVC3 project:

var query = "INSERT INTO MyTable VALUES(//some values)"; db.Database.ExecuteSqlCommand(query);

Is there an easy way to get the inserted row's ID?

EDIT:

My stored procedure:

CREATE PROCEDURE addGetID ( @PersonnelID INT, @ShiftWorkID BIGINT, @EntranceDateTime DATETIME, @WorkflowStateRelationID BIGINT ) AS INSERT INTO DynamicDataEntrances VALUES (@PersonnelID, @ShiftWorkID, @EntranceDateTime, @WorkflowStateRelationID) SELECT @@IDENTITY

My code lines:

string dt = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"); SqlParameter PersonnelID = new SqlParameter("@PersonnelID", 1); SqlParameter ShiftWorkID = new SqlParameter("@ShiftWorkID", 2); SqlParameter EntranceDateTime = new SqlParameter("@EntranceDateTime", dt); SqlParameter WorkflowStateRelationID = new SqlParameter("@WorkflowStateRelationID ", db.WorkflowStateRelations.Where(wsr => wsr.WorkflowID == dp.WorkflowID).Select(x => x.ID).FirstOrDefault()); SqlParameter returnValue = new SqlParameter(); returnValue.Direction = ParameterDirection.ReturnValue; // try 1 db.Database.ExecuteSqlCommand("addGetID(@PersonnelID, @ShiftWorkID, @EntranceDateTime, @WorkflowStateRelationID )", PersonnelID, ShiftWorkID, EntranceDateTime, WorkflowStateRelationID, returnValue); // try 2 db.Database.ExecuteSqlCommand("addGetID @PersonnelID @ShiftWorkID @EntranceDateTime @WorkflowStateRelationID ", PersonnelID, ShiftWorkID, EntranceDateTime, WorkflowStateRelationID, returnValue); // try 3 db.Database.ExecuteSqlCommand("exec addGetID @PersonnelID={0} @ShiftWorkID={1} @EntranceDateTime={2} @WorkflowStateRelationID={3} ", PersonnelID, ShiftWorkID, EntranceDateTime, WorkflowStateRelationID, returnValue); var id = returnValue.Value;

最满意答案

我同意[scartag] [1]。但要分两部分回答你的问题。

如果您要使用直接sql，那么要做的是创建一个返回@@IDENTITY值的存储过程。然后使用ParameterDirection.ReturnValue执行Sp并使用该值。

像这样：

SqlParameter entityField = new SqlParameter("@firstName", "Frank"); SqlParameter returnValue = new SqlParameter(); returnValue.Direction = ParameterDirection.ReturnValue; db.Database.ExecuteSqlCommand("sp_ByPassEFAndInsertEntity(@firstName)", entityField, returnValue); var id = returnValue.Value;

或者只使用EF。创建并初始化您的Entity ，将其添加到适用的DbSet并调用savechanges。您的Entity实例的Id列将使用数据库生成的值进行更新。

编辑2：我将第一个解决方案应用于我的本地数据库，并且可以让它给出返回值。经过一些研究，我得到了一个回报值。我有一个带有Blog表的本地数据库。我创建了一个addBlog SP，这就是你如何获得返回值 - 但是你必须使用SqlQuery而不是使用ExecSqlCommand。

这是我的SP：

ALTER PROCEDURE [dbo].[addBlog] @BlogName varchar(50), @CreateDateTime datetime AS BEGIN insert into Blog (Name, DateTimeCreated) values (@BlogName, @CreateDateTime) declare @return int set @return = @@identity select @return END

和我的EF代码：

using (var db = new StackOverflowEntities()) { SqlParameter blogName = new SqlParameter("BlogName", "New Name 2"); SqlParameter createDate = new SqlParameter("CreateDate", DateTime.Now); int? newIdentityValue = db.Database.SqlQuery<Int32>("addBlog @BlogName, @CreateDate", blogName, createDate).FirstOrDefault(); Debug.WriteLine(newIdentityValue); }

I agree with [scartag][1] on this one. But to answer your question in 2 parts.

If you're going to use direct sql then the thing to do is create a stored proc that returns the @@IDENTITY value. Then you exec your Sp with a parameter of ParameterDirection.ReturnValue and use that value.

Like such:

SqlParameter entityField = new SqlParameter("@firstName", "Frank"); SqlParameter returnValue = new SqlParameter(); returnValue.Direction = ParameterDirection.ReturnValue; db.Database.ExecuteSqlCommand("sp_ByPassEFAndInsertEntity(@firstName)", entityField, returnValue); var id = returnValue.Value;

Or just use EF. Create and initialize your Entity, add it to the applicable DbSet and call savechanges. Your Entity instance's Id column will be updated with the value generated by the database.

EDIT 2: I applied the first solution to a local db of mine and could get it to give a return value either. After some research I was able to get a return value. I have a local db with a Blog table. I created an addBlog SP and this is how you can get a return value - but instead of using ExecSqlCommand you must use SqlQuery.

Here's my SP:

ALTER PROCEDURE [dbo].[addBlog] @BlogName varchar(50), @CreateDateTime datetime AS BEGIN insert into Blog (Name, DateTimeCreated) values (@BlogName, @CreateDateTime) declare @return int set @return = @@identity select @return END

and my EF code:

using (var db = new StackOverflowEntities()) { SqlParameter blogName = new SqlParameter("BlogName", "New Name 2"); SqlParameter createDate = new SqlParameter("CreateDate", DateTime.Now); int? newIdentityValue = db.Database.SqlQuery<Int32>("addBlog @BlogName, @CreateDate", blogName, createDate).FirstOrDefault(); Debug.WriteLine(newIdentityValue); }

更多推荐