public List <Pais> ObtenerTodos()
{
var paises = new List <Pais>();
var pars = new SqlHelper.Params();
pars.Add("Id", 5);
//var connectionSql = new SqlHelper(_connection.ConnectionString);
var procedure = "dbo.GetPaises";
using (var reader = SqlHelper.ExecuteReader(_connection.ConnectionString, procedure))
{
while (reader.Read())
{
var pais = new Pais();
if (reader.IsColumnExists("Id"))
{
pais.Id = SqlParse.GetNullableInt32(reader, "Id");
}
if (reader.IsColumnExists("Nombre"))
{
pais.Nombre = SqlParse.GetNullableString(reader, "Nombre");
}
paises.Add(pais);
}
}
//connectionSql.CloseConnection();
return(paises);
}
private void RunOperationTest(Action <SqlParse> check)
{
SqlParse Parser = new SqlParse();
check(Parser);
var fieldsInclude = !Parser.ClassConfiguration.FieldsInclude;
check(Parser.Configure(fieldsInclude: !fieldsInclude));
var propsInclude = !Parser.ClassConfiguration.PropsInclude;
check(Parser.Configure(propsInclude: !propsInclude));
}
/// <summary>
/// Create definitions of loading functions from specified SQL query
/// </summary>
/// <param name="funcNamesPrefix">Optional prefix for functions names. If null or empty, first table from 'FROM' clause is used</param>
/// <param name="actualityInDays">Used to restrict the minimum queried timestamp // :MIN_TIME = :BEG_TIME-actualityInDays</param>
/// <param name="queryText">SQL query text. Only some subset of SQL is supported
/// (all sources in FROM cluase must have aliases, all fields in SELECT must be specified with source aliases, subqueries is not tested, etc)</param>
/// <returns>Enumeration of pairs (func_name, loading function definition)</returns>
internal static IEnumerable <FuncDef> FuncDefsForSql(Preprocessing.SqlFuncPreprocessingCtx c)
{
var sql = SqlParse.Do(c.queryText, SqlExpr.Options.EmptyFromPossible);
sql = c.PostProc(sql);
if (sql == null)
{
yield break;
}
var actuality = TimeSpan.FromDays((c.actualityInDays < 0) ? Attr.defaultActualityDays : c.actualityInDays);
if (string.IsNullOrEmpty(c.funcNamesPrefix))
{
c.funcNamesPrefix = sql.sources[0].expr.ToString();
}
// generate list of results
bool timedQuery = false;
bool withSeparator = false;
ValueInfo[] resultsInfo;
{
int n = sql.results.Length;
var lst = new List <ValueInfo>(n);
for (int i = 0; i < n; i++)
{
var d = sql.results[i].alias;//.ToUpperInvariant();
if (d == nameof(START_TIME))
{
timedQuery = timedQuery || i == 1;
}
else if (d == nameof(END_TIME) || d == nameof(END_TIME__DT))
{
//if (d.Length == nameof(END_TIME).Length)
// timedQuery = timedQuery || i == 2;
}
else if (d == nameof(INS_OUTS_SEPARATOR))
{
withSeparator = true;
}
else
{
//if (d.Length > 30)
// throw new Generator.Exception($"SQL identifier too long (max 30, but {d.Length} chars in \"{d}\")");
var vi = ValueInfo.Create(d, defaultLocation: c.DefaultLocationForValueInfo);
int DL = vi.DescriptorLength();
if (DL > 30)
{
throw new Generator.Exception($"SQL identifier too long (max 30, but {DL} chars in \"{vi}\")");
}
lst.Add(vi);
}
}
resultsInfo = lst.ToArray();
}
var dbConnName = c.tblAttrs.GetString(Attr.Tbl.DbConnName) ?? c.ldr.dbConnValueName;
#region Some query with INS_OUTS_SEPARATOR column
if (withSeparator || !timedQuery || (c.ldr.forKinds & DbFuncType.Raw) != 0)
{ // separator column present
string[] inputs, outputs;
var qt = SqlQueryNonTimed(sql, c.arrayResults, dbConnName, out inputs, out outputs);
Fn func = FuncNonTimedQuery(qt);
var colsNames = qt.colsNames.Where(s => s != nameof(START_TIME) && s != nameof(END_TIME) && s != nameof(END_TIME__DT)).ToList();
for (int i = inputs.Length - 1; i >= 0; i--)
{
if (!ValueInfo.IsID(inputs[i]))
{
colsNames.RemoveAt(i);
}
}
var fd = new FuncDef(func, c.funcNamesPrefix /* + "_Values"*/, inputs.Length, inputs.Length,
ValueInfo.CreateMany(inputs),
ValueInfo.CreateMany(colsNames.ToArray()),
FuncFlags.Defaults, 0, 0, c.ldr.cachingExpiration, c.ldr.cacheSubdomain,
c.tblAttrs.ToDictionary(p => p.Key.ToString(), p => p.Value)
);
fd.xtraAttrs.Add(nameof(QueryTemplate), qt);
yield return(fd);
yield break;
}
#endregion
#region Range
if ((c.ldr.forKinds & DbFuncType.TimeInterval) != 0)
{
var qt = SqlQueryTimed(sql, DbFuncType.TimeInterval, c.arrayResults, dbConnName);
Fn func = FuncTimedRangeQuery(actuality, qt);
var fd = new FuncDef(func, c.funcNamesPrefix + "_Range", 3, 3,
ValueInfo.CreateMany(qt.colsNames[0], nameof(ValueInfo.A_TIME__XT), nameof(ValueInfo.B_TIME__XT)),
resultsInfo, FuncFlags.Defaults, 0, 0, c.ldr.cachingExpiration, c.ldr.cacheSubdomain,
c.tblAttrs.ToDictionary(p => p.Key.ToString(), p => p.Value)
);
fd.xtraAttrs.Add(nameof(QueryTemplate), qt);
yield return(fd);
}
#endregion
#region Slice at AT_TIME
if ((c.ldr.forKinds & DbFuncType.TimeSlice) != 0)
{
var qt = SqlQueryTimed(sql, DbFuncType.TimeSlice, c.arrayResults, dbConnName);
Fn func = FuncTimedSliceQuery(actuality, qt);
var fd = new FuncDef(func, c.funcNamesPrefix + "_Slice", 2, 2,
ValueInfo.CreateMany(qt.colsNames[0], nameof(ValueInfo.At_TIME__XT)),
resultsInfo, FuncFlags.Defaults, 0, 0, c.ldr.cachingExpiration, c.ldr.cacheSubdomain,
c.tblAttrs.ToDictionary(p => p.Key.ToString(), p => p.Value)
);
fd.xtraAttrs.Add(nameof(QueryTemplate), qt);
yield return(fd);
}
#endregion
#region Raw interval // START_TIME in range MIN_TIME .. MAX_TIME
if ((c.ldr.forKinds & DbFuncType.TimeRawInterval) != 0)
{
var qt = SqlQueryTimed(sql, DbFuncType.TimeRawInterval, c.arrayResults, dbConnName);
Fn func = FuncRawIntervalQuery(qt);
var fd = new FuncDef(func, c.funcNamesPrefix + "_Raw", 3, 3,
ValueInfo.CreateMany(qt.colsNames[0], "MIN_TIME__XT", "MAX_TIME__XT"),
resultsInfo, FuncFlags.Defaults, 0, 0, c.ldr.cachingExpiration, c.ldr.cacheSubdomain,
c.tblAttrs.ToDictionary(p => p.Key.ToString(), p => p.Value)
);
fd.xtraAttrs.Add(nameof(QueryTemplate), qt);
yield return(fd);
}
#endregion
#region Insert rows function
if ((c.ldr.forKinds & DbFuncType.Insert) != 0)
{
var qt = SqlCommandInsert(sql, dbConnName, c.DefaultLocationForValueInfo, out var outputs);
//todo: Sql insert
//Fn func = FuncInsert(qt);
}
#endregion
}
