private static DBSchema populateXMLSchema(string schemaXMLFile)
{
DBSchema xmlSchema = new DBSchema();
XmlReaderSettings settings = new XmlReaderSettings();
settings.IgnoreWhitespace = true;
XmlReader reader = XmlReader.Create(schemaXMLFile, settings);
reader.MoveToContent();
reader.ReadStartElement("tables");
while ((reader.NodeType == XmlNodeType.Element) && (reader.Name == "table"))
{
DBTable table = xmlSchema.AddTable(reader.GetAttribute("name"));
reader.ReadStartElement();
while (reader.NodeType == XmlNodeType.Element)
{
switch (reader.Name)
{
case "column":
populateXMLColumn(reader, table);
break;
case "primary_key":
populateXMLPrimaryKey(reader, table);
break;
case "index":
populateXMLIndex(reader, table);
break;
case "foreign_key":
populateXMLForeignKey(reader, table);
break;
case "default_record":
populateXMLDefaultRecord(reader, table);
break;
}
}
reader.ReadEndElement();
}
reader.ReadEndElement();
reader.Close();
return(xmlSchema);
}
private static DBSchema populateDBSchema()
{
DBSchema dbSchema = new DBSchema();
List <String> tables = new List <string>();
DebugLog.Add("Reading Database Tables...");
SqlCommand tablesSQL = new SqlCommand("exec sp_tables", DatabaseConnection);
SqlDataReader tablesReader = tablesSQL.ExecuteReader();
while (tablesReader.Read())
{
string tableName = tablesReader["TABLE_NAME"].ToString();
string tableOwner = tablesReader["TABLE_OWNER"].ToString();
string tableType = tablesReader["TABLE_TYPE"].ToString();
if ((tableOwner == "dbo") && (tableType == "TABLE") &&
(tableName != "dtproperties"))
{
tables.Add(tableName);
}
}
tablesReader.Close();
DebugLog.Add("Found " + tables.Count.ToString() + " Tables...");
foreach (string tableName in tables)
{
DebugLog.Add("Populate DBSchema for table '" + tableName + "'...");
DBTable table = dbSchema.AddTable(tableName);
DebugLog.Add("Getting Columns for table '" + tableName + "'...");
SqlCommand columnsSQL = new SqlCommand("exec sp_columns [" + tableName + "]", DatabaseConnection);
SqlDataReader columnsReader = columnsSQL.ExecuteReader();
while (columnsReader.Read())
{
string columnName = columnsReader["COLUMN_NAME"].ToString();
string columnType = columnsReader["TYPE_NAME"].ToString();
int columnSize = Int32.Parse(columnsReader["LENGTH"].ToString());
string columnDefault = columnsReader["COLUMN_DEF"].ToString();
int columnNullable = Int32.Parse(columnsReader["NULLABLE"].ToString());
string columnOptions = String.Empty;
if (columnNullable == 0)
{
columnOptions = "NOT NULL";
}
else
{
columnOptions = "NULL";
}
if (columnDefault != String.Empty)
{
columnOptions += " DEFAULT " + columnDefault;
}
if (columnType.ToLower() == "varchar")
{
columnType += "(" + columnSize.ToString() + ")";
}
table.AddColumn(columnName, columnType, columnOptions);
}
columnsReader.Close();
DebugLog.Add("Getting Primary Keys for table '" + tableName + "'...");
Dictionary <string, List <string> > pkeys = new Dictionary <string, List <string> >();
SqlCommand pkeySQL = new SqlCommand("exec sp_pkeys [" + tableName + "]", DatabaseConnection);
SqlDataReader pkeysReader = pkeySQL.ExecuteReader();
while (pkeysReader.Read())
{
string keyName = pkeysReader["PK_NAME"].ToString();
string keyColumnName = pkeysReader["COLUMN_NAME"].ToString();
if (pkeys.ContainsKey(keyName) == false)
{
pkeys.Add(keyName, new List <string>());
}
pkeys[keyName].Add(keyColumnName);
}
pkeysReader.Close();
foreach (string keyName in pkeys.Keys)
{
table.PrimaryKey.Name = keyName;
foreach (string keyColumn in pkeys[keyName])
{
table.PrimaryKey.AddKeyColumn(keyColumn);
}
}
Version sqlVer = new Version(DatabaseConnection.ServerVersion);
if (sqlVer.Major > 8)
{
string selectIndexesSQL = "SELECT idx.name AS idx_name,idx.type_desc,idx.is_unique,cols.name AS col_name,ixc.is_descending_key";
selectIndexesSQL += " FROM sys.indexes idx";
selectIndexesSQL += " JOIN sys.index_columns ixc on (idx.index_id = ixc.index_id)";
selectIndexesSQL += " JOIN sys.columns cols on (ixc.column_id = cols.column_id)";
selectIndexesSQL += " WHERE idx.object_id = OBJECT_ID(@table)";
selectIndexesSQL += " AND ixc.object_id = idx.object_id";
selectIndexesSQL += " AND cols.object_id = idx.object_id";
selectIndexesSQL += " AND idx.is_primary_key = 0";
DebugLog.Add("Getting Indexes for table '" + tableName + "'...");
SqlCommand indexesSQL = new SqlCommand(selectIndexesSQL, DatabaseConnection);
indexesSQL.Parameters.AddWithValue("@table", tableName);
SqlDataReader indexesReader = indexesSQL.ExecuteReader();
while (indexesReader.Read())
{
string idxName = indexesReader["idx_name"].ToString();
string idxType = indexesReader["type_desc"].ToString();
bool idxUnique = Boolean.Parse(indexesReader["is_unique"].ToString());
string idxColName = indexesReader["col_name"].ToString();
bool idxColDesc = Boolean.Parse(indexesReader["is_descending_key"].ToString());
DBTable.Index idx = table.FindIndex(idxName);
if (idx == null)
{
idx = table.AddIndex(idxName, (idxType == "CLUSTERED"), idxUnique);
}
idx.AddIndexColumn(idxColName, idxColDesc);
}
indexesReader.Close();
}
DebugLog.Add("Getting Foreign Keys for table '" + tableName + "'...");
SqlCommand fkeySQL = new SqlCommand("exec sp_fkeys @fktable_name=[" + tableName + "]", DatabaseConnection);
SqlDataReader fkeysReader = fkeySQL.ExecuteReader();
while (fkeysReader.Read())
{
string keyName = fkeysReader["FK_NAME"].ToString();
string keyColumnName = fkeysReader["FKCOLUMN_NAME"].ToString();
string pkeyTableName = fkeysReader["PKTABLE_NAME"].ToString();
string pkeyColumnName = fkeysReader["PKCOLUMN_NAME"].ToString();
table.AddForeignKey(keyName, keyColumnName, pkeyTableName, pkeyColumnName);
}
fkeysReader.Close();
DebugLog.Add("DB Schema generated for table '" + tableName + "'");
}
return(dbSchema);
}
