/// <summary>
/// 增加一条数据
/// </summary>
public int Add(Model.user_amount_log model)
{
int i = 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 + "user_amount_log(");
foreach (PropertyInfo pi in pros)
{
//如果不是主键则追加sql字符串
if (!pi.Name.Equals("id"))
{
//判断属性值是否为空
if (pi.GetValue(model, null) != null && !pi.GetValue(model, null).ToString().Equals(""))
{
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
#region 用户表信息========================
StringBuilder strSql1 = new StringBuilder();
strSql1.Append("update " + databaseprefix + "users set amount=amount+" + model.value);
strSql1.Append(" where id=@0");
WriteDataBase.Execute(conn, trans, strSql1.ToString(), model.user_id);
#endregion
trans.Commit();//提交事务
}
catch (Exception ex)
{
trans.Rollback();//回滚事务
return(0);
}
}
}
return(model.id);
}
/// <summary>
/// 执行插件SQL语句
/// </summary>
public bool ExeSqlStr(string dirPath, string xPath)
{
bool result = true;
int count = 0;
List <string> ls = ReadChildNodesValue(dirPath + DTKeys.FILE_PLUGIN_XML_CONFING, xPath);
if (ls != null)
{
using (IDbConnection conn = new DapperView().Context())
{
using (IDbTransaction trans = conn.BeginTransaction())
{
try
{
count += WriteDataBase.ExecuteSqlTran(conn, trans, ls);
trans.Commit();
return(count > 0);
}
catch (Exception ex)
{
trans.Rollback();
return(false);
}
finally
{
conn.Close();
}
}
}
}
return(result);
}
/// <summary>
/// 删除一条数据
/// </summary>
public bool Delete(int id)
{
Model.article_attribute_field model = GetModel(id);//取得扩展字段实体
using (IDbConnection conn = new DapperView().Context())
{
using (IDbTransaction trans = conn.BeginTransaction())
{
try
{
//删除所关联的频道数据表相关列
DataTable dt = new DAL.site_channel(databaseprefix).GetFieldList(conn, trans, id).Tables[0];
if (dt.Rows.Count > 0)
{
foreach (DataRow dr in dt.Rows)
{
//检查有无该频道数据表和列
int rowsCount = ReadDataBase.ExecuteScalar <int>(conn, trans, "select count(1) from syscolumns where id=object_id('" + databaseprefix + DTKeys.TABLE_CHANNEL_ARTICLE + dr["name"].ToString() + "') and name='" + model.name + "'");
if (rowsCount > 0)
{
//删除频道数据表一列
WriteDataBase.Execute(conn, trans, "alter table " + databaseprefix + DTKeys.TABLE_CHANNEL_ARTICLE + dr["name"].ToString() + " drop column " + model.name);
}
}
}
//删除频道关联字段表
StringBuilder strSql1 = new StringBuilder();
strSql1.Append("delete from " + databaseprefix + "site_channel_field");
strSql1.Append(" where field_id=@0");
WriteDataBase.Execute(conn, trans, strSql1.ToString(), id);
//删除扩展字段主表
StringBuilder strSql = new StringBuilder();
strSql.Append("delete from " + databaseprefix + "article_attribute_field");
strSql.Append(" where id=@0");
WriteDataBase.Execute(conn, trans, strSql.ToString(), id);
trans.Commit();//提交事务
}
catch (Exception ex)
{
trans.Rollback();//回滚事务
return(false);
}
}
}
return(true);
}
/// <summary>
/// 确认充值订单
/// </summary>
public bool Confirm(string recharge_no)
{
Model.user_recharge model = GetModel("recharge_no='" + recharge_no + "'", "", "");//根据充值单号得到实体
if (model == null)
{
return(false);
}
using (IDbConnection conn = new DapperView().Context())
{
using (IDbTransaction trans = conn.BeginTransaction())
{
try
{
#region 增加一条账户余额记录===============
Model.user_amount_log amountModel = new Model.user_amount_log();
amountModel.user_id = model.user_id;
amountModel.user_name = model.user_name;
amountModel.value = model.amount;
amountModel.remark = "在线充值，单号：" + recharge_no;
amountModel.add_time = DateTime.Now;
new DAL.user_amount_log(databaseprefix).Add(conn, trans, amountModel);
#endregion
#region 更新充值表=========================
StringBuilder strSql = new StringBuilder();
strSql.Append("update " + databaseprefix + "user_recharge set ");
strSql.Append("status=@0,");
strSql.Append("complete_time=@1");
strSql.Append(" where recharge_no=@2");
WriteDataBase.Execute(conn, trans, strSql.ToString(), 1, DateTime.Now, recharge_no);
#endregion
trans.Commit();//提交事务
}
catch (Exception ex)
{
trans.Rollback();//回滚事务
return(false);
}
}
}
return(true);
}
/// <summary>
/// 删除一条数据
/// </summary>
public bool Delete(int id)
{
string build_path = GetBuildPath(id);
if (string.IsNullOrEmpty(build_path))
{
return(false);
}
//取得要删除的所有导航ID
string navIds = new navigation(databaseprefix).GetIds("channel_" + build_path);
using (IDbConnection conn = new DapperView().Context())
{
using (IDbTransaction trans = conn.BeginTransaction())
{
try
{
//删除导航
if (!string.IsNullOrEmpty(navIds))
{
WriteDataBase.Execute("delete from [" + databaseprefix + "navigation] where id in(" + navIds + ")");
}
//删除站点
StringBuilder strSql = new StringBuilder();
strSql.Append("delete from " + databaseprefix + "sites ");
strSql.Append(" where id=@0");
WriteDataBase.Execute(strSql.ToString(), id);
trans.Commit();
}
catch (Exception ex)
{
trans.Rollback();
return(false);
}
}
}
return(true);
}
/// <summary>
/// 增加一条数据
/// </summary>
public int Add(Model.manager_role model)
{
int i = 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 + "manager_role(");
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("@" + 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
#region 角色权限表信息====================
if (model.manager_role_values != null)
{
StringBuilder strSql2; //SQL字符串
StringBuilder str21; //数据库字段
StringBuilder str22; //声明参数
foreach (Model.manager_role_value modelt in model.manager_role_values)
{
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 + "manager_role_value(");
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("role_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
trans.Commit(); //提交事务
}
catch (Exception ex)
{
trans.Rollback(); //回滚事务
return(0);
}
}
}
return(model.id);
}
/// <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);
}
/// <summary>
/// 直接充值订单
/// </summary>
/// <summary>
/// 直接充值订单
/// </summary>
public bool Recharge(Model.user_recharge model)
{
int i = 0;
using (IDbConnection conn = new DapperView().Context())
{
using (IDbTransaction trans = conn.BeginTransaction())
{
try
{
#region 增加一条账户余额记录===============
Model.user_amount_log amountModel = new Model.user_amount_log();
amountModel.user_id = model.user_id;
amountModel.user_name = model.user_name;
amountModel.value = model.amount;
amountModel.remark = "在线充值，单号：" + model.recharge_no;
amountModel.add_time = DateTime.Now;
new DAL.user_amount_log(databaseprefix).Add(conn, trans, amountModel);
#endregion
#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 + "user_recharge(");
foreach (PropertyInfo pi in pros)
{
//如果不是主键则追加sql字符串
if (!pi.Name.Equals("id"))
{
//判断属性值是否为空
if (pi.GetValue(model, null) != null && !pi.GetValue(model, null).ToString().Equals(""))
{
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
trans.Commit();//提交事务
}
catch (Exception ex)
{
trans.Rollback();//回滚事务
return(false);
}
}
}
return(model.id > 0);
}
/// <summary>
/// 更新一条数据
/// </summary>
public bool Update(Model.article_category model)
{
int i = 0;
using (IDbConnection conn = new DapperView().Context())
{
using (IDbTransaction trans = conn.BeginTransaction())
{
try
{
//先判断选中的父节点是否被包含
if (IsContainNode(model.id, model.parent_id))
{
//查找旧数据
Model.article_category oldModel = GetModel(model.id);
//查找旧父节点数据
string class_list = "," + model.parent_id + ",";
int class_layer = 1;
if (oldModel.parent_id > 0)
{
Model.article_category oldParentModel = GetModel(conn, trans, oldModel.parent_id); //带事务
class_list = oldParentModel.class_list + model.parent_id + ",";
class_layer = oldParentModel.class_layer + 1;
}
//先提升选中的父节点
WriteDataBase.Execute(conn, trans, "update " + databaseprefix + "article_category set parent_id=" + oldModel.parent_id + ",class_list='" + class_list + "', class_layer=" + class_layer + " where id=" + model.parent_id); //带事务
UpdateChilds(conn, trans, model.parent_id); //带事务
}
//更新子节点
if (model.parent_id > 0)
{
Model.article_category model2 = GetModel(conn, trans, model.parent_id); //带事务
model.class_list = model2.class_list + model.id + ",";
model.class_layer = model2.class_layer + 1;
}
else
{
model.class_list = "," + model.id + ",";
model.class_layer = 1;
}
#region 修改主表数据==========================
StringBuilder strSql = new StringBuilder();
StringBuilder str1 = new StringBuilder();
//利用反射获得属性的所有公共属性
PropertyInfo[] pros = model.GetType().GetProperties();
List <object> paras = new List <object>();
strSql.Append("update " + databaseprefix + "article_category set ");
foreach (PropertyInfo pi in pros)
{
//如果不是主键则追加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 + "=@" + 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());
//首先判断菜单是否存在
object name = ReadDataBase.ExecuteScalar <object>(conn, trans, "select top 1 name from [" + databaseprefix + "site_channel] where id=" + model.channel_id); //带事务
if (null != name)
{
//自动分级
string parent_name = "channel_" + name.ToString() + "_category";
if (model.parent_id > 0)
{
parent_name += "_" + model.parent_id;
}
if (ReadDataBase.ExecuteScalar <int>(conn, trans, "select count(*) as H from [" + databaseprefix + "navigation] where name='" + "channel_" + name.ToString() + "_category_" + model.id + "'") == 0)
{
new DAL.navigation(databaseprefix).Add(conn, trans, parent_name, "channel_" + name.ToString() + "_category_" + model.id, model.title, "", model.sort_id, model.channel_id, "Show", 1);
}
else
{
int parent_id = ReadDataBase.ExecuteScalar <int>(conn, trans, "select top 1 id from [" + databaseprefix + "navigation] where name='" + parent_name + "'");
if (parent_id > 0)
{
new DAL.navigation(databaseprefix).Update(conn, trans, parent_name = "channel_" + name.ToString() + "_category_" + model.id, parent_id, parent_name, model.title, model.sort_id);
}
}
}
//更新子节点
UpdateChilds(conn, trans, model.id);
#endregion
#region 栏目规格===========================
//删除已删除的栏目规格
WriteDataBase.Execute(conn, trans, "delete from " + databaseprefix + "article_category_spec where category_id=" + model.id);
//添加/修改栏目规格
if (model.category_specs != null)
{
StringBuilder strSql3;
foreach (Model.article_category_spec modelt in model.category_specs)
{
strSql3 = new StringBuilder();
if (modelt.id > 0)
{
strSql3.Append("update " + databaseprefix + "article_category_spec ");
strSql3.Append("category_id=@0,");
strSql3.Append("spec_id=@1");
strSql3.Append(" where id=@2");
WriteDataBase.Execute(conn, trans, strSql3.ToString(), modelt.category_id, modelt.spec_id, modelt.id);
}
else
{
strSql3.Append("insert into " + databaseprefix + "article_category_spec(");
strSql3.Append("category_id,spec_id)");
strSql3.Append(" values (");
strSql3.Append("@0,@1)");
WriteDataBase.Execute(conn, trans, strSql3.ToString(), model.id, modelt.spec_id);
}
}
}
#endregion
trans.Commit();
}
catch (Exception ex)
{
trans.Rollback();
return(false);
}
}
}
return(true);
}
/// <summary>
/// 增加一条数据
/// </summary>
public int Add(Model.sites model)
{
int i = 0;
using (IDbConnection conn = new DapperView().Context())
{
using (IDbTransaction trans = conn.BeginTransaction())
{
try
{
//判断当前是否为默认，如果是，取消其它默认数据
if (model.is_default > 0)
{
UpDefault(conn, trans);
}
#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 + "sites(");
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("@" + 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
//如果非继承网站则添加导航菜单
if (model.inherit_id == 0)
{
new DAL.navigation(databaseprefix).Add(conn, trans, "sys_contents", "channel_" + model.build_path, model.title, "", model.sort_id, 0, "Show");
}
trans.Commit();
}
catch (Exception ex)
{
trans.Rollback();
return(0);
}
}
}
return(model.id);
}
/// <summary>
/// 更新一条数据
/// </summary>
public bool Update(Model.sites model, string old_build_path)
{
int i = 0;
using (IDbConnection conn = new DapperView().Context())
{
using (IDbTransaction trans = conn.BeginTransaction())
{
try
{
//判断当前是否为默认，如果是，取消其它默认数据
if (model.is_default > 0)
{
UpDefault(conn, trans);
}
#region 修改频道表======================
StringBuilder strSql = new StringBuilder();
StringBuilder str1 = new StringBuilder();
//利用反射获得属性的所有公共属性
PropertyInfo[] pros = model.GetType().GetProperties();
List <object> paras = new List <object>();
strSql.Append("update " + databaseprefix + "sites set ");
foreach (PropertyInfo pi in pros)
{
//如果不是主键则追加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 + "=@" + 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
//检查旧导航是否存在
if (new DAL.navigation(databaseprefix).GetModel(conn, trans, "channel_" + old_build_path) != null)
{
//如果非继承站点则修改导航菜单，是继承站点则删除旧导航
if (model.inherit_id == 0)
{
new DAL.navigation(databaseprefix).Update(conn, trans, "channel_" + old_build_path, "channel_" + model.build_path, model.title, model.sort_id, 0);
}
else
{
//第一种方法，只隐藏
new DAL.navigation(databaseprefix).Update(conn, trans, "channel_" + old_build_path, 1);
}
}
else if (model.inherit_id == 0) //没有旧菜单而需要添加新导航菜单
{
new DAL.navigation(databaseprefix).Add(conn, trans, "sys_contents", "channel_" + model.build_path, model.title, "", model.sort_id, 0, "Show");
}
trans.Commit();
}
catch (Exception ex)
{
trans.Rollback();
return(false);
}
}
}
return(true);
}
/// <summary>
/// 增加一条数据,及其子表数据
/// </summary>
public int Add(Model.orders model)
{
int i = 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 + "orders(");
foreach (PropertyInfo pi in pros)
{
//如果不是主键则追加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("@" + 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
#region 订单商品列表======================
if (model.order_goods != null)
{
StringBuilder strSql2; //SQL字符串
StringBuilder strSql3;
StringBuilder strSql4;
StringBuilder str21; //数据库字段
StringBuilder str22; //声明参数
foreach (Model.order_goods modelt in model.order_goods)
{
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 + "order_goods(");
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("order_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());
//扣减商品库存
if (modelt.goods_id > 0)
{
strSql3 = new StringBuilder();
strSql3.Append("update " + databaseprefix + "article_goods set stock_quantity=stock_quantity-@0");
strSql3.Append(" where id=@1 and channel_id=@2 and article_id=@3");
WriteDataBase.Execute(conn, trans, strSql3.ToString(), modelt.quantity, modelt.goods_id, modelt.channel_id, modelt.article_id);
string channelName = new DAL.site_channel(databaseprefix).GetChannelName(modelt.channel_id);//查询频道的名称
strSql4 = new StringBuilder();
strSql4.Append("update " + databaseprefix + DTKeys.TABLE_CHANNEL_ARTICLE + channelName + " set ");
strSql4.Append("stock_quantity=(select sum(stock_quantity) from " + databaseprefix + "article_goods where channel_id=@0 and article_id=@1)");
strSql4.Append(" where id=@1");
WriteDataBase.Execute(conn, trans, strSql4.ToString(), modelt.channel_id, modelt.article_id);
}
else
{
string channelName = new DAL.site_channel(databaseprefix).GetChannelName(modelt.channel_id);//查询频道的名称
strSql4 = new StringBuilder();
strSql4.Append("update " + databaseprefix + DTKeys.TABLE_CHANNEL_ARTICLE + channelName + " set ");
strSql4.Append("stock_quantity=stock_quantity-@0 where id=@1");
WriteDataBase.Execute(conn, trans, strSql4.ToString(), modelt.quantity, modelt.article_id);
}
}
}
#endregion
trans.Commit(); //提交事务
}
catch (Exception ex)
{
trans.Rollback(); //回滚事务
return(0);
}
}
}
return(model.id);
}
/// <summary>
/// 增加一条数据
/// </summary>
public int Add(Model.article model)
{
//查询频道名称
string channelName = new DAL.site_channel(databaseprefix).GetChannelName(model.channel_id);
if (channelName.Length == 0)
{
return(0);
}
int i = 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 + DTKeys.TABLE_CHANNEL_ARTICLE + channelName + "(");
//主表字段信息
foreach (PropertyInfo pi in pros)
{
//如果不是主键或List<T>则追加sql字符串
if (!pi.Name.Equals("id") && !pi.Name.Equals("fields") && !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("@" + i + ","); //声明参数
i++;
paras.Add(pi.GetValue(model, null)); //对参数赋值
}
}
}
//扩展字段信息
foreach (KeyValuePair <string, string> kvp in model.fields)
{
str1.Append(kvp.Key + ","); //拼接字段
str2.Append("@" + i + ","); //声明参数
i++;
paras.Add(kvp.Value); //对参数赋值
}
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
#region 添加图片相册====================
if (model.albums != null)
{
new DAL.article_albums(databaseprefix).Add(conn, trans, model.albums, model.channel_id, model.id);
}
#endregion
#region 添加内容附件====================
if (model.attach != null)
{
new DAL.article_attach(databaseprefix).Add(conn, trans, model.attach, model.channel_id, model.id);
}
#endregion
#region 添加商品价格====================
if (model.goods != null)
{
foreach (Model.article_goods modelt in model.goods)
{
new DAL.article_goods(databaseprefix).Add(conn, trans, modelt, model.channel_id, model.id);
}
}
#endregion
#region 添加商品规格====================
if (model.specs != null)
{
foreach (Model.article_goods_spec modelt in model.specs)
{
new DAL.article_goods_spec(databaseprefix).Add(conn, trans, modelt, model.channel_id, model.id);
}
}
#endregion
#region 保存自定义参数====================
if (model.attribute != null)
{
new DAL.article_attribute(databaseprefix).Add(conn, trans, model.attribute, model.channel_id, model.id);
}
#endregion
#region 添加Tags标签====================
if (model.tags != null && model.tags.Trim().Length > 0)
{
string[] tagsArr = model.tags.Trim().Split(',');
if (tagsArr.Length > 0)
{
foreach (string tagsStr in tagsArr)
{
new DAL.article_tags(databaseprefix).Update(conn, trans, tagsStr, model.channel_id, model.id);
}
}
}
#endregion
trans.Commit();
}
catch (Exception ex)
{
trans.Rollback();
return(0);
}
}
}
return(model.id);
}
/// <summary>
/// 更新一条数据
/// </summary>
public bool Update(Model.article model)
{
//查询频道名称
string channelName = new DAL.site_channel(databaseprefix).GetChannelName(model.channel_id);
if (channelName.Length == 0)
{
return(false);
}
int i = 0;
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 + DTKeys.TABLE_CHANNEL_ARTICLE + channelName + " set ");
//主表字段信息
foreach (PropertyInfo pi in pros)
{
//如果不是主键或List<T>则追加sql字符串
if (!pi.Name.Equals("id") && !pi.Name.Equals("fields") && !typeof(System.Collections.IList).IsAssignableFrom(pi.PropertyType))
{
//判断属性值是否为空
if (pi.GetValue(model, null) != null && !pi.GetValue(model, null).ToString().Equals(""))
{
str1.Append(pi.Name + "=@" + i + ","); //声明参数
i++;
paras.Add(pi.GetValue(model, null)); //对参数赋值
}
}
}
//扩展字段信息
foreach (KeyValuePair <string, string> kvp in model.fields)
{
str1.Append(kvp.Key + "=@" + i + ","); //声明参数
i++;
paras.Add(kvp.Value); //对参数赋值
}
strSql.Append(str1.ToString().Trim(','));
strSql.Append(" where id=" + model.id);
WriteDataBase.Execute(conn, trans, strSql.ToString(), paras.ToArray());
#endregion
#region 修改图片相册==========================
//删除/添加/修改相册图片
new DAL.article_albums(databaseprefix).Update(conn, trans, model.albums, model.channel_id, model.id);
#endregion
#region 修改内容附件==========================
//删除/添加/修改附件
new DAL.article_attach(databaseprefix).Update(conn, trans, model.attach, model.channel_id, model.id);
#endregion
#region 修改商品价格==========================
//删除旧商品价格
new DAL.article_goods(databaseprefix).Delete(conn, trans, model.channel_id, model.id);
//添加商品价格
if (model.goods != null)
{
foreach (Model.article_goods modelt in model.goods)
{
new DAL.article_goods(databaseprefix).Add(conn, trans, modelt, model.channel_id, model.id);
}
}
#endregion
#region 修改商品规格==========================
//先删除旧的规格
new DAL.article_goods_spec(databaseprefix).Delete(conn, trans, model.channel_id, model.id);
//添加商品规格
if (model.specs != null)
{
foreach (Model.article_goods_spec modelt in model.specs)
{
new DAL.article_goods_spec(databaseprefix).Add(conn, trans, modelt, model.channel_id, model.id);
}
}
#endregion
#region 修改Tags标签==========================
//删除已有的Tags标签关系
new DAL.article_tags(databaseprefix).Delete(conn, trans, model.channel_id, model.id);
//添加添加标签
if (model.tags != null && model.tags.Trim().Length > 0)
{
string[] tagsArr = model.tags.Trim().Split(',');
if (tagsArr.Length > 0)
{
foreach (string tagsStr in tagsArr)
{
new DAL.article_tags(databaseprefix).Update(conn, trans, tagsStr, model.channel_id, model.id);
}
}
}
#endregion
#region 修改扩展参数==========================
//删除/添加/修改扩展参数
new DAL.article_attribute(databaseprefix).Update(conn, trans, model.attribute, model.channel_id, model.id);
#endregion
trans.Commit();
}
catch (Exception ex)
{
trans.Rollback();
return(false);
}
}
}
return(true);
}
/// <summary>
/// 更新一条数据
/// </summary>
public bool Update(Model.manager_role model)
{
int i = 0;
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 + "manager_role set ");
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 + "=@" + 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
#region 角色权限表信息====================
//删除角色所有的权限
StringBuilder strSql1 = new StringBuilder();
strSql1.Append("delete from " + databaseprefix + "manager_role_value where role_id=@0");
WriteDataBase.Execute(strSql1.ToString(), model.id);
//重新添加角色权限
if (model.manager_role_values != null)
{
StringBuilder strSql2; //SQL字符串
StringBuilder str21; //数据库字段
StringBuilder str22; //声明参数
foreach (Model.manager_role_value modelt in model.manager_role_values)
{
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 + "manager_role_value(");
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("role_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
trans.Commit(); //提交事务
}
catch (Exception ex)
{
trans.Rollback(); //回滚事务
return(false);
}
}
}
return(true);
}
/// <summary>
/// 更新一条数据
/// </summary>
public bool Update(Model.article_attribute_field model)
{
int i = 0;
Model.article_attribute_field oldModel = GetModel(model.id);//取到旧的数据
using (IDbConnection conn = new DapperView().Context())
{
using (IDbTransaction trans = conn.BeginTransaction())
{
try
{
//修改主表信息
StringBuilder strSql = new StringBuilder();
StringBuilder str1 = new StringBuilder();
//利用反射获得属性的所有公共属性
PropertyInfo[] pros = model.GetType().GetProperties();
List <object> paras = new List <object>();
strSql.Append("update " + databaseprefix + "article_attribute_field set ");
foreach (PropertyInfo pi in pros)
{
//如果不是主键则追加sql字符串
if (!pi.Name.Equals("id"))
{
//判断属性值是否为空
if (pi.GetValue(model, null) != null && !pi.GetValue(model, null).ToString().Equals(""))
{
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());
//检查字段名和类型有无变化
if (oldModel.name.ToLower() != model.name.ToLower() || oldModel.data_type.ToLower() != model.data_type.ToLower())
{
DataTable dt = new DAL.site_channel(databaseprefix).GetFieldList(conn, trans, model.id).Tables[0];
if (dt.Rows.Count > 0)
{
foreach (DataRow dr in dt.Rows)
{
//检查有无该频道数据表和列
int rowsCount = ReadDataBase.ExecuteScalar <int>(conn, trans, "select count(1) from syscolumns where id=object_id('" + databaseprefix + DTKeys.TABLE_CHANNEL_ARTICLE + dr["name"].ToString() + "') and name='" + oldModel.name + "'");
if (rowsCount > 0)
{
//修改列数据类型
if (oldModel.data_type.ToLower() != model.data_type.ToLower())
{
WriteDataBase.Execute(conn, trans, "alter table " + databaseprefix + DTKeys.TABLE_CHANNEL_ARTICLE + dr["name"].ToString() + " alter column " + oldModel.name + " " + model.data_type);
}
//修改列名
if (oldModel.name.ToLower() != model.name.ToLower())
{
WriteDataBase.Execute(conn, trans, "exec sp_rename '" + databaseprefix + DTKeys.TABLE_CHANNEL_ARTICLE + dr["name"].ToString() + "." + oldModel.name + "','" + model.name + "','column'");
}
}
}
}
}
trans.Commit();//提交事务
}
catch (Exception ex)
{
trans.Rollback();//回滚事务
return(false);
}
}
}
return(true);
}
/// <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);
}
/// <summary>
/// 增加一条数据
/// </summary>
public int Add(Model.article_comment model)
{
int i = 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 + "article_comment(");
foreach (PropertyInfo pi in pros)
{
//如果不是主键则追加sql字符串
if (!pi.Name.Equals("id"))
{
//判断属性值是否为空
if (pi.GetValue(model, null) != null && !pi.GetValue(model, null).ToString().Equals(""))
{
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
if (model.parent_id > 0)
{
Model.article_comment model2 = GetModel(conn, trans, model.parent_id); //带事务
model.class_list = model2.class_list + model.id + ",";
model.class_layer = model2.class_layer + 1;
}
else
{
model.class_list = "," + model.id + ",";
model.class_layer = 1;
}
//修改节点列表和深度
WriteDataBase.Execute(conn, trans, "update [" + databaseprefix + "article_comment] set class_list='" + model.class_list + "', class_layer=" + model.class_layer + " where id=" + model.id); //带事务
trans.Commit();
}
catch (Exception ex)
{
trans.Rollback();
return(0);
}
}
}
return(model.id);
}
/// <summary>
/// 增加一条数据
/// </summary>
public int Add(Model.article_category model, int role_id)
{
int i = 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 + "article_category(");
foreach (PropertyInfo pi in pros)
{
//如果不是主键则追加sql字符串
if (!pi.Name.Equals("id"))
{
//判断属性值是否为空
if (pi.GetValue(model, null) != null && !pi.GetValue(model, null).ToString().Equals(""))
{
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);
if (model.parent_id > 0)
{
Model.article_category model2 = GetModel(conn, trans, model.parent_id); //带事务
model.class_list = model2.class_list + model.id + ",";
model.class_layer = model2.class_layer + 1;
}
else
{
model.class_list = "," + model.id + ",";
model.class_layer = 1;
}
//添加权限菜单
object name = ReadDataBase.ExecuteScalar <object>(conn, trans, "select top 1 name from [" + databaseprefix + "site_channel] where id=" + model.channel_id); //带事务
if (null != name)
{
//自动分级
string parent_name = "channel_" + name.ToString() + "_category";
if (model.parent_id > 0)
{
parent_name += "_" + model.parent_id;
}
new DAL.navigation(databaseprefix).Add(conn, trans, parent_name, "channel_" + name.ToString() + "_category_" + model.id, model.title, "", model.sort_id, model.channel_id, "Show", 1);
if (role_id > 0)
{
Model.manager_role_value valModel = new Model.manager_role_value();
valModel.role_id = role_id;
valModel.nav_name = "channel_" + name.ToString() + "_category_" + model.id;
valModel.action_type = "Show";
new DAL.manager_role_value(databaseprefix).Add(valModel);
}
}
//修改节点列表和深度
WriteDataBase.Execute(conn, trans, "update " + databaseprefix + "article_category set class_list='" + model.class_list + "', class_layer=" + model.class_layer + " where id=" + model.id); //带事务
#endregion
#region 栏目规格===========================
if (model.category_specs != null)
{
StringBuilder strSql3;
foreach (Model.article_category_spec modelt in model.category_specs)
{
strSql3 = new StringBuilder();
strSql3.Append("insert into " + databaseprefix + "article_category_spec(");
strSql3.Append("category_id,spec_id)");
strSql3.Append(" values (");
strSql3.Append("@0,@1)");
WriteDataBase.Execute(conn, trans, strSql3.ToString(), model.id, modelt.spec_id);
}
}
#endregion
trans.Commit();
}
catch (Exception ex)
{
trans.Rollback();
return(0);
}
}
}
return(model.id);
}
