[C#/WEB API/.NET5] PostgreSQL 데이터베이스 사용하기

TestProject.zip

0.01MB

▶ launchSettings.json

{
    "$schema"     : "http://json.schemastore.org/launchsettings.json",
    "iisSettings" :
    {
        "windowsAuthentication"   : false,
        "anonymousAuthentication" : true,
        "iisExpress"              :
        {
            "applicationUrl" : "http://localhost:35043",
            "sslPort"        : 44352
        }
    },
    "profiles" :
    {
        "IIS Express" :
        {
            "commandName"          : "IISExpress",
            "launchBrowser"        : true,
            "launchUrl"            : "swagger",
            "environmentVariables" :
            {
                "ASPNETCORE_ENVIRONMENT" : "Development"
            }
        },
        "TestProject" :
        {
            "commandName"          : "Project",
            "dotnetRunMessages"    : "true",
            "launchBrowser"        : true,
            "launchUrl"            : "swagger",
            "applicationUrl"       : "https://localhost:5001;http://localhost:5000",
            "environmentVariables" :
            {
                "ASPNETCORE_ENVIRONMENT" : "Development"
            }
        }
    }
}

 

728x90

 

▶ appsettings.json

{
    "Logging" :
    {
        "LogLevel" :
        {
            "Default"                    : "Information",
            "Microsoft"                  : "Warning",
            "Microsoft.Hosting.Lifetime" : "Information"
        }
    },
    "AllowedHosts"      : "*",
    "ConnectionStrings" :
    {
        "DefaultConnection" : "Server=localhost;Port=5432;Database=arca;User Id=admin;Password=1234;"
    }
}

 

300x250

 

▶ Models/CodeModel.cs

namespace TestProject.Models
{
    /// <summary>
    /// 코드 모델
    /// </summary>
    public class CodeModel
    {
        //////////////////////////////////////////////////////////////////////////////////////////////////// Property
        ////////////////////////////////////////////////////////////////////////////////////////// Public

        #region ID - ID (NOT NULL)

        /// <summary>
        /// ID
        /// </summary>
        public string ID { get; set; }

        #endregion
        #region 그룹 ID - GROUP_ID (NOT NULL)

        /// <summary>
        /// 그룹 ID
        /// </summary>
        public string GROUP_ID { get; set; }

        #endregion
        #region 명칭 - NAME (NOT NULL)

        /// <summary>
        /// 명칭
        /// </summary>
        public string NAME { get; set; }

        #endregion
        #region 값 - VALUE (NOT NULL)

        /// <summary>
        /// 값
        /// </summary>
        public string VALUE { get; set; }

        #endregion
        #region 표시 순서 - DISPLAY_ORDER (NOT NULL)

        /// <summary>
        /// 표시 순서
        /// </summary>
        public int DISPLAY_ORDER { get; set; }

        #endregion
        #region 상태 코드 - STATUS_CODE (NOT NULL)

        /// <summary>
        /// 상태 코드
        /// </summary>
        /// <remarks>
        /// A : 활성
        /// D : 삭제
        /// </remarks>
        public string STATUS_CODE { get; set; }

        #endregion
    }
}

 

▶ Models/CodeKeyModel.cs

namespace TestProject.Models
{
    /// <summary>
    /// 코드 키 모델
    /// </summary>
    public class CodeKeyModel
    {
        //////////////////////////////////////////////////////////////////////////////////////////////////// Property
        ////////////////////////////////////////////////////////////////////////////////////////// Public

        #region ID - ID

        /// <summary>
        /// ID
        /// </summary>
        public string ID { get; set; }

        #endregion
        #region 그룹 ID - GROUP_ID

        /// <summary>
        /// 그룹 ID
        /// </summary>
        public string GROUP_ID { get; set; }

        #endregion
    }
}

 

▶ CodeController.cs

using Microsoft.AspNetCore.Mvc;
using Microsoft.Extensions.Configuration;
using Microsoft.Extensions.Logging;
using System.Collections.Generic;
using System.Data;
using System.Linq;

using Npgsql;

using Dapper;

using TestProject.Models;

namespace TestProject.Controllers
{
    [ApiController]
    [Route("[controller]")]
    public class CodeController : ControllerBase
    {
        //////////////////////////////////////////////////////////////////////////////////////////////////// Field
        ////////////////////////////////////////////////////////////////////////////////////////// Static
        //////////////////////////////////////////////////////////////////////////////// Private

        #region INQUIRY_LIST_SQL

        /// <summary>
        /// INQUIRY_LIST_SQL
        /// </summary>
        private static string INQUIRY_LIST_SQL = @"
SELECT
    A.ID            AS ID
   ,A.GROUP_ID      AS GROUP_ID
   ,A.NAME          AS NAME
   ,A.VALUE         AS VALUE
   ,A.DISPLAY_ORDER AS DISPLAY_ORDER
   ,A.STATUS_CODE   AS STATUS_CODE
FROM     COM_CODE A
WHERE    A.GROUP_ID = @GROUP_ID
ORDER BY A.DISPLAY_ORDER ASC
";

        #endregion
        #region INQUIRY_ITEM

        /// <summary>
        /// INQUIRY_ITEM_SQL
        /// </summary>
        private static string INQUIRY_ITEM_SQL = @"
SELECT
    A.ID            AS ID
   ,A.GROUP_ID      AS GROUP_ID
   ,A.NAME          AS NAME
   ,A.VALUE         AS VALUE
   ,A.DISPLAY_ORDER AS DISPLAY_ORDER
   ,A.STATUS_CODE   AS STATUS_CODE
FROM  COM_CODE A
WHERE A.ID = @ID
";

        #endregion

        ////////////////////////////////////////////////////////////////////////////////////////// Instance
        //////////////////////////////////////////////////////////////////////////////// Private

        #region Field

        /// <summary>
        /// 구성
        /// </summary>
        private readonly IConfiguration configuration;

        /// <summary>
        /// 로거
        /// </summary>
        private readonly ILogger<CodeController> logger;

        /// <summary>
        /// 연결 문자열
        /// </summary>
        private readonly string connectionString;

        #endregion

        //////////////////////////////////////////////////////////////////////////////////////////////////// Constructor
        ////////////////////////////////////////////////////////////////////////////////////////// Public

        #region 생성자 - CodeController(configuration, logger)

        /// <summary>
        /// 생성자
        /// </summary>
        /// <param name="configuration">구성</param>
        /// <param name="logger">로거</param>
        public CodeController(IConfiguration configuration, ILogger<CodeController> logger)
        {
            this.configuration    = configuration;
            this.logger           = logger;
            this.connectionString = configuration.GetConnectionString("DefaultConnection");
        }

        #endregion

        //////////////////////////////////////////////////////////////////////////////////////////////////// Method
        ////////////////////////////////////////////////////////////////////////////////////////// Public

        #region 리스트 조회하기 - InquiryList(groupID)

        /// <summary>
        /// 리스트 조회하기
        /// </summary>
        /// <param name="groupID">그룹 ID</param>
        /// <returns>리스트</returns>
        [HttpGet]
        [Route("InquiryList")]
        public IEnumerable<CodeModel> InquiryList(string groupID)
        {
            using(IDbConnection connection = CreateConnection())
            {
                var result = connection.Query<CodeModel>(INQUIRY_LIST_SQL, new CodeKeyModel { GROUP_ID = groupID });

                return result;
            }
        }

        #endregion
        #region 항목 조회하기 - InquiryItem(id)

        /// <summary>
        /// 항목 조회하기
        /// </summary>
        /// <param name="id">ID</param>
        /// <returns></returns>
        [HttpGet]
        [Route("InquiryItem")]
        public CodeModel InquiryItem(string id)
        {
            using(IDbConnection connection = CreateConnection())
            {
                var result = connection.Query<CodeModel>(INQUIRY_ITEM_SQL, new CodeKeyModel { ID = id });

                return result.FirstOrDefault();
            }
        }

        #endregion

        ////////////////////////////////////////////////////////////////////////////////////////// Private

        #region 연결 생성하기 - CreateConnection()

        /// <summary>
        /// 연결 생성하기
        /// </summary>
        /// <returns>연결</returns>
        private IDbConnection CreateConnection()
        {
            return new NpgsqlConnection(this.connectionString);
        }

        #endregion
    }
}

 

▶ Startup.cs

using Microsoft.AspNetCore.Builder;
using Microsoft.AspNetCore.Hosting;
using Microsoft.Extensions.Configuration;
using Microsoft.Extensions.DependencyInjection;
using Microsoft.Extensions.Hosting;
using Microsoft.OpenApi.Models;

namespace TestProject
{
    /// <summary>
    /// 시작
    /// </summary>
    public class Startup
    {
        //////////////////////////////////////////////////////////////////////////////////////////////////// Property
        ////////////////////////////////////////////////////////////////////////////////////////// Public

        #region 구성 - Configuration

        /// <summary>
        /// 구성
        /// </summary>
        public IConfiguration Configuration { get; }

        #endregion

        //////////////////////////////////////////////////////////////////////////////////////////////////// Constructor
        ////////////////////////////////////////////////////////////////////////////////////////// Public

        #region 생성자 - Startup(configuration)

        /// <summary>
        /// 생성자
        /// </summary>
        /// <param name="configuration">구성</param>
        public Startup(IConfiguration configuration)
        {
            Configuration = configuration;
        }

        #endregion

        //////////////////////////////////////////////////////////////////////////////////////////////////// Method
        ////////////////////////////////////////////////////////////////////////////////////////// Public

        #region 서비스 구성하기 - ConfigureServices(IServiceCollection serviceCollection)

        /// <summary>
        /// 서비스 구성하기
        /// </summary>
        /// <param name="serviceCollection">서비스 컬렉션</param>
        public void ConfigureServices(IServiceCollection serviceCollection)
        {
            serviceCollection.AddControllers();

            serviceCollection.AddSwaggerGen
            (
                options =>
                {
                    options.SwaggerDoc("v1", new OpenApiInfo { Title = "TestProject", Version = "v1" });
                }
            );
        }

        #endregion
        #region 구성하기 - Configure(builder, environment)

        /// <summary>
        /// 구성하기
        /// </summary>
        /// <param name="builder">빌더</param>
        /// <param name="environment">환경</param>
        public void Configure(IApplicationBuilder builder, IWebHostEnvironment environment)
        {
            if(environment.IsDevelopment())
            {
                builder.UseDeveloperExceptionPage();

                builder.UseSwagger();

                builder.UseSwaggerUI(c => c.SwaggerEndpoint("/swagger/v1/swagger.json", "TestProject v1"));
            }

            builder.UseHttpsRedirection();

            builder.UseRouting();

            builder.UseAuthorization();

            builder.UseEndpoints
            (
                endpoints =>
                {
                    endpoints.MapControllers();
                }
            );
        }

        #endregion
    }
}

 

▶ Program.cs

using Microsoft.AspNetCore.Hosting;
using Microsoft.Extensions.Hosting;

namespace TestProject
{
    /// <summary>
    /// 프로그램
    /// </summary>
    public class Program
    {
        //////////////////////////////////////////////////////////////////////////////////////////////////// Method
        ////////////////////////////////////////////////////////////////////////////////////////// Static
        //////////////////////////////////////////////////////////////////////////////// Public

        #region 호스트 빌더 생성하기 - CreateHostBuilder(argumentList)

        /// <summary>
        /// 호스트 빌더 생성하기
        /// </summary>
        /// <param name="argumentList">인자 리스트</param>
        /// <returns>호스트 빌더</returns>
        public static IHostBuilder CreateHostBuilder(string[] argumentList) =>
            Host.CreateDefaultBuilder(argumentList)
                .ConfigureWebHostDefaults
                (
                    webHostBuilder =>
                    {
                        webHostBuilder.UseStartup<Startup>();
                    }
                );

        #endregion
        #region 프로그램 시작하기 - Main(argumentList)

        /// <summary>
        /// 프로그램 시작하기
        /// </summary>
        /// <param name="argumentList">인자 리스트</param>
        public static void Main(string[] argumentList)
        {
            CreateHostBuilder(argumentList).Build().Run();
        }

        #endregion
    }
}