IDENT_CURRENT question

[BruceG]

Hello -
Let's say I have multi-user VB 2010 app going against a SQL Server DB. A particular table has an identity column, the value of which I want to retrieve after doing an INSERT. Say the pseudocode is this:

strSQL = "INSERT INTO MyTable (blah blah) ..."
ExecuteNonQuery (strSQL)
strSQL = "SELECT IDENT_CURRENT('MyTable') AS MyTableID"
(process strSQL with DataReader)

Is this safe in a multi-user app? If 2 users are inserting into this table at the same time, what are the chances that I would retrieve the wrong ID?

[wild_bill]

Create an output parm to store the identity

strSQL = "INSERT INTO MyTable (blah blah) ... set @output = @@identity"
ExecuteNonQuery (strSQL)
'TODO: get identity value from output parm

[BruceG]

Thanks wild_bill - in doing my own research on this since my post, I did see that there is an OUTPUT option to the INSERT statement as of SQL 2005, and it can be useful for this purpose.

However, I'm thinking that it is the SCOPE_IDENTITY function that I want to use (as it will return the last inserted value in a given connection/scope), as opposed to IDENT_CURRENT (which gets the last inserted value of a table regardless of scope).

As a general rule, I always do each SQL statement in its own DB connection (i.e.: get in - get it (or do it) - get out). So I'm thinking it would be best to this as follows:

CONNECT TO DB
EXECUTE THE INSERT
DO A "SELECT SCOPE_IDENTITY() AS TheNewID"
EXECUTE DATAREADER (and save the value)
CLOSE CONNECTION

Do you think that is appropriate?

[jmcilhinney]

Not quite appropriate. Don't use a separate DataReader. Do use SCOPE_IDENTITY but either return the ID via an output parameter on the same Command or, if using a DataAdapter, just SELECT the ID straight back into the same DataRow.

[BruceG]

Thanks JM. With your remarks in mind, I refined my search, then found code suitable to adapt to my function "ExecuteIdentityInsert".
Coding style differences aside (as I recall from years back you are not a big fan of Hungarian), the idea of using a single command object has been implemented here.

The function would be called as follows:

Dim strSQL As String  _
    = "INSERT INTO MyTable(EmpFName, EmpLName) OUTPUT Inserted.EmpID VALUES('John', 'Doe')"
Dim intNewID As Integer = ExecuteIdentityInsert(strSQL, "EmpID")

The function itself, which uses a single SqlCommand object, is as follows:

   
 Public Function ExecuteIdentityInsert(ByVal pstrSQL As String, ByVal pstrIDFieldName As String) As Integer

        Dim objConn As New SqlConnection(DB_CONN_STRING)
        Dim objCommand As New SqlCommand
        objCommand.CommandType = CommandType.Text
        objCommand.CommandText = pstrSQL
        objCommand.Connection = objConn
        Dim objParm As SqlParameter = objCommand.CreateParameter
        objParm.SqlDbType = SqlDbType.Int
        objParm.ParameterName = pstrIDFieldName
        objParm.Direction = ParameterDirection.Output
        objCommand.Parameters.Insert(0, objParm)
        objConn.Open()
        Dim objRdr As SqlDataReader = objCommand.ExecuteReader
        objRdr.Read()
        Dim intNewID As Integer = CInt(objRdr(pstrIDFieldName))
        If objCommand.Connection.State = ConnectionState.Open Then objCommand.Connection.Close()
        If objConn.State = ConnectionState.Open Then objConn.Close()

        Return intNewID

    End Function