Returning the identity column value from a TableAdapter DBDirect method

  • Article

A common scenario when using tables with an auto-increment primary key is to get the value of the identity column for the row you just inserted. Sometimes you can't, or don't want to, use DataSets, which is why we created the DBDirect methods on the TableAdapter. However, the default INSERT function doesn't return the primary key value. How can we get this in one step?

I've created a Visual Basic Console Application and added a DataSource with the Orders table from the SQL Northwind database. The first thing I'm going to do is make a custom INSERT query on the OrdersTableAdapter. Go to the Northwind.xsd file, right-click on OrdersTableAdapter, and choose Add -> "Query...". The TableAdapter Query Wizard will appear. Choose "Use SQL Statements" since we won't be using stored procedures in this example. Select "INSERT" for the query type. Now the default SQL statement appears:

INSERT INTO [dbo].[Orders] ([CustomerID], [EmployeeID], [OrderDate], [RequiredDate],
[ShippedDate], [ShipVia], [Freight], [ShipName], [ShipAddress], [ShipCity], [ShipRegion], [ShipPostalCode],
[ShipCountry]) VALUES (@CustomerID, @EmployeeID, @OrderDate, @RequiredDate, @ShippedDate, @ShipVia, @Freight,
@ShipName, @ShipAddress, @ShipCity, @ShipRegion, @ShipPostalCode, @ShipCountry);
SELECT OrderID, CustomerID, EmployeeID, OrderDate, RequiredDate, ShippedDate, ShipVia, Freight, ShipName,
ShipAddress, ShipCity, ShipRegion, ShipPostalCode, ShipCountry FROM Orders WHERE
(OrderID = SCOPE_IDENTITY())

Let's delete the bottom half since we won't be using this query to refresh a DataSet:

INSERT INTO [dbo].[Orders] ([CustomerID], [EmployeeID], [OrderDate], [RequiredDate],
[ShippedDate], [ShipVia], [Freight], [ShipName], [ShipAddress], [ShipCity], [ShipRegion], [ShipPostalCode],
[ShipCountry]) VALUES (@CustomerID, @EmployeeID, @OrderDate, @RequiredDate, @ShippedDate, @ShipVia, @Freight,
@ShipName, @ShipAddress, @ShipCity, @ShipRegion, @ShipPostalCode, @ShipCountry);

That's a bit more manageable. Now comes the magic part. We'll add a statement to return the @@IDENTITY value:

INSERT INTO [dbo].[Orders] ([CustomerID], [EmployeeID], [OrderDate], [RequiredDate],
[ShippedDate], [ShipVia], [Freight], [ShipName], [ShipAddress], [ShipCity], [ShipRegion], [ShipPostalCode],
[ShipCountry]) VALUES (@CustomerID, @EmployeeID, @OrderDate, @RequiredDate, @ShippedDate, @ShipVia, @Freight,
@ShipName, @ShipAddress, @ShipCity, @ShipRegion, @ShipPostalCode, @ShipCountry);
SELECT @@IDENTITY;

I named the new function "InsertAndReturnIdentity". There's one last important piece: select the new query in the DataSet Designer and change the ExecuteMode property from NonQuery to Scalar. We'll explain the difference between the two in a later entry.

Now we're ready to code. We'll create a new TableAdapter and insert a row with a bunch of dummy values:

Sub Main()
Dim ordersTA As New NorthwindDataSetTableAdapters.OrdersTableAdapter
Dim id As Integer
id = ordersTA.InsertAndReturnIdentity("ALFKI", Nothing, Nothing, _
Nothing, Nothing, Nothing, Nothing, "", "", "", "", "", "")
Console.WriteLine("The new OrderID is {0}", id)
End Sub

The output is:
The new OrderID is 11083

And that's all there is to it!

- Ryan Cavanaugh