protected override int Connect_PerformanceTest(string strConnection) { // Version for performance testing int recordsRead = 0; DAO.DBEngine dbEngine = new DAO.DBEngine(); dbEngine.Idle(DAO.IdleEnum.dbRefreshCache); DAO.Database db = dbEngine.OpenDatabase(strConnection, false, false); DAO.Recordset rs = db.OpenRecordset( m_cfgDatabase.querySELECT.Replace("?", m_cfgDatabase.paramValue.ToString()), DAO.RecordsetTypeEnum.dbOpenDynaset, DAO.RecordsetOptionEnum.dbReadOnly); if (!(rs.BOF && rs.EOF)) { // Go through each record in the RecordSet; for this performance version just count // the number of records read rs.MoveFirst(); dbEngine.Idle(DAO.IdleEnum.dbFreeLocks); while (!rs.EOF) { recordsRead++; rs.MoveNext(); dbEngine.Idle(DAO.IdleEnum.dbFreeLocks); } rs.Close(); } db.Close(); return(recordsRead); }
protected override void Connect_Read(string strConnection) { // Use the DAO::DBEngine to open an Access database and read recordsets // Note: On one machine running Windows 10 and Office 365, the DBEngine had these details: // * TypeLib = {4AC9E1DA-5BAD-4AC7-86E3-24F4CDCECA28} // * Name = Microsoft Office 16.0 Access Database Engine Object Library // * Assembly = Microsoft.Office.Interop.Access.Dao, Version=15.0.0.0, Culture=neutral, PublicKeyToken=71E9BCE111E9429C // * Path = C:\Program Files\Microsoft Office\root\VFS\ProgramFilesCommonX64\Microsoft Shared\Office16\ACEDAO.DLL DAO.DBEngine dbEngine = new DAO.DBEngine(); dbEngine.Idle(DAO.IdleEnum.dbRefreshCache); DAO.Database db = dbEngine.OpenDatabase(strConnection, false, false); DAO.Recordset rs = db.OpenRecordset( m_cfgDatabase.querySELECT, DAO.RecordsetTypeEnum.dbOpenDynaset, DAO.RecordsetOptionEnum.dbReadOnly); if (!(rs.BOF && rs.EOF)) { // Go through each record in the RecordSet, writing the result to the console window Simple_Members rsMember = new Simple_Members(); Console.WriteLine(rsMember.GetRecordHeader()); int recordsRead = 0; rs.MoveFirst(); dbEngine.Idle(DAO.IdleEnum.dbFreeLocks); while (!rs.EOF) { recordsRead++; try { ConvertRecordset(in rs, ref rsMember); Console.WriteLine(rsMember.GetRecordAsString()); } catch (Exception ex) { Console.WriteLine(UtilitiesGeneral.FormatException( this.ToString(), System.Reflection.MethodBase.GetCurrentMethod().Name, ex.Message)); } rs.MoveNext(); dbEngine.Idle(DAO.IdleEnum.dbFreeLocks); } rs.Close(); Console.WriteLine(" ({0} records)", recordsRead); } db.Close(); Console.WriteLine(); }
protected override void Connect_Stats(string strConnection) { // Generate some statistics about the selected database (see "Northwind_DAO.Connect_Stats()" // for additional information) string dbName = m_utilsDAO.GetDbName(strConnection); DAO.DBEngine dbEngine = new DAO.DBEngine(); dbEngine.Idle(DAO.IdleEnum.dbRefreshCache); DAO.Database db = dbEngine.OpenDatabase(strConnection, false, false); // Tables if (db.TableDefs.Count > 0) { // Note: Access 97 databases tend to come with Console.WriteLine(" ({0} tables in {1})", db.TableDefs.Count, dbName); foreach (DAO.TableDef td in db.TableDefs) { Console.WriteLine(" {0}", td.Name); } } else Console.WriteLine(" (There are no tables in {0}!)", db.Name); db.Close(); Console.WriteLine(); }
public List <string> GetFields(string strConnection, string strTable) { // Return a list of the columns in the supplied table List <string> columns = new List <string>(); DAO.DBEngine dbEngine = new DAO.DBEngine(); dbEngine.Idle(DAO.IdleEnum.dbRefreshCache); DAO.Database db = dbEngine.OpenDatabase(strConnection, false, false); if (db.TableDefs.Count > 0) { try { if (db.TableDefs[strTable].Fields.Count > 0) { columns.Add(m_fieldHeader); foreach (DAO.Field fd in db.TableDefs[strTable].Fields) { columns.Add(string.Format(Schema_Header_Column_Formatting, fd.Name, fd.Type, GetFieldTypeAsString(fd), fd.Size, fd.Required)); } } } catch (Exception ex) { Console.WriteLine(UtilitiesGeneral.FormatException( this.ToString(), System.Reflection.MethodBase.GetCurrentMethod().Name, ex.Message)); } } db.Close(); return(columns); }
public List <string> GetTables(string strConnection, bool removeSysTables = false) { // Return a list of the tables in the supplied database // Note: Access databases use a number System tables used to manage the database, such as: // * MSysAccessObjects // * MSysACEs // * MSysCmdbars // * MSysIMEXColumns // * MSysIMEXSpecs // * MSysObjects // * MSysQueries // * MSysRelationships List <string> tables = new List <string>(); DAO.DBEngine dbEngine = new DAO.DBEngine(); dbEngine.Idle(DAO.IdleEnum.dbRefreshCache); DAO.Database db = dbEngine.OpenDatabase(strConnection, false, false); if (db.TableDefs.Count > 0) { foreach (DAO.TableDef td in db.TableDefs) { if ((!removeSysTables) || (!td.Name.StartsWith("MSys"))) { tables.Add(td.Name); } } } db.Close(); return(tables); }
protected override void Connect_Read(string strConnection) { // Use the DAO::DBEngine to open an Access database and read recordsets // Note: On one machine running Windows 10 and Office 365, the DBEngine had these details: // * TypeLib = {4AC9E1DA-5BAD-4AC7-86E3-24F4CDCECA28} // * Name = Microsoft Office 16.0 Access Database Engine Object Library // * Assembly = Microsoft.Office.Interop.Access.Dao, Version=15.0.0.0, Culture=neutral, PublicKeyToken=71E9BCE111E9429C // * Path = C:\Program Files\Microsoft Office\root\VFS\ProgramFilesCommonX64\Microsoft Shared\Office16\ACEDAO.DLL DAO.DBEngine dbEngine = new DAO.DBEngine(); dbEngine.Idle(DAO.IdleEnum.dbRefreshCache); DAO.Database db = dbEngine.OpenDatabase(strConnection, false, false); DAO.Recordset rs = db.OpenRecordset( m_cfgDatabase.querySELECT.Replace("?", m_cfgDatabase.paramValue.ToString()), DAO.RecordsetTypeEnum.dbOpenDynaset, DAO.RecordsetOptionEnum.dbReadOnly); if (!(rs.BOF && rs.EOF)) { // Go through each record in the RecordSet, writing the result to the console window int recordsRead = 0; Console.WriteLine("\t{0}{1}{2}", Northwind_Products.colProductID.PadRight(Northwind_Products.colProductIDWidth), Northwind_Products.colUnitPrice.PadRight(Northwind_Products.colUnitPriceWidth), Northwind_Products.colProductName); rs.MoveFirst(); dbEngine.Idle(DAO.IdleEnum.dbFreeLocks); while (!rs.EOF) { recordsRead++; Console.WriteLine("\t{0}{1}{2}", ((int)m_utilsDAO.SafeGetFieldValue(rs, Northwind_Products.colProductID)).ToString().PadRight(Northwind_Products.colProductIDWidth), ((decimal)m_utilsDAO.SafeGetFieldValue(rs, Northwind_Products.colUnitPrice)).ToString("0.00").PadRight(Northwind_Products.colUnitPriceWidth), (m_utilsDAO.SafeGetFieldValue(rs, Northwind_Products.colProductName)).ToString()); rs.MoveNext(); dbEngine.Idle(DAO.IdleEnum.dbFreeLocks); } rs.Close(); Console.WriteLine(" ({0} records)", recordsRead); } db.Close(); Console.WriteLine(); }
public override string GetDbName(string strConnection) { // Get the name of the database associated with the connection string string dbName = string.Empty; try { DAO.DBEngine dbEngine = new DAO.DBEngine(); dbEngine.Idle(DAO.IdleEnum.dbRefreshCache); DAO.Database db = dbEngine.OpenDatabase(strConnection, false, false); dbName = db.Name; db.Close(); } catch (Exception ex) { Console.WriteLine(UtilitiesGeneral.FormatException( this.ToString(), System.Reflection.MethodBase.GetCurrentMethod().Name, ex.Message)); } return(dbName); }
protected override void Connect_Writeable(string strConnection) { // Use the DAO::DBEngine to open an Access database and write recordsets DAO.DBEngine dbEngine = new DAO.DBEngine(); dbEngine.Idle(DAO.IdleEnum.dbRefreshCache); DAO.Database db = dbEngine.OpenDatabase(strConnection, false, false); string strQuery = m_cfgDatabase.querySELECT.Replace("?", m_cfgDatabase.paramValue.ToString()); Console.Write("Open database read-only: "); DAO.Recordset rs = db.OpenRecordset( strQuery, DAO.RecordsetTypeEnum.dbOpenDynaset, DAO.RecordsetOptionEnum.dbReadOnly); if (!(rs.BOF && rs.EOF)) { Console.WriteLine(m_utilsDAO.IsRecordUpdateable(rs)); rs.Close(); } Console.Write("Open database writeable: "); rs = db.OpenRecordset( strQuery, DAO.RecordsetTypeEnum.dbOpenDynaset); if (!(rs.BOF && rs.EOF)) { Console.WriteLine(m_utilsDAO.IsRecordUpdateable(rs)); Console.WriteLine(); // Now go through all records and check various properties int recordsRead = 0; Console.WriteLine(" (Using the \"ProductName\" field as an example)"); Console.WriteLine( "#\tRequired\tValidateOnSet\tValidationRule\tValidationText\tSize\tValue"); DAO.Field fd; rs.MoveFirst(); while (!rs.EOF) { recordsRead++; fd = m_utilsDAO.SafeGetField(rs, "ProductName"); if (fd != null) { Console.WriteLine("{0}\t{1}\t\t{2}\t\t{3}\t\t{4}\t\t{5}\t{6}", recordsRead, fd.Required, m_utilsDAO.BoolFieldToString(fd.ValidateOnSet), m_utilsDAO.StringFieldToString(fd.ValidationRule), m_utilsDAO.StringFieldToString(fd.ValidationText), fd.Size, fd.Value); } else { Console.WriteLine("{0}(record is null)", recordsRead); } rs.MoveNext(); } rs.Close(); } db.Close(); Console.WriteLine(); }