public IEnumerable <InfrastructureObjectMapInfo> GetObjectsByYear(int year, decimal startLon, decimal startLat, decimal endLon, decimal endLat) { var retVal = new List <InfrastructureObjectMapInfo>(); using (var connection = GetDbConnection()) { var cmd = connection.CreateCommand(); cmd.CommandText = SqlStrings.GetObjectsByYear; var dateFrom = new DateTime(year, 1, 1); var dateTo = new DateTime(year, 12, 31); cmd.AddParameter("@DateFrom", dateFrom); cmd.AddParameter("@DateTo", dateTo); cmd.AddParameter("@LongitudeFrom", startLon); cmd.AddParameter("@LatitudeFrom", startLat); cmd.AddParameter("@LongitudeTo", endLon); cmd.AddParameter("@LatitudeTo", endLat); var dataReader = cmd.ExecuteReader(); while (dataReader.Read()) { var databaseObject = new InfrastructureObjectDatabaseInfo { Id = (long)dataReader["id"], Address = dataReader.GetNullableString("address"), ObjectType = dataReader.GetNullableString("object_type"), Latitude = dataReader.GetNullable <decimal>("latitude"), Longitude = dataReader.GetNullable <decimal>("longitude"), ContractNumber = (string)(dataReader["contract_number"] == DBNull.Value ? "" : dataReader["contract_number"]), TempAddress = (string)(dataReader["temp_address"] == DBNull.Value ? "" : dataReader["temp_address"]), TempObjectType = (string)(dataReader["temp_object_type"] == DBNull.Value ? "" : dataReader["temp_object_type"]), WorkType = (string)(dataReader["work_type"] == DBNull.Value ? "" : dataReader["work_type"]), StartDate = (DateTime)(dataReader["start_date"] == DBNull.Value ? null : dataReader["start_date"]), FinishDate = (DateTime)(dataReader["finish_date"] == DBNull.Value ? null : dataReader["finish_date"]), CustomerName = (string)(dataReader["customer_name"] == DBNull.Value ? "" : dataReader["customer_name"]), CustomerPhone = (string)(dataReader["customer_phone"] == DBNull.Value ? "" : dataReader["customer_phone"]), ContractorName = (string)(dataReader["contractor_name"] == DBNull.Value ? "" : dataReader["contractor_name"]), ContractorPhone = (string)(dataReader["contractor_phone"] == DBNull.Value ? "" : dataReader["contractor_phone"]), Url = (string)(dataReader["contract_url"] == DBNull.Value ? "" : dataReader["contract_url"]), Description = (string)(dataReader["description"] == DBNull.Value ? "" : dataReader["description"]) }; var mapObject = new InfrastructureObjectMapInfo(); mapObject.ConvertToMapObject(databaseObject); retVal.Add(mapObject); } } return(retVal); }
public IEnumerable <InfrastructureObjectMapInfo> GetObjectById(int id) { var retVal = new List <InfrastructureObjectMapInfo>(); using (var connection = GetDbConnection()) { var cmd = connection.CreateCommand(); cmd.CommandText = SqlStrings.GetObjectById; cmd.AddParameter("@id", id); var dataReader = cmd.ExecuteReader(); while (dataReader.Read()) { var databaseObject = new InfrastructureObjectDatabaseInfo { Id = (long)dataReader["id"], ContractNumber = (string)(dataReader["contract_number"] == DBNull.Value ? "" : dataReader["contract_number"]), Address = (string)(dataReader["address"] == DBNull.Value ? "" : dataReader["address"]), TempAddress = (string)(dataReader["temp_address"] == DBNull.Value ? "" : dataReader["temp_address"]), Latitude = (decimal)(dataReader["latitude"] == DBNull.Value ? new decimal() : dataReader["latitude"]), Longitude = (decimal)(dataReader["longitude"] == DBNull.Value ? new decimal() : dataReader["longitude"]), TempObjectType = (string)(dataReader["temp_object_type"] == DBNull.Value ? "" : dataReader["temp_object_type"]), ObjectType = (string)(dataReader["object_type"] == DBNull.Value ? "" : dataReader["object_type"]), WorkType = (string)(dataReader["work_type"] == DBNull.Value ? "" : dataReader["work_type"]), StartDate = (DateTime)(dataReader["start_date"] == DBNull.Value ? null : dataReader["start_date"]), FinishDate = (DateTime)(dataReader["finish_date"] == DBNull.Value ? null : dataReader["finish_date"]), CustomerName = (string)(dataReader["customer_name"] == DBNull.Value ? "" : dataReader["customer_name"]), CustomerPhone = (string)(dataReader["customer_phone"] == DBNull.Value ? "" : dataReader["customer_phone"]), ContractorName = (string)(dataReader["contractor_name"] == DBNull.Value ? "" : dataReader["contractor_name"]), ContractorPhone = (string)(dataReader["contractor_phone"] == DBNull.Value ? "" : dataReader["contractor_phone"]), Url = (string)(dataReader["contract_url"] == DBNull.Value ? "" : dataReader["contract_url"]), Description = (string)(dataReader["description"] == DBNull.Value ? "" : dataReader["description"]) }; var mapObject = new InfrastructureObjectMapInfo(); mapObject.ConvertToMapObject(databaseObject); retVal.Add(mapObject); } } return(retVal); }