public PagedQueryResultModel ProcessSqlScript(SqlScriptModel sqlScript)
{
var sqlRequest = sqlScript.SqlRequest;
AddParameters(sqlScript);
var connectionString = string.IsNullOrEmpty(sqlRequest.ConnectionStringName) ? config.ConnectionString : config.GetUserConnectionString(sqlRequest.ConnectionStringName);
//var connectionString = "Data Source = 193.93.216.233;Initial Catalog = TILT2_DEV;Integrated Security = False;User Id = sae;Password=Devabit1@";
var offset = (sqlRequest.PaginationModel.PageNumber - 1) * sqlRequest.PaginationModel.PageSize + sqlRequest.PaginationModel.Skip;
var pageSize = sqlRequest.PaginationModel.PageSize;
using (var connection = new SqlConnection(connectionString))
{
connection.Open();
//var totalCount = new SqlCommand(SqlExpression.CountExpression(sqlRequest.SqlScript), connection).ExecuteScalar();
// sqlRequest.SqlScript.AddPaginationToScript(offset, pageSize);
var sqlCommand = new SqlCommand(sqlRequest.SqlScript, connection);
var sqlDataAdapter = new SqlDataAdapter(sqlCommand);
var dataSet = new DataSet();
sqlDataAdapter.Fill(dataSet);
var parser = new DataSetParser(dataSet);
var result = parser.ToQueryResultModel();
var totalCount = result.Rows.Count;
result.Rows = result.Rows.Skip(Convert.ToInt32(offset)).Take(Convert.ToInt32(pageSize)).ToList();
return(new PagedQueryResultModel
{
TotalCount = totalCount,// (int?)totalCount ?? 0,
PageCount = (long)Math.Ceiling((double)totalCount / sqlRequest.PaginationModel.PageSize),
PageNumber = sqlRequest.PaginationModel.PageNumber,
HasNext = Math.Ceiling((double)totalCount / sqlRequest.PaginationModel.PageSize) > sqlRequest.PaginationModel.PageNumber,
Result = result
});
}
}
/// <summary>
/// Processes query script
/// </summary>
/// <param name="script">script text</param>
/// <param name="connection">connection to DB</param>
/// <returns></returns>
private QueryResultModel ProcessQueryResult(string script, SqlConnection connection, int resultRowsNumber = 0)
{
var sqlDataAdapter = new SqlDataAdapter(new SqlCommand(script, connection));
var dataSet = new DataSet();
try
{
sqlDataAdapter.Fill(dataSet);
}
catch (Exception e)
{
throw new BadRequestException("Query can`t be processed");
}
var dataSetParser = new DataSetParser(dataSet);
return(dataSetParser.ToQueryResultModel(resultRowsNumber));
}