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); }
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); } }