直接上代码
using Abp.Dependency;
using Abp.EntityFrameworkCore;
using AutoCodePlan.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Reflection;
using System.Threading.Tasks;
namespace AutoCodePlan.Sql
{
/// <summary>
///
/// </summary>
public interface ISqlExecuter
{
/// <summary>
/// 执行给定的命令
/// </summary>
/// <param name="sql">命令字符串</param>
/// <returns>执行命令后由数据库返回的结果</returns>
int Execute(string sql);
/// <summary>
/// 这个抛弃掉,是按照下标注入的
/// </summary>
/// <param name="sql"></param>
/// <returns></returns>
//Task<List<T>> SqlQuery<T>(string sql) where T : class, new();
/// <summary>
/// 根据SQL语句查询
/// </summary>
/// <param name="sql">sql语句</param>
/// /// <param name="islist">是否时集合</param>
/// <returns></returns>
Task<String> SqlQueryJsonString(string sql, bool islist = true);
/// <summary>
/// 通过反射获取SQL语句查询获取的单例
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="sql"></param>
/// <returns></returns>
Task<T> SqlQueryReflex<T>(string sql) where T : class, new();
/// <summary>
/// 通过反射获取SQL语句查询的列表
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="sql"></param>
/// <returns></returns>
Task<List<T>> SqlQueryReflexList<T>(string sql) where T : class, new();
}
/// <summary>
///
/// </summary>
public class SqlExecuter : ISqlExecuter, ITransientDependency
{
private IDbContextProvider<AutoCodePlanDbContext> _dbContextProvider = null;
/// <summary>
///
/// </summary>
/// <param name="dbContextProvider"></param>
public SqlExecuter(IDbContextProvider<AutoCodePlanDbContext> dbContextProvider)
{
_dbContextProvider = dbContextProvider;//IocManager.Instance.Resolve<IDbContextProvider<OADbContext>>();
}
/// <summary>
///
/// </summary>
/// <param name="sql"></param>
/// <returns></returns>
public int Execute(string sql)
{
var result = 0;
var dbt = _dbContextProvider.GetDbContext();
var db = dbt.Database;
using (var command = db.GetDbConnection().CreateCommand())
{
//var _t = new T();
command.CommandText = sql;
db.OpenConnection();
result = command.ExecuteNonQuery();
}
return result;
}
/// <summary>
///
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="sql"></param>
/// <returns></returns>
//public async Task<List<T>> SqlQuery<T>(string sql) where T : class, new()
//{
// return await Task.Run(() =>
// {
// //var db = _dbContextProvider.GetDbContext().Database;
// var dbt = _dbContextProvider.GetDbContext();
// var db = dbt.Database;
// using (var command = db.GetDbConnection().CreateCommand())
// {
// command.CommandText = sql;
// db.OpenConnection();
// var result = new List<T>();
// using (var dr = command.ExecuteReader())
// {
// var properties = typeof(T).GetProperties().ToList();
// while (dr.Read())
// {
// var obj = new T();
// foreach (var property in properties)
// {
// //获取该字段明的列序号,从0开始
// var id = dr.GetOrdinal(property.Name.ToLower());
// if (!dr.IsDBNull(id))
// {
// if (dr.GetValue(id) != DBNull.Value)
// {
// property.SetValue(obj, dr.GetValue(id));
// }
// }
// }
// result.Add(obj);
// }
// }
// //db.CloseConnection();//用完后要关闭?
// return result;
// }
// });
//}
/// <summary>
/// 单个数据形式的查询反馈
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="sql"></param>
/// <returns></returns>
public async Task<T> SqlQueryReflex<T>(string sql) where T : class, new()
{
return await Task.Run(() =>
{
//var db = _dbContextProvider.GetDbContext().Database;
var dbt = _dbContextProvider.GetDbContext();
var db = dbt.Database;
using (var command = db.GetDbConnection().CreateCommand())
{
var _t = new T();
command.CommandText = sql;
db.OpenConnection();
//var result = new List<T>();
using (var dr = command.ExecuteReader())
{
//var properties = typeof(T).GetProperties().ToList();
while (dr.Read())
{
//var _t = new T();
_t = (T)GetValue(dr, _t);
//return _t;
break;
}
}
//db.CloseConnection();//用完后要关闭?
//return null;
return _t;
}
});
}
/// <summary>
/// 集合类型的查询反馈
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="sql"></param>
/// <returns></returns>
public async Task<List<T>> SqlQueryReflexList<T>(string sql) where T : class, new()
{
return await Task.Run(() =>
{
var dbt = _dbContextProvider.GetDbContext();
var db = dbt.Database;
using (var command = db.GetDbConnection().CreateCommand())
{
command.CommandText = sql;
db.OpenConnection();
var list = new List<T>();
T _t = new T();
//PropertyInfo[] infos = typeof(T).GetType().GetProperties();
DataRowCollection rowList;
using (var dr = command.ExecuteReader())
{
rowList = dr.GetSchemaTable().Rows;
//只反射一次
Type type = _t.GetType();
var infos = type.GetProperties();
while (dr.Read())
{
_t = new T();
_t = (T)GetValue(dr, infos, rowList, _t);
list.Add(_t);
}
}
db.CloseConnection();
return list;
}
});
}
/// <summary>
///
/// </summary>
/// <param name="sql"></param>
/// <param name="islist"></param>
/// <returns></returns>
public async Task<String> SqlQueryJsonString(string sql, bool islist = true)
{
return await Task.Run(() =>
{
//var db = _dbContextProvider.GetDbContext().Database;
var dbt = _dbContextProvider.GetDbContext();
var db = dbt.Database;
using (var command = db.GetDbConnection().CreateCommand())
{
command.CommandText = sql;
db.OpenConnection();
var resultString = String.Empty;
//var result = new List<T>();
using (var dr = command.ExecuteReader())
{
resultString = ToJson(dr, !islist);
}
db.CloseConnection();//用完后要关闭?
return resultString;
}
});
}
/// <summary>
/// 转JsonString
/// </summary>
/// <param name="dataReader"></param>
/// <param name="onlyone"></param>
/// <returns></returns>
private string ToJson(DbDataReader dataReader, bool onlyone = false)
{
System.Text.StringBuilder jsonString = new System.Text.StringBuilder();
if (!onlyone)
{
jsonString.Append("[");
}
while (dataReader.Read())
{
jsonString.Append("{");
for (int i = 0; i < dataReader.FieldCount; i++)
{
Type type = dataReader.GetFieldType(i);
string strKey = dataReader.GetName(i);
string strValue = dataReader[i].ToString();
jsonString.Append("\"x_" + strKey + "\":");
//strValue = String.Format(strValue, type);
if (type == typeof(String))
{
if (dataReader[i] == DBNull.Value)
{
jsonString.Append("null");
if (i <= dataReader.FieldCount - 1)
{
jsonString.Append(",");
}
}
else
{
if (strValue != "null")
{
jsonString.Append(JsonSerializer<string>(strValue));
}
else
{
jsonString.Append("null");
}
if (i <= dataReader.FieldCount - 1)
{
jsonString.Append(",");
}
}
}
else if (type == typeof(Boolean))
{
if (dataReader[i] == DBNull.Value)
{
jsonString.Append("false");
}
else
{
jsonString.Append(strValue.ToLower());
}
if (i <= dataReader.FieldCount - 1)
{
jsonString.Append(",");
}
}
else if (type == typeof(DateTime))
{
if (dataReader[i] == DBNull.Value)
{
//jsonString.Append("null");
jsonString.Append("\"");
jsonString.Append(string.Format("\\/Date({0}+0800)\\/", (DateTime.Parse("2000-01-01").ToUniversalTime() - DateTime.Parse("1970-01-01")).TotalMilliseconds));
jsonString.Append("\"");
}
else
{
jsonString.Append("\"");
jsonString.Append(string.Format("\\/Date({0}+0800)\\/", (DateTime.Parse(strValue).ToUniversalTime() - DateTime.Parse("1970-01-01")).TotalMilliseconds));
jsonString.Append("\"");
}
if (i <= dataReader.FieldCount - 1)
{
jsonString.Append(",");
}
}
//不需要加""的
else
{
if (!String.IsNullOrEmpty(strValue))
{
jsonString.Append("" + strValue + "");
}
else
{
if (type == typeof(Int32) || type == typeof(Int64) || type == typeof(Int16) || type == typeof(Decimal))
{
jsonString.Append("0");
}
else
{
jsonString.Append("" + strValue + "");
}
}
if (i <= dataReader.FieldCount - 1)
{
jsonString.Append(",");
}
}
}
if (dataReader.FieldCount > 0)
{
jsonString.Remove(jsonString.Length - 1, 1);
}
jsonString.Append("},");
if (onlyone)
{
break;
}
}
if (jsonString.Length > 1)
{
jsonString.Remove(jsonString.Length - 1, 1);
}
if (!onlyone)
{
jsonString.Append("]");
}
return jsonString.ToString();
}
/// <summary>
///
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="t"></param>
/// <returns></returns>
private string JsonSerializer<T>(T t)
{
System.Runtime.Serialization.Json.DataContractJsonSerializer ser = new System.Runtime.Serialization.Json.DataContractJsonSerializer(typeof(T));
System.IO.MemoryStream ms = new System.IO.MemoryStream();
ser.WriteObject(ms, t);
string jsonString = System.Text.Encoding.UTF8.GetString(ms.ToArray());
ms.Close();
return jsonString;
}
/// <summary>
///
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="jsonString"></param>
/// <returns></returns>
private T JsonDeserialize<T>(string jsonString)
{
System.Runtime.Serialization.Json.DataContractJsonSerializer ser = new System.Runtime.Serialization.Json.DataContractJsonSerializer(typeof(T));
System.IO.MemoryStream ms = new System.IO.MemoryStream(System.Text.Encoding.UTF8.GetBytes(jsonString));
T obj = (T)ser.ReadObject(ms);
return obj;
}
private bool HasColumn(DataTable table, string columnName)
{
foreach (DataRow item in table.Rows)
{
//if(item.ItemArray[0].ToString() == columnName){
// return true;
//}
if (item["ColumnName"].ToString() == columnName)
{
return true;
}
}
return false;
}
private object GetValue(DbDataReader reader, object obj)
{
Type type = obj.GetType();
for (var k = 0; k < type.GetProperties().Length; k++)
{
var item = type.GetProperties()[k];
//if (item.Name.Length > 2 && HasColumn(reader.GetSchemaTable(), item.Name.Substring(2)))
//{
// if (reader[item.Name.Substring(2)] != DBNull.Value)
// {
// item.SetValue(obj, reader[item.Name.Substring(2)], null);
// }
//}
if (item.Name.Length >= 2 && HasColumn(reader.GetSchemaTable(), item.Name))
{
if (reader[item.Name] != DBNull.Value)
{
item.SetValue(obj, reader[item.Name], null);
}
}
}
return obj;
}
private bool HasColumn(DataRowCollection rowList, string columnName)
{
foreach (DataRow item in rowList)
{
if (item["ColumnName"].ToString() == columnName)
{
return true;
}
//if (item.ItemArray[0].ToString().ToLower() == columnName.ToLower())
//{
// return true;
//}
}
return false;
}
private object GetValue(DbDataReader reader, PropertyInfo[] infos, DataRowCollection rowList, object obj)
{
//for (var k = 0; k < reader.FieldCount; k++)
//{
// Debug.WriteLine("key:"+reader.GetName(k)+",val:"+reader.GetValue(k));
//}
//return GetValue(reader,obj);
foreach (var item in infos)
{
//Debug.WriteLine("==============="+item.Name+"=================");
//Debug.WriteLine("+"+reader["Id"]+"+"+reader["id"]);
if (item.Name.Length >= 2 && HasColumn(rowList, item.Name))
{
if (reader[item.Name] != DBNull.Value)
{
item.SetValue(obj, reader[item.Name], null);
}
//else if (reader[item.Name.ToLower()] != DBNull.Value) {
// item.SetValue(obj, reader[item.Name.ToLower()], null);
//}
}
}
return obj;
}
}
}