/// <summary> /// 得到一个对象实体 /// </summary> public Model.article_category GetModel(int id) { StringBuilder strSql = new StringBuilder(); StringBuilder str1 = new StringBuilder(); Model.article_category model = new Model.article_category(); //利用反射获得属性的所有公共属性 PropertyInfo[] pros = model.GetType().GetProperties(); foreach (PropertyInfo p in pros) { //拼接字段,忽略List<T> if (!typeof(System.Collections.IList).IsAssignableFrom(p.PropertyType)) { str1.Append(p.Name + ","); } } strSql.Append("select top 1 " + str1.ToString().Trim(',')); strSql.Append(" from " + databaseprefix + "article_category"); strSql.Append(" where id=@id"); SqlParameter[] parameters = { new SqlParameter("@id", SqlDbType.Int, 4) }; parameters[0].Value = id; DataTable dt = DbHelperSQL.Query(strSql.ToString(), parameters).Tables[0]; if (dt.Rows.Count > 0) { return(DataRowToModel(dt.Rows[0])); } else { return(null); } }
/// <summary> /// 得到一个对象实体(重载,带事务) /// </summary> public Model.article_category GetModel(SqlConnection conn, SqlTransaction trans, int id) { StringBuilder strSql = new StringBuilder(); StringBuilder str1 = new StringBuilder(); Model.article_category model = new Model.article_category(); //利用反射获得属性的所有公共属性 PropertyInfo[] pros = model.GetType().GetProperties(); foreach (PropertyInfo p in pros) { //拼接字段,忽略List<T> if (!p.Name.Equals("category_brands") && !p.Name.Equals("category_specs")) { str1.Append(p.Name + ","); } } strSql.Append("select top 1 " + str1.ToString().Trim(',')); strSql.Append(" from " + databaseprefix + "article_category"); strSql.Append(" where id=@id"); SqlParameter[] parameters = { new SqlParameter("@id", SqlDbType.Int, 4) }; parameters[0].Value = id; DataTable dt = DbHelperSQL.Query(conn, trans, strSql.ToString(), parameters).Tables[0]; if (dt.Rows.Count > 0) { return(DataRowToModel(dt.Rows[0])); } else { return(null); } }
/// <summary> /// 将对象转换实体 /// </summary> public Model.article_category DataRowToModel(DataRow row) { Model.article_category model = new Model.article_category(); if (row != null) { //利用反射获得属性的所有公共属性 Type modelType = model.GetType(); for (int i = 0; i < row.Table.Columns.Count; i++) { PropertyInfo proInfo = modelType.GetProperty(row.Table.Columns[i].ColumnName); if (proInfo != null && row[i] != DBNull.Value) { proInfo.SetValue(model, row[i], null);//用索引值设置属性值 } } } return(model); }
/// <summary> /// 增加一条数据 /// </summary> public int Add(Model.article_category model) { 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 + "article_category("); 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); //查询父节点的深度赋值 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; } //修改节点列表和深度 DbHelperSQL.ExecuteSql(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("@category_id,@spec_id)"); SqlParameter[] parameters3 = { new SqlParameter("@category_id", SqlDbType.Int, 4), new SqlParameter("@spec_id", SqlDbType.Int, 4) }; parameters3[0].Value = model.id;//刚插入的ID parameters3[1].Value = modelt.spec_id; DbHelperSQL.ExecuteSql(conn, trans, strSql3.ToString(), parameters3); } } #endregion trans.Commit();//提交事务 } catch { trans.Rollback();//回滚事务 return(0); } } } return(model.id); }
/// <summary> /// 更新一条数据 /// </summary> public bool Update(Model.article_category model) { using (SqlConnection conn = new SqlConnection(DbHelperSQL.connectionString)) { conn.Open(); using (SqlTransaction trans = conn.BeginTransaction()) { try { #region 主表信息=========================== //先判断选中的父节点是否被包含 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; } //先提升选中的父节点 DbHelperSQL.ExecuteSql(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; } //更新本栏目类别信息 StringBuilder strSql = new StringBuilder(); StringBuilder str1 = new StringBuilder(); //利用反射获得属性的所有公共属性 PropertyInfo[] pros = model.GetType().GetProperties(); List <SqlParameter> paras = new List <SqlParameter>(); strSql.Append("update " + databaseprefix + "article_category 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 + "=@" + pi.Name + ","); //声明参数 paras.Add(new SqlParameter("@" + pi.Name, pi.GetValue(model, null))); //对参数赋值 } } } strSql.Append(str1.ToString().Trim(',')); strSql.Append(" where id=@id"); paras.Add(new SqlParameter("@id", model.id)); DbHelperSQL.ExecuteSql(conn, trans, strSql.ToString(), paras.ToArray()); //更新子节点 UpdateChilds(conn, trans, model.id); #endregion #region 栏目规格=========================== //删除已删除的栏目规格 DbHelperSQL.ExecuteSql(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=@category_id,"); strSql3.Append("spec_id=@spec_id"); strSql3.Append(" where id=@id"); SqlParameter[] parameters3 = { new SqlParameter("@category_id", SqlDbType.Int, 4), new SqlParameter("@spec_id", SqlDbType.Int, 4), new SqlParameter("@id", SqlDbType.Int, 4) }; parameters3[0].Value = modelt.category_id; parameters3[1].Value = modelt.spec_id; parameters3[2].Value = modelt.id; DbHelperSQL.ExecuteSql(conn, trans, strSql3.ToString(), parameters3); } else { strSql3.Append("insert into " + databaseprefix + "article_category_spec("); strSql3.Append("category_id,spec_id)"); strSql3.Append(" values ("); strSql3.Append("@category_id,@spec_id)"); SqlParameter[] parameters3 = { new SqlParameter("@category_id", SqlDbType.Int, 4), new SqlParameter("@spec_id", SqlDbType.Int, 4) }; parameters3[0].Value = model.id;//栏目的ID parameters3[1].Value = modelt.spec_id; DbHelperSQL.ExecuteSql(conn, trans, strSql3.ToString(), parameters3); } } } #endregion trans.Commit();//提交事务 } catch (Exception ex) { trans.Rollback();//回滚事务 return(false); } } } return(true); }