コード例 #1
0
 internal static void SaveNoteSkinless(PNote note)
 {
     try
     {
         var cr = new ColorConverter();
         var lfc = new WPFFontConverter();
         var sb = new StringBuilder();
         using (var oData = new SQLiteDataObject(PNData.ConnectionString))
         {
             if (isNotePresentedInCustomSettings(note))
             {
                 sb.Append("UPDATE CUSTOM_NOTES_SETTINGS SET BACK_COLOR = ");
                 if (note.Skinless != null)
                 {
                     sb.Append("'");
                     sb.Append(cr.ConvertToString(null, PNStatic.CultureInvariant, note.Skinless.BackColor));
                     sb.Append("'");
                 }
                 else
                 {
                     sb.Append("NULL");
                 }
                 sb.Append(", CAPTION_FONT_COLOR = ");
                 if (note.Skinless != null)
                 {
                     sb.Append("'");
                     sb.Append(cr.ConvertToString(null, PNStatic.CultureInvariant, note.Skinless.CaptionColor));
                     sb.Append("'");
                 }
                 else
                 {
                     sb.Append("NULL");
                 }
                 sb.Append(", CAPTION_FONT = ");
                 if (note.Skinless != null)
                 {
                     sb.Append("'");
                     sb.Append(lfc.ConvertToString(note.Skinless.CaptionFont));
                     sb.Append("'");
                 }
                 else
                 {
                     sb.Append("NULL");
                 }
                 sb.Append(" WHERE NOTE_ID = '");
                 sb.Append(note.ID);
                 sb.Append("'");
             }
             else
             {
                 sb.Append("INSERT INTO CUSTOM_NOTES_SETTINGS (NOTE_ID, BACK_COLOR, CAPTION_FONT_COLOR, CAPTION_FONT) VALUES(");
                 if (note.Skinless != null)
                 {
                     sb.Append("'");
                     sb.Append(note.ID);
                     sb.Append("', '");
                     sb.Append(cr.ConvertToString(null, PNStatic.CultureInvariant, note.Skinless.BackColor));
                     sb.Append("', '");
                     sb.Append(cr.ConvertToString(null, PNStatic.CultureInvariant, note.Skinless.CaptionColor));
                     sb.Append("', '");
                     sb.Append(lfc.ConvertToString(note.Skinless.CaptionFont));
                     sb.Append("')");
                 }
                 else
                 {
                     sb.Append("'");
                     sb.Append(note.ID);
                     sb.Append("', NULL, NULL, NULL)");
                 }
             }
             oData.Execute(sb.ToString());
         }
     }
     catch (Exception ex)
     {
         PNStatic.LogException(ex);
     }
 }
コード例 #2
0
ファイル: PNData.cs プロジェクト: hyrmedia/PNotes.NET
 internal static void SaveFontUi()
 {
     try
     {
         var fc = new WPFFontConverter();
         var fontString = fc.ConvertToString(PNSingleton.Instance.FontUser);
         var sb = new StringBuilder("UPDATE CONFIG SET UI_FONT = '");
         sb.Append(fontString);
         sb.Append("'");
         ExecuteTransactionForStringBuilder(sb, SettingsConnectionString);
     }
     catch (Exception ex)
     {
         PNStatic.LogException(ex);
     }
 }
コード例 #3
0
        internal static void LoadNoteCustomProperties(PNote note, DataRow r)
        {
            try
            {
                if (!PNData.IsDBNull(r["BACK_COLOR"]) || !PNData.IsDBNull(r["CAPTION_FONT_COLOR"]) || !PNData.IsDBNull(r["CAPTION_FONT"]))
                {
                    var drawingColorConverter = new System.Drawing.ColorConverter();
                    var wfc = new WPFFontConverter();
                    var mediaColorConverter = new ColorConverter();

                    note.Skinless = new PNSkinlessDetails();
                    if (!PNData.IsDBNull(r["BACK_COLOR"]))
                    {
                        try
                        {
                            var clr = mediaColorConverter.ConvertFromString(null, PNStatic.CultureInvariant,
                                                    (string)r["BACK_COLOR"]);
                            if (clr != null)
                            {
                                note.Skinless.BackColor = (Color)clr;
                            }
                        }
                        catch (FormatException)
                        {
                            //possible FormatException after synchronization with old database
                            var clr = drawingColorConverter.ConvertFromString(null, PNStatic.CultureInvariant,
                                                    (string)r["BACK_COLOR"]);
                            if (clr != null)
                            {
                                var drawingColor = (System.Drawing.Color)clr;
                                note.Skinless.BackColor = Color.FromArgb(drawingColor.A, drawingColor.R, drawingColor.G,
                                    drawingColor.B);
                                var sb = new StringBuilder("UPDATE CUSTOM_NOTES_SETTINGS SET BACK_COLOR = '");
                                sb.Append(mediaColorConverter.ConvertToString(null, PNStatic.CultureInvariant, note.Skinless.BackColor));
                                sb.Append("' WHERE NOTE_ID = '");
                                sb.Append(r["NOTE_ID"]);
                                sb.Append("'");
                                PNData.ExecuteTransactionForStringBuilder(sb, PNData.ConnectionString);
                            }
                        }
                    }
                    if (!PNData.IsDBNull(r["CAPTION_FONT_COLOR"]))
                    {
                        try
                        {
                            var clr = mediaColorConverter.ConvertFromString(null, PNStatic.CultureInvariant,
                                                    (string)r["CAPTION_FONT_COLOR"]);
                            if (clr != null)
                            {
                                note.Skinless.CaptionColor = (Color)clr;
                            }
                        }
                        catch (FormatException)
                        {
                            //possible FormatException after synchronization with old database
                            var clr = drawingColorConverter.ConvertFromString(null, PNStatic.CultureInvariant,
                                                    (string)r["CAPTION_FONT_COLOR"]);
                            if (clr != null)
                            {
                                var drawingColor = (System.Drawing.Color)clr;
                                note.Skinless.CaptionColor = Color.FromArgb(drawingColor.A, drawingColor.R, drawingColor.G,
                                    drawingColor.B);
                                var sb = new StringBuilder("UPDATE CUSTOM_NOTES_SETTINGS SET CAPTION_FONT_COLOR = '");
                                sb.Append(mediaColorConverter.ConvertToString(null, PNStatic.CultureInvariant, note.Skinless.CaptionColor));
                                sb.Append("' WHERE NOTE_ID = '");
                                sb.Append(r["NOTE_ID"]);
                                sb.Append("'");
                                PNData.ExecuteTransactionForStringBuilder(sb, PNData.ConnectionString);
                            }
                        }
                    }
                    if (!PNData.IsDBNull(r["CAPTION_FONT"]))
                    {
                        var fontString = (string)r["CAPTION_FONT"];
                        //try
                        //{
                        var fonts = new InstalledFontCollection();
                        var arr = fontString.Split(',');
                        if (fontString.Any(ch => ch == '^'))
                        {
                            //old format font string
                            var lfc = new PNStaticFonts.LogFontConverter();
                            var lf = lfc.ConvertFromString((string)r["CAPTION_FONT"]);
                            note.Skinless.CaptionFont = PNStatic.FromLogFont(lf);
                            var sb = new StringBuilder("UPDATE CUSTOM_NOTES_SETTINGS SET CAPTION_FONT = '");
                            sb.Append(wfc.ConvertToString(null, PNStatic.CultureInvariant, note.Skinless.CaptionFont));
                            sb.Append("' WHERE NOTE_ID = '");
                            sb.Append(r["NOTE_ID"]);
                            sb.Append("'");
                            PNData.ExecuteTransactionForStringBuilder(sb, PNData.ConnectionString);
                        }
                        else if (fonts.Families.Any(ff => ff.Name == arr[0]))
                        {
                            note.Skinless.CaptionFont = (PNFont)wfc.ConvertFromString((string)r["CAPTION_FONT"]);
                        }
                        else
                        {
                            //possible note existing font name
                            arr[0] = PNStrings.DEF_CAPTION_FONT;
                            fontString = string.Join(",", arr);
                            note.Skinless.CaptionFont = (PNFont)wfc.ConvertFromString(fontString);
                            var sb = new StringBuilder("UPDATE CUSTOM_NOTES_SETTINGS SET CAPTION_FONT = '");
                            sb.Append(fontString);
                            sb.Append("' WHERE NOTE_ID = '");
                            sb.Append(r["NOTE_ID"]);
                            sb.Append("'");
                            PNData.ExecuteTransactionForStringBuilder(sb, PNData.ConnectionString);
                        }
                        //}
                        //catch (IndexOutOfRangeException)
                        //{
                        //    //possible IndexOutOfRangeException after synchronization with old database
                        //    var lfc = new PNStaticFonts.LogFontConverter();
                        //    var lf = lfc.ConvertFromString((string)r["CAPTION_FONT"]);
                        //    note.Skinless.CaptionFont = PNStatic.FromLogFont(lf);
                        //    var sb = new StringBuilder("UPDATE CUSTOM_NOTES_SETTINGS SET CAPTION_FONT = '");
                        //    sb.Append(wfc.ConvertToString(null, PNStatic.CultureInvariant, note.Skinless.CaptionFont));
                        //    sb.Append("' WHERE NOTE_ID = '");
                        //    sb.Append(r["NOTE_ID"]);
                        //    sb.Append("'");
                        //    PNData.ExecuteTransactionForStringBuilder(sb, PNData.ConnectionString);
                        //}
                    }
                }
                if (!PNData.IsDBNull(r["SKIN_NAME"]))
                {
                    note.Skin = new PNSkinDetails { SkinName = (string)r["SKIN_NAME"] };
                    var path = Path.Combine(PNPaths.Instance.SkinsDir, note.Skin.SkinName);
                    path += ".pnskn";
                    if (File.Exists(path))
                    {
                        PNSkinsOperations.LoadSkin(path, note.Skin);
                    }
                }
                if (!PNData.IsDBNull(r["CUSTOM_OPACITY"]))
                {
                    note.CustomOpacity = (bool)r["CUSTOM_OPACITY"];
                }
            }
            catch (Exception ex)
            {
                PNStatic.LogException(ex);
            }
        }
