【手撸一个ORM】第十步、数据操作工具类 MyDb

说明

其实就是数据库操作的一些封装,很久不用SqlCommand操作数据库了,看了点园子里的文章就直接上手写了,功能上没问题,但写法上是否完美高效无法保证,建议有需要的朋友自己重写,当然如果能把最佳实践方式告知一下,不胜感激!!


 

因为文件比较大,所以将此类分成了四部分。

MyDb主体

using MyOrm.Commons;
using MyOrm.DbParameters;
using MyOrm.Expressions;
using MyOrm.Queryable;
using MyOrm.Reflections;
using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Linq.Expressions;

namespace MyOrm
{
    public partial class MyDb
    {
        private readonly string _connectionString;

        private readonly string _prefix;

        public MyDb(string connectionString, string prefix = "@")
        {
            _connectionString = connectionString;
            _prefix = prefix;
        }

        public MyDb()
        {
            if (string.IsNullOrWhiteSpace(MyDbConfiguration.GetConnectionString()))
            {
                throw new Exception("MyOrm尚未初始化");
            }

            _connectionString = MyDbConfiguration.GetConnectionString();
            _prefix = MyDbConfiguration.GetPrefix();
        }

        /// <summary>
        /// 使用默认配置,返回新MyDb实例
        /// </summary>
        /// <returns></returns>
        public static MyDb New()
        {
            return new MyDb();
        }

        /// <summary>
        /// 返回新MyDb实例
        /// </summary>
        /// <param name="connectionString"></param>
        /// <param name="prefix"></param>
        /// <returns></returns>
        public static MyDb New(string connectionString, string prefix = "@")
        {
            return new MyDb(connectionString, prefix);
        }

        #region 查询
        /// <summary>
        /// 返回MyQueryable实例
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <returns>实体,若记录为空,返回default(T)</returns>
        public MyQueryable<T> Query<T>() where T : class, IEntity, new()
        {
            return new MyQueryable<T>(_connectionString);
        }

        /// <summary>
        /// 根据ID加载一个实体
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="id">实体ID</param>
        /// <returns>实体,若记录为空,返回default(T)</returns>
        public T Load<T>(int id) where T : class, IEntity, new()
        {
            return new MyQueryable<T>(_connectionString).Where(t => t.Id == id).FirstOrDefault();
        }

        /// <summary>
        /// 根据条件加载一个实体
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="where">查询条件</param>
        /// <param name="orderBy">排序字段</param>
        /// <param name="dbSort">正序或倒序</param>
        /// <returns>实体,若记录为空,返回default(T)</returns>
        public T Load<T>(Expression<Func<T, bool>> where = null,
                         Expression<Func<T, object>> orderBy = null,
                         MyDbOrderBy dbSort = MyDbOrderBy.Asc) where T : class, new()
        {
            var query = new MyQueryable<T>(_connectionString);
            if (where != null)
            {
                query.Where(where);
            }

            if (orderBy != null)
            {
                query.OrderBy(orderBy, dbSort);
            }

            return query.FirstOrDefault();
        }

        /// <summary>
        /// 根据条件加载所有实体
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="where">查询条件</param>
        /// <param name="orderBy">排序字段</param>
        /// <param name="dbSort">正序或倒序</param>
        /// <returns>实体列表</returns>
        public List<T> Fetch<T>(Expression<Func<T, bool>> where = null, Expression<Func<T, object>> orderBy = null,
                                MyDbOrderBy dbSort = MyDbOrderBy.Asc) where T : class, new()
        {
            var query = new MyQueryable<T>(_connectionString);
            if (where != null)
            {
                query.Where(where);
            }

            if (orderBy != null)
            {
                query.OrderBy(orderBy, dbSort);
            }

            return query.ToList();
        }

        /// <summary>
        /// 加载分页列表
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="pageIndex">当前页码</param>
        /// <param name="pageSize">每页条数</param>
        /// <param name="recordCount">记录总数</param>
        /// <param name="where">查询条件</param>
        /// <param name="orderBy">排序字段</param>
        /// <param name="dbSort">正序或倒序</param>
        /// <returns>实体列表,输出记录总数</returns>
        public List<T> PageList<T>(int pageIndex,
            int pageSize,
            out int recordCount,
            Expression<Func<T, bool>> where = null,
            Expression<Func<T, object>> orderBy = null,
            MyDbOrderBy dbSort = MyDbOrderBy.Asc) where T : class, new()
        {
            var query = new MyQueryable<T>(_connectionString);
            if (where != null)
            {
                query.Where(where);
            }

            if (orderBy != null)
            {
                query.OrderBy(orderBy, dbSort);
            }

            return query.ToPageList(pageIndex, pageSize, out recordCount);
        }
        #endregion

