检索标识或自动编号值

项目
05/23/2023

适用于： .NET Framework .NET .NET Standard

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

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

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

备注

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

检索 SQL Server 标识列值

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

函数	说明
SCOPE_IDENTITY	返回当前执行范围内的最后一个标识值。对于多数方案，建议使用 SCOPE_IDENTITY。
@@IDENTITY	包含当前会话中任何表生成的最后一个标识值。 @@IDENTITY 受触发器的影响，可能不会返回预期的标识值。
IDENT_CURRENT	返回在任何会话中以及任何范围内为特定表生成的最后一个标识值。

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

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

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

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

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]);
            }
        }
    }
}

合并新标识值

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

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

第一种保留原始值的方法是将 AcceptChangesDuringUpdate 的 DataAdapter 属性设置为 false。此配置会影响要更新的 DataTable 中的每个 DataRow。有关更多信息及代码示例，请参阅 AcceptChangesDuringUpdate。
第二种方法是在 RowUpdated 的 DataAdapter 事件处理程序中编写代码，将 Status 设置为 SkipCurrentRow。 DataRow 将会更新，但每个 DataColumn 的原始值会保留。使用此方法可以对某些行保留原始值而不对其他行保留原始值。例如，通过先检查 StatementType 然后仅针对 Status 为 SkipCurrentRow 的行将 StatementType 设置为 Insert，代码可以为已添加的行保留原始值，而不为已编辑或已删除的行保留原始值。

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

如何将命令结果应用于每个要更新的 DataRow 由每个 UpdatedRowSource 的 DbCommand 属性确定。此属性设置为 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 并且 MissingSchemaAction 的 DataAdapter 属性设置为 MissingSchemaAction.AddWithKey。填充 DataTable 并且代码向 DataTable 添加一个新行。然后将已更改的行提取到一个新的 DataTable 中，将后者传递到 DataAdapter，它将随后更新服务器。

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

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;
    }
}

检索标识值

当列中的值必须唯一时，我们经常将该列设置为标识。而且有时我们需要新数据的标识值。此示例演示了如何检索标识值：

创建存储过程以插入数据和返回标识值。
执行命令以插入新数据并显示结果。
使用 SqlDataAdapter 以插入新数据并显示结果。

在你编译并运行该示例之前，你必须使用以下脚本创建示例数据库：

USE [master]
GO

CREATE DATABASE [MySchool]
GO

USE [MySchool]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE procedure [dbo].[CourseExtInfo] @CourseId int
as
select c.CourseID,c.Title,c.Credits,d.Name as DepartmentName
from Course as c left outer join Department as d on c.DepartmentID=d.DepartmentID
where c.CourseID=@CourseId

GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create procedure [dbo].[DepartmentInfo] @DepartmentId int,@CourseCount int output
as
select @CourseCount=Count(c.CourseID)
from course as c
where c.DepartmentID=@DepartmentId

select d.DepartmentID,d.Name,d.Budget,d.StartDate,d.Administrator
from Department as d
where d.DepartmentID=@DepartmentId

GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
Create PROCEDURE [dbo].[GetDepartmentsOfSpecifiedYear]
@Year int,@BudgetSum money output
AS
BEGIN
        SELECT @BudgetSum=SUM([Budget])
  FROM [MySchool].[dbo].[Department]
  Where YEAR([StartDate])=@Year

SELECT [DepartmentID]
      ,[Name]
      ,[Budget]
      ,[StartDate]
      ,[Administrator]
  FROM [MySchool].[dbo].[Department]
  Where YEAR([StartDate])=@Year

END
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[GradeOfStudent]
-- Add the parameters for the stored procedure here
@CourseTitle nvarchar(100),@FirstName nvarchar(50),
@LastName nvarchar(50),@Grade decimal(3,2) output
AS
BEGIN
select @Grade=Max(Grade)
from [dbo].[StudentGrade] as s join [dbo].[Course] as c on
s.CourseID=c.CourseID join [dbo].[Person] as p on s.StudentID=p.PersonID
where c.Title=@CourseTitle and p.FirstName=@FirstName
and p.LastName= @LastName
END
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[InsertPerson]
-- Add the parameters for the stored procedure here
@FirstName nvarchar(50),@LastName nvarchar(50),
@PersonID int output
AS
BEGIN
    insert [dbo].[Person](LastName,FirstName) Values(@LastName,@FirstName)

    set @PersonID=SCOPE_IDENTITY()
