ASP.NET Core와 Entity Framework Core를 사용한 금융 데이터 관리

• 6/10/2024
• 5 minutes to read

이 튜토리얼에서는 ASP.NET Core와 Entity Framework Core를 사용하여 금융 데이터를 관리하는 방법을 배웁니다. SQL Server의 MONEY 타입을 C#의 decimal 타입으로 매핑하고, 연도별 금액 데이터를 효율적으로 관리하는 방법을 다룹니다.

1. 프로젝트 설정

1.1. 새로운 ASP.NET Core 프로젝트 생성

dotnet new webapi -n FinancialDataManagement
cd FinancialDataManagement

1.2. Entity Framework Core 패키지 설치

dotnet add package Microsoft.EntityFrameworkCore.SqlServer
dotnet add package Microsoft.EntityFrameworkCore.Tools

2. 데이터베이스 모델 정의

2.1. PersonAdditionalInfo 클래스

public class PersonAdditionalInfo
{
    public long Id { get; set; }
    public long PersonId { get; set; }
    public string EDD_LossThreshold { get; set; }
    public int CTR_Count { get; set; }
    public int SAR_Count { get; set; }
    public decimal Lifetime_Amount { get; set; }
    public decimal Current_Amount { get; set; }
    public bool NoCriminalRecords { get; set; }
    public bool NoHitOFAC { get; set; }
    public bool NoNegativeNewsArticles { get; set; }
    public bool NoAdverseGoogleInfo { get; set; }
    public bool NoFederalCivilCriminalDockets { get; set; }
    public bool NoBankruptciesLiensJudgments { get; set; }
    public bool NoRealProperty { get; set; }
    public string BusinessConnection { get; set; }
    public string PossibleEmployer { get; set; }
    public string SOFW_Location { get; set; }
}

2.2. PersonYearlyAmount 클래스

public class PersonYearlyAmount
{
    public long Id { get; set; }
    public long PersonAdditionalInfoId { get; set; }
    public int Year { get; set; }
    public decimal Amount { get; set; }
}

3. 데이터베이스 컨텍스트 설정

3.1. ApplicationDbContext 클래스

using Microsoft.EntityFrameworkCore;

public class ApplicationDbContext : DbContext
{
    public DbSet<PersonAdditionalInfo> PersonAdditionalInfos { get; set; }
    public DbSet<PersonYearlyAmount> PersonYearlyAmounts { get; set; }

    public ApplicationDbContext(DbContextOptions<ApplicationDbContext> options)
        : base(options)
    {
    }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<PersonAdditionalInfo>(entity =>
        {
            entity.HasKey(e => e.Id);
            entity.Property(e => e.Lifetime_Amount).HasColumnType("money");
            entity.Property(e => e.Current_Amount).HasColumnType("money");
        });

        modelBuilder.Entity<PersonYearlyAmount>(entity =>
        {
            entity.HasKey(e => e.Id);
            entity.Property(e => e.Amount).HasColumnType("money");
        });
    }
}

3.2. 데이터베이스 연결 설정

appsettings.json 파일에 SQL Server 연결 문자열을 추가합니다.

{
  "ConnectionStrings": {
    "DefaultConnection": "Server=(localdb)\\mssqllocaldb;Database=FinancialDataDb;Trusted_Connection=True;MultipleActiveResultSets=true"
  },
  ...
}

Program.cs 파일에서 데이터베이스 컨텍스트를 등록합니다.

var builder = WebApplication.CreateBuilder(args);

// Add services to the container.
builder.Services.AddControllers();
builder.Services.AddDbContext<ApplicationDbContext>(options =>
    options.UseSqlServer(builder.Configuration.GetConnectionString("DefaultConnection")));

var app = builder.Build();
...

app.Run();

4. 마이그레이션 및 데이터베이스 생성

4.1. 마이그레이션 생성

dotnet ef migrations add InitialCreate

4.2. 데이터베이스 업데이트

dotnet ef database update

5. 컨트롤러 생성

5.1. PersonAdditionalInfoController

using Microsoft.AspNetCore.Mvc;
using Microsoft.EntityFrameworkCore;
using System.Threading.Tasks;
using System.Collections.Generic;

[Route("api/[controller]")]
[ApiController]
public class PersonAdditionalInfoController : ControllerBase
{
    private readonly ApplicationDbContext _context;

    public PersonAdditionalInfoController(ApplicationDbContext context)
    {
        _context = context;
    }

    [HttpGet]
    public async Task<ActionResult<IEnumerable<PersonAdditionalInfo>>> GetPersonAdditionalInfos()
    {
        return await _context.PersonAdditionalInfos.ToListAsync();
    }

    [HttpGet("{id}")]
    public async Task<ActionResult<PersonAdditionalInfo>> GetPersonAdditionalInfo(long id)
    {
        var personAdditionalInfo = await _context.PersonAdditionalInfos.FindAsync(id);

        if (personAdditionalInfo == null)
        {
            return NotFound();
        }

        return personAdditionalInfo;
    }

