private SqliteTestStore CreateShared(Action initializeDatabase) { CreateShared(typeof(SqliteTestStore).Name + _name, initializeDatabase); _connection = new SqliteConnection(CreateConnectionString(_name)); _connection.Open(); _transaction = _connection.BeginTransaction(); return this; }
public void Ctor_unsets_read_uncommitted_when_serializable() { using (var connection = new SqliteConnection("Data Source=:memory:")) { connection.Open(); using (connection.BeginTransaction(IsolationLevel.Serializable)) { Assert.Equal(0L, connection.ExecuteScalar<long>("PRAGMA read_uncommitted;")); } } }
public void Ctor_sets_read_uncommitted() { using (var connection = new SqliteConnection("Data Source=:memory:;Cache=Shared")) { connection.Open(); using (connection.BeginTransaction(IsolationLevel.ReadUncommitted)) { Assert.Equal(1L, connection.ExecuteScalar<long>("PRAGMA read_uncommitted;")); } } }
public void IsolationLevel_throws_when_completed() { using (var connection = new SqliteConnection("Data Source=:memory:")) { connection.Open(); var transaction = connection.BeginTransaction(); transaction.Dispose(); var ex = Assert.Throws<InvalidOperationException>(() => transaction.IsolationLevel); Assert.Equal(Strings.TransactionCompleted, ex.Message); } }
void CreateTable(Type keytype) { string type = "INTEGER"; if (keytype == typeof(long)) { type = "BIGINT"; } else if (keytype == typeof(string)) { type = "TEXT"; } string sql = $@" CREATE TABLE [main]( [key] " + type + @", [CreateTime] DATETIME, [Content] TEXT); "; string sql2 = @" CREATE INDEX primary_index ON [main] ( key ASC ); "; var tran = _sqlCon.BeginTransaction(); using (var cmd = _sqlCon.CreateCommand()) { cmd.Transaction = tran; cmd.CommandText = sql; cmd.ExecuteNonQuery(); cmd.CommandText = sql2; cmd.ExecuteNonQuery(); } tran.Commit(); }
public void Ctor_throws_when_invalid_isolation_level_without_cache() { using (var connection = new SqliteConnection("Data Source=:memory:")) { connection.Open(); var ex = Assert.Throws<ArgumentException>(() => connection.BeginTransaction(IsolationLevel.ReadUncommitted)); Assert.Equal(Strings.FormatInvalidIsolationLevelForUnsharedCache(IsolationLevel.ReadUncommitted), ex.Message); } using (var connection = new SqliteConnection("Data Source=:memory:;Cache=Shared")) { connection.Open(); connection.BeginTransaction(IsolationLevel.ReadUncommitted); } }
public void Ctor_sets_values() { using (var connection = new SqliteConnection("Data Source=:memory:")) { connection.Open(); using (var transaction = connection.BeginTransaction()) { var command = new SqliteCommand("SELECT 1;", connection, transaction); Assert.Equal("SELECT 1;", command.CommandText); Assert.Same(connection, command.Connection); Assert.Same(transaction, command.Transaction); } } }
public void BeginTransaction_works() { using (var connection = new SqliteConnection("Data Source=:memory:")) { connection.Open(); using (var transaction = connection.BeginTransaction(IsolationLevel.Serializable)) { Assert.NotNull(transaction); Assert.Equal(connection, transaction.Connection); Assert.Equal(IsolationLevel.Serializable, transaction.IsolationLevel); } } }
public void BeginTransaction_throws_when_parallel_transaction() { using (var connection = new SqliteConnection("Data Source=:memory:")) { connection.Open(); using (connection.BeginTransaction()) { var ex = Assert.Throws<InvalidOperationException>(() => connection.BeginTransaction()); Assert.Equal(Strings.ParallelTransactionsNotSupported, ex.Message); } } }
public void BeginTransaction_throws_when_closed() { var connection = new SqliteConnection(); var ex = Assert.Throws<InvalidOperationException>(() => connection.BeginTransaction()); Assert.Equal(Strings.FormatCallRequiresOpenConnection("BeginTransaction"), ex.Message); }
public void CreateCommand_returns_command() { using (var connection = new SqliteConnection("Data Source=:memory:")) { connection.Open(); using (var transaction = connection.BeginTransaction()) { var command = connection.CreateCommand(); Assert.NotNull(command); Assert.Same(connection, command.Connection); Assert.Same(transaction, command.Transaction); } } }
public void ExecuteReader_throws_when_transaction_mismatched() { using (var connection = new SqliteConnection("Data Source=:memory:")) { var command = connection.CreateCommand(); command.CommandText = "SELECT 1;"; connection.Open(); using (var otherConnection = new SqliteConnection("Data Source=:memory:")) { otherConnection.Open(); using (var transction = otherConnection.BeginTransaction()) { command.Transaction = transction; var ex = Assert.Throws<InvalidOperationException>(() => command.ExecuteReader()); Assert.Equal(Strings.TransactionConnectionMismatch, ex.Message); } } } }
public void Dispose_works() { using (var connection = new SqliteConnection("Data Source=:memory:")) { connection.Open(); CreateTestTable(connection); using (var transaction = connection.BeginTransaction()) { connection.ExecuteNonQuery("INSERT INTO TestTable VALUES (1);"); transaction.Dispose(); Assert.Null(connection.Transaction); Assert.Null(transaction.Connection); } Assert.Equal(0L, connection.ExecuteScalar<long>("SELECT COUNT(*) FROM TestTable;")); } }
public void IsolationLevel_is_infered_when_unspecified() { using (var connection = new SqliteConnection("Data Source=:memory:")) { connection.Open(); connection.ExecuteNonQuery("PRAGMA read_uncommitted = 1;"); using (var transaction = connection.BeginTransaction()) { Assert.Equal(IsolationLevel.ReadUncommitted, transaction.IsolationLevel); } } }
public static void Main(string[] args) { if (!File.Exists(dbloc)) { FileStream fs = File.Create(dbloc); fs.Dispose(); } SqliteConnection conn = new SqliteConnection("Data Source = " + dbloc); SqliteCommand comm = conn.CreateCommand(); conn.Open(); comm.CommandText = @"CREATE TABLE IF NOT EXISTS [companies] ( [id] integer PRIMARY KEY AUTOINCREMENT NOT NULL, [Title] string NOT NULL, [Country] string NOT NULL, [AddedDate] Date NOT NULL ); "; comm.ExecuteNonQuery(); // SqliteTransaction st2 = conn.BeginTransaction(); try { SqliteCommand comm2 = conn.CreateCommand(); comm2.CommandText = @"INSERT INTO companies(Title,Country,AddedDate) VALUES (@title, @country, @date);"; List<Company> companies = new List<Company>(); companies.Add(new Company("Roshen", "Ukraine", "10.10.2010")); companies.Add(new Company("Sandora", "Ukraine", "11.09.2011")); companies.Add(new Company("Svitoch","Ukraine","12.08.2012")); companies.Add(new Company("Rosinka","Ukraine","13.07.2013")); companies.Add(new Company("Korona","Ukraine","14.06.2014")); companies.Add(new Company("Mircrosoft","USA","10.10.2009")); companies.Add(new Company("Google","USA","10.10.2008")); companies.Add(new Company("Facebook","USA","10.10.2007")); companies.Add(new Company("Air France","France","10.10.2006")); companies.Add(new Company("Koenisegg","Sweden","10.10.2005")); comm2.Parameters.Add(new SqliteParameter("@title", SqliteType.Text)); comm2.Parameters.Add(new SqliteParameter("@country", SqliteType.Text)); comm2.Parameters.Add(new SqliteParameter("@date", SqliteType.Text)); foreach (Company comp in companies) { comm2.Parameters[0].Value = comp.Title; comm2.Parameters[1].Value = comp.Country; comm2.Parameters[2].Value = comp.AddedDate; if (comm2.ExecuteNonQuery() != 1) { throw new InvalidProgramException(); } } st2.Commit(); } catch (Exception ex) { st2.Rollback(); } // SqliteCommand comm3 = conn.CreateCommand(); comm3.CommandText = @"SELECT MAX(id),Title FROM companies"; var reader = comm3.ExecuteReader(); while (reader.Read()) { Console.WriteLine("{0}|{1}", reader.GetInt32(0), reader.GetString(1)); } Console.WriteLine("------------------"); // SqliteCommand comm4 = conn.CreateCommand(); comm4.CommandText = @" UPDATE companies SET Country= @country1 WHERE Country= @country2; "; var country1Param = new SqliteParameter(); country1Param.ParameterName = "@country1"; country1Param.Value = "USA"; var country2Param = new SqliteParameter(); country2Param.ParameterName = "@country2"; country2Param.Value = "Ukraine"; comm4.Parameters.Add(country1Param); comm4.Parameters.Add(country2Param); comm4.ExecuteNonQuery(); // SqliteCommand comm5 = conn.CreateCommand(); comm5.CommandText = @" DELETE FROM companies WHERE Country<>@country1; "; comm5.Parameters.Add(country1Param); comm5.ExecuteNonQuery(); // SqliteCommand comm6 = conn.CreateCommand(); comm6.CommandText = @" SELECT COUNT(*) FROM companies "; Console.WriteLine(comm6.ExecuteScalar()); Console.WriteLine("------------------"); // SqliteCommand comm7 = conn.CreateCommand(); comm7.CommandText = @"SELECT * FROM companies"; reader = comm7.ExecuteReader(); while (reader.Read()) { Console.WriteLine("{0}|{1}|{2}|{3}", reader.GetInt32(0), reader.GetString(1), reader.GetString(2), reader.GetString(3)); } Console.WriteLine("------------------"); // Console.WriteLine(@"input example:{'Title':'title','Country':'country','AddedDate':'20.10.2015'}"); Console.WriteLine("------------------"); List<Company> companiesToAdd = new List<Company>(); string str; while ((str = Console.ReadLine()) != "q") { if (str.StartsWith("{")) { Company obj = Newtonsoft.Json.JsonConvert.DeserializeObject<Company>(str); companiesToAdd.Add(obj); } } if (companiesToAdd.Count > 0) { SqliteTransaction st = conn.BeginTransaction(); try { SqliteCommand comm8 = conn.CreateCommand(); comm8.CommandText = @"INSERT INTO companies(Title,Country,AddedDate) VALUES (@title, @country, @date);"; comm8.Parameters.Add(new SqliteParameter("@title", SqliteType.Text)); comm8.Parameters.Add(new SqliteParameter("@country", SqliteType.Text)); comm8.Parameters.Add(new SqliteParameter("@date", SqliteType.Text)); foreach (Company comp in companiesToAdd) { comm8.Parameters[0].Value = comp.Title; comm8.Parameters[1].Value = comp.Country; comm8.Parameters[2].Value = comp.AddedDate; if (comm8.ExecuteNonQuery() != 1) { throw new InvalidProgramException(); } } st.Commit(); } catch (Exception ex) { st.Rollback(); } } conn.Close(); }
public void Serializable_locks() { using (var connectionA = new SqliteConnection("Data Source=testdb;Mode=Memory;Cache=Shared")) { connectionA.Open(); using (var transactionA = connectionA.BeginTransaction(IsolationLevel.Serializable)) { using (var connectionB = new SqliteConnection("Data Source=testdb;Mode=Memory;Cache=Shared")) { connectionB.Open(); var ex = Assert.Throws<SqliteException>(() => new SqliteTransaction(connectionB, IsolationLevel.Serializable, 1)); Assert.Equal(SQLITE_LOCKED, ex.SqliteErrorCode); } } } }
public void Dispose_can_be_called_more_than_once() { using (var connection = new SqliteConnection("Data Source=:memory:")) { connection.Open(); var transaction = connection.BeginTransaction(); transaction.Dispose(); transaction.Dispose(); } }
public void Ctor_throws_when_invalid_isolation_level() { using (var connection = new SqliteConnection("Data Source=:memory:")) { connection.Open(); var ex = Assert.Throws<ArgumentException>(() => connection.BeginTransaction(IsolationLevel.Snapshot)); Assert.Equal(Strings.FormatInvalidIsolationLevel(IsolationLevel.Snapshot), ex.Message); } }