/// <summary> /// 删除一条数据 /// </summary> public void Delete(int id) { Model.article_category model = GetModel(id); if (null != model) { //修改以事件删除数据时,同时删时删除权限 using (SqlConnection conn = new SqlConnection(DbHelperSQL.connectionString)) { conn.Open(); using (SqlTransaction trans = conn.BeginTransaction()) { try { //频道名称 object name = DbHelperSQL.GetSingle(conn, trans, "select top 1 name from [" + databaseprefix + "channel] where id=" + model.channel_id); //带事务 if (null != name) { //先判断选中是否存在子节点 if (Convert.ToInt32(DbHelperSQL.GetSingle(conn, trans, "select count(1) from [" + databaseprefix + "article_category] where parent_id=" + model.id)) > 0) { DbHelperSQL.ExecuteSql(conn, trans, "update [" + databaseprefix + "article_category] set parent_id=" + model.parent_id + " where parent_id=" + model.id); UpdateChilds(conn, trans, model.parent_id); //带事务 //修改权限菜单 Model.navigation modelt = new DAL.navigation(databaseprefix).GetModel(conn, trans, "channel_" + name.ToString() + "_category_" + model.id); if (null != modelt) { DbHelperSQL.ExecuteSql(conn, trans, "update [" + databaseprefix + "navigation] set parent_id=" + modelt.parent_id + " where parent_id=" + modelt.id); } } //删除权限菜单 DbHelperSQL.ExecuteSql(conn, trans, "delete from [" + databaseprefix + "navigation] where channel_id=" + model.channel_id + " and name='" + "channel_" + name.ToString() + "_category_" + model.id + "'"); //删除角色权限 DbHelperSQL.ExecuteSql(conn, trans, "delete from [" + databaseprefix + "manager_role_value] where nav_name='" + "channel_" + name.ToString() + "_category_" + model.id + "'"); } //删除类别 StringBuilder strSql = new StringBuilder(); strSql.Append("delete from [" + databaseprefix + "article_category] where id=@id"); SqlParameter[] parameters = { new SqlParameter("@id", SqlDbType.Int, 4) }; parameters[0].Value = id; DbHelperSQL.ExecuteSql(conn, trans, strSql.ToString(), parameters); trans.Commit(); } catch { trans.Rollback(); } } } //删除图片 Utils.DeleteFile(model.img_url); } }
public navigation() { dal = new DAL.navigation(sysConfig.sysdatabaseprefix); }
private readonly Model.siteconfig siteConfig = new BLL.siteconfig().loadConfig(); //���վ��������Ϣ #endregion Fields #region Constructors public navigation() { dal = new DAL.navigation(siteConfig.sysdatabaseprefix); }
/// <summary> /// 增加一条数据及其子表 /// </summary> public int Add(Model.site_channel model) { //取得站点对应的导航,如果站点导航不存在则退出新增 int parent_id = new DAL.sites(databaseprefix).GetSiteNavId(model.site_id); if (parent_id == 0) { return(0); } using (SqlConnection conn = new SqlConnection(DbHelperSQL.connectionString)) { conn.Open(); //打开数据连接 using (SqlTransaction trans = conn.BeginTransaction()) { try { #region 写入频道表数据================== StringBuilder strSql = new StringBuilder(); StringBuilder str1 = new StringBuilder(); //数据字段 StringBuilder str2 = new StringBuilder(); //数据参数 //利用反射获得属性的所有公共属性 PropertyInfo[] pros = model.GetType().GetProperties(); List <SqlParameter> paras = new List <SqlParameter>(); strSql.Append("insert into " + databaseprefix + "site_channel("); foreach (PropertyInfo pi in pros) { //如果不是主键和List<T>类型则追加sql字符串 if (!pi.Name.Equals("id") && !typeof(System.Collections.IList).IsAssignableFrom(pi.PropertyType)) { //判断属性值是否为空 if (pi.GetValue(model, null) != null && !pi.GetValue(model, null).ToString().Equals("")) { str1.Append(pi.Name + ","); //拼接字段 str2.Append("@" + pi.Name + ","); //声明参数 paras.Add(new SqlParameter("@" + pi.Name, pi.GetValue(model, null))); //对参数赋值 } } } strSql.Append(str1.ToString().Trim(',')); strSql.Append(") values ("); strSql.Append(str2.ToString().Trim(',')); strSql.Append(") "); strSql.Append(";select @@IDENTITY;"); object obj = DbHelperSQL.GetSingle(conn, trans, strSql.ToString(), paras.ToArray());//带事务 model.id = Convert.ToInt32(obj); #endregion //写入扩展字段及创建频道数据表 FieldAdd(conn, trans, model); #region 写入导航数据=============== int newNavId = new DAL.navigation(databaseprefix).Add(conn, trans, parent_id, "channel_" + model.name, model.title, "", model.sort_id, model.id, "Show"); new DAL.navigation(databaseprefix).Add(conn, trans, newNavId, "channel_" + model.name + "_list", "内容管理", "article/article_list.aspx", 99, model.id, "Show,View,Add,Edit,Delete,Audit"); new DAL.navigation(databaseprefix).Add(conn, trans, newNavId, "channel_" + model.name + "_category", "栏目类别", "article/category_list.aspx", 100, model.id, "Show,View,Add,Edit,Delete"); //开启规格新增菜单 if (model.is_spec > 0) { new DAL.navigation(databaseprefix).Add(conn, trans, newNavId, "channel_" + model.name + "_spec", "规格管理", "article/spec_list.aspx", 102, model.id, "Show,View,Add,Edit,Delete"); } //开启评论则新增菜单 if (model.is_comment > 0) { new DAL.navigation(databaseprefix).Add(conn, trans, newNavId, "channel_" + model.name + "_comment", "评论管理", "article/comment_list.aspx", 103, model.id, "Show,View,Delete,Reply"); } #endregion trans.Commit();//提交事务 } catch { trans.Rollback();//回滚事务 return(0); } } } return(model.id); }
/// <summary> /// 增加一条数据及其子表 /// </summary> public int Add(Model.channel model) { //取得站点对应的导航ID int parent_id = new DAL.channel_site(databaseprefix).GetSiteNavId(model.site_id); if (parent_id == 0) { return(0); } using (MySqlConnection conn = new MySqlConnection(DbHelperMySql.connectionString)) { conn.Open(); using (MySqlTransaction trans = conn.BeginTransaction()) { try { StringBuilder strSql = new StringBuilder(); strSql.Append("insert into " + databaseprefix + "channel("); strSql.Append("site_id,name,title,is_albums,is_attach,is_spec,sort_id)"); strSql.Append(" values ("); strSql.Append("@site_id,@name,@title,@is_albums,@is_attach,@is_spec,@sort_id)"); MySqlParameter[] parameters = { new MySqlParameter("@site_id", MySqlDbType.Int32, 4), new MySqlParameter("@name", MySqlDbType.VarChar, 50), new MySqlParameter("@title", MySqlDbType.VarChar, 100), new MySqlParameter("@is_albums", MySqlDbType.Int32, 4), new MySqlParameter("@is_attach", MySqlDbType.Int32, 4), new MySqlParameter("@is_spec", MySqlDbType.Int32, 4), new MySqlParameter("@sort_id", MySqlDbType.Int32, 4) }; parameters[0].Value = model.site_id; parameters[1].Value = model.name; parameters[2].Value = model.title; parameters[3].Value = model.is_albums; parameters[4].Value = model.is_attach; parameters[5].Value = model.is_spec; parameters[6].Value = model.sort_id; DbHelperMySql.ExecuteSql(conn, trans, strSql.ToString(), parameters); //取得新插入的ID model.id = GetMaxId(conn, trans); //扩展字段 if (model.channel_fields != null) { StringBuilder strSql2; foreach (Model.channel_field modelt in model.channel_fields) { strSql2 = new StringBuilder(); strSql2.Append("insert into " + databaseprefix + "channel_field("); strSql2.Append("channel_id,field_id)"); strSql2.Append(" values ("); strSql2.Append("@channel_id,@field_id)"); MySqlParameter[] parameters2 = { new MySqlParameter("@channel_id", MySqlDbType.Int32, 4), new MySqlParameter("@field_id", MySqlDbType.Int32, 4) }; parameters2[0].Value = model.id; parameters2[1].Value = modelt.field_id; DbHelperMySql.ExecuteSql(conn, trans, strSql2.ToString(), parameters2); } } //添加视图 StringBuilder strSql3 = new StringBuilder(); strSql3.Append("CREATE VIEW view_channel_" + model.name + " as"); strSql3.Append(" SELECT " + databaseprefix + "article.*"); if (model.channel_fields != null) { foreach (Model.channel_field modelt in model.channel_fields) { Model.article_attribute_field fieldModel = new DAL.article_attribute_field(databaseprefix).GetModel(modelt.field_id); if (fieldModel != null) { strSql3.Append("," + databaseprefix + "article_attribute_value." + fieldModel.name); } } } strSql3.Append(" FROM " + databaseprefix + "article_attribute_value INNER JOIN"); strSql3.Append(" " + databaseprefix + "article ON " + databaseprefix + "article_attribute_value.article_id = " + databaseprefix + "article.id"); strSql3.Append(" WHERE " + databaseprefix + "article.channel_id=" + model.id); DbHelperMySql.ExecuteSql(conn, trans, strSql3.ToString()); //添加导航菜单 int newNavId = new DAL.navigation(databaseprefix).Add(conn, trans, parent_id, "channel_" + model.name, model.title, "", model.sort_id, model.id, "Show"); new DAL.navigation(databaseprefix).Add(conn, trans, newNavId, "channel_" + model.name + "_list", "内容管理", "article/article_list.aspx", 99, model.id, "Show,View,Add,Edit,Delete,Audit"); new DAL.navigation(databaseprefix).Add(conn, trans, newNavId, "channel_" + model.name + "_category", "栏目类别", "article/category_list.aspx", 100, model.id, "Show,View,Add,Edit,Delete"); new DAL.navigation(databaseprefix).Add(conn, trans, newNavId, "channel_" + model.name + "_comment", "评论管理", "article/comment_list.aspx", 101, model.id, "Show,View,Delete,Reply"); trans.Commit(); } catch { trans.Rollback(); return(0); } } } return(model.id); }
public navigation() { dal = new DAL.navigation(databaseprefix); }
/// <summary> /// 更新一条数据 /// </summary> public bool Update(Model.site_channel model) { int i = 0; Model.site_channel oldModel = GetModel(model.id); //旧的数据 //取得站点对应的导航ID int parent_id = new DAL.sites(databaseprefix).GetSiteNavId(model.site_id); if (parent_id == 0) { return(false); } using (IDbConnection conn = new DapperView().Context()) { using (IDbTransaction trans = conn.BeginTransaction()) { try { #region 修改频道表====================== StringBuilder strSql = new StringBuilder(); StringBuilder str1 = new StringBuilder(); //利用反射获得属性的所有公共属性 PropertyInfo[] pros = model.GetType().GetProperties(); List <object> paras = new List <object>(); strSql.Append("update " + databaseprefix + "site_channel set "); foreach (PropertyInfo pi in pros) { //如果不是主键则追加sql字符串 //!pi.Name.Equals("channel_fields") if (!pi.Name.Equals("id") && !typeof(System.Collections.IList).IsAssignableFrom(pi.PropertyType)) { //判断属性值是否为空 if (pi.GetValue(model, null) != null) { str1.Append(pi.Name + "=@" + i + ","); //声明参数 i++; paras.Add(pi.GetValue(model, null)); //对参数赋值 } } } strSql.Append(str1.ToString().Trim(',')); strSql.Append(" where id=@" + i + " "); paras.Add(model.id); WriteDataBase.Execute(conn, trans, strSql.ToString(), paras.ToArray()); #endregion //删除已移除扩展字段及频道数据表列 FieldDelete(conn, trans, model, oldModel); //编辑扩展字段及频道数据表 FieldUpdate(conn, trans, model, oldModel); #region 缩略图尺寸 //删除已移除的尺寸 ThumDelete(conn, trans, model.channel_thums, model.id); //添加扩展字段 if (model.channel_thums != null) { StringBuilder strSql2; //SQL字符串 StringBuilder str21; //数据库字段 StringBuilder str22; //声明参数 foreach (Model.site_channel_thum modelt in model.channel_thums) { if (modelt.id > 0) { i = 0; //更新 strSql2 = new StringBuilder(); str21 = new StringBuilder(); PropertyInfo[] pros2 = modelt.GetType().GetProperties(); List <object> paras2 = new List <object>(); strSql2.Append("update " + databaseprefix + "site_channel_thum set "); foreach (PropertyInfo pi in pros2) { //如果不是主键则追加sql字符串 if (!pi.Name.Equals("id")) { //判断属性值是否为空 if (pi.GetValue(modelt, null) != null && !pi.GetValue(modelt, null).ToString().Equals("")) { str21.Append(pi.Name + "=@" + i + ","); //声明参数 i++; paras2.Add(pi.GetValue(modelt, null)); //对参数赋值 } } } strSql2.Append(str21.ToString().Trim(',')); strSql2.Append(" where id=@" + i + " "); paras2.Add(modelt.id); WriteDataBase.Execute(conn, trans, strSql2.ToString(), paras2.ToArray()); } else { i = 0; //新增 strSql2 = new StringBuilder(); str21 = new StringBuilder(); str22 = new StringBuilder(); PropertyInfo[] pros2 = modelt.GetType().GetProperties(); List <object> paras2 = new List <object>(); strSql2.Append("insert into " + databaseprefix + "site_channel_thum("); foreach (PropertyInfo pi in pros2) { if (!pi.Name.Equals("id")) { if (pi.GetValue(modelt, null) != null && !pi.GetValue(modelt, null).ToString().Equals("")) { str21.Append(pi.Name + ","); str22.Append("@" + i + ","); i++; if (pi.Name.Equals("channel_id")) { paras2.Add(model.id); //将规则ID赋值 } else { paras2.Add(pi.GetValue(modelt, null)); } } } } strSql2.Append(str21.ToString().Trim(',')); strSql2.Append(") values ("); strSql2.Append(str22.ToString().Trim(',')); strSql2.Append(") "); WriteDataBase.Execute(conn, trans, strSql2.ToString(), paras2.ToArray()); } } } #endregion #region int newNavId = 0; DAL.navigation dal = new DAL.navigation(databaseprefix); if (!dal.Exists("channel_" + model.name)) { //添加导航菜单 newNavId = dal.Add(conn, trans, parent_id, "channel_" + model.name, model.title, "", model.sort_id, model.id, "Show"); dal.Add(conn, trans, newNavId, "channel_" + model.name + "_list", "内容管理", "//admin/article/article_list", 99, model.id, "Show,View,Add,Edit,Delete,Audit"); dal.Add(conn, trans, newNavId, "channel_" + model.name + "_category", "栏目类别", "//admin/article/category_list", 100, model.id, "Show,View,Add,Edit,Delete"); //是否开启规格 if (model.is_spec > 0) { dal.Add(conn, trans, newNavId, "channel_" + model.name + "_spec", "规格管理", "//admin/article/spec_list", 101, model.id, "Show,View,Add,Edit,Delete"); } //是否开启评论 if (model.is_comment > 0) { dal.Add(conn, trans, newNavId, "channel_" + model.name + "_comment", "评论管理", "//admin/article/comment_list", 101, model.id, "Show,View,Delete,Audit,Reply"); } //是否开启回收站 if (model.is_recycle > 0) { dal.Add(conn, trans, newNavId, "channel_" + model.name + "_recycle", "回收站", "//admin/article/recycle_list", 102, model.id, "Show,View,Edit,Delete,Audit"); } } else { //修改菜单 newNavId = new DAL.navigation(databaseprefix).GetId(conn, trans, "channel_" + oldModel.name); dal.Update(conn, trans, "channel_" + oldModel.name, parent_id, "channel_" + model.name, model.title, model.sort_id); dal.Update(conn, trans, "channel_" + oldModel.name + "_list", "channel_" + model.name + "_list"); //内容管理 dal.Update(conn, trans, "channel_" + oldModel.name + "_category", "channel_" + model.name + "_category"); //栏目类别 //是否开启规格 if (model.is_spec > 0) { if (ReadDataBase.ExecuteScalar <int>(conn, trans, "select count(*) as H from [" + databaseprefix + "navigation] where name='channel_" + model.name + "_spec'") == 0) { dal.Add(conn, trans, newNavId, "channel_" + model.name + "_spec", "规格管理", "//admin/article/spec_list", 101, model.id, "Show,View,Add,Edit,Delete"); } else { dal.Update(conn, trans, "channel_" + oldModel.name + "_spec", "channel_" + model.name + "_spec"); //评论管理 } } else { dal.Delete(conn, trans, "channel_" + model.name + "_spec"); } //是否开启评论 if (model.is_comment > 0) { if (ReadDataBase.ExecuteScalar <int>(conn, trans, "select count(*) as H from [" + databaseprefix + "navigation] where name='channel_" + model.name + "_comment'") == 0) { dal.Add(conn, trans, newNavId, "channel_" + model.name + "_comment", "评论管理", "//admin/article/comment_list", 101, model.id, "Show,View,Delete,Audit,Reply"); } else { dal.Update(conn, trans, "channel_" + oldModel.name + "_comment", "channel_" + model.name + "_comment"); //评论管理 } } else { dal.Delete(conn, trans, "channel_" + model.name + "_comment"); } //是否开启回收站 if (model.is_recycle > 0) { if (ReadDataBase.ExecuteScalar <int>(conn, trans, "select count(*) as H from [" + databaseprefix + "navigation] where name='channel_" + model.name + "_recycle'") == 0) { dal.Add(conn, trans, newNavId, "channel_" + model.name + "_recycle", "回收站", "article/recycle_list.aspx", 102, model.id, "Show,View,Edit,Delete,Audit"); } else { dal.Update(conn, trans, "channel_" + oldModel.name + "_recycle", "channel_" + model.name + "_recycle"); //回收站 } } else { dal.Delete(conn, trans, "channel_" + model.name + "_recycle"); } } #endregion trans.Commit(); } catch (Exception ex) { trans.Rollback(); return(false); } } } return(true); }
/// <summary> /// 增加一条数据及其子表 /// </summary> public int Add(Model.site_channel model) { int i = 0; //取得站点对应的导航ID int parent_id = new DAL.sites(databaseprefix).GetSiteNavId(model.site_id); if (parent_id == 0) { return(0); } using (IDbConnection conn = new DapperView().Context()) { using (IDbTransaction trans = conn.BeginTransaction()) { try { #region 写入频道表数据================== StringBuilder strSql = new StringBuilder(); StringBuilder str1 = new StringBuilder(); //数据字段 StringBuilder str2 = new StringBuilder(); //数据参数 //利用反射获得属性的所有公共属性 PropertyInfo[] pros = model.GetType().GetProperties(); List <object> paras = new List <object>(); strSql.Append("insert into " + databaseprefix + "site_channel("); foreach (PropertyInfo pi in pros) { //如果不是主键和List<T>类型则追加sql字符串 if (!pi.Name.Equals("id") && !typeof(System.Collections.IList).IsAssignableFrom(pi.PropertyType)) { //判断属性值是否为空 if (pi.GetValue(model, null) != null) { str1.Append(pi.Name + ","); //拼接字段 str2.Append("@" + i + ","); //声明参数 i++; paras.Add(pi.GetValue(model, null)); //对参数赋值 } } } strSql.Append(str1.ToString().Trim(',')); strSql.Append(") values ("); strSql.Append(str2.ToString().Trim(',')); strSql.Append(") "); strSql.Append(";SELECT @@@IDENTITY;"); object obj = WriteDataBase.ExecuteScalar <object>(conn, trans, strSql.ToString(), paras.ToArray()); model.id = Convert.ToInt32(obj); #endregion //写入扩展字段及创建频道数据表 FieldAdd(conn, trans, model); #region 缩略图字段 //添加缩略图字段 if (model.channel_thums != null) { StringBuilder strSql2; //SQL字符串 StringBuilder str21; //数据库字段 StringBuilder str22; //声明参数 foreach (Model.site_channel_thum modelt in model.channel_thums) { i = 0; //新增 strSql2 = new StringBuilder(); str21 = new StringBuilder(); str22 = new StringBuilder(); PropertyInfo[] pros2 = modelt.GetType().GetProperties(); List <object> paras2 = new List <object>(); strSql2.Append("insert into " + databaseprefix + "site_channel_thum("); foreach (PropertyInfo pi in pros2) { if (!pi.Name.Equals("id")) { if (pi.GetValue(modelt, null) != null && !pi.GetValue(modelt, null).ToString().Equals("")) { str21.Append(pi.Name + ","); str22.Append("@" + i + ","); i++; if (pi.Name.Equals("channel_id")) { paras2.Add(model.id); //将规则ID赋值 } else { paras2.Add(pi.GetValue(modelt, null)); } } } } strSql2.Append(str21.ToString().Trim(',')); strSql2.Append(") values ("); strSql2.Append(str22.ToString().Trim(',')); strSql2.Append(") "); WriteDataBase.Execute(conn, trans, strSql2.ToString(), paras2.ToArray()); } } #endregion #region 写入导航数据=============== DAL.navigation dal = new DAL.navigation(databaseprefix); int newNavId = dal.Add(conn, trans, parent_id, "channel_" + model.name, model.title, "", model.sort_id, model.id, "Show"); dal.Add(conn, trans, newNavId, "channel_" + model.name + "_list", "内容管理", "/admin/article/article_list", 99, model.id, "Show,View,Add,Edit,Delete,Audit"); dal.Add(conn, trans, newNavId, "channel_" + model.name + "_category", "栏目类别", "/admin/article/category_list", 100, model.id, "Show,View,Add,Edit,Delete"); //开启规格新增菜单 if (model.is_spec > 0) { new DAL.navigation(databaseprefix).Add(conn, trans, newNavId, "channel_" + model.name + "_spec", "规格管理", "/admin/article/spec_list", 102, model.id, "Show,View,Add,Edit,Delete"); } if (model.is_comment == 1) { dal.Add(conn, trans, newNavId, "channel_" + model.name + "_comment", "评论管理", "/admin/article/comment_list", 101, model.id, "Show,View,Delete,Reply"); } if (model.is_recycle == 1) { dal.Add(conn, trans, newNavId, "channel_" + model.name + "_recycle", "回收站", "/admin/article/recycle_list", 102, model.id, "Show,View,Edit,Delete,Audit"); } #endregion trans.Commit(); } catch (Exception ex) { trans.Rollback(); return(0); } } } return(model.id); }
public navigation() { dal = new DAL.navigation(); }
/// <summary> /// 更新一条数据 /// </summary> public bool Update(Model.channel model) { Model.channel oldModel = GetModel(model.id); //旧的数据 //取得站点对应的导航ID int parent_id = new DAL.channel_site(databaseprefix).GetSiteNavId(model.site_id); if (parent_id == 0) { return(false); } using (SqlConnection conn = new SqlConnection(DbHelperSQL.connectionString)) { conn.Open(); using (SqlTransaction trans = conn.BeginTransaction()) { try { StringBuilder strSql = new StringBuilder(); strSql.Append("update [" + databaseprefix + "channel] set "); strSql.Append("site_id=@site_id,"); strSql.Append("name=@name,"); strSql.Append("title=@title,"); strSql.Append("is_albums=@is_albums,"); strSql.Append("is_attach=@is_attach,"); strSql.Append("is_spec=@is_spec,"); strSql.Append("sort_id=@sort_id,"); strSql.Append("seo_title=@seo_title,"); strSql.Append("seo_keywords=@seo_keywords,"); strSql.Append("seo_description=@seo_description,"); strSql.Append("is_type=@is_type,"); strSql.Append("is_attribute=@is_attribute,"); strSql.Append("is_comment=@is_comment,"); strSql.Append("height=@height,"); strSql.Append("width=@width,"); strSql.Append("is_recycle=@is_recycle"); strSql.Append(" where id=@id"); SqlParameter[] parameters = { new SqlParameter("@site_id", SqlDbType.Int, 4), new SqlParameter("@name", SqlDbType.VarChar, 50), new SqlParameter("@title", SqlDbType.VarChar, 100), new SqlParameter("@is_albums", SqlDbType.TinyInt, 1), new SqlParameter("@is_attach", SqlDbType.TinyInt, 1), new SqlParameter("@is_spec", SqlDbType.TinyInt, 1), new SqlParameter("@sort_id", SqlDbType.Int, 4), new SqlParameter("@seo_title", SqlDbType.NVarChar, 255), new SqlParameter("@seo_keywords", SqlDbType.NVarChar, 255), new SqlParameter("@seo_description", SqlDbType.NVarChar, 255), new SqlParameter("@is_type", SqlDbType.TinyInt, 1), new SqlParameter("@is_attribute", SqlDbType.TinyInt, 1), new SqlParameter("@is_comment", SqlDbType.TinyInt, 1), new SqlParameter("@height", SqlDbType.Int, 4), new SqlParameter("@width", SqlDbType.Int, 4), new SqlParameter("@is_recycle", SqlDbType.TinyInt, 1), new SqlParameter("@id", SqlDbType.Int, 4) }; parameters[0].Value = model.site_id; parameters[1].Value = model.name; parameters[2].Value = model.title; parameters[3].Value = model.is_albums; parameters[4].Value = model.is_attach; parameters[5].Value = model.is_spec; parameters[6].Value = model.sort_id; parameters[7].Value = model.seo_title; parameters[8].Value = model.seo_keywords; parameters[9].Value = model.seo_description; parameters[10].Value = model.is_type; parameters[11].Value = model.is_attribute; parameters[12].Value = model.is_comment; parameters[13].Value = model.height; parameters[14].Value = model.width; parameters[15].Value = model.is_recycle; parameters[16].Value = model.id; DbHelperSQL.ExecuteSql(conn, trans, strSql.ToString(), parameters); //删除已移除的扩展字段 FieldDelete(conn, trans, model.channel_fields, model.id); //添加扩展字段 if (model.channel_fields != null) { StringBuilder strSql2; foreach (Model.channel_field modelt in model.channel_fields) { strSql2 = new StringBuilder(); Model.channel_field fieldModel = null; if (oldModel.channel_fields != null) { fieldModel = oldModel.channel_fields.Find(p => p.field_id == modelt.field_id); //查找是否已经存在 } if (fieldModel == null) //如果不存在则添加 { strSql2.Append("insert into " + databaseprefix + "channel_field("); strSql2.Append("channel_id,field_id)"); strSql2.Append(" values ("); strSql2.Append("@channel_id,@field_id)"); SqlParameter[] parameters2 = { new SqlParameter("@channel_id", SqlDbType.Int, 4), new SqlParameter("@field_id", SqlDbType.Int, 4) }; parameters2[0].Value = modelt.channel_id; parameters2[1].Value = modelt.field_id; DbHelperSQL.ExecuteSql(conn, trans, strSql2.ToString(), parameters2); } } } //删除旧视图重建新视图 RehabChannelViews(conn, trans, model, oldModel.name); int newNavId = 0; DAL.navigation dal = new DAL.navigation(databaseprefix); if (!dal.Exists("channel_" + oldModel.name)) { //添加导航菜单 newNavId = dal.Add(conn, trans, parent_id, "channel_" + model.name, model.title, "", model.sort_id, model.id, "Show"); dal.Add(conn, trans, newNavId, "channel_" + model.name + "_list", "内容管理", "article/article_list.aspx", 99, model.id, "Show,View,Add,Edit,Delete,Audit"); dal.Add(conn, trans, newNavId, "channel_" + model.name + "_category", "栏目类别", "article/category_list.aspx", 100, model.id, "Show,View,Add,Edit,Delete"); //是否开启评论 if (model.is_comment > 0) { dal.Add(conn, trans, newNavId, "channel_" + model.name + "_comment", "评论管理", "article/comment_list.aspx", 101, model.id, "Show,View,Delete,Audit,Reply"); } //是否开启回收站 if (model.is_recycle > 0) { dal.Add(conn, trans, newNavId, "channel_" + model.name + "_recycle", "回收站", "article/recycle_list.aspx", 102, model.id, "Show,View,Edit,Delete,Audit"); } } else { //修改菜单 newNavId = new DAL.navigation(databaseprefix).GetId(conn, trans, "channel_" + oldModel.name); dal.Update(conn, trans, "channel_" + oldModel.name, parent_id, "channel_" + model.name, model.title, model.sort_id); dal.Update(conn, trans, "channel_" + oldModel.name + "_list", "channel_" + model.name + "_list"); //内容管理 dal.Update(conn, trans, "channel_" + oldModel.name + "_category", "channel_" + model.name + "_category"); //栏目类别 //是否开启评论 if (model.is_comment > 0) { if (Convert.ToInt32(DbHelperSQL.GetSingle(conn, trans, "select count(*) as H from [" + databaseprefix + "navigation] where name='channel_" + oldModel.name + "_comment'")) == 0) { dal.Add(conn, trans, newNavId, "channel_" + model.name + "_comment", "评论管理", "article/comment_list.aspx", 101, model.id, "Show,View,Delete,Audit,Reply"); } else { dal.Update(conn, trans, "channel_" + oldModel.name + "_comment", "channel_" + model.name + "_comment"); //评论管理 } } else { dal.Delete(conn, trans, "channel_" + oldModel.name + "_comment"); } //是否开启回收站 if (model.is_recycle > 0) { if (Convert.ToInt32(DbHelperSQL.GetSingle(conn, trans, "select count(*) as H from [" + databaseprefix + "navigation] where name='channel_" + oldModel.name + "_recycle'")) == 0) { dal.Add(conn, trans, newNavId, "channel_" + model.name + "_recycle", "回收站", "article/recycle_list.aspx", 102, model.id, "Show,View,Edit,Delete,Audit"); } else { dal.Update(conn, trans, "channel_" + oldModel.name + "_recycle", "channel_" + model.name + "_recycle"); //回收站 } } else { dal.Delete(conn, trans, "channel_" + oldModel.name + "_recycle"); } } trans.Commit(); } catch { trans.Rollback(); return(false); } } } return(true); }
/// <summary> /// 增加一条数据及其子表 /// </summary> public int Add(Model.channel model) { //取得站点对应的导航ID int parent_id = new DAL.channel_site(databaseprefix).GetSiteNavId(model.site_id); if (parent_id == 0) { return(0); } using (SqlConnection conn = new SqlConnection(DbHelperSQL.connectionString)) { conn.Open(); using (SqlTransaction trans = conn.BeginTransaction()) { try { StringBuilder strSql = new StringBuilder(); strSql.Append("insert into [" + databaseprefix + "channel]("); strSql.Append("site_id,name,title,is_albums,is_attach,is_spec,sort_id,seo_title,seo_keywords,seo_description,is_type,is_attribute,is_comment,height,width,is_recycle"); strSql.Append(") values("); strSql.Append("@site_id,@name,@title,@is_albums,@is_attach,@is_spec,@sort_id,@seo_title,@seo_keywords,@seo_description,@is_type,@is_attribute,@is_comment,@height,@width,@is_recycle)"); strSql.Append(";select @@IDENTITY"); SqlParameter[] parameters = { new SqlParameter("@site_id", SqlDbType.Int, 4), new SqlParameter("@name", SqlDbType.VarChar, 50), new SqlParameter("@title", SqlDbType.VarChar, 100), new SqlParameter("@is_albums", SqlDbType.TinyInt, 1), new SqlParameter("@is_attach", SqlDbType.TinyInt, 1), new SqlParameter("@is_spec", SqlDbType.TinyInt, 1), new SqlParameter("@sort_id", SqlDbType.Int, 4), new SqlParameter("@seo_title", SqlDbType.NVarChar, 255), new SqlParameter("@seo_keywords", SqlDbType.NVarChar, 255), new SqlParameter("@seo_description", SqlDbType.NVarChar, 255), new SqlParameter("@is_type", SqlDbType.TinyInt, 1), new SqlParameter("@is_attribute", SqlDbType.TinyInt, 1), new SqlParameter("@is_comment", SqlDbType.TinyInt, 1), new SqlParameter("@height", SqlDbType.Int, 4), new SqlParameter("@width", SqlDbType.Int, 4), new SqlParameter("@is_recycle", SqlDbType.TinyInt, 1) }; parameters[0].Value = model.site_id; parameters[1].Value = model.name; parameters[2].Value = model.title; parameters[3].Value = model.is_albums; parameters[4].Value = model.is_attach; parameters[5].Value = model.is_spec; parameters[6].Value = model.sort_id; parameters[7].Value = model.seo_title; parameters[8].Value = model.seo_keywords; parameters[9].Value = model.seo_description; parameters[10].Value = model.is_type; parameters[11].Value = model.is_attribute; parameters[12].Value = model.is_comment; parameters[13].Value = model.height; parameters[14].Value = model.width; parameters[15].Value = model.is_recycle; object obj = DbHelperSQL.GetSingle(conn, trans, strSql.ToString(), parameters); //带事务 model.id = Convert.ToInt32(obj); //扩展字段 if (model.channel_fields != null) { StringBuilder strSql2; foreach (Model.channel_field modelt in model.channel_fields) { strSql2 = new StringBuilder(); strSql2.Append("insert into " + databaseprefix + "channel_field("); strSql2.Append("channel_id,field_id)"); strSql2.Append(" values ("); strSql2.Append("@channel_id,@field_id)"); SqlParameter[] parameters2 = { new SqlParameter("@channel_id", SqlDbType.Int, 4), new SqlParameter("@field_id", SqlDbType.Int, 4) }; parameters2[0].Value = model.id; parameters2[1].Value = modelt.field_id; DbHelperSQL.ExecuteSql(conn, trans, strSql2.ToString(), parameters2); } } //添加视图 StringBuilder strSql3 = new StringBuilder(); strSql3.Append("CREATE VIEW view_channel_" + model.name + " as"); strSql3.Append(" SELECT " + databaseprefix + "article.*"); if (model.channel_fields != null) { foreach (Model.channel_field modelt in model.channel_fields) { Model.article_attribute_field fieldModel = new DAL.article_attribute_field(databaseprefix).GetModel(modelt.field_id); if (fieldModel != null) { strSql3.Append("," + databaseprefix + "article_attribute_value." + fieldModel.name); } } } strSql3.Append(" FROM " + databaseprefix + "article_attribute_value INNER JOIN"); strSql3.Append(" " + databaseprefix + "article ON " + databaseprefix + "article_attribute_value.article_id = " + databaseprefix + "article.id"); strSql3.Append(" WHERE " + databaseprefix + "article.channel_id=" + model.id); DbHelperSQL.ExecuteSql(conn, trans, strSql3.ToString()); //添加导航菜单 DAL.navigation dal = new DAL.navigation(databaseprefix); int newNavId = dal.Add(conn, trans, parent_id, "channel_" + model.name, model.title, "", model.sort_id, model.id, "Show"); dal.Add(conn, trans, newNavId, "channel_" + model.name + "_list", "内容管理", "article/article_list.aspx", 99, model.id, "Show,View,Add,Edit,Delete,Audit"); dal.Add(conn, trans, newNavId, "channel_" + model.name + "_category", "栏目类别", "article/category_list.aspx", 100, model.id, "Show,View,Add,Edit,Delete"); if (model.is_comment == 1) { dal.Add(conn, trans, newNavId, "channel_" + model.name + "_comment", "评论管理", "article/comment_list.aspx", 101, model.id, "Show,View,Delete,Reply"); } if (model.is_recycle == 1) { dal.Add(conn, trans, newNavId, "channel_" + model.name + "_recycle", "回收站", "article/recycle_list.aspx", 102, model.id, "Show,View,Edit,Delete,Audit"); } trans.Commit(); } catch { trans.Rollback(); return(0); } } } return(model.id); }
/// <summary> /// 增加一条数据及其子表 /// </summary> public int Add(Model.channel model) { //取得站点对应的导航ID int parent_id = new DAL.channel_site(databaseprefix).GetSiteNavId(model.site_id); if (parent_id == 0) { return 0; } using (SqlConnection conn = new SqlConnection(DbHelperSQL.connectionString)) { conn.Open(); using (SqlTransaction trans = conn.BeginTransaction()) { try { StringBuilder strSql=new StringBuilder(); strSql.Append("insert into " + databaseprefix + "channel("); strSql.Append("site_id,name,title,is_albums,is_attach,is_spec,sort_id)"); strSql.Append(" values ("); strSql.Append("@site_id,@name,@title,@is_albums,@is_attach,@is_spec,@sort_id)"); strSql.Append(";select @@IDENTITY"); SqlParameter[] parameters = { new SqlParameter("@site_id", SqlDbType.Int,4), new SqlParameter("@name", SqlDbType.VarChar,50), new SqlParameter("@title", SqlDbType.NVarChar,100), new SqlParameter("@is_albums", SqlDbType.TinyInt,1), new SqlParameter("@is_attach", SqlDbType.TinyInt,1), new SqlParameter("@is_spec", SqlDbType.TinyInt,1), new SqlParameter("@sort_id", SqlDbType.Int,4)}; parameters[0].Value = model.site_id; parameters[1].Value = model.name; parameters[2].Value = model.title; parameters[3].Value = model.is_albums; parameters[4].Value = model.is_attach; parameters[5].Value = model.is_spec; parameters[6].Value = model.sort_id; object obj = DbHelperSQL.GetSingle(conn, trans, strSql.ToString(), parameters); //带事务 model.id = Convert.ToInt32(obj); //扩展字段 if (model.channel_fields != null) { StringBuilder strSql2; foreach (Model.channel_field modelt in model.channel_fields) { strSql2 = new StringBuilder(); strSql2.Append("insert into " + databaseprefix + "channel_field("); strSql2.Append("channel_id,field_id)"); strSql2.Append(" values ("); strSql2.Append("@channel_id,@field_id)"); SqlParameter[] parameters2 = { new SqlParameter("@channel_id", SqlDbType.Int,4), new SqlParameter("@field_id", SqlDbType.Int,4)}; parameters2[0].Value = model.id; parameters2[1].Value = modelt.field_id; DbHelperSQL.ExecuteSql(conn, trans, strSql2.ToString(), parameters2); } } //添加视图 StringBuilder strSql3 = new StringBuilder(); strSql3.Append("CREATE VIEW view_channel_" + model.name + " as"); strSql3.Append(" SELECT " + databaseprefix + "article.*"); if (model.channel_fields != null) { foreach (Model.channel_field modelt in model.channel_fields) { Model.article_attribute_field fieldModel = new DAL.article_attribute_field(databaseprefix).GetModel(modelt.field_id); if (fieldModel != null) { strSql3.Append("," + databaseprefix + "article_attribute_value." + fieldModel.name); } } } strSql3.Append(" FROM " + databaseprefix + "article_attribute_value INNER JOIN"); strSql3.Append(" " + databaseprefix + "article ON " + databaseprefix + "article_attribute_value.article_id = " + databaseprefix + "article.id"); strSql3.Append(" WHERE " + databaseprefix + "article.channel_id=" + model.id); DbHelperSQL.ExecuteSql(conn, trans, strSql3.ToString()); //添加导航菜单 int newNavId = new DAL.navigation(databaseprefix).Add(conn, trans, parent_id, "channel_" + model.name, model.title, "", model.sort_id, model.id, "Show"); new DAL.navigation(databaseprefix).Add(conn, trans, newNavId, "channel_" + model.name + "_list", "内容管理", "article/article_list.aspx", 99, model.id, "Show,View,Add,Edit,Delete,Audit"); new DAL.navigation(databaseprefix).Add(conn, trans, newNavId, "channel_" + model.name + "_category", "栏目类别", "article/category_list.aspx", 100, model.id, "Show,View,Add,Edit,Delete"); new DAL.navigation(databaseprefix).Add(conn, trans, newNavId, "channel_" + model.name + "_comment", "评论管理", "article/comment_list.aspx", 101, model.id, "Show,View,Delete,Reply"); trans.Commit(); } catch { trans.Rollback(); return 0; } } } return model.id; }
/// <summary> /// 删除一条数据 /// </summary> public bool Delete(int id) { Model.article_category model = GetModel(id); if (null != model) { bool resault = true; //修改以事件删除数据时,同时删时删除权限 using (IDbConnection conn = new DapperView().Context()) { using (IDbTransaction trans = conn.BeginTransaction()) { try { //频道名称 object name = ReadDataBase.ExecuteScalar <object>(conn, trans, "select top 1 name from [" + databaseprefix + "site_channel] where id=" + model.channel_id); //带事务 if (null != name) { //先判断选中是否存在子节点 if (ReadDataBase.ExecuteScalar <int>(conn, trans, "select count(1) from [" + databaseprefix + "article_category] where parent_id=" + model.id) > 0) { WriteDataBase.Execute(conn, trans, "update [" + databaseprefix + "article_category] set parent_id=" + model.parent_id + " where parent_id=" + model.id); //带事务 UpdateChilds(conn, trans, model.parent_id); //带事务 //修改权限菜单 Model.navigation modelt = new DAL.navigation(databaseprefix).GetModel(conn, trans, "channel_" + name.ToString() + "_category_" + model.id); if (null != modelt) { WriteDataBase.Execute(conn, trans, "update [" + databaseprefix + "navigation] set parent_id=" + modelt.parent_id + " where parent_id=" + modelt.id); //带事务 } } //删除权限菜单 WriteDataBase.Execute(conn, trans, "delete from " + databaseprefix + "navigation where channel_id=" + model.channel_id + " and name='" + "channel_" + name.ToString() + "_category_" + model.id + "'");//删除角色权限 WriteDataBase.Execute(conn, trans, "delete from " + databaseprefix + "manager_role_value where nav_name='" + "channel_" + name.ToString() + "_category_" + model.id + "'"); } //删除栏目规格 StringBuilder strSql2 = new StringBuilder(); strSql2.Append("delete from " + databaseprefix + "article_category_spec"); strSql2.Append(" where category_id=@0"); WriteDataBase.Execute(conn, trans, strSql2.ToString(), id); //删除类别 StringBuilder strSql = new StringBuilder(); strSql.Append("delete from " + databaseprefix + "article_category where id=@0"); WriteDataBase.Execute(conn, trans, strSql.ToString(), id); trans.Commit(); } catch (Exception ex) { resault = false; trans.Rollback(); } } } if (resault) { //删除图片 Utils.DeleteFile(model.img_url); } return(resault); } return(false); }