/SQLExtendedProperties/Main.cs

Large files files are truncated, but you can click here to view the full file

using System;

using System.Collections;

using System.Collections.Generic;

using System.ComponentModel;

using System.Data;

using System.Drawing;

using System.Linq;

using System.Text;

using System.Windows.Forms;

using System.Data.SqlClient;

using System.Configuration;

using System.Threading;

using System.Reflection;

using System.Diagnostics;

using System.IO;

//SMO namespaces

using Microsoft.SqlServer.Management.Common;

using Microsoft.SqlServer.Management.Smo;

using Smo = Microsoft.SqlServer.Management.Smo;

using System.Xml.Linq;





namespace Rhsoft.SqlServer.MetadataManager

{

    public partial class Mainform : Form

    {

        #region Constants

        //Constants to label treenode metadata.

        const string NODETYPE_DB = "DB";

        const string NODETYPE_TABLE = "TBL";

        const string NODETYPE_COLUMN = "COL";

        const string NODETYPE_FK = "FK";

        const string NODETYPE_PROP = "PROP";

        const string NODETYPE_XPROP = "XPROP";

        const string NODETYPE_IX = "IX";

        //ORM Extended properties names

        const string ORM_CONNECTIONSTRING = "ORM_ConnectionStringName";

        const string ORM_CONTEXT_IMPORTS = "ORM_ContextImports";

        const string ORM_CONTEXT_NAME = "ORM_ContextName";

        const string ORM_CONTEXT_NAMESPACE = "ORM_ContextNamespace";

        const string ORM_ENTITY_IMPORTS = "ORM_EntityImports";

        const string ORM_ENTITY_NAMESPACE = "ORM_EntityNamespace";

        const string ORM_GENERATE_MULTIPLE_FILES = "ORM_GenerateMultipleClasses";

        const string ORM_INCLUDE_PREVENT_DEBUG = "ORM_IncludePreventingDebug";

        const string ORM_IS_SEALED = "ORM_IsSealed";

        const string ORM_IS_SERIALIZABLE = "ORM_IsSerializable";

        const string ORM_INCLUDE_CRUD_FUNCTIONS = "ORM_IncludeCRUDFunctions";

        const string ORM_OVERRIDE_CRUD_OPERATIONS = "ORM_OverrideCRUDOperations";

        const string ORM_ONLY_SELECTED = "ORM_OnlySelectedTables";

        const string ORM_SAVEPATH = "ORM_SavePath";

        //ORM Extended properties at table scope.

        const string ORM_CRUD_DELETE = "ORM_Crud_DeleteFunction";

        const string ORM_CRUD_INSERT = "ORM_Crud_InsertFunction";

        const string ORM_CRUD_UPDATE = "ORM_Crud_UpdateFunction";

        const string ORM_ENTITY_NAME = "ORM_EntityName";

        const string ORM_ENTITY_PLURALNAME = "ORM_EntityPluralName"; 

        const string ORM_ENTITY_IS_SEALED = "ORM_IsSealed";

        const string ORM_ENTITY_IS_SERIALIZED = "ORM_IsSerialized";

        #endregion



        #region Fields

        SqlConnection _conn; //Ado.NET Sql Connection.

        Server _serverObject; //Sql Server smo server object.

        ServerConnection _serverConn; //Smo server connection.

        Database _currentDatabase; //Store de current database  object.

        Table _currentTable; //Store the current table object.

        Column _currentColumn; //Store the current column object.

        SqlSmoObject _currentObject; //Store a generic server object.

        string _currentObjectType = null;

        Smo.ExtendedPropertyCollection _currentPropertiesCollection = null;

        bool _serverConnected = false; //Current server state.

        string _currentServerConnectionString = null;

        //DbProperties fields.

        string _propertyOldName = null;

        #endregion



        #region Assembly Attribute Accessors

        public string AssemblyTitle

        {

            get

            {

                object[] attributes = Assembly.GetExecutingAssembly( ).GetCustomAttributes( typeof( AssemblyTitleAttribute ), false );

                if( attributes.Length > 0 )

                {

                    AssemblyTitleAttribute titleAttribute = (AssemblyTitleAttribute)attributes[0];

                    if( titleAttribute.Title != "" )

                    {

                        return titleAttribute.Title;

                    }

                }

                return System.IO.Path.GetFileNameWithoutExtension( Assembly.GetExecutingAssembly( ).CodeBase );

            }

        }

        public string AssemblyVersion

        {

            get

            {

                return Assembly.GetExecutingAssembly( ).GetName( ).Version.ToString( );

            }

        }

        public string AssemblyDescription

        {

            get

            {

                object[] attributes = Assembly.GetExecutingAssembly( ).GetCustomAttributes( typeof( AssemblyDescriptionAttribute ), false );

                if( attributes.Length == 0 )

                {

                    return "";

                }

                return ( (AssemblyDescriptionAttribute)attributes[0] ).Description;

            }

        }

        public string AssemblyProduct

