private string FilterToString(FilterCondition f)
{
var filter = new StringBuilder(f.FieldName);
// These assume a SQLite-backed DSP
// SQLite has some specific date and time requirements becasue it doesn't actually have a DateTime data type
string formattedValue;
if (f.Value is DateTime)
{
// YYYY-MM-DD HH:MM:SS
formattedValue = string.Format("{0:yyyy-MM-dd HH:mm:ss}", f.Value);
}
else if (f.Value is TimeSpan)
{
// HH:MM:SS
formattedValue = string.Format("{0:HH:mm:ss}", f.Value);
}
else
{
formattedValue = f.Value.ToString();
}
switch (f.Operator)
{
case FilterCondition.FilterOperator.Equals:
filter.AppendFormat(" = '{0}'", formattedValue);
break;
case FilterCondition.FilterOperator.GreaterThan:
filter.AppendFormat(" > '{0}'", formattedValue);
break;
case FilterCondition.FilterOperator.LessThan:
filter.AppendFormat(" < '{0}'", formattedValue);
break;
case FilterCondition.FilterOperator.Like:
filter.AppendFormat(" LIKE '{0}'", formattedValue);
break;
}
return(filter.ToString());
}
public override object[] Fetch(Type entityType, int fetchCount, int firstRowOffset, string sortField, FieldOrder sortOrder, FilterCondition filter, bool fillReferences, bool filterReferences)
{
throw new NotImplementedException();
}
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 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)
{
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 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 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 object[] Fetch(Type entityType, int fetchCount, int firstRowOffset, string sortField, FieldOrder sortOrder, FilterCondition filter, bool fillReferences, bool filterReferences)
{
throw new NotSupportedException("Fetch is not currently supported with this Provider.");
}
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);
}
}
public abstract object[] Fetch(Type entityType, int fetchCount, int firstRowOffset, string sortField, FieldOrder sortOrder, FilterCondition filter, bool fillReferences, bool filterReferences);
public abstract T[] Fetch <T>(int fetchCount, int firstRowOffset, string sortField, FieldOrder sortOrder, FilterCondition filter, bool fillReferences, bool filterReferences) where T : new();
public override T[] Fetch <T>(int fetchCount, int firstRowOffset, string sortField, FieldOrder sortOrder, FilterCondition filter, bool fillReferences, bool filterReferences)
{
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 == FieldOrder.None)
{
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
{
CheckPrimaryKeyIndex(entityName);
indexName = Entities[entityName].Fields.KeyField.FieldName;
}
var connection = GetConnection(false);
try
{
using (var command = new SqlCommand())
{
//command.CommandText = Entities[entityName].EntityAttribute.NameInStore;
//command.CommandType = CommandType.TableDirect;
//command.IndexName = indexName;
//command.Connection = connection as SqlConnection;
//command = GetSelectCommand<SqlCommand, SqlParameter>(entityName, filters, out tableDirect);
//command.Connection = connection as SqlConnection;
//int count = 0;
//int currentOffset = 0;
//int filterOrdinal = -1;
//using (var results = command.ExecuteReader(CommandBehavior.SingleResult))
//{
// // 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 the object
// // var item = HydrateEntity<T>(entityName, results, fillReferences);
// // list.Add(item);
// // currentOffset++;
// // count++;
// // }
// //}
// //else
// //{
// // // hydrate the object
// // var item = HydrateEntity<T>(entityName, results, fillReferences);
// // list.Add(item);
// // currentOffset++;
// // count++;
// //}
// if (count >= fetchCount) break;
// }
// }
//}
}
}
finally
{
DoneWithConnection(connection, false);
}
return(list.ToArray());
}
public override IEnumerable <T> Fetch <T>(int fetchCount, int firstRowOffset, string sortField, FieldSearchOrder sortOrder, FilterCondition filter, bool fillReferences)
{
throw new NotImplementedException();
}
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 object[] Fetch(Type entityType, int fetchCount, int firstRowOffset, string sortField, FieldOrder sortOrder, FilterCondition filter, bool fillReferences, bool filterReferences)
{
throw new NotSupportedException("Fetch is not currently supported with this Provider.");
}
