public async Task Insert(Company item)
{
int result = -1;
using (SqlConnection connection =
SqlConnectionManager.GetConnection(DbConnectionString))
{
IEnumerable <int> results = await connection.QueryAsync <int>(@"
SET NOCOUNT ON;
DECLARE @Id int;
SET @Id = NULL;
SELECT TOP 1
@Id = Id
FROM
Company
WHERE
Name = @Name
IF(@Id IS NULL)
BEGIN
INSERT INTO Company
(Name)
VALUES
(@Name)
SELECT TOP 1
Id
FROM
Company
WHERE
Name = @Name
END
ELSE
BEGIN
SELECT -1
END" ,
new
{
Name = item.Name
}).ConfigureAwait(false);
result = results.FirstOrDefault();
}
if (result > 0)
{
item.Id = result;
CacheProvider
.ClearContainer("Company");
}
if (result == -1)
{
throw new ItemAlreadyExistsException();
}
}
public async Task Update(Company item)
{
int result = -1;
using (SqlConnection connection =
SqlConnectionManager.GetConnection(DbConnectionString))
{
IEnumerable <int> results = await connection.QueryAsync <int>(@"
SET NOCOUNT ON;
DECLARE @ExistingId int;
SET @ExistingId = NULL;
SELECT TOP 1
@ExistingId = Id
FROM
Company
WHERE
Name = @Name
IF(@ExistingId IS NULL OR @ExistingId = @Id)
BEGIN
UPDATE
Company
SET
Name = @Name
WHERE
Id = @Id
SELECT @Id
END
ELSE
BEGIN
SELECT -1
END",
new
{
Id = item.Id,
Name = item.Name
}).ConfigureAwait(false);
result = results.FirstOrDefault();
}
if (result > 0)
{
CacheProvider
.ClearContainer("Company");
}
if (result == -1)
{
throw new ItemAlreadyExistsException();
}
}
public async Task PurgeForTest()
{
#if DEBUG
using (SqlConnection connection =
SqlConnectionManager.GetConnection(DbConnectionString))
{
await connection.ExecuteAsync(@"
SET NOCOUNT ON;
DELETE FROM
Company").ConfigureAwait(false);
}
CacheProvider
.ClearContainer("Company");
#endif
}
public async Task Delete(int id)
{
using (SqlConnection connection =
SqlConnectionManager.GetConnection(DbConnectionString))
{
await connection.ExecuteAsync(@"
SET NOCOUNT ON;
DELETE FROM
Company
WHERE
Id = @Id",
new
{
Id = id
}).ConfigureAwait(false);
}
CacheProvider
.ClearContainer("Company");
}
public void ClearCache()
{
CacheProvider.ClearContainer("httpGet");
}