How to retrieve identity using SQLCE and DAAB

Topics: Data Access Application Block
Mar 15, 2009 at 9:08 PM
I am trying to retrieve the identity value inserted into a SQLCE database using the data access application block from the Enterprise Library 4.1.  I have found only one line in the documentation that says to use ExecuteNonQuerySQL().  The 2nd parameter is supposed to be the identity value.  However, I don't understand how its to be used.  If anyone can please provide sample code showing how it should be used, or how to retrieve the identity value any other way, I would appreciate it.  Thanks.
Mar 16, 2009 at 3:34 AM
Add an out parameter to your stored procedure and add also an out parameter to your dbcommand.  Retrieve the parameter value after the call to ExecuteNonQuery.

//create database, dbcommand, and add parameter here
db.AddOutParameter(dbCommand, "returnValue", DbType.Int32, 4);
int id = (int)db.GetParameterValue(dbCommand, "returnValue");

stored procedure:
ALTER procedure [dbo].[insertsample]
@p2 int,
@returnValue int output
INTO [Sample] (NumberOne, NumberTwo) Values(@p1, @p2)
SELECT @returnValue = Scope_Identity()

Sarah Urmeneta
Global Technology & Solutions
Avanade, Inc.

Mar 16, 2009 at 4:06 AM
SQLCE doesn't support stored procedures so that method won't work.  That is why I am having issues.  Enterprise Library closes the connection after each Executexxx command.  So you cannot just do a SELECT @@IDENTITY after the SQL command since it will not longer be in the same session.  That is why you are supposed to use the ExecuteNonQuerySQL() method.  If you read up on it, you will understand further why the issue is there.
Mar 16, 2009 at 4:27 AM
Right, kinda missed the SQLCE word, sorry.  I haven't used SQLCE before but I'll let you know what I can find out.

Sarah Urmeneta
Global Technology & Solutions
Avanade, Inc.

Mar 16, 2009 at 4:54 AM
I just go through the source code.  As I understand from the ExecuteNonQuerySql method, you need to pass the insert statement (and yes, without the SELECT @@IDENTITY statement).

DbParameters[] parameters; //your parameters
DbCommand command; // create command passing the insert statement;
int id = 0;
db.ExecuteNonQuerySql(command, out id, parameters);

after the call to ExecuteNonQuerySql, the variable id should contain the id of the last inserted record.  Inside the ExecuteNonQuerySql method, it executes  the "SELECT @@IDENTITY " statement using the same connection in order to retrieve the last id inserted. 

Sarah Urmeneta
Global Technology & Solutions
Avanade, Inc.
Mar 16, 2009 at 10:08 PM
I didn't realize the source code for the Enterprise Library was available online.  I downloaded it was able to see what I needed to do.  Your example above is slightly incorrect. The 1st parameter is not a command object but is instead a string.  Part of the confusion is the way you call the methods for SqlCeDatabase.Executexxx methods are so different than SqlDatabase.Executexxx.  I'm sure this is why you assumed the 1st parameter to be a DbCommand object and not a string.

Thanks for your help.
Mar 18, 2009 at 2:43 AM
Yup, my bad, it is a string.  If you installed entlib, it also comes with the source code installer so you won't have to download it.

Sarah Urmeneta
Global Technology & Solutions
Avanade, Inc.