public List <QueryMetadata> GetColumns(string tableName)
{
var result = MacheteAdoContext.getMetadata($"select top 0 * from {tableName}", _readonlyConnectionString);
result.ForEach(c => c.include = true);
return(result);
}
public List <string> Validate(string query)
{
// because query definition doesn't contain the parameters
// and the goal is only to return query errors to UI
string vars = @"DECLARE @beginDate Date
SET @beginDate = '2021-01-01'
DECLARE @endDate Date
SET @endDate = '2022-01-01'
DECLARE @dwccardnum int
SET @dwccardnum = 10000
";
string fullQuery = string.Concat(vars, query);
var result = MacheteAdoContext.ValidateQuery(fullQuery, _readonlyConnectionString).ToList();
return(result);
}
public List <dynamic> GetDynamicQuery(int id, SearchOptions o)
{
ReportDefinition report = dbset.Single(a => a.ID == id);
List <QueryMetadata> meta = MacheteAdoContext.getMetadata(report.sqlquery, _readonlyConnectionString);
Type queryType = ILVoodoo.buildQueryType(meta);
MethodInfo method = Type.GetType("Machete.Service.MacheteAdoContext")
.GetMethod("SqlQuery", new[] { typeof(string), typeof(string), typeof(SqlParameter[]) });
MethodInfo man = method.MakeGenericMethod(queryType);
var blarg = new List <SqlParameter>()
{
new SqlParameter {
ParameterName = "beginDate", Value = o.beginDate
},
new SqlParameter {
ParameterName = "endDate", Value = o.endDate
}
};
if (o.dwccardnum != null)
{
blarg.Add(new SqlParameter {
ParameterName = "dwccardnum", Value = o.dwccardnum
});
}
dynamic dynamicQuery = man.Invoke(null, new object[] {
report.sqlquery,
_readonlyConnectionString,
blarg.ToArray <SqlParameter>()
});
var dynamicList = new List <dynamic>();
foreach (var row in dynamicQuery)
{
dynamicList.Add(row);
}
return(dynamicList);
}
private string GetJsonColumns(string query)
{
return(MacheteAdoContext.getUIColumnsJson(query, _readonlyConnectionString));
}
public DataTable GetDataTable(string query)
{
// https://stackoverflow.com/documentation/epplus/8223/filling-the-document-with-data
return(MacheteAdoContext.Fill(query, _readonlyConnectionString));
}
