private DataTable GetPlainSQLReportResult(BaseHeaderDTO headerDTO, string locationID, IList<FilterDTO> allFilters, IDictionary<string, string> parameters) { var filterInfos = GetFilterInfo(allFilters, parameters); var filterString = GetFilterString(filterInfos, headerDTO.Type == "C"); var commandText = headerDTO.QueryStartString + " " + filterString + " " + headerDTO.QueryEndString; var connectionString = new ConnectionStringBuilder().Build(headerDTO.Database, locationID); using (var connection = new SqlConnection(connectionString)) { connection.Open(); using (var cmd = new SqlCommand(commandText, connection)) using (var reader = cmd.ExecuteReader()) using (var table = new DataTable()) { table.Load(reader); return table; } } }
private DataTable GetReportResultDataTable(BaseHeaderDTO header, string userId, string locationID, IList<FilterDTO> allFilters, IDictionary<string, string> parameters) { if (header.Type == "B") return GetStoredProcedureReportResult(header, userId, locationID, allFilters, parameters); return GetPlainSQLReportResult(header, locationID, allFilters, parameters); }
private DataTable GetStoredProcedureReportResult(BaseHeaderDTO headerDTO, string userID, string locationID, IList<FilterDTO> allFilters, IDictionary<string, string> parameters) { var connectionString = new ConnectionStringBuilder().Build(headerDTO.Database, locationID); using (var connection = new SqlConnection(connectionString)) { connection.Open(); using (var cmd = new SqlCommand(headerDTO.StoredProcedureName, connection)) { cmd.CommandType = CommandType.StoredProcedure; foreach(var filter in allFilters) cmd.Parameters.Add(new SqlParameter(filter.Name, parameters.ContainsKey(filter.Name) ? parameters[filter.Name] : "")); cmd.Parameters.Add(new SqlParameter("@strOrder", "")); cmd.Parameters.Add(new SqlParameter("@UID", userID)); using (var reader = cmd.ExecuteReader()) using (var table = new DataTable()) { table.Load(reader); return table; } } } }