Enterprise Library 2.0 DAAB - Retrieving PK and Timestamp When Inserting New Record

I was answering a number of questions on the Enterprise Library 2.0 DAAB Message Board today, and this one was worth a blog post since the question comes up a lot in various forums. Although I am showing this using Enterprise Library 2.0 DAAB, the answer applies to SqlClient as well.

Let's say you're inserting a new customer row in the Customers Table and you want to retrieve the PK and Timestap ( Rowversion ) of the row. You have a couple of ways to do this depending on how you handle the data access needs of your application. These solutions are when using an SQL string and not a stored procedure.

Solution #1: Retrieve PK Using ExecuteScalar Command and SCOPE_IDENTITY

If you regularly read my blog, this is one you should already be familiar with as I have talked about it numerous time. Here is the code:

SqlDatabase db = new SqlDatabase("...");

string sql = "INSERT INTO Customers (FirstName,LastName)
        VALUES (@FirstName,@LastName) SELECT SCOPE_IDENTITY()";
DbCommand command = db.GetSqlStringCommand(sql);
db.AddInParameter(command,"@FirstName",DbType.String, "David");
db.AddInParameter(command,"@LastName",DbType.String, "Hayden");

int customerId = Convert.ToInt32(db.ExecuteScalar(command));

We execute two sql statements: one inserts the new customer record and the other selects the SCOPE_IDENTITY(). The SCOPE_IDENTITY() is placed in Row 0, Column 0 which lets the ExecuteScalar Command return it as its return value. Once you have the Primary Key for the Customer, you can now issue a separate select command on the table. You probably have a method to do this already:

FetchCustomerById(int customerId) {...}

Solution #2: Retrieve Row After Insert In One Roundtrip

You can do it all in 1 roundtrip as such:

SqlDatabase db = new SqlDatabase("...");

string sql = "INSERT INTO Customers (FirstName,LastName)
                  VALUES (@FirstName,@LastName)
              SELECT CustomerId, FirstName, LastName, Rowversion
              FROM Customers WHERE CustomerId = SCOPE_IDENTITY()";
DbCommand command = db.GetSqlStringCommand(sql);
db.AddInParameter(command,"@FirstName",DbType.String, "David");
db.AddInParameter(command,"@LastName",DbType.String, "Hayden");

int customerId;
string firstName;
string lastName;
byte[] rowversion;

using (IDataReader dr = db.ExecuteReader(command))
{
    if (dr.Read())
    {
        customerId = (int)dr["CustomerId"];
        firstName = (string)dr["FirstName"];
        lastName = (string)dr["LastName"];
        rowversion = (byte[])dr["Rowversion"];
    }
}

In this instance we execute an INSERT Statement and SELECT Statement in the same ExecuteReader Command. It saves us from having to make an extra roundtrip to the database if performance is critical in your application. You, of course, wouldn't have to query for all fields if you really only wanted the PK and Timestamp of the record inserted. Typically you just select all of them and then have another method that parses the IDataReader object for its values:

LoadCustomerFromDataReader(IDataReader dr) {...}

Here are a few related articles to this subject:

Source: David Hayden ( .NET Developer )

Filed: ADO.NET Tutorials, Enterprise Library 2.0 Tutorials

posted on Wednesday, July 26, 2006 1:53 PM