/// <summary>
        /// Creates a database out of the structure it is given
        /// </summary>
        /// <param name="Database">Database structure</param>
        /// <param name="ConnectionString">The connection string to the database's location</param>
        public static void CreateDatabase(Database Database,string ConnectionString)
        {
            try
            {
                string Command = BuildCommands(Database);
                string[] Splitter = { "\n" };
                string[] Commands = Command.Split(Splitter, StringSplitOptions.RemoveEmptyEntries);
                string DatabaseConnectionString = Regex.Replace(ConnectionString, "Initial Catalog=(.*?;)", "");
                using (SQLHelper Helper = new SQLHelper(Commands[0], DatabaseConnectionString, CommandType.Text))
                {
                    try
                    {
                        Helper.Open();
                        Helper.ExecuteNonQuery();
                    }
                    catch { throw; }
                    finally { Helper.Close(); }
                }
                for (int x = 1; x < Commands.Length; ++x)
                {
                    using (SQLHelper Helper = new SQLHelper(Commands[x], ConnectionString, CommandType.Text))
                    {
                        try
                        {
                            Helper.Open();
                            Helper.ExecuteNonQuery();
                        }
                        catch { throw; }
                        finally { Helper.Close(); }
                    }
                }
            }
            catch { throw; }
        }

        /// <summary>
        /// Gets the structure of a database
        /// </summary>
        /// <param name="ConnectionString">Connection string</param>
        /// <returns>The database structure</returns>
        public static Database GetDatabaseStructure(string ConnectionString)
        {
            string DatabaseName = Regex.Match(ConnectionString, "Initial Catalog=(.*?;)").Value.Replace("Initial Catalog=", "").Replace(";", "");
            Database Temp = new Database(DatabaseName);
            GetTables(ConnectionString, Temp);
            SetupTables(ConnectionString, Temp);
            SetupViews(ConnectionString, Temp);
            SetupStoredProcedures(ConnectionString, Temp);
            SetupFunctions(ConnectionString, Temp);
            return Temp;
        }

        /// <summary>
        /// Builds the list of commands to build the database
        /// </summary>
        /// <param name="Database">Database object</param>
        /// <returns>The commands needed to  build the database</returns>
        private static string BuildCommands(Database Database)
        {
            StringBuilder Builder = new StringBuilder();
            Builder.Append("EXEC dbo.sp_executesql @statement = N'CREATE DATABASE ").Append(Database.Name).Append("'\n");
            foreach (Table Table in Database.Tables)
            {
                Builder.Append(GetTableCommand(Table));
            }
            foreach (Table Table in Database.Tables)
            {
                Builder.Append(GetForeignKeyCommand(Table));
            }
            foreach (Function Function in Database.Functions)
            {
                Builder.Append(GetFunctionCommand(Function));
            }
            foreach (View View in Database.Views)
            {
                Builder.Append(GetViewCommand(View));
            }
            foreach (StoredProcedure StoredProcedure in Database.StoredProcedures)
            {
                Builder.Append(GetStoredProcedure(StoredProcedure));
            }
            return Builder.ToString();
        }

        /// <summary>
        /// Gets the foreign keys creation command
        /// </summary>
        /// <param name="Table">Table object</param>
        /// <returns>The string creating the foreign keys</returns>
        private static string GetForeignKeyCommand(Table Table)
        {
            StringBuilder Builder=new StringBuilder();
            foreach (Column Column in Table.Columns)
            {
                if (Column.ForeignKey.Count > 0)
                {
                    foreach (Column ForeignKey in Column.ForeignKey)
                    {
                        Builder.Append("EXEC dbo.sp_executesql @statement = N'ALTER TABLE ");
                        Builder.Append(Column.ParentTable.Name).Append(" ADD FOREIGN KEY (");
                        Builder.Append(Column.Name).Append(") REFERENCES ").Append(ForeignKey.ParentTable.Name);
                        Builder.Append("(").Append(ForeignKey.Name).Append(")'\n");
                    }
                }
            }
            return Builder.ToString();
        }

        /// <summary>
        /// Gets the stored procedure creation command
        /// </summary>
        /// <param name="StoredProcedure">The stored procedure object</param>
        /// <returns>The string creating the stored procedure</returns>
        private static string GetStoredProcedure(StoredProcedure StoredProcedure)
        {
            return StoredProcedure.Definition.Replace("\n"," ").Replace("\r"," ")+"\n";
        }

        /// <summary>
        /// Gets the view creation command
        /// </summary>
        /// <param name="View">The view object</param>
        /// <returns>The string creating the view</returns>
        private static string GetViewCommand(View View)
        {
            return View.Definition.Replace("\n", " ").Replace("\r", " ") + "\n";
        }

        /// <summary>
        /// Gets the function command
        /// </summary>
        /// <param name="Function">The function object</param>
        /// <returns>The string creating the function</returns>
        private static string GetFunctionCommand(Function Function)
        {
            return Function.Definition.Replace("\n", " ").Replace("\r", " ") + "\n";
        }

        /// <summary>
        /// Gets the table creation commands
        /// </summary>
        /// <param name="Table">Table object</param>
        /// <returns>The string containing the creation commands</returns>
        private static string GetTableCommand(Table Table)
        {
            StringBuilder Builder=new StringBuilder();
            Builder.Append("EXEC dbo.sp_executesql @statement = N'CREATE TABLE ").Append(Table.Name).Append("(");
            string Splitter="";
            foreach (Column Column in Table.Columns)
            {
                Builder.Append(Splitter).Append(Column.Name).Append(" ").Append(Column.DataType.ToString());
                if (Column.DataType == SqlDbType.VarChar || Column.DataType == SqlDbType.NVarChar)
                {
                    if (Column.Length == -1)
                    {
                        Builder.Append("(MAX)");
                    }
                    else
                    {
                        Builder.Append("(").Append(Column.Length.ToString()).Append(")");
                    }
                }
                if (!Column.Nullable)
                {
                    Builder.Append(" NOT NULL");
                }
                if (Column.Unique)
                {
                    Builder.Append(" UNIQUE");
                }
                if (Column.PrimaryKey)
                {
                    Builder.Append(" PRIMARY KEY");
                }
                if (!string.IsNullOrEmpty(Column.Default))
                {
                    Builder.Append(" DEFAULT ").Append(Column.Default.Replace("(", "").Replace(")", "").Replace("'","''"));
                }
                if (Column.AutoIncrement)
                {
                    Builder.Append(" IDENTITY");
                }
                Splitter = ",";
            }
            Builder.Append(")'\n");
            int Counter = 0;
            foreach (Column Column in Table.Columns)
            {
                if (Column.Index&&Column.Unique)
                {
                    Builder.Append("EXEC dbo.sp_executesql @statement = N'CREATE UNIQUE INDEX ");
                    Builder.Append("Index_").Append(Column.Name).Append(Counter.ToString()).Append(" ON ");
                    Builder.Append(Column.ParentTable.Name).Append("(").Append(Column.Name).Append(")");
                    Builder.Append("'\n");
                }
                else if (Column.Index)
                {
                    Builder.Append("EXEC dbo.sp_executesql @statement = N'CREATE INDEX ");
                    Builder.Append("Index_").Append(Column.Name).Append(Counter.ToString()).Append(" ON ");
                    Builder.Append(Column.ParentTable.Name).Append("(").Append(Column.Name).Append(")");
                    Builder.Append("'\n");
                }
                ++Counter;
            }
            return Builder.ToString();
        }

        /// <summary>
        /// Sets up the functions
        /// </summary>
        /// <param name="ConnectionString">Connection string</param>
        /// <param name="Temp">Database object</param>
        private static void SetupFunctions(string ConnectionString, Database Temp)
        {
            string Command = "SELECT SPECIFIC_NAME as NAME,ROUTINE_DEFINITION as DEFINITION FROM INFORMATION_SCHEMA.ROUTINES WHERE INFORMATION_SCHEMA.ROUTINES.ROUTINE_TYPE='FUNCTION'";
            using (SQLHelper Helper = new SQLHelper(Command, ConnectionString, CommandType.Text))
            {
                try
                {
                    Helper.Open();
                    Helper.ExecuteReader();
                    while (Helper.Read())
                    {
                        string Name = (string)Helper.GetParameter("NAME", "");
                        string Definition = (string)Helper.GetParameter("DEFINITION", "");
                        Temp.AddFunction(Name, Definition);
                    }
                }
                catch { }
                finally { Helper.Close(); }
            }
        }

        /// <summary>
        /// Sets up stored procedures
        /// </summary>
        /// <param name="ConnectionString">Connection string</param>
        /// <param name="Temp">Database object</param>
        private static void SetupStoredProcedures(string ConnectionString, Database Temp)
        {
            string Command = "SELECT sys.procedures.name as NAME,OBJECT_DEFINITION(sys.procedures.object_id) as DEFINITION FROM sys.procedures";
            using (SQLHelper Helper = new SQLHelper(Command, ConnectionString, CommandType.Text))
            {
                try
                {
                    Helper.Open();
                    Helper.ExecuteReader();
                    while (Helper.Read())
                    {
                        string ProcedureName = (string)Helper.GetParameter("NAME", "");
                        string Definition = (string)Helper.GetParameter("DEFINITION", "");
                        Temp.AddStoredProcedure(ProcedureName, Definition);
                    }
                }
                catch { }
                finally { Helper.Close(); }
            }
            foreach (StoredProcedure Procedure in Temp.StoredProcedures)
            {
                Command = "SELECT sys.systypes.name as TYPE,sys.parameters.name as NAME,sys.parameters.max_length as LENGTH,sys.parameters.default_value as [DEFAULT VALUE] FROM sys.procedures INNER JOIN sys.parameters on sys.procedures.object_id=sys.parameters.object_id INNER JOIN sys.systypes on sys.systypes.xusertype=sys.parameters.system_type_id WHERE sys.procedures.name=@ProcedureName AND (sys.systypes.xusertype <> 256)";
                using (SQLHelper Helper = new SQLHelper(Command, ConnectionString, CommandType.Text))
                {
                    try
                    {
                        Helper.Open();
                        Helper.AddParameter("@ProcedureName", Procedure.Name, 128);
                        Helper.ExecuteReader();
                        while (Helper.Read())
                        {
                            string Type = (string)Helper.GetParameter("TYPE", "");
                            string Name = (string)Helper.GetParameter("NAME", "");
                            int Length = int.Parse(Helper.GetParameter("LENGTH", 0).ToString());
                            string Default = (string)Helper.GetParameter("DEFAULT VALUE", "");
                            Procedure.AddColumn(Name, Type, Length, Default);
                        }
                    }
                    catch { }
                    finally { Helper.Close(); }
                }
            }
        }

        /// <summary>
        /// Sets up the views
        /// </summary>
        /// <param name="ConnectionString">Connection string</param>
        /// <param name="Temp">Database object</param>
        private static void SetupViews(string ConnectionString, Database Temp)
        {
            foreach (View View in Temp.Views)
            {
                string Command = "SELECT OBJECT_DEFINITION(sys.views.object_id) as Definition FROM sys.views WHERE sys.views.name=@ViewName";
                using (SQLHelper Helper = new SQLHelper(Command, ConnectionString, CommandType.Text))
                {
                    try
                    {
                        Helper.Open();
                        Helper.AddParameter("@ViewName", View.Name, 128);
                        Helper.ExecuteReader();
                        if (Helper.Read())
                        {
                            View.Definition = (string)Helper.GetParameter("Definition", "");
                        }
                    }
                    catch { }
                    finally { Helper.Close(); }
                }
                Command = "SELECT sys.columns.name AS [Column], sys.systypes.name AS [COLUMN TYPE], sys.columns.max_length as [MAX LENGTH], sys.columns.is_nullable as [IS NULLABLE] FROM sys.views INNER JOIN sys.columns on sys.columns.object_id=sys.views.object_id INNER JOIN sys.systypes ON sys.systypes.xtype = sys.columns.system_type_id WHERE (sys.views.name = @ViewName) AND (sys.systypes.xusertype <> 256)";
                using (SQLHelper Helper = new SQLHelper(Command, ConnectionString, CommandType.Text))
                {
                    try
                    {
                        Helper.Open();
                        Helper.AddParameter("@ViewName", View.Name, 128);
                        Helper.ExecuteReader();
                        while (Helper.Read())
                        {
                            string ColumnName = (string)Helper.GetParameter("Column", "");
                            string ColumnType = (string)Helper.GetParameter("COLUMN TYPE", "");
                            int MaxLength = (int)(int.Parse(Helper.GetParameter("MAX LENGTH", 0).ToString()));
                            bool Nullable = (bool)Helper.GetParameter("IS NULLABLE", false);
                            View.AddColumn(ColumnName, ColumnType, MaxLength, Nullable);
                        }
                    }
                    catch { }
                    finally { Helper.Close(); }
                }
            }
        }

        /// <summary>
        /// Sets up the tables (pulls columns, etc.)
        /// </summary>
        /// <param name="ConnectionString">Connection string</param>
        /// <param name="Temp">Database object</param>
        private static void SetupTables(string ConnectionString, Database Temp)
        {
            foreach (Table Table in Temp.Tables)
            {
                string Command = "SELECT sys.columns.name AS [Column], sys.systypes.name AS [COLUMN TYPE], sys.columns.max_length as [MAX LENGTH], sys.columns.is_nullable as [IS NULLABLE], sys.columns.is_identity as [IS IDENTITY], sys.index_columns.index_id as [IS INDEX], key_constraints.name as [PRIMARY KEY], key_constraints_1.name as [UNIQUE], tables_1.name as [FOREIGN KEY TABLE], columns_1.name as [FOREIGN KEY COLUMN], sys.default_constraints.definition as [DEFAULT VALUE] FROM sys.tables INNER JOIN sys.columns on sys.columns.object_id=sys.tables.object_id INNER JOIN sys.systypes ON sys.systypes.xtype = sys.columns.system_type_id LEFT OUTER JOIN sys.index_columns on sys.index_columns.object_id=sys.tables.object_id and sys.index_columns.column_id=sys.columns.column_id LEFT OUTER JOIN sys.key_constraints on sys.key_constraints.parent_object_id=sys.tables.object_id and sys.key_constraints.parent_object_id=sys.index_columns.object_id and sys.index_columns.index_id=sys.key_constraints.unique_index_id and sys.key_constraints.type='PK' LEFT OUTER JOIN sys.foreign_key_columns on sys.foreign_key_columns.parent_object_id=sys.tables.object_id and sys.foreign_key_columns.parent_column_id=sys.columns.column_id LEFT OUTER JOIN sys.tables as tables_1 on tables_1.object_id=sys.foreign_key_columns.referenced_object_id LEFT OUTER JOIN sys.columns as columns_1 on columns_1.column_id=sys.foreign_key_columns.referenced_column_id and columns_1.object_id=tables_1.object_id LEFT OUTER JOIN sys.key_constraints as key_constraints_1 on key_constraints_1.parent_object_id=sys.tables.object_id and key_constraints_1.parent_object_id=sys.index_columns.object_id and sys.index_columns.index_id=key_constraints_1.unique_index_id and key_constraints_1.type='UQ' LEFT OUTER JOIN sys.default_constraints on sys.default_constraints.object_id=sys.columns.default_object_id WHERE (sys.tables.name = @TableName) AND (sys.systypes.xusertype <> 256)";
                using (SQLHelper Helper = new SQLHelper(Command, ConnectionString, CommandType.Text))
                {
                    try
                    {
                        Helper.Open();
                        Helper.AddParameter("@TableName", Table.Name, 128);
                        Helper.ExecuteReader();
                        while (Helper.Read())
                        {
                            string ColumnName = (string)Helper.GetParameter("Column", "");
                            string ColumnType = (string)Helper.GetParameter("COLUMN TYPE", "");
                            int MaxLength = (int)(int.Parse(Helper.GetParameter("MAX LENGTH", 0).ToString()));
                            bool Nullable = (bool)Helper.GetParameter("IS NULLABLE", false);
                            bool Identity = (bool)Helper.GetParameter("IS IDENTITY", false);
                            bool Index = (bool)((int)Helper.GetParameter("IS INDEX", 0) != 0);
                            bool PrimaryKey = string.IsNullOrEmpty((string)Helper.GetParameter("PRIMARY KEY", "")) ? false : true;
                            bool Unique = string.IsNullOrEmpty((string)Helper.GetParameter("UNIQUE", "")) ? false : true;
                            string ForeignKeyTable = (string)Helper.GetParameter("FOREIGN KEY TABLE", "");
                            string ForeignKeyColumn = (string)Helper.GetParameter("FOREIGN KEY COLUMN", "");
                            string DefaultValue = (string)Helper.GetParameter("DEFAULT VALUE", "");
                            if (Table.ContainsColumn(ColumnName))
                            {
                                Table.AddForeignKey(ColumnName, ForeignKeyTable, ForeignKeyColumn);
                            }
                            else
                            {
                                Table.AddColumn(ColumnName, ColumnType, MaxLength, Nullable, Identity, Index, PrimaryKey, Unique, ForeignKeyTable, ForeignKeyColumn, DefaultValue);
                            }
                        }
                    }
                    catch { }
                    finally { Helper.Close(); }
                }
                Command = "SELECT sys.triggers.name as Name,sys.trigger_events.type as Type,OBJECT_DEFINITION(sys.triggers.object_id) as Definition FROM sys.triggers INNER JOIN sys.trigger_events ON sys.triggers.object_id=sys.trigger_events.object_id INNER JOIN sys.tables on sys.triggers.parent_id=sys.tables.object_id where sys.tables.name=@TableName";
                using (SQLHelper Helper = new SQLHelper(Command, ConnectionString, CommandType.Text))
                {
                    try
                    {
                        Helper.Open();
                        Helper.AddParameter("@TableName", Table.Name, 128);
                        Helper.ExecuteReader();
                        while (Helper.Read())
                        {
                            string Name = (string)Helper.GetParameter("Name", "");
                            int Type = (int)Helper.GetParameter("Type", 0);
                            string Definition = (string)Helper.GetParameter("Definition", "");
                            Table.AddTrigger(Name, Definition, Type);
                        }
                    }
                    catch { }
                    finally { Helper.Close(); }
                }
            }
            foreach (Table Table in Temp.Tables)
            {
                Table.SetupForeignKeys();
            }
        }

        /// <summary>
        /// Gets the tables for a database
        /// </summary>
        /// <param name="ConnectionString">Connection string</param>
        /// <param name="Temp">The database object</param>
        private static void GetTables(string ConnectionString, Database Temp)
        {
            string Command = "SELECT TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, TABLE_TYPE FROM INFORMATION_SCHEMA.TABLES";
            using (SQLHelper Helper = new SQLHelper(Command, ConnectionString, CommandType.Text))
            {
                try
                {
                    Helper.Open();
                    Helper.ExecuteReader();
                    while (Helper.Read())
                    {
                        string TableName = (string)Helper.GetParameter("TABLE_NAME", "");
                        string TableType = (string)Helper.GetParameter("TABLE_TYPE", "");
                        if (TableType == "BASE TABLE")
                        {
                            Temp.AddTable(TableName);
                        }
                        else if (TableType == "VIEW")
                        {
                            Temp.AddView(TableName);
                        }
                    }
                }
                catch { }
                finally { Helper.Close(); }
            }
        }

        /// <summary>
        /// Checks if something exists
        /// </summary>
        /// <param name="Command">Command to run</param>
        /// <param name="Name">Name of the item</param>
        /// <param name="ConnectionString">Connection string</param>
        /// <returns>True if it exists, false otherwise</returns>
        private static bool CheckExists(string Command, string Name, string ConnectionString)
        {
            bool Exists = false;
            using (SQLHelper Helper = new SQLHelper(Command, ConnectionString, CommandType.Text))
            {
                try
                {
                    Helper.Open();
                    Helper.AddParameter("@Name", Name, 200);
                    Helper.ExecuteReader();
                    if (Helper.Read())
                        Exists = true;
                }
                catch { }
                finally { Helper.Close(); }
            }
            return Exists;
        }

