/// <summary>
/// 编辑表描述
/// </summary>
/// <param name="context"></param>
public void EditTableDescribe(HttpContext context)
{
var name = context.Request["tableName"];
var describe = context.Request["tableDescribe"];
var dbName = context.Request["dbName"];
// todo 因为无法判断当前是否已有描述 出此下策 目前是先执行更新出现异常再尝试添加
try
{
// 执行 更新
DbClient.Excute($@"USE {dbName}
EXEC sp_updateextendedproperty @name = N'MS_Description',
@value = N'{describe}',
@level0type = N'user',
@level0name = N'dbo',
@level1type = N'table',
@level1name = N'{name}';");
}
catch (Exception)
{
// 执行 添加
DbClient.Excute($@"USE {dbName}
EXEC sp_addextendedproperty @name = N'MS_Description',
@value = N'{describe}',
@level0type = N'user',
@level0name = N'dbo',
@level1type = N'table',
@level1name = N'{name}';");
}
}
/// <summary>
/// 更新一条数据 (需要依赖主键)
/// </summary>
/// <param name="model"></param>
/// <returns></returns>
public int Update(T model)
{
if (IsNullOrEmpty(IdentityKey))
{
throw new Exception(GetDescription(ErrorEnum.E1000));
}
SqlString = new StringBuilder();
SqlString.Append($" UPDATE {_tableName} SET ");
var properties = model.GetType().GetProperties();
var para = new DynamicParameters();
var keyValue = new object();
foreach (var t in properties)
{
if (IdentityKey != t.Name && PrimaryKey != t.Name)
{
SqlString.Append($" {t.Name} = @{t.Name},");
para.Add("@" + t.Name, t.GetValue(model, null));
}
else
{
if (t.Name == PrimaryKey)
{
keyValue = t.GetValue(model, null);
}
}
}
SqlString = RemoveEndNumber(SqlString, 1);
SqlString.Append($" WHERE {PrimaryKey} = @{PrimaryKey}_Key ");
para.Add($"@{PrimaryKey}_Key", keyValue);
return(DbClient.Excute(SqlString.ToString(), para));
}
/// <summary>
/// 依据主键删除一条数据
/// </summary>
/// <param name="id"></param>
/// <returns></returns>
public int DeleteByPrimaryKey(object id)
{
if (IsNullOrEmpty(PrimaryKey))
{
throw new Exception(GetDescription(ErrorEnum.E1000));
}
SqlString = new StringBuilder();
SqlString.Append($"DELETE {_tableName} WHERE {PrimaryKey} = @key ");
return(DbClient.Excute(SqlString.ToString(), new { key = id }));
}
/// <summary>
/// 删除
/// </summary>
/// <param name="where">where条件 以AND开头</param>
/// <returns></returns>
public int Delete(string where)
{
if (IsNullOrEmpty(where))
{
throw new Exception(GetDescription(ErrorEnum.E1001));
}
SqlString = new StringBuilder();
SqlString.Append($"DELETE {_tableName} WHERE 1=1 {where} ");
return(DbClient.Excute(SqlString.ToString()));
}
/// <summary>
/// 批量删除 (必须:AddWhere)
/// </summary>
/// <returns></returns>
public int Delete()
{
if (!_whereList.Any() && !_whereStr.Any())
{
throw new Exception(GetDescription(ErrorEnum.E1001));
}
SqlString = new StringBuilder();
var sq = GetWhereString();
SqlString.Append($"DELETE {_tableName} WHERE 1=1 {sq.SqlStr}");
var para = new DynamicParameters();
para.AddDynamicParams(sq.Parameter);
return(DbClient.Excute(SqlString.ToString(), para));
}
/// <summary>
/// 执行更新(必须AddUpdate 必须AddWhere)
/// </summary>
/// <returns></returns>
public int Update()
{
SqlString = new StringBuilder();
var top = Top > 0 ? $"TOP({Top})" : "";
SqlString.Append($"UPDATE {top} {_tableName} SET ");
var para = new DynamicParameters();
var sp = GetUpdateString();
if (IsNullOrEmpty(sp.SqlStr))
{
throw new Exception(GetDescription(ErrorEnum.E1002));
}
SqlString.Append($"{sp.SqlStr}");
para.AddDynamicParams(sp.Parameter);
sp = GetWhereString();
if (IsNullOrEmpty(sp.SqlStr))
{
throw new Exception(GetDescription(ErrorEnum.E1001));
}
SqlString.Append(" WHERE 1=1 " + sp.SqlStr);
para.AddDynamicParams(sp.Parameter);
return(DbClient.Excute(SqlString.ToString(), para));
}
