public DataTable GetViewData(string connectionString, ViewSchema view)
{
string sqlstring = @"
SELECT
*
FROM
" + view.Name;
ADODB.Connection cn = new ADODB.ConnectionClass();
cn.Open(connectionString, null, null, 0);
ADODB.Recordset rs = new ADODB.RecordsetClass();
rs.Open(sqlstring, cn, ADODB.CursorTypeEnum.adOpenForwardOnly, ADODB.LockTypeEnum.adLockReadOnly, 0);
OleDbDataAdapter dataAdapter = new OleDbDataAdapter();
DataTable viewData = new DataTable();
dataAdapter.Fill(viewData, rs);
rs.Close();
cn.Close();
return(viewData);
}
private dbdefinitionEntity GenerateEntity(ADOX.Table ptbl, ADODB.Connection pcon)
{
dbdefinitionEntity entity = new dbdefinitionEntity();
entity.name = ptbl.Name;
entity.description = GetTableDescription(ptbl, pcon);
// Generate attributes
ArrayList alAttributes = new ArrayList();
int intColIndex = 0;
ADODB.Recordset rs = new ADODB.RecordsetClass();
rs.Open("SELECT * FROM [" + ptbl.Name + "] WHERE (3=4)", pcon, ADODB.CursorTypeEnum.adOpenStatic, ADODB.LockTypeEnum.adLockReadOnly, 0);
foreach (ADODB.Field fld in rs.Fields)
{
alAttributes.Add(GenerateColumn(ptbl.Columns[fld.Name], intColIndex));
intColIndex++;
}
rs.Close();
if (alAttributes.Count != 0)
{
entity.attributes = (dbdefinitionEntityAttribute[])alAttributes.ToArray(typeof(dbdefinitionEntityAttribute));
}
// Generate primary/unique keys/constraints
entity.keys = new dbdefinitionEntityKeys();
ArrayList alUniqueKeys = new ArrayList();
ArrayList alForeignKeys = new ArrayList();
int intIndex = 0;
foreach (ADOX.Key key in ptbl.Keys)
{
switch (key.Type)
{
case ADOX.KeyTypeEnum.adKeyPrimary:
entity.keys.primarykey = new dbdefinitionEntityKeysPrimarykey();
entity.keys.primarykey.name = ptbl.Name + "_pk";
// get all primary key columns
ArrayList aAttr = new ArrayList();
foreach (ADOX.Column col in key.Columns)
{
dbdefinitionEntityKeysPrimarykeyAttributeref attr = new dbdefinitionEntityKeysPrimarykeyAttributeref();
attr.attribute = col.Name;
aAttr.Add(attr);
}
// create the primary key attribute array
entity.keys.primarykey.attributeref = (dbdefinitionEntityKeysPrimarykeyAttributeref [])
aAttr.ToArray(typeof(dbdefinitionEntityKeysPrimarykeyAttributeref));
break;
case ADOX.KeyTypeEnum.adKeyUnique:
dbdefinitionEntityKeysUniquekey uniquekey = new dbdefinitionEntityKeysUniquekey();
ArrayList alAttributeRefs = new ArrayList();
foreach (ADOX.Column col in key.Columns)
{
dbdefinitionEntityKeysUniquekeyAttributeref attributeref = new dbdefinitionEntityKeysUniquekeyAttributeref();
attributeref.attribute = col.Name;
alAttributeRefs.Add(attributeref);
}
if (alAttributeRefs.Count != 0)
{
uniquekey.attributeref = (dbdefinitionEntityKeysUniquekeyAttributeref[])alAttributeRefs.ToArray(typeof(dbdefinitionEntityKeysUniquekeyAttributeref));
}
// check for duplicate indexes - ignore duplicate index defintions
bool isDuplicate = false;
foreach (dbdefinitionEntityKeysUniquekey existingKey in alUniqueKeys)
{
if (uniquekey.attributeref.Length == existingKey.attributeref.Length)
{
isDuplicate = true;
for (int i = 0; i < uniquekey.attributeref.Length; i++)
{
if (!uniquekey.attributeref[i].attribute.Equals(existingKey.attributeref[i].attribute))
{
isDuplicate = false;
break;
}
}
if (isDuplicate)
{
break;
}
}
}
if (!isDuplicate)
{
uniquekey.name = ptbl.Name + "_uk" + intIndex;
alUniqueKeys.Add(uniquekey);
}
break;
case ADOX.KeyTypeEnum.adKeyForeign:
dbdefinitionEntityKeysForeignkey foreignkey = new dbdefinitionEntityKeysForeignkey();
foreignkey.name = ptbl.Name + "_fk" + intIndex;
foreignkey.foreignentity = key.RelatedTable;
foreignkey.cascadingdelete = (key.DeleteRule & ADOX.RuleEnum.adRICascade) != 0;
foreignkey.attributeref = new dbdefinitionEntityKeysForeignkeyAttributeref();
foreignkey.attributeref.attribute = key.Columns[0].Name;
foreignkey.attributeref.foreignattribute = key.Columns[0].RelatedColumn;
alForeignKeys.Add(foreignkey);
break;
}
intIndex++;
}
if (alUniqueKeys.Count != 0)
{
entity.keys.uniquekey = (dbdefinitionEntityKeysUniquekey[])alUniqueKeys.ToArray(typeof(dbdefinitionEntityKeysUniquekey));
}
if (alForeignKeys.Count != 0)
{
entity.keys.foreignkey = (dbdefinitionEntityKeysForeignkey[])alForeignKeys.ToArray(typeof(dbdefinitionEntityKeysForeignkey));
}
// Generate indices
ArrayList alIndexes = new ArrayList();
intIndex = 0;
foreach (ADOX.Index idx in ptbl.Indexes)
{
if (!idx.PrimaryKey)
{
dbdefinitionEntityIndex index = new dbdefinitionEntityIndex();
index.unique = idx.Unique;
index.ignorenulls = (idx.IndexNulls == ADOX.AllowNullsEnum.adIndexNullsIgnore);
ArrayList alAttributeRefs = new ArrayList();
foreach (ADOX.Column col in idx.Columns)
{
dbdefinitionEntityIndexAttributeref attributeref = new dbdefinitionEntityIndexAttributeref();
attributeref.attribute = col.Name;
alAttributeRefs.Add(attributeref);
}
index.attributeref = (dbdefinitionEntityIndexAttributeref[])alAttributeRefs.ToArray(typeof(dbdefinitionEntityIndexAttributeref));
// check for duplicate indexes - ignore duplicate index defintions
bool isDuplicate = false;
foreach (dbdefinitionEntityIndex existingIndex in alIndexes)
{
if (index.attributeref.Length == existingIndex.attributeref.Length)
{
isDuplicate = true;
for (int i = 0; i < index.attributeref.Length; i++)
{
if (!index.attributeref[i].attribute.Equals(existingIndex.attributeref[i].attribute))
{
isDuplicate = false;
break;
}
}
if (isDuplicate)
{
break;
}
}
}
if (!isDuplicate)
{
index.name = ptbl.Name + "_idx" + intIndex;
alIndexes.Add(index);
}
intIndex++;
}
}
if (alIndexes.Count != 0)
{
entity.indexes = (dbdefinitionEntityIndex[])alIndexes.ToArray(typeof(dbdefinitionEntityIndex));
}
// mark 1:1 relationship foreign keys with "one-to-one" attribute
if (entity.keys != null && entity.keys.foreignkey != null && entity.indexes != null)
{
foreach (dbdefinitionEntityKeysForeignkey foreignkey in entity.keys.foreignkey)
{
foreach (dbdefinitionEntityIndex index in entity.indexes)
{
if (index.attributeref.Length == 1 && index.attributeref[0].attribute.Equals(foreignkey.attributeref.foreignattribute))
{
if (index.unique)
{
foreignkey.onetoone = true;
}
}
}
}
}
// Generate hashcode of entity definition
XmlSerializer entityDefSerializer = new XmlSerializer(typeof(dbdefinitionEntity));
StringWriter stringWriter = new StringWriter();
entityDefSerializer.Serialize(stringWriter, entity);
String entityDef = stringWriter.ToString();
entity.hashcode = entityDef.GetHashCode().ToString();
return(entity);
}