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