private IEnumerable <NpgsqlParameter> ConvertToParameters(List <ChartReportParameter> parameters)
{
foreach (var param in parameters)
{
var postgreParam = new NpgsqlParameter(param.Name, _postgreSqlMapper.GetNpgsqlDbType(param.ValueType))
{
Value = param.CastedValue
};
yield return(postgreParam);
}
}
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
});
public Task <ExtractingSchemaQueryModel> Extract(DatabaseConnection database, string formattedString, IEnumerable <ExecuteParamModel> parameters)
{
var paramsList = new List <NpgsqlParameter>();
if (parameters != null)
{
foreach (var param in parameters)
{
// We need to detect a parameter type and then re-mapping to db type
var splitted = param.Name.Split("|");
var paramDbType = NpgsqlDbType.Text;
object parsedValue;
if (splitted.Length == 1)
{
// Default: string type
paramDbType = _postgreSqlMapper.GetNpgsqlDbType(MapperConstants.String);
parsedValue = param.ReplaceValue;
}
else
{
// It must contain 2 words
paramDbType = _postgreSqlMapper.GetNpgsqlDbType(splitted[1]);
parsedValue = _cSharpMapper.GetCSharpObjectByType(param.ReplaceValue, splitted[1]);
}
var fieldParam = StringUtil.GenerateUniqueName();
formattedString = formattedString.Replace("{{" + param.Name + "}}", "@" + fieldParam);
paramsList.Add(
new NpgsqlParameter(fieldParam, paramDbType)
{
Value = parsedValue,
Direction = ParameterDirection.Input
});
}
}
var extractModel = new ExtractingSchemaQueryModel
{
ColumnFields = new System.Collections.Generic.List <Models.Shared.ColumnField>()
};
using (var postgreDbConnection = new NpgsqlConnection(database.ConnectionString))
{
postgreDbConnection.Open();
var warpQuery = @"Select * from ({0}) s limit 1";
warpQuery = string.Format(warpQuery, formattedString);
using (var command = new NpgsqlCommand(formattedString, postgreDbConnection))
{
if (paramsList.Count > 0)
{
command.Parameters.AddRange(paramsList.ToArray());
}
using (var reader = command.ExecuteReader())
{
using (var dt = new DataTable())
{
dt.Load(reader);
foreach (DataColumn dc in dt.Columns)
{
extractModel.ColumnFields.Add(new Models.Shared.ColumnField
{
Name = dc.ColumnName,
DisplayName = dc.ColumnName,
FieldType = GetType(dc.DataType)
});
}
}
}
}
}
return(Task.FromResult(extractModel));
}