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);
}
public void TrimRecords(DAO.Database DB)
{
DAO.Recordset RS;
string SQL = "select * from tblProps";
RS = DB.OpenRecordset(SQL);
int dbMax = (int)(RS.Fields["dbMaxSize"].Value ?? 0);
int Size = (int)(new System.IO.FileInfo(cDB.Name).Length);
RS.Close();
if (Size > dbMax)
{
SQL = "Select top 20 percent * from tblRecords order by recID desc";
RS = DB.OpenRecordset(SQL);
while (!RS.EOF)
{
RS.Delete();
RS.MoveNext();
}
RS.Close();
}
}
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();
}
public void InsertDao()
{
object objOpt = System.Reflection.Missing.Value;
var dbEngine = new DAO.DBEngine();
DAO.Database cdb = dbEngine.OpenDatabase(@"c:\Projects\ะก\Databases\Databases\dbproba.mdb", objOpt, false, objOpt);
DAO.Recordset rec = cdb.OpenRecordset("Tabl1", DAO.RecordsetTypeEnum.dbOpenDynaset, DAO.RecordsetOptionEnum.dbSeeChanges, DAO.LockTypeEnum.dbOptimistic);
rec.MoveFirst();
MessageBox.Show(rec.Fields["s1"].Value.ToString());
rec.AddNew();
rec.Fields["s1"].Value = "ssssss";
rec.Update();
rec.Close();
cdb.Close();
}
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 void MegaInsertDao()
{
object objOpt = System.Reflection.Missing.Value;
var dbEngine = new DAO.DBEngine();
DAO.Database cdb = dbEngine.OpenDatabase(@"dbproba.mdb", objOpt, false, objOpt);
DAO.Recordset rec = cdb.OpenRecordset("Tabl3", DAO.RecordsetTypeEnum.dbOpenDynaset, DAO.RecordsetOptionEnum.dbSeeChanges, DAO.LockTypeEnum.dbOptimistic);
DateTime d = DateTime.Now;
for (int i = 1; i <= 10000; ++i)
{
rec.AddNew();
rec.Fields["s1"].Value = i.ToString();
rec.Fields["s2"].Value = "n";
rec.Update();
}
rec.Close();
cdb.Close();
MessageBox.Show(DateTime.Now.Subtract(d).ToString());
}
private string CheckDatabase(string DBname)
{
// check database type, version
string Result = "";
try
{
cDB = cDBE.OpenDatabase(DBname, DAO.DriverPromptEnum.dbDriverNoPrompt, false, "");
DAO.Recordset RS;
string SQL = "select * from tblProps";
RS = cDB.OpenRecordset(SQL);
if (RS.EOF)
{
RS.Close();
cDB.Close();
Result = "No Data.";
}
else
{
// check database type
string ReportedType = (string)(RS.Fields["dbType"].Value ?? "");
short ReportedVersion = (short)(RS.Fields["dbVersion"].Value ?? 0);
RS.Close();
cDB.Close();
if (ReportedType == DBtype)
{
// check version
Result = CheckVersion(ReportedVersion, DBname);
}
else
{
Result = "Wrong database type.";
}
}
}
catch (Exception ex)
{
mf.Tls.WriteErrorLog("clsDatabase:CheckDatabase: " + ex.Message);
}
return(Result);
}
public string LastValue(string Table, string Field, string SortField = "", string Where = "")
{
// returns the last value of a field in a table
DAO.Recordset RS;
if (SortField == "")
{
SortField = Field;
}
string SQL = "Select * from " + Table;
if (Where != "")
{
SQL += " " + Where + " ";
}
if (SortField != "")
{
SQL += " order by " + SortField;
}
RS = cDB.OpenRecordset(SQL);
if (RS.EOF)
{
RS.Close();
return("");
}
else
{
RS.MoveLast();
if (RS.Fields[Field].Type == (short)DAO.DataTypeEnum.dbMemo |
RS.Fields[Field].Type == (short)DAO.DataTypeEnum.dbText)
{
RS.Close();
return(RS.Fields[Field].Value ?? "");
}
else
{
RS.Close();
return(RS.Fields[Field].Value ?? 0);
}
}
}
private bool CopyData(DAO.Database DBnew, DAO.Database DBold)
{
DAO.Recordset RSnew;
DAO.Recordset RSold;
string TBL;
List <string> Flds = new List <string>();
try
{
foreach (DAO.TableDef TDnew in DBnew.TableDefs)
{
TBL = TDnew.Name;
if (!IsSysTable(TBL))
{
Flds.Clear(); // erase list
// check if table exists in old database
foreach (DAO.TableDef TDold in DBold.TableDefs)
{
if (TDold.Name == TBL)
{
// make a list of compatable fields
foreach (DAO.Field FldNew in TDnew.Fields)
{
if (!IsPrimaryKey(FldNew, TDnew))
{
foreach (DAO.Field Fldold in TDold.Fields)
{
if ((Fldold.Name == FldNew.Name) & (Fldold.Type == FldNew.Type))
{
// found matching field in old table
Flds.Add(FldNew.Name);
break;
}
}
}
}
// copy data from matching fields
RSnew = DBnew.OpenRecordset(TBL);
RSold = DBold.OpenRecordset(TBL);
while (!RSold.EOF)
{
RSnew.AddNew();
foreach (string MatchingField in Flds)
{
RSnew.Fields[MatchingField].Value = RSold.Fields[MatchingField].Value;
}
RSnew.Update();
RSold.MoveNext();
}
RSnew.Close();
RSold.Close();
break;
}
}
}
}
return(true);
}
catch (Exception Ex)
{
mf.Tls.WriteErrorLog("clsDatabase: CopyData: " + Ex.Message);
return(false);
}
}
private string CheckVersion(short Ver, string DBname)
{
string SQL;
DAO.Recordset RS;
string Result = "";
if (Ver > cDBversion)
{
// higher version, program out of date
Result = "Database version does not match, software out of date.";
}
else if (Ver == cDBversion)
{
Result = "true";
}
else
{
// lower version, update
// close current database
try
{
cDB.Close();
}
catch (Exception)
{
}
// copy base file to tmp file in data folder
string NewPath = mf.Tls.DataFolder + "\\NewTmp.mdb";
string BasePath = mf.Tls.SettingsFolder + "\\TempMonBase.mdb";
File.WriteAllBytes(NewPath, Properties.Resources.Base);
FileInfo BaseFile = new FileInfo(BasePath);
// copy current database to tmp file in data folder
string TmpPath = mf.Tls.DataFolder + "\\OldTmp.mdb";
try
{
// delete tmp file, if it exists
File.Delete(TmpPath);
}
catch (Exception Ex)
{
mf.Tls.WriteErrorLog("clsDatabase: CheckVersion: " + Ex.Message);
}
FileInfo OldFile = new FileInfo(DBname);
OldFile.CopyTo(TmpPath);
// open new tmp database and old tmp database
DAO.Database DBnew = cDBE.OpenDatabase(NewPath, DAO.DriverPromptEnum.dbDriverNoPrompt, false, "");
DAO.Database DBold = cDBE.OpenDatabase(TmpPath, DAO.DriverPromptEnum.dbDriverNoPrompt, false, "");
// delete new database properties records
SQL = "select * from tblProps";
RS = DBnew.OpenRecordset(SQL);
while (!RS.EOF)
{
RS.Delete();
RS.MoveNext();
}
RS.Close();
// copy matching data from old database to new database
if (CopyData(DBnew, DBold))
{
// update database properties
SQL = "select * from tblProps";
RS = DBnew.OpenRecordset(SQL);
if (RS.EOF)
{
RS.AddNew();
}
else
{
RS.Edit();
}
RS.Fields["dbType"].Value = DBtype;
RS.Fields["dbVersion"].Value = cDBversion;
RS.Update();
RS.Close();
// copy new updated database to current database name
DBnew.Close();
DBold.Close();
File.Delete(DBname); // delete current file
FileInfo NewFile = new FileInfo(NewPath);
NewFile.CopyTo(DBname); // copy new file to current file name
File.Delete(TmpPath); // delete tmp copy of current file
File.Delete(NewPath); // delete new file
Result = "true";
}
else
{
// failed to copy, remove new database and tmp database
File.Delete(TmpPath); // delete tmp copy of current file
File.Delete(NewPath); // delete new file
Result = "Failed to update database version.";
}
}
return(Result);
}
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();
}
