使用 SqlCeCommand 插入后选择 SCOPE_IDENTITY [英] Select SCOPE_IDENTITY after insert with SqlCeCommand

问题描述

我的程序有在线和离线模式.

My program has an online and offline mode.

• 它使用 C# SqlCommand 类在线连接到 SQL Server 实例.
• 离线它使用 C# SqlCeCommand 类连接到本地 .SDF 文件.

• Online it connects to a SQL Server instance using the C# SqlCommand classes.
• Offline it connects to a local .SDF file using the C# SqlCeCommand classes.

现在我想做以下在在线模式下有效的操作

Now I want to do the following which works when in online mode

GetSqlCommand("insert into my_table (col1) values (@c1); SELECT SCOPE_IDENTITY()", conn))

所以我插入一条记录并使用 SELECT SCOPE_IDENTITY() 取回该新记录的 ID.

So I insert a record a get back the ID of that new record with SELECT SCOPE_IDENTITY().

但是在离线模式下使用 SQL Server CE 时，它会引发错误.有没有办法在不运行单独查询的情况下使用 SQL Server CE 从插入中取回 ID?

But when in offline mode using SQL Server CE, it throws an error. Is there a way to get back ID from the insert with SQL Server CE without running a separate query?

推荐答案

SQL Server CE 不支持单个命令中的多个查询，尝试如下

SQL Server CE doesn't support multiple queries in single command, try as below

SqlCeCommand  cmd = new SqlCeCommand("insert into my_table (col1) values (@c1)", conn);
//set paremeter values 
//execute insert 
cmd.ExecuteNonQuery();
//now change the sql statment to take identity 
cmd.CommandText = "SELECT @@IDENTITY";
int id = Convert.ToInt32(cmd.ExecuteScalar());

https://stackoverflow.com/a/6480017/2558060

这篇关于使用 SqlCeCommand 插入后选择 SCOPE_IDENTITY的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！