private async Task <List <LocationsUserModel> > queryGetLocations(SqlConnection connection1, String id)
{
String commandText1 = "SELECT Cities.City, State.ShortName as stateShort, Country.ShortName as shortCountry, Cities.ID, LocationsUser.id, LocationsUser.Status FROM LocationsUser " +
"INNER JOIN Cities ON Cities.ID = LocationsUser.CityId " +
"INNER JOIN State ON Cities.StateId = State.ID " +
"INNER JOIN Country ON State.CountryId = Country.ID " +
"WHERE UserId = @id";
SqlCommand command1 = new SqlCommand(commandText1, connection1);
List <LocationsUserModel> locations = new List <LocationsUserModel>();
SqlParameter parameter = new SqlParameter("@id", SqlDbType.VarChar);
parameter.Value = id;
command1.Parameters.Add(parameter);
SqlDataReader reader = await command1.ExecuteReaderAsync();
while (await reader.ReadAsync())
{
LocationsUserModel location = new LocationsUserModel();
location.name = reader.GetString(0);
location.shortState = reader.GetString(1);
location.shortCountry = reader.GetString(2);
location.cityId = reader.GetInt32(3);
location.id = reader.GetInt32(4);
location.status = reader.GetBoolean(5);
locations.Add(location);
}
reader.Close();
return(locations);
}
private async Task CreateLocationData(SqlConnection connection1, RegisterUserModel registerUser, String id)
{
SqlCommand command1 = new SqlCommand();
command1.Connection = connection1;
StringBuilder sqlBuilder = new StringBuilder();
sqlBuilder.Append("INSERT INTO LocationsUser (CityId, UserId, Status) VALUES ");
var i = 1;
LocationsUserModel lastItem = registerUser.locations[registerUser.locations.Count - 1];
foreach (LocationsUserModel location in registerUser.locations)
{
var paramCityId = "@paramCityId" + i.ToString();
var paramUserId = "@paramUserId" + i.ToString();
var paramStatus = "@statusValue" + i.ToString();
if (location != lastItem)
{
sqlBuilder.AppendFormat(" ( {0}, {1}, {2} ), ", paramCityId, paramUserId, paramStatus);
}
else
{
sqlBuilder.AppendFormat(" ( {0}, {1}, {2} ) ", paramCityId, paramUserId, paramStatus);
}
command1.Parameters.AddWithValue(paramCityId, location.cityId);
command1.Parameters.AddWithValue(paramUserId, id);
command1.Parameters.AddWithValue(paramStatus, true);
i++;
}
command1.CommandText = sqlBuilder.ToString();
await command1.ExecuteNonQueryAsync();
}
private async Task UpdateLocationData(SqlConnection connection1, RegisterUserModel registerUser, String id)
{
SqlCommand command1 = new SqlCommand();
command1.Connection = connection1;
StringBuilder sqlBuilder = new StringBuilder();
sqlBuilder.Append("INSERT INTO LocationsUser (CityId, UserId, Status) VALUES ");
var i = 1;
bool flagInsert = false;
LocationsUserModel lastItem = registerUser.locations[registerUser.locations.Count - 1];
foreach (LocationsUserModel location in registerUser.locations)
{
if (location.id == 0) //Es un insert
{
var paramCityId = "@paramCityId" + i.ToString();
var paramUserId = "@paramUserId" + i.ToString();
var paramStatusLocation = "@paramStatusLocation" + i.ToString();
sqlBuilder.AppendFormat(" ( {0}, {1}, {2} ),", paramCityId, paramUserId, paramStatusLocation);
command1.Parameters.AddWithValue(paramCityId, location.cityId);
command1.Parameters.AddWithValue(paramUserId, id);
command1.Parameters.AddWithValue(paramStatusLocation, location.status);
flagInsert = true;
}
else //Es un update
{
await UpdateSingleLocationData(connection1, id, location);
}
i++;
}
if (flagInsert)
{
String sqlString = sqlBuilder.ToString();
sqlString = sqlString.Remove(sqlString.Length - 1);
command1.CommandText = sqlString;
await command1.ExecuteNonQueryAsync();
}
}
private async Task UpdateSingleLocationData(SqlConnection connection1, String id, LocationsUserModel location)
{
String commandText1 = "UPDATE LocationsUser " +
"SET CityId = @cityId, " +
"Status = @Status " +
"WHERE ID = @id";
SqlCommand command1 = new SqlCommand(commandText1, connection1);
SqlParameter parameter = new SqlParameter("@id", SqlDbType.VarChar);
parameter.Value = location.id;
command1.Parameters.Add(parameter);
parameter = new SqlParameter("@cityId", SqlDbType.Int);
parameter.Value = location.cityId;
command1.Parameters.Add(parameter);
parameter = new SqlParameter("@Status", SqlDbType.Bit);
parameter.Value = location.status;
command1.Parameters.Add(parameter);
await command1.ExecuteNonQueryAsync();
}
