public string Update(string table, DbKvs kvs, string where)
{
if (kvs == null || kvs.Count == 0)
{
throw new ArgumentNullException(nameof(kvs));
}
if (string.IsNullOrWhiteSpace(table))
{
throw new ArgumentNullException(nameof(table));
}
var sb = new StringBuilder();
sb.Append("UPDATE [")
.Append(table)
.Append("] SET ");
var clauses = kvs.Select(kv => $"[{kv.Key}]={Prefix}{kv.Key}");
sb.Append(string.Join(",", clauses));
if (!string.IsNullOrWhiteSpace(where))
{
sb.Append(" WHERE ").Append(where);
}
return(sb.ToString());
}
public ActionResult Delete(int id, FormCollection collection)
{
var entity = new School();
TryUpdateModel(entity);
try
{
//var result = _db.Delete<School>(id);
//if (result > 0)
//{
// return RedirectToAction("Index");
//}
var result = _db.Update <School>(DbKvs.New().Add("IsDel", true), s => s.Id == id);
if (result > 0)
{
return(RedirectToAction("Index"));
}
ModelState.AddModelError(string.Empty, "删除失败");
return(View(entity));
}
catch (Exception ex)
{
ModelState.AddModelError(string.Empty, ex.Message);
return(View(entity));
}
}
public async Task <ActionResult> Delete(int id, IFormCollection collection)
{
var entity = new AppUser();
await TryUpdateModelAsync(entity);
try
{
var result = await _db.UpdateAsync <AppUser>(id, DbKvs.New().Add("IsDelete", true));
if (result > 0)
{
return(RedirectToAction(nameof(Index)));
}
else
{
ModelState.AddModelError(string.Empty, "删除失败");
}
}
catch (Exception e)
{
ModelState.AddModelError(string.Empty, e.Message);
}
return(View(entity));
}
/// <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());
}
}
private static DbKvs ResolveNew(NewExpression node)
{
var kvs = DbKvs.New();
var members = node.Members;
var args = node.Arguments;
for (var i = 0; i < members.Count; i++)
{
var key = members[i].Name;
var val = args[i].GetValue();
kvs.Add(key, val);
}
return(kvs);
}
public async Task <int> UpdateAsync <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(await command.ExecuteNonQueryAsync());
}
}
