/// <summary> /// Creates a new SQLite profiling data provider and opens or creates a new database stored in a file. /// </summary> public ProfilingDataSQLiteWriter(string fileName, bool profileUnitTests, string[] unitTestNames) { if (File.Exists(fileName)) { throw new IOException("File already exists!"); } SQLiteConnectionStringBuilder conn = new SQLiteConnectionStringBuilder(); conn.Add("Data Source", fileName); conn.Add("New", true); // Disable protecting the database on crashes - it's a new database, // it may go corrupt if we crash during DB creation. Disabling journalling // makes Inserts faster. conn.Add("Journal Mode", "OFF"); conn.Add("Synchronous", "OFF"); this.connection = new SQLiteConnection(conn.ConnectionString); this.connection.Open(); InitializeTables(); File.SetAttributes(fileName, FileAttributes.Compressed); this.profileUnitTests = profileUnitTests; this.unitTestNames = unitTestNames; if (profileUnitTests && unitTestNames == null) { throw new InvalidOperationException("Please add unit tests to filter!"); } }
private void FillData(string selectCommand) { /// <remarks> /// Setting the path where the database file is located /// </remarks> string database = "RFIDTAGSTORE.s3db"; /// <remarks> /// Creating a ConnectionString pointing to the database file /// </remarks> SQLiteConnectionStringBuilder adatasource = new SQLiteConnectionStringBuilder(); adatasource.Add("Data Source", database); adatasource.Add("Version", "3"); adatasource.Add("New", "False"); adatasource.Add("Compress", "True"); SQLiteConnection conn = new SQLiteConnection("Data Source=RFIDTAGSTORE.s3db"); dataGridView1.AutoGenerateColumns = true; // string newselectCommand = "select * from Athletes"; SQLiteDataAdapter da = new SQLiteDataAdapter(selectCommand, adatasource.ConnectionString); conn.Open(); DataSet ds = new DataSet(); SQLiteCommandBuilder commandBuilder = new SQLiteCommandBuilder(da); da.Fill(ds); //,"Design1" dataGridView1.DataSource = ds.Tables[0]; // dataGridView1.DataMember = "dgm"; conn.Close(); }
private void PopulateMenu() { string NewQuery = "Select * from Races"; //+ "\'%"+ SelectRace.Text+"\'"; Console.WriteLine(NewQuery); //Pass string to function here.... /// <remarks> /// Creating a DataSet to feed the DataGridView /// </remarks> // DataSet results = new DataSet(); try { /// <remarks> /// Setting the path where the database file is located /// </remarks> string database = "RFIDTAGSTORE.s3db"; /// <remarks> /// Creating a ConnectionString pointing to the database file /// </remarks> SQLiteConnectionStringBuilder datasource = new SQLiteConnectionStringBuilder(); datasource.Add("Data Source", database); datasource.Add("Version", "3"); datasource.Add("New", "False"); datasource.Add("Compress", "True"); /// <remarks> /// Starting the connection and sending the query /// </remarks> using (SQLiteConnection aconnection = new SQLiteConnection(datasource.ConnectionString)) { using (SQLiteDataAdapter adapter = new SQLiteDataAdapter(NewQuery, aconnection)) { /// <remarks> /// Populating the DataGridView /// </remarks> adapter.Fill(results); ChooseRace.DataSource = results.Tables[0]; ChooseRace.DisplayMember = "RaceName"; ChooseRace.BindingContext = BindingContext; //dataGridView1.DataSource = results.Tables[0].DefaultView; } } } catch (Exception error) { MessageBox.Show("Exception caught: " + error.Message); } }
/// <summary> /// To get the full SQLite connection string given the SQLite database path /// </summary> public static string GetSQLiteConnectionString(string dbFileName, bool create = true) { // returns "Data Source= " + dbFileName + ";New=False;Compress=True;Version=3"; SQLiteConnectionStringBuilder conn = new SQLiteConnectionStringBuilder(); conn.DataSource = dbFileName; conn.Version = 3; conn.FailIfMissing = !create; conn.Add("New", create); conn.Add("Compress", true); return(conn.ConnectionString); }
public static bool SetPassword(string password, bool withPassword) { try { var conn = new SQLiteConnectionStringBuilder { DataSource = PathDatabaseFile(), Version = 3, }; if (withPassword) { conn.Add("Password", PasswordDatabase); } using (var con = new SQLiteConnection(conn.ConnectionString)) { con.Open(); con.ChangePassword(password); using (SQLiteCommand command = new SQLiteCommand("PRAGMA schema_version;", con)) { var ret = command.ExecuteScalar(); } con.Close(); return(true); } } catch (SQLiteException) { return(false); } }
public DatabaseDriver() { this.DatabaseEngine = Config.GetDatabaseEngine(); DbConnectionStringBuilder Builder; if (this.DatabaseEngine == DatabaseEngine.Sqlite) { Builder = new SQLiteConnectionStringBuilder(); string FullPath = Path.Combine(Utils.AssemblyPath, Config.GetType <string>("Database", "Database") + ".sqlite3"); IsNewDatabase = !File.Exists(FullPath) || new FileInfo(FullPath).Length == 0; Builder.Add("Data Source", FullPath); Connection = new SQLiteConnection(Builder.ConnectionString); } else if (this.DatabaseEngine == DatabaseEngine.Mysql) { Builder = new MySqlConnectionStringBuilder(); Builder.Add("Server", Config.GetType <string>("Database", "Hostname")); Builder.Add("Port", Config.GetType <int>("Database", "Port")); Builder.Add("User ID", Config.GetType <string>("Database", "Username")); Builder.Add("Password", Config.GetType <string>("Database", "Password")); Builder.Add("Database", Config.GetType <string>("Database", "Database")); Connection = new MySqlConnection(Builder.ConnectionString); } else { throw new Exception("Invalid Database type."); } }
SQLiteConnection OpenConnection() { SQLiteConnectionStringBuilder conn = new SQLiteConnectionStringBuilder(); conn.Add("Data Source", databaseFileName); SQLiteConnection connection = new SQLiteConnection(conn.ConnectionString); connection.Open(); return(connection); }
/// <summary> /// To get the full SQLite connection string given the SQLite database path /// </summary> public static string GetSQLiteConnectionString(string dbFileName) { var conn = new SQLiteConnectionStringBuilder { DataSource = dbFileName, Version = 3, FailIfMissing = true }; conn.Add("Compress", true); return(conn.ConnectionString); }
/// <summary> /// Internal. Creates a new DbConnection for the .NET/Windows implementation. /// </summary> /// <returns>The database connection.</returns> /// <param name="file">The SQLite File to connect to</param> private static DbConnection ConnectDotNet(string file) { SQLiteConnectionStringBuilder strBuild = new SQLiteConnectionStringBuilder(); strBuild.Add("Data Source", file); SQLiteConnection conn = new SQLiteConnection(strBuild.ToString()); conn.Open(); return(conn); }
public SQLiteOperate(string sqlFile) { SQLiteConnectionStringBuilder connectString = new SQLiteConnectionStringBuilder(); connectString.DataSource = sqlFile; connectString.FailIfMissing = true; connectString.Pooling = false; connectString.Add("Max Pool Size", 5); connectString.JournalMode = SQLiteJournalModeEnum.Wal; conn = new SQLiteConnection(connectString.ToString()); }
/// <summary> /// Creates a new SQLite profiling data provider and opens a database stored in a file. /// </summary> public ProfilingDataSQLiteProvider(string fileName) { this.nameMappingCache = new Dictionary <int, NameMapping>(); SQLiteConnectionStringBuilder conn = new SQLiteConnectionStringBuilder(); conn.Add("Data Source", fileName); this.connection = new SQLiteConnection(conn.ConnectionString); this.connection.Open(); CheckFileVersion(); }
public SQLiteOpenHelper(string DB) { builder = new SQLiteConnectionStringBuilder(); builder.DataSource = DB; builder.DefaultTimeout = 5000; builder.SyncMode = SynchronizationModes.Off; builder.JournalMode = SQLiteJournalModeEnum.Memory; builder.Add("cache", "shared"); builder.Pooling = true; builder.PageSize = 65536; builder.CacheSize = 16777216; builder.FailIfMissing = false; builder.Version = 3; }
/// <summary> /// To get the full SQLite connection string given the SQLite database path /// </summary> public static string GetSQLiteConnectionString(string dbFileName) { if (String.IsNullOrEmpty(dbFileName)) { throw new ArgumentException("dbFileName is null or empty.", "dbFileName"); } var conn = new SQLiteConnectionStringBuilder { DataSource = dbFileName, Version = 3, FailIfMissing = true }; conn.Add("Compress", true); return(conn.ConnectionString); }
private static string GetConnectionString(string dbPath) { var connectionBuilder = new SQLiteConnectionStringBuilder(); connectionBuilder.DataSource = dbPath; connectionBuilder.CacheSize = (int)-10.Megabytes(); connectionBuilder.DateTimeKind = DateTimeKind.Utc; connectionBuilder.JournalMode = OsInfo.IsOsx ? SQLiteJournalModeEnum.Truncate : SQLiteJournalModeEnum.Wal; connectionBuilder.Pooling = true; connectionBuilder.Version = 3; if (OsInfo.IsOsx) { connectionBuilder.Add("Full FSync", true); } return(connectionBuilder.ConnectionString); }
public static SQLiteConnectionStringBuilder GetDefault() { var bob = new SQLiteConnectionStringBuilder() { Version = 3, JournalMode = SQLiteJournalModeEnum.Wal, SyncMode = SynchronizationModes.Normal, BaseSchemaName = "main", Pooling = true, BusyTimeout = 5 * 60 * 1000, // 5 minutes PrepareRetries = 3, WaitTimeout = 5 * 60 * 1000 }; bob.Add("busy_timeout", 5 * 60 * 1000); // 5 minutes return(bob); }
private void Form1_Load(object sender, EventArgs e) { builder.DataSource = "./sample.db"; builder.BusyTimeout = 5000; builder.DateTimeFormat = SQLiteDateFormats.InvariantCulture; builder.DateTimeKind = DateTimeKind.Local; builder.JournalMode = SQLiteJournalModeEnum.Wal; builder.SyncMode = SynchronizationModes.Off; builder.CacheSize = 4000; builder.Add("cache", "shared"); string connStr = builder.ConnectionString; conn = new SQLiteConnection(connStr); conn.Open(); OrmConfiguration.DefaultDialect = SqlDialect.SqLite; RefreshGrid(); }
public string GetConnectionString(string partialConnStr, string salt, string encryptedPwd = null) { if (ProviderType == ProviderType.SQLITE) { SQLiteConnectionStringBuilder builder = new SQLiteConnectionStringBuilder(); builder.ConnectionString = partialConnStr; if (!string.IsNullOrWhiteSpace(encryptedPwd)) { string clearPwd = Util.DecryptPassword(encryptedPwd, salt); builder.Add("Password", clearPwd); } return(builder.ToString()); } else { OleDbConnectionStringBuilder builder = new OleDbConnectionStringBuilder(); builder.ConnectionString = partialConnStr; string clearPwd = Util.DecryptPassword(encryptedPwd, salt); builder.Add("Password", clearPwd); return(builder.ToString()); } }
/// <summary> /// Creates a new SQLite profiling data provider and opens a database stored in a file. /// </summary> ProfilingDataSQLiteProvider(string fileName, bool allowUpgrade) { this.nameMappingCache = new Dictionary <int, NameMapping>(); SQLiteConnectionStringBuilder conn = new SQLiteConnectionStringBuilder(); conn.Add("Data Source", fileName); this.connection = new SQLiteConnection(conn.ConnectionString); try { this.connection.Open(); CheckFileVersion(allowUpgrade); } catch { try { connection.Dispose(); } catch { // ignore errors during cleanup, rethrow the first error instead } throw; } }
private void SaveData(DataGridView dgv, string selectCommand) { string messageboxstring = string.Format("Did you select the correct race to add them to? {0}", ChooseRace.Text); DialogResult result1 = MessageBox.Show(messageboxstring, "Import Runners", MessageBoxButtons.YesNo); if (result1 == DialogResult.Yes) { //clean the database, remove any racers for the race... string cleantable = string.Format("DELETE FROM Athletes WHERE Race= '{0}'", ChooseRace.Text); SimpleSQliteInsert(cleantable); /// <remarks> /// Setting the path where the database file is located /// </remarks> string database = "RFIDTAGSTORE.s3db"; /// <remarks> /// Creating a ConnectionString pointing to the database file /// </remarks> SQLiteConnectionStringBuilder datasource2 = new SQLiteConnectionStringBuilder(); datasource2.Add("Data Source", database); datasource2.Add("Version", "3"); datasource2.Add("New", "False"); datasource2.Add("Compress", "True"); //stopp editing dataGridView1.EndEdit(); //turn the datagridview into a dataset DataSet ds = new DataSet(); DataTable dt = new DataTable("DataFromCSV"); //ds.Tables.Add(dt); //create colums // foreach (DataGridViewColumn col in dataGridView1.Columns) // { // dt.Columns.Add(col.DataPropertyName, typeof(string)); // } //fill the rows of table: string connectionString = @"Data Source=RFIDTAGSTORE.s3db"; System.Data.SQLite.SQLiteConnection RFIDTAGSTORE2 = new SQLiteConnection(connectionString); StringBuilder insert = new StringBuilder(); foreach (DataGridViewRow row in dataGridView1.Rows) { if (row.IsNewRow) { continue; } for (int c = 0; c < row.Cells.Count; c++) { // Append the cells data followed by a comma to delimit. insert.Append(row.Cells[c].Value + "|"); } string InsertRow = insert.ToString(); InsertRow = InsertRow.Remove(InsertRow.Length - 1); string[] values = InsertRow.Split('|'); RFIDTAGSTORE2.Open(); using (SQLiteTransaction sqlTransaction = RFIDTAGSTORE2.BeginTransaction()) { string insertSQL2 = "INSERT INTO Athletes(BibNumber, Fname, Lname, DOB, Age, Sex, Race) values "; insertSQL2 += "(@bib, @fname, @lname, @dob, @age, @sex, @racename)"; // insertSQL2 += "(@InsertRow)"; Console.WriteLine(insertSQL2); SQLiteCommand insertCommand = new SQLiteCommand(insertSQL2, RFIDTAGSTORE2); insertCommand.Parameters.AddWithValue("@bib", values[0].PadLeft(3, '0')); insertCommand.Parameters.AddWithValue("@fname", values[1]); insertCommand.Parameters.AddWithValue("@lname", values[2]); insertCommand.Parameters.AddWithValue("@dob", values[3]); insertCommand.Parameters.AddWithValue("@age", values[4]); insertCommand.Parameters.AddWithValue("@sex", values[5]); insertCommand.Parameters.AddWithValue("@racename", ChooseRace.Text); insertCommand.ExecuteNonQuery(); sqlTransaction.Commit(); RFIDTAGSTORE2.Close(); insert.Clear(); } } /* * using(SQLiteConnection conn = new SQLiteConnection(datasource2.ConnectionString)) * { * * * selectCommand = "Select BibNumber, Fname, Lname, Age, DOB, Sex from Athletes"; * * conn.Open(); * SQLiteDataAdapter da = new SQLiteDataAdapter(selectCommand, datasource2.ConnectionString); * SQLiteCommandBuilder commandBuilder = new SQLiteCommandBuilder(da); * // DataTable dt = dgv.DataSource as DataTable; * da.MissingSchemaAction = MissingSchemaAction.Ignore; * da.MissingMappingAction = MissingMappingAction.Passthrough; * da.Update(dt); * dt.AcceptChanges(); * Console.WriteLine("We think we just updated the table"); * * conn.Close(); * } * */ //Tell them the import worked and then close the window string messageboxstring2 = string.Format("Import appears to have worked for {0}. Close this window and check the result in the runners page.", ChooseRace.Text); DialogResult result2 = MessageBox.Show(messageboxstring2, "Import OK", MessageBoxButtons.YesNo); if (result2 == DialogResult.Yes) { //close the current window this.Close(); } } }
public virtual SQLiteConnection OpenConnection(string dbFilename = null) { if (IsMemoryDatabase) { Connection = new SQLiteConnection(MemoryConnectionString); Connection.Open(); //Connection.EnableExtensions(true); //Connection.LoadExtension("SQLite.Interop.dll", "sqlite3_json_init"); return(Connection); } if (dbFilename == null && !string.IsNullOrEmpty(App.Config["dbconnectionstring"].ToStringValue())) { Connection = new SQLiteConnection(App.Config["dbconnectionstring"].ToStringValue()); Connection.Open(); return(Connection); } if (string.IsNullOrEmpty(dbFilename)) { dbFilename = (!string.IsNullOrEmpty(App.Config["dbfilename"].ToStringValue())) ? App.Config["dbfilename"].ToStringValue() : Name; } if (!string.IsNullOrEmpty(dbFilename)) { var sb = new SQLiteConnectionStringBuilder(); sb.DataSource = dbFilename; var dbObject = App.Config["database"]; if (dbObject != null) { sb.Flags = SQLiteConnectionFlags.UseConnectionPool; //tt.NoSharedFlags = false; sb.JournalMode = (SQLiteJournalModeEnum)dbObject["journalmode"].ToInteger((int)SQLiteJournalModeEnum.Default); sb.Pooling = dbObject["pooling"].ToBoolean(true); sb.ReadOnly = dbObject["readonly"].ToBoolean(false); sb.Add("cache", dbObject["cache"].ToStringValue("shared")); sb.Add("Compress", dbObject["compress"].ToStringValue("False")); sb.SyncMode = (SynchronizationModes)dbObject["syncmode"].ToInteger((int)SynchronizationModes.Normal); //sb.DefaultIsolationLevel = System.Data.IsolationLevel.ReadUncommitted; //tt.DefaultDbType = System.Data.DbType. //var dd = new SQLiteConnection(; } else { //sb.DefaultIsolationLevel = System.Data.IsolationLevel.ReadUncommitted; sb.Flags = SQLiteConnectionFlags.UseConnectionPool; //tt.NoSharedFlags = false; //sb.JournalMode = SQLiteJournalModeEnum.Default; sb.Pooling = true; sb.ReadOnly = false; sb.Add("cache", "shared"); sb.Add("Compress", "False"); //sb.SyncMode = SynchronizationModes.Normal; } Connection = new SQLiteConnection(sb.ConnectionString); Connection.Open(); return(Connection); } throw new ApplicationException("No database connection found"); }