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