        #region 获取数量

        public int GetCount<T>(Expression<Func<T, bool>> expression = null) where T : class, IEntity, new()
        {
            var entityInfo = MyEntityContainer.Get(typeof(T));

            if (expression == null)
            {
                var sql = $"SELECT COUNT(0) FROM [{entityInfo.TableName}]";
                using (var conn = new SqlConnection(_connectionString))
                {
                    conn.Open();
                    var command = new SqlCommand(sql, conn);
                    return (int)command.ExecuteScalar();
                }
            }
            else
            {
                var resolver = new EditConditionResolver<T>(entityInfo);
                var result = resolver.Resolve(expression.Body);
                var condition = result.Condition;
                var parameters = result.Parameters;

                condition = string.IsNullOrWhiteSpace(condition) ? "1=1" : condition;

                var sql = $"SELECT COUNT(0) FROM [{entityInfo.TableName}] WHERE [{condition}]";
                using (var conn = new SqlConnection(_connectionString))
                {
                    conn.Open();
                    var command = new SqlCommand(sql, conn);
                    command.Parameters.AddRange(parameters.Parameters);
                    return (int)command.ExecuteScalar();
                }
            }
        }
        #endregion

        #region 执行SQL语句
        //public List<T> Fetch<T>(string sql, MyDbParameters parameters = null)
        //{

        //}
        #endregion
    }
}

MyDbUpdate 更新的相关操作

using MyOrm.Commons;
using MyOrm.DbParameters;
using MyOrm.Expressions;
using MyOrm.Reflections;
using MyOrm.SqlBuilder;
using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Linq;
using System.Linq.Expressions;

namespace MyOrm
{
    public partial class MyDb
    {
        /// <summary>
        /// 更新一个实体
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="entity">要更新的实体</param>
        /// <returns>受影响的记录数</returns>
        public int Update<T>(T entity) where T : class, IEntity, new()
        {
            var entityInfo = MyEntityContainer.Get(typeof(T));

            var sqlBuilder = new SqlServerBuilder();
            var sql = sqlBuilder.Update(entityInfo, "");

            var parameters = new MyDbParameters();
            parameters.Add(entity);

            var command = new SqlCommand(sql);
            command.Parameters.AddRange(parameters.Parameters);

            using (var conn = new SqlConnection(_connectionString))
            {
                conn.Open();
                command.Connection = conn;
                return command.ExecuteNonQuery();
            }
        }

        /// <summary>
        /// 更新多个实体
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="entityList">要更新的实体列表</param>
        /// <returns>受影响的记录数</returns>
        public int Update<T>(List<T> entityList) where T : class, IEntity, new()
        {
            var entityInfo = MyEntityContainer.Get(typeof(T));

            var sqlBuilder = new SqlServerBuilder();
            var sql = sqlBuilder.Update(entityInfo, "");

            var count = 0;

            using (var conn = new SqlConnection(_connectionString))
            {
                conn.Open();
                using (var trans = conn.BeginTransaction())
                {
                    try
                    {
                        foreach (var entity in entityList)
                        {
                            using (var command = new SqlCommand(sql, conn, trans))
                            {
                                var param = new MyDbParameters();
                                param.Add(entity);
                                command.Parameters.AddRange(param.Parameters);
                                count += command.ExecuteNonQuery();
                            }
                        }
                        trans.Commit();
                    }
                    catch (Exception)
                    {
                        trans.Rollback();
                        count = 0;
                    }
                }
            }

            return count;
        }

