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