public IActionResult UpdateQuery(QueryAndQueryParamsViewModel updateModel)
{
QueryControllerLogic qcl = new QueryControllerLogic(_queryRepository);
qcl.UpdateQuery(updateModel);
return(Ok());
}
/// <summary>
/// Gets the database result of the QueryString (Sql in Query) and the values added to the QueryParams
/// On its own its used to display the result in a html view
/// </summary>
/// <param name="svm">
/// the List<QueryParams> brings the ParameterCodes and the Values added by the user
/// The Query brings the Sql-QueryString to know what to search for and which Table
/// </param>
/// <returns>A DataTable with the matching result to the parameters value</returns>
public DataTable GetSqlRequestToPage(QueryAndQueryParamsViewModel svm)
{
string connectionString = _appSettings.UserConnectionString;
DataTable dataTable = new DataTable();
try
{
using (var da = new SqlDataAdapter(svm.Query.Sql, connectionString))
{
for (int i = 0; i < svm.QueryParams.Count; i++)
{
if (svm.QueryParams[i].Value != null)
{
da.SelectCommand.Parameters.AddWithValue($"{svm.QueryParams[i].ParameterCode}", svm.QueryParams[i].Value);
}
else
{
da.SelectCommand.Parameters.AddWithValue($"{svm.QueryParams[i].ParameterCode}", DBNull.Value);
}
}
da.Fill(dataTable);
}
}
catch (Exception)
{
}
return(dataTable);
}
public IActionResult GetQueryModelWithoutList(int queryId)
{
bool needSqlList = false;
QueryControllerLogic qcl = new QueryControllerLogic(_queryRepository);
QueryAndQueryParamsViewModel svm = qcl.GetQueryViewModel(queryId, needSqlList);
return(Ok(svm));
}
//Gets a Query by QueryId and the QueryParams that belongs to it
public QueryAndQueryParamsViewModel GetQueryViewModel(int id, bool needSqlList)
{
QueryAndQueryParamsViewModel svm = new QueryAndQueryParamsViewModel();
svm.Query = _queryRepository.GetQueryById(id);
svm.QueryParams = _queryRepository.GetQueryParamsByQueryId(id, needSqlList);
return(svm);
}
public IActionResult SearchQueryExcel([FromBody] SearchParameters parameters)
{
SearchControllerLogic scl = new SearchControllerLogic(_queryRepository);
QueryAndQueryParamsViewModel svm = scl.GetQueryResult(parameters);
MemoryStream ms = _queryRequestRepository.GetSqlRequestToExcelFile(svm);
return(new FileStreamResult(ms, new MediaTypeHeaderValue("application/octet-stream")));
}
public IActionResult SearchQuery([FromBody] SearchParameters parameters)
{
SearchControllerLogic scl = new SearchControllerLogic(_queryRepository);
QueryAndQueryParamsViewModel svm = scl.GetQueryResult(parameters);
DataTable sqlResults = _queryRequestRepository.GetSqlRequestToPage(svm);
SqlResultViewModel resultViewModel = SearchControllerLogic.GetResultViewModel(sqlResults);
return(Ok(resultViewModel));
}
//Gets a Query by QueryId and the QueryParams that belongs to it
//Also adds the values from the users search information to the "QueryAndQueryParamsViewModel"
internal QueryAndQueryParamsViewModel GetQueryResult(SearchParameters parameters)
{
bool needSqlList = true;
QueryAndQueryParamsViewModel result = new QueryAndQueryParamsViewModel();
result.Query = _queryRepository.GetQueryById(parameters.QueryId);
result.QueryParams = _queryRepository.GetQueryParamsByQueryId(parameters.QueryId, needSqlList);
for (int i = 0; i < result.QueryParams.Count; i++)
{
if (parameters.Parameters[i].Value != null)
{
result.QueryParams[i].Value = parameters.Parameters[i].Value;
}
}
return(result);
}
/// <summary>
/// Gets the result of the Search in a DataTable from the function "GetSqlRequestToPage"
/// then creates an memorystream to make it possible for the user to download the result to an excel file
/// </summary>
/// <param name="svm">
/// the List<QueryParams> brings the ParameterCodes and the Values added by the user
/// The Query brings the Sql-QueryString to know what to search for and which Table
/// </param>
/// <returns>A memorystream to be converted to a blob in AngularJs in the wwwroot/app/searchController</returns>
public MemoryStream GetSqlRequestToExcelFile(QueryAndQueryParamsViewModel svm)
{
DataTable dataTable = GetSqlRequestToPage(svm);
MemoryStream ms = new MemoryStream();
if (dataTable.Rows.Count != 0)
{
using (XLWorkbook wb = new XLWorkbook())
{
wb.Worksheets.Add(dataTable, "sql-request");
wb.SaveAs(ms);
ms.Seek(0, SeekOrigin.Begin);
}
}
return(ms);
}
//The overall function to update a Query
//Deletes chosen QueryParams, Inserts newly added QueryParams and updates all other changes in the tables [Query] and [QueryParam]
internal void UpdateQuery(QueryAndQueryParamsViewModel updateModel)
{
foreach (int param in updateModel.QueryParamsToDelete)
{
_queryRepository.DeleteQueryParamsById(param);
}
List <QueryParam> newQueryParams = new List <QueryParam>();
List <QueryParam> queryParamsToUpdate = new List <QueryParam>();
foreach (var param in updateModel.QueryParams)
{
if (param.Id == 0)
{
newQueryParams.Add(param);
}
else
{
queryParamsToUpdate.Add(param);
}
}
_queryRepository.InsertQueryParam(newQueryParams, updateModel.Query.Id);
_queryRepository.UpdateQuery(updateModel.Query, queryParamsToUpdate);
}
public IActionResult CreateQuery([FromBody] QueryAndQueryParamsViewModel createModel)
{
_queryRepository.InsertQuery(createModel.Query, createModel.QueryParams);
return(Ok());
}
public MemoryStream GetSqlRequestToExcelFile(QueryAndQueryParamsViewModel svm)
{
return(_queryRequestDb.GetSqlRequestToExcelFile(svm));
}
public DataTable GetSqlRequestToPage(QueryAndQueryParamsViewModel svm)
{
return(_queryRequestDb.GetSqlRequestToPage(svm));
}
