//Get All User's Addresses
public async Task <ServerResponseAddress> GetAllAddress(AddressSearchQuery query)
{
var response = new ServerResponseAddress();
try
{
if (con.State == ConnectionState.Closed)
{
con.Open();
}
string cmdText = query.AddressQuery != null ? $"Select * from UserAddressTable {(query.AddressQuery.HasCriteria() ? query.AddressQuery.QueryString() : "")}" : $"Select * from UserAddressTable";
//Get total
SqlCommand validate = new SqlCommand(cmdText.Replace("*", "count(*)"), con);
int total = Convert.ToInt32(validate.ExecuteScalar());
cmdText += " Order By DateUpdated desc OFFSET @offset ROWS FETCH NEXT @limit ROWS ONLY";
using (SqlCommand cmd =
new SqlCommand(
cmdText,
con))
{
cmd.Parameters.AddWithValue("@offset", query.Offset);
cmd.Parameters.AddWithValue("@limit", query.Limit);
SqlDataReader reader = await cmd.ExecuteReaderAsync();
if (reader.HasRows)
{
var addresses = new List <UserAddressDetails>();
while (reader.Read())
{
try
{
var address = new UserAddressDetails();
address.Id = reader.IsDBNull(0) ? null : reader.GetString(0);
address.UserId = reader.IsDBNull(1) ? null : reader.GetString(1);
address.Address = reader.IsDBNull(2) ? null : reader.GetString(2);
address.AddressType = reader.IsDBNull(3) ? null : reader.GetString(3);
address.City = reader.IsDBNull(4) ? null : reader.GetString(4);
address.Province = reader.IsDBNull(5) ? null : reader.GetString(5);
address.ZipCode_PostalCode = reader.IsDBNull(6) ? 0 : reader.GetInt32(6);
address.DateCreated = reader.IsDBNull(7) ? DateTime.MinValue : reader.GetDateTime(7);
address.DateUpdated = reader.IsDBNull(8) ? DateTime.MinValue : reader.GetDateTime(8);
addresses.Add(address);
}
catch (Exception ex)
{
Console.WriteLine("Failed to read line: " + reader.ToString() + " Error: " + ex);
}
}
response.Success = true;
response.Total = total;
response.Addresses = addresses;
}
}
}
catch (Exception ex)
{
response.Success = false;
response.ErrorMessage = "Failed to make connection: " + ex;
}
finally
{
con.Close();
}
return(response);
}
