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