    [HttpPost]
    public async Task<ActionResult<PersonAdditionalInfo>> PostPersonAdditionalInfo(PersonAdditionalInfo personAdditionalInfo)
    {
        _context.PersonAdditionalInfos.Add(personAdditionalInfo);
        await _context.SaveChangesAsync();

        return CreatedAtAction(nameof(GetPersonAdditionalInfo), new { id = personAdditionalInfo.Id }, personAdditionalInfo);
    }
}

5.2. PersonYearlyAmountController

using Microsoft.AspNetCore.Mvc;
using Microsoft.EntityFrameworkCore;
using System.Threading.Tasks;
using System.Collections.Generic;

[Route("api/[controller]")]
[ApiController]
public class PersonYearlyAmountController : ControllerBase
{
    private readonly ApplicationDbContext _context;

    public PersonYearlyAmountController(ApplicationDbContext context)
    {
        _context = context;
    }

    [HttpGet]
    public async Task<ActionResult<IEnumerable<PersonYearlyAmount>>> GetPersonYearlyAmounts()
    {
        return await _context.PersonYearlyAmounts.ToListAsync();
    }

    [HttpGet("{id}")]
    public async Task<ActionResult<PersonYearlyAmount>> GetPersonYearlyAmount(long id)
    {
        var personYearlyAmount = await _context.PersonYearlyAmounts.FindAsync(id);

        if (personYearlyAmount == null)
        {
            return NotFound();
        }

        return personYearlyAmount;
    }

    [HttpPost]
    public async Task<ActionResult<PersonYearlyAmount>> PostPersonYearlyAmount(PersonYearlyAmount personYearlyAmount)
    {
        _context.PersonYearlyAmounts.Add(personYearlyAmount);
        await _context.SaveChangesAsync();

        return CreatedAtAction(nameof(GetPersonYearlyAmount), new { id = personYearlyAmount.Id }, personYearlyAmount);
    }
}

6. 데이터 삽입 및 조회

6.1. 데이터 삽입 예제

PersonAdditionalInfo와 PersonYearlyAmount 데이터를 삽입하는 예제입니다.

using (var context = new ApplicationDbContext(options))
{
    var personInfo = new PersonAdditionalInfo
    {
        PersonId = 1,
        EDD_LossThreshold = "Threshold",
        CTR_Count = 20,
        SAR_Count = 0,
        Lifetime_Amount = 112507,
        Current_Amount = 57349,
        NoCriminalRecords = true,
        NoHitOFAC = true,
        NoNegativeNewsArticles = true,
        NoAdverseGoogleInfo = true,
        NoFederalCivilCriminalDockets = true,
        NoBankruptciesLiensJudgments = true,
        NoRealProperty = true,
        BusinessConnection = "EPIC PERSONNEL PARTNERS LLC 295 Sun Haven Pl Ste 3 Manteca, CA 95337",
        PossibleEmployer = "EPIC PERSONNEL PARTNERS LLC 295 Sun Haven Pl Ste 3 Manteca, CA 95337",
        SOFW_Location = "EPIC PERSONNEL PARTNERS LLC 295 Sun Haven Pl Ste 3 Manteca, CA 95337"
    };

    context.PersonAdditionalInfos.Add(personInfo);
    context.SaveChanges();

    var yearlyAmounts = new List<PersonYearlyAmount>
    {
        new PersonYearlyAmount { PersonAdditionalInfoId = personInfo.Id, Year = 2023, Amount = 53277 },
        new PersonYearlyAmount { PersonAdditionalInfoId = personInfo.Id, Year = 2022, Amount = 925 }
    };

    context.PersonYearlyAmounts.AddRange(yearlyAmounts);
    context.SaveChanges();
}

6.2. 데이터 조회 예제

PersonAdditionalInfo와 PersonYearlyAmount 데이터를 조회하는 예제입니다.

[HttpGet("details/{id}")]
public async Task<ActionResult<PersonAdditionalInfo>> GetPersonDetails(long id)
{
    var personInfo = await _context.PersonAdditionalInfos
                                   .Include(p => p.PersonYearlyAmounts)
                                   .FirstOrDefaultAsync(p => p.Id == id);

    if (personInfo == null)
    {
        return NotFound();
    }

    return personInfo;
}

결론

이 튜토리얼에서는 ASP.NET Core와 Entity Framework Core를 사용하여 금융 데이터를 관리하는 방법을 배웠습니다. SQL Server의 MONEY 타입을 C#의 decimal 타입으로 매핑하고, 연도별 금액 데이터를 효율적으로 관리하는 구조를 설계했습니다. 이 구조는 확장성과 관리 용이성을 제공하여, 실제 금융 데이터를 다루는 응용 프로그램에 유용하게 사용할 수 있습니다.