Access and @@Identity
The Jet 4.0 provider supports @@Identity, which means that developers no longer need to use Select Max(ID) or some other contrived method of obtaining new ID values. The key to @@Identity is that it returns the value of an autoincrement column that is generated on the same connection.
This last bit is important, because it means that the Connection object used for the Insert query must be re-used without closing it and opening it up again. Access doesn't support batch statements, so each must be run separately. It is also therefore possible, though not necessary, to create a new Command object to run the Select @@Identity query. The following code shows this in action where the Connection object is opened, then the first query is executed against cmd using ExecuteNonQuery() to perfom the Insert, followed by changing the CommandText property of cmd to "Select @@Identity" and running that.
//C#
string query = "Insert Into Categories (CategoryName) Values (?)";
string query2 = "Select @@Identity";
int ID;
string connect = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=|DataDirectory|Northwind.mdb";
using (OleDbConnection conn = new OleDbConnection(connect))
{
using (OleDbCommand cmd = new OleDbCommand(query, conn))
{
cmd.Parameters.AddWithValue("", Category.Text);
conn.Open();
cmd.ExecuteNonQuery();
cmd.CommandText = query2;
ID = (int)cmd.ExecuteScalar();
}
}
'VB
Dim query As String = "Insert Into Categories (CategoryName) Values (?)"
Dim query2 As String = "Select @@Identity"
Dim ID As Integer
Dim connect As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=|DataDirectory|Northwind.mdb"
Using conn As New OleDbConnection(connect)
Using cmd As New OleDbCommand(query, conn)
cmd.Parameters.AddWithValue("", Category.Text)
conn.Open()
cmd.ExecuteNonQuery()
cmd.CommandText = query2
ID = cmd.ExecuteScalar()
End Using
End Using
A quick word about the absence of conn.Close() in all these examples: in all the snippets, the Connection object is instantiated within a 'using' block. At the end of the 'using' block, Dispose() is automatically called on objects created in the beginning of the block. If you do not employ 'using' blocks, make sure you explicitly call conn.Close() as soon as you are done with the connection.
Sql Server and Scope_Identity()
While Sql Server also supports @@Identity, the recommended method for obtaining identity values on this platform is Scope_Identity(), which retrieves the last identity value created in the current scope. 'Scope' is a single module, which can be a stored procedure, batch, function or trigger. This can be used in a number of ways. Sql Server supports batch statements which would have you append Select Scope_Identity() to the end of the Insert statement, optionally separating the two statements with a semicolon, and just using ExecuteScalar() against the batch command to return the single value:
//C#
string query = "Insert Into Categories (CategoryName) Values (@CategoryName);" +
"Select Scope_Identity()";
int ID;
string connect = @"Server=.\SQLExpress;AttachDbFilename=|DataDirectory|Northwind.mdf;" +
"Database=Northwind;Trusted_Connection=Yes;";
using (SqlConnection conn = new SqlConnection(connect))
{
using (SqlCommand cmd = new SqlCommand(query, conn))
{
cmd.Parameters.AddWithValue("@CategoryName", Category.Text);
conn.Open();
ID = (int)cmd.ExecuteScalar();
}
}
'VB
Dim query As String = "Insert Into Categories (CategoryName) Values (@CategoryName);" & _
"Select Scope_Identity()"
Dim ID As Integer
Dim connect As String = "Server=.\SQLExpress;AttachDbFilename=|DataDirectory|Northwind.mdf;" & _
"Database=Northwind;Trusted_Connection=Yes;"
Using conn As New SqlConnection(connect)
Using cmd As New SqlCommand(query, conn)
cmd.Parameters.AddWithValue("@CategoryName", Category.Text)
conn.Open()
ID = cmd.ExecuteScalar()
End Using
End Using
Alternatively, you may prefer to use an output parameter from a stored procedure, and ExecuteNonQuery().
//C#
string query = "AddCategory";
int ID;
string connect = @"Server=.\SQLExpress;Database=Northwind;Trusted_Connection=Yes;";
using (SqlConnection conn = new SqlConnection(connect))
{
using (SqlCommand cmd = new SqlCommand(query, conn))
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@Category", Category.Text);
cmd.Parameters.Add("@CategoryID", SqlDbType.Int, 0, "CategoryID");
cmd.Parameters["@CategoryID"].Direction = ParameterDirection.Output;
conn.Open();
cmd.ExecuteNonQuery();
ID = (int)cmd.Parameters["@CategoryID"].Value;
}
}
'VB
Dim query As String = "AddCategory"
Dim ID As Integer
Dim connect As String = "Server=.\SQLExpress;Database=Northwind;Trusted_Connection=Yes;"
Using conn As New SqlConnection(connect)
Using cmd As New SqlCommand(query, conn)
cmd.CommandType = CommandType.StoredProcedure
cmd.Parameters.AddWithValue("@Category", Category.Text)
cmd.Parameters.Add("@CategoryID", SqlDbType.Int, 0, "CategoryID")
cmd.Parameters("@CategoryID").Direction = ParameterDirection.Output
conn.Open()
cmd.ExecuteNonQuery()
ID = cmd.Parameters("@CategoryID").Value
End Using
End Using
And the procedure...
CREATE PROCEDURE AddCategory
-- Add the parameters for the stored procedure here
@Category NVARCHAR(15),
@CategoryID INT OUTPUT
AS
BEGIN
SET NOCOUNT ON;
-- Insert statements for procedure here
INSERT INTO Categories (CategoryName) VALUES (@Category)
SET @CategoryID = SCOPE_IDENTITY()
END
Finally, you can create a stored procedure that contains no output parameter, but ends with 'Select Scope_Identity()'. This version requires ExecuteScalar(), and requires less ADO.NET code and a shorter Stored Procedure.
//C#
string query = "AddCategory";
int ID;
string connect = @"Server=.\SQLExpress;Database=Northwind;Trusted_Connection=Yes;";
using (SqlConnection conn = new SqlConnection(connect))
{
using (SqlCommand cmd = new SqlCommand(query, conn))
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@Category", Category.Text);
conn.Open();
ID = (int)cmd.ExecuteScalar();
}
}
'VB
Dim query As String = "AddCategory"
Dim ID As Integer
Dim connect As String = "Server=.\SQLExpress;Database=Northwind;Trusted_Connection=Yes;"
Using conn As New SqlConnection(connect)
Using cmd As New SqlCommand(query, conn)
cmd.CommandType = CommandType.StoredProcedure
cmd.Parameters.AddWithValue("@Category", Category.Text)
conn.Open()
ID = cmd.ExecuteScalar()
End Using
End Using
The (slightly) smaller procedure:
CREATE PROCEDURE AddCategory
-- Add the parameters for the stored procedure here
@Category NVARCHAR(15)
AS
BEGIN
SET NOCOUNT ON;
-- Insert statements for procedure here
INSERT INTO Categories (CategoryName) VALUES (@Category)
SELECT SCOPE_IDENTITY()
END
So which method should you use with SQL Server? ExecuteNonQuery and an output parameter? Or no parameter and ExecuteScalar? The answer to that is whichever way you prefer. There is no real performance difference between the methods. My personal preference is for a stored procedure and ExecuteScalar. It requires the least amount of code :)
DataSource controls
Sql Server
And finally, for those that like to use the SqlDataSource, here's how to use the Insert() method to retrieve Scope_Identity() from SQL Server.
The SqlDataSource control internally performs an ExecuteNonQuery() when it is called on to perform an Insert, so by just appending SELECT SCOPE_IDENTITY() to the InsertCommand and trying to retrieve the return value will not work. This is because ExecuteNonQuery() returns either the number of rows affected by an Update, Insert or Delete operation, or -1 for all other operations, which will overwrite any resultset produced from the appended Select statement. The way to work around this is to use a parameter value. This can be achieved declaratively using the following steps:
- First, add the following to the InsertCommand: "SET @NewId = Scope_Identity()", making sure it is separated from the first part of the command by a semicolon
- Select the SqlDataSource control in Design view
- Hit F4 to bring up its properties
- Click "InsertQuery"
- Click the elipses (...)
- In the Command and Parameter Editor, click "Add Parameter"
- Give the new parameter a name (such as NewId) and click "Show advanced properties"
- Change the Type to Int32 and the Direction to Output
- Click OK to seal the deal.
Now if you switch to Source view, you will see that an additional parameter has been added to the InsertParameters collection:
<asp:Parameter Direction="Output" Name="NewId" Type="Int32" />
To retrieve the value, we need to use the SqlDataSource.Inserted event. Back in Design view, bring up the properties of the SqlDataSource again, if they are not still showing, and click the lightning bolt to open up the events list. Double click the Inserted event, which will create an event handler in code-behind, and associate the SqlDataSource with the event handler. To retrieve the value from the event handler is straightforward:
//C#
protected void SqlDataSource1_Inserted(object sender, SqlDataSourceStatusEventArgs e)
{
int newid = (int)e.Command.Parameters["@NewId"].Value;
}
'VB
Protected Sub SqlDataSource1_Inserted(ByVal sender As Object,
ByVal e As SqlDataSourceStatusEventArgs)
Handles SqlDataSource1.Inserted
Dim newid As Integer = e.Command.Parameters("@NewId").Value
End Sub
Access
The above approach won't work with Access, whether you use a SqlDataSource control or an AccessDataSource control. This is because Access is unable to accept batch commands. An alternative that will work with Access and the DataSource controls is to reuse the control's Connection object against a new Command object to retrieve @@Identity. In order for this apporach to work, you must reference the System.Data.OleDb namespace in your code-behind with a using or Imports statement as per your language of choice:
//C#
protected void AccessDataSource1_Inserted(object sender, SqlDataSourceStatusEventArgs e)
{
string query = "SELECT @@IDENTITY";
OleDbCommand cmd = new OleDbCommand(query, (OleDbConnection)e.Command.Connection);
int newid = (int)cmd.ExecuteScalar();
}
'VB
Protected Sub AccessDataSource1_Inserted(ByVal sender As Object,
ByVal e As SqlDataSourceStatusEventArgs)
Handles AccessDataSource1.Inserted
Dim query As String = "SELECT @@IDENTITY"
Dim cmd As New OleDbCommand(query, CType(e.Command.Connection, OleDbConnection))
Dim newid As Integer = cmd.ExecuteScalar()
End Sub
Scope_Identity() for Sql Server will not work with the above, because the change in command object changes the scope from the original command.
