/// <summary>
/// 得到Access列的描述
/// </summary>
/// <param name="TableName">表名</param>
/// <param name="ColumnName">列名</param>
public static string GetColumnDescription(string TableName, string ColumnName)
{
//Caption
//ColumnHidden
//ColumnOrder
//ColumnWidth
//DecimalPlaces
//Description
//Format
//InputMask
const int dbUseJet = 2;
String dbName = System.Web.HttpContext.Current.Server.MapPath(ConfigurationManager.ConnectionStrings["DbPath"].ToString());
DAO.Workspace DAOWorkspace;
DAO.Database DAODatabase;
DAO.DBEngine DAODBEngine = new DAO.DBEngine();
// 创建一个工作区
DAOWorkspace = DAODBEngine.CreateWorkspace(" WorkSpace ", "admin", null, dbUseJet);
// 打开数据库
DAODatabase = DAOWorkspace.OpenDatabase(dbName, false, false, null);
DAO.TableDef DAOTable;
DAO.Field DAOField;
// 表对象
DAOTable = DAODatabase.TableDefs[TableName];
DAOField = DAOTable.Fields[ColumnName];
return(DAOField.Properties["Description"].Value.ToString());
}
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);
}
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);
}
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();
}
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);
}
/// <summary>
/// 创建一个 Access 数据库实例（创建 mdb 文件）
/// </summary>
/// <param name="fileName">欲创建的数据库文件名</param>
/// <returns>一个 Access 实例数据库</returns>
public static AccessDb CreateDbInstance(string fileName)
{
DAO.DBEngine daoDBE = new DAO.DBEngine();
DAO.Database daoDB = daoDBE.CreateDatabase(fileName, ";LANGID=0x0804;CP=936;COUNTRY=0;", DAO.DatabaseTypeEnum.dbVersion40);
daoDB.Close();
return(new AccessDb(fileName));
}
static void Main(string[] args)
{
if (args.Length == 0) {
Console.WriteLine("You must call this utility with the full path to the tpdb8 database.");
return;
}
var dbPath = Path.GetFullPath(args[0]);
if (!File.Exists(dbPath)) {
Console.WriteLine("Could not find the file '{0}'", dbPath);
return;
}
pathToThumbs = dbPath + "_files";
if (!Directory.Exists(pathToThumbs)) {
Console.WriteLine("Could not find the Thumbs directory '{0}'", pathToThumbs);
return;
}
// Record the amout of free space available on the drive before we start
var drive = Path.GetPathRoot(dbPath);
ulong freespaceBefore = 0;
DriveFreeBytes(drive, out freespaceBefore);
// Open the database
var dbEngine = new DAO.DBEngine();
var db = dbEngine.OpenDatabase(dbPath);
var rs = db.OpenRecordset("SELECT idThumb FROM Thumbnail ORDER BY idThumb ASC", DAO.RecordsetTypeEnum.dbOpenForwardOnly);
// Go through each Thumb ID and if there are gaps,
// attempt to delete the thumbnails that would be in these gaps.
int prevID = 0;
int curID;
while (!rs.EOF) {
curID = (int)rs.Fields[0].Value;
for (int i = prevID; i < curID; i++)
DeleteThumb(i);
rs.MoveNext();
prevID = curID + 1;
}
rs.Close();
rs = null;
db.Close();
db = null;
dbEngine = null;
// Write a summary report
if (countOfDeleted > 0) {
Console.WriteLine("Deleted a total of '{0}' thumbnails for database '{1}'", countOfDeleted, dbPath);
// Check how much free space we have now
ulong freespaceAfter = 0;
if (DriveFreeBytes(drive, out freespaceAfter)) {
var freed = freespaceBefore - freespaceAfter;
Console.WriteLine("Total disk space saved: {0:N0} bytes.", freed);
}
} else {
Console.WriteLine("No orphaned thumbnails were found, database is clean.");
}
}
private void FillDefaultLine(Control container, string[] ids)
{
string dbpath = GetDirectory() + userDir + MAIN_USER_DATABASE;
DAO.Database dat = null;
Control[] txtNewRecord = new Control[FIELDS.Length];
try
{
DAO.DBEngine daoEngine = new DAO.DBEngine();
dat = daoEngine.OpenDatabase(dbpath, false, false, "");
DAO.TableDef tableDef = dat.TableDefs[TABLE_NAME];
for (int i = 1; i < FIELDS.Length; i++)
{
if (!FIELDS[i].Equals("opnam1") && !FIELDS[i].Equals("opnam2"))
{ // aliases which are not in the database
string defaultValue = (string)tableDef.Fields[FIELDS[i]].Properties["DefaultValue"].Value;
if (defaultValue != null && !defaultValue.Equals("\" \"") && !defaultValue.Equals(""))
{
txtNewRecord[i] = container.FindControl(ids[i]);
if (txtNewRecord[i] != null)
{
defaultValue = MyUtilities.clean(defaultValue, '"');
if (COMBOS[i])
{
((AjaxControlToolkit.ComboBox)txtNewRecord[i]).Text = defaultValue;
}
else if (CHECKBOXES[i])
{
((CheckBox)txtNewRecord[i]).Checked = defaultValue.ToString().Equals("-1");
}
else if (!((TextBox)txtNewRecord[i]).Enabled)
{
defaultValue = null;
}
else
{
((TextBox)txtNewRecord[i]).Text = defaultValue;
}
}
}
}
}
dat.Close();
}
catch (Exception)
{
try
{
dat.Close();
}
catch (Exception) { }
}
}
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 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());
}
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 void Start_Click(object sender, EventArgs e)
{
if (comboNam.Visible)
{
Program.DbYear = comboNam.SelectedItem.ToString();
}
else
{
Program.DbYear = numericNam.Value.ToString();
}
String DBFileName = Program.DbYear + ".mdb";
FileInfo fileInfo = new FileInfo(Environment.CurrentDirectory + @"\Database\" + DBFileName);
if (!fileInfo.Exists)
{
Directory.CreateDirectory(Environment.CurrentDirectory + @"\Database");
using (Stream s = System.Reflection.Assembly.GetExecutingAssembly().GetManifestResourceStream("CongNo.DB.mdb"))
{
using (FileStream ResourceFile = new FileStream(fileInfo.ToString(), FileMode.Create, FileAccess.Write))
{
s.CopyTo(ResourceFile);
}
}
String db_file = fileInfo.ToString();
DAO.DBEngine dBEngine = new DAO.DBEngine();
DAO.Database db;
db = dBEngine.OpenDatabase(db_file);
String queryName = "cong_no_draft";
String querySql = String.Format("SELECT invoice.ngay_ct, invoice.ngay_hoa_don, department.ma_phong," +
" department.ten_phong, customers.mst, customers.cong_ty, " +
"invoice.ki_hieu_hoa_don, invoice.so_hoa_don, invoice.han_thanh_toan, revenue.ma_nv, revenue.user_nhap, invoice.kenh_kt, " +
"revenue.so_tai_khoan, revenue.so_tham_chieu, invoice.loai_tien, invoice.tong_nguyen_te, " +
"IIf(Year(invoice.ngay_ct)<{0},invoice.so_tien_phat_sinh) AS du_dau_ky, IIf(Year(invoice.ngay_ct)={0} " +
"And Month(invoice.ngay_ct)=1,invoice.so_tien_phat_sinh) AS no1, IIf(Year(invoice.ngay_ct)={0} And " +
"Month(invoice.ngay_ct)=2,invoice.so_tien_phat_sinh) AS no2, IIf(Year(invoice.ngay_ct)={0} And " +
"Month(invoice.ngay_ct)=3,invoice.so_tien_phat_sinh) AS no3, IIf(Year(invoice.ngay_ct)={0} And " +
"Month(invoice.ngay_ct)=4,invoice.so_tien_phat_sinh) AS no4, IIf(Year(invoice.ngay_ct)={0} And " +
"Month(invoice.ngay_ct)=5,invoice.so_tien_phat_sinh) AS no5, IIf(Year(invoice.ngay_ct)={0} And " +
"Month(invoice.ngay_ct)=6,invoice.so_tien_phat_sinh) AS no6, IIf(Year(invoice.ngay_ct)={0} And " +
"Month(invoice.ngay_ct)=7,invoice.so_tien_phat_sinh) AS no7, IIf(Year(invoice.ngay_ct)={0} And " +
"Month(invoice.ngay_ct)=8,invoice.so_tien_phat_sinh) AS no8, IIf(Year(invoice.ngay_ct)={0} And " +
"Month(invoice.ngay_ct)=9,invoice.so_tien_phat_sinh) AS no9, IIf(Year(invoice.ngay_ct)={0} And " +
"Month(invoice.ngay_ct)=10,invoice.so_tien_phat_sinh) AS no10, IIf(Year(invoice.ngay_ct)={0} And " +
"Month(invoice.ngay_ct)=11,invoice.so_tien_phat_sinh) AS no11, IIf(Year(invoice.ngay_ct)={0} And " +
"Month(invoice.ngay_ct)=12,invoice.so_tien_phat_sinh) AS no12 FROM department " +
"INNER JOIN((revenue INNER JOIN invoice ON (revenue.ki_hieu_hoa_don = invoice.ki_hieu_hoa_don)" +
" AND(revenue.so_hoa_don = invoice.so_hoa_don)) INNER JOIN customers ON invoice.mst = customers.mst)" +
" ON department.ma_phong = revenue.ma_phong ORDER BY invoice.ki_hieu_hoa_don, invoice.so_hoa_don;", Program.DbYear);
DAO.QueryDef cong_no_draft = new DAO.QueryDef();
cong_no_draft.Name = queryName;
cong_no_draft.SQL = querySql;
db.QueryDefs.Append(cong_no_draft);
db.Close();
}
Form form1 = new Form1();
this.Hide();
form1.Show();
Program.OpenDetailFormOnClose = true;
this.Close();
}
public clsDatabase(FormMain CallingForm)
{
mf = CallingForm;
cDBE = new DAO.DBEngine();
}
public void Insert(Type t, System.Collections.IEnumerable data, bool insertDefaultColumns)
{
// temporarily close the connection to the file
bool wasOpen = false;
if (this.Connection.State != ConnectionState.Closed)
{
wasOpen = true;
this.Connection.Close();
}
// get the properties of the type we're inserting
var attr = MappedObjectAttribute.GetAttribute <MappedClassAttribute>(t);
if (attr == null)
{
throw new Exception(string.Format("Type {0}.{1} could not be automatically inserted.", t.Namespace, t.Name));
}
attr.InferProperties(t);
var props = attr.Properties.Where(p => p.Include).ToArray();
// setup DAO
var engine = new DAO.DBEngine();
var db = engine.OpenDatabase(this.Connection.DataSource);
var recordSet = db.OpenRecordset(attr.Name);
var fields = new DAO.Field[props.Length];
var fieldNames = recordSet.Fields
.OfType <DAO.Field>()
.Select(f => f.Name);
for (int i = 0; i < fields.Length; ++i)
{
fields[i] = recordSet.Fields[props[i].Name];
}
// copy
foreach (var obj in data)
{
recordSet.AddNew();
for (int j = 0; j < fields.Length; ++j)
{
if (!props[j].IsIdentity || insertDefaultColumns)
{
var value = props[j].GetValue(obj);
if (props[j].SystemType == typeof(Guid))
{
value = string.Format("{{{0}}}", value);
}
fields[j].Value = value;
}
}
recordSet.Update();
}
// clean up
recordSet.Clone();
db.Close();
// reopen the connection, if the user is expecting it.
if (wasOpen)
{
this.Connection.Open();
}
}
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();
}
