private static void CreateDBIndex(DBTable table, DBTable.Index index)
{
DebugLog.Add("Altering table " + table.Name + " adding index '" + index.Name + "'...");
string createSQL = "CREATE";
if (index.Unique == true)
{
createSQL += " UNIQUE";
}
if (index.Clustered == true)
{
createSQL += " CLUSTERED";
}
createSQL += " INDEX [" + index.Name + "] ON [" + table.Name + "] (" + Environment.NewLine;
foreach (KeyValuePair <string, bool> idxColumn in index.Columns)
{
createSQL += "\t[" + idxColumn.Key + "]";
if (idxColumn.Value == true)
{
createSQL += " DESC";
}
createSQL += "," + Environment.NewLine;
}
createSQL = createSQL.TrimEnd(new char[] { ',', '\r', '\n' });
createSQL += ") ;" + Environment.NewLine;
SqlCommand createQuery = new SqlCommand(createSQL, DatabaseConnection);
createQuery.ExecuteNonQuery();
}
private static void populateXMLIndex(XmlReader reader, DBTable table)
{
string idxName = reader.GetAttribute("name");
bool idxClustered = Boolean.Parse(reader.GetAttribute("clustered").ToString());
bool idxUnique = Boolean.Parse(reader.GetAttribute("unique").ToString());
reader.ReadStartElement();
DBTable.Index idx = table.AddIndex(idxName, idxClustered, idxUnique);
while ((reader.NodeType == XmlNodeType.Element) && (reader.Name == "column"))
{
String idxColumnName = reader.GetAttribute("name");
bool idxColumnDesc = Boolean.Parse(reader.GetAttribute("desc").ToString());
idx.AddIndexColumn(idxColumnName, idxColumnDesc);
reader.ReadStartElement();
if ((reader.NodeType == XmlNodeType.EndElement) && (reader.Name == "column"))
{
reader.ReadEndElement();
}
}
reader.ReadEndElement();
}
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);
}