        {

            get

            {

                object[] attributes = Assembly.GetExecutingAssembly( ).GetCustomAttributes( typeof( AssemblyProductAttribute ), false );

                if( attributes.Length == 0 )

                {

                    return "";

                }

                return ( (AssemblyProductAttribute)attributes[0] ).Product;

            }

        }

        public string AssemblyCopyright

        {

            get

            {

                object[] attributes = Assembly.GetExecutingAssembly( ).GetCustomAttributes( typeof( AssemblyCopyrightAttribute ), false );

                if( attributes.Length == 0 )

                {

                    return "";

                }

                return ( (AssemblyCopyrightAttribute)attributes[0] ).Copyright;

            }

        }

        public string AssemblyCompany

        {

            get

            {

                object[] attributes = Assembly.GetExecutingAssembly( ).GetCustomAttributes( typeof( AssemblyCompanyAttribute ), false );

                if( attributes.Length == 0 )

                {

                    return "";

                }

                return ( (AssemblyCompanyAttribute)attributes[0] ).Company;

            }

        }

        #endregion



        #region Main

        public Mainform( )

        {

            

            InitializeComponent( );

            this.Text = string.Format( "{0} v{1}", AssemblyTitle, AssemblyVersion );

            LoadConfiguration( );

            DisableDatabaseControls( );

            PopulateServers( );

        }

        /// <summary>

        /// Load configuration values.

        /// </summary>

        private void LoadConfiguration( )

        {

            //ConfigValues.DescriptionLabel = ConfigurationManager.AppSettings["DescriptionLabel"].ToString( );

            //ConfigValues.SaveOnLostFocus = Convert.ToBoolean( ConfigurationManager.AppSettings["SaveOnLostFocus"] );

            ConfigValues.DescriptionLabel = Properties.Settings.Default.DescriptionPropertyName;

            ConfigValues.SaveOnLostFocus = Properties.Settings.Default.SaveOnLostFocus;

            ConfigValues.ViewSystemDatabases = Properties.Settings.Default.IncludeSystemDatabases;

            ConfigValues.ViewSystemTables = Properties.Settings.Default.IncludeSystemTables;

            ConfigValues.OverrideProperties = Properties.Settings.Default.OverrideProperties;

            

        }

        

        #endregion



        #region Helpers

        private string ParseValue( string value )

        {

            string ParsedValue = value;

            //Parse variables.

            if(_currentTable !=null)

                ParsedValue = ParseTableName( value, _currentTable.Name );

            if(_currentColumn != null)

                ParsedValue = ParseColumnName( value, _currentColumn.Name );

            return ParsedValue;

        }

        private string ParseTableName( string value, string maskValue  )

        {

            string ParsedValue=string.Empty;

            //Parse variables.

            ParsedValue = value.Replace( "$TableName$", maskValue );

            return ParsedValue;

        }

        private string ParseDatabaseName( string value, string maskValue )

        {

            string ParsedValue = string.Empty;

            //Parse variables.

            ParsedValue = value.Replace( "$DatabaseName$", maskValue );

            return ParsedValue;

        }

        private string ParseColumnName( string value, string maskValue )

        {

            string ParsedValue = string.Empty;

            //Parse variables.

            ParsedValue = value.Replace( "$ColumnName$", maskValue );

            return ParsedValue;

        }

        private void ShowinfoMessage( string message )

        {

            MessageBox.Show( message, "Information message", MessageBoxButtons.OK, MessageBoxIcon.Information );

        }

        private DialogResult ShowConfirmationMessage( string message )

        {

            return MessageBox.Show( message, "Dialog confirmation", MessageBoxButtons.OKCancel, MessageBoxIcon.Question );

        }

        private void ShowAlertMessage( string message )

        {

            MessageBox.Show( message, "Alert", MessageBoxButtons.OK, MessageBoxIcon.Exclamation );

        }

        private void ShowErrorMessage( string message )

        {

            MessageBox.Show( message, "Error notification", MessageBoxButtons.OK, MessageBoxIcon.Error );

        }

        private void ShowWaitDialog( string message )

        {

            

        }

        #endregion



        #region User interface

        private void EnableDatabaseControls( )

        {

            DbTree.Enabled = true;

            DbList.Enabled = true;

            FieldDescription.Enabled = true;

            DbProperties.Enabled = true;

            bSaveDescription.Enabled = true;

            bAddProperty.Enabled = true;

        }

        private void DisableDatabaseControls( )

        {

            DbTree.Enabled = false;

            DbList.Enabled = false;

            FieldDescription.Enabled = false;

            DbProperties.Enabled = false;

            bSaveDescription.Enabled = false;

            bAddProperty.Enabled = false;

        }

        private void ClearDatabaseControls( )

        {

            DbTree.Nodes.Clear( );

            DbList.Items.Clear( ); DbList.Refresh( );

            DbProperties.Rows.Clear( );

            FieldDescription.Text = "";

        }

        private void EnableMenuItems( )

        {

        }

        private void DisableMenuItems( )

        {



        }

        #endregion



        #region Extended properties management

        /// <summary>

        /// Take current properties collection and fill the properties listview.

        /// </summary>

        /// <param name="propCol"></param>

