Thursday, September 24, 2015

ORM mappers

There are a few ORM mappers available other than the Enitty Framework, however I have tested none of them yet.

http://stackoverflow.com/questions/47239/how-can-i-generate-database-tables-from-c-sharp-classes

https://github.com/StackExchange/dapper-dot-net

http://mybatis.github.io/mybatis-3/dynamic-sql.html

http://www.codeproject.com/Articles/27830/SQL-Class-Shell-Generator

I have modified the class in stackoverflow post #1 as below

public class TableClass
    {
        static int AliasIndex = 0;
        private List<ColumnInfo> _columnInfo = new List<ColumnInfo>();
        private string _className = String.Empty;
        private string _tableAlias = String.Empty;
        private string _fkColumnName = String.Empty;

        private Dictionary<Type, String> dataMapper
        {
            get
            {
                // Add the rest of your CLR Types to SQL Types mapping here
                Dictionary<Type, String> dataMapper = new Dictionary<Type, string>();
                dataMapper.Add(typeof(int), "BIGINT");
                dataMapper.Add(typeof(string), "NVARCHAR(MAX)");
                dataMapper.Add(typeof(bool), "BIT");
                dataMapper.Add(typeof(DateTime), "DATETIME");
                dataMapper.Add(typeof(float), "FLOAT");
                dataMapper.Add(typeof(decimal), "DECIMAL(18,0)");
                dataMapper.Add(typeof(Guid), "UNIQUEIDENTIFIER");

                return dataMapper;
            }
        }

        public List<ColumnInfo> Fields
        {
            get { return this._columnInfo; }
            set { this._columnInfo = value; }
        }

        public string ClassName
        {
            get { return this._className; }
            set { this._className = value; }
        }

        public TableClass(Type t)
        {
            this._className = t.Name;
            AliasIndex++;
            _tableAlias = this.ClassName + AliasIndex;
            foreach (FieldInfo p in t.GetFields())
            {
                ColumnInfo field = null;
                if (dataMapper.ContainsKey(p.FieldType))
                {
                    field = new ColumnInfo
                    {
                        Key = p.Name,
                        SystemType = p.FieldType,
                        IsForeignKey = false
                    };
                }
                else
                {
                    field = new ColumnInfo
                    {
                        Key = p.Name + "ID",
                        SystemType = p.FieldType,
                        IsForeignKey = true,
                        Table = new TableClass(p.FieldType.UnderlyingSystemType)
                    };

                }
                this.Fields.Add(field);
            }
        }

        public TableClass(Type t, string ForeignKeyColumnName)
            : this(t)
        {
            this._fkColumnName = ForeignKeyColumnName;
        }

        public string CreateTableScript()
        {
            StringBuilder script = new StringBuilder();
            StringBuilder scriptsToAppend = new StringBuilder();

            script.AppendLine("CREATE TABLE " + this.ClassName);
            script.AppendLine("(");
            List<string> fields = new List<string>();
            fields.Add("\t ID UNIQUEIDENTIFIER");

            foreach (ColumnInfo col in this._columnInfo)
            {
                if (!col.IsForeignKey)
                {
                    fields.Add("\t " + col.Key + " " + dataMapper[col.SystemType]);
                }
                else
                {
                    fields.Add("\t " + col.Key + "ID UNIQUEIDENTIFIER");
                    TableClass tc = col.Table;
                    scriptsToAppend.Append(tc.CreateTableScript());
                    scriptsToAppend.AppendLine(string.Empty);
                    // We have a FK Relationship!
                    scriptsToAppend.Append("GO");
                    scriptsToAppend.AppendLine(string.Empty);
                    scriptsToAppend.Append("ALTER TABLE " + this.ClassName + " WITH NOCHECK");
                    scriptsToAppend.AppendLine(string.Empty);
                    scriptsToAppend.Append("ADD CONSTRAINT FK_" + tc.ClassName + "_" + this.ClassName + " FOREIGN KEY (" + col.Key + "ID) REFERENCES " + tc.ClassName + "(ID)");
                    scriptsToAppend.AppendLine(string.Empty);
                    scriptsToAppend.Append("GO");
                    scriptsToAppend.AppendLine(string.Empty);
                }
            }
            script.Append(string.Join(", ", fields));
            script.Append(Environment.NewLine);
            script.AppendLine(")");
            script.Append(scriptsToAppend.ToString());
            return script.ToString();
        }

        public string GetSelectColumnsScript()
        {

            StringBuilder script = new StringBuilder();
            List<TableClass> ChildTables = new List<TableClass>();
            List<string> fields = new List<string>();
            fields.Add("\t " + _tableAlias + ".ID");
            foreach (ColumnInfo col in _columnInfo)
            {
                if (!col.IsForeignKey)
                {
                    fields.Add("\t " + _tableAlias + "." + col.Key);
                }
                else
                {
                    TableClass tc = col.Table;
                    script.Append(tc.GetSelectColumnsScript());
                    script.Append(Environment.NewLine);
                }
            }
            script.Append(string.Join(", ", fields));
            script.Append(Environment.NewLine);
            return script.ToString();
        }

        public string CreateSelectScript()
        {
            StringBuilder script = new StringBuilder();
            List<string> fields = new List<string>();
            script.Append("SELECT ");
            script.Append(Environment.NewLine);
            script.AppendLine(this.GetSelectColumnsScript());
            script.AppendLine("FROM " + this.ClassName + " AS " + _tableAlias);
            foreach (ColumnInfo col in _columnInfo)
            {
                if (col.IsForeignKey)
                {
                    TableClass tc = col.Table;
                    script.Append("INNER JOIN " + tc.ClassName + " AS " + tc._tableAlias);
                    script.Append("    ON " + _tableAlias + "." + tc.ClassName + "ID = " + tc._tableAlias + ".ID");
                    script.Append(Environment.NewLine);
                }
            }
            return script.ToString();
        }
    }
 

Sqlformatter.cs
protected void AddAlias(AliasedExpression aliasEx)
        {
            string name;
            if (!this.aliases.TryGetValue(aliasEx.Alias, out name))
            {
                name = ((IQToolkit.Data.Common.TableExpression)(aliasEx)).Name + this.aliases.Count;
                this.aliases.Add(aliasEx.Alias, name);
            }
        }
protected virtual void AddAliases(Expression expr)
             if (ax != null)
            {
                this.AddAlias(ax);
            }
 

Tuesday, September 15, 2015

Creating class properties dynamically

The class below is exacly what I was looking for when I realised that I had to code too many classes in an Entity Framework project which uses dependency injection.

https://weblog.west-wind.com/posts/2012/Feb/08/Creating-a-dynamic-extensible-C-Expando-Object

Also, the blog below explains implementation of dynamic property accessor

http://omegacoder.com/?p=650 

Secure micro services using jwt and ocelot

  Secure Microservices Using JWT With Ocelot in .NET Core (code-maze.com)