private void tleRel_Click(object sender, EventArgs e)
{
if (cboTable1.Text == cboTable2.Text)
{
MetroMessageBox.Show(this, "Invalid selection, can't relate members of the same table!", "Create Relation", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
if (myDB.TableDefs[cboTable1.Text].Fields[txtTbl1.Text].Type != myDB.TableDefs[cboTable2.Text].Fields[txtTbl2.Text].Type)
{
MetroMessageBox.Show(this, "Invalid selection, both elements must be of the same data type!", "Create Relation", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
Relation myRel = myDB.CreateRelation(txtRelation.Text, lblTbl1.Text, lblTbl2.Text);
myFL = myRel.CreateField(txtTbl1.Text);
myFL.ForeignName = txtTbl2.Text;
myRel.Fields.Append(myFL);
myDB.Relations.Append(myRel);
MetroMessageBox.Show(this, "Relation Created with success", "Create Relation", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
txtRelation.Clear();
txtTbl1.Clear();
txtTbl2.Clear();
}
private void btn_addRelation_Click(object sender, EventArgs e)
{
// Checking whether the user has added any field pair to the relation.
if (grd_relationFields.Rows.Count == 0)
{
string message = "Please add at least one field pair to the relation.";
MessageBox.Show(message, this.MdiParent.Text, MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
cbo_leftTableFields.Focus();
return;
}
string relationName = txt_relationName.Text.Trim();
// Checking whether the user provided a name for the relation.
if (String.IsNullOrEmpty(relationName))
{
string message = "Please enter the name of the relation before adding it to the database.";
MessageBox.Show(message, this.MdiParent.Text, MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
txt_relationName.Focus();
return;
}
try
{
string leftTable = cbo_leftTable.Text;
string rightTable = cbo_rightTable.Text;
// Creating the relation.
Relation rel = clsGlobal.myDB.CreateRelation(relationName, leftTable, rightTable);
// Adding the field pairs to the relation.
foreach (DataGridViewRow row in grd_relationFields.Rows)
{
string leftField = row.Cells["col_leftField"].Value.ToString();
string rightField = row.Cells["col_rightField"].Value.ToString();
Field fld = rel.CreateField(leftField);
fld.ForeignName = rightField;
rel.Fields.Append(fld);
}
// Adding the relation to the database.
clsGlobal.myDB.Relations.Append(rel);
}
catch (Exception exc)
{
string message = "The following error occurred while creating and adding the new relation:\n\n";
message += exc.Message;
MessageBox.Show(message, this.MdiParent.Text, MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
MessageBox.Show("The relation has been added successfully.", this.MdiParent.Text, MessageBoxButtons.OK, MessageBoxIcon.Information);
clearFormFields();
}
private void btnCreate_Click(object sender, EventArgs e)
{
//triggers the creation of relationship
bool exist = false;
if (cmbTableUn.Text == "" || cmbTableDeux.Text == "") //check if the tables are chosen
{
MetroMessageBox.Show(this, "Please select the tables.");
}
else if (cmbTableUn.Text == cmbTableDeux.Text) //check if the tables have the same name
{
MetroMessageBox.Show(this, "Tables selected have to be different.");
}
else if (txtRelation.Text == "") //checks if the relation name was given
{
MetroMessageBox.Show(this, "Enter a name before creating a relation.");
}
else if (cmbFieldUn.Text == "" || cmbFieldDeux.Text == "") //check if the fields were chosen
{
MetroMessageBox.Show(this, "Please select the fields for the relation.");
}
else if (clsDataStorage.db.TableDefs[cmbTableUn.Text].Fields[cmbFieldUn.Text].Type
!= clsDataStorage.db.TableDefs[cmbTableDeux.Text].Fields[cmbFieldDeux.Text].Type) //check if fields are of same type
{
MetroMessageBox.Show(this, "Fields must be of the same type.");
}
else
{
foreach (Relation rl in clsDataStorage.db.Relations) //loop through the database
{
if (rl.Name == txtRelation.Text) //if a relationship with given name already exists, return error
{
MetroMessageBox.Show(this, "The relation: " + rl.Name + " already exists.");
exist = true;
}
}
if (!exist)
{ //creates the relationship
Relation rel = clsDataStorage.db.CreateRelation();
rel.Name = txtRelation.Text;
rel.Table = cmbTableUn.Text;
rel.ForeignTable = cmbTableDeux.Text;
Field fd = rel.CreateField();
fd.Name = cmbFieldUn.Text;
fd.ForeignName = cmbFieldDeux.Text;
rel.Fields.Append(fd);
clsDataStorage.db.Relations.Append(rel);
MetroMessageBox.Show(this, "Relation: " + rel.Name + " was successfully created.");
this.Close();
}
}
}
private void bttnRelation_Click(object sender, EventArgs e)
{
myDB = dbe.OpenDatabase(path);
Relation myRel = myDB.CreateRelation(cmbTablesIndex1.Text + cmbTablesIndex2.Text, cmbTablesIndex1.Text, cmbTablesIndex2.Text);
Field myFL = myRel.CreateField(cmbFieldsIndex1.Text);
myFL.ForeignName = cmbFieldsIndex2.Text;
myRel.Fields.Append(myFL);
myDB.Relations.Append(myRel);
MessageBox.Show("The relationship called " + myRel.Name + " was created with success.", "Important Message", MessageBoxButtons.OK, MessageBoxIcon.Information);
myDB.Close();
this.Close();
}
private void buttonCreate_Click(object sender, EventArgs e)
{
bool exist = false;
if (comboBoxTable1.Text == "" || comboBoxTable2.Text == "")
{
MessageBox.Show("Select tables .");
}
else if (comboBoxTable1.Text == comboBoxTable2.Text)
{
MessageBox.Show("You have to select different tables.");
}
else if (textBoxName.Text == "")
{
MessageBox.Show("Enter a name for a relation.");
}
else if (listBoxTable1.SelectedItems.Count != 1 || listBoxTable2.SelectedItems.Count != 1)
{
MessageBox.Show("Select a field for a relation.");
}
else if (frmMain.db.TableDefs[comboBoxTable1.Text].Fields[listBoxTable1.SelectedItem.ToString()].Type != frmMain.db.TableDefs[comboBoxTable2.Text].Fields[listBoxTable2.SelectedItem.ToString()].Type)
{
MessageBox.Show("Fields must be same type.");
}
else
{
foreach (Relation rl in frmMain.db.Relations)
{
if (rl.Name == textBoxName.Text)
{
MessageBox.Show("Relation : " + rl.Name + " is already exist.");
exist = true;
}
}
if (!exist)
{
Relation rel = frmMain.db.CreateRelation();
rel.Name = textBoxName.Text;
rel.Table = comboBoxTable1.Text;
rel.ForeignTable = comboBoxTable2.Text;
Field fd = rel.CreateField();
fd.Name = listBoxTable1.SelectedItem.ToString();
fd.ForeignName = listBoxTable2.SelectedItem.ToString();
rel.Fields.Append(fd);
frmMain.db.Relations.Append(rel);
MessageBox.Show("Relation : " + rel.Name + " successfully created.");
this.Close();
}
}
}
//Function that create the relation
private Database CreateRelation()
{
MyDB.Close();
DBEngine Dbe = new DBEngine();
MyDB = Dbe.OpenDatabase(FrmMain.GetPath);
Field MyFL;
Relation MyRL = MyDB.CreateRelation(txtRelationName.Text, CBOParentTable.SelectedItem, CBOForeignTable.SelectedItem);
MyFL = MyRL.CreateField(CBOPrimaryKey.SelectedItem.ToString());
MyFL.ForeignName = CBOForeignKey.SelectedItem.ToString();
MyRL.Fields.Append(MyFL);
MyDB.Relations.Append(MyRL);
return(MyDB);
}
private void btnCreateRelation_Click(object sender, EventArgs e)
{
if (txtRelName.Text == "")
{
MessageBox.Show("Please enter relation name!", "Relation Name ERROR", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
if (cboPrimaryTable.SelectedIndex == -1)
{
MessageBox.Show("Please choose appropriate origin table", "Origin Table ERROR", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
if (cboPrimaryField.SelectedIndex == -1)
{
MessageBox.Show("Please choose appropriate origin field", "Origin Field ERROR", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
if (cboForeignTable.SelectedIndex == -1)
{
MessageBox.Show("Please choose appropriate foreign table", "Foreign Table ERROR", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
if (cboForeignField.SelectedIndex == -1)
{
MessageBox.Show("Please choose appropriate foreign field", "Foreign Field ERROR", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
Relation myRel = myDB.CreateRelation(txtRelName.Text, cboPrimaryTable.Text, cboForeignTable.Text);
myFL = myRel.CreateField(cboPrimaryField.Text);
myFL.ForeignName = cboForeignField.Text;
myRel.Fields.Append(myFL);
myDB.Relations.Append(myRel);
ListViewItem lvitem = new ListViewItem(txtRelName.Text);
lvitem.SubItems.Add(cboPrimaryField.Text);
lvitem.SubItems.Add(cboForeignField.Text);
lvRelation.Items.Add(lvitem);
MessageBox.Show("Relation created successfully!", "Create Relation", MessageBoxButtons.OK, MessageBoxIcon.Information);
ClearAll();
}
private void metroButtonCreateRelations_Click(object sender, EventArgs e)
{
myDB.Close();
myDB = dbe.OpenDatabase(saveFileDialog1.FileName);
myRel = myDB.CreateRelation(metroComboBoxTabla1.Text + metroComboBoxTabla2.Text, metroComboBoxTabla1.Text, metroComboBoxTabla2.Text);
myFL = myRel.CreateField(metroComboBoxIndex1.Text);
myFL.ForeignName = metroComboBoxIndex2.Text;
myRel.Fields.Append(myFL);
myDB.Relations.Append(myRel);
MessageBox.Show(myRel.Name + " was created with succes", "Important Message", MessageBoxButtons.OK, MessageBoxIcon.Information);
metroComboBoxTabla1.Items.Clear();
metroComboBoxTabla2.Items.Clear();
metroComboBoxIndex1.Items.Clear();
metroComboBoxIndex2.Items.Clear();
myDB.Close();
metroTileHome.Show();
metroTileDisplay.Show();
metroTileExit.Show();
}
Database myDB; //---- create database object
//TableDef myTb; //-----table object
private void btnRelSave_Click(object sender, EventArgs e)
{
//reating a relations in the table:
Field myFl;
string tabParent = cboParentTbl.Text;
string tabChild = cboChildTbl.Text;
string relName = txtRelName.Text.Trim();
if (relName == "")
{
MessageBox.Show("Relatin Name could not be empty! \nPlease provide valid name.",
"Error Relation Ceation", MessageBoxButtons.OK, MessageBoxIcon.Stop);
txtRelName.Focus();
return;
}
try
{
string tabParentFld = lstParentTbl.SelectedItem.ToString();
string tabChldFld = lstChldTbl.SelectedItem.ToString();
Relation myRel = myDB.CreateRelation(relName, tabParent, tabChild);
//myRel.Table = tabParent;
//myRel.ForeignTable = tabChild;
myFl = myRel.CreateField(tabParentFld);
myFl.ForeignName = tabChldFld;
myRel.Fields.Append(myFl);
myDB.Relations.Append(myRel);
MessageBox.Show("Relation creatd! \n Going back...");
}
catch (Exception ex)
{
MessageBox.Show("Error Creating relations!!! \n" + ex.Message,
"Error Relation Ceation", MessageBoxButtons.OK, MessageBoxIcon.Stop);
return;
}
this.Close();
}
public static bool CheckDB(string mdbPath)
{
TableList = new List <string>();
List <string> colList = new List <string>();
var dbe = new DBEngine();
var dbTemplate = dbe.OpenDatabase(global.ApplicationPath + "\\template.mdb");
var dbData = dbe.OpenDatabase(mdbPath);
var tdName = "";
foreach (TableDef td in dbData.TableDefs)
{
if (td.Name.Length > 4 && td.Name.Substring(0, 4) != "MSys" && td.Name.Substring(0, 5) != "temp_")
{
TableList.Add(td.Name);
}
}
foreach (TableDef tdTemplate in dbTemplate.TableDefs)
{
if (tdTemplate.Name.Length > 4 && tdTemplate.Name.Substring(0, 4) != "MSys" && tdTemplate.Name.Substring(0, 5) != "temp_")
{
tdName = tdTemplate.Name;
if (!TableList.Contains(tdTemplate.Name))
{
var sql = $@"SELECT [{tdTemplate.Name}].* INTO
[{tdTemplate.Name}] IN '{dbData.Name}'
FROM [{tdTemplate.Name}]";
var qdf = dbTemplate.CreateQueryDef("", sql);
qdf.Execute();
dbData.TableDefs.Refresh();
qdf.Close();
}
//enumerate all fields in the current data table
foreach (Field f in dbData.TableDefs[tdTemplate.Name].Fields)
{
colList.Add(f.Name);
}
//enumerate all fields in the corresponding template table
foreach (Field fldTemplate in tdTemplate.Fields)
{
//if a template field is not found in the data table field
//we will add this field to the data table
var fldData = new Field();
if (!colList.Contains(fldTemplate.Name))
{
dbData.TableDefs[tdTemplate.Name].With(o =>
{
fldData = o.CreateField(fldTemplate.Name, fldTemplate.Type, fldTemplate.Size);
o.Fields.Append(fldData);
o.Fields.Refresh();
});
}
fldData = dbData.TableDefs[tdTemplate.Name].Fields[fldTemplate.Name];
fldData.AllowZeroLength = fldTemplate.AllowZeroLength;
fldData.Required = fldTemplate.Required;
if (fldData.Type != fldTemplate.Type)
{
FixField(dbData.TableDefs[tdName], fldData, fldTemplate, mdbPath);
}
foreach (Property pTemplate in fldTemplate.Properties)
{
if (pTemplate.Name == "Description")
{
try
{
fldData.Properties["Description"].Value = pTemplate.Value;
}
catch
{
fldData.Properties.Append(fldData.CreateProperty(pTemplate.Name, pTemplate.Type, pTemplate.Value));
}
fldData.Properties.Refresh();
}
}
}
colList.Clear();
//enumerate all indexes in the current data table and put in the list
foreach (Index i in dbData.TableDefs[tdTemplate.Name].Indexes)
{
var name = tdTemplate.Name;
colList.Add(i.Name);
if (tdTemplate.Name == "tblGearInventoryBarangayData" && i.Name == "AltKey")
{
RemoveGearInventoryBarangayDataAltKey(mdbPath);
}
}
//enumerate all indexes in the current template table
foreach (Index templateIndex in tdTemplate.Indexes)
{
//if the current index is not in the list,
//we will make one and add it to the indexes of the data table
var dataIndex = new Index();
if (!colList.Contains(templateIndex.Name))
{
dbData.TableDefs[tdTemplate.Name].With(o =>
{
dataIndex = o.CreateIndex(templateIndex.Name);
dataIndex.Fields = templateIndex.Fields;
dataIndex.Primary = templateIndex.Primary;
dataIndex.Required = templateIndex.Primary;
dataIndex.IgnoreNulls = templateIndex.IgnoreNulls;
dataIndex.Unique = templateIndex.Unique;
try
{
o.Indexes.Append(dataIndex);
}
catch (Exception ex)
{
Logger.Log(ex.Message, "DBCheck.cs", $"CheckDB: Add index {templateIndex.Name} to table { tdTemplate.Name}");
}
o.Indexes.Refresh();
});
}
else
{
foreach (Index i in dbData.TableDefs[tdTemplate.Name].Indexes)
{
if (i.Name == templateIndex.Name)
{
}
}
}
}
}
}
colList.Clear();
foreach (Relation rel in dbData.Relations)
{
colList.Add(rel.Table + "|" + rel.ForeignTable);
}
foreach (Relation templateRel in dbTemplate.Relations)
{
var dataRel = new Relation();
if (!colList.Contains(templateRel.Table + "|" + templateRel.ForeignTable))
{
dataRel = dbData.CreateRelation(templateRel.Name, templateRel.Table, templateRel.ForeignTable);
foreach (Field f in templateRel.Fields)
{
dataRel.Fields.Append(dataRel.CreateField(f.Name));
dataRel.Fields[f.Name].ForeignName = templateRel.Fields[f.Name].ForeignName;
}
try
{
dbData.Relations.Append(dataRel);
}
catch
{
dataRel.Name += "1";
try
{
dbData.Relations.Append(dataRel);
}
catch (Exception ex)
{
if (templateRel.ForeignTable == "tblSampling" && templateRel.Table == "tblFishingExpense")
{
FixSamplingExpenseRelation(mdbPath);
}
Logger.LogError(ex.Message, ex.StackTrace);
}
}
finally
{
dbData.Relations.Refresh();
}
}
}
dbData.Close();
dbTemplate.Close();
dbData = null;
dbTemplate = null;
return(true);
}
