/// <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));
}
