public void Transaction_Rollback_Transaction()
{
using var connection = new JetConnection(JetConnection.GetConnectionString(StoreName, Helpers.DataAccessProviderFactory));
connection.Open();
using var firstTransaction = connection.BeginTransaction();
using (var command = connection.CreateCommand("select count(*) from SimpleTable"))
{
command.Transaction = firstTransaction;
command.ExecuteScalar();
}
firstTransaction.Rollback();
using var secondTransaction = connection.BeginTransaction();
using (var command = connection.CreateCommand("select count(*) from SimpleTable"))
{
command.Transaction = secondTransaction;
command.ExecuteScalar();
}
connection.Close();
connection.Open();
using (var command = connection.CreateCommand("select count(*) from SimpleTable"))
{
command.ExecuteScalar();
}
}
public void Run()
{
try
{
for (var i = 0; i < 100; i++)
{
using var connection = new JetConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=Northwind.accdb");
connection.Open();
for (var j = 0; j < 2000; j++)
{
Console.WriteLine($"{i:00}: {j:000}");
//
// Select_Union:
//
using (var command1 = connection.CreateCommand())
{
command1.CommandText = @"SELECT `c`.`Address`
FROM `Customers` AS `c`
WHERE `c`.`City` = 'Berlin'
UNION
SELECT `c0`.`Address`
FROM `Customers` AS `c0`
WHERE `c0`.`City` = 'London'";
using (var dataReader1 = command1.ExecuteReader())
{
while (dataReader1.Read())
{
}
}
}
//
// Select_bool_closure:
//
using (var command2 = connection.CreateCommand())
{
command2.CommandText = @"SELECT 1
FROM `Customers` AS `c`";
using (var dataReader2 = command2.ExecuteReader())
{
while (dataReader2.Read())
{
}
}
}
}
}
}
catch (AccessViolationException e)
{
Console.WriteLine(e);
Console.ReadKey(true);
}
}
public void Transaction_Rollback_Transaction()
{
DbCommand command;
JetConnection connection = new JetConnection(ConnectionString);
connection.Open();
DbTransaction firstTransaction = connection.BeginTransaction();
command = connection.CreateCommand("Select count(*) from SimpleTable");
command.Transaction = firstTransaction;
command.ExecuteScalar();
firstTransaction.Rollback();
DbTransaction secondTransaction = connection.BeginTransaction();
command = connection.CreateCommand("Select count(*) from SimpleTable");
command.Transaction = secondTransaction;
command.ExecuteScalar();
connection.Close();
connection.Open();
command = connection.CreateCommand("Select count(*) from SimpleTable");
command.ExecuteScalar();
}
public void Raise_Events()
{
var stateChangeCount = 0;
using var connection = new JetConnection(JetConnection.GetConnectionString(StoreName, Helpers.DataAccessProviderFactory));
connection.StateChange += (sender, args) =>
{
Console.WriteLine($"{args.OriginalState} => {args.CurrentState}");
stateChangeCount++;
};
connection.Open();
using var command = connection.CreateCommand("select * from MSysAccessStorage");
var dataReader = command.ExecuteReader();
while (dataReader.Read())
{
}
connection.Close();
connection.Open();
connection.Dispose();
Assert.AreEqual(4, stateChangeCount);
}
public void CreateDatabaseWithWrongPassword()
{
using var connection = new JetConnection(Helpers.DataAccessProviderFactory);
var command = connection.CreateCommand();
command.CommandText = $"CREATE DATABASE '{StoreName}' PASSWORD 'wrong password'";
command.ExecuteNonQuery();
Assert.IsTrue(File.Exists(StoreName));
var csb = Helpers.DataAccessProviderFactory.CreateConnectionStringBuilder();
csb.SetDataSource(StoreName);
csb.SetDatabasePassword("right password");
connection.ConnectionString = csb.ConnectionString;
try
{
connection.Open();
}
catch (Exception e)
{
Assert.IsInstanceOfType(e, typeof(DbException));
}
}
public void RenameColumnQuery()
{
try
{
JetConnection.ClearAllPools();
File.Delete("AdoxTest.accdb");
}
catch (Exception e)
{
Console.WriteLine(e);
}
AdoxWrapper.CreateEmptyDatabase(JetConnection.GetConnectionString("AdoxTest.accdb"));
using (JetConnection connection = new JetConnection(JetConnection.GetConnectionString("AdoxTest.accdb")))
{
connection.Open();
CreateTable(connection);
CheckColumnExists(connection, "tableName", "columnName");
connection.CreateCommand("rename column tableName.columnName to newColumnName").ExecuteNonQuery();
connection.Close();
JetConnection.ClearAllPools();
connection.Open();
CheckColumnExists(connection, "tableName", "newColumnName");
}
JetConnection.ClearAllPools();
File.Delete("AdoxTest.accdb");
}
public void CreateAndDropDatabaseWithUnsetConnectionWithoutDataAccessProviderFactoryThrows()
{
using var connection = new JetConnection();
Assert.ThrowsException <InvalidOperationException>(
() => { using var command = connection.CreateCommand(); });
}
public void Transaction_Execute_Close_Open_Execute()
{
DbCommand command;
JetConnection connection = new JetConnection(ConnectionString);
connection.Open();
var transaction = connection.BeginTransaction();
command = connection.CreateCommand("Select count(*) from MSysAccessStorage");
command.Transaction = transaction;
command.ExecuteScalar();
connection.Close();
connection.Open();
command = connection.CreateCommand("Select count(*) from MSysAccessStorage");
command.ExecuteScalar();
}
public void Initialize()
{
if (!JetConnection.DatabaseExists(ConnectionString))
{
JetConnection.CreateEmptyDatabase(ConnectionString);
}
using (JetConnection connection = new JetConnection(ConnectionString))
{
connection.Open();
if (!connection.TableExists("SimpleTable"))
{
connection.CreateCommand("CREATE TABLE SimpleTable ( Col varchar(10) )").ExecuteNonQuery();
}
connection.CreateCommand("DELETE FROM SimpleTable").ExecuteNonQuery();
}
JetConnection.ClearAllPools();
}
public void Transaction_Execute_Close_Open()
{
DbCommand command;
JetConnection connection = new JetConnection(ConnectionString);
connection.Open();
var transaction = connection.BeginTransaction();
command = connection.CreateCommand("INSERT INTO SimpleTable(Col) VALUES ('aaa')");
command.Transaction = transaction;
command.ExecuteScalar();
connection.Close();
connection.Open();
command = connection.CreateCommand("Select count(*) from SimpleTable");
Assert.AreEqual(0, command.ExecuteScalar());
}
public void Delete_rollback_implicit()
{
using (var transaction = _connection.BeginTransaction())
{
using var deleteCommand = _connection.CreateCommand();
deleteCommand.CommandText = @"delete * from `Cookies` where `Name` = 'Basic'";
deleteCommand.Transaction = transaction;
var affected = deleteCommand.ExecuteNonQuery();
Assert.AreEqual(1, affected);
}
using var verifyCommand = _connection.CreateCommand();
verifyCommand.CommandText = @"select count(*) as `Count` from `Cookies`";
var count = verifyCommand.ExecuteScalar();
Assert.AreEqual(2, count);
}
private static void CreateTable(JetConnection connection)
{
DbCommand command;
command = connection.CreateCommand(@"
CREATE TABLE tableName (columnName int);
CREATE INDEX indexName ON tableName (columnName);");
command.ExecuteNonQuery();
command.Dispose();
}
public void IfExists()
{
using var command = _connection.CreateCommand(
@"IF NOT EXISTS (SELECT * FROM `INFORMATION_SCHEMA.TABLES` WHERE `TABLE_NAME` = '__EFMigrationsHistory') THEN CREATE TABLE `__EFMigrationsHistory` (
`MigrationId` varchar(150) NOT NULL,
`ProductVersion` varchar(32) NOT NULL,
CONSTRAINT `PK___EFMigrationsHistory` PRIMARY KEY (`MigrationId`)
)");
command.ExecuteNonQuery();
}
private void CheckColumnExists(JetConnection connection, string tableName, string columnName)
{
var command = connection.CreateCommand($"SELECT COUNT(*) FROM (SHOW TABLECOLUMNS) WHERE Table='{tableName}' AND Name='{columnName}'");
int result = (int)command.ExecuteScalar();
command.Dispose();
if (result != 1)
{
throw new Exception($"Column {tableName}.{columnName} not found");
}
}
private void CheckIndexExists(JetConnection connection, string indexName)
{
var command = connection.CreateCommand($"SELECT COUNT(*) FROM (SHOW INDEXES) WHERE Name='{indexName}'");
int result = (int)command.ExecuteScalar();
command.Dispose();
if (result != 1)
{
throw new Exception($"Index {indexName} not found");
}
}
public void GetDataReader_From_Open_Connection()
{
JetConnection connection = new JetConnection(ConnectionString);
connection.Open();
var dataReader = connection.CreateCommand("Select * from MSysAccessStorage").ExecuteReader();
while (dataReader.Read())
{
}
}
public static int CountRows(JetConnection jetConnection, string sqlStatement)
{
DbCommand command = jetConnection.CreateCommand(sqlStatement);
DbDataReader dataReader = command.ExecuteReader();
int count = 0;
while (dataReader.Read())
{
count++;
}
return(count);
}
public void GetDataReader_From_Open_Connection()
{
using var connection = new JetConnection(JetConnection.GetConnectionString(StoreName, Helpers.DataAccessProviderFactory), Helpers.DataAccessProviderFactory);
connection.Open();
var dataReader = connection.CreateCommand($"select * from `{JetConnection.DefaultDualTableName}`")
.ExecuteReader();
while (dataReader.Read())
{
}
}
public void Transaction_Execute_Close_Open()
{
using var connection = new JetConnection(JetConnection.GetConnectionString(StoreName, Helpers.DataAccessProviderFactory));
connection.Open();
using var transaction = connection.BeginTransaction();
using (var command = connection.CreateCommand("INSERT INTO SimpleTable(Col) VALUES ('aaa')"))
{
command.Transaction = transaction;
command.ExecuteScalar();
}
connection.Close();
connection.Open();
using (var command = connection.CreateCommand("select count(*) from SimpleTable"))
{
Assert.AreEqual(0, command.ExecuteScalar());
}
}
public void Transaction_Execute_Commit_Execute_Transaction()
{
DbCommand command;
JetConnection connection = new JetConnection(ConnectionString);
connection.Open();
DbTransaction transaction = connection.BeginTransaction();
command = connection.CreateCommand("Select count(*) from SimpleTable");
command.Transaction = transaction;
command.ExecuteScalar();
transaction.Commit();
command = connection.CreateCommand("Select count(*) from SimpleTable");
command.Transaction = transaction;
command.ExecuteScalar();
transaction.Commit();
connection.Close();
}
public void GetDataReader_From_Open_Connection()
{
using var connection = new JetConnection(JetConnection.GetConnectionString(StoreName, Helpers.DataAccessProviderFactory));
connection.Open();
var dataReader = connection.CreateCommand("select * from MSysAccessStorage")
.ExecuteReader();
while (dataReader.Read())
{
}
}
public void Transaction_Execute_Close_Open_Execute()
{
using var connection = new JetConnection(JetConnection.GetConnectionString(StoreName, Helpers.DataAccessProviderFactory));
connection.Open();
using var transaction = connection.BeginTransaction();
using (var command = connection.CreateCommand("select count(*) from MSysAccessStorage"))
{
command.Transaction = transaction;
command.ExecuteScalar();
}
connection.Close();
connection.Open();
using (var command = connection.CreateCommand("select count(*) from MSysAccessStorage"))
{
command.ExecuteScalar();
}
}
public void Transaction_Execute_Commit_Commit()
{
using var connection = new JetConnection(JetConnection.GetConnectionString(StoreName, Helpers.DataAccessProviderFactory));
connection.Open();
using var transaction = connection.BeginTransaction();
using var command = connection.CreateCommand("select count(*) from SimpleTable");
command.Transaction = transaction;
command.ExecuteScalar();
transaction.Commit();
transaction.Commit();
}
public void Cleanup()
{
using (JetConnection connection = JetDatabaseFixture.GetConnection())
{
connection.Open();
for (int i = 0; i < 300; i++)
{
string sql = $@"DROP TABLE [Employees_{i}]";
connection.CreateCommand(sql).ExecuteNonQuery();
}
}
}
public void CreateAndDropDatabaseFromConnection()
{
using var connection = new JetConnection(StoreName, Helpers.DataAccessProviderFactory);
connection.CreateDatabase();
Assert.IsTrue(File.Exists(StoreName));
using var command = connection.CreateCommand();
command.CommandText = "DROP DATABASE " + StoreName;
command.ExecuteNonQuery();
Assert.IsFalse(File.Exists(StoreName));
}
private DbCommand CreateCommand(string commandText, object[] parameters)
{
var command = _connection.CreateCommand();
command.CommandText = commandText;
for (var i = 0; i < parameters.Length; i++)
{
command.Parameters.AddWithValue("p" + i, parameters[i]);
}
return(command);
}
public void Transaction_Execute_Commit_Close_Open_Execute()
{
using var connection = new JetConnection(JetConnection.GetConnectionString(StoreName, Helpers.DataAccessProviderFactory));
connection.Open();
using var transaction = connection.BeginTransaction();
using (var command = connection.CreateCommand($"select count(*) from `{JetConnection.DefaultDualTableName}`"))
{
command.Transaction = transaction;
command.ExecuteScalar();
}
transaction.Commit();
connection.Close();
connection.Open();
using (var command = connection.CreateCommand($"select count(*) from `{JetConnection.DefaultDualTableName}`"))
{
command.ExecuteScalar();
}
}
public void RenameTable_Query()
{
_connection.CreateCommand("ALTER TABLE tableName RENAME TO newTableName")
.ExecuteNonQuery();
ReOpenConnection();
AssertTableExists("newTableName");
}
public void Prepare_From_Closed_Connection()
{
using var connection = new JetConnection(JetConnection.GetConnectionString(StoreName, Helpers.DataAccessProviderFactory));
try
{
using var command = connection.CreateCommand("select * from MSysAccessStorage");
command.Prepare();
}
catch (Exception e)
{
Assert.AreEqual("\"Prepare\" requires a connection in Open state. Current connection state is Closed", e.Message);
throw;
}
}
public void ExecuteScalar_From_Closed_Connection()
{
JetConnection connection = new JetConnection(ConnectionString);
try
{
var a = connection.CreateCommand("Select * from MSysAccessStorage").ExecuteScalar();
}
catch (Exception e)
{
Assert.AreEqual("\"ExecuteScalar\" requires a connection in Open state. Current connection state is Closed", e.Message);
throw;
}
}