END
Go

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Course]([CourseID] [nvarchar](10) NOT NULL,
[Year] [smallint] NOT NULL,
[Title] [nvarchar](100) NOT NULL,
[Credits] [int] NOT NULL,
[DepartmentID] [int] NOT NULL,
 CONSTRAINT [PK_Course] PRIMARY KEY CLUSTERED
(
[CourseID] ASC,
[Year] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY]

GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Department]([DepartmentID] [int] IDENTITY(1,1) NOT NULL,
[Name] [nvarchar](50) NOT NULL,
[Budget] [money] NOT NULL,
[StartDate] [datetime] NOT NULL,
[Administrator] [int] NULL,
 CONSTRAINT [PK_Department] PRIMARY KEY CLUSTERED
(
[DepartmentID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY]

GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Person]([PersonID] [int] IDENTITY(1,1) NOT NULL,
[LastName] [nvarchar](50) NOT NULL,
[FirstName] [nvarchar](50) NOT NULL,
[HireDate] [datetime] NULL,
[EnrollmentDate] [datetime] NULL,
[Picture] [varbinary](max) NULL,
 CONSTRAINT [PK_School.Student] PRIMARY KEY CLUSTERED
(
[PersonID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[StudentGrade]([EnrollmentID] [int] IDENTITY(1,1) NOT NULL,
[CourseID] [nvarchar](10) NOT NULL,
[StudentID] [int] NOT NULL,
[Grade] [decimal](3, 2) NOT NULL,
 CONSTRAINT [PK_StudentGrade] PRIMARY KEY CLUSTERED
(
[EnrollmentID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY]

GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create view [dbo].[EnglishCourse]
as
select c.CourseID,c.Title,c.Credits,c.DepartmentID
from Course as c join Department as d on c.DepartmentID=d.DepartmentID
where d.Name=N'English'

GO
INSERT [dbo].[Course] ([CourseID], [Year], [Title], [Credits], [DepartmentID]) VALUES (N'C1045', 2012, N'Calculus', 4, 7)
INSERT [dbo].[Course] ([CourseID], [Year], [Title], [Credits], [DepartmentID]) VALUES (N'C1061', 2012, N'Physics', 4, 1)
INSERT [dbo].[Course] ([CourseID], [Year], [Title], [Credits], [DepartmentID]) VALUES (N'C2021', 2012, N'Composition', 3, 2)
INSERT [dbo].[Course] ([CourseID], [Year], [Title], [Credits], [DepartmentID]) VALUES (N'C2042', 2012, N'Literature', 4, 2)
SET IDENTITY_INSERT [dbo].[Department] ON

INSERT [dbo].[Department] ([DepartmentID], [Name], [Budget], [StartDate], [Administrator]) VALUES (1, N'Engineering', 350000.0000, CAST(0x0000999C00000000 AS DateTime), 2)
INSERT [dbo].[Department] ([DepartmentID], [Name], [Budget], [StartDate], [Administrator]) VALUES (2, N'English', 120000.0000, CAST(0x0000999C00000000 AS DateTime), 6)
INSERT [dbo].[Department] ([DepartmentID], [Name], [Budget], [StartDate], [Administrator]) VALUES (4, N'Economics', 200000.0000, CAST(0x0000999C00000000 AS DateTime), 4)
INSERT [dbo].[Department] ([DepartmentID], [Name], [Budget], [StartDate], [Administrator]) VALUES (7, N'Mathematics', 250024.0000, CAST(0x0000999C00000000 AS DateTime), 3)
SET IDENTITY_INSERT [dbo].[Department] OFF
SET IDENTITY_INSERT [dbo].[Person] ON

INSERT [dbo].[Person] ([PersonID], [LastName], [FirstName], [HireDate], [EnrollmentDate]) VALUES (1, N'Hu', N'Nan', NULL, CAST(0x0000A0BF00000000 AS DateTime))
INSERT [dbo].[Person] ([PersonID], [LastName], [FirstName], [HireDate], [EnrollmentDate]) VALUES (2, N'Norman', N'Laura', NULL, CAST(0x0000A0BF00000000 AS DateTime))
INSERT [dbo].[Person] ([PersonID], [LastName], [FirstName], [HireDate], [EnrollmentDate]) VALUES (3, N'Olivotto', N'Nino', NULL, CAST(0x0000A0BF00000000 AS DateTime))
INSERT [dbo].[Person] ([PersonID], [LastName], [FirstName], [HireDate], [EnrollmentDate]) VALUES (4, N'Anand', N'Arturo', NULL, CAST(0x0000A0BF00000000 AS DateTime))
INSERT [dbo].[Person] ([PersonID], [LastName], [FirstName], [HireDate], [EnrollmentDate]) VALUES (5, N'Jai', N'Damien', NULL, CAST(0x0000A0BF00000000 AS DateTime))
INSERT [dbo].[Person] ([PersonID], [LastName], [FirstName], [HireDate], [EnrollmentDate]) VALUES (6, N'Holt', N'Roger', CAST(0x000097F100000000 AS DateTime), NULL)
INSERT [dbo].[Person] ([PersonID], [LastName], [FirstName], [HireDate], [EnrollmentDate]) VALUES (7, N'Martin', N'Randall', CAST(0x00008B1A00000000 AS DateTime), NULL)
SET IDENTITY_INSERT [dbo].[Person] OFF
SET IDENTITY_INSERT [dbo].[StudentGrade] ON

INSERT [dbo].[StudentGrade] ([EnrollmentID], [CourseID], [StudentID], [Grade]) VALUES (1, N'C1045', 1, CAST(3.50 AS Decimal(3, 2)))
INSERT [dbo].[StudentGrade] ([EnrollmentID], [CourseID], [StudentID], [Grade]) VALUES (2, N'C1045', 2, CAST(3.00 AS Decimal(3, 2)))
INSERT [dbo].[StudentGrade] ([EnrollmentID], [CourseID], [StudentID], [Grade]) VALUES (3, N'C1045', 3, CAST(2.50 AS Decimal(3, 2)))
INSERT [dbo].[StudentGrade] ([EnrollmentID], [CourseID], [StudentID], [Grade]) VALUES (4, N'C1045', 4, CAST(4.00 AS Decimal(3, 2)))
INSERT [dbo].[StudentGrade] ([EnrollmentID], [CourseID], [StudentID], [Grade]) VALUES (5, N'C1045', 5, CAST(3.50 AS Decimal(3, 2)))
INSERT [dbo].[StudentGrade] ([EnrollmentID], [CourseID], [StudentID], [Grade]) VALUES (6, N'C1061', 1, CAST(4.00 AS Decimal(3, 2)))
INSERT [dbo].[StudentGrade] ([EnrollmentID], [CourseID], [StudentID], [Grade]) VALUES (7, N'C1061', 3, CAST(3.50 AS Decimal(3, 2)))
INSERT [dbo].[StudentGrade] ([EnrollmentID], [CourseID], [StudentID], [Grade]) VALUES (8, N'C1061', 4, CAST(2.50 AS Decimal(3, 2)))
INSERT [dbo].[StudentGrade] ([EnrollmentID], [CourseID], [StudentID], [Grade]) VALUES (9, N'C1061', 5, CAST(1.50 AS Decimal(3, 2)))
INSERT [dbo].[StudentGrade] ([EnrollmentID], [CourseID], [StudentID], [Grade]) VALUES (10, N'C2021', 1, CAST(2.50 AS Decimal(3, 2)))
INSERT [dbo].[StudentGrade] ([EnrollmentID], [CourseID], [StudentID], [Grade]) VALUES (11, N'C2021', 2, CAST(3.50 AS Decimal(3, 2)))
INSERT [dbo].[StudentGrade] ([EnrollmentID], [CourseID], [StudentID], [Grade]) VALUES (12, N'C2021', 4, CAST(3.00 AS Decimal(3, 2)))
INSERT [dbo].[StudentGrade] ([EnrollmentID], [CourseID], [StudentID], [Grade]) VALUES (13, N'C2021', 5, CAST(3.00 AS Decimal(3, 2)))
INSERT [dbo].[StudentGrade] ([EnrollmentID], [CourseID], [StudentID], [Grade]) VALUES (14, N'C2042', 1, CAST(2.00 AS Decimal(3, 2)))
INSERT [dbo].[StudentGrade] ([EnrollmentID], [CourseID], [StudentID], [Grade]) VALUES (15, N'C2042', 2, CAST(3.50 AS Decimal(3, 2)))
INSERT [dbo].[StudentGrade] ([EnrollmentID], [CourseID], [StudentID], [Grade]) VALUES (16, N'C2042', 3, CAST(4.00 AS Decimal(3, 2)))
INSERT [dbo].[StudentGrade] ([EnrollmentID], [CourseID], [StudentID], [Grade]) VALUES (17, N'C2042', 5, CAST(3.00 AS Decimal(3, 2)))
SET IDENTITY_INSERT [dbo].[StudentGrade] OFF
ALTER TABLE [dbo].[Course]  WITH CHECK ADD  CONSTRAINT [FK_Course_Department] FOREIGN KEY([DepartmentID])
REFERENCES [dbo].[Department] ([DepartmentID])
GO
ALTER TABLE [dbo].[Course] CHECK CONSTRAINT [FK_Course_Department]
GO
ALTER TABLE [dbo].[StudentGrade]  WITH CHECK ADD  CONSTRAINT [FK_StudentGrade_Student] FOREIGN KEY([StudentID])
REFERENCES [dbo].[Person] ([PersonID])
GO
ALTER TABLE [dbo].[StudentGrade] CHECK CONSTRAINT [FK_StudentGrade_Student]
GO

代码列表如下：

static void Main(string[] args)
{
    String SqlDbConnectionString = "Data Source=(local);Initial Catalog=MySchool;Integrated Security=True;";

    InsertPersonInCommand(SqlDbConnectionString, "Janice", "Galvin");
    Console.WriteLine();

    InsertPersonInAdapter(SqlDbConnectionString, "Peter", "Krebs");
    Console.WriteLine();

    Console.WriteLine("Please press any key to exit.....");
    Console.ReadKey();
}

// Using stored procedure to insert a new row and retrieve the identity value
static void InsertPersonInCommand(String connectionString, String firstName, String lastName)
{
    String commandText = "dbo.InsertPerson";

    using (SqlConnection conn = new SqlConnection(connectionString))
    {
        using (SqlCommand cmd = new SqlCommand(commandText, conn))
        {
            cmd.CommandType = CommandType.StoredProcedure;

            cmd.Parameters.Add(new SqlParameter("@FirstName", firstName));
            cmd.Parameters.Add(new SqlParameter("@LastName", lastName));
            SqlParameter personId = new SqlParameter("@PersonID", SqlDbType.Int);
            personId.Direction = ParameterDirection.Output;
            cmd.Parameters.Add(personId);

            conn.Open();
            cmd.ExecuteNonQuery();

            Console.WriteLine("Person Id of new person:{0}", personId.Value);
        }
    }
}

// Using stored procedure in adapter to insert new rows and update the identity value.
static void InsertPersonInAdapter(String connectionString, String firstName, String lastName)
{
    String commandText = "dbo.InsertPerson";
    using (SqlConnection conn = new SqlConnection(connectionString))
    {
        SqlDataAdapter mySchool = new SqlDataAdapter("Select PersonID,FirstName,LastName from [dbo].[Person]", conn);

        mySchool.InsertCommand = new SqlCommand(commandText, conn);
        mySchool.InsertCommand.CommandType = CommandType.StoredProcedure;

        mySchool.InsertCommand.Parameters.Add(
            new SqlParameter("@FirstName", SqlDbType.NVarChar, 50, "FirstName"));
        mySchool.InsertCommand.Parameters.Add(
            new SqlParameter("@LastName", SqlDbType.NVarChar, 50, "LastName"));

        SqlParameter personId = mySchool.InsertCommand.Parameters.Add(new SqlParameter("@PersonID", SqlDbType.Int, 0, "PersonID"));
        personId.Direction = ParameterDirection.Output;

        DataTable persons = new DataTable();
        mySchool.Fill(persons);

        DataRow newPerson = persons.NewRow();
        newPerson["FirstName"] = firstName;
        newPerson["LastName"] = lastName;
        persons.Rows.Add(newPerson);

        mySchool.Update(persons);
        Console.WriteLine("Show all persons:");
        ShowDataTable(persons, 14);
    }
}

private static void ShowDataTable(DataTable table, Int32 length)
{
    foreach (DataColumn col in table.Columns)
    {
        Console.Write("{0,-" + length + "}", col.ColumnName);
    }
    Console.WriteLine();

    foreach (DataRow row in table.Rows)
    {
        foreach (DataColumn col in table.Columns)
        {
            if (col.DataType.Equals(typeof(DateTime)))
                Console.Write("{0,-" + length + ":d}", row[col]);
            else if (col.DataType.Equals(typeof(Decimal)))
                Console.Write("{0,-" + length + ":C}", row[col]);
            else
                Console.Write("{0,-" + length + "}", row[col]);
        }

        Console.WriteLine();
    }
}

通过

检索标识或自动编号值

检索 SQL Server 标识列值

合并新标识值

示例

检索标识值

请参阅

反馈

其他资源