public QResult ExecuteQResult(IDbCommand command)
{
using (var transaction = new DBTransaction(this, null, true))
{
return(transaction.ExecuteQResult(command));
}
}
public override async Task DeleteLOB(uint oid, DBTransaction transaction)
{
var command = (SqlCommand)transaction.AddCommand($"delete from db_lob where oid = @oid");
command.Parameters.AddWithValue($"@oid", (long)oid);
await transaction.ExecuteQueryAsync(command);
}
public override async Task <bool> SaveItem(DBItem item, DBTransaction transaction)
{
if ((item.UpdateState & DBUpdateState.Delete) == DBUpdateState.Delete &&
item is DBLogItem logItem && FileLOBKey is DBLogColumn logColumn)
{
var lob = item.GetValue <uint?>(logColumn);
var current = logItem.BaseItem?.GetValue <uint?>(logColumn.BaseColumn);
if (lob != null && lob != current)
{
var qquery = new QQuery(this);
qquery.BuildParam(FileLOBKey, lob);
if (!Load(qquery).Any(p => p != item))
{
try
{
using (var transactionDeleteLOB = new DBTransaction(transaction.DbConnection, transaction.Caller))
{
await System.DeleteLOB(lob.Value, transactionDeleteLOB);
transactionDeleteLOB.Commit();
}
}
catch (Exception ex)
{
Helper.OnException(ex);
}
}
}
}
return(await base.SaveItem(item, transaction));
}
public async Task <uint> SetLOBBuffered(Stream value, DBTransaction transaction)
{
if (value.CanSeek)
{
value.Position = 0;
}
var count = 0;
var bufferSize = 81920;
var buffer = new byte[bufferSize];
var tempFileName = Helper.GetDocumentsFullPath(Path.GetRandomFileName(), "Temp");
try
{
using (var tempStream = new FileStream(tempFileName, FileMode.Create, FileAccess.ReadWrite))
{
while ((count = await value.ReadAsync(buffer, 0, bufferSize)) != 0)
{
tempStream.Write(buffer, 0, count);
}
}
var manager = new NpgsqlLargeObjectManager((NpgsqlConnection)transaction.Connection);
var result = await(Task <object>) manager.ImportRemoteAsync(tempFileName, 0, CancellationToken.None);
return((uint)result);
}
finally
{
File.Delete(tempFileName);
}
}
public DBItemEventArgs(DBItem item, DBTransaction transaction, IUserIdentity user)
{
Item = item;
State = item.UpdateState;
Transaction = transaction;
User = user;
}
public QResult ExecuteQResult(string query)
{
using (var transaction = new DBTransaction(this, null, true))
{
transaction.AddCommand(query);
return(transaction.ExecuteQResult());
}
}
public List <Dictionary <string, object> > ExecuteListDictionary(string query)
{
using (var transaction = new DBTransaction(this, null, true))
{
var command = transaction.AddCommand(query);
return(transaction.ExecuteListDictionary(command));
}
}
public IEnumerable <T> Load(DBLoadParam param = DBLoadParam.None)
{
using (var transaction = new DBTransaction(Table.Connection, null, true)
{
View = this
})
{
return(table.Load(Query, param, transaction));
}
}
public override async Task <uint> SetLOB(Stream value, DBTransaction transaction)
{
var command = (MySqlCommand)transaction.AddCommand(@"insert into db_lob (lob_data) values (@lob_data);");
command.Parameters.Add("@lob_data", MySqlDbType.LongBlob).Value = await Helper.GetBytesAsync(value);
await transaction.ExecuteQueryAsync(command);
return((uint)command.LastInsertedId);
}
public TaskExecutor GetExecutor(DBItem document, DBTransaction transaction, bool autoCommit = false)
{
var param = new ExecuteArgs(document)
{
Transaction = transaction,
AutoCommit = autoCommit
};
return(GetExecutor(CreateObject(param), param));
}
public object ExecuteDBFunction(IDbCommand command, DBTransaction transaction)
{
object bufer = null;
transaction.AddCommand(command);
//UpdateCommand(command, parameters);
transaction.ExecuteQuery(command);
bufer = ((IDataParameter)command.Parameters[0]).Value;
return(bufer);
}
public async void LoadAsynch(DBLoadParam param = DBLoadParam.None)
{
using (var transaction = new DBTransaction(Table.Connection, null, true)
{
View = this
})
{
var items = await table.LoadAsync(Query, param, transaction).ConfigureAwait(false);
}
}
public async Task ExportTable(ExportProgressArgs ea)
{
try
{
DBETable table = ea.Table;
if ((mode & ExportMode.Patch) != ExportMode.Patch)
{
table.SourceTable.Clear();
table.TargetTable.Clear();
}
using (var transacton = new DBTransaction(table.SourceTable.Connection))
{
ea.Current = 0;
ea.Count = table.SourceTable.GetRowCount(transacton, table.Query);
ea.Description = null;
OnExportProgress(ea);
using (transacton.Reader = transacton.ExecuteQuery(table.SourceTable.CreateQuery(table.Query, null), DBExecuteType.Reader) as IDataReader)
{
table.SourceTable.CheckColumns(transacton);
while (transacton.Reader.Read())
{
if (ea.Cancel)
{
transacton.Cancel();
return;
}
var row = table.SourceTable.LoadItemFromReader(transacton);
var newRow = ExportRow(table, row);
await table.TargetTable.SaveItem(newRow, null);
ea.Current++;
ea.Row = newRow;
OnExportProgress(ea);
if ((mode & ExportMode.Patch) != ExportMode.Patch)
{
table.SourceTable.Clear();
table.TargetTable.Clear();
}
}
transacton.Reader.Close();
}
transacton.Commit();
}
}
catch (Exception ex)
{
ea.Exception = ex;
OnExportProgress(ea);
}
}
public object GetValue(object targe, DBTransaction transaction)
{
if (Member is PropertyInfo)
{
return(MemberInvoker.GetValue(targe));
}
else if (Member is MethodInfo)
{
return(((IIndexInvoker)MemberInvoker).GetValue(targe, new object[] { transaction }));
}
return(null);
}
public T LoadByCode(string code, DBColumn column, DBLoadParam param, DBTransaction transaction = null)
{
var row = SelectOne(column, code);
if (row == null && (param & DBLoadParam.Load) == DBLoadParam.Load)//&& !IsSynchronized
{
var command = System.CreateCommand(Schema.Connection, CreateQuery($"where a.{column.Name}={Schema.System.ParameterPrefix}{column.Name}", "a", Columns));
System.CreateParameter(command, Schema.System.ParameterPrefix + column.Name, code, column);
row = Load(command, param, transaction).FirstOrDefault();
}
return(row);
}
public IEnumerable <T> LoadByStamp(QQuery query, DBTransaction transaction = null)
{
if (items.Count == 0)
{
return(Load(query));
}
query.Columns.Clear();
query.Columns.Add(new QColumn(PrimaryKey));
query.Columns.Add(new QColumn(StampKey));
return(Load(query.ToCommand(), DBLoadParam.Synchronize, transaction));
}
public override async Task <Stream> GetLOB(uint oid, DBTransaction transaction, int bufferSize = 81920)
{
var command = (SqlCommand)transaction.AddCommand($"select oid, lob_data from db_lob where oid = @oid");
command.Parameters.AddWithValue($"@oid", (long)oid);
transaction.Reader = (IDataReader)await transaction.ExecuteQueryAsync(command, DBExecuteType.Reader, CommandBehavior.SequentialAccess);
if (await transaction.ReadAsync())
{
return(((SqlDataReader)transaction.Reader).GetStream(1));
}
throw new Exception("No Data Found!");
}
public object ExecuteQuery(string query, bool noTransaction = false, DBExecuteType type = DBExecuteType.Scalar)
{
if (string.IsNullOrEmpty(query))
{
return(null);
}
using (var transaction = new DBTransaction(this, null, noTransaction))
{
var result = transaction.ExecuteQuery(transaction.AddCommand(query), type);
transaction.Commit();
return(result);
}
}
public static void Deserialize(byte[] buffer)
{
lock (loadLock)
{
using (var transaction = new DBTransaction(DBService.Schems.DefaultSchema.Connection, null, true))
{
var stream = new MemoryStream(buffer);
using (var reader = new BinaryReader(stream))
{
while (reader.PeekChar() == 1)
{
reader.ReadChar();
var tableName = reader.ReadString();
DBTable table = DBService.Schems.ParseTable(tableName);
if (table == null)
{
continue;
}
while (reader.PeekChar() == 2)
{
reader.ReadChar();
var type = (DBLogType)reader.ReadInt32();
var user = reader.ReadInt32();
var id = Helper.ReadBinary(reader);
if (type == DBLogType.Insert)
{
table.LoadItemById(id, DBLoadParam.Load, null, transaction);
}
else if (type == DBLogType.Update)
{
var item = table.LoadItemById(id, DBLoadParam.None);
if (item != null)
{
table.ReloadItem(id, DBLoadParam.Load, transaction);
}
}
else if (type == DBLogType.Delete)
{
var item = table.LoadItemById(id, DBLoadParam.None);
if (item != null)
{
item.Table.Remove(item);
}
}
}
}
}
}
}
}
public void RemoveSubtransaction(DBTransaction subTransaction)
{
if (subTransactions != null)
{
foreach (var entry in subTransactions)
{
if (entry.Value == subTransaction)
{
subTransactions.Remove(entry.Key);
return;
}
}
}
}
public virtual T LoadFromReader(DBTransaction transaction)
{
T item = null;
if (transaction.ReaderPrimaryKey >= 0)
{
item = PrimaryKey.SelectOneFromReader <T>(transaction, transaction.ReaderPrimaryKey);
}
if (transaction.ReaderStampKey >= 0 && !transaction.Reader.IsDBNull(transaction.ReaderStampKey))
{
if (item != null && (transaction.ReaderParam & DBLoadParam.Referencing) != 0)
{
var stamp = transaction.Reader.GetDateTime(transaction.ReaderStampKey);
stamp = DateTime.SpecifyKind(stamp, DateTimeKind.Utc);
if (Nullable.Compare(item.Stamp, stamp) >= 0)
{
return(item);
}
}
else if ((transaction.ReaderParam & DBLoadParam.Synchronize) != 0)
{
var stamp = transaction.Reader.GetDateTime(transaction.ReaderStampKey);
stamp = DateTime.SpecifyKind(stamp, DateTimeKind.Utc);
if (item != null && Nullable.Compare(item.Stamp, stamp) >= 0)
{
return(item);
}
else if (transaction.ReaderColumns.Count < 4)
{
return(LoadById(transaction.Reader.GetValue(transaction.ReaderPrimaryKey)));
}
}
}
if (item == null)
{
var typeIndex = 0;
if (transaction.ReaderItemTypeKey >= 0)
{
typeIndex = transaction.Reader.IsDBNull(transaction.ReaderItemTypeKey) ? 0 : transaction.Reader.GetInt32(transaction.ReaderItemTypeKey);
}
item = (T)NewItem(transaction.ReaderState, false, typeIndex);
}
for (int i = 0; i < transaction.ReaderColumns.Count; i++)
{
var column = transaction.ReaderColumns[i];
column.LoadFromReader(transaction, item, i);
}
return(item);
}
public override async Task <uint> SetLOB(Stream value, DBTransaction transaction)
{
var command = (SqlCommand)transaction.AddCommand(@"select @oid = next value for db_lob_seq;
insert into db_lob (oid, lob_data) values (@oid, @lob_data);
select @oid;");
command.Parameters.Add("@lob_data", SqlDbType.Binary, -1).Value = value;
var oid = (long)await transaction.ExecuteQueryAsync(command, DBExecuteType.Scalar);
//command = (SqlCommand)transaction.AddCommand("select current_value from sys.sequences where name = 'db_lob_seq'");
//transaction.ExecuteQuery(command, DBExecuteType.Scalar);
return((uint)oid);
}
public override async Task <uint> SetLOB(Stream value, DBTransaction transaction)
{
var command = (SqliteCommand)transaction.AddCommand(@"insert into db_lob (lob_data) values (zeroblob($length));
select last_insert_rowid();");
command.Parameters.AddWithValue("$length", value.Length);
var oid = (long)await transaction.ExecuteQueryAsync(command);
// Open a stream to write the data
using (var blobStream = new SqliteBlob((SqliteConnection)transaction.Connection, "db_lob", "lob_data", oid))
{
await value.CopyToAsync(blobStream);
}
return((uint)oid);
}
public QResult ExecuteQueryResult(IDbCommand command)
{
using (var transaction = new DBTransaction(Schema.Connection))
{
try
{
return(ExecuteQueryResult(command, transaction));
}
catch (Exception ex)
{
Helper.OnException(ex);
transaction.Rollback();
throw ex;
}
}
}
public List <Dictionary <string, object> > ExecuteListDictionary(IDbCommand command)
{
using (var transaction = new DBTransaction(Schema.Connection))
{
try
{
return(ExecuteListDictionary(command, transaction));
}
catch (Exception ex)
{
Helper.OnException(ex);
transaction.Rollback();
throw ex;
}
}
}
public DBTable <T> ExecuteTable <T>(string tableName, string query) where T : DBItem, new()
{
var schema = new DBSchema()
{
Name = "temp", Connection = this
};
var table = new DBTable <T>(tableName)
{
Schema = schema
};
using (var transaction = new DBTransaction(this, null, true))
{
table.Load(transaction.AddCommand(query));
}
return(table);
}
public async Task Save()
{
using (var transaction = new DBTransaction(Table.Connection))
{
try
{
await Save(transaction);
transaction.Commit();
}
catch (Exception ex)
{
Helper.OnException(ex);
transaction.Rollback();
}
}
}
public async Task <Stream> GetLOBBuffered(uint oid, DBTransaction transaction)
{
var outStream = new MemoryStream();
var manager = new NpgsqlLargeObjectManager((NpgsqlConnection)transaction.Connection);
var bufferSize = 81920;
var buffer = new byte[bufferSize];
using (var lobStream = await manager.OpenReadAsync(oid, CancellationToken.None))
{
int count;
while ((count = await lobStream.ReadAsync(buffer, 0, bufferSize)) != 0)
{
outStream.Write(buffer, 0, count);
}
}
outStream.Position = 0;
return(outStream);
}
public DBTransaction GetSubTransaction(DBConnection config, bool checkSelf = true, bool noTransaction = false)
{
if (checkSelf && config == DbConnection && Reader == null)
{
return(this);
}
if (subTransactions == null)
{
subTransactions = new Dictionary <DBConnection, DBTransaction>();
}
//TODO Check several subtransaction with same config (IDBConnections leak!!!)
else if (subTransactions.TryGetValue(config, out var subTransaction) && subTransaction.Reader == null)
{
return(subTransaction);
}
return(subTransactions[config] = new DBTransaction(config, Caller, noTransaction));
//TODO Check several opened connections in sqlite config.System == DBSystem.SQLite && DbConnection.System == DBSystem.SQLite
}
public override async Task <uint> SetLOB(Stream value, DBTransaction transaction)
{
using (var blob = new OracleBlob((OracleConnection)transaction.Connection))
{
await value.CopyToAsync(blob);
var command = (OracleCommand)transaction.AddCommand(@"begin
select db_lob_seq.nextval into :oid = next from dual;
insert into db_lob (oid, lob_data) values (:oid, :lob_data);
select :oid;");
var oidParameter = command.Parameters.Add(":oid", OracleDbType.Long);
oidParameter.Direction = ParameterDirection.Output;
command.Parameters.Add(":lob_data", OracleDbType.Blob, -1).Value = blob;
await transaction.ExecuteQueryAsync(command, DBExecuteType.NoReader);
var oid = (long)oidParameter.Value;
return((uint)oid);
}
}
