Retrieving Identity or AutoGenerated Column in ADO.NET

  • Article

I recently handled an issue where customer was facing difficulty in picking up the Auto Generated primary key column in DataAdapter.Update() call. Looking around on the internet I saw many posts on the forums regarding how to get this value. So thought of posting it here

There are 2 ways to go about this

  • Using an output parameter
  • Using a Select statement inside the same batch as that of the insert

I see the output parameter being already documented in MSDN here https://msdn.microsoft.com/en-us/library/ks9f57t0.aspx.

The second method is documented like this MSDN and will be our topic of concern,

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

Here is how to go about this :

I have a sample table called Categories with the following schema

CategoriesTable

Here the CategoryId column is set to Identity

Next I created the stored procedure that will help insert the CategoryName column. Here is how the definition looks like

 CREATE PROCEDURE InsertCategory
(
    @CategoryName nvarchar(50)
)
as
begin

    Insert Into Categories(CategoryName)
    values (@CategoryName)
    Select SCOPE_IDENTITY() as CategoryId
end

Notice that in the procedure the last T-SQL statement is a Select statement which queries the Identity value in the scope and returns it as the name of the column for the primary key.

Once this is in place lets take a look at the ADO.NET code

             string strCatName;
            Console.WriteLine("Enter the category");
            strCatName = Console.ReadLine();

            using (SqlConnection conn = new SqlConnection())
            {
                try
                {
                    conn.ConnectionString = @"Server=.\yukon;integrated security=true;initial catalog=Test";
                    conn.Open();

                    DataSet ds = new DataSet("Test");
                    //create an adapter specifying a select
                    SqlDataAdapter aDap = new SqlDataAdapter("Select * From Categories", conn);
                    //specify the insert command for the aDap
                    SqlCommand insertCommand = new SqlCommand();
                    insertCommand.CommandText = "InsertCategory";
                    insertCommand.CommandType = CommandType.StoredProcedure;
                    insertCommand.Parameters.Add("@CategoryName", SqlDbType.NVarChar, 50, "CategoryName");
                    insertCommand.UpdatedRowSource = UpdateRowSource.FirstReturnedRecord;
                    insertCommand.Connection = conn;
                    //bind the insertCommand to DataAdapter
                    aDap.InsertCommand = insertCommand;
                    //fill the dataset
                    aDap.Fill(ds, "Categories");

                    //get the Categories table
                    DataTable categoriesTbl = ds.Tables["Categories"];

                    //create a new row
                    DataRow newRow = categoriesTbl.NewRow();
                    //fill in the values for the column
                    newRow["CategoryName"] = strCatName;
                    //add the new row
                    categoriesTbl.Rows.Add(newRow);

                    //update the adapter
                    aDap.Update(ds, "Categories");

                    Console.WriteLine("Printing updated results");
                    PrintResults(ds);

                    Console.Read();


                }
                catch (SqlException ex)
                {
                    Console.WriteLine(ex.Message);
                    Console.Read();
                }

            }

Notice that in the insertCommand above I have the following property set

insertCommand.UpdatedRowSource = UpdateRowSource.FirstReturnedRecord;

This is specifying the adapter that once updated the first returned record is mapped to the changed row in DataSet. And since we are sending the CategoryId which the DataTable already knows, the DataAdapter.Update() will be able to map the column and put the value for you.

Hope this helps !

Comments