public override void ConnectToDatabase(string connectionString, string migratorName, bool validateTables)
{
_connectionString = connectionString;
string[] s1 = _connectionString.Split(new[] {"Data Source=", ","}, StringSplitOptions.RemoveEmptyEntries);
s1[0] = s1[0].Remove(0, 7);
_fileName = Path.GetFileName(s1[0]);
if (_fileName == s1[0]) //Only add this if we arn't an absolute path already
_connectionString = string.Format("Data Source=file://{0}", Path.Combine(Util.BasePathCombine(""), _fileName));
SqliteConnection connection = new SqliteConnection(_connectionString);
connection.Open();
var migrationManager = new MigrationManager(this, migratorName, validateTables);
migrationManager.DetermineOperation();
migrationManager.ExecuteOperation();
connection.Close();
}
/// <summary>
/// Shortcut to ExecuteNonQuery with SqlStatement and object[] param values
/// </summary>
/// <param name="connectionString">SQLite Connection String</param>
/// <param name="commandText">Sql Statement with embedded "@param" style parameters</param>
/// <param name="paramList">object[] array of parameter values</param>
/// <returns></returns>
public static int ExecuteNonQuery(string connectionString, string commandText, object[] paramList)
{
SqliteConnection cn = new SqliteConnection(connectionString);
IDbCommand cmd = cn.CreateCommand();
cmd.CommandText = commandText;
if (paramList != null)
{
cmd.CommandText = string.Format(commandText, paramList);
}
if (cn.State == ConnectionState.Closed)
cn.Open();
int result = cmd.ExecuteNonQuery();
cmd.Dispose();
cn.Close();
return result;
}
public bool AddMember(CodeProjectMember member)
{
var connection = new SqliteConnection("Data Source=" + dbPath);
connection.Open();
using (var command = connection.CreateCommand())
{
command.CommandText = "SELECT [Key] FROM [Member] WHERE [Key]=" + member.Id;
var r = command.ExecuteReader();
if (r.HasRows)
{
using (var uc = connection.CreateCommand())
{
uc.CommandText = "UPDATE [Member] SET " +
" [Name] = '" + member.Name + "'," +
" [ArticleCnt] = '" + member.ArticleCount + "'," +
" [BlogCnt] = '" + member.BlogCount + "'," +
" [Reputation] = '" + member.Reputation + "'," +
" WHERE [Key]=" + member.Id;
uc.ExecuteNonQuery();
}
}
else
{
using (var ic = connection.CreateCommand())
{
ic.CommandText = "INSERT INTO [Member] ([Key], [Name], [ArticleCnt], [BlogCnt], [Reputation])"
+ " VALUES(" + member.Id + ", '" + member.Name + "', '" + member.ArticleCount + "', '" + member.BlogCount + "', '" + member.Reputation + "')";
ic.ExecuteNonQuery();
}
}
}
connection.Close();
FileStorageService storage = new FileStorageService();
if (member.Avatar != null)
{
storage.WriteBytes(member.Avatar, member.Id.ToString());
}
return true;
}
public void Apply(SqliteConnection connection)
{
_connection = connection;
_log.Info("Start updating db");
_connection.Open();
try
{
CreateVcard();
SetupVersion();
}
catch (Exception e)
{
_log.Fatal("Unexpected error", e);
throw;
}
finally
{
_connection.Close();
}
}
public override void ConnectToDatabase(string connectionString, string migratorName, bool validateTables)
{
_connectionString = connectionString;
string[] s1 = _connectionString.Split(new[] { "Data Source=", "," }, StringSplitOptions.RemoveEmptyEntries);
bool needsUTFConverted = false;
_fileName = Path.GetFileName(s1[0]);
if (s1[0].EndsWith(";"))
{
_fileName = Path.GetFileNameWithoutExtension(s1[1].Substring(7, s1[1].Length - 7)) + "utf8.db";
_connectionString = "Data Source=file://" + _fileName;
s1 = new string[1] { "file://" + _fileName };
needsUTFConverted = true;
_hadToConvert = true;
}
if (_fileName == s1[0]) //Only add this if we arn't an absolute path already
_connectionString = _connectionString.Replace("Data Source=", "Data Source=" + Util.BasePathCombine("") + "\\");
SqliteConnection connection = new SqliteConnection(_connectionString);
connection.Open();
var migrationManager = new MigrationManager(this, migratorName, validateTables);
migrationManager.DetermineOperation();
migrationManager.ExecuteOperation();
connection.Close();
if (needsUTFConverted && _hadToConvert)
{
string file = connectionString.Split(new[] { "Data Source=", "," }, StringSplitOptions.RemoveEmptyEntries)[1].Substring(7);
if (File.Exists(file))
{
//UTF16 db, gotta convert it
System.Data.SQLite.SQLiteConnection conn = new System.Data.SQLite.SQLiteConnection("Data Source=" + file + ";version=3;UseUTF16Encoding=True");
conn.Open();
var RetVal = new List<string>();
using (var cmd = new System.Data.SQLite.SQLiteCommand("SELECT name FROM Sqlite_master", conn))
{
using (IDataReader rdr = cmd.ExecuteReader())
{
while (rdr.Read())
{
for (int i = 0; i < rdr.FieldCount; i++)
{
RetVal.Add(rdr.GetValue(i).ToString());
}
}
}
}
foreach (string table in RetVal)
{
if (TableExists(table) && !table.StartsWith("sqlite") && !table.StartsWith("idx_") && table != "aurora_migrator_version")
{
var retVal = new List<object[]>();
using (var cmd = new System.Data.SQLite.SQLiteCommand("SELECT * FROM " + table, conn))
{
using (IDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
List<object> obs = new List<object>();
for (int i = 0; i < reader.FieldCount; i++)
{
Type r = reader[i].GetType();
if (r == typeof(DBNull))
obs.Add(null);
else
obs.Add(reader[i].ToString());
}
retVal.Add(obs.ToArray());
}
}
}
try
{
if(retVal.Count > 0)
InsertMultiple(table, retVal);
}
catch { }
}
}
}
}
}
public void Test2()
{
Console.WriteLine("Test2 Start.");
Console.WriteLine("Create connection...");
SqliteConnection con = new SqliteConnection();
string dbFilename = @"SqliteTest3.db";
string cs = string.Format("Version=3,uri=file:{0}", dbFilename);
Console.WriteLine("Set connection String: {0}", cs);
if (File.Exists(dbFilename))
File.Delete(dbFilename);
con.ConnectionString = cs;
Console.WriteLine("Open database...");
con.Open();
Console.WriteLine("create command...");
IDbCommand cmd = con.CreateCommand();
Console.WriteLine("create table TEST_TABLE...");
cmd.CommandText = "CREATE TABLE TBL ( ID NUMBER, NAME TEXT)";
cmd.ExecuteNonQuery();
Console.WriteLine("insert row 1...");
cmd.CommandText = "INSERT INTO TBL ( ID, NAME ) VALUES (1, 'ONE' )";
cmd.ExecuteNonQuery();
Console.WriteLine("insert row 2...");
cmd.CommandText = "INSERT INTO TBL ( ID, NAME ) VALUES (2, 'ä¸æ–‡' )";
cmd.ExecuteNonQuery();
//Console.WriteLine("commit...");
//cmd.CommandText = "COMMIT";
//cmd.ExecuteNonQuery();
Console.WriteLine("SELECT data from TBL...");
cmd.CommandText = "SELECT id,NAME FROM tbl WHERE name = 'ä¸æ–‡'";
IDataReader reader = cmd.ExecuteReader();
int r = 0;
Console.WriteLine("Read the data...");
while (reader.Read())
{
Console.WriteLine(" Row: {0}", r);
int i = reader.GetInt32(reader.GetOrdinal("ID"));
Console.WriteLine(" ID: {0}", i);
string s = reader.GetString(reader.GetOrdinal("NAME"));
Console.WriteLine(" NAME: {0} = {1}", s, s == "ä¸æ–‡");
r++;
}
Console.WriteLine("Rows retrieved: {0}", r);
//alxwest: DataTable & SqliteDataAdapter currently unavailable for Silverlight
#if !SQLITE_SILVERLIGHT
SqliteCommand command = new SqliteCommand("PRAGMA table_info('TEST_TABLE')", con);
DataTable dataTable = new DataTable();
SqliteDataAdapter dataAdapter = new SqliteDataAdapter();
dataAdapter.SelectCommand = command;
dataAdapter.Fill(dataTable);
DisplayDataTable(dataTable, "Columns");
#endif
Console.WriteLine("Close and cleanup...");
con.Close();
con = null;
Console.WriteLine("Test1 Done.");
}
public void Issue_119()
{
Console.WriteLine("Test Start.");
Console.WriteLine("Create connection...");
SqliteConnection con = new SqliteConnection();
string dbFilename = @"=SqliteTest3=.db";
string cs = string.Format("Version=3,uri=file:{0}", dbFilename);
Console.WriteLine("Set connection String: {0}", cs);
if (File.Exists(dbFilename))
File.Delete(dbFilename);
con.ConnectionString = cs;
Console.WriteLine("Open database...");
con.Open();
Console.WriteLine("create command...");
IDbCommand cmd = con.CreateCommand();
Console.WriteLine("create table TEST_TABLE...");
cmd.CommandText = "CREATE TABLE TEST_TABLE ( COLA INTEGER, COLB TEXT, COLC DATETIME )";
cmd.ExecuteNonQuery();
Console.WriteLine("insert row 1...");
cmd.CommandText = "INSERT INTO TEST_TABLE ( COLA, COLB, COLC ) VALUES (123,'ABC','2008-12-31 18:19:20' )";
cmd.ExecuteNonQuery();
Console.WriteLine("insert row 2...");
cmd.CommandText = "INSERT INTO TEST_TABLE ( COLA, COLB, COLC ) VALUES (124,'DEF', '2009-11-16 13:35:36' )";
cmd.ExecuteNonQuery();
Console.WriteLine("SELECT data from TEST_TABLE...");
cmd.CommandText = "SELECT RowID, COLA, COLB, COLC FROM TEST_TABLE";
IDataReader reader = cmd.ExecuteReader();
int r = 0;
Console.WriteLine("Read the data...");
while (reader.Read())
{
Console.WriteLine(" Row: {0}", r);
int rowid = reader.GetInt32(reader.GetOrdinal("RowID"));
Console.WriteLine(" RowID: {0}", rowid);
int i = reader.GetInt32(reader.GetOrdinal("COLA"));
Console.WriteLine(" COLA: {0}", i);
string s = reader.GetString(reader.GetOrdinal("COLB"));
Console.WriteLine(" COLB: {0}", s);
DateTime dt = reader.GetDateTime(reader.GetOrdinal("COLC"));
Console.WriteLine(" COLB: {0}", dt.ToString("MM/dd/yyyy HH:mm:ss"));
r++;
}
Console.WriteLine("Close and cleanup...");
con.Close();
con = null;
Console.WriteLine("Test Done.");
}
//Issue 76 Encryption is not implemented in C#SQLite client connection and command objects
public void Issue_76()
{
Console.WriteLine("Test for Issue_76 Start.");
Console.WriteLine("Create connection...");
SqliteConnection con = new SqliteConnection();
string dbFilename = @"SqliteTest3.db";
string cs = string.Format("Version=3,uri=file:{0}", dbFilename);
Console.WriteLine("Set connection String: {0}", cs);
if (File.Exists(dbFilename))
File.Delete(dbFilename);
con.ConnectionString = cs;
Console.WriteLine("Open database...");
con.Open();
Console.WriteLine("create command...");
IDbCommand cmd = con.CreateCommand();
cmd.CommandText = "pragma hexkey='0x73656372657470617373776F72640f11'";
Console.WriteLine(cmd.CommandText);
cmd.ExecuteNonQuery();
cmd.CommandText = "create table a (b); insert into a values ('row 1');select * from a;";
Console.WriteLine(cmd.CommandText);
Console.WriteLine("Result {0}", cmd.ExecuteScalar());
Console.WriteLine("Close & Reopen Connection");
con.Close();
con.Open();
cmd.CommandText = "select * from a;";
Console.WriteLine(cmd.CommandText);
Console.WriteLine("Result {0}", cmd.ExecuteScalar());
Console.WriteLine("Close & Reopen Connection");
con.Close();
con.Open();
cmd.CommandText = "pragma hexkey='0x73656372657470617373776F72640f11'";
Console.WriteLine(cmd.CommandText);
cmd.ExecuteNonQuery();
cmd.CommandText = "select * from a;";
Console.WriteLine(cmd.CommandText);
Console.WriteLine("Result {0}", cmd.ExecuteScalar());
Console.WriteLine("Close & Reopen Connection with password");
con.Close();
con.ConnectionString = cs + ",Password=0x73656372657470617373776F72640f11";
con.Open();
cmd.CommandText = "select * from a;";
Console.WriteLine(cmd.CommandText);
Console.WriteLine("Result {0}", cmd.ExecuteScalar());
con = null;
Console.WriteLine("Issue_76 Done.");
}
public void Run() {
// Set up paths and create required folders.
try { // Trycrash-Construct
StorageKeyPath = String.Format("{0}\\Keys", StoragePath);
StorageDatabasePath = String.Format("{0}\\Sqlite.db", StoragePath);
Directory.CreateDirectory(StoragePath);
Directory.CreateDirectory(StorageKeyPath);
//Directory.CreateDirectory(StorageDatabasePath);
} catch (Exception e) {
Console.WriteLine(e);
throw;
}
// Connect or create Sqlite database and setup tables, if they don't exist.
try { // Trycrash-Construct
Database = new SqliteConnection(new DbConnectionStringBuilder {
{ "Uri", new Uri(@StorageDatabasePath).AbsoluteUri },
}.ConnectionString);
Database.Open();
// Try create threads table.
var createThreadsTableCmd = Database.CreateCommand();
createThreadsTableCmd.CommandText = String.Format(TABLETHREADS, TABLETHREADSNAME);
createThreadsTableCmd.ExecuteNonQuery();
// Try create blocks table.
var createBlocksTableCmd = Database.CreateCommand();
createBlocksTableCmd.CommandText = String.Format(TABLEBLOCKS, TABLEBLOCKSNAME);
createBlocksTableCmd.ExecuteNonQuery();
// Try create stati table.
var createStatiTableCmd = Database.CreateCommand();
createStatiTableCmd.CommandText = String.Format(TABLESTATUS, TABLESTATUSNAME);
createStatiTableCmd.ExecuteNonQuery();
} catch (Exception e) {
Console.WriteLine(e);
throw;
}
DatabaseMutex = new Mutex();
// Calculate Block limits.
BlockMaximum = BigInteger.Divide(BigInteger.Pow(8, 48), BlockSize);
// Create Threads.
var crackerThreads = new CrackerThread[Environment.ProcessorCount];
for (uint currentThreadId = 0; currentThreadId < Environment.ProcessorCount; currentThreadId++) {
crackerThreads[currentThreadId] = new CrackerThread(this, currentThreadId);
crackerThreads[currentThreadId].TryStart();
}
// Wait for user input to be 'stop'.
String userCommand;
while ((userCommand = Console.ReadLine()) != "stop") {
Console.WriteLine("Unknown command: {0}", userCommand);
}
// Stop threads.
for (uint proc = 0; proc < Environment.ProcessorCount; proc++) {
crackerThreads[proc].TryStop();
}
Database.Close();
}
// Connectio string parsing tests/
public void Test8 ()
{
Console.WriteLine ("Test8 Start.");
Console.WriteLine ("Create connection...");
SqliteConnection con = new SqliteConnection ();
string dbFilename = @"SqliteTest3.db";
// Test Read Only = True, missing db file
string cs = string.Format ("Version=3;Read Only=True;uri=file:{0}", dbFilename);
Console.WriteLine ("Set connection string: {0}", cs);
if (File.Exists (dbFilename))
File.Delete (dbFilename);
con.ConnectionString = cs;
Console.WriteLine ("Open database...");
con.Open ();
Console.WriteLine ("create command...");
IDbCommand cmd = con.CreateCommand ();
Console.WriteLine ("create table TEST_TABLE...");
cmd.CommandText = "CREATE TABLE TEST_TABLE ( COLA INTEGER, COLB TEXT )";
bool didFail = false;
try {
cmd.ExecuteNonQuery ();
} catch (Exception ex) {
didFail = true;
}
if (!didFail) {
Console.WriteLine ("Test failed!");
throw new ApplicationException("Test failed!");
}
con.Close ();
// Test Read Only = True, existng db file
cs = string.Format ("Version=3;uri=file:{0}", dbFilename);
Console.WriteLine ("Set connection string: {0}", cs);
if (File.Exists (dbFilename))
File.Delete (dbFilename);
con.ConnectionString = cs;
con.Open ();
cmd = con.CreateCommand ();
cmd.CommandText = "CREATE TABLE TEST_TABLE ( COLA INTEGER, COLB TEXT )";
cmd.ExecuteNonQuery ();
con.Close ();
cs = string.Format ("Version=3;Read Only=True;uri=file:{0}", dbFilename);
Console.WriteLine ("Set connection string: {0}", cs);
con.ConnectionString = cs;
Console.WriteLine ("Open database...");
con.Open ();
Console.WriteLine ("create command...");
cmd = con.CreateCommand ();
Console.WriteLine ("create table TEST_TABLE2...");
cmd.CommandText = "CREATE TABLE TEST_TABLE2 ( COLA INTEGER, COLB TEXT )";
didFail = false;
try {
cmd.ExecuteNonQuery ();
} catch (Exception ex) {
didFail = true;
}
if (didFail) {
Console.WriteLine ("Test failed!");
throw new ApplicationException("Test failed!");
}
// Test FailIfMissing = True, existng db file
cs = string.Format ("Version=3;FailIfMissing=True;uri=file:{0}", dbFilename);
Console.WriteLine ("Set connection string: {0}", cs);
if (File.Exists (dbFilename))
File.Delete (dbFilename);
con.ConnectionString = cs;
Console.WriteLine ("Open database...");
con.Open ();
Console.WriteLine ("create command...");
cmd = con.CreateCommand ();
Console.WriteLine ("create table TEST_TABLE2...");
cmd.CommandText = "CREATE TABLE TEST_TABLE2 ( COLA INTEGER, COLB TEXT )";
didFail = false;
try {
cmd.ExecuteNonQuery ();
} catch (Exception ex) {
didFail = true;
}
if (!didFail) {
Console.WriteLine ("Test failed!");
throw new ApplicationException("Test failed!");
}
Console.WriteLine( "Test8 Done." );
}
public void Test2()
{
try
{
ConsoleWriteLine("Test2 Start.");
ConsoleWriteLine("Create connection...");
SqliteConnection con = new SqliteConnection();
string dbFilename = Path.Combine(ApplicationData.Current.LocalFolder.Path, @"SqliteTest3.db");
string cs = string.Format("Version=3,uri=file:{0}", dbFilename);
ConsoleWriteLine(String.Format("Set connection String: {0}", cs));
if (FileExists(dbFilename))
FileDelete(dbFilename);
con.ConnectionString = cs;
ConsoleWriteLine("Open database...");
con.Open();
ConsoleWriteLine("create command...");
IDbCommand cmd = con.CreateCommand();
ConsoleWriteLine("create table TEST_TABLE...");
cmd.CommandText = "CREATE TABLE TBL ( ID NUMBER, NAME TEXT)";
cmd.ExecuteNonQuery();
ConsoleWriteLine("insert row 1...");
cmd.CommandText = "INSERT INTO TBL ( ID, NAME ) VALUES (1, 'ONE' )";
cmd.ExecuteNonQuery();
ConsoleWriteLine("insert row 2...");
cmd.CommandText = "INSERT INTO TBL ( ID, NAME ) VALUES (2, 'ä¸æ–‡' )";
cmd.ExecuteNonQuery();
//Console.WriteLine("commit...");
//cmd.CommandText = "COMMIT";
//cmd.ExecuteNonQuery();
ConsoleWriteLine("SELECT data from TBL...");
cmd.CommandText = "SELECT id,NAME FROM tbl WHERE name = 'ä¸æ–‡'";
IDataReader reader = cmd.ExecuteReader();
int r = 0;
ConsoleWriteLine("Read the data...");
while (reader.Read())
{
ConsoleWriteLine(String.Format(" Row: {0}", r));
int i = reader.GetInt32(reader.GetOrdinal("ID"));
ConsoleWriteLine(String.Format(" ID: {0}", i));
string s = reader.GetString(reader.GetOrdinal("NAME"));
ConsoleWriteLine(String.Format(" NAME: {0} = {1}", s, s == "ä¸æ–‡"));
r++;
}
ConsoleWriteLine(String.Format("Rows retrieved: {0}", r));
ConsoleWriteLine("Close and cleanup...");
con.Close();
con = null;
ConsoleWriteLine("Test2 Done.");
}
catch (Exception e)
{
ConsoleWriteError("ERROR: " + e.Message);
ConsoleWriteError(e.StackTrace);
}
}
public void Test1()
{
try
{
ConsoleWriteLine("Test1 Start.");
ConsoleWriteLine("Create connection...");
SqliteConnection con = new SqliteConnection();
string dbFilename = Path.Combine(ApplicationData.Current.LocalFolder.Path, @"SqliteTest3.db");
string cs = string.Format("Version=3,uri=file:{0}", dbFilename);
ConsoleWriteLine(String.Format("Set connection String: {0}", cs));
if (FileExists(dbFilename))
FileDelete(dbFilename);
con.ConnectionString = cs;
ConsoleWriteLine("Open database...");
con.Open();
ConsoleWriteLine("create command...");
IDbCommand cmd = con.CreateCommand();
ConsoleWriteLine("create table TEST_TABLE...");
cmd.CommandText = "CREATE TABLE TEST_TABLE ( COLA INTEGER, COLB TEXT, COLC DATETIME )";
cmd.ExecuteNonQuery();
ConsoleWriteLine("insert row 1...");
cmd.CommandText = "INSERT INTO TEST_TABLE ( COLA, COLB, COLC ) VALUES (123,'ABC','2008-12-31 18:19:20' )";
cmd.ExecuteNonQuery();
ConsoleWriteLine("insert row 2...");
cmd.CommandText = "INSERT INTO TEST_TABLE ( COLA, COLB, COLC ) VALUES (124,'DEF', '2009-11-16 13:35:36' )";
cmd.ExecuteNonQuery();
//Console.WriteLine("commit...");
//cmd.CommandText = "COMMIT";
//cmd.ExecuteNonQuery();
ConsoleWriteLine("SELECT data from TEST_TABLE...");
cmd.CommandText = "SELECT COLA, COLB, COLC FROM TEST_TABLE";
IDataReader reader = cmd.ExecuteReader();
int r = 0;
ConsoleWriteLine("Read the data...");
while (reader.Read())
{
ConsoleWriteLine(String.Format(" Row: {0}", r));
int i = reader.GetInt32(reader.GetOrdinal("COLA"));
ConsoleWriteLine(String.Format(" COLA: {0}", i));
string s = reader.GetString(reader.GetOrdinal("COLB"));
ConsoleWriteLine(String.Format(" COLB: {0}", s));
DateTime dt = reader.GetDateTime(reader.GetOrdinal("COLC"));
ConsoleWriteLine(String.Format(" COLB: {0}", dt.ToString("MM/dd/yyyy HH:mm:ss")));
r++;
}
ConsoleWriteLine(String.Format("Rows retrieved: {0}", r));
ConsoleWriteLine("Close and cleanup...");
con.Close();
con = null;
ConsoleWriteLine("Test1 Done.");
}
catch (Exception e)
{
ConsoleWriteError("ERROR: " + e.Message);
ConsoleWriteError(e.StackTrace);
}
}
public override void ConnectToDatabase(string connectionString, string migratorName, bool validateTables)
{
// connection string in the format...
// Data Source=File:<db_filename>
_connectionString = connectionString;
string[] s1 = _connectionString.Split(new[] {"Data Source=", ";", ","}, StringSplitOptions.RemoveEmptyEntries);
// first element should be file:<db_filename>
s1[0] = s1[0].Remove(0, 5);
_fileName = s1 [0];
// some sanity checks
string filePath = Path.GetDirectoryName (s1[0]);
string fileName = Path.GetFileName (s1[0]);
if (filePath == "") //Only add this if we arn't an absolute path already
{
filePath = Util.BasePathCombine ("");
_connectionString = string.Format ("Data Source=file://{0}", Path.Combine (Util.BasePathCombine (""), fileName));
}
if (!Directory.Exists (filePath))
Directory.CreateDirectory (filePath); // directory does not exist!
if (!File.Exists(_fileName))
File.Create(_fileName).Dispose(); // database file does not exist, create an empty one to use
SqliteConnection connection = new SqliteConnection(_connectionString);
connection.Open ();
var migrationManager = new MigrationManager(this, migratorName, validateTables);
migrationManager.DetermineOperation();
migrationManager.ExecuteOperation();
connection.Close();
}
private int QueryVersion(SqliteConnection connection)
{
connection.Open();
try
{
var tableExists = connection.CreateCommand();
tableExists.CommandText = "SELECT name FROM sqlite_master WHERE type='table' AND name='version';";
var versionNumber = connection.CreateCommand();
versionNumber.CommandText = "SELECT num FROM version;";
_log.Info("Query version table");
using (var reader = tableExists.ExecuteReader())
{
if (!reader.HasRows)
{
_log.Info("Database is empty");
return 0;
}
}
_log.Info("Query version number");
using (var reader = versionNumber.ExecuteReader())
{
reader.Read();
var numb = (int) reader.GetValue(0);
_log.Info(string.Format("Database version is {0}", numb));
return numb;
}
}
catch (Exception e)
{
_log.Fatal("Unexpected exception", e);
throw;
}
finally
{
connection.Close();
}
}
public CodeProjectMember GetMember(int memberId)
{
var connection = new SqliteConnection("Data Source=" + dbPath);
connection.Open();
CodeProjectMember member = new CodeProjectMember();
member.Id = memberId;
using (var command = connection.CreateCommand())
{
command.CommandText = "SELECT [Key], [Name], [ArticleCnt], [BlogCnt], [Reputation], [IsMe] FROM [Member] WHERE [Key]=" + memberId;
var r = command.ExecuteReader();
while (r.Read())
{
FillMemberFromDataReader(member, r);
}
}
connection.Close();
return member;
}
public void Test1()
{
Console.WriteLine("Test1 Start.");
Console.WriteLine("Create connection...");
SqliteConnection con = new SqliteConnection();
string dbFilename = @"SqliteTest3.db";
string cs = string.Format("Version=3,uri=file:{0}", dbFilename);
Console.WriteLine("Set connection String: {0}", cs);
if (File.Exists(dbFilename))
File.Delete(dbFilename);
con.ConnectionString = cs;
Console.WriteLine("Open database...");
con.Open();
Console.WriteLine("create command...");
IDbCommand cmd = con.CreateCommand();
Console.WriteLine("create table TEST_TABLE...");
cmd.CommandText = "CREATE TABLE TEST_TABLE ( COLA INTEGER, COLB TEXT, COLC DATETIME )";
cmd.ExecuteNonQuery();
Console.WriteLine("insert row 1...");
cmd.CommandText = "INSERT INTO TEST_TABLE ( COLA, COLB, COLC ) VALUES (123,'ABC','2008-12-31 18:19:20' )";
cmd.ExecuteNonQuery();
Console.WriteLine("insert row 2...");
cmd.CommandText = "INSERT INTO TEST_TABLE ( COLA, COLB, COLC ) VALUES (124,'DEF', '2009-11-16 13:35:36' )";
cmd.ExecuteNonQuery();
//Console.WriteLine("commit...");
//cmd.CommandText = "COMMIT";
//cmd.ExecuteNonQuery();
Console.WriteLine("SELECT data from TEST_TABLE...");
cmd.CommandText = "SELECT COLA, COLB, COLC FROM TEST_TABLE";
IDataReader reader = cmd.ExecuteReader();
int r = 0;
Console.WriteLine("Read the data...");
while (reader.Read())
{
Console.WriteLine(" Row: {0}", r);
int i = reader.GetInt32(reader.GetOrdinal("COLA"));
Console.WriteLine(" COLA: {0}", i);
string s = reader.GetString(reader.GetOrdinal("COLB"));
Console.WriteLine(" COLB: {0}", s);
DateTime dt = reader.GetDateTime(reader.GetOrdinal("COLC"));
Console.WriteLine(" COLB: {0}", dt.ToString("MM/dd/yyyy HH:mm:ss"));
r++;
}
Console.WriteLine("Rows retrieved: {0}", r);
//alxwest: DataTable & SqliteDataAdapter currently unavailable for Silverlight
#if !SQLITE_SILVERLIGHT
SqliteCommand command = new SqliteCommand("PRAGMA table_info('TEST_TABLE')", con);
DataTable dataTable = new DataTable();
SqliteDataAdapter dataAdapter = new SqliteDataAdapter();
dataAdapter.SelectCommand = command;
dataAdapter.Fill(dataTable);
DisplayDataTable(dataTable, "Columns");
#endif
Console.WriteLine("Close and cleanup...");
con.Close();
con = null;
Console.WriteLine("Test1 Done.");
}
public List<CodeProjectMember> GetMembers()
{
List<CodeProjectMember> memberList = new List<CodeProjectMember>();
var connection = new SqliteConnection("Data Source=" + dbPath);
connection.Open();
using (var command = connection.CreateCommand())
{
command.CommandText = "SELECT [Key], [Name], [ArticleCnt], [BlogCnt], [Reputation], [IsMe] FROM [Member]";
var r = command.ExecuteReader();
while (r.Read())
{
CodeProjectMember member = new CodeProjectMember();
FillMemberFromDataReader(member, r);
memberList.Add(member);
}
}
connection.Close();
return memberList;
}
public void Test3()
{
Console.WriteLine("Test3 (Date Parameters) Start.");
Console.WriteLine("Create connection...");
SqliteConnection con = new SqliteConnection();
string dbFilename = @"SqliteTest3.db";
string cs = string.Format("Version=3,uri=file:{0}", dbFilename);
Console.WriteLine("Set connection String: {0}", cs);
if (File.Exists(dbFilename))
File.Delete(dbFilename);
con.ConnectionString = cs;
Console.WriteLine("Open database...");
con.Open();
Console.WriteLine("create command...");
IDbCommand cmd = con.CreateCommand();
Console.WriteLine("create table TEST_TABLE...");
cmd.CommandText = "CREATE TABLE TBL ( ID NUMBER, DATE_TEXT REAL)";
cmd.ExecuteNonQuery();
Console.WriteLine("insert ...");
cmd.CommandText = "INSERT INTO TBL ( ID, DATE_TEXT) VALUES ( 1, @DATETEXT)";
cmd.Parameters.Add(
new SqliteParameter
{
ParameterName = "@DATETEXT",
Value = DateTime.Now
}
);
cmd.ExecuteNonQuery();
Console.WriteLine("SELECT data from TBL...");
cmd.CommandText = "SELECT * FROM tbl";
IDataReader reader = cmd.ExecuteReader();
int r = 0;
Console.WriteLine("Read the data...");
while (reader.Read())
{
Console.WriteLine(" Row: {0}", r);
int i = reader.GetInt32(reader.GetOrdinal("ID"));
Console.WriteLine(" ID: {0}", i);
string s = reader.GetString(reader.GetOrdinal("DATE_TEXT"));
Console.WriteLine(" DATE_TEXT: {0}", s);
r++;
}
Console.WriteLine("Rows retrieved: {0}", r);
Console.WriteLine("Close and cleanup...");
con.Close();
con = null;
Console.WriteLine("Test3 Done.");
}
public void DeleteMember(int memberId)
{
var connection = new SqliteConnection("Data Source=" + dbPath);
connection.Open();
using (var ic = connection.CreateCommand())
{
ic.CommandText = "DELETE FROM [Member] " +
" WHERE [Key]=" + memberId;
ic.ExecuteNonQuery();
}
connection.Close();
FileStorageService storage = new FileStorageService();
storage.DeleteFile(memberId.ToString());
}
public void Issue_65()
{
//alxwest: causes error "Unable to open database" as TempDirectory.ToString() is set to "B:/TEMP/"
//string datasource = "file://" + TempDirectory.ToString() + "myBigDb.s3db";
string datasource = "file://" + "myBigDb.s3db";
using (IDbConnection conn = new SqliteConnection("uri=" + datasource))
{
long targetFileSize = (long)Math.Pow(2, 32) - 1;
int rowLength = 1024; // 2^10
long loopCount = (int)(targetFileSize / rowLength) + 10000;
char[] chars = new char[rowLength];
for (int i = 0; i < rowLength; i++)
{
chars[i] = 'A';
}
string row = new string(chars);
conn.Open();
IDbCommand cmd = conn.CreateCommand();
try
{
cmd.CommandText = "PRAGMA cache_size = 16000; PRAGMA synchronous = OFF; PRAGMA journal_mode = MEMORY;";
cmd.ExecuteNonQuery();
cmd.CommandText = "drop table if exists [MyTable]";
cmd.ExecuteNonQuery();
cmd.CommandText = "create table [MyTable] ([MyField] varchar(" + rowLength + ") null)";
cmd.ExecuteNonQuery();
cmd.CommandText = "insert into [MyTable] ([MyField]) VALUES ('" + row + "')";
for (int i = 0; i < loopCount; i++)
{
cmd.ExecuteNonQuery();
}
}
catch
{
Console.WriteLine(((SqliteCommand)cmd).GetLastError());
}
finally
{
cmd.Cancel();
conn.Close();
conn.Dispose();
}
}
}
public override void ConnectToDatabase(string connectionString, string migratorName, bool validateTables)
{
// connection string in the format...
// Data Source=File:<db_filename>
m_connectionString = connectionString;
string[] s1 = m_connectionString.Split(new[] {"Data Source=", ";", ","}, StringSplitOptions.RemoveEmptyEntries);
// first element should be file:<db_filename>
s1[0] = s1[0].Remove(0, 5);
m_fileName = s1 [0];
// some sanity checks
string filePath = Path.GetDirectoryName (s1[0]);
string fileName = Path.GetFileName (s1[0]);
if (filePath == "") //Only add this if we aren't an absolute path already eg file:data.db
{
m_connectionString = string.Format ("Data Source=file://{0}", Path.Combine (m_defaultDataPath, fileName));
filePath = m_defaultDataPath;
m_fileName = Path.Combine (m_defaultDataPath, fileName);
}
if (!Directory.Exists (filePath))
Directory.CreateDirectory (filePath); // directory does not exist!
if (!File.Exists(m_fileName))
File.Create(m_fileName).Dispose(); // database file does not exist, create an empty one to use
SqliteConnection connection = new SqliteConnection(m_connectionString);
try {
connection.Open ();
var migrationManager = new MigrationManager (this, migratorName, validateTables);
migrationManager.DetermineOperation ();
migrationManager.ExecuteOperation ();
} catch {
MainConsole.Instance.Warn ("[Sqlite]: Unable to connect to database ("+m_connectionString+")");
}
connection.Close();
}
// Constructor
public MainPage()
{
InitializeComponent();
IsolatedStorageFile isf = IsolatedStorageFile.GetUserStoreForApplication();
isf.DeleteFile("test.db");
using (SqliteConnection conn = new SqliteConnection("Version=3,uri=file:test.db"))
{
conn.Open();
using (SqliteCommand cmd = conn.CreateCommand())
{
cmd.CommandText = "CREATE TABLE test ( [id] INTEGER PRIMARY KEY, [col] INTEGER UNIQUE, [col2] INTEGER, [col3] REAL, [col4] TEXT, [col5] BLOB)";
cmd.ExecuteNonQuery();
cmd.Transaction = conn.BeginTransaction();
cmd.CommandText = "INSERT INTO test(col, col2, col3, col4, col5) VALUES(@col, @col2, @col3, @col4, @col5);SELECT last_insert_rowid();";
cmd.Parameters.Add("@col", null);
cmd.Parameters.Add("@col2", null);
cmd.Parameters.Add("@col3", null);
cmd.Parameters.Add("@col4", null);
cmd.Parameters.Add("@col5", null);
DateTime start = DateTime.Now;
this.lstResult.Items.Add("Inserting 100 Rows with transaction");
for (int i = 0; i < 100; i++)
{
cmd.Parameters["@col"].Value = i;
cmd.Parameters["@col2"].Value = i;
cmd.Parameters["@col3"].Value = i * 0.515;
cmd.Parameters["@col4"].Value = "สวัสดี な. あ · か · さ · た · な · は · ま · や · ら · わ. 形容詞 hello " + i;
cmd.Parameters["@col5"].Value = Encoding.UTF8.GetBytes("สวัสดี");
object s = cmd.ExecuteScalar();
}
cmd.Transaction.Commit();
cmd.Transaction = null;
this.lstResult.Items.Add("Time taken :" + DateTime.Now.Subtract( start ).TotalMilliseconds + " ms.");
cmd.CommandText = "SELECT * FROM test";
using (SqliteDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
var bytes = (byte[])reader.GetValue(5);
this.lstResult.Items.Add(string.Format("{0},{1},{2},{3},{4}, {5}",
reader.GetInt32(0),
reader.GetInt32(1),
reader.GetInt32(2),
reader.GetDouble(3),
reader.GetString(4),
Encoding.UTF8.GetString(bytes, 0, bytes.Length)));
}
}
conn.Close();
}
}
}
