Пример #1
0
        /// <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>(DbKvs kvs, Expression <Func <T, bool> > expression = null)
        {
            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 whereExpressionVisitor = new WhereExpressionVisitor<T>();
                //whereExpressionVisitor.Visit(expression);
                //var where = whereExpressionVisitor.GetCondition();
                //var whereParameters = whereExpressionVisitor.GetParameters().ToSqlParameters();

                var resolver = new ConditionResolver(entityInfo);
                resolver.Resolve(expression.Body);
                var where = resolver.GetCondition();
                var whereParameters = resolver.GetParameters().ToSqlParameters();

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

                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());
            }
        }
Пример #2
0
        /// <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 whereExpressionVisitor = new WhereExpressionVisitor<T>(entityInfo);
                //whereExpressionVisitor.Visit(where);
                //var condition = whereExpressionVisitor.GetCondition();
                //var parameters = whereExpressionVisitor.GetParameters().ToSqlParameters();

                var resolver = new ConditionResolver(entityInfo);
                resolver.Resolve(where.Body);
                var condition  = resolver.GetCondition();
                var parameters = resolver.GetParameters().ToSqlParameters();

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

                var sqlBuilder = new SqlServerSqlBuilder();
                var sql        = sqlBuilder.InsertIfNotExists(entityInfo, condition);

                parameters.AddRange(
                    entityInfo
                    .Properties
                    .Where(p => !p.InsertIgnore)
                    .Select(p => new SqlParameter($"{_prefix}{p.Name}",
                                                  ResolveParameterValue(p.PropertyInfo.GetValue(entity)))));
                var command = new SqlCommand(sql);
                command.Parameters.AddRange(parameters.ToArray());

                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);
                }
            }
        }
Пример #3
0
        /// <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 whereExpressionVisitor = new WhereExpressionVisitor<T>(entityInfo);
            //whereExpressionVisitor.Visit(where);
            //var condition = whereExpressionVisitor.GetCondition();
            //var parameters = whereExpressionVisitor.GetParameters().ToSqlParameters();

            var resolver = new ConditionResolver(entityInfo);

            resolver.Resolve(where.Body);
            var condition  = resolver.GetCondition();
            var parameters = resolver.GetParameters().ToSqlParameters();

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

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

            sql += $" AND NOT EXISTS (SELECT 1 FROM [{entityInfo.TableName}] WHERE {condition})";

            parameters.AddRange(
                entityInfo
                .Properties
                .Where(p => !p.UpdateIgnore || p.IsKey)
                .Select(p => new SqlParameter($"{_prefix}{p.Name}",
                                              ResolveParameterValue(p.PropertyInfo.GetValue(entity)))));

            var command = new SqlCommand(sql);

            command.Parameters.AddRange(parameters.ToArray());

            using (var conn = new SqlConnection(_connectionString))
            {
                conn.Open();
                command.Connection = conn;
                return(command.ExecuteNonQuery());
            }
        }
Пример #4
0
        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 whereExpressionVisitor = new WhereExpressionVisitor<T>();
                //whereExpressionVisitor.Visit(expression);
                //var condition = whereExpressionVisitor.GetCondition();
                //var parameters = whereExpressionVisitor.GetParameters().ToSqlParameters();

                var resolver = new ConditionResolver(entityInfo);
                resolver.Resolve(expression.Body);
                var condition  = resolver.GetCondition();
                var parameters = resolver.GetParameters().ToSqlParameters();

                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.ToArray());
                    return((int)command.ExecuteScalar());
                }
            }
        }
Пример #5
0
        /// <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 whereExpressionVisitor = new WhereExpressionVisitor<T>();
            //whereExpressionVisitor.Visit(expression);
            //var where = whereExpressionVisitor.GetCondition();
            //var whereParameters = whereExpressionVisitor.GetParameters().ToSqlParameters();

            var resolver = new ConditionResolver(entityInfo);

            resolver.Resolve(expression.Body);
            var condition  = resolver.GetCondition();
            var parameters = resolver.GetParameters().ToSqlParameters();

            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.ToArray());
                return(command.ExecuteNonQuery());
            }
        }