コード例 #4
0
ファイル: PNData.cs プロジェクト: hyrmedia/PNotes.NET
 internal static void InsertNewGroup(PNGroup group)
 {
     try
     {
         var c = new ColorConverter();
         var drcc = new System.Drawing.ColorConverter();
         var wfc = new WPFFontConverter();
         var lfc = new LogFontConverter();
         var sb = new StringBuilder();
         sb.Append("INSERT INTO GROUPS (GROUP_ID, PARENT_ID, GROUP_NAME, ICON, BACK_COLOR, CAPTION_FONT_COLOR, CAPTION_FONT, SKIN_NAME, PASSWORD_STRING, FONT, FONT_COLOR, IS_DEFAULT_IMAGE) VALUES(");
         sb.Append(group.ID);
         sb.Append(",");
         sb.Append(group.ParentID);
         sb.Append(",'");
         sb.Append(group.Name.Replace("'", "''"));
         sb.Append("','");
         if (!group.IsDefaultImage)
         {
             var base64String = Convert.ToBase64String(group.Image.ToBytes());
             sb.Append(base64String);
         }
         else
         {
             sb.Append(group.ImageName);
             sb.Append(".png");
         }
         //using (var ms = new MemoryStream(1))
         //{
         //    group.Image.Save(ms, System.Drawing.Imaging.ImageFormat.Png);
         //    ms.Position = 0;
         //    string base64String = Convert.ToBase64String(ms.ToArray());
         //    sb.Append(base64String);
         //}
         sb.Append("','");
         sb.Append(c.ConvertToString(null, PNStatic.CultureInvariant, group.Skinless.BackColor));
         sb.Append("','");
         sb.Append(c.ConvertToString(null, PNStatic.CultureInvariant, group.Skinless.CaptionColor));
         sb.Append("','");
         sb.Append(wfc.ConvertToString(group.Skinless.CaptionFont));
         sb.Append("','");
         sb.Append(group.Skin.SkinName);
         sb.Append("','");
         sb.Append(group.PasswordString);
         sb.Append("','");
         sb.Append(lfc.ConvertToString(group.Font));
         sb.Append("','");
         sb.Append(drcc.ConvertToString(null, PNStatic.CultureInvariant, group.FontColor));
         sb.Append("', ");
         sb.Append(Convert.ToInt32(group.IsDefaultImage));
         sb.Append("); ");
         if (ExecuteTransactionForStringBuilder(sb, ConnectionString))
         {
             sb = new StringBuilder();
             var id = HK_START;
             using (var oData = new SQLiteDataObject(ConnectionString))
             {
                 var o = oData.GetScalar("SELECT MAX(ID) FROM HOT_KEYS");
                 if (o != null && !DBNull.Value.Equals(o))
                 {
                     id = (int)(long)o + 1;
                 }
             }
             var prefix = group.ID + "_show";
             sb.Append("INSERT INTO HOT_KEYS (HK_TYPE, MENU_NAME, ID, SHORTCUT) VALUES(");
             sb.Append(((int)HotkeyType.Group).ToString(PNStatic.CultureInvariant));
             sb.Append(",'");
             sb.Append(prefix);
             sb.Append("',");
             sb.Append(id.ToString(PNStatic.CultureInvariant));
             sb.Append(",'');");
             if (ExecuteTransactionForStringBuilder(sb, ConnectionString))
             {
                 PNStatic.HotKeysGroups.Add(new PNHotKey { MenuName = prefix, ID = id, Type = HotkeyType.Group });
             }
             sb = new StringBuilder();
             id++;
             prefix = group.ID + "_hide";
             sb.Append("INSERT INTO HOT_KEYS (HK_TYPE, MENU_NAME, ID, SHORTCUT) VALUES(");
             sb.Append(((int)HotkeyType.Group).ToString(PNStatic.CultureInvariant));
             sb.Append(",'");
             sb.Append(prefix);
             sb.Append("',");
             sb.Append(id.ToString(PNStatic.CultureInvariant));
             sb.Append(",'');");
             if (ExecuteTransactionForStringBuilder(sb, ConnectionString))
             {
                 PNStatic.HotKeysGroups.Add(new PNHotKey { MenuName = prefix, ID = id, Type = HotkeyType.Group });
             }
         }
     }
     catch (Exception ex)
     {
         PNStatic.LogException(ex);
     }
 }
コード例 #5
0
ファイル: PNData.cs プロジェクト: hyrmedia/PNotes.NET
 internal static void InsertDefaultGroup(int id, int parentID, string name, BitmapImage image, ImageFormat imageFormat)
 {
     try
     {
         var c = new ColorConverter();
         var wpfFontConverter = new WPFFontConverter();
         var sb = new StringBuilder();
         sb.Append("INSERT INTO GROUPS (GROUP_ID, PARENT_ID, GROUP_NAME, ICON, BACK_COLOR, CAPTION_FONT_COLOR, CAPTION_FONT, SKIN_NAME, PASSWORD_STRING, IS_DEFAULT_IMAGE) VALUES(");
         sb.Append(id);
         sb.Append(",");
         sb.Append(parentID);
         sb.Append(",'");
         sb.Append(name.Replace("'", "''"));
         sb.Append("','");
         var base64String = Convert.ToBase64String(image.ToBytes());
         sb.Append(base64String);
         //using (var ms = new MemoryStream(1))
         //{
         //    image.Save(ms, imageFormat);
         //    ms.Position = 0;
         //    string base64String = Convert.ToBase64String(ms.ToArray());
         //    sb.Append(base64String);
         //}
         sb.Append("','");
         sb.Append(c.ConvertToString(null, PNStatic.CultureInvariant, PNSkinlessDetails.DefColor));
         sb.Append("','");
         sb.Append(c.ConvertToString(null, PNStatic.CultureInvariant, SystemColors.ControlTextColor));
         sb.Append("','");
         var f = new PNFont { FontWeight = FontWeights.Bold };
         sb.Append(wpfFontConverter.ConvertToString(f));
         sb.Append("','");
         sb.Append(PNSkinDetails.NO_SKIN);
         sb.Append("','',1");
         sb.Append("); ");
         ExecuteTransactionForStringBuilder(sb, ConnectionString);
     }
     catch (Exception ex)
     {
         PNStatic.LogException(ex);
     }
 }
コード例 #6
0
ファイル: PNData.cs プロジェクト: hyrmedia/PNotes.NET
        internal static void NormalizeGroupsTable(SQLiteDataObject oData, bool updateIcons = true)
        {
            try
            {
                var listSql = new List<string>();
                var lfc = new LogFontConverter();
                var wfc = new WPFFontConverter();
                var drawingColorConverter = new System.Drawing.ColorConverter();
                var mediaColorConverter = new ColorConverter();

                if (updateIcons)
                {
                    using (
                        var t =
                            oData.FillDataTable(
                                "SELECT GROUP_ID, ICON FROM GROUPS WHERE ICON NOT LIKE '%.png' OR ICON LIKE 'resource.%'")
                        )
                    {
                        foreach (DataRow r in t.Rows)
                        {
                            var sb = new StringBuilder("UPDATE GROUPS SET ");
                            var icon = Convert.ToString(r["ICON"]);
                            if (icon.StartsWith("resource."))
                            {
                                var imageName = icon.Substring("resource.".Length);
                                imageName = imageName + ".png";
                                sb.Append("ICON = '");
                                sb.Append(imageName);
                                sb.Append("'");
                            }
                            else
                            {
                                sb.Append("IS_DEFAULT_IMAGE = 0");
                            }
                            sb.Append(" WHERE GROUP_ID = ");
                            sb.Append(r["GROUP_ID"]);
                            listSql.Add(sb.ToString());
                        }
                    }
                }
                using (
                    var t =
                        oData.FillDataTable(
                            "SELECT GROUP_ID, CAPTION_FONT FROM GROUPS WHERE CAPTION_FONT LIKE '%^%'"))
                {
                    foreach (DataRow r in t.Rows)
                    {
                        var lf = lfc.ConvertFromString((string)r["CAPTION_FONT"]);
                        var fnt = PNStatic.FromLogFont(lf);
                        var sb = new StringBuilder("UPDATE GROUPS SET CAPTION_FONT = '");
                        sb.Append(wfc.ConvertToString(null, PNStatic.CultureInvariant, fnt));
                        sb.Append("' WHERE GROUP_ID = ");
                        sb.Append(r["GROUP_ID"]);
                        listSql.Add(sb.ToString());
                    }
                }
                using (var t = oData.FillDataTable("SELECT GROUP_ID, BACK_COLOR, CAPTION_FONT_COLOR FROM GROUPS"))
                {
                    var backColors = new List<TempColor>();
                    var capColors = new List<TempColor>();
                    foreach (DataRow r in t.Rows)
                    {
                        if (!IsDBNull(r["BACK_COLOR"]))
                            backColors.Add(new TempColor
                            {
                                Color = Convert.ToString(r["BACK_COLOR"]),
                                Id = Convert.ToString(r["GROUP_ID"])
                            });
                        if (!IsDBNull(r["CAPTION_FONT_COLOR"]))
                            capColors.Add(new TempColor
                            {
                                Color = Convert.ToString(r["CAPTION_FONT_COLOR"]),
                                Id = Convert.ToString(r["GROUP_ID"])
                            });
                    }
                    foreach (var cr in backColors.Where(c => !IsMediaColor(c.Color)))
                    {
                        var convertFromString = drawingColorConverter.ConvertFromString(null, PNStatic.CultureInvariant, cr.Color);
                        if (convertFromString == null) continue;
                        var clr = (System.Drawing.Color)convertFromString;
                        var color = Color.FromArgb(clr.A, clr.R, clr.G, clr.B);
                        var sb = new StringBuilder("UPDATE GROUPS SET BACK_COLOR = '");
                        sb.Append(mediaColorConverter.ConvertToString(null, PNStatic.CultureInvariant, color));
                        sb.Append("' WHERE GROUP_ID = ");
                        sb.Append(cr.Id);
                        listSql.Add(sb.ToString());
                    }
                    foreach (var cr in capColors.Where(c => !IsMediaColor(c.Color)))
                    {
                        var convertFromString = drawingColorConverter.ConvertFromString(null, PNStatic.CultureInvariant, cr.Color);
                        if (convertFromString == null) continue;
                        var clr = (System.Drawing.Color)convertFromString;
                        var color = Color.FromArgb(clr.A, clr.R, clr.G, clr.B);
                        var sb = new StringBuilder("UPDATE GROUPS SET CAPTION_FONT_COLOR = '");
                        sb.Append(mediaColorConverter.ConvertToString(null, PNStatic.CultureInvariant, color));
                        sb.Append("' WHERE GROUP_ID = ");
                        sb.Append(cr.Id);
                        listSql.Add(sb.ToString());
                    }
                }

                if (listSql.Count == 0) return;
                ExecuteTransactionForList(listSql, ConnectionString);
            }
            catch (Exception ex)
            {
                PNStatic.LogException(ex);
            }
        }
コード例 #7
0
ファイル: PNData.cs プロジェクト: hyrmedia/PNotes.NET
        internal static void NormalizeCustomNotesTable(SQLiteDataObject oData)
        {
            try
            {
                var listSql = new List<string>();
                var lfc = new LogFontConverter();
                var wfc = new WPFFontConverter();
                var drawingColorConverter = new System.Drawing.ColorConverter();
                var mediaColorConverter = new ColorConverter();

                using (
                        var t =
                            oData.FillDataTable(
                                "SELECT NOTE_ID, CAPTION_FONT FROM CUSTOM_NOTES_SETTINGS WHERE CAPTION_FONT LIKE '%^%'"))
                {
                    foreach (DataRow r in t.Rows)
                    {
                        var lf = lfc.ConvertFromString((string)r["CAPTION_FONT"]);
                        var fnt = PNStatic.FromLogFont(lf);
                        var sb = new StringBuilder("UPDATE CUSTOM_NOTES_SETTINGS SET CAPTION_FONT = '");
                        sb.Append(wfc.ConvertToString(null, PNStatic.CultureInvariant, fnt));
                        sb.Append("' WHERE NOTE_ID = ");
                        sb.Append(r["NOTE_ID"]);
                        listSql.Add(sb.ToString());
                    }
                }
                using (var t = oData.FillDataTable("SELECT NOTE_ID, BACK_COLOR, CAPTION_FONT_COLOR FROM CUSTOM_NOTES_SETTINGS"))
                {
                    var backColors = new List<TempColor>();
                    var capColors = new List<TempColor>();
                    foreach (DataRow r in t.Rows)
                    {
                        if (!IsDBNull(r["BACK_COLOR"]))
                            backColors.Add(new TempColor
                            {
                                Color = Convert.ToString(r["BACK_COLOR"]),
                                Id = Convert.ToString(r["NOTE_ID"])
                            });
                        if (!IsDBNull(r["CAPTION_FONT_COLOR"]))
                            capColors.Add(new TempColor
                            {
                                Color = Convert.ToString(r["CAPTION_FONT_COLOR"]),
                                Id = Convert.ToString(r["NOTE_ID"])
                            });
                    }
                    foreach (var cr in backColors.Where(c => !IsMediaColor(c.Color)))
                    {
                        var convertFromString = drawingColorConverter.ConvertFromString(null, PNStatic.CultureInvariant, cr.Color);
                        if (convertFromString == null) continue;
                        var clr = (System.Drawing.Color)convertFromString;
                        var color = Color.FromArgb(clr.A, clr.R, clr.G, clr.B);
                        var sb = new StringBuilder("UPDATE CUSTOM_NOTES_SETTINGS SET BACK_COLOR = '");
                        sb.Append(mediaColorConverter.ConvertToString(null, PNStatic.CultureInvariant, color));
                        sb.Append("' WHERE NOTE_ID = ");
                        sb.Append(cr.Id);
                        listSql.Add(sb.ToString());
                    }
                    foreach (var cr in capColors.Where(c => !IsMediaColor(c.Color)))
                    {
                        var convertFromString = drawingColorConverter.ConvertFromString(null, PNStatic.CultureInvariant, cr.Color);
                        if (convertFromString == null) continue;
                        var clr = (System.Drawing.Color)convertFromString;
                        var color = Color.FromArgb(clr.A, clr.R, clr.G, clr.B);
                        var sb = new StringBuilder("UPDATE CUSTOM_NOTES_SETTINGS SET CAPTION_FONT_COLOR = '");
                        sb.Append(mediaColorConverter.ConvertToString(null, PNStatic.CultureInvariant, color));
                        sb.Append("' WHERE NOTE_ID = ");
                        sb.Append(cr.Id);
                        listSql.Add(sb.ToString());
                    }
                }

                if (listSql.Count == 0) return;
                ExecuteTransactionForList(listSql, ConnectionString);
            }
            catch (Exception ex)
            {
                PNStatic.LogException(ex);
            }
        }
