Transaction begin, commit or rollback while inserting in multiple table if any exception occurs.

Protect void InsertRecord(object sender,EventArgs e)
{ 
string strConnection = string.Empty;
SqlConnection sqlCon;
SqlCommand sqlCommand;
SqlTransaction sqlTran = null;
DataSet ds;

Try
{
BaseHelper objBase = new BaseHelper();
strConnection = objBase.getConnectionString(companyId, “TRM”);

sqlCon = new SqlConnection();
sqlCon.ConnectionString = strConnection;
sqlCon.Open();
sqlTran = sqlCon.BeginTransaction();
#region ————First Table Insert————————
sqlCommand = new SqlCommand();
sqlCommand.CommandText = sp_test
sqlCommand.CommandType = CommandType.StoredProcedure;
sqlCommand.Connection = sqlCon;
sqlCommand.Transaction = sqlTran;
sqlCommand.Parameters.Add(“@qryno”, SqlDbType.TinyInt).Value =”123″;
string[] sSplit = sqlCommand.ExecuteScalar().ToString().Split(‘#’);
id= Convert.ToInt32(sSplit[0].ToString());
name= sSplit[1].ToString();
#endregion ————First Table Insert————————

#region ————Second Table Insert————————

sqlCommand = new SqlCommand();
sqlCommand.CommandText = “fsp_Hotel_Reservation_Ins”;
sqlCommand.CommandType = CommandType.StoredProcedure;
sqlCommand.Connection = sqlCon;
sqlCommand.Transaction = sqlTran;
for(int i=0;i<5;i++)
{
sqlCommand.Parameters.Clear();
sqlCommand.Parameters.Add(“@id”, SqlDbType.BigInt).Value = Convert.ToInt32(Id)
Id = Convert.ToInt32(sqlCommand.ExecuteScalar());
}

#region ————Second Table Insert————————

sqlTran.Commit();
sMsg = “DONE#” + reservationID.ToString() + “#” + ReservationRef;
}
catch (Exception ex)
{
sqlTran.Rollback();
sMsg = “ERROR#ERROR”;
appendString(“—- In Catch:log Error —-“);
appendString(“—- Error : —-” + ex.ToString());
appendString(“—- XML : —-” + xDoc.OuterXml);
}
finally
{
sqlCon.Close();
if (strLog.Length > 0)
{
CommonLib.WriteTraceInfo(“App_Code”, “Test Class”, strLog.ToString(), companyId);
strLog.Remove(0, strLog.Length);
}
}
}