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