/// <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");
}