.Net core中对dataTable不像.net framework中那么支持,
所以这里需要自己去对于dataTable处理(也是在.Net core中执行自定义sql的核心)
public class EfService : IEfService
{
public IConfiguration _Configuration;
public string _ConnectionString;
public EfService(IConfiguration configuration)
{
this._Configuration = configuration;
this._ConnectionString = _Configuration.GetConnectionString("DbConnectionString");
}
#region 查询集合
public List<T> QueryList<T>(string sql, Dictionary<string, object> dicPara = null) where T : class
{
var dataList = new List<T>();
var dt = QueryDataTable(_ConnectionString, sql, dicPara);
var columns = GetColumns(dt);
foreach (DataRow row in dt.Rows)
{
var data = row.ItemArray;
var dic = new Dictionary<string, object>();
for (int i = 0; i < data.Length; i++)
{
var value = data[i];
if (!IsEmpty(value))
{
var columnName = columns[i];
dic.Add(columnName, value);
}
}
var model = DictionaryToModel<T>(dic);
dataList.Add(model);
}
return dataList;
}
public List<T> QueryList<T>(string connectionString, string sql, Dictionary<string, object> dicPara = null) where T : class
{
var dataList = new List<T>();
var dt = QueryDataTable(connectionString, sql, dicPara);
var columns = GetColumns(dt);
foreach (DataRow row in dt.Rows)
{
var data = row.ItemArray;
var dic = new Dictionary<string, object>();
for (int i = 0; i < data.Length; i++)
{
var value = data[i];
if (!IsEmpty(value))
{
var columnName = columns[i];
dic.Add(columnName, value);
}
}
var model = DictionaryToModel<T>(dic);
dataList.Add(model);
}
return dataList;
}
#endregion
#region 查询DataTable
public DataTable QueryDataTable(string connectionString, string sql, Dictionary<string, object> dicPara)
{
var dt = new DataTable();
using (var conn = new SqlConnection(connectionString))
{
if (conn.State != ConnectionState.Open) conn.Open();
using (var comm = new SqlCommand(sql, conn))
{
comm.Parameters.Clear();
if (dicPara != null && dicPara.Any()) comm.Parameters.AddRange(CreateSqlParameter(dicPara));
using (var da = new SqlDataAdapter(comm))
{
da.Fill(dt);
}
}
}
return dt;
}
public SqlParameter[] CreateSqlParameter(Dictionary<string, object> dicPara)
{
var paraList = new List<SqlParameter>();
foreach (var model in dicPara)
{
string key = model.Key;
if (!key.StartsWith("@")) key = "@" + key;
paraList.Add(new SqlParameter(key, model.Value));
}
return paraList.ToArray();
}
#endregion
#region 辅助方法
public string[] GetColumns(DataTable dt)
{
var columns = new List<string>();
foreach (DataColumn dc in dt.Columns)
{
columns.Add(dc.ColumnName);
}
return columns.ToArray();
}
public bool IsEmpty(object value)
{
return value is DBNull;
}
public T DictionaryToModel<T>(IDictionary dic) where T : class
{
var json = JsonConvert.SerializeObject(dic);
return JsonConvert.DeserializeObject<T>(json);
}
#endregion
}
测试调用:
public class EfCoreController : Controller
{
public LingbugDbContext _DbContext;
public IEfService _EfCoreService;
public EfCoreController(LingbugDbContext dbContext, IEfService efService)
{
this._DbContext = dbContext;
this._EfCoreService = efService;
}
public IActionResult Index()
{
#region 新增
//var user = _DbContext.UserInfo.Add(new UserInfo()
//{
// Name = "李四",
// Sex = "女",
// Status = true,
// CreateDate = DateTime.Now,
// LastUpdateDate = DateTime.Now
//});
//int count = _DbContext.SaveChanges();
#endregion
#region 扩展efcore执行自定义sql - 查询
//var conn = _DbContext.Database.GetDbConnection().ConnectionString;
//var result = _EfCoreService.QueryList<UserInfo>(conn, @"SELECT * FROM dbo.UserInfoForEfCore");
//return Content(JsonConvert.SerializeObject(result));
#endregion
#region 扩展efcore执行自定义sql - 增删改
var conn = _DbContext.Database.GetDbConnection().ConnectionString;
var result = _DbContext.Database.ExecuteSqlCommand(@"UPDATE dbo.UserInfoForEfCore SET Name = '王五',Birthday = GETDATE(),LastUpdateDate = GETDATE() WHERE Id = 1001");
return Content(result.ToString());
#endregion
#region 执行自定义sql
//var result = _EfCoreService.QueryList<UserInfo>(@"SELECT * FROM dbo.UserInfoForEfCore");
//return Content(JsonConvert.SerializeObject(result));
#endregion
}
}
至于那些EFCore,配置文件以及服务的依赖注入,这里我就不细说了,不懂的可以去看我其他博客,有详细操作讲解
Ending~