        private void PopulateExtendedProperties( Smo.ExtendedPropertyCollection propCol )

        {

            try

            {

                //Set the current properties collection object which is used to add or remove properties.

                _currentPropertiesCollection = propCol;

                //Populate extended properties view.

                DbProperties.Rows.Clear( );

                foreach( ExtendedProperty item in propCol )

                {

                    //Omit the property to store description and avoid delete or editing name.

                    if( !( item.Name == ConfigValues.DescriptionLabel ) )

                        DbProperties.Rows.Add( item.Name, item.Value.ToString( ) );

                }

                //Fill de description field.

                if( _currentPropertiesCollection[ConfigValues.DescriptionLabel] != null )

                {

                    //Show in text field to edit.

                    FieldDescription.Text = _currentPropertiesCollection[ConfigValues.DescriptionLabel].Value.ToString( );

                }

                

            }

            catch( InvalidOperationException ex ) { }

            catch( Exception ex ) { MessageBox.Show( ex.Message ); }

        }

        /// <summary>

        /// Update property value. Support variables:

        /// $TableName$, $ColumnName$ to custom values with tables or column names

        /// </summary>

        /// <param name="extendedPropertyName"></param>

        /// <param name="value"></param>

        private void SetExtendedProperty( string extendedPropertyName, string value )

        {

            //

            Smo.ExtendedPropertyCollection PropCol = _currentTable.ExtendedProperties;

            string ParsedValue = value;

            //Parse variables.

            ParsedValue = ParsedValue.Replace( "$TableName$", _currentTable.Name );

            ParsedValue = ParsedValue.Replace( "$ColumnName$", _currentColumn.Name );

            PropCol[extendedPropertyName].Value = ParsedValue;

            _currentTable.Alter( );

        }

        private void RemoveProperty( SqlSmoObject smoObject, string objectType, string name )

        {

            try

            {

                //Delete the property.            

                _currentPropertiesCollection[name].Drop();

                //Repopulate the list view control with the current properties collection.

                PopulateExtendedProperties( _currentPropertiesCollection );

            }

            catch( Exception ex ) { MessageBox.Show( ex.Message ); }

        }

        private void AddSingleExtendedProperty( SqlSmoObject smoObject, string objectType )

        {

            //

            Smo.ExtendedPropertyCollection PropertiesCol = null;

            try

            {

                switch( objectType )

                {

                    case NODETYPE_DB:

                        Database Db = (Database)smoObject;

                        PropertiesCol = Db.ExtendedProperties;

                        PropertiesCol.Add( new ExtendedProperty( smoObject, string.Format( "NewProperty{0}", PropertiesCol.Count + 1 ), "DefaultValue" ) );

                        Db.Alter( );

                        break;

                    case NODETYPE_TABLE:

                        Table t = (Table)smoObject;

                        PropertiesCol = t.ExtendedProperties;

                        PropertiesCol.Add( new ExtendedProperty( smoObject, string.Format("NewProperty{0}", PropertiesCol.Count + 1), "DefaultValue" ) );

                        t.Alter( );

                        break;

                    case NODETYPE_COLUMN:

                        Column c = (Column)smoObject;

                        PropertiesCol = c.ExtendedProperties;

                        PropertiesCol.Add( new ExtendedProperty( smoObject, string.Format("NewProperty{0}", PropertiesCol.Count + 1), "DefaultValue" )  ) ;

                        c.Alter( );

                        break;

                    default:

                        break;

                }

                PopulateExtendedProperties( PropertiesCol );

            }

            catch( Exception ex ) { MessageBox.Show( ex.Message ); }

        }

        private void AddExtendedProperty( SqlSmoObject smoObject, string objectType, string name, string value )

        {

            //

            Smo.ExtendedPropertyCollection PropertiesCol = null;

            try

            {

                switch( objectType )

                {

                    case NODETYPE_DB:

                        Database db = (Database)smoObject;

                        PropertiesCol = db.ExtendedProperties;

                        if( PropertiesCol[name] != null )

                        {

                            //Drop first to override.

                            if( Properties.Settings.Default.OverrideProperties )

                            {

                                PropertiesCol[name].Drop( );

                                db.Alter( );

                                PropertiesCol.Add( new ExtendedProperty( db, name, ParseDatabaseName( value, db.Name ) ) );

                                db.Alter( );

                            }

                        }

                        else

                        {

                            PropertiesCol.Add( new ExtendedProperty( db, name, ParseDatabaseName( value, db.Name ) ) );

                            db.Alter( );

                        }

                        //Refresh current properties view.

                        PropertiesCol = db.ExtendedProperties;

                        PopulateExtendedProperties( PropertiesCol );

                        break;

                    case NODETYPE_TABLE:

                        Table t = (Table)smoObject;             

                        PropertiesCol = t.ExtendedProperties;

                        if( PropertiesCol[name] != null )

                        {

                            //Drop first to override.

                            if( Properties.Settings.Default.OverrideProperties  )

                            {

                                PropertiesCol[name].Drop( );

                                t.Alter( );

                                PropertiesCol.Add( new ExtendedProperty( t, name, ParseTableName( value, t.Name ) ) );

                                t.Alter( );

                            }

                        }

                        else

                        {

                            PropertiesCol.Add( new ExtendedProperty( t, name, ParseTableName( value, t.Name ) ) );

                            t.Alter( );

                        }

                        //Refresh current properties view.

                        PropertiesCol = t.ExtendedProperties;

                        PopulateExtendedProperties( PropertiesCol );

                        break;

                    default:

                        break;

                }

                

            }

            catch( Exception ex ) { ShowErrorMessage( ex.Message ); }

            finally { ProgressBar.Visible = false; }

        }

