.Net6 DbContext 连接指定数据库,使用sql语句直接进行操作,执行sql
出发点,在项目过程中,涉及到了大数据量的日志,导致查询非常慢,需要进行分表分库等操作,但是这样不能使用实体来进行操作。
·
出发点,在项目过程中,涉及到了大数据量的日志,导致查询非常慢,需要进行分表分库等操作,但是这样不能使用实体来进行操作
添加包并引用
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;
}
}
版权
更多推荐
已为社区贡献1条内容
所有评论(0)