internal static List <FProbRelationBLL> getAllRelation()
{
//"SELECT * FROM SystemRelation", "system_relation")
List <FProbRelationBLL> relations = new List <FProbRelationBLL>();
DataBase db = new DataBase();
DataSet dts = new DataSet();
dts.Tables.Add(db.GetDataTable("SELECT * FROM SystemRelation", "system_relation"));
try
{
foreach (DataRow row in dts.Tables["system_relation"].Rows)
{
string relationname = row[1].ToString();
int schemeID = Convert.ToInt16(row[2]);
FProbSchemaBLL schemeName = new FProbSchemaBLL(schemeID).getSchemeById();
List <FProbTupleBLL> probTuples = new List <FProbTupleBLL>();
int nTriples = schemeName.FproAttributes.Count;
probTuples = new FProbTupleBLL().getAllTypleByRelationName(relationname, nTriples);
FProbRelationBLL relation = new FProbRelationBLL(Convert.ToInt16(row[0]), relationname, probTuples, schemeName);
relations.Add(relation);
}
return(relations);
}
catch
{
return(null);
}
}
private static bool DropDatabaseData()
{
try
{
List <FProbRelationBLL> relations = new List <FProbRelationBLL>();
relations = new FProbRelationBLL().getAllRelation();
foreach (FProbRelationBLL item in relations)
{
item.DropTableByTableName();
}
FProbSchemaBLL probScheme = new FProbSchemaBLL();
probScheme.DeleteAllScheme();
FProbRelationBLL probRelation = new FProbRelationBLL();
probRelation.DeleteAllRelation();
FProbAttributeBLL probAttribute = new FProbAttributeBLL();
probAttribute.DeleteAllAttribute();
FProbQueryBLL probQuery = new FProbQueryBLL();
probQuery.DeleteAllQuery();
}
catch (Exception EX)
{
MessageBox.Show(EX.Message);
return(false);
}
return(true);
}
internal static void DropTableByTableName(FProbRelationBLL probRelation)
{
DataBase db = new DataBase();
if (!db.DropTable(probRelation.RelationName))
{
throw new Exception(db.errorMessage);
}
}
internal static void DeleteRelationById(FProbRelationBLL probRelation)
{
DataBase db = new DataBase();
if (!db.Update("DELETE FROM SystemRelation where ID = " + probRelation.IdRelation))
{
throw new Exception(db.errorMessage);
}
}
private void btSaveRenameRelation_Click(object sender, EventArgs e)
{
errorProvider.SetError(txtRenameRelation, null);
if (txtRenameRelation.Text.Trim().Length <= 0)
{
errorProvider.SetError(txtRenameRelation, "You did not enter a relation name");
return;
}
if (txtRenameRelation.Text.ToLower() == "select" || txtRenameRelation.Text.ToLower() == "from" || txtRenameRelation.Text.ToLower() == "where")
{
errorProvider.SetError(txtRenameRelation, "Relation name is not valid ( not match with keyword 'select', 'from', 'where') ");
return;
}
if (this.CurrentNameRelation == txtRenameRelation.Text.Trim().ToLower())
{
return;
}
if (this.CurrentNameRelation != txtRenameRelation.Text.Trim().ToLower())
{
foreach (var item in this.probDatabase.ListOfRelationNameToLower())
{
if (item.Equals(txtRenameRelation.Text.ToLower()))
{
errorProvider.SetError(txtRenameRelation, "This relation name has already existed in the database ");
return;
}
}
}
FProbRelationBLL relation = this.probDatabase.FproRelations.SingleOrDefault(c => c.RelationName.ToLower() == CurrentNameRelation.ToLower());
this.probDatabase.FproRelations.Remove(relation);
relation.DropTableByTableName();
relation.DeleteRelationById();
relation.RelationName = txtRenameRelation.Text.Trim();
relation.InsertSystemRelation();
relation.CreateTableRelation();
relation.InsertTupleIntoTableRelation();
this.probDatabase.FproRelations.Add(relation);
XtraMessageBox.Show("Rename relation successful", "Notification", MessageBoxButtons.OK, MessageBoxIcon.Information);
this.Close();
}
private void btSaveCreateNewRel_Click(object sender, EventArgs e)
{
try
{
errorProvider.SetError(txtRelationName, null);
if (txtRelationName.Text.Trim().Length <= 0)
{
errorProvider.SetError(txtRelationName, "You must enter a relation name, please try again !");
return;
}
if (txtRelationName.Text.ToLower() == "select" || txtRelationName.Text.ToLower() == "from" || txtRelationName.Text.ToLower() == "where")
{
errorProvider.SetError(txtRelationName, "Relation name is not valid ( not match with keyword 'select', 'from', 'where') ");
return;
}
foreach (var item in this.probDatabase.ListOfRelationNameToLower())
{
if (item.Equals(txtRelationName.Text.ToLower(), StringComparison.OrdinalIgnoreCase))
{
errorProvider.SetError(txtRelationName, "This relation name has already existed in the database, please try again !");
return;
}
}
if (XtraMessageBox.Show("Do you want save?", "Question", MessageBoxButtons.YesNo, MessageBoxIcon.Question) == System.Windows.Forms.DialogResult.Yes)
{
FProbSchemaBLL scheme = this.probDatabase.FproSchemas.SingleOrDefault(c => c.SchemaName.ToLower() == ComboBox_RelationName.Properties.Items[ComboBox_RelationName.SelectedIndex].ToString());
FProbRelationBLL relation = new FProbRelationBLL();
relation.RelationName = txtRelationName.Text;
relation.FproSchema = scheme;
relation.InsertSystemRelation();
relation.CreateTableRelation();
this.probDatabase.FproRelations.Add(relation);
XtraMessageBox.Show("Add successfully");
this.Close();
}
}
catch (Exception EX)
{
XtraMessageBox.Show(EX.Message);
}
}
internal static void CreateTableRelation(FProbRelationBLL probRelation)
{
if (probRelation.FproSchema.FproAttributes.Count > 0)
{
DataBase db = new DataBase();
string SQL = "";
SQL += "CREATE TABLE " + probRelation.RelationName + " ( ";
foreach (FProbAttributeBLL attribute in probRelation.FproSchema.FproAttributes)
{
SQL += attribute.AttributeName + " " + "TEXT" + ", ";
}
SQL = SQL.Remove(SQL.LastIndexOf(','), 1);
SQL += " ); ";
if (!db.CreateTable(SQL))
{
throw new Exception(db.errorMessage);
}
}
}
internal static void InsertSystemRelation(FProbRelationBLL probRelation)
{
if (probRelation.IdRelation == -1)
{
probRelation.IdRelation = new DataBase().GetMaxIdInTable("SystemRelation");
}
DataBase db = new DataBase();
string SQL = "";
SQL = "";
SQL += "INSERT INTO SystemRelation VALUES ( ";
SQL += probRelation.IdRelation + ",";
SQL += "'" + probRelation.RelationName + "'" + ",";
SQL += probRelation.FproSchema.IdSchema;
SQL += " );";
if (!db.Update(SQL))
{
throw new Exception(db.errorMessage);
}
}
private void btOkDelRelation_Click(object sender, EventArgs e)
{
if (comboBoxDelRelation.SelectedIndex != -1)
{
FProbRelationBLL relation = this.probDatabase.FproRelations.SingleOrDefault(c => c.RelationName.ToLower() == comboBoxDelRelation.Properties.Items[comboBoxDelRelation.SelectedIndex].ToString());
if (XtraMessageBox.Show("Are you sure delete this relation?", "Delete Relation" + relation.RelationName, MessageBoxButtons.YesNo, MessageBoxIcon.Question) == System.Windows.Forms.DialogResult.Yes)
{
this.probDatabase.FproRelations.Remove(relation);
relation.DropTableByTableName();
relation.DeleteRelationById();
relationNameRemove = relation.RelationName;
XtraMessageBox.Show(" Delete successfully!", "Infomation ", MessageBoxButtons.OK, MessageBoxIcon.Information);
this.Close();
}
}
else
{
this.Close();
}
}
internal static FProbDatabaseBLL OpenExistingDatabase(FProbDatabaseBLL probDatabase)
{
FProbDatabaseBLL newProbDatabase = new FProbDatabaseBLL(probDatabase);
try
{
List <FProbSchemaBLL> Schemes = new List <FProbSchemaBLL>();
Schemes = new FProbSchemaBLL().getAllScheme();
newProbDatabase.FproSchemas = Schemes;
List <FProbRelationBLL> relations = new List <FProbRelationBLL>();
relations = new FProbRelationBLL().getAllRelation();
newProbDatabase.FproRelations = relations;
List <FProbQueryBLL> querys = new List <FProbQueryBLL>();
querys = new FProbQueryBLL().getAllQuery();
newProbDatabase.FproQueries = querys;
}
catch (Exception)
{
return(null);
}
return(newProbDatabase);
}
internal static void InsertTupleIntoTableRelation(FProbRelationBLL probRelation)
{
DataBase db = new DataBase();
if (probRelation.FproTuples.Count > 0)
{
foreach (FProbTupleBLL tuple in probRelation.FproTuples)
{
string SQL = "";
SQL += "INSERT INTO " + probRelation.RelationName + " VALUES (";
foreach (FProbTripleBLL triple in tuple.FproTriples)
{
SQL += "'" + triple.GetStrValue() + "'" + ",";
}
SQL = SQL.Remove(SQL.LastIndexOf(','), 1);
SQL += " ); ";
if (!db.Update(SQL))
{
throw new Exception(db.errorMessage);
}
}
}
}
private void btSaveNewSchema_Click(object sender, EventArgs e)
{
dxErrorProviderNewSchema.SetError(txtSchemeName, null);
if (txtSchemeName.Text.Trim().Length <= 0)
{
dxErrorProviderNewSchema.SetError(txtSchemeName, "You must enter a schema name, please try again!");
return;
}
dxErrorProviderNewSchema.SetError(txtSchemeName, null);
if (txtSchemeName.Text.ToLower() == "select" || txtSchemeName.Text.ToLower() == "from" || txtSchemeName.Text.ToLower() == "where")
{
dxErrorProviderNewSchema.SetError(txtSchemeName, "Schema name is not valid ( not match with keyword 'select', 'from', 'where') ");
return;
}
foreach (var item in this.probDatabase.ListOfSchemeNameToLower())
{
if (item.Equals(txtSchemeName.Text.ToLower()))
{
dxErrorProviderNewSchema.SetError(txtSchemeName, "This schema name has already existed in the database, please try again !");
return;
}
}
dxErrorProviderNewSchema.SetError(txtSchemeName, null);
if (gridViewNewSchema.DataRowCount < 1)
{
XtraMessageBox.Show("Error: Unable to create Schema. Schema attribute is required !", "Notification", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
//insert scheme
FProbSchemaBLL scheme = new FProbSchemaBLL(txtSchemeName.Text);
scheme.IdSchema = scheme.getMaxIdinTable();
scheme.Insert();
scheme.FproAttributes = getAllAttributeFromDataGridView(gridViewNewSchema);
//insert attribute
int attributeID = new FProbAttributeBLL().getMaxIdinTable();
foreach (FProbAttributeBLL attr in scheme.FproAttributes)
{
attr.FproSchema = scheme;
attr.IdAttribute = attributeID;
attr.Insert();
attributeID++;
}
/// add scheme
this.listProbScheme.Add(scheme);
FProbRelationBLL relation = new FProbRelationBLL();
relation.RelationName = txtSchemeName.Text;
relation.FproSchema = scheme;
relation.InsertSystemRelation();
relation.CreateTableRelation();
this.probDatabase.FproRelations.Add(relation);
XtraMessageBox.Show("Add successfully");
this.Close();
}