public IList <ServiceItemInfo> GetList(int pageIndex, int pageSize, out int totalRecords, string sqlWhere, params SqlParameter[] cmdParms) { StringBuilder sb = new StringBuilder(250); sb.Append(@"select count(*) from Service_Item "); if (!string.IsNullOrEmpty(sqlWhere)) { sb.AppendFormat(" where 1=1 {0} ", sqlWhere); } totalRecords = (int)SqlHelper.ExecuteScalar(SqlHelper.SqlProviderConnString, CommandType.Text, sb.ToString(), cmdParms); if (totalRecords == 0) { return(new List <ServiceItemInfo>()); } sb.Clear(); int startIndex = (pageIndex - 1) * pageSize + 1; int endIndex = pageIndex * pageSize; sb.Append(@"select * from(select row_number() over(order by LastUpdatedDate desc) as RowNumber, Id,Named,ParentId,PictureId,Sort,HasVote,HasContent,HasLink,EnableStartTime,EnableEndTime,IsDisable,LastUpdatedDate from Service_Item " ); if (!string.IsNullOrEmpty(sqlWhere)) { sb.AppendFormat(" where 1=1 {0} ", sqlWhere); } sb.AppendFormat(@")as objTable where RowNumber between {0} and {1} ", startIndex, endIndex); IList <ServiceItemInfo> list = new List <ServiceItemInfo>(); using (SqlDataReader reader = SqlHelper.ExecuteReader(SqlHelper.SqlProviderConnString, CommandType.Text, sb.ToString(), cmdParms)) { if (reader != null && reader.HasRows) { while (reader.Read()) { ServiceItemInfo model = new ServiceItemInfo(); model.Id = reader.GetGuid(1); model.Named = reader.GetString(2); model.ParentId = reader.GetGuid(3); model.PictureId = reader.GetGuid(4); model.Sort = reader.GetInt32(5); model.HasVote = reader.GetBoolean(6); model.HasContent = reader.GetBoolean(7); model.HasLink = reader.GetBoolean(8); model.EnableStartTime = reader.GetDateTime(9); model.EnableEndTime = reader.GetDateTime(10); model.IsDisable = reader.GetBoolean(11); model.LastUpdatedDate = reader.GetDateTime(12); list.Add(model); } } } return(list); }
public string SaveServiceItem(ServiceItemInfo model) { if (string.IsNullOrWhiteSpace(model.Named)) { return(MessageContent.Submit_Params_InvalidError); } Guid gId = Guid.Empty; Guid.TryParse(model.Id.ToString(), out gId); model.Id = gId; Guid parentId = Guid.Empty; Guid.TryParse(model.ParentId.ToString(), out parentId); model.ParentId = parentId; model.LastUpdatedDate = DateTime.Now; ServiceItem bll = new ServiceItem(); int effect = -1; if (!gId.Equals(Guid.Empty)) { var oldModel = bll.GetModel(gId); if (oldModel == null) { return(MessageContent.Submit_Data_NotExists); } model.HasVote = oldModel.HasVote; model.HasContent = oldModel.HasContent; model.HasLink = oldModel.HasLink; effect = bll.Update(model); } else { model.HasVote = false; model.HasContent = false; model.HasLink = false; effect = bll.Insert(model); } if (effect == 110) { return(MessageContent.Submit_Exist); } if (effect > 0) { return("1"); } else { return(MessageContent.Submit_Error); } }
public ServiceItemInfo GetModelByJoin(object Id) { ServiceItemInfo model = null; StringBuilder sb = new StringBuilder(300); sb.Append(@"select top 1 pi.Id,pi.Named,pi.ParentId,pi.PictureId,pi.Sort,pi.HasVote,pi.HasContent,pi.HasLink,pi.EnableStartTime,pi.EnableEndTime,pi.IsDisable,pi.LastUpdatedDate, pip.Named ParentName,psi.FileExtension,psi.FileDirectory,psi.RandomFolder from Service_Item pi left join Service_Item pip on pip.Id = pi.ParentId left join Picture_ServiceItem psi on psi.Id = pi.PictureId where pi.Id = @Id " ); SqlParameter parm = new SqlParameter("@Id", SqlDbType.UniqueIdentifier); parm.Value = Guid.Parse(Id.ToString()); using (SqlDataReader reader = SqlHelper.ExecuteReader(SqlHelper.SqlProviderConnString, CommandType.Text, sb.ToString(), parm)) { if (reader != null) { while (reader.Read()) { model = new ServiceItemInfo(); model.Id = reader.GetGuid(0); model.Named = reader.GetString(1); model.ParentId = reader.GetGuid(2); model.PictureId = reader.GetGuid(3); model.Sort = reader.GetInt32(4); model.HasVote = reader.GetBoolean(5); model.HasContent = reader.GetBoolean(6); model.HasLink = reader.GetBoolean(7); model.EnableStartTime = reader.GetDateTime(8); model.EnableEndTime = reader.GetDateTime(9); model.IsDisable = reader.GetBoolean(10); model.LastUpdatedDate = reader.GetDateTime(11); model.ParentName = reader.IsDBNull(12) ? "" : reader.GetString(12); model.FileExtension = reader.IsDBNull(13) ? "" : reader.GetString(13); model.FileDirectory = reader.IsDBNull(14) ? "" : reader.GetString(14); model.RandomFolder = reader.IsDBNull(15) ? "" : reader.GetString(15); } } } return(model); }
public int Update(ServiceItemInfo model) { if (IsExist(model.Named, model.ParentId, model.Id)) { return(110); } StringBuilder sb = new StringBuilder(250); sb.Append(@"update Service_Item set Named = @Named,ParentId = @ParentId,PictureId = @PictureId,Sort = @Sort,HasVote = @HasVote,HasContent = @HasContent,HasLink = @HasLink,EnableStartTime = @EnableStartTime,EnableEndTime = @EnableEndTime,IsDisable = @IsDisable,LastUpdatedDate = @LastUpdatedDate where Id = @Id " ); SqlParameter[] parms = { new SqlParameter("@Id", SqlDbType.UniqueIdentifier), new SqlParameter("@Named", SqlDbType.NVarChar, 30), new SqlParameter("@ParentId", SqlDbType.UniqueIdentifier), new SqlParameter("@PictureId", SqlDbType.UniqueIdentifier), new SqlParameter("@Sort", SqlDbType.Int), new SqlParameter("@HasVote", SqlDbType.Bit), new SqlParameter("@HasContent", SqlDbType.Bit), new SqlParameter("@HasLink", SqlDbType.Bit), new SqlParameter("@EnableStartTime", SqlDbType.DateTime), new SqlParameter("@EnableEndTime", SqlDbType.DateTime), new SqlParameter("@IsDisable", SqlDbType.Bit), new SqlParameter("@LastUpdatedDate", SqlDbType.DateTime) }; parms[0].Value = model.Id; parms[1].Value = model.Named; parms[2].Value = model.ParentId; parms[3].Value = model.PictureId; parms[4].Value = model.Sort; parms[5].Value = model.HasVote; parms[6].Value = model.HasContent; parms[7].Value = model.HasLink; parms[8].Value = model.EnableStartTime; parms[9].Value = model.EnableEndTime; parms[10].Value = model.IsDisable; parms[11].Value = model.LastUpdatedDate; return(SqlHelper.ExecuteNonQuery(SqlHelper.SqlProviderConnString, CommandType.Text, sb.ToString(), parms)); }
public List <ServiceItemInfo> GetListByJoin() { StringBuilder sb = new StringBuilder(250); sb.Append(@"select si.Id,si.Named,si.ParentId,si.PictureId,si.Sort,si.HasVote,si.HasContent,si.HasLink,si.EnableStartTime,si.EnableEndTime,si.IsDisable,si.LastUpdatedDate, psi.FileExtension,psi.FileDirectory,psi.RandomFolder from Service_Item si left join Picture_ServiceItem psi on psi.Id = si.PictureId order by si.Sort " ); List <ServiceItemInfo> list = new List <ServiceItemInfo>(); using (SqlDataReader reader = SqlHelper.ExecuteReader(SqlHelper.SqlProviderConnString, CommandType.Text, sb.ToString())) { if (reader != null && reader.HasRows) { while (reader.Read()) { ServiceItemInfo model = new ServiceItemInfo(); model.Id = reader.GetGuid(0); model.Named = reader.GetString(1); model.ParentId = reader.GetGuid(2); model.PictureId = reader.GetGuid(3); model.Sort = reader.GetInt32(4); model.HasVote = reader.GetBoolean(5); model.HasContent = reader.GetBoolean(6); model.HasLink = reader.GetBoolean(7); model.EnableStartTime = reader.GetDateTime(8); model.EnableEndTime = reader.GetDateTime(9); model.IsDisable = reader.GetBoolean(10); model.LastUpdatedDate = reader.GetDateTime(11); model.FileExtension = reader.IsDBNull(12) ? "" : reader.GetString(12); model.FileDirectory = reader.IsDBNull(13) ? "" : reader.GetString(13); model.RandomFolder = reader.IsDBNull(14) ? "" : reader.GetString(14); list.Add(model); } } } return(list); }
public IList <ServiceItemInfo> GetList(string sqlWhere, params SqlParameter[] cmdParms) { StringBuilder sb = new StringBuilder(250); sb.Append(@"select Id,Named,ParentId,PictureId,Sort,HasVote,HasContent,HasLink,EnableStartTime,EnableEndTime,IsDisable,LastUpdatedDate from Service_Item "); if (!string.IsNullOrEmpty(sqlWhere)) { sb.AppendFormat(" where 1=1 {0} ", sqlWhere); } IList <ServiceItemInfo> list = new List <ServiceItemInfo>(); using (SqlDataReader reader = SqlHelper.ExecuteReader(SqlHelper.SqlProviderConnString, CommandType.Text, sb.ToString(), cmdParms)) { if (reader != null && reader.HasRows) { while (reader.Read()) { ServiceItemInfo model = new ServiceItemInfo(); model.Id = reader.GetGuid(0); model.Named = reader.GetString(1); model.ParentId = reader.GetGuid(2); model.PictureId = reader.GetGuid(3); model.Sort = reader.GetInt32(4); model.HasVote = reader.GetBoolean(5); model.HasContent = reader.GetBoolean(6); model.HasLink = reader.GetBoolean(7); model.EnableStartTime = reader.GetDateTime(8); model.EnableEndTime = reader.GetDateTime(9); model.IsDisable = reader.GetBoolean(10); model.LastUpdatedDate = reader.GetDateTime(11); list.Add(model); } } } return(list); }
public ServiceItemInfo GetModel(object Id) { ServiceItemInfo model = null; StringBuilder sb = new StringBuilder(300); sb.Append(@"select top 1 Id,Named,ParentId,PictureId,Sort,HasVote,HasContent,HasLink,EnableStartTime,EnableEndTime,IsDisable,LastUpdatedDate from Service_Item where Id = @Id " ); SqlParameter parm = new SqlParameter("@Id", SqlDbType.UniqueIdentifier); parm.Value = Guid.Parse(Id.ToString()); using (SqlDataReader reader = SqlHelper.ExecuteReader(SqlHelper.SqlProviderConnString, CommandType.Text, sb.ToString(), parm)) { if (reader != null) { while (reader.Read()) { model = new ServiceItemInfo(); model.Id = reader.GetGuid(0); model.Named = reader.GetString(1); model.ParentId = reader.GetGuid(2); model.PictureId = reader.GetGuid(3); model.Sort = reader.GetInt32(4); model.HasVote = reader.GetBoolean(5); model.HasContent = reader.GetBoolean(6); model.HasLink = reader.GetBoolean(7); model.EnableStartTime = reader.GetDateTime(8); model.EnableEndTime = reader.GetDateTime(9); model.IsDisable = reader.GetBoolean(10); model.LastUpdatedDate = reader.GetDateTime(11); } } } return(model); }
public IList <ServiceItemInfo> GetListByJoin(int pageIndex, int pageSize, out int totalRecords, string sqlWhere, params SqlParameter[] cmdParms) { StringBuilder sb = new StringBuilder(500); sb.Append(@"select count(*) from Service_Item si left join Service_Picture sp on sp.Id = si.PictureId "); if (!string.IsNullOrEmpty(sqlWhere)) { sb.AppendFormat(" where 1=1 {0} ", sqlWhere); } totalRecords = (int)SqlHelper.ExecuteScalar(SqlHelper.SqlProviderConnString, CommandType.Text, sb.ToString(), cmdParms); if (totalRecords == 0) { return(new List <ServiceItemInfo>()); } sb.Clear(); int startIndex = (pageIndex - 1) * pageSize + 1; int endIndex = pageIndex * pageSize; sb.Append(@"select * from(select row_number() over(order by si.Sort) as RowNumber, si.Id,si.Named,si.ParentId,si.PictureId,si.Sort,si.HasVote,si.HasContent,si.HasLink,si.EnableStartTime,si.EnableEndTime,si.IsDisable,si.LastUpdatedDate, psi.FileExtension,psi.FileDirectory,psi.RandomFolder, (select COUNT(1) from Service_UserPraise where ServiceItemId = si.Id) as TotalPraise, (select COUNT(1) from Service_UserVole where ServiceItemId = si.Id) as TotalVole from Service_Item si left join Picture_ServiceItem psi on psi.Id = si.PictureId "); if (!string.IsNullOrEmpty(sqlWhere)) { sb.AppendFormat(" where 1=1 {0} ", sqlWhere); } sb.AppendFormat(@")as objTable where RowNumber between {0} and {1} ", startIndex, endIndex); IList <ServiceItemInfo> list = new List <ServiceItemInfo>(); using (SqlDataReader reader = SqlHelper.ExecuteReader(SqlHelper.SqlProviderConnString, CommandType.Text, sb.ToString(), cmdParms)) { if (reader != null && reader.HasRows) { while (reader.Read()) { ServiceItemInfo model = new ServiceItemInfo(); model.Id = reader.GetGuid(1); model.Named = reader.GetString(2); model.ParentId = reader.GetGuid(3); model.PictureId = reader.GetGuid(4); model.Sort = reader.GetInt32(5); model.HasVote = reader.GetBoolean(6); model.HasContent = reader.GetBoolean(7); model.HasLink = reader.GetBoolean(8); model.EnableStartTime = reader.GetDateTime(9); model.EnableEndTime = reader.GetDateTime(10); model.IsDisable = reader.GetBoolean(11); model.LastUpdatedDate = reader.GetDateTime(12); model.FileExtension = reader.IsDBNull(13) ? "" : reader.GetString(13); model.FileDirectory = reader.IsDBNull(14) ? "" : reader.GetString(14); model.RandomFolder = reader.IsDBNull(15) ? "" : reader.GetString(15); model.TotalPraise = reader.IsDBNull(16) ? 0 : reader.GetInt32(16); model.TotalVole = reader.IsDBNull(17) ? 0 : reader.GetInt32(17); list.Add(model); } } } return(list); }
/// <summary> /// 修改数据 /// </summary> /// <param name="model"></param> /// <returns></returns> public int Update(ServiceItemInfo model) { return(dal.Update(model)); }
/// <summary> /// 添加数据到数据库 /// </summary> /// <param name="model"></param> /// <returns></returns> public int Insert(ServiceItemInfo model) { return(dal.Insert(model)); }