        /// <summary>
        /// 如果不存在,则更新
        /// 如:UpdateIfNotExists(user, u=>u.Name == user.Name && u.Id != user.Id)
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="entity"></param>
        /// <param name="where"></param>
        /// <returns>受影响的记录数</returns>
        public int UpdateIfNotExits<T>(T entity, Expression<Func<T, bool>> where)
        {
            var entityInfo = MyEntityContainer.Get(typeof(T));

            var resolver = new EditConditionResolver<T>(entityInfo);
            var result = resolver.Resolve(where.Body);
            var condition = result.Condition;
            var parameters = result.Parameters;
            parameters.Add(entity);

            condition = string.IsNullOrWhiteSpace(condition) ? "1=1" : condition;

            var sqlBuilder = new SqlServerBuilder();
            var sql = sqlBuilder.Update(entityInfo, "");
            sql += $" AND NOT EXISTS (SELECT 1 FROM [{entityInfo.TableName}] WHERE {condition})";

            var command = new SqlCommand(sql);
            command.Parameters.AddRange(parameters.Parameters);

            using (var conn = new SqlConnection(_connectionString))
            {
                conn.Open();
                command.Connection = conn;
                return command.ExecuteNonQuery();
            }
        }

        #region 扩展
        /// <summary>
        /// 通过Id修改指定列
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="id">实体ID</param>
        /// <param name="kvs">属性和值的键值对。用法 DbKvs.New().Add("属性名", 值)</param>
        /// <returns>受影响的记录数</returns>
        public int Update<T>(int id, DbKvs kvs)
        {
            var entityInfo = MyEntityContainer.Get(typeof(T));
            var setProperties = kvs.Where(kv => kv.Key != "Id").Select(kv => kv.Key);
            var includeProperties = entityInfo.Properties.Where(p => setProperties.Contains(p.Name)).ToList();
            if (includeProperties.Count == 0)
            {
                return 0;
            }

            var sql =
                $"UPDATE [{entityInfo.TableName}] SET {string.Join(",", includeProperties.Select(p => $"{p.FieldName}=@{p.Name}"))} WHERE Id=@Id";
            var parameters = kvs.ToSqlParameters();
            parameters.Add(new SqlParameter("@Id", id));
            using (var conn = new SqlConnection(_connectionString))
            {
                conn.Open();
                var command = new SqlCommand(sql, conn);
                command.Parameters.AddRange(parameters.ToArray());
                return command.ExecuteNonQuery();
            }
        }

        /// <summary>
        /// 通过查询条件修改指定列
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="kvs">属性和值的键值对。用法 DbKvs.New().Add("属性名", 值)</param>
        /// <param name="expression">查询条件,注意:不支持导航属性,如 "student => student.School.Id > 0" 将无法解析</param>
        /// <returns>受影响的记录数</returns>
        public int Update<T>(Expression<Func<T, bool>> expression, DbKvs kvs)
        {
            var entityInfo = MyEntityContainer.Get(typeof(T));
            var setProperties = kvs.Where(kv => kv.Key != "Id").Select(kv => kv.Key);
            var includeProperties = entityInfo.Properties.Where(p => setProperties.Contains(p.Name)).ToList();
            if (includeProperties.Count == 0)
            {
                return 0;
            }

            string sql;
            List<SqlParameter> parameters;
            if (expression == null)
            {
                sql =
                    $"UPDATE [{entityInfo.TableName}] SET {string.Join(",", includeProperties.Select(p => $"{p.FieldName}=@{p.Name}"))} WHERE Id=@Id";
                parameters = kvs.ToSqlParameters();
            }
            else
            {
                var resolver = new EditConditionResolver<T>(entityInfo);
                var result = resolver.Resolve(expression.Body);
                var where = result.Condition;
                var whereParameters = result.Parameters;

                parameters = kvs.ToSqlParameters();
                parameters.AddRange(whereParameters.Parameters);

                where = string.IsNullOrWhiteSpace(where) ? "1=1" : where;

                sql =
                    $"UPDATE [{entityInfo.TableName}] SET {string.Join(",", includeProperties.Select(p => $"{p.FieldName}=@{p.Name}"))} WHERE {where}";
            }

            using (var conn = new SqlConnection(_connectionString))
            {
                conn.Open();
                var command = new SqlCommand(sql, conn);
                command.Parameters.AddRange(parameters.ToArray());
                return command.ExecuteNonQuery();
            }
        }

