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)); }