/// <inheritdoc />
public async Task <short> GetCurrentDatabaseVersion()
{
var query = $@"IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = N'{TableName.DatabaseVersion.Replace("[", "").Replace("]", "")}')
BEGIN
SELECT VersionNo FROM {TableName.DatabaseVersion}
END
ELSE
BEGIN
SELECT CAST(-1 as SMALLINT)
END;";
return((short)await RepositorySqlHelper.ExcecuteNonScalarAsync(query));
}
/// <inheritdoc />
public async Task UpdateDatabaseVersionNumber(int newVersionNumber)
{
var query = $@" IF EXISTS (SELECT 1 FROM {TableName.DatabaseVersion})
BEGIN
UPDATE
{TableName.DatabaseVersion}
SET
VersionNo = {newVersionNumber}
END ELSE BEGIN
INSERT INTO {TableName.DatabaseVersion} (VersionNo)
VALUES ({newVersionNumber})
END;";
await RepositorySqlHelper.ExcecuteNonQueryAsync(query);
}
/// <inheritdoc />
public async Task SetBulk(IEnumerable <Company> companies)
{
var companiesList = companies?.ToList() ?? new List <Company>();
if (companiesList.Count == 0)
{
throw new BrArgumentException("Companies list must have at least 1 value.", ResultCode.CompaniesListIsEmpty);
}
var companiesSqlDataAsWhole = companiesList.ToTableTypeCompanies();
var companiesSqlDataBulks = companiesSqlDataAsWhole.SplitList();
foreach (var companiesSqlDataBulk in companiesSqlDataBulks)
{
if (!companiesSqlDataBulk.Any())
{
continue;
}
var query = $@" MERGE {TableName.Company} AS t --Target
USING @tableTypeRows AS s --Source
ON (t.BusinessCode = s.BusinessCode)
WHEN MATCHED THEN
UPDATE SET
t.[Name] = s.[Name],
t.BusinessCode = s.BusinessCode,
t.VatNo = s.VatNo,
t.[Status] = s.[Status],
t.FullAddress = s.FullAddress,
t.[Url] = s.[Url]
WHEN NOT MATCHED BY TARGET THEN
INSERT ([Name], BusinessCode, VatNo, [Status], FullAddress, [Url])
VALUES (s.[Name], s.BusinessCode, s.VatNo, s.[Status], s.FullAddress, s.[Url]);";
var parameters = new object[]
{
new SqlParameter("tableTypeRows", SqlDbType.Structured)
{
TypeName = TableType.Company,
Value = companiesSqlDataBulk
}
};
await RepositorySqlHelper.ExcecuteNonQueryAsync(query, parameters);
}
}
/// <summary>
/// Database intial Setup.
/// Create first required table: <see cref="TableName.DatabaseVersion"/> and populate it.
/// Also limits it with trigger so it cannot never be empty table.
/// Sets Database version to 0
/// </summary>
private async Task DatabaseInitialSetup()
{
using (var transaction = new TransactionScope(TransactionScopeAsyncFlowOption.Enabled))
{
var createTable = $@"CREATE TABLE {TableName.DatabaseVersion}(
[VersionNo] [smallint] NOT NULL
) ON [PRIMARY];";
await RepositorySqlHelper.ExcecuteNonQueryAsync(createTable);
var createTrigger = $@"
-- =============================================
-- Author: Rannes Pärn
-- Create date: {DateTime.Now:dd.MM.yyyy}
-- Description: Instead of trigger for DatabaseVersion so it can not have more than 1 row and do not allow Deletion for the value.
-- =============================================
CREATE TRIGGER [dbo].{TableName.DatabaseVersion.Replace("]", "")}_InsteadOfInsUpDel]
ON [dbo].{TableName.DatabaseVersion} INSTEAD OF INSERT, UPDATE, DELETE AS
BEGIN
SET NOCOUNT ON;
IF EXISTS (SELECT 1 FROM inserted) --INSERT OR UPDATE
BEGIN
IF NOT EXISTS (SELECT 1 FROM {TableName.DatabaseVersion})
BEGIN
INSERT INTO {TableName.DatabaseVersion} (VersionNo)
VALUES ((SELECT MAX(VersionNo) FROM inserted))
END ELSE
IF NOT EXISTS (SELECT 1 FROM {TableName.DatabaseVersion} WHERE VersionNo > (SELECT MAX(VersionNo) FROM inserted))
BEGIN
UPDATE
{TableName.DatabaseVersion}
SET
VersionNo = (SELECT MAX(VersionNo) FROM inserted)
END
END;
--IGNORE IF DELETE
END;";
await RepositorySqlHelper.ExcecuteNonQueryAsync(createTrigger);
await _databaseVersionRepository.UpdateDatabaseVersionNumber(0);
transaction.Complete();
}
}
/// <inheritdoc />
protected BaseRepository(ConnectionString connectionString, ILogger logger)
{
RepositorySqlHelper = new RepositorySqlHelper(connectionString.BusinessRegister, logger);
BusinessRegistryConnectionString = connectionString.BusinessRegister;
Logger = logger;
}
/// <summary>
/// Upgrade database to Version 1.
/// Creates Company table based of <see cref="Company"/>.
/// Adds also 3 indexes to Company table
/// </summary>
/// <returns></returns>
private async Task UpgradeToVersion1()
{
using (var transaction = new TransactionScope(TransactionScopeAsyncFlowOption.Enabled))
{
var companyTableCration = $@"CREATE TABLE {TableName.Company}
(
[Name] [nvarchar](400) NOT NULL,
[BusinessCode] [varchar](50) NOT NULL,
[VatNo] [varchar](200) NULL,
[Status] [varchar](20) NOT NULL,
[FullAddress] [nvarchar] (1024) NOT NULL,
[Url] [varchar] (200) NOT NULL,
[Added] [datetime] NOT NULL CONSTRAINT [DF_BusinessRegister.Company_Added] DEFAULT GETDATE(),
[Modified] [datetime] NULL,
CONSTRAINT [IX_BusinessRegister.Company_BusinessCode] PRIMARY KEY CLUSTERED
(
[BusinessCode] ASC
),
INDEX [IX_BusinessRegister.Company_Name] NONCLUSTERED
(
[Name] ASC
),
INDEX [IX_BusinessRegister.Company_VatNo] NONCLUSTERED
(
[VatNo] ASC
)
);";
await RepositorySqlHelper.ExcecuteNonQueryAsync(companyTableCration);
var companyTableTrigger = $@"
-- =============================================
-- Author: Rannes Pärn
-- Create date: {DateTime.Now:dd.MM.yyyy}
-- Description: Update modified date when a row has been updated
-- =============================================
CREATE TRIGGER [dbo].{TableName.Company.Replace("]", "")}_Update]
ON [dbo].{TableName.Company} AFTER UPDATE
AS
BEGIN
SET NOCOUNT ON;
IF EXISTS (SELECT 1 FROM inserted)
BEGIN
SELECT * INTO #InsertedTemp FROM inserted;
SELECT * INTO #DeletedTemp FROM deleted;
ALTER TABLE #InsertedTemp DROP COLUMN Added, Modified;
ALTER TABLE #DeletedTemp DROP COLUMN Added, Modified;
UPDATE
[dbo].{TableName.Company}
SET
Modified = GETDATE()
WHERE
BusinessCode IN
(SELECT tt.BusinessCode FROM
(SELECT * FROM #InsertedTemp EXCEPT SELECT * FROM #DeletedTemp) tt)
END;
END;
";
await RepositorySqlHelper.ExcecuteNonQueryAsync(companyTableTrigger);
var tableTypeCompany = $@"CREATE Type {TableType.Company} AS TABLE
(
[Name] [nvarchar](400) NOT NULL,
[BusinessCode] [varchar](50) NOT NULL,
[VatNo] [varchar](200) NULL,
[Status] [varchar](20) NOT NULL,
[FullAddress] [nvarchar] (1024) NOT NULL,
[Url] [varchar] (200) NOT NULL,
INDEX [IX_BusinessCode] NONCLUSTERED
(
[BusinessCode] ASC
)
);";
await RepositorySqlHelper.ExcecuteNonQueryAsync(tableTypeCompany);
await _databaseVersionRepository.UpdateDatabaseVersionNumber(1);
transaction.Complete();
}
}