public BugQueryResult ExecuteQuery(IIdentity identity, int start, int length, string orderBy, string sortDirection, bool idOnly, BugQueryFilter[] filters = null) { if (!string.IsNullOrEmpty(orderBy) && !_columnNames.Contains(orderBy)) { throw new ArgumentException("Invalid order by column specified: {0}", orderBy); } bool hasFilters = filters != null && filters.Any(); string columnsToSelect = idOnly ? "id" : "*"; var innerSql = GetInnerSql(identity); var countSql = string.Format("SELECT COUNT(1) FROM ({0}) t", GetInnerSql(identity)); SQLString sqlString = new SQLString(countSql); sqlString.Append(";"); if (hasFilters) { sqlString.Append(countSql); ApplyWhereClause(sqlString, filters); sqlString.Append(";"); } var bugsSql = string.Format("SELECT t.{0} FROM ({1}) t",columnsToSelect, innerSql); sqlString.Append(bugsSql); sqlString.Append(" WHERE id IN ("); var innerBugsSql = string.Format("SELECT t.id FROM ({0}) t", innerSql); sqlString.Append(innerBugsSql); ApplyWhereClause(sqlString, filters); if (hasFilters) { foreach (var filter in filters) { sqlString.AddParameterWithValue(GetCleanParameterName(filter.Column), filter.Value); } } sqlString.Append(" ORDER BY "); sqlString.Append(BuildDynamicOrderByClause(orderBy, sortDirection)); sqlString.Append(" OFFSET @offset ROWS FETCH NEXT @page_size ROWS ONLY)"); int userId = identity.GetUserId(); sqlString.AddParameterWithValue("@ME", userId); sqlString.AddParameterWithValue("page_size", length > 0 ? length : MaxLength); sqlString.AddParameterWithValue("offset", start); DataSet dataSet = DbUtil.get_dataset(sqlString); var countUnfiltered = Convert.ToInt32(dataSet.Tables[0].Rows[0][0]); var countFiltered = hasFilters ? Convert.ToInt32(dataSet.Tables[1].Rows[0][0]) : countUnfiltered; var bugDataTableIndex = hasFilters ? 2 : 1; return new BugQueryResult { CountUnfiltered = countUnfiltered, CountFiltered = countFiltered, Data = dataSet.Tables[bugDataTableIndex] }; }
private void ApplyWhereClause(SQLString sqlString, BugQueryFilter[] filters) { if (filters != null && filters.Any()) { sqlString.Append(" WHERE "); List<string> conditions = new List<string>(); foreach (var filter in filters) { if (!_columnNames.Contains(filter.Column)) { throw new ArgumentException("Invalid filter column: {0}", filter.Column); } string parameterName = GetCleanParameterName(filter.Column); conditions.Add(string.Format("[{0}] = @{1}", filter.Column, parameterName)); } sqlString.Append(string.Join(" AND ", conditions)); } }