检索标识或 Autonumber 值 (ADO.NET)

关系数据库中的主键是始终包含唯一值的列或列的组合。 知道主键值使您可以定位包含该值的行。 关系数据库引擎（如 SQL Server、Oracle 和 Microsoft Access/Jet）支持创建可指定为主键的自动递增列。 在向表中添加行时，服务器会生成这些值。 在 SQL Server 中可以设置列的标识属性，在 Oracle 中可以创建序列，在 Microsoft Access 中可以创建 AutoNumber 列。

通过将 AutoIncrement 属性设置为 True，也可以使用 DataColumn 来生成自动递增值。 但是，如果多个客户端应用程序独立生成自动递增值，最后会使 DataTable 的单独实例中出现重复的值。 让服务器生成自动递增值，以允许每个用户为每个插入的行检索生成的值，从而消除潜在的冲突。

在调用 DataAdapter 的 Update 方法期间，数据库可以将数据以输出参数的形式或以与 INSERT 语句同一批执行的 SELECT 语句返回的结果集的第一个记录的形式，发回到您的 ADO.NET 应用程序。 ADO.NET 可以检索这些值并在要更新的 DataRow 中更新相应的列。

某些数据库引擎（如 Microsoft Access Jet 数据库引擎）不支持输出参数，不能在单个批处理中处理多个语句。 当使用 Jet 数据库引擎时，可以通过在 DataAdapter 的 RowUpdated 事件的事件处理程序中执行单独的 SELECT 命令来检索为插入行生成的新 AutoNumber 值。

说明：

使用自动递增值的替代方法是使用 Guid 对象的 NewGuid 方法在客户端计算机上生成一个 GUID（即全局唯一标识符），插入每个新行时可以将此标识符复制到服务器。 NewGuid 方法使用算法生成一个 16 字节的二进制值，该算法不生成重复值的概率很高。 在 SQL Server 数据库中，GUID 存储在 uniqueidentifier 列中，SQL Server 可以使用 Transact-SQL NEWID() 函数自动生成此列。 将 GUID 用作主键会对性能产生负面影响。 SQL Server 2005 引入对 NEWSEQUENTIALID() 函数的支持，此函数生成一个连续 GUID，虽然不能保证此 GUID 全局唯一，却可以更有效地进行索引。

 检索 SQL Server“标识”列的值

使用 Microsoft SQL Server 时，可以创建通过输出参数返回插入行标识值的存储过程。 下表说明 SQL Server 中可用来检索标识列值的三个 Transact-SQL 函数。

函数

说明

SCOPE_IDENTITY

返回当前执行范围内的最后一个标识值。 对于多数方案，建议使用 SCOPE_IDENTITY。

@@IDENTITY

包含当前会话中任何表生成的最后一个标识值。 @@IDENTITY 受触发器的影响，可能不会返回预期的标识值。

IDENT_CURRENT

返回在任何会话中以及任何范围内为特定表生成的最后一个标识值。

下面的存储过程演示如何向 Categories 表中插入行并使用输出参数返回由 Transact-SQL SCOPE_IDENTITY() 函数生成的新标识值。

复制代码

CREATE PROCEDURE dbo.InsertCategory  @CategoryName nvarchar(15),  @Identity int OUTASINSERT INTO Categories (CategoryName) VALUES(@CategoryName)SET @Identity = SCOPE_IDENTITY()

然后可以将此存储过程指定为 SqlDataAdapter 对象的 InsertCommand 的源。 InsertCommand 的 CommandType 属性必须设置为 StoredProcedure。 可以通过创建一个 ParameterDirection 为 Output 的 SqlParameter 来检索标识输出。 如果将插入命令的 UpdatedRowSource 属性设置为 UpdateRowSource.OutputParameters 或 UpdateRowSource.Both，则在处理 InsertCommand 时，会返回自动递增的标识值并将其置于当前行的 CategoryID 列中。

如果插入命令执行的是同时包括 INSERT 语句和可返回新标识值的 SELECT 语句的批处理，则可以通过将插入命令的 UpdatedRowSource 属性设置为 UpdateRowSource.FirstReturnedRecord 来检索这个新值。

Visual Basic

复制代码

