public ComplaintEntity Get(int entityId) { StringBuilder strSql = new StringBuilder(); strSql.Append("SELECT c.*, s.SystemName, c.Comments AS UpdatedByName from Complaints c, Systems s "); strSql.Append("WHERE c.ComplaintID=@ComplaintID AND c.SystemID=s.SystemID "); ComplaintEntity comEntity = null; Database db = DatabaseFactory.CreateDatabase(); using (DbCommand dbCommand = db.GetSqlStringCommand(strSql.ToString())) { try { db.AddInParameter(dbCommand, "ComplaintID", DbType.Int32, entityId); using (IDataReader dataReader = db.ExecuteReader(dbCommand)) { if (dataReader.Read()) { comEntity = ComplaintEntity.ReaderBind(dataReader); } } } catch (Exception ex) { WebLogAgent.Write(string.Format("[SQLText:{0},{1}Messages:\r\n{2}]", strSql.ToString(), base.FormatParameters(dbCommand.Parameters), ex.Message)); } } return(comEntity); }
public List <ComplaintEntity> SearchComplaints(ComplaintSearchEntity request, out int recordCount) { recordCount = 0; int startID = request.CurrentPage * request.PageCount + 1 - request.PageCount; int endID = request.CurrentPage * request.PageCount; StringBuilder strSql = new StringBuilder(); StringBuilder strSqlBody = new StringBuilder(); StringBuilder strSelAll = new StringBuilder(); StringBuilder strSelCnt = new StringBuilder(); StringBuilder strOrderBy = new StringBuilder(); StringBuilder strFitPage = new StringBuilder(); strSelCnt.Append("SELECT Count(*) FROM "); strSelAll.Append(String.Format("SELECT * from(SELECT *, Row_Number() Over(Order By {0} {1}) RowNum FROM ", request.OrderExpression, request.OrderDirection)); strSqlBody.Append("(SELECT c.*, s.SystemName, '' AS UpdatedByName "); strSqlBody.Append("FROM Complaints c, Systems s "); strSqlBody.Append("WHERE c.SystemID=s.SystemID AND c.UpdatedByID is NULL "); strSqlBody.Append("UNION "); strSqlBody.Append("SELECT c.*, s.SystemName, u.FirstName+' '+u.LastName AS UpdatedByName "); strSqlBody.Append("FROM Complaints c, Systems s, Users u "); strSqlBody.Append("WHERE c.SystemID=s.SystemID AND c.UpdatedByID=u.UserID) combine "); strSqlBody.Append("WHERE combine.ComplaintID>0 "); strFitPage.Append(") bigCombine where RowNum BETWEEN @StartID AND @EndID "); if (request.Type >= 0) { strSqlBody.Append("AND combine.Type=@Type "); } if (request.Reason >= 0) { strSqlBody.Append("AND combine.Reason=@Reason "); } if (request.SystemID >= 0) { strSqlBody.Append("AND combine.SystemID=@SystemID "); } if (request.AppSrc >= 0) { strSqlBody.Append("AND combine.AppSrc=@AppSrc "); } if (request.Status >= 0) { strSqlBody.Append("AND combine.Status=@Status "); } if (request.Keyword.Length > 0) { strSqlBody.Append("AND (combine.AdditionalInfo like @Keywords OR combine.SystemName like @Keywords) "); } if (request.UpdatedByName.Length > 0) { strSqlBody.Append("AND combine.UpdatedByName like @UpdatedByName "); } strSql.Append(strSelCnt); strSql.Append(strSqlBody); strSql.Append(";"); strSql.Append(strSelAll); strSql.Append(strSqlBody); strSql.Append(strFitPage); // Execute List <ComplaintEntity> list = new List <ComplaintEntity>(); Database db = DatabaseFactory.CreateDatabase(); using (DbCommand dbCommand = db.GetSqlStringCommand(strSql.ToString())) { try { db.AddInParameter(dbCommand, "Type", DbType.Int32, request.Type); db.AddInParameter(dbCommand, "Reason", DbType.Int32, request.Reason); db.AddInParameter(dbCommand, "SystemID", DbType.Int32, request.SystemID); db.AddInParameter(dbCommand, "AppSrc", DbType.Int32, request.AppSrc); db.AddInParameter(dbCommand, "Status", DbType.Int32, request.Status); db.AddInParameter(dbCommand, "Keywords", DbType.String, request.Keyword); db.AddInParameter(dbCommand, "UpdatedByName", DbType.String, request.UpdatedByName); db.AddInParameter(dbCommand, "StartID", DbType.Int32, startID); db.AddInParameter(dbCommand, "EndID", DbType.Int32, endID); using (IDataReader dataReader = db.ExecuteReader(dbCommand)) { if (dataReader.Read()) { recordCount = dataReader.GetInt32(0); dataReader.NextResult(); } while (dataReader.Read()) { list.Add(ComplaintEntity.ReaderBind(dataReader)); } } } catch (Exception ex) { WebLogAgent.Write(string.Format("[SQLText:{0},{1}Messages:\r\n{2}]", strSqlBody.ToString(), base.FormatParameters(dbCommand.Parameters), ex.Message)); } return(list); } }