        private void AddExtendedPropertyAtScope( string objectType, string name, string value, Scope scope )

        {

            //If the scope is set to database scope.

            if( scope == Scope.DatabaseScope )

            {

                foreach( Table table in _currentDatabase.Tables )

                {

                    AddExtendedProperty( table, NODETYPE_TABLE, name, value );

                }

                ShowinfoMessage( "Edit operation finished sucessfully." );

                //MessageBox.Show( "Edit operation finished sucessfully." );

            }

            //if the scope is set at scope to selected tables.

            if( scope == Scope.SelectedScope )

            {

                foreach( TreeNode node in DbTree.Nodes[0].Nodes.OfType<TreeNode>( ).Where( c => c.Checked ).ToList( ) )

                {

                    Hashtable NodeTagValues = (Hashtable)node.Tag;

                    Table TableByNode = _currentDatabase.Tables[NodeTagValues["Name"].ToString( ), NodeTagValues["Schema"].ToString( )];

                    AddExtendedProperty( TableByNode, NODETYPE_TABLE, name, value );

                }

                ShowinfoMessage( "Edit operation finished sucessfully." );

                //MessageBox.Show( "Edit operation finished sucessfully." );

            }

        }

        /// <summary>

        /// Update property name and value, behavior set with IsName flag.

        /// </summary>

        /// <param name="smoObject"></param>

        /// <param name="objectType"></param>

        /// <param name="name"></param>

        /// <param name="value"></param>

        /// <param name="isName"></param>

        private void UpdateProperty( SqlSmoObject smoObject, string objectType, string name, string value, bool isName)

        {

            //

            Smo.ExtendedPropertyCollection PropertiesCol = null;

            try

            {

                switch( objectType )

                {

                    case NODETYPE_DB:

                        Database d = (Database)smoObject;

                        PropertiesCol = d.ExtendedProperties;

                        //Update name or value based in isName flag.

                        if( isName )

                        {

                            //Recreate the property. parameter value is the new name.

                            if( PropertiesCol[name] != null )

                            {

                                string _value = PropertiesCol[name].Value.ToString( );

                                PropertiesCol[name].Drop( );

                                PropertiesCol.Add( new ExtendedProperty( smoObject, value, _value ) );

                            }

                        }

                        else

                        {

                            //Update property value.

                            if( PropertiesCol[name] != null )

                            {

                                PropertiesCol[name].Value = this.ParseValue( value );

                            }



                        }

                        d.Alter( );

                        break;

                    case NODETYPE_TABLE:

                        Table t = (Table)smoObject;

                        PropertiesCol = t.ExtendedProperties;

                        //Update name or value based in isName flag.

                        if( isName )

                        { 

                            //Remove and add new property. parameter value is the new name.

                            if( PropertiesCol[name] != null )

                            {

                                string _value = PropertiesCol[name].Value.ToString( );

                                PropertiesCol[name].Drop( );

                                PropertiesCol.Add( new ExtendedProperty( smoObject, value, _value ) );

                            }

                        }

                        else 

                        { 

                            //Update property value.

                            if( PropertiesCol[name] != null )

                            {

                                PropertiesCol[name].Value = this.ParseValue( value );

                            }

                            

                        }

                        t.Alter( );

                        break;

                    case NODETYPE_COLUMN:

                        Column c = (Column)smoObject;

                        PropertiesCol = c.ExtendedProperties;

                        //Update name or value based in isName flag.

                        if( isName )

                        {

                            //Remove and add new property. parameter value is the new name.

                            if( PropertiesCol[name] != null )

                            {

                               string _value = PropertiesCol[name].Value.ToString( );

                               PropertiesCol[name].Drop( );

                               PropertiesCol.Add( new ExtendedProperty( smoObject, value, _value ) );

                            }

                        }

                        else

                        {

                            //Update property value.

                            PropertiesCol[name].Value = this.ParseValue( value );

                        }

                        c.Alter( );

                        break;

                    default:

                        break;

                }

                PopulateExtendedProperties( PropertiesCol );

            }

            catch( Exception ex ) { ShowErrorMessage( ex.Message ); }

        }

        private void UpdatePropertyAtScope( string objectType, string name, string value, Scope scope, bool isName )

