internal static List <FProbQueryBLL> getAllQuery()
{
List <FProbQueryBLL> probQuerys = new List <FProbQueryBLL>();
DataBase db = new DataBase();
DataSet dts = dts = new DataSet();
dts.Tables.Add(db.GetDataTable("SELECT * FROM SystemQuery", "system_query"));
foreach (DataRow queryRow in dts.Tables["system_query"].Rows)
{
FProbQueryBLL NewQuery = new FProbQueryBLL();
NewQuery.IdQuery = Convert.ToInt16(queryRow[0].ToString());
NewQuery.QueryName = queryRow[1].ToString();
if (queryRow[2].ToString() != "Empty")
{
NewQuery.QueryString = queryRow[2].ToString();
}
else
{
NewQuery.QueryString = "";
}
probQuerys.Add(NewQuery);
}
return(probQuerys);
}
internal static void Insert(FProbQueryBLL probQuery)
{
if (probQuery.IdQuery == -1)
{
probQuery.IdQuery = new DataBase().GetMaxIdInTable("SystemQuery");
}
DataBase db = new DataBase();
string SQL = "";
if (probQuery.QueryString == "")
{
probQuery.QueryString = "Empty";
}
SQL += "INSERT INTO SystemQuery VALUES (";
SQL += probQuery.IdQuery + ",";
SQL += "'" + probQuery.QueryName + "'" + ",";
SQL += "'" + probQuery.QueryString + "'";
SQL += " );";
if (!db.Update(SQL))
{
throw new Exception(db.errorMessage);
}
}
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 btSaveRenameQuery_Click(object sender, EventArgs e)
{
errorProvider.SetError(txtRenameQuery, null);
if (txtRenameQuery.Text.Trim().Length <= 0)
{
errorProvider.SetError(txtRenameQuery, "You did not enter a query name");
return;
}
foreach (string item in this.listNameQuery)
{
if (item == txtRenameQuery.Text.Trim().ToLower() && txtRenameQuery.Text.Trim() != this.listNameQuery[ComboBox_RenameQuery.SelectedIndex])
{
errorProvider.SetError(txtRenameQuery, "This query name has already existed in the database");
return;
}
}
FProbQueryBLL query = this.probDatabase.FproQueries.SingleOrDefault(c => c.QueryName == this.listNameQuery[ComboBox_RenameQuery.SelectedIndex]);
QueryNameRename = query.QueryName;
query.QueryName = txtRenameQuery.Text.Trim().ToLower();
query.Update();
queryName = query.QueryName;
XtraMessageBox.Show("Rename query successfully", "Notification", MessageBoxButtons.OK, MessageBoxIcon.Information);
this.Close();
}
internal static void DeleteById(FProbQueryBLL probQuery)
{
DataBase db = new DataBase();
if (!db.Update("DELETE FROM SystemQuery where ID = " + probQuery.IdQuery))
{
throw new Exception(db.errorMessage);
}
}
internal static void Update(FProbQueryBLL probQuery)
{
string SQL = "";
SQL += "Update SystemQuery SET ";
SQL += " QueryName = '" + probQuery.QueryName + "'";
SQL += " , QueryString = '" + probQuery.QueryString.Replace("'", "''") + "'";
SQL += " Where ID = " + probQuery.IdQuery;
new DataBase().Update(SQL);
}
private void btOkDelQuery_Click(object sender, EventArgs e)
{
DialogResult result = new DialogResult();
result = XtraMessageBox.Show("Are you sure want to delete this query?", "Delete Queries", MessageBoxButtons.YesNo);
if (result == DialogResult.Yes)
{
string selectName = comboBoxDelQuery.Properties.Items[comboBoxDelQuery.SelectedIndex].ToString();
FProbQueryBLL query = this.probDatabase.FproQueries.SingleOrDefault(c => c.QueryName.ToLower() == selectName);
query.DeleteById();
QueryNameRemove = query.QueryName;
this.probDatabase.FproQueries.Remove(query);
XtraMessageBox.Show(" Delete successfully!", "Infomation ", MessageBoxButtons.OK, MessageBoxIcon.Information);
this.Close();
}
}
internal static FProbQueryBLL getQueryByName(FProbQueryBLL probQuery)
{
DataBase db = new DataBase();
DataSet dts = dts = new DataSet();
dts.Tables.Add(db.GetDataTable("SELECT * FROM SystemQuery where QueryName = '" + probQuery.QueryName + "'", "system_query"));
FProbQueryBLL NewQuery = new FProbQueryBLL();
foreach (DataRow queryRow in dts.Tables["system_query"].Rows)
{
NewQuery = new FProbQueryBLL();
NewQuery.IdQuery = Convert.ToInt16(queryRow[0].ToString());
NewQuery.QueryName = queryRow[1].ToString();
NewQuery.QueryString = queryRow[2].ToString();
}
return(NewQuery);
}
private void btOkNewQuery_Click(object sender, EventArgs e)
{
errorProvider.SetError(txtQueryName, null);
if (txtQueryName.Text.Trim().Length < 0)
{
errorProvider.SetError(txtQueryName, "You must enter a query name, please try again !");
return;
}
foreach (string item in this.probDatabase.ListOfQueryNameToLower())
{
if (item == txtQueryName.Text.ToLower())
{
errorProvider.SetError(txtQueryName, "The query name has already existed in the database, please try again !");
return;
}
}
this.queryName = txtQueryName.Text.Trim().ToLower();
FProbQueryBLL query = new FProbQueryBLL(this.queryName);
query.Insert();
query = query.getQueryByName();
this.probDatabase.FproQueries.Add(query);
if (XtraMessageBox.Show("Add successfully. Do you want add a new query name ?", "Question", MessageBoxButtons.YesNo, MessageBoxIcon.Question) == System.Windows.Forms.DialogResult.Yes)
{
txtQueryName.Text = "";
txtQueryName.Focus();
}
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);
}
