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();
}
}
}
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);
}
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();
}
}
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();
}
}
}
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;
}
/// <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();
}
}
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
}
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);
}
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
}
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();
}
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
}
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();
}
}
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;
}
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);
}
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();
}
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();
}
}
}
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();
}
}
}
public static void executeQuery(string sql)
{
myConnection.Open();
SqliteCommand com = new SqliteCommand(sql, myConnection);
com.ExecuteNonQuery();
myConnection.Close();
}
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);
}
}
/**
* 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);
}
}
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;
}
//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;
}
public void NonQuery( string cmdstr )
{
using (var cmd = new SqliteCommand( con )) {
cmd.CommandText = cmdstr;
cmd.ExecuteNonQuery();
}
}
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;
}
/**
* 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();
}
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;
}
}
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();
}
}
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;
}
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();
}
}
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;
}
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");
}
}
}
}
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);
}
}
}
//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);
}
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;
}
}
}
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();
}
}
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();
}
//////////////////////////////////////////////////////////////
//
// 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();
}
}
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();
}
}
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!");
}
}
}
}
//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();
}
/*
* 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();
}
/*
* 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();
}
public void WrongSyntax()
{
SqliteCommand insertCmd = new SqliteCommand("INSERT INTO t1 VALUES (,')",_conn);
using(_conn)
{
_conn.Open();
int res = insertCmd.ExecuteNonQuery();
Assert.AreEqual(res,1);
}
}
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");
}
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();
}
}
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();
}
}
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;
}
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();
}
}
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;
}
}
}
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();
}
}
}
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);
}
}
/**
* 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);
}
}
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);
}
}
/**
* 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));
}
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);
}
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);
}
}
/**
* 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);
}
}
