private void AddInsertSongVerses(Dictionary <string, MigrationSongVerse> mVerses) { using (JetTask t = new JetTask()) { t.Parameters.Add("@AutoNumber", OleDbType.Integer); t.Parameters.Add("@IsChorus", OleDbType.SmallInt); t.Parameters.Add("@Verse", OleDbType.VarWChar, 0); t.Parameters.Add("@OrderNum", OleDbType.Integer); foreach (MigrationSongVerse msv in mVerses.Values) { t.Parameters["@AutoNumber"].Value = msv.autoNumber; t.Parameters["@IsChorus"].Value = msv.isChorus ? 1 : 0; t.Parameters["@Verse"].Value = msv.verse; t.Parameters["@OrderNum"].Value = msv.orderNumber; if (!msv.foundInNew) { t.CommandText = "INSERT INTO [SongVerses] ([AutoNumber], [IsChorus], [Verse], [OrderNum]) " + "VALUES (@AutoNumber, @IsChorus, @Verse, @OrderNum)"; t.ExecuteNonQuery(); } else { t.CommandText = "UPDATE [SongVerses] " + "SET [AutoNumber] = @AutoNumber, " + "[IsChorus] = @IsChorus, " + "[Verse] = @Verse, " + "[OrderNum] = @OrderNum " + "WHERE ([AutoNumber] = @AutoNumber AND [OrderNum] = @OrderNum)"; t.ExecuteNonQuery(); } } } }
private void AddInsertSongs(Dictionary <int, MigrationSong> mSongs) { using (JetTask t = new JetTask()) { t.Parameters.Add("@AutoNumber", OleDbType.Integer); t.Parameters.Add("@Number", OleDbType.Integer); t.Parameters.Add("@Title", OleDbType.VarWChar, 0); t.Parameters.Add("@Chorus", OleDbType.VarWChar, 0); t.Parameters.Add("@Location", OleDbType.VarWChar, 0); t.Parameters.Add("@DisplayDefault", OleDbType.SmallInt); t.Parameters.Add("@ImageId", OleDbType.Integer); t.Parameters.Add("@Overlay", OleDbType.Integer); t.Parameters.Add("@FontId", OleDbType.Integer); t.Parameters.Add("@Settings", OleDbType.VarWChar, 0); foreach (MigrationSong ms in mSongs.Values) { t.Parameters["@AutoNumber"].Value = ms.autoNumber; t.Parameters["@Number"].Value = ms.number; t.Parameters["@Title"].Value = ms.title; t.Parameters["@Chorus"].Value = ms.chorus; t.Parameters["@Location"].Value = ms.location; t.Parameters["@DisplayDefault"].Value = ms.displayDefault ? 1 : 0; t.Parameters["@ImageId"].Value = ms.imageId; t.Parameters["@Overlay"].Value = ms.overlay; t.Parameters["@FontId"].Value = ms.fontId; t.Parameters["@Settings"].Value = ms.settings; if (!ms.foundInNew) { t.CommandText = "INSERT INTO [Songs] " + "([AutoNumber], [Number], [Title], [Chorus], [Location], [DisplayDefault], [ImageId], [Overlay], [FontId], [Settings]) VALUES " + "(@AutoNumber, @Number, @Title, @Chorus, @Location, @DisplayDefault, @ImageId, @Overlay, @FontId, @Settings)"; t.ExecuteNonQuery(); } else { t.CommandText = "UPDATE [Songs] SET " + "[AutoNumber] = @AutoNumber, " + "[Number] = @Number, " + "[Title] = @Title, " + "[Chorus] = @Chorus, " + "[Location] = @Location, " + "[DisplayDefault] = @DisplayDefault, " + "[ImageId] = @ImageId, " + "[Overlay] = @Overlay, " + "[FontId] = @FontId, " + "[Settings] = @Settings WHERE [AutoNumber] = @AutoNumber"; t.ExecuteNonQuery(); } } } }
private bool GetDifSongs(string oldRoot, Dictionary <int, MigrationSong> mSongs) { using (FBirdTask told = new FBirdTask()) using (JetTask tnew = new JetTask()) { // Set old connection string string path2olddb = Path.Combine(oldRoot, "bibdata.fdb"); string oldConstring = "ServerType=1;Database=" + path2olddb + ";User=SYSDBA;Password=masterkey;Charset=WIN1251"; told.Connection = new FbConnection(oldConstring); string fbQuery = "SELECT " + "\"Songs\".\"AutoNumber\", " + "\"Songs\".\"Number\", " + "\"Songs\".\"Title\", " + "\"Songs\".\"Chorus\", " + "\"Songs\".\"Location\", " + "\"Songs\".\"DisplayDefault\", " + "\"Songs\".\"ImageId\", " + "\"Songs\".\"Overlay\", " + "\"Songs\".\"Copyright\" " + "FROM \"Songs\" " + "ORDER BY \"AutoNumber\""; string jtQuery = "SELECT " + "[Songs].[AutoNumber], " + "[Songs].[Number], " + "[Songs].[Title], " + "[Songs].[Chorus], " + "[Songs].[Location], " + "[Songs].[DisplayDefault], " + "[Songs].[ImageId], " + "[Songs].[Overlay], " + "[Songs].[Copyright] " + "FROM [Songs] " + "ORDER BY [AutoNumber]"; told.CommandText = fbQuery; tnew.CommandText = jtQuery; told.ExecuteReader(); tnew.ExecuteReader(); if (told.DR == null || tnew.DR == null) { this.TopMost = false; MessageBox.Show(this, "Migration failed to migrate song data", "Error"); return(false); } bool oldAvailable = true; bool newAvailable = true; while (oldAvailable || newAvailable) { oldAvailable = told.DR.Read(); newAvailable = tnew.DR.Read(); // Old data if (oldAvailable) { MigrationSong mgo = new MigrationSong(); DrFillSongObj(told.DR, mgo); mgo.foundInOld = true; // Check if image got changed if (mgo.imageId == 4) { mgo.imageId = -2; mgo.overlay = 777; } if (mSongs.ContainsKey(mgo.autoNumber)) { MigrationSong v2 = mSongs[mgo.autoNumber]; v2.foundInOld = true; // Drop if the two are the same if (v2.Compare(mgo, v2)) { mSongs.Remove(mgo.autoNumber); } } else { mSongs[mgo.autoNumber] = mgo; } } // New data if (newAvailable) { MigrationSong mgn = new MigrationSong(); DrFillSongObj(tnew.DR, mgn); mgn.foundInNew = true; if (mSongs.ContainsKey(mgn.autoNumber)) { MigrationSong v2 = mSongs[mgn.autoNumber]; v2.foundInNew = true; // Drop if the two are the same if (v2.Compare(mgn, v2)) { mSongs.Remove(mgn.autoNumber); } } else { mSongs[mgn.autoNumber] = mgn; } } } } return(true); }