Private Sub RetrieveIdentity(ByVal connectionString As String)    Using connection As SqlConnection = New SqlConnection( _       connectionString)        ' Create a SqlDataAdapter based on a SELECT query.        Dim adapter As SqlDataAdapter = New SqlDataAdapter( _           "SELECT CategoryID, CategoryName FROM dbo.Categories", _           connection)        ' Create the SqlCommand to execute the stored procedure.         adapter.InsertCommand = New SqlCommand("dbo.InsertCategory", _           connection)        adapter.InsertCommand.CommandType = CommandType.StoredProcedure        ' Add the parameter for the CategoryName. Specifying the        ' ParameterDirection for an input parameter is not required.        adapter.InsertCommand.Parameters.Add( _          "@CategoryName", SqlDbType.NVarChar, 15, "CategoryName")        ' Add the SqlParameter to retrieve the new identity value.        ' Specify the ParameterDirection as Output.        Dim parameter As SqlParameter = _           adapter.InsertCommand.Parameters.Add( _          "@Identity", SqlDbType.Int, 0, "CategoryID")        parameter.Direction = ParameterDirection.Output        ' Create a DataTable and fill it.        Dim categories As DataTable = New DataTable        adapter.Fill(categories)        ' Add a new row.        Dim newRow As DataRow = categories.NewRow()        newRow("CategoryName") = "New Category"        categories.Rows.Add(newRow)        ' Update the database.        adapter.Update(categories)        Console.WriteLine("List All Rows:")        Dim row As DataRow        For Each row In categories.Rows            Console.WriteLine("{0}: {1}", row(0), row(1))        Next    End UsingEnd Sub

C#

复制代码

