ExecuteNonQuery() public method

Execute the command and return the number of rows inserted/updated affected by it.
public ExecuteNonQuery ( ) : int
return int
Ejemplo n.º 1
1
        public LibraryDatabaseManager(String dbFolderPath)
        {
            DatabaseFile = String.Format(dbFolderPath + "{0}Library.db",
                                         Path.DirectorySeparatorChar);
            Connection = new SqliteConnection (
                "Data Source = " + LibraryDatabaseManager.DatabaseFile +
                "; Version = 3;");

            bool exists = File.Exists (LibraryDatabaseManager.DatabaseFile);
            if (!exists) {
                SqliteConnection.CreateFile (LibraryDatabaseManager.DatabaseFile);
            }

            Connection.Open ();
            if (!exists) {
                using (SqliteCommand command = new SqliteCommand (Connection)) {
                    command.CommandText =
                        "CREATE TABLE Books (" +
                            "BookID INTEGER PRIMARY KEY NOT NULL, " +
                            "BookTitle TEXT, " +
                            "BookAuthor TEXT, " +
                            "BookGenre TEXT, " +
                            "BookPublishedYear INTEGER, " +
                            "BookPath TEXT);";
                    command.ExecuteNonQuery();
                }
            }
        }
Ejemplo n.º 2
0
        private bool _CreateCoverDB()
        {
            try
            {
                using (var command = new SQLiteCommand(_Connection))
                {
                    command.CommandText = "CREATE TABLE IF NOT EXISTS Version (Value INTEGER NOT NULL);";
                    command.ExecuteNonQuery();

                    command.CommandText = "INSERT INTO Version (Value) VALUES(@Value)";
                    command.Parameters.Add("@Value", DbType.Int32).Value = CSettings.DatabaseCoverVersion;
                    command.ExecuteNonQuery();

                    command.CommandText = "CREATE TABLE IF NOT EXISTS Cover ( id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, " +
                                          "Path TEXT NOT NULL, width INTEGER NOT NULL, height INTEGER NOT NULL);";
                    command.ExecuteNonQuery();

                    command.CommandText = "CREATE TABLE IF NOT EXISTS CoverData ( id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, " +
                                          "CoverID INTEGER NOT NULL, Data BLOB NOT NULL);";
                    command.ExecuteNonQuery();
                }
            }
            catch (Exception e)
            {
                CLog.LogError("Error creating Cover DB " + e);
                return(false);
            }
            return(true);
        }
Ejemplo n.º 3
0
        private static void InitDefaultTables()
        {
            using (SQLiteCommand command = new SQLiteCommand(connection))
            {
                command.CommandText  = "CREATE TABLE IF NOT EXISTS `logStats` (";
                command.CommandText += "  `id` INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,";
                command.CommandText += "  `accountID` INTEGER NOT NULL,";
                command.CommandText += "  `type` INTEGER NOT NULL,";
                command.CommandText += "  `value` INTEGER NOT NULL,";
                command.CommandText += "  `time` INTEGER NOT NULL";
                command.CommandText += ")";
                command.ExecuteNonQuery();

                command.CommandText  = "CREATE TABLE IF NOT EXISTS `logEvents` (";
                command.CommandText += "  `id` INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,";
                command.CommandText += "  `accountID` INTEGER NOT NULL,";
                command.CommandText += "  `InteractAccountID` INTEGER NOT NULL,";
                command.CommandText += "  `type` INTEGER NOT NULL,";
                command.CommandText += "  `lastPing` INTEGER NOT NULL,";
                command.CommandText += "  `averagePing` INTEGER NOT NULL,";
                command.CommandText += "  `time` INTEGER NOT NULL,";
                command.CommandText += "  `message` TEXT NOT NULL";
                command.CommandText += ")";
                command.ExecuteNonQuery();
            }
        }
Ejemplo n.º 4
0
        private void _CreateHighscoreDBV1(string filePath)
        {
            using (var connection = new SQLiteConnection())
            {
                connection.ConnectionString = "Data Source=" + filePath;

                try
                {
                    connection.Open();
                }
                catch (Exception)
                {
                    return;
                }

                using (var command = new SQLiteCommand(connection))
                {
                    command.CommandText = "CREATE TABLE IF NOT EXISTS Version ( id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, Value INTEGER NOT NULL);";
                    command.ExecuteNonQuery();

                    command.CommandText = "INSERT INTO Version (id, Value) VALUES(NULL, 1 )";
                    command.ExecuteNonQuery();

                    command.CommandText = "CREATE TABLE IF NOT EXISTS Songs ( id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, " +
                                          "Artist TEXT NOT NULL, Title TEXT NOT NULL, NumPlayed INTEGER);";
                    command.ExecuteNonQuery();

                    command.CommandText = "CREATE TABLE IF NOT EXISTS Scores ( id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, " +
                                          "SongID INTEGER NOT NULL, PlayerName TEXT NOT NULL, Score INTEGER NOT NULL, LineNr INTEGER NOT NULL, Date BIGINT NOT NULL, " +
                                          "Medley INTEGER NOT NULL, Duet INTEGER NOT NULL, Difficulty INTEGER NOT NULL);";
                    command.ExecuteNonQuery();
                }
            }
        }
Ejemplo n.º 5
0
        private bool _CreateDB()
        {
            if (_Connection == null)
                return false;

            try
            {
                using (SQLiteCommand command = new SQLiteCommand(_Connection))
                {
                    command.CommandText = "CREATE TABLE IF NOT EXISTS Version (Value INTEGER NOT NULL);";
                    command.ExecuteNonQuery();

                    command.CommandText = "INSERT INTO Version (Value) VALUES(0)";
                    command.ExecuteNonQuery();

                    command.CommandText = "CREATE TABLE IF NOT EXISTS Images ( id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, " +
                                          "Path TEXT NOT NULL, width INTEGER NOT NULL, height INTEGER NOT NULL);";
                    command.ExecuteNonQuery();

                    command.CommandText = "CREATE TABLE IF NOT EXISTS ImageData ( id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, " +
                                          "ImageID INTEGER NOT NULL, Data BLOB NOT NULL);";
                    command.ExecuteNonQuery();
                }
            }
            catch (Exception e)
            {
                CLog.Error("Error creating Ressource DB " + e);
                return false;
            }
            return true;
        }
Ejemplo n.º 6
0
		/// <summary>
		/// Write or update files and hashes.
		/// </summary>
		/// <param name="liste"></param>
		public void WriteFiles(List<FileObject> liste)
		{
			using (SqliteTransaction sqt = connection.BeginTransaction()) {
				using (SqliteCommand sqc = new SqliteCommand(connection)) {
					// Alle vorhandenen Einträge löschen, da diese in jedem Fall überschrieben werden sollten
					sqc.CommandText = "DELETE FROM Files WHERE path=:path";
					sqc.Parameters.Add(new SqliteParameter("path"));
					foreach (FileObject d in liste) {
						sqc.Parameters["path"].Value = d.Filepath;
						sqc.ExecuteNonQuery();
					}
					//sqc.Parameters.Clear(); // Nötig?

					// Dateien eintragen
					sqc.CommandText = "INSERT INTO Files (path, hash, size, changedate) VALUES (:path, :hash, :size, :changedate)";
					sqc.Parameters.Add(new SqliteParameter("path"));
					sqc.Parameters.Add(new SqliteParameter("hash"));
					sqc.Parameters.Add(new SqliteParameter("size"));
					sqc.Parameters.Add(new SqliteParameter("changedate"));
					foreach (FileObject d in liste) {
						sqc.Parameters["path"].Value = d.Filepath;
						sqc.Parameters["hash"].Value = d.Hash;
						sqc.Parameters["size"].Value = d.Size;
						sqc.Parameters["changedate"].Value = d.ChangeDate;
						sqc.ExecuteNonQuery();
					}
				}
				sqt.Commit();
			}
		}
