/// <summary>
/// Queries a database for information, composing the query with information provided in the
/// specified query structure. This query uses a single request and as such cannot be used
/// with large data sets.
/// </summary>
/// <param name="databaseId">
/// The unique identifier of the EMS database to query.
/// </param>
/// <param name="query">
/// The information used to construct a query for which results are returned.
/// </param>
/// <param name="callback">
/// A callback to execute for each row of data received.
/// </param>
/// <param name="emsSystem">
/// The unique identifier of the system containing the EMS data.
/// </param>
public virtual Task SimpleQueryAsync(string databaseId, DatabaseQuery query, RowCallback callback, int emsSystem = NoEmsServerSpecified)
{
int ems = GetEmsSystemForMethodCall(emsSystem);
return(CallApiTask(api => api.QueryDatabase(ems, databaseId, query.Raw)).ContinueWith(task =>
{
// Callback for each row.
DbQueryResult queryResult = task.Result;
var result = new DatabaseQueryResult(queryResult.Header);
result.CallbackRows(query, queryResult.Rows, callback);
}));
}
/// <summary>
/// Queries a database for information, composing the query with information provided in the
/// specified query structure. This query uses a single request and as such cannot be used
/// with large data sets.
/// </summary>
/// <param name="databaseId">
/// The unique identifier of the EMS database to query.
/// </param>
/// <param name="query">
/// The information used to construct a query for which results are returned.
/// </param>
/// <param name="emsSystem">
/// The unique identifier of the system containing the EMS data.
/// </param>
public virtual Task <DatabaseQueryResult> SimpleQueryAsync(string databaseId, DatabaseQuery query, int emsSystem = NoEmsServerSpecified)
{
int ems = GetEmsSystemForMethodCall(emsSystem);
return(CallApiTask(api => api.QueryDatabase(ems, databaseId, query.Raw)).ContinueWith(task =>
{
// Convert to our result format.
DbQueryResult queryResult = task.Result;
var result = new DatabaseQueryResult(queryResult.Header);
result.AddRows(query, queryResult.Rows);
return result;
}));
}
public static List <Dictionary <string, object> > RunFullRecordListDictionary(string storedProcedureName, List <Parameter> parameters)
{
List <Dictionary <string, object> > result = new List <Dictionary <string, object> >();
Dictionary <string, object> details = null;
try
{
var r = new DatabaseQueryResult();
using (var conn = new SqlConnection(connectionString))
{
try
{
conn.Open();
using (var cmd = GetCommand(conn, storedProcedureName))
{
cmd.CommandType = CommandType.StoredProcedure;
buildParameters(parameters, cmd);
using (var dr = cmd.ExecuteReader(CommandBehavior.CloseConnection))
{
while (dr.HasRows && dr.Read())
{
details = new Dictionary <string, object>();
for (int i = 0; i < dr.FieldCount; i++)
{
details.Add(dr.GetName(i), dr.IsDBNull(i) ? null : dr.GetValue(i));
}
result.Add(details);
}
}
}
}
finally
{
conn.Close();
}
}
}
catch (Exception e)
{
LoggingHelper.Instance.AgregarLog(new LogSqlEntity("RunFullRecordDictionary", storedProcedureName, buildParametrosToLog(parameters), e));
LoggingHelper.HandleException(e);
}
return(result);
}
private void TestAdvanced(bool orderResults)
{
using (var api = NewService())
{
var query = CreateQuery(orderResults);
// Limit to 100 rows to save bandwidth.
query.Top = 100;
// The regular query uses async-query under the covers and handles pagination.
DatabaseQueryResult result = api.Databases.Query(Monikers.FlightDatabase, query);
foreach (DatabaseQueryResult.Row row in result.Rows)
{
TestRow(row);
}
}
}
/// <summary>
/// Queries the database for information, composing the query with information provided in the
/// specified query structure. This method is a convenience wrapper around the async-query routes
/// to handle pagination, and it also executes a callback function for each row of data.
/// </summary>
/// <param name="databaseId">
/// The unique identifier of the EMS database to query.
/// </param>
/// <param name="query">
/// The information used to construct a query for which results are returned.
/// </param>
/// <param name="callback">
/// A callback to execute for each row of data received.
/// </param>
/// <param name="rowsPerCall">
/// The number of rows to read for each API call.
/// </param>
/// <param name="emsSystem">
/// The unique identifier of the system containing the EMS data.
/// </param>
public virtual async Task QueryAsync(string databaseId, DatabaseQuery query, RowCallback callback, int rowsPerCall = 20000, int emsSystem = NoEmsServerSpecified)
{
AsyncQueryInfo info = await StartQueryAsync(databaseId, query, emsSystem);
var result = new DatabaseQueryResult(info.Header);
int startingRow = 0;
bool moreToRead = true;
while (moreToRead)
{
AsyncQueryData data = await ReadQueryAsync(databaseId, info.Id, startingRow, startingRow + rowsPerCall - 1, emsSystem);
result.CallbackRows(query, data.Rows, callback);
startingRow = startingRow + rowsPerCall;
moreToRead = data.HasMoreRows;
}
await StopQueryAsync(databaseId, info.Id, emsSystem);
}
private void TestSimple(bool orderResults)
{
using (var api = NewService())
{
var query = CreateQuery(orderResults);
// Limit the number of flights returned for the simple query test.
const int numRows = 10;
query.Top = numRows;
// The simple query uses the non-async database route.
DatabaseQueryResult result = api.Databases.SimpleQuery(Monikers.FlightDatabase, query);
result.Rows.Count.Should().Be(numRows);
foreach (DatabaseQueryResult.Row row in result.Rows)
{
TestRow(row);
}
}
}
public static List <T> RunFullRecordGenericForAs400 <T>(string storedProcedureName, List <SqlParameter> parameters)
{
List <T> list = new List <T>();
try
{
var r = new DatabaseQueryResult();
using (var conn = new SqlConnection(connectionString))
{
try
{
conn.Open();
using (var cmd = GetCommand(conn, storedProcedureName))
{
cmd.CommandTimeout = 10;
cmd.CommandType = CommandType.StoredProcedure;
if (parameters != null && parameters.Count > 0)
{
cmd.Parameters.AddRange(parameters.ToArray());
}
using (var dr = cmd.ExecuteReader(CommandBehavior.CloseConnection))
{
list = DataReaderMapToListAs400 <T>(dr);
}
}
}
finally
{
conn.Close();
}
}
}
catch (Exception ex)
{
LoggingHelper.Instance.AgregarLog(new LogSqlEntity("RunFullRecordGeneric", storedProcedureName, parameters, ex));
LoggingHelper.HandleException(ex);
}
return(list);
}
public static List <T> RunFullRecordGeneric <T>(string storedProcedureName, List <Parameter> parameters)
{
List <T> list = new List <T>();
try
{
var r = new DatabaseQueryResult();
using (var conn = new SqlConnection(connectionString))
{
try
{
conn.Open();
using (var cmd = GetCommand(conn, storedProcedureName))
{
cmd.CommandType = CommandType.StoredProcedure;
buildParameters(parameters, cmd);
using (var dr = cmd.ExecuteReader(CommandBehavior.CloseConnection))
{
list = DataReaderMapToList <T>(dr);
}
}
}
finally
{
conn.Close();
}
}
}
catch (Exception ex)
{
LoggingHelper.Instance.AgregarLog(new LogSqlEntity("RunFullRecordGeneric", storedProcedureName, buildParametrosToLog(parameters), ex));
LoggingHelper.HandleException(ex);
}
return(list);
}
public static DatabaseQueryResult RunCombos(string storedProcedureName, List <Parameter> parameters)
{
try
{
var r = new DatabaseQueryResult();
using (var conn = new SqlConnection(connectionString))
{
try
{
conn.Open();
using (SqlCommand cmd = GetCommand(conn, storedProcedureName))
{
cmd.CommandType = CommandType.StoredProcedure;
buildParameters(parameters, cmd);
using (var dr = cmd.ExecuteReader(CommandBehavior.CloseConnection))
{
var resultSetCount = 0;
do
{
var resultSetData = new ArrayList(100);
while (dr.Read())
{
var row = new ArrayList(dr.FieldCount);
for (var i = 0; i < dr.FieldCount; i++)
{
var colValue = dr[i];
row.Add(colValue);
if (dr[i].GetType().Name.Equals("Byte[]"))
{
row[i] = BitConverter.ToString((byte[])colValue, 0);
}
}
resultSetData.Add(row);
}
var rs = new ResultSet(resultSetCount.ToString());
rs.Data = resultSetData;
rs.Columns = getColumnNames(dr);
r.ResultSets.Add(rs);
resultSetCount++;
} while (dr.NextResult());
return(r);
}
}
}
finally
{
conn.Close();
}
}
}
catch (Exception e)
{
LoggingHelper.Instance.AgregarLog(new LogSqlEntity("RunCombos", storedProcedureName, buildParametrosToLog(parameters), e));
LoggingHelper.HandleException(e);
return(null);
}
}
public string e(Querye querye)
{
Dictionary <string, object> metaData = null;
QueryResult qr = null;
Stopwatch stopWatch = null;
bool includeServerMetrics = false;
bool returnColumnNames = false;
string queryName = querye.d;
var inCourseRequest = InCourseRequest.New();
OrdenesApplication ordenesApplication = OrdenesApplication.Instance;
inCourseRequest.SecurityTokenId = ordenesApplication.GetSecurityTokenIdFromHeader();
inCourseRequest.Agencia = ordenesApplication.GetSecurityAgenciaFromHeader();
Query query = null;
try
{
SecurityHelper.ensureAuthenticated(inCourseRequest);
query = JsonConvert.DeserializeObject <Query>(querye.d, new JsonSerializerSettings
{
TypeNameHandling = TypeNameHandling.Objects,
TypeNameAssemblyFormatHandling = TypeNameAssemblyFormatHandling.Simple
});
var type = (QueryType)Enum.Parse(typeof(QueryType), query.Type);
//includeServerMetrics = null != opts && opts.ContainsKey("includeServerMetrics");
//returnColumnNames = null != opts && opts.ContainsKey("returnColumnNames");
includeServerMetrics = null != query.Options && query.Options.Exists(x => x.Key.Equals("includeServerMetrics", StringComparison.OrdinalIgnoreCase));
returnColumnNames = null != query.Options && query.Options.Exists(x => x.Key.Equals("returnColumnNames", StringComparison.OrdinalIgnoreCase));
if (includeServerMetrics)
{
stopWatch = Stopwatch.StartNew();
metaData = new Dictionary <string, object>(1);
}
SecurityHelper.ensureAuthorized(query, inCourseRequest);
queryName = query.Name;
//query.ensureAuthorized(inCourseRequest);
//var queryName = query.getDecriptedQueryName();
//TODO Encender QueryLog.Start para loguear... hay que adaptarlo a Ordenes
QueryLog.Start(query, inCourseRequest);
var requireIdentity = query.Options.Find(x => x.Key.Equals("requireIdentityFilter", StringComparison.OrdinalIgnoreCase));
// Es comun que varias consultas necesiten el usuario que las activa....
if (requireIdentity != null && Convert.ToBoolean(requireIdentity.Value))
{
//query.Filters.Add("IdUsuario", session.Identity_rid);
query.Filters.Add(new Parameter()
{
Key = "IdUsuario", Value = MAEUserSession.Instancia.IdUsuario
});
}
if (query.Options.Exists(x => x.Key == "extendToKnownType"))
{
ExtendQueryFilters(query);
}
if (queryName.Contains("_CUSTOM_"))
{
qr = new QueryResult();
var tName = queryName.Split(new string[] { "_CUSTOM_" }, StringSplitOptions.None)[1] + "CustomQueryById";
object result = reflect(query, tName);
qr.Data = new object[] { result };
qr.Status = "EX0000";
qr.MetaData = metaData;
}
else
{
switch (type)
{
case QueryType.Grid:
var r = new QueryPagingResult();
int totalRows;
string[] columnNames;
object[] r_grid = null;
var draw = 0;
//query.Filters.Remove("maxrecord"); //TODO: porque remueves este parametro?
if (query.Options.Exists(x => x.Key.Equals("gridAdapter", StringComparison.OrdinalIgnoreCase)))
{
var result = (CustomQueryReturn)reflect(query, queryName);
r_grid = result.Data;
totalRows = result.TotalRows;
}
else
{
r_grid = SqlServerHelper.RunGrid("orden_owner." + queryName, query.Filters, out columnNames, out totalRows);
if (returnColumnNames)
{
r.ColumnNames = columnNames;
}
}
var pageSize = query.Filters.Find(i => i.Key == "pageSize");
draw = (pageSize != null) ? Convert.ToInt32(pageSize.Value) : totalRows;
r.Data = r_grid;
r.Status = "EX0000";
r.MetaData = metaData;
r.draw = (int)draw;
r.recordsTotal = totalRows;
r.recordsFiltered = totalRows; //r_grid.Length;
qr = r;
break;
case QueryType.Combos:
qr = new QueryResult();
DatabaseQueryResult r_combos = null;
if (query.Options.Exists(x => x.Key == "filtersAdapter"))
{
r_combos = (DatabaseQueryResult)reflect(query, queryName);
}
else
{
r_combos = SqlServerHelper.RunCombos("orden_owner." + queryName, query.Filters);
}
qr.Data = r_combos.ResultSets.ToArray();
qr.Status = "EX0000";
qr.MetaData = metaData;
break;
case QueryType.Data:
qr = new QueryResult();
qr.Data = new object[1]; // TODO: FIXME: arreglar para tener mas de un data set
qr.Data[0] = SqlServerHelper.RunDictionary("orden_owner." + queryName, query.Filters);
qr.Status = "EX0000";
qr.MetaData = metaData;
break;
case QueryType.FullRecord:
if (query.Options.Exists(x => x.Key.Equals("gridAdapter", StringComparison.OrdinalIgnoreCase)))
{
var rqr = new QueryPagingResult();
var result = (CustomQueryReturn)reflect(query, queryName);
r_grid = result.Data;
totalRows = result.TotalRows;
draw = totalRows;
rqr.Data = r_grid;
rqr.Status = "EX0000";
rqr.MetaData = metaData;
rqr.draw = (int)draw;
rqr.recordsTotal = totalRows;
rqr.recordsFiltered = totalRows; //r_grid.Length;
qr = rqr;
}
else
{
qr = new QueryResult();
var r_fullrecord = SqlServerHelper.RunFullRecord("orden_owner." + queryName, query.Filters);
qr.Data = r_fullrecord.ResultSets.ToArray();
qr.Status = "EX0000";
qr.MetaData = metaData;
}
break;
case QueryType.FullByEntity:
qr = new QueryResult();
//TODO ADAPTAR PARA USAR EF...
//var r_fullrecord = ReadDatabase.Instance.RunFullRecord("orden_owner." + queryName, query.Filters);
//qr.Data = r_fullrecord.ResultSets.ToArray();
qr.Status = "EX0000";
qr.MetaData = metaData;
break;
}
}
//TODO Encender QueryLog.FinishOK para loguear... adapatar a Ordenes
QueryLog.FinishOK(query, qr, inCourseRequest);
}
catch (SessionException sex)
{
qr = new QueryResult();
qr.Data = new object[2] {
inCourseRequest.Id, "Sessión Expirada"
};
qr.Status = "SE6666";
qr.MetaData = metaData;
//TODO: levantar log despues...
QueryLog.FinishWithError(queryName, qr, sex, inCourseRequest);
}
catch (FunctionalException fe)
{
qr = new QueryResult();
qr.Data = new object[2] {
inCourseRequest.Id, fe.Message
};
qr.Status = string.Format("FE{0}", fe.Code.ToString("0000"));
qr.MetaData = metaData;
//TODO: levantar log despues...
QueryLog.FinishWithError(queryName, qr, fe, inCourseRequest);
}
catch (Exception ex)
{
qr = new QueryResult();
qr.Data = new object[2] {
inCourseRequest.Id, ex.Message
};
qr.Status = "TE9999";
qr.MetaData = metaData;
//TODO: levantar log despues...
QueryLog.FinishWithError(queryName, qr, ex, inCourseRequest);
}
finally
{
if (includeServerMetrics)
{
qr.MetaData.Add("serverMetrics", stopWatch.ElapsedMilliseconds);
stopWatch.Reset();
}
}
qr.RequestId = inCourseRequest.Id;
WebOperationContext.Current.OutgoingResponse.ContentType = "application/json; charset=utf-8";
return(JsonConvert.SerializeObject(qr));
}
