/// <summary>
/// 删除已移除的扩展字段及频道数据表列
/// </summary>
private void FieldDelete(IDbConnection conn, IDbTransaction trans, Model.site_channel newModel, Model.site_channel oldModel)
{
if (oldModel.channel_fields == null)
{
return;
}
string fieldIds = string.Empty;
foreach (Model.site_channel_field modelt in oldModel.channel_fields)
{
//查找对应的字段ID，不在旧实体则删除
if (newModel.channel_fields.Find(p => p.field_id == modelt.field_id) == null)
{
//记住要删除的字段ID
fieldIds += modelt.field_id + ",";
//删除该旧字段
WriteDataBase.Execute(conn, trans, "delete from " + databaseprefix + "site_channel_field where channel_id=" + newModel.id + " and field_id=" + modelt.field_id);
}
}
//删除频道数据表列
if (fieldIds.Length > 0)
{
List <Model.article_attribute_field> field = new List <Model.article_attribute_field>();
field = WriteDataBase.Query <Model.article_attribute_field>(conn, trans, Sql.Builder.Select("id,name").From(databaseprefix + "article_attribute_field").Where("id in(" + fieldIds.TrimEnd(',') + ")")).ToList();
foreach (var dr in field)
{
//删除频道数据表列
ReadDataBase.Execute(conn, trans, "alter table " + databaseprefix + DTKeys.TABLE_CHANNEL_ARTICLE + oldModel.name + " drop column " + dr.name);
}
}
}
/// <summary>
/// 查找不存在的图片并删除已移除的图片及数据
/// </summary>
public void DeleteList(IDbConnection conn, IDbTransaction trans, List <Model.article_albums> models, int channel_id, int article_id)
{
StringBuilder idList = new StringBuilder();
if (models != null)
{
foreach (Model.article_albums modelt in models)
{
if (modelt.id > 0)
{
idList.Append(modelt.id + ",");
}
}
}
string delIds = idList.ToString().TrimEnd(',');
string strwhere = "channel_id=" + channel_id + " and article_id=" + article_id;
if (!string.IsNullOrEmpty(delIds))
{
strwhere += " and id not in(" + delIds + ")";
}
List <Model.article_albums> albums = new List <Model.article_albums>();
albums = WriteDataBase.Query <Model.article_albums>(conn, trans, Sql.Builder.Select("channel_id,id,thumb_path,original_path").From(TableName).Where(strwhere)).ToList();
foreach (var dr in albums)
{
int rows = WriteDataBase.Execute(conn, trans, "delete from " + databaseprefix + "article_albums where id=" + dr.id); //删除数据库
if (rows > 0)
{
FileHelper.DeleteFile(dr.thumb_path); //删除缩略图
FileHelper.DeleteFile(dr.original_path); //删除原图
}
}
}
/// <summary>
/// 删除已移除的图片扩展字段
/// </summary>
private void ThumDelete(IDbConnection conn, IDbTransaction trans, List <Model.site_channel_thum> thums, int channel_id)
{
List <Model.site_channel_thum> thum = new List <Model.site_channel_thum>();
thum = WriteDataBase.Query <Model.site_channel_thum>(conn, trans, Sql.Builder.Select("id").From(databaseprefix + "site_channel_thum").Where("channel_id=" + channel_id)).ToList();
foreach (var dr in thum)
{
Model.site_channel_thum model = thums.Find(p => p.id == dr.id); //查找对应的字段ID
if (model == null)
{
WriteDataBase.Execute(conn, trans, "delete from " + databaseprefix + "site_channel_thum where channel_id=" + channel_id + " and id=" + dr.id);//删除该行
}
}
}
/// <summary>
/// 获取一个集合
/// </summary>
/// <param name="sqlwhere">条件</param>
/// <returns>bool</returns>
public List <T> GetModelList(string sql, params object[] args)
{
try
{
List <T> t = WriteDataBase.Query <T>(sql, args).ToList();
return(t);
}
catch (Exception ex)
{
LogHelper.Error(ex);
WriteDataBase.CloseSharedConnection();
return(null);
}
finally
{
WriteDataBase.CloseSharedConnection();
}
}
/// <summary>
/// 编辑扩展字段及频道数据表
/// </summary>
private void FieldUpdate(IDbConnection conn, IDbTransaction trans, Model.site_channel newModel, Model.site_channel oldModel)
{
if (newModel.channel_fields != null)
{
string newFieldIds = string.Empty; //用来存储新增的字段ID
//添加扩展字段
StringBuilder strSql1;
foreach (Model.site_channel_field modelt in newModel.channel_fields)
{
strSql1 = new StringBuilder();
Model.site_channel_field fieldModel = null;
if (oldModel.channel_fields != null)
{
fieldModel = oldModel.channel_fields.Find(p => p.field_id == modelt.field_id); //查找是否已经存在
}
if (fieldModel == null) //如果不存在则添加
{
newFieldIds += modelt.field_id + ","; //以逗号分隔开存储
strSql1.Append("insert into " + databaseprefix + "site_channel_field(");
strSql1.Append("channel_id,field_id)");
strSql1.Append(" values (");
strSql1.Append("@0,@1)");
WriteDataBase.Execute(conn, trans, strSql1.ToString(), modelt.channel_id, modelt.field_id);
}
}
//添加频道数据表列
if (newFieldIds.Length > 0)
{
List <Model.article_attribute_field> field = new List <Model.article_attribute_field>();
field = WriteDataBase.Query <Model.article_attribute_field>(conn, trans, Sql.Builder.Select("id,name,data_type").From(databaseprefix + "article_attribute_field").Where("id in(" + newFieldIds.TrimEnd(',') + ")")).ToList();
foreach (var dr in field)
{
ReadDataBase.Execute(conn, trans, "alter table " + databaseprefix + DTKeys.TABLE_CHANNEL_ARTICLE + oldModel.name + " add " + dr.name + " " + dr.data_type);
//(!string.IsNullOrEmpty(dr.default_value) ? "DEFAULT "+ dr.default_value : "")
}
}
}
//如果频道名称改变则需要更改数据表名
if (newModel.name != oldModel.name)
{
ReadDataBase.Execute(conn, trans, "exec sp_rename '" + databaseprefix + DTKeys.TABLE_CHANNEL_ARTICLE + oldModel.name + "', '" + databaseprefix + DTKeys.TABLE_CHANNEL_ARTICLE + newModel.name + "'");
}
}
/// <summary>
/// 修改子节点的ID列表及深度（自身迭代）
/// </summary>
/// <param name="parent_id"></param>
private void UpdateChilds(IDbConnection conn, IDbTransaction trans, int parent_id)
{
//查找父节点信息
Model.article_category model = GetModel(conn, trans, parent_id);
if (model != null)
{
//查找子节点
List <Model.article_category> category = new List <Model.article_category>();
category = WriteDataBase.Query <Model.article_category>(conn, trans, Sql.Builder.Select("id").From(TableName).Where("parent_id=" + parent_id)).ToList();
foreach (var dr in category)
{
//修改子节点的ID列表及深度
int id = dr.id;
string class_list = model.class_list + id + ",";
int class_layer = model.class_layer + 1;
WriteDataBase.Execute(conn, trans, "update " + databaseprefix + "article_category set class_list='" + class_list + "', class_layer=" + class_layer + " where id=" + id); //带事务
//调用自身迭代
this.UpdateChilds(conn, trans, id); //带事务
}
}
}
/// <summary>
/// 获取一个集合
/// </summary>
/// <param name="sqlwhere">条件</param>
/// <param name="files">查询字段</param>
/// <param name="orderby">排序</param>
/// <returns>bool</returns>
public List <T> GetModelList(int top, string sqlwhere, string files, string orderby)
{
if (string.IsNullOrEmpty(sqlwhere))
{
sqlwhere = "1=1";
}
if (string.IsNullOrEmpty(files))
{
files = "*";
}
if (string.IsNullOrEmpty(orderby))
{
orderby = "id desc";
}
try
{
List <T> t = null;
if (top != 0)
{
t = WriteDataBase.Query <T>(Sql.Builder.Select(string.Format("top {0} {1}", top, files)).From(TableName).Where(sqlwhere).OrderBy(orderby)).ToList();
}
else
{
t = WriteDataBase.Query <T>(Sql.Builder.Select(files).From(TableName).Where(sqlwhere).OrderBy(orderby)).ToList();
}
return(t);
}
catch (Exception ex)
{
LogHelper.Error(ex);
WriteDataBase.CloseSharedConnection();
return(null);
}
finally
{
WriteDataBase.CloseSharedConnection();
}
}