Ejemplo n.º 7
0
        public void CreateDB(string databaseFile)
        {
            if (!File.Exists(databaseFile))
             {

            // Create the database
            SqliteConnection.CreateFile(databaseFile);
            Console.Write("vt oluşturuldu");
             }
             else
            Console.Write("vt mevcut");

             try
             {

            // Connect to the database
            using (SqliteConnection sqlCon = new SqliteConnection (String.Format ("Data Source = {0};", databaseFile)))
            {
               sqlCon.Open ();

               // Create a table
               using (SqliteCommand sqlCom = new SqliteCommand (sqlCon))
               {

                  if(SubeSistemi=="Y")

                  sqlCom.CommandText = "CREATE TABLE IF NOT EXISTS EMIRLERY (ID INTEGER PRIMARY KEY, Title VARCHAR(20), Subject VARCHAR(20), PostDate VARCHAR(20), ImgUrl VARCHAR(20), DocAdress  VARCHAR(20), Sube VARCHAR(11))";

                    else if(SubeSistemi=="Z") {
                     sqlCom.CommandText = "CREATE TABLE IF NOT EXISTS EMIRLERZ (ID INTEGER PRIMARY KEY, Title VARCHAR(20), Subject VARCHAR(20), PostDate VARCHAR(20), ImgUrl VARCHAR(20), DocAdress  VARCHAR(20), Sube VARCHAR(11))";

                  }
                  else if (SubeSistemi=="X") {
                     sqlCom.CommandText = "CREATE TABLE IF NOT EXISTX EMIRLERX (ID INTEGER PRIMARY KEY, Title VARCHAR(20), Subject VARCHAR(20), PostDate VARCHAR(20), ImgUrl VARCHAR(20), DocAdress  VARCHAR(20), Sube VARCHAR(11))";

                  }

                  sqlCom.ExecuteNonQuery ();

                  Console.WriteLine(sqlCom.ExecuteNonQuery());
               }
               //end using sqlCom

               sqlCon.Close ();

            }
            //end using sqlCon

            this.durumF.Text = "hazır!";

             } catch (Exception ex)
             {

            this.durumF.Text = String.Format ("Sqlite error: {0}", ex.Message);

             }//end try catch
        }
Ejemplo n.º 8
0
        private bool _ConvertV2toV3(SQLiteConnection connection)
        {
            var command = new SQLiteCommand(connection)
            {
                CommandText = "ALTER TABLE Songs ADD DateAdded BIGINT"
            };

            command.ExecuteNonQuery();
            command.CommandText = "UPDATE Songs SET [DateAdded] = @DateAdded";
            command.Parameters.Add("@DateAdded", DbType.Int64, 0).Value = DateTime.Now.Ticks;
            command.ExecuteNonQuery();
            command.CommandText = "UPDATE Version SET [Value] = @version";
            command.Parameters.Add("@version", DbType.Int32, 0).Value = 3;
            command.ExecuteNonQuery();

            //Read NumPlayed from Scores and save to Songs
            command.CommandText = "SELECT SongID, Date FROM Scores ORDER BY Date ASC";

            SQLiteDataReader reader;

            try
            {
                reader = command.ExecuteReader();
            }
            catch (Exception)
            {
                return(false);
            }

            long       lastDateAdded = -1;
            int        lastID        = -1;
            DateTime   dt            = new DateTime(1, 1, 1, 0, 0, 5);
            long       sec           = dt.Ticks;
            List <int> ids           = new List <int>();

            while (reader.Read())
            {
                int  id        = reader.GetInt32(0);
                long dateAdded = reader.GetInt64(1);
                if (id != lastID || dateAdded > lastDateAdded + sec)
                {
                    ids.Add(id);
                    lastID        = id;
                    lastDateAdded = dateAdded;
                }
            }
            reader.Dispose();

            foreach (int id in ids)
            {
                _IncreaseSongCounter(id, command);
            }
            command.Dispose();

            return(true);
        }
Ejemplo n.º 9
0
        public void CreateDB(string databaseFile)
        {
            try
             {

            // Check if database already exists
            if (!File.Exists (databaseFile))
            {

               // Create the database
               SqliteConnection.CreateFile (databaseFile);

               // Connect to the database
               using (SqliteConnection sqlCon = new SqliteConnection (String.Format ("Data Source = {0};", databaseFile)))
               {
                  sqlCon.Open ();

                  // Create a table
                  using (SqliteCommand sqlCom = new SqliteCommand (sqlCon))
                  {
                     sqlCom.CommandText = "CREATE TABLE Personel (ID INTEGER PRIMARY KEY, UserName VARCHAR(20), Password VARCHAR(20))";
                     //veri Ekleme
                     //Update
                     //   sqlCom.CommandText = "UPDATE Customers SET FirstName= 'Haluk' WHERE LastName = @lastName";
                     // sqlCom.Parameters.Add(new SqliteParameter("@lastName","Haluky"));

                     sqlCom.ExecuteNonQuery ();

                     Console.WriteLine(sqlCom.ExecuteNonQuery());
                  }
                  //end using sqlCom

                  sqlCon.Close ();

               }
               //end using sqlCon

               this.durumGostericiT.Text = "Database hazır!";

            } else
            {

               this.durumGostericiT.Text = "Database Mevcut!";

            }//end if else

             } catch (Exception ex)
             {

            this.durumGostericiT.Text = String.Format ("Sqlite error: {0}", ex.Message);

             }//end try catch
        }
Ejemplo n.º 10
0
        public void CreateStandardTables()
        {
            SQLiteCommand command = new SQLiteCommand(connection);


            command.CommandText  = "CREATE TABLE IF NOT EXISTS `world_container` (";
            command.CommandText += "  `id` INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,";
            command.CommandText += "  `name` text NOT NULL,";
            command.CommandText += " `posx` REAL  NOT NULL,";
            command.CommandText += " `posy` REAL  NOT NULL,";
            command.CommandText += " `posz` REAL  NOT NULL,";
            command.CommandText += " `world` text NOT NULL,";
            command.CommandText += " `opened` INTEGER NOT NULL";
            command.CommandText += ")";
            command.ExecuteNonQuery();

            command.CommandText  = "CREATE TABLE IF NOT EXISTS `world_items` (";
            command.CommandText += "  `id` INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,";
            command.CommandText += "  `iteminstance` text NOT NULL,";
            command.CommandText += "  `amount` INTEGER NOT NULL,";
            command.CommandText += "  `posx` REAL NOT NULL,";
            command.CommandText += "  `posy` REAL NOT NULL,";
            command.CommandText += "  `posz` REAL NOT NULL,";
            command.CommandText += "  `world` text NOT NULL";
            command.CommandText += ")";
            command.ExecuteNonQuery();


            command.CommandText  = "CREATE TABLE IF NOT EXISTS `world_mobinteract` (";
            command.CommandText += " `id` INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,";
            command.CommandText += " `vobType` INTEGER NOT NULL,";
            command.CommandText += " `name` text NOT NULL,";
            command.CommandText += " `posx` REAL NOT NULL,";
            command.CommandText += " `posy` REAL NOT NULL,";
            command.CommandText += " `posz` REAL NOT NULL,";
            command.CommandText += " `world` text NOT NULL,";
            command.CommandText += " `triggered` INTEGER NOT NULL";
            command.CommandText += ")";
            command.ExecuteNonQuery();


            command.CommandText  = "CREATE TABLE IF NOT EXISTS `world_container_items` (";
            command.CommandText += " `id` INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,";
            command.CommandText += " `containerid` INTEGER NOT NULL,";
            command.CommandText += " `item` text NOT NULL,";
            command.CommandText += " `amount` INTEGER NOT NULL";
            command.CommandText += ")";
            command.ExecuteNonQuery();

            command.Dispose();
        }
