/// <summary> /// 修改子节点的ID列表及深度(自身迭代) /// </summary> /// <param name="parent_id"></param> private void UpdateChilds(SqlConnection conn, SqlTransaction trans, int parent_id) { //查找父节点信息 Model.ForderModel model = GetModel(conn, trans, parent_id); if (model != null) { //查找子节点 string strSql = "select id from " + databaseprefix + "Forder where parent_id=" + parent_id; DataSet ds = DbHelperSQL.Query(conn, trans, strSql); //带事务 foreach (DataRow dr in ds.Tables[0].Rows) { //修改子节点的ID列表及深度 int id = int.Parse(dr["id"].ToString()); string class_list = model.Class_List + id + ","; int class_layer = model.Class_Layer + 1; DbHelperSQL.ExecuteSql(conn, trans, "update " + databaseprefix + "Forder set class_list='" + class_list + "', class_layer=" + class_layer + " where id=" + id); //带事务 //调用自身迭代 this.UpdateChilds(conn, trans, id); //带事务 } } }
/// <summary> /// 根据路径获取文件夹信息 /// </summary> /// <param name="forders">路径</param> /// <param name="num">层次</param> /// <param name="forder">对应服务器目录</param> /// <returns></returns> private static Model.ForderModel GetForderFiles(List <string> forders, int num, Model.ForderModel forder) { //指定文件夹不存在 if (forder == null || forder.SubForders == null || forder.SubForders.Count == 0) { return(null); } foreach (var forderItem in _rootForder.SubForders) { if (forders[num] == forderItem.Title) { return(GetForderFiles(forders, ++num, forderItem)); } } //没有对应文件夹 return(null); }
/// <summary> /// 增加一条数据 /// </summary> public int Add(Model.ForderModel model) { using (SqlConnection conn = new SqlConnection(DbHelperSQL.connectionString)) { conn.Open(); using (SqlTransaction trans = conn.BeginTransaction()) { try { StringBuilder strSql = new StringBuilder(); strSql.Append("insert into " + databaseprefix + "Forder("); strSql.Append("[Title],[Call_Index],UserName,Project_Id,[Parent_Id],[Class_List],[Class_Layer],[Sort_Id],[Image],[Remark],ClientPath,[Forder_Modify_Time],Add_Time)"); strSql.Append(" values ("); strSql.Append("@Title,@Call_Index,@UserName,@Project_Id,@Parent_Id,@Class_List,@Class_Layer,@Sort_Id,@Image,@Remark,@ClientPath,@Forder_Modify_Time,@Add_Time)"); strSql.Append(";select @@IDENTITY"); SqlParameter[] parameters = { new SqlParameter("@Title", SqlDbType.NVarChar, 200), new SqlParameter("@Call_Index", SqlDbType.NVarChar, 200), new SqlParameter("@UserName", SqlDbType.NVarChar, 100), new SqlParameter("@Project_Id", SqlDbType.Int), new SqlParameter("@Parent_Id", SqlDbType.Int), new SqlParameter("@Class_List", SqlDbType.NVarChar, 500), new SqlParameter("@Class_Layer", SqlDbType.Int), new SqlParameter("@Sort_Id", SqlDbType.Int), new SqlParameter("@Image", SqlDbType.VarBinary), new SqlParameter("@Remark", SqlDbType.NVarChar, 2000), new SqlParameter("@ClientPath", SqlDbType.NVarChar, 2000), new SqlParameter("@Forder_Modify_Time", SqlDbType.BigInt), new SqlParameter("@Add_Time", SqlDbType.DateTime), }; parameters[0].Value = model.Title; parameters[1].Value = model.Call_Index; parameters[2].Value = model.UserName; parameters[3].Value = model.Project_Id; parameters[4].Value = model.Parent_Id; parameters[5].Value = model.Class_List; parameters[6].Value = model.Class_Layer; parameters[7].Value = model.Sort_Id; parameters[8].Value = model.Image; parameters[9].Value = model.Remark; parameters[10].Value = model.ClientPath; parameters[11].Value = model.Forder_Modify_Time; parameters[12].Value = model.Add_Time; object obj = DbHelperSQL.GetSingle(conn, trans, strSql.ToString(), parameters); //带事务 model.ID = Convert.ToInt32(obj); if (model.Parent_Id > 0) { Model.ForderModel 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; } //修改节点列表和深度 DbHelperSQL.ExecuteSql(conn, trans, "update " + databaseprefix + "Forder set class_list='" + model.Class_List + "', class_layer=" + model.Class_Layer + " where id=" + model.ID); //带事务 trans.Commit(); } catch { trans.Rollback(); return(0); } } } return(model.ID); }
/// <summary> /// 得到一个对象实体(重载,带事务) /// </summary> public Model.ForderModel GetModel(SqlConnection conn, SqlTransaction trans, int id) { StringBuilder strSql = new StringBuilder(); strSql.Append("select top 1 * from " + databaseprefix + "Forder "); strSql.Append(" where id=@id"); SqlParameter[] parameters = { new SqlParameter("@id", SqlDbType.Int, 4) }; parameters[0].Value = id; Model.ForderModel model = new Model.ForderModel(); DataSet ds = DbHelperSQL.Query(conn, trans, strSql.ToString(), parameters); if (ds.Tables[0].Rows.Count > 0) { if (ds.Tables[0].Rows[0]["id"] != null && ds.Tables[0].Rows[0]["id"].ToString() != "") { model.ID = int.Parse(ds.Tables[0].Rows[0]["id"].ToString()); } if (ds.Tables[0].Rows[0]["title"] != null && ds.Tables[0].Rows[0]["title"].ToString() != "") { model.Title = ds.Tables[0].Rows[0]["title"].ToString(); } if (ds.Tables[0].Rows[0]["call_index"] != null && ds.Tables[0].Rows[0]["call_index"].ToString() != "") { model.Call_Index = ds.Tables[0].Rows[0]["call_index"].ToString(); } if (ds.Tables[0].Rows[0]["parent_id"] != null && ds.Tables[0].Rows[0]["parent_id"].ToString() != "") { model.Parent_Id = int.Parse(ds.Tables[0].Rows[0]["parent_id"].ToString()); } if (ds.Tables[0].Rows[0]["class_list"] != null && ds.Tables[0].Rows[0]["class_list"].ToString() != "") { model.Class_List = ds.Tables[0].Rows[0]["class_list"].ToString(); } if (ds.Tables[0].Rows[0]["class_layer"] != null && ds.Tables[0].Rows[0]["class_layer"].ToString() != "") { model.Class_Layer = int.Parse(ds.Tables[0].Rows[0]["class_layer"].ToString()); } if (ds.Tables[0].Rows[0]["sort_id"] != null && ds.Tables[0].Rows[0]["sort_id"].ToString() != "") { model.Sort_Id = int.Parse(ds.Tables[0].Rows[0]["sort_id"].ToString()); } if (ds.Tables[0].Rows[0]["Image"] != null && ds.Tables[0].Rows[0]["Image"].ToString() != "") { model.Image = (byte[])ds.Tables[0].Rows[0]["Image"]; } if (ds.Tables[0].Rows[0]["remark"] != null && ds.Tables[0].Rows[0]["remark"].ToString() != "") { model.Remark = ds.Tables[0].Rows[0]["remark"].ToString(); } if (ds.Tables[0].Rows[0]["Forder_Modify_Time"] != null && ds.Tables[0].Rows[0]["Forder_Modify_Time"].ToString() != "") { model.Forder_Modify_Time = long.Parse(ds.Tables[0].Rows[0]["Forder_Modify_Time"].ToString()); } return(model); } else { return(null); } }
/// <summary> /// 更新一条数据 /// </summary> public bool Update(Model.ForderModel model) { using (SqlConnection conn = new SqlConnection(DbHelperSQL.connectionString)) { conn.Open(); using (SqlTransaction trans = conn.BeginTransaction()) { try { //先判断选中的父节点是否被包含 if (IsContainNode(model.ID, model.Parent_Id)) { //查找旧数据 Model.ForderModel oldModel = GetModel(model.ID); //查找旧父节点数据 string class_list = "," + model.Parent_Id + ","; int class_layer = 1; if (oldModel.Parent_Id > 0) { Model.ForderModel oldParentModel = GetModel(conn, trans, oldModel.Parent_Id); //带事务 class_list = oldParentModel.Class_List + model.Parent_Id + ","; class_layer = oldParentModel.Class_Layer + 1; } //先提升选中的父节点 DbHelperSQL.ExecuteSql(conn, trans, "update " + databaseprefix + "Forder 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.ForderModel 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; } StringBuilder strSql = new StringBuilder(); strSql.Append("update " + databaseprefix + "Forder set "); strSql.Append("title=@title,"); strSql.Append("call_index=@call_index,"); strSql.Append("UserName=@UserName,"); strSql.Append("parent_id=@parent_id,"); strSql.Append("class_list=@class_list,"); strSql.Append("class_layer=@class_layer,"); strSql.Append("Image=@Image,"); strSql.Append("remark=@remark,"); strSql.Append("Forder_Modify_Time=@Forder_Modify_Time,"); strSql.Append("Add_Time=@Add_Time"); strSql.Append(" where id=@id"); SqlParameter[] parameters = { new SqlParameter("@Title", SqlDbType.NVarChar, 100), new SqlParameter("@Call_Index", SqlDbType.NVarChar, 50), new SqlParameter("@UserName", SqlDbType.NVarChar, 100), new SqlParameter("@parent_id", SqlDbType.Int, 4), new SqlParameter("@class_list", SqlDbType.NVarChar, 500), new SqlParameter("@class_layer", SqlDbType.Int, 4), new SqlParameter("@sort_id", SqlDbType.Int, 4), new SqlParameter("@Image", SqlDbType.VarBinary), new SqlParameter("@remark", SqlDbType.NText), new SqlParameter("@Forder_Modify_Time", SqlDbType.BigInt), new SqlParameter("@Add_Time", SqlDbType.DateTime), new SqlParameter("@id", SqlDbType.Int, 4) }; parameters[0].Value = model.Title; parameters[1].Value = model.Call_Index; parameters[2].Value = model.UserName; parameters[3].Value = model.Parent_Id; parameters[4].Value = model.Class_List; parameters[5].Value = model.Class_Layer; parameters[6].Value = model.Sort_Id; parameters[7].Value = model.Image; parameters[8].Value = model.Remark; parameters[9].Value = model.Forder_Modify_Time; parameters[10].Value = model.Add_Time; parameters[11].Value = model.ID; DbHelperSQL.ExecuteSql(conn, trans, strSql.ToString(), parameters); //更新子节点 UpdateChilds(conn, trans, model.ID); trans.Commit(); } catch (Exception ex) { trans.Rollback(); return(false); } } } return(true); }
/// <summary> /// 更新一条数据 /// </summary> public bool Update(Model.ForderModel model) { return(dal.Update(model)); }
/// <summary> /// 增加一条数据 /// </summary> public int Add(Model.ForderModel model) { return(dal.Add(model)); }