public bool IsValidAssociation(BCSField sourceField) { if (sourceField.ReferencedEntity == null) { return false; } if (sourceField.IsKey) { //keys cannot be part of an foreignkey //return false; } //check if the referenced entity and the referenced field is in the model foreach (BCSEntity entity in this.Entities) { if (entity.Name == sourceField.ReferencedEntity) { foreach (BCSField field in entity.Fields) { if (field.Name == sourceField.ReferencedField) { //found return true; } } } } return false; }
public bool IsValidAssociation(BCSField sourceField) { if (sourceField.ReferencedEntity == null) { return(false); } if (sourceField.IsKey) { //keys cannot be part of an foreignkey //return false; } //check if the referenced entity and the referenced field is in the model foreach (BCSEntity entity in this.Entities) { if (entity.Name == sourceField.ReferencedEntity) { foreach (BCSField field in entity.Fields) { if (field.Name == sourceField.ReferencedField) { //found return(true); } } } } return(false); }
private void AddField(BCSEntity entity, string name, string display, Type datatype, bool isKey, bool isIdentity, bool isRequired) { BCSField field = new BCSField(); field.Name = name; field.DisplayName = display; field.DataType = datatype; field.IsIdentity = isIdentity; field.IsKey = isKey; field.IsRequired = isRequired; entity.Fields.Add(field); }
private void SetAssocationName(BCSField field, BCSEntity entity) { field.AssociationName = field.ReferencedField + "Of" + field.ReferencedEntity + "To" + field.Name + "Of" + entity.Name; field.AssociationDisplayName = field.ReferencedEntity + " to " + entity.Name + "(" + field.Name + ")"; }
public void LoadData(string server, string database) { if (IsDesignMode) { return; } if ((server == lastServer) && (database == lastDatabase)) { //same server, do nothing return; } lastServer = server; lastDatabase = database; string connString = "Provider=SQLOLEDB;Data Source=" + server + ";Initial Catalog=" + database + ";Integrated Security=SSPI;"; treeView1.Nodes.Clear(); string datatypes = ""; using (OleDbConnection conn = new OleDbConnection(connString)) { try { // open the connection to the database conn.Open(); ArrayList arrViews = new ArrayList(); ArrayList arrTables = new ArrayList(); // Get the Tables DataTable SchemaTable = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new Object[] { null, null, null, "TABLE" }); // Store the table names in the class scoped array // list of table names for (int i = 0; i < SchemaTable.Rows.Count; i++) { BCSEntity entity = new BCSEntity(); entity.Name = MakeSafe(SchemaTable.Rows[i].ItemArray[2].ToString()); entity.DisplayName = SchemaTable.Rows[i].ItemArray[2].ToString(); entity.Schema = MakeSafe(SchemaTable.Rows[i].ItemArray[1].ToString()); datatypes += "-------------------------------------------------------" + Environment.NewLine; datatypes += entity.Name + Environment.NewLine; TreeNode tableNode = new TreeNode(); tableNode.Text = entity.Name; tableNode.ImageKey = "Table"; tableNode.SelectedImageKey = "Table"; tableNode.Tag = entity; treeView1.Nodes.Add(tableNode); /////////////// List<string> autoIncs = new List<string>(); OleDbDataAdapter adapter = new OleDbDataAdapter("SELECT SCHEMA_NAME( OBJECTPROPERTY( OBJECT_ID, 'SCHEMAID' )) AS SCHEMA_NAME, OBJECT_NAME( OBJECT_ID ) AS TABLE_NAME, NAME AS COLUMN_NAME FROM SYS.COLUMNS WHERE COLUMNPROPERTY(OBJECT_ID, NAME, 'IsIdentity') = 1 AND SCHEMA_NAME( OBJECTPROPERTY( OBJECT_ID, 'SCHEMAID' )) = '" + entity.Schema + "' AND OBJECT_NAME( OBJECT_ID ) = '" + entity.Name + "'", conn); DataTable tableSchema = new DataTable(); adapter.Fill(tableSchema); foreach (DataRow dr in tableSchema.Rows) { autoIncs.Add(MakeSafe(dr["COLUMN_NAME"].ToString())); } DataTable mySchema = (conn as OleDbConnection). GetOleDbSchemaTable(OleDbSchemaGuid.Primary_Keys, new Object[] { null, null, SchemaTable.Rows[i].ItemArray[2].ToString() }); int columnOrdinalForName = mySchema.Columns["COLUMN_NAME"].Ordinal; List<string> keys = new List<string>(); foreach (DataRow r in mySchema.Rows) { keys.Add(MakeSafe(r.ItemArray[columnOrdinalForName].ToString())); } DataTable dtField = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Columns, new object[] { null, null, SchemaTable.Rows[i].ItemArray[2].ToString() }); foreach (DataRow dr in dtField.Rows) { foreach (DataColumn columnName in dtField.Columns) { datatypes += dr[columnName].ToString() + "\t"; } BCSField field = new BCSField(); field.Name = MakeSafe(dr["COLUMN_NAME"].ToString()); if (field.Name.StartsWith("Timestamp")) { } field.OleDbType = ((OleDbType)dr[11]).ToString(); field.DbType = ConvertToDbType((OleDbType)dr[11], dr["CHARACTER_MAXIMUM_LENGTH"].ToString()); field.DataType = ConvertType((OleDbType)dr[11]); field.DisplayName = dr["COLUMN_NAME"].ToString(); field.DataTypeSize = dr["CHARACTER_MAXIMUM_LENGTH"].ToString(); field.IsNullAllowed = Boolean.Parse(dr["IS_NULLABLE"].ToString()); if (autoIncs.Contains(field.Name)) { field.IsIdentity = true; } if (keys.Contains(field.Name)) { field.IsKey = true; } //exception: "Date" in SQL kommt als WChar bzw NChar(10) an if (field.DbType == "NChar(10)") { field.DataType = typeof(DateTime); } datatypes += field.Name + "\t" + field.OleDbType + "\t" + field.DbType + "\t" + field.DataType + Environment.NewLine; string columnNodeText = dr["COLUMN_NAME"].ToString(); //append type columnNodeText += " (" + ((OleDbType)dr[11]).ToString(); if (!field.IsNullAllowed) { columnNodeText += ", NOT NULL"; } columnNodeText += ")"; TreeNode columnNode = new TreeNode(); columnNode.Tag = field; UpdateNodeText(columnNode); tableNode.Nodes.Add(columnNode); } //get all assocations (where fields in this entity references other entities) DataTable foreigns = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Foreign_Keys, new object[] { null, null, null, null, null, entity.Name }); foreach (DataRow foreign in foreigns.Rows) { //stay in same database if (foreign["FK_TABLE_CATALOG"].ToString() == foreign["PK_TABLE_CATALOG"].ToString()) { string fieldInCurrentEntity = foreign["FK_COLUMN_NAME"].ToString(); string referencedEntityName = foreign["PK_TABLE_NAME"].ToString(); string refrencedEntityField = foreign["PK_COLUMN_NAME"].ToString(); foreach (TreeNode fieldNode in tableNode.Nodes) { BCSField field = fieldNode.Tag as BCSField; if (field.Name == fieldInCurrentEntity) { field.ReferencedEntity = referencedEntityName; field.ReferencedField = refrencedEntityField; SetAssocationName(field, entity); fieldNode.ImageKey = "Foreign"; fieldNode.SelectedImageKey = "Foreign"; } } } } } // Get the Views SchemaTable = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new Object[] { null, null, null, "VIEW" }); // Store the view names in the class scoped array // list of view names for (int i = 0; i < SchemaTable.Rows.Count; i++) { arrViews.Add(SchemaTable.Rows[i]. ItemArray[2].ToString()); } } catch (Exception ex) { // break and notify if the connection fails MessageBox.Show(ex.Message, "Connection Error"); } } string a = datatypes; string b = a; }
private void toolStripButtonAddField_Click(object sender, EventArgs e) { if (treeView1.SelectedNode != null) { if (treeView1.SelectedNode.Tag is BCSEntity) { TreeNode tableNode = treeView1.SelectedNode; BCSField field = new BCSField(); int index = tableNode.Nodes.Count + 1; field.Name = "Field" + index.ToString(); field.DisplayName = "Field " + index.ToString(); field.OleDbType = null; field.DbType = null; field.DataType = typeof(String); field.DataTypeSize = null; field.IsNullAllowed = false; field.IsIdentity = false; field.IsKey = false; TreeNode columnNode = new TreeNode(); columnNode.Text = field.Name; columnNode.ImageKey = "Field"; columnNode.SelectedImageKey = "Field"; if (field.IsKey) { columnNode.ImageKey = "Key"; columnNode.SelectedImageKey = "Key"; } columnNode.Tag = field; tableNode.Nodes.Add(columnNode); treeView1.SelectedNode = columnNode; } } UpdateToolbar(); UpdateResult(); }