        /// <summary>
        /// 修改实体的指定属性
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="entity">要修改的实体</param>
        /// <param name="includes">要修改的属性名称,注意:是实体的属性名而不是数据库字段名</param>
        /// <param name="ignoreAttribute">是否忽略实体的UpdateIgnore描述。默认为true,既includes中包含的所有属性都会被修改</param>
        /// <returns>受影响的记录数</returns>
        public int UpdateInclude<T>(T entity, IEnumerable<string> includes, bool ignoreAttribute = true) where T : IEntity
        {
            var entityInfo = MyEntityContainer.Get(typeof(T));
            var includeProperties = entityInfo.Properties.Where(p => includes.Contains(p.Name) && p.Name != "Id").ToList();

            if (!ignoreAttribute)
            {
                includeProperties = includeProperties.Where(p => !p.UpdateIgnore).ToList();
            }

            if (includeProperties.Count == 0)
            {
                return 0;
            }

            var sql =
                $"UPDATE [{entityInfo.TableName}] SET {string.Join(",", includeProperties.Select(p => $"{p.FieldName}=@{p.Name}"))} WHERE Id=@Id";
            var parameters = new List<SqlParameter> { new SqlParameter("@Id", entity.Id) };

            foreach (var property in includeProperties)
            {
                parameters.Add(new SqlParameter($"@{property.Name}", ResolveParameterValue(property.PropertyInfo.GetValue(entity))));
            }

            using (var conn = new SqlConnection(_connectionString))
            {
                conn.Open();
                var command = new SqlCommand(sql, conn);
                command.Parameters.AddRange(parameters.ToArray());
                return command.ExecuteNonQuery();
            }
        }

        /// <summary>
        /// 修改实体的指定属性
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="entity">要修改的实体</param>
        /// <param name="expression">要修改的属性,注意不支持导航属性及其子属性</param>
        /// <param name="ignoreAttribute">是否忽略实体的UpdateIgnore描述。默认为true,既includes中包含的所有属性都会被修改</param>
        /// <returns>受影响的记录数</returns>
        public int UpdateInclude<T>(T entity, Expression<Func<T, object>> expression, bool ignoreAttribute = true) where T : IEntity
        {
            var entityInfo = MyEntityContainer.Get(typeof(T));
            var visitor = new ObjectMemberVisitor();
            visitor.Visit(expression);
            var include = visitor.GetPropertyList();
            return UpdateInclude(entity, include, ignoreAttribute);
        }

        /// <summary>
        /// 修改实体除指定属性外的其他属性
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="entity">要修改的实体</param>
        /// <param name="ignore">要忽略的属性,注意:是实体的属性名而不是数据表的列名</param>
        /// <param name="ignoreAttribute">是否忽略实体的UpdateIgnore描述。默认为true,既includes中包含的所有属性都会被修改</param>
        /// <returns>受影响的记录数</returns>
        public int UpdateIgnore<T>(T entity, IEnumerable<string> ignore, bool ignoreAttribute = true) where T : IEntity
        {
            var entityInfo = MyEntityContainer.Get(typeof(T));
            var includeProperties = entityInfo.Properties.Where(p => !ignore.Contains(p.Name) && p.Name != "Id").ToList();

            if (!ignoreAttribute)
            {
                includeProperties = includeProperties.Where(p => !p.UpdateIgnore).ToList();
            }

            if (includeProperties.Count() == 0)
            {
                return 0;
            }

            var sql =
                $"UPDATE [{entityInfo.TableName}] SET {string.Join(",", includeProperties.Select(p => $"{p.FieldName}=@{p.Name}"))} WHERE Id=@Id";
            var parameters = new List<SqlParameter> { new SqlParameter("@Id", entity.Id) };

            foreach (var property in includeProperties)
            {
                parameters.Add(new SqlParameter($"@{property.Name}", ResolveParameterValue(property.PropertyInfo.GetValue(entity))));
            }

            using (var conn = new SqlConnection(_connectionString))
            {
                conn.Open();
                var command = new SqlCommand(sql, conn);
                command.Parameters.AddRange(parameters.ToArray());
                return command.ExecuteNonQuery();
            }
        }

        /// <summary>
        /// 
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="entity">要修改的实体</param>
        /// <param name="expression">要修改的属性,注意不支持导航属性及其子属性</param>
        /// <param name="ignoreAttribute">是否忽略实体的UpdateIgnore描述。默认为true,既includes中包含的所有属性都会被修改</param>
        /// <returns>受影响的记录数</returns>
        public int UpdateIgnore<T>(T entity, Expression<Func<T, object>> expression, bool ignoreAttribute = true) where T : IEntity
        {
            var entityInfo = MyEntityContainer.Get(typeof(T));
            var visitor = new ObjectMemberVisitor();
            visitor.Visit(expression);
            var include = visitor.GetPropertyList();
            return UpdateIgnore(entity, include, ignoreAttribute);
        }
        #endregion

