在SQL服务器上插入或更新并获取新创建的id的解决方案

Question

是否可以使用以下约束执行插入或更新操作:

1. Dapper在项目
中使用
2. 主键是自动递增的
3. 新插入的数据可能没有的PK值(或者有0的值)
4. 数据需要具有新插入行上的PK值。
5. 查询正在生成过程中，并且泛化它将是更好的

例如:

int newId = db.QueryValue<int>( <<insert or update>>, someData );

我读过不同的解决方案，最好的解决方案似乎是这个:

merge tablename as target
using (values ('new value', 'different value'))
    as source (field1, field2)
    on target.idfield = 7
when matched then
    update
    set field1 = source.field1,
        field2 = source.field2,
        ...
when not matched then
    insert ( idfield, field1, field2, ... )
    values ( 7,  source.field1, source.field2, ... )

,

1. 它似乎在第三个约束和
上失败了
2. 不保证返回新生成的id。

由于第5个约束(或首选项)，存储过程看起来过于复杂。

可能的解决方案是什么?谢谢!

Answer 1

如果您的表有一个自动递增字段，您不能在插入记录时为该字段赋值。好吧，你可以，但这通常是个坏主意:)

使用T-SQL MERGE语句，您可以将所有值放入源表中，包括默认的无效标识值，然后将插入子句写入:

when not matched then
    insert (field1, field2, ...)
    values (source.field1, source.field2, ...)

:并使用output子句获取插入的标识值:

OUTPUT inserted.idfield

---

也就是说，我认为您可能会使SQL代码生成变得复杂一些，特别是对于具有许多字段的表。生成不同的UPDATE和INSERT查询通常效果更好。特别是如果你有一些方法可以跟踪对象的更改，这样你就只能更新更改的字段。

假设你在MS SQL上工作，你可以在INSERT语句之后使用SCOPE_IDENTITY()函数来获取复合语句中记录的标识字段的值:

INSERT INTO tablename(field1, field2, ...)
VALUES('field1value', 'field2value', ...);
SELECT CAST(SCOPE_IDENTITY() AS INT) ident;

当您执行此SQL语句时，您将返回一个结果集，其中包含插入的标识在单列中。然后你的db.QueryValue<int>调用将返回你所追求的值。

对于标准的整型自增字段，以上是可以的。对于其他字段类型，或者对于更一般的情况，尝试将SCOPE_IDENTITY()结果转换为VARCHAR(MAX)，并将结果字符串值解析为标识列期望的任何类型——GUID等。

一般情况下，在db类中尝试以下操作:

public string InsertWithID(string insertQuery, params object[] parms)
{
    string query = insertQuery + "nSELECT CAST(SCOPE_IDENTITY() AS VARCHAR(MAX)) ident;n";
    return this.QueryValue<string>(insertQuery, parms);
}

和/或:

public int InsertWithIntID(string insertQuery, params object[] parms)
{
    string query = insertQuery + "nSELECT CAST(SCOPE_IDENTITY() AS INT) ident;n";
    return this.QueryValue<int>(query, parms);
}

这样，您就可以准备插入查询并调用适当的InsertWithID方法来获得结果标识值。如果运气好的话，这应该可以满足你的第五个约束:)