Ejemplo n.º 11
0
        private void CreateSQLiteDatabase(string databaseFile)
        {
            try
             {

            // Check if database already exists
            if (!File.Exists (databaseFile))
            {

               // Create the database
               SqliteConnection.CreateFile (databaseFile);

               // Connect to the database
               using (SqliteConnection sqlCon = new SqliteConnection (String.Format ("Data Source = {0};", databaseFile)))
               {
                  sqlCon.Open ();

                  // Create a table
                  using (SqliteCommand sqlCom = new SqliteCommand (sqlCon))
                  {

                     sqlCom.CommandText="CREATE TABLE Personel (ID INTEGER PRIMARY KEY, UserName VARCHAR(20), Password VARCHAR (20), Bolum VARCHAR(20), Gorev VARCHAR(20), Isim VARCHAR(20), Telefon VARCHAR(20), Email VARCHAR(20))";

                      sqlCom.ExecuteNonQuery ();

                     Console.WriteLine(sqlCom.ExecuteNonQuery());
                  }
                  //end using sqlCom

                  sqlCon.Close ();

               }
               //end using sqlCon

               this.durumT.Text = "oluştu";

            } else
            {

               this.durumT.Text = "Zaten var";

            }//end if else

             } catch (Exception ex)
             {

            this.durumT.Text = String.Format ("Sqlite error: {0}", ex.Message);

             }//end try catch
        }
Ejemplo n.º 12
0
        public void CreateStandardTables()
        {
            using (SQLiteCommand command = new SQLiteCommand(Sqlite.getSqlite().connection)) {
                command.CommandText  = "CREATE TABLE IF NOT EXISTS `account` (";
                command.CommandText += "  `id` INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,";
                command.CommandText += "  `name` text NOT NULL,";
                command.CommandText += "  `password` text NOT NULL,";
                command.CommandText += " `posx` REAL  NOT NULL,";
                command.CommandText += " `posy` REAL  NOT NULL,";
                command.CommandText += " `posz` REAL  NOT NULL,";
                command.CommandText += " `world` text NOT NULL";
                command.CommandText += ")";
                command.ExecuteNonQuery();

                command.CommandText  = "CREATE TABLE IF NOT EXISTS `account_hitchances` (";
                command.CommandText += "  `id` INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,";
                command.CommandText += "  `accountID` INTEGER NOT NULL,";
                command.CommandText += "  `type` INTEGER NOT NULL,";
                command.CommandText += "  `value` INTEGER NOT NULL";
                command.CommandText += ")";
                command.ExecuteNonQuery();

                command.CommandText  = "CREATE TABLE IF NOT EXISTS `account_attributes` (";
                command.CommandText += "  `id` INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,";
                command.CommandText += "  `accountID` INTEGER NOT NULL,";
                command.CommandText += "  `type` INTEGER NOT NULL,";
                command.CommandText += "  `value` INTEGER NOT NULL";
                command.CommandText += ")";
                command.ExecuteNonQuery();

                command.CommandText  = "CREATE TABLE IF NOT EXISTS `account_talents` (";
                command.CommandText += "  `id` INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,";
                command.CommandText += "  `accountID` INTEGER NOT NULL,";
                command.CommandText += "  `type` INTEGER NOT NULL,";
                command.CommandText += "  `value` INTEGER NOT NULL,";
                command.CommandText += "  `skill` INTEGER NOT NULL";
                command.CommandText += ")";
                command.ExecuteNonQuery();

                command.CommandText  = "CREATE TABLE IF NOT EXISTS `account_items` (";
                command.CommandText += "  `id` INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,";
                command.CommandText += "  `accountID` INTEGER NOT NULL,";
                command.CommandText += "  `instanceID` text NOT NULL,";
                command.CommandText += "  `amount` INTEGER NOT NULL";
                command.CommandText += ")";
                command.ExecuteNonQuery();
            }
        }
Ejemplo n.º 13
0
        public bool CSQL_AddDevice(CDevice device)
        {
            string sql = "INSERT INTO devices VALUES ('" +
                               device.DeviceID + "', '" +
                               device.DeviceDescription + "', '" +
                                 device.DeviceLocation + "', '" +
                         		device.DeviceServer + "', '" +
                         		device.DeviceServerSubDir + "');";

            SqliteCommand dbhCmd;
            int num_recs = 0;

            if (CSQL_Connected ()) {

                dbhCmd = new SqliteCommand (sql, dbh);
                num_recs = dbhCmd.ExecuteNonQuery ();

                if (num_recs > 0) {
                    CSQL_AddDeviceLogFileDescription (device, dbhCmd.Connection);
                }

            }

            return false;
        }
Ejemplo n.º 14
0
        public bool register(String name, String password)
        {
            if (existsName(name))
            {
                return(false);
            }

            //Setting default parameter! Can be overwritten by other modules, after registration!
            player.setSpawnInfos(@"NEWWORLD\NEWWORLD.ZEN", null, null);
            player.HPMax = 10;
            player.HP    = 10;


            using (SQLiteCommand command = new SQLiteCommand(Sqlite.getSqlite().connection)) {
                command.CommandText  = "INSERT INTO `account` (";
                command.CommandText += "  `id`, `name`, `password`, `posx`, `posy`, `posz`, `world`)";
                command.CommandText += "VALUES( NULL, @name, @password, @posx, @posy, @posz, @world)";

                command.Parameters.AddWithValue("@name", name);
                command.Parameters.AddWithValue("@password", password);
                command.Parameters.AddWithValue("@posx", 0);
                command.Parameters.AddWithValue("@posy", 0);
                command.Parameters.AddWithValue("@posz", 0);
                command.Parameters.AddWithValue("@world", player.Map);

                command.ExecuteNonQuery();

                command.CommandText = @"select last_insert_rowid()";
                accountID           = (long)command.ExecuteScalar();
            }

            state = State.LoggedIn;
            return(true);
        }
Ejemplo n.º 15
0
        private void apply_Click(object sender, EventArgs e)
        {
            SqliteCommand command = new SqliteCommand(connection);
            String date = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");

            command.CommandText = "INSERT OR REPLACE INTO comments (guid, comment, date) VALUES (@guid, @comment, @date)";
            command.Parameters.Add(new SqliteParameter("@guid", guid));
            command.Parameters.Add(new SqliteParameter("@comment", input.Text));
            command.Parameters.Add(new SqliteParameter("@date", date));
            command.ExecuteNonQuery();

            command.Dispose();

            if (mode == "players")
            {
                Thread thread = new Thread(new ThreadStart(gui.thread_Player));
                thread.IsBackground = true;
                thread.Start();
            }
            else if (mode == "bans")
            {
                Thread thread = new Thread(new ThreadStart(gui.thread_Bans));
                thread.IsBackground = true;
                thread.Start();
            }
            else if (mode == "player database")
            {
                Thread thread = new Thread(new ThreadStart(gui.thread_Database));
                thread.IsBackground = true;
                thread.Start();
            }

            this.Close();
        }
Ejemplo n.º 16
0
        public bool ExcuteTransaction(string sql)
        {
            var cmds = sql.Split(';');
            using (SqliteConnection conn = new SqliteConnection(this.SqlConfig.ConnectionString))
            {
                conn.Open();
                SqliteCommand cmd = new SqliteCommand(conn);

                SqliteTransaction tran = conn.BeginTransaction();
                try
                {
                    foreach (var cmdSql in cmds)
                    {
                        cmd.CommandText = cmdSql;
                        cmd.ExecuteNonQuery();
                    }
                    tran.Commit();
                    conn.Close();
                    return true;
                }
                catch (Exception e)
                {
                    tran.Rollback();
                    conn.Close();
                    throw new Exception(e.Message + "  sql:" + sql);
                }
                finally
                {
                    conn.Close();
                }
            }
        }
Ejemplo n.º 17
0
        public static void log_CharacterStat(NPCProto proto, CharStat stat, int value)
        {
            if (proto is NPC)
            {
                return;
            }
            Player player = (Player)proto;

            if (player.getAccount() == null || !player.IsSpawned())
            {
                return;
            }

            lock (connection)
            {
                using (SQLiteCommand command = new SQLiteCommand(connection))
                {
                    command.CommandText  = "INSERT INTO `logStats` (";
                    command.CommandText += "  `id`, `accountID`, `type`, `value`, `time`)";
                    command.CommandText += "VALUES( NULL, @accID, @type, @value, @time)";

                    command.Parameters.AddWithValue("@accID", player.getAccount().accountID);
                    command.Parameters.AddWithValue("@type", (int)stat);
                    command.Parameters.AddWithValue("@value", value);
                    command.Parameters.AddWithValue("@time", DateTime.Now.Ticks);

                    command.ExecuteNonQuery();
                }
            }
        }