        private object ResolveParameterValue(object val)
        {
            if (val is null)
            {
                val = DBNull.Value;
            }

            return val;
        }
    }
}

MyDbInsert 插入相关的操作

using MyOrm.Commons;
using MyOrm.DbParameters;
using MyOrm.Expressions;
using MyOrm.Reflections;
using MyOrm.SqlBuilder;
using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Linq;
using System.Linq.Expressions;

namespace MyOrm
{
    public partial class MyDb
    {
        /// <summary>
        /// 创建一个实体,新的记录Id将绑定到entity的Id属性
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="entity">要创建的实体</param>
        /// <returns>新生成记录的ID,若失败返回0</returns>
        public int Insert<T>(T entity) where T : class, IEntity, new()
        {
            var entityInfo = MyEntityContainer.Get(typeof(T));

            var sqlBuilder = new SqlServerBuilder();
            var sql = sqlBuilder.Insert(entityInfo);

            var parameters = new MyDbParameters();
            parameters.Add(entity);

            var command = new SqlCommand(sql);
            command.Parameters.AddRange(parameters.Parameters);

            using (var conn = new SqlConnection(_connectionString))
            {
                conn.Open();
                command.Connection = conn;
                var result = command.ExecuteScalar().ToString();
                entity.Id = Convert.ToInt32(string.IsNullOrWhiteSpace(result) ? "0" : result);
                return entity.Id;
            }
        }

        /// <summary>
        /// 如果不满足条件则创建一个实体,
        /// 如限制用户名不能重复 InsertIfNotExist(user, u => u.Name == user.Name)
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="entity">要创建的实体</param>
        /// <param name="where">条件</param>
        /// <returns>新生成记录的ID,若失败返回0</returns>
        public int InsertIfNotExists<T>(T entity, Expression<Func<T, bool>> where) where T : class, IEntity, new()
        {
            if (where == null)
            {
                return Insert(entity);
            }
            else
            {
                var entityInfo = MyEntityContainer.Get(typeof(T));
                var resolver = new EditConditionResolver<T>(entityInfo);
                var result = resolver.Resolve(where.Body);
                var condition = result.Condition;
                var parameters = result.Parameters;
                parameters.Add(entity);

                condition = string.IsNullOrWhiteSpace(condition) ? "1=1" : condition;

                var sqlBuilder = new SqlServerBuilder();
                var sql = sqlBuilder.InsertIfNotExists(entityInfo, condition);
                var command = new SqlCommand(sql);
                command.Parameters.AddRange(parameters.Parameters);

                using (var conn = new SqlConnection(_connectionString))
                {
                    conn.Open();
                    command.Connection = conn;
                    var idString = command.ExecuteScalar().ToString();
                    entity.Id = Convert.ToInt32(string.IsNullOrWhiteSpace(idString) ? "0" : idString);
                    return entity.Id;
                }
            }
        }

        /// <summary>
        /// 批量创建实体,注意此方法效率不高
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="entityList">实体列表</param>
        /// <returns>受影响的记录数</returns>
        public int Insert<T>(List<T> entityList) where T : class, IEntity, new()
        {
            var entityInfo = MyEntityContainer.Get(typeof(T));

            var sqlBuilder = new SqlServerBuilder();
            var sql = sqlBuilder.Insert(entityInfo);

            var count = 0;

            using (var conn = new SqlConnection(_connectionString))
            {
                conn.Open();
                using (var trans = conn.BeginTransaction())
                {
                    try
                    {
                        foreach (var entity in entityList)
                        {
                            using (var command = new SqlCommand(sql, conn, trans))
                            {
                                var parameters = new MyDbParameters();
                                parameters.Add(entity);
                                command.Parameters.AddRange(parameters.Parameters);
                                var result = command.ExecuteScalar().ToString();
                                entity.Id = Convert.ToInt32(string.IsNullOrWhiteSpace(result) ? "0" : result);
                                count++;
                            }
                        }
                        trans.Commit();
                    }
                    catch
                    {
                        trans.Rollback();
                        count = 0;
                    }
                }
            }

            return count;
        }
    }
}

MyDbDelete 删除相关的操作

using MyOrm.Commons;
using MyOrm.Expressions;
using MyOrm.Reflections;
using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Linq.Expressions;

namespace MyOrm
{
    public partial class MyDb
    {
        #region 删除

