private IEnumerable <SqlParameter> ConvertToParameters(List <ChartReportParameter> parameters) { foreach (var param in parameters) { var postgreParam = new SqlParameter(param.Name, _sqlServerMapper.GetSqlDbType(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 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 <ExtractingSchemaQueryModel> Extract(DatabaseConnection database, string formattedString, IEnumerable <ExecuteParamModel> parameters) { var paramsList = new List <SqlParameter>(); 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 = SqlDbType.NVarChar; object parsedValue; if (splitted.Length == 1) { // Default: string type paramDbType = _sqlServerMapper.GetSqlDbType(MapperConstants.String); parsedValue = param.ReplaceValue; } else { // It must contain 2 words paramDbType = _sqlServerMapper.GetSqlDbType(splitted[1]); parsedValue = _cSharpMapper.GetCSharpObjectByType(param.ReplaceValue, splitted[1]); } var fieldParam = StringUtil.GenerateUniqueName(); formattedString = formattedString.Replace("{{" + param.Name + "}}", "@" + fieldParam); paramsList.Add( new SqlParameter(fieldParam, paramDbType) { Value = parsedValue, Direction = ParameterDirection.Input }); } } var extractModel = new ExtractingSchemaQueryModel { ColumnFields = new System.Collections.Generic.List <Models.Shared.ColumnField>() }; using (var sqlDbConnection = new SqlConnection(database.ConnectionString)) { sqlDbConnection.Open(); var warpQuery = @"Select * from ({0}) s limit 1"; warpQuery = string.Format(warpQuery, formattedString); using (var command = new SqlCommand(formattedString, sqlDbConnection)) { 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)); }