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

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;
    }

CodePudding user response：

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