SqlCommandBuilder是否可以在插入时检索IDENTITY列的值（而无需使用存储过程）？

Question

FYI：我正在运行dotnet 3.5 SP1

我正在尝试在使用SqlDataAdapter和SqlCommandBuilder执行更新后，从我的数据集中检索标识列的值。在执行SqlDataAdapter.Update(myDataset)之后，我想能够读取myDataset.tables(0).Rows(0)("ID")的自动分配值，但它是System.DBNull（尽管已插入该行）。

（注意：我不想显式编写新的存储过程来完成此操作！）

经常发布的一种方法http://forums.asp.net/t/951025.aspx修改SqlDataAdapter.InsertCommand和UpdatedRowSource，如下所示：

SqlDataAdapter.InsertCommand.CommandText += "; SELECT MyTableID = SCOPE_IDENTITY()"
InsertCommand.UpdatedRowSource = UpdateRowSource.FirstReturnedRecord

显然，这似乎在过去对许多人有效，但对我无效。

另一种技术：http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=619031&SiteID=1 对我也不起作用，因为执行SqlDataAdapter.Update后，SqlDataAdapter.InsertCommand.Parameters集合会重置为原始状态（丢失额外添加的参数）。

有人知道答案吗？？？

- tbone

---

仅供参考：Code Project文章“使用SCOPE_IDENTITY与CommandBuilder和DataAdapter”是帮助我的解决方案。（我不是该文章的作者）。 - Uwe Keim

我知道这已经很老了，但在vs 2015中仍然存在很多错误。我遇到了同样的问题，并且已经使用了上面文章中的代码。对我来说，解决方法是将“set ID to SCOPE_IDENTITY()”重命名为“SET SCOPE_ID = SCOPE_IDENTITY()”。是的，我知道实际上没有什么区别，但现在它又可以正常工作了，原因不明。 - GuidoG

Answer 1

这是我之前遇到的一个问题，bug似乎在于当你调用da.Update(ds);时，插入命令的参数数组会被重置为从命令生成器创建的初始列表，这将删除你添加的输出参数以获取标识符。

解决方法是创建一个新的dataAdapter并复制命令，然后使用这个新的dataAdapter来执行da.update(ds);

像这样：

SqlDataAdapter da = new SqlDataAdapter("select Top 0 " + GetTableSelectList(dt) + 
"FROM " + tableName,_sqlConnectString);
SqlCommandBuilder custCB = new SqlCommandBuilder(da);
custCB.QuotePrefix = "[";
custCB.QuoteSuffix = "]";
da.TableMappings.Add("Table", dt.TableName);

da.UpdateCommand = custCB.GetUpdateCommand();
da.InsertCommand = custCB.GetInsertCommand();
da.DeleteCommand = custCB.GetDeleteCommand();

da.InsertCommand.CommandText = String.Concat(da.InsertCommand.CommandText, 
"; SELECT ",GetTableSelectList(dt)," From ", tableName, 
" where ",pKeyName,"=SCOPE_IDENTITY()");

SqlParameter identParam = new SqlParameter("@Identity", SqlDbType.BigInt, 0, pKeyName);
identParam.Direction = ParameterDirection.Output;
da.InsertCommand.Parameters.Add(identParam);

da.InsertCommand.UpdatedRowSource = UpdateRowSource.FirstReturnedRecord;

//new adaptor for performing the update                     
SqlDataAdapter daAutoNum = new SqlDataAdapter();
daAutoNum.DeleteCommand = da.DeleteCommand;
daAutoNum.InsertCommand = da.InsertCommand;
daAutoNum.UpdateCommand = da.UpdateCommand;

daAutoNum.Update(dt);

- Ray Fitzharris

---

谢谢！这个奇怪的解决方法可行。所以，故事的寓意就是，如果你想要调整命令生成器中的命令并在数据适配器中使用它们，那么请为命令生成器创建一个单独的数据适配器。在比较两个'完全相同'的命令内部后发现，其中一个能正常工作而另一个不能，我发现内部的IsDirty对于无法工作的命令仍然为false。 - Mark Whitfeld

该情况仍然存在于NET.Core 2.2中。如果插入语句是通过sqlCommandBuilder生成的，那么在使用字符串操作操纵SQL语句后，调用dataAdapter.Update(table/rows)将会被还原。 这可以在sql-profiler和调试模式下通过观察CommandText属性来观察到。提示：如果您想在插入后从数据库检索多个列，请在插入语句中使用OUTPUT INSERTED.*。这将返回完整的行。 - Karl

Answer 2

我在这里找到了适合我的答案：http://www.dotnetmonster.com/Uwe/Forum.aspx/dotnet-ado-net/4933/Have-soln-but-need-understanding-return-IDENTITY-issue 下面是代码（来自该网站，归功于Girish）：

