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