public async Task <List <ProgressItem> > LoadByCorrelationIdAsync(Guid correlationId, CancellationToken cancellationToken) { var items = new List <ProgressItem>(); await ExecuteAsync(async cmd => { cmd.CommandText = await SqlLoader.GetScript("Progress.LoadByCorrelationId"); cmd.AddParameter("@CorrelationId", correlationId); using (var rdr = await cmd.ExecuteReaderAsync(cancellationToken)) { while (await rdr.ReadAsync(cancellationToken)) { var workItem = new ProgressItem { CorrelationId = rdr.Map <Guid>(nameof(ProgressItem.CorrelationId)), Id = rdr.Map <Guid>(nameof(ProgressItem.Id)), Name = rdr.Map <string>(nameof(ProgressItem.Name)), Note = rdr.Map <string>(nameof(ProgressItem.Note)), Queue = rdr.Map <string>(nameof(ProgressItem.Queue)), RecordedOn = rdr.Map <DateTime>(nameof(ProgressItem.RecordedOn)), Value = rdr.Map <int>(nameof(ProgressItem.Value)), WorkId = rdr.Map <Guid>(nameof(ProgressItem.WorkId)) }; items.Add(workItem); } } }, cancellationToken); return(items); }
private async Task <decimal> SumAmount(GetExpenseSumQuery query) { using (var sql = GetSqlConnection()) { return(Math.Round(await sql.ExecuteScalarAsync <decimal>(SqlLoader.Load(SqlScripts.GetExpenseSumInDefaultCurrency), query), 2)); } }
public async Task <IWorkProvider> CreateAsync(string[] queues, CancellationToken cancellationToken = default(CancellationToken)) { try { await SyncLock.WaitAsync(cancellationToken); if (!_initialized) { await SqlRunner.ExecuteAsync(async cmd => { var schemaSetup = await SqlLoader.GetScript("Schema.Setup"); var workSetup = await SqlLoader.GetScript("Work.Setup"); cmd.CommandText = $"{schemaSetup};\r\n{workSetup}"; await cmd.ExecuteNonQueryAsync(cancellationToken); }, _sqlServerQuidjiboConfiguration.ConnectionString, false, cancellationToken); _initialized = true; } return(new SqlWorkProvider( _loggerFactory.CreateLogger <SqlWorkProvider>(), _sqlServerQuidjiboConfiguration.ConnectionString, queues, _sqlServerQuidjiboConfiguration.LockInterval, _sqlServerQuidjiboConfiguration.BatchSize, _sqlServerQuidjiboConfiguration.DaysToKeep)); } finally { SyncLock.Release(); } }
public async Task <IProgressProvider> CreateAsync(string[] queues, CancellationToken cancellationToken = default(CancellationToken)) { if (_provider != null) { return(_provider); } try { await SyncLock.WaitAsync(cancellationToken); await SqlRunner.ExecuteAsync(async cmd => { cmd.CommandText = await SqlLoader.GetScript("Progress.Setup"); await cmd.ExecuteNonQueryAsync(cancellationToken); }, _connectionString, false, cancellationToken); _provider = new SqlProgressProvider(_connectionString); return(_provider); } finally { SyncLock.Release(); } }
public async Task <IWorkProvider> CreateAsync(string[] queues, CancellationToken cancellationToken = default(CancellationToken)) { try { await SyncLock.WaitAsync(cancellationToken); if (!_initialized) { await SqlRunner.ExecuteAsync(async cmd => { var schemaSetup = await SqlLoader.GetScript("Schema.Setup"); var workSetup = await SqlLoader.GetScript("Work.Setup"); cmd.CommandText = $"{schemaSetup};\r\n{workSetup}"; await cmd.ExecuteNonQueryAsync(cancellationToken); }, _connectionString, false, cancellationToken); _initialized = true; } return(new SqlWorkProvider(_connectionString, queues, _visibilityTimeout, _batchSize)); } finally { SyncLock.Release(); } }
public async Task <decimal> GetSum(IncomeGetSumBinding binding) { using (var context = GetMainContext()) { int targetCurrencyId = context.GetCurrencyId(binding.TargetCurrencyId, UserId); var incomeIds = context.Incomes.WhereUser(UserId) .Where(binding, context) .Select(x => x.Id) .ToList(); if (incomeIds.Count == 0) { return(0); } using (var sql = GetSqlConnection()) { var parameters = new GetIncomeSumQuery() { IncomeIds = incomeIds, TargetCurrencyId = targetCurrencyId, UserId = UserId }; return(Math.Round(await sql.ExecuteScalarAsync <decimal>(SqlLoader.Load(SqlScripts.GetIncomeSum), parameters), 2)); } } }
public async Task <Unit> Handle(UpdateCelestialObjectCommand request, CancellationToken cancellationToken) { var sqlParams = new { request.Id, request.Magnitude, request.AbsoluteMagnitude, request.Name, ObjectType = request.ObjectType.ToString(), request.Designation1, request.Designation2, request.Designation3, request.Designation4, request.Description, request.Distance, request.DistanceTolerance }; var result = await _dataRepo.ExecuteAsync( SqlLoader.GetSql(SqlResourceNames.CelestialObjects.CelestialObject_Update), sqlParams); if (result == 0) { throw new NotFoundException($"Unable to find resource with id '{request.Id}'for deletion."); } return(Unit.Value); }
public List <InvoiceAutoItem> ListLot(Guid id_document) { string str = "exec USP_INVOICE_AUTO_REST null, '{0}'"; SqlLoader <InvoiceAutoItem> sqlLoader = this.loader; object[] idDocument = new object[] { id_document }; return(sqlLoader.GetList(str, idDocument)); }
public List <InvoiceAutoItem> ListRest(long id_store) { string str = "exec USP_INVOICE_AUTO_REST {0}, null"; SqlLoader <InvoiceAutoItem> sqlLoader = this.loader; object[] idStore = new object[] { id_store }; return(sqlLoader.GetList(str, idStore)); }
public async Task <List <WorkItem> > ReceiveAsync(string worker, CancellationToken cancellationToken) { var receiveOn = DateTime.UtcNow; var deleteOn = _daysToKeep > 0 ? receiveOn.AddDays(-_daysToKeep) : receiveOn.AddHours(-1); if (_receiveSql == null) { _receiveSql = await SqlLoader.GetScript("Work.Receive"); if (_queues.Length > 0) { _receiveSql = _receiveSql.Replace("@Queue1", string.Join(",", _queues.Select((x, i) => $"@Queue{i}"))); } } var workItems = new List <WorkItem>(_batchSize); await ExecuteAsync(async cmd => { cmd.CommandText = _receiveSql; cmd.AddParameter("@Worker", worker); cmd.AddParameter("@Take", _batchSize); cmd.AddParameter("@InFlight", StatusFlags.InFlight); cmd.AddParameter("@VisibleOn", receiveOn.AddSeconds(Math.Max(_visibilityTimeout, 30))); cmd.AddParameter("@ReceiveOn", receiveOn); cmd.AddParameter("@MaxAttempts", _maxAttempts); cmd.AddParameter("@DeleteOn", deleteOn); // dynamic parameters for (var i = 0; i < _queues.Length; i++) { cmd.Parameters.AddWithValue($"@Queue{i}", _queues[i]); } using (var rdr = await cmd.ExecuteReaderAsync(cancellationToken)) { while (await rdr.ReadAsync(cancellationToken)) { var workItem = new WorkItem { Attempts = rdr.Map <int>(nameof(WorkItem.Attempts)), CorrelationId = rdr.Map <Guid>(nameof(WorkItem.CorrelationId)), ExpireOn = rdr.Map <DateTime>(nameof(WorkItem.ExpireOn)), Id = rdr.Map <Guid>(nameof(WorkItem.Id)), Name = rdr.Map <string>(nameof(WorkItem.Name)), Payload = rdr.Map <byte[]>(nameof(WorkItem.Payload)), Queue = rdr.Map <string>(nameof(WorkItem.Queue)), ScheduleId = rdr.Map <Guid?>(nameof(WorkItem.ScheduleId)) }; workItem.Token = workItem.Id.ToString(); workItems.Add(workItem); } } }, cancellationToken); return(workItems); }
public void Can_get_mapping() { var map = SqlLoader.LoadFromConnectionString(@"Data Source=(LocalDB)\v11.0;AttachDbFilename=""C:\Users\Kathleen\Documents\Visual Studio 2013\Projects\CodeFirstMetadata\SchemaTests\TestDatabase.mdf"";Integrated Security=True;Connect Timeout=30"); Assert.AreEqual(7, map.Tables.Count()); Assert.AreEqual(2, map.Views.Count()); Assert.AreEqual(4, map.Tables.First().Columns.Count()); Assert.AreEqual(5, map.Views.First().Columns.Count()); }
public async Task DeleteAsync(Guid id, CancellationToken cancellationToken = default(CancellationToken)) { await ExecuteAsync(async cmd => { cmd.CommandText = await SqlLoader.GetScript("Schedule.Delete"); cmd.AddParameter("@Id", id); await cmd.ExecuteNonQueryAsync(cancellationToken); }, cancellationToken); }
public async Task CompleteAsync(WorkItem item, CancellationToken cancellationToken) { await ExecuteAsync(async cmd => { cmd.CommandText = await SqlLoader.GetScript("Work.Complete"); cmd.AddParameter("@Id", item.Id); cmd.AddParameter("@Complete", StatusFlags.Complete); await cmd.ExecuteNonQueryAsync(cancellationToken); }, cancellationToken); }
public async Task CompleteAsync(ScheduleItem item, CancellationToken cancellationToken = default(CancellationToken)) { await ExecuteAsync(async cmd => { cmd.CommandText = await SqlLoader.GetScript("Schedule.Complete"); cmd.AddParameter("@Id", item.Id); cmd.AddParameter("@EnqueueOn", item.EnqueueOn); cmd.AddParameter("@EnqueuedOn", item.EnqueuedOn); await cmd.ExecuteNonQueryAsync(cancellationToken); }, cancellationToken); }
public async Task <bool> ExistsAsync(string name, CancellationToken cancellationToken = default(CancellationToken)) { var count = 0; await ExecuteAsync(async cmd => { cmd.CommandText = await SqlLoader.GetScript("Schedule.Exists"); cmd.AddParameter("@Name", name); count = (int)await cmd.ExecuteScalarAsync(cancellationToken); }, cancellationToken); return(count > 0); }
public async Task <IEnumerable <CelestialObjectDto> > Handle(SearchCelestialQuery request, CancellationToken cancellationToken) { var searchResult = await _dataRepo.QueryAsync <CelestialObjectEntityModel>( SqlLoader.GetSql(SqlResourceNames.CelestialObjects.CelestialObject_Search), new { searchText = $"\"{request.SearchText}*\"" }); if (searchResult == null) { return(new List <CelestialObjectDto>()); } return(searchResult.Select(c => _mapper.Map <CelestialObjectDto>(c))); }
public async Task FaultAsync(WorkItem item, CancellationToken cancellationToken) { var faultedOn = DateTime.UtcNow; await ExecuteAsync(async cmd => { cmd.CommandText = await SqlLoader.GetScript("Work.Fault"); cmd.AddParameter("@Id", item.Id); cmd.AddParameter("@VisibleOn", faultedOn.AddSeconds(Math.Max(_visibilityTimeout, 30))); cmd.AddParameter("@Faulted", StatusFlags.Faulted); await cmd.ExecuteNonQueryAsync(cancellationToken); }, cancellationToken); }
public async Task <Unit> Handle(DeleteCelestialObjectCommand request, CancellationToken cancellationToken) { var result = await _dataRepo.ExecuteAsync( SqlLoader.GetSql(sqlResourceName: SqlResourceNames.CelestialObjects.CelestialObject_Delete), new { Id = request.CelestialObjectId }); if (result == 0) { throw new NotFoundException($"Unable to find resource with id '{request.CelestialObjectId}'for deletion."); } return(Unit.Value); }
public async Task <CelestialObjectDto> Handle(GetCelestialByIdQuery request, CancellationToken cancellationToken) { var celestialObject = await _dataRepo.QueryFirstOrDefaultAsync <CelestialObjectEntityModel>( SqlLoader.GetSql(SqlResourceNames.CelestialObjects.CelestialObject_Get), new { Id = request.CelestialId }); if (celestialObject == null) { throw new NotFoundException($"No celestial object found for Id: {request.CelestialId}"); } return(_mapper.Map <CelestialObjectDto>(celestialObject)); }
public IEnumerable <KeyValuePair <int, int> > SumVolumeByDayOfWeek(ConsumationGetBinding binding) { using (var sqlConnection = GetSqlConnection()) { return(sqlConnection.Query <KeyValuePair <int, int> >(SqlLoader.Load(SqlScripts.GetConsumationSumByDayOfWeek), new { binding.From, binding.To, UserId = UserId }) .Select(x => new KeyValuePair <int, int>(x.Key == 1 ? 6 : x.Key - 2, x.Value)) .OrderBy(x => x.Key)); } }
public async Task <DateTime> RenewAsync(WorkItem item, CancellationToken cancellationToken) { var lockExpireOn = (item.VisibleOn ?? DateTime.UtcNow).AddSeconds(Math.Max(_visibilityTimeout, 30)); await ExecuteAsync(async cmd => { cmd.CommandText = await SqlLoader.GetScript("Work.Renew"); cmd.AddParameter("@Id", item.Id); cmd.AddParameter("@VisibleOn", lockExpireOn); await cmd.ExecuteNonQueryAsync(cancellationToken); }, cancellationToken); return(lockExpireOn); }
public List <InvoiceAutoItem> ListRest(long id_store, long id_store_to) { var logger = new SimpleLogger(); /*2019 расчет перемещения товара*/ string str = "exec USP_INVOICE_AUTO_REST_2 {0}, null, {1}"; //str = str.Replace("{0}", id_store.ToString()); //str = str.Replace("{1}", id_store_to.ToString()); logger.Trace("Запускаем выборку по складу " + str); SqlLoader <InvoiceAutoItem> sqlLoader = this.loader; object[] idStore = new object[] { id_store, id_store_to }; return(sqlLoader.GetList(str, idStore)); //return sqlLoader.GetList(str); }
public async Task CreateAsync(ScheduleItem item, CancellationToken cancellationToken = default(CancellationToken)) { await ExecuteAsync(async cmd => { cmd.CommandText = await SqlLoader.GetScript("Schedule.Create"); cmd.AddParameter("@Id", item.Id); cmd.AddParameter("@Name", item.Name); cmd.AddParameter("@Queue", item.Queue); cmd.AddParameter("@CronExpression", item.CronExpression); cmd.AddParameter("@CreatedOn", item.CreatedOn); cmd.AddParameter("@EnqueuedOn", item.EnqueuedOn); cmd.AddParameter("@EnqueueOn", item.EnqueueOn); cmd.AddParameter("@VisibleOn", item.VisibleOn); cmd.AddParameter("@Payload", item.Payload); await cmd.ExecuteNonQueryAsync(cancellationToken); }, cancellationToken); }
public int GetTimeSum(WebTimeGetBinding binding) { using (var db = GetSqlConnection()) { var parameters = new { DeviceValueId = binding.DeviceId, DomainValueId = binding.DomainId, WebValueId = binding.WebId, IsSecured = binding.IsSecured, From = binding.From, To = binding.To, UserId = UserId }; return(db.ExecuteScalar <int>(SqlLoader.Load(SqlScripts.GetWebTimeTotal), parameters)); } }
public async Task ReportAsync(ProgressItem item, CancellationToken cancellationToken) { await ExecuteAsync(async cmd => { cmd.CommandText = await SqlLoader.GetScript("Progress.Create"); cmd.AddParameter("@Id", item.Id); cmd.AddParameter("@WorkId", item.WorkId); cmd.AddParameter("@CorrelationId", item.CorrelationId); cmd.AddParameter("@Name", item.Name); cmd.AddParameter("@Queue", item.Queue); cmd.AddParameter("@RecordedOn", item.RecordedOn); cmd.AddParameter("@Value", item.Value); cmd.AddParameter("@Note", item.Note); await cmd.ExecuteNonQueryAsync(cancellationToken); }, cancellationToken); await Task.CompletedTask; }
public async Task <ScheduleItem> LoadByNameAsync(string name, CancellationToken cancellationToken = default(CancellationToken)) { var item = default(ScheduleItem); await ExecuteAsync(async cmd => { cmd.CommandText = await SqlLoader.GetScript("Schedule.LoadByName"); cmd.AddParameter("@Name", name); using (var rdr = await cmd.ExecuteReaderAsync(cancellationToken)) { if (await rdr.ReadAsync(cancellationToken)) { item = MapScheduleItem(rdr); } } }, cancellationToken); return(item); }
public IEnumerable <WebTime> GetTimeSummed(WebTimeGetPagedBinding binding) { using (var db = GetSqlConnection()) { var parameters = new { DeviceValueId = binding.DeviceId, From = binding.From, Page = binding.Page, PageSize = binding.PageSize, To = binding.To, UserId = UserId }; var command = new CommandDefinition(SqlLoader.Load(SqlScripts.GetWebTimeSum), parameters); return(db.Query <WebTime>(command)); } }
public IEnumerable <TimeByDay> GetTimeTotalByDay(WebTimeGetBinding binding) { using (var db = GetSqlConnection()) { var parameters = new { DeviceValueId = binding.DeviceId, DomainValueId = binding.DomainId, WebValueId = binding.WebId, IsSecured = binding.IsSecured, From = binding.From, To = binding.To, UserId = UserId }; var command = new CommandDefinition(SqlLoader.Load(SqlScripts.GetWebTimeTotalByDay), parameters); return(db.Query <TimeByDay>(command)); } }
public async Task <IScheduleProvider> CreateAsync(string[] queues, CancellationToken cancellationToken = default(CancellationToken)) { try { await SyncLock.WaitAsync(cancellationToken); await SqlRunner.ExecuteAsync(async cmd => { var schemaSetup = await SqlLoader.GetScript("Schema.Setup"); var scheduleSetup = await SqlLoader.GetScript("Schedule.Setup"); cmd.CommandText = $"{schemaSetup};\r\n{scheduleSetup}"; await cmd.ExecuteNonQueryAsync(cancellationToken); }, _connectionString, false, cancellationToken); return(await Task.FromResult <IScheduleProvider>(new SqlScheduleProvider(_connectionString, queues))); } finally { SyncLock.Release(); } }
public IEnumerable <GroupedByMonth <int> > GetTimeTotalByMonth(WebTimeGetBinding binding) { using (var db = GetSqlConnection()) { var parameters = new { DeviceValueId = binding.DeviceId, DomainValueId = binding.DomainId, WebValueId = binding.WebId, IsSecured = binding.IsSecured, From = binding.From, To = binding.To, UserId = UserId }; var command = new CommandDefinition(SqlLoader.Load(SqlScripts.GetWebTimeTotalByMonth), parameters); return(db.Query <GetWebTimeTotalByMonth>(command) .Select(x => new GroupedByMonth <int>(x.Seconds, x.Year, x.Month))); } }