Ejemplo n.º 18
0
 public static void executeQuery(string sql)
 {
     myConnection.Open();
     SqliteCommand com = new SqliteCommand(sql, myConnection);
     com.ExecuteNonQuery();
     myConnection.Close();
 }
Ejemplo n.º 19
0
        private bool _ConvertV1toV2(SQLiteConnection connection)
        {
            using (var command = new SQLiteCommand(connection))
            {
                command.CommandText = "ALTER TABLE Scores ADD ShortSong INTEGER";
                command.ExecuteNonQuery();
                command.CommandText = "UPDATE Scores SET [ShortSong] = @ShortSong";
                command.Parameters.Add("@ShortSong", DbType.Int32, 0).Value = 0;
                command.ExecuteNonQuery();
                command.CommandText = "UPDATE Version SET [Value] = @version";
                command.Parameters.Add("@version", DbType.Int32, 0).Value = 2;
                command.ExecuteNonQuery();
            }

            return(true);
        }
        /// <summary>
        /// Clears all items from the database where their PublishDate is before the date provided.
        /// </summary>
        /// <param name="date"></param>
        public void ClearItemsBeforeDate(DateTime date)
        {
            try
            {
                using (SqliteConnection connection = new SqliteConnection(ItemsConnectionString))
                {
                    connection.Open();
                    using (SqliteCommand command = new SqliteCommand(connection))
                    {
                        string sql = @"DELETE FROM items WHERE DATETIME(publishdate) <= DATETIME(@date)";
                        command.CommandText = sql;

                        SqliteParameter parameter = new SqliteParameter("@date", DbType.String);
                        parameter.Value = date.ToString("yyyy-MM-dd HH:mm:ss");
                        command.Parameters.Add(parameter);

                        int rows = command.ExecuteNonQuery();
                        Logger.Info("ClearItemsBeforeDate before {0} cleared {1} rows.", date.ToString("yyyy-MM-dd HH:mm:ss"), rows);
                    }
                }
            }
            catch (SqliteException e)
            {
                Logger.Warn("SqliteException occured while clearing items before {0}: \n{1}", date, e);
            }
        }
Ejemplo n.º 21
0
        /**
         * Adds a new player to the player table
         * @param tempPlayer the player so far that we will add.
         * @param password the salted-hashes password the player used to login
         * @param salt the salt used to hash the password and used for encryption
         */
        public bool AddPlayer(Player tempPlayer, String password, String salt)
        {
            var command = new sqliteCommand("select * from " + playerTableName + " where name =:id", Database);

            command.Parameters.Add("id", DbType.String).Value = tempPlayer.PlayerName;
            var reader = command.ExecuteReader();

            if (reader.HasRows == false && !U.HasBadChars(tempPlayer.PlayerName) && !U.HasBadChars(password))
            {
                try
                {
                    command = new sqliteCommand("INSERT INTO " + playerTableName +
                                                " (name, password, salt,  rIndex) " +
                                                "VALUES ($n, $p, $s, $i) ", Database);

                    command.Parameters.Add("$n", DbType.String).Value = tempPlayer.PlayerName;
                    command.Parameters.Add("$p", DbType.String).Value = password;
                    command.Parameters.Add("$s", DbType.String).Value = salt;
                    command.Parameters.Add("$i", DbType.Int32).Value  = tempPlayer.RoomIndex;
                    command.ExecuteNonQuery();

                    ActivePlayers.Add(tempPlayer.PlayerName);
                    return(true);
                }
                catch (Exception ex)
                {
                    Console.WriteLine("Failed Adding to DB: " + ex);
                    return(false);
                }
            }
            else
            {
                return(false);
            }
        }
Ejemplo n.º 22
0
		public static int DoNonQuery (SqliteConnection connection, string command_text, string[] param_names, object[] param_args)
		{
			int ret = 0;

			using (SqliteCommand command = new SqliteCommand ()) {
				command.Connection = connection;
				command.CommandText = command_text;

				if (param_names != null) {
					if (param_args == null || param_names.Length != param_args.Length)
						throw new ArgumentException ("param_names, param_args", "param_names and param_args should have same number of items");
					for (int i = 0; i < param_names.Length; ++i)
						command.Parameters.AddWithValue (param_names [i], param_args [i]);
				}

				while (true) {
					try {
						ret = command.ExecuteNonQuery ();
						break;
					} catch (SqliteException e) {
						if (e.ErrorCode == SQLiteErrorCode.Busy) {
							Thread.Sleep (50);
						} else {
							throw;
						}
					} catch (Exception e) {
						Log.Error (e, "SQL that caused the exception: {0}", command_text);
						throw;
					}
				}
			}

			return ret;
		}
Ejemplo n.º 23
0
 //Update from V0 (empty) to V1
 private bool _UpdateV1()
 {
     if (_Connection == null || _Version >= 1)
         return false;
     using (SQLiteTransaction transaction = _Connection.BeginTransaction())
     {
         try
         {
             foreach (string file in _FilesV1)
             {
                 string filePath = Path.Combine(CSettings.ProgramFolder, file);
                 if (!_AddImageToCreditsDB(filePath, transaction))
                 {
                     transaction.Rollback();
                     return false;
                 }
             }
             using (SQLiteCommand command = new SQLiteCommand(_Connection))
             {
                 command.Transaction = transaction;
                 command.CommandText = "Update Version SET Value=@Value)";
                 command.Parameters.Add("@Value", DbType.Int32).Value = 1;
                 command.ExecuteNonQuery();
             }
             transaction.Commit();
         }
         catch (Exception)
         {
             transaction.Rollback();
             return false;
         }
     }
     return true;
 }
Ejemplo n.º 24
0
 public void NonQuery( string cmdstr )
 {
     using (var cmd = new SqliteCommand( con )) {
         cmd.CommandText = cmdstr;
         cmd.ExecuteNonQuery();
     }
 }
Ejemplo n.º 25
0
        public override bool addWorker(string fname, string lname, string village, string hnr, int plz, string email, string mobile, string tel, string street)
        {
            try
            {
                sqlite_cmd = sqlite_conn.CreateCommand ();
                sqlite_cmd.CommandText = "INSERT INTO tbl_worker (fname, lname, hnr, PLZ, village, email, mobile, telephone, street) VALUES ('"+fname+"', '"+lname+"','"+hnr+"','"+plz+"','"+village+"','"+email+"','"+mobile+"','"+tel+"','"+street+"')";
                sqlite_conn.Open();
                sqlite_cmd.ExecuteNonQuery();
                sqlite_conn.Close();

                int WorkerID = this.readWorkerID(fname,lname,village,hnr,email);

                if(WorkerID != 0)
                {
                    return true;
                }
                else
                {
                    return false;
                }
            }
            catch (Exception ex)
            {
                sqlite_conn.Close ();
                return false;
            }
        }
        public bool Delete(Guid presentationUID)
        {
            bool ret = false;

            if (presentationUID == Guid.Empty)
                return false;

            using (SqliteConnection conn = GetConnection())
            {
                using (SqliteCommand sqlCmd = new SqliteCommand(@"
                    BEGIN; " +
                    "DELETE FROM presentations " +
                    "WHERE PresentationUID=@PresentationUID;" +
                    "COMMIT;", conn))
                {
                    sqlCmd.Parameters.AddWithValue("@PresentationUID", presentationUID.ToString());

                    conn.Open();

                    try
                    {
                        sqlCmd.ExecuteNonQuery();
                        ret = true;
                    }
                    catch (SqliteException ex)
                    {
                        Logging.Log(this, Logging.LoggingTypeError, "SQL cmd: " + sqlCmd, ex);
                    }

                    conn.Close();
                }
            }

            return ret;
        }
