ExecuteNonQuery() public method

public ExecuteNonQuery ( ) : int
return int
Example #1
1
        /// <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());
            }
        }
Example #2
0
        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();
        }
Example #3
0
        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;
            }
        }
Example #5
0
        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();
            }
        }
Example #6
0
 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();
 }
Example #7
0
 public void ReCreateTable()
 {
     SQLiteCommand command = new SQLiteCommand("DROP TABLE os;", SQLiteConnector.getInstance());
     command.ExecuteNonQuery();
     command.CommandText = "Create TABLE os([id] INTEGER PRIMARY KEY AUTOINCREMENT,[name] VARCHAR(200),[color] VARCHAR(6));";
     command.ExecuteNonQuery();
 }
Example #8
0
        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();
        }
Example #9
0
        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();
            }
        }
Example #11
0
        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());
            }
        }
Example #12
0
        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();
        }
Example #13
0
        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();
        }
Example #14
0
        /// <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();
                    }
                }
        }
Example #15
0
        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();
            }
        }
Example #16
0
        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;
        }
Example #17
0
        public void DeleteTopologyAndWeightsMatrix(string NeuroNetName, LoadingWindow loadingWindow)
        {
            connector.ConnectToDB();
            SQLiteCommand cmd = new SQLiteCommand(connector.connection);
            cmd.CommandText = "SELECT ID FROM NeuroNet WHERE NAME = '" + NeuroNetName + "'";
            int NNID = Convert.ToInt32(cmd.ExecuteScalar());

            cmd.CommandText = "SELECT ID FROM NetTopology WHERE NeuroNetID = '" + Convert.ToString(NNID) + "'";
            List<int> ls = new List<int>();

            double progress = 0.0;
            try
            {
                SQLiteDataReader reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    ls.Add(Convert.ToInt32(reader[0]));
                }
                reader.Close();

                foreach(int item in ls)
                {
                    cmd.CommandText = "DELETE FROM WeightsMatrix WHERE NetTopologyID = '" + Convert.ToString(item) + "'";
                    cmd.ExecuteNonQuery();

                    cmd.CommandText = "DELETE FROM NetTopology WHERE ID = '" + Convert.ToString(item) + "'";
                    cmd.ExecuteNonQuery();

                    progress += 100.0 / Convert.ToDouble(ls.Count);
                    Action f = new Action(() => loadingWindow.LoadingBar.Value = Convert.ToInt32(progress));
                    if (loadingWindow.LoadingBar.InvokeRequired)
                    {
                        loadingWindow.LoadingBar.Invoke(f);
                    }
                    else
                    {
                        f();
                    }
                }

            }
            catch (SQLiteException ex)
            {
                MessageBox.Show(ex.Message);
            }

            connector.DisconnectFromDB();
            loadingWindow.Invoke(new Action(() => loadingWindow.Close()));
        }
Example #18
0
        public void CreateTables()
        {
            var command = new SQLiteCommand { Connection = _connection };

            // Erstellen der Config-Tabelle, sofern diese noch nicht existiert.
            command.CommandText = "CREATE TABLE IF NOT EXISTS config ( id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, name VARCHAR(100) NOT NULL, value VARCHAR(100) NOT NULL);";
            command.ExecuteNonQuery();

            // Einfügen der Sprache
            command.CommandText = "INSERT INTO config (id, name, value) VALUES(NULL, 'Language', 'de')";
            command.ExecuteNonQuery();

            // Freigabe der Ressourcen.
            command.Dispose();
        }