SELECT sys.columns.name AS [Column], sys.systypes.name AS [COLUMN TYPE], sys.columns.max_length as [MAX LENGTH], sys.columns.is_nullable as [IS NULLABLE], sys.columns.is_identity as [IS IDENTITY], sys.index_columns.index_id as [IS INDEX], key_constraints.name as [PRIMARY KEY], key_constraints_1.name as [UNIQUE], tables_1.name as [FOREIGN KEY TABLE], columns_1.name as [FOREIGN KEY COLUMN], sys.default_constraints.definition as [DEFAULT VALUE] FROM sys.tables INNER JOIN sys.columns on sys.columns.object_id=sys.tables.object_id INNER JOIN sys.systypes ON sys.systypes.xtype = sys.columns.system_type_id LEFT OUTER JOIN sys.index_columns on sys.index_columns.object_id=sys.tables.object_id and sys.index_columns.column_id=sys.columns.column_id LEFT OUTER JOIN sys.key_constraints on sys.key_constraints.parent_object_id=sys.tables.object_id and sys.key_constraints.parent_object_id=sys.index_columns.object_id and sys.index_columns.index_id=sys.key_constraints.unique_index_id and sys.key_constraints.type='PK' LEFT OUTER JOIN sys.foreign_key_columns on sys.foreign_key_columns.parent_object_id=sys.tables.object_id and sys.foreign_key_columns.parent_column_id=sys.columns.column_id LEFT OUTER JOIN sys.tables as tables_1 on tables_1.object_id=sys.foreign_key_columns.referenced_object_id LEFT OUTER JOIN sys.columns as columns_1 on columns_1.column_id=sys.foreign_key_columns.referenced_column_id and columns_1.object_id=tables_1.object_id LEFT OUTER JOIN sys.key_constraints as key_constraints_1 on key_constraints_1.parent_object_id=sys.tables.object_id and key_constraints_1.parent_object_id=sys.index_columns.object_id and sys.index_columns.index_id=key_constraints_1.unique_index_id and key_constraints_1.type='UQ' LEFT OUTER JOIN sys.default_constraints on sys.default_constraints.object_id=sys.columns.default_object_id WHERE (sys.tables.name = @TableName) AND (sys.systypes.xusertype <> 256)

    /// <summary>
    /// Functions helpful for SQL Server
    /// </summary>
    public static class SQLServer
    {
        #region Public Static Functions

        /// <summary>
        /// Checks if a database exists
        /// </summary>
        /// <param name="Database">Name of the database</param>
        /// <param name="ConnectionString">Connection string</param>
        /// <returns>True if it exists, false otherwise</returns>
        public static bool DoesDatabaseExist(string Database, string ConnectionString)
        {
            return CheckExists("SELECT * FROM Master.sys.Databases WHERE name=@Name", Database, ConnectionString);
        }

        /// <summary>
        /// Checks if a table exists
        /// </summary>
        /// <param name="Table">Table name</param>
        /// <param name="ConnectionString">Connection string</param>
        /// <returns>True if it exists, false otherwise</returns>
        public static bool DoesTableExist(string Table, string ConnectionString)
        {
            return CheckExists("SELECT * FROM sys.Tables WHERE name=@Name", Table, ConnectionString);
        }

        /// <summary>
        /// Checks if a view exists
        /// </summary>
        /// <param name="View">View name</param>
        /// <param name="ConnectionString">Connection string</param>
        /// <returns>True if it exists, false otherwise</returns>
        public static bool DoesViewExist(string View, string ConnectionString)
        {
            return CheckExists("SELECT * FROM sys.views WHERE name=@Name", View, ConnectionString);
        }

        /// <summary>
        /// Checks if stored procedure exists
        /// </summary>
        /// <param name="StoredProcedure">Stored procedure's name</param>
        /// <param name="ConnectionString">Connection string</param>
        /// <returns>True if it exists, false otherwise</returns>
        public static bool DoesStoredProcedureExist(string StoredProcedure, string ConnectionString)
        {
            return CheckExists("SELECT * FROM sys.Procedures WHERE name=@Name", StoredProcedure, ConnectionString);
        }

        /// <summary>
        /// Checks if trigger exists
        /// </summary>
        /// <param name="Trigger">Trigger's name</param>
        /// <param name="ConnectionString">Connection string</param>
        /// <returns>True if it exists, false otherwise</returns>
        public static bool DoesTriggerExist(string Trigger, string ConnectionString)
        {
            return CheckExists("SELECT * FROM sys.triggers WHERE name=@Name", Trigger, ConnectionString);
        }

        /// <summary>
        /// Gets a list of columns associated with the table
        /// </summary>
        /// <param name="TableName">Table name</param>
        /// <param name="ConnectionString">Connection string</param>
        /// <returns>A list of column name/type pairs</returns>
        public static System.Collections.Generic.List<Pair<string, string>> GetListOfColumns(string TableName, string ConnectionString)
        {
            System.Collections.Generic.List<Pair<string, string>> Columns = new System.Collections.Generic.List<Pair<string, string>>();
            string Command="SELECT [sys].[columns].name as [Column],[sys].[systypes].name as [ColumnType] FROM [sys].[systypes],[sys].[columns],[sys].[tables] WHERE [sys].[systypes].xtype=[sys].[columns].system_type_id    AND [sys].[columns].object_id=[sys].[tables].object_id    AND [sys].[tables].name=@TableName";
            using (SQLHelper Helper = new SQLHelper(Command, ConnectionString, CommandType.Text))
            {
                try
                {
                    Helper.Open();
                    Helper.AddParameter("@TableName", TableName, 200);
                    Helper.ExecuteReader();
                    while (Helper.Read())
                    {
                        Columns.Add(new Pair<string, string>((string)Helper.GetParameter("Column", ""), (string)Helper.GetParameter("ColumnType", "")));
                    }
                }
                catch { }
                finally { Helper.Close(); }
            }
            return Columns;
        }

        #endregion

        #region Private Static Functions

        /// <summary>
        /// Checks if something exists
        /// </summary>
        /// <param name="Command">Command to run</param>
        /// <param name="Name">Name of the item</param>
        /// <param name="ConnectionString">Connection string</param>
        /// <returns>True if it exists, false otherwise</returns>
        private static bool CheckExists(string Command, string Name, string ConnectionString)
        {
            bool Exists = false;
            using (SQLHelper Helper = new SQLHelper(Command, ConnectionString, CommandType.Text))
            {
                try
                {
                    Helper.Open();
                    Helper.AddParameter("@Name", Name, 200);
                    Helper.ExecuteReader();
                    if (Helper.Read())
                        Exists = true;
                }
                catch { }
                finally { Helper.Close(); }
            }
            return Exists;
        }

        #endregion
    }

    /// <summary>
    /// Factory mapping
    /// </summary>
    /// <typeparam name="Source">Source type</typeparam>
    public class FactoryMapping<Source>:IMapping
    {
        #region Constructor

        /// <summary>
        /// Constructor
        /// </summary>
        public FactoryMapping()
        {
            Setup();
        }

        #endregion

        #region IMapping Members

        public Type SourceType
        {
            get { return typeof(Source); }
        }

        public Type DestinationType
        {
            get { return typeof(List<object>); }
        }

        public void Sync(object Source, object Destination)
        {
            List<object> TempDestination = (List<object>)Destination;
            int x = 0;
            foreach (MappingInfo PropertyMapping in PropertyMappings)
            {
                if (!string.IsNullOrEmpty(PropertyMapping.Description))
                {
                    ++x;
                }
                PropertyInfo PropertyInfo = null;
                object TempSourceObject = Utilities.Reflection.GetPropertyParent(Source, PropertyMapping.Source, out PropertyInfo);
                SourceMapping.Sync(TempSourceObject, PropertyInfo, TempDestination[x]);
                ++x;
            }
        }

        public object Create(object Source)
        {
            List<object> ReturnList = new List<object>();
            foreach (MappingInfo PropertyMapping in PropertyMappings)
            {
                if (!string.IsNullOrEmpty(PropertyMapping.Description))
                {
                    ReturnList.Add(SourceMapping.CreateDescription(PropertyMapping.Description));
                }
                ReturnList.Add(SourceMapping.CreateDestination(Utilities.Reflection.GetPropertyValue(Source, PropertyMapping.Source),
                    Utilities.Reflection.GetPropertyType(Source,PropertyMapping.Source)));
            }
            return ReturnList;
        }

        #endregion

        #region Protected Functions

        /// <summary>
        /// Maps source property
        /// </summary>
        /// <param name="SourceExpression">Source property to map</param>
        protected void Map(Expression<Func<Source, object>> SourceExpression)
        {
            Map(SourceExpression, "");
        }

        /// <summary>
        /// Maps source property
        /// </summary>
        /// <param name="SourceExpression">Source property to map</param>
        /// <param name="Format">Format string</param>
        protected void Map(Expression<Func<Source, object>> SourceExpression, string Format)
        {
            Setup();
            string SourceName = Utilities.Reflection.GetPropertyName<Source>(SourceExpression);
            PropertyInfo SourceProperty = Utilities.Reflection.GetProperty<Source>(SourceName);
            object[] Attributes = SourceProperty.GetCustomAttributes(typeof(Description), true);
            if (Attributes.Length > 0)
            {
                PropertyMappings.Add(new MappingInfo(SourceName, "", Format, ((Description)Attributes[0]).DescriptionValue));
            }
            else
            {
                PropertyMappings.Add(new MappingInfo(SourceName, "", Format, ""));
            }
        }

        #endregion

        #region Private Functions

        /// <summary>
        /// Sets up the mappings
        /// </summary>
        private void Setup()
        {
            if (PropertyMappings != null)
                return;
            SourceMapping = (TypeMapping)Manager.Instance.TypeMapping;
            PropertyMappings = new List<MappingInfo>();
        }

        #endregion

        #region Internal Properties

        internal TypeMapping SourceMapping { get; set; }
        internal List<MappingInfo> PropertyMappings { get; set; }

        #endregion
    }

    /// <summary>
    /// Maps a type to another type
    /// </summary>
    public class TypeMapping : Factory<Type, object>, ITypeMapping
    {
        #region Constructor

        /// <summary>
        /// Constructor
        /// </summary>
        public TypeMapping()
        {
            Setup();
        }

        #endregion

        #region Private Functions

        /// <summary>
        /// Sets up the mappings
        /// </summary>
        private void Setup()
        {
            if (PropertyMappings != null)
                return;
            PropertyMappings = new Dictionary<Type, MappingInfo>();
        }

        #endregion

        #region Protected Functions

        /// <summary>
        /// Maps a type to another type
        /// </summary>
        /// <typeparam name="Source">Source type</typeparam>
        /// <typeparam name="Destination">Destination type</typeparam>
        /// <param name="DestinationType">Function for creating the destination type</param>
        /// <param name="DestinationProperty">Property to map to</param>
        protected void Map<Source, Destination>(Func<object> DestinationType,
            Expression<Func<Destination, object>> DestinationProperty)
        {
            Setup();
            Register(typeof(Source), DestinationType);
            PropertyMappings.Add(typeof(Source), new MappingInfo("", Utilities.Reflection.GetPropertyName<Destination>(DestinationProperty), "",""));
        }

        /// <summary>
        /// Maps a type to another type
        /// </summary>
        /// <typeparam name="Source">Source type</typeparam>
        /// <typeparam name="Destination">Destination type</typeparam>
        /// <param name="DestinationType">Function for creating the destination type</param>
        /// <param name="DestinationProperty">Property to map to</param>
        /// <param name="Format">Format string</param>
        protected void Map<Source, Destination>(Func<object> DestinationType,
            Expression<Func<Destination, object>> DestinationProperty,
            string Format)
        {
            Setup();
            Register(typeof(Source), DestinationType);
            PropertyMappings.Add(typeof(Source), new MappingInfo("", Utilities.Reflection.GetPropertyName<Destination>(DestinationProperty), Format, ""));
        }

        /// <summary>
        /// Maps a description to another type
        /// </summary>
        /// <typeparam name="Destination">Destination type</typeparam>
        /// <param name="DestinationType">Function for creating the destination type</param>
        /// <param name="DestinationProperty">Property to map to</param>
        protected void MapDescription<Destination>(Func<object> DestinationType,
            Expression<Func<Destination, object>> DestinationProperty)
        {
            MapDescription<Destination>(DestinationType, DestinationProperty, "");
        }

        /// <summary>
        /// Maps a description to another type
        /// </summary>
        /// <typeparam name="Destination">Destination type</typeparam>
        /// <param name="DestinationType">Function for creating the destination type</param>
        /// <param name="DestinationProperty">Property to map to</param>
        /// <param name="Format">Formatting string</param>
        protected void MapDescription<Destination>(Func<object> DestinationType,
            Expression<Func<Destination, object>> DestinationProperty,
            string Format)
        {
            Register(typeof(MappingInfo), DestinationType);
            DescriptionMapping = new MappingInfo("", Utilities.Reflection.GetPropertyName<Destination>(DestinationProperty), Format, "");
        }

        #endregion

        #region Public Functions

        /// <summary>
        /// Creates the destination type
        /// </summary>
        /// <param name="SourceValue">Source value</param>
        /// <param name="SourceType">Source type</param>
        /// <returns>The destination type</returns>
        public object CreateDestination(object SourceValue, Type SourceType)
        {
            object DestinationObject = Create(SourceType);
            Utilities.Reflection.SetValue(SourceValue, DestinationObject, PropertyMappings[SourceType].Destination, PropertyMappings[SourceType].Format);
            return DestinationObject;
        }

        /// <summary>
        /// Creates a description object based off of the description input
        /// </summary>
        /// <param name="DescriptionValue">The description value</param>
        /// <returns>The description object</returns>
        public object CreateDescription(object DescriptionValue)
        {
            object DestinationObject = Create(typeof(MappingInfo));
            Utilities.Reflection.SetValue(DescriptionValue, DestinationObject, DescriptionMapping.Destination, DescriptionMapping.Format);
            return DestinationObject;
        }

        /// <summary>
        /// Syncs the source to the destination
        /// </summary>
        /// <param name="SourceObject">Source object</param>
        /// <param name="SourcePropertyInfo">Property to copy</param>
        /// <param name="DestinationObject">Destination object</param>
        public void Sync(object SourceObject, PropertyInfo SourcePropertyInfo, object DestinationObject)
        {
            object Value = Utilities.Reflection.GetPropertyValue(DestinationObject, PropertyMappings[SourcePropertyInfo.PropertyType].Destination);
            Utilities.Reflection.SetValue(Value, SourceObject, SourcePropertyInfo, PropertyMappings[SourcePropertyInfo.PropertyType].Format);
        }

        #endregion

        #region Internal Properties

        internal Dictionary<Type, MappingInfo> PropertyMappings { get; set; }
        internal MappingInfo DescriptionMapping { get; set; }

        #endregion
    }

    [AttributeUsage(AttributeTargets.Property, AllowMultiple = false)]
    public class Description:Attribute
    {
        #region Constructor

        /// <summary>
        /// Constructor
        /// </summary>
        /// <param name="Value">Description of the property</param>
        public Description(string Value)
        {
            DescriptionValue = Value;
        }

        #endregion

        #region Public Properties

        /// <summary>
        /// Description value
        /// </summary>
        public string DescriptionValue { get; set; }

        #endregion
    }

        [Description("Text Field Description: ")]
        public string Text { get; set; }