コード例 #8
0
ファイル: PNData.cs プロジェクト: hyrmedia/PNotes.NET
        internal static void LoadDBSettings()
        {
            try
            {
                var mediaConverter = new ColorConverter();
                var drawingConverter = new System.Drawing.ColorConverter();
                var sc = new SizeConverter();
                var pc = new PointConverter();
                var wpfc = new WPFFontConverter();
                //var fc = new FontConverter();
                var v = Assembly.GetExecutingAssembly().GetName().Version;
                PNStatic.Settings = new PNSettings();
                SettingsConnectionString = SQLiteDataObject.CheckAndCreateDatabase(PNPaths.Instance.SettingsDBPath);
                using (var oData = new SQLiteDataObject(SettingsConnectionString))
                {
                    string sqlQuery;
                    //config
                    var pnc = PNStatic.Settings.Config;
                    if (!oData.TableExists("CONFIG"))
                    {
                        sqlQuery = "CREATE TABLE [CONFIG] ([LAST_PAGE] TEXT, [EXIT_FLAG] INT, [CP_LAST_GROUP] INT, [SKINNABLE] BOOLEAN, [CP_PVW_COLOR] TEXT, [CP_USE_CUST_PVW_COLOR] BOOLEAN, [CP_SIZE] TEXT, [CP_LOCATION] TEXT, [CONTROLS_STYLE] TEXT, [CP_PVW_RIGHT] BOOLEAN, [UI_FONT] TEXT, [PROGRAM_VERSION] TEXT, [CP_PVW_SHOW] BOOLEAN, [CP_GROUPS_SHOW] BOOLEAN, [NOTES_WITH_SHORTCUTS] TEXT, [SEARCH_NOTES_SETT] TEXT)";
                        oData.Execute(sqlQuery);
                        sqlQuery =
                            "INSERT INTO CONFIG VALUES(NULL, -1, NULL, NULL, NULL, NULL, '1000,600', NULL, NULL, NULL, NULL, '" +
                            v.ToString(3) + "', NULL, NULL, NULL, NULL)";
                        oData.Execute(sqlQuery);
                        PNSingleton.Instance.FontUser = new PNFont();
                        pnc.CPSize = new Size(1000, 600);
                    }
                    else
                    {
                        using (var t = oData.GetSchema("Columns"))
                        {
                            var rows = t.Select("COLUMN_NAME = 'PROGRAM_VERSION' AND TABLE_NAME = 'CONFIG'");
                            if (rows.Length == 0)
                            {
                                PNSingleton.Instance.PlatformChanged = true;
                                sqlQuery = "ALTER TABLE CONFIG ADD COLUMN [PROGRAM_VERSION] TEXT";
                                oData.Execute(sqlQuery);
                                //save previous edition files
                                PNStatic.SpTextProvider.SplashText = PNLang.Instance.GetMessageText("back_prev",
                                    "Backing up files from previous edition...");
                                savePreviousFiles();
                            }
                            rows = t.Select("COLUMN_NAME = 'CP_PVW_RIGHT' AND TABLE_NAME = 'CONFIG'");
                            if (rows.Length == 0)
                            {
                                sqlQuery = "ALTER TABLE CONFIG ADD COLUMN [CP_PVW_RIGHT] BOOLEAN";
                                oData.Execute(sqlQuery);
                            }
                            rows = t.Select("COLUMN_NAME = 'CP_PVW_SHOW' AND TABLE_NAME = 'CONFIG'");
                            if (rows.Length == 0)
                            {
                                sqlQuery = "ALTER TABLE CONFIG ADD COLUMN [CP_PVW_SHOW] BOOLEAN";
                                oData.Execute(sqlQuery);
                            }
                            rows = t.Select("COLUMN_NAME = 'CP_GROUPS_SHOW' AND TABLE_NAME = 'CONFIG'");
                            if (rows.Length == 0)
                            {
                                sqlQuery = "ALTER TABLE CONFIG ADD COLUMN [CP_GROUPS_SHOW] BOOLEAN";
                                oData.Execute(sqlQuery);
                            }
                            rows = t.Select("COLUMN_NAME = 'UI_FONT' AND TABLE_NAME = 'CONFIG'");
                            if (rows.Length == 0)
                            {
                                sqlQuery = "ALTER TABLE CONFIG ADD COLUMN [UI_FONT] TEXT";
                                oData.Execute(sqlQuery);
                            }
                            rows = t.Select("COLUMN_NAME = 'NOTES_WITH_SHORTCUTS' AND TABLE_NAME = 'CONFIG'");
                            if (rows.Length == 0)
                            {
                                sqlQuery = "ALTER TABLE CONFIG ADD COLUMN [NOTES_WITH_SHORTCUTS] TEXT";
                                oData.Execute(sqlQuery);
                            }
                            rows = t.Select("COLUMN_NAME = 'SEARCH_NOTES_SETT' AND TABLE_NAME = 'CONFIG'");
                            if (rows.Length == 0)
                            {
                                sqlQuery = "ALTER TABLE CONFIG ADD COLUMN [SEARCH_NOTES_SETT] TEXT";
                                oData.Execute(sqlQuery);
                            }
                        }
                        //store version
                        sqlQuery = "UPDATE CONFIG SET PROGRAM_VERSION = '" + v.ToString(3) + "'";
                        oData.Execute(sqlQuery);
                        //upgrade colors an fonts
                        if (PNSingleton.Instance.PlatformChanged)
                        {
                            var obj = oData.GetScalar("SELECT CP_PVW_COLOR FROM CONFIG");
                            if (obj != null && !IsDBNull(obj))
                            {
                                obj = drawingConverter.ConvertFromString(null, PNStatic.CultureInvariant, (string)obj);
                                if (obj != null)
                                {
                                    var clrD = (System.Drawing.Color)obj;
                                    var clrM = Color.FromArgb(clrD.A, clrD.R, clrD.G, clrD.B);
                                    var clrText = mediaConverter.ConvertToString(clrM);
                                    oData.Execute("UPDATE CONFIG SET CP_PVW_COLOR = '" + clrText + "'");
                                }
                            }
                            obj = oData.GetScalar("SELECT UI_FONT FROM CONFIG");
                            if (obj != null && !IsDBNull(obj))
                            {
                                var pnFonf = PNStatic.FromDrawingFont((string)obj);
                                var fontText = wpfc.ConvertToString(pnFonf);
                                oData.Execute("UPDATE CONFIG SET UI_FONT = '" + fontText + "'");
                            }
                        }
                        sqlQuery = "SELECT * FROM CONFIG";
                        using (var t = oData.FillDataTable(sqlQuery))
                        {
                            if (t.Rows.Count > 0)
                            {
                                var r = t.Rows[0];
                                if (!IsDBNull(r["LAST_PAGE"]))
                                {
                                    pnc.LastPage = Convert.ToInt32(r["LAST_PAGE"]);
                                }

                                if (!IsDBNull(r["EXIT_FLAG"]))
                                {
                                    pnc.ExitFlag = (int)r["EXIT_FLAG"];
                                }
                                if (!IsDBNull(r["CP_LAST_GROUP"]))
                                {
                                    pnc.CPLastGroup = (int)r["CP_LAST_GROUP"];
                                }
                                if (!IsDBNull(r["SKINNABLE"]))
                                {
                                    pnc.Skinnable = Convert.ToBoolean(r["SKINNABLE"]);
                                }
                                if (!IsDBNull(r["CP_PVW_COLOR"]))
                                {
                                    var convertFromString = mediaConverter.ConvertFromString(null, PNStatic.CultureInvariant, (string)r["CP_PVW_COLOR"]);
                                    if (
                                        convertFromString != null)
                                        pnc.CPPvwColor = (Color)convertFromString;
                                }
                                if (!IsDBNull(r["CP_USE_CUST_PVW_COLOR"]))
                                {
                                    pnc.CPUseCustPvwColor = Convert.ToBoolean(r["CP_USE_CUST_PVW_COLOR"]);
                                }
                                if (!IsDBNull(r["CP_SIZE"]))
                                {
                                    var str = Convert.ToString(r["CP_LOCATION"]);
                                    if (!str.Contains('-'))
                                    {
                                        str = Convert.ToString(r["CP_SIZE"]);
                                        var convertFromString = sc.ConvertFromString(null, PNStatic.CultureInvariant,
                                            str);
                                        if (convertFromString != null)
                                            pnc.CPSize = (Size)convertFromString;
                                    }
                                }
                                if (!IsDBNull(r["CP_LOCATION"]))
                                {
                                    var convertFromString = pc.ConvertFromString(null, PNStatic.CultureInvariant, (string)r["CP_LOCATION"]);
                                    if (
                                        convertFromString != null)
                                        pnc.CPLocation = (Point)convertFromString;
                                }
                                if (!IsDBNull(r["CONTROLS_STYLE"]))
                                {
                                    pnc.ControlsStyle = (string)r["CONTROLS_STYLE"];
                                }
                                if (!IsDBNull(r["CP_PVW_RIGHT"]))
                                {
                                    pnc.CPPvwRight = Convert.ToBoolean(r["CP_PVW_RIGHT"]);
                                }
                                if (!IsDBNull(r["CP_PVW_SHOW"]))
                                {
                                    pnc.CPPvwShow = Convert.ToBoolean(r["CP_PVW_SHOW"]);
                                }
                                if (!IsDBNull(r["CP_GROUPS_SHOW"]))
                                {
                                    pnc.CPGroupsShow = Convert.ToBoolean(r["CP_GROUPS_SHOW"]);
                                }
                                if (!IsDBNull(r["UI_FONT"]))
                                {
                                    var temp = (string)(r["UI_FONT"]);
                                    if (temp != "")
                                    {
                                        PNSingleton.Instance.FontUser = (PNFont)wpfc.ConvertFromString(temp);
                                    }
                                    else
                                    {
                                        PNSingleton.Instance.FontUser = new PNFont();
                                    }
                                }
                                else
                                {
                                    PNSingleton.Instance.FontUser = new PNFont();
                                }
                                if (!IsDBNull(r["SEARCH_NOTES_SETT"]))
                                {
                                    var arr = Convert.ToString(r["SEARCH_NOTES_SETT"]).Split('|');
                                    pnc.SearchNotesSettings.WholewWord = Convert.ToBoolean(arr[0]);
                                    pnc.SearchNotesSettings.MatchCase = Convert.ToBoolean(arr[1]);
                                    pnc.SearchNotesSettings.IncludeHidden = Convert.ToBoolean(arr[2]);
                                    pnc.SearchNotesSettings.Criteria = Convert.ToInt32(arr[3]);
                                    pnc.SearchNotesSettings.Scope = Convert.ToInt32(arr[4]);
                                }
                                SaveExitFlag(-1);
                            }
                            else
                            {
                                sqlQuery =
                                    "INSERT INTO CONFIG VALUES(NULL, -1, NULL, NULL, NULL, NULL, '1000,600', NULL, NULL, NULL, NULL, '" +
                                    v.ToString(3) + "', NULL, NULL, NULL, NULL)";
                                oData.Execute(sqlQuery);
                                PNSingleton.Instance.FontUser = new PNFont();
                                pnc.CPSize = new Size(1000, 600);
                            }
                        }
                    }

                    //general setting
                    var pngeneral = PNStatic.Settings.GeneralSettings;
                    if (!oData.TableExists("GENERAL_SETTINGS"))
                    {
                        sqlQuery = "CREATE TABLE [GENERAL_SETTINGS] ([LANGUAGE] TEXT, [RUN_ON_START] BOOLEAN, [SHOW_CP_ON_START] BOOLEAN, [CHECK_NEW_VERSION_ON_START] BOOLEAN, [HIDE_TOOLBAR] BOOLEAN, [USE_CUSTOM_FONTS] BOOLEAN, [SHOW_SCROLLBAR] BOOLEAN, [HIDE_DELETE_BUTTON] BOOLEAN, [CHANGE_HIDE_TO_DELETE] BOOLEAN, [HIDE_HIDE_BUTTON] BOOLEAN, [BULLETS_INDENT] INT, [MARGIN_WIDTH] INT, [SAVE_ON_EXIT] BOOLEAN, [CONFIRM_SAVING] BOOLEAN, [CONFIRM_BEFORE_DELETION] BOOLEAN, [SAVE_WITHOUT_CONFIRM_ON_HIDE] BOOLEAN, [WARN_ON_AUTOMATICAL_DELETE] BOOLEAN, [AUTO_SAVE] BOOLEAN, [AUTO_SAVE_PERIOD] INT, [REMOVE_FROM_BIN_PERIOD] INT, [DATE_FORMAT] TEXT, [TIME_FORMAT] TEXT, [SKINLESS_WIDTH] INT, [SKINLESS_HEIGHT] INT, [SPELL_COLOR] TEXT, [USE_SKINS] BOOLEAN, [SPELL_MODE] INT, [SPELL_DICT] TEXT, [DOCK_WIDTH] INT, [DOCK_HEIGHT] INT, [SHOW_PRIORITY_ON_START] BOOLEAN, [BUTTONS_SIZE] INT, [AUTOMATIC_SMILIES] BOOLEAN, [SPACE_POINTS] INT, [RESTORE_AUTO] BOOLEAN, [PARAGRAPH_INDENT] INT, [AUTO_HEIGHT] BOOLEAN, [CRITICAL_ON_START] BOOLEAN, [CRITICAL_PERIODICALLY] BOOLEAN, [DELETE_SHORTCUTS_ON_EXIT] BOOLEAN, [RESTORE_SHORTCUTS_ON_START] BOOLEAN, [CLOSE_ON_SHORTCUT] BOOLEAN)";
                        oData.Execute(sqlQuery);
                        sqlQuery = "INSERT INTO GENERAL_SETTINGS VALUES(NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL)";
                        oData.Execute(sqlQuery);
                        //store default english language
                        var langPath = Path.Combine(PNPaths.Instance.LangDir, pngeneral.Language);
                        PNLang.Instance.LoadLanguage(langPath);
                    }
                    else
                    {
                        using (var t = oData.GetSchema("Columns"))
                        {
                            var rows = t.Select("COLUMN_NAME = 'BUTTONS_SIZE' AND TABLE_NAME = 'GENERAL_SETTINGS'");
                            if (rows.Length == 0)
                            {
                                sqlQuery = "ALTER TABLE GENERAL_SETTINGS ADD COLUMN [BUTTONS_SIZE] INT";
                                oData.Execute(sqlQuery);
                            }
                            rows = t.Select("COLUMN_NAME = 'AUTOMATIC_SMILIES' AND TABLE_NAME = 'GENERAL_SETTINGS'");
                            if (rows.Length == 0)
                            {
                                sqlQuery = "ALTER TABLE GENERAL_SETTINGS ADD COLUMN [AUTOMATIC_SMILIES] BOOLEAN";
                                oData.Execute(sqlQuery);
                            }
                            rows = t.Select("COLUMN_NAME = 'SPACE_POINTS' AND TABLE_NAME = 'GENERAL_SETTINGS'");
                            if (rows.Length == 0)
                            {
                                sqlQuery = "ALTER TABLE GENERAL_SETTINGS ADD COLUMN [SPACE_POINTS] INT";
                                oData.Execute(sqlQuery);
                            }
                            rows = t.Select("COLUMN_NAME = 'RESTORE_AUTO' AND TABLE_NAME = 'GENERAL_SETTINGS'");
                            if (rows.Length == 0)
                            {
                                sqlQuery = "ALTER TABLE GENERAL_SETTINGS ADD COLUMN [RESTORE_AUTO] BOOLEAN";
                                oData.Execute(sqlQuery);
                            }
                            rows = t.Select("COLUMN_NAME = 'PARAGRAPH_INDENT' AND TABLE_NAME = 'GENERAL_SETTINGS'");
                            if (rows.Length == 0)
                            {
                                sqlQuery = "ALTER TABLE GENERAL_SETTINGS ADD COLUMN [PARAGRAPH_INDENT] INT";
                                oData.Execute(sqlQuery);
                            }
                            rows = t.Select("COLUMN_NAME = 'AUTO_HEIGHT' AND TABLE_NAME = 'GENERAL_SETTINGS'");
                            if (rows.Length == 0)
                            {
                                sqlQuery = "ALTER TABLE GENERAL_SETTINGS ADD COLUMN [AUTO_HEIGHT] BOOLEAN";
                                oData.Execute(sqlQuery);
                            }
                            rows = t.Select("COLUMN_NAME = 'CRITICAL_ON_START' AND TABLE_NAME = 'GENERAL_SETTINGS'");
                            if (rows.Length == 0)
                            {
                                sqlQuery = "ALTER TABLE GENERAL_SETTINGS ADD COLUMN [CRITICAL_ON_START] BOOLEAN";
                                oData.Execute(sqlQuery);
                            }
                            rows = t.Select("COLUMN_NAME = 'CRITICAL_PERIODICALLY' AND TABLE_NAME = 'GENERAL_SETTINGS'");
                            if (rows.Length == 0)
                            {
                                sqlQuery = "ALTER TABLE GENERAL_SETTINGS ADD COLUMN [CRITICAL_PERIODICALLY] BOOLEAN";
                                oData.Execute(sqlQuery);
                            }
                            rows = t.Select("COLUMN_NAME = 'DELETE_SHORTCUTS_ON_EXIT' AND TABLE_NAME = 'GENERAL_SETTINGS'");
                            if (rows.Length == 0)
                            {
                                sqlQuery = "ALTER TABLE GENERAL_SETTINGS ADD COLUMN [DELETE_SHORTCUTS_ON_EXIT] BOOLEAN";
                                oData.Execute(sqlQuery);
                            }
                            rows = t.Select("COLUMN_NAME = 'RESTORE_SHORTCUTS_ON_START' AND TABLE_NAME = 'GENERAL_SETTINGS'");
                            if (rows.Length == 0)
                            {
                                sqlQuery = "ALTER TABLE GENERAL_SETTINGS ADD COLUMN [RESTORE_SHORTCUTS_ON_START] BOOLEAN";
                                oData.Execute(sqlQuery);
                            }
                            rows = t.Select("COLUMN_NAME = 'CLOSE_ON_SHORTCUT' AND TABLE_NAME = 'GENERAL_SETTINGS'");
                            if (rows.Length == 0)
                            {
                                sqlQuery = "ALTER TABLE GENERAL_SETTINGS ADD COLUMN [CLOSE_ON_SHORTCUT] BOOLEAN";
                                oData.Execute(sqlQuery);
                            }
                        }
                        sqlQuery = "SELECT * FROM GENERAL_SETTINGS";
                        using (var t = oData.FillDataTable(sqlQuery))
                        {
                            if (t.Rows.Count > 0)
                            {
                                var r = t.Rows[0];
                                if (!IsDBNull(r["LANGUAGE"]))
                                {
                                    pngeneral.Language = (string) r["LANGUAGE"];
                                }
                                var langPath = Path.Combine(PNPaths.Instance.LangDir, pngeneral.Language);
                                PNLang.Instance.LoadLanguage(langPath);
                                if (!IsDBNull(r["RUN_ON_START"]))
                                {
                                    pngeneral.RunOnStart = (bool)r["RUN_ON_START"];
                                }
                                if (!IsDBNull(r["HIDE_TOOLBAR"]))
                                {
                                    pngeneral.HideToolbar = (bool)r["HIDE_TOOLBAR"];
                                }
                                if (!IsDBNull(r["SHOW_CP_ON_START"]))
                                {
                                    pngeneral.ShowCPOnStart = (bool)r["SHOW_CP_ON_START"];
                                }
                                if (!IsDBNull(r["CHECK_NEW_VERSION_ON_START"]))
                                {
                                    pngeneral.CheckNewVersionOnStart = (bool)r["CHECK_NEW_VERSION_ON_START"];
                                }
                                if (!IsDBNull(r["USE_CUSTOM_FONTS"]))
                                {
                                    pngeneral.UseCustomFonts = (bool)r["USE_CUSTOM_FONTS"];
                                }
                                if (!IsDBNull(r["SHOW_SCROLLBAR"]))
                                {
                                        pngeneral.ShowScrollbar =
                                            (System.Windows.Forms.RichTextBoxScrollBars)
                                                Convert.ToInt32(r["SHOW_SCROLLBAR"]);
                                }
                                if (!IsDBNull(r["HIDE_DELETE_BUTTON"]))
                                {
                                    pngeneral.HideDeleteButton = (bool)r["HIDE_DELETE_BUTTON"];
                                }
                                if (!IsDBNull(r["CHANGE_HIDE_TO_DELETE"]))
                                {
                                    pngeneral.ChangeHideToDelete = (bool)r["CHANGE_HIDE_TO_DELETE"];
                                }
                                if (!IsDBNull(r["HIDE_HIDE_BUTTON"]))
                                {
                                    pngeneral.HideHideButton = (bool)r["HIDE_HIDE_BUTTON"];
                                }
                                if (!IsDBNull(r["BULLETS_INDENT"]))
                                {
                                    pngeneral.BulletsIndent = (short)(int)r["BULLETS_INDENT"];
                                }
                                if (!IsDBNull(r["MARGIN_WIDTH"]))
                                {
                                    pngeneral.MarginWidth = (short)(int)r["MARGIN_WIDTH"];
                                }
                                if (!IsDBNull(r["DATE_FORMAT"]))
                                {
                                    pngeneral.DateFormat =
                                        ((string)r["DATE_FORMAT"]).Replace("H", "")
                                            .Replace("h", "")
                                            .Replace("m", "")
                                            .Replace(":", "")
                                            .Trim();
                                    Thread.CurrentThread.CurrentCulture.DateTimeFormat.ShortDatePattern = pngeneral.DateFormat;
                                }
                                if (!IsDBNull(r["TIME_FORMAT"]))
                                {
                                    pngeneral.TimeFormat = (string)r["TIME_FORMAT"];
                                    Thread.CurrentThread.CurrentCulture.DateTimeFormat.LongTimePattern = pngeneral.TimeFormat;
                                }
                                if (!IsDBNull(r["SAVE_ON_EXIT"]))
                                {
                                    pngeneral.SaveOnExit = (bool)r["SAVE_ON_EXIT"];
                                }
                                if (!IsDBNull(r["CONFIRM_SAVING"]))
                                {
                                    pngeneral.ConfirmSaving = (bool)r["CONFIRM_SAVING"];
                                }
                                if (!IsDBNull(r["CONFIRM_BEFORE_DELETION"]))
                                {
                                    pngeneral.ConfirmBeforeDeletion = (bool)r["CONFIRM_BEFORE_DELETION"];
                                }
                                if (!IsDBNull(r["SAVE_WITHOUT_CONFIRM_ON_HIDE"]))
                                {
                                    pngeneral.SaveWithoutConfirmOnHide = (bool)r["SAVE_WITHOUT_CONFIRM_ON_HIDE"];
                                }
                                if (!IsDBNull(r["WARN_ON_AUTOMATICAL_DELETE"]))
                                {
                                    pngeneral.WarnOnAutomaticalDelete = (bool)r["WARN_ON_AUTOMATICAL_DELETE"];
                                }
                                if (!IsDBNull(r["REMOVE_FROM_BIN_PERIOD"]))
                                {
                                    pngeneral.RemoveFromBinPeriod = (int)r["REMOVE_FROM_BIN_PERIOD"];
                                }
                                if (!IsDBNull(r["AUTO_SAVE"]))
                                {
                                    pngeneral.Autosave = (bool)r["AUTO_SAVE"];
                                }
                                if (!IsDBNull(r["AUTO_SAVE_PERIOD"]))
                                {
                                    pngeneral.AutosavePeriod = (int)r["AUTO_SAVE_PERIOD"];
                                }
                                if (!IsDBNull(r["SKINLESS_WIDTH"]))
                                {
                                    pngeneral.Width = (int)r["SKINLESS_WIDTH"];
                                }
                                if (!IsDBNull(r["SKINLESS_HEIGHT"]))
                                {
                                    pngeneral.Height = (int)r["SKINLESS_HEIGHT"];
                                }
                                if (!IsDBNull(r["SPELL_COLOR"]))
                                {
                                    pngeneral.SpellColor = (System.Drawing.Color)
                                            drawingConverter.ConvertFromString(null, PNStatic.CultureInvariant,
                                                (string)r["SPELL_COLOR"]);
                                }
                                if (!IsDBNull(r["USE_SKINS"]))
                                {
                                    pngeneral.UseSkins = (bool)r["USE_SKINS"];
                                }
                                if (!IsDBNull(r["SPELL_MODE"]))
                                {
                                    pngeneral.SpellMode = (int)r["SPELL_MODE"];
                                }
                                if (!IsDBNull(r["SPELL_DICT"]))
                                {
                                    pngeneral.SpellDict = (string)r["SPELL_DICT"];
                                }
                                if (!IsDBNull(r["DOCK_WIDTH"]))
                                {
                                    pngeneral.DockWidth = (int)r["DOCK_WIDTH"];
                                }
                                if (!IsDBNull(r["DOCK_HEIGHT"]))
                                {
                                    pngeneral.DockHeight = (int)r["DOCK_HEIGHT"];
                                }
                                if (!IsDBNull(r["SHOW_PRIORITY_ON_START"]))
                                {
                                    pngeneral.ShowPriorityOnStart = (bool)r["SHOW_PRIORITY_ON_START"];
                                }
                                if (!IsDBNull(r["BUTTONS_SIZE"]))
                                {
                                    pngeneral.ButtonsSize = (ToolStripButtonSize)((int)r["BUTTONS_SIZE"]);
                                }
                                if (!IsDBNull(r["AUTOMATIC_SMILIES"]))
                                {
                                    pngeneral.AutomaticSmilies = (bool)r["AUTOMATIC_SMILIES"];
                                }
                                if (!IsDBNull(r["SPACE_POINTS"]))
                                {
                                    pngeneral.SpacePoints = (int)r["SPACE_POINTS"];
                                }
                                if (!IsDBNull(r["RESTORE_AUTO"]))
                                {
                                    pngeneral.RestoreAuto = (bool)r["RESTORE_AUTO"];
                                }
                                if (!IsDBNull(r["PARAGRAPH_INDENT"]))
                                {
                                    pngeneral.ParagraphIndent = (int)r["PARAGRAPH_INDENT"];
                                }
                                if (!IsDBNull(r["AUTO_HEIGHT"]))
                                {
                                    pngeneral.AutoHeight = (bool)r["AUTO_HEIGHT"];
                                }
                                if (!IsDBNull(r["CRITICAL_ON_START"]))
                                {
                                    pngeneral.CheckCriticalOnStart = (bool)r["CRITICAL_ON_START"];
                                }
                                if (!IsDBNull(r["CRITICAL_PERIODICALLY"]))
                                {
                                    pngeneral.CheckCriticalPeriodically = (bool)r["CRITICAL_PERIODICALLY"];
                                }
                                if (!IsDBNull(r["DELETE_SHORTCUTS_ON_EXIT"]))
                                {
                                    pngeneral.DeleteShortcutsOnExit = (bool)r["DELETE_SHORTCUTS_ON_EXIT"];
                                }
                                if (!IsDBNull(r["RESTORE_SHORTCUTS_ON_START"]))
                                {
                                    pngeneral.RestoreShortcutsOnStart = (bool)r["RESTORE_SHORTCUTS_ON_START"];
                                }
                                if (!IsDBNull(r["CLOSE_ON_SHORTCUT"]))
                                {
                                    pngeneral.CloseOnShortcut = (bool)r["CLOSE_ON_SHORTCUT"];
                                }
                            }
                            else
                            {
                                sqlQuery = "INSERT INTO GENERAL_SETTINGS VALUES(NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL)";
                                oData.Execute(sqlQuery);
                            }
                        }
                    }

                    PNStatic.SpTextProvider.SplashText = PNLang.Instance.GetMessageText("load_program_settings", "Loading program settings");

                    //schedule
                    if (!oData.TableExists("SCHEDULE"))
                    {
                        sqlQuery = "CREATE TABLE [SCHEDULE] ([SOUND] TEXT, [DATE_FORMAT] TEXT, [TIME_FORMAT] TEXT, [VOICE] TEXT, [ALLOW_SOUND] BOOLEAN, [TRACK_OVERDUE] BOOLEAN, [VISUAL_NOTIFY] BOOLEAN, [CENTER_SCREEN] BOOLEAN, [VOICE_VOLUME] INT, [VOICE_SPEED] INT, [VOICE_PITCH] INT)";
                        oData.Execute(sqlQuery);
                        sqlQuery = "INSERT INTO SCHEDULE VALUES(NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL)";
                        oData.Execute(sqlQuery);
                    }
                    else
                    {
                        sqlQuery = "SELECT * FROM SCHEDULE";
                        using (var t = oData.FillDataTable(sqlQuery))
                        {
                            if (t.Rows.Count > 0)
                            {
                                var r = t.Rows[0];
                                var pnsc = PNStatic.Settings.Schedule;
                                if (!IsDBNull(r["SOUND"]))
                                {
                                    pnsc.Sound = (string)r["SOUND"];
                                }
                                if (!IsDBNull(r["VOICE"]))
                                {
                                    pnsc.Voice = (string)r["VOICE"];
                                }
                                if (!IsDBNull(r["ALLOW_SOUND"]))
                                {
                                    pnsc.AllowSoundAlert = (bool)r["ALLOW_SOUND"];
                                }
                                if (!IsDBNull(r["TRACK_OVERDUE"]))
                                {
                                    pnsc.TrackOverdue = (bool)r["TRACK_OVERDUE"];
                                }
                                if (!IsDBNull(r["VISUAL_NOTIFY"]))
                                {
                                    pnsc.VisualNotification = (bool)r["VISUAL_NOTIFY"];
                                }
                                if (!IsDBNull(r["CENTER_SCREEN"]))
                                {
                                    pnsc.CenterScreen = (bool)r["CENTER_SCREEN"];
                                }
                                if (!IsDBNull(r["VOICE_VOLUME"]))
                                {
                                    pnsc.VoiceVolume = (int)r["VOICE_VOLUME"];
                                }
                                if (!IsDBNull(r["VOICE_SPEED"]))
                                {
                                    pnsc.VoiceSpeed = (int)r["VOICE_SPEED"];
                                }
                                if (!IsDBNull(r["VOICE_PITCH"]))
                                {
                                    pnsc.VoicePitch = (int)r["VOICE_PITCH"];
                                }
                            }
                            else
                            {
                                sqlQuery = "INSERT INTO SCHEDULE VALUES(NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL)";
                                oData.Execute(sqlQuery);
                            }
                        }
                    }
                    //behavior
                    if (!oData.TableExists("BEHAVIOR"))
                    {
                        sqlQuery = "CREATE TABLE [BEHAVIOR] ([NEW_ALWAYS_ON_TOP] BOOLEAN, [RELATIONAL_POSITION] BOOLEAN, [HIDE_COMPLETED] BOOLEAN, [BIG_ICONS_ON_CP] BOOLEAN, [DO_NOT_SHOW_IN_LIST] BOOLEAN, [KEEP_VISIBLE_ON_SHOW_DESKTOP] BOOLEAN, [DBL_CLICK_ACTION] INT, [SINGLE_CLICK_ACTION] INT, [DEFAULT_NAMING] INT, [DEFAULT_NAME_LENGHT] INT, [CONTENT_COLUMN_LENGTH] INT, [HIDE_FLUENTLY] BOOLEAN, [PLAY_SOUND_ON_HIDE] BOOLEAN, [OPACITY] REAL, [RANDOM_COLOR] BOOLEAN, [INVERT_TEXT_COLOR] BOOLEAN, [ROLL_ON_DBLCLICK] BOOLEAN, [FIT_WHEN_ROLLED] BOOLEAN, [SHOW_SEPARATE_NOTES] BOOLEAN, [PIN_CLICK_ACTION] INT, [NOTE_START_POSITION] INT, [HIDE_MAIN_WINDOW] BOOLEAN, [THEME] TEXT, [PREVENT_RESIZING] BOOLEAN, [SHOW_PANEL] BOOLEAN, [PANEL_DOCK] INT, [PANEL_AUTO_HIDE] BOOLEAN, [PANEL_REMOVE_MODE] INT, [PANEL_SWITCH_OFF_ANIMATION] BOOLEAN, [PANEL_ENTER_DELAY] INT)";
                        oData.Execute(sqlQuery);
                        sqlQuery = "INSERT INTO BEHAVIOR VALUES(NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL)";
                        oData.Execute(sqlQuery);
                    }
                    else
                    {
                        using (var t = oData.GetSchema("Columns"))
                        {
                            var rows = t.Select("COLUMN_NAME = 'NOTE_START_POSITION' AND TABLE_NAME = 'BEHAVIOR'");
                            if (rows.Length == 0)
                            {
                                sqlQuery = "ALTER TABLE BEHAVIOR ADD COLUMN [NOTE_START_POSITION] INT";
                                oData.Execute(sqlQuery);
                            }
                            rows = t.Select("COLUMN_NAME = 'HIDE_MAIN_WINDOW' AND TABLE_NAME = 'BEHAVIOR'");
                            if (rows.Length == 0)
                            {
                                sqlQuery = "ALTER TABLE BEHAVIOR ADD COLUMN [HIDE_MAIN_WINDOW] BOOLEAN";
                                oData.Execute(sqlQuery);
                            }
                            rows = t.Select("COLUMN_NAME = 'THEME' AND TABLE_NAME = 'BEHAVIOR'");
                            if (rows.Length == 0)
                            {
                                sqlQuery = "ALTER TABLE BEHAVIOR ADD COLUMN [THEME] TEXT";
                                oData.Execute(sqlQuery);
                            }
                            rows = t.Select("COLUMN_NAME = 'PREVENT_RESIZING' AND TABLE_NAME = 'BEHAVIOR'");
                            if (rows.Length == 0)
                            {
                                sqlQuery = "ALTER TABLE BEHAVIOR ADD COLUMN [PREVENT_RESIZING] BOOLEAN";
                                oData.Execute(sqlQuery);
                            }
                            rows = t.Select("COLUMN_NAME = 'SHOW_PANEL' AND TABLE_NAME = 'BEHAVIOR'");
                            if (rows.Length == 0)
                            {
                                sqlQuery = "ALTER TABLE BEHAVIOR ADD COLUMN [SHOW_PANEL] BOOLEAN";
                                oData.Execute(sqlQuery);
                            }
                            rows = t.Select("COLUMN_NAME = 'PANEL_DOCK' AND TABLE_NAME = 'BEHAVIOR'");
                            if (rows.Length == 0)
                            {
                                sqlQuery = "ALTER TABLE BEHAVIOR ADD COLUMN [PANEL_DOCK] INT";
                                oData.Execute(sqlQuery);
                            }
                            rows = t.Select("COLUMN_NAME = 'PANEL_AUTO_HIDE' AND TABLE_NAME = 'BEHAVIOR'");
                            if (rows.Length == 0)
                            {
                                sqlQuery = "ALTER TABLE BEHAVIOR ADD COLUMN [PANEL_AUTO_HIDE] BOOLEAN";
                                oData.Execute(sqlQuery);
                            }
                            rows = t.Select("COLUMN_NAME = 'PANEL_REMOVE_MODE' AND TABLE_NAME = 'BEHAVIOR'");
                            if (rows.Length == 0)
                            {
                                sqlQuery = "ALTER TABLE BEHAVIOR ADD COLUMN [PANEL_REMOVE_MODE] INT";
                                oData.Execute(sqlQuery);
                            }
                            rows = t.Select("COLUMN_NAME = 'PANEL_SWITCH_OFF_ANIMATION' AND TABLE_NAME = 'BEHAVIOR'");
                            if (rows.Length == 0)
                            {
                                sqlQuery = "ALTER TABLE BEHAVIOR ADD COLUMN [PANEL_SWITCH_OFF_ANIMATION] BOOLEAN";
                                oData.Execute(sqlQuery);
                            }
                            rows = t.Select("COLUMN_NAME = 'PANEL_ENTER_DELAY' AND TABLE_NAME = 'BEHAVIOR'");
                            if (rows.Length == 0)
                            {
                                sqlQuery = "ALTER TABLE BEHAVIOR ADD COLUMN [PANEL_ENTER_DELAY] INT";
                                oData.Execute(sqlQuery);
                            }
                        }
                        sqlQuery = "SELECT * FROM BEHAVIOR";
                        using (var t = oData.FillDataTable(sqlQuery))
                        {
                            if (t.Rows.Count > 0)
                            {
                                var r = t.Rows[0];
                                var pnb = PNStatic.Settings.Behavior;
                                if (!IsDBNull(r["NEW_ALWAYS_ON_TOP"]))
                                {
                                    pnb.NewNoteAlwaysOnTop = (bool)r["NEW_ALWAYS_ON_TOP"];
                                }
                                if (!IsDBNull(r["RELATIONAL_POSITION"]))
                                {
                                    pnb.RelationalPositioning = (bool)r["RELATIONAL_POSITION"];
                                }
                                if (!IsDBNull(r["HIDE_COMPLETED"]))
                                {
                                    pnb.HideCompleted = (bool)r["HIDE_COMPLETED"];
                                }
                                if (!IsDBNull(r["BIG_ICONS_ON_CP"]))
                                {
                                    pnb.BigIconsOnCP = (bool)r["BIG_ICONS_ON_CP"];
                                }
                                if (!IsDBNull(r["DO_NOT_SHOW_IN_LIST"]))
                                {
                                    pnb.DoNotShowNotesInList = (bool)r["DO_NOT_SHOW_IN_LIST"];
                                }
                                if (!IsDBNull(r["KEEP_VISIBLE_ON_SHOW_DESKTOP"]))
                                {
                                    pnb.KeepVisibleOnShowDesktop = (bool)r["KEEP_VISIBLE_ON_SHOW_DESKTOP"];
                                }
                                if (!IsDBNull(r["DBL_CLICK_ACTION"]))
                                {
                                    var index = (int) r["DBL_CLICK_ACTION"];
                                    if (index < Enum.GetValues(typeof (TrayMouseAction)).Length)
                                        pnb.DoubleClickAction = (TrayMouseAction) index;
                                }
                                if (!IsDBNull(r["SINGLE_CLICK_ACTION"]))
                                {
                                    var index = (int)r["SINGLE_CLICK_ACTION"];
                                    if (index < Enum.GetValues(typeof (TrayMouseAction)).Length)
                                        pnb.SingleClickAction = (TrayMouseAction) index;
                                }
                                if (!IsDBNull(r["DEFAULT_NAMING"]))
                                {
                                    var index = (int)r["DEFAULT_NAMING"];
                                    if (index < Enum.GetValues(typeof(DefaultNaming)).Length)
                                        pnb.DefaultNaming = (DefaultNaming)index;
                                }
                                if (!IsDBNull(r["DEFAULT_NAME_LENGHT"]))
                                {
                                    pnb.DefaultNameLength = (int)r["DEFAULT_NAME_LENGHT"];
                                }
                                if (!IsDBNull(r["CONTENT_COLUMN_LENGTH"]))
                                {
                                    pnb.ContentColumnLength = (int)r["CONTENT_COLUMN_LENGTH"];
                                }
                                if (!IsDBNull(r["HIDE_FLUENTLY"]))
                                {
                                    pnb.HideFluently = (bool)r["HIDE_FLUENTLY"];
                                }
                                if (!IsDBNull(r["PLAY_SOUND_ON_HIDE"]))
                                {
                                    pnb.PlaySoundOnHide = (bool)r["PLAY_SOUND_ON_HIDE"];
                                }
                                if (!IsDBNull(r["OPACITY"]))
                                {
                                    pnb.Opacity = (double)r["OPACITY"];
                                }
                                if (!IsDBNull(r["RANDOM_COLOR"]))
                                {
                                    pnb.RandomBackColor = (bool)r["RANDOM_COLOR"];
                                }
                                if (!IsDBNull(r["INVERT_TEXT_COLOR"]))
                                {
                                    pnb.InvertTextColor = (bool)r["INVERT_TEXT_COLOR"];
                                }
                                if (!IsDBNull(r["ROLL_ON_DBLCLICK"]))
                                {
                                    pnb.RollOnDblClick = (bool)r["ROLL_ON_DBLCLICK"];
                                }
                                if (!IsDBNull(r["FIT_WHEN_ROLLED"]))
                                {
                                    pnb.FitWhenRolled = (bool)r["FIT_WHEN_ROLLED"];
                                }
                                if (!IsDBNull(r["SHOW_SEPARATE_NOTES"]))
                                {
                                    pnb.ShowSeparateNotes = (bool)r["SHOW_SEPARATE_NOTES"];
                                }
                                if (!IsDBNull(r["PIN_CLICK_ACTION"]))
                                {
                                    var index = (int)r["PIN_CLICK_ACTION"];
                                    if (index < Enum.GetValues(typeof(PinClickAction)).Length)
                                        pnb.PinClickAction = (PinClickAction)index;
                                }
                                if (!IsDBNull(r["NOTE_START_POSITION"]))
                                {
                                    var index = (int)r["NOTE_START_POSITION"];
                                    if (index < Enum.GetValues(typeof(NoteStartPosition)).Length)
                                        pnb.StartPosition = (NoteStartPosition)index;
                                }
                                if (!IsDBNull(r["HIDE_MAIN_WINDOW"]))
                                {
                                    pnb.HideMainWindow = (bool)r["HIDE_MAIN_WINDOW"];
                                }
                                if (!IsDBNull(r["THEME"]))
                                {
                                    pnb.Theme = (string) r["THEME"];
                                }
                                else
                                {
                                    pnb.Theme = PNStrings.DEF_THEME;
                                }
                                if (!IsDBNull(r["PREVENT_RESIZING"]))
                                {
                                    pnb.PreventAutomaticResizing = (bool)r["PREVENT_RESIZING"];
                                }
                                if (!IsDBNull(r["SHOW_PANEL"]))
                                {
                                    pnb.ShowNotesPanel = (bool)r["SHOW_PANEL"];
                                }
                                if (!IsDBNull(r["PANEL_DOCK"]))
                                {
                                    pnb.NotesPanelOrientation = (NotesPanelOrientation)r["PANEL_DOCK"];
                                }
                                if (!IsDBNull(r["PANEL_AUTO_HIDE"]))
                                {
                                    pnb.PanelAutoHide = (bool)r["PANEL_AUTO_HIDE"];
                                }
                                if (!IsDBNull(r["PANEL_REMOVE_MODE"]))
                                {
                                    pnb.PanelRemoveMode = (PanelRemoveMode)r["PANEL_REMOVE_MODE"];
                                }
                                if (!IsDBNull(r["PANEL_SWITCH_OFF_ANIMATION"]))
                                {
                                    pnb.PanelSwitchOffAnimation = (bool)r["PANEL_SWITCH_OFF_ANIMATION"];
                                }
                                if (!IsDBNull(r["PANEL_ENTER_DELAY"]))
                                {
                                    pnb.PanelEnterDelay = (int)r["PANEL_ENTER_DELAY"];
                                }
                            }
                            else
                            {
                                sqlQuery = "INSERT INTO BEHAVIOR VALUES(NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL)";
                                oData.Execute(sqlQuery);
                            }
                        }
                    }
                    //protection
                    if (!oData.TableExists("PROTECTION"))
                    {
                        sqlQuery = "CREATE TABLE [PROTECTION] ([STORE_AS_ENCRYPTED] BOOLEAN, [HIDE_TRAY_ICON] BOOLEAN, [BACKUP_BEFORE_SAVING] BOOLEAN, [SILENT_FULL_BACKUP] BOOLEAN, [BACKUP_DEEPNESS] INT, [DO_NOT_SHOW_CONTENT] BOOLEAN, [INCLUDE_BIN_IN_SYNC] BOOLEAN, [PASSWORD_STRING] TEXT, [FULL_BACKUP_DAYS] TEXT, [FULL_BACKUP_TIME] TEXT, [FULL_BACKUP_DATE] TEXT, [PROMPT_PASSWORD] BOOLEAN)";
                        oData.Execute(sqlQuery);
                        sqlQuery = "INSERT INTO PROTECTION VALUES(NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL)";
                        oData.Execute(sqlQuery);
                    }
                    else
                    {
                        using (var t = oData.GetSchema("Columns"))
                        {
                            var rows = t.Select("COLUMN_NAME = 'FULL_BACKUP_DAYS' AND TABLE_NAME = 'PROTECTION'");
                            if (rows.Length == 0)
                            {
                                sqlQuery = "ALTER TABLE PROTECTION ADD COLUMN [FULL_BACKUP_DAYS] TEXT";
                                oData.Execute(sqlQuery);
                            }
                            rows = t.Select("COLUMN_NAME = 'FULL_BACKUP_TIME' AND TABLE_NAME = 'PROTECTION'");
                            if (rows.Length == 0)
                            {
                                sqlQuery = "ALTER TABLE PROTECTION ADD COLUMN [FULL_BACKUP_TIME] TEXT";
                                oData.Execute(sqlQuery);
                            }
                            rows = t.Select("COLUMN_NAME = 'FULL_BACKUP_DATE' AND TABLE_NAME = 'PROTECTION'");
                            if (rows.Length == 0)
                            {
                                sqlQuery = "ALTER TABLE PROTECTION ADD COLUMN [FULL_BACKUP_DATE] TEXT";
                                oData.Execute(sqlQuery);
                            }
                            rows = t.Select("COLUMN_NAME = 'PROMPT_PASSWORD' AND TABLE_NAME = 'PROTECTION'");
                            if (rows.Length == 0)
                            {
                                sqlQuery = "ALTER TABLE PROTECTION ADD COLUMN [PROMPT_PASSWORD] BOOLEAN";
                                oData.Execute(sqlQuery);
                            }
                        }
                        sqlQuery = "SELECT * FROM PROTECTION";
                        using (var t = oData.FillDataTable(sqlQuery))
                        {
                            if (t.Rows.Count > 0)
                            {
                                var r = t.Rows[0];
                                var pnp = PNStatic.Settings.Protection;
                                if (!IsDBNull(r["STORE_AS_ENCRYPTED"]))
                                {
                                    pnp.StoreAsEncrypted = (bool)r["STORE_AS_ENCRYPTED"];
                                }
                                if (!IsDBNull(r["HIDE_TRAY_ICON"]))
                                {
                                    pnp.HideTrayIcon = (bool)r["HIDE_TRAY_ICON"];
                                }
                                if (!IsDBNull(r["BACKUP_BEFORE_SAVING"]))
                                {
                                    pnp.BackupBeforeSaving = (bool)r["BACKUP_BEFORE_SAVING"];
                                }
                                if (!IsDBNull(r["SILENT_FULL_BACKUP"]))
                                {
                                    pnp.SilentFullBackup = (bool)r["SILENT_FULL_BACKUP"];
                                }
                                if (!IsDBNull(r["BACKUP_DEEPNESS"]))
                                {
                                    pnp.BackupDeepness = (int)r["BACKUP_DEEPNESS"];
                                }
                                if (!IsDBNull(r["DO_NOT_SHOW_CONTENT"]))
                                {
                                    pnp.DontShowContent = (bool)r["DO_NOT_SHOW_CONTENT"];
                                }
                                if (!IsDBNull(r["INCLUDE_BIN_IN_SYNC"]))
                                {
                                    pnp.IncludeBinInSync = (bool)r["INCLUDE_BIN_IN_SYNC"];
                                }
                                if (!IsDBNull(r["PASSWORD_STRING"]))
                                {
                                    pnp.PasswordString = (string)r["PASSWORD_STRING"];
                                }
                                if (!IsDBNull(r["FULL_BACKUP_DAYS"]))
                                {
                                    var temp = Convert.ToString(r["FULL_BACKUP_DAYS"]);
                                    if (!string.IsNullOrEmpty(temp))
                                    {
                                        var days = temp.Split(',');
                                        foreach (var d in days)
                                        {
                                            pnp.FullBackupDays.Add((DayOfWeek)Convert.ToInt32(d));
                                        }
                                    }
                                }
                                if (!IsDBNull(r["FULL_BACKUP_TIME"]))
                                {
                                    pnp.FullBackupTime = DateTime.Parse((string)r["FULL_BACKUP_TIME"], PNStatic.CultureInvariant);
                                }
                                if (!IsDBNull(r["FULL_BACKUP_DATE"]))
                                {
                                    pnp.FullBackupDate = DateTime.Parse((string)r["FULL_BACKUP_DATE"], PNStatic.CultureInvariant);
                                }
                                if (!IsDBNull(r["PROMPT_PASSWORD"]))
                                {
                                    pnp.PromptForPassword = (bool)r["PROMPT_PASSWORD"];
                                }
                            }
                            else
                            {
                                sqlQuery = "INSERT INTO PROTECTION VALUES(NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL)";
                                oData.Execute(sqlQuery);
                            }
                        }
                    }
                    //diary
                    if (!oData.TableExists("DIARY"))
                    {
                        sqlQuery = "CREATE TABLE [DIARY] ([CUSTOM_SETTINGS] BOOLEAN, [ADD_WEEKDAY] BOOLEAN, [FULL_WEEKDAY_NAME] BOOLEAN, [WEEKDAY_AT_THE_END] BOOLEAN, [DO_NOT_SHOW_PREVIOUS] BOOLEAN, [ASC_ORDER] BOOLEAN, [NUMBER_OF_PAGES] INT, [DATE_FORMAT] TEXT)";
                        oData.Execute(sqlQuery);
                        sqlQuery = "INSERT INTO DIARY VALUES(NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL)";
                        oData.Execute(sqlQuery);
                    }
                    else
                    {
                        sqlQuery = "SELECT * FROM DIARY";
                        using (var t = oData.FillDataTable(sqlQuery))
                        {
                            if (t.Rows.Count > 0)
                            {
                                var r = t.Rows[0];
                                var pndr = PNStatic.Settings.Diary;
                                if (!IsDBNull(r["CUSTOM_SETTINGS"]))
                                {
                                    pndr.CustomSettings = (bool)r["CUSTOM_SETTINGS"];
                                }
                                if (!IsDBNull(r["ADD_WEEKDAY"]))
                                {
                                    pndr.AddWeekday = (bool)r["ADD_WEEKDAY"];
                                }
                                if (!IsDBNull(r["FULL_WEEKDAY_NAME"]))
                                {
                                    pndr.FullWeekdayName = (bool)r["FULL_WEEKDAY_NAME"];
                                }
                                if (!IsDBNull(r["WEEKDAY_AT_THE_END"]))
                                {
                                    pndr.WeekdayAtTheEnd = (bool)r["WEEKDAY_AT_THE_END"];
                                }
                                if (!IsDBNull(r["DO_NOT_SHOW_PREVIOUS"]))
                                {
                                    pndr.DoNotShowPrevious = (bool)r["DO_NOT_SHOW_PREVIOUS"];
                                }
                                if (!IsDBNull(r["ASC_ORDER"]))
                                {
                                    pndr.AscendingOrder = (bool)r["ASC_ORDER"];
                                }
                                if (!IsDBNull(r["NUMBER_OF_PAGES"]))
                                {
                                    pndr.NumberOfPages = (int)r["NUMBER_OF_PAGES"];
                                }
                                if (!IsDBNull(r["DATE_FORMAT"]))
                                {
                                    pndr.DateFormat = (string)r["DATE_FORMAT"];
                                }
                            }
                            else
                            {
                                sqlQuery = "INSERT INTO DIARY VALUES(NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL)";
                                oData.Execute(sqlQuery);
                            }
                        }
                    }
                    //network
                    if (!oData.TableExists("NETWORK"))
                    {
                        sqlQuery = "CREATE TABLE [NETWORK] ([INCLUDE_BIN_IN_SYNC] BOOLEAN, [SYNC_ON_START] BOOLEAN, [SAVE_BEFORE_SYNC] BOOLEAN, [ENABLE_EXCHANGE] BOOLEAN, [SAVE_BEFORE_SEND] BOOLEAN, [NO_NOTIFY_ON_ARRIVE] BOOLEAN, [SHOW_RECEIVED_ON_CLICK] BOOLEAN, [SHOW_INCOMING_ON_CLICK] BOOLEAN, [NO_SOUND_ON_ARRIVE] BOOLEAN, [NO_NOTIFY_ON_SEND] BOOLEAN, [SHOW_AFTER_ARRIVE] BOOLEAN, [HIDE_AFTER_SEND] BOOLEAN, [NO_CONTACTS_IN_CONTEXT_MENU] BOOLEAN, [EXCHANGE_PORT] INT, [POST_COUNT] INT, [ALLOW_PING] BOOLEAN, [RECEIVED_ON_TOP] BOOLEAN)";
                        oData.Execute(sqlQuery);
                        sqlQuery = "INSERT INTO NETWORK VALUES(NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL)";
                        oData.Execute(sqlQuery);
                    }
                    else
                    {
                        using (var t = oData.GetSchema("Columns"))
                        {
                            var rows = t.Select("COLUMN_NAME = 'POST_COUNT' AND TABLE_NAME = 'NETWORK'");
                            if (rows.Length == 0)
                            {
                                sqlQuery = "ALTER TABLE NETWORK ADD COLUMN [POST_COUNT] INT";
                                oData.Execute(sqlQuery);
                            }
                            rows = t.Select("COLUMN_NAME = 'ALLOW_PING' AND TABLE_NAME = 'NETWORK'");
                            if (rows.Length == 0)
                            {
                                sqlQuery = "ALTER TABLE NETWORK ADD COLUMN [ALLOW_PING] BOOLEAN";
                                oData.Execute(sqlQuery);
                            }
                            rows = t.Select("COLUMN_NAME = 'RECEIVED_ON_TOP' AND TABLE_NAME = 'NETWORK'");
                            if (rows.Length == 0)
                            {
                                sqlQuery = "ALTER TABLE NETWORK ADD COLUMN [RECEIVED_ON_TOP] BOOLEAN";
                                oData.Execute(sqlQuery);
                            }
                        }
                        sqlQuery = "SELECT * FROM NETWORK";
                        using (var t = oData.FillDataTable(sqlQuery))
                        {
                            if (t.Rows.Count > 0)
                            {
                                var r = t.Rows[0];
                                var pnw = PNStatic.Settings.Network;
                                if (!IsDBNull(r["INCLUDE_BIN_IN_SYNC"]))
                                {
                                    pnw.IncludeBinInSync = (bool)r["INCLUDE_BIN_IN_SYNC"];
                                }
                                if (!IsDBNull(r["SYNC_ON_START"]))
                                {
                                    pnw.SyncOnStart = (bool)r["SYNC_ON_START"];
                                }
                                if (!IsDBNull(r["SAVE_BEFORE_SYNC"]))
                                {
                                    pnw.SaveBeforeSync = (bool)r["SAVE_BEFORE_SYNC"];
                                }
                                if (!IsDBNull(r["ENABLE_EXCHANGE"]))
                                {
                                    pnw.EnableExchange = (bool)r["ENABLE_EXCHANGE"];
                                }
                                if (!IsDBNull(r["SAVE_BEFORE_SEND"]))
                                {
                                    pnw.SaveBeforeSending = (bool)r["SAVE_BEFORE_SEND"];
                                }
                                if (!IsDBNull(r["NO_NOTIFY_ON_ARRIVE"]))
                                {
                                    pnw.NoNotificationOnArrive = (bool)r["NO_NOTIFY_ON_ARRIVE"];
                                }
                                if (!IsDBNull(r["SHOW_RECEIVED_ON_CLICK"]))
                                {
                                    pnw.ShowReceivedOnClick = (bool)r["SHOW_RECEIVED_ON_CLICK"];
                                }
                                if (!IsDBNull(r["SHOW_INCOMING_ON_CLICK"]))
                                {
                                    pnw.ShowIncomingOnClick = (bool)r["SHOW_INCOMING_ON_CLICK"];
                                }
                                if (!IsDBNull(r["NO_SOUND_ON_ARRIVE"]))
                                {
                                    pnw.NoSoundOnArrive = (bool)r["NO_SOUND_ON_ARRIVE"];
                                }
                                if (!IsDBNull(r["NO_NOTIFY_ON_SEND"]))
                                {
                                    pnw.NoNotificationOnSend = (bool)r["NO_NOTIFY_ON_SEND"];
                                }
                                if (!IsDBNull(r["SHOW_AFTER_ARRIVE"]))
                                {
                                    pnw.ShowAfterArrive = (bool)r["SHOW_AFTER_ARRIVE"];
                                }
                                if (!IsDBNull(r["HIDE_AFTER_SEND"]))
                                {
                                    pnw.HideAfterSending = (bool)r["HIDE_AFTER_SEND"];
                                }
                                if (!IsDBNull(r["NO_CONTACTS_IN_CONTEXT_MENU"]))
                                {
                                    pnw.NoContactsInContextMenu = (bool)r["NO_CONTACTS_IN_CONTEXT_MENU"];
                                }
                                if (!IsDBNull(r["EXCHANGE_PORT"]))
                                {
                                    pnw.ExchangePort = (int)r["EXCHANGE_PORT"];
                                }
                                if (!IsDBNull(r["POST_COUNT"]))
                                {
                                    pnw.PostCount = (int)r["POST_COUNT"];
                                }
                                if (!IsDBNull(r["ALLOW_PING"]))
                                {
                                    pnw.AllowPing = (bool)r["ALLOW_PING"];
                                }
                                if (!IsDBNull(r["RECEIVED_ON_TOP"]))
                                {
                                    pnw.ReceivedOnTop = (bool)r["RECEIVED_ON_TOP"];
                                }
                            }
                            else
                            {
                                sqlQuery = "INSERT INTO NETWORK VALUES(NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL)";
                                oData.Execute(sqlQuery);
                            }
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                PNStatic.LogException(ex);
            }
        }
コード例 #9
0
ファイル: PNData.cs プロジェクト: hyrmedia/PNotes.NET
 internal static void SaveGroupChanges(PNGroup group)
 {
     try
     {
         var c = new ColorConverter();
         var dcc = new System.Drawing.ColorConverter();
         var lfc = new LogFontConverter();
         var wfc = new WPFFontConverter();
         var sb = new StringBuilder();
         sb.Append("UPDATE GROUPS SET GROUP_NAME = '");
         sb.Append(group.Name.Replace("'", "''"));
         sb.Append("', PARENT_ID = ");
         sb.Append(group.ParentID);
         sb.Append(", ICON = ");
         if (group.Image != null)
         {
             sb.Append("'");
             if (!group.IsDefaultImage)
             {
                 var base64String = Convert.ToBase64String(group.Image.ToBytes());
                 sb.Append(base64String);
             }
             else
             {
                 sb.Append(group.ImageName);
                 sb.Append(".png");
             }
             //using (var ms = new MemoryStream(1))
             //{
             //    group.Image.Save(ms, System.Drawing.Imaging.ImageFormat.Png);
             //    ms.Position = 0;
             //    string base64String = Convert.ToBase64String(ms.ToArray());
             //    sb.Append(base64String);
             //}
             sb.Append("',");
         }
         else
         {
             sb.Append("NULL,");
         }
         sb.Append(" BACK_COLOR = '");
         sb.Append(c.ConvertToString(null, PNStatic.CultureInvariant, group.Skinless.BackColor));
         sb.Append("', CAPTION_FONT_COLOR = '");
         sb.Append(c.ConvertToString(null, PNStatic.CultureInvariant, group.Skinless.CaptionColor));
         sb.Append("', CAPTION_FONT = '");
         sb.Append(wfc.ConvertToString(group.Skinless.CaptionFont));
         sb.Append("', SKIN_NAME = '");
         sb.Append(group.Skin.SkinName.Replace("'", "''"));
         sb.Append("', FONT_COLOR = '");
         sb.Append(dcc.ConvertToString(null, PNStatic.CultureInvariant, group.FontColor));
         sb.Append("', FONT = '");
         sb.Append(lfc.ConvertToString(group.Font));
         sb.Append("', IS_DEFAULT_IMAGE = ");
         sb.Append(Convert.ToInt32(group.IsDefaultImage));
         sb.Append(" WHERE GROUP_ID = ");
         sb.Append(group.ID);
         ExecuteTransactionForStringBuilder(sb, ConnectionString);
     }
     catch (Exception ex)
     {
         PNStatic.LogException(ex);
     }
 }
コード例 #10
0
ファイル: WndMain.xaml.cs プロジェクト: hyrmedia/PNotes.NET
 private string prepareGroupInsert(int id, int parentID, string name, string imageName)
 {
     try
     {
         var c = new ColorConverter();
         var lfc = new WPFFontConverter();
         var sb = new StringBuilder();
         sb.Append("INSERT INTO GROUPS (GROUP_ID, PARENT_ID, GROUP_NAME, ICON, BACK_COLOR, CAPTION_FONT_COLOR, CAPTION_FONT, SKIN_NAME, PASSWORD_STRING, IS_DEFAULT_IMAGE) VALUES(");
         sb.Append(id);
         sb.Append(",");
         sb.Append(parentID);
         sb.Append(",'");
         sb.Append(name.Replace("'", "''"));
         sb.Append("','");
         //sb.Append(PNStrings.RESOURCE_PREFIX);
         sb.Append(imageName);
         sb.Append("','");
         sb.Append(c.ConvertToString(null, PNStatic.CultureInvariant, PNSkinlessDetails.DefColor));
         sb.Append("','");
         sb.Append(c.ConvertToString(null, PNStatic.CultureInvariant, SystemColors.ControlTextColor));
         sb.Append("','");
         var f = new PNFont { FontWeight = FontWeights.Bold };
         sb.Append(lfc.ConvertToString(f));
         sb.Append("','");
         sb.Append(PNSkinDetails.NO_SKIN);
         sb.Append("','',1");
         sb.Append("); ");
         return sb.ToString();
     }
     catch (Exception ex)
     {
         PNStatic.LogException(ex);
         return "";
     }
 }
コード例 #11
0
ファイル: WndMain.xaml.cs プロジェクト: hyrmedia/PNotes.NET
        private void fillGroup(PNGroup gr, DataRow r)
        {
            try
            {
                var c = new ColorConverter();
                var wfc = new WPFFontConverter();
                var lfc = new LogFontConverter();
                var dcc = new System.Drawing.ColorConverter();

                gr.ID = (int)r["GROUP_ID"];
                gr.ParentID = (int)r["PARENT_ID"];
                gr.Name = (string)r["GROUP_NAME"];
                gr.PasswordString = (string)r["PASSWORD_STRING"];
                gr.IsDefaultImage = (bool)r["IS_DEFAULT_IMAGE"];
                if (!PNData.IsDBNull(r["ICON"]))
                {
                    var base64String = (string)r["ICON"];
                    if (!base64String.EndsWith(PNStrings.PNG_EXT))
                    {
                        try
                        {
                            var buffer = Convert.FromBase64String(base64String);
                            if (gr.ID.In((int)SpecialGroups.AllGroups, 0, (int)SpecialGroups.Diary,
                                (int)SpecialGroups.Backup, (int)SpecialGroups.SearchResults,
                                (int)SpecialGroups.Favorites, (int)SpecialGroups.Incoming, (int)SpecialGroups.Docking,
                                (int)SpecialGroups.RecycleBin) || base64String.StartsWith("resource."))
                            {
                                //possible image data stored as string when data directory just copied into new edition folder
                                upgradeGroupIcon(gr, gr.ID, (string)r["ICON"]);
                            }
                            else
                            {
                                using (var ms = new MemoryStream(buffer))
                                {
                                    ms.Position = 0;
                                    gr.Image = new BitmapImage();
                                    gr.Image.BeginInit();
                                    gr.Image.CacheOption = BitmapCacheOption.OnLoad;
                                    gr.Image.StreamSource = ms;
                                    gr.Image.EndInit();
                                }
                                if (gr.IsDefaultImage)
                                {
                                    gr.IsDefaultImage = false;
                                    var sb = new StringBuilder("UPDATE GROUPS SET IS_DEFAULT_IMAGE = 0");
                                    PNData.ExecuteTransactionForStringBuilder(sb, PNData.ConnectionString);
                                }
                            }
                        }
                        catch (FormatException)
                        {
                            //possible exception when data directory just copied into new edition folder
                            upgradeGroupIcon(gr, gr.ID, (string)r["ICON"]);
                        }
                    }
                    else
                    {
                        gr.Image = TryFindResource(Path.GetFileNameWithoutExtension(base64String)) as BitmapImage;// new BitmapImage(new Uri(base64String));
                    }
                }

                try
                {
                    var clr = c.ConvertFromString(null, PNStatic.CultureInvariant, (string)r["BACK_COLOR"]);
                    if (clr != null)
                        gr.Skinless.BackColor = (Color)clr;
                }
                catch (FormatException)
                {
                    //possible FormatException after synchronization with old database
                    var clr = dcc.ConvertFromString(null, PNStatic.CultureInvariant, (string)r["BACK_COLOR"]);
                    if (clr != null)
                    {
                        var drawingColor = (System.Drawing.Color)clr;
                        gr.Skinless.BackColor = Color.FromArgb(drawingColor.A, drawingColor.R,
                            drawingColor.G, drawingColor.B);
                        var sb = new StringBuilder("UPDATE GROUPS SET BACK_COLOR = '");
                        sb.Append(c.ConvertToString(null, PNStatic.CultureInvariant, gr.Skinless.BackColor));
                        sb.Append("' WHERE GROUP_ID = ");
                        sb.Append(gr.ID);
                        PNData.ExecuteTransactionForStringBuilder(sb, PNData.ConnectionString);
                    }
                }

                try
                {
                    var clr = c.ConvertFromString(null, PNStatic.CultureInvariant, (string)r["CAPTION_FONT_COLOR"]);
                    if (clr != null)
                        gr.Skinless.CaptionColor = (Color)clr;
                }
                catch (FormatException)
                {
                    //possible FormatException after synchronization with old database
                    var clr = dcc.ConvertFromString(null, PNStatic.CultureInvariant, (string)r["CAPTION_FONT_COLOR"]);
                    if (clr != null)
                    {
                        var drawingColor = (System.Drawing.Color)clr;
                        gr.Skinless.CaptionColor = Color.FromArgb(drawingColor.A, drawingColor.R,
                            drawingColor.G, drawingColor.B);
                        var sb = new StringBuilder("UPDATE GROUPS SET CAPTION_FONT_COLOR = '");
                        sb.Append(c.ConvertToString(null, PNStatic.CultureInvariant, gr.Skinless.CaptionColor));
                        sb.Append("' WHERE GROUP_ID = ");
                        sb.Append(gr.ID);
                        PNData.ExecuteTransactionForStringBuilder(sb, PNData.ConnectionString);
                    }
                }

                var fontString = (string)r["CAPTION_FONT"];
                //try
                //{
                var fonts = new InstalledFontCollection();
                var arr = fontString.Split(',');
                if (fontString.Any(ch => ch == '^'))
                {
                    //old format font string
                    var lf = lfc.ConvertFromString(fontString);
                    gr.Skinless.CaptionFont = PNStatic.FromLogFont(lf);
                    var sb = new StringBuilder("UPDATE GROUPS SET CAPTION_FONT = '");
                    sb.Append(wfc.ConvertToString(null, PNStatic.CultureInvariant, gr.Skinless.CaptionFont));
                    sb.Append("' WHERE GROUP_ID = ");
                    sb.Append(gr.ID);
                    PNData.ExecuteTransactionForStringBuilder(sb, PNData.ConnectionString);
                }
                else if (fonts.Families.Any(ff => ff.Name == arr[0]))
                {
                    //normal font string
                    gr.Skinless.CaptionFont = (PNFont)wfc.ConvertFromString(fontString);
                }
                else
                {
                    //possible not existing font name
                    arr[0] = PNStrings.DEF_CAPTION_FONT;
                    fontString = string.Join(",", arr);
                    gr.Skinless.CaptionFont = (PNFont)wfc.ConvertFromString(fontString);
                    var sb = new StringBuilder("UPDATE GROUPS SET CAPTION_FONT = '");
                    sb.Append(fontString);
                    sb.Append("' WHERE GROUP_ID = ");
                    sb.Append(gr.ID);
                    PNData.ExecuteTransactionForStringBuilder(sb, PNData.ConnectionString);
                }
                //}
                //catch (IndexOutOfRangeException)
                //{
                //    //possible IndexOutOfRangeException after synchronization with old database
                //    var lf = lfc.ConvertFromString(fontString);
                //    gr.Skinless.CaptionFont = PNStatic.FromLogFont(lf);
                //    var sb = new StringBuilder("UPDATE GROUPS SET CAPTION_FONT = '");
                //    sb.Append(wfc.ConvertToString(null, PNStatic.CultureInvariant, gr.Skinless.CaptionFont));
                //    sb.Append("' WHERE GROUP_ID = ");
                //    sb.Append(gr.ID);
                //    PNData.ExecuteTransactionForStringBuilder(sb, PNData.ConnectionString);
                //}

                var skinName = (string)r["SKIN_NAME"];
                if (skinName != PNSkinDetails.NO_SKIN)
                {
                    gr.Skin.SkinName = skinName;
                    //load skin
                    var path = Path.Combine(PNPaths.Instance.SkinsDir, gr.Skin.SkinName) + ".pnskn";
                    if (File.Exists(path))
                    {
                        PNSkinsOperations.LoadSkin(path, gr.Skin);
                    }
                }
                if (!PNData.IsDBNull(r["FONT"]))
                {
                    gr.Font = lfc.ConvertFromString((string)r["FONT"]);
                }
                if (!PNData.IsDBNull(r["FONT_COLOR"]))
                {
                    var clr = dcc.ConvertFromString(null, PNStatic.CultureInvariant, (string)r["FONT_COLOR"]);
                    if (clr != null)
                        gr.FontColor = (System.Drawing.Color)clr;
                }
            }
            catch (Exception ex)
            {
                PNStatic.LogException(ex);
            }
        }
コード例 #12
0
ファイル: PNConverters.cs プロジェクト: hyrmedia/PNotes.NET
        public override object ConvertTo(ITypeDescriptorContext context, CultureInfo culture, object value, Type destinationType)
        {
            if (destinationType != typeof (string)) return base.ConvertTo(context, culture, value, destinationType);
            var skl = value as PNSkinlessDetails;
            if (skl == null) return "";
            var cr = new ColorConverter();
            var lfc = new WPFFontConverter();

            var sb = new StringBuilder();
            sb.Append(cr.ConvertToString(null, PNStatic.CultureInvariant, skl.BackColor));
            sb.Append(DEL_INNER);
            sb.Append(cr.ConvertToString(null, PNStatic.CultureInvariant, skl.CaptionColor));
            sb.Append(DEL_INNER);
            sb.Append(lfc.ConvertToString(skl.CaptionFont));
            return sb.ToString();
        }