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 AddEntity(BCSModel bcsModel, string name, string display)
{
BCSEntity entity = new BCSEntity();
entity.Name = name;
entity.DisplayName = display;
entity.CreateCreateOperation = true;
entity.CreateDeleteOperation = true;
entity.CreateReadOperation = true;
entity.CreateUpdateOperation = true;
bcsModel.Entities.Add(entity);
AddField(entity, "Field1", "Field 1", typeof(System.Int32), true, true, false);
AddField(entity, "Field2", "Field 2", typeof(System.String), false, false, false);
AddField(entity, "Field3", "Field 3", typeof(System.String), false, false, true);
}
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 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 + ")";
}
private void toolStripButton1_Click(object sender, EventArgs e)
{
int index = treeView1.Nodes.Count + 1;
BCSEntity entity = new BCSEntity();
entity.Name = "Entity" + index.ToString();
entity.DisplayName = "Entity " + index.ToString();
entity.Schema = "";
TreeNode tableNode = new TreeNode();
tableNode.Text = entity.Name;
tableNode.ImageKey = "Table";
tableNode.SelectedImageKey = "Table";
tableNode.Tag = entity;
treeView1.Nodes.Add(tableNode);
treeView1.SelectedNode = tableNode;
UpdateToolbar();
UpdateResult();
}
