/// <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());
}
}
/// <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);
}
}
}
/// <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());
}
}
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());
}
}
}
/// <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());
}
}