        {

            //If the scope is set to database scope.

            if( scope == Scope.DatabaseScope )

            {

                foreach( Table table in _currentDatabase.Tables )

                {

                    UpdateProperty( table, NODETYPE_TABLE, name, value, isName );

                }

                ShowinfoMessage( "Edit operation finished sucessfully." );

                //MessageBox.Show( "Edit operation finished sucessfully." );

            }

            //if the scope is set at scope to selected tables.

            if( scope == Scope.SelectedScope )

            {

                foreach( TreeNode node in DbTree.Nodes[0].Nodes.OfType<TreeNode>().Where( c=>c.Checked ).ToList() )

                {

                    Hashtable NodeTagValues = (Hashtable)node.Tag;

                    Table TableByNode = _currentDatabase.Tables[NodeTagValues["Name"].ToString( ), NodeTagValues["Schema"].ToString( )];

                    UpdateProperty( TableByNode, NODETYPE_TABLE, name, value, isName );

                }

                ShowinfoMessage( "Edit operation finished sucessfully." );

                //MessageBox.Show( "Edit operation finished sucessfully." );

            }



        }

        private void DeleteGlobalProperty( SqlSmoObject smoObject, string objectType, string name )

        {

            try

            {

                Smo.ExtendedPropertyCollection _propertiesCol = null;

                switch( objectType )

                {

                    case NODETYPE_DB:

                        Database DbObj = (Database)smoObject;

                        //

                        ProgressBar.Maximum = DbObj.Tables.Count; //ProgressBar

                        ProgressBar.Value = 0;

                        ProgressBar.Visible = true;



                        foreach( Table table in DbObj.Tables )

                        {

                            _propertiesCol = table.ExtendedProperties;

                            if( _propertiesCol[name] != null )

                            {

                                _propertiesCol[name].Drop( );

                                table.Alter( );

                            }

                            ProgressBar.PerformStep( );

                        }

                        //Rebuild properties gridview.

                        PopulateExtendedProperties( _currentTable.ExtendedProperties );

                        break;

                    case NODETYPE_TABLE:

                        Table tableObj = (Table)smoObject;

                        //

                        ProgressBar.Maximum = tableObj.Columns.Count; //ProgressBar

                        ProgressBar.Value = 0;

                        ProgressBar.Visible = true;

                        //

                        foreach( Column column in tableObj.Columns )

                        {

                            _propertiesCol = column.ExtendedProperties;

                            if( _propertiesCol[name] != null )

                            {

                                _propertiesCol[name].Drop( );

                                column.Alter( );

                            }

                            ProgressBar.PerformStep( );

                        }

                        //Rebuild properties gridview.

                        PopulateExtendedProperties( _currentColumn.ExtendedProperties );

                        break;

                    default:

                        break;

                }

                

            }

            catch( Exception ex ) { MessageBox.Show( ex.Message ); }

            finally { ProgressBar.Visible = false; }

        }

        private void AddORMExtendedForeingKeyProperties( )

        {

            try

            {

            }

            catch( Exception ex ) { throw; }

        }

        private void AddORMExtendedProperties( Scope scope )

        {

            try

            {

                switch(scope)

                {

                    case Scope.DatabaseScope:

                        //Add ORM database properties.

                        AddExtendedProperty( _currentDatabase, NODETYPE_DB, ORM_CONNECTIONSTRING, "" );

                        AddExtendedProperty( _currentDatabase, NODETYPE_DB, ORM_CONTEXT_IMPORTS, "" );

                        AddExtendedProperty( _currentDatabase, NODETYPE_DB, ORM_CONTEXT_NAME, "$DatabaseName$DataContext" );

                        AddExtendedProperty( _currentDatabase, NODETYPE_DB, ORM_CONTEXT_NAMESPACE, "$DatabaseName$.Data" );

                        AddExtendedProperty( _currentDatabase, NODETYPE_DB, ORM_ENTITY_IMPORTS, "" );

                        AddExtendedProperty( _currentDatabase, NODETYPE_DB, ORM_ENTITY_NAMESPACE, "$DatabaseName$.Entities" );

                        AddExtendedProperty( _currentDatabase, NODETYPE_DB, ORM_GENERATE_MULTIPLE_FILES, "true" );

                        AddExtendedProperty( _currentDatabase, NODETYPE_DB, ORM_INCLUDE_PREVENT_DEBUG, "false" );

                        AddExtendedProperty( _currentDatabase, NODETYPE_DB, ORM_IS_SEALED, "true" );

                        AddExtendedProperty( _currentDatabase, NODETYPE_DB, ORM_IS_SERIALIZABLE, "true" );

                        AddExtendedProperty( _currentDatabase, NODETYPE_DB, ORM_SAVEPATH, @"c:\temp" );

                        AddExtendedProperty( _currentDatabase, NODETYPE_DB, ORM_INCLUDE_CRUD_FUNCTIONS, "false" );

                        AddExtendedProperty( _currentDatabase, NODETYPE_DB, ORM_OVERRIDE_CRUD_OPERATIONS, "false" );



                        _currentDatabase.Alter( );

                        //

                        foreach( Table table in _currentDatabase.Tables )

                        {

                            AddExtendedProperty( table, NODETYPE_TABLE, ORM_CRUD_DELETE, "" );

                            AddExtendedProperty( table, NODETYPE_TABLE, ORM_CRUD_INSERT, "" );

                            AddExtendedProperty( table, NODETYPE_TABLE, ORM_CRUD_UPDATE, "" );

                            AddExtendedProperty( table, NODETYPE_TABLE, ORM_ENTITY_NAME, "$TableName$" );

                            AddExtendedProperty( table, NODETYPE_TABLE, ORM_ENTITY_PLURALNAME, "$TableName$s" );

                            AddExtendedProperty( table, NODETYPE_TABLE, ORM_ENTITY_IS_SEALED, "true" );

                            AddExtendedProperty( table, NODETYPE_TABLE, ORM_ENTITY_IS_SERIALIZED, "true" );

                        }

                        break;

                    default:

                        break;

                }

                ShowConfirmationMessage( "Extended properties added successfully." );

            }

            catch( Exception ex ) { throw; }

        }

