public IList <PRelation> getAllRelation()
{
//"SELECT * FROM SystemRelation", "system_relation")
var relations = new List <PRelation>();
DataSet dts = new DataSet();
try
{
dts.Tables.Add(ConcreteDb.Instance.getDataTable("SELECT * FROM SystemRelation", "system_relation"));
foreach (DataRow row in dts.Tables["system_relation"].Rows)
{
string relationname = row[1].ToString();
PSchema schema = SchemaService.Instance().getSchemeById(Convert.ToInt16(row[2]));
var tuple = PTupleService.Instance().getAllTupleByRelationName(relationname, schema.Attributes);
if (tuple is null)
{
tuple = new List <PTuple>();
}
PRelation relation = new PRelation(Convert.ToInt16(row[0]), schema, relationname, tuple);
relations.Add(relation);
}
}
catch
{
}
return(relations);
}
public PSchema Delete(PSchema pSchema)
{
if (!checkDelSch(pSchema))
{
throw new Exception("The Schema have some relation!");
}
using (var conn = ConcreteDb.Instance.BeginTransaction())
{
try
{
//del attr
AttributeService.Instance().DeleteAllAttributeByScheme(pSchema, false);
if (ConcreteDb.Instance.Update("DELETE FROM SystemScheme Where ID = " + pSchema.id, false) < 0)
{
throw new Exception(ConcreteDb.Instance.errorMessage);
}
conn.Commit();
}
catch (Exception ex)
{
conn.Rollback();
throw new Exception(String.Format("Cannot delete Schema {0} : {1}", pSchema.SchemaName, ex.Message));
}
ConcreteDb.Instance.closeConnection();
}
return(pSchema);
}
public bool DeleteAllAttributeByScheme(PSchema pSchema, bool con)
{
if (ConcreteDb.Instance.Update(String.Format("DELETE FROM SystemAttribute Where SchemeID = {0}", pSchema.id), con) < 0)
{
throw new Exception(ConcreteDb.Instance.errorMessage);
}
return(true);
}
private bool checkDelSch(PSchema pSchema)
{
var listRel = RawDatabaseService.Instance().getRelByIdSch(pSchema);
if (listRel.Count == 0 || listRel is null)
{
return(true);
}
return(false);
}
public PSchema Update(PSchema pSchema)
{
string SQL = "";
SQL += "Update SystemScheme SET ";
SQL += " SchemeName = '" + pSchema.SchemaName + "'";
SQL += " Where ID = '" + pSchema.id + "'";
if (ConcreteDb.Instance.Update(SQL) < 0)
{
throw new Exception("Failed to Update Schema, plz try again!");
}
return(pSchema);
}
public PSchema getSchemeById(int id)
{
PSchema newSchema = new PSchema();
var dts = new DataSet();
dts.Tables.Add(ConcreteDb.Instance.getDataTable("SELECT * FROM SystemScheme where ID = " + id, "system_scheme"));
foreach (DataRow row in dts.Tables["system_scheme"].Rows)
{
IList <PAttribute> attributes = AttributeService.Instance().getListAttributeByScheme(new PSchema(Convert.ToInt16(row[0])));
newSchema = new PSchema(Convert.ToInt16(row[0]), row[1].ToString(), attributes);
}
return(newSchema);
}
public PSchema Insert(PSchema pSchema) // chi add tren pDatabase
{
try
{
string SQL = "";
SQL += "INSERT INTO SystemScheme VALUES (";
SQL += pSchema.id + ",";
SQL += "'" + pSchema.SchemaName + "'";
SQL += " );";
if (ConcreteDb.Instance.Update(SQL) == 0)
{
throw new Exception(ConcreteDb.Instance.errorMessage);
}
return(pSchema);
}
catch
{
return(null);
}
}
public IList <PAttribute> getListAttributeByScheme(PSchema pSchema)
{
IList <PAttribute> probAttributes = new List <PAttribute>();
DataTable dtb = ConcreteDb.Instance.getDataTable("SELECT * FROM SystemAttribute Where SchemeID = " + pSchema.id);
if (dtb != null)
{
foreach (DataRow attrRow in dtb.Rows)
{
var NewAttr = new PAttribute();
NewAttr.id = Convert.ToInt32(attrRow[0]);
NewAttr.primaryKey = Convert.ToBoolean(attrRow[1]);
//NewAttr.AttributeName = String.Format("{0}.{1}", pSchema.SchemaName, Convert.ToString(attrRow[2]).ToLower());
NewAttr.AttributeName = Convert.ToString(attrRow[2]).ToLower();
NewAttr.Type.TypeName = Convert.ToString(attrRow[3]);
NewAttr.Type.GetDomain(Convert.ToString(attrRow[4]));
NewAttr.Type.GetDataType();
NewAttr.Description = Convert.ToString(attrRow[5]);
NewAttr.Schema.id = (int)attrRow[6];
probAttributes.Add(NewAttr);
}
}
return(probAttributes);
}
public IList <PRelation> getRelByIdSch(PSchema pSchema)
{
var relation = RelationService.Instance().getAllRelation().Where(p => p.schema.id == pSchema.id).ToList();
return(relation);
}
public PSchema Delete(PSchema pSchema)
{
return(SchemaService.Instance().Delete(pSchema));
}
public PSchema Update(PSchema pSchema)
{
return(SchemaService.Instance().Update(pSchema));
}
public PSchema Insert(PSchema pSchema)
{
return(SchemaService.Instance().Insert(pSchema));
}
private void btnSave_Click(object sender, RoutedEventArgs e)
{
if (this.cbxSch.SelectedItem is null)
{
MessageBox.Show("Please choose any item of Combobox", "Notification", MessageBoxButton.OK, MessageBoxImage.Information);
return;
}
try
{
if (this.txtRelName.Text.Trim().Length <= 0)
{
MessageBox.Show("You must enter a relation name, please try again !", "Error", MessageBoxButton.OK, MessageBoxImage.Error);
return;
}
if (this.txtRelName.Text.ToLower() == "select" || this.txtRelName.Text.ToLower() == "from" || this.txtRelName.Text.ToLower() == "where")
{
MessageBox.Show("Relation name is not valid ( not match with keyword 'select', 'from', 'where')", "Error", MessageBoxButton.OK, MessageBoxImage.Error);
return;
}
foreach (var item in StaticParams.currentDb.Relations.ToList())
{
if (item.relationName.Equals(this.txtRelName.Text.ToLower(), StringComparison.OrdinalIgnoreCase))
{
MessageBox.Show("This relation name has already existed in the database, please try again !", "Error", MessageBoxButton.OK, MessageBoxImage.Error);
return;
}
}
PSchema schema = StaticParams.currentDb.Schemas.SingleOrDefault(c => c == this.cbxSch.SelectedItem);
var relation = new PRelation();
relation.relationName = this.txtRelName.Text.ToLower();
relation.id = RawDatabaseService.Instance().getNextIdRl();
relation.schema = schema;
try
{
//in db
RawDatabaseService.Instance().Insert(relation);
//in ram
StaticParams.currentDb.Relations.Add(relation);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "Error", MessageBoxButton.OK, MessageBoxImage.Error);
}
if (MessageBox.Show("Add successfully.Do you want add a new relation name ?", "Question", MessageBoxButton.YesNo, MessageBoxImage.Question) == MessageBoxResult.Yes)
{
this.txtRelName.Focus();
this.txtRelName.Text = null;
this.Window_Loaded(sender, e);
}
else
{
this.Close();
}
}
catch (Exception EX)
{
MessageBox.Show(EX.Message);
}
}
private void btnSave_Click(object sender, RoutedEventArgs e)
{
var attLs = new List <PAttribute>();
if (String.IsNullOrEmpty(this.txtSchName.Text.ToString()))
{
MessageBox.Show("You must enter a schema name, please try again!", "Error", MessageBoxButton.OK, MessageBoxImage.Error);
return;
}
if ("select".Equals(this.txtSchName.Text.ToLower(), StringComparison.CurrentCultureIgnoreCase) ||
"from".Equals(this.txtSchName.Text.ToLower(), StringComparison.CurrentCultureIgnoreCase) ||
"where".Equals(this.txtSchName.Text.ToLower(), StringComparison.CurrentCultureIgnoreCase))
{
MessageBox.Show("Schema name is not valid ( not match with keyword 'select', 'from', 'where')", "Error", MessageBoxButton.OK, MessageBoxImage.Error);
return;
}
foreach (var item in StaticParams.currentDb.Schemas.ToList())
{
if (item.SchemaName.ToLower().Equals(this.txtSchName.Text.ToLower(), StringComparison.CurrentCultureIgnoreCase))
{
MessageBox.Show("This schema name has already existed in the database, please try again !", "Error", MessageBoxButton.OK, MessageBoxImage.Error);
return;
}
}
if (!CheckValidatedDataGridView(attrs))
{
return;
}
//create schema
PSchema schema = new PSchema();
schema.id = RawDatabaseService.Instance().getNextIdSch(); //important
schema.SchemaName = this.txtSchName.Text.Trim();
PAttribute attribute;
//insert Schema and relative component in Db
try
{
#region addAtrr
foreach (SchemaModelView attr in attrs.ToList())
{
attribute = new PAttribute();
attribute.id = RawDatabaseService.Instance().getNextIdAttr();
attribute.AttributeName = attr.attrName;
attribute.primaryKey = attr.isPri;
attribute.Type = new Domain.Unit.PDataType()
{
DomainString = attr.domain, DataType = attr.datatype, TypeName = attr.typeName
};
attribute.Description = attr.descs;
attribute.Schema = schema;
RawDatabaseService.Instance().Insert(attribute); //=>csdl need id schema
//update full info schema
schema.Attributes.Add(attribute);
}
//Ps
var datatype = new PDataType();
datatype.TypeName = "String";
datatype.GetDomain("[0 ... 32767] characters");
datatype.GetDataType();
var attrPs = new PAttribute()
{
AttributeName = "Ps",
id = RawDatabaseService.Instance().getNextIdAttr(),
Schema = schema,
primaryKey = false,
Description = "Prob",
Type = datatype
};
RawDatabaseService.Instance().Insert(attrPs);
schema.Attributes.Add(attrPs);
#endregion addAtrr
//insert scheme in db
RawDatabaseService.Instance().Insert(schema);
//insert scheme in ram
StaticParams.currentDb.Schemas.Add(schema);
//commit
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "Error", MessageBoxButton.OK, MessageBoxImage.Error);
}
if (MessageBox.Show("Add successfully. Do you want add new schema ?", "Question", MessageBoxButton.YesNo, MessageBoxImage.Question) == MessageBoxResult.Yes)
{
this.txtSchName.Text = "";
this.attrs.Clear();
this.dtg.ItemsSource = attrs;
this.dtg.Items.Refresh();
this.txtSchName.Focus();
}
else
{
this.Close();
}
}
