//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); }