private void CreateTreeJson(List <MesCategoryInfo> list, object parentId, MesCategoryInfo rootNode, ref StringBuilder jsonAppend) { jsonAppend.Append("["); var childList = list.FindAll(x => x.ParentId.Equals(parentId)); if (childList.Count > 0) { int index = 0; foreach (var model in childList) { var state = (model.Id == rootNode.Id) ? "open" : "closed"; var sText = model.ParentId.Equals(Guid.Empty) ? model.Named : string.Format("{0} {1}", model.Coded, model.Named); jsonAppend.AppendFormat(@"{{""id"":""{0}"",""text"":""{1}"",""state"":""{2}"",""attributes"":{{""parentId"":""{3}""}}", model.Id, sText, state, model.ParentId); if (list.Any(r => r.ParentId.Equals(model.Id))) { jsonAppend.Append(",\"children\":"); CreateTreeJson(list, model.Id, rootNode, ref jsonAppend); } jsonAppend.Append("}"); if (index < childList.Count - 1) { jsonAppend.Append(","); } index++; } } jsonAppend.Append("]"); }
public IList <MesCategoryInfo> GetList(int pageIndex, int pageSize, out int totalRecords, string sqlWhere, params SqlParameter[] cmdParms) { StringBuilder sb = new StringBuilder(500); sb.Append(@"select count(*) from MesCategory "); if (!string.IsNullOrEmpty(sqlWhere)) { sb.AppendFormat(" where 1=1 {0} ", sqlWhere); } totalRecords = (int)SqlHelper.ExecuteScalar(SqlHelper.WmsDbConnString, CommandType.Text, sb.ToString(), cmdParms); if (totalRecords == 0) { return(new List <MesCategoryInfo>()); } sb.Clear(); int startIndex = (pageIndex - 1) * pageSize + 1; int endIndex = pageIndex * pageSize; sb.Append(@"select * from(select row_number() over(order by Sort) as RowNumber, Id,UserId,ParentId,Coded,Named,Step,WorkStation,StandardHours,Barcode,Sort,Remark,LastUpdatedDate from MesCategory " ); if (!string.IsNullOrEmpty(sqlWhere)) { sb.AppendFormat(" where 1=1 {0} ", sqlWhere); } sb.AppendFormat(@")as objTable where RowNumber between {0} and {1} ", startIndex, endIndex); IList <MesCategoryInfo> list = new List <MesCategoryInfo>(); using (SqlDataReader reader = SqlHelper.ExecuteReader(SqlHelper.WmsDbConnString, CommandType.Text, sb.ToString(), cmdParms)) { if (reader != null && reader.HasRows) { while (reader.Read()) { MesCategoryInfo model = new MesCategoryInfo(); model.Id = reader.GetGuid(1); model.UserId = reader.GetGuid(2); model.ParentId = reader.GetGuid(3); model.Coded = reader.GetString(4); model.Named = reader.GetString(5); model.Step = reader.GetString(6); model.WorkStation = reader.GetString(7); model.StandardHours = reader.GetDouble(8); model.Barcode = reader.GetString(9); model.Sort = reader.GetInt32(10); model.Remark = reader.GetString(11); model.LastUpdatedDate = reader.GetDateTime(12); list.Add(model); } } } return(list); }
public IList <MesCategoryInfo> GetList(string sqlWhere, params SqlParameter[] cmdParms) { StringBuilder sb = new StringBuilder(500); sb.Append(@"select Id,UserId,ParentId,Coded,Named,Step,WorkStation,StandardHours,Barcode,Sort,Remark,LastUpdatedDate from MesCategory "); if (!string.IsNullOrEmpty(sqlWhere)) { sb.AppendFormat(" where 1=1 {0} ", sqlWhere); } sb.Append("order by Sort "); IList <MesCategoryInfo> list = new List <MesCategoryInfo>(); using (SqlDataReader reader = SqlHelper.ExecuteReader(SqlHelper.WmsDbConnString, CommandType.Text, sb.ToString(), cmdParms)) { if (reader != null && reader.HasRows) { while (reader.Read()) { MesCategoryInfo model = new MesCategoryInfo(); model.Id = reader.GetGuid(0); model.UserId = reader.GetGuid(1); model.ParentId = reader.GetGuid(2); model.Coded = reader.GetString(3); model.Named = reader.GetString(4); model.Step = reader.GetString(5); model.WorkStation = reader.GetString(6); model.StandardHours = reader.GetDouble(7); model.Barcode = reader.GetString(8); model.Sort = reader.GetInt32(9); model.Remark = reader.GetString(10); model.LastUpdatedDate = reader.GetDateTime(11); list.Add(model); } } } return(list); }
public MesCategoryInfo GetModel(Guid id) { MesCategoryInfo model = null; StringBuilder sb = new StringBuilder(300); sb.Append(@"select top 1 Id,UserId,ParentId,Coded,Named,Step,WorkStation,StandardHours,Barcode,Sort,Remark,LastUpdatedDate from MesCategory where Id = @Id " ); SqlParameter[] parms = { new SqlParameter("@Id", SqlDbType.UniqueIdentifier) }; parms[0].Value = id; using (SqlDataReader reader = SqlHelper.ExecuteReader(SqlHelper.WmsDbConnString, CommandType.Text, sb.ToString(), parms)) { if (reader != null) { if (reader.Read()) { model = new MesCategoryInfo(); model.Id = reader.GetGuid(0); model.UserId = reader.GetGuid(1); model.ParentId = reader.GetGuid(2); model.Coded = reader.GetString(3); model.Named = reader.GetString(4); model.Step = reader.GetString(5); model.WorkStation = reader.GetString(6); model.StandardHours = reader.GetDouble(7); model.Barcode = reader.GetString(8); model.Sort = reader.GetInt32(9); model.Remark = reader.GetString(10); model.LastUpdatedDate = reader.GetDateTime(11); } } } return(model); }
public int InsertByOutput(MesCategoryInfo model) { StringBuilder sb = new StringBuilder(300); sb.Append(@"insert into MesCategory (Id,UserId,ParentId,Coded,Named,Step,WorkStation,StandardHours,Barcode,Sort,Remark,LastUpdatedDate) values (@Id,@UserId,@ParentId,@Coded,@Named,@Step,@WorkStation,@StandardHours,@Barcode,@Sort,@Remark,@LastUpdatedDate) " ); SqlParameter[] parms = { new SqlParameter("@Id", SqlDbType.UniqueIdentifier), new SqlParameter("@UserId", SqlDbType.UniqueIdentifier), new SqlParameter("@ParentId", SqlDbType.UniqueIdentifier), new SqlParameter("@Coded", SqlDbType.VarChar, 36), new SqlParameter("@Named", SqlDbType.NVarChar, 100), new SqlParameter("@Step", SqlDbType.VarChar, 1000), new SqlParameter("@WorkStation", SqlDbType.NVarChar, 256), new SqlParameter("@StandardHours", SqlDbType.Float), new SqlParameter("@Barcode", SqlDbType.VarChar, 36), new SqlParameter("@Sort", SqlDbType.Int), new SqlParameter("@Remark", SqlDbType.NVarChar, 100), new SqlParameter("@LastUpdatedDate", SqlDbType.DateTime) }; parms[0].Value = model.Id; parms[1].Value = model.UserId; parms[2].Value = model.ParentId; parms[3].Value = model.Coded; parms[4].Value = model.Named; parms[5].Value = model.Step; parms[6].Value = model.WorkStation; parms[7].Value = model.StandardHours; parms[8].Value = model.Barcode; parms[9].Value = model.Sort; parms[10].Value = model.Remark; parms[11].Value = model.LastUpdatedDate; return(SqlHelper.ExecuteNonQuery(SqlHelper.WmsDbConnString, CommandType.Text, sb.ToString(), parms)); }
public int Update(MesCategoryInfo model) { return(dal.Update(model)); }
public int InsertByOutput(MesCategoryInfo model) { return(dal.InsertByOutput(model)); }