internal void Sync(long rowid) { IsValid = false; _command.Parameters[0].Value = rowid; _reader = _command.ExecuteReader(); _reader.Read(); }
public void CloseSqlConnection() { if (dbCommand != null) { dbCommand.Dispose (); } dbCommand = null; if (reader != null) { reader.Dispose (); } reader = null; if (dbConnection != null) { dbConnection.Close (); } dbConnection = null; Debug.Log ("Disconnected from db."); }
static int get_Item(IntPtr L) { try { int count = LuaDLL.lua_gettop(L); if (count == 2 && TypeChecker.CheckTypes(L, 1, typeof(Mono.Data.Sqlite.SqliteDataReader), typeof(int))) { Mono.Data.Sqlite.SqliteDataReader obj = (Mono.Data.Sqlite.SqliteDataReader)ToLua.ToObject(L, 1); int arg0 = (int)LuaDLL.lua_tonumber(L, 2); object o = obj[arg0]; ToLua.Push(L, o); return(1); } else if (count == 2 && TypeChecker.CheckTypes(L, 1, typeof(Mono.Data.Sqlite.SqliteDataReader), typeof(string))) { Mono.Data.Sqlite.SqliteDataReader obj = (Mono.Data.Sqlite.SqliteDataReader)ToLua.ToObject(L, 1); string arg0 = ToLua.ToString(L, 2); object o = obj[arg0]; ToLua.Push(L, o); return(1); } else { return(LuaDLL.luaL_throw(L, "invalid arguments to method: Mono.Data.Sqlite.SqliteDataReader.get_Item")); } } catch (Exception e) { return(LuaDLL.toluaL_exception(L, e)); } }
static public SqliteDataReader ExecuteQuery(string sqlQuery) { dbCommand = OpenDB("monsters.db").CreateCommand(); dbCommand.CommandText = sqlQuery; reader = dbCommand.ExecuteReader(); return reader; }
public void InsertMonsters() { string[] nombres = SaveMonster.GetMonsterList(); Monstruo temp; if (PlayerPrefs.GetString ("botonPresionado") == "new") { DeleteMonsters(); } for(int i = 0;i < nombres.Length;++i){ temp = SaveMonster.LoadMonster(nombres[i]); _query="SELECT * FROM tablaMonstruos WHERE owner='PEPE' and name='"+temp.nombre+"'"; _command = _conexion.CreateCommand (); _command.CommandText = _query; _reader = _command.ExecuteReader (); int cont=0; if(_reader != null){ while(_reader.Read()){ cont++; } if(cont!=0){ _query= "UPDATE tablaMonstruos set specie='"+temp.especie+"',exp='"+temp.exp.ToString()+"',modStats='"+temp.modStats.ToString()+"',estado='"+temp.estado.ToString()+"' WHERE owner='PEPE' and name='"+temp.nombre+"'"; }else{ _query = "INSERT INTO tablaMonstruos VALUES('"+temp.nombre+"','"+temp.especie+"','"+temp.exp.ToString()+"','"+temp.modStats.ToString()+"','"+temp.estado.ToString()+"','PEPE')"; } } _command = _conexion.CreateCommand(); _command.CommandText = _query; _command.ExecuteReader(); } }
/// <summary>Convert from DataReader to Task object</summary> TodoItem FromReader (SqliteDataReader r) { var t = new TodoItem(); t.ID = Convert.ToInt32 (r ["_id"]); t.Name = r ["Name"].ToString (); t.Notes = r ["Notes"].ToString (); t.Done = Convert.ToInt32 (r ["Done"]) == 1 ? true : false; return t; }
MatchingTable CreateMatchingTable(SqliteDataReader reader) { return new MatchingTable( reader["id"].ToString(), reader["host"].ToString(), reader["guest"].ToString() ); }
/// <summary> /// Decode the specified sdr. /// </summary> /// <param name="sdr">Sdr.</param> public override void Decode (SqliteDataReader sdr) { ID = System.Convert.ToInt32(sdr ["ID"]); Name = System.Convert.ToString(sdr ["Name"]); Motion = System.Convert.ToString(sdr ["Motion"]); MotionTransition= System.Convert.ToSingle(sdr ["MotionTransition"]); MotionSpeed = System.Convert.ToSingle(sdr ["MotionSpeed"]); }
public SqliteDataReader ExecuteQuery(string q) { databaseCommand = databaseConnection.CreateCommand(); databaseCommand.CommandText = q; reader = databaseCommand.ExecuteReader(); return reader; }
/// <summary> /// Decode the specified sdr. /// </summary> /// <param name="sdr">Sdr.</param> public override void Decode (SqliteDataReader sdr) { ID = System.Convert.ToInt32(sdr ["ID"]); Name = System.Convert.ToString(sdr ["Name"]); ShapeID = System.Convert.ToInt32(sdr ["ShapeID"]); Weapon = System.Convert.ToString(sdr ["Weapon"]); Describe = System.Convert.ToString(sdr ["Describe"]); }
public void CloseDB() { _reader.Close (); _reader = null; _command = null; _conexion.Close (); _conexion = null; }
/// <summary> /// DBFill /// </summary> /// <param name="reader"></param> /// <param name="slide"></param> public static void DBFill(SqliteDataReader reader, Slide slide) { slide.SlideID = Convert.ToInt32(reader["slide_id"]); slide.SlideText = HttpUtility.UrlDecode(reader["slide_text"].ToString(), System.Text.Encoding.Default); slide.SlideIndex = Convert.ToInt32(reader["slide_index"]); slide.PhotoID = Convert.ToInt32(reader["photo_id"]); slide.Photo = Photo.Get(slide.PhotoID); }
/// <summary> /// Decode the specified sdr. /// </summary> /// <param name="sdr">Sdr.</param> public override void Decode (SqliteDataReader sdr) { ID = System.Convert.ToInt32(sdr ["ID"]); Name = System.Convert.ToString(sdr ["Name"]); ShapeID = System.Convert.ToInt32(sdr ["Shape"]); Level = System.Convert.ToInt32(sdr ["Level"]); Blueprint = System.Convert.ToString(sdr ["AI"]); }
public BlockData(SqliteDataReader reader) { ID = reader.GetInt32(0); File = reader.GetString(1); Width = reader.GetInt32(2); Height = reader.GetInt32(3); AvailableWidth = reader.GetInt32(4); AvailableHeight = reader.GetInt32(5); }
internal HyenaSqliteArrayDataReader (SqliteDataReader reader) { rows = new List<object[]> (); column_names = new Dictionary<string, int> (); closed = false; current_row = -1; ReadAllRows (reader); reader.Dispose (); }
/// <summary>Convert from DataReader to Task object</summary> private TodoItem FromReader (SqliteDataReader r) { var t = new TodoItem { ID = Convert.ToInt32 (r["_id"]), Name = r["Name"].ToString (), Notes = r["Notes"].ToString (), Done = (Convert.ToInt32 (r["Done"]) == 1) }; return t; }
/// <summary> /// Decode the specified sdr. /// </summary> /// <param name="sdr">Sdr.</param> public override void Decode (SqliteDataReader sdr) { ID = System.Convert.ToInt32(sdr ["ID"]); Name = System.Convert.ToString(sdr ["Name"]); ShapeID = System.Convert.ToInt32(sdr ["ShapeID"]); State = System.Convert.ToInt32(sdr ["State"]); Blueprint = System.Convert.ToString(sdr ["AI"]); Describe = System.Convert.ToString(sdr ["Describe"]); }
/// <summary> /// Inicializa uma nova instancia da classe <see cref="Spartacus.Database.Sqlite"/>. /// </summary> /// <param name='p_file'> /// Caminho para o arquivo DB. /// </param> public Sqlite(string p_file) : base(p_file) { this.v_connectionstring = "Data Source=" + p_file + ";Version=3;Synchronous=Full;Journal Mode=Off;"; this.v_con = null; this.v_cmd = null; this.v_reader = null; }
/// <summary> /// Decode the specified sdr. /// </summary> /// <param name="sdr">Sdr.</param> public override void Decode (SqliteDataReader sdr) { ID = System.Convert.ToInt32(sdr ["id"]); OffsetPosition = MathfEx.ToVector3(System.Convert.ToString(sdr ["lockPosition"])); OffsetPositionLerp = System.Convert.ToSingle(sdr ["lockPositionLerp"]); OffsetEulerAngles = MathfEx.ToVector3(System.Convert.ToString(sdr ["lockRotation"])); OffsetEulerAnglesLerp = System.Convert.ToSingle(sdr ["lockRotationLerp"]); FieldOfView = System.Convert.ToSingle(sdr ["fieldOfView"]); IsRelative = System.Convert.ToInt32 (sdr ["Relative"]) != 0 ? true : false; }
/// <summary> /// Decode the specified sdr. /// </summary> /// <param name="sdr">Sdr.</param> public override void Decode (SqliteDataReader sdr) { ID = System.Convert.ToInt32(sdr["ID"]); Name = System.Convert.ToString(sdr["Name"]); Url = System.Convert.ToString(sdr["Url"]); Type = System.Convert.ToInt32(sdr["Type"]); Part = System.Convert.ToInt32(sdr["PartType"]); Icon = System.Convert.ToString(sdr["Icon"]); ExtendUrl = System.Convert.ToString(sdr["ExtendUrl"]); }
/// <summary> /// Decode the specified sdr. /// </summary> /// <param name="sdr">Sdr.</param> public override void Decode (SqliteDataReader sdr) { ID = System.Convert.ToInt32(sdr ["ID"]); Name = System.Convert.ToString(sdr ["Name"]); SelfBuffID = System.Convert.ToInt32(sdr ["SelfBuffID"]); TargetBuffID = System.Convert.ToInt32(sdr ["TargetBuffID"]); SelfActionID = System.Convert.ToInt32(sdr ["SelfActionID"]); TargetActionID = System.Convert.ToInt32(sdr ["TargetActionID"]); AttackDistance = System.Convert.ToSingle(sdr ["AttackDistance"]); }
private static TaskItem ReadTask(SqliteDataReader reader) { TaskItem task = new TaskItem { Id = Convert.ToInt32(reader["Id"]), Name = Convert.ToString(reader["Name"]), IsComplete = Convert.ToBoolean(reader["IsComplete"]), }; return task; }
/// <summary> /// Decode the specified sdr. /// </summary> /// <param name="sdr">Sdr.</param> public override void Decode (SqliteDataReader sdr) { ID = System.Convert.ToInt32(sdr ["ID"]); Name = System.Convert.ToString(sdr ["Name"]); AnimationID = System.Convert.ToInt32(sdr ["AnimationID"]); DecodeEffect( System.Convert.ToString(sdr ["Effect"]) ); }
/// <summary> /// Decode the specified sdr. /// </summary> /// <param name="sdr">Sdr.</param> public override void Decode (SqliteDataReader sdr) { Version = System.Convert.ToString(sdr ["Version"]); UserName = System.Convert.ToString(sdr ["UserName"]); Password = System.Convert.ToString(sdr ["Password"]); MainScript = System.Convert.ToString(sdr ["MainScript"]); ServerVersion = System.Convert.ToInt32(sdr ["ServerVersion"]); ServerID = System.Convert.ToInt32(sdr ["ServerID"]); IPAddress = System.Convert.ToString(sdr ["IPAddress"]); Port = System.Convert.ToInt32(sdr ["Port"]); }
public void CloseDatabase() { if (reader != null) { reader.Close(); reader = null; } databaseCommand = null; databaseConnection.Close(); databaseConnection = null; }
/// <summary> /// Decode the specified sdr. /// </summary> /// <param name="sdr">Sdr.</param> public override void Decode (SqliteDataReader sdr) { ID = System.Convert.ToInt32(sdr["ID"]); Name = System.Convert.ToString(sdr["Name"]); Url = System.Convert.ToString(sdr["Url"]); Stream = System.Convert.ToInt32(sdr["Stream"]) != 0; Script = System.Convert.ToString(sdr["Script"]); Describe = System.Convert.ToString(sdr["Describe"]); Type = (SceneType)System.Convert.ToInt32(sdr["Type"]); Born = MathfEx.ToVector3(System.Convert.ToString(sdr["Born"])); CameraID = System.Convert.ToInt32(sdr["CameraID"]); }
/// <summary> /// Decode the specified sdr. /// </summary> /// <param name="sdr">Sdr.</param> public override void Decode (SqliteDataReader sdr) { ID = System.Convert.ToInt32(sdr ["ID"]); Name = System.Convert.ToString(sdr ["Name"]); Icon = System.Convert.ToString(sdr ["Icon"]); Level = System.Convert.ToInt32(sdr ["Level"]); State = System.Convert.ToInt32(sdr ["State"]); Area = System.Convert.ToInt32(sdr ["Area"]); Observer = System.Convert.ToString(sdr ["Observer"]); EnableMenu = System.Convert.ToInt32(sdr ["EnableMenu"]) != 0; EnableJoystick = System.Convert.ToInt32(sdr ["EnableJoystick"]) != 0; }
static int Close(IntPtr L) { try { ToLua.CheckArgsCount(L, 1); Mono.Data.Sqlite.SqliteDataReader obj = (Mono.Data.Sqlite.SqliteDataReader)ToLua.CheckObject(L, 1, typeof(Mono.Data.Sqlite.SqliteDataReader)); obj.Close(); return(0); } catch (Exception e) { return(LuaDLL.toluaL_exception(L, e)); } }
static int GetEnumerator(IntPtr L) { try { ToLua.CheckArgsCount(L, 1); Mono.Data.Sqlite.SqliteDataReader obj = (Mono.Data.Sqlite.SqliteDataReader)ToLua.CheckObject(L, 1, typeof(Mono.Data.Sqlite.SqliteDataReader)); System.Collections.IEnumerator o = obj.GetEnumerator(); ToLua.Push(L, o); return(1); } catch (Exception e) { return(LuaDLL.toluaL_exception(L, e)); } }
static int GetSchemaTable(IntPtr L) { try { ToLua.CheckArgsCount(L, 1); Mono.Data.Sqlite.SqliteDataReader obj = (Mono.Data.Sqlite.SqliteDataReader)ToLua.CheckObject(L, 1, typeof(Mono.Data.Sqlite.SqliteDataReader)); System.Data.DataTable o = obj.GetSchemaTable(); ToLua.PushObject(L, o); return(1); } catch (Exception e) { return(LuaDLL.toluaL_exception(L, e)); } }
// Close database after use public void CloseDB() { if (reader != null) { reader.Close(); } reader = null; if (dbcmd != null) { dbcmd.Cancel(); } dbcmd = null; if (dbcon != null) { dbcon.Close(); } dbcon = null; }
/// <summary>Convert from DataReader to Task object</summary> StepEntry FromReader (SqliteDataReader r) { var t = new StepEntry (); t.ID = Convert.ToInt32 (r ["_id"]); t.Steps = Convert.ToInt64(r ["Steps"]); var date = r ["Date"].ToString (); var culture = CultureInfo.CreateSpecificCulture("en-US"); var styles = DateTimeStyles.None; DateTime dateOut; if (!DateTime.TryParse (date, culture, styles, out dateOut)) { //back compat, but will never come in here really. DateTime.TryParse (date, out dateOut); } t.Date = dateOut; return t; }
public SqliteDataReader ExecuteQuery(string sqlQuery) { try { dbCommand = dbConnection.CreateCommand(); dbCommand.CommandText = sqlQuery; reader = dbCommand.ExecuteReader(); return reader; } catch (Exception e) { SqlErr = e.Message; return reader; } }
static int Read(IntPtr L) { try { ToLua.CheckArgsCount(L, 1); Mono.Data.Sqlite.SqliteDataReader obj = (Mono.Data.Sqlite.SqliteDataReader)ToLua.CheckObject(L, 1, typeof(Mono.Data.Sqlite.SqliteDataReader)); bool o = obj.Read(); LuaDLL.lua_pushboolean(L, o); return(1); } catch (Exception e) { return(LuaDLL.toluaL_exception(L, e)); } }
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; } } }
static int ExecuteQuery(IntPtr L) { try { ToLua.CheckArgsCount(L, 2); SqliteDbHelper obj = (SqliteDbHelper)ToLua.CheckObject(L, 1, typeof(SqliteDbHelper)); string arg0 = ToLua.CheckString(L, 2); Mono.Data.Sqlite.SqliteDataReader o = obj.ExecuteQuery(arg0); ToLua.PushObject(L, o); return(1); } catch (Exception e) { return(LuaDLL.toluaL_exception(L, e)); } }
static int GetValue(IntPtr L) { try { ToLua.CheckArgsCount(L, 2); Mono.Data.Sqlite.SqliteDataReader obj = (Mono.Data.Sqlite.SqliteDataReader)ToLua.CheckObject(L, 1, typeof(Mono.Data.Sqlite.SqliteDataReader)); int arg0 = (int)LuaDLL.luaL_checknumber(L, 2); object o = obj.GetValue(arg0); ToLua.Push(L, o); return(1); } catch (Exception e) { return(LuaDLL.toluaL_exception(L, e)); } }
static int GetValues(IntPtr L) { try { ToLua.CheckArgsCount(L, 2); Mono.Data.Sqlite.SqliteDataReader obj = (Mono.Data.Sqlite.SqliteDataReader)ToLua.CheckObject(L, 1, typeof(Mono.Data.Sqlite.SqliteDataReader)); object[] arg0 = ToLua.CheckObjectArray(L, 2); int o = obj.GetValues(arg0); LuaDLL.lua_pushinteger(L, o); return(1); } catch (Exception e) { return(LuaDLL.toluaL_exception(L, e)); } }
static int IsDBNull(IntPtr L) { try { ToLua.CheckArgsCount(L, 2); Mono.Data.Sqlite.SqliteDataReader obj = (Mono.Data.Sqlite.SqliteDataReader)ToLua.CheckObject(L, 1, typeof(Mono.Data.Sqlite.SqliteDataReader)); int arg0 = (int)LuaDLL.luaL_checknumber(L, 2); bool o = obj.IsDBNull(arg0); LuaDLL.lua_pushboolean(L, o); return(1); } catch (Exception e) { return(LuaDLL.toluaL_exception(L, e)); } }
static int InsertInto(IntPtr L) { try { ToLua.CheckArgsCount(L, 3); SqliteDbHelper obj = (SqliteDbHelper)ToLua.CheckObject(L, 1, typeof(SqliteDbHelper)); string arg0 = ToLua.CheckString(L, 2); string[] arg1 = ToLua.CheckStringArray(L, 3); Mono.Data.Sqlite.SqliteDataReader o = obj.InsertInto(arg0, arg1); ToLua.PushObject(L, o); return(1); } catch (Exception e) { return(LuaDLL.toluaL_exception(L, e)); } }
/// <summary> /// 转换实体 /// </summary> /// <param name="read">OleDbDataReader</param> /// <returns>TermInfo</returns> private static List<StatisticsInfo> DataReaderToList(SqliteDataReader read) { List<StatisticsInfo> list = new List<StatisticsInfo>(); while (read.Read()) { StatisticsInfo _site = new StatisticsInfo(); _site.PostCount = Convert.ToInt32(read["PostCount"]); _site.CommentCount = Convert.ToInt32(read["CommentCount"]); _site.VisitCount = Convert.ToInt32(read["VisitCount"]); _site.TagCount = Convert.ToInt32(read["TagCount"]); list.Add(_site); } read.Close(); return list; }
static int get_RecordsAffected(IntPtr L) { object o = null; try { o = ToLua.ToObject(L, 1); Mono.Data.Sqlite.SqliteDataReader obj = (Mono.Data.Sqlite.SqliteDataReader)o; int ret = obj.RecordsAffected; LuaDLL.lua_pushinteger(L, ret); return(1); } catch (Exception e) { return(LuaDLL.toluaL_exception(L, e, o == null ? "attempt to index RecordsAffected on a nil value" : e.Message)); } }
static int get_IsClosed(IntPtr L) { object o = null; try { o = ToLua.ToObject(L, 1); Mono.Data.Sqlite.SqliteDataReader obj = (Mono.Data.Sqlite.SqliteDataReader)o; bool ret = obj.IsClosed; LuaDLL.lua_pushboolean(L, ret); return(1); } catch (Exception e) { return(LuaDLL.toluaL_exception(L, e, o == null ? "attempt to index IsClosed on a nil value" : e.Message)); } }
static int GetChars(IntPtr L) { try { ToLua.CheckArgsCount(L, 6); Mono.Data.Sqlite.SqliteDataReader obj = (Mono.Data.Sqlite.SqliteDataReader)ToLua.CheckObject(L, 1, typeof(Mono.Data.Sqlite.SqliteDataReader)); int arg0 = (int)LuaDLL.luaL_checknumber(L, 2); long arg1 = LuaDLL.tolua_checkint64(L, 3); char[] arg2 = ToLua.CheckCharBuffer(L, 4); int arg3 = (int)LuaDLL.luaL_checknumber(L, 5); int arg4 = (int)LuaDLL.luaL_checknumber(L, 6); long o = obj.GetChars(arg0, arg1, arg2, arg3, arg4); LuaDLL.tolua_pushint64(L, o); return(1); } catch (Exception e) { return(LuaDLL.toluaL_exception(L, e)); } }
static int SelectWhere(IntPtr L) { try { ToLua.CheckArgsCount(L, 6); SqliteDbHelper obj = (SqliteDbHelper)ToLua.CheckObject(L, 1, typeof(SqliteDbHelper)); string arg0 = ToLua.CheckString(L, 2); string[] arg1 = ToLua.CheckStringArray(L, 3); string[] arg2 = ToLua.CheckStringArray(L, 4); string[] arg3 = ToLua.CheckStringArray(L, 5); string[] arg4 = ToLua.CheckStringArray(L, 6); Mono.Data.Sqlite.SqliteDataReader o = obj.SelectWhere(arg0, arg1, arg2, arg3, arg4); ToLua.PushObject(L, o); return(1); } catch (Exception e) { return(LuaDLL.toluaL_exception(L, e)); } }
/// <summary> /// Creates a new Vocaluxe Database if no file exists. Converts an existing old Ultrastar Deluxe highscore database into vocaluxe format. /// </summary> /// <param name="filePath">Database file path</param> /// <returns></returns> private bool _CreateOrConvert(string filePath) { bool result = true; using (var connection = new SQLiteConnection()) { connection.ConnectionString = "Data Source=" + filePath; try { connection.Open(); } catch (Exception) { return(false); } using (var command = new SQLiteCommand(connection)) { command.CommandText = "PRAGMA user_version"; SQLiteDataReader reader = command.ExecuteReader(); reader.Read(); int version = reader.GetInt32(0); reader.Dispose(); //Check if old scores table exists command.CommandText = "SELECT name FROM sqlite_master WHERE type='table' AND name='US_Scores';"; reader = command.ExecuteReader(); reader.Read(); bool scoresTableExists = reader.HasRows; reader.Dispose(); command.CommandText = "SELECT Value FROM Version"; reader = null; try { reader = command.ExecuteReader(); } catch (Exception) {} if (reader == null || reader.FieldCount == 0) { // create new database/tables if (version == 1) { //Check for USDX 1.1 DB _CreateHighscoreDBV1(filePath); result &= !_ConvertFrom110(filePath); result &= _UpdateDatabase(1, connection); result &= _UpdateDatabase(2, connection); } else if (version == 0 && scoresTableExists) { //Check for USDX 1.01 or CMD Mod DB _CreateHighscoreDBV1(filePath); result &= _ConvertFrom101(filePath); result &= _UpdateDatabase(1, connection); result &= _UpdateDatabase(2, connection); } else { _CreateHighscoreDB(filePath); } } else { reader.Read(); int currentVersion = reader.GetInt32(0); if (currentVersion < CSettings.DatabaseHighscoreVersion) { // update database result &= _UpdateDatabase(currentVersion, connection); } } if (reader != null) { reader.Dispose(); } } } return(result); }
public bool login(String name, String password) { using (SQLiteCommand command = new SQLiteCommand(Sqlite.getSqlite().connection)) { command.CommandText = "SELECT * FROM `account` WHERE `name`=@name AND `password`=@password"; command.Parameters.AddWithValue("@name", name); command.Parameters.AddWithValue("@password", password); using (SQLiteDataReader sdrITM = command.ExecuteReader()) { if (!sdrITM.HasRows) { return(false); } sdrITM.Read(); accountID = Convert.ToInt32(sdrITM["id"]); String world = Convert.ToString(sdrITM["world"]); player.setSpawnInfos(world, new Types.Vec3f(Convert.ToSingle(sdrITM["posx"]), Convert.ToSingle(sdrITM["posy"]), Convert.ToSingle(sdrITM["posz"])), null); } //Attributes: command.CommandText = "SELECT * FROM `account_attributes` WHERE `accountID`=@accountID ORDER BY `type` DESC"; command.Parameters.AddWithValue("@accountID", accountID); using (SQLiteDataReader sdrITM = command.ExecuteReader()) { if (sdrITM.HasRows) { while (sdrITM.Read()) { player.setAttribute((NPCAttribute)Convert.ToInt32(sdrITM["type"]), Convert.ToInt32(sdrITM["value"])); } } } //Talents: command.CommandText = "SELECT * FROM `account_talents` WHERE `accountID`=@accountID"; command.Parameters.AddWithValue("@accountID", accountID); using (SQLiteDataReader sdrITM = command.ExecuteReader()) { if (sdrITM.HasRows) { while (sdrITM.Read()) { player.setTalentValues((NPCTalent)Convert.ToInt32(sdrITM["type"]), Convert.ToInt32(sdrITM["value"])); player.setTalentSkills((NPCTalent)Convert.ToInt32(sdrITM["type"]), Convert.ToInt32(sdrITM["skill"])); } } } //Hitchances command.CommandText = "SELECT * FROM `account_hitchances` WHERE `accountID`=@accountID"; command.Parameters.AddWithValue("@accountID", accountID); using (SQLiteDataReader sdrITM = command.ExecuteReader()) { if (sdrITM.HasRows) { while (sdrITM.Read()) { player.setHitchances((NPCTalent)Convert.ToInt32(sdrITM["type"]), Convert.ToInt32(sdrITM["value"])); } } } //Items command.CommandText = "SELECT * FROM `account_items` WHERE `accountID`=@accountID"; command.Parameters.AddWithValue("@accountID", accountID); using (SQLiteDataReader sdrITM = command.ExecuteReader()) { if (sdrITM.HasRows) { while (sdrITM.Read()) { player.addItem(ItemInstance.getItemInstance(Convert.ToString(sdrITM["instanceID"])), Convert.ToInt32(sdrITM["amount"])); } } } return(true); } }
public static void LogInHandler(StateObject state, Socket handler) { Console.WriteLine("Received 'Log In' Request from {0}:{1}", ((IPEndPoint)handler.RemoteEndPoint).Address, ((IPEndPoint)handler.RemoteEndPoint).Port); ClientLogInPacket dataReceived = GetLogInPacketFromBytes(state.buffer); //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 USERNAME = @username and PASSWORD = @password"; SQLiteCommand command = new SQLiteCommand(query, db_connection); command.Parameters.AddWithValue("@username", dataReceived.username); command.Parameters.AddWithValue("@password", dataReceived.password); SQLiteDataReader reader = command.ExecuteReader(); ServerConfirmationPacket packet; packet.ID = 0; packet.msg = 0; if (reader.HasRows) { while (reader.Read()) { //Check if the username & password are correct if ((string)reader["USERNAME"] == dataReceived.username && (string)reader["PASSWORD"] == dataReceived.password) { uint id = Convert.ToUInt32(reader["ID"]); //Check if that user has already logged in if (CheckPlayerLogged(id)) { packet.msg = (uint)ServerMessage.Server_Denied_AlreadyLogged; break; } if (packet.msg != (uint)ServerMessage.Server_Denied_AlreadyLogged) { PlayerInfo newPlayer; newPlayer.ID = id; newPlayer.handler = handler; newPlayer.authority = false; newPlayer.username = dataReceived.username; newPlayer.ready = false; newPlayer.bomb = 0; newPlayer.dead = false; newPlayer.winCount = Convert.ToUInt32(reader["WINCOUNT"]); newPlayer.ep = null; packet.ID = id; packet.msg = (uint)ServerMessage.Server_Accepted; if (players.Count < 1) { packet.msg = (uint)ServerMessage.Server_AcceptedAuthority; newPlayer.authority = true; } //Broadcast the message to the rest of the players ServerMessagePacket broadcastPacket; broadcastPacket.msg = (uint)ServerMessage.Server_PlayerConnected; Broadcast(ConnectionType.TCP, GetBytesFromPacket(broadcastPacket)); players.Add(newPlayer); } break; } } } db_connection.Close(); SendTCP(handler, GetBytesFromPacket(packet)); }
public static bool LoadFromDB(ref List <List <List <object> > > results, string completeQuery, string dataSource) { SqliteConnection con = new SqliteConnection(); con.ConnectionString = dataSource; try { con.Open(); } catch (Exception ex) { MakeLogErrorStatic(typeof(DBReader), ex); if (con.State.ToString() == "Open") { con.Close(); con.Dispose(); } return(false); } // security check and close connection if necessary if (!DBSecurity.IsSecureSQLCommand(completeQuery)) { MakeLogWarningStatic(typeof(DBReader), "LoadFromDB: Prevented forwarding of insecure sql-command: " + completeQuery); return(false); } using (SQLiteCommand cmd = new SQLiteCommand(completeQuery, con)) { SQLiteDataReader rdr = null; try { rdr = cmd.ExecuteReader(); if (rdr == null) { return(false); } if (!rdr.HasRows) { return(true); } // temporary array to put all data of a row into object[] rowArr = null; do { // add new result-list results.Add(new List <List <object> >()); while (rdr.Read()) { // create and fill array of the temporary data row rowArr = new object[rdr.FieldCount]; rdr.GetValues(rowArr); results[results.Count - 1].Add(new List <object>(rowArr)); } }while (rdr.NextResult()); } catch (Exception ex) { throw new Exception("LoadFromDB: Could not execute SQLiteDataReader: " + ex); } finally { if (rdr != null) { rdr.Close(); rdr.Dispose(); } } } // close connection if still opened if (con.State.ToString() == "Open") { con.Close(); con.Dispose(); } // everything went through without errors thrown return(true); }
/// <summary> /// This function does all the nasty work at determining what keys need to be returned for /// a given statement. /// </summary> /// <param name="cnn"></param> /// <param name="reader"></param> /// <param name="stmt"></param> internal SqliteKeyReader(SqliteConnection cnn, SqliteDataReader reader, SqliteStatement stmt) { Dictionary <string, int> catalogs = new Dictionary <string, int>(); Dictionary <string, List <string> > tables = new Dictionary <string, List <string> >(); List <string> list; List <KeyInfo> keys = new List <KeyInfo>(); // Record the statement so we can use it later for sync'ing _stmt = stmt; // Fetch all the attached databases on this connection using (DataTable tbl = cnn.GetSchema("Catalogs")) { foreach (DataRow row in tbl.Rows) { catalogs.Add((string)row["CATALOG_NAME"], Convert.ToInt32(row["ID"])); } } // Fetch all the unique tables and catalogs used by the current statement using (DataTable schema = reader.GetSchemaTable(false, false)) { foreach (DataRow row in schema.Rows) { // Check if column is backed to a table if (row[SchemaTableOptionalColumn.BaseCatalogName] == DBNull.Value) { continue; } // Record the unique table so we can look up its keys string catalog = (string)row[SchemaTableOptionalColumn.BaseCatalogName]; string table = (string)row[SchemaTableColumn.BaseTableName]; if (tables.ContainsKey(catalog) == false) { list = new List <string>(); tables.Add(catalog, list); } else { list = tables[catalog]; } if (list.Contains(table) == false) { list.Add(table); } } // For each catalog and each table, query the indexes for the table. // Find a primary key index if there is one. If not, find a unique index instead foreach (KeyValuePair <string, List <string> > pair in tables) { for (int i = 0; i < pair.Value.Count; i++) { string table = pair.Value[i]; DataRow preferredRow = null; using (DataTable tbl = cnn.GetSchema("Indexes", new string[] { pair.Key, null, table })) { // Loop twice. The first time looking for a primary key index, // the second time looking for a unique index for (int n = 0; n < 2 && preferredRow == null; n++) { foreach (DataRow row in tbl.Rows) { if (n == 0 && (bool)row["PRIMARY_KEY"] == true) { preferredRow = row; break; } else if (n == 1 && (bool)row["UNIQUE"] == true) { preferredRow = row; break; } } } if (preferredRow == null) // Unable to find any suitable index for this table so remove it { pair.Value.RemoveAt(i); i--; } else // We found a usable index, so fetch the necessary table details { using (DataTable tblTables = cnn.GetSchema("Tables", new string[] { pair.Key, null, table })) { // Find the root page of the table in the current statement and get the cursor that's iterating it int database = catalogs[pair.Key]; int rootPage = Convert.ToInt32(tblTables.Rows[0]["TABLE_ROOTPAGE"]); int cursor = stmt._sql.GetCursorForTable(stmt, database, rootPage); // Now enumerate the members of the index we're going to use using (DataTable indexColumns = cnn.GetSchema("IndexColumns", new string[] { pair.Key, null, table, (string)preferredRow["INDEX_NAME"] })) { KeyQuery query = null; List <string> cols = new List <string>(); for (int x = 0; x < indexColumns.Rows.Count; x++) { bool addKey = true; // If the column in the index already appears in the query, skip it foreach (DataRow row in schema.Rows) { if (row.IsNull(SchemaTableColumn.BaseColumnName)) { continue; } if ((string)row[SchemaTableColumn.BaseColumnName] == (string)indexColumns.Rows[x]["COLUMN_NAME"] && (string)row[SchemaTableColumn.BaseTableName] == table && (string)row[SchemaTableOptionalColumn.BaseCatalogName] == pair.Key) { indexColumns.Rows.RemoveAt(x); x--; addKey = false; break; } } if (addKey == true) { cols.Add((string)indexColumns.Rows[x]["COLUMN_NAME"]); } } // If the index is not a rowid alias, record all the columns // needed to make up the unique index and construct a SQL query for it if ((string)preferredRow["INDEX_NAME"] != "sqlite_master_PK_" + table) { // Whatever remains of the columns we need that make up the index that are not // already in the query need to be queried separately, so construct a subquery if (cols.Count > 0) { string[] querycols = new string[cols.Count]; cols.CopyTo(querycols); query = new KeyQuery(cnn, pair.Key, table, querycols); } } // Create a KeyInfo struct for each column of the index for (int x = 0; x < indexColumns.Rows.Count; x++) { string columnName = (string)indexColumns.Rows[x]["COLUMN_NAME"]; KeyInfo key = new KeyInfo(); key.rootPage = rootPage; key.cursor = cursor; key.database = database; key.databaseName = pair.Key; key.tableName = table; key.columnName = columnName; key.query = query; key.column = x; keys.Add(key); } } } } } } } } // Now we have all the additional columns we have to return in order to support // CommandBehavior.KeyInfo _keyInfo = new KeyInfo[keys.Count]; keys.CopyTo(_keyInfo); }
/// <summary> /// Converts a USDX 1.01 or CMD 1.01 database to Vocaluxe format /// </summary> /// <param name="filePath">Database file path</param> /// <returns>True if succeeded</returns> private bool _ConvertFrom101(string filePath) { using (var connection = new SQLiteConnection()) { connection.ConnectionString = "Data Source=" + filePath; try { connection.Open(); } catch (Exception) { return(false); } using (var command = new SQLiteCommand(connection)) { command.CommandText = "PRAGMA table_info(US_Scores);"; bool dateExists = false; using (SQLiteDataReader reader = command.ExecuteReader()) { //Check for column Date while (reader.Read()) { for (int i = 0; i < reader.FieldCount; i++) { if (reader.GetName(i) == "name") { if (reader.GetString(i) == "Date") { dateExists = true; } break; } } } } //This is a USDX 1.01 DB command.CommandText = !dateExists ? "INSERT INTO Scores (SongID, PlayerName, Score, LineNr, Date, Medley, Duet, Difficulty) SELECT SongID, Player, Score, '0', '0', '0', '0', Difficulty from US_Scores" : "INSERT INTO Scores (SongID, PlayerName, Score, LineNr, Date, Medley, Duet, Difficulty) SELECT SongID, Player, Score, '0', Date, '0', '0', Difficulty from US_Scores"; command.ExecuteNonQuery(); command.CommandText = "INSERT INTO Songs SELECT ID, Artist, Title, TimesPlayed from US_Songs"; command.ExecuteNonQuery(); // convert from CP1252 to UTF8 var scores = new List <SData>(); var songs = new List <SData>(); Sqlite3.sqlite3 oldDB; int res = Sqlite3.sqlite3_open(filePath, out oldDB); if (res != Sqlite3.SQLITE_OK) { CLog.LogError("Error opening Database: " + filePath + " (" + Sqlite3.sqlite3_errmsg(oldDB) + ")"); } else { var stmt = new Sqlite3.Vdbe(); res = Sqlite3.sqlite3_prepare_v2(oldDB, "SELECT id, Artist, Title FROM Songs", -1, ref stmt, 0); if (res != Sqlite3.SQLITE_OK) { CLog.LogError("Error query Database: " + filePath + " (" + Sqlite3.sqlite3_errmsg(oldDB) + ")"); } else { //Sqlite3.sqlite3_step(Stmt); Encoding utf8 = Encoding.UTF8; Encoding cp1252 = Encoding.GetEncoding(1252); while (Sqlite3.sqlite3_step(stmt) == Sqlite3.SQLITE_ROW) { var data = new SData { Id = Sqlite3.sqlite3_column_int(stmt, 0) }; byte[] bytes = Sqlite3.sqlite3_column_rawbytes(stmt, 1); data.Str1 = bytes != null?utf8.GetString(Encoding.Convert(cp1252, utf8, bytes)) : "Someone"; bytes = Sqlite3.sqlite3_column_rawbytes(stmt, 2); data.Str2 = bytes != null?utf8.GetString(Encoding.Convert(cp1252, utf8, bytes)) : "Someone"; songs.Add(data); } Sqlite3.sqlite3_finalize(stmt); } stmt = new Sqlite3.Vdbe(); // ReSharper disable ConvertIfStatementToConditionalTernaryExpression if (!dateExists) { // ReSharper restore ConvertIfStatementToConditionalTernaryExpression res = Sqlite3.sqlite3_prepare_v2(oldDB, "SELECT id, PlayerName FROM Scores", -1, ref stmt, 0); } else { res = Sqlite3.sqlite3_prepare_v2(oldDB, "SELECT id, PlayerName, Date FROM Scores", -1, ref stmt, 0); } if (res != Sqlite3.SQLITE_OK) { CLog.LogError("Error query Database: " + filePath + " (" + Sqlite3.sqlite3_errmsg(oldDB) + ")"); } else { //Sqlite3.sqlite3_step(Stmt); Encoding utf8 = Encoding.UTF8; Encoding cp1252 = Encoding.GetEncoding(1252); while (Sqlite3.sqlite3_step(stmt) == Sqlite3.SQLITE_ROW) { var data = new SData { Id = Sqlite3.sqlite3_column_int(stmt, 0) }; byte[] bytes = Sqlite3.sqlite3_column_rawbytes(stmt, 1); data.Str1 = bytes != null?utf8.GetString(Encoding.Convert(cp1252, utf8, bytes)) : "Someone"; if (dateExists) { data.Ticks = _UnixTimeToTicks(Sqlite3.sqlite3_column_int(stmt, 2)); } scores.Add(data); } Sqlite3.sqlite3_finalize(stmt); } } Sqlite3.sqlite3_close(oldDB); SQLiteTransaction transaction = connection.BeginTransaction(); // update Title and Artist strings foreach (SData data in songs) { command.CommandText = "UPDATE Songs SET [Artist] = @artist, [Title] = @title WHERE [ID] = @id"; command.Parameters.Add("@title", DbType.String, 0).Value = data.Str2; command.Parameters.Add("@artist", DbType.String, 0).Value = data.Str1; command.Parameters.Add("@id", DbType.Int32, 0).Value = data.Id; command.ExecuteNonQuery(); } // update player names foreach (SData data in scores) { if (!dateExists) { command.CommandText = "UPDATE Scores SET [PlayerName] = @player WHERE [id] = @id"; } else { command.CommandText = "UPDATE Scores SET [PlayerName] = @player, [Date] = @date WHERE [id] = @id"; command.Parameters.Add("@date", DbType.Int64, 0).Value = data.Ticks; } command.Parameters.Add("@player", DbType.String, 0).Value = data.Str1; command.Parameters.Add("@id", DbType.Int32, 0).Value = data.Id; command.ExecuteNonQuery(); } transaction.Commit(); //Delete old tables after conversion command.CommandText = "DROP TABLE US_Scores;"; command.ExecuteNonQuery(); command.CommandText = "DROP TABLE US_Songs;"; command.ExecuteNonQuery(); } } return(true); }
/// <summary> /// Converts a USDX 1.1 database into the Vocaluxe format /// </summary> /// <param name="filePath">Database file path</param> /// <returns>True if succeeded</returns> private bool _ConvertFrom110(string filePath) { using (var connection = new SQLiteConnection()) { connection.ConnectionString = "Data Source=" + filePath; try { connection.Open(); } catch (Exception) { return(false); } using (var command = new SQLiteCommand(connection)) { //The USDX database has no column for LineNr, Medley and Duet so just fill 0 in there command.CommandText = "INSERT INTO Scores (SongID, PlayerName, Score, LineNr, Date, Medley, Duet, Difficulty) SELECT SongID, Player, Score, '0', Date, '0', '0', Difficulty from US_Scores"; command.ExecuteNonQuery(); command.CommandText = "INSERT INTO Songs SELECT ID, Artist, Title, TimesPlayed from US_Songs"; command.ExecuteNonQuery(); var scores = new List <SData>(); var songs = new List <SData>(); command.CommandText = "SELECT id, PlayerName, Date FROM Scores"; SQLiteDataReader reader = command.ExecuteReader(); if (reader != null && reader.HasRows) { while (reader.Read()) { var data = new SData { Id = reader.GetInt32(0), Str1 = reader.GetString(1) }; Int64 ticks = 0; try { ticks = reader.GetInt64(2); } catch {} data.Ticks = _UnixTimeToTicks((int)ticks); scores.Add(data); } reader.Close(); } command.CommandText = "SELECT id, Artist, Title FROM Songs"; reader = command.ExecuteReader(); if (reader != null && reader.HasRows) { while (reader.Read()) { var data = new SData { Id = reader.GetInt32(0), Str1 = reader.GetString(1), Str2 = reader.GetString(2) }; songs.Add(data); } } if (reader != null) { reader.Dispose(); } SQLiteTransaction transaction = connection.BeginTransaction(); // update Title and Artist strings foreach (SData data in songs) { command.CommandText = "UPDATE Songs SET [Artist] = @artist, [Title] = @title WHERE [ID] = @id"; command.Parameters.Add("@title", DbType.String, 0).Value = data.Str2; command.Parameters.Add("@artist", DbType.String, 0).Value = data.Str1; command.Parameters.Add("@id", DbType.Int32, 0).Value = data.Id; command.ExecuteNonQuery(); } // update player names foreach (SData data in scores) { command.CommandText = "UPDATE Scores SET [PlayerName] = @player, [Date] = @date WHERE [id] = @id"; command.Parameters.Add("@player", DbType.String, 0).Value = data.Str1; command.Parameters.Add("@date", DbType.Int64, 0).Value = data.Ticks; command.Parameters.Add("@id", DbType.Int32, 0).Value = data.Id; command.ExecuteNonQuery(); } transaction.Commit(); //Delete old tables after conversion command.CommandText = "DROP TABLE US_Scores;"; command.ExecuteNonQuery(); command.CommandText = "DROP TABLE US_Songs;"; command.ExecuteNonQuery(); command.CommandText = "DROP TABLE us_statistics_info;"; command.ExecuteNonQuery(); //This versioning is not used in Vocaluxe so reset it to 0 command.CommandText = "PRAGMA user_version = 0"; command.ExecuteNonQuery(); } } return(true); }
private bool _ImportData(string sourceDBPath) { #region open db using (var connSource = new SQLiteConnection()) { connSource.ConnectionString = "Data Source=" + sourceDBPath; try { connSource.Open(); } catch (Exception e) { CLog.LogError("Error on import high score data. Can't open source database \"" + sourceDBPath + "\" (" + e.Message + ")"); return(false); } #endregion open db using (var cmdSource = new SQLiteCommand(connSource)) { #region import table scores cmdSource.CommandText = "SELECT SongID, PlayerName, Score, LineNr, Date, Medley, Duet, ShortSong, Difficulty FROM Scores"; SQLiteDataReader source = cmdSource.ExecuteReader(); if (source == null) { return(false); } if (source.FieldCount == 0) { source.Close(); return(true); } while (source.Read()) { int songid = source.GetInt32(0); string player = source.GetString(1); int score = source.GetInt32(2); int linenr = source.GetInt32(3); long date = source.GetInt64(4); int medley = source.GetInt32(5); int duet = source.GetInt32(6); int shortsong = source.GetInt32(7); int diff = source.GetInt32(8); string artist, title; DateTime dateadded; int numplayed; if (_GetDataBaseSongInfos(songid, out artist, out title, out numplayed, out dateadded, sourceDBPath)) { AddScore(player, score, linenr, date, medley, duet, shortsong, diff, artist, title, numplayed, _FilePath); } } #endregion import table scores source.Close(); } } return(true); }
public bool GetCover(string coverPath, ref CTextureRef tex, int maxSize) { if (_Connection == null) { return(false); } if (!File.Exists(coverPath)) { CLog.Error("Can't find File: " + coverPath); return(false); } lock (_Mutex) { //Double check here because we may have just closed our connection if (_Connection == null) { return(false); } using (var command = new SQLiteCommand(_Connection)) { command.CommandText = "SELECT id, width, height FROM Cover WHERE [Path] = @path"; command.Parameters.Add("@path", DbType.String).Value = coverPath; SQLiteDataReader reader = command.ExecuteReader(); if (reader != null && reader.HasRows) { reader.Read(); int id = reader.GetInt32(0); int w = reader.GetInt32(1); int h = reader.GetInt32(2); reader.Close(); command.CommandText = "SELECT Data FROM CoverData WHERE CoverID = @id"; command.Parameters.Add("@id", DbType.Int32).Value = id; reader = command.ExecuteReader(); if (reader.HasRows) { reader.Read(); byte[] data2 = _GetBytes(reader); reader.Dispose(); tex = CDraw.EnqueueTexture(w, h, data2); return(true); } command.CommandText = "DELETE FROM Cover WHERE id = @id"; command.Parameters.Add("@id", DbType.Int32).Value = id; command.ExecuteNonQuery(); } if (reader != null) { reader.Close(); } } } // At this point we do not have a mathing entry in the CoverDB (either no Data found and deleted or nothing at all) // We break out of the lock to do the bitmap loading and resizing here to allow multithreaded loading Bitmap origin = CHelper.LoadBitmap(coverPath); if (origin == null) { return(false); } Size size = origin.GetSize(); if (size.Width > maxSize || size.Height > maxSize) { size = CHelper.FitInBounds(new SRectF(0, 0, maxSize, maxSize, 0), (float)size.Width / size.Height, EAspect.LetterBox).SizeI; Bitmap tmp = origin.Resize(size); origin.Dispose(); origin = tmp; } byte[] data; try { data = new byte[size.Width * size.Height * 4]; BitmapData bmpData = origin.LockBits(origin.GetRect(), ImageLockMode.ReadOnly, PixelFormat.Format32bppArgb); Marshal.Copy(bmpData.Scan0, data, 0, data.Length); origin.UnlockBits(bmpData); } finally { origin.Dispose(); } tex = CDraw.EnqueueTexture(size.Width, size.Height, data); lock (_Mutex) { //Double check here because we may have just closed our connection if (_Connection == null) { return(false); } if (_TransactionCover == null) { _TransactionCover = _Connection.BeginTransaction(); } using (var command = new SQLiteCommand(_Connection)) { command.CommandText = "INSERT INTO Cover (Path, width, height) VALUES (@path, @w, @h)"; command.Parameters.Add("@w", DbType.Int32).Value = size.Width; command.Parameters.Add("@h", DbType.Int32).Value = size.Height; command.Parameters.Add("@path", DbType.String).Value = coverPath; command.ExecuteNonQuery(); command.CommandText = "SELECT id FROM Cover WHERE [Path] = @path"; command.Parameters.Add("@path", DbType.String).Value = coverPath; SQLiteDataReader reader = command.ExecuteReader(); if (reader != null) { reader.Read(); int id = reader.GetInt32(0); reader.Dispose(); command.CommandText = "INSERT INTO CoverData (CoverID, Data) VALUES (@id, @data)"; command.Parameters.Add("@id", DbType.Int32).Value = id; command.Parameters.Add("@data", DbType.Binary).Value = data; command.ExecuteNonQuery(); return(true); } } } return(false); }
public List <SDBScoreEntry> LoadScore(int songID, EGameMode gameMode, EHighscoreStyle style) { var scores = new List <SDBScoreEntry>(); using (var connection = new SQLiteConnection()) { connection.ConnectionString = "Data Source=" + _FilePath; try { connection.Open(); } catch (Exception) { return(scores); } using (var command = new SQLiteCommand(connection)) { int medley = 0; int duet = 0; int shortSong = 0; switch (gameMode) { case EGameMode.TR_GAMEMODE_MEDLEY: medley = 1; break; case EGameMode.TR_GAMEMODE_DUET: duet = 1; break; case EGameMode.TR_GAMEMODE_SHORTSONG: shortSong = 1; break; } int dataBaseSongID = _GetDataBaseSongID(songID, command); if (dataBaseSongID < 0) { return(scores); } switch (style) { case EHighscoreStyle.TR_CONFIG_HIGHSCORE_LIST_BEST: command.CommandText = "SELECT os.PlayerName, os.Score, os.Date, os.Difficulty, os.LineNr, os.id " + "FROM Scores os " + "INNER JOIN ( " + "SELECT sc.PlayerName, sc.Score, sc.Difficulty, sc.LineNr, MIN(sc.Date) AS Date " + "FROM Scores sc " + "INNER JOIN ( " + "SELECT Playername, MAX(Score) AS Score, Difficulty, LineNr " + "FROM Scores " + "WHERE [SongID] = @SongID AND [Medley] = @Medley AND [Duet] = @Duet AND [ShortSong] = @ShortSong " + "GROUP BY PlayerName, Difficulty, LineNr " + ") AS mc " + "ON sc.PlayerName = mc.PlayerName AND sc.Difficulty = mc.Difficulty AND sc.LineNr = mc.LineNr AND sc.Score = mc.Score " + "WHERE [SongID] = @SongID AND [Medley] = @Medley AND [Duet] = @Duet AND [ShortSong] = @ShortSong " + "GROUP BY sc.PlayerName, sc.Difficulty, sc.LineNr, sc.Score " + ") AS iq " + "ON os.PlayerName = iq.PlayerName AND os.Difficulty = iq.Difficulty AND os.LineNr = iq.LineNr AND os.Score = iq.Score AND os.Date = iq.Date " + "WHERE [SongID] = @SongID AND [Medley] = @Medley AND [Duet] = @Duet AND [ShortSong] = @ShortSong " + "GROUP BY os.PlayerName, os.Difficulty, os.LineNr, os.Score " + "ORDER BY os.Score DESC, os.Date ASC"; break; case EHighscoreStyle.TR_CONFIG_HIGHSCORE_LIST_ALL: command.CommandText = "SELECT PlayerName, Score, Date, Difficulty, LineNr, id " + "FROM Scores " + "WHERE [SongID] = @SongID AND [Medley] = @Medley AND [Duet] = @Duet AND [ShortSong] = @ShortSong " + "ORDER BY [Score] DESC, [Date] ASC"; break; } command.Parameters.Add("@SongID", DbType.Int32, 0).Value = dataBaseSongID; command.Parameters.Add("@Medley", DbType.Int32, 0).Value = medley; command.Parameters.Add("@Duet", DbType.Int32, 0).Value = duet; command.Parameters.Add("@ShortSong", DbType.Int32, 0).Value = shortSong; SQLiteDataReader reader = command.ExecuteReader(); if (reader != null && reader.HasRows) { while (reader.Read()) { var score = new SDBScoreEntry { Name = reader.GetString(0), Score = reader.GetInt32(1), Date = new DateTime(reader.GetInt64(2)).ToString("dd/MM/yyyy"), Difficulty = (EGameDifficulty)reader.GetInt32(3), VoiceNr = reader.GetInt32(4), ID = reader.GetInt32(5) }; scores.Add(score); } reader.Dispose(); } } } return(scores); }
private int _AddScore(string playerName, int score, int lineNr, long date, int medley, int duet, int shortSong, int difficulty, int dataBaseSongID, SQLiteCommand command) { int lastInsertID = -1; if (dataBaseSongID >= 0) { command.CommandText = "SELECT id FROM Scores WHERE SongID = @SongID AND PlayerName = @PlayerName AND Score = @Score AND " + "LineNr = @LineNr AND Date = @Date AND Medley = @Medley AND Duet = @Duet AND ShortSong = @ShortSong AND Difficulty = @Difficulty"; command.Parameters.Add("@SongID", DbType.Int32, 0).Value = dataBaseSongID; command.Parameters.Add("@PlayerName", DbType.String, 0).Value = playerName; command.Parameters.Add("@Score", DbType.Int32, 0).Value = score; command.Parameters.Add("@LineNr", DbType.Int32, 0).Value = lineNr; command.Parameters.Add("@Date", DbType.Int64, 0).Value = date; command.Parameters.Add("@Medley", DbType.Int32, 0).Value = medley; command.Parameters.Add("@Duet", DbType.Int32, 0).Value = duet; command.Parameters.Add("@ShortSong", DbType.Int32, 0).Value = shortSong; command.Parameters.Add("@Difficulty", DbType.Int32, 0).Value = difficulty; SQLiteDataReader reader = null; try { reader = command.ExecuteReader(); } catch (Exception) {} if (reader != null && reader.HasRows) { if (reader.Read()) { return(reader.GetInt32(0)); } } if (reader != null) { reader.Dispose(); } command.CommandText = "INSERT INTO Scores (SongID, PlayerName, Score, LineNr, Date, Medley, Duet, ShortSong, Difficulty) " + "VALUES (@SongID, @PlayerName, @Score, @LineNr, @Date, @Medley, @Duet, @ShortSong, @Difficulty)"; command.Parameters.Add("@SongID", DbType.Int32, 0).Value = dataBaseSongID; command.Parameters.Add("@PlayerName", DbType.String, 0).Value = playerName; command.Parameters.Add("@Score", DbType.Int32, 0).Value = score; command.Parameters.Add("@LineNr", DbType.Int32, 0).Value = lineNr; command.Parameters.Add("@Date", DbType.Int64, 0).Value = date; command.Parameters.Add("@Medley", DbType.Int32, 0).Value = medley; command.Parameters.Add("@Duet", DbType.Int32, 0).Value = duet; command.Parameters.Add("@ShortSong", DbType.Int32, 0).Value = shortSong; command.Parameters.Add("@Difficulty", DbType.Int32, 0).Value = difficulty; command.ExecuteNonQuery(); //Read last insert line command.CommandText = "SELECT id FROM Scores ORDER BY id DESC LIMIT 0, 1"; reader = command.ExecuteReader(); if (reader != null && reader.HasRows) { while (reader.Read()) { lastInsertID = reader.GetInt32(0); } reader.Dispose(); } } return(lastInsertID); }
public CommonSqliteDataReader(SQLiteDataReader sqliteDataReader) #endif { this.sqliteDataReader = sqliteDataReader; }
/// <summary> /// Called by the SqliteDataReader when the data reader is closed. /// </summary> internal void ClearDataReader() { _activeReader = null; }