public static async Task <OrderEntity> FetchOrderByOrderId(string connectionString, int id)
{
const string sql =
@"SELECT Id,
Revision,
CallOffId,
[Description],
OrderingPartyId,
OrderingPartyContactId,
OrderStatusId AS OrderStatus,
Created,
SupplierId,
SupplierContactId,
LastUpdated,
LastUpdatedBy,
CommencementDate,
LastUpdatedByName,
FundingSourceOnlyGMS,
IsDeleted,
Completed
FROM dbo.[Order]
WHERE Id = @id;";
return(await SqlRunner.QueryFirstAsync <OrderEntity>(connectionString, sql, new { id }));
}
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 void ExecuteNonQuery_NullCommandSettings_ArgumentNullExceptionThrown()
{
var runner = new SqlRunner(new MyUnitOfWork());
Action act = () => runner.ExecuteNonQuery(null);
act.Should().Throw <ArgumentNullException>(CMD_EX);
}
public void ExecuteReaderFirst_EmptyCommandText_ArgumentNullExceptionThrown(string commandText)
{
var runner = new SqlRunner(new MyUnitOfWork());
Action act = () => runner.ExecuteReaderFirst <TestModel>(new CommandSettings(commandText));
act.Should().Throw <ArgumentException>(CMD_TEXT_EX);
}
public void ExecuteReaderFirst_NullCommandSettings_ArgumentNullExceptionThrown()
{
var runner = new SqlRunner(new MyUnitOfWork());
Action act = () => runner.ExecuteReaderFirst <TestModel>(null);
act.Should().Throw <ArgumentNullException>(CMD_EX);
}
public ActionResult Index(DatabaseSetupModel databaseSetupModel)
{
var isCredentialsValid = _dbContext.IsCredentialsValid(databaseSetupModel.ConnectionString);
if (ModelState.IsValid && isCredentialsValid)
{
var files = _pathMapper.GetAvailableScripts().ToList();
try
{
var runner = new SqlRunner(ApplicationConfiguration.ConnectionString);
var results = runner.RunScripts(files);
UpdateScriptsRan(results);
return(RedirectToRoute("SetupIndex"));
}
catch (Exception exception)
{
databaseSetupModel.Message = "Unable to complete the installation, scroll below...";
databaseSetupModel.MessageCss = "error";
databaseSetupModel.CompleteException = exception.ToString();
}
}
if (!string.IsNullOrEmpty(databaseSetupModel.ConnectionString) && !isCredentialsValid)
{
databaseSetupModel.Message = "An invalid connection string was entered";
databaseSetupModel.MessageCss = "error";
}
databaseSetupModel.Scripts = _pathMapper.GetAvailableScripts().ToList().Select(s => Path.GetFileName(s.ScriptPath)).ToList();
return(View(databaseSetupModel));
}
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 <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 static async Task <OrganisationEntity> GetByNameAsync(string connectionString, string organisationName) =>
await SqlRunner.FetchSingleResultAsync <OrganisationEntity>(connectionString, $@"SELECT TOP (1)
OrganisationId,
Name
FROM Organisations
WHERE Name = @organisationName
ORDER BY Name ASC", new { organisationName });
public void ExecuteNonQuery_EmptyCommandText_ArgumentExceptionThrown(string commandText)
{
var runner = new SqlRunner(new MyUnitOfWork());
Action act = () => runner.ExecuteNonQuery(new CommandSettings(commandText));
act.Should().Throw <ArgumentException>(CMD_TEXT_EX);
}
private List <SchemaVersion> RunScripts(List <SchemaVersion> files)
{
var runner = new SqlRunner(ApplicationConfiguration.ConnectionString);
var results = runner.RunScripts(files);
return(results);
}
public static async Task <IEnumerable <SolutionEntity> > FetchAllAsync()
{
const string selectSql = @"
SELECT s.Id AS SolutionId,
c.[Name],
s.[Version],
s.Summary,
s.FullDescription,
s.Features,
s.ClientApplication,
s.Hosting,
s.ImplementationDetail,
s.RoadMap,
s.IntegrationsUrl,
s.AboutUrl,
s.ServiceLevelAgreement,
s.WorkOfPlan,
s.LastUpdated,
s.LastUpdatedBy
FROM dbo.Solution AS s
INNER JOIN dbo.CatalogueItem AS c
ON c.CatalogueItemId = s.Id;";
return(await SqlRunner.FetchAllAsync <SolutionEntity>(selectSql));
}
public static async Task <OrderEntity> FetchOrderByOrderId(string connectionString, string orderId)
{
return(await SqlRunner.QueryFirstAsync <OrderEntity>(connectionString, @"SELECT
OrderId,
Description,
OrganisationId,
OrganisationName,
OrganisationOdsCode,
OrganisationAddressId,
OrganisationContactId,
OrderStatusId,
Created,
SupplierId,
SupplierName,
SupplierAddressId,
SupplierContactId,
LastUpdated,
LastUpdatedBy,
CommencementDate,
LastUpdatedByName,
SupplierId,
SupplierName,
ServiceRecipientsViewed,
CatalogueSolutionsViewed
FROM dbo.[Order]
WHERE OrderId = @orderId;", new { orderId }));
}
public static async Task <IEnumerable <SolutionSupplierStatusEntity> > FetchAllAsync()
{
return(await SqlRunner.FetchAllAsync <SolutionSupplierStatusEntity>($@"
SELECT [Id]
,[Name]
FROM SolutionSupplierStatus")
.ConfigureAwait(false));
}
public Profesional getProfesional()
{
SqlRunner runner = new SqlRunner(Properties.Settings.Default.GD2C2013ConnectionString);
var result = runner
.Single("SELECT * FROM SIGKILL.profesional WHERE pro_usuario={0}", this.usr_id.ToString());
return(new Adapter().Transform <Profesional>(result));
}
public Afiliado getAfiliado()
{
SqlRunner runner = new SqlRunner(Properties.Settings.Default.GD2C2013ConnectionString);
var result = runner
.Single("SELECT * FROM SIGKILL.afiliado WHERE afil_usuario={0}", this.usr_id.ToString());
return(new Adapter().Transform <Afiliado>(result));
}
public static async Task <IReadOnlyCollection <DataProtectionKey> > GetFromDbAsync(string connectionString)
{
const string sql = "SELECT FriendlyName, Xml FROM dbo.DataProtectionKeys;";
IEnumerable <dynamic> keys = await SqlRunner.QueryAsync(connectionString, sql);
return(keys.Select(k => new DataProtectionKey((string)k.Xml)).ToList());
}
public static async Task <IEnumerable <ServiceRecipientEntity> > FetchAllServiceRecipients(string connectionString)
{
return(await SqlRunner.QueryAsync <ServiceRecipientEntity>(connectionString, @"SELECT
OdsCode,
Name,
OrderId
FROM dbo.ServiceRecipient;"));
}
public static async Task <IEnumerable <SupplierEntity> > FetchAllAsync()
{
return(await SqlRunner.FetchAllAsync <SupplierEntity>($@"SELECT
[Id],
[Summary],
[SupplierUrl],
[LastUpdated]
FROM Supplier").ConfigureAwait(false));
}
public static async Task <IEnumerable <FrameworkSolutionEntity> > FetchAllAsync()
{
return(await SqlRunner.FetchAllAsync <FrameworkSolutionEntity>($@"SELECT [FrameworkId]
,[SolutionId]
,[IsFoundation]
,[LastUpdated]
,[LastUpdatedBy]")
.ConfigureAwait(false));
}
public static async Task <IEnumerable <string> > FetchAllEpicIdsForSolutionAsync(string solutionId)
{
const string selectSql = @"
SELECT EpicId
FROM dbo.SolutionEpic
WHERE SolutionId = @solutionId;";
return(await SqlRunner.FetchAllAsync <string>(selectSql, new { solutionId }));
}
public static async Task SaveToDbAsync(string connectionString, IEnumerable <DataProtectionKey> keys)
{
const string sql = "INSERT INTO dbo.DataProtectionKeys(FriendlyName, [Xml]) VALUES (@FriendlyName, @Xml);";
var existingDbKeys = await GetFromDbAsync(connectionString);
var newKeys = keys.Except(existingDbKeys);
await SqlRunner.ExecuteAsync(connectionString, sql, newKeys);
}
public static async Task <IEnumerable <EpicEntity> > FetchAllAsync()
{
return(await SqlRunner.FetchAllAsync <EpicEntity>($@"SELECT [Id]
,[Name]
,[CapabilityId]
,[SourceUrl]
,[CompliancyLevelId]
,[Active]
FROM Epic").ConfigureAwait(false));
}
public static async Task <DefaultDeliveryDateEntity> Fetch(string connectionString, object parameters)
{
const string sql = @"
SELECT OrderId, CatalogueItemId, DeliveryDate
FROM dbo.DefaultDeliveryDate
WHERE OrderId = @orderId
AND CatalogueItemId = @catalogueItemId;" ;
return(await SqlRunner.QueryFirstAsync <DefaultDeliveryDateEntity>(connectionString, sql, parameters));
}
public async Task InsertAndSetCurrentForSolutionAsync()
{
await base.InsertAsync().ConfigureAwait(false);
await SqlRunner.ExecuteAsync(ConnectionStrings.GPitFuturesSetup,
$@"UPDATE Solution SET SolutionDetailId = @Id
WHERE Id = @SolutionId",
this)
.ConfigureAwait(false);
}
public static async Task <SupplierEntity> FetchById(string connectionString, string id)
{
const string sql = @"
SELECT Id,
[Name],
AddressId
FROM dbo.Supplier
WHERE Id = @id;";
return(await SqlRunner.QueryFirstAsync <SupplierEntity>(connectionString, sql, new { id }));
}
public static async Task <IEnumerable <string> > FetchAllEpicIdsForSolutionAsync(string solutionId)
{
return(await SqlRunner.FetchAllAsync <string>($@"SELECT
[EpicId]
FROM SolutionEpic
WHERE SolutionId = @solutionId;", new
{
solutionId
})
.ConfigureAwait(false));
}
public static async Task <IEnumerable <SupplierEntity> > FetchAllAsync()
{
const string selectSql = @"
SELECT Id,
Summary,
SupplierUrl,
LastUpdated
FROM dbo.Supplier;";
return(await SqlRunner.FetchAllAsync <SupplierEntity>(selectSql));
}
public static async Task <IEnumerable <string> > FetchForSolutionAsync(string solutionId)
{
const string selectSql = @"
SELECT c.CapabilityRef
FROM dbo.SolutionCapability AS s
INNER JOIN Capability AS c
ON s.CapabilityId = c.Id
WHERE s.SolutionId = @solutionId;";
return(await SqlRunner.FetchAllAsync <string>(selectSql, new { solutionId }));
}
public static async Task <IEnumerable <string> > FetchForSolutionAsync(string solutionId)
{
return(await SqlRunner.FetchAllAsync <string>($@"SELECT
[Capability].[CapabilityRef]
FROM SolutionCapability
INNER JOIN Capability ON SolutionCapability.CapabilityId = Capability.Id
WHERE SolutionId = @solutionId;", new
{
solutionId
})
.ConfigureAwait(false));
}