Ejemplo n.º 27
0
        /**
         * Constructor will try and open the database if none exsists it will create one
         * In addition it will create new tables if non exsist.
         */
        public SqlWrapper(GameObjectList objectList)
        {
            gameObjectList = objectList;



            //try and open database, if failed make one!
            Database = new sqliteConnection("Data Source=Dungeon" + ";Version=3;FailIfMissing=True");
            try
            {
                Database.Open();
            }
            catch
            {
                Console.WriteLine("Open existing DB failed: So creating one");
                sqliteConnection.CreateFile("Dungeon");
                Database = new sqliteConnection("Data Source=Dungeon" + ";Version=3;FailIfMissing=True");
                Database.Open();
            }

            //do the disable roll back thingy
            //Not sure what this does, fixed a problem, found it on stack overflow
            String        disableRollback = "PRAGMA journal_mode = OFF";
            sqliteCommand cmd             = new sqliteCommand();

            cmd.Connection  = Database;
            cmd.CommandText = disableRollback;
            cmd.ExecuteNonQuery();

            //create tables
            new sqliteCommand(createTable + itemTableName + itemColumns, Database).ExecuteNonQuery();

            new sqliteCommand(createTable + playerTableName + playerColumns, Database).ExecuteNonQuery();
        }
Ejemplo n.º 28
0
        public void CommitTransaction()
        {
            var lst = new List<string>();
            using (var cmd = new SqliteCommand(String.Format("SELECT DISTINCT [TableName] FROM {0}", TranStatusTable), ActiveConnection))
            {
                using (SqliteDataReader r = cmd.ExecuteReader())
                {
                    while (r.Read())
                    {
                        lst.Add(r.GetString(0));
                    }
                }
            }

            SqliteTransaction tran = ActiveConnection.BeginTransaction();
            try
            {
                foreach (String tableName in lst)
                {
                    using (var cmd = new SqliteCommand(String.Format("DELETE FROM __{0}", tableName), tran.Connection, tran))
                        cmd.ExecuteNonQuery();
                }
                using (var cmd = new SqliteCommand(String.Format("DELETE FROM {0}", TranStatusTable), tran.Connection, tran))
                    cmd.ExecuteNonQuery();

                tran.Commit();
            }
            catch
            {
                tran.Rollback();
                throw;
            }
        }
Ejemplo n.º 29
0
		public void Create()
		{
			try {
				if(File.Exists(dbPath)) {
					cnn.Dispose();
					// We want to start with a fresh db for each full run
					// The database is created on the first open()
					File.Delete(dbPath);
				}
			}
			catch(Exception e) {
				throw e;
			}

			try {
				using (var createCommand = new SqliteCommand ("CREATE TABLE Company (RecordId int, Name text);", cnn))
				using (var insertCommand = new SqliteCommand ("INSERT INTO Company VALUES (1, 'Test CO')", cnn)) {
					cnn.Open();
					createCommand.ExecuteNonQuery();
					insertCommand.ExecuteNonQuery();
				}
			}
			catch(Exception e) {
				Console.WriteLine (e);
				throw new AssertionException ("Create table failed", e);
			}
			finally {
				cnn.Close();  
			}
		}
Ejemplo n.º 30
0
    public DBManager(string DatabaseName)
    {
        if (!File.Exists(DatabaseName))
        {
            SqliteConnection.CreateFile(DatabaseName);
        }

        dbConnection = new SqliteConnection("Data Source=" + DatabaseName + ";Version=3;");
        dbConnection.Open();
        cmd = dbConnection.CreateCommand();
        string checkForTable = "SELECT name FROM sqlite_master WHERE type='table' AND name='users';";

        cmd.CommandText = checkForTable;
        if (cmd.ExecuteNonQuery() == 0)
        {
            string create = "CREATE TABLE users (id INT PRIMARY KEY, name CHAR(50));";
            cmd             = dbConnection.CreateCommand();
            cmd.CommandText = create;
            try
            {
                cmd.ExecuteNonQuery();
            }
            catch
            {
                System.Diagnostics.Debug.WriteLine("Table already exists...");
            }
        }
        cmd = null;
    }
Ejemplo n.º 31
0
		private void MakeSureTableExists()
		{
			using (SqliteCommand sqc = new SqliteCommand(connection)) {
				sqc.CommandText = "CREATE TABLE IF NOT EXISTS `Files` (`id` INTEGER NOT NULL PRIMARY KEY, `path` TEXT NOT NULL, `changedate` INTEGER NOT NULL, `size` INTEGER NOT NULL, `hash` TEXT NOT NULL)";
				sqc.ExecuteNonQuery();
			}
		}
Ejemplo n.º 32
0
        protected int ExecuteNonQuery(SqliteCommand cmd)
        {
            try
            {
                lock (m_Connection)
                {
                    var newConnection =
                        (SqliteConnection)((ICloneable)m_Connection).Clone();
                    if (newConnection.State != ConnectionState.Open)
                        newConnection.Open();
                    cmd.Connection = newConnection;

                    return cmd.ExecuteNonQuery();
                }
            }
            catch (Mono.Data.Sqlite.SqliteException)
            {
                //m_log.Warn("[SQLiteDataManager]: Exception processing command: " + cmd.CommandText + ", Exception: " + ex);
                //throw ex;
            }
            catch (Exception ex)
            {
                m_log.Warn("[SQLiteDataManager]: Exception processing command: " + cmd.CommandText + ", Exception: " + ex);
                throw ex;
            }
            return 0;
        }
Ejemplo n.º 33
0
		public void DateTimeConvert ()
		{
			var dateTime = new DateTime (2016, 9, 15, 12, 1, 53);
			var guid = Guid.NewGuid ();

			using (var connection = new SqliteConnection ("Data Source=" + _databasePath)) {
				connection.Open ();

				var sqlCreate = "CREATE TABLE TestTable (ID uniqueidentifier PRIMARY KEY, Modified datetime)";
				using (var cmd = new SqliteCommand (sqlCreate, connection)) {
					cmd.ExecuteNonQuery ();
				}

				var sqlInsert = "INSERT INTO TestTable (ID, Modified) VALUES (@id, @mod)";
				using (var cmd = new SqliteCommand (sqlInsert, connection)) {
					cmd.Parameters.Add (new SqliteParameter ("@id", guid));
					cmd.Parameters.Add (new SqliteParameter ("@mod", dateTime));
					cmd.ExecuteNonQuery ();
				}
			}

			using (var connection = new SqliteConnection ("Data Source=" + _databasePath)) {
				connection.Open ();

				var sqlSelect = "SELECT * from TestTable";
				using (var cmd = new SqliteCommand (sqlSelect, connection))
				using (var reader = cmd.ExecuteReader ()) {
					while (reader.Read ()) {
						Assert.AreEqual (guid, reader.GetGuid (0), "#1");
						Assert.AreEqual (dateTime, reader.GetDateTime (1), "#2");
					}
				}
			}
		}
Ejemplo n.º 34
0
        public static void Main(string[] args)
        {
            var db = "../../stalkr.db";

            if (args.Length > 0) {
                db = args[0];
            }

            // COUCHSURFING

            var profiles = new Dictionary<int, string>();

            using (var conn = new SqliteConnection("Data Source=" + db + ";Version=3;")) {
                conn.Open();
                var sql = "SELECT * FROM person_profile WHERE profile_type = 'couchsurfing'";
                using (var cmd = new SqliteCommand(sql, conn)) {
                    var reader = cmd.ExecuteReader();

                    while (reader.Read()) {
                        int id = Convert.ToInt32(reader["person_id"]);
                        string profile = reader["profile"] as string;
                        profiles[id] = profile;
                    }
                }

                foreach (var profile in profiles) {
                    Console.WriteLine("{0}: {1}", profile.Key, profile.Value);

                    var name = "";
                    var age = 0;
                    var gender = "";

                    try {
                        var url = profile.Value; //"https://www.couchsurfing.com/people/casey";
                        var html = new HtmlDocument();
                        html.LoadHtml(new WebClient().DownloadString(url));

                        var root = html.DocumentNode;
                        name = root.SelectSingleNode("/html/body/main/div[2]/div/section/header/h1/a").InnerHtml;
                        var ageAndGender = root.SelectSingleNode("/html/body/main/div[2]/section[3]/div/div/div[1]/ul/li[3]").InnerText
                        .Split(new char[]{ ',' }, 2);
                        age = int.Parse(ageAndGender[0].Trim());
                        gender = ageAndGender[1].Trim();
                    } catch (Exception) {
                    }

                    Console.WriteLine(name);
                    Console.WriteLine(age);
                    Console.WriteLine(gender);

                    sql = string.Format("INSERT INTO fact_couchsurfing(person_id, timestamp, name, age, gender) VALUES ({0}, {1}, '{2}', {3}, '{4}')", profile.Key, "DATETIME('now')", name, age, gender);
                    using (var cmd = new SqliteCommand(sql, conn)) {
                        cmd.ExecuteNonQuery();
                    }

                    Thread.Sleep(1000);
                }
            }
        }
