protected override object[] Select(string entityName, IEnumerable<FilterCondition> filters, int fetchCount, int firstRowOffset, bool fillReferences, bool filterReferences, IDbConnection connection)
{
if (!m_entities.HasEntity(entityName)) throw new EntityNotFoundException(entityName);
UpdateIndexCacheForType(entityName);
//var genType = typeof(List<>).MakeGenericType(objectType);
//var items = (System.Collections.IList)Activator.CreateInstance(genType);
var items = new List<object>();
SqlEntityInfo entity = m_entities[entityName];
var isDynamicEntity = entity is DynamicEntityInfo;
if (connection == null) connection = GetConnection(false);
SQLiteCommand command = null;
try
{
//Deprecated
//CheckOrdinals(entityName);
OnBeforeSelect(m_entities[entityName], filters, fillReferences);
var start = DateTime.Now;
bool tableDirect;
command = GetSelectCommand<SQLiteCommand, SQLiteParameter>(entityName, filters, firstRowOffset, fetchCount, out tableDirect);
command.Connection = connection as SQLiteConnection;
int searchOrdinal = -1;
// ResultSetOptions options = ResultSetOptions.Scrollable;
object matchValue = null;
string matchField = null;
// TODO: we need to ensure that the search value does not exceed the length of the indexed
// field, else we'll get an exception on the Seek call below (see the SQL CE implementation)
using (var results = command.ExecuteReader(CommandBehavior.SingleResult))
{
if (results.HasRows)
{
var ordinals = GetOrdinals(entityName, results);
ReferenceAttribute[] referenceFields = null;
int currentOffset = 0;
if (matchValue != null)
{
// convert enums to an int, else the .Equals later check will fail
// this feels a bit kludgey, but for now it's all I can think of
if (matchValue.GetType().IsEnum)
{
matchValue = (int)matchValue;
}
if (searchOrdinal < 0)
{
searchOrdinal = ordinals[matchField];
}
}
// autofill references if desired
if (referenceFields == null)
{
referenceFields = Entities[entityName].References.ToArray();
}
while (results.Read())
{
if (currentOffset < firstRowOffset)
{
currentOffset++;
continue;
}
object item = null;
object rowPK = null;
if (isDynamicEntity)
{
var dynamic = new DynamicEntity(entity as DynamicEntityInfo);
foreach (var pair in ordinals)
{
if (entity.Fields[pair.Key].DataType == DbType.Object)
{
if (entity.Deserializer == null)
{
throw new MissingMethodException(
string.Format("The field '{0}' requires a custom serializer/deserializer method pair in the '{1}' Entity",
pair.Key, entityName));
}
dynamic[pair.Key] = entity.Deserializer(dynamic, pair.Key, results[pair.Value]);
}
else
{
dynamic[pair.Key] = results[pair.Value];
}
}
item = dynamic;
}
else if (entity.CreateProxy == null)
{
if (entity.DefaultConstructor == null)
item = Activator.CreateInstance(entity.EntityType);
else
item = entity.DefaultConstructor.Invoke(null);
foreach (var field in Entities[entityName].Fields)
{
var value = results[ordinals[field.FieldName]];
if (value != DBNull.Value)
{
if (field.DataType == DbType.Object)
{
if (entity.Deserializer == null)
{
throw new MissingMethodException(
string.Format("The field '{0}' requires a custom serializer/deserializer method pair in the '{1}' Entity",
field.FieldName, entityName));
}
var @object = entity.Deserializer.Invoke(item, field.FieldName, value);
field.PropertyInfo.SetValue(item, @object, null);
}
else if (field.IsRowVersion)
{
// sql stores this an 8-byte array
field.PropertyInfo.SetValue(item, BitConverter.ToInt64((byte[])value, 0), null);
}
else if (field.PropertyInfo.PropertyType.UnderlyingTypeIs<TimeSpan>())
{
// SQL Compact doesn't support Time, so we're convert to ticks in both directions
var valueAsTimeSpan = new TimeSpan((long)value);
field.PropertyInfo.SetValue(item, valueAsTimeSpan, null);
}
else if ((field.IsPrimaryKey) && (value is Int64) && (field.PropertyInfo.PropertyType.Equals(typeof(Int32))))
{
// SQLite automatically makes auto-increment fields 64-bit, so this works around that behavior
field.PropertyInfo.SetValue(item, Convert.ToInt32(value), null);
}
else if ((value is Int64) || (value is double))
{
// Work Around SQLite underlying storage type
if (field.PropertyInfo.PropertyType.Equals(typeof(UInt32)))
{
field.PropertyInfo.SetValue(item, Convert.ToUInt32(value), null);
}
else if (field.PropertyInfo.PropertyType.Equals(typeof(Int32)))
{
field.PropertyInfo.SetValue(item, Convert.ToInt32(value), null);
}
else if (field.PropertyInfo.PropertyType.Equals(typeof(decimal)))
{
field.PropertyInfo.SetValue(item, Convert.ToDecimal(value), null);
}
else if (field.PropertyInfo.PropertyType.Equals(typeof(float)))
{
field.PropertyInfo.SetValue(item, Convert.ToSingle(value), null);
}
else
{
field.PropertyInfo.SetValue(item, value, null);
}
}
else
{
field.PropertyInfo.SetValue(item, value, null);
}
}
//Check if it is reference key to set, not primary.
ReferenceAttribute attr = referenceFields.Where(
x => x.ReferenceField == field.FieldName).FirstOrDefault();
if (attr != null)
{
rowPK = value;
}
if (field.IsPrimaryKey)
{
rowPK = value;
}
}
}
else
{
item = entity.CreateProxy(results, ordinals);
}
if ((fillReferences) && (referenceFields.Length > 0))
{
//FillReferences(item, rowPK, referenceFields, true);
FillReferences(item, rowPK, referenceFields, false, filterReferences, connection);
}
items.Add(item);
if ((fetchCount > 0) && (items.Count >= fetchCount))
{
break;
}
}
}
}
OnAfterSelect(m_entities[entityName], filters, fillReferences, DateTime.Now.Subtract(start), command.CommandText);
}
finally
{
if ((!UseCommandCache) && (command != null))
{
command.Dispose();
}
if (UseCommandCache)
{
Monitor.Exit(CommandCache);
}
FlushReferenceTableCache();
DoneWithConnection(connection, false);
}
return items.ToArray();
}
protected override object[] Select(string entityName, IEnumerable <FilterCondition> filters, int fetchCount, int firstRowOffset, bool fillReferences, bool filterReferences, IDbConnection connection)
{
if (!m_entities.HasEntity(entityName))
{
throw new EntityNotFoundException(entityName);
}
UpdateIndexCacheForType(entityName);
//var genType = typeof(List<>).MakeGenericType(objectType);
//var items = (System.Collections.IList)Activator.CreateInstance(genType);
var items = new List <object>();
bool tableDirect;
SqlEntityInfo entity = m_entities[entityName];
var isDynamicEntity = entity is DynamicEntityInfo;
if (connection == null)
{
connection = GetConnection(false);
}
FbCommand command = null;
if (UseCommandCache)
{
Monitor.Enter(CommandCache);
}
try
{
command = GetSelectCommand <FbCommand, FbParameter>(entityName, filters, firstRowOffset, fetchCount, out tableDirect);
command.Connection = connection as FbConnection;
int searchOrdinal = -1;
//ResultSetOptions options = ResultSetOptions.Scrollable;
object matchValue = null;
string matchField = null;
using (var results = command.ExecuteReader(CommandBehavior.Default))
{
if (results.HasRows)
{
var ordinals = GetOrdinals(entityName, results);
ReferenceAttribute[] referenceFields = null;
int currentOffset = 0;
if (matchValue != null)
{
// convert enums to an int, else the .Equals later check will fail
// this feels a bit kludgey, but for now it's all I can think of
if (matchValue.GetType().IsEnum)
{
matchValue = (int)matchValue;
}
if (searchOrdinal < 0)
{
searchOrdinal = results.GetOrdinal(matchField);
}
}
// autofill references if desired
if (referenceFields == null)
{
referenceFields = Entities[entityName].References.ToArray();
}
while (results.Read())
{
if (currentOffset < firstRowOffset)
{
currentOffset++;
continue;
}
object item = null;
object rowPK = null;
if (isDynamicEntity)
{
var dynamic = new DynamicEntity(entity as DynamicEntityInfo);
foreach (var pair in ordinals)
{
if (entity.Fields[pair.Key].DataType == DbType.Object)
{
if (entity.Deserializer == null)
{
throw new MissingMethodException(
string.Format("The field '{0}' requires a custom serializer/deserializer method pair in the '{1}' Entity",
pair.Key, entityName));
}
dynamic[pair.Key] = entity.Deserializer(dynamic, pair.Key, results[pair.Value]);
}
else
{
dynamic[pair.Key] = results[pair.Value];
}
}
item = dynamic;
}
else if (entity.CreateProxy == null)
{
if (entity.DefaultConstructor == null)
{
item = Activator.CreateInstance(entity.EntityType);
}
else
{
item = entity.DefaultConstructor.Invoke(null);
}
foreach (var field in Entities[entityName].Fields)
{
var value = results[field.FieldName];
if (value != DBNull.Value)
{
if (field.DataType == DbType.Object)
{
if (entity.Deserializer == null)
{
throw new MissingMethodException(
string.Format("The field '{0}' requires a custom serializer/deserializer method pair in the '{1}' Entity",
field.FieldName, entityName));
}
var @object = entity.Deserializer.Invoke(item, field.FieldName, value);
field.PropertyInfo.SetValue(item, @object, null);
}
else if (field.IsRowVersion)
{
// sql stores this an 8-byte array
field.PropertyInfo.SetValue(item, BitConverter.ToInt64((byte[])value, 0), null);
}
else if (field.PropertyInfo.PropertyType.UnderlyingTypeIs <TimeSpan>())
{
// SQL Compact doesn't support Time, so we're convert to ticks in both directions
var valueAsTimeSpan = new TimeSpan((long)value);
field.PropertyInfo.SetValue(item, valueAsTimeSpan, null);
}
else
{
field.PropertyInfo.SetValue(item, value, null);
}
}
//Check if it is reference key to set, not primary.
ReferenceAttribute attr = referenceFields.Where(
x => x.ReferenceField == field.FieldName).FirstOrDefault();
if (attr != null)
{
rowPK = value;
}
if (field.IsPrimaryKey)
{
rowPK = value;
}
}
}
else
{
item = entity.CreateProxy(results, ordinals);
}
if ((fillReferences) && (referenceFields.Length > 0))
{
//FillReferences(item, rowPK, referenceFields, true);
FillReferences(item, rowPK, referenceFields, false, filterReferences, connection);
}
items.Add(item);
if ((fetchCount > 0) && (items.Count >= fetchCount))
{
break;
}
}
}
}
}
finally
{
if ((!UseCommandCache) && (command != null))
{
command.Dispose();
}
if (UseCommandCache)
{
Monitor.Exit(CommandCache);
}
FlushReferenceTableCache();
DoneWithConnection(connection, false);
}
return(items.ToArray());
}
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);
}
}
private void OnUpdateDynamicEntity(DynamicEntity item)
{
var connection = GetConnection(false);
var entityName = item.EntityName;
try
{
using (var command = GetNewCommandObject())
{
var keyField = Entities[entityName].Fields.FirstOrDefault(f => f.IsPrimaryKey).FieldName;
var keyValue = item.Fields[keyField];
command.Connection = connection;
command.CommandText = string.Format("SELECT * FROM {0} WHERE {1} = {2}keyparam",
entityName,
Entities[entityName].Fields.KeyField.FieldName,
ParameterPrefix);
command.CommandType = CommandType.Text;
command.Parameters.Add(CreateParameterObject(ParameterPrefix + "keyparam", keyValue));
command.Transaction = CurrentTransaction;
var updateSQL = new StringBuilder(string.Format("UPDATE {0} SET ", entityName));
using (var reader = command.ExecuteReader() as OracleDataReader)
{
if (!reader.HasRows)
{
// TODO: the PK value has changed - we need to store the original value in the entity or diallow this kind of change
throw new RecordNotFoundException("Cannot locate a record with the provided primary key. You cannot update a primary key value through the Update method");
}
reader.Read();
using (var insertCommand = GetNewCommandObject())
{
bool changeDetected = false;
// update the values
foreach (var field in item.Fields)
{
// do not update PK fields
if ((keyField != null) && (field.Name == keyField))
{
continue;
}
var value = field.Value;
if (reader[field.Name] != value)
{
changeDetected = true;
if (value == null)
{
updateSQL.AppendFormat("{0}=NULL, ", field.Name);
}
else
{
updateSQL.AppendFormat("{0}={1}{0}, ", field.Name, ParameterPrefix);
insertCommand.Parameters.Add(CreateParameterObject(ParameterPrefix + field.Name, value));
}
}
}
// only execute if a change occurred
if (changeDetected)
{
// remove the trailing comma and append the filter
updateSQL.Length -= 2;
updateSQL.AppendFormat(" WHERE {0} = {1}keyparam", keyField, ParameterPrefix);
insertCommand.Parameters.Add(CreateParameterObject(ParameterPrefix + "keyparam", keyValue));
insertCommand.CommandText = updateSQL.ToString();
insertCommand.Connection = connection;
insertCommand.Transaction = CurrentTransaction;
insertCommand.ExecuteNonQuery();
}
}
}
}
}
finally
{
DoneWithConnection(connection, false);
}
}
private void OnInsertDynamicEntity(DynamicEntity item, bool insertReferences)
{
var connection = GetConnection(false);
try
{
var entityName = item.EntityName;
var command = GetInsertCommand(entityName);
command.Connection = connection as SQLiteConnection;
command.Transaction = CurrentTransaction as SQLiteTransaction;
foreach (var field in item.Fields)
{
// if it's an ID field, don't set it
if (field.Name == item.KeyField)
{
continue;
}
var paramName = ParameterPrefix + field.Name;
if (!command.Parameters.Contains(paramName))
{
// is this a key field that got missed? This is unlikely (it's actually a bug) but
// this check is defensive coding
continue;
}
var @param = command.Parameters[paramName];
if (field.Value is TimeSpan)
{
@param.Value = ((TimeSpan)field.Value).Ticks;
}
else
{
switch (@param.DbType)
{
case DbType.DateTime:
@param.Value = Convert.ToDateTime(field.Value);
break;
default:
@param.Value = field.Value;
break;
}
}
}
command.ExecuteNonQuery();
// did we have a PK field? If so, we need to update that value in the item
if (Entities[entityName].EntityAttribute.KeyScheme == KeyScheme.Identity)
{
var keyField = Entities[entityName].Fields.FirstOrDefault(f => f.IsPrimaryKey);
if (keyField != null)
{
if (Entities[entityName].EntityAttribute.KeyScheme == KeyScheme.Identity)
{
item.Fields[keyField.FieldName] = GetIdentity(connection);
}
}
}
}
finally
{
DoneWithConnection(connection, false);
}
}