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