//_dataCommand is an instance of SqlDataAdapter
//connection is an instance of ConnectionProvider which has a property called DBConnection of type SqlConnection
//_dataTable is an instance of DataTable

SqlCommandBuilder bldr = new SqlCommandBuilder(_dataCommand);
SqlCommand cmdInsert = new SqlCommand(bldr.GetInsertCommand().CommandText, connection.DBConnection);
cmdInsert.CommandText += ";Select SCOPE_IDENTITY() as id";

SqlParameter[] aParams = new
SqlParameter[bldr.GetInsertCommand().Parameters.Count];
bldr.GetInsertCommand().Parameters.CopyTo(aParams, 0);
bldr.GetInsertCommand().Parameters.Clear();

for(int i=0 ; i < aParams.Length; i++)
{
 cmdInsert.Parameters.Add(aParams[i]);
}

_dataCommand.InsertCommand = cmdInsert;
_dataCommand.InsertCommand.UpdatedRowSource = UpdateRowSource.FirstReturnedRecord;
_dataCommand.Update(_dataTable);

- Mladen Mihajlovic

---

这个可行！只需确保在“Select SCOPE_IDENTITY() as id”中替换id为您表的主键字段名称。 - Marquez

Answer 3

我的建议是配置一个MissingSchemaAction：

SqlCommandBuilder commandBuilder = new SqlCommandBuilder(myDataAdapter);
myDataAdapter.MissingSchemaAction = MissingSchemaAction.AddWithKey;

这让我可以在插入后检索主键（如果它是标识或自动编号）。

祝好运。

- Rob Vermeulen

---

非常好用，没有与命令搞混。我想知道为什么我在其他地方找不到这个黄金提示？非常感谢！ - M Granja

Answer 4

插入命令可以通过输出参数或第一个返回的记录（或两者兼有）使用UpdatedRowSource属性来更新插入的记录...

InsertCommand.UpdatedRowSource = UpdateRowSource.Both;

如果你想使用存储过程，那么你的工作就完成了。但是你想使用原始命令（也称为命令生成器的输出），这不允许 a) 输出参数或 b) 返回记录。为什么呢？对于 a)，你的 InsertCommand 将如下所示...

INSERT INTO [SomeTable] ([Name]) VALUES (@Name)

无法在命令中输入输出参数，那么b)怎么办呢？ 不幸的是，DataAdapter通过调用命令ExecuteNonQuery方法来执行Insert命令。这不会返回任何记录，因此适配器无法更新插入的记录。

因此，您需要使用存储过程或放弃使用DataAdapter。

- dbugger

---

DataAdapter通过调用命令的ExecuteNonQuery方法执行插入命令。我想这可以解释我看到的行为，但其他人如何使我发布的示例技术工作呢？UpdatedRowSource属性的目的是什么？ - tbone

我看到的所有例子都使用存储过程。UpdatedRowSource与存储过程一起工作得很好。那些没有使用存储过程的人也没有使用DataAdapters。 - dbugger

Answer 5

实际上，这对我起作用：

SqlDataAdapter.InsertCommand.CommandText += "; SELECT MyTableID = SCOPE_IDENTITY()" InsertCommand.UpdatedRowSource = UpdateRowSource.OutputParameters;

干杯。

- boutblock

Answer 6

我曾经遇到过同样的问题。当我克隆由命令生成器生成的命令时，问题就解决了。似乎即使您更改插入命令的commandText，它仍会获取由Commandbuilder生成的命令...这是我用来克隆命令的代码...

        private static void CloneBuilderCommand(System.Data.Common.DbCommand toClone,System.Data.Common.DbCommand repository)
    {
        repository.CommandText = toClone.CommandText;
        //Copying parameters
        if (toClone.Parameters.Count == 0) return;//No parameters to clone? go away!
        System.Data.Common.DbParameter[] parametersArray= new System.Data.Common.DbParameter[toClone.Parameters.Count];
        toClone.Parameters.CopyTo(parametersArray, 0);
        toClone.Parameters.Clear();//Removing association before link to the repository one
        repository.Parameters.AddRange(parametersArray);            
    }

- Pedrao

Answer 7

如果您只想(a)向表中插入一条记录，(b)使用DataSet，且(c)不使用存储过程，则可以按照以下步骤操作：

1. 创建您的dataAdapter，但在select语句中添加WHERE 1=0，以便您不必下载整个表 - 可选步骤，可提高性能。

2. 创建自定义的INSERT语句，包括scope identity select语句和一个输出参数。

3. 进行正常处理，填充数据集，添加记录并保存表更新。

4. 现在应该能够直接从参数中提取标识。

