protected Dispose ( bool disposing ) : void | ||
disposing | bool | True if the connection is being explicitly closed. |
Résultat | void |
public virtual bool CreateDatabase( string sFile, bool bKeepOpen = false ) { myDatabase = new SqliteConnection(); try { if( System.IO.File.Exists(sFile) ) { if( bKeepOpen == true ) { myDatabase.ConnectionString = "Data Source=" + sFile + ";"; myDatabase.Open(); } return false; } myDatabase.ConnectionString = "Data Source=" + sFile + ";"; myDatabase.Open(); if( bKeepOpen == false ) { myDatabase.Close(); myDatabase.Dispose(); } return true; } catch { return false; } }
public SqliteVsMonoDSSpeedTests() { // create path and filename to the database file. var documents = Environment.GetFolderPath ( Environment.SpecialFolder.Personal); _db = Path.Combine (documents, "mydb.db3"); if (File.Exists (_db)) File.Delete (_db); SqliteConnection.CreateFile (_db); var conn = new SqliteConnection("URI=" + _db); using (var c = conn.CreateCommand()) { c.CommandText = "CREATE TABLE DataIndex (SearchKey INTEGER NOT NULL,Name TEXT NOT NULL,Email Text NOT NULL)"; conn.Open (); c.ExecuteNonQuery (); conn.Close(); } conn.Dispose(); // create path and filename to the database file. var documentsPath = Environment.GetFolderPath (Environment.SpecialFolder.MyDocuments); var libraryPath = Path.Combine (documentsPath, "..", "Library"); _dataDirectory = Path.Combine (libraryPath, "MonoDS"); _entity = "PersonEntity"; _serializer = new Serializer(); }
public void Open(string filePath) { if (_command != null) _command.Dispose(); if (_conn != null) _conn.Close(); try { ConnectionStringBuilder connstr = new ConnectionStringBuilder(); _conn = new Connection(); connstr.DataSource = (filePath.EndsWith("/") || filePath.EndsWith("\\")) ? filePath + "sys.db" : filePath + "/sys.db"; _conn.ConnectionString = connstr.ToString(); _conn.Open(); _command = new Command(_conn); _command.CommandText = "SELECT Content FROM [Text] WHERE [Name]=:name AND [Language]=:language"; _command.Parameters.Add(new Parameter(":name", DbType.Binary)); _command.Parameters.Add(new Parameter(":language", DbType.Binary)); } catch { if (_command != null) _command.Dispose(); if (_conn != null) _conn.Dispose(); _command = null; _conn = null; throw new DatabaseException("Cannot Open System Database"); } }
public string proDataExc(string[] strTT) { try { if (strTT.Length < 1) { return "No SQL"; } string DatabaseName = "PUB.db3"; string documents = System.Environment.GetFolderPath(System.Environment.SpecialFolder.Personal); string db = System.IO.Path.Combine(documents, DatabaseName); var conn = new SqliteConnection("Data Source=" + db); var sqlitecmd = conn.CreateCommand(); conn.Open(); sqlitecmd.CommandType = CommandType.Text; for (int j = 0; j < strTT.Length; j++) { if (strTT[j] == "") { continue; } sqlitecmd.CommandText = strTT[j]; sqlitecmd.ExecuteNonQuery(); } conn.Close(); conn.Dispose(); return ""; } catch (Exception Ex) { return Ex.ToString(); } }
private void Cleanup(SqliteConnection cnn) { if (_disposeConnection) cnn.Dispose(); _transaction = null; _scope = null; }
public virtual void Close() { lock (_Mutex) { if (_Connection != null) { _Connection.Dispose(); _Connection = null; } } }
public EvolutionSummaryTracker (string directory, string account_name, string folder_name) { // Make the on-disk files for folders have sane names folder_name = folder_name.Replace ('/', '-'); folder_name = folder_name.Replace (':', '_'); folder_name = folder_name.Replace (',', ' '); // Causes problems with the ConnectionString otherwise string filename = Path.Combine (directory, String.Format ("SummaryTracker-{0}-{1}.db", account_name, folder_name)); bool create_new_db = ! File.Exists (filename); bool purge_old_db = false; connection = GetConnection (filename); try { connection.Open (); } catch (ApplicationException) { purge_old_db = true; } if (! create_new_db && ! purge_old_db) { // Run a dummy SELECT statement to catch more errors // indicating sqlite version mismatches. using (SqliteCommand command = new SqliteCommand ()) { command.Connection = connection; command.CommandText = "SELECT flags FROM mapping WHERE uid = 'fo/ky'"; SqliteDataReader reader; try { reader = SqliteUtils.ExecuteReaderOrWait (command); reader.Close (); } catch (ApplicationException) { purge_old_db = true; } } } if (purge_old_db) { connection.Dispose (); // Purge the old database and create a new one File.Delete (filename); connection = GetConnection (filename); connection.Open (); create_new_db = true; } if (create_new_db) CreateDatabase (); // Start a transaction for any updates SqliteUtils.DoNonQuery (connection, "BEGIN"); }
public virtual bool Init() { lock (_Mutex) { if (_Connection != null) { return(false); } _Connection = new SQLiteConnection("Data Source=" + _FilePath); try { _Connection.Open(); } catch (Exception) { _Connection.Dispose(); _Connection = null; return(false); } using (var command = new SQLiteCommand(_Connection)) { command.CommandText = "SELECT Value FROM Version"; SQLiteDataReader reader = null; try { reader = command.ExecuteReader(); } catch (Exception) {} if (reader == null || !reader.Read() || reader.FieldCount == 0) { _Version = -1; } else { _Version = reader.GetInt32(0); } if (reader != null) { reader.Dispose(); } } } return(true); }
public string proDataCreate() { string DatabaseName = "PUB.db3"; string documents = System.Environment.GetFolderPath(System.Environment.SpecialFolder.Personal); string db = System.IO.Path.Combine(documents, DatabaseName); bool BFile = File.Exists(db); if (!BFile) { SqliteConnection.CreateFile(db); } var conn = new SqliteConnection("Data Source=" + db); string[] commands = proDataCreateFmt(); try { foreach (var cmd in commands) { var sqlitecmd = conn.CreateCommand(); sqlitecmd.CommandText = cmd; sqlitecmd.CommandType = CommandType.Text; conn.Open(); sqlitecmd.ExecuteNonQuery(); conn.Close(); } if (!BFile) // 初始化 { var sqlitecmd = conn.CreateCommand(); conn.Open(); sqlitecmd.CommandType = CommandType.Text; sqlitecmd.CommandText = "INSERT INTO T_PUB_USERID (USERID) VALUES (0);"; sqlitecmd.ExecuteNonQuery(); sqlitecmd.CommandText = "INSERT INTO T_PUB_USERTELNO (USERTELNO) VALUES ('');"; sqlitecmd.ExecuteNonQuery(); sqlitecmd.CommandText = "INSERT INTO T_PUB_USERTYPE (USERTYPE) VALUES (0);"; sqlitecmd.ExecuteNonQuery(); conn.Close(); } conn.Dispose(); return ""; } catch (System.Exception sysExc) { return "创建数据出错: " + sysExc.Message; } }
public void Dispose() { try { if (_SqlTransaction != null) { _SqlTransaction.Commit(); _SqlTransaction.Dispose(); } if (_SqlCommand != null) { _SqlCommand.Dispose(); } if (_SqlConnection != null) { _SqlConnection.Dispose(); } } catch { } }
public static void Execute(string command, Object obj, Dictionary<string, DbType> lookup) { SqliteConnection conn = new SqliteConnection (data_source); SqliteCommand cmd; conn.Open (); cmd = new SqliteCommand (command, conn); try { if (obj != null) Database.AddParameters (cmd, obj, lookup); cmd.ExecuteNonQuery (); } catch (KeyNotFoundException) { Log.Warning ("Missing a parameter somewhere; not executing SQL statement"); } catch (Exception e) { Log.Exception ("Exception occurred while executing query", e); } finally { cmd.Dispose (); conn.Dispose (); } }
public static void InitXBansDB() { if (!EBConfig.UseMysql) { string sql = Path.Combine(EBDB); if (!File.Exists(EBDB)) { SqliteConnection.CreateFile(EBDB); } DBSqlite = new SqliteConnection(string.Format("uri=file://{0},Version=3", sql)); CheckTables(DBSqlite); DBSqlite.Dispose(); } else { DBMysql = new MySqlConnection(string.Format("Data Source={0};User Id={1};Password={2}", EBConfig.MysqlHost, EBConfig.MysqlLogin, EBConfig.MysqlPassword)); string CMD = string.Format("CREATE DATABASE IF NOT EXISTS {0}", EBConfig.MysqlDatabase); RunExec(CMD); DBMysql = new MySqlConnection(string.Format("Database={0};Data Source={1};User Id={2};Password={3}", EBConfig.MysqlDatabase, EBConfig.MysqlHost, EBConfig.MysqlLogin, EBConfig.MysqlPassword)); CheckTables(DBMysql); ImportToMysql(); } }
internal static void ImportToMysql() { string sql = Path.Combine(EBDB); if (File.Exists(EBDB)) { string[] baninfo = new string[5]; DBSqlite = new SqliteConnection(string.Format("uri=file://{0},Version=3", sql)); var DBQuery = DBSqlite.QueryReader("SELECT * FROM BannedIP"); while (DBQuery.Read()) { baninfo[0] = DBQuery.Get<string>("IP"); baninfo[1] = DBQuery.Get<int>("BanDate").ToString(); baninfo[2] = DBQuery.Get<int>("UnbanDate").ToString(); baninfo[3] = DBQuery.Get<string>("BannedBy"); baninfo[4] = DBQuery.Get<string>("Reason"); RunExec("INSERT INTO BannedIP (IP, BanDate, UnbanDate, BannedBy, Reason) VALUES ('" + baninfo[0] + "', '" + int.Parse(baninfo[1]) + "', '" + int.Parse(baninfo[2]) + "', '" + baninfo[3] + "', '" + baninfo[4] + "')"); } DBSqlite.Dispose(); DBQuery.Dispose(); DBQuery = DBSqlite.QueryReader("SELECT * FROM BannedPlayer"); while (DBQuery.Read()) { baninfo[0] = DBQuery.Get<string>("Player"); baninfo[1] = DBQuery.Get<int>("BanDate").ToString(); baninfo[2] = DBQuery.Get<int>("UnbanDate").ToString(); baninfo[3] = DBQuery.Get<string>("BannedBy"); baninfo[4] = DBQuery.Get<string>("Reason"); RunExec("INSERT INTO BannedPlayer (Player, BanDate, UnbanDate, BannedBy, Reason) VALUES ('" + baninfo[0] + "', '" + int.Parse(baninfo[1]) + "', '" + int.Parse(baninfo[2]) + "', '" + baninfo[3] + "', '" + baninfo[4] + "')"); } DBSqlite.Dispose(); DBQuery.Dispose(); DBQuery = DBSqlite.QueryReader("SELECT * FROM MutedPlayer"); while (DBQuery.Read()) { baninfo[0] = DBQuery.Get<string>("Player"); baninfo[1] = DBQuery.Get<int>("MuteDate").ToString(); baninfo[2] = DBQuery.Get<int>("UnmuteDate").ToString(); baninfo[3] = DBQuery.Get<string>("BannedBy"); baninfo[4] = DBQuery.Get<string>("MutedBy"); RunExec("INSERT INTO MutedPlayer (Player, MuteDate, UnmuteDate, MutedBy, Reason) VALUES ('" + baninfo[0] + "', '" + int.Parse(baninfo[1]) + "', '" + int.Parse(baninfo[2]) + "', '" + baninfo[3] + "', '" + baninfo[4] + "')"); } DBSqlite.Dispose(); DBSqlite.Close(); DBQuery.Dispose(); File.Delete(EBDB); } }
void PullDataButton_Click(object sender, EventArgs e) { var conn = new SqliteConnection("Data Source=" + dbPath ()); //object om db te lezen var strSql = "select Name from Customer where STATEID=@STATEID"; var cmd = new SqliteCommand(strSql, conn); cmd.CommandType = CommandType.Text; cmd.Parameters.Add(new SqliteParameter("@STATEID", 1)); try { conn.Open(); var sdr = cmd.ExecuteReader ();//lezer while (sdr.Read()) { // TODO RunOnUiThread /* Found out how - not sure why though? */ string name = Convert.ToString(sdr["Name"]); RunOnUiThread(() => tv.Text = name); } } catch (System.Exception sysExc) { tv.Text = sysExc.Message; } finally { if (conn.State != ConnectionState.Closed) { conn.Close(); } conn.Dispose(); } }
/// <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 static bool ConvertFrom101(string FilePath) { SQLiteConnection connection = new SQLiteConnection(); connection.ConnectionString = "Data Source=" + FilePath; SQLiteCommand command; SQLiteDataReader reader = null; try { connection.Open(); } catch (Exception) { return false; } command = new SQLiteCommand(connection); command.CommandText = "PRAGMA table_info(US_Scores);"; reader = command.ExecuteReader(); bool dateExists = false; //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; } } } reader.Close(); //This is a USDX 1.01 DB if (!dateExists) command.CommandText = "INSERT INTO Scores (SongID, PlayerName, Score, LineNr, Date, Medley, Duet, Difficulty) SELECT SongID, Player, Score, '0', '0', '0', '0', Difficulty from US_Scores"; else // This is a CMD 1.01 DB 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(); // convert from CP1252 to UTF8 List<SData> scores = new List<SData>(); List<SData> 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 { Sqlite3.Vdbe 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) { SData data = new SData(); data.id = Sqlite3.sqlite3_column_int(Stmt, 0); byte[] bytes = Sqlite3.sqlite3_column_rawbytes(Stmt, 1); if (bytes != null) data.str1 = UTF8.GetString(Encoding.Convert(CP1252, UTF8, bytes)); else data.str1 = "Someone"; bytes = Sqlite3.sqlite3_column_rawbytes(Stmt, 2); if (bytes != null) data.str2 = UTF8.GetString(Encoding.Convert(CP1252, UTF8, bytes)); else data.str2 = "Someone"; songs.Add(data); } Sqlite3.sqlite3_finalize(Stmt); } Stmt = new Sqlite3.Vdbe(); if (!dateExists) 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) { SData data = new SData(); data.id = Sqlite3.sqlite3_column_int(Stmt, 0); byte[] bytes = Sqlite3.sqlite3_column_rawbytes(Stmt, 1); if (bytes != null) data.str1 = UTF8.GetString(Encoding.Convert(CP1252, UTF8, bytes)); else data.str1 = "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", System.Data.DbType.String, 0).Value = data.str2; command.Parameters.Add("@artist", System.Data.DbType.String, 0).Value = data.str1; command.Parameters.Add("@id", System.Data.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", System.Data.DbType.Int64, 0).Value = data.ticks; } command.Parameters.Add("@player", System.Data.DbType.String, 0).Value = data.str1; command.Parameters.Add("@id", System.Data.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(); reader.Dispose(); command.Dispose(); connection.Close(); connection.Dispose(); return true; }
public static int AddScore(SPlayer player) { SQLiteConnection connection = new SQLiteConnection(); connection.ConnectionString = "Data Source=" + _HighscoreFilePath; SQLiteCommand command; try { connection.Open(); } catch (Exception) { return -1; } command = new SQLiteCommand(connection); int DataBaseSongID = GetDataBaseSongID(player, command); int result = AddScore(player, command, DataBaseSongID); connection.Close(); connection.Dispose(); return result; }
//If you want to add an image to db, call this method! private static bool AddImageToCreditsDB(String ImagePath) { bool result = false; STexture tex; if (File.Exists(ImagePath)) { SQLiteConnection connection = new SQLiteConnection(); connection.ConnectionString = "Data Source=" + _CreditsRessourcesFilePath; SQLiteCommand command; try { connection.Open(); } catch (Exception) { return false; } command = new SQLiteCommand(connection); SQLiteDataReader reader = null; if (reader != null) reader.Close(); Bitmap origin; try { origin = new Bitmap(ImagePath); } catch (Exception) { CLog.LogError("Error loading Texture: " + ImagePath); tex = new STexture(-1); if (reader != null) { reader.Close(); reader.Dispose(); } command.Dispose(); connection.Close(); connection.Dispose(); return false; } int w = origin.Width; int h = origin.Height; Bitmap bmp = new Bitmap(w, h); Graphics g = Graphics.FromImage(bmp); g.DrawImage(origin, new Rectangle(0, 0, w, h)); g.Dispose(); tex = CDraw.AddTexture(bmp); byte[] data = new byte[w * h * 4]; BitmapData bmp_data = bmp.LockBits(new Rectangle(0, 0, bmp.Width, bmp.Height), ImageLockMode.ReadOnly, System.Drawing.Imaging.PixelFormat.Format32bppArgb); Marshal.Copy(bmp_data.Scan0, data, 0, w * h * 4); bmp.UnlockBits(bmp_data); bmp.Dispose(); command.CommandText = "INSERT INTO Images (Path, width, height) " + "VALUES (@path, " + w.ToString() + ", " + h.ToString() + ")"; command.Parameters.Add("@path", System.Data.DbType.String, 0).Value = Path.GetFileName(ImagePath); command.ExecuteNonQuery(); command.CommandText = "SELECT id FROM Images WHERE [Path] = @path"; command.Parameters.Add("@path", System.Data.DbType.String, 0).Value = Path.GetFileName(ImagePath); reader = null; try { reader = command.ExecuteReader(); } catch (Exception) { throw; } if (reader != null) { reader.Read(); int id = reader.GetInt32(0); reader.Close(); command.CommandText = "INSERT INTO ImageData (ImageID, Data) " + "VALUES ('" + id.ToString() + "', @data)"; command.Parameters.Add("@data", System.Data.DbType.Binary, 20).Value = data; command.ExecuteReader(); result = true; } } return result; }
/// <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 static bool ConvertFrom110(string FilePath) { SQLiteConnection connection = new SQLiteConnection(); connection.ConnectionString = "Data Source=" + FilePath; SQLiteCommand command; try { connection.Open(); } catch (Exception) { return false; } 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(); List<SData> scores = new List<SData>(); List<SData> songs = new List<SData>(); SQLiteDataReader reader = null; command.CommandText = "SELECT id, PlayerName, Date FROM Scores"; try { reader = command.ExecuteReader(); } catch (Exception) { throw; } if (reader != null && reader.HasRows) { while (reader.Read()) { SData data = new SData(); data.id = reader.GetInt32(0); data.str1 = reader.GetString(1); data.ticks = UnixTimeToTicks((int)reader.GetInt64(2)); scores.Add(data); } reader.Close(); } command.CommandText = "SELECT id, Artist, Title FROM Songs"; try { reader = command.ExecuteReader(); } catch (Exception) { throw; } if (reader != null && reader.HasRows) { while (reader.Read()) { SData data = new SData(); data.id = reader.GetInt32(0); data.str1 = reader.GetString(1); data.str2 = reader.GetString(2); songs.Add(data); } reader.Close(); } 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", System.Data.DbType.String, 0).Value = data.str2; command.Parameters.Add("@artist", System.Data.DbType.String, 0).Value = data.str1; command.Parameters.Add("@id", System.Data.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", System.Data.DbType.String, 0).Value = data.str1; command.Parameters.Add("@date", System.Data.DbType.Int64, 0).Value = data.ticks; command.Parameters.Add("@id", System.Data.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(); command.Dispose(); connection.Close(); connection.Dispose(); return true; }
private static void CreateHighscoreDBV1(string FilePath) { SQLiteConnection connection = new SQLiteConnection(); connection.ConnectionString = "Data Source=" + FilePath; SQLiteCommand command; try { connection.Open(); } catch (Exception) { return; } 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(); command.Dispose(); connection.Close(); connection.Dispose(); }
public static int AddScore(string PlayerName, int Score, int LineNr, long Date, int Medley, int Duet, int ShortSong, int Diff, string Artist, string Title, int NumPlayed, string FilePath) { SPlayer player = new SPlayer(); player.Name = PlayerName; player.Points = Score; player.LineNr = LineNr; player.DateTicks = Date; player.Medley = (Medley == 1); player.Duet = (Duet == 1); player.ShortSong = (ShortSong == 1); player.Difficulty = (EGameDifficulty)Diff; SQLiteConnection connection = new SQLiteConnection(); SQLiteCommand command; connection.ConnectionString = "Data Source=" + FilePath; try { connection.Open(); } catch (Exception) { return -1; } command = new SQLiteCommand(connection); int DataBaseSongID = GetDataBaseSongID(Artist, Title, NumPlayed, command); int result = AddScore(player, command, DataBaseSongID); command.Dispose(); connection.Close(); connection.Dispose(); return result; }
public static object ExecuteScalar(string command, Object obj, Dictionary<string, DbType> lookup) { SqliteConnection conn = new SqliteConnection (data_source); SqliteCommand cmd; object result = null; conn.Open (); cmd = new SqliteCommand (command, conn); try { if (obj != null) Database.AddParameters (cmd, obj, lookup); result = cmd.ExecuteScalar (); } catch (KeyNotFoundException) { Log.Warning ("Missing a parameter somewhere; not executing SQL statement"); } catch (Exception e) { Log.Exception (e); } finally { cmd.Dispose (); conn.Dispose (); } return result; }
private static bool GetDataBaseSongInfos(int SongID, out string Artist, out string Title, out int NumPlayed, string FilePath) { Artist = String.Empty; Title = String.Empty; NumPlayed = 0; SQLiteConnection connection = new SQLiteConnection(); connection.ConnectionString = "Data Source=" + FilePath; SQLiteCommand command; try { connection.Open(); } catch (Exception) { return false; } command = new SQLiteCommand(connection); command.CommandText = "SELECT Artist, Title, NumPlayed FROM Songs WHERE [id] = @id"; command.Parameters.Add("@id", System.Data.DbType.String, 0).Value = SongID; SQLiteDataReader reader = null; try { reader = command.ExecuteReader(); } catch (Exception) { return false; } if (reader != null && reader.HasRows) { reader.Read(); Artist = reader.GetString(0); Title = reader.GetString(1); NumPlayed = reader.GetInt32(2); reader.Close(); reader.Dispose(); command.Dispose(); connection.Close(); connection.Dispose(); return true; } else { if (reader != null) reader.Close(); } command.Dispose(); connection.Close(); connection.Dispose(); return false; }
void CreateDataBaseButton_Click(object sender, EventArgs e) { //Database aanmaken bool exists = File.Exists(dbPath ()); if (!exists) { SqliteConnection.CreateFile(dbPath ());// db maken indien't niet bestaat } var conn = new SqliteConnection("Data Source=" + dbPath ()); //object dat communiceert met database //TODO where do these commands come from? /* other language with specific syntax? */ var commands = new[] { "DROP TABLE IF EXISTS TWITTERDATA", "DROP TRIGGER IF EXISTS TWITTERDATA_INSERT", "CREATE TABLE IF NOT EXISTS STATE (STATEID INT PRIMARY KEY, STATENAME VARCHAR(50))", "CREATE TABLE IF NOT EXISTS CUSTOMER(CUSTOMERID BIGINT PRIMARY KEY, " + "NAME VARCHAR(100), CONTACTNAME VARCHAR(100), DATEJOINED DATETIME, " + "PHONE VARCHAR(25), ADDRESS VARCHAR(100), CITY VARCHAR(50), " + "STATEID INT, ZIPCODE VARCHAR(25), DATEENTERED DATETIME, " + "DATEUPDATED DATETIME, FOREIGN KEY(STATEID) REFERENCES STATE(STATEID))", "CREATE TRIGGER IF NOT EXISTS CUSTOMER_INSERT INSERT ON CUSTOMER " + "BEGIN UPDATE CUSTOMER SET DATEENTERED=DATE('now') " + "WHERE CUSTOMERID=NEW.CUSTOMERID; END;", "CREATE INDEX IF NOT EXISTS IDX_CUSTOMERNAME ON CUSTOMER (NAME)", "CREATE INDEX IF NOT EXISTS IDX_STATEID ON CUSTOMER (STATEID)", "CREATE INDEX IF NOT EXISTS IDX_DATEENTERED ON CUSTOMER (DATEENTERED)", "INSERT INTO STATE (STATENAME) VALUES ('TENNESSEE');", "INSERT INTO STATE (STATENAME) VALUES ('GEORGIA');"}; try { // TODO indentation (like this? or...??) foreach (var cmd in commands) using (var sqlitecmd = conn.CreateCommand()) { sqlitecmd.CommandText = cmd; sqlitecmd.CommandType = CommandType.Text; conn.Open(); sqlitecmd.ExecuteNonQuery(); conn.Close(); } SqliteCommand sqlc = new SqliteCommand(); sqlc.Connection = conn; conn.Open(); string strSql = "INSERT INTO CUSTOMER (NAME, " + "CONTACTNAME, STATEID) VALUES " + "(@NAME, @CONTACTNAME, @STATEID)"; sqlc.CommandText = strSql; sqlc.CommandType = CommandType.Text; sqlc.Parameters.Add(new SqliteParameter("@NAME", "The Coca-Cola Company")); sqlc.Parameters.Add(new SqliteParameter("@CONTACTNAME", "John Johns")); sqlc.Parameters.Add(new SqliteParameter("@STATEID", 1));//parameters voor commando toevoegen sqlc.ExecuteNonQuery(); if (conn.State != ConnectionState.Closed) { conn.Close(); } conn.Dispose(); tv.Text = "Commands completed."; } catch (System.Exception sysExc) { tv.Text = "Exception: " + sysExc.Message; } }
private static bool InitCoverDB() { SQLiteConnection connection = new SQLiteConnection(); connection.ConnectionString = "Data Source=" + _CoverFilePath; SQLiteCommand command; try { connection.Open(); } catch (Exception) { return false; } command = new SQLiteCommand(connection); command.CommandText = "SELECT Value FROM Version"; SQLiteDataReader reader = null; try { reader = command.ExecuteReader(); } catch (Exception) { ; } if (reader == null) { // create new database/tables CreateCoverDB(); } else if (reader.FieldCount == 0) { // create new database/tables CreateCoverDB(); } else { reader.Read(); if (reader.GetInt32(0) < CSettings.iDatabaseHighscoreVersion) { // update database } } if (reader != null) { reader.Close(); reader.Dispose(); } command.Dispose(); connection.Close(); connection.Dispose(); return true; }
private static bool InitCreditsRessourcesDB() { SQLiteConnection connection = new SQLiteConnection(); connection.ConnectionString = "Data Source=" + _CreditsRessourcesFilePath; SQLiteCommand command; try { connection.Open(); } catch (Exception) { return false; } command = new SQLiteCommand(connection); command.CommandText = "SELECT Value FROM Version"; SQLiteDataReader reader = null; try { reader = command.ExecuteReader(); } catch (Exception) { ; } if (reader == null) { // Log error CLog.LogError("Can't find Credits-DB!"); } else if (reader.FieldCount == 0) { // Log error CLog.LogError("Can't find Credits-DB! Field-Count = 0"); } else { reader.Read(); if (reader.GetInt32(0) < CSettings.iDatabaseHighscoreVersion) { // update database } } if (reader != null) { reader.Close(); reader.Dispose(); } command.Dispose(); connection.Close(); connection.Dispose(); return true; }
public TextCache (string storage_dir, bool read_only) { text_cache_dir = Path.Combine (storage_dir, "TextCache"); if (! Directory.Exists (text_cache_dir)) { Directory.CreateDirectory (text_cache_dir); // Create our cache subdirectories. for (int i = 0; i < 256; ++i) { string subdir = i.ToString ("x"); if (i < 16) subdir = "0" + subdir; subdir = Path.Combine (text_cache_dir, subdir); Directory.CreateDirectory (subdir); } } // Create our Sqlite database string db_filename = Path.Combine (text_cache_dir, "TextCache.db"); bool create_new_db = false; if (! File.Exists (db_filename)) create_new_db = true; // Funky logic here to deal with sqlite versions. // // When sqlite 3 tries to open an sqlite 2 database, // it will throw an SqliteException with SqliteError // NOTADB when trying to execute a command. // // When sqlite 2 tries to open an sqlite 3 database, // it will throw an ApplicationException when it // tries to open the database. try { connection = Open (db_filename); } catch (ApplicationException) { Logger.Log.Warn ("Likely sqlite database version mismatch trying to open {0}. Purging.", db_filename); create_new_db = true; } if (!create_new_db) { // Run a dummy query to see if we get a NOTADB error. Sigh. SqliteCommand command; SqliteDataReader reader = null; command = new SqliteCommand (); command.Connection = connection; command.CommandText = "SELECT filename FROM textcache_data WHERE uri='blah'"; try { reader = SqliteUtils.ExecuteReaderOrWait (command); } catch (ApplicationException ex) { Logger.Log.Warn ("Likely sqlite database version mismatch trying to read from {0}. Purging.", db_filename); create_new_db = true; } catch (SqliteException ex) { // When the table name changed from 0.2.18 -> 0.3.0. Logger.Log.Warn ("Sqlite error: {0}. Purging textcache.", ex.Message); create_new_db = true; } if (reader != null) reader.Dispose (); command.Dispose (); } if (create_new_db) { if (connection != null) connection.Dispose (); if (read_only) throw new UnauthorizedAccessException (String.Format ("Unable to create read only text cache {0}", db_filename)); File.Delete (db_filename); try { connection = Open (db_filename); } catch (Exception e) { Log.Debug (e, "Exception opening text cache {0}", db_filename); } // Database schema: uri, filename, data SqliteUtils.DoNonQuery (connection, "CREATE TABLE textcache_data ( " + " uri TEXT UNIQUE NOT NULL, " + " filename TEXT NOT NULL, " + " data BLOB " + ")"); } #if ENABLE_RDF_ADAPTER try { SqliteUtils.DoNonQuery (connection, "CREATE TABLE IF NOT EXISTS links_data ( " + " uri TEXT UNIQUE NOT NULL, " + " links TEXT " + ")"); } catch (SqliteException) { } #endif this.InitCommands (); }
/// <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 static bool CreateOrConvert(string FilePath) { SQLiteConnection connection = new SQLiteConnection(); connection.ConnectionString = "Data Source=" + FilePath; SQLiteCommand command; try { connection.Open(); } catch (Exception) { return false; } SQLiteDataReader reader = null; command = new SQLiteCommand(connection); command.CommandText = "PRAGMA user_version"; reader = command.ExecuteReader(); reader.Read(); int version = reader.GetInt32(0); reader.Close(); 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.Close(); reader.Dispose(); command.CommandText = "SELECT Value FROM Version"; reader = null; try { reader = command.ExecuteReader(); } catch (Exception) { ; } if (reader == null) { // create new database/tables if (version == 1) //Check for USDX 1.1 DB { CreateHighscoreDBV1(FilePath); ConvertFrom110(FilePath); UpdateDatabase(1, connection); } else if (version == 0 && scoresTableExists) //Check for USDX 1.01 or CMD Mod DB { CreateHighscoreDBV1(FilePath); ConvertFrom101(FilePath); UpdateDatabase(1, connection); } else CreateHighscoreDB(FilePath); } else if (reader.FieldCount == 0) { // create new database/tables if (version == 1) //Check for USDX 1.1 DB { CreateHighscoreDBV1(FilePath); ConvertFrom110(FilePath); UpdateDatabase(1, connection); } else if (version == 0 && scoresTableExists) //Check for USDX 1.01 or CMD Mod DB { CreateHighscoreDBV1(FilePath); ConvertFrom101(FilePath); UpdateDatabase(1, connection); } else CreateHighscoreDB(FilePath); } else { reader.Read(); int CurrentVersion = reader.GetInt32(0); if (CurrentVersion < CSettings.iDatabaseHighscoreVersion) { // update database UpdateDatabase(CurrentVersion, connection); } } if (reader != null) { reader.Close(); reader.Dispose(); } command.Dispose(); connection.Close(); connection.Dispose(); return true; }
private static void CreateCreditsRessourcesDB() { SQLiteConnection connection = new SQLiteConnection(); connection.ConnectionString = "Data Source=" + _CreditsRessourcesFilePath; SQLiteCommand command; try { connection.Open(); } catch (Exception) { return; } 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, " + CSettings.iDatabaseCreditsRessourcesVersion.ToString() + ")"; 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(); command.Dispose(); connection.Close(); connection.Dispose(); }
public static bool GetCreditsRessource(string FileName, ref STexture tex) { bool result = false; SQLiteConnection connection = new SQLiteConnection(); connection.ConnectionString = "Data Source=" + _CreditsRessourcesFilePath; SQLiteCommand command; try { connection.Open(); } catch (Exception) { return false; } command = new SQLiteCommand(connection); command.CommandText = "SELECT id, width, height FROM Images WHERE [Path] = @path"; command.Parameters.Add("@path", System.Data.DbType.String, 0).Value = FileName; SQLiteDataReader reader = null; try { reader = command.ExecuteReader(); } catch (Exception) { throw; } 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 ImageData WHERE ImageID = " + id.ToString(); try { reader = command.ExecuteReader(); } catch (Exception) { throw; } if (reader.HasRows) { result = true; reader.Read(); byte[] data = GetBytes(reader); tex = CDraw.AddTexture(w, h, ref data); } } if (reader != null) { reader.Close(); reader.Dispose(); } command.Dispose(); connection.Close(); connection.Dispose(); return result; }
public DataTable proDataSql(string strSql) { string DatabaseName = "PUB.db3"; string documents = System.Environment.GetFolderPath(System.Environment.SpecialFolder.Personal); string db = System.IO.Path.Combine(documents, DatabaseName); var conn = new SqliteConnection("Data Source=" + db); var cmd = new SqliteCommand(strSql, conn); cmd.CommandType = CommandType.Text; try { conn.Open(); SqliteDataReader sdr = cmd.ExecuteReader(); System.Data.DataTable dtBack = new System.Data.DataTable(); for (int i = 0; i < sdr.FieldCount; i++) { dtBack.Columns.Add(sdr.GetName(i)); } while (sdr.Read()) { DataRow dr = dtBack.NewRow(); for (int j = 0; j < dtBack.Columns.Count; j++) { dr[j] = sdr[j].ToString(); } dtBack.Rows.Add(dr); } return dtBack; } catch // (System.Exception sysExc) { return null; } finally { if (conn.State != ConnectionState.Closed) { conn.Close(); } conn.Dispose(); } }
public static void LoadScore(ref List<SScores> Score, SPlayer player) { SQLiteConnection connection = new SQLiteConnection(); connection.ConnectionString = "Data Source=" + _HighscoreFilePath; SQLiteCommand command; Score = new List<SScores>(); try { connection.Open(); } catch (Exception) { return; } command = new SQLiteCommand(connection); int Medley = 0; if (player.Medley) Medley = 1; int Duet = 0; if (player.Duet) Duet = 1; int ShortSong = 0; if (player.ShortSong) ShortSong = 1; int DataBaseSongID = GetDataBaseSongID(player, command); if (DataBaseSongID >= 0) { 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"; command.Parameters.Add("@SongID", System.Data.DbType.Int32, 0).Value = DataBaseSongID; command.Parameters.Add("@Medley", System.Data.DbType.Int32, 0).Value = Medley; command.Parameters.Add("@Duet", System.Data.DbType.Int32, 0).Value = Duet; command.Parameters.Add("@ShortSong", System.Data.DbType.Int32, 0).Value = ShortSong; SQLiteDataReader reader = null; try { reader = command.ExecuteReader(); } catch (Exception) { throw; } if (reader != null && reader.HasRows) { while (reader.Read()) { SScores score = new SScores(); score.Name = reader.GetString(0); score.Score = reader.GetInt32(1); score.Date = new DateTime(reader.GetInt64(2)).ToString("dd/MM/yyyy"); score.Difficulty = (EGameDifficulty)reader.GetInt32(3); score.LineNr = reader.GetInt32(4); score.ID = reader.GetInt32(5); Score.Add(score); } reader.Close(); reader.Dispose(); } } command.Dispose(); connection.Close(); connection.Dispose(); }
public void Open() { string connString = ConnStrForFile (m_FileName); var dirInfo = new FileInfo (m_FileName).Directory; if (!dirInfo.Exists) dirInfo.Create (); m_rwLock.AcquireWriterLock (s_lockTimeout); try { // Backup if needed var backup = m_FileName + ".bak"; var needsUpgrade = NeedsUpgrade; if (needsUpgrade && File.Exists (m_FileName)) File.Move (m_FileName, backup); else if (!File.Exists (m_FileName)) File.WriteAllText (VersionFile, String.Format ("{0}", s_version)); // Build m_conn = new SqliteConnection (connString); m_conn.Open (); EnsureTables (); if (needsUpgrade) { File.WriteAllText (VersionFile, String.Format ("{0}", s_version)); // Open backup database var conn = new SqliteConnection (ConnStrForFile (backup)); conn.Open (); // Copy the database in a thread ThreadPool.QueueUserWorkItem (delegate { try { this.CopyDatabase (conn); } catch (Exception ex) { Console.WriteLine (ex.ToString ()); } conn.Close (); conn.Dispose (); if (File.Exists (backup)) File.Delete (backup); }); } } finally { m_rwLock.ReleaseWriterLock (); } }
private static int GetDataBaseSongID(string Artist, string Title, string FilePath, int DefNumPlayed) { SQLiteConnection connection = new SQLiteConnection(); connection.ConnectionString = "Data Source=" + FilePath; SQLiteCommand command; try { connection.Open(); } catch (Exception) { return -1; } command = new SQLiteCommand(connection); int id = GetDataBaseSongID(Artist, Title, DefNumPlayed, command); command.Dispose(); connection.Close(); connection.Dispose(); return id; }