.Net core中执行自定义sql以及扩展EFCore执行自定义sql

.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~