public override void CreateDatabase(
string fileNameOrConnectionString,
DatabaseVersion version = DatabaseVersion.Newest,
CollatingOrder collatingOrder = CollatingOrder.General,
string databasePassword = null)
{
if (databasePassword != null &&
databasePassword.Length > 20)
{
throw new ArgumentOutOfRangeException(nameof(databasePassword));
}
var filePath = JetStoreDatabaseHandling.ExpandFileName(JetStoreDatabaseHandling.ExtractFileNameFromConnectionString(fileNameOrConnectionString));
try
{
using dynamic catalog = new ComObject("ADOX.Catalog");
// ADOX is an ADO eXtension and ADO is build on top of OLE DB.
var connectionString = GetConnectionString(filePath, version, collatingOrder, databasePassword);
using var connection = catalog.Create(connectionString);
}
catch (Exception e)
{
throw new Exception($"Cannot create database \"{filePath}\" using DAO.", e);
}
}
private static string GetOleDbConnectionString(string fileNameOrConnectionString)
{
if (JetStoreDatabaseHandling.IsConnectionString(fileNameOrConnectionString) &&
JetConnection.GetDataAccessProviderType(fileNameOrConnectionString) == DataAccessProviderType.OleDb)
{
return(fileNameOrConnectionString);
}
var filePath = JetStoreDatabaseHandling.ExpandFileName(JetStoreDatabaseHandling.ExtractFileNameFromConnectionString(fileNameOrConnectionString));
var connectionString = JetConnection.GetConnectionString(filePath, DataAccessProviderType.OleDb);
if (JetStoreDatabaseHandling.IsConnectionString(fileNameOrConnectionString) &&
JetConnection.GetDataAccessProviderType(fileNameOrConnectionString) == DataAccessProviderType.Odbc)
{
var oldCsb = new DbConnectionStringBuilder(true)
{
ConnectionString = fileNameOrConnectionString
};
var newCsb = new DbConnectionStringBuilder {
ConnectionString = connectionString
};
newCsb.SetUserId(oldCsb.GetUserId(DataAccessProviderType.Odbc), DataAccessProviderType.OleDb);
newCsb.SetPassword(oldCsb.GetPassword(DataAccessProviderType.Odbc), DataAccessProviderType.OleDb);
newCsb.SetSystemDatabase(oldCsb.GetSystemDatabase(DataAccessProviderType.Odbc), DataAccessProviderType.OleDb);
newCsb.SetDatabasePassword(oldCsb.GetDatabasePassword(DataAccessProviderType.Odbc), DataAccessProviderType.OleDb);
connectionString = newCsb.ConnectionString;
}
return(connectionString);
}
/// <summary>
/// Executes the non query.
/// </summary>
/// <returns></returns>
public override int ExecuteNonQuery()
{
if (Connection == null)
{
throw new InvalidOperationException(Messages.PropertyNotInitialized(nameof(Connection)));
}
LogHelper.ShowCommandText("ExecuteNonQuery", _WrappedCommand);
if (JetStoreDatabaseHandling.TryDatabaseOperation(_WrappedCommand.CommandText))
{
return(1);
}
if (JetRenameHandling.TryDatabaseOperation(Connection.ConnectionString, _WrappedCommand.CommandText))
{
return(1);
}
if (Connection.State != ConnectionState.Open)
{
throw new InvalidOperationException(Messages.CannotCallMethodInThisConnectionState(nameof(ExecuteNonQuery), ConnectionState.Open, Connection.State));
}
_WrappedCommand.Connection = _Connection.InnerConnection;
_WrappedCommand.Transaction = _Transaction == null ? null : _Transaction.WrappedTransaction;
if (_WrappedCommand.CommandType != CommandType.Text)
{
return(_WrappedCommand.ExecuteNonQuery());
}
string[] commandTextList = SplitCommands(_WrappedCommand.CommandText);
int returnValue = -1;
for (int i = 0; i < commandTextList.Length; i++)
{
string commandText = commandTextList[i];
if (_selectRowCountRegularExpression.Match(commandText).Success)
{
if (_rowCount == null)
{
throw new InvalidOperationException("Invalid " + commandText + ". Run a DataReader before.");
}
returnValue = _rowCount.Value;
continue;
}
commandText = ParseIdentity(commandText);
commandText = ParseGuid(commandText);
returnValue = InternalExecuteNonQuery(commandText);
}
return(returnValue);
}
public static bool DatabaseExists(string connectionString)
{
string fileName = JetStoreDatabaseHandling.ExtractFileNameFromConnectionString(connectionString);
if (string.IsNullOrWhiteSpace(fileName))
{
throw new Exception("Cannot retrieve file name from connection string");
}
return(System.IO.File.Exists(fileName));
}
public static void DropDatabase(string connectionString, bool throwOnError = true)
{
string fileName = JetStoreDatabaseHandling.ExtractFileNameFromConnectionString(connectionString);
if (string.IsNullOrWhiteSpace(fileName))
{
throw new Exception("Cannot retrieve file name from connection string");
}
JetStoreDatabaseHandling.DeleteFile(fileName.Trim(), throwOnError);
}
public override void CreateDualTable(
string fileNameOrConnectionString,
string databasePassword = null)
{
if (databasePassword != null &&
databasePassword.Length > 20)
{
throw new ArgumentOutOfRangeException(nameof(databasePassword));
}
using dynamic connection = new ComObject("ADODB.Connection");
// ADOX is an ADO eXtension and ADO is build on top of OLE DB.
var filePath = JetStoreDatabaseHandling.ExpandFileName(JetStoreDatabaseHandling.ExtractFileNameFromConnectionString(fileNameOrConnectionString));
var connectionString = GetConnectionString(filePath, default, default, databasePassword);
public override void CreateDualTable(string fileNameOrConnectionString, string databasePassword = null)
{
try
{
if (databasePassword != null &&
databasePassword.Length > 20)
{
throw new ArgumentOutOfRangeException(nameof(databasePassword));
}
var filePath = JetStoreDatabaseHandling.ExpandFileName(JetStoreDatabaseHandling.ExtractFileNameFromConnectionString(fileNameOrConnectionString));
using var dbEngine = CreateDbEngine();
using var database = dbEngine.OpenDatabase(
filePath,
/* Exclusive */ false,
/* ReadOnly */ false,
"MS Access" + (string.IsNullOrEmpty(databasePassword)
? null
: $";PWD={databasePassword}"));
database.Execute(@"CREATE TABLE `#Dual` (`ID` INTEGER NOT NULL CONSTRAINT `PrimaryKey` PRIMARY KEY)", RecordsetOptionEnum.dbFailOnError);
database.Execute(@"INSERT INTO `#Dual` (`ID`) VALUES (1)", RecordsetOptionEnum.dbFailOnError);
// Check constraints are not supported by DAO. They are supported by ADOX.
// database.Execute(@"ALTER TABLE [#Dual] ADD CONSTRAINT SingleRecord CHECK ([ID] = 1)", RecordsetOptionEnum.dbFailOnError);
using var tableDefs = database.TableDefs;
using var tableDef = tableDefs["#Dual"];
tableDef.ValidationRule = "[ID] = 1"; // not as good as a CHECK CONSTRAINT, but better than nothing
var attributes = (TableDefAttributeEnum)tableDef.Attributes;
attributes |= TableDefAttributeEnum.dbSystemObject;
tableDef.Attributes = attributes;
}
catch (Exception e)
{
throw new Exception($"Could not create dual table `#Dual` using DAO.", e);
}
}
public static string CreateEmptyDatabase(string fileNameOrConnectionString /*, DbProviderFactory dataAccessProviderFactory*/)
{
var fileName = JetStoreDatabaseHandling.ExpandFileName(JetStoreDatabaseHandling.ExtractFileNameFromConnectionString(fileNameOrConnectionString));
string connectionString = null;
using var catalog = GetCatalogInstance();
try
{
// ADOX is an ADO eXtension and ADO is build on top of OLE DB. We always need to use an OLE DB
// connection string for ADOX.
connectionString = JetConnection.GetConnectionString(fileName, DataAccessProviderType.OleDb);
using var connection = catalog.Create(connectionString);
//.Dispose(); // Dispose the returned Connection object, because we don't use it here.
int recordsAffected;
connection.Execute("CREATE TABLE `#Dual` (`ID` COUNTER CONSTRAINT `PrimaryKey` PRIMARY KEY)", out recordsAffected, /*adCmdText*/ 0x1 | /*adExecuteNoRecords*/ 0x80);
connection.Execute("INSERT INTO `#Dual` (`ID`) VALUES (1)", out recordsAffected, /*adCmdText*/ 0x1 | /*adExecuteNoRecords*/ 0x80);
connection.Execute("ALTER TABLE `#Dual` ADD CONSTRAINT `SingleRecord` CHECK (`ID` = 1)", out recordsAffected, /*adCmdText*/ 0x1 | /*adExecuteNoRecords*/ 0x80);
}
catch (Exception e)
{
throw new Exception($"Cannot create database \"{fileName}\" using ADOX with the following connection string: " + connectionString, e);
}
// try
// {
/*
* connectionString = JetConnection.GetConnectionString(fileName, dataAccessProviderFactory);
*
* using var connection = (JetConnection)JetFactory.Instance.CreateConnection();
* connection.ConnectionString = connectionString;
* connection.DataAccessProviderFactory = dataAccessProviderFactory;
* connection.Open();
*
* var script = @"CREATE TABLE `MSysAccessStorage` (
* `DateCreate` DATETIME NULL,
* `DateUpdate` DATETIME NULL,
* `Id` COUNTER NOT NULL,
* `Lv` IMAGE,
* `Name` VARCHAR(128) NULL,
* `ParentId` INT NULL,
* `Type` INT NULL,
* CONSTRAINT `Id` PRIMARY KEY (`Id`)
* );
* CREATE UNIQUE INDEX `ParentIdId` ON `MSysAccessStorage` (`ParentId`, `Id`);
* CREATE UNIQUE INDEX `ParentIdName` ON `MSysAccessStorage` (`ParentId`, `Name`);";
*
* foreach (var commandText in script.Split(new[] { ';' }, StringSplitOptions.RemoveEmptyEntries))
* {
* using var command = connection.CreateCommand(commandText);
* command.ExecuteNonQuery();
* }*/
//
//
// }
// catch (Exception e)
// {
// throw new Exception($"Cannot setup the newly created database \"{fileName}\" using {Enum.GetName(typeof(DataAccessProviderType), JetConnection.GetDataAccessProviderType(dataAccessProviderFactory))} with the following connection string: " + connectionString, e);
// }
//
// try
// {
// using var connection = catalog.ActiveConnection;
// connection.Close();
// }
// catch (Exception e)
// {
// Diagnostics.Debug.WriteLine("Cannot close active connection after create statement.\r\nThe exception is: {0}", e.Message);
// }
return(connectionString);
}
public override void CreateDatabase(
string fileNameOrConnectionString,
DatabaseVersion version = DatabaseVersion.NewestSupported,
CollatingOrder collatingOrder = CollatingOrder.General,
string databasePassword = null)
{
if (databasePassword != null &&
databasePassword.Length > 20)
{
throw new ArgumentOutOfRangeException(nameof(databasePassword));
}
var filePath = JetStoreDatabaseHandling.ExpandFileName(JetStoreDatabaseHandling.ExtractFileNameFromConnectionString(fileNameOrConnectionString));
if (version == DatabaseVersion.NewestSupported &&
string.Equals(System.IO.Path.GetExtension(filePath), ".mdb"))
{
version = DatabaseVersion.Version40;
}
try
{
using var dbEngine = CreateDbEngine();
var databaseType = version switch
{
DatabaseVersion.Version10 => (int)DatabaseTypeEnum.dbVersion10,
DatabaseVersion.Version11 => (int)DatabaseTypeEnum.dbVersion11,
DatabaseVersion.Version20 => (int)DatabaseTypeEnum.dbVersion20,
DatabaseVersion.Version30 => (int)DatabaseTypeEnum.dbVersion30,
DatabaseVersion.Version40 => (int)DatabaseTypeEnum.dbVersion40,
DatabaseVersion.Version120 => (int)DatabaseTypeEnum.dbVersion120,
_ => 0,
};
var daoCollatingOrder = (CollatingOrderEnum)collatingOrder;
var collatingOrderString = daoCollatingOrder switch
{
CollatingOrderEnum.dbSortArabic => ";LANGID=0x0401;CP=1256;COUNTRY=0",
CollatingOrderEnum.dbSortChineseSimplified => ";LANGID=0x0804;CP=936;COUNTRY=0",
CollatingOrderEnum.dbSortChineseTraditional => ";LANGID=0x0404;CP=950;COUNTRY=0",
CollatingOrderEnum.dbSortCyrillic => ";LANGID=0x0419;CP=1251;COUNTRY=0",
CollatingOrderEnum.dbSortCzech => ";LANGID=0x0405;CP=1250;COUNTRY=0",
CollatingOrderEnum.dbSortDutch => ";LANGID=0x0413;CP=1252;COUNTRY=0",
CollatingOrderEnum.dbSortGeneral => ";LANGID=0x0409;CP=1252;COUNTRY=0",
CollatingOrderEnum.dbSortGreek => ";LANGID=0x0408;CP=1253;COUNTRY=0",
CollatingOrderEnum.dbSortHebrew => ";LANGID=0x040D;CP=1255;COUNTRY=0",
CollatingOrderEnum.dbSortHungarian => ";LANGID=0x040E;CP=1250;COUNTRY=0",
CollatingOrderEnum.dbSortIcelandic => ";LANGID=0x040F;CP=1252;COUNTRY=0",
CollatingOrderEnum.dbSortJapanese => ";LANGID=0x0411;CP=932;COUNTRY=0",
CollatingOrderEnum.dbSortKorean => ";LANGID=0x0412;CP=949;COUNTRY=0",
//CollatingOrderEnum.dbSortNordic => ";LANGID=0x041D;CP=1252;COUNTRY=0",
CollatingOrderEnum.dbSortNorwdan => ";LANGID=0x0406;CP=1252;COUNTRY=0",
CollatingOrderEnum.dbSortPolish => ";LANGID=0x0415;CP=1250;COUNTRY=0",
CollatingOrderEnum.dbSortSlovenian => ";LANGID=0x0424;CP=1250;COUNTRY=0",
CollatingOrderEnum.dbSortSpanish => ";LANGID=0x040A;CP=1252;COUNTRY=0",
CollatingOrderEnum.dbSortSwedFin => ";LANGID=0x041D;CP=1252;COUNTRY=0",
CollatingOrderEnum.dbSortThai => ";LANGID=0x041E;CP=874;COUNTRY=0",
CollatingOrderEnum.dbSortTurkish => ";LANGID=0x041F;CP=1254;COUNTRY=0",
_ => ";LANGID=0x0409;CP=1252;COUNTRY=0",
};
if (!string.IsNullOrEmpty(databasePassword))
{
collatingOrderString += $";pwd={databasePassword}";
}
using var workspace = dbEngine.CreateWorkspace(string.Empty, "admin", string.Empty, WorkspaceTypeEnum.dbUseJet);
using var database = databaseType > 0
? workspace.CreateDatabase(filePath, collatingOrderString, databaseType)
: workspace.CreateDatabase(filePath, collatingOrderString);
}
catch (Exception e)
{
throw new Exception($"Cannot create database \"{filePath}\" using DAO.", e);
}
}
