MESProductionWeb/AntORM/Ant.ORM/Tool/SqlHelp.cs

using Ant.Common;
using Ant.Data;
using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Reflection;
using System.Text;

namespace Ant.ORM
{
    /// <summary>
    /// 自定义SQL执行类
    /// </summary>
    public class SqlHelp
    {

        static readonly Dictionary<Type, DbType> typeMap;
        /// <summary>
        /// 
        /// </summary>
        static SqlHelp()
        {
            typeMap = new Dictionary<Type, DbType>();
            typeMap[typeof(byte)] = DbType.Byte;
            typeMap[typeof(sbyte)] = DbType.SByte;
            typeMap[typeof(short)] = DbType.Int16;
            typeMap[typeof(ushort)] = DbType.UInt16;
            typeMap[typeof(int)] = DbType.Int32;
            typeMap[typeof(uint)] = DbType.UInt32;
            typeMap[typeof(long)] = DbType.Int64;
            typeMap[typeof(ulong)] = DbType.UInt64;
            typeMap[typeof(float)] = DbType.Single;
            typeMap[typeof(double)] = DbType.Double;
            typeMap[typeof(decimal)] = DbType.Decimal;
            typeMap[typeof(bool)] = DbType.Boolean;
            typeMap[typeof(string)] = DbType.String;
            typeMap[typeof(char)] = DbType.StringFixedLength;
            typeMap[typeof(Guid)] = DbType.Guid;
            typeMap[typeof(DateTime)] = DbType.DateTime;
            typeMap[typeof(DateTimeOffset)] = DbType.DateTimeOffset;
            typeMap[typeof(TimeSpan)] = DbType.Time;
            typeMap[typeof(byte[])] = DbType.Binary;
            typeMap[typeof(byte?)] = DbType.Byte;
            typeMap[typeof(sbyte?)] = DbType.SByte;
            typeMap[typeof(short?)] = DbType.Int16;
            typeMap[typeof(ushort?)] = DbType.UInt16;
            typeMap[typeof(int?)] = DbType.Int32;
            typeMap[typeof(uint?)] = DbType.UInt32;
            typeMap[typeof(long?)] = DbType.Int64;
            typeMap[typeof(ulong?)] = DbType.UInt64;
            typeMap[typeof(float?)] = DbType.Single;
            typeMap[typeof(double?)] = DbType.Double;
            typeMap[typeof(decimal?)] = DbType.Decimal;
            typeMap[typeof(bool?)] = DbType.Boolean;
            typeMap[typeof(char?)] = DbType.StringFixedLength;
            typeMap[typeof(Guid?)] = DbType.Guid;
            typeMap[typeof(DateTime?)] = DbType.DateTime;
            typeMap[typeof(DateTimeOffset?)] = DbType.DateTimeOffset;
            typeMap[typeof(TimeSpan?)] = DbType.Time;
            typeMap[typeof(Object)] = DbType.Object;
        }
        /// <summary>
        /// 添加映射
        /// </summary>
        /// <param name="type"></param>
        /// <param name="dbType"></param>
        public static void AddTypeMap(Type type, DbType dbType)
        {
            typeMap[type] = dbType;
        }

        internal const string LinqBinary = "System.Data.Linq.Binary";
        /// <summary>
        /// 通过C#的实体属性的类型返回数据库中的类型
        /// </summary>
        /// <param name="type"></param>
        /// <param name="name"></param>
        /// <returns></returns>
        internal static DbType LookupDbType(Type type, string name)
        {
            DbType dbType;
            var nullUnderlyingType = Nullable.GetUnderlyingType(type);
            if (nullUnderlyingType != null) type = nullUnderlyingType;
            if (type.IsEnum && !typeMap.ContainsKey(type))
            {
                type = Enum.GetUnderlyingType(type);
            }
            if (typeMap.TryGetValue(type, out dbType))
            {
                return dbType;
            }
            if (type.FullName == LinqBinary)
            {
                return DbType.Binary;
            }
            if (typeof(IEnumerable).IsAssignableFrom(type))
            {
                return (DbType)(-1);
            }
            throw new NotSupportedException(string.Format("类型{1}的成员{0}不能用作参数值", name, type));
        }

        /// <summary>
        /// 获取DataTable数据
        /// </summary>
        /// <param name="mod"></param>
        /// <returns></returns>
        public static ResponseModel GetDataTable(RequestItemSqlModel mod)
        {
            DataAccess db = mod.db; ResponseModel res = new ResponseModel(); QueryCommand cmd = new QueryCommand();
            db = (db.IsNull()) ? DataAccessFactory.GetWriteDataDefault : mod.db;//创建数据库连接
            if (db.IsNull())
            {
                res.IsSuccess = false;
                res.Message = "数据库配置不正确";
                return res;
            }
            try
            {
                res = GetSQLParameter(cmd, mod);
                var dtt = db.ExecuteDataTable(res.StrSql, cmd.Parameters);
                res.DataTable = dtt;
                res.IsSuccess = dtt.Rows.Count > 0;
                res.Message = (res.IsSuccess) ? "返回成功" : "没有查询到数据";
            }
            catch (Exception ex)
            {
                res.IsSuccess = false;
                res.Message = ex.Message;
            }

            return res;
        }

