private static string CreateQuery(ExcelQuery.ExcelQuery query) { const string pattern = @"SELECT ROW_NUMBER() OVER(ORDER BY {0} {1}) AS RowNumber,* FROM {3} WHERE 1=1 {2} "; var sb = new StringBuilder(); string cond = GetSearchItemCondition(query); sb.AppendFormat(pattern, Query.EncloseInBracket(query.SortOrderItem), query.SortOrderType, cond, query.ViewName); return sb.ToString(); }
public static string GetExcelQuery(ExcelQuery.ExcelQuery query, string baseQuery) { string outPutHeader = string.Empty; string outPutBody = string.Empty; string withBody = "WITH cte AS (" + baseQuery + ")"; foreach (ExcelQueryColumn col in query.ExcelColumns) { outPutHeader += "'" + col.ExcelColumn + "' AS 'th',"; outPutBody += string.Format(" {0} AS 'td',", col.DataBaseColumn); } outPutHeader = outPutHeader.TrimEnd(','); outPutBody = outPutBody.TrimEnd(','); outPutHeader = "SELECT" + outPutHeader + " FOR XML RAW('tr'),ELEMENTS,TYPE"; outPutHeader = "SELECT (" + outPutHeader + ") FOR XML RAW('thead'),ELEMENTS,TYPE"; outPutBody = "SELECT " + outPutBody + " FROM cte FOR XML RAW('tr'),ELEMENTS,TYPE "; outPutBody = "SELECT (" + outPutBody + ") FOR XML RAW('tbody'),ELEMENTS,TYPE"; string outPutQuery; if (query.CompressInDataBase && !string.IsNullOrEmpty(query.CompressFunctionName)) { outPutQuery = withBody + " SELECT ref.fn_compress(CONVERT (varbinary(max),(SELECT '1' border,(" + outPutHeader + "), (" + outPutBody + ") FOR XML RAW ('table'),TYPE)))"; } else { outPutQuery = withBody + " SELECT (" + outPutHeader + "), (" + outPutBody + ") FOR XML PATH ('table'),TYPE"; } return outPutQuery; }
public static string GetExcelQuery(ExcelQuery.ExcelQuery query) { string baseQuery = CreateQuery(query); return GetExcelQuery(query, baseQuery); }