Пример #1
0
        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);
        }
Пример #2
0
        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);
        }
Пример #3
0
        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);
        }