public IEnumerable <Song> getSongs() { IEnumerable <Song> songs = new List <Song>(); if (songsConnection.State == System.Data.ConnectionState.Open && songWordsConnection.State == System.Data.ConnectionState.Open) { var fetchSongsCommand = new SQLiteCommand("select * from song", songsConnection); var fetchSongWordsCommand = new SQLiteCommand("select * from word", songWordsConnection); Dictionary <int, Song> dSongs = new Dictionary <int, Song>(); SQLiteDataReader songsReader = fetchSongsCommand.ExecuteReader(); SQLiteDataReader songWordsReader = fetchSongWordsCommand.ExecuteReader(); while (songsReader.Read()) { var song = new Song() { id = int.Parse(songsReader["rowid"].ToString()), title = songsReader["title"] != System.DBNull.Value ? (string)songsReader["title"] : "", author = songsReader["author"] != System.DBNull.Value ? (string)songsReader["author"] : "", copyright = songsReader["copyright"] != System.DBNull.Value ? (string)songsReader["copyright"] : "", administrator = songsReader["administrator"] != System.DBNull.Value ? (string)songsReader["administrator"] : "", description = songsReader["description"] != System.DBNull.Value ? (string)songsReader["description"] : "", tags = songsReader["tags"] != System.DBNull.Value ? (string)songsReader["tags"] : "", text = "" }; dSongs[(int)song.id] = song; } while (songWordsReader.Read()) { Song song; var id = int.Parse(songWordsReader["song_id"].ToString()); if (dSongs.ContainsKey(id)) { song = dSongs[id]; } else { song = new Song() { id = id }; dSongs[id] = song; } song.text = songWordsReader["words"] != System.DBNull.Value ? RichTextStripper.StripRichTextFormat((string)songWordsReader["words"]) : ""; } songs = dSongs.Select(p => p.Value); } return(songs); }
public Dictionary <int, int> fixDatabase() { Console.WriteLine("fixing database"); if (songsConnection.State == System.Data.ConnectionState.Open && songWordsConnection.State == System.Data.ConnectionState.Open) { var fetchSongsCommand = new SQLiteCommand("select * from song", songsConnection); var fetchSongWordsCommand = new SQLiteCommand("select * from word", songWordsConnection); SQLiteDataReader songsReader = fetchSongsCommand.ExecuteReader(); Dictionary <int, Song> dbSongs = new Dictionary <int, Song>(); while (songsReader.Read()) { var song = new Song() { id = int.Parse(songsReader["rowid"].ToString()), title = songsReader["title"] != System.DBNull.Value ? (string)songsReader["title"] : "", author = songsReader["author"] != System.DBNull.Value ? (string)songsReader["author"] : "", copyright = songsReader["copyright"] != System.DBNull.Value ? (string)songsReader["copyright"] : "", administrator = songsReader["administrator"] != System.DBNull.Value ? (string)songsReader["administrator"] : "", description = songsReader["description"] != System.DBNull.Value ? (string)songsReader["description"] : "", tags = songsReader["tags"] != System.DBNull.Value ? (string)songsReader["tags"] : "" }; dbSongs[(int)song.id] = song; } SQLiteDataReader songWordsReader = fetchSongWordsCommand.ExecuteReader(); while (songWordsReader.Read()) { Song song; var id = int.Parse(songWordsReader["song_id"].ToString()); if (dbSongs.ContainsKey(id)) { song = dbSongs[id]; } else { song = new Song() { id = id }; dbSongs[id] = song; } song.text = songWordsReader["words"] != System.DBNull.Value ? RichTextStripper.StripRichTextFormat((string)songWordsReader["words"]) : ""; } var removeSongWords = new SQLiteCommand("delete from word", songWordsConnection); removeSongWords.ExecuteNonQuery(); var removeSongTableCommand = new SQLiteCommand("DROP TABLE song", songsConnection); removeSongTableCommand.ExecuteNonQuery(); var createSongTableCommand = new SQLiteCommand("CREATE TABLE song (rowid integer PRIMARY KEY AUTOINCREMENT NOT NULL UNIQUE, song_item_uid text UNIQUE, song_rev_uid text, song_uid text, title text NOT NULL, author text, copyright text, administrator text, description text, tags text, reference_number text, vendor_id integer, presentation_id integer, layout_revision integer DEFAULT 1, revision integer DEFAULT 1 )", songsConnection); createSongTableCommand.ExecuteNonQuery(); Dictionary <int, int> newidToOldId = new Dictionary <int, int>(); Dictionary <int, int> oldidToNewId = new Dictionary <int, int>(); foreach (var item in dbSongs) { var songUid = Guid.NewGuid(); var insertSongCommand = new SQLiteCommand("insert into song (song_item_uid, song_uid, title, author, copyright, administrator, description, tags) values(@song_item_uid, @song_uid, @title, @author, @copyright, @administrator, @description, @tags)", songsConnection); insertSongCommand.Parameters.Add(new SQLiteParameter("@song_item_uid", songUid)); insertSongCommand.Parameters.Add(new SQLiteParameter("@song_uid", songUid)); insertSongCommand.Parameters.Add(new SQLiteParameter("@title", item.Value.title)); insertSongCommand.Parameters.Add(new SQLiteParameter("@author", item.Value.author)); insertSongCommand.Parameters.Add(new SQLiteParameter("@copyright", item.Value.copyright)); insertSongCommand.Parameters.Add(new SQLiteParameter("@administrator", item.Value.administrator)); insertSongCommand.Parameters.Add(new SQLiteParameter("@description", item.Value.description)); insertSongCommand.Parameters.Add(new SQLiteParameter("@tags", item.Value.tags)); insertSongCommand.ExecuteNonQuery(System.Data.CommandBehavior.SingleRow); var songId = songsConnection.LastInsertRowId; oldidToNewId[item.Key] = (int)songId; string words = @"{\rtf1{\pard " + item.Value.text.Replace("\n", @"\par ") + "}}"; var insertSongWordsCommand = new SQLiteCommand("insert into word (song_id, words) values (@song_id, @words)", songWordsConnection); insertSongWordsCommand.Parameters.Add(new SQLiteParameter("@song_id", songId.ToString())); insertSongWordsCommand.Parameters.Add(new SQLiteParameter("@words", words)); insertSongWordsCommand.ExecuteNonQuery(); } return(oldidToNewId); } return(null); }
public Song getSong(uint id) { Song song = null; if (songsConnection.State == System.Data.ConnectionState.Open && songWordsConnection.State == System.Data.ConnectionState.Open) { song = new Song(); var fetchSongCommand = new SQLiteCommand("select * from song", songsConnection); var fetchSongWordsCommand = new SQLiteCommand("select * from word", songWordsConnection); SQLiteDataReader songReader = fetchSongCommand.ExecuteReader(); SQLiteDataReader songWordsReader = fetchSongWordsCommand.ExecuteReader(); while (songReader.Read()) { song = new Song() { id = (int)songReader["rowid"], title = songReader["title"] != System.DBNull.Value ? (string)songReader["title"] : "", author = songReader["author"] != System.DBNull.Value ? (string)songReader["author"] : "", copyright = songReader["copyright"] != System.DBNull.Value ? (string)songReader["copyright"] : "", administrator = songReader["administrator"] != System.DBNull.Value ? (string)songReader["administrator"] : "", description = songReader["description"] != System.DBNull.Value ? (string)songReader["description"] : "", tags = songReader["tags"] != System.DBNull.Value ? (string)songReader["tags"] : "" }; } if (song != null) { while (songWordsReader.Read()) { song.text = songWordsReader["words"] != System.DBNull.Value ? RichTextStripper.StripRichTextFormat((string)songWordsReader["words"]) : ""; } } } return(song); }