Overview : MSSQL DB 데이타 캐싱 하기
1. MySQL
2. MSSQL Data
UML
솔루션 파일들
- Microsoft.EntityFrameworkCore.SqlSever 사용 / 없으면 패키지 추가
- MySql 캐싱에서 Data-> AirlineDBContext.cs 부분만 다름
AirlineController
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 |
using Microsoft.AspNetCore.Mvc; using InMemoryCacheCore.Models; using InMemoryCacheCore.Services; using InMemoryCacheCore.Data; namespace InMemoryCacheCore.Controllers { [ApiController] [Route("[controller]")] public class AirlineController : ControllerBase { private readonly ICodeService _codeService; private readonly ICacheService _cacheService; private readonly ILogger<AirlineController> _logger; public AirlineController(ICodeService codeService, ICacheService cacheService, ILogger<AirlineController> logger) { _codeService = codeService ?? throw new ArgumentNullException(nameof(codeService)); _cacheService = cacheService ?? throw new ArgumentNullException(nameof(cacheService)); _logger = logger; } [HttpGet(Name = "AirlineCode")] public async Task<IEnumerable<Code>> Get() { try { IEnumerable<Code> codes = _cacheService.GetCachedCode("_Codes"); if (codes == null || !codes.Any()) { //AirlineMySQLDBContext _context = new AirlineMySQLDBContext(); codes = await _codeService.GetCodeAsync(); } return codes ?? new List<Code>(); //codes = codes.Where(s => s.Airlinecode.Equals("KE", StringComparison.Ordinal)) ?? new List<Code>(); //return codes; } catch (Exception ex) { _logger.LogError(ex, "정보를 가져오는 동안 오류가 발생했습니다."); throw; } } } } |
AirlineDBContext
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
using Microsoft.EntityFrameworkCore; using InMemoryCacheCore.Models; namespace InMemoryCacheCore.Data { public class AirlineDBContext : DbContext { static readonly string connectionString = "Server=SQLOLEDB.1;Password=XXXXXXX;Persist Security Info=True;User ID=UserXXX;Initial Catalog=BasicCode;Data Source=XXX.XXX.XXX.XXX"; public DbSet<Code>? AIRLINE { get; set; } protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder) { optionsBuilder.UseSqlServer(connectionString); } } } |
AirlineHttpClient
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 |
using Microsoft.EntityFrameworkCore; using InMemoryCacheCore.Models; using InMemoryCacheCore.Data; namespace InMemoryCacheCore.Infrastructure { public interface IHttpClient { Task<IEnumerable<Code>> Get(); } public class AirlineHttpClient : IHttpClient { public async Task<IEnumerable<Code>> Get() { AirlineDBContext _context = new AirlineDBContext(); if (_context.AIRLINE != null) { var codesResponse = await _context.AIRLINE.ToListAsync(); return codesResponse; } else { //throw new Exception(""); return Array.Empty<Code>(); } //return new Code[0]; } } } |
CacheProvider
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 |
using Microsoft.Extensions.Caching.Memory; namespace InMemoryCacheCore.Infrastructure { public interface ICacheProvider { T? GetFromCache<T>(string key) where T : class; void SetCache<T>(string key, T value, MemoryCacheEntryOptions options) where T : class; void ClearCache(string key); } public class CacheProvider : ICacheProvider { private readonly IMemoryCache _cache; public CacheProvider(IMemoryCache cache) { _cache = cache; } public T? GetFromCache<T>(string key) where T : class { //_cache.TryGetValue(key, out T cachedResponse); //return cachedResponse as T; return _cache.Get<T>(key); } public void SetCache<T>(string key, T value, MemoryCacheEntryOptions options) where T : class { _cache.Set(key, value, options); } public void ClearCache(string key) { _cache.Remove(key); } } } |
Models -> Airline
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 |
using System.ComponentModel.DataAnnotations; using System.Xml.Linq; using System.Xml.Serialization; namespace InMemoryCacheCore.Models { public class Code { [Key] [Display(Name = "Airlinecode")] public string? Airlinecode { get; set; } [Display(Name = "Airline3code")] public string? Airline3code { get; set; } //Can't convert VarChar to Int32 [Display(Name = "Airlinekor")] public string? Airlinekor { get; set; } [Display(Name = "Airlineeng")] public string? Airlineeng { get; set; } } } |
CachedCodeService
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 |
using InMemoryCacheCore.Data; using InMemoryCacheCore.Infrastructure; using InMemoryCacheCore.Models; using Microsoft.Extensions.Caching.Memory; namespace InMemoryCacheCore.Services { public class CachedCodeService : ICodeService { private readonly CodeService _codeService; private readonly ICacheProvider _cacheProvider; private const int CacheTTLInSeconds = 100; private readonly MemoryCacheEntryOptions cacheEntryOptions = new MemoryCacheEntryOptions() .SetSlidingExpiration(TimeSpan.FromSeconds(CacheTTLInSeconds)); private static readonly SemaphoreSlim GetUsersSemaphore = new(1, 1); public CachedCodeService(CodeService codeService, ICacheProvider cacheProvider) { _codeService = codeService; _cacheProvider = cacheProvider; } public async Task<IEnumerable<Code>> GetCodeAsync() { return await GetCachedResponse("_Codes", GetUsersSemaphore, () => _codeService.GetCodeAsync()); } private async Task<IEnumerable<Code>> GetCachedResponse(string cacheKey, SemaphoreSlim semaphore, Func<Task<IEnumerable<Code>>> func) { var users = _cacheProvider?.GetFromCache<IEnumerable<Code>>(cacheKey); if (users != null) return users; try { await semaphore.WaitAsync(); users = _cacheProvider?.GetFromCache<IEnumerable<Code>>(cacheKey); // Recheck to make sure it didn't populate before entering semaphore if (users != null) return users; users = await func(); _cacheProvider?.SetCache(cacheKey, users, cacheEntryOptions); } finally { semaphore.Release(); } return users; } } } |
CacheService
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 |
using InMemoryCacheCore.Infrastructure; using InMemoryCacheCore.Models; namespace InMemoryCacheCore.Services { public interface ICacheService { IEnumerable<Code> GetCachedCode(string cacheKey); } public class CacheService : ICacheService { private readonly ICacheProvider _cacheProvider; public CacheService(ICacheProvider cacheProvider) { _cacheProvider = cacheProvider; } public IEnumerable<Code> GetCachedCode(string cacheKey) { return _cacheProvider.GetFromCache<IEnumerable<Code>>(cacheKey) ?? Enumerable.Empty<Code>(); } public void ClearCache(string cachekey) { _cacheProvider.ClearCache(cachekey); } } } |
CodeService
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 |
using InMemoryCacheCore.Data; using InMemoryCacheCore.Infrastructure; using InMemoryCacheCore.Models; namespace InMemoryCacheCore.Services { public interface ICodeService { Task<IEnumerable<Code>> GetCodeAsync(); } public class CodeService : ICodeService { private readonly IHttpClient _airlineMySQLClient; public CodeService(IHttpClient AirlineMySQLClient) { _airlineMySQLClient = AirlineMySQLClient; } public Task<IEnumerable<Code>> GetCodeAsync() { return _airlineMySQLClient.Get(); } } } |
Program.cs
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 |
using InMemoryCacheCore.Data; using InMemoryCacheCore.Infrastructure; using InMemoryCacheCore.Models; using InMemoryCacheCore.Services; using Microsoft.EntityFrameworkCore; var builder = WebApplication.CreateBuilder(args); // Add services to the container. builder.Services.AddControllers(); // Learn more about configuring Swagger/OpenAPI at https://aka.ms/aspnetcore/swashbuckle builder.Services.AddEndpointsApiExplorer(); builder.Services.AddSwaggerGen(); // builder.Services.AddHttpClient(); builder.Services.AddSingleton<CodeService>(); //MainService builder.Services.AddSingleton<ICodeService, CachedCodeService>(); //User 연결 서비스 builder.Services.AddSingleton<ICacheService, CacheService>(); //캐싱 서비스 builder.Services.AddSingleton<ICacheProvider, CacheProvider>(); //infrastructure builder.Services.AddSingleton<IHttpClient, AirlineHttpClient>(); //infrastructure builder.Services.AddMemoryCache(); var app = builder.Build(); // Configure the HTTP request pipeline. if (app.Environment.IsDevelopment()) { app.UseSwagger(); app.UseSwaggerUI(); } //if (!app.Environment.IsDevelopment()) //{ app.UseHttpsRedirection(); //} app.UseAuthorization(); app.MapControllers(); app.Run(); |
Reponse
