locked
FAQ Item: How to retrieve identity or auto number values in ADO.NET? RRS feed

  • Question

  • How to retrieve identity or auto number values in ADO.NET?
    Sunday, June 20, 2010 1:24 PM

Answers

  • When working with Microsoft SQL Server, you can create a stored procedure with an output parameter to return the identity value for an inserted row. The following describes the three Transact-SQL functions in SQL Server that can be used to retrieve identity column values.

    SCOPE_IDENTITY, returns the last identity value within the current execution scope. SCOPE_IDENTITY is recommended for most scenarios.

     @@IDENTITY, contains the last identity value generated in any table in the current session. @@IDENTITY can be affected by triggers and may not return the identity value that you expect.

     IDENT_CURRENT, returns the last identity value generated for a specific table in any session and any scope.

    The following stored procedure demonstrates how to insert a row into the Categories table and use an output parameter to return the new identity value generated by the Transact-SQL SCOPE_IDENTITY() function.

    CopyCREATE PROCEDURE dbo.InsertCategory

      @CategoryName nvarchar(15),

      @Identity int OUT

    AS

    INSERT INTO Categories (CategoryName) VALUES(@CategoryName)

    SET @Identity = SCOPE_IDENTITY()

     

    The stored procedure can then be specified as the source of the InsertCommand of a SqlDataAdapter object. The CommandType property of the InsertCommand must be set to StoredProcedure. The identity output is retrieved by creating a SqlParameter that has a ParameterDirection of Output. When the InsertCommand is processed, the auto-incremented identity value is returned and placed in the CategoryID column of the current row if you set the UpdatedRowSource property of the insert command to UpdateRowSource.OutputParameters or to UpdateRowSource.Both.

    If your insert command executes a batch that includes both an INSERT statement and a SELECT statement that returns the new identity value, then you can retrieve the new value by setting the UpdatedRowSource property of the insert command to UpdateRowSource.FirstReturnedRecord.

     

    Codes:

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

                        }

                    }

                }

            }

     

    Related threads:

    http://social.msdn.microsoft.com/Forums/en-US/adodotnetdataproviders/thread/11709532-4554-4d03-8067-72018c005c0f

    http://social.msdn.microsoft.com/Forums/en-US/adodotnetdataproviders/thread/e262d4d7-0dea-4680-b845-bad2e378f15d

    http://msdn.microsoft.com/en-us/library/ks9f57t0.aspx
    • Marked as answer by MSDN FAQ Sunday, June 20, 2010 1:25 PM
    Sunday, June 20, 2010 1:25 PM