c# Retrieving Inserted ID inside Transaction

Question

I have a bunch of inserts which I want to have in a transaction so I can rollback in case of any errors. Now my issue is that for some of the inserts I need the ID of the previous insert. However, doing var id = (int)command.ExecuteScalar(); throws an exception ("Object reference not set to an instance of an object"). If i run command.ExecuteScalar() without trying to assign the result the code work perfectly fine. Is it possible to get ID before committing the transaction? If no, what are my alternatives?

My code:

using (SqlConnection connection = new SqlConnection("ConnectionHere")
{
            connection.Open();

            SqlCommand command = connection.CreateCommand();
            SqlTransaction transaction;

            // Start a local transaction.
            transaction = connection.BeginTransaction();
            command.Connection = connection;
            command.Transaction = transaction;

            try
            {                    
                string insertStudent = "INSERT INTO Students (FirstName, MiddleName, LastName)" +
                " VALUES (@firstName, @middleName, @lastName)";

                command.CommandText = insertStudent;
                command.Parameters.AddWithValue("@firstName", application.FirstName);
                command.Parameters.AddWithValue("@middleName", application.MiddleName);
                command.Parameters.AddWithValue("@lastName", application.LastName);

                var id = (int) command.ExecuteScalar();//EXCEPTION THROWN HERE

                // Attempt to commit the transaction.
                transaction.Commit();
            }
            catch (Exception ex)
            {
                Console.WriteLine("Commit Exception Type: {0}", ex.GetType());
                Console.WriteLine("  Message: {0}", ex.Message);

                // Attempt to roll back the transaction.        
                transaction.Rollback();
            }
}

Answer 1

I assume that a column in the Students table is setup as an identity column, and that you use SQL Server 2008+.

In that case you need to change your SQL statement to return the inserted identity value like this:

  string insertStudent = "INSERT INTO Students (FirstName, MiddleName, LastName)" +
            " VALUES (@firstName, @middleName, @lastName);" +
            " SELECT SCOPE_IDENTITY();";

More information regarding SCOPE_IDENTITY can be found here: https://learn.microsoft.com/en-us/sql/t-sql/functions/scope-identity-transact-sql