public CodeProjectDatabase()
{
dbPath = "items.db3";
IsolatedStorageFile isf = IsolatedStorageFile.GetUserStoreForApplication();
bool exists = isf.FileExists(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 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;
}
/// <summary>
/// Create schema of sync source table
/// supports transaction
/// </summary>
/// <param name="con"></param>
public static void CreateSchema(SqliteConnection con)
{
using (SqliteCommand cmd = con.CreateCommand())
{
cmd.CommandText = "CREATE TABLE IF NOT EXISTS " + Configuration.TBL_DATASOURCE_INFO +
" ( " + Configuration.COL_SOURCE_ABSOLUTE_PATH + " TEXT, " +
Configuration.COL_SOURCE_ID + " TEXT PRIMARY KEY)";
cmd.ExecuteNonQuery();
}
}
public static int ExecuteNonQuery(string sql)
{
using (SqliteConnection connection = new SqliteConnection())
{
connection.ConnectionString = ConnectionString;
System.Data.IDbCommand cmd = connection.CreateCommand();
cmd.CommandText = sql;
connection.Open();
return cmd.ExecuteNonQuery();
}
}
/// <summary>
/// Update details of sync source
/// Pass SQLiteConnection object to make atomic action
/// </summary>
/// <param name="source"></param>
/// <param name="con"></param>
/// <returns></returns>
public static bool Update(SyncSource source, SqliteConnection con )
{
using (SqliteCommand cmd = con.CreateCommand())
{
cmd.CommandText = "UPDATE " + Configuration.TBL_DATASOURCE_INFO +
" SET " + Configuration.COL_SOURCE_ABSOLUTE_PATH + " = @path WHERE "
+ Configuration.COL_SOURCE_ID + " = @id";
cmd.Parameters.Add(new SqliteParameter("@id", DbType.String) { Value = source.ID });
cmd.Parameters.Add(new SqliteParameter("@path", DbType.String) { Value = source.Path });
cmd.ExecuteNonQuery();
return true;
}
}
/// <summary>
/// This method takes in SQLiteConnection object as a parameter
/// </summary>
/// <param name="s"></param>
/// <param name="con"></param>
/// <returns></returns>
public static bool Add(SyncSource s, SqliteConnection con)
{
using (SqliteCommand cmd = con.CreateCommand())
{
cmd.CommandText = "INSERT INTO " + Configuration.TBL_DATASOURCE_INFO +
"(" + Configuration.COL_SOURCE_ID + "," + Configuration.COL_SOURCE_ABSOLUTE_PATH +
") VALUES (@id, @path)";
cmd.Parameters.Add(new SqliteParameter("@id", DbType.String) { Value = s.ID });
cmd.Parameters.Add(new SqliteParameter("@path", DbType.String) { Value = s.Path });
cmd.ExecuteNonQuery();
}
return true;
}
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();
}
}
/// <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;
}
/// <summary>
/// Initializes a new instance of the <see cref="SqliteDataAdapter">SqliteDataAdapter</see> class
/// with a SelectCommand and a SqliteConnection object.
/// </summary>
/// <param name="selectCommandText"></param>
/// <param name="connection"></param>
public SqliteDataAdapter(string selectCommandText, SqliteConnection connection)
{
DbCommand cmd = connection.CreateCommand();
cmd.CommandText = selectCommandText;
SelectCommand = cmd;
}
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 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 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.");
}
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.");
}
/// <summary>
/// Initializes a new instance of the <see cref="SqliteDataAdapter">SqliteDataAdapter</see> class
/// with a SelectCommand and a SqliteConnection object.
/// </summary>
/// <param name="selectCommandText"></param>
/// <param name="connection"></param>
public SqliteDataAdapter(string selectCommandText, SqliteConnection connection)
{
#if NET_2_0
DbCommand cmd;
#else
IDbCommand cmd;
#endif
cmd = connection.CreateCommand();
cmd.CommandText = selectCommandText;
SelectCommand = cmd;
}
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 static void CreateSchema(SqliteConnection con)
{
using (SqliteCommand cmd = con.CreateCommand())
{
cmd.CommandText = "CREATE TABLE IF NOT EXISTS " + SYNCJOB_TABLE +
"(" + COL_SYNCJOB_ID + " VARCHAR(50) PRIMARY KEY, "
+ COL_SYNCJOB_NAME + " VARCHAR(50) UNIQUE NOT NULL, "
+ COL_METADATA_SOURCE_LOCATION + " TEXT, "
+ COL_SYNC_SOURCE_ID + " VARCHAR (50), "
+ "FOREIGN KEY (" + COL_SYNC_SOURCE_ID + ") REFERENCES "
+ DATASOURCE_INFO_TABLE + "(" + COL_SOURCE_ID + "))";
cmd.ExecuteNonQuery();
}
}
private void Setup_T7()
{
SqliteConnection con = new SqliteConnection();
con.ConnectionString = connstring_T7;
con.Open();
IDbCommand cmd = con.CreateCommand();
cmd = con.CreateCommand();
cmd.CommandText = "CREATE TABLE IF NOT EXISTS ATABLE(A integer primary key , B varchar (50), C integer)";
cmd.ExecuteNonQuery();
cmd.CommandText = "CREATE TABLE IF NOT EXISTS BTABLE(A integer primary key , B varchar (50), C integer)";
cmd.ExecuteNonQuery();
cmd.CommandText = String.Format("INSERT INTO BTABLE ( A, B, C ) VALUES (6,'threader', '1' )");
cmd.ExecuteNonQuery();
}
private void MultiInsertsSameThread_T5()
{
for (int i = 0; i < 10; i++)
{
//Console.WriteLine( "SELECT/INSERT ON Thread {0}", i );
Thread worker = new Thread(() =>
{
string commandt = String.Empty;
try
{
// Cannot use value of i, since it exceeds the scope of this thread and will be
// reused by multiple threads
int aValue = 100 + Thread.CurrentThread.ManagedThreadId;
int op = aValue % 2;
SqliteConnection con = new SqliteConnection();
con.ConnectionString = connstring_T5;
con.Open();
IDbCommand cmd = con.CreateCommand();
cmd = con.CreateCommand();
if (op == 0)
{
for (int j = 0; j < 1000; j++)
{
int rows;
int retry = 0;
cmd.CommandText = String.Format("INSERT INTO BTABLE ( A, B, C ) VALUES ({0},'threader', '1' )", (aValue * 10000) + j);
commandt = cmd.CommandText;
do
{
rows = cmd.ExecuteNonQuery();
if (rows == 0)
{
retry += 1; // Insert Failed
Console.WriteLine(cmd.CommandText);
Console.WriteLine("retry {0}", retry);
Console.WriteLine(((SqliteCommand)cmd).GetLastError());
}
} while (rows == 0 && retry < 5);
}
}
else
{
cmd.CommandText = String.Format("Select * FROM ATABLE");
commandt = cmd.CommandText;
cmd.ExecuteReader();
}
}
catch (Exception ex)
{
Console.WriteLine(String.Format("Command {0} threw exception {1}", commandt, ex.Message));
}
});
worker.Start();
}
}
protected SqliteCommand PrepReader(string query)
{
try
{
SqliteConnection connection = new SqliteConnection(_connectionString);
connection.Open();
var cmd = connection.CreateCommand();
cmd.CommandText = query;
return cmd as SqliteCommand;
}
catch (SqliteException)
{
//throw ex;
}
catch (Exception ex)
{
throw ex;
}
return null;
}
//we need concurrency support on a table level inside of the database file.
private void InsertSameTable_T4()
{
for (int i = 0; i < 10; i++)
{
Console.WriteLine("INSERTING ON Thread {0}", i);
Thread worker = new Thread(() =>
{
// Cannot use value of i, since it exceeds the scope of this thread and will be
// reused by multiple threads
int aValue = Thread.CurrentThread.ManagedThreadId;
SqliteConnection con = new SqliteConnection();
con.ConnectionString = connstring_T4;
con.Open();
IDbCommand cmd = con.CreateCommand();
cmd = con.CreateCommand();
cmd.CommandText = String.Format("INSERT INTO ATABLE ( A, B, C ) VALUES ({0},'threader', '1' )", aValue);
Console.WriteLine(cmd.CommandText);
cmd.ExecuteNonQuery();
}
);
worker.Start();
}
}
private void SelectorWrite_T4()
{
//concurrent reads/writes in the same table, if there were only Selects it would be preferable for the sqlite engine not to lock internally.
for (int i = 0; i < 10; i++)
{
Console.WriteLine("SELECT/INSERT ON Thread {0}", i);
Thread worker = new Thread(() =>
{
// Cannot use value of i, since it exceeds the scope of this thread and will be
// reused by multiple threads
int aValue = 100 + Thread.CurrentThread.ManagedThreadId;
int op = aValue % 2;
SqliteConnection con = new SqliteConnection();
con.ConnectionString = connstring_T4;
con.Open();
IDbCommand cmd = con.CreateCommand();
cmd = con.CreateCommand();
if (op == 0)
{
cmd.CommandText = String.Format("Select * FROM ATABLE");
cmd.ExecuteReader();
}
else
{
cmd.CommandText = String.Format("INSERT INTO ATABLE ( A, B, C ) VALUES ({0},'threader', '1' )", aValue);
Console.WriteLine(cmd.CommandText);
cmd.ExecuteNonQuery();
}
});
worker.Start();
}
}
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.");
}
// 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 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 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());
}
//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.");
}
private static void T6_ThreadStart(object data)
{
string commandt = String.Empty;
int i = (int)data;
try
{
int aValue = 100 + i;
int op = aValue % 2;
SqliteConnection con = new SqliteConnection();
con.ConnectionString = connstring_T6;
con.Open();
IDbCommand cmd = con.CreateCommand();
cmd = con.CreateCommand();
if (op == 0)
{
SqliteTransaction trans = (SqliteTransaction)con.BeginTransaction();
for (int j = 0; j < 5000; j++)
{
int rows;
int retry = 0;
cmd.CommandText = String.Format("INSERT INTO BTABLE ( A, B, C ) VALUES ({0},'threader', '1' )", (aValue * 10000) + j);
commandt = cmd.CommandText;
do
{
rows = cmd.ExecuteNonQuery();
if (rows == 0)
{
retry += 1; // Insert Failed
Console.WriteLine("retry {0}:{1}:{2}", retry, ((SqliteCommand)cmd).GetLastError(), cmd.CommandText);
Thread.Sleep(rnd.Next(50, 1000));
}
} while (rows == 0 && retry < 10);
}
trans.Commit();
}
else
{
cmd.CommandText = String.Format("Select * FROM ATABLE");
commandt = cmd.CommandText;
cmd.ExecuteReader();
}
}
catch (Exception ex)
{
Console.WriteLine(String.Format("Command {0} threw exception {1}", commandt, ex.Message));
}
}
public bool Add(SyncJob job, SqliteConnection con)
{
if (this.SyncJobExists(job.Name, job.ID))
throw new SyncJobNameExistException(String.Format(m_ResourceManager.GetString("err_syncjobCreated"), job.Name));
using (SqliteCommand cmd = con.CreateCommand ())
{
cmd.CommandText = "INSERT INTO " + SYNCJOB_TABLE +
" (" + COL_SYNCJOB_ID + ", " + COL_SYNCJOB_NAME +
" ," + COL_METADATA_SOURCE_LOCATION + ", " + COL_SYNC_SOURCE_ID +
") VALUES (@id, @name, @meta, @source)";
cmd.Parameters.Add ( new SqliteParameter("@id", System.Data.DbType.String) { Value = job.ID });
cmd.Parameters.Add(new SqliteParameter("@name", System.Data.DbType.String) { Value = job.Name });
cmd.Parameters.Add(new SqliteParameter("@meta", System.Data.DbType.String) { Value = job.IntermediaryStorage.Path });
cmd.Parameters.Add(new SqliteParameter("@source", System.Data.DbType.String) { Value = job.SyncSource.ID });
cmd.ExecuteNonQuery ();
}
SQLiteSyncSourceProvider.Add(job.SyncSource, con);
return true;
}
protected SqliteCommand PrepReader(string query)
{
try
{
SqliteConnection connection = new SqliteConnection(m_connectionString);
connection.Open();
var cmd = connection.CreateCommand();
cmd.CommandText = query;
return cmd as SqliteCommand;
}
catch (SqliteException ex)
{
MainConsole.Instance.WarnFormat ("[Sqlite]: Exception prepping reader command: {0}, Exception: {1}",
query, ex);
}
catch (Exception ex)
{
MainConsole.Instance.WarnFormat ("[Sqlite]: Exception prepping reader command: {0}, Exception: {1}",
query, ex);
}
return null;
}
private void T7_ThreadStart(object iSequence)
{
int aValue = (int)iSequence * 1000;
SqliteConnection con = new SqliteConnection();
con.ConnectionString = connstring_T7;
con.Open();
IDbCommand cmd = con.CreateCommand();
cmd = con.CreateCommand();
string commandt = String.Format("CREATE TABLE IF NOT EXISTS ATABLE{0}(A integer primary key , B varchar (50), C integer, D varchar (500))", aValue);
cmd.CommandText = commandt;
try
{
cmd.ExecuteNonQuery();
Console.WriteLine("Created table: ATABLE" + aValue);
}
catch (Exception ex)
{
Console.WriteLine(String.Format("Command {0} threw exception {1}", commandt, ex.Message));
}
}
