public DataTable GetQuerySourceInDatatable(QuerySourceFilterDto filter, out string columnNames)
{
var query = _queryRepository.Get(filter.QueryGUID);
if (query == null)
{
throw new NotFoundException("Invalid QueryGUID.");
}
var AllColumns = GetAllColumns(filter.QueryGUID);
DataTable data = new DataTable();
string sourceConn = _cfg.GetConnectionString("DefaultConnection");
using (SqlConnection conn = new SqlConnection(sourceConn))
{
SqlCommand cmd = new SqlCommand(GetCommandText(AllColumns, filter, query.ResultTableName), conn);
conn.Open();
data.Load(cmd.ExecuteReader());
}
columnNames = String.Empty;
foreach (Column col in AllColumns.Columns.Where(x => filter.Columns.Contains(x.Name)).ToList())
{
if (String.IsNullOrEmpty(columnNames))
{
columnNames += col.Text;
}
else
{
columnNames += ";" + col.Text;
}
}
return(data);
}
public object GetQuerySource(QuerySourceFilterDto filter)
{
var query = _queryRepository.Get(filter.QueryGUID);
if (query == null)
{
throw new NotFoundException("Invalid QueryGUID.");
}
var AllColumns = GetAllColumns(filter.QueryGUID);
DataTable data = new DataTable();
DataTable count = new DataTable();
string sourceConn = _cfg.GetConnectionString("DefaultConnection");
using (SqlConnection conn = new SqlConnection(sourceConn))
{
SqlCommand cmd = new SqlCommand(GetCommandText(AllColumns, filter, query.ResultTableName), conn);
conn.Open();
data.Load(cmd.ExecuteReader());
SqlCommand countCmd = new SqlCommand(GetCountCommandText(AllColumns, filter, query.ResultTableName), conn);
count.Load(countCmd.ExecuteReader());
}
string countstring = $"\"TotalCount\":{count.Rows[0]["Count"].ToString()}";
string json = "{" + countstring + ",\"Data\" : [";
StringBuilder sb = new StringBuilder(json);
foreach (DataRow row in data.Rows)
{
sb.Append("{");
bool isFirst = true;
foreach (string col in filter.Columns)
{
if (isFirst)
{
sb.Append($"\"{col}\" : \"{row[col].ToString()}\"");
isFirst = false;
}
else
{
sb.Append($",\"{col}\" : \"{row[col].ToString()}\"");
}
}
sb.Append("},");
}
sb[sb.Length - 1] = ']'; //remove last comma
sb.Append("}");
return(sb.ToString());
}
private string GetCountCommandText(AllColumns allColumns, QuerySourceFilterDto filter, string table)
{
string where = String.Empty;
foreach (var col in filter.Columns)
{
if (String.IsNullOrEmpty(where))
{
where += col + $" like '%{filter.Filter}%'";
}
else
{
where += "or " + col + $" like '%{filter.Filter}%'";
}
}
string cmd = $"select count ({allColumns.PrimeryKeyColumn}) as Count from {table} where ({where})";
return(cmd);
}
private string GetCommandText(AllColumns allColumns, QuerySourceFilterDto filter, string table)
{
string columns = String.Empty;
string where = String.Empty;
foreach (var col in filter.Columns)
{
if (String.IsNullOrEmpty(columns))
{
columns += col;
where += col + $" like '%{filter.Filter}%'";
}
else
{
columns += ", " + col;
where += "or " + col + $" like '%{filter.Filter}%'";
}
}
string order_by = filter.Sort.ColumnName + " " + filter.Sort.Direction.ToString();
string skip = String.Empty;
if (filter.Page > 1)
{
skip += $" {allColumns.PrimeryKeyColumn} not in (select top {filter.Rows * (filter.Page - 1)} {allColumns.PrimeryKeyColumn} from {table} order by {order_by}) and ";
}
string top = String.Empty;
if (filter.Rows > 0)
{
top = $"top { filter.Rows}";
}
string cmd = $"select {top} {columns} from {table} where {skip} ({where}) order by {order_by}";
return(cmd);
}
public IActionResult GetQuerySource([FromBody] QuerySourceFilterDto filter)
{
try
{
if (filter == null)
{
throw new BasicException("Wrong input format.");
}
if (!ModelState.IsValid)
{
BadRequest(ModelState);
}
var jsonResult = _manager.GetQuerySource(filter);
return(Ok(jsonResult));
}
catch (BasicException ex)
{
_logger.LogError(ex.Message);
return(BadRequest(ex.Message));
}
catch (NotFoundException ex)
{
_logger.LogError(ex.Message);
return(NotFound(ex.Message));
}
catch (PermissionException ex)
{
_logger.LogError(ex.Message);
return(Unauthorized());
}
catch (Exception ex)
{
_logger.LogError(ex.Message);
return(BadRequest());
}
}