.Net6 DbContext 连接指定数据库，使用sql语句直接进行操作，执行sql
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 = (i);
values.Add(bb,a?.ToString());
}
dic.Add(values);
}
}
}
return dic;
}
}