private static void RetrieveIdentity(string connectionString){    using (SqlConnection connection =               new SqlConnection(connectionString))    {        // Create a SqlDataAdapter based on a SELECT query.        SqlDataAdapter adapter =            new SqlDataAdapter(            "SELECT CategoryID, CategoryName FROM dbo.Categories",            connection);        //Create the SqlCommand to execute the stored procedure.        adapter.InsertCommand = new SqlCommand("dbo.InsertCategory",             connection);        adapter.InsertCommand.CommandType = CommandType.StoredProcedure;        // Add the parameter for the CategoryName. Specifying the        // ParameterDirection for an input parameter is not required.        adapter.InsertCommand.Parameters.Add(           new SqlParameter("@CategoryName", SqlDbType.NVarChar, 15,           "CategoryName"));        // Add the SqlParameter to retrieve the new identity value.        // Specify the ParameterDirection as Output.        SqlParameter parameter =             adapter.InsertCommand.Parameters.Add(            "@Identity", SqlDbType.Int, 0, "CategoryID");        parameter.Direction = ParameterDirection.Output;        // Create a DataTable and fill it.        DataTable categories = new DataTable();        adapter.Fill(categories);        // Add a new row.         DataRow newRow = categories.NewRow();        newRow["CategoryName"] = "New Category";        categories.Rows.Add(newRow);        adapter.Update(categories);        Console.WriteLine("List All Rows:");        foreach (DataRow row in categories.Rows)        {            {                Console.WriteLine("{0}: {1}", row[0], row[1]);            }        }    }}

 合并新标识值

常见的方案是调用 DataTable 的 GetChanges 方法来创建仅包含已更改行的副本，并在调用 DataAdapter 的 Update 方法时使用这个新副本。 这在需要将已更改的行封送到执行更新的单独组件时十分有用。 更新后，此副本会包含随后必须合并回原始 DataTable 中的新标识值。 新标识值很可能会不同于 DataTable 中的原始值。 若要完成合并，必须保留副本中 AutoIncrement 列的原始值，以便能够在原始 DataTable 中定位和更新现有行，而不是追加包含新标识值的新行。 但在默认情况下，在调用 DataAdapter 的 Update 方法后，这些原始值会丢失，这是因为会对每个已更新的 DataRow 隐式调用 AcceptChanges。

在 DataAdapter 更新期间，可以采用两种方法保留 DataRow 中 DataColumn 的原始值：

• 第一种保留原始值的方法是将 DataAdapter 的 AcceptChangesDuringUpdate 属性设置为 false。 这会影响将要更新的 DataTable 中的每个 DataRow。 有关更多信息和代码示例，请参见 AcceptChangesDuringUpdate。

• 第二种方法是在 DataAdapter 的 RowUpdated 事件处理程序中编写代码，将 Status 设置为 SkipCurrentRow。 DataRow 将会更新，但每个 DataColumn 的原始值会保留。 使用此方法可以对某些行保留原始值而不对其他行保留原始值。 例如，通过先检查 StatementType 然后仅针对 StatementType 为 Insert 的行将 Status 设置为 SkipCurrentRow，代码可以为已添加的行保留原始值，而不为已编辑或已删除的行保留原始值。

在 DataAdapter 更新期间使用这些方法中的任一方法保留 DataRow 中的原始值时，ADO.NET 会执行一系列操作将 DataRow 的当前值设置为由输出参数返回的新值或由结果集的第一个返回行返回的新值，同时仍在每个 DataColumn 中保留原始值。 首先调用 DataRow 的 AcceptChanges 方法将当前值保留为原始值，然后分配新值。 完成这些操作后， RowState 属性设置为 Added 的 DataRows 将会使其 RowState 属性设置为 Modified，这可能是不需要的行为。

如何将命令结果应用于每个要更新的 DataRow 由每个 DbCommand 的 UpdatedRowSource 属性确定。 此属性设置为 UpdateRowSource 枚举中的一个值。

下表说明 UpdateRowSource 枚举值如何影响已更新行的 RowState 属性。

成员名称

说明

Both

调用 AcceptChanges 并将输出参数值和/或任何返回的数据集的第一行中的值放在要更新的 DataRow 中。 如果没有要应用的值，则 RowState 将为 Unchanged。

FirstReturnedRecord

如果返回一个行，则调用 AcceptChanges 并将此行映射到 DataTable 中已更改的行，同时将 RowState 设置为 Modified。 如果未返回任何行，则不调用 AcceptChanges，RowState 将保持为 Added。

None

忽略任何返回的参数或行。 不对 AcceptChanges 进行调用，RowState 将保持为 Added。

OutputParameters

调用 AcceptChanges 并将任何输出参数映射到 DataTable 中已更改的行，同时将 RowState 设置为 Modified。 如果没有输出参数，则 RowState 将为 Unchanged。

示例

此示例演示从 DataTable 中提取已更改的行，然后使用 SqlDataAdapter 更新数据源并检索新标识列值。 InsertCommand 执行两个 Transact-SQL 语句：第一个是 INSERT 语句，第二个是使用 SCOPE_IDENTITY 函数检索标识值的 SELECT 语句。

复制代码

INSERT INTO dbo.Shippers (CompanyName) VALUES (@CompanyName);SELECT ShipperID, CompanyName FROM dbo.Shippers WHERE ShipperID = SCOPE_IDENTITY();

插入命令的 UpdatedRowSource 属性设置为 UpdateRowSource.FirstReturnedRow 并且 DataAdapter 的 MissingSchemaAction 属性设置为 MissingSchemaAction.AddWithKey。 填充 DataTable 并且代码向 DataTable 添加一个新行。 然后将已更改的行提取到一个新的 DataTable 中，将后者传递到 DataAdapter，它将随后更新服务器。

Visual Basic

复制代码

Private Sub MergeIdentityColumns(ByVal connectionString As String)    Using connection As SqlConnection = New SqlConnection( _       connectionString)        ' Create the DataAdapter        Dim adapter As SqlDataAdapter = New SqlDataAdapter( _          "SELECT ShipperID, CompanyName FROM dbo.Shippers", connection)        ' Add the InsertCommand to retrieve new identity value.        adapter.InsertCommand = New SqlCommand( _            "INSERT INTO dbo.Shippers (CompanyName) " & _            "VALUES (@CompanyName); " & _            "SELECT ShipperID, CompanyName FROM dbo.Shippers " & _            "WHERE ShipperID = SCOPE_IDENTITY();", _            connection)        ' Add the parameter for the inserted value.        adapter.InsertCommand.Parameters.Add( _           New SqlParameter("@CompanyName", SqlDbType.NVarChar, 40, _           "CompanyName"))        adapter.InsertCommand.UpdatedRowSource = UpdateRowSource.Both        ' MissingSchemaAction adds any missing schema to         ' the DataTable, including identity columns        adapter.MissingSchemaAction = MissingSchemaAction.AddWithKey        ' Fill the DataTable.        Dim shipper As New DataTable        adapter.Fill(shipper)        ' Add a new shipper.         Dim newRow As DataRow = shipper.NewRow()        newRow("CompanyName") = "New Shipper"        shipper.Rows.Add(newRow)        ' Add changed rows to a new DataTable. This        ' DataTable will be used by the DataAdapter.        Dim dataChanges As DataTable = shipper.GetChanges()        ' Add the event handler.         AddHandler adapter.RowUpdated, New _           SqlRowUpdatedEventHandler(AddressOf OnRowUpdated)        ' Update the datasource with the modified records.        adapter.Update(dataChanges)        ' Merge the two DataTables.        shipper.Merge(dataChanges)        ' Commit the changes.        shipper.AcceptChanges()        Console.WriteLine("Rows after merge.")        Dim row As DataRow        For Each row In shipper.Rows            Console.WriteLine("{0}: {1}", row(0), row(1))        Next    End UsingEnd Sub

C#

复制代码

private static void MergeIdentityColumns(string connectionString){    using (SqlConnection connection =               new SqlConnection(connectionString))    {        // Create the DataAdapter        SqlDataAdapter adapter =            new SqlDataAdapter(            "SELECT ShipperID, CompanyName FROM dbo.Shippers",            connection);        //Add the InsertCommand to retrieve new identity value.        adapter.InsertCommand = new SqlCommand(            "INSERT INTO dbo.Shippers (CompanyName) " +            "VALUES (@CompanyName); " +            "SELECT ShipperID, CompanyName FROM dbo.Shippers " +            "WHERE ShipperID = SCOPE_IDENTITY();", connection);        // Add the parameter for the inserted value.        adapter.InsertCommand.Parameters.Add(           new SqlParameter("@CompanyName", SqlDbType.NVarChar, 40,           "CompanyName"));        adapter.InsertCommand.UpdatedRowSource = UpdateRowSource.Both;        // MissingSchemaAction adds any missing schema to         // the DataTable, including identity columns        adapter.MissingSchemaAction = MissingSchemaAction.AddWithKey;        // Fill the DataTable.        DataTable shipper = new DataTable();        adapter.Fill(shipper);        // Add a new shipper.         DataRow newRow = shipper.NewRow();        newRow["CompanyName"] = "New Shipper";        shipper.Rows.Add(newRow);        // Add changed rows to a new DataTable. This        // DataTable will be used by the DataAdapter.        DataTable dataChanges = shipper.GetChanges();        // Add the event handler.         adapter.RowUpdated +=            new SqlRowUpdatedEventHandler(OnRowUpdated);        adapter.Update(dataChanges);        connection.Close();        // Merge the updates.        shipper.Merge(dataChanges);        // Commit the changes.        shipper.AcceptChanges();        Console.WriteLine("Rows after merge.");        foreach (DataRow row in shipper.Rows)        {            {                Console.WriteLine("{0}: {1}", row[0], row[1]);            }        }    }}

OnRowUpdated 事件处理程序检查 SqlRowUpdatedEventArgs 的 StatementType 以确定行是否为插入项。 如果是，则将 Status 属性设置为 SkipCurrentRow。 该行将被更新，但会保留该行中的原始值。 在过程的正文中，调用 Merge 方法以将新标识值合并到原始 DataTable 中，最后调用 AcceptChanges。

Visual Basic

复制代码

Private Sub OnRowUpdated( _    ByVal sender As Object, ByVal e As SqlRowUpdatedEventArgs)    ' If this is an insert, then skip this row.    If e.StatementType = StatementType.Insert Then        e.Status = UpdateStatus.SkipCurrentRow    End IfEnd Sub

C#

复制代码

protected static void OnRowUpdated(    object sender, SqlRowUpdatedEventArgs e){    // If this is an insert, then skip this row.    if (e.StatementType == StatementType.Insert)    {        e.Status = UpdateStatus.SkipCurrentRow;    }}

 检索 Microsoft Access Autonumber 值

本节包含一个演示如何从 Jet 4.0 数据库中检索 Autonumber 值的示例。 Jet 数据库引擎不支持在一个批处理中执行多个语句或使用输出参数，因此不能使用这些技术返回分配给已插入行的新 Autonumber 值。 但是，您可以向 RowUpdated 事件处理程序中添加代码，执行一个单独的 SELECT @@IDENTITY 语句来检索新的 Autonumber 值。

示例

此示例不使用 MissingSchemaAction.AddWithKey 添加架构信息，而是在调用 OleDbDataAdapter 以填充 DataTable 之前用正确的架构配置 DataTable。 在本例中，通过将 AutoIncrement 设置为 true、将 AutoIncrementSeed 设置为 0 以及将 AutoIncrementStep 设置为 -1，将 CategoryID 列配置为从零开始并递减，递减的幅度是为每个插入行分配的值。 然后，代码添加两个新行并使用 GetChanges 将已更改的行添加到传递给 Update 方法的新 DataTable 中。

Visual Basic

复制代码

Shared connection As OleDbConnection = NothingPrivate Shared Sub MergeIdentityColumns(ByVal connection As OleDbConnection)    Using connection        ' Create a DataAdapter based on a SELECT query.        Dim adapter As OleDbDataAdapter = New OleDbDataAdapter( _          "SELECT CategoryID, CategoryName FROM Categories", _          connection)        ' Create the INSERT command for the new category.        adapter.InsertCommand = New OleDbCommand( _          "INSERT INTO Categories (CategoryName) Values(?)", connection)        adapter.InsertCommand.CommandType = CommandType.Text        ' Add the parameter for the CategoryName.        adapter.InsertCommand.Parameters.Add( _          "@CategoryName", OleDbType.VarWChar, 15, "CategoryName")        adapter.InsertCommand.UpdatedRowSource = UpdateRowSource.Both        ' Create a DataTable.        Dim categories As DataTable = New DataTable        ' Create the CategoryID column and set its auto        ' incrementing properties to decrement from zero.        Dim column As New DataColumn()        column.DataType = System.Type.GetType("System.Int32")        column.ColumnName = "CategoryID"        column.AutoIncrement = True        column.AutoIncrementSeed = 0        column.AutoIncrementStep = -1        categories.Columns.Add(column)        ' Create the CategoryName column.        column = New DataColumn()        column.DataType = System.Type.GetType("System.String")        column.ColumnName = "CategoryName"        categories.Columns.Add(column)        ' Set the primary key on CategoryID.        Dim pKey(1) As DataColumn        pKey(0) = categories.Columns("CategoryID")        categories.PrimaryKey = pKey        ' Fetch the data and fill the DataTable.        adapter.Fill(categories)        ' Add a new row.        Dim newRow As DataRow = categories.NewRow()        newRow("CategoryName") = "New Category"        categories.Rows.Add(newRow)        ' Add another new row.        Dim newRow2 As DataRow = categories.NewRow()        newRow2("CategoryName") = "Another New Category"        categories.Rows.Add(newRow2)        ' Add changed rows to a new DataTable that will be        ' used to post the inserts to the database.        Dim dataChanges As DataTable = categories.GetChanges()        ' Include an event to fill in the Autonumber value.        AddHandler adapter.RowUpdated, _          New OleDbRowUpdatedEventHandler(AddressOf OnRowUpdated)        ' Update the database, inserting the new rows.        adapter.Update(dataChanges)        Console.WriteLine("Rows before merge:")        Dim row1 As DataRow        For Each row1 In categories.Rows            Console.WriteLine("  {0}: {1}", row1(0), row1(1))        Next        ' Merge the two DataTables.        categories.Merge(dataChanges)        ' Commit the changes.        categories.AcceptChanges()        Console.WriteLine("Rows after merge:")        Dim row As DataRow        For Each row In categories.Rows            Console.WriteLine("  {0}: {1}", row(0), row(1))        Next    End UsingEnd Sub

C#

复制代码

private static OleDbConnection connection = null;private static void MergeIdentityColumns(OleDbConnection connection){    using (connection)    {        // Create a DataAdapter based on a SELECT query.        OleDbDataAdapter adapter = new OleDbDataAdapter(         "SELECT CategoryID, CategoryName FROM Categories",         connection);        // Create the INSERT command for the new category.        adapter.InsertCommand = new OleDbCommand(          "INSERT INTO Categories (CategoryName) Values(?)", connection);        adapter.InsertCommand.CommandType = CommandType.Text;        // Add the parameter for the CategoryName.        adapter.InsertCommand.Parameters.Add(          "@CategoryName", OleDbType.VarWChar, 15, "CategoryName");        adapter.InsertCommand.UpdatedRowSource = UpdateRowSource.Both;        // Create a DataTable        DataTable categories = new DataTable();        // Create the CategoryID column and set its auto         // incrementing properties to decrement from zero.         DataColumn column = new DataColumn();        column.DataType = System.Type.GetType("System.Int32");        column.ColumnName = "CategoryID";        column.AutoIncrement = true;        column.AutoIncrementSeed = 0;        column.AutoIncrementStep = -1;        categories.Columns.Add(column);        // Create the CategoryName column.        column = new DataColumn();        column.DataType = System.Type.GetType("System.String");        column.ColumnName = "CategoryName";        categories.Columns.Add(column);        // Set the primary key on CategoryID.        DataColumn[] pKey = new DataColumn[1];        pKey[0] = categories.Columns["CategoryID"];        categories.PrimaryKey = pKey;        // Fetch the data and fill the DataTable        adapter.Fill(categories);        // Add a new row.        DataRow newRow = categories.NewRow();        newRow["CategoryName"] = "New Category";        categories.Rows.Add(newRow);        // Add another new row.        DataRow newRow2 = categories.NewRow();        newRow2["CategoryName"] = "Another New Category";        categories.Rows.Add(newRow2);        // Add changed rows to a new DataTable that will be        // used to post the inserts to the database.        DataTable dataChanges = categories.GetChanges();        // Include an event to fill in the Autonumber value.        adapter.RowUpdated +=            new OleDbRowUpdatedEventHandler(OnRowUpdated);        // Update the database, inserting the new rows.         adapter.Update(dataChanges);        Console.WriteLine("Rows before merge:");        foreach (DataRow row in categories.Rows)        {            {                Console.WriteLine("  {0}: {1}", row[0], row[1]);            }        }        // Merge the two DataTables.        categories.Merge(dataChanges);        // Commit the changes.        categories.AcceptChanges();        Console.WriteLine("Rows after merge:");        foreach (DataRow row in categories.Rows)        {            {                Console.WriteLine("  {0}: {1}", row[0], row[1]);            }        }    }}

RowUpdated 事件处理程序使用相同的开放式 OleDbConnection 作为 OleDbDataAdapter 的 Update 语句。 它会检查已插入行的 OleDbRowUpdatedEventArgs 的 StatementType。 对于每个已插入的行，会创建一个新的 OleDbCommand 以在连接上执行 SELECT @@IDENTITY 语句，返回新的 Autonumber 值，该值将被放在 DataRow 的 CategoryID 列中。 然后将 Status 属性设置为 UpdateStatus.SkipCurrentRow 以取消对 AcceptChanges 的隐藏调用。 在过程的正文中，调用 Merge 方法以合并两个 DataTable 对象，最后调用 AcceptChanges。

Visual Basic

复制代码

Private Shared Sub OnRowUpdated( _    ByVal sender As Object, ByVal e As OleDbRowUpdatedEventArgs)    ' Conditionally execute this code block on inserts only.    If e.StatementType = StatementType.Insert Then        ' Retrieve the Autonumber and store it in the CategoryID column.        Dim cmdNewID As New OleDbCommand("SELECT @@IDENTITY", _           connection)        e.Row("CategoryID") = CInt(cmdNewID.ExecuteScalar)        e.Status = UpdateStatus.SkipCurrentRow    End IfEnd Sub

C#

复制代码

private static void OnRowUpdated(  object sender, OleDbRowUpdatedEventArgs e){    // Conditionally execute this code block on inserts only.    if (e.StatementType == StatementType.Insert)    {        OleDbCommand cmdNewID = new OleDbCommand("SELECT @@IDENTITY",            connection);        // Retrieve the Autonumber and store it in the CategoryID column.        e.Row["CategoryID"] = (int)cmdNewID.ExecuteScalar();        e.Status = UpdateStatus.SkipCurrentRow;    }}