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);
}
private static string GetConnectionString(string filePath, DatabaseVersion version, CollatingOrder collatingOrder, string databasePassword)
{
var connectionString = JetConnection.GetConnectionString(filePath, DataAccessProviderType.OleDb);
var databaseType = version switch
{
DatabaseVersion.Version10 => 1,
DatabaseVersion.Version11 => 2,
DatabaseVersion.Version20 => 3,
DatabaseVersion.Version30 => 5,
DatabaseVersion.Version40 => 5,
DatabaseVersion.Version120 => 6,
_ => 0
};
if (collatingOrder > 0)
{
//connectionString += $";Locale Identifier={collatingOrder}";
}
if (databaseType > 0)
{
connectionString += $";Jet OLEDB:Engine Type={databaseType}";
}
if (!string.IsNullOrEmpty(databasePassword))
{
connectionString += $";Jet OLEDB:Database Password={databasePassword}";
}
return(connectionString);
}
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);
}