public virtual IEnumerable <DynamicEntity> Fetch(string entityName, int fetchCount, int firstRowOffset, string sortField, FieldSearchOrder sortOrder, FilterCondition filter, bool fillReferences)
{
throw new NotSupportedException("This version of Fetch not supported by this provider");
// I could build a complex LINQ statement here, but I think it would end up being crazy inefficient and should be overridden and implemented with sanity instead
// this is *way* inefficient and should almost always be overridden
//var items = from Select(entityName)
// where filter.FieldName
}
public override IEnumerable <T> Fetch <T>(int fetchCount, int firstRowOffset, string sortField, FieldSearchOrder sortOrder, FilterCondition filter, bool fillReferences)
{
if (fillReferences)
{
throw new NotSupportedException("References not currently supported for this Provider");
}
throw new NotImplementedException();
}
public override IEnumerable <DynamicEntity> Fetch(string entityName, int fetchCount, int firstRowOffset, string sortField, FieldSearchOrder sortOrder, FilterCondition filter, bool fillReferences)
{
if (fillReferences)
{
throw new NotSupportedException("References not currently supported with this version of Fetch.");
}
if (filter != null)
{
throw new NotSupportedException("Filters not currently supported with this version of Fetch. Try post-filtering with LINQ");
}
var entities = new List <DynamicEntity>();
if (!Entities.Contains(entityName))
{
// check to see if the underlying table exists
// if it does, add to the Entities and continue the query
if (DiscoverDynamicEntity(entityName) == null)
{
return(entities);
}
}
var v = ServerVersion;
string sql;
if ((sortField == null) && (sortOrder != FieldSearchOrder.NotSearchable))
{
// sorted, but not specific field, so sort by key, if available
if (Entities[entityName].Fields.KeyField != null)
{
sortField = Entities[entityName].Fields.KeyField.FieldName;
}
}
// TODO: get this working then revert the version number to 11 in the "if" startement below
// For now, all paths lead through the else condition
if (v.Major >= 99) // sql server 2012 or later support OFFSET and FETCH
{
Debug.WriteLineIf(TracingEnabled, "SQL Server 2012 or later detected");
if (firstRowOffset <= 0)
{
sql = string.Format("SELECT * FROM {0} ", entityName);
if (!string.IsNullOrEmpty(sortField))
{
sql += string.Format("ORDER BY {0} {1} ", sortField, sortOrder == FieldSearchOrder.Descending ? "DESC" : "ASC");
}
else if (sortOrder != FieldSearchOrder.NotSearchable)
{
if (Entities[entityName].Fields.KeyField != null)
{
sql += string.Format(" ORDER BY {0} {1}", Entities[entityName].Fields.KeyField.FieldName, sortOrder == FieldSearchOrder.Descending ? "DESC" : "ASC");
}
}
if (fetchCount > 0)
{
sql = sql.Replace("*", string.Format("TOP ({0}) *", fetchCount));
}
}
else
{
if (string.IsNullOrEmpty(sortField))
{
sql = string.Format("DECLARE @orderrow varchar(500); "
+ "SELECT @orderrow = column_name "
+ "FROM information_schema.columns "
+ "WHERE table_name = '{0}' "
+ " AND ordinal_position = 1; "
+ "SELECT * FROM {0} "
+ "ORDER BY @orderrow "
+ "OFFSET {1} ROWS "
, entityName
, firstRowOffset);
if (fetchCount > 0)
{
sql += string.Format("FETCH FIRST {0} ROWS ONLY", fetchCount);
}
Debug.WriteLineIf(TracingEnabled, "SQL for empty sort field generated");
}
else
{
sql = string.Format("SELECT * FROM {0} ", entityName);
sql += string.Format("ORDER BY {0} {1} ", sortField, sortOrder == FieldSearchOrder.Descending ? "DESC" : "ASC");
sql += string.Format("OFFSET {0} ROWS ", firstRowOffset);
if (fetchCount > 0)
{
sql += string.Format("FETCH FIRST {0} ROWS ONLY", fetchCount);
}
Debug.WriteLineIf(TracingEnabled, "SQL for search with sort field generated");
}
}
}
else // pre-2012
{
if (firstRowOffset <= 0)
{
sql = string.Format("SELECT * FROM {0} ", entityName);
if (!string.IsNullOrEmpty(sortField))
{
sql += string.Format("ORDER BY {0} {1} ", sortField, sortOrder == FieldSearchOrder.Descending ? "DESC" : "ASC");
}
else if (sortOrder != FieldSearchOrder.NotSearchable)
{
if (Entities[entityName].Fields.KeyField != null)
{
sql += string.Format(" ORDER BY {0} {1}", Entities[entityName].Fields.KeyField.FieldName, sortOrder == FieldSearchOrder.Descending ? "DESC" : "ASC");
}
}
if (fetchCount > 0)
{
sql = sql.Replace("*", string.Format("TOP ({0}) *", fetchCount));
}
Debug.WriteLineIf(TracingEnabled, "SQL for page 1 generated");
}
else
{
if (string.IsNullOrEmpty(sortField))
{
//DECLARE @orderrow varchar(500);
//SELECT @orderrow = column_name
//FROM information_schema.columns
//WHERE table_name = 'C00261854A503_Collector017'
//AND ordinal_position = 1
//;WITH results AS
//(
// SELECT
// *,
// ROW_NUMBER() OVER (
// ORDER BY
// @orderrow
// ) AS orm_row_num
// FROM C00261854A503_Collector017
//)
//SELECT TOP (100) *
//FROM results
//WHERE orm_row_num >= 100
sql = string.Format("DECLARE @orderrow varchar(500); "
+ "SELECT @orderrow = column_name "
+ "FROM information_schema.columns "
+ "WHERE table_name = '{0}' "
+ " AND ordinal_position = 1 "
+ ";WITH results AS ("
+ "SELECT *, "
+ "ROW_NUMBER() OVER (ORDER BY @orderrow) AS orm_row_num "
+ "FROM {0} ) "
+ "{1} * "
+ "FROM results "
+ "WHERE orm_row_num >= {2}",
entityName,
fetchCount > 0 ? string.Format("SELECT TOP ({0}) ", fetchCount) : string.Empty,
firstRowOffset);
Debug.WriteLineIf(TracingEnabled, "SQL for empty sort field generated");
}
else
{
sql = string.Format(";WITH results AS ("
+ "SELECT *, "
+ "ROW_NUMBER() OVER (ORDER BY {0} {1}) AS orm_row_num "
+ "FROM {2} ) "
+ "{3} * "
+ "FROM results "
+ "WHERE orm_row_num >= {4}",
sortField,
sortOrder == FieldSearchOrder.Descending ? "DESC" : "ASC",
entityName,
fetchCount > 0 ? string.Format("SELECT TOP ({0}) ", fetchCount) : string.Empty,
firstRowOffset);
Debug.WriteLineIf(TracingEnabled, "SQL for search with sort field generated");
}
}
}
Debug.WriteLineIf(TracingEnabled, "Getting connection");
var connection = GetConnection(false);
try
{
Debug.WriteLineIf(TracingEnabled, "Getting command");
using (var command = GetNewCommandObject())
{
command.CommandType = CommandType.Text;
command.Connection = connection;
command.CommandText = sql;
using (var reader = command.ExecuteReader())
{
while (reader.Read())
{
var e = new DynamicEntity(entityName);
// TODO: cache ordinals to improve perf
for (int i = 0; i < reader.FieldCount; i++)
{
var name = reader.GetName(i);
// this is a temp column variable - don't return it
if (name == "orm_row_num")
{
continue;
}
e.Fields.Add(name, reader.GetValue(i));
}
entities.Add(e);
}
}
}
}
catch (Exception ex2)
{
}
finally
{
DoneWithConnection(connection, false);
}
return(entities);
}
public abstract IEnumerable <T> Fetch <T>(int fetchCount, int firstRowOffset, string sortField, FieldSearchOrder sortOrder, FilterCondition filter, bool fillReferences) where T : new();
public override IEnumerable <DynamicEntity> Fetch(string entityName, int fetchCount, int firstRowOffset, string sortField, FieldSearchOrder sortOrder, FilterCondition filter, bool fillReferences)
{
// yes, this is very limited in scope capability, but it's purpose-built for a specific use-case (and better than no functionality at all)
if (fillReferences)
{
throw new NotSupportedException("References not currently supported with this version of Fetch.");
}
if (filter != null)
{
throw new NotSupportedException("Filters not currently supported with this version of Fetch. Try post-filtering with LINQ");
}
var entities = new List <DynamicEntity>();
if (!Entities.Contains(entityName))
{
// check to see if the underlying table exists
// if it does, add to the Entities and continue the query
if (DiscoverDynamicEntity(entityName) == null)
{
return(entities);
}
}
var sql = string.Format("SELECT * FROM {0}", entityName);
if (!string.IsNullOrEmpty(sortField))
{
sql += string.Format(" ORDER BY {0} {1}", sortField, sortOrder == FieldSearchOrder.Descending ? "DESC" : "ASC");
}
else if (sortOrder != FieldSearchOrder.NotSearchable)
{
if (Entities[entityName].Fields.KeyField != null)
{
sql += string.Format(" ORDER BY {0} {1}", Entities[entityName].Fields.KeyField.FieldName, sortOrder == FieldSearchOrder.Descending ? "DESC" : "ASC");
}
}
sql += string.Format(" LIMIT {0}", fetchCount);
if (firstRowOffset > 0)
{
sql += string.Format(" OFFSET {0}", firstRowOffset);
}
var connection = GetConnection(false);
try
{
string keyName = null;
if ((m_entities.Contains(entityName)) && (m_entities[entityName].Fields.KeyField != null))
{
keyName = m_entities[entityName].Fields.KeyField.FieldName;
}
using (var command = GetNewCommandObject())
{
command.CommandType = CommandType.Text;
command.Connection = connection;
command.CommandText = sql;
using (var reader = command.ExecuteReader())
{
while (reader.Read())
{
var e = new DynamicEntity(entityName);
for (int i = 0; i < reader.FieldCount; i++)
{
e.Fields.Add(reader.GetName(i), reader.GetValue(i));
}
if (keyName != null)
{
e.KeyField = keyName;
}
entities.Add(e);
}
}
}
return(entities);
}
finally
{
DoneWithConnection(connection, false);
}
}
public override IEnumerable <DynamicEntity> Fetch(string entityName, int fetchCount, int firstRowOffset, string sortField, FieldSearchOrder sortOrder, FilterCondition filter, bool fillReferences)
{
// yes, this is very limited in scope capability, but it's purpose-built for a specific use-case (and better than no functionality at all)
if (fillReferences)
{
throw new NotSupportedException("References not currently supported with this version of Fetch.");
}
if (filter != null)
{
throw new NotSupportedException("Filters not currently supported with this version of Fetch. Try post-filtering with LINQ");
}
var sql = $"SELECT * FROM {entityName} ";
if (!string.IsNullOrEmpty(sortField))
{
sql += string.Format("ORDER BY {0} {1} ", sortField, sortOrder == FieldSearchOrder.Descending ? "DESC" : "ASC");
}
sql += $"LIMIT {fetchCount} ";
if (firstRowOffset > 0)
{
sql += $"OFFSET {firstRowOffset} ";
}
var connection = GetConnection(false);
try
{
var entities = new List <DynamicEntity>();
using (var command = GetNewCommandObject())
{
command.CommandType = CommandType.Text;
command.Connection = connection;
command.CommandText = sql;
using (var reader = command.ExecuteReader())
{
while (reader.Read())
{
var e = new DynamicEntity(entityName);
for (int i = 0; i < reader.FieldCount; i++)
{
e.Fields.Add(reader.GetName(i), reader.GetValue(i));
}
entities.Add(e);
}
}
}
return(entities);
}
finally
{
DoneWithConnection(connection, false);
}
}
public SortBy(string key, FieldSearchOrder searchOrder)
{
this.Key = key;
this.SearchOrder = searchOrder;
}
protected virtual void AddOrderBy <TKey>(Expression <Func <TEntity, TKey> > expr, FieldSearchOrder searchOrder)
{
string columnName = GetColumnName <TKey>(expr);
if (this.order == null)
{
this.order = new Dictionary <string, FieldSearchOrder>();
}
this.order.Add(columnName, searchOrder);
}
public override IEnumerable <T> Fetch <T>(int fetchCount, int firstRowOffset, string sortField, FieldSearchOrder sortOrder, FilterCondition filter, bool fillReferences)
{
throw new NotImplementedException();
}
public override IEnumerable <DynamicEntity> Fetch(string entityName, int fetchCount, int firstRowOffset, string sortField, FieldSearchOrder sortOrder, FilterCondition filter, bool fillReferences)
{
if (fillReferences)
{
throw new NotSupportedException("References not currently supported in this DataStore implementation");
}
string filterString = null;
string orderString = null;
if (filter != null)
{
filterString = FilterToString(filter);
}
if (!string.IsNullOrEmpty(sortField))
{
switch (sortOrder)
{
case FieldSearchOrder.Ascending:
orderString = string.Format("{0} ASC", sortField);
break;
case FieldSearchOrder.Descending:
orderString = string.Format("{0} DESC", sortField);
break;
}
}
var table = m_session.Data.GetTable(entityName);
if (table == null)
{
return(null);
}
try
{
var records = table.GetRecords(fetchCount, firstRowOffset, filterString, orderString);
if (records == null)
{
return(null);
}
var items = new List <DynamicEntity>();
foreach (var record in records)
{
var instance = DynamicEntityFromEntityValueDictionary(entityName, table.Fields, record);
items.Add(instance);
}
return(items);
}
catch
{
return(null);
}
}
public override IEnumerable <T> Fetch <T>(int fetchCount, int firstRowOffset, string sortField, FieldSearchOrder sortOrder, FilterCondition filter, bool fillReferences)
{
if (fillReferences)
{
throw new NotSupportedException("References not currently supported in this DataStore implementation");
}
var entityType = typeof(T);
var entityName = Entities.GetNameForType(entityType);
var table = m_session.Data.GetTable(entityName);
string orderString = null;
string filterString = null;
if (!string.IsNullOrEmpty(sortField))
{
switch (sortOrder)
{
case FieldSearchOrder.Ascending:
orderString = string.Format("{0} ASC", sortField);
break;
case FieldSearchOrder.Descending:
orderString = string.Format("{0} DESC", sortField);
break;
}
}
if (filter != null)
{
filterString = FilterToString(filter);
}
return(table.GetRecords(fetchCount, firstRowOffset, filterString, orderString).Cast <T>());
}
public override IEnumerable <T> Fetch <T>(int fetchCount, int firstRowOffset, string sortField, FieldSearchOrder sortOrder, FilterCondition filter, bool fillReferences)
{
var type = typeof(T);
string entityName = m_entities.GetNameForType(type);
List <T> list = new List <T>();
string indexName;
if (!string.IsNullOrEmpty(sortField))
{
var field = Entities[entityName].Fields[sortField];
if (field == null)
{
throw new FieldNotFoundException(
string.Format("Sort Field '{0}' not found in Entity '{1}'", sortField, entityName));
}
if (sortOrder == FieldSearchOrder.NotSearchable)
{
throw new System.ArgumentException("You must select a valid sort order if providing a sort field.");
}
// ensure that an index exists on the sort field - yes this may slow things down on the first query
indexName = VerifyIndex(entityName, sortField, sortOrder);
}
else
{
indexName = ((SqlEntityInfo)Entities[entityName]).PrimaryKeyIndexName;
}
var connection = GetConnection(false);
try
{
using (var command = new SqlCeCommand())
{
command.CommandText = Entities[entityName].EntityAttribute.NameInStore;
command.CommandType = CommandType.TableDirect;
command.IndexName = indexName;
command.Connection = connection as SqlCeConnection;
command.Transaction = CurrentTransaction as SqlCeTransaction;
int count = 0;
int currentOffset = 0;
int filterOrdinal = -1;
using (var results = command.ExecuteResultSet(ResultSetOptions.Scrollable))
{
// get the ordinal for the filter field (if one is used)
if (filter != null)
{
filterOrdinal = results.GetOrdinal(filter.FieldName);
}
while (results.Read())
{
// skip until we hit the desired offset - respecting any filter
if (currentOffset < firstRowOffset)
{
if (filterOrdinal >= 0)
{
var checkValue = results[filterOrdinal];
if (MatchesFilter(checkValue, filter))
{
currentOffset++;
}
}
else
{
currentOffset++;
}
}
else // pull values (respecting filters) until we have the count fulfilled
{
if (filterOrdinal >= 0)
{
var checkValue = results[filterOrdinal];
if (MatchesFilter(checkValue, filter))
{
// hydrate and return the object
yield return(HydrateEntity <T>(entityName, results, fillReferences));
currentOffset++;
count++;
}
}
else
{
// hydrate and return the object
yield return(HydrateEntity <T>(entityName, results, fillReferences));
currentOffset++;
count++;
}
if (count >= fetchCount)
{
break;
}
}
}
}
}
}
finally
{
DoneWithConnection(connection, false);
}
}
protected override string VerifyIndex(string entityName, string fieldName, FieldSearchOrder searchOrder, IDbConnection connection)
{
bool localConnection = false;
if (connection == null)
{
localConnection = true;
connection = GetConnection(true);
}
try
{
var indexName = string.Format("ORM_IDX_{0}_{1}_{2}", entityName, fieldName,
searchOrder == FieldSearchOrder.Descending ? "DESC" : "ASC");
if (m_indexNameCache.FirstOrDefault(ii => ii.Name == indexName) != null)
{
return(indexName);
}
using (var command = GetNewCommandObject())
{
command.Connection = connection;
var sql = string.Format("SELECT COUNT(*) FROM sqlite_master WHERE type = 'index' AND name = '{0}'", indexName);
command.CommandText = sql;
var i = (long)command.ExecuteScalar();
if (i == 0)
{
sql = string.Format("CREATE INDEX {0} ON {1}({2} {3})",
indexName,
entityName,
fieldName,
searchOrder == FieldSearchOrder.Descending ? "DESC" : string.Empty);
Debug.WriteLine(sql);
command.CommandText = sql;
command.Transaction = CurrentTransaction;
command.ExecuteNonQuery();
}
var indexinfo = new IndexInfo
{
Name = indexName,
MaxCharLength = -1
};
m_indexNameCache.Add(indexinfo);
}
return(indexName);
}
finally
{
if (localConnection)
{
DoneWithConnection(connection, true);
}
}
}