        /// <summary>
        /// 根据ID删除记录,如果支持软删除并且非强制删除,则更新IsDel字段为true,否则,删除记录
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="id">要删除的实体ID</param>
        /// <param name="isForce">是否强制删除,默认为false</param>
        /// <returns>受影响的记录数</returns>
        public int Delete<T>(int id, bool isForce = false) where T : class, IEntity, new()
        {
            var entityInfo = MyEntityContainer.Get(typeof(T));
            if (isForce || !entityInfo.IsSoftDelete)
            {
                var sql = $"DELETE [{entityInfo.TableName}] WHERE [{entityInfo.KeyColumn}]={_prefix}Id";
                using (var conn = new SqlConnection(_connectionString))
                {
                    conn.Open();
                    var command = new SqlCommand(sql, conn);
                    command.Parameters.AddWithValue($"{_prefix}Id", id);
                    return command.ExecuteNonQuery();
                }
            }
            else
            {
                var sql = $"UPDATE [{entityInfo.TableName}] SET IsDel=1 WHERE [{entityInfo.KeyColumn}]={_prefix}Id";
                using (var conn = new SqlConnection(_connectionString))
                {
                    conn.Open();
                    var command = new SqlCommand(sql, conn);
                    command.Parameters.AddWithValue($"{_prefix}Id", id);
                    return command.ExecuteNonQuery();
                }
            }
        }

        /// <summary>
        /// 根据ID批量删除记录,如果支持软删除并且非强制删除,则更新IsDel字段为true,否则,删除记录
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="idList">要删除的ID列表</param>
        /// <param name="isForce">是否强制删除,默认为false</param>
        /// <returns>受影响的记录数</returns>
        public int Delete<T>(IEnumerable<int> idList, bool isForce = false) where T : class, IEntity, new()
        {
            var entityInfo = MyEntityContainer.Get(typeof(T));
            if (isForce || !entityInfo.IsSoftDelete)
            {
                var sql =
                    $"EXEC('DELETE [{entityInfo.TableName}] WHERE [{entityInfo.KeyColumn}] in ('+{_prefix}Ids+')')";
                using (var conn = new SqlConnection(_connectionString))
                {
                    conn.Open();
                    var command = new SqlCommand(sql, conn);
                    command.Parameters.AddWithValue($"{_prefix}Ids", string.Join(",", idList));
                    return command.ExecuteNonQuery();
                }
            }
            else
            {
                var sql = $"EXEC('UPDATE [{entityInfo.TableName}] SET IsDel=1 WHERE [{entityInfo.KeyColumn}] in ('+{_prefix}Ids+')')";
                using (var conn = new SqlConnection(_connectionString))
                {
                    conn.Open();
                    var command = new SqlCommand(sql, conn);
                    command.Parameters.AddWithValue($"{_prefix}Id", idList);
                    return command.ExecuteNonQuery();
                }
            }
        }

        /// <summary>
        /// 根据条件删除记录,如果支持软删除并且非强制删除,则更新IsDel字段为true,否则,删除记录
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="expression">条件,注意不支持导航属性及其子属性</param>
        /// <param name="isForce">是否强制删除</param>
        /// <returns>受影响的记录数</returns>
        public int Delete<T>(Expression<Func<T, bool>> expression, bool isForce) where T : IEntity
        {
            var entityInfo = MyEntityContainer.Get(typeof(T));

            var resolver = new EditConditionResolver<T>(entityInfo);
            var result = resolver.Resolve(expression.Body);
            var condition = result.Condition;
            var parameters = result.Parameters;

            condition = string.IsNullOrWhiteSpace(condition) ? "1=1" : condition;
            string sql;
            if (isForce || !entityInfo.IsSoftDelete)
            {
                sql =
                    $"DELETE [{entityInfo.TableName}] WHERE {condition}";
            }
            else
            {
                sql =
                    $"UPDATE [{entityInfo.TableName}] SET IsDel=1 WHERE {condition}";
            }

            using (var conn = new SqlConnection(_connectionString))
            {
                conn.Open();
                var command = new SqlCommand(sql, conn);
                command.Parameters.AddRange(parameters.Parameters);
                return command.ExecuteNonQuery();
            }
        }
        #endregion
    }
}
posted @ 2019-04-06 23:42  没追求的码农  阅读(544)  评论(0编辑  收藏  举报