public void OpenConnection_WithOpenCallWhenOpen_CausesException() { //Arrange string dbPath = TempDatabaseFiles.GetNewDatabasePath(this); Exception openException = null; //Act try { using (var db = new SqliteConnection(dbPath, true)) { db.Open(); //Since the database should already be open, this should throw an exception db.Open(); } } catch (Exception e) { openException = e; } //Assert Assert.IsNotNull(openException as InvalidOperationException); }
public void TableSchema_GetTableColumnList_ReturnsCorrectColumnList() { //Arrange IColumnInfo[] columnList = null; string dbPath = TempDatabaseFiles.GetNewDatabasePath(this); //Act using (var db = new SqliteConnection(dbPath, true)) { using (var cmd = new SqliteCommand(String.Format(tableCreateSql1, tableName), db)) { db.Open(); cmd.ExecuteNonQuery(); //With the table created, we should be able to get a list of columns columnList = db.GetTableColumnList(tableName); } } //Assert Assert.IsNotNull(columnList); Assert.AreEqual(3, columnList.Length); //Should be three columns Assert.AreEqual("IdColumn", columnList[0].Name); Assert.IsTrue(columnList[0].IsPrimaryKey); Assert.AreEqual(DbType.Int64, columnList[0].DataType); Assert.AreEqual("DateTimeColumn", columnList[1].Name); Assert.IsFalse(columnList[1].IsPrimaryKey); Assert.IsTrue(columnList[1].IsNotNull); Assert.AreEqual(DbType.DateTime, columnList[1].DataType); Assert.AreEqual("TextColumn", columnList[2].Name); Assert.IsFalse(columnList[2].IsPrimaryKey); Assert.AreEqual(DbType.String, columnList[2].DataType); }
public void OpenConnection_WithOpenCloseSafeOpenCalls_DatabaseIsOpen() { //Arrange string dbPath = TempDatabaseFiles.GetNewDatabasePath(this); Exception openException = null; ConnectionState dbStateAfterOpen = ConnectionState.Broken; ConnectionState dbStateAfterClose = ConnectionState.Broken; ConnectionState dbStateAfterSafeOpen = ConnectionState.Broken; //Act try { using (var db = new SqliteConnection(dbPath, true)) { db.Open(); dbStateAfterOpen = db.State; db.Close(); dbStateAfterClose = db.State; db.SafeOpen(); dbStateAfterSafeOpen = db.State; } } catch (Exception e) { openException = e; } //Assert Assert.IsNull(openException); Assert.AreEqual(ConnectionState.Open, dbStateAfterOpen); Assert.AreEqual(ConnectionState.Closed, dbStateAfterClose); Assert.AreEqual(ConnectionState.Open, dbStateAfterSafeOpen); }
public void OpenConnection_WithFailIfMissingOnNewDatabase_CausesException() { //Arrange var csb = new SqliteConnectionStringBuilder { DatabaseFilePath = TempDatabaseFiles.GetNewDatabasePath(this), FailIfMissing = true }; Exception openException = null; //Act try { using (var db = new SqliteConnection(csb.ConnectionString)) { db.Open(); } } catch (Exception e) { openException = e; } //Assert Assert.IsNotNull(openException as SqliteException); Assert.IsFalse(File.Exists(csb.DatabaseFilePath)); }
public void OpenConnection_CheckingStateOnDisposedConnection_CausesException() { //Arrange string dbPath = TempDatabaseFiles.GetNewDatabasePath(this); Exception disposedException = null; // ReSharper disable once NotAccessedVariable ConnectionState dbStateBeforeOpen = ConnectionState.Broken; //Act var db = new SqliteConnection(dbPath, true); db.Dispose(); try { // ReSharper disable once RedundantAssignment dbStateBeforeOpen = db.State; } catch (Exception e) { disposedException = e; } //Assert Assert.IsNotNull(disposedException as ObjectDisposedException); }
public void TableSchema_CreateTableWithoutOpen_CausesException() { //Arrange bool tableExists = false; Exception openException = null; //Act try { using (var db = new SqliteConnection(TempDatabaseFiles.GetNewDatabasePath(this), true)) { using (var cmd = new SqliteCommand(String.Format(tableCreateSql1, tableName), db)) { //db.Open(); - testing executing query without opening first cmd.ExecuteNonQuery(); //The following line should never be hit tableExists = db.TableExists(tableName); } } } catch (Exception e) { openException = e; } //Assert Assert.IsFalse(tableExists); Assert.IsNotNull(openException as InvalidOperationException); }
public void OpenConnection_OpenDefaultDatabaseWithMissingDefaultDatabase_CausesException() { //Arrange string dbPath = TempDatabaseFiles.GetNewDatabasePath(this); Exception secondConnectionException = null; //Calling ShutdownSqlite3Provider() should not be necessary, but it is the only way // to dispose of the default database; which is necessary here because we are running // multiple tests that need to set the default database. SqliteConnection.ShutdownSqlite3Provider(); //Act using (var db = new SqliteConnection(dbPath, true, SqliteOpenFlags.ReadWrite, false)) //false = not default database { db.Open(); try { //This should fail, because the default database has not been set. using (var db2 = new SqliteConnection()) { db2.Open(); } } catch (Exception e) { secondConnectionException = e; } } //Assert Assert.IsTrue(File.Exists(dbPath)); Assert.IsNotNull(secondConnectionException as InvalidOperationException); }
public void TableSchema_CreateTableWithOpen_IsSuccessful() { //Arrange bool tableExists; bool fakeTableExists; string dbPath = TempDatabaseFiles.GetNewDatabasePath(this); //Act using (var db = new SqliteConnection(dbPath, true)) { using (var cmd = new SqliteCommand(String.Format(tableCreateSql1, tableName), db)) { db.Open(); cmd.ExecuteNonQuery(); tableExists = db.TableExists(tableName); //Testing calling TableExists() on a table that doesn't exist fakeTableExists = db.TableExists("fake.table.name_123456"); } } using (var db = new SqliteConnection(dbPath, true)) { //Make sure table still exists tableExists &= db.TableExists(tableName); } //Assert Assert.IsTrue(tableExists); Assert.IsFalse(fakeTableExists); }
public void TableSchema_AddColumns_IsSuccessful() { //Arrange IColumnInfo[] initialColumnList = null; IColumnInfo[] finalColumnList = null; string dbPath = TempDatabaseFiles.GetNewDatabasePath(this); //Act using (var db = new SqliteConnection(dbPath, true)) { using (var cmd = new SqliteCommand(String.Format(tableCreateSql1, tableName), db)) { db.Open(); cmd.ExecuteNonQuery(); initialColumnList = db.GetTableColumnList(tableName); cmd.CommandText = String.Format(addColumnSql1, tableName); cmd.ExecuteNonQuery(); cmd.CommandText = String.Format(addColumnSql2, tableName); cmd.ExecuteNonQuery(); finalColumnList = db.GetTableColumnList(tableName); } } //Assert Assert.IsNotNull(initialColumnList); Assert.AreEqual(3, initialColumnList.Length); //Should be three columns Assert.IsNotNull(finalColumnList); Assert.AreEqual(5, finalColumnList.Length); //Should be five columns, with the two that were added Assert.AreEqual("EncryptedColumn", finalColumnList[3].Name); Assert.AreEqual(DbType.Encrypted, finalColumnList[3].DataType); Assert.AreEqual("DateTimeOffsetColumn", finalColumnList[4].Name); Assert.AreEqual(DbType.DateTimeOffset, finalColumnList[4].DataType); }
public void OpenConnection_WithSafeCloseCallWhenClosed_DatabaseIsClosed() { //Arrange string dbPath = TempDatabaseFiles.GetNewDatabasePath(this); Exception openException = null; ConnectionState dbState = ConnectionState.Broken; //Act try { using (var db = new SqliteConnection(dbPath, true)) { //It should be safe to call SafeClose() on a closed database db.SafeClose(); dbState = db.State; } } catch (Exception e) { openException = e; } //Assert Assert.IsNull(openException); Assert.AreEqual(ConnectionState.Closed, dbState); }
public void OpenConnection_MultipleConnectionsWithTheDefaultDatabase_IsSuccessful() { //Arrange string dbPath = TempDatabaseFiles.GetNewDatabasePath(this); Exception secondConnectionException = null; Exception fourthConnectionException = null; Exception thirdConnectionException = null; //Calling ShutdownSqlite3Provider() should not be necessary, but it is the only way // to dispose of the default database; which is necessary here because we are running // multiple tests that need to set the default database. SqliteConnection.ShutdownSqlite3Provider(); //Act using (var db = new SqliteConnection(dbPath, true, SqliteOpenFlags.ReadWrite, true)) //true (as last parameter) = make this default database { db.Open(); try { //Using the default constructor for SqliteConnection causes it to use the default database, if that has been set // - this should be successful - i.e. not throw an exception using (var db2 = new SqliteConnection()) { db2.Open(); } } catch (Exception e) { secondConnectionException = e; } try { //Opening a new database pointing to the same file, should still fail using (var db3 = new SqliteConnection(dbPath, true)) { db3.Open(); } } catch (Exception e) { thirdConnectionException = e; } } try { //Using the default constructor for SqliteConnection causes it to use the default database, if that has been set // - this should be successful even though the original connection was disposed, because default databases do not get disposed using (var db4 = new SqliteConnection()) { db4.Open(); } } catch (Exception e) { fourthConnectionException = e; } //Assert Assert.IsTrue(File.Exists(dbPath)); Assert.IsNull(secondConnectionException); Assert.IsNotNull(thirdConnectionException as InvalidOperationException); Assert.IsNull(fourthConnectionException); }
public void SchemaVersion_CheckAndSetVersion_ManagesConnectionStateProperly() { //Arrange string dbPath = TempDatabaseFiles.GetNewDatabasePath(this); long schemaVersionBeforeSet; long intendedSchemaVersion = 23; long schemaVersionAfterSet; ConnectionState beforeGetClosed; ConnectionState afterGetClosed; ConnectionState beforeGetOpen; ConnectionState afterGetOpen; ConnectionState beforeSetClosed; ConnectionState afterSetClosed; ConnectionState beforeSetOpen; ConnectionState afterSetOpen; //Act using (var db = new SqliteConnection(dbPath, true)) { schemaVersionBeforeSet = db.GetDatabaseSchemaVersion(); db.SafeClose(); //Want the database closed so we can make sure that setting // the schema doesn't require the connection to be open. beforeSetClosed = db.State; db.SetDatabaseSchemaVersion(intendedSchemaVersion); afterSetClosed = db.State; db.SafeOpen(); //Should now be open beforeSetOpen = db.State; db.SetDatabaseSchemaVersion(intendedSchemaVersion); afterSetOpen = db.State; } using (var db = new SqliteConnection(dbPath, true)) { beforeGetClosed = db.State; schemaVersionAfterSet = db.GetDatabaseSchemaVersion(); afterGetClosed = db.State; db.SafeOpen(); beforeGetOpen = db.State; schemaVersionAfterSet = db.GetDatabaseSchemaVersion(); afterGetOpen = db.State; } //Assert Assert.AreEqual(0, schemaVersionBeforeSet); Assert.AreEqual(intendedSchemaVersion, schemaVersionAfterSet); Assert.AreEqual(ConnectionState.Closed, beforeSetClosed); //Should be closed because just closed Assert.AreEqual(ConnectionState.Closed, afterSetClosed); //Should be closed if previously closed Assert.AreEqual(ConnectionState.Open, beforeSetOpen); //Should be open because just opened Assert.AreEqual(ConnectionState.Open, afterSetOpen); //Should be open if previously open Assert.AreEqual(ConnectionState.Closed, beforeGetClosed); //Should be closed before activity, or explicit Open() Assert.AreEqual(ConnectionState.Closed, afterGetClosed); //Should be closed if previously closed Assert.AreEqual(ConnectionState.Open, beforeGetOpen); //Should be open because just opened Assert.AreEqual(ConnectionState.Open, afterGetOpen); //Should be open if previously open }
public void TableSchema_AddColumnsInCorrectMode_IsSuccessful() { //Arrange IColumnInfo[] columnList = null; string dbPath = TempDatabaseFiles.GetNewDatabasePath(this); bool beginMaintenanceModeSuccess; bool endMaintenanceModeSuccess; Exception openException = null; //Act using (var db = new SqliteConnection(dbPath, true)) { using (var cmd = new SqliteCommand(String.Format(tableCreateSql1, tableName), db)) { db.Open(); cmd.ExecuteNonQuery(); } //Adding the first column using Maintenance Mode using (var cmd = new SqliteCommand(String.Format(addColumnSql1, tableName), db, true)) //true = maintenance mode { beginMaintenanceModeSuccess = db.BeginDatabaseMaintenanceMode(); try { //Opening the database should fail, because BeginDatabaseMaintenanceMode() already opens it db.Open(); } catch (Exception e) { openException = e; } db.SafeOpen(); //Safe open should be fine - because it is "safe" cmd.ExecuteNonQuery(); //GetTableColumnList() should work correctly whether the database is in Maintenance Mode or not columnList = db.GetTableColumnList(tableName); endMaintenanceModeSuccess = db.EndDatabaseMaintenanceMode(); } //Adding the second column NOT using Maintenance Mode using (var cmd = new SqliteCommand(String.Format(addColumnSql2, tableName), db, false)) //false = not maintenance mode { db.Open(); cmd.ExecuteNonQuery(); //GetTableColumnList() should work correctly whether the database is in Maintenance Mode or not columnList = db.GetTableColumnList(tableName); } } //Assert Assert.IsNotNull(columnList); Assert.AreEqual(5, columnList.Length); //Should be five columns, with the two that were added Assert.IsTrue(beginMaintenanceModeSuccess); Assert.IsTrue(endMaintenanceModeSuccess); Assert.IsNotNull(openException as InvalidOperationException); }
public void OpenConnection_MultipleConnectionsWithTheSameDatabaseFilePath_CausesException() { //Arrange string dbPath = TempDatabaseFiles.GetNewDatabasePath(this); Exception secondConnectionException = null; Exception thirdConnectionException = null; Exception fourthConnectionException = null; //Calling ShutdownSqlite3Provider() should not be necessary, but it is the only way // to dispose of the default database; which is necessary here because we are running // multiple tests that need to set the default database. SqliteConnection.ShutdownSqlite3Provider(); //Act using (var db = new SqliteConnection(dbPath, true, SqliteOpenFlags.ReadWrite, false)) //false = not default database { db.Open(); try { //This should fail, because there cannot be multiple simultaneous connections to the same database file using (var db2 = new SqliteConnection(dbPath, true)) { db2.Open(); } } catch (Exception e) { secondConnectionException = e; } try { //This should fail, because there cannot be multiple simultaneous connections to the same database file using (var db3 = new SqliteConnection(dbPath, true)) { db3.Open(); } } catch (Exception e) { thirdConnectionException = e; } } try { //This should be fine, because the other connection has been disposed. using (var db4 = new SqliteConnection(dbPath, true)) { db4.Open(); } } catch (Exception e) { fourthConnectionException = e; } //Assert Assert.IsTrue(File.Exists(dbPath)); Assert.IsNotNull(secondConnectionException as InvalidOperationException); Assert.IsNotNull(thirdConnectionException as InvalidOperationException); Assert.IsNull(fourthConnectionException); }
public void OpenConnection_WithSafeOpenMethodCall_DatabaseIsOpen() { //Arrange string dbPath = TempDatabaseFiles.GetNewDatabasePath(this); //Act using (var db = new SqliteConnection(dbPath, true)) { db.SafeOpen(); //Assert Assert.AreEqual(ConnectionState.Open, db.State); } }
public void OpenConnection_WithFilePathOnly_CreatesFile() { //Arrange string dbPath = TempDatabaseFiles.GetNewDatabasePath(this); //Act using (var db = new SqliteConnection(dbPath, true)) { db.Open(); } //Assert Assert.IsTrue(File.Exists(dbPath)); }
public void WriteAndReadData_EncryptedColumn_IsSuccessful() { //Arrange var cryptEngine = new SampleAesCryptEngine("myTestPassword"); string dbPath = TempDatabaseFiles.GetNewDatabasePath(this); int dataItemIndex = 0; using (var db = new SqliteConnection(dbPath, cryptEngine, true)) { //Creating my table & adding encrypted column using (var cmd = new SqliteCommand(String.Format(tableCreateSql, tableName), db)) { db.SafeOpen(); cmd.ExecuteNonQuery(); cmd.CommandText = String.Format(addColumnSql, tableName); cmd.ExecuteNonQuery(); } //Act using (var cmd = new SqliteCommand(String.Format(insertSql2, tableName), db)) { //Adding my records foreach (Tuple <DateTime, string> dataItem in _sampleData) { cmd.Parameters.Clear(); cmd.Parameters.Add("@date", dataItem.Item1); cmd.Parameters.Add("@text", dataItem.Item2); cmd.AddEncryptedParameter(new SqliteParameter("@encrypted", dataItem.Item2)); //Adds the string value (Tuple Item2) but with encryption db.SafeOpen(); cmd.ExecuteNonQuery(); } } //Assert using (var cmd = new SqliteCommand(String.Format(selectSql1, "[IdColumn], [DateTimeColumn], [TextColumn], [EncryptedColumn]", tableName, "[IdColumn]"), db)) using (var reader = new SqliteDataReader(cmd)) { while (reader.Read()) { Assert.AreEqual(_sampleData[dataItemIndex].Item2, reader.GetString("TextColumn")); Assert.AreNotEqual(_sampleData[dataItemIndex].Item2, reader.GetString("EncryptedColumn")); //The encrypted text should not match the unencrypted text Assert.AreEqual(_sampleData[dataItemIndex].Item2, reader.GetDecrypted <string>("EncryptedColumn")); //The decrypted text should match the unencrypted text dataItemIndex++; } Assert.AreEqual(3, dataItemIndex); //Should have read three records } } }
public void SchemaVersion_CheckVersionOnNewDatabase_ReturnsZero() { //Arrange string dbPath = TempDatabaseFiles.GetNewDatabasePath(this); long schemaVersion; //Act using (var db = new SqliteConnection(dbPath, true)) { schemaVersion = db.GetDatabaseSchemaVersion(); } //Assert Assert.AreEqual(0, schemaVersion); }
public void OpenConnection_WithMissingOpenMethodCall_DatabaseIsClosed() { //Arrange string dbPath = TempDatabaseFiles.GetNewDatabasePath(this); //Act using (var db = new SqliteConnection(dbPath, true)) { //Not calling Open() for this test //db.Open(); //Assert Assert.AreEqual(ConnectionState.Closed, db.State); } }
public void OpenConnection_WithConnectionString_CreatesFile() { //Arrange var csb = new SqliteConnectionStringBuilder { DatabaseFilePath = TempDatabaseFiles.GetNewDatabasePath(this), BusyTimeout = 100, JournalMode = SqliteJournalModeEnum.Wal }; //Act using (var db = new SqliteConnection(csb.ConnectionString)) { db.Open(); } //Assert Assert.IsTrue(File.Exists(csb.DatabaseFilePath)); }
public void InsertSelect_DateTimeColumnStoredUsingStrings_MatchesInsertedValue() { //Arrange string dbPath = TempDatabaseFiles.GetNewDatabasePath(this); DateTime dateTimeColumnInsertValue = DateTime.Now; string textColumnInsertValue = "Hello SQLite!"; DateTime dateTimeColumnSelectValue; string textColumnSelectValue; var csb = new SqliteConnectionStringBuilder { DatabaseFilePath = dbPath, JournalMode = SqliteJournalModeEnum.Default, StoreDateTimeAsTicks = false }; //Act using (var db = new SqliteConnection(csb.ConnectionString)) { using (var cmd = new SqliteCommand(String.Format(tableCreateSql1, tableName), db)) { db.Open(); cmd.ExecuteNonQuery(); cmd.CommandText = String.Format(insertSql, tableName, "DateTimeColumn"); cmd.Parameters.Add(new SqliteParameter("@date", dateTimeColumnInsertValue)); cmd.Parameters.Add(new SqliteParameter("text", textColumnInsertValue)); //prefixing the parameter name with @ is not required cmd.ExecuteNonQuery(); cmd.Parameters.Clear(); //This isn't really necessary, because the parameters added above are ignored/not present in the following CommandText values cmd.CommandText = String.Format(selectSql1, "DateTimeColumn", tableName, "DateTimeColumn"); dateTimeColumnSelectValue = (DateTime)cmd.ExecuteScalar(); cmd.CommandText = String.Format(selectSql1, "TextColumn", tableName, "DateTimeColumn"); textColumnSelectValue = (string)cmd.ExecuteScalar(); } } //Assert Assert.IsFalse(csb.StoreDateTimeAsTicks); Assert.AreEqual(dateTimeColumnInsertValue, dateTimeColumnSelectValue); Assert.AreEqual(textColumnInsertValue, textColumnSelectValue); }
public void OpenConnection_CallingSafeCloseOnDisposedConnection_CausesNoException() { //Arrange string dbPath = TempDatabaseFiles.GetNewDatabasePath(this); Exception disposedException = null; //Act var db = new SqliteConnection(dbPath, true); db.Dispose(); try { db.SafeClose(); } catch (Exception e) { disposedException = e; } //Assert Assert.IsNull(disposedException); }
public void SchemaVersion_SetVersionOnNewDatabase_IsSuccessful() { //Arrange string dbPath = TempDatabaseFiles.GetNewDatabasePath(this); long schemaVersionBeforeSet; long intendedSchemaVersion = 23; long schemaVersionAfterSet; //Act using (var db = new SqliteConnection(dbPath, true)) { schemaVersionBeforeSet = db.GetDatabaseSchemaVersion(); db.SetDatabaseSchemaVersion(intendedSchemaVersion); } using (var db = new SqliteConnection(dbPath, true)) { schemaVersionAfterSet = db.GetDatabaseSchemaVersion(); } //Assert Assert.AreEqual(0, schemaVersionBeforeSet); Assert.AreEqual(intendedSchemaVersion, schemaVersionAfterSet); }
public void OpenConnection_MultipleDefaultDatabases_CausesException() { //Arrange string dbPath = TempDatabaseFiles.GetNewDatabasePath(this); string dbPath2 = TempDatabaseFiles.GetNewDatabasePath(this); Exception secondConnectionException = null; //Calling ShutdownSqlite3Provider() should not be necessary, but it is the only way // to dispose of the default database; which is necessary here because we are running // multiple tests that need to set the default database. SqliteConnection.ShutdownSqlite3Provider(); //Act using (var db = new SqliteConnection(dbPath, true, SqliteOpenFlags.ReadWrite, true)) //true (as last parameter) = make this default database { db.Open(); } try { //This should fail, because it is not possible to set two databases to be the default using (var db2 = new SqliteConnection(dbPath2, true, SqliteOpenFlags.ReadWrite, true)) //true (as last parameter) = make this default database { db2.Open(); } } catch (Exception e) { secondConnectionException = e; } //Assert Assert.IsTrue(File.Exists(dbPath)); Assert.IsFalse(File.Exists(dbPath2)); Assert.IsNotNull(secondConnectionException as InvalidOperationException); }
public void TableSchema_CreateTableThenDrop_IsSuccessful() { //Arrange bool tableExistsBeforeDrop; bool tableExistsAfterDrop; string dbPath = TempDatabaseFiles.GetNewDatabasePath(this); //Act using (var db = new SqliteConnection(dbPath, true)) { using (var cmd = new SqliteCommand(String.Format(tableCreateSql1, tableName), db)) { db.Open(); cmd.ExecuteNonQuery(); } } using (var db = new SqliteConnection(dbPath, true)) { using (var cmd = new SqliteCommand(String.Format(tableDropSql, tableName), db)) { db.Open(); tableExistsBeforeDrop = db.TableExists(tableName); cmd.ExecuteNonQuery(); } } using (var db = new SqliteConnection(dbPath, true)) { tableExistsAfterDrop = db.TableExists(tableName); } //Assert Assert.IsTrue(tableExistsBeforeDrop); Assert.IsFalse(tableExistsAfterDrop); }
public void InsertSelect_DateTimeOffsetColumn_MatchesInsertedValue() { //Arrange string dbPath = TempDatabaseFiles.GetNewDatabasePath(this); DateTimeOffset dateTimeColumnInsertValue1 = DateTimeOffset.Now; DateTimeOffset dateTimeColumnSelectValue1; DateTimeOffset dateTimeColumnInsertValue2 = DateTimeOffset.UtcNow.AddHours(2); DateTimeOffset dateTimeColumnSelectValue2; string textColumnInsertValue1 = "Hello SQLite!"; string textColumnSelectValue1; string textColumnInsertValue2 = "Goodbye SQLite!"; string textColumnSelectValue2; long recordId1; long recordId2; Exception duplicateParameterException = null; //Act using (var db = new SqliteConnection(dbPath, true)) { using (var cmd = new SqliteCommand(String.Format(tableCreateSql2, tableName), db)) { db.Open(); cmd.ExecuteNonQuery(); cmd.CommandText = String.Format(insertSql, tableName, "DateTimeOffsetColumn"); cmd.Parameters.Add(new SqliteParameter("@date", dateTimeColumnInsertValue1)); cmd.Parameters.Add(new SqliteParameter("text", textColumnInsertValue1)); //prefixing the parameter name with @ is not required recordId1 = cmd.ExecuteReturnRowId(); try { //This should fail, because adding multiple parameters with the same name is not allowed; we need to clear parameters first. cmd.Parameters.Add(new SqliteParameter("@date", dateTimeColumnInsertValue2)); cmd.Parameters.Add(new SqliteParameter("text", textColumnInsertValue2)); //prefixing the parameter name with @ is not required recordId2 = cmd.ExecuteReturnRowId(); } catch (Exception e) { duplicateParameterException = e; } cmd.Parameters.Clear(); //allows parameters to be re-added cmd.Parameters.Add(new SqliteParameter("@date", dateTimeColumnInsertValue2)); cmd.Parameters.Add(new SqliteParameter("@text", textColumnInsertValue2)); recordId2 = cmd.ExecuteReturnRowId(); cmd.Parameters.Clear(); //This isn't really necessary, because the parameters added above are ignored/not present in the following CommandText values cmd.CommandText = String.Format(selectSql2, "DateTimeOffsetColumn", tableName, "IdColumn", recordId1); dateTimeColumnSelectValue1 = (DateTimeOffset)cmd.ExecuteScalar(); cmd.CommandText = String.Format(selectSql2, "TextColumn", tableName, "IdColumn", recordId1); textColumnSelectValue1 = (string)cmd.ExecuteScalar(); cmd.CommandText = String.Format(selectSql2, "DateTimeOffsetColumn", tableName, "IdColumn", recordId2); dateTimeColumnSelectValue2 = (DateTimeOffset)cmd.ExecuteScalar(); cmd.CommandText = String.Format(selectSql2, "TextColumn", tableName, "IdColumn", recordId2); textColumnSelectValue2 = (string)cmd.ExecuteScalar(); } } //Assert Assert.IsNotNull(duplicateParameterException as InvalidOperationException); Assert.AreEqual(1, recordId1); //First inserted record should have IdColumn of 1 Assert.AreEqual(2, recordId2); //Second inserted record should have IdColumn of 2 Assert.AreEqual(dateTimeColumnInsertValue1, dateTimeColumnSelectValue1); Assert.AreEqual(textColumnInsertValue1, textColumnSelectValue1); Assert.AreEqual(dateTimeColumnInsertValue2, dateTimeColumnSelectValue2); Assert.AreEqual(textColumnInsertValue2, textColumnSelectValue2); }
public void WriteAndReadData_EncryptedColumnWithWrongPassword_CausesException() { //Arrange string rightPassword = "******"; string wrongPassword = "******"; var cryptEngine = new SampleAesCryptEngine(rightPassword); string dbPath = TempDatabaseFiles.GetNewDatabasePath(this); Exception decryptException = null; using (var db = new SqliteConnection(dbPath, cryptEngine, true)) { //Creating my table & adding encrypted column using (var cmd = new SqliteCommand(String.Format(tableCreateSql, tableName), db)) { db.SafeOpen(); cmd.ExecuteNonQuery(); cmd.CommandText = String.Format(addColumnSql, tableName); cmd.ExecuteNonQuery(); } //Act using (var cmd = new SqliteCommand(String.Format(insertSql2, tableName), db)) { //Adding my records foreach (Tuple <DateTime, string> dataItem in _sampleData) { cmd.Parameters.Clear(); cmd.Parameters.Add("@date", dataItem.Item1); cmd.Parameters.Add("@text", dataItem.Item2); cmd.AddEncryptedParameter(new SqliteParameter("@encrypted", dataItem.Item2)); //Adds the string value (Tuple Item2) but with encryption db.SafeOpen(); cmd.ExecuteNonQuery(); } } } //Assert cryptEngine = new SampleAesCryptEngine(wrongPassword); //Testing first with the wrong password using (var db = new SqliteConnection(dbPath, cryptEngine, true)) using (var cmd = new SqliteCommand(String.Format(selectSql2, "[IdColumn], [DateTimeColumn], [TextColumn], [EncryptedColumn]", tableName, "[IdColumn]"), db)) using (var reader = new SqliteDataReader(cmd)) { while (reader.Read()) { try { //This should fail, because the value that is read from the column cannot be properly decrypted without the correct password Assert.AreEqual(_sampleData[0].Item2, reader.GetDecrypted <string>("EncryptedColumn")); } catch (Exception e) { decryptException = e; } } Assert.IsNotNull(decryptException as CryptographicException); } //Just checking to make sure we can still decrypt the value with the correct password cryptEngine = new SampleAesCryptEngine(rightPassword); //Should work correctly with the right password using (var db = new SqliteConnection(dbPath, cryptEngine, true)) using (var cmd = new SqliteCommand(String.Format(selectSql2, "[IdColumn], [DateTimeColumn], [TextColumn], [EncryptedColumn]", tableName, "[IdColumn]"), db)) using (var reader = new SqliteDataReader(cmd)) { while (reader.Read()) { Assert.AreEqual(_sampleData[0].Item2, reader.GetDecrypted <string>("EncryptedColumn")); //The decrypted text should match the unencrypted text } } }
public void WriteAndReadData_BasicColumns_IsSuccessful() { //Arrange string dbPath = TempDatabaseFiles.GetNewDatabasePath(this); int dataItemIndex = 0; using (var db = new SqliteConnection(dbPath, true)) { //Creating my table using (var cmd = new SqliteCommand(String.Format(tableCreateSql, tableName), db)) { db.SafeOpen(); cmd.ExecuteNonQuery(); } //Act using (var cmd = new SqliteCommand(String.Format(insertSql1, tableName), db)) { //Adding my records foreach (Tuple <DateTime, string> dataItem in _sampleData) { cmd.Parameters.Clear(); cmd.Parameters.Add("@date", dataItem.Item1); cmd.Parameters.Add("@text", dataItem.Item2); db.SafeOpen(); cmd.ExecuteNonQuery(); } } db.SafeClose(); //Closing the database to confirm that readers automatically open the underlying database connection, when necessary //Assert using (var cmd = new SqliteCommand(String.Format(selectSql1, "[IdColumn], [DateTimeColumn], [TextColumn]", tableName, "[IdColumn]"), db)) using (var reader = new SqliteDataReader(cmd)) { while (reader.Read()) { Assert.AreEqual((dataItemIndex + 1), reader.GetInt64("IdColumn")); Assert.AreEqual(_sampleData[dataItemIndex].Item1, reader.GetDateTime("DateTimeColumn")); Assert.AreEqual(_sampleData[dataItemIndex].Item2, reader.GetString("TextColumn")); dataItemIndex++; } Assert.AreEqual(3, dataItemIndex); //Should have read three records } Assert.AreEqual(ConnectionState.Open, db.State); //It is expected that a database connection will be open after being used with a DataReader db.SafeOpen(); //Setting the database to open to confirm that readers do not have problems with an already-open database dataItemIndex = 0; using (var cmd = new SqliteCommand(String.Format(selectSql2, "[DateTimeColumn], [TextColumn]", tableName, "[DateTimeColumn] ASC"), db)) using (var reader = new SqliteDataReader(cmd)) { while (reader.Read()) { Assert.AreEqual(DateTime.MinValue, reader.GetDateTime("DateTimeColumn")); //Should have found the MinValue based on the ORDER BY clause dataItemIndex++; } Assert.AreEqual(1, dataItemIndex); //Should have only read one record based on the LIMIT clause } dataItemIndex = 0; using (var cmd = new SqliteCommand(String.Format(selectSql2, "[DateTimeColumn], [TextColumn]", tableName, "[DateTimeColumn] DESC"), db)) using (var reader = new SqliteDataReader(cmd)) { while (reader.Read()) { Assert.AreEqual(DateTime.MaxValue, reader.GetDateTime("DateTimeColumn")); //Should have found the MaxValue based on the ORDER BY clause dataItemIndex++; } Assert.AreEqual(1, dataItemIndex); //Should have only read one record based on the LIMIT clause } } }
public void TableSchema_AddColumnsInWrongMode_CausesException() { //Arrange IColumnInfo[] columnList = null; string dbPath = TempDatabaseFiles.GetNewDatabasePath(this); bool beginMaintenanceModeSuccess; bool endMaintenanceModeSuccess; Exception maintenanceModeException = null; Exception notMaintenanceModeException = null; //Act using (var db = new SqliteConnection(dbPath, true)) { using (var cmd = new SqliteCommand(String.Format(tableCreateSql1, tableName), db)) { db.Open(); cmd.ExecuteNonQuery(); } //Adding the first column using Maintenance Mode using (var cmd = new SqliteCommand(String.Format(addColumnSql1, tableName), db, true)) //true = maintenance mode { db.SafeOpen(); try { //Oops. This should fail, because I am executing the command before I set maintenance mode (below) cmd.ExecuteNonQuery(); } catch (Exception e) { maintenanceModeException = e; } beginMaintenanceModeSuccess = db.BeginDatabaseMaintenanceMode(); //GetTableColumnList() should work correctly whether the database is in Maintenance Mode or not columnList = db.GetTableColumnList(tableName); } //Adding the second column NOT using Maintenance Mode using (var cmd = new SqliteCommand(String.Format(addColumnSql2, tableName), db, false)) //false = not maintenance mode { db.SafeOpen(); try { //Oops. This should fail, because I am executing the command before I turned off maintenance mode (below) cmd.ExecuteNonQuery(); } catch (Exception e) { notMaintenanceModeException = e; } endMaintenanceModeSuccess = db.EndDatabaseMaintenanceMode(); //GetTableColumnList() should work correctly whether the database is in Maintenance Mode or not columnList = db.GetTableColumnList(tableName); } } //Assert Assert.IsNotNull(columnList); Assert.AreEqual(3, columnList.Length); //Should be three columns, because the add column sql should have failed Assert.IsTrue(beginMaintenanceModeSuccess); Assert.IsTrue(endMaintenanceModeSuccess); Assert.IsNotNull(maintenanceModeException as InvalidOperationException); Assert.IsNotNull(notMaintenanceModeException as InvalidOperationException); }