Ejemplo n.º 35
0
        //depending on what the client sent (1 or 2) add a username to databse or retreive a name from the database
        static bool username(string Key, Socket client)
        {
            int           bytesSent;
            ASCIIEncoding encoder = new ASCIIEncoding();
            sqliteCommand command;
            var           input = Key.Split(' ');

            switch (input[0])
            {
            case "1":
                for (int i = 1; i < input.Length; i++)
                {
                    name += input[i];
                }

                try
                {
                    var sql = "insert into " + "table_usernames" + " (name) values ";
                    sql    += "('" + name + "'";
                    sql    += ")";
                    command = new sqliteCommand(sql, conn);
                    command.ExecuteNonQuery();
                }

                catch (Exception ex)
                {
                    Console.WriteLine("Failed to add: " + name + " : to DB " + ex);
                }
                return(true);

            case "2":
                for (int i = 1; i < input.Length; i++)
                {
                    name += input[i];
                }

                try
                {
                    command = new sqliteCommand("select * from  table_usernames where name == '" + name + "'", conn);
                    var reader = command.ExecuteReader();


                    if (reader.HasRows == true)
                    {
                        byte[] sendBuffer = encoder.GetBytes("signed in");
                        bytesSent = client.Send(sendBuffer);
                    }
                }

                catch (Exception ex)
                {
                    Console.WriteLine("Failed to find: " + name + " : to DB " + ex);
                }
                return(true);
            }
            return(false);
        }
Ejemplo n.º 36
0
        static void clientReceiveThread(object obj)
        {
            ReceiveThreadLaunchInfo receiveInfo = obj as ReceiveThreadLaunchInfo;
            bool socketactive = true;

            socketToCharacter.Add(receiveInfo.socket, receiveInfo.clientCharacter);

            var sql = "insert into " + "table_characters" + " (name, room) values";

            sql += "('" + receiveInfo.clientCharacter.name + "'";
            sql += ",";
            sql += "'" + receiveInfo.clientCharacter.playerRoom + "'";
            sql += ")";
            SQLiteCommand command = new sqliteCommand(sql, connection);

            try
            {
                command.ExecuteNonQuery();
            }
            catch
            {
                Console.WriteLine("Failed to perform simple addition but still did it anyway.");
            }

            MudowRun.RoomInfo(receiveInfo.socket, connection, socketToCharacter);

            while ((active == true) && (socketactive == true))
            {
                byte[] buffer = new byte[4094];

                try
                {
                    int result = receiveInfo.socket.Receive(buffer);


                    if (result > 0)
                    {
                        ASCIIEncoding encoder = new ASCIIEncoding();

                        lock (incommingMessages)
                        {
                            string message = encoder.GetString(buffer, 0, result);

                            if (receiveInfo.clientCharacter.PlayerLoginDetails(receiveInfo.userState, message, connection) == false)
                            {
                                MudowRun.Process(receiveInfo.clientCharacter, message, receiveInfo.socket, socketToCharacter, connection);
                            }
                            MudowRun.RoomInfo(receiveInfo.socket, connection, socketToCharacter);
                        }
                    }
                }
                catch (System.Exception ex)
                {
                    socketactive = false;
                }
            }
        }
Ejemplo n.º 37
0
    public void AddUser(string username)
    {
        string insertuser = "******"" + username + "\");";

        //Console.WriteLine(insertuser);
        cmd             = dbConnection.CreateCommand();
        cmd.CommandText = insertuser;
        cmd.ExecuteNonQuery();
    }
        public void Create()
        {
#if NETFX_CORE
            try
            {
                try
                {
                    var file = Windows.Storage.ApplicationData.Current.LocalFolder.GetFileAsync(_uri).AsTask();
                    file.Wait();
                    _conn.Dispose();
                    // We want to start with a fresh db for each full run
                    // The database is created on the first open()
                    file.Result.DeleteAsync().AsTask().Wait();
                }
                catch (AggregateException ex)
                {
                    if (!(ex.InnerExceptions.Single() is FileNotFoundException))
                    {
                        throw;
                    }
                }
            }
            catch (Exception)
            {
                throw;
            }
#else
            using (var store = System.IO.IsolatedStorage.IsolatedStorageFile.GetUserStoreForApplication())
            {
                store.Remove();
            }
#endif

            try
            {
                using (SqliteCommand dropCommand = new SqliteCommand("DROP TABLE IF EXISTS T1;", _conn))
                using (SqliteCommand createCommand = new SqliteCommand("CREATE TABLE t1(t  TEXT,  f FLOAT, i INTEGER, b TEXT);", _conn))
                using (SqliteCommand insertCommand = new SqliteCommand("INSERT INTO t1  (t, f, i, b ) VALUES('" + stringvalue + "',123,123,'123')", _conn))
                {
                    _conn.Open();
                    dropCommand.ExecuteNonQuery();
                    createCommand.ExecuteNonQuery();
                    insertCommand.ExecuteNonQuery();
                }
            }
            catch (Exception e)
            {
                System.Diagnostics.Debug.WriteLine(e);
                //System.Diagnostics.Debug.WriteLine(e);
                throw new AssertFailedException("Create table failed", e);
            }
            finally
            {
                _conn.Close();
            }
        }
Ejemplo n.º 39
0
        protected override void OnLoad(EventArgs e)
        {
            base.OnLoad(e);

            DataTable dtArtists = new DataTable();

            #region fetch data for artists

            Mono.Data.Sqlite.SqliteConnection  cn      = new Mono.Data.Sqlite.SqliteConnection("library.sqlite");
            Mono.Data.Sqlite.SqliteCommand     comm    = new Mono.Data.Sqlite.SqliteCommand(cn);
            Mono.Data.Sqlite.SqliteDataAdapter adapter = new Mono.Data.Sqlite.SqliteDataAdapter(comm);
            comm.CommandText = @"
SELECT  name, id, fetched
FROM    artists
";
            adapter.Fill(dtArtists);

            #endregion

            if (dtArtists.Rows.Count == 0)
            {
                List <SubsonicItem> artists = Subsonic.GetIndexes();

                foreach (SubsonicItem artist in artists)
                {
                    DataRow dr = dtArtists.NewRow();
                    dr["name"]     = artist.name;
                    dr["id"]       = artist.id;
                    dr["feteched"] = DateTime.Now.ToString();
                    dtArtists.Rows.Add(dr);

                    comm             = new Mono.Data.Sqlite.SqliteCommand(cn);
                    comm.CommandText = @"
INSERT INTO artists (name, id, fetched)
VALUES(@name, @id, @fetched);
";
                    comm.Parameters.AddWithValue("@name", artist.name);
                    comm.Parameters.AddWithValue("@id", artist.id);
                    comm.Parameters.AddWithValue("@fetched", DateTime.Now.ToString());

                    if (cn.State != ConnectionState.Open)
                    {
                        cn.Open();
                    }
                    comm.ExecuteNonQuery();
                }

                if (cn.State != ConnectionState.Closed)
                {
                    cn.Close();
                }
            }

            rptArtists.DataSource = dtArtists;
            rptArtists.DataBind();
        }
