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"); }