/// <summary> /// 得到一个对象实体(重载,带事务) /// </summary> public Dapper.Model.article_category GetModel(SqlConnection conn, SqlTransaction trans, int id) { StringBuilder strSql = new StringBuilder(); StringBuilder str1 = new StringBuilder(); Dapper.Model.article_category model = new Dapper.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 Dapper.Model.article_category GetModel(int id) { StringBuilder strSql = new StringBuilder(); StringBuilder str1 = new StringBuilder(); Dapper.Model.article_category model = new Dapper.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 Dapper.Model.article_category DataRowToModel(DataRow row) { Dapper.Model.article_category model = new Dapper.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> /// 修改子节点的ID列表及深度(自身迭代) /// </summary> private void UpdateChilds(SqlConnection conn, SqlTransaction trans, int parent_id) { //查找父节点信息 Dapper.Model.article_category model = GetModel(conn, trans, parent_id); if (model != null) { //查找子节点 string strSql = "select id from " + databaseprefix + "article_category 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 + "article_category set class_list='" + class_list + "', class_layer=" + class_layer + " where id=" + id);//带事务 //调用自身迭代 this.UpdateChilds(conn, trans, id);//带事务 } } }
/// <summary> /// 增加一条数据 /// </summary> public int Add(Dapper.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) { 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) { Dapper.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 trans.Commit();//提交事务 } catch { trans.Rollback();//回滚事务 return(0); } } } return(model.id); }
/// <summary> /// 更新一条数据 /// </summary> public bool Update(Dapper.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)) { //查找旧数据 Dapper.Model.article_category oldModel = GetModel(model.id); //查找旧父节点数据 string class_list = "," + model.parent_id + ","; int class_layer = 1; if (oldModel.parent_id > 0) { Dapper.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) { Dapper.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) { 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 trans.Commit();//提交事务 } catch (Exception ex) { trans.Rollback();//回滚事务 return(false); } } } return(true); }