public async Task <Company> Get(int id)
{
Company item = (Company)CacheProvider.Get("Company" + id);
if (item != null)
{
return(item);
}
using (SqlConnection connection =
SqlConnectionManager.GetConnection(DbConnectionString))
{
IEnumerable <Company> ciudades = await connection.QueryAsync <Company>(@"
SET NOCOUNT ON;
SELECT
Id, Name
FROM
Company
WHERE
Id = @Id",
new
{
Id = id
}).ConfigureAwait(false);
item = ciudades.FirstOrDefault();
}
CacheProvider
.Insert("Company",
"Company" + id, item);
return(item);
}
public async Task <IEnumerable <Company> > List()
{
IEnumerable <Company> items =
(IEnumerable <Company>)CacheProvider.Get("CompanyList");
if (items != null)
{
return(items);
}
using (SqlConnection connection =
SqlConnectionManager.GetConnection(DbConnectionString))
{
items = await connection.QueryAsync <Company>(@"
SET NOCOUNT ON;
SELECT
Id, Name
FROM
Company
ORDER BY
Name"
).ConfigureAwait(false);
}
CacheProvider
.Insert("Company",
"CompanyList", items);
return(items);
}
/// <summary>
/// Imports a set of bulk records into the data store.
/// </summary>
public async Task BulkImport(IEnumerable <T> items)
{
if (string.IsNullOrEmpty(StagingTableName))
{
return;
}
using (SqlConnection connection =
SqlConnectionManager.GetConnection(DbConnectionString))
{
using (SqlBulkCopy sqlBulkCopy = new SqlBulkCopy(connection))
{
sqlBulkCopy.DestinationTableName = StagingTableName;
sqlBulkCopy.BatchSize = 500;
foreach (KeyValuePair <string, string> kvp in BulkColumnMapping)
{
sqlBulkCopy.ColumnMappings.Add(kvp.Key, kvp.Value);
}
DataTable dt = ItemToDataTable(items);
await sqlBulkCopy.WriteToServerAsync(dt);
connection.Close();
}
}
await FinalizeBulkImport();
}
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");
}
