static void Main(string[] args)
{
DAO.DBEngine dbEng = new DAO.DBEngine();
DAO.Workspace ws = dbEng.CreateWorkspace("", "admin", "", DAO.WorkspaceTypeEnum.dbUseJet);
DAO.Database db = ws.OpenDatabase("z:\\docs\\dbfrom.mdb", false, false, "");
DAO.TableDef tdf = db.TableDefs["Test"];
DAO.Field fld = tdf.Fields["AYesNo"];
//dbInteger 3
//accheckbox 106
DAO.Property prp = fld.CreateProperty("DisplayControl", 3, 106);
fld.Properties.Append(prp);
}
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 static void Main(string[] args)
{
try
{
if (args.Length == 0)
{
Console.WriteLine("Please enter an MS Access application path as a parameter!");
return;
}
dbEngine = new DAO.DBEngine();
database = dbEngine.OpenDatabase(args[0]);
DAO.Property allowBypassKeyProperty = null;
foreach (dao.Property property in database.Properties)
{
if (property.Name == "AllowBypassKey")
{
allowBypassKeyProperty = property;
break;
}
}
if (allowBypassKeyProperty == null)
{
allowBypassKeyProperty = database.CreateProperty("AllowBypassKey", DAO.DataTypeEnum.dbBoolean, false, true);
database.Properties.Append(allowBypassKeyProperty);
Console.WriteLine("AllowBypassKey Property has been added. It's Value is '" + allowBypassKeyProperty.Value + "'");
}
else
{
allowBypassKeyProperty.Value = !allowBypassKeyProperty.Value;
Console.WriteLine("AllowBypassKey set to '" + allowBypassKeyProperty.Value + "'!");
}
}
catch (Exception exception)
{
Console.WriteLine("Exception Message: " + exception.Message);
Console.WriteLine("Inner Exception:" + exception.InnerException);
}
finally
{
database.Close();
System.Runtime.InteropServices.Marshal.ReleaseComObject(database);
database = null;
System.Runtime.InteropServices.Marshal.ReleaseComObject(dbEngine);
dbEngine = null;
Console.WriteLine();
Console.WriteLine("Press enter to continue ...");
Console.ReadLine();
}
}
public bool DoesFieldExist(DAO.Database db, string strTable, string strField)
{
// DAO: HelperBoolFieldToString function to determine if a field exists
try
{
DAO.Field ThisField = db.TableDefs[strTable].Fields[strField];
return(true);
}
catch (Exception ex)
{
Console.WriteLine(UtilitiesGeneral.FormatException(
this.ToString(), System.Reflection.MethodBase.GetCurrentMethod().Name, ex.Message));
}
return(false);
}
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 bool TableIsExist(string tableName)
{
DAO.DBEngine daoDBE = new DAO.DBEngine();
DAO.Database daoDB = null;
daoDB = daoDBE.OpenDatabase(this.FileName, false, false, "MS ACCESS;PWD=" + this.Password);
DAO.TableDefs daoTables = daoDB.TableDefs;
foreach (DAO.TableDef daoTable in daoDB.TableDefs)
{
if (daoTable.Name.Equals(tableName))
{
daoDB.Close();
return(true);
}
}
daoDB.Close();
return(false);
}
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);
}
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);
}
private void backgroundWorkerDataService_DoWork(object sender, DoWorkEventArgs e)
{
try
{
if ((e.Argument as Dictionary<string, string>).Count != 0)
{
var db = new DAO.Database(e.Argument as Dictionary<string, string>);
e.Result = db.GetImage(DAO.IMAGE_TYPE.wsq, _id);
}
else
{
var db = new DBHelper.DBUtil();
e.Result = db.GetImageFromWebService(IMAGE_TYPE.wsq, _id);
}
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
}
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();
}
}
public void DummyOpenClose()
{
Console.WriteLine(" (Dummy open and close databases using DAO)");
int startTicks = Environment.TickCount;
string strConnection = string.Empty;
DAO.DBEngine dbEngine = new DAO.DBEngine();
DAO.Database db = null;
foreach (MSAccessDbType dbType in Enum.GetValues(typeof(MSAccessDbType)))
{
m_cfgDatabase.dbType = dbType;
Console.WriteLine(" Testing: {0}", HelperGetAccessName(true));
if (SetConnectionString(ref strConnection))
{
db = dbEngine.OpenDatabase(strConnection, false, false);
db.Close();
}
}
int elapsedTicks = (Environment.TickCount - startTicks);
Console.WriteLine(" (Completed dummy open/close. Took {0}ms.)", elapsedTicks);
}
private static DateTime CreateTableTime = DateTime.MinValue; //最后一次创建表的时间
#region IDb 成员
public void CreateTable(Table table, string tableName)
{
CreateTableTime = DateTime.Now;
if (tableName == null || tableName.Length == 0)
{
tableName = table.Code;
}
DAO.DBEngine daoDBE = new DAO.DBEngine();
DAO.Database daoDB = null;
daoDB = daoDBE.OpenDatabase(this.FileName, false, false, "MS ACCESS;PWD=" + this.Password);
DAO.TableDefs daoTables = daoDB.TableDefs;
foreach (DAO.TableDef daoT in daoDB.TableDefs)
{
if (daoT.Name.Equals(tableName))
{
daoTables.Delete(tableName); //删除现存的表
}
}
DAO.TableDef daoTable = daoDB.CreateTableDef(tableName, 0, "", "");
string strPrimaryKeyFields = "";
foreach (Column _Column in table.Columns)
{
DAO.Field daoField = new DAO.Field();
daoField.Name = _Column.Code;
switch (_Column.DataType)
{
case DataTypeOptions.Int:
daoField.Type = Convert.ToInt16(DAO.DataTypeEnum.dbLong);
break;
case DataTypeOptions.Long:
case DataTypeOptions.Decimal:
daoField.Type = Convert.ToInt16(DAO.DataTypeEnum.dbDouble);
break;
case DataTypeOptions.VarChar:
if (_Column.DataLength > 255)
{
daoField.Type = Convert.ToInt16(DAO.DataTypeEnum.dbMemo);
daoField.AllowZeroLength = true;
}
else
{
daoField.Type = Convert.ToInt16(DAO.DataTypeEnum.dbText);
daoField.Size = _Column.DataLength;
daoField.AllowZeroLength = true;
}
break;
case DataTypeOptions.Text:
daoField.Type = Convert.ToInt16(DAO.DataTypeEnum.dbMemo);
daoField.AllowZeroLength = true;
break;
case DataTypeOptions.File:
daoField.Type = Convert.ToInt16(DAO.DataTypeEnum.dbLongBinary);
break;
default:
throw new Exception("尚未实现的数据类型 " + _Column.DataType);
}
daoField.Required = _Column.IsNotNull;
if (_Column.IsPrimaryKey)
{
strPrimaryKeyFields += _Column.Code + ";";
}
daoTable.Fields.Append(daoField);
}
daoDB.TableDefs.Append(daoTable);
if (table.Name != null && table.Name.Length > 0)
{
DAO.Property daoTableProperty = daoTable.CreateProperty("Description", DAO.DataTypeEnum.dbText, table.Name, 0);
daoTable.Properties.Append(daoTableProperty);
}
foreach (Column _Column in table.Columns)
{
if (_Column.Name != null && _Column.Name.Length > 0)
{
DAO.Field daoField = daoTable.Fields[_Column.Code];
DAO.Property daoColumnProperty = daoField.CreateProperty("Description", DAO.DataTypeEnum.dbText, _Column.Name, 0);
daoField.Properties.Append(daoColumnProperty);
}
}
if (strPrimaryKeyFields.Length > 0)
{
DAO.Index daoIndex = daoTable.CreateIndex("PK_" + tableName);
daoIndex.Fields = strPrimaryKeyFields;
daoIndex.Primary = true;
daoIndex.Unique = true;
daoTable.Indexes.Append(daoIndex);
}
foreach (Index _Index in table.Indexs)
{
string strKeyFields = "";
foreach (Column _KeyColumn in _Index.Columns)
{
strKeyFields += "+" + _KeyColumn.Code + ";";
}
if (strKeyFields.Length > 0)
{
DAO.Index daoIndex = daoTable.CreateIndex(_Index.Code);
daoIndex.Fields = strKeyFields;
daoIndex.Primary = false;
daoIndex.Unique = false;
daoTable.Indexes.Append(daoIndex);
}
}
daoDB.Close();
TimeSpan ts = DateTime.Now - CreateTableTime;
if (ts.Seconds < 2)
{
System.Threading.Thread.Sleep(10000); //Access 数据表创建后需要一段时间才能访问。
}
}
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();
}
private void saveWsqInDatabase(int id, byte[] buffer)
{
//FileStream fs = null;
try
{
// fs = new FileStream("lindex.wsq", FileMode.Open);
// BinaryReader br = new BinaryReader(fs);
// byte[] buffer = br.ReadBytes((int)fs.Length);
if (System.Configuration.ConfigurationManager.AppSettings["Enroll"] == "service")
{
DBUtil db = new DBUtil();
db.UploadImage(IMAGE_TYPE.wsq, id, ref buffer);
}
else
{
var bioProcessor = new BioProcessor.BioProcessor();
Dictionary<string, byte[]> templates = bioProcessor.GetTemplatesFromWSQImage(id, buffer);
Dictionary<string, string> settings = new Dictionary<string, string>();
foreach (var key in ConfigurationManager.AppSettings.AllKeys)
{
settings.Add(key, ConfigurationManager.AppSettings[key]);
}
foreach (ConnectionStringSettings cs in ConfigurationManager.ConnectionStrings)
{
settings.Add(cs.Name, cs.ConnectionString);
}
var db = new DAO.Database(settings);
db.SaveWSQTemplate(id, templates);
this.InvokeOnClick(buttonReadFingers, new MyEventArgs(0));
}
//db.SaveTemplate(id, buffer);
}
catch (Exception ex)
{
toolStripStatusLabelError.ForeColor = Color.Red;
toolStripStatusLabelError.Text = ex.Message;
}
finally
{
//fs.Dispose();
}
}
public Database(DAO.Database _db)
{
db=_db;
}