示例：

    '-- post a new entry and return the column number
    ' get the table stucture
    Dim ds As DataSet = New DataSet()
    Dim da As SqlDataAdapter = New SqlDataAdapter(String.Concat("SELECT * FROM [", fileRegisterSchemaName, "].[", fileRegisterTableName, "] WHERE 1=0"), sqlConnectionString)
    Dim cb As SqlCommandBuilder = New SqlCommandBuilder(da)

    ' since we want the identity column back (FileID), we need to write our own INSERT statement
    da.InsertCommand = New SqlCommand(String.Concat("INSERT INTO [", fileRegisterSchemaName, "].[", fileRegisterTableName, "] (FileName, [User], [Date], [Table]) VALUES (@FileName, @User, @Date, @Table); SELECT @FileID = SCOPE_IDENTITY();"))
    da.InsertCommand.UpdatedRowSource = UpdateRowSource.FirstReturnedRecord
    With da.InsertCommand.Parameters
        .Add("@FileName", SqlDbType.VarChar, 1024, "FileName")
        .Add("@User", SqlDbType.VarChar, 24, "User")
        .Add("@Date", SqlDbType.DateTime, 0, "Date")
        .Add("@Table", SqlDbType.VarChar, 128, "FileName")
        ' allow the @FileID to be returned back to us
        .Add("@FileID", SqlDbType.Int, 0, "FileID")
        .Item("@FileID").Direction = ParameterDirection.Output
    End With

    ' copy the table structure from the server and create a reference to the table(dt)
    da.Fill(ds, fileRegisterTableName)
    Dim dt As DataTable = ds.Tables(fileRegisterTableName)

    ' add a new record
    Dim dr As DataRow = dt.NewRow()
    dr("FileName") = fileName
    dr("User") = String.Concat(Environment.UserDomainName, "\", Environment.UserName)
    dr("Date") = DateTime.Now()
    dr("Table") = targetTableName
    dt.Rows.Add(dr)

    ' save the new record
    da.Update(dt)

    ' return the FileID (Identity)
    Return da.InsertCommand.Parameters("@FileID").Value

但是这样做会变得比较冗长，与下面的方法实现同样的功能...

' add the file record
    Dim sqlCmd As SqlCommand = New SqlCommand(String.Concat("INSERT INTO [", fileRegisterSchemaName, "].[", fileRegisterTableName, "] (FileName, [User], [Date], [Table]) VALUES (@FileName, @User, @Date, @Table); SELECT SCOPE_IDENTITY();"), New SqlConnection(sqlConnectionString))
    With sqlCmd.Parameters
        .AddWithValue("@FileName", fileName)
        .AddWithValue("@User", String.Concat(Environment.UserDomainName, "\", Environment.UserName))
        .AddWithValue("@Date", DateTime.Now())
        .AddWithValue("@Table", targetTableName)
    End With
    sqlCmd.Connection.Open()
    Return sqlCmd.ExecuteScalar

- Jay Kidd

Answer 8

对我来说问题在于代码放置的位置。

在RowUpdating事件处理程序中添加代码，类似于以下内容：

void dataSet_RowUpdating(object sender, SqlRowUpdatingEventArgs e)
{
    if (e.StatementType == StatementType.Insert)
    {
        e.Command.CommandText += "; SELECT ID = SCOPE_IDENTITY()";
        e.Command.UpdatedRowSource = UpdateRowSource.FirstReturnedRecord;
     }
}

- David

Answer 9

还有一种方法，只使用一个命令对象。

Dim sb As New StringBuilder
sb.Append(" Insert into ")
sb.Append(tbl)
sb.Append(" ")
sb.Append(cnames)
sb.Append(" values ")
sb.Append(cvals)
sb.Append(";Select SCOPE_IDENTITY() as id") 'add identity selection

Dim sql As String = sb.ToString

Dim cmd As System.Data.Common.DbCommand = connection.CreateCommand
cmd.Connection = connection
cmd.CommandText = sql
cmd.CommandType = CommandType.Text
cmd.UpdatedRowSource = UpdateRowSource.FirstReturnedRecord

'retrieve the new identity value, and update the object
Dim dec as decimal = CType(cmd.ExecuteScalar, Decimal)

- user117499

Answer 10

如果其他方法对您没有用，.Net提供的工具（如SqlDataAdapter）在灵活性方面并没有太多提供。通常需要将其提升到下一个级别并开始手动处理。存储过程是继续使用SqlDataAdapter的一种方式。否则，您需要转向另一个数据访问工具，因为.Net数据库有限，因为它们被设计为简单。如果您的模型与其视觉不符，则必须在某个点/级别上编写自己的代码。

- Mufasa