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