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(); } }