出发点，在项目过程中，涉及到了大数据量的日志，导致查询非常慢，需要进行分表分库等操作，但是这样不能使用实体来进行操作

添加包并引用

using Microsoft.EntityFrameworkCore;

SQL server 需要添加包 Microsoft.EntityFrameworkCore.SqlServer
MySql 需要添加包 Pomelo.EntityFrameworkCore.MySql
Oracle 需要引用 Citms.EntityFrameworkCore.Oracle （未验证）

数据库类型枚举

	public enum DBType
    {
        SqlServer=0,
        MySql=1,
        Oracle=2,
        MongoDB=3
    }

数据库连接类

继承DbContext

    public partial class SqlBase : DbContext
    {
        /// <summary>
        /// 连接字符串
        /// </summary>
        private string _ConnString { get; set; }

        /// <summary>
        /// 数据库类型 不同的数据库之间sql也会存在差异
        /// </summary>
        public DBType DBType { get; set; }

        public SqlBase(string connstring, DBType DBType) : base()
        {

            this._ConnString = connstring;
            this.DBType = DBType;
        }

        /// <summary>
		/// 创建数据库连接
		/// </summary>
		/// <param name="builder"></param>
		protected override void OnConfiguring(DbContextOptionsBuilder builder)
        {
            if (this.DBType == DBType.SqlServer)
            {
                /* 通过构造函数传入连接字符串 */
                builder.UseSqlServer(this._ConnString);
            }


            if (this.DBType == DBType.MySql)
            {
                /* 通过构造函数传入连接字符串 */
                builder.UseMySql(this._ConnString, new MySqlServerVersion(new Version()));
            }
        }
    }

执行Sql扩展

using Microsoft.EntityFrameworkCore;
using System.Data;

    public static class DBExt
    {

        /// <summary>
        /// 执行任意sql
        /// 有返回内容时，返回一个 object 对象 否则返回 null
        /// </summary>
        /// <param name="ef"></param>
        /// <param name="sql">sql语句</param>
        /// <param name="TimeOut">超时设置 默认30秒</param>
        /// <returns></returns>
        public static object ExecuteSql(this DbContext ef, string sql, int TimeOut = 30)
        {
            return ef.ExecuteSql<object>(sql);
        }


        /// <summary>
        /// 执行任意sql 可以序列化为指定实体类型 List<T>
        /// 有返回内容时，返回一个 object 对象 否则返回 null
        /// </summary>
        /// <param name="ef"></param>
        /// <param name="sql">sql语句</param>
        /// <param name="TimeOut">超时设置 默认30秒</param>
        /// <returns></returns>
        public static List<T> ExecuteSql<T>(this DbContext ef, string sql, int TimeOut = 30)
        {
            var list = new List<string>();
            var comm = ef.Database.GetDbConnection();
            if (comm.State != ConnectionState.Open)
            {
                comm.Open();
            }

            using (var cmd = comm.CreateCommand())
            {
                cmd.CommandTimeout = TimeOut;
                cmd.CommandText = sql;
                using (var r = cmd.ExecuteReader())
                {
                    var c = r.FieldCount;
                    while (r.Read())
                    {
                        var node = "{";
                        List<string> values = new List<string>();
                        for (int i = 0; i < c; i++)
                        {


                            var bb = r.GetName(i);
                            var a = r.GetFieldValue<object>(i);
                            var t=r.GetFieldType(i);
                            if(t.Name== "UInt64")
                            {
                                values.Add($"\"{bb}\":{a}");
                            }
                            else
                            {
                                var v = a.ToString().Replace("\"","'");
                                values.Add($"\"{bb}\":\"{v}\"");
                            }
                        }
                        node += string.Join(',', values);
                        node += "}";

                        list.Add(node);
                    }
                }
            }
            var json = "[" + string.Join(',', list) + "]";

            if (json == "[]")
            {
                return null;
            }
            return json.ToEntity<List<T>>();

        }

        /// <summary>
        /// 返回字典列表
        /// 因为 object 无法进行读写操作
        /// </summary>
        /// <param name="ef"></param>
        /// <param name="sql"></param>
        /// <param name="TimeOut"></param>
        /// <returns></returns>
        public static List<Dictionary<string,string?>> ExecuteSqlToDic(this DbContext ef, string sql, int TimeOut = 30)
        {
            List<Dictionary<string, string?>> dic=new List<Dictionary<string, string?>>();

            var comm = ef.Database.GetDbConnection();
            if (comm.State != ConnectionState.Open)
            {
                comm.Open();
            }
            using (var cmd = comm.CreateCommand())
            {
                cmd.CommandTimeout = TimeOut;
                cmd.CommandText = sql;
                using (var r = cmd.ExecuteReader())
                {
                    var c = r.FieldCount;
                    while (r.Read())
                    {
                        Dictionary<string, string?> values =new Dictionary<string, string?>();

                        for (int i = 0; i < c; i++)
                        {
                            var bb = r.GetName(i);
                            var a = r.GetFieldValue<object>(i);
                            //var t = r.GetFieldType(i);

                            values.Add(bb,a?.ToString());
                        }


                        dic.Add(values);

                    }
                }
            }

                return dic;
        }

    }

版权

正版地址 csdn