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