public object ExecuteScalar(string connKey, CommandType cmdType, string cmdText, int timeout, params DbParameter[] commandParameters)
{
string connectionString;
GetConnectionInfo(connKey, out connectionString);
DbCommand cmd = _dbFactory.CreateCommand();
ConnectionWrapper <DbConnection> wrapper = null;
try
{
wrapper = GetOpenConnection(connectionString, _dbFactory);
PrepareCommand(cmd, wrapper.Connection, null, cmdType, cmdText, timeout, commandParameters);
object val = cmd.ExecuteScalar();
cmd.Parameters.Clear();
return(val);
}
catch (System.Exception ex)
{
throw new DataAccessException(ex, connectionString, cmdText, commandParameters);
}
finally
{
if (wrapper != null)
{
wrapper.Dispose();
}
}
}
private void InsertLog(LogEntry log, string connStr, string sql, string dbProviderName)
{
using (ConnectionWrapper <DbConnection> connection =
TransactionScopeConnections.GetOpenConnection(connStr, () => { return(CreateDbConnection(dbProviderName)); }))
{
object pro = log.ExtendedProperties == null ? (object)DBNull.Value : (object)log.ExtendedProperties.Serialize();
List <DbParameter> pList = new List <DbParameter>()
{
CreateDbParameter(dbProviderName, "@LogID", log.LogID, DbType.Guid), // 1
CreateDbParameter(dbProviderName, "@Source", log.Source, DbType.AnsiString), // 2
CreateDbParameter(dbProviderName, "@Category", log.Category, DbType.AnsiString), // 3
CreateDbParameter(dbProviderName, "@RequestUrl", log.RequestUrl, DbType.AnsiString), // 4
CreateDbParameter(dbProviderName, "@ServerName", log.ServerName, DbType.AnsiString), // 5
CreateDbParameter(dbProviderName, "@ServerTime", log.ServerTime, DbType.DateTime), // 6
CreateDbParameter(dbProviderName, "@ServerIP", log.ServerIP, DbType.AnsiString), // 7
CreateDbParameter(dbProviderName, "@ReferenceKey", log.ReferenceKey, DbType.String), // 8
CreateDbParameter(dbProviderName, "@UserHostName", log.UserHostName, DbType.String), // 9
CreateDbParameter(dbProviderName, "@UserHostAddress", log.UserHostAddress, DbType.String), // 10
CreateDbParameter(dbProviderName, "@Content", log.Content, DbType.String), // 11
CreateDbParameter(dbProviderName, "@ProcessID", log.ProcessID, DbType.Int32), // 12
CreateDbParameter(dbProviderName, "@ProcessName", log.ProcessName, DbType.String), // 13
CreateDbParameter(dbProviderName, "@ThreadID", log.ThreadID, DbType.Int32), // 14
CreateDbParameter(dbProviderName, "@ExtendedProperties", pro, DbType.String) // 15
};
using (DbCommand command = CreateDbCommand(dbProviderName, sql, connection.Connection))
{
command.Parameters.AddRange(pList.ToArray());
command.ExecuteNonQuery();
}
}
}
public static DataTable ExecuteDataTable(string connStrKey, CommandType cmdType, string cmdText, object parameter = null)
{
cmdText = ProcessDbName(cmdText);
IDbFactory dbFactory = GetDbFactory(connStrKey);
string connectionString = GetConnectionString(connStrKey);
DbParameter[] commandParameters = DataMapper.BuildDbParameters(cmdText, parameter, dbFactory.CreateParameter, dbFactory.BuildParameterName);
DbCommand cmd = dbFactory.CreateCommand();
ConnectionWrapper wrapper = null;
DataTable table = new DataTable();
try
{
wrapper = GetOpenConnection(connectionString, dbFactory);
PrepareCommand(cmd, wrapper.Connection, null, cmdType, cmdText, commandParameters);
DbDataAdapter sda = dbFactory.CreateDataAdapter();
sda.SelectCommand = cmd;
sda.Fill(table);
cmd.Parameters.Clear();
}
catch (Exception ex)
{
throw new DaoSqlException(ex, connectionString, cmdText, commandParameters);
}
finally
{
if (wrapper != null)
{
wrapper.Dispose();
}
}
return(table);
}
public void InsertBenchmark (int warmupLength, int numInsertions, Func<IJobQueue> jobQueueFactory, Func<ConnectionWrapper, Query, int, IEnumerator<object>> insertTask) {
DoQuery("CREATE TABLE Test (A INTEGER NOT NULL, B INTEGER NOT NULL)");
var jobQueue = jobQueueFactory();
var wjq = jobQueue as WindowsMessageJobQueue;
using (var scheduler = new TaskScheduler(() => jobQueue))
using (var cw = new ConnectionWrapper(scheduler, Connection))
using (var query = cw.BuildQuery("INSERT INTO Test (A, B) VALUES (?, ?)")) {
scheduler.WaitFor(insertTask(cw, query, warmupLength));
DoQuery("DELETE FROM Test");
var e = insertTask(cw, query, numInsertions);
long timeStart = Time.Ticks;
scheduler.WaitFor(e);
long elapsed = Time.Ticks - timeStart;
var elapsedSeconds = (decimal)elapsed / Time.SecondInTicks;
Console.WriteLine(
"Inserted {0} row(s) in {1:00.000} second(s) at ~{2:000000.00} rows/sec.",
numInsertions, elapsedSeconds, numInsertions / elapsedSeconds
);
}
}
public void TestConnectionWrapperCheckValuesAre()
{
var wrapper = new ConnectionWrapper();
Mock <IConfiguration> mockAStorage = new Mock <IConfiguration>();
APIConnectionInformation info = new APIConnectionInformation();
info.Token = "Token";
info.ApiUri = "https://apiuri.com";
info.StorageUri = "https://storageuri.com";
info.AccountEmail = "*****@*****.**";
info.ForcePathStyle = true;
info.StorageUnsafeSsl = true;
mockAStorage.Setup(foo => foo.ApiConnection).Returns(info);
IConfiguration config = mockAStorage.Object;
QarnotSDK.Connection connection = wrapper.CreateConnection(config);
Assert.AreEqual("Token", connection.Token);
Assert.AreEqual(new Uri("https://apiuri.com/"), connection.Uri);
Assert.AreEqual(new Uri("https://storageuri.com/"), connection.StorageUri);
Assert.AreEqual(true, connection.ForceStoragePathStyle);
Assert.AreEqual("*****@*****.**", connection.StorageAccessKey);
Assert.IsInstanceOf <UnsafeS3HttpClientFactory>(connection.S3HttpClientFactory);
}
private async Task CloseVersionsForCrossProjectMovedArtifacts(IEnumerable <int> artifactIds, int revisionId, IDbTransaction transaction)
{
var aids = artifactIds.ToArray();
if (aids.IsEmpty())
{
return;
}
var parameters = new DynamicParameters();
parameters.Add("@revisionId", revisionId);
parameters.Add("@artifactIds", SqlConnectionWrapper.ToDataTable(aids));
if (transaction == null)
{
await ConnectionWrapper.ExecuteAsync
(
"CloseAllPropertyVersions",
parameters,
commandType : CommandType.StoredProcedure);
return;
}
await transaction.Connection.ExecuteAsync
(
"CloseAllPropertyVersions",
parameters,
transaction,
commandType : CommandType.StoredProcedure);
}
public async Task <Dictionary <int, bool> > DoItemsContainReadonlyReuse(IEnumerable <int> itemIds, IDbTransaction transaction = null)
{
var parameters = new DynamicParameters();
parameters.Add("@itemIds", SqlConnectionWrapper.ToDataTable(itemIds));
if (transaction == null)
{
return
((
await ConnectionWrapper.QueryAsync <dynamic>
(
"DoItemsContainReadonlyReuse",
parameters,
commandType: CommandType.StoredProcedure)).ToDictionary(a => (int)a.ItemId, b => (bool)b.IsReadOnlyReuse));
}
return
((
await transaction.Connection.QueryAsync <dynamic>
(
"DoItemsContainReadonlyReuse",
parameters,
transaction,
commandType: CommandType.StoredProcedure)).ToDictionary(a => (int)a.ItemId, b => (bool)b.IsReadOnlyReuse));
}
public async Task <int> GetTransactionStatus(long transactionId)
{
var param = new DynamicParameters();
param.Add("@transactionId", transactionId);
return(await ConnectionWrapper.ExecuteScalarAsync <int>("[dbo].[GetTransactionStatus]", param, commandType : CommandType.StoredProcedure));
}
private async Task <IEnumerable <int> > AddHistoryInternal(int revisionId, ISet <int> artifactIds, IDbTransaction transaction)
{
if (artifactIds.Count == 0)
{
return(await Task.FromResult(new[] { 0 }));
}
var parameters = new DynamicParameters();
parameters.Add("@revisionId", revisionId);
var artifactIdsTable = SqlConnectionWrapper.ToDataTable(artifactIds);
parameters.Add("@artifactIds", artifactIdsTable);
if (transaction == null)
{
return(await ConnectionWrapper.QueryAsync <int>
(
"AddHistory",
parameters,
commandType : CommandType.StoredProcedure));
}
return(await transaction.Connection.QueryAsync <int>
(
"AddHistory",
parameters,
transaction,
commandType : CommandType.StoredProcedure));
}
public static int ExecuteNonQuery(string databaseName, CommandType cmdType, string cmdText, int timeout, params DbParameter[] commandParameters)
{
IDbFactory dbFactory;
string connectionString;
GetConnectionInfo(databaseName, out connectionString, out dbFactory);
DbCommand cmd = dbFactory.CreateCommand();
ConnectionWrapper <DbConnection> wrapper = null;
try
{
wrapper = GetOpenConnection(connectionString, dbFactory);
PrepareCommand(cmd, wrapper.Connection, null, cmdType, cmdText, timeout, commandParameters);
int val = cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
return(val);
}
catch (Exception ex)
{
throw new DataAccessException(ex, connectionString, cmdText, commandParameters);
}
finally
{
if (wrapper != null)
{
wrapper.Dispose();
}
}
}
public static DataTable ExecuteDataTable(string databaseName, CommandType cmdType, string cmdText, int timeout, params DbParameter[] commandParameters)
{
IDbFactory dbFactory;
string connectionString;
GetConnectionInfo(databaseName, out connectionString, out dbFactory);
DbCommand cmd = dbFactory.CreateCommand();
DataTable table = new DataTable();
ConnectionWrapper <DbConnection> wrapper = null;
try
{
wrapper = GetOpenConnection(connectionString, dbFactory);
PrepareCommand(cmd, wrapper.Connection, null, cmdType, cmdText, timeout, commandParameters);
DbDataAdapter sda = dbFactory.CreateDataAdapter();
sda.SelectCommand = cmd;
sda.Fill(table);
cmd.Parameters.Clear();
}
catch (Exception ex)
{
throw new DataAccessException(ex, connectionString, cmdText, commandParameters);
}
finally
{
if (wrapper != null)
{
wrapper.Dispose();
}
}
return(table);
}
public async Task <int> RepopulateSearchItems(IEnumerable <int> artifactIds)
{
var param = new DynamicParameters();
param.Add("@artifactIds", SqlConnectionWrapper.ToDataTable(artifactIds));
return(await ConnectionWrapper.ExecuteAsync("[dbo].[RepopulateSearchItems]", param, commandType : CommandType.StoredProcedure));
}
public void TestClonePipelining()
{
DoQuery("DROP TABLE IF EXISTS Test");
DoQuery("CREATE TABLE Test (value int)");
for (int i = 0; i < 10; i++)
DoQuery(String.Format("INSERT INTO Test (value) VALUES ({0})", i));
using (var scheduler = new TaskScheduler())
using (var qm = new ConnectionWrapper(scheduler, Connection)) {
var q = qm.BuildQuery("SELECT * FROM Test");
var iter = q.Execute();
var iterF = scheduler.Start(iter.Fetch());
var fClone = qm.Clone();
Assert.IsFalse(fClone.Completed);
iter.Dispose();
iterF.Dispose();
scheduler.WaitFor(fClone);
using (var dupe = fClone.Result) {
q = dupe.BuildQuery("SELECT COUNT(value) FROM Test WHERE value = ?");
var f = q.ExecuteScalar(5);
var result = scheduler.WaitFor(f);
Assert.AreEqual(result, 1);
}
}
}
private static int RunDatabaseCommands(RunDatabaseCommandsOptions options)
{
try
{
var postgresOptions = CreatePostgresOptions(options);
if (options.NoDatabase)
{
postgresOptions.PostgresDatabase = "";
}
var sql = string.Join(";", options.Commands);
Log.Information("Running {Sql}", sql);
using (var connection = new ConnectionWrapper(postgresOptions.ConnectionString))
using (var cmd = connection.Command(sql))
{
cmd.Command.ExecuteNonQuery();
}
Log.Information("Done.");
return(0);
}
catch (Exception e)
{
Log.Error(e, "Failed to run database commands");
return(1);
}
}
public virtual IDataReader ExecuteReader(DbCommand command)
{
ConnectionWrapper wrapper = GetOpenConnection(false);
try
{
//
// JS-L: I moved the PrepareCommand inside the try because it can fail.
//
PrepareCommand(command, wrapper.Connection);
//
// If there is a current transaction, we'll be using a shared connection, so we don't
// want to close the connection when we're done with the reader.
//
if (Transaction.Current != null)
{
return(DoExecuteReader(command, CommandBehavior.Default));
}
else
{
return(DoExecuteReader(command, CommandBehavior.CloseConnection));
}
}
catch
{
wrapper.Connection.Close();
throw;
}
}
public SortedDictionary <int, decimal> CheckLoanIntegrity()
{
var res = new SortedDictionary <int, decimal>();
ConnectionWrapper cw = m_oDB.GetPersistent();
IEnumerable <SafeReader> loansTbl = m_oDB.ExecuteEnumerable(cw, "SELECT Id, LoanAmount FROM Loan");
foreach (SafeReader sr in loansTbl)
{
int loanId = sr[0];
int loanAmount = sr[1];
decimal paidSoFar = m_oDB.ExecuteScalar <decimal>(cw, string.Format("SELECT CASE WHEN sum(Amount) IS NULL THEN 0 ELSE sum(Amount) END FROM LoanTransaction WHERE LoanId={0} AND Status='Done' AND Type = 'PaypointTransaction'", loanId));
decimal scheduledToPay = m_oDB.ExecuteScalar <decimal>(cw, string.Format("SELECT CASE WHEN sum(AmountDue) IS NULL THEN 0 ELSE sum(AmountDue) END FROM LoanSchedule WHERE LoanId={0} AND (Status='StillToPay' OR Status='Late')", loanId));
decimal simpleDiff = loanAmount - paidSoFar - scheduledToPay;
if (simpleDiff > 0)
{
Info("Alert!!! Loan:{0} has an error. LoanAmount:{1}, paidSoFar:{2} ScheduledToPay:{3}", loanId, loanAmount, paidSoFar, scheduledToPay);
}
res.Add(loanId, simpleDiff);
} // for each
cw.Close();
return(res);
} // CheckLoanIntegrity
public async Task <ChatInfo> ReadAsync(object parameters, CancellationToken token)
{
using (ConnectionWrapper connectionWrapper = await connectionsFactory.GetConnectionAsync(token))
{
if (parameters is ChatInfoRequest request)
{
DbCommand additionalCommand = connectionWrapper.Connection.CreateCommand();
additionalCommand.CommandType = System.Data.CommandType.Text;
additionalCommand.CommandText = "select last_message_id from chats where id=@_chat_id";
additionalCommand.Parameters.Add(new NpgsqlParameter("_chat_id", NpgsqlTypes.NpgsqlDbType.Bigint));
additionalCommand.Parameters["_chat_id"].Value = request.Id;
DbDataReader reader = await additionalCommand.ExecuteReaderAsync(token);
while (await reader.ReadAsync(token))
{
long offset = 1;
if (!await reader.IsDBNullAsync(0))
{
offset = reader.GetInt64(0);
}
return(new ChatInfo()
{
Id = request.Id, Offset = offset
});
}
}
throw new Exception("Reading failed");
}
}
public int UpdateDataSet(DataSet dataSet, string tableName, DbCommand insertCommand, DbCommand updateCommand, DbCommand deleteCommand, UpdateBehavior updateBehavior, int?updateBatchSize)
{
using (ConnectionWrapper wrapper = this.GetOpenConnection())
{
if ((updateBehavior == UpdateBehavior.Transactional) && (Transaction.Current == null))
{
DbTransaction transaction = this.BeginTransaction(wrapper.Connection);
try
{
int num = this.UpdateDataSet(dataSet, tableName, insertCommand, updateCommand, deleteCommand, transaction, updateBatchSize);
this.CommitTransaction(transaction);
return(num);
}
catch
{
this.RollbackTransaction(transaction);
throw;
}
}
if (insertCommand != null)
{
PrepareCommand(insertCommand, wrapper.Connection);
}
if (updateCommand != null)
{
PrepareCommand(updateCommand, wrapper.Connection);
}
if (deleteCommand != null)
{
PrepareCommand(deleteCommand, wrapper.Connection);
}
return(this.DoUpdateDataSet(updateBehavior, dataSet, tableName, insertCommand, updateCommand, deleteCommand, updateBatchSize));
}
}
private async Task action(CancellationToken token)
{
while (!token.IsCancellationRequested)
{
while (!token.IsCancellationRequested)
{
using (ConnectionWrapper connection = await connectionPoolManager.GetConnectionAsync(token))
{
try
{
NpgsqlCommand mainCommand = connection.Connection.CreateCommand();
mainCommand.CommandType = System.Data.CommandType.StoredProcedure;
mainCommand.CommandText = "ban";
bool continuation = true;
while (continuation)
{
using NpgsqlDataReader reader = await mainCommand.ExecuteReaderAsync(token);
while (await reader.ReadAsync(token))
{
continuation = reader.GetBoolean(0);
}
}
}
catch (Exception)
{
}
await Task.Delay((int)settings.StartWritingInterval, token);
}
}
}
}
private async Task CloseAttachmentVersions(HashSet <int> closeVersionIds, int revisionId, bool keepLatest, IDbTransaction transaction)
{
if (closeVersionIds.Count == 0)
{
return;
}
var parameters = new DynamicParameters();
parameters.Add("@revisionId", revisionId);
parameters.Add("@versionIds", SqlConnectionWrapper.ToDataTable(closeVersionIds));
parameters.Add("@keepLatest", keepLatest);
if (transaction == null)
{
await ConnectionWrapper.ExecuteAsync
(
"CloseAttachmentVersions",
parameters,
commandType : CommandType.StoredProcedure);
}
else
{
await transaction.Connection.ExecuteAsync
(
"CloseAttachmentVersions",
parameters,
transaction,
commandType : CommandType.StoredProcedure);
}
// Log.Assert(updatedRowsCount == closeVersionIds.Count, "Publish: Some attachment versions are not closed");
}
public BaseApiMessagingClient(ConnectionWrapper connectionWrapper)
{
this.connection = connectionWrapper.GetConnection();
this.channel = connection.CreateModel();
this.channel.ExchangeDeclare("API", ExchangeType.Direct);
}
private async Task <ICollection <DraftAndLatestAttachment> > GetDraftAndLatestAttachments(ISet <int> artifactIds, int userId, IDbTransaction transaction)
{
var parameters = new DynamicParameters();
parameters.Add("@userId", userId);
parameters.Add("@artifactIds", SqlConnectionWrapper.ToDataTable(artifactIds));
if (transaction == null)
{
return
((
await ConnectionWrapper.QueryAsync <DraftAndLatestAttachment>
(
"GetDraftAndLatestAttachmentVersions",
parameters,
commandType: CommandType.StoredProcedure)).ToList());
}
return
((
await transaction.Connection.QueryAsync <DraftAndLatestAttachment>
(
"GetDraftAndLatestAttachmentVersions",
parameters,
transaction,
commandType: CommandType.StoredProcedure)).ToList());
}
// загрузка списка точек осуществляется из базы АИС УРТ
private void initializeSalePoints()
{
// загрузка информации из XML
var salePointsXml = XmlHelper.Deserialize <SalePointList>(Path.Combine(Application.StartupPath, "SalePointIps.xml"));
// загрузка информации из БД
List <SalePointRecordSql> salePointsSql;
List <Region> regions;
List <Person> persons;
List <Contact> contacts;
using (var connAisurt = new SqlConnection(ConnectionWrapper.CreateLoginPassword(
"192.168.1.202", "AISURT", "BlankPublishAgent", "BlankPublishAgent").ConnectionString))
{
connAisurt.Open();
salePointsSql = loadList <SalePointRecordSql>(
"select SubjectID ID, SubjectCode Code, SubjectName Name, AddressText Address, RegionCode Region from ViewSalePoint", connAisurt);
regions = loadList <Region>(
"select cast(Code as bigint) ID, Name from RoleRegion", connAisurt);
persons = loadList <Person>(
"select SubjectID, FName, IName, OName, Position from TrustPerson", connAisurt);
contacts = loadList <Contact>(
"select SubjectID, Phone, MobilePhone, FAX from ContactInformation", connAisurt);
}
// сведение всех данных воедино
foreach (var xml in salePointsXml.SalePoints)
{
salePoints.Add(SalePoint.LoadInfo(xml, salePointsSql, regions, persons, contacts, salePoints.FindAll(sp => sp.Code == xml.Code)));
}
}
private async Task <WorkflowState> ChangeStateForArtifactInternal(int userId, int artifactId, int desiredStateId, IDbTransaction transaction = null)
{
var parameters = new DynamicParameters();
parameters.Add("@userId", userId);
parameters.Add("@artifactId", artifactId);
parameters.Add("@desiredStateId", desiredStateId);
parameters.Add("@result");
IEnumerable <SqlWorkFlowState> result;
if (transaction == null)
{
result = await ConnectionWrapper.QueryAsync <SqlWorkFlowState>
(
"ChangeStateForArtifact",
parameters,
commandType : CommandType.StoredProcedure);
}
else
{
result = await transaction.Connection.QueryAsync <SqlWorkFlowState>
(
"ChangeStateForArtifact",
parameters,
transaction,
commandType : CommandType.StoredProcedure);
}
return(ToWorkflowStates(result).FirstOrDefault());
}
private async Task ClosePropertyVersions(ISet <int> closeVersionIds, int revisionId, IDbTransaction transaction)
{
if (closeVersionIds.Count == 0)
{
return;
}
var parameters = new DynamicParameters();
parameters.Add("@revisionId", revisionId);
parameters.Add("@versionIds", SqlConnectionWrapper.ToDataTable(closeVersionIds));
if (transaction == null)
{
await ConnectionWrapper.ExecuteAsync
(
CloseVersionsStoredProcedureName,
parameters,
commandType : CommandType.StoredProcedure);
return;
}
await transaction.Connection.ExecuteAsync
(
CloseVersionsStoredProcedureName,
parameters,
transaction,
commandType : CommandType.StoredProcedure);
// TODO: Fix this assert
// Log.Assert(updatedRowsCount == closeVersionIds.Count, "Publish: Some property versions are not closed");
}
private async Task <WorkflowTriggersContainer> GetWorkflowEventTriggersForNewArtifactEventInternal(int userId,
IEnumerable <int> artifactIds,
int revisionId,
bool addDrafts)
{
var param = new DynamicParameters();
param.Add("@userId", userId);
var artifactIdsTable = SqlConnectionWrapper.ToDataTable(artifactIds);
param.Add("@artifactIds", artifactIdsTable);
param.Add("@revisionId", revisionId);
param.Add("@addDrafts", addDrafts);
var newArtifactEvents = (await
ConnectionWrapper.QueryAsync <SqlWorkflowNewArtifactEvent>("GetWorkflowEventTriggersForNewArtifact",
param,
commandType: CommandType.StoredProcedure)).ToList();
var eventTriggers = new WorkflowEventTriggers();
newArtifactEvents.Where(n => n != null).ForEach(n =>
{
eventTriggers.AddRange(ToWorkflowTriggers(SerializationHelper.FromXml <XmlWorkflowEventTriggers>(n.Triggers), userId));
});
return(GetWorkflowTriggersContainer(eventTriggers));
}
private async Task <int?> CreateNewRevisionInternal(int userId, string comment, IDbTransaction transaction)
{
var parameters = new DynamicParameters();
parameters.Add("@userId", userId);
parameters.Add("@comment", comment);
if (transaction == null)
{
return
((
await ConnectionWrapper.QueryAsync <int?>
(
"CreateRevision",
parameters,
commandType: CommandType.StoredProcedure)).FirstOrDefault());
}
return
((
await transaction.Connection.QueryAsync <int?>
(
"CreateRevision",
parameters,
transaction,
commandType: CommandType.StoredProcedure)).FirstOrDefault());
}
public TECInterlockConnection(Guid guid, IEnumerable <TECConnectionType> connectionTypes) : base(guid)
{
this.connection = new ConnectionWrapper(guid, new TECHardwiredProtocol(connectionTypes));
this.ConnectionTypes = new ObservableCollection <TECConnectionType>(connectionTypes);
ConnectionTypes.CollectionChanged += connectionTypesCollectionChanged;
subscribeToConnection();
}
public void TestConnectionWrapperStoredNull()
{
var wrapper = new ConnectionWrapper();
var ex = Assert.Throws <System.ArgumentNullException>(() => wrapper.CreateConnection(null));
Assert.IsNotNull(ex);
}
} // LoadChildrenFromXml
protected virtual bool SelfSave(AConnection oDB, ConnectionWrapper oPersistent)
{
try {
oDB.ExecuteNonQuery(
oPersistent,
DBSaveProcName,
CommandSpecies.StoredProcedure,
oDB.CreateTableParameter(
this.GetType(),
"@Tbl",
new List <AExperianLtdDataRow> {
this
},
TypeUtils.GetConvertorToObjectArray(this.GetType()),
GetDBColumnTypes()
)
);
return(true);
}
catch (Exception e) {
Log.Warn(e, "Failed to save {0} to DB.", this.GetType().Name);
return(false);
} // try
} // SelfSave
public static object ExecuteScalar(string connStrKey, CommandType cmdType, string cmdText, object parameter = null)
{
cmdText = ProcessDbName(cmdText);
IDbFactory dbFactory = GetDbFactory(connStrKey);
string connectionString = GetConnectionString(connStrKey);
DbParameter[] commandParameters = DataMapper.BuildDbParameters(cmdText, parameter, dbFactory.CreateParameter, dbFactory.BuildParameterName);
DbCommand cmd = dbFactory.CreateCommand();
ConnectionWrapper wrapper = null;
try
{
wrapper = GetOpenConnection(connectionString, dbFactory);
PrepareCommand(cmd, wrapper.Connection, null, cmdType, cmdText, commandParameters);
object val = cmd.ExecuteScalar();
cmd.Parameters.Clear();
return(val);
}
catch (Exception ex)
{
throw new DaoSqlException(ex, connectionString, cmdText, commandParameters);
}
finally
{
if (wrapper != null)
{
wrapper.Dispose();
}
}
}
public DataSet ExecuteDataSet(string connKey, CommandType cmdType, string cmdText, int timeout, params DbParameter[] commandParameters)
{
string connectionString;
GetConnectionInfo(connKey, out connectionString);
DbCommand cmd = _dbFactory.CreateCommand();
DataSet ds = new DataSet();
ConnectionWrapper <DbConnection> wrapper = null;
try
{
wrapper = GetOpenConnection(connectionString, _dbFactory);
PrepareCommand(cmd, wrapper.Connection, null, cmdType, cmdText, timeout, commandParameters);
DbDataAdapter sda = _dbFactory.CreateDataAdapter();
sda.SelectCommand = cmd;
sda.Fill(ds);
cmd.Parameters.Clear();
}
catch (System.Exception ex)
{
throw new DataAccessException(ex, connectionString, cmdText, commandParameters);
}
finally
{
if (wrapper != null)
{
wrapper.Dispose();
}
}
return(ds);
}
static IEnumerator<object> TransactionalSequentialInsertTask (ConnectionWrapper cw, Query query, int numInsertions) {
using (var xact = cw.CreateTransaction()) {
yield return xact;
for (int i = 0; i < numInsertions; i++)
yield return query.ExecuteNonQuery(i, i*2);
yield return xact.Commit();
}
}
public void TestCloneConnectionWrapper()
{
DoQuery("DROP TABLE IF EXISTS Test");
DoQuery("CREATE TABLE Test (value int)");
for (int i = 0; i < 10; i++)
DoQuery(String.Format("INSERT INTO Test (value) VALUES ({0})", i));
using (var scheduler = new TaskScheduler())
using (var qm = new ConnectionWrapper(scheduler, Connection)) {
var fClone = qm.Clone();
using (var dupe = scheduler.WaitFor(fClone)) {
var q = dupe.BuildQuery("SELECT COUNT(value) FROM Test WHERE value = ?");
var f = q.ExecuteScalar(5);
var result = scheduler.WaitFor(f);
Assert.AreEqual(result, 1);
}
}
}
public TagDatabase(TaskScheduler scheduler, string filename)
{
Scheduler = scheduler;
string connectionString = String.Format("Data Source={0}", filename);
NativeConnection = new SQLiteConnection(connectionString);
NativeConnection.Open();
Connection = new ConnectionWrapper(scheduler, NativeConnection);
CompileQueries();
_TaskMap["GetSourceFileID"] = GetSourceFileID;
#if DEBUG
scheduler.Start(
MemoizationHitRateLogger(), TaskExecutionPolicy.RunAsBackgroundTask
);
#endif
}
public void TestQueryParameters()
{
using (var scheduler = new TaskScheduler())
using (var wrapper = new ConnectionWrapper(scheduler, Connection)) {
scheduler.WaitFor(wrapper.ExecuteSQL("CREATE TEMPORARY TABLE Test (a INTEGER, b VARIANT)"));
using (var q = wrapper.BuildQuery("INSERT INTO Test (a, b) VALUES (?, ?)")) {
q.Parameters[1].DbType = DbType.Object;
Assert.AreEqual(DbType.Object, q.Parameters[1].DbType);
}
}
}
public void TestQueryPipelining()
{
DoQuery("CREATE TEMPORARY TABLE Test (value int)");
for (int i = 0; i < 100; i++)
DoQuery(String.Format("INSERT INTO Test (value) VALUES ({0})", i));
using (var scheduler = new TaskScheduler())
using (var qm = new ConnectionWrapper(scheduler, Connection)) {
var q1 = qm.BuildQuery("SELECT value FROM test");
var q2 = qm.BuildQuery("INSERT INTO test (value) VALUES (?)");
var iterator = q1.Execute();
var f1 = scheduler.Start(iterator.Fetch());
var f2 = q2.ExecuteNonQuery(200);
f1.RegisterOnComplete((f) => {
Assert.IsNull(f.Error);
Assert.AreEqual(f1, f);
Assert.AreEqual(true, f.Result);
Assert.IsTrue(f1.Completed);
Assert.IsFalse(f2.Completed);
});
f2.RegisterOnComplete((f) => {
Assert.IsNull(f.Error);
Assert.AreEqual(f2, f);
Assert.IsTrue(f1.Completed);
Assert.IsTrue(f2.Completed);
});
scheduler.WaitFor(f1);
scheduler.WaitFor(scheduler.Start(new Sleep(1.0)));
Assert.IsFalse(f2.Completed);
iterator.Dispose();
scheduler.WaitFor(f2);
}
}
public void TestTransactionAutoRollback()
{
DoQuery("CREATE TEMPORARY TABLE Test (value int)");
using (var scheduler = new TaskScheduler())
using (var qm = new ConnectionWrapper(scheduler, Connection)) {
var getNumValues = qm.BuildQuery("SELECT COUNT(value) FROM test");
var addValue = qm.BuildQuery("INSERT INTO test (value) VALUES (?)");
var f = scheduler.Start(CrashyTransactionTask(qm, addValue));
try {
scheduler.WaitFor(f);
Assert.Fail("Did not throw");
} catch (FutureException fe) {
Exception inner = fe.InnerException;
Assert.IsInstanceOfType(typeof(InvalidOperationException), inner);
}
var fgnv = getNumValues.ExecuteScalar();
long numValues = Convert.ToInt64(
scheduler.WaitFor(fgnv)
);
Assert.AreEqual(0, numValues);
}
}
public void TestTransactionPipelining()
{
DoQuery("CREATE TEMPORARY TABLE Test (value int)");
using (var scheduler = new TaskScheduler())
using (var qm = new ConnectionWrapper(scheduler, Connection)) {
var getNumValues = qm.BuildQuery("SELECT COUNT(value) FROM test");
var addValue = qm.BuildQuery("INSERT INTO test (value) VALUES (?)");
var t = qm.CreateTransaction();
var fq = addValue.ExecuteNonQuery(1);
var fr = t.Rollback();
scheduler.WaitFor(Future.WaitForAll(t.Future, fq, fr));
var fgnv = getNumValues.ExecuteScalar();
long numValues = Convert.ToInt64(
scheduler.WaitFor(fgnv)
);
Assert.AreEqual(0, numValues);
t = qm.CreateTransaction();
fq = addValue.ExecuteNonQuery(1);
var fc = t.Commit();
scheduler.WaitFor(Future.WaitForAll(t.Future, fq, fc));
fgnv = getNumValues.ExecuteScalar();
numValues = Convert.ToInt64(
scheduler.WaitFor(fgnv)
);
Assert.AreEqual(1, numValues);
}
}
public void TestGetResultAsObject()
{
DoQuery("DROP TABLE IF EXISTS Test");
DoQuery("CREATE TABLE Test (value int)");
for (int i = 0; i < 10; i++)
DoQuery(String.Format("INSERT INTO Test (value) VALUES ({0})", i));
using (var scheduler = new TaskScheduler())
using (var qm = new ConnectionWrapper(scheduler, Connection)) {
var q = qm.BuildQuery("SELECT * FROM Test");
var f = q.ExecuteArray<object>();
Assert.IsFalse(f.Completed);
var seq = scheduler.WaitFor(f);
Assert.AreEqual(
new object[] { 0, 1, 2, 3, 4, 5, 6, 7, 8, 9 }, seq
);
}
}
public void SetUp()
{
Connection = new SQLiteConnection("Data Source=:memory:");
Connection.Open();
Scheduler = new TaskScheduler();
Wrapper = new ConnectionWrapper(Scheduler, Connection);
}
public void SetConnection(ConnectionWrapper connection)
{
Connection = connection;
this.Enabled = true;
this.UseWaitCursor = false;
}
private void SearchDialog_FormClosing(object sender, FormClosingEventArgs e)
{
if (Connection != null) {
Connection.Dispose();
Connection = null;
}
if (ActiveSearch != null) {
ActiveSearch.Dispose();
ActiveSearch = null;
}
if (ActiveQueue != null) {
ActiveQueue.Dispose();
ActiveQueue = null;
}
}
public Transaction (ConnectionWrapper wrapper, bool exclusive) {
_Wrapper = wrapper;
_Future = _Wrapper.BeginTransaction(exclusive);
_Active = true;
}
public Transaction (ConnectionWrapper wrapper) {
_Wrapper = wrapper;
_Future = _Wrapper.BeginTransaction();
_Active = true;
}
public IEnumerator<object> OpenReadConnection()
{
var connectionString = NativeConnection.ConnectionString + ";Read Only=True";
var f = Future.RunInThread(
(Func<SQLiteConnection>)(() => {
var conn = new SQLiteConnection(connectionString);
conn.Open();
return conn;
})
);
yield return f;
var result = new ConnectionWrapper(
Scheduler,
f.Result as SQLiteConnection
);
yield return new Result(result);
}
public void TestDbTaskIterator()
{
DoQuery("CREATE TEMPORARY TABLE Test (value int)");
for (int i = 0; i < 100; i++)
DoQuery(String.Format("INSERT INTO Test (value) VALUES ({0})", i));
using (var scheduler = new TaskScheduler())
using (var qm = new ConnectionWrapper(scheduler, Connection)) {
var q = qm.BuildQuery("SELECT value FROM Test WHERE value = ?");
using (var iterator = q.Execute(5)) {
scheduler.WaitFor(iterator.Fetch());
using (var e = iterator.CurrentItems) {
Assert.IsTrue(e.MoveNext());
Assert.AreEqual(e.Current.GetInt32(0), 5);
}
}
}
}
private static ConnectionWrapper make_ConnectionWrapper()
{
var scheduler = new TaskScheduler();
var filename = "file.txt";
string connectionString = String.Format("Data Source={0}", filename);
var nativeConnection = new SQLiteConnection(connectionString);
////nativeConnection.Open();
var connection = new ConnectionWrapper(scheduler, nativeConnection);
return connection;
}
public void TestConnectionWrapper()
{
DoQuery("CREATE TEMPORARY TABLE Test (value int)");
for (int i = 0; i < 100; i++)
DoQuery(String.Format("INSERT INTO Test (value) VALUES ({0})", i));
using (var scheduler = new TaskScheduler())
using (var qm = new ConnectionWrapper(scheduler, Connection)) {
var q = qm.BuildQuery("SELECT COUNT(value) FROM Test WHERE value = ?");
var f = q.ExecuteScalar(5);
var result = scheduler.WaitFor(f);
Assert.AreEqual(result, 1);
q = qm.BuildQuery("SELECT @p0 - @p1");
f = q.ExecuteScalar(2, 3);
result = scheduler.WaitFor(f);
Assert.AreEqual(result, -1);
f = q.ExecuteScalar(new NamedParam { N = "p0", V = 4 }, new NamedParam { N = "p1", V = 3 });
result = scheduler.WaitFor(f);
Assert.AreEqual(result, 1);
f = q.ExecuteScalar(5, new NamedParam { N = "p1", V = 3 });
result = scheduler.WaitFor(f);
Assert.AreEqual(result, 2);
q = qm.BuildQuery("SELECT @parm1-@parm2");
f = q.ExecuteScalar(new NamedParam { N = "parm1", V = 1 }, new NamedParam { N = "parm2", V = 2 });
result = scheduler.WaitFor(f);
Assert.AreEqual(result, -1);
}
}
public void TestDisposal()
{
DoQuery("DROP TABLE IF EXISTS Test");
DoQuery("CREATE TABLE Test (value int)");
for (int i = 0; i < 10; i++)
DoQuery(String.Format("INSERT INTO Test (value) VALUES ({0})", i));
TaskEnumerator<IDataRecord> iter;
IFuture f;
using (var scheduler = new TaskScheduler())
using (var qm = new ConnectionWrapper(scheduler, Connection)) {
var q = qm.BuildQuery("SELECT * FROM Test");
var q2 = qm.BuildQuery("SELECT COUNT(*) FROM Test");
iter = q.Execute();
scheduler.Start(iter.Fetch());
f = q2.ExecuteScalar();
}
iter.Dispose();
try {
int count = (int)f.Result;
Assert.Fail("Future's result was not a ConnectionDisposedException");
} catch (FutureException fe) {
Assert.IsInstanceOfType(typeof(ConnectionDisposedException), fe.InnerException);
}
}
static IEnumerator<object> SequentialInsertTask(ConnectionWrapper cw, Query query, int numInsertions)
{
for (int i = 0; i < numInsertions; i++)
yield return query.ExecuteNonQuery(i, i * 2);
}
IEnumerator<object> CrashyTransactionTask(ConnectionWrapper cw, Query addValue)
{
using (var trans = cw.CreateTransaction()) {
yield return addValue.ExecuteNonQuery(1);
yield return addValue.ExecuteNonQuery();
yield return trans.Commit();
}
}
public static IEnumerator<object> GetDBSchemaVersion(ConnectionWrapper cw)
{
using (var q = cw.BuildQuery("PRAGMA user_version")) {
var f = q.ExecuteScalar();
yield return f;
yield return new Result(f.Result);
}
}
public static IEnumerator<object> RebuildIndexTask(bool saveOldData)
{
using (new ActiveWorker("Rebuilding index...")) {
var conn = new SQLiteConnection(String.Format("Data Source={0}", DatabasePath + "_new"));
conn.Open();
var cw = new ConnectionWrapper(Scheduler, conn);
yield return cw.ExecuteSQL("PRAGMA auto_vacuum=none");
long schemaVersion = GetEmbeddedSchemaVersion();
var fSchema = cw.ExecuteSQL(GetEmbeddedSchema());
yield return fSchema;
var trans = cw.CreateTransaction();
yield return trans;
if (saveOldData)
using (var iter = new TaskEnumerator<TagDatabase.Folder>(Database.GetFolders()))
while (!iter.Disposed) {
yield return iter.Fetch();
foreach (TagDatabase.Folder item in iter)
yield return cw.ExecuteSQL(
"INSERT INTO Folders (Folders_Path, Folders_Excluded) VALUES (?, ?)",
item.Path, item.Excluded
);
}
if (saveOldData)
using (var iter = new TaskEnumerator<TagDatabase.Filter>(Database.GetFilters()))
while (!iter.Disposed) {
yield return iter.Fetch();
foreach (TagDatabase.Filter item in iter)
yield return cw.ExecuteSQL(
"INSERT INTO Filters (Filters_Pattern) VALUES (?)",
item.Pattern
);
}
if (saveOldData)
using (var iter = Database.Connection.BuildQuery(
"SELECT Preferences_Name, Preferences_Value FROM Preferences"
).Execute())
while (!iter.Disposed) {
yield return iter.Fetch();
foreach (IDataRecord item in iter)
yield return cw.ExecuteSQL(
"INSERT INTO Preferences (Preferences_Name, Preferences_Value) VALUES (?, ?)",
item.GetValue(0), item.GetValue(1)
);
}
yield return trans.Commit();
yield return Database.Connection.Dispose();
yield return RestartTask();
}
}