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);
}
private void btSaveEditSchema_Click(object sender, EventArgs e)
{
errorProvider.SetError(txtSchemeName, null);
if (txtSchemeName.Text.Trim().Length <= 0)
{
errorProvider.SetError(txtSchemeName, "You must enter a schema name, please try again!");
return;
}
errorProvider.SetError(txtSchemeName, null);
if (txtSchemeName.Text.ToLower() == "select" || txtSchemeName.Text.ToLower() == "from" || txtSchemeName.Text.ToLower() == "where")
{
errorProvider.SetError(txtSchemeName, "Schema name is not valid (not match with keyword 'select', 'from', 'where') ");
return;
}
if (this.currentSchemeName != txtSchemeName.Text.Trim().ToLower())
{
foreach (var item in this.probDatabase.ListOfSchemeNameToLower())
{
if (item.Equals(txtSchemeName.Text.ToLower()))
{
errorProvider.SetError(txtSchemeName, "This schema name has already existed in the database, please try again !");
return;
}
}
}
errorProvider.SetError(txtSchemeName, null);
if (gridViewEditSchema.DataRowCount < 1)
{
XtraMessageBox.Show("Error: Unable to update Schema. Schema attribute is required !", "Notification", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
currentScheme.SchemaName = txtSchemeName.Text;
currentScheme.Update();
foreach (FProbAttributeBLL attr in currentScheme.FproAttributes)
{
attr.FproSchema = currentScheme;
attr.DeleteAllAttributeByIdScheme();
}
currentScheme.FproAttributes.Clear();
currentScheme.FproAttributes = new FrmNewSchema().getAllAttributeFromDataGridView(this.gridViewEditSchema);
int ID = new FProbAttributeBLL().getMaxIdinTable();
foreach (FProbAttributeBLL attr in currentScheme.FproAttributes)
{
attr.IdAttribute = ID;
attr.FproSchema = currentScheme;
attr.Insert();
ID++;
}
XtraMessageBox.Show("Edit successfully");
this.Close();
}
internal static void DeleteAllAttributeByIdScheme(FProbAttributeBLL probAttribute)
{
DataBase db = new DataBase();
if (!db.Update("DELETE FROM SystemAttribute Where SchemeID = " + probAttribute.FproSchema.IdSchema))
{
throw new Exception(db.errorMessage);
}
}
internal static List <FProbSchemaBLL> getAllScheme()
{
List <FProbSchemaBLL> newSchemes = new List <FProbSchemaBLL>();
DataBase db = new DataBase();
DataSet dts = new DataSet();
dts.Tables.Add(db.GetDataTable("SELECT * FROM SystemScheme", "system_scheme"));
foreach (DataRow row in dts.Tables["system_scheme"].Rows)
{
List <FProbAttributeBLL> attributes = new FProbAttributeBLL().getListAttributeByIDScheme(Convert.ToInt16(row[0]));
newSchemes.Add(new FProbSchemaBLL(Convert.ToInt16(row[0]), row[1].ToString(), attributes));
}
return(newSchemes);
}
internal static void Insert(FProbAttributeBLL probAttribute)
{
DataBase db = new DataBase();
string SQL = "";
SQL += "INSERT INTO SystemAttribute VALUES ( ";
SQL += probAttribute.IdAttribute + ",";
SQL += "'" + probAttribute.PrimaryKey + "'" + ",";
SQL += "'" + probAttribute.AttributeName + "'" + ",";
SQL += "'" + probAttribute.FproDataType.TypeName + "'" + ",";
SQL += "'" + probAttribute.FproDataType.DomainString + "'" + ",";
SQL += "'" + probAttribute.Description + "'" + ",";
SQL += probAttribute.FproSchema.IdSchema;
SQL += " );";
if (!db.Update(SQL))
{
throw new Exception(db.errorMessage);
}
}
internal static List <FProbAttributeBLL> getListAttributeByIDScheme(int IDScheme)
{
List <FProbAttributeBLL> probAttributes = new List <FProbAttributeBLL>();
DataBase db = new DataBase();
DataTable dtb = db.GetDataTable("SELECT * FROM SystemAttribute Where SchemeID=" + IDScheme);
if (dtb != null)
{
foreach (DataRow attrRow in dtb.Rows)
{
FProbAttributeBLL NewAttr = new FProbAttributeBLL();
NewAttr.PrimaryKey = Convert.ToBoolean(attrRow[1]);
NewAttr.AttributeName = Convert.ToString(attrRow[2]);
NewAttr.FproDataType.TypeName = Convert.ToString(attrRow[3]);
NewAttr.FproDataType.GetDomain(Convert.ToString(attrRow[4]));
NewAttr.FproDataType.GetDataType();
NewAttr.Description = Convert.ToString(attrRow[5]);
probAttributes.Add(NewAttr);
}
}
return(probAttributes);
}
public List <FProbAttributeBLL> getAllAttributeFromDataGridView(DevExpress.XtraGrid.Views.Grid.GridView gridViewNewSchema)
{
List <FProbAttributeBLL> probAttributes = new List <FProbAttributeBLL>();
int nRow = gridViewNewSchema.DataRowCount - 1;
for (int i = 0; i <= nRow; i++)
{
FProbAttributeBLL attribute = new FProbAttributeBLL();
if (gridViewNewSchema.GetRowCellValue(i, "gridColPrimaryKey").ToString() == "")
{
gridViewNewSchema.SetRowCellValue(i, "gridColPrimaryKey", false);
}
attribute.PrimaryKey = Convert.ToBoolean(gridViewNewSchema.GetRowCellValue(i, "gridColPrimaryKey"));
attribute.AttributeName = gridViewNewSchema.GetRowCellValue(i, "gridColAttributeName").ToString();
attribute.FproDataType.TypeName = gridViewNewSchema.GetRowCellValue(i, "gridColDataType").ToString();
attribute.FproDataType.GetDomain(gridViewNewSchema.GetRowCellValue(i, "gridColDomain").ToString());
attribute.Description = (gridViewNewSchema.GetRowCellValue(i, "gridColDescription") == null ? "" : gridViewNewSchema.GetRowCellValue(i, "gridColDescription").ToString());
attribute.FproDataType.GetDataType();
probAttributes.Add(attribute);
}
return(probAttributes);
}
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();
}
