Question

如何在dapper中获取刚插入行的ID

Answer 1

dapper 返回Sql server 自增长ID 标识列SCOPE_IDENTITY
SQL获取刚插入的记录的自动增长列ID的值

CREATE TABLE [dbo].[MesTest](
[ID] [int] IDENTITY(1,1) NOT NULL,
[Msg] [nvarchar](500) NULL
) ON [PRIMARY]

[TestMethod]
public void TestMethod1()
{
var sql = "INSERT INTO [MesTest] ([Msg]) VALUES('ss');SELECT @id=SCOPE_IDENTITY()";
using (var db = Database.DbService)
{
var p = new DynamicParameters();
p.Add("@ID", dbType: DbType.Int32, direction: ParameterDirection.Output);
var i = db.Execute(sql,p);
var i1 = p.Get<int>("@ID");

Assert.Inconclusive(i1.ToString());
}
}