/// <summary>
/// Retrieves a single Note Record from the database
/// </summary>
/// <param name="id">Note identifier</param>
public async Task <Note> GetNote(int id)
{
Note noteRecord = null;
using (var conn = GetConnection())
{
conn.Open();
var cmd = new MySqlCommand("SELECT * FROM note WHERE NoteId = @noteId", conn);
cmd.Parameters.AddWithValue("@noteId", id);
using (var reader = await cmd.ExecuteReaderAsync())
{
while (reader.Read())
{
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 = await BasicCoordinatesDBContext.GetGeoposition(Convert.ToUInt32(reader["NoteId"]), Common.CoordinatesParentType.Note),
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);
}
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 the whole list of Note Records from the database
/// </summary>
public async Task <List <Note> > GetNotes()
{
var list = new List <Note>();
using (var conn = GetConnection())
{
conn.Open();
var cmd = new MySqlCommand("SELECT * FROM note", conn);
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")));
list.Add(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(list);
}