        #endregion



        #region Database Objects Managment

        private void PopulateServers( )

        {

            XElement XServers = XElement.Load( "Servers.xml" );

            foreach( var item in XServers.Elements( "Server" ) )

            {

                ServersList.Items.Add( item.Attribute( "Name" ).Value );

            }

        }

        /// <summary>

        /// Fill de database drop down control.

        /// </summary>

        private void PopulateDatabases( )

        {

            try

            {

                ProgressBar.Maximum = _serverObject.Databases.Count; //ProgressBar

                ProgressBar.Value = 0;

                ProgressBar.Visible = true;

                //Clear control.

                DbList.Items.Clear( );

                foreach( Database item in _serverObject.Databases )

                {



                    //Fill control.

                    if( item.IsSystemObject )

                    {

                        if( ConfigValues.ViewSystemDatabases ) DbList.Items.Add( item.Name );

                    }

                    else

                    {

                        DbList.Items.Add( item.Name );

                    }

                    ProgressBar.PerformStep( );

                    //Thread.Sleep( 120 );

                }

                if( DbList.Items.Count > 0 )

                    DbList.SelectedIndex = 1;

            }

            catch( Exception ex ) { MessageBox.Show( ex.Message ); }

            finally

            {

                ProgressBar.Visible = false;

            }

        }

        private void PopulateTables( bool refresh )

        {

            try

            {

                //---------------------------------

                string DatabaseName = DbList.Text;

                //This step is necesary to load database objects. Cant use directly in loop.

                _currentDatabase = _serverObject.Databases[DatabaseName];

                //Check if request refresh

                if( refresh )

                {

                    _currentDatabase.Refresh( ); //Force refleshing database data.

                    _currentDatabase.Tables.Refresh( ); //Force refreshing database tables (names not data).

                }



                ProgressBar.Maximum = _currentDatabase.Tables.Count; //ProgressBar

                ProgressBar.Value = 0;

                ProgressBar.Visible = true;



                //Configure treeview.

                DbTree.Nodes.Clear( );

                TreeNode DatabaseNode = DbTree.Nodes.Add( DatabaseName );

                DatabaseNode.ImageIndex = 1;

                DatabaseNode.SelectedImageIndex = 1;

                //Set object tag with values used to check the node type.

                Hashtable DatabaseTagValues = new Hashtable( );

                DatabaseTagValues.Add( "Type", NODETYPE_DB ); //Node type = database.

                DatabaseTagValues.Add( "Name", DatabaseName ); //Table name.

                DatabaseNode.Tag = DatabaseTagValues;

                

                //========================================

                //  Add database tables nodes.

                //========================================

                Hashtable TableTagValues;

                foreach( Table table in _currentDatabase.Tables )

                {

                    //Check if request refresh

                    if( refresh )

                    {

                        table.Refresh( ); //Force refreshing only table data.

                        table.Columns.Refresh( ); //Force refreshing table columns (names not properties).                   



                    }

                    //Check if it is a system table.

                    if( table.IsSystemObject )

                    {

                        //If system tables allowed.

                        if( ConfigValues.ViewSystemTables )

                        {

                            //Add node.

                            TreeNode TableNode = DatabaseNode.Nodes.Add( table.Schema + "." + table.Name );

                            TableNode.ImageIndex = 5;

                            TableNode.SelectedImageIndex = 5;

                            //Add virtually node to force show expand icon. It remove when populate columns.

                            TableNode.Nodes.Add( " " );



                            //Set object tag with values used to check the node values.

                            TableTagValues = new Hashtable( );

                            TableTagValues.Add( "Type", NODETYPE_TABLE ); //Node type = Table.

                            TableTagValues.Add( "Schema", table.Schema ); //Schema name of table.

                            TableTagValues.Add( "Name", table.Name ); //Table name.

                            TableNode.Tag = TableTagValues;

                        }

                    }

                    else

                    {

                        //Add node.

                        TreeNode TableNode = DatabaseNode.Nodes.Add( table.Schema + "." + table.Name );

                        TableNode.ImageIndex = 5;

                        TableNode.SelectedImageIndex = 5;

                        //Add virtually node to force show expand icon. It remove when populate columns.

                        TableNode.Nodes.Add( " " );



                        //Set object tag with values used to check the node values.

                        TableTagValues = new Hashtable( );

                        TableTagValues.Add( "Type", NODETYPE_TABLE ); //Node type = Table.

                        TableTagValues.Add( "Schema", table.Schema ); //Schema name of table.

                        TableTagValues.Add( "Name", table.Name ); //Table name.

                        TableNode.Tag = TableTagValues;



                    }

                    //Thread.Sleep( 120 );

                    ProgressBar.PerformStep( );

                }

                DatabaseNode.Expand( );

            }

            catch( Exception ex ) { MessageBox.Show( ex.Message ); }

            finally

            {

                ProgressBar.Visible = false;

            }



        }

