기간계 시스템(ERP/SCM/WMS...) 개발자로서 화면 GRID 데이터를 어떻게 하면 DB에 효율적으로 입력/조회 할 수 있을까 늘 고민거리입니다.

1차로 Postgresql의 Composite Type을 사용하여 TVP를 구현했으나 해당 Composite Type을 C#의 Class로 구현하여 type을 맵핑을 해야만 합니다. MS-SQL과 비슷하게 구현 할 수 있어서 상당히 나름 쓸만합니다.

이 방법의 단점 (불편한점)

1. type을 맵핑해야 한다.

2. 화면 GRID 데이터를 해당 type의 Class로 List를 만들어 줘야 한다.(이거 귀찮음.)

pg의 create function 메뉴얼을 보던 중 함수의 파라미터로 JSON type을 사용 할 수 있는것을 보고

c#의 Newtonsoft.Json을 사용하면 조회 하여 결과를 grid에 출력 할때나 저장 할때 유용하게 사용 할 수 있을거라 생각이 되서 메뉴얼보고 해보니 잘 작동해서 관련 내용 정리해서 올립니다.

> 조회 함수(프로시저)

멀티row의 데이터를 한줄의 json형식으로 만들어 준다. 일전에 올렸던 함수표(?)에서 보면 여러줄일 경우 RETURNS SETOF json를 하면 되는데 json 한줄로 리턴하기 때문에 json만 사용하면 됩니다.

CREATE OR REPLACE FUNCTION public.usp_get_emp (
  p_emp_nm varchar
)
RETURNS json AS
$body$
/*
 ROW TO JSON
*/
select
 array_to_json
    (
      ARRAY
      (
              select row_to_json(tmp)
              from
              (
               SELECT a.emp_id, a.emp_nm FROM public.employee a
               WHERE a.emp_nm = CASE WHEN p_emp_nm  = '' THEN a.emp_nm ELSE p_emp_nm END
              ) tmp
      )
 );
$body$
LANGUAGE 'sql';

>C#의 조회

private void btnSearch_Click(object sender, EventArgs e)
{
    var _param = new[] 
    {
        new NpgsqlParameter
        {
            ParameterName = "p_emp_nm",
            NpgsqlDbType = NpgsqlDbType.Varchar,
            NpgsqlValue = this.txtEmpNm.Text.Trim()
        }
    };
 
    string jsonString = SqlHelper.ExecuteReader(this.connstring, CommandType.StoredProcedure, "usp_get_emp", _param);
 
    DataTable dt = JsonConvert.DeserializeObject<DataTable>(jsonString);
    --정렬은 Client에서...
     DataView dv = new DataView(dt)
    {
        Sort = "emp_id"
    };
 
    this.dataGridView1.DataSource = dv.ToTable();
}

> 저장 함수(프로시저)

CREATE OR REPLACE FUNCTION public.usp_set_emp (
  p_employee json
)
RETURNS void AS
$body$
/*
2017.01.19, hm.ji, JSON을 사용한 TVP
*/

DECLARE
  text_var1 text;
  text_var2 text;
  text_var3 text;
BEGIN

--아래와 같이 2가지로 구현이 가능합니다.

--how to : 1, JSON을 테이블로 변환 했을 경우 table형식 선언
with upsert as (SELECT A.emp_id, A.emp_nm FROM json_to_recordset(p_employee) as A(emp_id INTEGER, emp_nm VARCHAR(40)) ),
--how to : 2, Composite Type (employee_udt) 사용
--with upsert as (SELECT A.emp_id, A.emp_nm FROM json_populate_recordset(null::employee_udt, p_employee) A),
del as
      (
        delete from employee
        where not exists(select 1 from  upsert a where a.emp_id = employee.emp_id)
      ),
upd as
      (
          update employee
          set emp_nm = upsert.emp_nm
          , last_chg_time = now()
          from upsert
          where upsert.emp_id = employee.emp_id
          AND upsert.emp_nm <> employee.emp_nm
      )
insert into employee
select upsert.emp_id, upsert.emp_nm, now(), now()
from upsert
where not exists(select 1 from  employee  where employee.emp_id = upsert.emp_id);
   
 EXCEPTION
  WHEN OTHERS THEN
    GET STACKED DIAGNOSTICS text_var1 = RETURNED_SQLSTATE,
       text_var2 =  MESSAGE_TEXT,
       text_var3 = PG_EXCEPTION_CONTEXT;
   RAISE EXCEPTION E'RETURNED_SQLSTATE: %,\n\nMESSAGE_TEXT: %,\n\nPG_EXCEPTION_CONTEXT: %', text_var1, text_var2, text_var3;

END;
$body$
LANGUAGE 'plpgsql'

>C# 저장

private void btnSave_Click(object sender, EventArgs e)
{
    string jsonString = JsonConvert.SerializeObject((DataTable)this.dataGridView1.DataSource);
 
    var _param = new[] 
    {
        new NpgsqlParameter
        {
            ParameterName="p_employee",
            NpgsqlDbType = NpgsqlDbType.Json,
            NpgsqlValue = jsonString
        }
    };
 
    SqlHelper.ExecuteNonQuery(this.connstring, "usp_set_emp", _param);
}

----

C#+Postgresql에서 함수(프로시저)의 TVP를 구현하는 방법중에 첫번째 올렸던 C#+Composite Type 개발 방법보다 C#+JSON이 더 깔끔하고 괜찮아 보입니다.

성능상에는 두가지 방법이 어떤 차이점이 있는지 다음에 올리겠습니다.