public static List <Event> GetSystemEvent(this TableOperations <Event> eventTable, DateTime startTime, DateTime endTime, double timeTolerance)
{
AdoDataConnection connection = eventTable.Connection;
using (IDbCommand command = connection.Connection.CreateCommand())
{
command.CommandText = "GetSystemEvent";
command.CommandType = CommandType.StoredProcedure;
command.CommandTimeout = connection.DefaultTimeout;
AddParameter(command, "startTime", startTime, DbType.DateTime2);
AddParameter(command, "endTime", endTime, DbType.DateTime2);
AddParameter(command, "timeTolerance", timeTolerance, DbType.Double);
IDataAdapter adapter = (IDataAdapter)Activator.CreateInstance(connection.AdapterType, command);
using (adapter as IDisposable)
{
DataSet dataSet = new DataSet();
adapter.Fill(dataSet);
return(dataSet.Tables[0].Rows
.Cast <DataRow>()
.Select(row => eventTable.LoadRecord(row))
.ToList());
}
}
}
private T QueryRecordWhere(string filterExpression, params object[] parameters)
{
using (AdoDataConnection connection = new AdoDataConnection(Connection))
{
if (CustomView == String.Empty)
{
return(new TableOperations <T>(connection).QueryRecordWhere(filterExpression, parameters));
}
string whereClause = filterExpression;
object[] param = parameters;
if (RootQueryRestriction != null)
{
whereClause = RootQueryRestriction.FilterExpression + " AND " + filterExpression;
param = RootQueryRestriction.Parameters.Concat(parameters).ToArray();
}
whereClause = " WHERE " + whereClause;
string sql = "SELECT * FROM (" + CustomView + ") FullTbl";
DataTable dataTbl = connection.RetrieveData(sql + whereClause, param);
TableOperations <T> tblOperations = new TableOperations <T>(connection);
if (dataTbl.Rows.Count > 0)
{
return(tblOperations.LoadRecord(dataTbl.Rows[0]));
}
return(null);
}
}
private IEnumerable <T> QueryRecords(string sortBy, bool ascending)
{
string orderString = "";
if (!string.IsNullOrEmpty(sortBy))
{
orderString = sortBy + (ascending ? " ASC" : " DESC");
}
if (string.IsNullOrEmpty(sortBy) && !string.IsNullOrEmpty(DefaultSort))
{
orderString = DefaultSort;
}
using (AdoDataConnection connection = new AdoDataConnection(Connection))
{
if (CustomView == String.Empty)
{
if (Take == null)
{
return(new TableOperations <T>(connection).QueryRecords(orderString));
}
else
{
return(new TableOperations <T>(connection).QueryRecords(orderString).Take((int)Take));
}
}
string sql = $@"
SELECT * FROM
({CustomView}) FullTbl
{(sortBy != null ? " ORDER BY " + orderString : "")}";
DataTable dataTbl;
if (RootQueryRestriction != null)
{
sql = $@"
SELECT * FROM
({CustomView}) FullTbl
WHERE ({RootQueryRestriction.FilterExpression})
{(sortBy != null ? " ORDER BY " + orderString : "")}";
dataTbl = connection.RetrieveData(sql, RootQueryRestriction.Parameters);
}
else
{
dataTbl = connection.RetrieveData(sql);
}
List <T> result = new List <T>();
TableOperations <T> tblOperations = new TableOperations <T>(connection);
foreach (DataRow row in dataTbl.Rows)
{
result.Add(tblOperations.LoadRecord(row));
}
if (Take == null)
{
return(result);
}
else
{
return(result.Take((int)Take));
}
}
}
private IEnumerable <T> QueryRecordsWhere(string orderBy, bool ascending, string filterExpression, params object[] parameters)
{
string orderString = "";
if (!string.IsNullOrEmpty(orderBy))
{
orderString = orderBy + (ascending ? " ASC" : " DESC");
}
if (string.IsNullOrEmpty(orderBy) && !string.IsNullOrEmpty(DefaultSort))
{
orderString = DefaultSort;
}
using (AdoDataConnection connection = new AdoDataConnection(Connection))
{
if (CustomView == String.Empty)
{
if (Take == null)
{
return(new TableOperations <T>(connection).QueryRecords(orderString, new RecordRestriction(filterExpression, parameters)));
}
else
{
return(new TableOperations <T>(connection).QueryRecords(orderString, new RecordRestriction(filterExpression, parameters)).Take((int)Take));
}
}
string flt = filterExpression;
object[] param = parameters;
if (RootQueryRestriction != null)
{
flt = RootQueryRestriction.FilterExpression + " AND " + filterExpression;
param = RootQueryRestriction.Parameters.Concat(parameters).ToArray();
}
string sql = $@"
SELECT * FROM
({CustomView}) FullTbl
WHERE {flt}
{(orderBy != null ? " ORDER BY " + orderString : "")}";
DataTable dataTbl = connection.RetrieveData(sql, param);
List <T> result = new List <T>();
TableOperations <T> tblOperations = new TableOperations <T>(connection);
foreach (DataRow row in dataTbl.Rows)
{
result.Add(tblOperations.LoadRecord(row));
}
if (Take == null)
{
return(result);
}
else
{
return(result.Take((int)Take));
}
}
}
