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 the whole list of Audio Records from the database /// </summary> public async Task <List <Image> > GetImages() { var list = new List <Image>(); using (var conn = GetConnection()) { conn.Open(); var cmd = new MySqlCommand("SELECT * FROM image", conn); using (var reader = await cmd.ExecuteReaderAsync()) { while (reader.Read()) { var coordinates = new BasicGeoposition(ConversionHelpers.SafeGetDouble(reader, reader.GetOrdinal("ImageCoordinateLat")), ConversionHelpers.SafeGetDouble(reader, reader.GetOrdinal("ImageCoordinateLng")), ConversionHelpers.SafeGetDouble(reader, reader.GetOrdinal("ImageCoordinateAlt"))); list.Add(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(list); }
/// <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); }
/// <summary> /// Retrieves a single Note Record from the database /// </summary> /// <param name="id">Note identifier</param> public async Task <Note> GetNote(int noteId) { Note noteRecord = null; using (var conn = GetConnection()) { conn.Open(); var cmd = new MySqlCommand("SELECT * FROM note 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["NoteId"]), BasicGeoposition.CoordinatesParentType.Note, ConversionHelpers.SafeGetDouble(reader, reader.GetOrdinal("NoteCoordinateLat")), ConversionHelpers.SafeGetDouble(reader, reader.GetOrdinal("NoteCoordinateLng")), ConversionHelpers.SafeGetDouble(reader, reader.GetOrdinal("NoteCoordinateAlt"))); noteRecord = new Note() { NoteId = Convert.ToUInt32(reader["NoteId"]), CategoryId = Convert.ToUInt32(reader["CategoryId"]), NoteClassification = (NoteClassification)Convert.ToUInt32(reader["NoteClassification"]), NoteTitle = reader["NoteTitle"].ToString(), APP_GUID = reader["APP_GUID"].ToString(), NoteContent = reader["NoteContent"].ToString(), NoteCoordinates = coordinates, NoteDatetime = ConversionHelpers.SafeGetDateTime(reader, reader.GetOrdinal("NoteDatetime")), CreatedAt = Convert.ToDateTime(reader["CreatedAt"]), CreatedBy = Convert.ToUInt32(reader["CreatedBy"]), ModifiedAt = ConversionHelpers.SafeGetDateTime(reader, reader.GetOrdinal("ModifiedAt")), ModifiedBy = ConversionHelpers.SafeGetInt(reader, reader.GetOrdinal("ModifiedBy")), }; } } } return(noteRecord); }