Getting the Identity value from an inserted SQL Row

[zachattack05]

I am writing a custom MembershipProvider for ASP.NET and would like to record certain events in a table when they happen.
I have the methods to do it, but I would like to get the value of the "ID" column of the row I insert into the table.

I have the following method (which is obviously incomplete), but I'm not sure if it will work and I'm not in a place where I can test it yet, so I thought I would ask if this appears to be a valid way of retreiving the RecordID value.

The SQL Query and the SqlCommand object are my biggest concern.

The command includes "OUTPUT Inserted.RecordID" which should work according to this. If the command is okay, then my concern is how to execute the command to read the returned value. I figured the ExecuteScalar() method is the best choice, but I wasn't completely sure what type of object is returned. Is it a row/column set or is it the literal value returned from the server where casting it as Int32 would work?

Anyway, suggestions? Comments? Ideas?

        private bool EventLog(int userNumber, int userID, UserEventTypes eventType, out int recordID)
        {
            SqlConnection Conn = new SqlConnection(ConnectionString);
            SqlCommand EventCommand = new SqlCommand("INSERT INTO EventLog " +
            " (UserNumber, UserID, EventType, DateTime) OUTPUT Inserted.RecordID Values(?,?,?,?)", Conn);

            EventCommand.Parameters.Add("@UserNumber", SqlDbType.Int).Value = userNumber;
            EventCommand.Parameters.Add("@UserID", SqlDbType.NVarChar, 50).Value = userID;
            EventCommand.Parameters.Add("@EventType", SqlDbType.NVarChar, 50).Value = eventType.ToString();
            EventCommand.Parameters.Add("@DateTime", SqlDbType.DateTime).Value = DateTime.Now;

            try
            {
                Conn.Open();

                Int32 EventRecordID = (Int32) EventCommand.ExecuteScalar();

                recordID = EventRecordID;
            }
            catch (Exception)
            {

                throw;
            }
        }

Thanks a ton! You guys are always so helpful!

[darkagn]

ExecuteScalar always returns a single value, so you should use it on any query that is expected to return a single record, single field result set. It's represented as object because its unknown what the correct data type should be but should be converted to whatever data type you expect. You will need to handle DBNull.Value during your conversion when using ExecuteScalar, but in your specific example this probably won't be necessary as your query should always either return an Int32 or throw an SQL exception if a timeout or connection error occurs for example.

Your other option would be to use ExecuteReader. This can be used to return the list of records with one or more fields, and then you iterate over the DataReader's Read() call to read each field from an array by column name or index. But in your example query this would be overkill and I would just use ExecuteScalar instead.

[Teme64]

ExecuteScalar() method is the correct choice. However returning identity value is not done correctly.
Here's a rewritten code. I also replaced questionmarks with parameter names.

    private bool EventLog(int userNumber, int userID, UserEventTypes eventType, out int recordID)
    {
        string ConnectionString;
        object scalarObject; // Identity value to be returned

        // Return '0' if something goes wrong
        recordID = 0;

        ConnectionString = @"Server=ANGELINA\SQLEXPRESS;Database=BlogTest;User Id=xxxxxxxx;Password=yyyyyyyy";

        SqlConnection Conn = new SqlConnection(ConnectionString);

        // Question marks are replaced with parameter names
        SqlCommand EventCommand = new SqlCommand("INSERT INTO EventLog " +
        " (UserNumber, UserID, EventType, DateTime) Values(@UserNumber,@UserID,@EventType,@DateTime);" +
        "SELECT @@IDENTITY AS 'EventRecordID';", Conn);
        // @@IDENTITY function returns last identity value which is assigned to EventRecordID name

        EventCommand.Parameters.Add("@UserNumber", SqlDbType.Int).Value = userNumber;
        EventCommand.Parameters.Add("@UserID", SqlDbType.NVarChar, 50).Value = userID.ToString();
        EventCommand.Parameters.Add("@EventType", SqlDbType.NVarChar, 50).Value = eventType.ToString();
        EventCommand.Parameters.Add("@DateTime", SqlDbType.DateTime).Value = DateTime.Now;
        try
        {
            Conn.Open();
            // ExecuteScalar() returns an object so assign it to a variable which is of type object
            scalarObject = EventCommand.ExecuteScalar();
            // If the returned object is of integer type convert it to an integer
            // You could/should check that scalarObject variable is not null value
            if (int.TryParse(scalarObject.ToString(), out recordID))
            {
                // Now recordID has identity field's value which will be returned. The 
                // messagebox is just for debugging
                MessageBox.Show("Inserted record with ID value: " + recordID.ToString());
            }
        }
        catch (Exception)
        {
            throw;
        }
        finally
        {
            // Finally close the connection
            if (Conn.State != ConnectionState.Closed)
            {
                Conn.Close();
            }
            // If you don't re-use connection and command objects you should dispose them
            EventCommand = null;
            Conn = null;
        }
        return true; // Or something?
    }

HTH

[zachattack05]

Sorry for taking so long to respond, I've been very busy!

Thank you both so much for your help! You guys are the best!