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(); } } }
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 void GotoColorfulScene() { SceneName = "Colorfull"; playGameUI.SetActive(true); string connection = "URI=file:" + Application.dataPath + "/StreamingAssets/User.db"; Debug.Log(connection); IDbConnection dbcon = new Mono.Data.Sqlite.SqliteConnection(connection); dbcon.Open(); IDbCommand cmnd_read = dbcon.CreateCommand(); IDataReader reader; string query = "SELECT * FROM my_user"; cmnd_read.CommandText = query; reader = cmnd_read.ExecuteReader(); int index = 0; while (reader.Read()) { //Debug.Log(reader[0].ToString()); GameObject tempGO = Instantiate(rowSample) as GameObject; tempGO.transform.Find("Name").GetComponent <Text>().text = reader[1].ToString(); tempGO.transform.Find("ID").GetComponent <Text>().text = reader[0].ToString(); tempGO.transform.GetComponent <PlayGame_Button_Control>().id = reader[0].ToString(); tempGO.transform.GetComponent <PlayGame_Button_Control>().sceneName = "Colorfull"; tempGO.transform.SetParent(rowParent.transform); data.Add(tempGO); } dbcon.Close(); reader.Close(); cmnd_read.Dispose(); }
public void DataBaseRead(String query) //DB 읽어오기 - 인자로 쿼리문을 받는다. { IDbConnection dbConnection = new SqliteConnection(GetDBFilePath()); dbConnection.Open(); // DB 열기 IDbCommand dbCommand = dbConnection.CreateCommand(); dbCommand.CommandText = query; // 쿼리 입력 IDataReader dataReader = dbCommand.ExecuteReader(); // 쿼리 실행 while (dataReader.Read()) // 쿼리로 돌아온 레코드 읽기 { Debug.Log(dataReader.GetInt32(5)); // 5번 점수 필드 읽기 int maxScore = dataReader.GetInt32(5); MaxScore_Text.text = "최고점수 : " + maxScore; break; // 내림차순 정렬이므로 처음에 한 번만 레코드값을 가져오면 된다. } dataReader.Dispose(); // 생성순서와 반대로 닫아줍니다. dataReader = null; dbCommand.Dispose(); dbCommand = null; // DB에는 1개의 쓰레드만이 접근할 수 있고 동시에 접근시 에러가 발생한다. 그래서 Open과 Close는 같이 써야한다. dbConnection.Close(); dbConnection = null; }
public void DatabaseSQLAdd(string query) //삽입이라고 썼지만 삭제도 가능 { IDbConnection dbConnection = new SqliteConnection(GetDBFilePath()); dbConnection.Open(); // DB 열기 IDbCommand dbCommand = dbConnection.CreateCommand(); dbCommand.CommandText = query; // 쿼리 입력 try { dbCommand.ExecuteNonQuery(); // 쿼리 실행 } catch (Exception e) { Debug.LogError(e); test.text = e.ToString(); // 빌드시 에러확인을 위해 넣음 결과패널에 Game Over대신 에러 나오게 하는 코드 test_Result.GetComponent <Text>().fontSize = 30; // 폰트 사이즈 작게 변경 - 이유 : 오류가 길어서 test_Result.text = e.ToString(); } dbCommand.Dispose(); dbCommand = null; dbConnection.Close(); dbConnection = null; Debug.Log("데이터 삽입 완료"); test.text = "결과 데이터 삽입 완료"; }
public static void Main(string[] args) { var bookmarks = new Bookmarks(); using (var dbConn = new Mono.Data.Sqlite.SqliteConnection("Data Source=/mnt/ramdisk/test.db")) { dbConn.Open(); var cmdGetGroups = dbConn.CreateCommand(); cmdGetGroups.CommandText = "SELECT gid, name, IFNULL(pid,0) FROM rel JOIN groups ON rel.gid=groups.rowid WHERE depth=1"; using (IDataReader groups = cmdGetGroups.ExecuteReader()) { ApplyRow(groups, (g) => bookmarks.GroupAdd(g.GetInt32(0), new Group { Id = g.GetInt32(0), Name = g.GetString(1), Pid = g.GetInt32(2) })); } var cmdGetRows = dbConn.CreateCommand(); cmdGetRows.CommandText = "SELECT IFNULL(gid,0), key, value FROM data"; using (IDataReader rows = cmdGetRows.ExecuteReader()) { ApplyRow(rows, (r) => bookmarks.LinkAdd(r.GetInt32(0), new LinkItem { Title = r.GetString(1), Target = r.GetString(2) })); } } var document = new XDocument(); document.Add(bookmarks.ToXml()); document.Save("test.xml"); }
/** * 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 static bool Init() { try { m_cards = new Dictionary <int, CardData>(); string currentPath = System.Reflection.Assembly.GetExecutingAssembly().Location; currentPath = Path.GetDirectoryName(currentPath) ?? ""; string absolutePath = Path.Combine(currentPath, "Content/cards.cdb"); if (!File.Exists(absolutePath)) { return(false); } using (SQLiteConnection connection = new SQLiteConnection("Data Source=" + absolutePath)) { connection.Open(); const string select = "SELECT datas.id, alias, type, level, race, attribute, atk, def, name, desc " + "FROM datas INNER JOIN texts ON datas.id = texts.id"; SQLiteCommand command = new SQLiteCommand(select, connection); using (SQLiteDataReader reader = command.ExecuteReader()) InitCards(reader); command.Dispose(); } return(true); } catch (Exception) { return(false); } }
private void connectDatabase() { foundDB = File.Exists(filename); if (!foundDB) SqliteConnection.CreateFile(filename); database = new SqliteConnection("Data Source=" + filename); }
// Use this for initialization void Start() { string conn = "URI=file:" + Application.dataPath + "/sqlite_userinfo"; IDbConnection dbconn = new SqliteConnection(conn); dbconn.Open(); IDbCommand dbcmd = dbconn.CreateCommand(); string query = "SELECT idx, name, email FROM userinfo"; dbcmd.CommandText = query; IDataReader reader = dbcmd.ExecuteReader(); while(reader.Read()) { int idx = reader.GetInt32(0); string name = reader.GetString(1); string email = reader.GetString(2); Debug.Log("idx : " + idx + ", name : " + name + ", email : " + email); } reader.Close(); reader = null; dbcmd.Dispose(); dbcmd = null; dbconn.Close(); dbconn = null; }
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(); } }
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(); } } }
/// <summary> /// Returns the category for the specified id. /// </summary> /// <param name="categoryId">Category identifier.</param> public Category GetCategory(int categoryId) { Category category = null; using (var connection = new SqliteConnection("Data Source=" + dbPath)) using (var query = new SqliteCommand("SELECT * FROM Categories WHERE id = @categoryId", connection)) { query.Parameters.AddWithValue("@categoryId", categoryId); connection.Open(); var reader = query.ExecuteReader(CommandBehavior.CloseConnection); while (reader.Read()) { category = new Category(); category.Id = int.Parse(reader["id"].ToString()); category.Name = reader ["name"].ToString(); } reader.Close(); } return category; }
/// <summary> /// Returns all categories. /// </summary> public IEnumerable<Category> GetAllCategories() { var categories = new List<Category>(); using (var connection = new SqliteConnection("Data Source=" + dbPath)) using (var query = new SqliteCommand("SELECT * FROM Categories", connection)) { connection.Open(); var reader = query.ExecuteReader(CommandBehavior.CloseConnection); while (reader.Read()) { var category = new Category(); category.Id = int.Parse(reader["id"].ToString()); category.Name = reader ["name"].ToString(); categories.Add(category); } reader.Close(); } return categories; }
public ComboBoxDataSource (SqliteConnection conn, string tableName, string displayField) { // Initialize this.Conn = conn; this.TableName = tableName; this.DisplayField = displayField; }
public void DeleteAllData() { Mono.Data.Sqlite.SqliteConnection conn = null; Mono.Data.Sqlite.SqliteTransaction trans = null; try { conn = openDbConnection(); trans = conn.BeginTransaction(); this.executeNonqueryCommand("DELETE FROM [Athlete]", conn, trans); this.executeNonqueryCommand("DELETE FROM [Result]", conn, trans); this.executeNonqueryCommand("DELETE FROM [Score]", conn, trans); trans.Commit(); } catch (Exception exc) { if (trans != null) { trans.Rollback(); } throw exc; } finally { if (conn != null) { conn.Close(); } } new DatabaseSetup().CreateEmptyAthleteRecord(); }
public SQLiteMonoTransformationProvider(Dialect dialect, string connectionString) : base(dialect, connectionString) { _connection = new SqliteConnection(_connectionString); _connection.ConnectionString = _connectionString; _connection.Open(); }
public static void Init() { try { _cards = new Dictionary<int, CardData>(); string currentPath = Assembly.GetExecutingAssembly().Location; currentPath = Path.GetDirectoryName(currentPath) ?? ""; string absolutePath = Path.Combine(currentPath, "cards.cdb"); if (!File.Exists(absolutePath)) { throw new Exception("Could not find the cards database."); } using (SqliteConnection connection = new SqliteConnection("Data Source=" + absolutePath)) { connection.Open(); const string select = "SELECT datas.id, alias, type, level, race, attribute, atk, def, name, desc " + "FROM datas INNER JOIN texts ON datas.id = texts.id"; using (SqliteCommand command = new SqliteCommand(select, connection)) using (SqliteDataReader reader = command.ExecuteReader()) InitCards(reader); } } catch (Exception ex) { throw new Exception("Could not initialize the cards database. Check the inner exception for more details.", ex); } }
private SqliteConnection openDbConnection() { var conn = new Mono.Data.Sqlite.SqliteConnection("Data Source=" + this.getDbFileName()); conn.Open(); return(conn); }
//constructor static folios() { try { //conexion a DB var connection = new SqliteConnection(dbSource); connection.Open(); //crear query var query = connection.CreateCommand(); query.CommandText = "select consecutivo from folios where fecha='" + fecha + "'"; //inicia en el folio de la DB generarConsecutivo=Convert.ToInt16(query.ExecuteScalar()); //si el valor es 0, entonces creamos un nuevo renglon para ese dia //esto del 0 no necesariamente funciona en VistaDB if(generarConsecutivo.Equals(0)) { generarConsecutivo=1; query = connection.CreateCommand(); query.CommandText = "insert into folios (fecha,consecutivo) values ('" + fecha + "'," + generarConsecutivo.ToString() + ")"; query.ExecuteNonQuery(); } //cerrar conexion a DB connection.Close(); } catch(Exception ex) { //en caso de error en la base de datos empezamos en 1 generarConsecutivo=1; } }
public void Close() { if (conn != null) { conn.Close (); conn = null; } }
public void UpdateAllScores(List <Score> scores) { Mono.Data.Sqlite.SqliteConnection conn = null; Mono.Data.Sqlite.SqliteTransaction trans = null; try { conn = openDbConnection(); trans = conn.BeginTransaction(); var command = conn.CreateCommand(); command.Transaction = trans; command.CommandText = "DELETE FROM [Score]"; command.ExecuteNonQuery(); command.Dispose(); foreach (var score in scores) { this.addScore(conn, trans, score); } trans.Commit(); } catch (Exception exc) { trans.Rollback(); throw exc; } finally { if (conn != null) { conn.Close(); } } }
public static IDbConnection Open(string dbPath, bool create) { if (dbPath == null) throw new ArgumentNullException("dbPath"); if (create) { if (File.Exists(dbPath)) File.Delete(dbPath); #if WIN32 SQLiteConnection.CreateFile(dbPath); #else SqliteConnection.CreateFile(dbPath); #endif } else { if (!File.Exists(dbPath)) throw new FileNotFoundException(string.Format("Database '{0}' not found", dbPath)); } IDbConnection conn; string connStr = string.Format("Data Source={0}", dbPath); #if WIN32 conn = new SQLiteConnection(connStr); #else conn = new SqliteConnection(connStr); #endif conn.Open(); return conn; }
public void GetSnookerBests(int athleteID, out int?bestBreakPoints, out int?bestBreakBalls, out int?bestFrameScore) { bestBreakPoints = null; bestBreakBalls = null; bestFrameScore = null; Mono.Data.Sqlite.SqliteConnection conn = null; try { conn = openDbConnection(); string sql = "SELECT Count FROM [Result] WHERE AthleteID=" + athleteID + " ORDER BY Count DESC"; var obj1 = this.executeScalarCommand(sql, conn, null); if (obj1 != null && obj1 != DBNull.Value) { bestBreakPoints = (int)obj1; } sql = "SELECT Count2 FROM [Result] WHERE AthleteID=" + athleteID + " ORDER BY Count2 DESC"; var obj2 = this.executeScalarCommand(sql, conn, null); if (obj2 != null && obj2 != DBNull.Value) { bestBreakBalls = (int)obj2; } bestFrameScore = null; } finally { if (conn != null) { conn.Close(); } } }
public void Initialise(string connectionString) { m_connectionString = connectionString; m_log.Info("[ESTATE DB]: Sqlite - connecting: "+m_connectionString); m_connection = new SqliteConnection(m_connectionString); m_connection.Open(); Assembly assem = GetType().Assembly; Migration m = new Migration(m_connection, assem, "EstateStore"); m.Update(); //m_connection.Close(); // m_connection.Open(); Type t = typeof(EstateSettings); m_Fields = t.GetFields(BindingFlags.NonPublic | BindingFlags.Instance | BindingFlags.DeclaredOnly); foreach (FieldInfo f in m_Fields) if (f.Name.Substring(0, 2) == "m_") m_FieldMap[f.Name.Substring(2)] = f; }
public List <int> GetAthleteNamesFromResults(bool includeDeleted) { Mono.Data.Sqlite.SqliteConnection conn = null; try { conn = openDbConnection(); string sql = "SELECT AthleteID FROM [Result]"; if (includeDeleted == false) { sql += " WHERE IsDeleted=0"; } sql += " ORDER BY [Date] DESC"; List <int> ids = new List <int>(); var command = new SqliteCommand(sql, conn); var reader = command.ExecuteReader(); while (reader.Read()) { int id = (int)reader["AthleteID"]; ids.Add(id); } return(ids); } finally { if (conn != null) { conn.Close(); } } }
void Start () { string connectionString = "URI=file:" + Application.dataPath + "/GameMaster"; //Path to database. IDbConnection dbcon = new SqliteConnection(connectionString) as IDbConnection; dbcon.Open(); //Open connection to the database. IDbCommand dbcmd = dbcon.CreateCommand(); dbcmd.CommandText = "SELECT firstname, lastname " + "FROM addressbook"; IDataReader reader = dbcmd.ExecuteReader(); while(reader.Read()) { string FirstName = reader.GetString (0); string LastName = reader.GetString (1); Console.WriteLine("Name: " + FirstName + " " + LastName); UnityEngine.Debug.LogWarning("Name: " + FirstName + " " + LastName); } reader.Close(); reader = null; dbcmd.Dispose(); dbcmd = null; dbcon.Close(); dbcon = null; }
public void UpdateAllAthleteResults(int athleteID, List <Result> results) { Mono.Data.Sqlite.SqliteConnection conn = null; Mono.Data.Sqlite.SqliteTransaction trans = null; try { conn = openDbConnection(); trans = conn.BeginTransaction(); var command = conn.CreateCommand(); command.Transaction = trans; command.CommandText = "DELETE FROM [Result] WHERE AthleteID=" + athleteID; command.ExecuteNonQuery(); command.Dispose(); foreach (var result in results) { result.AthleteID = athleteID; addResult(conn, trans, result); } trans.Commit(); } catch { trans.Rollback(); } finally { if (conn != null) { conn.Close(); } } }
public static bool TestVersion(string version, SqliteConnection MainSQLiteConnection) { try { using (var contents = SQLiteUtilities.MainSQLiteConnection.CreateCommand()) { contents.CommandText = "SELECT Nr FROM [Version]"; var r = contents.ExecuteReader(); while (r.Read()) { Console.WriteLine("Version: " + r["Nr"]); if ( r["Nr"].ToString() == version) return true; else return false; } } } catch (Exception ex) { DataAccessLayer.ExceptionWriter.WriteLogFile(ex); } return false; }
/// <summary> /// Initializes a new instance of the <see cref="Tasky.DL.TaskDatabase"/> TaskDatabase. /// if the database doesn't exist, it will create the database and all the tables. /// </summary> public ADODatabase(string dbPath) { var output = ""; path = dbPath; // create the tables bool exists = File.Exists (dbPath); if (!exists) { connection = new SqliteConnection ("Data Source=" + dbPath); connection.Open (); var commands = new[] { "CREATE TABLE [Items] (_id INTEGER PRIMARY KEY ASC, Name NTEXT, Notes NTEXT, Done INTEGER);" }; foreach (var command in commands) { using (var c = connection.CreateCommand ()) { c.CommandText = command; var i = c.ExecuteNonQuery (); } } } else { // already exists, do nothing. } Console.WriteLine (output); }
public void CreateDataBase(string dbPath, string pwd) { if (!File.Exists(dbPath)) { SqliteConnection.CreateFile(dbPath); if (string.IsNullOrEmpty(pwd)) { using (SqliteConnection conn = new SqliteConnection("Data Source=" + dbPath)) { try { conn.SetPassword(pwd); } catch { conn.Close(); throw; } finally { conn.Close(); } } } } else throw new Exception("已经存在名叫:" + dbPath + "的数据库!"); }
public IDbConnection Get() { IDbConnection dbConnection = new SQL.SqliteConnection(connectionString); dbConnection.Open(); return(dbConnection); }
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"); } }
internal static void DatabaseSQLAdd(string query) //삽입이라고 썼지만 삭제도 가능 { IDbConnection dbConnection = new SqliteConnection(GetDBFilePath()); dbConnection.Open(); // DB 열기 dbCommand = dbConnection.CreateCommand(); dbCommand.CommandText = query; // 쿼리 입력 try { dbCommand.ExecuteNonQuery(); // 쿼리 실행 } catch (Exception e) { Debug.LogError(e); //test.text = e.ToString(); } dbCommand.Dispose(); dbCommand = null; dbConnection.Close(); dbConnection = null; Debug.Log("데이터 삽입 완료"); //test.text = "결과 데이터 삽입 완료"; }
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(); } } }
/// <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); } }
public void Init() { connection = new SQLiteConnection(); connection.ConnectionString = "Data Source=" + "save.db"; Open(); }
public CodeProjectDatabase () { dbPath = Path.Combine ( Environment.GetFolderPath (Environment.SpecialFolder.Personal), "items.db3"); bool exists = File.Exists (dbPath); if (!exists) { SqliteConnection.CreateFile (dbPath); } var connection = new SqliteConnection ("Data Source=" + dbPath); connection.Open (); if (!exists) { var commands = new[]{ "CREATE TABLE [Member] (Key integer, Name ntext, ArticleCnt integer, BlogCnt integer, Reputation ntext, IsMe integer);" }; foreach (var command in commands) { using (var c = connection.CreateCommand ()) { c.CommandText = command; c.ExecuteNonQuery (); } } } }
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 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; }
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 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."); }
public SQLiteDatabase(string connectionString) { createDatabase(connectionString); _conn = new SqliteConnection(); _conn.ConnectionString = connectionString; _conn.Open(); }
public void Initialise(string connectionString) { m_connectionString = connectionString; m_log.Info("[ESTATE DB]: Sqlite - connecting: "+m_connectionString); m_connection = new SqliteConnection(m_connectionString); try { m_connection.Open(); } catch (Exception ex) { throw new Exception("SQLite has errored out on opening the database. If you are on a 64 bit system, please run OpenSim.32BitLaunch.exe and try again. If this is not a 64 bit error :" + ex); } Assembly assem = GetType().Assembly; Migration m = new Migration(m_connection, assem, "EstateStore"); m.Update(); //m_connection.Close(); // m_connection.Open(); Type t = typeof(EstateSettings); m_Fields = t.GetFields(BindingFlags.NonPublic | BindingFlags.Instance | BindingFlags.DeclaredOnly); foreach (FieldInfo f in m_Fields) if (f.Name.Substring(0, 2) == "m_") m_FieldMap[f.Name.Substring(2)] = f; }
/// <summary> /// Creates and opens a database. /// </summary> /// <param name="name">Database name.</param> /// <returns>Created database, or null if cannot be created.</returns> public override Database CreateDatabase(string name) { Database database = null; try { database = new Database(name, DEFAULT_DATABASE_OPTION_NEW, DEFAULT_DATABASE_OPTION_COMPRESS); string dbPath = Path.Combine(GetBasePath(), name); // Checks if a file already exists, if not creates the file. bool exists = File.Exists (dbPath); if (!exists) { SqliteConnection.CreateFile (dbPath); } SqliteConnection connection = new SqliteConnection( "Data Source=" + dbPath + ",version=" + DEFAULT_DATABASE_VERSION); connection.Open(); StoreConnection(name, connection); StoreDatabase(name, database); } catch (Exception e) { SystemLogger.Log(SystemLogger.Module.PLATFORM, "Exception creating database.", e); database = null; } return database; }
public static bool DatabaseHelperCanAccessDatabase(String overrideConnectionInfo) { bool result = false; SqliteConnection connection; if ( (overrideConnectionInfo != null) && (overrideConnectionInfo.Length > 0) ) { connection = new SqliteConnection(overrideConnectionInfo); } else { connection = new SqliteConnection(GetConnectionString()); } try { connection.Open(); result = (connection.State == ConnectionState.Open); } catch { } finally { if (connection.State == ConnectionState.Open) connection.Close(); } return result; }
internal static void Init(Mono.Data.Sqlite.SqliteConnection sqliteConnection) { ToLowerFunction.RegisterFunction(typeof(ToLowerFunction)); ToUpperFunction.RegisterFunction(typeof(ToUpperFunction)); ContainsFunction.RegisterFunction(typeof(ContainsFunction)); FormatNumberFunction.RegisterFunction(typeof(FormatNumberFunction)); FormatDateFunction.RegisterFunction(typeof(FormatDateFunction)); }
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(); }
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); }
private void addScore(Mono.Data.Sqlite.SqliteConnection conn, Mono.Data.Sqlite.SqliteTransaction trans, Score score) { string sql = @" INSERT INTO [Score] ([AthleteAID],[AthleteBID],[Date],[IsUnfinished],[TimeModified],[Guid],[IsDeleted],[SportID],[VenueID],[Type1],[PointsA],[PointsB],[InnerPoints1A],[InnerPoints1B],[InnerPoints2A],[InnerPoints2B],[InnerPoints3A],[InnerPoints3B],[InnerPoints4A],[InnerPoints4B],[InnerPoints5A],[InnerPoints5B],[InnerPoints6A],[InnerPoints6B],[InnerPoints7A],[InnerPoints7B],[InnerPoints8A],[InnerPoints8B],[InnerPoints9A],[InnerPoints9B],[InnerPoints10A],[InnerPoints10B],[OpponentConfirmation],[ExtraData]) VALUES (@AthleteAID,@AthleteBID,@Date,@IsUnfinished,@TimeModified,@Guid,@IsDeleted,@SportID,@VenueID,@Type1,@PointsA,@PointsB,@InnerPoints1A,@InnerPoints1B,@InnerPoints2A,@InnerPoints2B,@InnerPoints3A,@InnerPoints3B,@InnerPoints4A,@InnerPoints4B,@InnerPoints5A,@InnerPoints5B,@InnerPoints6A,@InnerPoints6B,@InnerPoints7A,@InnerPoints7B,@InnerPoints8A,@InnerPoints8B,@InnerPoints9A,@InnerPoints9B,@InnerPoints10A,@InnerPoints10B,@OpponentConfirmation,@ExtraData)"; var command = createCommandForScore(sql, conn, trans, score); command.ExecuteNonQuery(); command.Dispose(); }
private bool _GetDataBaseSongInfos(int songID, out string artist, out string title, out int numPlayed, out DateTime dateAdded, string filePath) { artist = String.Empty; title = String.Empty; numPlayed = 0; dateAdded = DateTime.Today; 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 = "SELECT Artist, Title, NumPlayed, DateAdded FROM Songs WHERE [id] = @id"; command.Parameters.Add("@id", DbType.String, 0).Value = songID; SQLiteDataReader reader; 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); dateAdded = new DateTime(reader.GetInt64(3)); reader.Dispose(); return(true); } if (reader != null) { reader.Dispose(); } } } return(false); }
public virtual void Close() { lock (_Mutex) { if (_Connection != null) { _Connection.Dispose(); _Connection = null; } } }
private SqliteCommand createCommandForScore(string sql, Mono.Data.Sqlite.SqliteConnection conn, Mono.Data.Sqlite.SqliteTransaction trans, Score score) { var command = conn.CreateCommand(); command.Transaction = trans; command.CommandText = sql; command.Parameters.Add(new SqliteParameter("@AthleteAID", score.AthleteAID)); command.Parameters.Add(new SqliteParameter("@AthleteBID", score.AthleteBID)); command.Parameters.Add(new SqliteParameter("@Date", score.Date)); command.Parameters.Add(new SqliteParameter("@IsUnfinished", (int)(score.IsUnfinished ? 1 : 0))); command.Parameters.Add(new SqliteParameter("@TimeModified", score.TimeModified)); command.Parameters.Add(new SqliteParameter("@Guid", score.Guid.ToString())); command.Parameters.Add(new SqliteParameter("@IsDeleted", score.IsDeleted)); command.Parameters.Add(new SqliteParameter("@SportID", score.SportID)); if (score.VenueID == null) { command.Parameters.Add(new SqliteParameter("@VenueID", DBNull.Value)); } else { command.Parameters.Add(new SqliteParameter("@VenueID", score.VenueID)); } if (score.Type1 == null) { command.Parameters.Add(new SqliteParameter("@Type1", DBNull.Value)); } else { command.Parameters.Add(new SqliteParameter("@Type1", score.Type1)); } command.Parameters.Add(new SqliteParameter("@PointsA", score.PointsA)); command.Parameters.Add(new SqliteParameter("@PointsB", score.PointsB)); for (int i = 1; i <= 10; ++i) { command.Parameters.Add(new SqliteParameter("@InnerPoints" + i.ToString() + "A", score.InnerPointsA[i - 1])); command.Parameters.Add(new SqliteParameter("@InnerPoints" + i.ToString() + "B", score.InnerPointsB[i - 1])); } command.Parameters.Add(new SqliteParameter("@OpponentConfirmation", score.AthleteBConfirmation)); if (score.ExtraData == null) { command.Parameters.Add(new SqliteParameter("@ExtraData", DBNull.Value)); } else { command.Parameters.Add(new SqliteParameter("@ExtraData", score.ExtraData)); } return(command); }
private void executeNonqueryCommand(string sql, Mono.Data.Sqlite.SqliteConnection conn, Mono.Data.Sqlite.SqliteTransaction trans) { var command = conn.CreateCommand(); if (trans != null) { command.Transaction = trans; } command.CommandText = sql; command.ExecuteNonQuery(); command.Dispose(); }
public static void Create(string dbpath, params string[] sql) { if (!File.Exists(dbpath)) { try{ SQLiteConnection.CreateFile(dbpath); Command(dbpath, sql); } catch (Exception) { } } // CREATE TABLE wins(id INTEGER PRIMARY KEY AUTOINCREMENT,[IDCardNo] VARCHAR (50),endtime TimeStamp NOT NULL DEFAULT CURRENT_TIMESTAMP); }
private string DBCreate() { //<summary><summary/> //<params><params/> //<output><output/> #region DBCreate_declarations string strPath = Path.GetDirectoryName(Environment.GetCommandLineArgs()[0]); string strDBPath = strPath + "/" + _localDBName; var testStr = Path.GetPathRoot(strPath); #endregion #region DBCreate_validaton // Create the database if it doesn't already exist if (!File.Exists(strDBPath)) { SqliteConnection.CreateFile(strDBPath); } else { return(strDBPath); //db exists so exit now and return default path } #endregion #region DBCreate_procedure // Create connection to the database var sqliteConn = new Mono.Data.Sqlite.SqliteConnection("Data Source=" + strDBPath); // Set the structure of the database if (File.Exists(strDBPath)) { var sqliteCMD = new[] { "CREATE TABLE LocalSettings (Type TEXT, StringValue1 TEXT, StringValue2 TEXT, StringValue3 TEXT, StringValue4 TEXT, StringValue5 TEXT)" }; sqliteConn.Open(); foreach (var cmd in sqliteCMD) { using (var c = sqliteConn.CreateCommand()) { c.CommandText = cmd; c.CommandType = System.Data.CommandType.Text; c.ExecuteNonQuery(); } } } sqliteConn.Close(); return(strDBPath); #endregion }
private object executeScalarCommand(string sql, Mono.Data.Sqlite.SqliteConnection conn, Mono.Data.Sqlite.SqliteTransaction trans) { var command = conn.CreateCommand(); if (trans != null) { command.Transaction = trans; } command.CommandText = sql; object result = command.ExecuteScalar(); command.Dispose(); return(result); }
public void CreateNew() { try { sqliteConnection.CreateFile(m_DataBaseName); m_Connection = new sqliteConnection("Data Source=" + m_DataBaseName + ";Version=3;FailIfMissing=True"); m_Connection.Open(); } catch (Exception ex) { Console.WriteLine("Create DB failed: " + ex); } }
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 void MakeCvsThenSend() { /*Make CVS file*/ //Filepath for the data.csv file string fileName = Application.persistentDataPath + "/" + "data.csv"; //timestamp, page, date, feeling, urge, intensity, thoughts using (StreamWriter writer = new StreamWriter(fileName, false, Encoding.UTF8)) { //Write the each columns title, for each field in pateint_info writer.Write("Timestamp | Page | Date | Feeling | Urge | Intensity | Thoughts"); writer.Write(Environment.NewLine); //Write data from the the db conn = new sqliteConnection("URI=file:" + Application.persistentDataPath + "/" + dbName + ";Version=3;FailIfMissing=True;mode=cvs"); conn.Open(); var Sqlcommand = new sqliteCommand("select * from patient_info", conn); var reader = Sqlcommand.ExecuteReader(); while (reader.Read()) { writer.Write(reader["timestamp"] + "|" + reader["page"] + "|" + reader["date"] + "|" + reader["feeling"] + "|" + reader["urge"] + "|" + reader["intensity"] + "|" + reader["thoughts"]); writer.Write(Environment.NewLine); } conn.Close(); } /*SendMail*/ MailMessage mail = new MailMessage(); mail.From = new MailAddress("*****@*****.**"); mail.To.Add(email.text); mail.Subject = "Patients Data from App"; mail.Body = "The File attached is the patients data (every entry is seperated by '|' when importing into excel or google sheets that custom seporator should be used) "; //Add the data.csv file to the email mail.Attachments.Add(new Attachment(fileName)); SmtpClient smtpServer = new SmtpClient(); smtpServer.Host = "smtp.gmail.com"; smtpServer.Port = 587; //Has to be 587 so it works on Networks with port protections. smtpServer.DeliveryMethod = SmtpDeliveryMethod.Network; smtpServer.Credentials = new System.Net.NetworkCredential("*****@*****.**", "HeartRateAppFxu2018") as ICredentialsByHost; smtpServer.EnableSsl = true; ServicePointManager.ServerCertificateValidationCallback = delegate(object s, X509Certificate certificate, X509Chain chain, SslPolicyErrors sslPolicyErrors) { return(true); }; smtpServer.Send(mail); }