private static void DisplayDebug(IDbCommand command)
{
#if DEBUG
OrmDebug.Trace(command.CommandText);
foreach (IDbDataParameter commandParameter in command.Parameters)
{
string value;
if (commandParameter.Value == DBNull.Value)
{
value = "NULL";
}
else if (commandParameter.Value is DateTime)
{
value = ((DateTime)commandParameter.Value).ToString("MM/dd/yyyy HH:mm:ss.fff");
}
else if (commandParameter.Value is byte[] byteArrayValue)
{
value = Encoding.UTF8.GetString(byteArrayValue, 0, byteArrayValue.Length);
}
else
{
value = commandParameter.Value.ToString();
}
OrmDebug.Trace($"\t{commandParameter.ParameterName}:{value}");
}
#endif
}
public void Insert(object item)
{
var itemType = item.GetType();
var entityName = _datastore.Entities.GetNameForType(itemType);
if (entityName == null)
{
throw new EntityNotFoundException(item.GetType());
}
using var command = ToInsertCommand(_datastore.Entities[entityName], item);
OrmDebug.Info(command.CommandText);
command.ExecuteNonQuery();
// did we have an identity field? If so, we need to update that value in the item
var primaryKey = _datastore.Entities[entityName].PrimaryKey;
if (primaryKey == null)
{
return;
}
if (primaryKey.KeyScheme == KeyScheme.Identity)
{
var id = GetIdentity(primaryKey);
primaryKey.SetEntityValue(item, id);
}
_datastore.Cache?.Cache(item, primaryKey.GetEntityValue(item));
}
protected override void ReleaseManagedResources()
{
base.ReleaseManagedResources();
try
{
foreach (var connection in _pool)
{
if (connection.State != ConnectionState.Closed)
{
connection.Close();
}
connection.Dispose();
}
_pool.Clear();
}
catch (Exception ex)
{
OrmDebug.Info(ex.Message);
if (Debugger.IsAttached)
{
Debugger.Break();
}
}
}
public override void Dispose()
{
try
{
base.Dispose();
foreach (var connection in _connectionPool)
{
if (connection.State != ConnectionState.Closed)
{
connection.Close();
}
connection.Dispose();
}
_connectionPool.Clear();
}
catch (Exception ex)
{
OrmDebug.Info(ex.Message);
if (Debugger.IsAttached)
{
Debugger.Break();
}
}
GC.SuppressFinalize(this);
}
public string[] GetTableNames()
{
var connection = _datastore.GetConnection();
using (var command = connection.CreateCommand())
{
command.CommandText = "SELECT name FROM sqlite_master WHERE type = 'table'";
OrmDebug.Info(command.CommandText);
using (var reader = command.ExecuteReader())
{
var names = new List <string>();
while (reader.Read())
{
var name = reader.GetString(0);
if (name == "sqlite_sequence")
{
continue;
}
names.Add(name);
}
return(names.ToArray());
}
}
}
private void CreateIndex(Index index)
{
var connection = SqlDataStore.GetWriteConnection();
using var command = connection.CreateCommand();
var sql = index.GetCreateSqlQuery();
OrmDebug.Trace(sql);
command.CommandText = sql;
command.ExecuteNonQuery();
}
public string ToStatement(List <IDataParameter> @params)
{
var result = new StringBuilder();
result.Append($"UPDATE [{_entityName}]");
result.Append(_setFieldList.ToStatement(@params));
result.Append(_where.ToStatement(@params));
var sql = result.Append(";").ToString();
OrmDebug.Trace(sql);
return(sql);
}
/// <summary>
/// Synchronize specific remote by it id
/// </summary>
/// <param name="remoteId">Remote id (Index start at 0)</param>
public void SyncRemote(int remoteId)
{
var remote = GetRemote(remoteId);
if (remote == null)
{
return;
}
OrmDebug.Info(string.Format("Synchronize Desktop with remote {0}.", remoteId));
SyncRemote(remote);
}
protected override void CreateForeignKey(ForeignKey foreignKey)
{
var connection = SqlDataStore.GetConnection();
using (var command = connection.CreateCommand())
{
var sql = foreignKey.GetCreateSqlQuery();
OrmDebug.Trace(sql);
command.CommandText = sql;
command.ExecuteNonQuery();
}
}
private void FillEntity(Action <int, object> setter, string entityName, object item)
{
#if DEBUG
var header = string.Empty;
var row = string.Empty;
#endif
var fieldsOrdinal = GetOrdinalsField(entityName);
foreach (var field in _datastore.Entities[entityName].Fields)
{
if (!field.Settable)
{
continue;
}
var fieldOrdinal = fieldsOrdinal[field.FieldName];
var sqlValue = field.ToSqlValue(item);
setter(fieldOrdinal, sqlValue);
#if DEBUG
string rowValue;
if (sqlValue == DBNull.Value)
{
rowValue = string.Format("{0}", "NULL");
}
else if (sqlValue is DateTime)
{
rowValue = string.Format("{0:MM/dd/yyyy hh:mm:ss.fff}", (DateTime)sqlValue);
}
else
{
rowValue = string.Format("{0}", sqlValue);
}
var maxLength = rowValue.Length >= field.FieldName.Length
? rowValue.Length
: field.FieldName.Length;
row += string.Format("| {0} ", rowValue.PadRight(maxLength));
header += string.Format("| {0} ", field.FieldName.PadRight(maxLength));
#endif
}
#if DEBUG
var totalLength = row.Length + 2;
OrmDebug.Info("".PadRight(totalLength, '='));
OrmDebug.Info(header + " |");
OrmDebug.Info(row + " |");
OrmDebug.Info("".PadRight(totalLength, '='));
#endif
}
public string ToStatement(List <IDataParameter> @params)
{
var result = new StringBuilder();
result.Append(FromStatement());
result.Append(JoinStatement(@params));
result.Append(_where.ToStatement(@params));
result.Append(_groupBy.ToStatement());
result.Append(_orderBy.ToStatement());
var sql = result.Append(";").ToString();
OrmDebug.Trace(sql);
return(sql);
}
public void Update(object item)
{
var itemType = item.GetType();
var entityName = _datastore.Entities.GetNameForType(itemType);
if (entityName == null)
{
throw new EntityNotFoundException(itemType);
}
if (_datastore.Entities[entityName].PrimaryKey == null)
{
throw new PrimaryKeyRequiredException("A primary key is required on an Entity in order to perform Updates");
}
var connection = _datastore.GetConnection();
using (var command = new SqlCeCommand())
{
command.Connection = connection as SqlCeConnection;
command.CommandText = entityName;
command.CommandType = CommandType.TableDirect;
command.IndexName = _datastore.Entities[entityName].PrimaryKey.ConstraintName;
command.Transaction = _datastore.CurrentTransaction as SqlCeTransaction;
using (var results = command.ExecuteResultSet(ResultSetOptions.Scrollable | ResultSetOptions.Updatable))
{
var keyValue = _datastore.Entities[entityName].PrimaryKey.GetEntityValue(item);
// seek on the PK
var found = results.Seek(DbSeekOptions.BeforeEqual, keyValue);
if (!found)
{
// 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");
}
results.Read();
OrmDebug.Info("".PadRight(entityName.Length + 11, '='));
OrmDebug.Info(string.Format("| Update {0} |", entityName));
FillEntity(results.SetValue, entityName, item);
results.Update();
}
}
}
public void ApplyDelete(IEntityConflict entityConflict)
{
var entity = GetSyncableEntity();
foreach (var row in Delete)
{
var existing = FindExisting(row);
if (existing == null)
{
OrmDebug.Trace(string.Format("Entity:{0}. deleted on remote but does not exist locally.", EntityName));
continue;
}
ApplyDeletetWhenExistInLocal(entityConflict, existing, entity, row);
}
}
/// <summary>
/// Inserts the provided entity instance into the underlying data store.
/// </summary>
/// <remarks>
/// If the entity has an identity field, calling Insert will populate that field with the identity vale vefore returning
/// </remarks>
public void Insert(object item)
{
var itemType = item.GetType();
var entityName = _datastore.Entities.GetNameForType(itemType);
if (entityName == null)
{
throw new EntityNotFoundException(item.GetType());
}
// we'll use table direct for inserts - no point in getting the query parser involved in this
var connection = _datastore.GetConnection();
using (var command = new SqlCeCommand())
{
command.Connection = connection as SqlCeConnection;
command.Transaction = _datastore.CurrentTransaction as SqlCeTransaction;
command.CommandText = entityName;
command.CommandType = CommandType.TableDirect;
using (var results = command.ExecuteResultSet(ResultSetOptions.Updatable))
{
var record = results.CreateRecord();
OrmDebug.Info("".PadRight(entityName.Length + 11, '='));
OrmDebug.Info(string.Format("| Insert {0} |", entityName));
FillEntity(record.SetValue, entityName, item);
results.Insert(record);
// did we have an identity field? If so, we need to update that value in the item
var primaryKey = _datastore.Entities[entityName].PrimaryKey;
if (primaryKey.KeyScheme == KeyScheme.Identity)
{
var id = GetIdentity(connection);
primaryKey.SetEntityValue(item, id);
}
if (_datastore.Cache != null)
{
_datastore.Cache.Cache(item, primaryKey.GetEntityValue(item));
}
}
command.Dispose();
}
}
private IDbConnection GetPoolConnection()
{
if (IsDisposed)
{
throw new ObjectDisposedException("SqlStoreBase");
}
lock (_connectionPool)
{
IDbConnection connection;
do
{
connection = GetFreeConnectionInPool();
if (connection != null)
{
if (Open(connection))
{
return(connection);
}
// Broken connection, maybe disposed
_connectionPool.Remove(connection);
}
if (_connectionPool.Count < ConnectionPoolSize)
{
connection = _dbEngine.GetNewConnection();
connection.Open();
_connectionPool.Add(connection);
Interlocked.Increment(ref _connectionCount);
OrmDebug.Trace("Creating pooled connection");
return(connection);
}
// pool is full, we have to wait
Thread.Sleep(1000);
// TODO: add a timeout?
} while (connection == null);
// this should never happen
throw new TimeoutException("Unable to get a pooled connection.");
}
}
public void Populate()
{
var entity = GetSyncableEntity();
OrmDebug.Trace("");
PopulateInsert(entity);
OrmDebug.Trace(GetChangeDisplay("Inserted", Insert));
PopulateUpdate(entity);
OrmDebug.Trace(GetChangeDisplay("Updated", Update));
if (entity.IsDeleteTrackEnable)
{
PopulateDelete(entity);
OrmDebug.Trace(GetChangeDisplay("Deleted", Delete));
}
PopulateLastSync(entity);
OrmDebug.Trace(GetChangeDisplay("LastSync", Update));
}
public IDbConnection GetConnection()
{
if (IsDisposed)
{
throw new ObjectDisposedException("ConnectionPool");
}
lock (_pool)
{
IDbConnection connection;
do
{
connection = GetFreeConnectionInPool();
if (connection != null)
{
if (Open(connection))
{
return(connection);
}
// Broken connection, maybe disposed
connection.Dispose();
_pool.Remove(connection);
}
if (_pool.Count < ConnectionPoolSize)
{
connection = _dbEngine.GetNewConnection();
connection.Open();
_pool.Add(connection);
OrmDebug.Trace("Creating pooled connection");
return(connection);
}
OrmDebug.Trace("Pool full waiting for free connection");
Thread.Sleep(1000);
// TODO: add a timeout?
} while (connection == null);
throw new TimeoutException("Unable to get a pooled connection.");
}
}
/// <summary>
/// </summary>
/// <param name="sql"></param>
/// <returns></returns>
/// <remarks>You <b>MUST</b> call CloseReader after calling this method to prevent a leak</remarks>
public virtual IDataReader ExecuteReader(string sql)
{
try
{
var connection = GetReadConnection();
var command = SqlFactory.CreateCommand();
command.CommandText = sql;
command.Connection = connection;
command.Transaction = CurrentTransaction;
var reader = command.ExecuteReader(CommandBehavior.Default);
return(reader);
}
catch (Exception ex)
{
OrmDebug.Trace("SQLStoreBase::ExecuteReader threw: " + ex.Message);
throw;
}
}
public void Delete(object item)
{
var itemType = item.GetType();
var entityName = _datastore.Entities.GetNameForType(itemType);
if (entityName == null)
{
throw new EntityNotFoundException(itemType);
}
if (_datastore.Entities[entityName].PrimaryKey == null)
{
throw new PrimaryKeyRequiredException(
"A primary key is required on an Entity in order to perform delete");
}
using var command = ToDeleteCommand(_datastore.Entities[entityName], item);
OrmDebug.Info(command.CommandText);
command.ExecuteNonQuery();
}
public string CreateTable(IEntityInfo entity)
{
var sql = new StringBuilder();
sql.AppendFormat("CREATE TABLE [{0}] (", entity.GetNameInStore());
var first = true;
foreach (var field in entity.Fields)
{
if (first)
{
first = false;
}
else
{
sql.Append(", ");
}
sql.AppendFormat(field.GetFieldDefinition());
}
foreach (var field in entity.Fields)
{
var constraint = field.GetFieldConstraint();
if (string.IsNullOrEmpty(constraint))
{
continue;
}
sql.AppendFormat(", {0}", constraint);
}
sql.Append(")");
OrmDebug.Info(sql.ToString());
return(sql.ToString());
}
private static void DisplayDebug(IDbCommand command)
{
#if DEBUG
OrmDebug.Trace(command.CommandText);
foreach (IDbDataParameter commandParameter in command.Parameters)
{
string value;
if (commandParameter.Value == DBNull.Value)
{
value = "NULL";
}
else if (commandParameter.Value is DateTime)
{
value = ((DateTime)commandParameter.Value).ToString("MM/dd/yyyy HH:mm:ss.fff");
}
else
{
value = commandParameter.Value.ToString();
}
OrmDebug.Trace(string.Format("\t{0}:{1}", commandParameter.ParameterName, value));
}
#endif
}
public void CreateTable(IDbConnection connection, IEntityInfo entity)
{
var sql = new StringBuilder();
var entityNameInStore = entity.GetNameInStore();
if (ReservedWords.Contains(entityNameInStore, StringComparer.InvariantCultureIgnoreCase))
{
throw new ReservedWordException(entityNameInStore);
}
sql.AppendFormat("CREATE TABLE [{0}] (", entityNameInStore);
var first = true;
foreach (var field in entity.Fields)
{
if (ReservedWords.Contains(field.FieldName, StringComparer.InvariantCultureIgnoreCase))
{
throw new ReservedWordException(field.FieldName);
}
if (first)
{
first = false;
}
else
{
sql.Append(", ");
}
sql.AppendFormat(field.GetFieldDefinitionSqlQuery());
}
foreach (var foreignKey in entity.ForeignKeys)
{
sql.Append(", ");
sql.AppendFormat(foreignKey.GetTableCreateSqlQuery());
}
sql.Append(")");
OrmDebug.Info(sql.ToString());
using (var command = SqlFactory.CreateCommand())
{
command.CommandText = sql.ToString();
command.Connection = connection;
command.Transaction = CurrentTransaction;
command.ExecuteNonQuery();
}
VerifiyPrimaryKey(entity.PrimaryKey);
foreach (var foreignKey in entity.ForeignKeys)
{
VerifyForeignKey(foreignKey);
}
foreach (var index in entity.Indexes)
{
VerifyIndex(index);
}
}
