public static IList <ServiceBE> GetServicesByQuery(DreamContext context, out uint totalCount, out uint queryCount) { ServiceType filterType = context.GetParam("type", ServiceType.UNDEFINED); uint limit, offset; SortDirection sortDir; string sortFieldString; Utils.GetOffsetAndCountFromRequest(context, 100, out limit, out offset, out sortDir, out sortFieldString); // Attempt to read the sort field. If a parsing error occurs, default to undefined. ServicesSortField sortField = ServicesSortField.UNDEFINED; if (!String.IsNullOrEmpty(sortFieldString)) { try { sortField = SysUtil.ChangeType <ServicesSortField>(sortFieldString); } catch { } } return(DbUtils.CurrentSession.Services_GetByQuery(filterType == ServiceType.UNDEFINED ? null : filterType.ToString(), sortDir, sortField, offset, limit, out totalCount, out queryCount)); }
public IList<ServiceBE> Services_GetByQuery(string serviceType, SortDirection sortDir, ServicesSortField sortField, uint? offset, uint? limit, out uint totalCount, out uint queryCount) { IList<ServiceBE> services = null; uint totalCountTemp = 0; uint queryCountTemp = 0; StringBuilder whereQuery = new StringBuilder(" where 1=1"); if(!string.IsNullOrEmpty(serviceType)) { whereQuery.AppendFormat(" AND service_type = '{0}'", DataCommand.MakeSqlSafe(serviceType)); } StringBuilder sortLimitQuery = new StringBuilder(); string sortFieldString = null; //Sort by id if no sort specified. if(sortField == ServicesSortField.UNDEFINED) { sortField = ServicesSortField.ID; } if(SERVICES_SORT_FIELD_MAPPING.TryGetValue(sortField, out sortFieldString)) { sortLimitQuery.AppendFormat(" order by {0} ", sortFieldString); if(sortDir != SortDirection.UNDEFINED) { sortLimitQuery.Append(sortDir.ToString()); } } if(limit != null || offset != null) { sortLimitQuery.AppendFormat(" limit {0} offset {1}", limit ?? int.MaxValue, offset ?? 0); } string query = string.Format(@" /* Services_GetByQuery */ select * from services {0} {1}; select service_config.* from service_config join ( select service_id from services {0} {1} ) s on service_config.service_id = s.service_id; select service_prefs.* from service_prefs join ( select service_id from services {0} {1} ) s on service_prefs.service_id = s.service_id; select count(*) as totalcount from services; select count(*) as querycount from services {0}; ", whereQuery, sortLimitQuery); Catalog.NewQuery(query) .Execute(delegate(IDataReader dr) { services = Services_Populate(dr); if(dr.NextResult() && dr.Read()) { totalCountTemp = DbUtils.Convert.To<uint>(dr["totalcount"], 0); } if(dr.NextResult() && dr.Read()) { queryCountTemp = DbUtils.Convert.To<uint>(dr["querycount"], 0); } }); totalCount = totalCountTemp; queryCount = queryCountTemp; return services == null ? new List<ServiceBE>() : services; }
public IList <ServiceBE> Services_GetByQuery(string serviceType, SortDirection sortDir, ServicesSortField sortField, uint?offset, uint?limit, out uint totalCount, out uint queryCount) { IList <ServiceBE> services = null; uint totalCountTemp = 0; uint queryCountTemp = 0; StringBuilder whereQuery = new StringBuilder(" where 1=1"); if (!string.IsNullOrEmpty(serviceType)) { whereQuery.AppendFormat(" AND service_type = '{0}'", DataCommand.MakeSqlSafe(serviceType)); } StringBuilder sortLimitQuery = new StringBuilder(); string sortFieldString = null; //Sort by id if no sort specified. if (sortField == ServicesSortField.UNDEFINED) { sortField = ServicesSortField.ID; } if (SERVICES_SORT_FIELD_MAPPING.TryGetValue(sortField, out sortFieldString)) { sortLimitQuery.AppendFormat(" order by {0} ", sortFieldString); if (sortDir != SortDirection.UNDEFINED) { sortLimitQuery.Append(sortDir.ToString()); } } if (limit != null || offset != null) { sortLimitQuery.AppendFormat(" limit {0} offset {1}", limit ?? int.MaxValue, offset ?? 0); } string query = string.Format(@" /* Services_GetByQuery */ select * from services {0} {1}; select service_config.* from service_config join ( select service_id from services {0} {1} ) s on service_config.service_id = s.service_id; select service_prefs.* from service_prefs join ( select service_id from services {0} {1} ) s on service_prefs.service_id = s.service_id; select count(*) as totalcount from services; select count(*) as querycount from services {0}; ", whereQuery, sortLimitQuery); Catalog.NewQuery(query) .Execute(delegate(IDataReader dr) { services = Services_Populate(dr); if (dr.NextResult() && dr.Read()) { totalCountTemp = DbUtils.Convert.To <uint>(dr["totalcount"], 0); } if (dr.NextResult() && dr.Read()) { queryCountTemp = DbUtils.Convert.To <uint>(dr["querycount"], 0); } }); totalCount = totalCountTemp; queryCount = queryCountTemp; return(services == null ? new List <ServiceBE>() : services); }
public IList<ServiceBE> Services_GetByQuery(string serviceType, SortDirection sortDir, ServicesSortField sortField, uint? offset, uint? limit, out uint totalCount, out uint queryCount) { Stopwatch sw = Stopwatch.StartNew(); var ret = _next.Services_GetByQuery(serviceType, sortDir, sortField, offset, limit, out totalCount, out queryCount); LogQuery(CATEGORY_SERVICES, "Services_GetByQuery", sw, "serviceType", serviceType, "sortDir", sortDir, "sortField", sortField, "offset", offset, "limit", limit); return ret; }