Unable to get record Id for Insert command using ADO.NET in .NET 4.0

Question

I am working on ADO.NET with NET Framework 4.0 Insert Command. I need to return the primary of created record. I have implemented following code but getting empty value. I want to ensure I get correct Id and not just most recently created as it may be scenario where multiple user trying to add record at the same time

private bool InsertData()
    {
        bool isRecordCreated = false;

        try
        {
            using (SqlCommand cmd = new SqlCommand(InsertScript(), dbConnection))
            {
                
                cmd.Parameters.Add("@UserProfileStatus", SqlDbType.Int).Value = UserProfiles.UserProfileStatus;
                cmd.Parameters.Add("@UserProfileAccount", SqlDbType.VarChar).Value = UserProfiles.UserProfileAccount;
                cmd.Parameters.Add("@UserProfileTimeStamp", SqlDbType.DateTime).Value = UserProfiles.UserProfileTimeStamp;
                cmd.Parameters.Add("@UserProfileId", SqlDbType.Int).Direction = ParameterDirection.Output;

                dbConnection.Open();

                cmd.ExecuteNonQuery();

                string id = cmd.Parameters["@UserProfileId"].Value.ToString(); // need help here.....
            }              
        }
        catch(Exception ex)
        {
            Console.WriteLine(ex.Message);
        }
        finally
        {
            if (dbConnection != null)
            {
                dbConnection.Close();
            }
        }

        return isRecordCreated;
    }

    private string InsertScript()
    {
        string script = string.Empty;

        script = "INSERT INTO [dbo].[UserProfile]([UserProfileStatus],[UserProfileAccount],[UserProfileTimeStamp]) " +
                 "VALUES (@UserProfileStatus, @UserProfileAccount, @UserProfileTimeStamp)";

        return script;
    }

`@"INSERT INTO [dbo].[UserProfile]([UserProfileStatus],[UserProfileAccount],[UserProfileTimeStamp]) VALUES (@UserProfileStatus, @UserProfileAccount, @UserProfileTimeStamp); SET @UserProfileId = SCOPE_IDENTITY();"` — Charlieface, Jul 24 '22 at 16:15
Does this answer your question? [Get the last inserted row ID (with SQL statement)](https://stackoverflow.com/questions/9477502/get-the-last-inserted-row-id-with-sql-statement) Also https://stackoverflow.com/questions/5228780/how-to-get-last-inserted-id — Charlieface, Jul 24 '22 at 16:15
@DaleK Because the question wasn't originally tagged [tag:sql-server] so couldn't dupe-hammer it, even if I edit the tags. I still edited the tags in order that others like you might come along and sort it out. — Charlieface, Jul 24 '22 at 23:22

Steve · Accepted Answer · 2022-07-24T16:22:19.060

You just need to add this line to your previous script

script = @"INSERT INTO [dbo].[UserProfile]([UserProfileStatus], 
               [UserProfileAccount],[UserProfileTimeStamp]) 
           VALUES (@UserProfileStatus, @UserProfileAccount,  
                   @UserProfileTimeStamp);
           SELECT SCOPE_IDENTITY();";

And then execute the query using ExecuteScalar removing the output parameter. The SCOPE_IDENTITY will return the next IDENTITY value generated on the current connection. So it will be independent on what other users are doing

int id = cmd.ExecuteScalar();

Notice how the first part of the script is separated from the second part adding a semicolon. This technique is called "Batch Sql Statements" and is supported by Sql Server

Unable to get record Id for Insert command using ADO.NET in .NET 4.0

1 Answers1