        private void PopulateColumns( TreeNode tableNode, bool refresh )

        {

            try

            {

                tableNode.Nodes.Clear( );

                Hashtable TableTag = (Hashtable)tableNode.Tag;

                Table t = _currentDatabase.Tables[TableTag["Name"].ToString( ), TableTag["Schema"].ToString( )];

                foreach( Column column in t.Columns )

                {

                    //Check if request refresh

                    if( refresh )

                    {

                        column.Refresh( ); //Force refreshing column data.

                    }

                    //Add node, set tag value to  nodetype "Column"

                    TreeNode ColumnNode = tableNode.Nodes.Add( column.Name );

                    ColumnNode.ToolTipText = column.DataType.ToString( );

                    if( column.InPrimaryKey )

                    {

                        ColumnNode.ImageIndex = 3;

                        ColumnNode.SelectedImageIndex = 3;

                    }

                    else

                    {

                        ColumnNode.ImageIndex = 0;

                        ColumnNode.SelectedImageIndex = 0;

                    }



                    //Set the tag object.

                    Hashtable ColumnTag = new Hashtable( ); //Tag object host a hashtable.

                    ColumnTag.Add( "Type", NODETYPE_COLUMN ); //Node type Column.

                    ColumnTag.Add( "Name", column.Name ); //Column name.

                    ColumnNode.Tag = ColumnTag;



                }

            }

            catch( SqlException ex ) { MessageBox.Show( ex.Message ); }

        }

        #endregion



        #region LinqToSql Generator



        #region Helpers

        public Table GetReferencedTable( ForeignKey foreignKey )

        {

            return _currentDatabase.Tables[foreignKey.ReferencedTable, foreignKey.ReferencedTableSchema];

        }

        public string GetEntityName( Table table )

        {

            return table.ExtendedProperties["ORM_EntityName"].Value.ToString( );

        }

        public string GetEntityPluralName( Table table )

        {

            return table.ExtendedProperties["ORM_EntityPluralName"].Value.ToString( );

        }

        /// <summary>

        /// Return the reference name extended propertie of the column passed.

        /// </summary>

        /// <param name="column"></param>

        /// <returns></returns>

        public string GetColumnReferenceName( Column column )

        {

            if( column.ExtendedProperties["ORM_ReferenceName"] != null )

            {

                return column.ExtendedProperties["ORM_ReferenceName"].Value.ToString( );

            }

            else

            {

                return null;

            }

           

        }

        public string GetColumnReferencePluralName( Column column )

        {

            if( column.ExtendedProperties["ORM_ReferencePluralName"] != null )

            {

                return column.ExtendedProperties["ORM_ReferencePluralName"].Value.ToString( );

            }

            else

            {

                return null;

            }



        }

        public string GetParsedEnumColumnName( Column column )

        {

            return column.Name.Replace( "Enum", "" );

        }

        public string ParseForeignkeyName( string value )

        {

            return value.Replace( "FK_", "" );

        }

        public string ParseParameterName( string name )

        {

            return name.Replace( "@", "" );

        }

        /// <summary>

        /// Get the equivalent clr type of Sql data type.

        /// </summary>

        /// <param name="column"></param>

        /// <returns></returns>

        public string GetClrType( Column column )

        {

            string ClrType = string.Empty;

            if( column != null )

            {

                switch( column.DataType.SqlDataType.ToString( ).ToLower( ) )

                {

                    case "xml":

                        ClrType = "XElement";

                        break;

                    case "binary":

                    case "varbinary":

                    case "varbinarymax":

                    case "image":

                        ClrType = "System.Data.Linq.Binary";

                        break;

                    case "varchar":

                    case "varcharmax":

                    case "nvarchar":

                    case "nvarcharmax":

                    case "char":

                    case "nchar":

                    case "text":

                    case "ntext":

                        ClrType = "string";

                        break;

                    case "timestamp":

                        ClrType = "byte[]";

                        break;

                    case "datetime":

                    case "date":

                    case "smalldatetime":

                        ClrType = "System.DateTime";

                        break;

                    case "datetimeoffset":

                        ClrType = "System.DateTimeOffset";

                        break;

                    case "decimal":

                    case "money":

                    case "smallmoney":

                    case "numeric":

                        ClrType = "decimal";

                        break;

                    case "float":

                    case "real":

                        ClrType = "double";

                        break;

                    case "bigint":

                        ClrType = "long";

                        break;

                    case "int":

                        ClrType = "int";

                        break;

                    case "smallint":

                        ClrType = "short";

                        break;

                    case "tinyint":

                        ClrType = "byte";

                        break;

                    case "bit":

                        ClrType = "bool";

                        break;

                    case "uniqueidentifier":

                        ClrType = "System.Guid";

                        break;

                    case "time":

                        ClrType = "System.TimeSpan";

                        break;

                    default:

                        ClrType = "System.Object";

                        break;

                }

            }

            return ClrType;

        }

