public Task <DynamicListResponseDataModel> Query(DatabaseConnection databaseConnection, DynamicList dynamicList, DynamicListFetchDataModel fetchDataModel)
{
var response = new DynamicListResponseDataModel();
var hasRows = false;
using (var sqlDbConnection = new SqlConnection(databaseConnection.ConnectionString))
{
var combinedQuery =
_builder
.Init(
dynamicList.ListDatasource.DatabaseConnectionOptions.Query,
fetchDataModel.FilledParameterOptions.FilledParameters,
options =>
{
options.ContainsOperatorFormat = " LIKE '%' + {0} + '%'";
options.PaginationFormat = "OFFSET {1} ROWS FETCH NEXT {0} ROWS ONLY";
options.FieldFormat = "[{0}]";
options.DateCompareFormat = "cast({0} as date){1}cast({2} as date) {3}";
})
.AddTextSearch(fetchDataModel.TextSearch, dynamicList.ColumnsList.ColumnDefs.Where(a => a.SearchOptions.AllowTextSearch).Select(b => b.Name))
.AddFilter(fetchDataModel.FilterGroupOptions.FilterGroups)
.AddSort(fetchDataModel.SortOptions.SortableFields)
.AddPagination(fetchDataModel.PaginationOptions.PageNumber, fetchDataModel.PaginationOptions.PageSize)
.Build();
sqlDbConnection.Open();
_serviceLogger.Info("Current executed query {query}", combinedQuery.CombinedQuery);
_serviceLogger.Info("Current parameters {@parameters}", combinedQuery.Parameters);
using (var cmd = new SqlCommand(combinedQuery.CombinedQuery, sqlDbConnection))
{
foreach (var param in combinedQuery.Parameters)
{
if (param.IsReplacedValue)
{
var castObject = _cSharpMapper.GetCSharpObjectByType(param.Value, param.ReplaceValueType);
cmd.Parameters.Add(
new SqlParameter(
param.Name, _sqlServerMapper.GetSqlDbType(param.ReplaceValueType))
{
Value = castObject,
Direction = System.Data.ParameterDirection.Input
});
}
else
{
cmd.Parameters.Add(
new SqlParameter(
param.Name, GetSqlDbType(param, out var castObject))
{
Value = castObject,
Direction = System.Data.ParameterDirection.Input
});
public Task <DynamicListResponseDataModel> Query(DatabaseConnection databaseConnection, DynamicList dynamicList, DynamicListFetchDataModel fetchDataModel)
{
var response = new DynamicListResponseDataModel();
var hasRows = false;
using (var mysqlDbConnection = new MySqlConnection(databaseConnection.ConnectionString))
{
var combinedQuery =
_builder
.Init(
dynamicList.ListDatasource.DatabaseConnectionOptions.Query,
fetchDataModel.FilledParameterOptions.FilledParameters,
options =>
{
options.ContainsOperatorFormat = "LIKE CONCAT('%', {0},'%')";
options.FieldFormat = "`{0}`";
options.DateCompareFormat = "date({0}){1}date({2})";
})
.AddTextSearch(fetchDataModel.TextSearch, dynamicList.ColumnsList.ColumnDefs.Where(a => a.SearchOptions.AllowTextSearch).Select(b => b.Name))
.AddFilter(fetchDataModel.FilterGroupOptions.FilterGroups)
.AddSort(fetchDataModel.SortOptions.SortableFields)
.AddPagination(fetchDataModel.PaginationOptions.PageNumber, fetchDataModel.PaginationOptions.PageSize)
.Build();
mysqlDbConnection.Open();
using (var cmd = new MySqlCommand(combinedQuery.CombinedQuery, mysqlDbConnection))
{
foreach (var param in combinedQuery.Parameters)
{
if (param.IsReplacedValue)
{
var castObject = _cSharpMapper.GetCSharpObjectByType(param.Value, param.ReplaceValueType);
cmd.Parameters.Add(
new MySqlParameter(
param.Name, _mySqlMapper.GetMySqlDbType(param.ReplaceValueType))
{
Value = castObject,
Direction = System.Data.ParameterDirection.Input
});
}
else
{
cmd.Parameters.Add(
new MySqlParameter(
param.Name, GetMySqlDbType(param, out var castObj))
{
Value = castObj,
Direction = System.Data.ParameterDirection.Input
});
public Task <DynamicListResponseDataModel> Query(DatabaseConnection databaseConnection, DynamicList dynamicList, DynamicListFetchDataModel fetchDataModel)
{
var response = new DynamicListResponseDataModel();
var hasRows = false;
using (var postgreDbConnection = new NpgsqlConnection(databaseConnection.ConnectionString))
{
var combinedQuery =
_builder
.Init(
dynamicList.ListDatasource.DatabaseConnectionOptions.Query,
fetchDataModel.FilledParameterOptions.FilledParameters)
.AddTextSearch(fetchDataModel.TextSearch, dynamicList.ColumnsList.ColumndDefs.Where(a => a.SearchOptions.AllowTextSearch).Select(b => b.Name))
.AddFilter(fetchDataModel.FilterGroupOptions.FilterGroups)
.AddSort(fetchDataModel.SortOptions.SortableFields)
.AddPagination(fetchDataModel.PaginationOptions.PageNumber, fetchDataModel.PaginationOptions.PageSize)
.Build();
postgreDbConnection.Open();
using (var cmd = new NpgsqlCommand(combinedQuery.CombinedQuery, postgreDbConnection))
{
foreach (var param in combinedQuery.Parameters)
{
if (param.IsReplacedValue)
{
var castObject = _cSharpMapper.GetCSharpObjectByType(param.Value, param.ReplaceValueType);
cmd.Parameters.Add(
new NpgsqlParameter(
param.Name, _postgreSqlMapper.GetNpgsqlDbType(param.ReplaceValueType))
{
Value = castObject,
Direction = System.Data.ParameterDirection.Input
});
}
else
{
cmd.Parameters.Add(
new NpgsqlParameter(
param.Name, GetNpgsqlDbType(param, out var castObject))
{
Value = castObject,
Direction = System.Data.ParameterDirection.Input
});