ASP.NET MVC获取插入项目的ID，由ExecuteSqlCommand [英] ASP.NET MVC Get the ID of Inserted Item by ExecuteSqlCommand
问题描述
我插在我的ASP.NET MVC3项目如下：
VAR的查询=INSERT INTO MyTable的VALUES（//一些值）;
db.Database.ExecuteSqlCommand（查询）;
有没有一种简单的方法来获取插入行的ID？
编辑：
我的存储过程：
CREATE PROCEDURE addGetID
（
@PersonnelID INT，
@ShiftWorkID BIGINT，
@EntranceDateTime DATETIME，
@WorkflowStateRelationID BIGINT
）
如
INSERT INTO DynamicDataEntrances VALUES（@PersonnelID，@ShiftWorkID，@EntranceDateTime，@WorkflowStateRelationID）
SELECT @@ IDENTITY
我的code线：
串DT = DateTime.Now.ToString（YYYY-MM-DD HH：MM：SS）;
的SqlParameter PersonnelID =新的SqlParameter（@ PersonnelID，1）;
的SqlParameter ShiftWorkID =新的SqlParameter（@ ShiftWorkID，2）;
的SqlParameter EntranceDateTime =新的SqlParameter（@ EntranceDateTime，DT）;
的SqlParameter WorkflowStateRelationID =新的SqlParameter（@ WorkflowStateRelationID，db.WorkflowStateRelations.Where（WSR =＆GT; wsr.WorkflowID == dp.WorkflowID）。选择（X =＆GT; x.ID）.FirstOrDefault（））;
的SqlParameter的returnValue =新的SqlParameter（）;
returnValue.Direction = ParameterDirection.ReturnValue;
//尝试1
db.Database.ExecuteSqlCommand（addGetID（@PersonnelID，@ShiftWorkID，@EntranceDateTime，@WorkflowStateRelationID），PersonnelID，ShiftWorkID，EntranceDateTime，WorkflowStateRelationID，的returnValue）;
//尝试2
db.Database.ExecuteSqlCommand（addGetID @PersonnelID @ShiftWorkID @EntranceDateTime @WorkflowStateRelationID，PersonnelID，ShiftWorkID，EntranceDateTime，WorkflowStateRelationID，的returnValue）;
//尝试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 的参数exec的你的SP，并使用该值。
像这样的：
的SqlParameter entityField =新的SqlParameter（@名字，弗兰克）;
的SqlParameter的returnValue =新的SqlParameter（）;
returnValue.Direction = ParameterDirection.ReturnValue;
db.Database.ExecuteSqlCommand（sp_ByPassEFAndInsertEntity（@firstName），
entityField，的returnValue）;
VAR ID = returnValue.Value;
或者只是使用EF。创建和初始化你的实体，将其添加到适用 DbSet 和调用的SaveChanges。你的实体实例的Id列将与由数据库生成的值了。
编辑2：我申请了第一个解决方案，我的一个本地数据库，并能得到它给出一个返回值无论是。经过一番研究，我能得到一个返回值。我有一个本地数据库与博客表。我创建了一个addBlog SP，这是你可以得到一个返回值 - 但不是使用ExecSqlCommand您必须使用SqlQuery类
下面是我的SP：
ALTER PROCEDURE [DBO]。[addBlog]
@BlogName的varchar（50），
@CreateDateTime日期时间
如
开始
插入博客（名称，DateTimeCreated）值（@BlogName，@CreateDateTime）
声明@返回INT
设置@return = @@身份
选择@return
结束
和我的EF code：
使用（VAR DB =新StackOverflowEntities（））{
的SqlParameter BLOGNAME =新的SqlParameter（BLOGNAME，新名称2）;
的SqlParameter CREATEDATE =新的SqlParameter（CREATEDATE，DateTime.Now）;
诠释？ newIdentityValue = db.Database.SqlQuery＆LT;的Int32＆GT;（addBlog @BlogName，@CreateDate，BLOGNAME，CREATEDATE）.FirstOrDefault（）;
的Debug.WriteLine（newIdentityValue）;
}
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;
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);
}
这篇关于ASP.NET MVC获取插入项目的ID，由ExecuteSqlCommand的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
