Using SQL Transactions Properly

Today, I was creating a small tiny script to generate a set of acocunts to be placed in my Database at work. I thought that such a small script doesn't require creating Stored Procedures. So I thought of using simple scripts to execute. However, I had a requirement which was that, if one transaction failed, I must stop the execution of all other scripts executing. I remember I can use SqlTransactions inside my C# code, and not inside TSQL Stored Procedures. Make sure always, when you have a set of SQL queries to execute to use SqlTransaction. The code below works for both ASP.NET 1.x/2.0. 

using ( SqlConnection DbConnection = 
                new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString))
            {
                using (SqlCommand DbCommand = DbConnection.CreateCommand())
                {
                    SqlTransaction DbTransaction = null;

                    try
                    {
                        //Open connection required by BeginTransaction
                        DbConnection.Open();

                         // Begin the transaction
                        DbTransaction = DbConnection.BeginTransaction();

                         // Assign Transaction to Command
                        DbCommand.Transaction = DbTransaction;
 
                        // Execute Create User Command
                        DbCommand.CommandText = "INSERT INTO TableName(Name) VALUES ('Bilal Haidar')";
                        DbCommand.ExecuteNonQuery();

                         // Execute Get newly inserted User ID
                        DbCommand.CommandText = "SELECT MAX(UserID) FROM TableName"; 
                        int UserID = Convert.ToInt32(DbCommand.ExecuteScalar().ToString());

                         // If you reached this stage, commit transaction
                         DbTransaction.Commit();
                    }
                    catch
                    {
                        DbTransaction.Rollback();
                    }
              }

 }

Regards