EF core执行SQl命令

EF Core中取消了EF中十分好用的SqlQuery方法

但是如果想用的话我们可以自己封装

面向CV编程吧

public static  class DbContextExtensions
    {
        public static dynamic pagin<T>(DatabaseFacade facade, string SortField,string SqlBody,  string SqlWhere, params object [] ParameterArr)  where T : class, new()
        {
            string sql = "";
            if (SqlWhere == null)
            {
                sql = $"select * from (select  * from (select row_number() OVER (ORDER BY {SortField}) rowindex, {SqlBody})info limit @rowindex , @pagecount)t  ";
            }
            else
            {
                sql = $"select * from (select  * from (select row_number() OVER (ORDER BY {SortField}) rowindex, {SqlBody}  where {SqlWhere} )info limit @rowindex , @pagecount)t ";
            }
            return SqlQuery<T>(facade, sql, ParameterArr);
        }
        //分页
        public static dynamic pagin(DatabaseFacade facade, string SortField, string SqlBody,string SqlWhere, params object[] ParameterArr) 
        {
            string sql ="";
            if (SqlWhere == null)
            {
                sql = $"select * from (select  * from (select row_number() OVER (ORDER BY {SortField}) rowindex, {SqlBody})info limit @rowindex , @pagecount)t  ";
            }
            else
            {
                sql = $"select * from (select  * from (select row_number() OVER (ORDER BY {SortField}) rowindex, {SqlBody}  where {SqlWhere} )info limit @rowindex , @pagecount)t ";
            }
            return SqlQuery(facade, sql, ParameterArr);
        }
        //处理参数
        private static void CombineParams(ref DbCommand command, params object[] parameters)
        {
            if (parameters != null)
            {
                foreach (MySqlParameter parameter in parameters)
                {
                    if (!parameter.ParameterName.Contains("@"))
                        parameter.ParameterName = $"@{parameter.ParameterName}";
                    command.Parameters.Add(parameter);
                }
            }
        }
        //创建SQL命令
        private static DbCommand CreateCommand(DatabaseFacade facade, string sql, out DbConnection dbConn, params object[] parameters)
        {
            DbConnection conn = facade.GetDbConnection();
            dbConn = conn;
            if (conn.State == ConnectionState.Open)
            {
                conn.Close();
            }
            conn.Open();
            DbCommand cmd = conn.CreateCommand();
            if (facade.IsMySql())
            {
                cmd.CommandText = sql;
                CombineParams(ref cmd, parameters);
            }
            return cmd;
        }
        //查询
        public static DataTable SqlQuery( DatabaseFacade facade, string sql, params object[] parameters)
        {
            DbCommand cmd = CreateCommand(facade, sql, out DbConnection conn, parameters);
            DbDataReader reader = cmd.ExecuteReader();
            DataTable dt = new DataTable();
            dt.Load(reader);
            reader.Close();
            conn.Close();
            return dt;
        }
        //泛型查询
        public static IEnumerable<T> SqlQuery<T>( DatabaseFacade facade, string sql, params object[] parameters) where T : class, new()
        {
            DataTable dt = SqlQuery(facade, sql, parameters);
            return ToEnumerable<T>(dt);
        }
        //类型转换
        public static IEnumerable<T> ToEnumerable<T>( DataTable dt) where T : class, new()
        {
            PropertyInfo[] propertyInfos = typeof(T).GetProperties();
            T[] ts = new T[dt.Rows.Count];
            int i = 0;
            foreach (DataRow row in dt.Rows)
            {
                T t = new T();
                foreach (PropertyInfo p in propertyInfos)
                {
                    if (dt.Columns.IndexOf(p.Name) != -1 && row[p.Name] != DBNull.Value)
                        p.SetValue(t, row[p.Name], null);
                }
                ts[i] = t;
                i++;
            }
            return ts;
        }
    }

  • 1
    点赞
  • 1
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值