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