Ejemplo n.º 40
0
        //////////////////////////////////////////////////////////////
        //
        // All non queries are funneled through one connection
        // to increase performance a little
        //
        protected int ExecuteNonQuery(SqliteCommand cmd)
        {
            using (SqliteConnection dbcon = new SqliteConnection(m_connectionString))
            {
                dbcon.Open();
                cmd.Connection = dbcon;

                return cmd.ExecuteNonQuery();
            }
        }
Ejemplo n.º 41
0
    public static void InitDB(string filename)
    {
        string cs = "URI=file:" + filename;

        using(SqliteConnection con = new SqliteConnection(cs))
        {
            con.Open();
            using(SqliteCommand cmd = new SqliteCommand(con))
            {
                cmd.CommandText = "DROP TABLE IF EXISTS Cable";
                cmd.ExecuteNonQuery();
                cmd.CommandText = @"CREATE TABLE Cable(
                    Id INTEGER PRIMARY KEY,
                       Name TEXT)";
                cmd.ExecuteNonQuery();
            }
            con.Close();
        }
    }
Ejemplo n.º 42
0
		public static void RunTests(string dbPath)
	    {
			using (var cnn = new SqliteConnection("Data Source=" + dbPath))
			{
				cnn.Open();

				// commit
				using (var trn = cnn.BeginTransaction(IsolationLevel.Serializable))
				using (var cmd = new SqliteCommand("CREATE TABLE IF NOT EXISTS nugettest (id INTEGER PRIMARY KEY AUTOINCREMENT, data TEXT);", cnn))
				{
					cmd.ExecuteNonQuery();

					cmd.CommandText = "DELETE FROM nugettest;";
					cmd.ExecuteNonQuery();

					cmd.CommandText = "INSERT INTO nugettest (data) VALUES (@someData);";
					cmd.Parameters.AddWithValue("@someData", "data here");

					cmd.ExecuteNonQuery();

					trn.Commit();
				}

				// rollback
				using (var trn = cnn.BeginTransaction(IsolationLevel.Serializable))
				using (var cmd = new SqliteCommand("INSERT INTO nugettest (data) VALUES (@someData);", cnn))
				{
					cmd.Parameters.AddWithValue("@someData", "data here");
					cmd.ExecuteNonQuery();

					trn.Rollback();
				}

				// check
				using (var cmd = new SqliteCommand("SELECT COUNT(*) nugettest;", cnn))
				{
					if (Convert.ToInt32(cmd.ExecuteScalar()) != 1)
					{
						throw new Exception("Something bad happened!");
					}
				}
			}
	    }
Ejemplo n.º 43
0
 //Called from initialize
 public static void Insert(string myName, string myValue, SqliteCommand mycmd)
 {
     //Sqlite.Open();
     mycmd.CommandText = "INSERT INTO " + Constants.PreferencesTable +
         " (name, value) VALUES (\"" +
         myName + "\", \"" + myValue + "\")" ;
     LogB.SQL(mycmd.CommandText.ToString());
     mycmd.ExecuteNonQuery();
     //Sqlite.Close();
 }
Ejemplo n.º 44
0
        /*
         * Sets the field with the int data for the entry where 'id' == id
         */
        public void setFieldFromID(int id, int data, String field)
        {
            sqliteCommand command = new sqliteCommand("update " + m_TableName + " set " + field + " = @data where id = @id", m_Connection);

            command.Parameters.Add("@id", System.Data.DbType.UInt32).Value    = id;
            command.Parameters.Add("@data", System.Data.DbType.UInt32).Value  = data;
            command.Parameters.Add("@field", System.Data.DbType.String).Value = field;

            command.ExecuteNonQuery();
        }
Ejemplo n.º 45
0
        /*
         * Sets the field with the String data for the entry where 'name' == name
         */
        public void setFieldFromName(String name, String data, String field)
        {
            sqliteCommand command = new sqliteCommand("update " + m_TableName + " set " + field + " = @data where name = @name", m_Connection);

            command.Parameters.Add("@data", System.Data.DbType.String).Value  = data;
            command.Parameters.Add("@name", System.Data.DbType.String).Value  = name;
            command.Parameters.Add("@field", System.Data.DbType.String).Value = field;

            command.ExecuteNonQuery();
        }
Ejemplo n.º 46
0
		public void WrongSyntax()
		{
			SqliteCommand insertCmd = new SqliteCommand("INSERT INTO t1 VALUES (,')",_conn);
			using(_conn)
			{
				_conn.Open();
				int res = insertCmd.ExecuteNonQuery();
				Assert.AreEqual(res,1);
			}
		}
Ejemplo n.º 47
0
		public void deleteCert(){
			using (SqliteConnection con = new SqliteConnection ("Data Source=" + firstSetup.mainDir + "/" + mainWindow.selectedCA + "-ca/certsdb/certDB.sqlite")) {
				con.Open ();
				string stm = "DELETE FROM certs WHERE certNr = '" + certNum + "'";
				using (SqliteCommand cmd = new SqliteCommand (stm, con)) {
					cmd.ExecuteNonQuery ();
				}
				con.Close ();
			}
			caHandling.callProc ("/bin/rm", certPath, "Cert: " + certNum + "removed");
		}
Ejemplo n.º 48
0
 public void Delete( string buildid )
 {
     var sql = "DELETE FROM results WHERE BuildId = :BUILD";
     using (var conn = Connect())
     using (var cmd = new SqliteCommand(conn))
     {
         cmd.Parameters.Add(new SqliteParameter(":BUILD", buildid));
         cmd.CommandText = sql;
         cmd.ExecuteNonQuery();
     }
 }
Ejemplo n.º 49
0
 public void Delete( TestProject project )
 {
     var sql = "DELETE FROM results WHERE Project = :PROJ";
     using (var conn = Connect())
     using (var cmd = new SqliteCommand(conn))
     {
         cmd.Parameters.Add(new SqliteParameter(":PROJ", project.Project));
         cmd.CommandText = sql;
         cmd.ExecuteNonQuery();
     }
 }
Ejemplo n.º 50
0
 string INoteRepository.UpdateTitle(INoteCore note, string title)
 {
     var command = "UPDATE Notes SET Name=@name WHERE ID=@id";
     using (var cmd = new SqliteCommand (database.Connection)) {
         cmd.CommandText = command;
         cmd.Parameters.AddWithValue ("@name", title);
         cmd.Parameters.AddIdParameter (note);
         cmd.ExecuteNonQuery ();
     }
     return title;
 }
Ejemplo n.º 51
0
        public SQLTable(sqliteConnection connection, String tableName, String tableColumns, bool createNew)
        {
            m_Connection   = connection;
            m_TableName    = tableName;
            m_TableColumns = tableColumns;

            if (createNew)
            {
                sqliteCommand command = new sqliteCommand("create table " + m_TableName + " (" + m_TableColumns + ")", m_Connection);

                command.Parameters.Add("@name", System.Data.DbType.String).Value = m_TableName;
                command.ExecuteNonQuery();
            }
        }
Ejemplo n.º 52
0
    public static void ModifyPlayerWinCount(uint ID, int value)
    {
        for (int i = 0; i < players.Count; i++)
        {
            if (players [i].ID == ID)
            {
                //Confirm log in credentials through database
                string   databasePath = "";
                Platform platform     = RunningPlatform();
                if (platform == Platform.Windows)
                {
                    databasePath = "Data Source=" + System.Environment.CurrentDirectory + "\\database.db; FailIfMissing=True";
                }
                else if (platform == Platform.Mac)
                {
                    databasePath = "Data Source=" + System.Environment.CurrentDirectory + "/data/database.db; FailIfMissing=True";
                }

                SQLiteConnection db_connection = new SQLiteConnection(databasePath);
                db_connection.Open();

                string        query   = "select * from Users where ID = @id";
                SQLiteCommand command = new SQLiteCommand(query, db_connection);
                command.Parameters.AddWithValue("@id", players[i].ID);

                SQLiteDataReader reader = command.ExecuteReader();

                if (reader.HasRows)
                {
                    while (reader.Read())
                    {
                        uint currentWinCount = Convert.ToUInt32(reader ["WINCOUNT"]);
                        players [i] = SetPlayerWinCount(players [i], currentWinCount + 1);

                        string sql = "update Users set WINCOUNT = @wincount where ID = @id";
                        command = new SQLiteCommand(sql, db_connection);
                        command.Parameters.AddWithValue("@wincount", currentWinCount + 1);
                        command.Parameters.AddWithValue("@id", players[i].ID);

                        command.ExecuteNonQuery();
                    }
                }

                db_connection.Close();
                break;
            }
        }
    }
