public async Task <BasicGeoposition> GetGeoposition(uint parentId, CoordinatesParentType parentType) { BasicGeoposition geoposition = null; using (var conn = GetConnection()) { conn.Open(); var cmd = new MySqlCommand(@"SELECT * FROM BasicGeoposition WHERE ParentId = @parentId AND ParentType = @parentType", conn); cmd.Parameters.AddWithValue("@parentId", parentId); cmd.Parameters.AddWithValue("@parentType", parentType); using (var reader = await cmd.ExecuteReaderAsync()) { while (reader.Read()) { geoposition = new BasicGeoposition() { ParentId = Convert.ToUInt32(reader["ParentId"]), ParentType = (CoordinatesParentType)Convert.ToUInt32(reader["ParentType"]), Latitude = ConversionHelpers.SafeGetDouble(reader, reader.GetOrdinal("Latitude")), Longitude = ConversionHelpers.SafeGetDouble(reader, reader.GetOrdinal("Longitude")), Altitude = ConversionHelpers.SafeGetDouble(reader, reader.GetOrdinal("Altitude")), Description = ConversionHelpers.SafeGetString(reader, reader.GetOrdinal("Description")) }; } } } return(geoposition); }
/// <summary> /// Retrieves a single Image Record from the database /// </summary> public async Task <Image> GetImage(uint imageId, uint noteId) { Image imageRecord = null; using (var conn = GetConnection()) { conn.Open(); var cmd = new MySqlCommand("SELECT * FROM Image WHERE ImageId = @imageId AND NoteId = @noteId", conn); cmd.Parameters.AddWithValue("@noteId", noteId); cmd.Parameters.AddWithValue("@imageId", imageId); using (var reader = await cmd.ExecuteReaderAsync()) { while (reader.Read()) { var coordinates = new BasicGeoposition(Convert.ToUInt32(reader["ImageId"]), BasicGeoposition.CoordinatesParentType.Image, ConversionHelpers.SafeGetDouble(reader, reader.GetOrdinal("ImageCoordinateLat")), ConversionHelpers.SafeGetDouble(reader, reader.GetOrdinal("ImageCoordinateLng")), ConversionHelpers.SafeGetDouble(reader, reader.GetOrdinal("ImageCoordinateAlt")), ConversionHelpers.SafeGetString(reader, reader.GetOrdinal("ImageCoordinateDescription"))); imageRecord = new Image() { ImageId = Convert.ToUInt32(reader["ImageId"]), NoteId = Convert.ToUInt32(reader["NoteId"]), ImageNo = reader["ImageNo"].ToString(), ImageName = reader["ImageName"].ToString(), ImageDescription = reader["ImageDescription"].ToString(), ImagePath = reader["ImagePath"].ToString(), ImageCoordinates = coordinates, ImageDatetime = Convert.ToDateTime(reader["ImageDatetime"]), CreatedAt = Convert.ToDateTime(reader["CreatedAt"]), CreatedBy = Convert.ToUInt32(reader["CreatedBy"]), ModifiedAt = Convert.ToDateTime(reader["ModifiedAt"]), ModifiedBy = Convert.ToUInt32(reader["ModifiedBy"]), }; } } } return(imageRecord); }
/// <summary> /// Retrieves a single Photo Record from the database /// </summary> public async Task <Photo> GetPhoto(uint videoId, uint noteId) { Photo photoRecord = null; using (var conn = GetConnection()) { conn.Open(); var cmd = new MySqlCommand("SELECT * FROM Photo WHERE PhotoId = @photoId AND NoteId = @noteId", conn); cmd.Parameters.AddWithValue("@photoId", videoId); cmd.Parameters.AddWithValue("@noteId", noteId); using (var reader = await cmd.ExecuteReaderAsync()) { while (reader.Read()) { var coordinates = new BasicGeoposition(Convert.ToUInt32(reader["PhotoId"]), BasicGeoposition.CoordinatesParentType.Photo, ConversionHelpers.SafeGetDouble(reader, reader.GetOrdinal("PhotoCoordinateLat")), ConversionHelpers.SafeGetDouble(reader, reader.GetOrdinal("PhotoCoordinateLng")), ConversionHelpers.SafeGetDouble(reader, reader.GetOrdinal("PhotoCoordinateAlt")), ConversionHelpers.SafeGetString(reader, reader.GetOrdinal("PhotoCoordinateDescription"))); photoRecord = new Photo() { PhotoId = Convert.ToUInt32(reader["PhotoId"]), NoteId = Convert.ToUInt32(reader["NoteId"]), PhotoNo = reader["PhotoNo"].ToString(), PhotoName = reader["PhotoName"].ToString(), PhotoDescription = reader["PhotoDescription"].ToString(), PhotoPath = reader["PhotoPath"].ToString(), PhotoCoordinates = coordinates, PhotoDatetime = Convert.ToDateTime(reader["PhotoDatetime"]), CreatedAt = Convert.ToDateTime(reader["CreatedAt"]), CreatedBy = Convert.ToUInt32(reader["CreatedBy"]), ModifiedAt = Convert.ToDateTime(reader["ModifiedAt"]), ModifiedBy = Convert.ToUInt32(reader["ModifiedBy"]), }; } } } return(photoRecord); }
/// <summary> /// Retrieves the whole list of Video Records from the database /// </summary> public async Task <List <Video> > GetVideos(uint noteId) { var list = new List <Video>(); using (var conn = GetConnection()) { conn.Open(); var cmd = new MySqlCommand("SELECT * FROM video WHERE NoteId = @noteId", conn); cmd.Parameters.AddWithValue("@noteId", noteId); using (var reader = await cmd.ExecuteReaderAsync()) { while (reader.Read()) { var coordinates = new BasicGeoposition(Convert.ToUInt32(reader["VideoId"]), BasicGeoposition.CoordinatesParentType.Video, ConversionHelpers.SafeGetDouble(reader, reader.GetOrdinal("VideoCoordinateLat")), ConversionHelpers.SafeGetDouble(reader, reader.GetOrdinal("VideoCoordinateLng")), ConversionHelpers.SafeGetDouble(reader, reader.GetOrdinal("VideoCoordinateAlt")), ConversionHelpers.SafeGetString(reader, reader.GetOrdinal("VideoCoordinateDescription"))); list.Add(new Video() { VideoId = Convert.ToUInt32(reader["VideoId"]), NoteId = Convert.ToUInt32(reader["NoteId"]), VideoNo = reader["VideoNo"].ToString(), VideoName = reader["VideoName"].ToString(), VideoDescription = reader["VideoDescription"].ToString(), VideoPath = reader["VideoPath"].ToString(), VideoCoordinates = coordinates, VideoDatetime = Convert.ToDateTime(reader["VideoDatetime"]), CreatedAt = Convert.ToDateTime(reader["CreatedAt"]), CreatedBy = Convert.ToUInt32(reader["CreatedBy"]), ModifiedAt = Convert.ToDateTime(reader["ModifiedAt"]), ModifiedBy = Convert.ToUInt32(reader["ModifiedBy"]), }); } } } return(list); }