Sort Database Tables By Foreign Keys

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Text;

namespace DbTableSorter
{
	[Flags]
	public enum RefType
	{
		None = 0x00,
		ReferencedBy = 0x01,
		RefAndRefBy = 0x02,
		References = 0x04
	}

	/// <summary>
	/// 
	/// </summary>
	public class TableInfo
	{
		#region Constants

		private const string connStr = "Data Source=.;Initial Catalog=AdventureWorks;Integrated Security=SSPI;Persist Security Info=False";

		#endregion

		#region Data Members

		// Class variables
		public static int MaxLen;
		public static readonly SortedList<string, TableInfo> sortedList = new SortedList<string, TableInfo>();

		// Get DataSet Column Information
		private static DataColumn _colTblTableName;
		private static DataColumn _colRefReferenceTableName;
		//private static DataColumn _colRefReferenceColumnName;
		private static DataColumn _colRefTableName;
		//private static DataColumn _colRefColumnName;
		private static DataRelation _dataRelation;

		// Instance variables
		private List<string> _referencedBy = new List<string>();
		private RefType _refType;
		private string _name;

		#endregion

		#region Properites

		/// <summary>
		/// Get/Set for ReferencedBy
		/// </summary>
		public List<string> ReferencedBy
		{
			get { return (_referencedBy); }
			set { _referencedBy = value; }
		}

		/// <summary>
		/// Get/Set for RefType
		/// </summary>
		public RefType RefType
		{
			get { return (_refType); }
			set { _refType = value; }
		}

		/// <summary>
		/// Get/Set for Name
		/// </summary>
		public string Name
		{
			get { return (_name); }
			set { _name = value; }
		}

		#endregion

		#region Constructor

		/// <summary>
		/// 
		/// </summary>
		/// <param name="row"></param>
		private TableInfo(DataRow row)
		{
			Name = row[_colTblTableName].ToString();
			if (Name.Length > MaxLen)
				MaxLen = Name.Length;

			AddRefType(RefType.None);
		}

		#endregion

		#region Methods - Public

		/// <summary>
		/// 
		/// </summary>
		/// <returns></returns>
		public static List<TableInfo> CreateTableList()
		{
			Console.Write("\n\nStarting Data Retrieval...");

			// Retrieve Tables and Foreign Key Relationships
			DataSet dataSet = GetDatabaseTableInformation();

			// Add All Tables to a List
			foreach (DataRow row in dataSet.Tables["Tables"].Rows)
			{
				TableInfo tableInfo = new TableInfo(row);
				sortedList.Add(tableInfo.Name, tableInfo);
			}

			// Find Reference Table Information For Each Table
			foreach (DataRow row in dataSet.Tables["Tables"].Rows)
			{
				TableInfo tableInfo = sortedList[row[_colTblTableName].ToString()];

				// Get the Reference Tables
				foreach (DataRow childRow in row.GetChildRows(_dataRelation))
				{
					string childTable = childRow[_colRefTableName].ToString();

					if (childTable.Equals(tableInfo.Name))
						continue;

					// Add Child Table to Current Table
					if (!tableInfo.ReferencedBy.Contains(childTable))
					{
						tableInfo.ReferencedBy.Add(childTable);

						// Add Reference Type Enumeration For Both Referenced and Referenced By Tables
						tableInfo.AddRefType(RefType.ReferencedBy);
						sortedList[childTable].AddRefType(RefType.References);
					}
				}

				// Sort Referenced By Tables By Name
				tableInfo.ReferencedBy.Sort();
			}

			Console.WriteLine("Complete\n");

			Console.WriteLine("Starting To Sort...\n\n");

			// Perform the Sort with the Custom Sorter
			List<TableInfo> list = new List<TableInfo>(sortedList.Values);
			list.Sort(new TableInfoComparer());

			return (list);
		}

		#endregion

		#region Methods - Private

		/// <summary>
		/// 
		/// </summary>
		/// <returns></returns>
		private static DataSet GetDatabaseTableInformation()
		{
			DataSet dataSet = new DataSet();

			// Retrieve Table Information
			SqlConnection sqlConnection = new SqlConnection(connStr);
			SqlDataAdapter dataAdapter = new SqlDataAdapter(GetSqlStatementForTables(), sqlConnection);
			dataAdapter.Fill(dataSet, "Tables");
			dataAdapter = new SqlDataAdapter(GetSqlStatementForReferences(), sqlConnection);
			dataAdapter.Fill(dataSet, "Reference");

			// Get DataColumn Information
			_colTblTableName = dataSet.Tables["Tables"].Columns["TableName"];
			_colRefReferenceTableName = dataSet.Tables["Reference"].Columns["ReferenceTableName"];
			//_colRefReferenceColumnName = ds.Tables["Reference"].Columns["ReferenceColumnName"];
			_colRefTableName = dataSet.Tables["Reference"].Columns["TableName"];
			//_colRefColumnName = ds.Tables["Reference"].Columns["ColumnName"];

			// Create Table Relationship
			_dataRelation = new DataRelation("tableRelation", _colTblTableName, _colRefReferenceTableName, true);
			dataSet.Relations.Add(_dataRelation);

			return dataSet;
		}

		/// <summary>
		/// 
		/// </summary>
		/// <param name="referenceType"></param>
		private void AddRefType(RefType referenceType)
		{
			if (_refType == RefType.RefAndRefBy)
				return;

			_refType |= referenceType;

			if (_refType == (RefType.ReferencedBy | RefType.References))
				_refType = RefType.RefAndRefBy;
		}

		/// <summary>
		/// 
		/// </summary>
		/// <returns></returns>
		private static string GetSqlStatementForTables()
		{
			StringBuilder sb = new StringBuilder();

			sb.Append("SELECT TABLE_SCHEMA + \'.\' + TABLE_NAME AS TableName ");
			sb.Append("FROM INFORMATION_SCHEMA.Tables ");
			sb.Append("WHERE TABLE_TYPE=\'BASE TABLE\' ");
			sb.Append("ORDER BY TableName ");

			return (sb.ToString());
		}

		/// <summary>
		/// 
		/// </summary>
		/// <returns></returns>
		private static string GetSqlStatementForReferences()
		{
			StringBuilder sb = new StringBuilder();

			sb.Append("SELECT  ");
			sb.Append("    OBJECT_SCHEMA_NAME (fkey.referenced_object_id) + \'.\' +  ");
			sb.Append("        OBJECT_NAME (fkey.referenced_object_id) AS ReferenceTableName ");
			sb.Append("    ,COL_NAME(fcol.referenced_object_id, fcol.referenced_column_id) AS ReferenceColumnName ");
			sb.Append("    ,OBJECT_SCHEMA_NAME (fkey.parent_object_id) + \'.\' +  ");
			sb.Append("        OBJECT_NAME(fkey.parent_object_id) AS TableName ");
			sb.Append("    ,COL_NAME(fcol.parent_object_id, fcol.parent_column_id) AS ColumnName ");
			sb.Append("FROM sys.foreign_keys AS fkey ");
			sb.Append("    INNER JOIN sys.foreign_key_columns AS fcol ON fkey.OBJECT_ID = fcol.constraint_object_id ");
			sb.Append("ORDER BY ReferenceTableName, ReferenceColumnName, TableName, ColumnName ");

			return (sb.ToString());
		}

		#endregion
	}
}