public async Task <bool> UpdateTourAsync(Tour tour)
{
bool result = false;
try
{
using (SqlConnection cnn = new SqlConnection(connectionStr))
{
if (cnn.State == ConnectionState.Closed)
{
cnn.Open();
}
using (SqlTransaction transaction = cnn.BeginTransaction())
{
using (SqlCommand cmd = new SqlCommand())
{
cmd.Connection = cnn;
cmd.Transaction = transaction;
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "spUpdateTour";
try
{
cmd.Parameters.AddWithValue("@Id", tour.Id);
cmd.Parameters.AddWithValue("@Name", tour.Name);
cmd.Parameters.AddWithValue("@Description", tour.Description);
cmd.Parameters.AddWithValue("@FromDate", tour.FromDate);
cmd.Parameters.AddWithValue("@ToDate", tour.ToDate);
cmd.Parameters.AddWithValue("@AdultFare", tour.AdultFare);
cmd.Parameters.AddWithValue("@KidFare", tour.KidFare);
cmd.Parameters.AddWithValue("@Transport", tour.Transport);
cmd.Parameters.AddWithValue("@MaxGuest", tour.MaxGuest);
cmd.Parameters.AddWithValue("@Image", tour.Image);
cmd.Parameters.AddWithValue("@IsActive", tour.IsActive);
result = await cmd.ExecuteNonQueryAsync() > 0;
if (result) // if update tour successfully, then update details
{
result = false;
cmd.CommandText = "spDeleteTourGuidesByTourId";
cmd.Parameters.Clear();
cmd.Parameters.AddWithValue("@Id", tour.Id);
result = await cmd.ExecuteNonQueryAsync() > 0;
if (!result)
{
transaction.Rollback(); // if delete tour destination failed then roll back transaction
}
else
{
result = false;
cmd.CommandText = "spAddTourGuide";
foreach (AppUser guide in tour.Guides)
{
result = false;
cmd.Parameters.Clear();
cmd.Parameters.AddWithValue("@TourId", tour.Id);
cmd.Parameters.AddWithValue("@UserId", guide.Id);
cmd.Parameters.AddWithValue("@AssignDate", DateTime.Now);
result = await cmd.ExecuteNonQueryAsync() > 0;
if (!result)
{
transaction.Rollback(); // if update tour guide failed then roll back transaction
break; // stop for to return
}
}
if (result) // if update tour guides successfully
{
cmd.CommandText = "spDeleteTourDestinationsByTourId";
cmd.Parameters.Clear();
cmd.Parameters.AddWithValue("@Id", tour.Id);
result = await cmd.ExecuteNonQueryAsync() > 0;
if (!result)
{
transaction.Rollback(); // if delete tour destination failed then roll back transaction
}
else
{
result = false;
int i = 0;
cmd.CommandText = "spAddTourDestination";
foreach (Destination destination in tour.Destinations)
{
result = false;
cmd.Parameters.Clear();
cmd.Parameters.AddWithValue("@TourId", tour.Id);
cmd.Parameters.AddWithValue("@DestinationId", destination.Id);
cmd.Parameters.AddWithValue("@IndexNumber", i);
i++;
result = await cmd.ExecuteNonQueryAsync() > 0;
if (!result)
{
transaction.Rollback(); // if update tour destination failed then roll back transaction
break; // stop for to return
}
}
}
}
}
}
transaction.Commit(); // commit transaction whether update tour is successful or not
}
catch (Exception ex)
{
logger.Error(ex.Message);
try
{
transaction.Rollback(); //if an exception is throw, roll back transaction
}
catch (Exception rollbackEx)
{
logger.Error(rollbackEx.Message);
}
}
}
}
}
}
catch (Exception)
{
throw;
}
return(result);
}
public async Task <IEnumerable <Tour> > SearchToursAsync(TourSearchViewModel search)
{
List <Tour> result = null;
Tour tour;
try
{
using (SqlConnection cnn = new SqlConnection(connectionStr))
{
SqlCommand cmd;
if (string.IsNullOrEmpty(search.Destination))
{
cmd = new SqlCommand("spSearchTours", cnn);
}
else
{
cmd = new SqlCommand("spSearchToursIncludeDestinationId", cnn);
}
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@Skip", search.Skip);
cmd.Parameters.AddWithValue("@Fetch", search.Fetch);
if (!string.IsNullOrEmpty(search.Id))
{
cmd.Parameters.AddWithValue("@Id", $"%{search.Id}%");
}
if (!string.IsNullOrEmpty(search.Name))
{
cmd.Parameters.AddWithValue("@Name", $"%{search.Name}%");
}
if (!string.IsNullOrEmpty(search.Destination))
{
cmd.Parameters.AddWithValue("@DestinationId", search.Destination);
}
if (search.FromDate != null)
{
cmd.Parameters.AddWithValue("@FromDate", search.FromDate);
}
if (search.Duration > 0 && search.Duration < 8)
{
cmd.Parameters.AddWithValue("@Duration", search.Duration * 24);
}
if (search.MinPrice != null)
{
cmd.Parameters.AddWithValue("@MinPrice", search.MinPrice);
}
if (search.MaxPrice != null)
{
cmd.Parameters.AddWithValue("@MaxPrice", search.MaxPrice);
}
if (search.IsActive == null)
{
cmd.Parameters.AddWithValue("@IsActive", true);
cmd.Parameters.AddWithValue("@IsActiveCheck", false);
}
else
{
cmd.Parameters.AddWithValue("@IsActive", search.IsActive);
cmd.Parameters.AddWithValue("@IsActiveCheck", search.IsActive);
}
if (cnn.State == ConnectionState.Closed)
{
cnn.Open();
}
using (SqlDataReader sdr = await cmd.ExecuteReaderAsync(CommandBehavior.SequentialAccess))
{
if (sdr.HasRows)
{
result = new List <Tour>();
while (await sdr.ReadAsync())
{
tour = new Tour();
tour.Id = sdr.GetString(sdr.GetOrdinal("Id"));
tour.Name = sdr.GetString(sdr.GetOrdinal("Name"));
tour.Description = sdr.GetString(sdr.GetOrdinal("Description"));
tour.FromDate = sdr.GetDateTime(sdr.GetOrdinal("FromDate"));
tour.ToDate = sdr.GetDateTime(sdr.GetOrdinal("ToDate"));
tour.Transport = sdr.GetString(sdr.GetOrdinal("Transport"));
tour.AdultFare = sdr.GetDecimal(sdr.GetOrdinal("AdultFare"));
tour.KidFare = sdr.GetDecimal(sdr.GetOrdinal("KidFare"));
tour.Image = sdr.GetString(sdr.GetOrdinal("Image"));
tour.Duration = sdr.GetInt32(sdr.GetOrdinal("Duration"));
result.Add(tour);
}
}
}
}
}
catch (Exception)
{
throw;
}
return(result);
}