[.NET Core] 3. Web API DB 연결 (Dapper)

이전글: 

[.NET Core] 2. Web API MiddleWare 구성

 

 

먼저 Web API 에 Dapper로 DB연결을 하기위해  Nuget 패키지 관리자에서 Dapper 를 추가해준다.

 

 

appsettings.json 파일에 connection string 을 정의하고

연결 문자열에 액세스할 수 있게  SqlConnection 개체를 반환하는 CreateConnection 메서드를 DapperContext 에 만든다.

 

appsettings.json

{
  "Logging": {
    "LogLevel": {
      "Default": "Warning"
    }
  },
  "ConnectionStrings": {
    "DefaultConnection": "Server=xx.xxx.xx.xx,xxxx;Database=xxxx;User Id=xxxx;Password=xxxx"
  },
  "AllowedHosts": "*",
  "ApiKey": "test"
}

 

DapperContext.cs

using Microsoft.Extensions.Configuration;
using System.Data;
using System.Data.SqlClient;

namespace ApiProject.Models
{
    public class DapperConntext
    {
        private readonly IConfiguration _configuration;
        private readonly string _connectionString;
        public DapperConntext(IConfiguration configuration)
        {
            _configuration = configuration;
            _connectionString = _configuration.GetConnectionString("DefaultConnection");
        }
        public IDbConnection CreateConnection()
            => new SqlConnection(_connectionString);
    }

}

 

 

그리고 Startup.cs ConfigureServices 부분에 SingleTon 패턴으로 DapperContext의 인스턴스를 생성해준다.

 

public void ConfigureServices(IServiceCollection services)
{
    services.AddSingleton<DapperContext>();
    services.AddControllers();
}

 

Repository , Interface  생성

public interface ICommonRepository
{
}

public class CommonRepository : ICommonRepository
{
    private readonly DapperContext _context;
    public CommonRepository(DapperContext context)
    {
        _context = context;
    }
}

 

마지막으로 Repository 파일까지 생성했다면 Startup.cs 파일에 서비스를 등록해준뒤 사용하면된다.

 

 

public void ConfigureServices(IServiceCollection services)
{
    services.AddSingleton<DapperContext>();
    services.AddScoped<IComonRepository, CommonRepository>();
    services.AddControllers();
}

 

사용예 

  public class CommonRepository : ICommonRepository
    {
        IApiResponse response;
        private readonly DapperConntext _context;
        public CommonRepository(DapperConntext context)
        {
            _context = context;
        }

        public IApiResponse ApiAuthCheck(string ClientID, string ApiKey)
        {
            try
            {
                using (var c = _context.CreateConnection())
                {
                    var d_param = new DynamicParameters();
                    d_param.Add("@ClientID", ClientID);
                    d_param.Add("@ApiKey", ApiKey);
                    d_param.Add("@bResult", null, DbType.Boolean, ParameterDirection.Output);
                    d_param.Add("@message", null, DbType.String, ParameterDirection.Output, 200);
                    IList<dynamic> list = c.Query<dynamic>("usp_auth_check", d_param, null, false, null, CommandType.StoredProcedure).ToList();

                    response = new ApiResponse()
                    {
                        ResultCode = d_param.Get<Boolean>("@bResult"),
                        ResultMessage = d_param.Get<String>("@message"),
                        ResponseData = list
                    };
                }
            }
            catch (Exception e)
            {
                throw e;
            }
            return response;
        }
    }

 

참고

Using Dapper with ASP.NET Core Web API - Code Maze