        /// <summary>
        /// 获取实体对象
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="mod"></param>
        /// <returns></returns>
        public ResponseModel GetModelList<T>(RequestItemSqlModel mod)
        {
            DataAccess db = mod.db; ResponseModel res = new ResponseModel(); QueryCommand cmd = new QueryCommand();
            db = (db.IsNull()) ? DataAccessFactory.GetWriteDataDefault : mod.db;//创建数据库连接
            if (db.IsNull())
            {
                res.IsSuccess = false;
                res.Message = "数据库配置不正确";
                return res;
            }
            try
            {
                res = GetSQLParameter(cmd, mod);
                var drr = db.ExecuteDataReader(res.StrSql, cmd.Parameters);
                var list = DataToModel.Dr2EnList<T>(drr);
                res.ResultModel = list;
                res.IsSuccess = list.Count > 0;
                res.Message = (res.IsSuccess) ? "返回成功" : "没有查询到数据";
            }
            catch (Exception ex)
            {
                res.IsSuccess = false;
                res.Message = ex.Message;
            }
            finally
            {
                if (!db.IsNull()) db.Close();
            }

            return res;
        }

        /// <summary>
        /// 获取数据库表返回的第一个值
        /// </summary>
        /// <param name="comText">执行的sql查询</param>
        /// <returns></returns>
        public ResponseModel GetFirst(RequestItemSqlModel mod)
        {
            DataAccess db = mod.db; ResponseModel res = new ResponseModel(); QueryCommand cmd = new QueryCommand();
            try
            {
                if (db.ExecuteScalar(mod.StrSql) != null)
                {
                    var obj = db.ExecuteScalar(mod.StrSql);
                }
                else
                {
                    return res;
                }
            }
            catch (Exception ex)
            {

                return null;
            }
            finally
            {
                db.Close();
            }
            return res;
        }




        /// <summary>
        /// 获取参数和SQL语句
        /// </summary>
        /// <param name="cmd"></param>
        /// <param name="mod"></param>
        /// <returns></returns>
        internal static ResponseModel GetSQLParameter(QueryCommand cmds, RequestItemSqlModel mods)
        {
            ResponseModel res = new ResponseModel();
            cmds.CommandText = mods.StrSql; PropertyInfo[] pInfos;
            var para = mods.Parameter;
            if (para.IfNotNull())
            {
                Type t = para.GetType();
                pInfos = (!object.Equals(para, null)) ? para.GetType().GetProperties() : t.GetProperties();
                foreach (PropertyInfo pinfo in pInfos)
                {
                    if (!Object.Equals(para, null))//enty不等空时，并进行赋值
                    {
                        object obj = pinfo.GetValue(para, null);//列的值
                        if (!Object.Equals(obj, null))
                        {
                            var dbtype = pinfo.PropertyType;
                            QueryParameter parameter = new QueryParameter();
                            parameter.ParameterName = "@" + pinfo.Name;
                            parameter.DbType = GetDbType(dbtype);
                            parameter.Value = obj;
                            cmds.Parameters.Add(parameter);
                        }
                    }
                }
            }
            System.Diagnostics.StackFrame[] sfs = mods.newSt.NewSt.GetFrames();
            string sqlWithComment = string.Empty;
            if (sfs.Count() > 0)
            {
                System.Diagnostics.StackFrame infos = sfs[0];
                System.Reflection.MethodBase mb = infos.GetMethod();
                var filename = infos.GetFileName();
                sqlWithComment = string.Format(ConstSql.SQL_WITH_COMMENT, "自定义SQL执行", mods.newSt.Author, mods.newSt.SqlDesc, filename, mb.Name, mb.DeclaringType.FullName);
            }
            res.StrSql = string.Format(@"{0}{1}", cmds.CommandText, sqlWithComment);
            return res;
        }

        /// <summary>
        /// Type类型转DbType
        /// </summary>
        /// <param name="dbtype"></param>
        /// <returns></returns>
        internal static DbType GetDbType(Type dbtype)
        {
            DbType dts;
            if (dbtype == typeof(Int64) || dbtype == typeof(Int64?))
            {
                dts = DbType.Int64; return dts;
            }
            else if (dbtype == typeof(Int32) || dbtype == typeof(Int32?))
            {
                dts = DbType.Int32; return dts;
            }
            else if (dbtype == typeof(DateTime) || dbtype == typeof(DateTime?))
            {
                dts = DbType.DateTime; return dts;
            }
            else if (dbtype == typeof(Boolean) || dbtype == typeof(Boolean?))
            {
                dts = DbType.Boolean; return dts;
            }
            else if (dbtype == typeof(Single) || dbtype == typeof(Single?))
            {
                dts = DbType.Single; return dts;
            }
            else if (dbtype == typeof(Double) || dbtype == typeof(Double?))
            {
                dts = DbType.Double; return dts;
            }
            else if (dbtype == typeof(Decimal) || dbtype == typeof(Decimal?))
            {
                dts = DbType.Decimal; return dts;
            }
            else if (dbtype == typeof(Guid) || dbtype == typeof(Guid?))
            {
                dts = DbType.Guid; return dts;
            }
            else if (dbtype == typeof(Byte) || dbtype == typeof(Byte?))
            {
                dts = DbType.Byte; return dts;
            }
            else if (dbtype == typeof(SByte) || dbtype == typeof(SByte?))
            {
                dts = DbType.Byte; return dts;
            }
            else if (dbtype == typeof(Int16) || dbtype == typeof(Int16?))
            {
                dts = DbType.Int16; return dts;
            }
            else if (dbtype == typeof(Char) || dbtype == typeof(Char?))
            {
                dts = DbType.String; return dts;
            }
            else
                dts = SQL.DataType.GetDbType(dbtype);
            return dts;
        }


        /// <summary>
        /// C#数据类型转换为DbType这种方法类型不带?可以用这种,例如(Int32？)就不适用
        /// </summary>
        /// <param name="t"></param>
        /// <returns></returns>
        internal static DbType TypeToDbType(Type t)
        {
            DbType dbt;
            try
            {
                dbt = (DbType)Enum.Parse(typeof(DbType), t.Name);
            }
            catch
            {
                dbt = DbType.Object;
            }
            return dbt;
        }


    }
}