Ejemplo n.º 53
0
        public static void log_Event(LOGEventTypes let, Player player, Vob interact, String message)
        {
            if (player.getAccount() == null || !player.IsSpawned())
            {
                return;
            }

            long  accountID   = player.getAccount().accountID;
            long  interactID  = 0;
            short lastPing    = (short)player.LastPing;
            short averagePing = (short)player.AveragePing;
            long  now         = DateTime.Now.Ticks;

            if (message == null)
            {
                message = "";
            }

            if (interact != null && !(interact is Player))
            {
                message += "Interact-Vob: " + interact.ID + ": " + interact.Visual + ", " + interact;
            }
            else if (interact != null && interact is Player && ((Player)interact).getAccount() != null)
            {
                interactID = ((Player)interact).getAccount().accountID;
            }


            lock (connection)
            {
                using (SQLiteCommand command = new SQLiteCommand(connection))
                {
                    command.CommandText  = "INSERT INTO `logEvents` (";
                    command.CommandText += "  `id`, `accountID`, `InteractAccountID`, `type`, `lastPing`, `averagePing`, `time`, `message`)";
                    command.CommandText += "VALUES( NULL, @accID, @interID, @type, @lastPing, @aPing, @time, @message)";

                    command.Parameters.AddWithValue("@accID", accountID);
                    command.Parameters.AddWithValue("@interID", interactID);
                    command.Parameters.AddWithValue("@type", (int)let);
                    command.Parameters.AddWithValue("@lastPing", lastPing);
                    command.Parameters.AddWithValue("@aPing", averagePing);
                    command.Parameters.AddWithValue("@time", now);
                    command.Parameters.AddWithValue("@message", message);

                    command.ExecuteNonQuery();
                }
            }
        }
Ejemplo n.º 54
0
        public void AddIDEntry(string name, int id)
        {
            try
            {
                sqliteCommand command = new sqliteCommand("insert into " + m_TableName + " values (@name, @id)", m_Connection);

                command.Parameters.Add("@name", System.Data.DbType.String).Value = name;
                command.Parameters.Add("@id", System.Data.DbType.Int32).Value    = id;

                command.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                Console.WriteLine("Failed to add: " + name + " to DB " + ex);
            }
        }
Ejemplo n.º 55
0
        /**
         * Adds grafiti to a room
         * @param graf the message to write to the wall
         * @param roomindex the index of the room to write too
         */
        public void AddGrafiti(String graf, int roomIndex)
        {
            var command = new sqliteCommand(Database);

            command.CommandText = "update " + dungeonTableName + " set grafiti = :graf where rIndex=:id";
            command.Parameters.Add("graf", DbType.String).Value = graf;
            command.Parameters.Add("id", DbType.Int32).Value    = roomIndex.ToString();
            try
            {
                command.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                Console.WriteLine("failed to write grafiti: " + graf + ex);
            }
        }
Ejemplo n.º 56
0
        public override void AddEntry(string[] dataArray)
        {
            try
            {
                sqliteCommand command = new sqliteCommand("insert into " + m_TableName + " values (@name, @id, @securityLevel)", m_Connection);

                command.Parameters.Add("@name", System.Data.DbType.String).Value          = dataArray[0];
                command.Parameters.Add("@id", System.Data.DbType.UInt32).Value            = dataArray[1];
                command.Parameters.Add("@securityLevel", System.Data.DbType.UInt32).Value = dataArray[2];

                command.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                Console.WriteLine("Failed to add: " + dataArray[0] + " to DB " + ex);
            }
        }
Ejemplo n.º 57
0
        /**
         * Update the database to players move.
         * @param player the player that has moved
         */
        public string UpdatePlayerPos(Player player)
        {
            var command = new sqliteCommand(Database);

            command.CommandText = "update " + playerTableName + " set rIndex = :i where name=:id";
            command.Parameters.Add("i", DbType.Int32).Value   = player.RoomIndex;
            command.Parameters.Add("id", DbType.String).Value = player.PlayerName;
            try
            {
                command.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                Console.WriteLine("failed to write player: " + player.PlayerName + ex);
            }
            return(GetRoomDescrption(player.RoomIndex));
        }
Ejemplo n.º 58
0
        private int _GetDataBaseSongID(string artist, string title, int defNumPlayed, SQLiteCommand command)
        {
            command.CommandText = "SELECT id FROM Songs WHERE [Title] = @title AND [Artist] = @artist";
            command.Parameters.Add("@title", DbType.String, 0).Value  = title;
            command.Parameters.Add("@artist", DbType.String, 0).Value = artist;

            SQLiteDataReader reader = command.ExecuteReader();

            if (reader != null && reader.HasRows)
            {
                reader.Read();
                int id = reader.GetInt32(0);
                reader.Dispose();
                return(id);
            }

            if (reader != null)
            {
                reader.Close();
            }

            command.CommandText = "INSERT INTO Songs (Title, Artist, NumPlayed, DateAdded) " +
                                  "VALUES (@title, @artist, @numplayed, @dateadded)";
            command.Parameters.Add("@title", DbType.String, 0).Value    = title;
            command.Parameters.Add("@artist", DbType.String, 0).Value   = artist;
            command.Parameters.Add("@numplayed", DbType.Int32, 0).Value = defNumPlayed;
            command.Parameters.Add("@dateadded", DbType.Int64, 0).Value = DateTime.Now.Ticks;
            command.ExecuteNonQuery();

            command.CommandText = "SELECT id FROM Songs WHERE [Title] = @title AND [Artist] = @artist";
            command.Parameters.Add("@title", DbType.String, 0).Value  = title;
            command.Parameters.Add("@artist", DbType.String, 0).Value = artist;

            reader = command.ExecuteReader();

            if (reader != null)
            {
                reader.Read();
                int id = reader.GetInt32(0);
                reader.Dispose();
                return(id);
            }

            return(-1);
        }
Ejemplo n.º 59
0
        public void UpdatePassword(string[] dataArray)
        {
            try
            {
                sqliteCommand command = new sqliteCommand("update " + m_TableName + " set passwordHash = @password, passwordSalt = @salt, passwordRenewalDate = @passwordRenewalDate where name = @name", m_Connection);

                command.Parameters.Add("@name", System.Data.DbType.String).Value                = dataArray[0];
                command.Parameters.Add("@password", System.Data.DbType.String).Value            = dataArray[1];
                command.Parameters.Add("@salt", System.Data.DbType.String).Value                = dataArray[2];
                command.Parameters.Add("@passwordRenewalDate", System.Data.DbType.String).Value = dataArray[3];

                command.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                Console.WriteLine("Failed to change password for: " + dataArray[0] + " : " + ex);
            }
        }
Ejemplo n.º 60
0
        /**
         * This function actually changes owner of an item.
         * @param itemID the unique ID of the item in question
         * @param owner the name of the new owner of the item.
         */
        public bool ChangeItemOwner(String itemID, String owner)
        {
            var command = new sqliteCommand(Database);

            command.CommandText = "update " + itemTableName + " set owner =:own where uniqueID=:item";
            command.Parameters.Add("own", DbType.String).Value  = owner;
            command.Parameters.Add("item", DbType.String).Value = itemID;
            try
            {
                command.ExecuteNonQuery();
                return(true);
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex);
                return(false);
            }
        }