Example #19
0
        void TestSQLite()
        {
            using (SQLiteConnection cn = new SQLiteConnection("Data Source=Test.db;Pooling=true;FailIfMissing=false"))
            {
                //在打开数据库时,会判断数据库是否存在,如果不存在,则在当前目录下创建一个
                try
                {
                    cn.Open();

                    using (SQLiteCommand cmd = new SQLiteCommand())
                    {
                        cmd.Connection = cn;

                        //建立表,如果表已经存在,则报错
                        cmd.CommandText = "CREATE TABLE [test] (id int, name nvarchar(20))";
                        cmd.ExecuteNonQuery();

                        //插入测试数据
                        for (int i = 2; i < 5; i++)
                        {
                            cmd.CommandText = string.Format("INSERT INTO [test] VALUES ({0}, '杜思波技术讨论区域')", i);
                            cmd.ExecuteNonQuery();
                        }

                        for (int i = 5; i < 10; i++)
                        {
                            cmd.CommandText = string.Format("INSERT INTO [test] VALUES ({0}, 'English Test')", i);
                            cmd.ExecuteNonQuery();
                        }

                        //读取数据
                        cmd.CommandText = "SELECT * FROM [test]";
                        using (SQLiteDataReader dr = cmd.ExecuteReader())
                        {
                            while (dr.Read())
                            {
                                //Console.WriteLine("第{0} 条:{1}", dr.GetValue(0), dr.GetString(1));
                                MessageBox.Show(string.Format("第{0} 条:{1}", dr.GetValue(0), dr.GetString(1)));
                            }
                        }
                    }
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.Message);
                }
            }
        }
        public SourceTypeFile()
        {
            OleDbDataReader oledbReader;
            oledbReader = base.GetOleDbDataReader("*_a.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 SourceType;";
                        cmd.ExecuteNonQuery();

                        foreach (DbDataRecord row in oledbReader)
                        {
                            string sql = "INSERT INTO SourceType (RULESET,DSID,SOURTYPE,TRANS_TO,NAME,FOOT,SHORT,BIB,CUSTFOOT,CUSTSHORT,CUSTBIB,SAMEAS,SAMEASMSG,\"PRIMARY\",REMINDERS,TT) ";
                            sql += string.Format("VALUES ({0},{1},{2},{3},'{4}','{5}','{6}','{7}','{8}','{9}','{10}',{11},'{12}','{13}','{14}','{15}');",
                                    (decimal)row["RULESET"],
                                    (int)row["DSID"],
                                    (int)row["SOURTYPE"],
                                    (int)row["TRANS_TO"],
                                    row["NAME"].ToString().Replace("'","`"),
                                    row["FOOT"].ToString().Replace("'","`"),
                                    row["SHORT"].ToString().Replace("'","`"),
                                    row["BIB"].ToString().Replace("'","`"),
                                    row["CUSTFOOT"].ToString().Replace("'","`"),
                                    row["CUSTSHORT"].ToString().Replace("'","`"),
                                    row["CUSTBIB"].ToString().Replace("'","`"),
                                    (int)row["SAMEAS"],
                                    row["SAMEASMSG"].ToString().Replace("'","`"),
                                    (bool)row["PRIMARY"],
                                    row["REMINDERS"].ToString().Replace("'","`"),
                                    row["TT"].ToString().Replace("'","`")
                            );

                            cmd.CommandText = sql;
                            cmd.ExecuteNonQuery();
                            Tracer("Source Types: {0} {1}%");
                        }
                        transaction.Commit();
                    }
                }
                conn.Close();
            }
        }
        public string AddComment(NewsFeedModel comment)
        {
            string status = "";
            string query;
            DataTable dt1 = new DataTable();
            SQLiteCommand cmd;

            try
            {
                dbConn.Open();

                query = "insert into news_feed values (" + comment.UserId + ", '" + comment.FirstName + "', '" + comment.LastName + "', '" + comment.Comment + "', "+ comment.Month +
                        ", " + comment.Day + ", " + comment.Year + ", " + comment.Hour + ", " + comment.Minute + ")";

                cmd = new SQLiteCommand(query, dbConn);
                cmd.ExecuteNonQuery();

                status = "Comment Added";

                dbConn.Close();
            }
            catch (SQLiteException ex)
            {
                Console.Write(ex.ToString());
                status = ex.ToString();
                dbConn.Close();
            }

            return status;
        }
    private static int InsertNewBook(
        string filePath,
        string title,
        string author,
        DateTime publicationDate,
        string isbn)
    {
        SQLiteConnection connection = GetConnection(filePath);

        connection.Open();
        using (connection)
        {
            SQLiteCommand insertBookCommand = new SQLiteCommand(
                @"INSERT INTO Books (BookTitle, BookAuthor, PublicationDate, ISBN)
                  VALUES (@bookTitle, @bookAuthor, @publicationDate, @isbn)", connection);

            insertBookCommand.Parameters.AddWithValue("@bookTitle", title);
            insertBookCommand.Parameters.AddWithValue("@bookAuthor", author);
            insertBookCommand.Parameters.AddWithValue("@publicationDate", publicationDate);
            insertBookCommand.Parameters.AddWithValue("@isbn", isbn);

            int rowsAffected = insertBookCommand.ExecuteNonQuery();
            return rowsAffected;
        }
    }
Example #23
0
        public static void ChapterFinished(string mangaTitle)
        {
            try
            {
                using (SQLiteConnection sqLiteConnection = new SQLiteConnection(ConnectionString))
                {
                    sqLiteConnection.Open();

                    SQLiteCommand sqLiteCommand = new SQLiteCommand(sqLiteConnection)
                                                      {
                                                          CommandText = "UPDATE READING_LIST " +
                                                                        "SET READ_CURRENT_CHAPTER = READ_CURRENT_CHAPTER + 1, READ_LAST_TIME = ? " +
                                                                        "WHERE MANGA_ID = ?"
                                                      };
                    sqLiteCommand.Parameters.AddWithValue(null, DateTime.Now);
                    sqLiteCommand.Parameters.AddWithValue(null, GetMangaId(mangaTitle));
                    sqLiteCommand.ExecuteNonQuery();

                    sqLiteConnection.Close();
                }
            }
            catch (Exception ex)
            {
                ErrorMessageBox.Show(ex.Message, ex.ToString());
                Logger.ErrorLogger("error.txt", ex.ToString());
            }
        }
Example #24
0
 private void DeleteCardBase()
 {
     if (dataGridView1.SelectedRows.Count > 0)
     {
         id = Int32.Parse(dataGridView1.SelectedRows[0].Cells[0].Value.ToString());
         if (dataGridView1.RowCount != 0) index = dataGridView1.SelectedRows[0].Index;
         DialogResult result = MessageBox.Show("Czy na pewno chcesz usunąć bazę karty numer " + id + "? \n\nOperacji nie można cofnąć.", "Ważne", MessageBoxButtons.YesNo, MessageBoxIcon.Warning);
         if (result == DialogResult.Yes)
             using (SQLiteConnection conn = new SQLiteConnection(connString))
             {
                 conn.Open();
                 SQLiteCommand command = new SQLiteCommand(conn);
                 command.CommandText = "DELETE FROM [CardBase] WHERE id = @id";
                 command.Parameters.Add(new SQLiteParameter("@id", id));
                 command.ExecuteNonQuery();
                 conn.Close();
                 Odswierz();
                 if (dataGridView1.RowCount != 0)
                 {
                     if (index == dataGridView1.RowCount) dataGridView1.CurrentCell = dataGridView1.Rows[index - 1].Cells[0];
                     else dataGridView1.CurrentCell = dataGridView1.Rows[index].Cells[0];
                 }
             }
     }
 }
Example #25
0
        public static void crear()
        {
            SQLiteConnection.CreateFile("db/interna.wpdb");
            ObjConnection.Open();

            string sql1 = "create table cliente " +
                         "(id INTEGER PRIMARY KEY AUTOINCREMENT," +
                         "nombre VARCHAR(20)," +
                         "direccion VARCHAR(20)," +
                         "telefono VARCHAR(20)" +
                         ")";
            SQLiteCommand command1 = new SQLiteCommand(sql1, ObjConnection);
            command1.ExecuteNonQuery();

            string sql = "create table equipo " +
                         "(id INTEGER PRIMARY KEY AUTOINCREMENT," +
                         "marca VARCHAR(20)," +
                         "modelo VARCHAR(20)," +
                         "color VARCHAR(20)," +
                         "foto VARCHAR(20)," +
                         "fechaIng VARCHAR(20)," +
                         "fechaEnt VARCHAR(20)," +
                         "pagado INTEGER DEFAULT 0," +
                         "arreglado INTEGER DEFAULT 0," +
                         "entregado INTEGER DEFAULT 0," +
                         "precio FLOAT," +
                         "obser VARCHAR(40),"+
                         "id_cliente INTEGER," +
                         "FOREIGN KEY(id_cliente) REFERENCES cliente(id)" +
                         ")";
            SQLiteCommand command = new SQLiteCommand(sql, ObjConnection);
            command.ExecuteNonQuery();

            ObjConnection.Close();
        }
 /// <summary>
 /// Сохраняет изменения в таблице
 /// </summary>
 /// <param name="question">вопрос</param>
 public void SaveOrUpdate(SQLiteConnection conn, QuestionResult question)
 {
     if (question.ID == 0)
     {
         int ID = indexService.NextID(TABLE_NAME);
         using (SQLiteCommand insertSQL = new SQLiteCommand("insert into QUESTION_RESULT (ID, QuestionTitle, TEST_RESULT_ID) values (@ID, @QuestionTitle, @TEST_RESULT_ID)", conn))
         {
             insertSQL.Parameters.AddWithValue("@QuestionTitle", question.QuestionTitle);
             insertSQL.Parameters.AddWithValue("@TEST_RESULT_ID", question.testResult.ID);
             insertSQL.Parameters.AddWithValue("@ID", ID);
             insertSQL.ExecuteNonQuery();
             question.ID = ID;
         }
         
     }
     else
     {
         using (SQLiteCommand insertSQL = new SQLiteCommand("UPDATE QUESTION_RESULT SET QuestionTitle = @QuestionTitle, TEST_RESULT_ID = @TEST_RESULT_ID WHERE ID = @ID", conn))
         {
             insertSQL.Parameters.AddWithValue("@QuestionTitle", question.QuestionTitle);
             insertSQL.Parameters.AddWithValue("@TEST_RESULT_ID", question.testResult.ID);
             insertSQL.Parameters.AddWithValue("@ID", question.ID);
             insertSQL.ExecuteNonQuery();
         }
     }
 }
Example #27
0
        /// <summary>
        /// Constructor creates a database if it doesn't exist and creates the database's tables
        /// </summary>
        public DataBaseRepository()
        {
            // "using " keywords ensures the object is properly disposed.
            using (var conn = new SQLiteConnection(Connectionstring))
            {
                try
                {
                    if (!File.Exists(Startup.dbSource))
                    {
                        SQLiteConnection.CreateFile(Startup.dbSource);
                    }

                    conn.Open();
                    //Create a Table
                    string query = $"create table IF NOT EXISTS {TableName} (Id INTEGER PRIMARY KEY, Date VARCHAR NOT NULL DEFAULT CURRENT_DATE, Title nvarchar(255) not null, Content nvarchar(1000) Not NULL) ";
                    SQLiteCommand command = new SQLiteCommand(query, conn);
                    command.ExecuteNonQuery();
                }
                //TODO: Handle try catch better cath a more specific error type. 
                catch (SQLiteException ex )
                {
                    Console.WriteLine(ex.ToString());
                }
                conn.Close();
            }
        }
Example #28
0
        public bool insertRecord(string sql)
        {
            connectToDb("Invoice.db");

            SQLiteCommand command = new SQLiteCommand(sql, dbConnection);
            try
            {
                command.ExecuteNonQuery();
            }
            catch(SQLiteException e )
            {
                if (e.ErrorCode == 1)
                {
                    MessageBox.Show("Unable to find database. Program will exit.");
                    Environment.Exit(0);
                }
                else
                {
                    MessageBox.Show("Value already in database"); //exception 19 if more need to be added
                }

                return false;
            }
            dbConnection.Close();
            return true;
        }
Example #29
0
        private static void addSomeKids(string connectionString)
        {
            using (SQLiteConnection myConnection = new SQLiteConnection())
            {
                myConnection.ConnectionString = connectionString;
                myConnection.Open();

                using (SQLiteTransaction myTransaction = myConnection.BeginTransaction())
                {
                    using (SQLiteCommand myCommand = new SQLiteCommand(myConnection))
                    {
                        var rnd = new Random();
                        for (int i = 1; i < 100; i++)
                        {
                            myCommand.CommandText = @"insert into tblKids(ParentId, BirthDate, Name)
                                                                  values(@ParentId, @BirthDate, @Name)";
                            myCommand.Parameters.AddWithValue("@Name", "Kid" + i);
                            myCommand.Parameters.AddWithValue("@ParentId", rnd.Next(1, 11));
                            myCommand.Parameters.AddWithValue("@BirthDate", DateTime.Now.AddYears(-i));

                            myCommand.ExecuteNonQuery();
                        }
                    }
                    myTransaction.Commit();
                }
            }
        }
Example #30
0
        public static void AddSomeTblBlogsTestRecords(string connectionString, IList<string> imagesPath)
        {
            using (SQLiteConnection myConnection = new SQLiteConnection())
            {
                myConnection.ConnectionString = connectionString;
                myConnection.Open();

                using (SQLiteTransaction myTransaction = myConnection.BeginTransaction())
                {
                    using (SQLiteCommand myCommand = new SQLiteCommand(myConnection))
                    {
                        foreach (var itemPath in imagesPath)
                        {
                            myCommand.CommandText = @"insert into tblBlogs(url, name, thumbnail, NumberOfPosts, AddDate)
                                                                  values(@url, @name, @thumbnail, @NumberOfPosts, @AddDate)";
                            var name = Path.GetFileNameWithoutExtension(itemPath);
                            myCommand.Parameters.AddWithValue("@url", "www.blog" + name + ".com");
                            myCommand.Parameters.AddWithValue("@name", "blog" + name);
                            var data = File.ReadAllBytes(itemPath);
                            myCommand.Parameters.AddWithValue("@thumbnail", data);
                            myCommand.Parameters.AddWithValue("@NumberOfPosts", 10);
                            myCommand.Parameters.AddWithValue("@AddDate", DateTime.Now);

                            myCommand.ExecuteNonQuery();
                        }
                    }
                    myTransaction.Commit();
                }
            }
        }