public List <DictInfo> GetDictInfoTree(int parentId) { string sql = @"SELECT A.[DictID],A.[DictName],A.[DictCode],A.[IsSystem],ISNULL(B.ChildCount,0) AS ChildCount FROM [DictInfos] A LEFT JOIN (Select COUNT(1) as ChildCount, ParentID From DictInfos Group By ParentID) B ON A.DictID=B.ParentID WHERE A.ParentID=@ParentID Order By A.Sequence"; SqlParameter pa = new SqlParameter("@ParentID", parentId); List <DictInfo> list = new List <DictInfo>(); using (IDataReader reader = base.ExcuteDataReader(sql, pa)) { while (reader.Read()) { DictInfo dict = new DictInfo(); dict.DictID = reader.GetInt32(0); dict.DictName = reader.GetString(1); dict.DictCode = reader.GetString(2); dict.IsSystem = reader.GetBoolean(3); dict.HasChild = reader.GetInt32(4) > 0; dict.IsNew = false; list.Add(dict); } } return(list); }
public DictInfo GetDictInfo(int dictId) { string sql = @"SELECT A.[DictID],A.[DictName],A.[DictCode],A.[IsSystem],A.[ParentID],A.[Sequence],A.[Remark] ,CASE WHEN A.ParentID=0 THEN '数据字典' ELSE B.DictName END AS ParentName FROM [DictInfos] A LEFT JOIN [DictInfos] B on A.ParentID=B.DictID Where a.DictID=@DictID ORDER BY A.Sequence"; SqlParameter pa = new SqlParameter("@DictID", dictId); DictInfo dict = null; using (IDataReader reader = base.ExcuteDataReader(sql, pa)) { if (reader.Read()) { dict = new DictInfo(); dict.DictID = reader.GetInt32(0); dict.DictName = reader.GetString(1); dict.DictCode = reader.GetString(2); dict.IsSystem = reader.GetBoolean(3); dict.ParentID = reader.GetInt32(4); dict.Sequence = reader.GetInt32(5); dict.Remark = reader.IsDBNull(6) ? "" : reader.GetString(6); dict.ParentName = reader.IsDBNull(7) ? "" : reader.GetString(7); dict.IsNew = false; } } return(dict); }
public DictInfo GetDictInfo(int dictId) { string sql = @"SELECT A.[DictID],A.[DictName],A.[DictCode],A.[IsSystem],A.[ParentID],A.[Sequence],A.[Remark] ,CASE WHEN A.ParentID=0 THEN '数据字典' ELSE B.DictName END AS ParentName FROM [DictInfos] A LEFT JOIN [DictInfos] B on A.ParentID=B.DictID Where a.DictID=@DictID ORDER BY A.Sequence"; SqlParameter pa = new SqlParameter("@DictID", dictId); DictInfo dict=null; using (IDataReader reader = base.ExcuteDataReader(sql, pa)) { if (reader.Read()) { dict = new DictInfo(); dict.DictID = reader.GetInt32(0); dict.DictName = reader.GetString(1); dict.DictCode = reader.GetString(2); dict.IsSystem = reader.GetBoolean(3); dict.ParentID = reader.GetInt32(4); dict.Sequence = reader.GetInt32(5); dict.Remark = reader.IsDBNull(6) ? "" : reader.GetString(6); dict.ParentName = reader.IsDBNull(7) ? "" : reader.GetString(7); dict.IsNew = false; } } return dict; }
public List <DictInfo> QueryDictInfoList(int parentId) { string sql = @"SELECT [DictID],[DictName],[DictCode],[IsSystem],[ParentID],[Sequence],[Remark] FROM [DictInfos] WHERE ParentID=@ParentID Order By Sequence"; SqlParameter pa = new SqlParameter("@ParentID", parentId); List <DictInfo> list = new List <DictInfo>(); using (IDataReader reader = base.ExcuteDataReader(sql, pa)) { while (reader.Read()) { DictInfo dict = new DictInfo(); dict.DictID = reader.GetInt32(0); dict.DictName = reader.GetString(1); dict.DictCode = reader.GetString(2); dict.IsSystem = reader.GetBoolean(3); dict.ParentID = reader.GetInt32(4); dict.Sequence = reader.GetInt32(5); dict.Remark = reader.IsDBNull(6)?"":reader.GetString(6); dict.IsNew = false; list.Add(dict); } } return(list); }
public DictInfo Get(int key) { string sql = "SELECT [DictID],[DictName],[DictCode],[IsSystem],[ParentID],[Sequence],[Remark] FROM [DictInfos] WHERE [DictID]=@DictID"; SqlParameter p =new SqlParameter("@DictID",key); DictInfo item =null; using(IDataReader reader = base.ExcuteDataReader(sql,p)) { if(reader.Read()) { item =new DictInfo(); item.DictID = reader.GetInt32(0); item.DictName = reader.GetString(1); item.DictCode = reader.GetString(2); item.IsSystem = reader.GetBoolean(3); if(!reader.IsDBNull(4)) { item.ParentID = reader.GetInt32(4); } if(!reader.IsDBNull(5)) { item.Sequence = reader.GetInt32(5); } if(!reader.IsDBNull(6)) { item.Remark = reader.GetString(6); } } } return item; }
public void Insert(DictInfo item) { string sql="INSERT INTO [DictInfos] ([DictName],[DictCode],[IsSystem],[ParentID],[Sequence],[Remark]) VALUES (@DictName,@DictCode,@IsSystem,@ParentID,@Sequence,@Remark)"; List<SqlParameter> SPParams = new List<SqlParameter>(); SPParams.Add(new SqlParameter("@DictName",item.DictName)); SPParams.Add(new SqlParameter("@DictCode",item.DictCode)); SPParams.Add(new SqlParameter("@IsSystem",item.IsSystem)); SPParams.Add(new SqlParameter("@ParentID",item.ParentID)); SPParams.Add(new SqlParameter("@Sequence",item.Sequence)); SPParams.Add(new SqlParameter("@Remark",item.Remark)); sql +=";SELECT Scope_Identity()"; object o = base.ExecuteScalar(sql, SPParams.ToArray()); if(o!=null){ item.DictID =Convert.ToInt32(o); } }
/// <summary> /// 获取子数据字典信息 /// </summary> /// <param name="dictCode">The dict code.</param> /// <returns></returns> public List<DictInfo> GetChildDictInfos(string dictCode) { string sql = @"SELECT A.[DictID], A.[DictName], A.[DictCode], A.[IsSystem] FROM [DictInfos] A INNER JOIN [DictInfos] B ON A.ParentID=B.DictID Where B.DictCode=@ParentCode Order by A.Sequence"; SqlParameter pa = new SqlParameter("@ParentCode", dictCode); List<DictInfo> list = new List<DictInfo>(); using (IDataReader reader = base.ExcuteDataReader(sql, pa)) { while (reader.Read()) { DictInfo dict = new DictInfo(); dict.DictID = reader.GetInt32(0); dict.DictName = reader.GetString(1); dict.DictCode = reader.GetString(2); dict.IsSystem = reader.GetBoolean(3); dict.IsNew = false; list.Add(dict); } } return list; }
/// <summary> /// 获取子数据字典信息 /// </summary> /// <param name="dictCode">The dict code.</param> /// <returns></returns> public List <DictInfo> GetChildDictInfos(string dictCode) { string sql = @"SELECT A.[DictID], A.[DictName], A.[DictCode], A.[IsSystem] FROM [DictInfos] A INNER JOIN [DictInfos] B ON A.ParentID=B.DictID Where B.DictCode=@ParentCode Order by A.Sequence"; SqlParameter pa = new SqlParameter("@ParentCode", dictCode); List <DictInfo> list = new List <DictInfo>(); using (IDataReader reader = base.ExcuteDataReader(sql, pa)) { while (reader.Read()) { DictInfo dict = new DictInfo(); dict.DictID = reader.GetInt32(0); dict.DictName = reader.GetString(1); dict.DictCode = reader.GetString(2); dict.IsSystem = reader.GetBoolean(3); dict.IsNew = false; list.Add(dict); } } return(list); }
public List<DictInfo> QueryAll() { string sql ="SELECT [DictID],[DictName],[DictCode],[IsSystem],[ParentID],[Sequence],[Remark] FROM [DictInfos]"; List<DictInfo> list =new List<DictInfo>(); using(IDataReader reader = base.ExcuteDataReader(sql)) { while(reader.Read()) { DictInfo item =new DictInfo(); item.DictID = reader.GetInt32(0); item.DictName = reader.GetString(1); item.DictCode = reader.GetString(2); item.IsSystem = reader.GetBoolean(3); if(!reader.IsDBNull(4)) { item.ParentID = reader.GetInt32(4); } if(!reader.IsDBNull(5)) { item.Sequence = reader.GetInt32(5); } if(!reader.IsDBNull(6)) { item.Remark = reader.GetString(6); } list.Add(item); } } return list; }
public void Update(DictInfo item) { if(item.ChangedPropertyCount>0) { StringBuilder sqlbuilder = new StringBuilder(); sqlbuilder.Append("UPDATE [DictInfos] SET "); Dictionary<string,string> cols =new Dictionary<string,string>(); cols.Add("DictName","[DictName]"); cols.Add("DictCode","[DictCode]"); cols.Add("IsSystem","[IsSystem]"); cols.Add("ParentID","[ParentID]"); cols.Add("Sequence","[Sequence]"); cols.Add("Remark","[Remark]"); int i = 0; //UPDATE COLUMNS foreach (string p in item.ChangedPropertyList) { if(!cols.ContainsKey(p)) { continue; } if (i > 0) { sqlbuilder.Append(","); } sqlbuilder.AppendFormat("{0}=@{1}", cols[p], p); i++; } //WHERE; sqlbuilder.Append(" WHERE [DictID]=@DictID"); List<SqlParameter> SPParams = new List<SqlParameter>(); SPParams.Add(new SqlParameter("@DictID",item.DictID)); if(item.IsChanged("DictName")) { SPParams.Add(new SqlParameter("@DictName",item.DictName)); } if(item.IsChanged("DictCode")) { SPParams.Add(new SqlParameter("@DictCode",item.DictCode)); } if(item.IsChanged("IsSystem")) { SPParams.Add(new SqlParameter("@IsSystem",item.IsSystem)); } if(item.IsChanged("ParentID")) { SPParams.Add(new SqlParameter("@ParentID",item.ParentID)); } if(item.IsChanged("Sequence")) { SPParams.Add(new SqlParameter("@Sequence",item.Sequence)); } if(item.IsChanged("Remark")) { SPParams.Add(new SqlParameter("@Remark",item.Remark)); } base.ExecuteNonQuery(sqlbuilder.ToString(), SPParams.ToArray()); } }
public void Save(DictInfo item) { if(item.IsNew) { Insert(item); } else { Update(item); } }
public List<DictInfo> GetDictInfoTree(int parentId) { string sql = @"SELECT A.[DictID],A.[DictName],A.[DictCode],A.[IsSystem],ISNULL(B.ChildCount,0) AS ChildCount FROM [DictInfos] A LEFT JOIN (Select COUNT(1) as ChildCount, ParentID From DictInfos Group By ParentID) B ON A.DictID=B.ParentID WHERE A.ParentID=@ParentID Order By A.Sequence"; SqlParameter pa = new SqlParameter("@ParentID", parentId); List<DictInfo> list = new List<DictInfo>(); using (IDataReader reader = base.ExcuteDataReader(sql, pa)) { while (reader.Read()) { DictInfo dict = new DictInfo(); dict.DictID = reader.GetInt32(0); dict.DictName = reader.GetString(1); dict.DictCode = reader.GetString(2); dict.IsSystem = reader.GetBoolean(3); dict.HasChild = reader.GetInt32(4) > 0; dict.IsNew = false; list.Add(dict); } } return list; }
public List<DictInfo> QueryDictInfoList(int parentId) { string sql = @"SELECT [DictID],[DictName],[DictCode],[IsSystem],[ParentID],[Sequence],[Remark] FROM [DictInfos] WHERE ParentID=@ParentID Order By Sequence"; SqlParameter pa = new SqlParameter("@ParentID", parentId); List<DictInfo> list = new List<DictInfo>(); using (IDataReader reader = base.ExcuteDataReader(sql, pa)) { while (reader.Read()) { DictInfo dict = new DictInfo(); dict.DictID = reader.GetInt32(0); dict.DictName = reader.GetString(1); dict.DictCode = reader.GetString(2); dict.IsSystem = reader.GetBoolean(3); dict.ParentID = reader.GetInt32(4); dict.Sequence = reader.GetInt32(5); dict.Remark = reader.IsDBNull(6)?"":reader.GetString(6); dict.IsNew = false; list.Add(dict); } } return list; }
public JsonResult SaveDictInfo(int? id, DictInfo di) { JsonReturnMessages msg = new JsonReturnMessages(); try { if (id.HasValue && id.Value > 0) { di.IsNew = false; di.DictID = id.Value; } else { di.IsNew = true; } di.IsSystem = false; sysManageService.SaveDictInfo(di); msg.IsSuccess = true; msg.Msg = "操作成功"; } catch (BizException ex) { msg.IsSuccess = false; msg.Msg = ex.Message; } catch (Exception ex) { msg.IsSuccess = false; msg.Msg = "操作失败:" + ex.Message; } return Json(msg); }
public ActionResult EditDictInfo(int? id, int? ParentID, string ParentName) { DictInfo di; if (id.HasValue) { di = sysManageService.GetDictInfo(id.Value); if (di == null) { throw new ArgumentException("参数错误,不存在对应的数据字典", "DictID"); } else //处理 { } } else { di = new DictInfo(); if (ParentID.HasValue) { di.ParentID = ParentID.Value; di.ParentName = ParentName; } else { di.ParentID = 0; di.ParentName = "数据字典"; } } return View(di); }