C# SQLiteCommand.ExecuteNonQuery方法代码示例

        /// <summary>
        ///   Removes the old entries (entries that have been in the database more than the specified time) from the database.
        /// </summary>
        /// <param name="olderThan"> The number of days in the database after which the entry is considered old... </param>
        public static void CleanNewsOlderThan(int olderThan)
        {
            try
            {
                using (SQLiteConnection sqLiteConnection = new SQLiteConnection(ConnectionString))
                {
                    sqLiteConnection.Open();

                    DateTime olderDate = DateTime.Now;
                    TimeSpan daySpan = new TimeSpan(olderThan, 0, 0, 0);
                    olderDate = olderDate.Subtract(daySpan);

                    SQLiteCommand sqLiteCommand = new SQLiteCommand(sqLiteConnection)
                                                      {
                                                          CommandText = "DELETE " +
                                                                        "FROM NEWS_STORAGE " +
                                                                        "WHERE NEWSITEM_AQUISITION_DATE <= ?"
                                                      };
                    sqLiteCommand.Parameters.AddWithValue(null, olderDate);
                    sqLiteCommand.ExecuteNonQuery();

                    sqLiteConnection.Close();
                }
            }
            catch (Exception ex)
            {
                ErrorMessageBox.Show(ex.Message, ex.ToString());
                Logger.ErrorLogger("error.txt", ex.ToString());
            }
        }

        public void NewConversation(string s, string r, string m)
        {
            SQLiteConnection dbConnection = new SQLiteConnection("Data Source=db.sqlite;Version=3;");
            dbConnection.Open();

            string sql = "insert into conversations default values";

            SQLiteCommand command = new SQLiteCommand(sql, dbConnection);
            command.ExecuteNonQuery();

            string sqlId = "select last_insert_rowid() as id";
            command.CommandText = sqlId;
            command.ExecuteNonQuery();

            SQLiteDataReader reader = command.ExecuteReader();
            reader.Read();
            string id = reader["id"].ToString();
            reader.Close();

            string sql2 = "insert into messages (sender, recipient, message, conversationid) values (@s, @r, @m, @id)";
            command.Parameters.Add(new SQLiteParameter("@s", s));
            command.Parameters.Add(new SQLiteParameter("@r", r));
            command.Parameters.Add(new SQLiteParameter("@m", m));
            command.Parameters.Add(new SQLiteParameter("@id", id));
            command.CommandText = sql2;
            command.ExecuteNonQuery();

            string sql3 = "insert into participants (participant, conversationid) values (@s, @id), (@r, @id)";
            command.Parameters.Add(new SQLiteParameter("@s", s));
            command.Parameters.Add(new SQLiteParameter("@id", id));
            command.CommandText = sql3;
            command.ExecuteNonQuery();

            dbConnection.Close();
        }

        public void AddMovie(MovieFinder.Data.Movie movie)
        {
            using (var cmd = new SQLiteCommand(connection))
            {

                cmd.CommandText = String.Format("INSERT INTO MOVIE(ID,Name,ImageUrl,ReleaseDate,LanguageCode,Description, CreatedDate,ModifiedDate, Version, UniqueID, HasSubtitle) " +
                    "VALUES({0},'{1}','{2}','{3}','{4}','{5}','{6}','{7}',{8},'{9}',{10})",
                    movie.ID, Sanitize(movie.Name), movie.ImageUrl, movie.ReleaseDate.ToString("yyyy-MM-dd"),
                    movie.LanguageCode,Sanitize( movie.Description),
                    movie.CreateDate.ToString("yyyy-MM-dd"),
                    movie.ModifiedDate != null ?
                    movie.ModifiedDate.Value.ToString("yyyy-MM-dd") : null, movie.Version,
                    movie.UniqueID, movie.MovieLinks.Any(x => x.HasSubtitle) ? 1 : 0);
                cmd.ExecuteNonQuery();

                foreach (var link in movie.MovieLinks)
                {
                    if (link.FailedAttempts > 3)
                        continue;
                    cmd.CommandText = String.Format("INSERT INTO MOVIELINK(ID,MovieID,LinkTitle,PageUrl,PageSiteID,DownloadUrl,DownloadSiteID,Version, HasSubtitle) " +
                    "VALUES({0},{1},'{2}','{3}','{4}','{5}','{6}',{7},{8})",
                    link.ID, link.MovieID, Sanitize(link.LinkTitle), link.PageUrl, link.PageSiteID, link.DowloadUrl, link.DownloadSiteID,
                    link.Version, link.HasSubtitle ? 1 : 0);
                    cmd.ExecuteNonQuery();
                }
            }
        }

        public static int SqlNonQueryText(SQLiteConnection cn, SQLiteCommand cmd)
        {
            int rows = 0;

            //LogLine("SqlNonQueryText: " + cmd.CommandText);

            try
            {
                if (cn.State == ConnectionState.Open)
                {
                    rows = cmd.ExecuteNonQuery();
                }
                else
                {
                    cn.Open();
                    rows = cmd.ExecuteNonQuery();
                    cn.Close();
                }
                return rows;
            }
            catch (Exception ex)
            {
                System.Diagnostics.Debug.WriteLine("SqlNonQueryText Exception: " + ex.Message);
                throw;
            }
        }

        public Database()
        {
            if (!File.Exists("Settings/" + DatabaseName)) {
                // -- We need to create the PlayerDB.
                SQLiteConnection.CreateFile(Path.GetFullPath("Settings/" + DatabaseName));

                // -- Now we need to connect and create the table.
                lock (_dbLock) {
                    var connection = new SQLiteConnection("Data Source=" + Path.GetFullPath("Settings/" + DatabaseName));
                    connection.Open();

                    var command = new SQLiteCommand("CREATE TABLE PlayerDB (Number INTEGER PRIMARY KEY, Name TEXT UNIQUE, Rank TEXT, RankStep TEXT, BoundBlock INTEGER, RankChangedBy TEXT, LoginCounter INTEGER, KickCounter INTEGER, Ontime INTEGER, LastOnline INTEGER, IP TEXT, Stopped INTEGER, StoppedBy TEXT, Banned INTEGER, Vanished INTEGER, BannedBy STRING, BannedUntil INTEGER, Global INTEGER, Time_Muted INTEGER, BanMessage TEXT, KickMessage TEXT, MuteMessage TEXT, RankMessage TEXT, StopMessage TEXT)", connection);
                    command.ExecuteNonQuery();

                    command.CommandText = "CREATE INDEX PlayerDB_Index ON PlayerDB (Name COLLATE NOCASE)";
                    command.ExecuteNonQuery();

                    command.CommandText = "CREATE TABLE IPBanDB (Number INTEGER PRIMARY KEY, IP TEXT UNIQUE, Reason TEXT, BannedBy TEXT)";
                    command.ExecuteNonQuery();

                    DBConnection = connection; // -- All done.
                }
            } else {
                DBConnection = new SQLiteConnection("Data Source=" + Path.GetFullPath("Settings/" + DatabaseName));
                DBConnection.Open();
            }
        }

 public void ReCreateTable()
 {
     SQLiteCommand command = new SQLiteCommand("DROP TABLE info;", SQLiteConnector.getInstance());
     command.ExecuteNonQuery();
     command.CommandText = "CREATE TABLE info ([id] INTEGER PRIMARY KEY AUTOINCREMENT,[mac] VARCHAR(200),[date] DATETIME,[os] INTEGER);";
     command.ExecuteNonQuery();
 }

        private void vCreateDB()
        {
            if (Directory.Exists("ServerData")) Directory.Delete("ServerData", true);
            Directory.CreateDirectory("ServerData");

            File.WriteAllText("ServerData\\test.xml", "<Test><localtest/></Test>");

            SQLiteConnection.CreateFile("ServerData\\PersonaData.db");
            SQLiteConnection.CreateFile("ServerData\\GarageData.db");

            SQLiteConnection m_dbConnection;

            m_dbConnection = new SQLiteConnection("Data Source=\"ServerData\\PersonaData.db\";Version=3;");
            m_dbConnection.Open();
            string sql = "create table personas (Id bigint, IconIndex smallint, Name varchar(14), Motto varchar(30), Level smallint, IGC int, Boost int, ReputationPercentage smallint, LevelReputation int, TotalReputation int)";
            SQLiteCommand command = new SQLiteCommand(sql, m_dbConnection);
            command.ExecuteNonQuery();
            sql = "insert into personas (Id, IconIndex, Name, Motto, Level, IGC, Boost, ReputationPercentage, LevelReputation, TotalReputation) values (0, 27, 'Debug', 'Test Build', 69, 0, 0, 0, 0, 699)";
            command = new SQLiteCommand(sql, m_dbConnection);
            command.ExecuteNonQuery();
            sql = "insert into personas (Id, IconIndex, Name, Motto, Level, IGC, Boost, ReputationPercentage, LevelReputation, TotalReputation) values (1, 26, 'DefaultProfile', 'Literally, the first.', 1, 25000, 1500, 0, 0, 0)";
            command = new SQLiteCommand(sql, m_dbConnection);
            command.ExecuteNonQuery();
            m_dbConnection.Close();
            m_dbConnection = new SQLiteConnection("Data Source=\"ServerData\\GarageData.db\";Version=3;");
            m_dbConnection.Open();
            sql = "create table Id0 (BaseCarId bigint, RaceClass int, ApId bigint, Paints longtext, PerformanceParts longtext, PhysicsProfileHash bigint, Rating int, ResalePrice int, SkillModParts longtext, Vinyls longtext, VisualParts longtext, Durability smallint, ExpirationDate text, HeatLevel smallint, Id int)";
            command = new SQLiteCommand(sql, m_dbConnection);
            command.ExecuteNonQuery();
            sql = "insert into Id0 (BaseCarId, RaceClass, ApId, Paints, PerformanceParts, PhysicsProfileHash, Rating, ResalePrice, SkillModParts, Vinyls, VisualParts, Durability, ExpirationDate, HeatLevel, Id) values (1816139026, -405837480, 1, "+
                "'<Paints><CustomPaintTrans><Group>-1480403439</Group><Hue>496032624</Hue><Sat>0</Sat><Slot>1</Slot><Var>76</Var></CustomPaintTrans><CustomPaintTrans><Group>-1480403439</Group><Hue>496032624</Hue><Sat>0</Sat><Slot>2</Slot><Var>76</Var></CustomPaintTrans><CustomPaintTrans><Group>595033610</Group><Hue>496032624</Hue><Sat>0</Sat><Slot>6</Slot><Var>254</Var></CustomPaintTrans><CustomPaintTrans><Group>595033610</Group><Hue>496032624</Hue><Sat>0</Sat><Slot>0</Slot><Var>254</Var></CustomPaintTrans><CustomPaintTrans><Group>595033610</Group><Hue>496032624</Hue><Sat>0</Sat><Slot>3</Slot><Var>254</Var></CustomPaintTrans><CustomPaintTrans><Group>595033610</Group><Hue>496032624</Hue><Sat>0</Sat><Slot>4</Slot><Var>254</Var></CustomPaintTrans><CustomPaintTrans><Group>595033610</Group><Hue>496032624</Hue><Sat>0</Sat><Slot>5</Slot><Var>254</Var></CustomPaintTrans></Paints>', "+
                "'<PerformanceParts><PerformancePartTrans><PerformancePartAttribHash>-1962598619</PerformancePartAttribHash></PerformancePartTrans><PerformancePartTrans><PerformancePartAttribHash>-183076819</PerformancePartAttribHash></PerformancePartTrans><PerformancePartTrans><PerformancePartAttribHash>7155944</PerformancePartAttribHash></PerformancePartTrans><PerformancePartTrans><PerformancePartAttribHash>754340312</PerformancePartAttribHash></PerformancePartTrans><PerformancePartTrans><PerformancePartAttribHash>1621862030</PerformancePartAttribHash></PerformancePartTrans><PerformancePartTrans><PerformancePartAttribHash>1727386028</PerformancePartAttribHash></PerformancePartTrans></PerformanceParts>', "+
                "-846723009, 708, 350000, "+
                "'<SkillModParts><SkillModPartTrans><IsFixed>false</IsFixed><SkillModPartAttribHash>-1196331958</SkillModPartAttribHash></SkillModPartTrans><SkillModPartTrans><IsFixed>false</IsFixed><SkillModPartAttribHash>-1012293684</SkillModPartAttribHash></SkillModPartTrans><SkillModPartTrans><IsFixed>false</IsFixed><SkillModPartAttribHash>-577002039</SkillModPartAttribHash></SkillModPartTrans><SkillModPartTrans><IsFixed>false</IsFixed><SkillModPartAttribHash>861531645</SkillModPartAttribHash></SkillModPartTrans><SkillModPartTrans><IsFixed>false</IsFixed><SkillModPartAttribHash>917249206</SkillModPartAttribHash></SkillModPartTrans></SkillModParts>', "+
                "'<Vinyls><CustomVinylTrans><Hash>-883491363</Hash><Hue1>-799662319</Hue1><Hue2>-799662186</Hue2><Hue3>-799662452</Hue3><Hue4>-799662452</Hue4><Layer>0</Layer><Mir>true</Mir><Rot>128</Rot><Sat1>0</Sat1><Sat2>0</Sat2><Sat3>0</Sat3><Sat4>0</Sat4><ScaleX>7162</ScaleX><ScaleY>11595</ScaleY><Shear>0</Shear><TranX>2</TranX><TranY>327</TranY><Var1>204</Var1><Var2>0</Var2><Var3>0</Var3><Var4>0</Var4></CustomVinylTrans><CustomVinylTrans><Hash>-1282944374</Hash><Hue1>-799662156</Hue1><Hue2>-799662354</Hue2><Hue3>-799662385</Hue3><Hue4>-799662385</Hue4><Layer>1</Layer><Mir>true</Mir><Rot>60</Rot><Sat1>0</Sat1><Sat2>0</Sat2><Sat3>0</Sat3><Sat4>0</Sat4><ScaleX>735</ScaleX><ScaleY>1063</ScaleY><Shear>0</Shear><TranX>-52</TranX><TranY>268</TranY><Var1>255</Var1><Var2>0</Var2><Var3>0</Var3><Var4>0</Var4></CustomVinylTrans></Vinyls>', "+
                "'<VisualParts><VisualPartTrans><PartHash>-541305606</PartHash><SlotHash>1694991</SlotHash></VisualPartTrans><VisualPartTrans><PartHash>-273819714</PartHash><SlotHash>-2126743923</SlotHash></VisualPartTrans><VisualPartTrans><PartHash>-48607787</PartHash><SlotHash>453545749</SlotHash></VisualPartTrans><VisualPartTrans><PartHash>948331475</PartHash><SlotHash>2106784967</SlotHash></VisualPartTrans></VisualParts>', "+
                "100, '2016-01-30T17:30:00.0000000+00:00', 1, 1)";
            command = new SQLiteCommand(sql, m_dbConnection);
            command.ExecuteNonQuery();
            command.ExecuteNonQuery();
            command.ExecuteNonQuery(); // 3 cars
            sql = "create table Id1 (BaseCarId bigint, RaceClass int, ApId bigint, Paints longtext, PerformanceParts longtext, PhysicsProfileHash bigint, Rating int, ResalePrice int, SkillModParts longtext, Vinyls longtext, VisualParts longtext, Durability smallint, ExpirationDate text, HeatLevel smallint, Id int)";
            command = new SQLiteCommand(sql, m_dbConnection);
            command.ExecuteNonQuery();
            sql = "insert into Id1 (BaseCarId, RaceClass, ApId, Paints, PerformanceParts, PhysicsProfileHash, Rating, ResalePrice, SkillModParts, Vinyls, VisualParts, Durability, ExpirationDate, HeatLevel, Id) values (1816139026, -405837480, 2, " +
                "'<Paints><CustomPaintTrans><Group>-1480403439</Group><Hue>496032624</Hue><Sat>0</Sat><Slot>1</Slot><Var>76</Var></CustomPaintTrans><CustomPaintTrans><Group>-1480403439</Group><Hue>496032624</Hue><Sat>0</Sat><Slot>2</Slot><Var>76</Var></CustomPaintTrans><CustomPaintTrans><Group>595033610</Group><Hue>496032624</Hue><Sat>0</Sat><Slot>6</Slot><Var>254</Var></CustomPaintTrans><CustomPaintTrans><Group>595033610</Group><Hue>496032624</Hue><Sat>0</Sat><Slot>0</Slot><Var>254</Var></CustomPaintTrans><CustomPaintTrans><Group>595033610</Group><Hue>496032624</Hue><Sat>0</Sat><Slot>3</Slot><Var>254</Var></CustomPaintTrans><CustomPaintTrans><Group>595033610</Group><Hue>496032624</Hue><Sat>0</Sat><Slot>4</Slot><Var>254</Var></CustomPaintTrans><CustomPaintTrans><Group>595033610</Group><Hue>496032624</Hue><Sat>0</Sat><Slot>5</Slot><Var>254</Var></CustomPaintTrans></Paints>', " +
                "'<PerformanceParts><PerformancePartTrans><PerformancePartAttribHash>-1962598619</PerformancePartAttribHash></PerformancePartTrans><PerformancePartTrans><PerformancePartAttribHash>-183076819</PerformancePartAttribHash></PerformancePartTrans><PerformancePartTrans><PerformancePartAttribHash>7155944</PerformancePartAttribHash></PerformancePartTrans><PerformancePartTrans><PerformancePartAttribHash>754340312</PerformancePartAttribHash></PerformancePartTrans><PerformancePartTrans><PerformancePartAttribHash>1621862030</PerformancePartAttribHash></PerformancePartTrans><PerformancePartTrans><PerformancePartAttribHash>1727386028</PerformancePartAttribHash></PerformancePartTrans></PerformanceParts>', " +
                "-846723009, 708, 350000, " +
                "'<SkillModParts><SkillModPartTrans><IsFixed>false</IsFixed><SkillModPartAttribHash>-1196331958</SkillModPartAttribHash></SkillModPartTrans><SkillModPartTrans><IsFixed>false</IsFixed><SkillModPartAttribHash>-1012293684</SkillModPartAttribHash></SkillModPartTrans><SkillModPartTrans><IsFixed>false</IsFixed><SkillModPartAttribHash>-577002039</SkillModPartAttribHash></SkillModPartTrans><SkillModPartTrans><IsFixed>false</IsFixed><SkillModPartAttribHash>861531645</SkillModPartAttribHash></SkillModPartTrans><SkillModPartTrans><IsFixed>false</IsFixed><SkillModPartAttribHash>917249206</SkillModPartAttribHash></SkillModPartTrans></SkillModParts>', " +
                "'<Vinyls><CustomVinylTrans><Hash>-883491363</Hash><Hue1>-799662319</Hue1><Hue2>-799662186</Hue2><Hue3>-799662452</Hue3><Hue4>-799662452</Hue4><Layer>0</Layer><Mir>true</Mir><Rot>128</Rot><Sat1>0</Sat1><Sat2>0</Sat2><Sat3>0</Sat3><Sat4>0</Sat4><ScaleX>7162</ScaleX><ScaleY>11595</ScaleY><Shear>0</Shear><TranX>2</TranX><TranY>327</TranY><Var1>204</Var1><Var2>0</Var2><Var3>0</Var3><Var4>0</Var4></CustomVinylTrans><CustomVinylTrans><Hash>-1282944374</Hash><Hue1>-799662156</Hue1><Hue2>-799662354</Hue2><Hue3>-799662385</Hue3><Hue4>-799662385</Hue4><Layer>1</Layer><Mir>true</Mir><Rot>60</Rot><Sat1>0</Sat1><Sat2>0</Sat2><Sat3>0</Sat3><Sat4>0</Sat4><ScaleX>735</ScaleX><ScaleY>1063</ScaleY><Shear>0</Shear><TranX>-52</TranX><TranY>268</TranY><Var1>255</Var1><Var2>0</Var2><Var3>0</Var3><Var4>0</Var4></CustomVinylTrans></Vinyls>', " +
                "'<VisualParts><VisualPartTrans><PartHash>-541305606</PartHash><SlotHash>1694991</SlotHash></VisualPartTrans><VisualPartTrans><PartHash>-273819714</PartHash><SlotHash>-2126743923</SlotHash></VisualPartTrans><VisualPartTrans><PartHash>-48607787</PartHash><SlotHash>453545749</SlotHash></VisualPartTrans><VisualPartTrans><PartHash>948331475</PartHash><SlotHash>2106784967</SlotHash></VisualPartTrans></VisualParts>', " +
                "100, 'null', 1, 2)";
            command = new SQLiteCommand(sql, m_dbConnection);
            command.ExecuteNonQuery();
            m_dbConnection.Close();
            m_dbConnection.Dispose();
        }

        public PersonFile()
        {
            OleDbDataReader oledbReader;
            oledbReader = base.GetOleDbDataReader("*_$.dbf");

            using (var conn = new SQLiteConnection(ConfigurationManager.ConnectionStrings["TMG.DataExtractor.Properties.Settings.tmgConnectionString"].ToString()))
            {
                conn.Open();

                using (var cmd = new SQLiteCommand(conn))
                {
                    using (var transaction = conn.BeginTransaction())
                    {
                        cmd.CommandText = "DELETE FROM Person;";
                        cmd.ExecuteNonQuery();

                        foreach (DbDataRecord row in oledbReader)
                        {
                            string sql =	"INSERT INTO Person (";
                            sql += "PER_NO, FATHER, MOTHER, LAST_EDIT, DSID, REF_ID, REFERENCE, SPOULAST, SCBUFF, PBIRTH, PDEATH, SEX, LIVING, ";
                            sql += "BIRTHORDER, MULTIBIRTH, ADOPTED, ANCE_INT, DESC_INT, RELATE, RELATEFO, TT, FLAG1) VALUES (";
                            sql += string.Format("{0},{1},{2},'{3}',{4},{5},'{6}',{7},'{8}','{9}','{10}','{11}','{12}','{13}','{14}','{15}','{16}','{17}',{18},{19},'{20}','{21}');",
                            (int)row["PER_NO"],
                            (int)row["FATHER"],
                            (int)row["MOTHER"],
                            (DateTime)row["LAST_EDIT"],
                            (int)row["DSID"],
                            (int)row["REF_ID"],
                            row["REFERENCE"].ToString().Replace("'", "`"),
                            (int)row["SPOULAST"],
                            row["SCBUFF"].ToString().Replace("'", "`"),
                            row["PBIRTH"].ToString().Replace("'", "`"),
                            row["PDEATH"].ToString().Replace("'", "`"),
                            row["SEX"].ToString().Replace("'", "`"),
                            row["LIVING"].ToString().Replace("'", "`"),
                            row["BIRTHORDER"].ToString().Replace("'", "`"),
                            row["ADOPTED"].ToString().Replace("'", "`"),
                            row["MULTIBIRTH"].ToString().Replace("'", "`"),
                            row["ANCE_INT"].ToString().Replace("'", "`"),
                            row["DESC_INT"].ToString().Replace("'", "`"),
                            (int)row["RELATE"],
                            (int)row["RELATEFO"],
                            row["TT"].ToString(),
                            row["FLAG1"].ToString());
                            //TODO: Need to add the dynamically generated flag columns

                            cmd.CommandText = sql;
                            cmd.ExecuteNonQuery();
                            Tracer("People Added: {0} {1}%");
                        }
                        transaction.Commit();
                    }
                }
                conn.Close();
            }
        }

        public ResearchLogFile()
        {
            OleDbDataReader oledbReader;
            oledbReader = base.GetOleDbDataReader("*_l.dbf");

            using (var conn = new SQLiteConnection(ConfigurationManager.ConnectionStrings["TMG.DataExtractor.Properties.Settings.tmgConnectionString"].ToString()))
            {
                conn.Open();

                using (var cmd = new SQLiteCommand(conn))
                {
                    using (var transaction = conn.BeginTransaction())
                    {
                        cmd.CommandText = "DELETE FROM ResearchLog;";
                        cmd.ExecuteNonQuery();

                        foreach (DbDataRecord row in oledbReader)
                        {
                            string sql = "INSERT INTO ResearchLog (RLTYPE,RLNUM,RLPER1,RLPER2,RLGTYPE,TASK,RLEDITED,DESIGNED,BEGUN,PROGRESS,COMPLETED,PLANNED,";
                            sql += "EXPENSES,COMMENTS,RLNOTE,KEYWORDS,DSID,ID_PERSON,ID_EVENT,ID_SOURCE,ID_REPOS,TT,REFERENCE) ";
                            sql += string.Format("VALUES ('{0}',{1},{2},{3},{4},'{5}','{6}','{7}','{8}','{9}','{10}','{11}',{12},'{13}','{14}','{15}',{16},{17},{18},{19},{20},'{21}','{22}');",
                                    row["RLTYPE"].ToString().Replace("'","`"),
                                    (int)row["RLNUM"],
                                    (int)row["RLPER1"],
                                    (int)row["RLPER2"],
                                    (int)row["RLGTYPE"],
                                    row["TASK"].ToString().Replace("'","`"),
                                    row["RLEDITED"].ToString().Replace("'","`"),
                                    row["DESIGNED"].ToString().Replace("'","`"),
                                    row["BEGUN"].ToString().Replace("'","`"),
                                    row["PROGRESS"].ToString().Replace("'","`"),
                                    row["COMPLETED"].ToString().Replace("'","`"),
                                    row["PLANNED"].ToString().Replace("'","`"),
                                    (decimal)row["EXPENSES"],
                                    row["COMMENTS"].ToString().Replace("'","`"),
                                    row["RLNOTE"].ToString().Replace("'","`"),
                                    row["KEYWORDS"].ToString().Replace("'","`"),
                                    (int)row["DSID"],
                                    (int)row["ID_PERSON"],
                                    (int)row["ID_EVENT"],
                                    (int)row["ID_SOURCE"],
                                    (int)row["ID_REPOS"],
                                    row["TT"].ToString(),
                                    row["REFERENCE"].ToString().Replace("'","`")
                            );

                            cmd.CommandText = sql;
                            cmd.ExecuteNonQuery();
                            Tracer("Research Logs: {0} {1}%");
                        }
                        transaction.Commit();
                    }
                }
                conn.Close();
            }
        }

        public static void Create()
        {
            string appDataFolder = Environment.GetFolderPath(Environment.SpecialFolder.ApplicationData);
            string dataFolder = Path.Combine(appDataFolder, "Pedal Builder");

            try
            {
                if (!Directory.Exists(dataFolder))
                {
                    Directory.CreateDirectory(dataFolder);
                }

                if (!File.Exists(Path.Combine(dataFolder, "Pedals.sqlite")))
                {
                    SQLiteConnection.CreateFile(Path.Combine(dataFolder, "Pedals.sqlite"));
                }

                SQLiteConnection con = new SQLiteConnection(@"Data Source=" + dataFolder + "/Pedals.sqlite;Version=3");
                con.Open();

                string pedalSql = "CREATE TABLE IF NOT EXISTS pedals (" +
                                    "id INTEGER PRIMARY KEY AUTOINCREMENT," +
                                    "name TEXT NOT NULL," +
                                    "builds INTEGER," +
                                    "notes TEXT)";

                string componentSql = "CREATE TABLE IF NOT EXISTS components (" +
                                        "id INTEGER PRIMARY KEY AUTOINCREMENT," +
                                        "type TEXT," +
                                        "value TEXT," +
                                        "notes TEXT," +
                                        "url TEXT," +
                                        "price REAL)";

            string partListSql = "CREATE TABLE IF NOT EXISTS partlist (" +
                                    "id INTEGER PRIMARY KEY AUTOINCREMENT," +
                                    "partname TEXT NOT NULL," +
                                    "component_id INTEGER NOT NULL," +
                                    "pedal_id INTEGER NOT NULL)";

                SQLiteCommand cmd = new SQLiteCommand(pedalSql, con);
                cmd.ExecuteNonQuery();

                cmd.CommandText = componentSql;
                cmd.ExecuteNonQuery();

                cmd.CommandText = partListSql;
                cmd.ExecuteNonQuery();

                con.Close();
            }
            catch (SQLiteException ex)
            {
                MessageBox.Show(ex.ToString());
            }
        }

        protected void newClipBoardData()
        {
            if (db.State == System.Data.ConnectionState.Open)
            {
                //insert capture record
                SQLiteCommand insertCmd = new SQLiteCommand(db);
                insertCmd.CommandText = @"INSERT INTO capture (timestamp) VALUES('"+DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")+"')";
                int rowsUpdated = insertCmd.ExecuteNonQuery();

                insertCmd.CommandText = @"SELECT last_insert_rowid()"; //get id from capture record to use as foreign key
                long captureID = (long) insertCmd.ExecuteScalar();

                IDataObject clipData = Clipboard.GetDataObject();
                string[] formats = clipData.GetFormats();
                if (clipData.GetData(DataFormats.Html) != null)
                {
                    string html = (string)Clipboard.GetData(@"text\HTML");
                    if (html != null)
                    {
                        insertCmd.CommandText = @"INSERT INTO htmlData (captureID, text) VALUES('" + captureID + "', (@html))";
                        insertCmd.Parameters.Add("@html", DbType.String, html.Length).Value = html;
                        insertCmd.ExecuteNonQuery();
                    }
                }
                if (Clipboard.ContainsText())//insert text data
                {
                    insertCmd.CommandText = @"INSERT INTO textData (captureID, text) VALUES('" + captureID + "', '" + Clipboard.GetText().Replace("'", "''") + "')";
                    rowsUpdated = insertCmd.ExecuteNonQuery();
                }
                if (Clipboard.ContainsImage())
                {
                    System.IO.MemoryStream memStream = new System.IO.MemoryStream() ;
                    Clipboard.GetImage().Save(memStream, System.Drawing.Imaging.ImageFormat.Jpeg); //save image into stream
                    byte[] imgData = new byte[memStream.Length];
                    memStream.Seek(0, SeekOrigin.Begin);
                    memStream.Read(imgData, 0, (int) memStream.Length); //write stream onto imgData
                    //insertCmd.CommandText = @"INSERT INTO imageData (captureID, image) VALUES('" +captureID + "', '";// + imgData + "')";
                    insertCmd.CommandText = @"INSERT INTO imageData (captureID, image) VALUES('" +captureID + "', (@image))";

                    //Writes to file for testing
                    //FileStream fs = File.OpenWrite("toSQL.jpg");
                    //fs.Write(imgData, 0, imgData.Length);
                    //fs.Close();
                    //

                    //for (int i = 0; i < imgData.Length; i++)
                    //    insertCmd.CommandText += imgData[i]; //adds image data to command
                    insertCmd.Parameters.Add("@image", DbType.Binary, imgData.Length).Value = imgData;
                    //insertCmd.CommandText += "')";
                    rowsUpdated = insertCmd.ExecuteNonQuery();
                }
            }
            populateTreeMenu();
        }

        public Storage()
        {
            string datasource = "map.db";

            if (!System.IO.File.Exists(datasource))
                SQLiteConnection.CreateFile(datasource);

            conn = new SQLiteConnection();
            conStr = new SQLiteConnectionStringBuilder();

            conStr.DataSource = datasource;

            conn.ConnectionString = conStr.ToString();

            // open connection;
            conn.Open();

            SQLiteCommand cmd = new SQLiteCommand();
            string sql = string.Empty;
            cmd.Connection = conn;

            sql = "drop table if exists label;";
            cmd.CommandText = sql;
            cmd.ExecuteNonQuery();

            sql = "drop table if exists path;";
            cmd.CommandText = sql;
            cmd.ExecuteNonQuery();

            sql = "drop table if exists quadtree;";
            cmd.CommandText = sql;
            cmd.ExecuteNonQuery();

            sql = "create table label (id INTEGER, data BLOB);";
            cmd.CommandText = sql;
            cmd.ExecuteNonQuery();

            sql = "create table path (id INTEGER, data BLOB);";
            cmd.CommandText = sql;
            cmd.ExecuteNonQuery();

            sql = "create table quadtree (quadkey TEXT, data BLOB);";
            cmd.CommandText = sql;
            cmd.ExecuteNonQuery();

            sql = "create index label_id ON label(id);";
            cmd.CommandText = sql;
            cmd.ExecuteNonQuery();

            sql = "create index path_id ON path(id);";
            cmd.CommandText = sql;
            cmd.ExecuteNonQuery();

            sql = "create index quadtree_quadkey ON quadtree (quadkey);";
            cmd.CommandText = sql;
            cmd.ExecuteNonQuery();
        }

        /// <summary>     
        /// 创建SQLite数据库文件     
        /// </summary>     
        /// <param name="dbPath">要创建的SQLite数据库文件路径</param>     
        public static void CreateDB(string dbPath)
        {
            using (SQLiteConnection connection = new SQLiteConnection("Data Source=" + dbPath)) {
                    connection.Open();
                    using (SQLiteCommand command = new SQLiteCommand(connection)) {
                        command.CommandText = "CREATE TABLE Demo(id integer NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE)";
                        command.ExecuteNonQuery();

                        command.CommandText = "DROP TABLE Demo";
                        command.ExecuteNonQuery();
                    }
                }
        }

        public EventFile()
        {
            OleDbDataReader oledbReader;
            oledbReader = GetOleDbDataReader("*_g.dbf");

            using (var conn = new SQLiteConnection(ConfigurationManager.ConnectionStrings["TMG.DataExtractor.Properties.Settings.tmgConnectionString"].ToString()))
            {
                conn.Open();

                using (var cmd = new SQLiteCommand(conn))
                {
                    using (var transaction = conn.BeginTransaction())
                    {
                        cmd.CommandText = "DELETE FROM Event;";
                        cmd.ExecuteNonQuery();

                        foreach (DbDataRecord row in oledbReader)
                        {
                            string sql = "INSERT INTO Event (ETYPE,DSID,PER1SHOW,PER2SHOW,PER1,PER2,EDATE,PLACENUM,EFOOT,ENSURE,ESSURE,EDSURE,EPSURE,EFSURE,RECNO,SENTENCE,SRTDATE,TT,REF_ID) ";
                            sql += string.Format("VALUES({0},{1},'{2}','{3}',{4},{5},'{6}',{7},'{8}','{9}','{10}','{11}','{12}','{13}',{14},'{15}','{16}','{17}',{18});",
                            (int)row["ETYPE"],
                            (int)row["DSID"],
                            (bool)row["PER1SHOW"],
                            (bool)row["PER2SHOW"],
                            (int)row["PER1"],
                            (int)row["PER2"],
                            row["EDATE"].ToString(),
                            (int)row["PLACENUM"],
                            row["EFOOT"].ToString().Replace("'","`"),
                            row["ENSURE"].ToString(),
                            row["ESSURE"].ToString(),
                            row["EDSURE"].ToString(),
                            row["EPSURE"].ToString(),
                            row["EFSURE"].ToString(),
                            (int)row["RECNO"],
                            row["SENTENCE"].ToString(),
                            row["SRTDATE"].ToString(),
                            row["TT"].ToString(),
                            (int)row["REF_ID"]);

                            cmd.CommandText = sql;
                            cmd.ExecuteNonQuery();

                            Tracer("Events Added: {0} {1}%");
                        }
                        transaction.Commit();
                    }
                }
                conn.Close();
            }
        }

        public bool renewActiveConnections(ArrayList clients)
        {
            while (true)
            {
                SQLiteConnection connection = new SQLiteConnection();

                connection.ConnectionString = "Data Source=" + dataSource;
                connection.Open();
                SQLiteCommand command = new SQLiteCommand(connection);

                // Erstellen der Tabelle, sofern diese noch nicht existiert.
                command.CommandText = "DROP TABLE IF EXISTS clients;";
                command.ExecuteNonQuery();

                command.CommandText = "CREATE TABLE clients ( id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, name VARCHAR(100) NOT NULL, IP VARCHAR(100) NOT NULL );";
                command.ExecuteNonQuery();

                // Das Kommando basteln
                string commandString = "INSERT INTO clients (name, IP) VALUES ";
                if (clients.Count != 0)
                {

                    int i = 0;
                    foreach (Server.extended item in clients)
                    {
                        i++;
                        commandString += "('" + item.Name + "', '" + item.IP + "')";
                        if (i != clients.Count)
                        {
                            commandString += ", ";
                        }

                    }

                    commandString += ";";
                }

                // Einfügen eines Test-Datensatzes.
                command.CommandText = commandString;
                command.ExecuteNonQuery();

                // Freigabe der Ressourcen.
                command.Dispose();

                System.Threading.Thread.Sleep(5000);

            }
            return true;
        }