FCC/Applications/HengshanEpsWrapper/SpsDbManager/DbContextExtensions.cs

using Microsoft.EntityFrameworkCore;
using MySql.Data.MySqlClient;
using System;
using System.Collections.Generic;
using System.Collections.ObjectModel;
using System.Data;
using System.Data.Common;
using System.Linq;
using System.Reflection;
using System.Text;

namespace Dfs.WayneChina.SpsDbManager
{
    public static class DbContextExtensions
    {
        public static MySqlCommand LoadStoredProc(this DbContext context, string storedProcName)
        {
            var cmd = context.Database.GetDbConnection().CreateCommand();
            cmd.CommandText = storedProcName;
            cmd.CommandType = CommandType.StoredProcedure;
            return (MySqlCommand)cmd;
        }

        public static MySqlCommand WithSqlParam(this MySqlCommand cmd, string paramName, object paramValue)
        {
            if (string.IsNullOrEmpty(cmd.CommandText))
            {
                throw new InvalidOperationException("Call LoadStoredProc before using this method");
            }

            var param = cmd.CreateParameter();
            param.ParameterName = paramName;
            param.Value = paramValue;
            cmd.Parameters.Add(param);
            return cmd;
        }

        public static MySqlCommand WithSqlParam(this MySqlCommand cmd, MySqlDbType dbType, string paramName, object paramValue)
        {
            if (string.IsNullOrEmpty(cmd.CommandText))
            {
                throw new InvalidOperationException("Call LoadStoredProc before using this method");
            }

            var param = cmd.CreateParameter();
            param.ParameterName = paramName;
            param.MySqlDbType = dbType;
            param.Value = paramValue;
            cmd.Parameters.Add(param);
            return cmd;
        }


        private static IList<T> MapToList<T>(this MySqlDataReader dr)
        {
            var objList = new List<T>();
            var props = typeof(T).GetRuntimeProperties();

            var columnSchema = dr.GetCustomColumnSchema();
            var colMapping = columnSchema//dr.GetColumnSchema()
                .Where(x => props.Any(y => y.Name.ToLower() == x.ColumnName.ToLower()))
                .ToDictionary(key => key.ColumnName.ToLower());

            if (dr.HasRows)
            {
                while (dr.Read())
                {
                    T obj = Activator.CreateInstance<T>();

                    foreach (var prop in props)
                    {
                        var mappedColumn = colMapping[prop.Name.ToLower()];
                        var ordinal = colMapping[prop.Name.ToLower()].ColumnOrdinal.Value;
                        var val = dr.GetValue(ordinal - 1);
                        prop.SetValue(obj, val == DBNull.Value ? null : val);
                    }
                    objList.Add(obj);
                }
            }
            return objList;
        }

        public static int ExecuteStoredProc(this MySqlCommand command)
        {
            using (command)
            {
                try
                {
                    if (command.Connection.State == ConnectionState.Closed)
                        command.Connection.Open();

                    return command.ExecuteNonQuery();
                }
                catch(MySqlException ex)
                {
                    Console.WriteLine(ex.ToString());
                }
                finally
                {
                    command.Connection.Close();
                }

                return -1;
            }
        }

        public static IList<T> ExecuteStoredProc<T>(this MySqlCommand command)
        {
            using (command)
            {
                try
                {
                    if (command.Connection.State == ConnectionState.Closed)
                        command.Connection.Open();

                    using (MySqlDataReader reader = command.ExecuteReader())
                    {
                        return reader.MapToList<T>();
                    }
                }
                catch(MySqlException ex)
                {
                    Console.WriteLine(ex.ToString());
                }
                finally
                {
                    command.Connection.Close();
                }

                return null;
            }
        }

        /// <summary>
        /// Custom column schema to support lack of native method.
        /// </summary>
        private static ReadOnlyCollection<DbColumn> GetCustomColumnSchema(this DbDataReader reader)
        {
            IList<DbColumn> columnSchema = new List<DbColumn>();
            DataTable schemaTable = reader.GetSchemaTable();

            DataColumnCollection schemaTableColumns = schemaTable.Columns;
            foreach (DataRow row in schemaTable.Rows)
            {
                DbColumn dbColumn = new DataRowDbColumn(row, schemaTableColumns);

                if (!columnSchema.Any(c => c.ColumnName == dbColumn.ColumnName))
                {
                    columnSchema.Add(dbColumn);
                }
            }

            ReadOnlyCollection<DbColumn> readOnlyColumnSchema = new ReadOnlyCollection<DbColumn>(columnSchema);
            return readOnlyColumnSchema;
        }
    }

    internal class ColumnNameComparer : IEqualityComparer<string>
    {
        public bool Equals(string x, string y)
        {
            return x.Equals(y);
        }

        public int GetHashCode(string obj)
        {
            return obj.GetHashCode();
        }
    }
}