        /// <summary>

        /// Parse Sql data types to clr types.

        /// </summary>

        /// <param name="column"></param>

        /// <returns></returns>

        public string GetNullableClrType( Column column )

        {



            string ClrType = string.Empty;

            if( column != null )

            {

                switch( column.DataType.SqlDataType.ToString( ).ToLower( ) )

                {

                    case "xml":

                        ClrType = "XElement";

                        break;

                    case "binary":

                    case "varbinary":

                    case "varbinarymax":

                    case "image":

                        ClrType = "System.Data.Linq.Binary";

                        break;

                    case "varchar":

                    case "varcharmax":

                    case "nvarchar":

                    case "nvarcharmax":

                    case "char":

                    case "nchar":

                    case "text":

                    case "ntext":

                        ClrType = "string";

                        break;

                    case "timestamp":

                        ClrType = "byte[]";

                        break;

                    case "datetime":

                    case "date":

                    case "smalldatetime":

                        ClrType = "System.DateTime";

                        break;

                    case "datetimeoffset":

                        ClrType = "System.DateTimeOffset";

                        break;

                    case "decimal":

                    case "money":

                    case "smallmoney":

                    case "numeric":

                        ClrType = "decimal";

                        break;

                    case "float":

                    case "real":

                        ClrType = "double";

                        break;

                    case "bigint":

                        ClrType = "long";

                        break;

                    case "int":

                        ClrType = "int";

                        break;

                    case "smallint":

                        ClrType = "short";

                        break;

                    case "tinyint":

                        if( column.Name.Contains( "Enum" ) )

                        {

                            ClrType = column.Name.Replace("Enum","") + "Enum";

                        }

                        else

                        {

                            ClrType = "Byte";

                        }

                        break;

                    case "bit":

                        ClrType = "bool";

                        break;

                    case "uniqueidentifier":

                        ClrType = "System.Guid";

                        break;

                    case "time":

                        ClrType = "System.TimeSpan";

                        break;

                    default:

                        ClrType = "System.Object";

                        break;

                }

                if( ( !column.Nullable ) || ClrType == "string" || ClrType == "System.Object" || ClrType == "XElement" || ClrType == "System.Data.Linq.Binary" )

                    return ClrType;

                else

                    //return "System.Nullable<" + systemType + ">";

                    return string.Format( "System.Nullable<{0}>", ClrType );

            }

            else

            {

                return null;

            }

        }

        /// <summary>

        /// Not used.

        /// </summary>

        /// <param name="column"></param>

        /// <returns></returns>

        public string GetDbTypeAttribute( Column column )

        {

            string columnDbType = column.DataType.ToString( );

            //

            if( column.DataType.ToString().Contains( "char" ) )

                columnDbType += String.Format( "({0})", column.DataType.MaximumLength== -1 ? "MAX" : column.DataType.MaximumLength.ToString( ) );

            //

            if( !column.Nullable ) { columnDbType += " NOT NULL"; }

            //

            if( column.Identity)

            {

               columnDbType += " IDENTITY";

            }

            return columnDbType;

        }

        public string GetSimpleDbType( Column column )

        {

            string columnDbType = column.DataType.ToString( );

            //

            if( column.DataType.ToString( ).Contains( "char" ) )

                columnDbType += String.Format( "({0})", column.DataType.MaximumLength == -1 ? "MAX" : column.DataType.MaximumLength.ToString( ) );

            return columnDbType;

        }

        public string GetColumnKeyList(ForeignKeyColumnCollection keys )

        {

            List<string> list = new List<string>( );

            foreach( Column column in keys )

                list.Add( column.Name );

            return String.Join( ", ", list.ToArray( ) );

        }

        public string Indent( string value, int level )

        {

            string Result = new String( ' ', level );

            return string.Concat( Result, value );

        }

        #endregion  



        /// <summary>

        /// Retrieve the ContextNamespace value.

        /// </summary>

        /// <returns></returns>

        public string GetContextNamespace( )

        {

            if( _currentDatabase.ExtendedProperties[ORM_CONTEXT_NAMESPACE] != null )

            {

                return _currentDatabase.ExtendedProperties[ORM_CONTEXT_NAMESPACE].Value.ToString( );

            }

            else

            {

                //If not exist the database property uses general preference value.

                return Properties.Settings.Default.LqGenContextNamespace;

            }

        }

        /// <summary>

        /// Retrieve the context name extended property value.

        /// </summary>

        /// <returns></returns>

        public string GetContextName( )

        {

            if( _currentDatabase.ExtendedPr…

Large files files are truncated, but you can click here to view the full file