private void bttnSaveTable_Click(object sender, EventArgs e)
{
if (txtTables.Text == "")
{
}
else
{
myDB = dbe.OpenDatabase(svflDialog.FileName);
myTB = myDB.CreateTableDef(txtTables.Text.ToString());
myFL = myTB.CreateField("Ref" + txtTables.Text, DAO.DataTypeEnum.dbLong);
myFL.Attributes = (int)DAO.FieldAttributeEnum.dbAutoIncrField;
myTB.Fields.Append(myFL);
Index myInd = myTB.CreateIndex("primaryKey");
myFL = myInd.CreateField("Ref" + txtTables.Text);
((IndexFields)(myInd.Fields)).Append(myFL);
myInd.Primary = true;
myTB.Indexes.Append(myInd);
myDB.TableDefs.Append(myTB);
MessageBox.Show(myTB.Name + " was created with success.", "Important Message", MessageBoxButtons.OK, MessageBoxIcon.Information);
this.Height = 280;
txtTables.Clear();
txtTables.Enabled = false;
bttnSaveTable.Enabled = false;
txtfields.Focus();
txtfields.Clear();
}
}
private void metroButtonCreateTable_Click(object sender, EventArgs e)
{
myDB = dbe.OpenDatabase(saveFileDialog1.FileName);
if (metroTextBoxTableName.Text == "")
{
MessageBox.Show("You must give a name to your Table", "Important Message", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
else
{
myTB = myDB.CreateTableDef(metroTextBoxTableName.Text);
myFL = myTB.CreateField("Ref" + metroTextBoxTableName.Text, DAO.DataTypeEnum.dbLong);
myFL.Attributes = (int)DAO.FieldAttributeEnum.dbAutoIncrField;
myTB.Fields.Append(myFL);
myInd = myTB.CreateIndex("primaryKey");
myFL = myInd.CreateField("Ref" + metroTextBoxTableName.Text);
((IndexFields)(myInd.Fields)).Append(myFL);
myInd.Primary = true;
myTB.Indexes.Append(myInd);
int n = metroGrid1.Rows.Add();
metroGrid1.Rows[n].Cells[0].Value = "Ref" + metroTextBoxTableName.Text;
metroGrid1.Rows[n].Cells[1].Value = "AutoNumber";
metroButtonCreateTable.Enabled = false;
metroButtonRelation.Enabled = false;
}
}
private void lstTables_SelectedIndexChanged(object sender, EventArgs e)
{
//string dbPath = Variables.NewFilePath;
try
{
myDB = dbE.OpenDatabase(Variables.NewFilePath);
}
catch (Exception ex)
{
//MessageBox.Show("Error openning database! \n" +
// "File path: " + dbPath + "\n" + ex,
// "Error!", MessageBoxButtons.OK, MessageBoxIcon.Error);
errorMsgDB(ex.ToString());
return;
}
//show tables fields
if (lstTables.SelectedIndices.Count <= 0)
{
return;
}
int intselectedindex = lstTables.SelectedIndices[0];
if (intselectedindex < 0)
{
return;
}
string tblName = lstTables.Text;
gridTblFld.Rows.Clear();
int i = 0;
//show table info
try
{
TableDef myTable = myDB.TableDefs[tblName];
foreach (Field fldExtItem in ((Fields)myTable.Fields))
{
gridTblFld.Rows.Add(1);
gridTblFld.Rows[i].Cells[0].Value = fldExtItem.Name.ToString();
gridTblFld.Rows[i].Cells[1].Value = TableField.decodeFldType(fldExtItem.Type);
gridTblFld.Rows[i].Cells[2].Value = fldExtItem.Size.ToString();
i++;
}
}
catch (Exception ex)
{
//MessageBox.Show("Error reading database! \n" +
// "File path: " + Variables.NewFilePath + "\n" + ex,
// "Error!", MessageBoxButtons.OK, MessageBoxIcon.Error);
errorMsgDB(ex.ToString());
return;
}
}
private void FrmRelations_Load(object sender, EventArgs e)
{
myDB = dbe.OpenDatabase(path);
for (int t = 0; t < myDB.TableDefs.Count; t++)
{
if (myDB.TableDefs[t].Attributes == 0)
{
cmbTablesIndex1.Items.Add(myDB.TableDefs[t].Name);
cmbTablesIndex2.Items.Add(myDB.TableDefs[t].Name);
}
}
myDB.Close();
}
private void frmNewTable_Load(object sender, EventArgs e)
{
dbE = new DBEngine();
try
{
myDB = dbE.OpenDatabase(dbPath);
}
catch (Exception ex)
{
MessageBox.Show("Error openning database! \n" + ex);
this.Close();
}
// datatype combobox
cboDataType.Items.Add("dbInt");
cboDataType.Items.Add("dbLong");
cboDataType.Items.Add("dbText");
cboDataType.Items.Add("dbBoolean");
cboDataType.Items.Add("dbDate");
cboDataType.SelectedIndex = 1;
fliItemList = new List <TableField>();
gridFields.Columns.Add("ColName", "Name");
gridFields.Columns.Add("ColNames", "Type");
gridFields.Columns.Add("ColLength", "Length");
gridFields.Columns.Add("ColPriKey", "Primary-Key");
gridFields.Columns.Add("ColAutoNum", "AutoNumber");
//grpNewTblCreate.Enabled = false;
txtTblName.Focus();
}
private string ChangeAllowBypassKey(string dbPath)
{
var strBuilder = new StringBuilder();
try
{
var dbe = new DBEngine();
var db = dbe.OpenDatabase(dbPath);
Property prop = db.Properties["AllowBypassKey"];
switch (MessageBox.Show("Allow bypass key?", "Allow bypass key?", MessageBoxButton.YesNo, MessageBoxImage.Question, MessageBoxResult.No))
{
case MessageBoxResult.Yes:
prop.Value = true;
strBuilder.AppendLine("Property 'AllowBypassKey' is set to 'True'.");
strBuilder.AppendLine("You can access the design (developer) mode by keep pressing SHIFT key while opening the file.");
break;
case MessageBoxResult.No:
prop.Value = false;
strBuilder.AppendLine("Property 'AllowBypassKey' is set to 'False'.");
strBuilder.AppendLine("You can no longer use the SHIFT key to enter the design mode.");
break;
}
return(ListProperties(db, strBuilder));
}
catch (Exception)
{
throw;
}
}
private void frmOpenDB_Load(object sender, EventArgs e)
{
this.Text = "Database: " + _dbPath;
dbe = new DBEngine();
try
{
myDB = dbe.OpenDatabase(_dbPath);
}
catch (Exception ex)
{
MessageBox.Show("Error openning database! \n" + ex.Message
+ "\n" + ex.HResult.ToString());
try
{
myDB.Close();
}
catch (Exception exx)
{
MessageBox.Show("Error openning database! \n" + exx.Message
+ "\n" + exx.HResult.ToString());
}
this.Close();
return;
}
showTablesAndRelations();
//creating columnd for a grid view to display tables fields:
gridOpenTblView.Columns.Add("Names", "Name");
gridOpenTblView.Columns.Add("Types", "Data Type");
gridOpenTblView.Columns.Add("Sizes", "Size");
}
//Статические члены
//Выполнить запрос
public static void Execute(string file, //путь к файлу базы данных
string stSql, //строка запроса
object options = null) //опции запроса
{
if (file.IsEmpty() || stSql.IsEmpty())
{
throw new NullReferenceException("Файл базы данных и строка запроса не могут быть пустыми или null");
}
var en = new DBEngine();
var db = en.OpenDatabase(file);
try
{
if (options == null)
{
db.Execute(stSql);
}
else
{
db.Execute(stSql, options);
}
}
finally
{
try { db.Close(); } catch { }
db = null;
en = null;
GC.Collect();
}
}
private static void FixField(TableDef td, Field fData, Field fTemplate, string mdbPath)
{
var newType = "";
switch (fTemplate.Type)
{
case (short)DataTypeEnum.dbInteger:
newType = "SMALLINT";
break;
case (short)DataTypeEnum.dbLong:
newType = "INTEGER";
break;
case (short)DataTypeEnum.dbText:
newType = "TEXT";
break;
case (short)DataTypeEnum.dbDouble:
newType = "DOUBLE";
break;
}
var dbe = new DBEngine();
var dbData = dbe.OpenDatabase(mdbPath);
var sql = $"ALTER TABLE {td.Name} ALTER COLUMN {fData.Name} {newType}";
dbData.Execute(sql);
dbData.Close();
dbData = null;
}
public List <string> GetAccessTableFields(string filepath, string tableName)
{
if (!string.IsNullOrWhiteSpace(filepath))
{
try
{
var list = new List <string>();
var dbe = new DBEngine();
var db = dbe.OpenDatabase(filepath, false, false, "");
var table = db.TableDefs[tableName];
foreach (dao.Field wField in table.Fields)
{
list.Add(wField.Name);
}
db.Close();
db = null;
dbe = null;
return(list);
}
catch
{
return(new List <string>());
}
}
else
{
return(new List <string>());
}
}
//Update Photo in DB
private void insertPicture()
{
if (fileName != "")
{
//code from http://stackoverflow.com/questions/779211/programmatically-managing-microsoft-access-attachment-typed-field-with-net
DBEngine dbe = new DBEngine();
Database db = dbe.OpenDatabase("PineSpringsPottery.accdb", false, false, "");
Recordset rs = db.OpenRecordset("SELECT * FROM PATTERN WHERE PATTERN.PatternNo = " + currentPattern.patternNo, RecordsetTypeEnum.dbOpenDynaset, 0, LockTypeEnum.dbOptimistic);
rs.MoveFirst();
rs.Edit();
Recordset2 rs2 = (Recordset2)rs.Fields["PatternPicture"].Value;
//delete previous pics
if (rs2.RecordCount != 0)
{
rs2.Delete();
}
//if not just deleting previous, add new
if (fileName != "delete")
{
rs2.AddNew();
Field2 f2 = (Field2)rs2.Fields["FileData"];
f2.LoadFromFile(fileName);
rs2._30_Update();
}
rs2.Close();
rs._30_Update();
rs.Close();
}
}
public static void MakeVesselTypeTable()
{
var dbe = new DBEngine();
var dbData = dbe.OpenDatabase(global.MDBPath);
try
{
dbData.TableDefs.Delete("temp_VesselType");
}
catch { }
string sql = "SELECT 1 AS VesselTypeNo, 'Motorized' AS VesselType into temp_VesselType";
var qd = dbData.CreateQueryDef("", sql);
qd.Execute();
sql = "Insert into temp_VesselType (VesselTypeNo, VesselType) values (2,'Non-Motorized')";
qd = dbData.CreateQueryDef("", sql);
qd.Execute();
sql = "Insert into temp_VesselType (VesselTypeNo, VesselType) values (3,'No vessel used')";
qd = dbData.CreateQueryDef("", sql);
qd.Execute();
sql = "Insert into temp_VesselType (VesselTypeNo, VesselType) values (4,'Not provided')";
qd = dbData.CreateQueryDef("", sql);
qd.Execute();
qd.Close();
qd = null;
dbData.Close();
dbData = null;
}
private static void ChangeAllowBypassKey(string dbPath, bool propValue)
{
try
{
var dbe = new DBEngine();
var db = dbe.OpenDatabase(dbPath);
Property prop = db.Properties["AllowBypassKey"];
prop.Value = propValue;
if (propValue)
{
Console.WriteLine("Property 'AllowBypassKey' is set to 'True'.");
Console.WriteLine("You can access the design (developer) mode by keep pressing SHIFT key while opening the file.");
}
else
{
Console.WriteLine("Property 'AllowBypassKey' is set to 'False'.");
Console.WriteLine("You can no longer use the SHIFT key to enter the design mode.");
}
}
catch (Exception e)
{
if (e.Message.Contains("SSMA"))
{
var url = @"https://www.microsoft.com/en-us/download/details.aspx?id=10910";
Console.WriteLine($"The following error is thrown: {e.Message}.{Environment.NewLine}Please download and install the MS Access runtime (32 bit or 64 bit).{Environment.NewLine}URL: {url}");
}
else
{
Console.WriteLine($"The following error is thrown: {e.Message}");
}
}
}
/// <summary>
/// Inspect database and create point table if not found
/// </summary>
/// <param name="FileName"></param>
/// <returns></returns>
private static bool CheckandCreateTable(string fileName)
{
var dbe = new DBEngine();
var tableFound = false;
if (File.Exists(fileName))
{
var dbData = dbe.OpenDatabase(fileName);
foreach (TableDef td in dbData.TableDefs)
{
if (td.Name == "tblGrid25Inland")
{
tableFound = true;
break;
}
}
if (!tableFound)
{
tableFound = CreateInlandGridTable(dbData);
}
}
if (!tableFound)
{
var dbData = dbe.CreateDatabase(fileName, LanguageConstants.dbLangGeneral);
tableFound = CreateInlandGridTable(dbData);
}
return(tableFound);
}
public List <string> GetAccessQueryList(string filepath)
{
try
{
var list = new List <string>();
var dbe = new DBEngine();
var db = dbe.OpenDatabase(filepath, false, false, "");
foreach (dao.QueryDef queryDef in db.QueryDefs)
{
if (queryDef.Name.Length >= 4 && queryDef.Name.Substring(0, 4).ToLower() == "msys")
{
continue;
}
if (queryDef.Connect.Length > 0)
{
continue;
}
list.Add(queryDef.Name);
}
db.Close();
db = null;
dbe = null;
return(list);
}
catch
{
return(new List <string>());
}
}
private void Executar()
{
var dbe = new DBEngine();
_db = dbe.OpenDatabase(_caminho);
if (hasProperty(_db, "AllowByPassKey"))
{
_db.Properties.Delete("AllowByPassKey");
}
var prop = _db.CreateProperty("AllowByPassKey", 1, _valor);
try
{
_db.Properties.Append(prop);
}
catch (Exception ex)
{
//Acontece em arquivos .mdb. Há uma falha no metodo "hasProperty", que nao identifica a propriedade
//"AllowByPassKey", apesar de existir.
if (ex.Message == "Cannot append. An object with that name already exists in the collection.")
{
_db.Properties.Delete("AllowByPassKey");
_db.Properties.Append(prop);
}
else
{
throw;
}
}
}
private void tleOpen_Click(object sender, EventArgs e)
{
if (lblPath.Text == ".")
{
MetroMessageBox.Show(this, "Please select a valid folder and file name!", "Create Database", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
DBEngine dbe = new DBEngine();
myDB = dbe.OpenDatabase(lblPath.Text);
switch (bound)
{
case 1:
frmTable ft = new frmTable(myDB);
ft.MdiParent = this.ParentForm;
ft.Show();
break;
case 2:
frmRelations fr = new frmRelations(myDB);
fr.MdiParent = this.ParentForm;
fr.Show();
break;
case 3:
frmView fv = new frmView(myDB);
fv.MdiParent = this.ParentForm;
fv.Show();
break;
}
this.Close();
}
private void FrmAddRecord_Load(object sender, EventArgs e)
{
DBEngine Dbe = new DBEngine();
MyDB = Dbe.OpenDatabase(FrmMain.GetPath);
OpenDB.DisplayTables(CBOTables, MyDB);
}
public static bool Diagnose(string mdbPath, string productVersion)
{
var dbe = new DBEngine();
var dbTemplate = dbe.OpenDatabase(global.ApplicationPath + "\\template.mdb");
var dbData = dbe.OpenDatabase(mdbPath);
var count = 0;
var os = Environment.OSVersion;
Logger.LogSimple("");
Logger.Log("start FADDiagnostics");
Logger.LogSimple($"OS: {Registry.GetValue(@"HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows NT\CurrentVersion", "ProductName", "")} {os.Version.Major}.{os.Version.Minor} {os.ServicePack}");
Logger.LogSimple($"FAD version: {productVersion}");
Logger.LogSimple($"Application path: {GetAppPath()}");
Logger.LogSimple($"Logged in user: {Environment.UserName}");
Logger.LogSimple($"Machine name: {Environment.MachineName}");
Logger.LogSimple($"RAM: {((int)(new ComputerInfo().TotalPhysicalMemory / (Math.Pow(1024, 3)) + 0.5)).ToString()} GB");
Logger.LogSimple($"CPU: {GetProcessor()}");
Logger.LogSimple("");
Logger.LogSimple("------------------------------------------------------------");
Logger.LogSimple("Database tables");
Logger.LogSimple($"Row\t{string.Format("{0,-40}", "Template table")}\tData table");
Logger.LogSimple("------------------------------------------------------------");
count = 0;
foreach (TableDef tdTemplate in dbTemplate.TableDefs)
{
if (tdTemplate.Name.Substring(0, 4) != "MSys" && tdTemplate.Name.Substring(0, 5) != "temp_")
{
count++;
Logger.LogSimpleEx($"{count}\t{string.Format("{0,-40}", tdTemplate.Name)}");
foreach (TableDef tdData in dbData.TableDefs)
{
if (tdData.Name == tdTemplate.Name)
{
Logger.LogSimpleEx("\tx\r\n");
break;
}
}
}
}
Logger.LogSimple("------------------------------------------------------------");
Logger.LogSimple("");
Logger.Log("end FADDiagnostics");
return(count > 0);
}
private void CreateRelations_Load(object sender, EventArgs e)
{
DBEngine Dbe = new DBEngine();
MyDB = Dbe.OpenDatabase(FrmMain.GetPath);
ShowTable(CBOParentTable);
ShowTable(CBOForeignTable);
}
private void OnFormLoad(object sender, EventArgs e)
{
global.LoadFormSettings(this, true);
group2.Visible = false;
Size = new Size(group1.Width + (group1.Left * 5), Height);
_dbe = new DBEngine();
_dbData = _dbe.OpenDatabase(global.MDBPath);
}
public void RunDao()
{
var en = new DBEngine();
var db = en.OpenDatabase("Db.accdb");
db.Close();
MessageBox.Show("DB OK");
}
private void ButDao_Click(object sender, EventArgs e)
{
var en = new DBEngine();
var db = en.OpenDatabase("Db.accdb");
db.Close();
MessageBox.Show("DB OK");
}
private Database CreateTable()
{
MyDB = Dbe.OpenDatabase(FrmMain.GetPath);
string Tablename = txtTableName.Text;
MyTb = MyDB.CreateTableDef(Tablename);
MessageBox.Show("The table " + txtTableName.Text + " has been created!", "Successful creation", MessageBoxButtons.OK, MessageBoxIcon.Information);
txtTableName.Enabled = false;
return MyDB;
}
private void OpenDB_Load(object sender, EventArgs e)
{
Dbe = new DBEngine();
MyDB = Dbe.OpenDatabase(FrmMain.GetPath);
DisplayTables(CBOTables, MyDB);
GridTable.Columns.Add("colFields", "Fields");
GridTable.Columns.Add("colSize", "Size");
GridTable.Columns.Add("colType", "Type");
}
private void frmRelations_Load(object sender, EventArgs e)
{
this.Text = "Database: " + dbPath;
dbE = new DBEngine();
try
{
myDB = dbE.OpenDatabase(dbPath);
}
catch (Exception ex)
{
MessageBox.Show("Error openning database! \n" + ex);
return;
}
//pre-loading comboboxes with table names
try
{
foreach (TableDef myTable in myDB.TableDefs)
{
if (myTable.Attributes == 0)
{
cboParentTbl.Items.Add(myTable.Name);
cboChildTbl.Items.Add(myTable.Name);
}
}
}
catch (Exception ex)
{
MessageBox.Show("Error reading database! \n" + ex);
return;
}
gridRelations.Columns.Add("RelName", "Relation_Name");
gridRelations.Columns.Add("ParentTable", "Parent_Table");
gridRelations.Columns.Add("ChildTable", "Child_Table");
int i = 0;
try
{
foreach (Relation myRel in myDB.Relations)
{
if (myRel.Attributes == 0)
{
foreach (Field myFl in myRel.Fields)
{
gridRelations.Rows.Add(1);
gridRelations.Rows[i].Cells[0].Value = myRel.Name.ToString();
gridRelations.Rows[i].Cells[1].Value = myRel.Table.ToString();
gridRelations.Rows[i].Cells[2].Value = myRel.ForeignTable.ToString();
}
}
}
}
catch (Exception ex)
{
MessageBox.Show("Error reading database relations! \n" + ex);
return;
}
}
private void BtnCreateTB_Click(object sender, EventArgs e)
{
CreateTB Ctb = new CreateTB();
Ctb.ShowDialog();
MyDB.Close();
MyDB = Dbe.OpenDatabase(FrmMain.GetPath);
CBOTables.Items.Clear();
DisplayTables(CBOTables, MyDB);
}
public static void ConnectDatabase()
{
if (File.Exists(Properties.Settings.Default.PathToDatabase))
{
_dbe = new DBEngine();
try { _db = _dbe.OpenDatabase(Properties.Settings.Default.PathToDatabase, false, false); }
catch { throw; }
}
else
throw new FileNotFoundException("База данных по указанному пути не найдена");
}
private void FrmDisplayRelations_Load(object sender, EventArgs e)
{
DBEngine Dbe = new DBEngine();
Database MyDB = Dbe.OpenDatabase(FrmMain.GetPath);
GridRelations.Columns.Add("ColParentTable", "Parent Table");
GridRelations.Columns.Add("ColPrimary Key", "Primary Key");
GridRelations.Columns.Add("ColForeign Table", "Foreign Table");
GridRelations.Columns.Add("ColForeign Key", "Foreign Key");
DisplayRel(MyDB, GridRelations);
}
//Проверяет, что файл file является файлом типа fileType (в SysSubTab параметр FileOptions\FileType)
//Также производится проверка на содержание в файле списка указанных таблиц tables
//Возвращает true, если проверка прошла удачно
public static bool Check(string file, string fileType, IEnumerable <string> tables = null)
{
try
{
if (file.IsEmpty() || !new FileInfo(file).Exists)
{
return(false);
}
if (!fileType.IsEmpty())
{
using (var daodb = new DaoDb(file))
using (var sys = new SysTabl(daodb))
if (sys.SubValue("FileOptions", "FileType") != fileType)
{
return(false);
}
}
var en = new DBEngine();
var db = en.OpenDatabase(file);
try
{
var missing = new SortedSet <string>();
if (tables != null)
{
foreach (var table in tables)
{
missing.Add(table);
}
foreach (var t in db.TableDefs)
{
string s = ((TableDef)t).Name;
if (missing.Contains(s))
{
missing.Remove(s);
}
}
}
return(missing.Count == 0);
}
finally
{
try
{
try { db.Close(); } catch { }
db = null;
en = null;
GC.Collect();
}
catch { }
}
}
catch { return(false); }
}
//Установить соединение по Ado или Dao
public DaoDb ConnectDao()
{
if (Database == null)
{
if (!new FileInfo(File).Exists)
{
throw new FileNotFoundException("Файл базы данных не найден", File);
}
Engine = new DBEngine();
Database = Engine.OpenDatabase(File);
}
return(this);
}