public void loadBookSongs(string code)
{
try
{
lstSongResults.Items.Clear();
songno = 0;
sqlQuery = "SELECT songid, number, title FROM songs WHERE book='" + code + "';";
appDB = new AppDatabase();
dRowCol = appDB.GetList(sqlQuery);
foreach (DataRow row in dRowCol)
{
songno = int.Parse(row["number"] + "");
lstSongResults.Items.Add(row["number"] + "# " + vsbf.textRender(row["title"].ToString()));
lstSongResults.SelectedIndex = 0;
}
grpSongResults.Text = lstSongResults.Items.Count + " " + lstBookcodes.Text + " songs found";
txtNumber.Placeholder = (songno + 1).ToString();
txtNumber.Text = (songno + 1).ToString();
}
catch (Exception ex)
{
LoadFeedback("Oops! Sorry song listing failed: " + ex.Message, false, true);
}
}
public void loadBookSongs(string code)
{
try
{
lstSongResults.Items.Clear();
lstSongids.Items.Clear();
sqlQuery = "SELECT songid, number, title FROM songs WHERE book='" + code + "';";
appDB = new AppDatabase();
dRowCol = appDB.getList(sqlQuery);
foreach (DataRow row in dRowCol)
{
lstSongResults.Items.Add(row["number"] + "# " + vsbf.textRender(row["title"].ToString()));
lstSongids.Items.Add(row["songid"]);
lstSongResults.SelectedIndex = 0;
lstSongids.SelectedIndex = 0;
}
string[] valuestrings = { lstSongResults.Items.Count.ToString(), lstBookcodes.Text };
grpSongResults.Text = langs.As_Lang_Arr(langs.As_Lang(vsb_lang, "songs_found"), valuestrings);
}
catch (Exception ex)
{
loadFeedback(langs.As_Lang(vsb_lang, "error_songs_listing") + " " + ex.Message, false);
}
}
public void DeleteOwnsong(int id)
{
using (AppDatabase context = new AppDatabase(AppDatabase.DBConnectionString))
{
IQueryable <vsb_ownsong> entityQuery = from own in context.OwnSongs where own.ID.Equals(id) select own;
vsb_ownsong entityToDelete = entityQuery.FirstOrDefault();
context.OwnSongs.DeleteOnSubmit(entityToDelete);
context.SubmitChanges();
}
}
public void DeleteFavourite(int id)
{
using (AppDatabase context = new AppDatabase(AppDatabase.DBConnectionString))
{
IQueryable <vsb_favourite> entityQuery = from fav in context.Favourites where fav.ID.Equals(id) select fav;
vsb_favourite entityToDelete = entityQuery.FirstOrDefault();
context.Favourites.DeleteOnSubmit(entityToDelete);
context.SubmitChanges();
}
}
public void DeleteAllFavourites()
{
using (AppDatabase context = new AppDatabase(AppDatabase.DBConnectionString))
{
IQueryable <vsb_favourite> entityQuery = from favs in context.Favourites select favs;
IList <vsb_favourite> entityToDelete = entityQuery.ToList();
context.Favourites.DeleteAllOnSubmit(entityToDelete);
context.SubmitChanges();
}
}
public void DeleteAllOwnsongs()
{
using (AppDatabase context = new AppDatabase(AppDatabase.DBConnectionString))
{
IQueryable <vsb_ownsong> entityQuery = from owns in context.OwnSongs select owns;
IList <vsb_ownsong> entityToDelete = entityQuery.ToList();
context.OwnSongs.DeleteAllOnSubmit(entityToDelete);
context.SubmitChanges();
}
}
public void AddOwnSong(String title, String content)
{
using (AppDatabase context = new AppDatabase(AppDatabase.DBConnectionString))
{
vsb_ownsong ownsong = new vsb_ownsong();
ownsong.Title = title;
ownsong.Content = content;
context.OwnSongs.InsertOnSubmit(ownsong);
context.SubmitChanges();
}
}
public void UpdateOwnSong(int id, String title, string content)
{
using (AppDatabase context = new AppDatabase(AppDatabase.DBConnectionString))
{
IQueryable <vsb_ownsong> entityQuery = from own in context.OwnSongs where own.ID == id select own;
vsb_ownsong entityToUpdate = entityQuery.FirstOrDefault();
entityToUpdate.Title = title;
entityToUpdate.Content = content;
context.SubmitChanges();
}
}
public IList <vsb_ownsong> GetOwnSongs()
{
IList <vsb_ownsong> list = null;
using (AppDatabase context = new AppDatabase(AppDatabase.DBConnectionString))
{
IQueryable <vsb_ownsong> query = from plist in context.OwnSongs select plist;
list = query.ToList();
}
return(list);
}
public IList <vsb_favourite> GetFavourites()
{
IList <vsb_favourite> list = null;
using (AppDatabase context = new AppDatabase(AppDatabase.DBConnectionString))
{
IQueryable <vsb_favourite> query = from plist in context.Favourites select plist;
list = query.ToList();
}
return(list);
}
public void ViewSong(String id)
{
using (AppDatabase context = new AppDatabase(AppDatabase.DBConnectionString))
{
IQueryable <vsb_songbook> entityQuery = from slist in context.SongBooks
where slist.ID.Equals(id) select slist;
vsb_songbook songview = entityQuery.FirstOrDefault();
context.SongBooks.DeleteOnSubmit(songview);
context.SubmitChanges();
}
}
public void FavoriteThis(String title, String content)
{
using (AppDatabase context = new AppDatabase(AppDatabase.DBConnectionString))
{
vsb_favourite favour = new vsb_favourite();
favour.Title = title;
favour.Content = content;
context.Favourites.InsertOnSubmit(favour);
context.SubmitChanges();
}
}
public IList <vsb_songbook> GetSingleOne(String sarch)
{
IList <vsb_songbook> list = null;
using (AppDatabase context = new AppDatabase(AppDatabase.DBConnectionString))
{
IQueryable <vsb_songbook> query = from plist in context.SongBooks
where plist.Content.Equals(sarch) select plist;
list = query.ToList();
}
return(list);
}
public void AddSongBook(String title, String content, String icon)
{
using (AppDatabase context = new AppDatabase(AppDatabase.DBConnectionString))
{
vsb_songbook newsong = new vsb_songbook();
newsong.Icon = icon;
newsong.Title = title;
newsong.Content = content;
context.SongBooks.InsertOnSubmit(newsong);
context.SubmitChanges();
}
}
public void UpdateUserToLower()
{
using (AppDatabase context = new AppDatabase(AppDatabase.DBConnectionString))
{
IQueryable <vsb_ownsong> entityQuery = from own in context.OwnSongs select own;
IList <vsb_ownsong> entityToUpdate = entityQuery.ToList();
foreach (vsb_ownsong song in entityToUpdate)
{
song.Title = song.Title.ToLower();
}
context.SubmitChanges();
}
}
/// <summary>
/// Constructor for the Application object.
/// </summary>
public App()
{
// Global handler for uncaught exceptions.
UnhandledException += Application_UnhandledException;
// Standard XAML initialization
InitializeComponent();
// Phone-specific initialization
InitializePhoneApplication();
// Language display initialization
InitializeLanguage();
// Show graphics profiling information while debugging.
if (Debugger.IsAttached)
{
// Display the current frame rate counters.
Application.Current.Host.Settings.EnableFrameRateCounter = true;
// Show the areas of the app that are being redrawn in each frame.
//Application.Current.Host.Settings.EnableRedrawRegions = true;
// Enable non-production analysis visualization mode,
// which shows areas of a page that are handed off GPU with a colored overlay.
//Application.Current.Host.Settings.EnableCacheVisualization = true;
// Prevent the screen from turning off while under the debugger by disabling
// the application's idle detection.
// Caution:- Use this under debug mode only. Application that disables user idle detection will continue to run
// and consume battery power when the user is not using the phone.
PhoneApplicationService.Current.UserIdleDetectionMode = IdleDetectionMode.Disabled;
}
// Specify the local database connection string.
//string DBConnectionString = "Data Source=isostore:/vSongBook.sdf";
using (AppDatabase context = new AppDatabase(AppDatabase.DBConnectionString))
{
if (!context.DatabaseExists())
{
context.CreateDatabase();
}
}
virtualSongbook = new AppDataView(AppDatabase.DBConnectionString);
}
private void btnUpdate_Click(object sender, EventArgs e)
{
appDB = new AppDatabase();
string editbook = appDB.editBook(lstBookids.Text, txtBookTitle.Text, txtBookCode.Text, txtNotes.Text);
if (editbook == "success")
{
loadFeedback(txtBookTitle.Text + " has been updated successfully!", true, true);
loadBooks();
}
else
{
loadFeedback("Unable to edit a book: " + editbook, false);
}
loadBooks();
}
private void btnSaveNew_Click(object sender, EventArgs e)
{
appDB = new AppDatabase();
string newbook = appDB.addNewBook(txtBookTitle.Text, txtBookCode.Text, txtNotes.Text);
if (newbook == "success")
{
loadFeedback("A new book " + txtBookTitle.Text + "has been added successfully!", true, true);
loadBooks();
clearFields();
}
else
{
loadFeedback("Unable to add a book: " + newbook, false);
}
}
private void btnSaveOnly_Click(object sender, EventArgs e)
{
appDB = new AppDatabase();
string editsong = appDB.EditSong(int.Parse(lstSongids.Text), lstBookcodes.Text, txtNumber.Text,
txtSongTitle.Text, txtSongContent.Text, txtSongKey.Text, "", "");
if (editsong == "success")
{
appDB.SongsUpdate(lstBookcodes.Text, lstSongResults.Items.Count);
LoadFeedback(txtSongTitle.Text + " has been updated successfully!", true, true);
}
else
{
LoadFeedback("Unable to edit the song: " + editsong, false);
}
}
public void seachForSongs(string searchstr, bool criteria)
{
if (txtSearch.Text != langs.As_Lang(vsb_lang, "search_for_songs"))
{
try
{
lstSongResults.Items.Clear();
lstSongids.Items.Clear();
string searchcriteria = "";
if (!criteria)
{
searchcriteria = " AND book='" + lstBookcodes.Text + "'";
}
if (searchstr.Length < 5)
{
sqlQuery = "SELECT songid, number, title FROM songs " +
"WHERE number=" + int.Parse(searchstr) + searchcriteria + ";";
}
else
{
sqlQuery = "SELECT songid, number, title FROM songs " +
"WHERE title LIKE '%" + searchstr + "%' " + searchcriteria +
"OR content LIKE '%" + searchstr + searchcriteria + "%';";
}
appDB = new AppDatabase();
dRowCol = appDB.GetList(sqlQuery);
foreach (DataRow row in dRowCol)
{
lstSongResults.Items.Add(row["number"] + "# " + row["title"]);
lstSongids.Items.Add(row["songid"]);
lstSongResults.SelectedIndex = 0;
lstSongids.SelectedIndex = 0;
}
string[] valuestrings = { lstSongResults.Items.Count.ToString(), searchstr };
grpSongResults.Text = langs.As_Lang_Arr(langs.As_Lang(vsb_lang, "songs_found_with"), valuestrings);
lstSongResults.SelectedIndex = 0;
}
catch (Exception ex)
{
loadFeedback(langs.As_Lang(vsb_lang, "error_song_searching") + ex.Message, false);
}
}
}
private void btnSaveClose_Click(object sender, EventArgs e)
{
appDB = new AppDatabase();
string newsong = appDB.QuickUpdate("books", "songs", "34", "code", "bebmb");
//string newsong = appDB.NewSong(lstBookcodes.Text, txtNumber.Text, txtSongTitle.Text, txtSongContent.Text, txtSongKey.Text, "", "");
if (newsong == "success")
{
LoadFeedback(txtSongTitle.Text + " has been added successfully!", true, true);
loadBooks();
txtNumber.box.Clear();
txtSongKey.box.Clear();
txtSongTitle.box.Clear();
txtSongContent.Clear();
Close();
}
else
{
LoadFeedback("Unable to add a song: " + newsong, false);
}
}
public void loadSingleSong(int songid)
{
try
{
txtSongContent.Clear();
sqlQuery = "SELECT * FROM songs WHERE songid=" + songid + ";";
appDB = new AppDatabase();
reader = appDB.GetSingle(sqlQuery);
while (reader.Read())
{
txtNumber.Text = reader["number"].ToString();
txtSongKey.Text = reader["key"].ToString();
txtSongTitle.Text = vsbf.songRender(reader["title"].ToString());
txtSongContent.Text = vsbf.songRender(reader["content"].ToString());
}
appDB.SQLClose();
}
catch (Exception ex)
{
LoadFeedback("Oops! Sorry song viewing failed: " + ex.Message, false, true);
}
}
private void btnSaveAdd_Click(object sender, EventArgs e)
{
appDB = new AppDatabase();
int selectedbook = lstBookcodes.SelectedIndex;
string newsong = appDB.NewSong(lstBookcodes.Text, txtNumber.Text, txtSongTitle.Text, txtSongContent.Text,
txtSongKey.Text, "", "");
if (newsong == "success")
{
LoadFeedback(txtSongTitle.Text + " has been added successfully!", true, true);
loadBooks();
cmbBooks.SelectedIndex = selectedbook;
txtNumber.box.Clear();
txtSongKey.box.Clear();
txtSongTitle.box.Clear();
txtSongContent.Clear();
}
else
{
LoadFeedback("Unable to add a song: " + newsong, false);
}
}
public void loadBookDetails(string bookid)
{
try
{
txtNotes.Clear();
sqlQuery = "SELECT * FROM books WHERE bookid=" + bookid + ";";
appDB = new AppDatabase();
reader = appDB.getSingle(sqlQuery);
while (reader.Read())
{
txtBookTitle.Text = reader["title"].ToString();
txtBookCode.Text = reader["code"].ToString();
txtNotes.Text = reader["notes"].ToString();
lblInfo.Text = reader["songs"].ToString() + " songs";
}
appDB.sqlClose();
}
catch (Exception ex)
{
loadFeedback("Oops! Sorry book viewing failed: " + ex.Message, false, true);
}
}
public void loadSingleSong(int songid)
{
try
{
txtSongContent.Clear();
sqlQuery = "SELECT *, books.title AS songbook FROM songs LEFT JOIN books ON songs.book = books.code WHERE songid=" + songid + ";";
appDB = new AppDatabase();
reader = appDB.getSingle(sqlQuery);
while (reader.Read())
{
txtSongTitle.Text = reader["number"].ToString() + "# " + vsbf.textRender(reader["title"].ToString()) +
" | " + reader["songbook"].ToString();
txtSongContent.Text = vsbf.songRender(reader["content"].ToString());
txtSongDetails.Text = "Key: " + reader["key"].ToString() + " © " + reader["author"].ToString() + "\r\n" +
reader["notes"].ToString() + "\r\nLast Updated: " + reader["updated"].ToString();
}
appDB.sqlClose();
}
catch (Exception ex)
{
loadFeedback(langs.As_Lang(vsb_lang, "error_songs_viewing") + " " + ex.Message, false, true);
}
}
public void loadBooks()
{
try
{
cmbBooks.Items.Clear();
lstBookcodes.Items.Clear();
sqlQuery = "SELECT * FROM books WHERE state=1;";
appDB = new AppDatabase();
dRowCol = appDB.getList(sqlQuery);
foreach (DataRow row in dRowCol)
{
cmbBooks.Items.Add(row["title"] + " (" + row["songs"] + ")");
lstBookcodes.Items.Add(row["code"]);
cmbBooks.SelectedIndex = 0;
lstBookcodes.SelectedIndex = 0;
}
}
catch (Exception ex)
{
loadFeedback(langs.As_Lang(vsb_lang, "error_books_listing") + " " + ex.Message, false);
}
}
public void loadBooks()
{
try
{
cmbBooks.Items.Clear();
lstBookcodes.Items.Clear();
sqlQuery = "SELECT * FROM books WHERE state=1;";
appDB = new AppDatabase();
dRowCol = appDB.GetList(sqlQuery);
foreach (DataRow row in dRowCol)
{
cmbBooks.Items.Add(row["title"] + " (" + row["songs"] + ")");
lstBookcodes.Items.Add(row["code"]);
cmbBooks.SelectedIndex = 0;
lstBookcodes.SelectedIndex = 0;
}
}
catch (Exception ex)
{
LoadFeedback("Oops! Sorry books listing failed: " + ex.Message, false, true);
}
}
public void loadBooks()
{
try
{
lstBooks.Items.Clear();
lstBookids.Items.Clear();
sqlQuery = "SELECT * FROM books;";
appDB = new AppDatabase();
dRowCol = appDB.getList(sqlQuery);
foreach (DataRow row in dRowCol)
{
lstBooks.Items.Add(row["title"] + " (" + row["songs"] + ")");
lstBookids.Items.Add(row["bookid"]);
lstBooks.SelectedIndex = 0;
lstBookids.SelectedIndex = 0;
}
grpBookResults.Text = lstBooks.Items.Count + " books exist currently";
}
catch (Exception ex)
{
loadFeedback("Oops! Sorry, books listing failed: " + ex.Message, false);
}
}
public AppDataView(string DBConnectionString)
{
vSongBookDb = new AppDatabase(AppDatabase.DBConnectionString);
}
public void loadSingleSong(int songid)
{
try
{
string temptext = "", tempverse = "", tempcount = "";
cur_stz = 0;
sqlQuery = "SELECT *, books.title AS songbook FROM songs LEFT JOIN books ON songs.book = books.code WHERE songid=" + songid + ";";
appDB = new AppDatabase();
reader = appDB.getSingle(sqlQuery);
while (reader.Read())
{
lblSongno.Text = "#" + reader["number"].ToString();
lblSongTitle.Text = reader["number"].ToString() + "# " + vsbf.textRender(reader["title"].ToString()) + " " + reader["key"].ToString().Replace("-", "");
lblDetails.Text = reader["songbook"].ToString();
songtext = reader["content"].ToString();
}
appDB.sqlClose();
if (Regex.Matches(songtext, "CHORUS").Count == 1)
{
songtemps = songtext.Split('|');
int tempscount = songtemps.Length - 1;
string chorustr = songtemps[1].Replace("CHORUS$", "");
temptext = songtemps[0] + "#" + chorustr;
tempverse = "VERSE#CHORUS";
tempcount = "1 of " + tempscount + "# ";
for (int i = 2; i < tempscount + 1; i++)
{
temptext = temptext + "#" + songtemps[i] + "#" + chorustr;
tempverse = tempverse + "#VERSE#CHORUS";
tempcount = tempcount + "#" + i + " of " + tempscount + "# ";
}
songstanzas = temptext.Split('#');
hasChorus = true;
}
else
{
songstanzas = songtext.Split('|');
tempverse = "VERSE";
tempcount = "1 of " + songstanzas.Length;
for (int i = 2; i < songstanzas.Length; i++)
{
tempverse = tempverse + "#VERSE";
tempcount = tempcount + "#" + (i - 1) + " of " + songstanzas.Length;
}
hasChorus = false;
}
versechorus = tempverse.Split('#');
versecounts = tempcount.Split('#');
stanzas = songstanzas.Length;
pbxDown.Visible = true;
lblSongText.Text = vsbf.songRender(songstanzas[cur_stz]);
lblItem.Text = versechorus[cur_stz];
lblVerses.Text = versecounts[cur_stz];
//lblDetails.Text = stanzas.ToString();
}
catch (Exception ex)
{
lblSongTitle.Text = "Song projection failed";
lblSongText.Text = "Oops! Song projection failed due to: " + ex.Message;
}
}
