POSTGRESQL Archives - 8 중 3 번째 페이지

[DATABASE/POSTGRESQL] DATE_PART 함수 : 날짜 값에서 월 구하기

■ DATE_PART 함수를 사용해 날짜 값에서 월을 구하는 방법을 보여준다. ▶ 예제 코드 (SQL)


SELECT CURRENT_TIMESTAMP, DATE_PART('month', CURRENT_TIMESTAMP) AS MONTH;

SELECT CURRENT_TIMESTAMP, DATE_PART('month', CURRENT_TIMESTAMP) AS MONTH;

[DATABASE/POSTGRESQL] EXTRACT 함수 : 날짜 값에서 요일 구하기 (일요일-토요일)

■ EXTRACT 함수를 사용해 날짜 값에서 요일을 구하는 방법을 보여준다. (일요일-토요일) ▶ 예제 코드 (SQL)


SELECT
    CASE EXTRACT(DOW FROM DATE '2023-02-20')
	     WHEN 0 THEN '일'
	     WHEN 1 THEN '월'
	     WHEN 2 THEN '화'
	     WHEN 3 THEN '수'
	     WHEN 4 THEN '목'
	     WHEN 5 THEN '금'
	     WHEN 6 THEN '토'
    END AS WEEK_DAY;

SELECT

CASE EXTRACT(DOW FROM DATE '2023-02-20')

WHEN 0 THEN '일'

WHEN 1 THEN '월'

WHEN 2 THEN '화'

WHEN 3 THEN '수'

WHEN 4 THEN '목'

WHEN 5 THEN '금'

WHEN 6 THEN '토'

END AS WEEK_DAY;

[DATABASE/POSTGRESQL] EXTRACT 함수 : 날짜 값에서 월 구하기

■ EXTRACT 함수를 사용해 날짜 값에서 월을 구하는 방법을 보여준다. ▶ 예제 코드 (SQL)


SELECT EXTRACT(MONTH FROM TIMESTAMP '2020-12-16 10:41:35') AS MONTH;

SELECT CURRENT_TIMESTAMP, EXTRACT(MONTH FROM CURRENT_TIMESTAMP) AS MONTH;

SELECT EXTRACT(MONTH FROM TIMESTAMP '2020-12-16 10:41:35') AS MONTH;

SELECT CURRENT_TIMESTAMP, EXTRACT(MONTH FROM CURRENT_TIMESTAMP) AS MONTH;

[DATABASE/POSTGRESQL] 윈도우즈에서 디폴트 데이터 디렉토리(PGDATA) 이동하기

■ 윈도우즈에서 디폴트 데이터 디렉토리(PGDATA)를 이동하는 방법을 보여준다. 1. 데이터베이스에 연결된 모든 애플리케이션을 종료한다. 2. [서비스] 대화 상자에서 아래와 같이 해당 PostgreSQL

[DATABASE/POSTGRESQL] REGEXP_REPLACE 함수 : 정규식을 사용해 문자열에서 모든 공백 제거하기

■ REGEXP_REPLACE 함수를 사용해 정규식을 사용해 문자열에서 모든 공백을 제거하는 방법을 보여준다. ▶ 예제 코드 (SQL)


SELECT REGEXP_REPLACE(' 테 스 트 ', '\s', '', 'g');

SELECT REGEXP_REPLACE(' 테 스 트 ', '\s', '', 'g');

[DATABASE/POSTGRESQL] CAST 함수 : 데이터 타입 변환하기

■ CAST 함수를 사용해 데이터 타입을 변환하는 방법을 보여준다. ▶ 예제 코드 (SQL)


SELECT CATEGORY6
FROM   PUBLIC.LIBRARY_DATA
WHERE  MENU_ID = '439BE38B-F7CD-447A-9266-54F906A22A7B'
AND    CATEGORY1 = '공개'
AND    LENGTH(CATEGORY6) > 0
ORDER BY CAST(category6 AS INTEGER) ASC;

SELECT CATEGORY6

FROM PUBLIC.LIBRARY_DATA

WHERE MENU_ID = '439BE38B-F7CD-447A-9266-54F906A22A7B'

AND CATEGORY1 = '공개'

AND LENGTH(CATEGORY6) > 0

ORDER BY CAST(category6 AS INTEGER) ASC;

[DATABASE/POSTGRESQL] SET STATEMENT_TIMEOUT TO 명령 : 쿼리 실행시 타임아웃 시간 설정하기

■ SET STATEMENT_TIMEOUT TO 명령을 사용해 쿼리 실행시 타임아웃 시간을 설정하는 방법을 보여준다. ▶ 예제 코드 (SQL)


SET STATEMENT_TIMEOUT TO 60000;

※ 60000 : 1분

SET STATEMENT_TIMEOUT TO 60000;

※ 60000 : 1분

[C#/COMMON/IBATIS] ISqlMapper 인터페이스 : QueryForObject 메소드를 사용해 데이터 행 수 구하기

■ ISqlMapper 인터페이스의 QueryForObject 메소드를 사용해 데이터 행 수를 구하는 방법을 보여준다. ▶ providers.config


<?xml version="1.0" encoding="utf-8"?>
<providers
    xmlns="http://ibatis.apache.org/providers"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
    <clear/>
    <provider name="PostgreSql4.0.7.0"
        description="PostgreSql, Npgsql provider V4.0.7.0"
        assemblyName="Npgsql, Version=4.0.7.0, Culture=neutral, PublicKeyToken=5d8b90d52f46fda7"
        connectionClass="Npgsql.NpgsqlConnection"
        commandClass="Npgsql.NpgsqlCommand"
        parameterClass="Npgsql.NpgsqlParameter"
        parameterDbTypeClass="NpgsqlTypes.NpgsqlDbType"
        parameterDbTypeProperty="NpgsqlDbType"
        dataAdapterClass="Npgsql.NpgsqlDataAdapter"
        commandBuilderClass="Npgsql.NpgsqlCommandBuilder"
        usePositionalParameters="false"
        useParameterPrefixInSql="true"
        useParameterPrefixInParameter="true"
        parameterPrefix=":"
        allowMARS="true"
        enabled="true"
        default="true" />
</providers>

<?xml version="1.0" encoding="utf-8"?>

<providers

xmlns="http://ibatis.apache.org/providers"

xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">

<provider name="PostgreSql4.0.7.0"

description="PostgreSql, Npgsql provider V4.0.7.0"

assemblyName="Npgsql, Version=4.0.7.0, Culture=neutral, PublicKeyToken=5d8b90d52f46fda7"

connectionClass="Npgsql.NpgsqlConnection"

commandClass="Npgsql.NpgsqlCommand"

parameterClass="Npgsql.NpgsqlParameter"

parameterDbTypeClass="NpgsqlTypes.NpgsqlDbType"

parameterDbTypeProperty="NpgsqlDbType"

dataAdapterClass="Npgsql.NpgsqlDataAdapter"

commandBuilderClass="Npgsql.NpgsqlCommandBuilder"

usePositionalParameters="false"

useParameterPrefixInSql="true"

useParameterPrefixInParameter="true"

parameterPrefix=":"

allowMARS="true"

enabled="true"

default="true" />

</providers>

▶ sql.xml


<?xml version="1.0" encoding="utf-8" ?>
<sqlMap namespace="TestProject"
    xmlns="http://ibatis.apache.org/mapping"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
    <statements>
        <statement id="GetDataCount" parameterClass="System.Int32" resultClass="System.String">
SELECT COUNT(1) AS CNT
FROM   PUBLIC.LIBRARY_DATA
WHERE  MENU_ID =  #value#
        </statement>
    </statements>
</sqlMap>

<?xml version="1.0" encoding="utf-8" ?>

<sqlMap namespace="TestProject"

xmlns="http://ibatis.apache.org/mapping"

xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">

SELECT COUNT(1) AS CNT

FROM PUBLIC.LIBRARY_DATA

WHERE MENU_ID = #value#

</statement>

</statements>

</sqlMap>

▶ SqlMap.config


<?xml version="1.0" encoding="utf-8" ?>
<sqlMapConfig
    xmlns="http://ibatis.apache.org/dataMapper"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
    <settings>
        <setting useStatementNamespaces="false" />
        <setting cacheModelsEnabled="true"      />
        <setting validateSqlMap="true"          />
    </settings>
    <database>
        <provider name="PostgreSql4.0.7.0" />
        <dataSource name="TestDB"
            connectionString="Host=127.0.0.1;Port=5432;Database=TestDB;User ID=postgres;Password=postgres;" />
    </database>
    <sqlMaps>
        <sqlMap embedded="sql.xml, TestProject" />
    </sqlMaps>
</sqlMapConfig>

<?xml version="1.0" encoding="utf-8" ?>

<sqlMapConfig

xmlns="http://ibatis.apache.org/dataMapper"

xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">

</settings>

<dataSource name="TestDB"

connectionString="Host=127.0.0.1;Port=5432;Database=TestDB;User ID=postgres;Password=postgres;" />

</database>

</sqlMaps>

</sqlMapConfig>

▶

[C#/COMMON/IBATIS] iBatis를 사용해 PostgreSQL 데이터베이스 액세스하기

■ iBatis를 사용해 PostgreSQL 데이터베이스를 액세스하는 방법을 보여준다. ▶ providers.config


<?xml version="1.0" encoding="utf-8"?>
<providers
    xmlns="http://ibatis.apache.org/providers"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
    <clear />
    <provider name="Odbc1.1"
        description="Odbc, provider V1.0.5000.0 in framework .NET V1.1"
        assemblyName="System.Data, Version=1.0.5000.0, Culture=neutral, PublicKeyToken=b77a5c561934e089"
        connectionClass="System.Data.Odbc.OdbcConnection"
        commandClass="System.Data.Odbc.OdbcCommand"
        parameterClass="System.Data.Odbc.OdbcParameter"
        parameterDbTypeClass="System.Data.Odbc.OdbcType"
        parameterDbTypeProperty="OdbcType"
        dataAdapterClass="System.Data.Odbc.OdbcDataAdapter"
        commandBuilderClass="System.Data.Odbc.OdbcCommandBuilder"
        usePositionalParameters="true"
        useParameterPrefixInSql="false"
        useParameterPrefixInParameter="false"
        parameterPrefix="@"
        allowMARS="false"
        enabled="false" />
    <provider name="Odbc2.0"
        description="Odbc, provider V2.0.0.0 in framework .NET V2"
        assemblyName="System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089"
        connectionClass="System.Data.Odbc.OdbcConnection"
        commandClass="System.Data.Odbc.OdbcCommand"
        parameterClass="System.Data.Odbc.OdbcParameter"
        parameterDbTypeClass="System.Data.Odbc.OdbcType"
        parameterDbTypeProperty="OdbcType"
        dataAdapterClass="System.Data.Odbc.OdbcDataAdapter"
        commandBuilderClass="System.Data.Odbc.OdbcCommandBuilder"
        usePositionalParameters="true"
        useParameterPrefixInSql="false"
        useParameterPrefixInParameter="false"
        parameterPrefix="@"
        allowMARS="false"
        enabled="false" />
    <provider name="OleDb1.1"
        description="OleDb, provider V1.0.5000.0 in framework .NET V1.1"
        assemblyName="System.Data, Version=1.0.5000.0, Culture=neutral, PublicKeyToken=b77a5c561934e089"
        connectionClass="System.Data.OleDb.OleDbConnection"
        commandClass="System.Data.OleDb.OleDbCommand"
        parameterClass="System.Data.OleDb.OleDbParameter"
        parameterDbTypeClass="System.Data.OleDb.OleDbType"
        parameterDbTypeProperty="OleDbType"
        dataAdapterClass="System.Data.OleDb.OleDbDataAdapter"
        commandBuilderClass="System.Data.OleDb.OleDbCommandBuilder"
        usePositionalParameters="true"
        useParameterPrefixInSql="false"
        useParameterPrefixInParameter="false"
        parameterPrefix=""
        allowMARS="false"
        enabled="false" />
    <provider name="OleDb2.0"
        description="OleDb, provider V2.0.0.0 in framework .NET V2"
        assemblyName="System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089"
        connectionClass="System.Data.OleDb.OleDbConnection"
        commandClass="System.Data.OleDb.OleDbCommand"
        parameterClass="System.Data.OleDb.OleDbParameter"
        parameterDbTypeClass="System.Data.OleDb.OleDbType"
        parameterDbTypeProperty="OleDbType"
        dataAdapterClass="System.Data.OleDb.OleDbDataAdapter"
        commandBuilderClass="System.Data.OleDb.OleDbCommandBuilder"
        usePositionalParameters="true"
        useParameterPrefixInSql="false"
        useParameterPrefixInParameter="false"
        parameterPrefix=""
        allowMARS="false"
        enabled="false" />
    <provider name="sqlServer1.0"
        description="Microsoft SQL Server, provider V1.0.3300.0 in framework .NET V1.0"
        assemblyName="System.Data, Version=1.0.3300.0, Culture=neutral, PublicKeyToken=b77a5c561934e089"
        connectionClass="System.Data.SqlClient.SqlConnection"
        commandClass="System.Data.SqlClient.SqlCommand"
        parameterClass="System.Data.SqlClient.SqlParameter"
        parameterDbTypeClass="System.Data.SqlDbType"
        parameterDbTypeProperty="SqlDbType"
        dataAdapterClass="System.Data.SqlClient.SqlDataAdapter"
        commandBuilderClass="System.Data.SqlClient.SqlCommandBuilder"
        usePositionalParameters="false"
        useParameterPrefixInSql="true"
        useParameterPrefixInParameter="true"
        parameterPrefix="@"
        allowMARS="false"
        enabled="false" />
    <provider name="sqlServer1.1"
        description="Microsoft SQL Server, provider V1.0.5000.0 in framework .NET V1.1"
        assemblyName="System.Data, Version=1.0.5000.0, Culture=neutral, PublicKeyToken=b77a5c561934e089"
        connectionClass="System.Data.SqlClient.SqlConnection"
        commandClass="System.Data.SqlClient.SqlCommand"
        parameterClass="System.Data.SqlClient.SqlParameter"
        parameterDbTypeClass="System.Data.SqlDbType"
        parameterDbTypeProperty="SqlDbType"
        dataAdapterClass="System.Data.SqlClient.SqlDataAdapter"
        commandBuilderClass="System.Data.SqlClient.SqlCommandBuilder"
        usePositionalParameters="false"
        useParameterPrefixInSql="true"
        useParameterPrefixInParameter="true"
        parameterPrefix="@"
        allowMARS="false"
        enabled="false" />
    <provider name="sqlServer2.0"
        description="Microsoft SQL Server, provider V2.0.0.0 in framework .NET V2.0"
        enabled="false"
        assemblyName="System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089"
        connectionClass="System.Data.SqlClient.SqlConnection"
        commandClass="System.Data.SqlClient.SqlCommand"
        parameterClass="System.Data.SqlClient.SqlParameter"
        parameterDbTypeClass="System.Data.SqlDbType"
        parameterDbTypeProperty="SqlDbType"
        dataAdapterClass="System.Data.SqlClient.SqlDataAdapter"
        commandBuilderClass=" System.Data.SqlClient.SqlCommandBuilder"
        usePositionalParameters = "false"
        useParameterPrefixInSql = "true"
        useParameterPrefixInParameter = "true"
        parameterPrefix="@"
        allowMARS="false" />
    <provider name="sqlServer4.0"
        description="Microsoft SQL Server, provider V4.0.0.0 in framework .NET V4.0"
        assemblyName="System.Data, Version=4.0.0.0, Culture=Neutral, PublicKeyToken=b77a5c561934e089"
        connectionClass="System.Data.SqlClient.SqlConnection"
        commandClass="System.Data.SqlClient.SqlCommand"
        parameterClass="System.Data.SqlClient.SqlParameter"
        parameterDbTypeClass="System.Data.SqlDbType"
        parameterDbTypeProperty="SqlDbType"
        dataAdapterClass="System.Data.SqlClient.SqlDataAdapter"
        commandBuilderClass=" System.Data.SqlClient.SqlCommandBuilder"
        usePositionalParameters = "false"
        useParameterPrefixInSql = "true"
        useParameterPrefixInParameter = "true"
        parameterPrefix="@"
        allowMARS="false"
        enabled="false" />
    <provider name="sqlServer2005"
        description="Microsoft SQL Server, provider V2.0.0.0 in framework .NET V2.0"
        assemblyName="System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089"
        connectionClass="System.Data.SqlClient.SqlConnection"
        commandClass="System.Data.SqlClient.SqlCommand"
        parameterClass="System.Data.SqlClient.SqlParameter"
        parameterDbTypeClass="System.Data.SqlDbType"
        parameterDbTypeProperty="SqlDbType"
        dataAdapterClass="System.Data.SqlClient.SqlDataAdapter"
        commandBuilderClass=" System.Data.SqlClient.SqlCommandBuilder"
        usePositionalParameters = "false"
        useParameterPrefixInSql = "true"
        useParameterPrefixInParameter = "true"
        parameterPrefix="@"
        allowMARS="true"
        enabled="false" />
    <provider name="oracle9.2"
        description="Oracle, Oracle provider V9.2.0.401"
        assemblyName="Oracle.DataAccess, Version=9.2.0.401, Culture=neutral, PublicKeyToken=89b483f429c47342"
        connectionClass="Oracle.DataAccess.Client.OracleConnection"
        commandClass="Oracle.DataAccess.Client.OracleCommand"
        parameterClass="Oracle.DataAccess.Client.OracleParameter"
        parameterDbTypeClass="Oracle.DataAccess.Client.OracleDbType"
        parameterDbTypeProperty="OracleDbType"
        dataAdapterClass="Oracle.DataAccess.Client.OracleDataAdapter"
        commandBuilderClass="Oracle.DataAccess.Client.OracleCommandBuilder"
        usePositionalParameters="false"
        useParameterPrefixInSql="true"
        useParameterPrefixInParameter="false"
        parameterPrefix=":"
        useDeriveParameters="false"
        allowMARS="false"
        enabled="false" />
    <provider name="oracle10.1"
        description="Oracle, oracle provider V10.1.0.301"
        assemblyName="Oracle.DataAccess, Version=10.1.0.301, Culture=neutral, PublicKeyToken=89b483f429c47342"
        connectionClass="Oracle.DataAccess.Client.OracleConnection"
        commandClass="Oracle.DataAccess.Client.OracleCommand"
        parameterClass="Oracle.DataAccess.Client.OracleParameter"
        parameterDbTypeClass="Oracle.DataAccess.Client.OracleDbType"
        parameterDbTypeProperty="OracleDbType"
        dataAdapterClass="Oracle.DataAccess.Client.OracleDataAdapter"
        commandBuilderClass="Oracle.DataAccess.Client.OracleCommandBuilder"
        usePositionalParameters="true"
        useParameterPrefixInSql="true"
        useParameterPrefixInParameter="true"
        parameterPrefix=":"
        useDeriveParameters="false"
        allowMARS="false"
        enabled="false" />
    <provider name="oracleClient1.0"
        description="Oracle, Microsoft provider V1.0.5000.0"
        assemblyName="System.Data.OracleClient, Version=1.0.5000.0, Culture=neutral, PublicKeyToken=b77a5c561934e089"
        connectionClass="System.Data.OracleClient.OracleConnection"
        commandClass="System.Data.OracleClient.OracleCommand"
        parameterClass="System.Data.OracleClient.OracleParameter"
        parameterDbTypeClass="System.Data.OracleClient.OracleType"
        parameterDbTypeProperty="OracleType"
        dataAdapterClass="System.Data.OracleClient.OracleDataAdapter"
        commandBuilderClass="System.Data.OracleClient.OracleCommandBuilder"
        usePositionalParameters="false"
        useParameterPrefixInSql="true"
        useParameterPrefixInParameter="false"
        parameterPrefix=":"
        allowMARS="false"
        enabled="false" />
    <provider name="PostgreSql4.0.7.0"
        description="PostgreSql, Npgsql provider V4.0.7.0"
        assemblyName="Npgsql, Version=4.0.7.0, Culture=neutral, PublicKeyToken=5d8b90d52f46fda7"
        connectionClass="Npgsql.NpgsqlConnection"
        commandClass="Npgsql.NpgsqlCommand"
        parameterClass="Npgsql.NpgsqlParameter"
        parameterDbTypeClass="NpgsqlTypes.NpgsqlDbType"
        parameterDbTypeProperty="NpgsqlDbType"
        dataAdapterClass="Npgsql.NpgsqlDataAdapter"
        commandBuilderClass="Npgsql.NpgsqlCommandBuilder"
        usePositionalParameters="false"
        useParameterPrefixInSql="true"
        useParameterPrefixInParameter="true"
        parameterPrefix=":"
        allowMARS="true"
        enabled="true" />
    <provider name="MySql"
        description="MySQL, MySQL provider 1.0.7.30072"
        assemblyName="MySql.Data, Version=1.0.7.30072, Culture=neutral, PublicKeyToken=c5687fc88969c44d"
        connectionClass="MySql.Data.MySqlClient.MySqlConnection"
        commandClass="MySql.Data.MySqlClient.MySqlCommand"
        parameterClass="MySql.Data.MySqlClient.MySqlParameter"
        parameterDbTypeClass="MySql.Data.MySqlClient.MySqlDbType"
        parameterDbTypeProperty="MySqlDbType"
        dataAdapterClass="MySql.Data.MySqlClient.MySqlDataAdapter"
        commandBuilderClass="MySql.Data.MySqlClient.MySqlCommandBuilder"
        usePositionalParameters="false"
        useParameterPrefixInSql="true"
        useParameterPrefixInParameter="true"
        parameterPrefix="?"
        allowMARS="false"
        enabled="false" />
    <provider name="ByteFx"
        description="MySQL, ByteFx provider V0.7.6.15073"
        assemblyName="ByteFX.MySqlClient, Version=0.7.6.15073, Culture=neutral, PublicKeyToken=f2fef6fed1732fc1"
        connectionClass="ByteFX.Data.MySqlClient.MySqlConnection"
        commandClass="ByteFX.Data.MySqlClient.MySqlCommand"
        parameterClass="ByteFX.Data.MySqlClient.MySqlParameter"
        parameterDbTypeClass="ByteFX.Data.MySqlClient.MySqlDbType"
        parameterDbTypeProperty="MySqlDbType"
        dataAdapterClass="ByteFX.Data.MySqlClient.MySqlDataAdapter"
        commandBuilderClass="ByteFX.Data.MySqlClient.MySqlCommandBuilder"
        usePositionalParameters="false"
        useParameterPrefixInSql="true"
        useParameterPrefixInParameter="true"
        parameterPrefix="@"
        allowMARS="false"
        enabled="false" />
    <provider name="iDb2.10"
        description="IBM DB2 Provider, V 10.0"
        assemblyName="IBM.Data.DB2.iSeries, Version=10.0.0.0,Culture=neutral, PublicKeyToken=9cdb2ebfb1f93a26, Custom=null"
        connectionClass="IBM.Data.DB2.iSeries.iDB2Connection"
        commandClass="IBM.Data.DB2.iSeries.iDB2Command"
        parameterClass="IBM.Data.DB2.iSeries.iDB2Parameter"
        parameterDbTypeClass="IBM.Data.DB2.iSeries.iDB2DbType"
        parameterDbTypeProperty="iDB2DbType"
        dataAdapterClass="IBM.Data.DB2.iSeries.iDB2DataAdapter"
        commandBuilderClass="IBM.Data.DB2.iSeries.iDB2CommandBuilder"
        usePositionalParameters="true"
        useParameterPrefixInSql="false"
        useParameterPrefixInParameter="false"
        parameterPrefix=""
        allowMARS="false"
        enabled="false" />
    <provider name="Informix"
        description="Informix NET Provider, 2.81.0.0"
        assemblyName="IBM.Data.Informix, Version=2.81.0.0, Culture=neutral, PublicKeyToken=7c307b91aa13d208"
        connectionClass="IBM.Data.Informix.IfxConnection"
        commandClass="IBM.Data.Informix.IfxCommand"
        parameterClass="IBM.Data.Informix.IfxParameter"
        parameterDbTypeClass="IBM.Data.Informix.IfxType"
        parameterDbTypeProperty="IfxType"
        dataAdapterClass="IBM.Data.Informix.IfxDataAdapter"
        commandBuilderClass="IBM.Data.Informix.IfxCommandBuilder"
        usePositionalParameters = "true"
        useParameterPrefixInSql = "false"
        useParameterPrefixInParameter = "false"
        useDeriveParameters="false"
        allowMARS="false"
        enabled="false" />
    <provider name="SQLite3"
        description="SQLite, SQLite.NET provider V1.0.105.1"
        assemblyName="System.Data.SQLite, Version=1.0.105.1, Culture=neutral, PublicKeyToken=db937bc2d44ff139"
        connectionClass="System.Data.SQLite.SQLiteConnection"
        commandClass="System.Data.SQLite.SQLiteCommand"
        parameterClass="System.Data.SQLite.SQLiteParameter"
        parameterDbTypeClass="System.Data.SQLite.SQLiteType"
        parameterDbTypeProperty="DbType"
        dataAdapterClass="System.Data.SQLite.SQLiteDataAdapter"
        commandBuilderClass="System.Data.SQLite.SQLiteCommandBuilder"
        usePositionalParameters="false"
        useParameterPrefixInSql="true"
        useParameterPrefixInParameter="true"
        parameterPrefix="@"
        setDbParameterPrecision="false"
        setDbParameterScale="false"
        allowMARS="false"
        enabled="false" />
    <provider name="SQLite3 Finisar"
        description="SQLite, SQLite.NET provider V0.21.1869.3794"
        assemblyName="SQLite.NET, Version=0.21.1869.3794, Culture=neutral, PublicKeyToken=c273bd375e695f9c"
        connectionClass="Finisar.SQLite.SQLiteConnection"
        commandClass="Finisar.SQLite.SQLiteCommand"
        parameterClass="Finisar.SQLite.SQLiteParameter"
        parameterDbTypeClass="System.Data.DbType, System.Data"
        parameterDbTypeProperty="DbType"
        dataAdapterClass="Finisar.SQLite.SQLiteDataAdapter"
        commandBuilderClass="Finisar.SQLite.SQLiteCommandBuilder"
        usePositionalParameters="false"
        useParameterPrefixInSql="true"
        useParameterPrefixInParameter="true"
        parameterPrefix="@"
        setDbParameterPrecision="false"
        setDbParameterScale="false"
        allowMARS="false"
        enabled="false"  />
    <provider name="Firebird1.7"
        description="Firebird, Firebird SQL .NET provider V1.7.0.33200"
        assemblyName="FirebirdSql.Data.Firebird, Version=1.7.0.33200, Culture=neutral, PublicKeyToken=fa843d180294369d"
        connectionClass="FirebirdSql.Data.Firebird.FbConnection"
        commandClass="FirebirdSql.Data.Firebird.FbCommand"
        parameterClass="FirebirdSql.Data.Firebird.FbParameter"
        parameterDbTypeClass="FirebirdSql.Data.Firebird.FbDbType"
        parameterDbTypeProperty="FbDbType"
        dataAdapterClass="FirebirdSql.Data.Firebird.FbDataAdapter"
        commandBuilderClass="FirebirdSql.Data.Firebird.FbCommandBuilder"
        usePositionalParameters="false"
        useParameterPrefixInSql="true"
        useParameterPrefixInParameter="true"
        parameterPrefix="@"
        allowMARS="false"
        enabled="false" />
</providers>

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

154

155

156

157

158

159

160

161

162

163

164

165

166

167

168

169

170

171

172

173

174

175

176

177

178

179

180

181

182

183

184

185

186

187

188

189

190

191

192

193

194

195

196

197

198

199

200

201

202

203

204

205

206

207

208

209

210

211

212

213

214

215

216

217

218

219

220

221

222

223

224

225

226

227

228

229

230

231

232

233

234

235

236

237

238

239

240

241

242

243

244

245

246

247

248

249

250

251

252

253

254

255

256

257

258

259

260

261

262

263

264

265

266

267

268

269

270

271

272

273

274

275

276

277

278

279

280

281

282

283

284

285

286

287

288

289

290

291

292

293

294

295

296

297

298

299

300

301

302

303

304

305

306

307

308

309

310

311

312

313

314

315

316

317

318

319

320

321

322

323

324

325

326

327

328

329

330

331

332

333

334

<?xml version="1.0" encoding="utf-8"?>

<providers

xmlns="http://ibatis.apache.org/providers"

xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">

<provider name="Odbc1.1"

description="Odbc, provider V1.0.5000.0 in framework .NET V1.1"

assemblyName="System.Data, Version=1.0.5000.0, Culture=neutral, PublicKeyToken=b77a5c561934e089"

connectionClass="System.Data.Odbc.OdbcConnection"

commandClass="System.Data.Odbc.OdbcCommand"

parameterClass="System.Data.Odbc.OdbcParameter"

parameterDbTypeClass="System.Data.Odbc.OdbcType"

parameterDbTypeProperty="OdbcType"

dataAdapterClass="System.Data.Odbc.OdbcDataAdapter"

commandBuilderClass="System.Data.Odbc.OdbcCommandBuilder"

usePositionalParameters="true"

useParameterPrefixInSql="false"

useParameterPrefixInParameter="false"

parameterPrefix="@"

allowMARS="false"

enabled="false" />

<provider name="Odbc2.0"

description="Odbc, provider V2.0.0.0 in framework .NET V2"

assemblyName="System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089"

connectionClass="System.Data.Odbc.OdbcConnection"

commandClass="System.Data.Odbc.OdbcCommand"

parameterClass="System.Data.Odbc.OdbcParameter"

parameterDbTypeClass="System.Data.Odbc.OdbcType"

parameterDbTypeProperty="OdbcType"

dataAdapterClass="System.Data.Odbc.OdbcDataAdapter"

commandBuilderClass="System.Data.Odbc.OdbcCommandBuilder"

usePositionalParameters="true"

useParameterPrefixInSql="false"

useParameterPrefixInParameter="false"

parameterPrefix="@"

allowMARS="false"

enabled="false" />

<provider name="OleDb1.1"

description="OleDb, provider V1.0.5000.0 in framework .NET V1.1"

assemblyName="System.Data, Version=1.0.5000.0, Culture=neutral, PublicKeyToken=b77a5c561934e089"

connectionClass="System.Data.OleDb.OleDbConnection"

commandClass="System.Data.OleDb.OleDbCommand"

parameterClass="System.Data.OleDb.OleDbParameter"

parameterDbTypeClass="System.Data.OleDb.OleDbType"

parameterDbTypeProperty="OleDbType"

dataAdapterClass="System.Data.OleDb.OleDbDataAdapter"

commandBuilderClass="System.Data.OleDb.OleDbCommandBuilder"

usePositionalParameters="true"

useParameterPrefixInSql="false"

useParameterPrefixInParameter="false"

parameterPrefix=""

allowMARS="false"

enabled="false" />

<provider name="OleDb2.0"

description="OleDb, provider V2.0.0.0 in framework .NET V2"

assemblyName="System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089"

connectionClass="System.Data.OleDb.OleDbConnection"

commandClass="System.Data.OleDb.OleDbCommand"

parameterClass="System.Data.OleDb.OleDbParameter"

parameterDbTypeClass="System.Data.OleDb.OleDbType"

parameterDbTypeProperty="OleDbType"

dataAdapterClass="System.Data.OleDb.OleDbDataAdapter"

commandBuilderClass="System.Data.OleDb.OleDbCommandBuilder"

usePositionalParameters="true"

useParameterPrefixInSql="false"

useParameterPrefixInParameter="false"

parameterPrefix=""

allowMARS="false"

enabled="false" />

<provider name="sqlServer1.0"

description="Microsoft SQL Server, provider V1.0.3300.0 in framework .NET V1.0"

assemblyName="System.Data, Version=1.0.3300.0, Culture=neutral, PublicKeyToken=b77a5c561934e089"

connectionClass="System.Data.SqlClient.SqlConnection"

commandClass="System.Data.SqlClient.SqlCommand"

parameterClass="System.Data.SqlClient.SqlParameter"

parameterDbTypeClass="System.Data.SqlDbType"

parameterDbTypeProperty="SqlDbType"

dataAdapterClass="System.Data.SqlClient.SqlDataAdapter"

commandBuilderClass="System.Data.SqlClient.SqlCommandBuilder"

usePositionalParameters="false"

useParameterPrefixInSql="true"

useParameterPrefixInParameter="true"

parameterPrefix="@"

allowMARS="false"

enabled="false" />

<provider name="sqlServer1.1"

description="Microsoft SQL Server, provider V1.0.5000.0 in framework .NET V1.1"

assemblyName="System.Data, Version=1.0.5000.0, Culture=neutral, PublicKeyToken=b77a5c561934e089"

connectionClass="System.Data.SqlClient.SqlConnection"

commandClass="System.Data.SqlClient.SqlCommand"

parameterClass="System.Data.SqlClient.SqlParameter"

parameterDbTypeClass="System.Data.SqlDbType"

parameterDbTypeProperty="SqlDbType"

dataAdapterClass="System.Data.SqlClient.SqlDataAdapter"

commandBuilderClass="System.Data.SqlClient.SqlCommandBuilder"

usePositionalParameters="false"

useParameterPrefixInSql="true"

useParameterPrefixInParameter="true"

parameterPrefix="@"

allowMARS="false"

enabled="false" />

<provider name="sqlServer2.0"

description="Microsoft SQL Server, provider V2.0.0.0 in framework .NET V2.0"

enabled="false"

assemblyName="System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089"

connectionClass="System.Data.SqlClient.SqlConnection"

commandClass="System.Data.SqlClient.SqlCommand"

parameterClass="System.Data.SqlClient.SqlParameter"

parameterDbTypeClass="System.Data.SqlDbType"

parameterDbTypeProperty="SqlDbType"

dataAdapterClass="System.Data.SqlClient.SqlDataAdapter"

commandBuilderClass=" System.Data.SqlClient.SqlCommandBuilder"

usePositionalParameters = "false"

useParameterPrefixInSql = "true"

useParameterPrefixInParameter = "true"

parameterPrefix="@"

allowMARS="false" />

<provider name="sqlServer4.0"

description="Microsoft SQL Server, provider V4.0.0.0 in framework .NET V4.0"

assemblyName="System.Data, Version=4.0.0.0, Culture=Neutral, PublicKeyToken=b77a5c561934e089"

connectionClass="System.Data.SqlClient.SqlConnection"

commandClass="System.Data.SqlClient.SqlCommand"

parameterClass="System.Data.SqlClient.SqlParameter"

parameterDbTypeClass="System.Data.SqlDbType"

parameterDbTypeProperty="SqlDbType"

dataAdapterClass="System.Data.SqlClient.SqlDataAdapter"

commandBuilderClass=" System.Data.SqlClient.SqlCommandBuilder"

usePositionalParameters = "false"

useParameterPrefixInSql = "true"

useParameterPrefixInParameter = "true"

parameterPrefix="@"

allowMARS="false"

enabled="false" />

<provider name="sqlServer2005"

description="Microsoft SQL Server, provider V2.0.0.0 in framework .NET V2.0"

assemblyName="System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089"

connectionClass="System.Data.SqlClient.SqlConnection"

commandClass="System.Data.SqlClient.SqlCommand"

parameterClass="System.Data.SqlClient.SqlParameter"

parameterDbTypeClass="System.Data.SqlDbType"

parameterDbTypeProperty="SqlDbType"

dataAdapterClass="System.Data.SqlClient.SqlDataAdapter"

commandBuilderClass=" System.Data.SqlClient.SqlCommandBuilder"

usePositionalParameters = "false"

useParameterPrefixInSql = "true"

useParameterPrefixInParameter = "true"

parameterPrefix="@"

allowMARS="true"

enabled="false" />

<provider name="oracle9.2"

description="Oracle, Oracle provider V9.2.0.401"

assemblyName="Oracle.DataAccess, Version=9.2.0.401, Culture=neutral, PublicKeyToken=89b483f429c47342"

connectionClass="Oracle.DataAccess.Client.OracleConnection"

commandClass="Oracle.DataAccess.Client.OracleCommand"

parameterClass="Oracle.DataAccess.Client.OracleParameter"

parameterDbTypeClass="Oracle.DataAccess.Client.OracleDbType"

parameterDbTypeProperty="OracleDbType"

dataAdapterClass="Oracle.DataAccess.Client.OracleDataAdapter"

commandBuilderClass="Oracle.DataAccess.Client.OracleCommandBuilder"

usePositionalParameters="false"

useParameterPrefixInSql="true"

useParameterPrefixInParameter="false"

parameterPrefix=":"

useDeriveParameters="false"

allowMARS="false"

enabled="false" />

<provider name="oracle10.1"

description="Oracle, oracle provider V10.1.0.301"

assemblyName="Oracle.DataAccess, Version=10.1.0.301, Culture=neutral, PublicKeyToken=89b483f429c47342"

connectionClass="Oracle.DataAccess.Client.OracleConnection"

commandClass="Oracle.DataAccess.Client.OracleCommand"

parameterClass="Oracle.DataAccess.Client.OracleParameter"

parameterDbTypeClass="Oracle.DataAccess.Client.OracleDbType"

parameterDbTypeProperty="OracleDbType"

dataAdapterClass="Oracle.DataAccess.Client.OracleDataAdapter"

commandBuilderClass="Oracle.DataAccess.Client.OracleCommandBuilder"

usePositionalParameters="true"

useParameterPrefixInSql="true"

useParameterPrefixInParameter="true"

parameterPrefix=":"

useDeriveParameters="false"

allowMARS="false"

enabled="false" />

<provider name="oracleClient1.0"

description="Oracle, Microsoft provider V1.0.5000.0"

assemblyName="System.Data.OracleClient, Version=1.0.5000.0, Culture=neutral, PublicKeyToken=b77a5c561934e089"

connectionClass="System.Data.OracleClient.OracleConnection"

commandClass="System.Data.OracleClient.OracleCommand"

parameterClass="System.Data.OracleClient.OracleParameter"

parameterDbTypeClass="System.Data.OracleClient.OracleType"

parameterDbTypeProperty="OracleType"

dataAdapterClass="System.Data.OracleClient.OracleDataAdapter"

commandBuilderClass="System.Data.OracleClient.OracleCommandBuilder"

usePositionalParameters="false"

useParameterPrefixInSql="true"

useParameterPrefixInParameter="false"

parameterPrefix=":"

allowMARS="false"

enabled="false" />

<provider name="PostgreSql4.0.7.0"

description="PostgreSql, Npgsql provider V4.0.7.0"

assemblyName="Npgsql, Version=4.0.7.0, Culture=neutral, PublicKeyToken=5d8b90d52f46fda7"

connectionClass="Npgsql.NpgsqlConnection"

commandClass="Npgsql.NpgsqlCommand"

parameterClass="Npgsql.NpgsqlParameter"

parameterDbTypeClass="NpgsqlTypes.NpgsqlDbType"

parameterDbTypeProperty="NpgsqlDbType"

dataAdapterClass="Npgsql.NpgsqlDataAdapter"

commandBuilderClass="Npgsql.NpgsqlCommandBuilder"

usePositionalParameters="false"

useParameterPrefixInSql="true"

useParameterPrefixInParameter="true"

parameterPrefix=":"

allowMARS="true"

enabled="true" />

<provider name="MySql"

description="MySQL, MySQL provider 1.0.7.30072"

assemblyName="MySql.Data, Version=1.0.7.30072, Culture=neutral, PublicKeyToken=c5687fc88969c44d"

connectionClass="MySql.Data.MySqlClient.MySqlConnection"

commandClass="MySql.Data.MySqlClient.MySqlCommand"

parameterClass="MySql.Data.MySqlClient.MySqlParameter"

parameterDbTypeClass="MySql.Data.MySqlClient.MySqlDbType"

parameterDbTypeProperty="MySqlDbType"

dataAdapterClass="MySql.Data.MySqlClient.MySqlDataAdapter"

commandBuilderClass="MySql.Data.MySqlClient.MySqlCommandBuilder"

usePositionalParameters="false"

useParameterPrefixInSql="true"

useParameterPrefixInParameter="true"

parameterPrefix="?"

allowMARS="false"

enabled="false" />

<provider name="ByteFx"

description="MySQL, ByteFx provider V0.7.6.15073"

assemblyName="ByteFX.MySqlClient, Version=0.7.6.15073, Culture=neutral, PublicKeyToken=f2fef6fed1732fc1"

connectionClass="ByteFX.Data.MySqlClient.MySqlConnection"

commandClass="ByteFX.Data.MySqlClient.MySqlCommand"

parameterClass="ByteFX.Data.MySqlClient.MySqlParameter"

parameterDbTypeClass="ByteFX.Data.MySqlClient.MySqlDbType"

parameterDbTypeProperty="MySqlDbType"

dataAdapterClass="ByteFX.Data.MySqlClient.MySqlDataAdapter"

commandBuilderClass="ByteFX.Data.MySqlClient.MySqlCommandBuilder"

usePositionalParameters="false"

useParameterPrefixInSql="true"

useParameterPrefixInParameter="true"

parameterPrefix="@"

allowMARS="false"

enabled="false" />

<provider name="iDb2.10"

description="IBM DB2 Provider, V 10.0"

assemblyName="IBM.Data.DB2.iSeries, Version=10.0.0.0,Culture=neutral, PublicKeyToken=9cdb2ebfb1f93a26, Custom=null"

connectionClass="IBM.Data.DB2.iSeries.iDB2Connection"

commandClass="IBM.Data.DB2.iSeries.iDB2Command"

parameterClass="IBM.Data.DB2.iSeries.iDB2Parameter"

parameterDbTypeClass="IBM.Data.DB2.iSeries.iDB2DbType"

parameterDbTypeProperty="iDB2DbType"

dataAdapterClass="IBM.Data.DB2.iSeries.iDB2DataAdapter"

commandBuilderClass="IBM.Data.DB2.iSeries.iDB2CommandBuilder"

usePositionalParameters="true"

useParameterPrefixInSql="false"

useParameterPrefixInParameter="false"

parameterPrefix=""

allowMARS="false"

enabled="false" />

<provider name="Informix"

description="Informix NET Provider, 2.81.0.0"

assemblyName="IBM.Data.Informix, Version=2.81.0.0, Culture=neutral, PublicKeyToken=7c307b91aa13d208"

connectionClass="IBM.Data.Informix.IfxConnection"

commandClass="IBM.Data.Informix.IfxCommand"

parameterClass="IBM.Data.Informix.IfxParameter"

parameterDbTypeClass="IBM.Data.Informix.IfxType"

parameterDbTypeProperty="IfxType"

dataAdapterClass="IBM.Data.Informix.IfxDataAdapter"

commandBuilderClass="IBM.Data.Informix.IfxCommandBuilder"

usePositionalParameters = "true"

useParameterPrefixInSql = "false"

useParameterPrefixInParameter = "false"

useDeriveParameters="false"

allowMARS="false"

enabled="false" />

<provider name="SQLite3"

description="SQLite, SQLite.NET provider V1.0.105.1"

assemblyName="System.Data.SQLite, Version=1.0.105.1, Culture=neutral, PublicKeyToken=db937bc2d44ff139"

connectionClass="System.Data.SQLite.SQLiteConnection"

commandClass="System.Data.SQLite.SQLiteCommand"

parameterClass="System.Data.SQLite.SQLiteParameter"

parameterDbTypeClass="System.Data.SQLite.SQLiteType"

parameterDbTypeProperty="DbType"

dataAdapterClass="System.Data.SQLite.SQLiteDataAdapter"

commandBuilderClass="System.Data.SQLite.SQLiteCommandBuilder"

usePositionalParameters="false"

useParameterPrefixInSql="true"

useParameterPrefixInParameter="true"

parameterPrefix="@"

setDbParameterPrecision="false"

setDbParameterScale="false"

allowMARS="false"

enabled="false" />

<provider name="SQLite3 Finisar"

description="SQLite, SQLite.NET provider V0.21.1869.3794"

assemblyName="SQLite.NET, Version=0.21.1869.3794, Culture=neutral, PublicKeyToken=c273bd375e695f9c"

connectionClass="Finisar.SQLite.SQLiteConnection"

commandClass="Finisar.SQLite.SQLiteCommand"

parameterClass="Finisar.SQLite.SQLiteParameter"

parameterDbTypeClass="System.Data.DbType, System.Data"

parameterDbTypeProperty="DbType"

dataAdapterClass="Finisar.SQLite.SQLiteDataAdapter"

commandBuilderClass="Finisar.SQLite.SQLiteCommandBuilder"

usePositionalParameters="false"

useParameterPrefixInSql="true"

useParameterPrefixInParameter="true"

parameterPrefix="@"

setDbParameterPrecision="false"

setDbParameterScale="false"

allowMARS="false"

enabled="false" />

<provider name="Firebird1.7"

description="Firebird, Firebird SQL .NET provider V1.7.0.33200"

assemblyName="FirebirdSql.Data.Firebird, Version=1.7.0.33200, Culture=neutral, PublicKeyToken=fa843d180294369d"

connectionClass="FirebirdSql.Data.Firebird.FbConnection"

commandClass="FirebirdSql.Data.Firebird.FbCommand"

parameterClass="FirebirdSql.Data.Firebird.FbParameter"

parameterDbTypeClass="FirebirdSql.Data.Firebird.FbDbType"

parameterDbTypeProperty="FbDbType"

dataAdapterClass="FirebirdSql.Data.Firebird.FbDataAdapter"

commandBuilderClass="FirebirdSql.Data.Firebird.FbCommandBuilder"

usePositionalParameters="false"

useParameterPrefixInSql="true"

useParameterPrefixInParameter="true"

parameterPrefix="@"

allowMARS="false"

enabled="false" />

</providers>

▶ sql.xml


<?xml version="1.0" encoding="utf-8" ?>
<sqlMap
    namespace="Ibatis"
    xmlns="http://ibatis.apache.org/mapping"
    xmlns:xls="http://www.w3.org/2001/XMLSchema-instance">
    <statements>
        <select id="DataDAO.GetDataList" resultClass="TestProject.DataModel">
            SELECT *
            FROM   PUBLIC.LIBRARY_DATA
            WHERE  MENU_ID = '439BE38B-F7CD-447A-9266-54F906A22A7B'
        </select>
    </statements>
</sqlMap>

<?xml version="1.0" encoding="utf-8" ?>

<sqlMap

namespace="Ibatis"

xmlns="http://ibatis.apache.org/mapping"

xmlns:xls="http://www.w3.org/2001/XMLSchema-instance">

SELECT *

FROM PUBLIC.LIBRARY_DATA

WHERE MENU_ID = '439BE38B-F7CD-447A-9266-54F906A22A7B'

</select>

</statements>

</sqlMap>

▶ SqlMap.config


<?xml version="1.0" encoding="utf-8" ?>
<sqlMapConfig
    xmlns="http://ibatis.apache.org/dataMapper"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
    <settings>
        <setting useStatementNamespaces="false" />
    </settings>
    <providers resource="Providers.config" />
    <database>
        <provider name="PostgreSql4.0.7.0" />
        <dataSource name="TestDB"
            connectionString="Host=127.0.0.1;Port=5432;Database=TestDB;User ID=postgres;Password=postgres;" />
    </database>
    <sqlMaps>
        <sqlMap resource="sql.xml" />
    </sqlMaps>
</sqlMapConfig>

<?xml version="1.0" encoding="utf-8" ?>

<sqlMapConfig

xmlns="http://ibatis.apache.org/dataMapper"

xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">

</settings>

<dataSource name="TestDB"

connectionString="Host=127.0.0.1;Port=5432;Database=TestDB;User ID=postgres;Password=postgres;" />

</database>

</sqlMaps>

</sqlMapConfig>

▶ DataModel.cs


namespace TestProject
{
    /// <summary>
    /// 자료 모델
    /// </summary>
    public class DataModel
    {
        //////////////////////////////////////////////////////////////////////////////////////////////////// Property
        ////////////////////////////////////////////////////////////////////////////////////////// Public

        #region 자료 ID - ID

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

        #endregion
        #region 메뉴 ID - MENU_ID

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

        #endregion
        #region 제목 - SUBJECT

        /// <summary>
        /// 제목
        /// </summary>
        public string SUBJECT { get; set; }

        #endregion
    }
}

namespace TestProject

{

/// <summary>

/// 자료 모델

/// </summary>

public class DataModel

{

//////////////////////////////////////////////////////////////////////////////////////////////////// Property

////////////////////////////////////////////////////////////////////////////////////////// Public

#region 자료 ID - ID

/// <summary>

/// 자료 ID

/// </summary>

public string ID { get; set; }

#endregion

#region 메뉴 ID - MENU_ID

/// <summary>

/// 메뉴 ID

/// </summary>

public string MENU_ID { get; set; }

#endregion

#region 제목 - SUBJECT

/// <summary>

/// 제목

/// </summary>

public string SUBJECT { get; set; }

#endregion

}

▶ DataDAO.cs

[DATABASE/POSTGRESQL] “SSL 중지 연결에 대한 설정이 pg_hba.conf 파일에 없습니다” 오류시 조치 사항

■ "SSL 중지 연결에 대한 설정이 pg_hba.conf 파일에 없습니다" 오류시 조치하는 방법을 보여준다. 1. 메모장에서 아래 경로 파일을 연다. ▶ 실행 명령

[DATABASE/POSTGRESQL] COPY 명령 : CSV 파일 데이터를 테이블에 복사하기

■ COPY 명령을 사용해 CSV 파일 데이터를 테이블에 복사하는 방법을 보여준다. ▶ 예제 코드 (SQL)


COPY PUBLIC.COM_USER FROM 'd:/export.csv';

COPY PUBLIC.COM_USER FROM 'd:/export.csv';

※ export.csv 파일 데이터를 COM_USER 테이블에

[DATABASE/POSTGRESQL] COPY 명령 : 테이블 데이터를 CSV 파일로 복사하기

■ COPY 명령을 사용해 테이블 데이터를 CSV 파일로 복사하는 방법을 보여준다. ▶ 예제 코드 (SQL)


COPY (SELECT * FROM PUBLIC.COM_USER) TO 'd:/export.csv';

COPY (SELECT * FROM PUBLIC.COM_USER) TO 'd:/export.csv';

※ COM_USER 테이블 데이터를 export.csv 파일로

[DATABASE/POSTGRESQL] INSERT ON CONFLICT 명령 : 추가 또는 수정하기

■ INSERT ON CONFLICT 명령을 사용해 데이터를 추가 또는 수정하는 방법을 보여준다. ▶ 예제 코드 (SQL)


DROP TABLE IF EXISTS TEST_CUSTOMER;

CREATE TABLE TEST_CUSTOMER
(
    CUSTOMER_ID SERIAL PRIMARY KEY
   ,NAME        VARCHAR UNIQUE
   ,EMAIL       VARCHAR NOT NULL
   ,ACTIVE      BOOL NOT NULL DEFAULT TRUE
);

INSERT INTO TEST_CUSTOMER (NAME, EMAIL) VALUES
    ('IBM'      , 'contact@ibm.com'      ),
    ('Microsoft', 'contact@microsoft.com'),
    ('Intel'    , 'contact@intel.com'    );

INSERT INTO TEST_CUSTOMER (NAME, EMAIL) VALUES ('Microsoft', 'hotline@microsoft.com')
ON CONFLICT (NAME)
DO UPDATE SET EMAIL = EXCLUDED.EMAIL || ';' || TEST_CUSTOMER.EMAIL;

SELECT * FROM PUBLIC.TEST_CUSTOMER;

DROP TABLE IF EXISTS TEST_CUSTOMER;

CREATE TABLE TEST_CUSTOMER

(

CUSTOMER_ID SERIAL PRIMARY KEY

,NAME VARCHAR UNIQUE

,EMAIL VARCHAR NOT NULL

,ACTIVE BOOL NOT NULL DEFAULT TRUE

);

INSERT INTO TEST_CUSTOMER (NAME, EMAIL) VALUES

('IBM' , 'contact@ibm.com' ),

('Microsoft', 'contact@microsoft.com'),

('Intel' , 'contact@intel.com' );

INSERT INTO TEST_CUSTOMER (NAME, EMAIL) VALUES ('Microsoft', 'hotline@microsoft.com')

ON CONFLICT (NAME)

DO UPDATE SET EMAIL = EXCLUDED.EMAIL || ';' || TEST_CUSTOMER.EMAIL;

SELECT * FROM PUBLIC.TEST_CUSTOMER;

[DATABASE/POSTGRESQL] INSERT ON CONFLICT 명령 : 제약 조건 위반시 무시하기

■ INSERT ON CONFLICT 명령을 사용해 제약 조건 위반시 예외 발생을 무시하는 방법을 보여준다. ▶ 예제 코드 (SQL)


DROP TABLE IF EXISTS TEST_CUSTOMER;

CREATE TABLE TEST_CUSTOMER
(
    CUSTOMER_ID SERIAL PRIMARY KEY
   ,NAME        VARCHAR UNIQUE
   ,EMAIL       VARCHAR NOT NULL
   ,ACTIVE      BOOL NOT NULL DEFAULT TRUE
);

INSERT INTO TEST_CUSTOMER (NAME, EMAIL) VALUES
    ('IBM'      , 'contact@ibm.com'      ),
    ('Microsoft', 'contact@microsoft.com'),
    ('Intel'    , 'contact@intel.com'    );

INSERT INTO TEST_CUSTOMER (NAME, EMAIL) VALUES ('Microsoft','hotline@microsoft.com')
ON CONFLICT (NAME)
DO NOTHING;

DROP TABLE IF EXISTS TEST_CUSTOMER;

CREATE TABLE TEST_CUSTOMER

(

CUSTOMER_ID SERIAL PRIMARY KEY

,NAME VARCHAR UNIQUE

,EMAIL VARCHAR NOT NULL

,ACTIVE BOOL NOT NULL DEFAULT TRUE

);

INSERT INTO TEST_CUSTOMER (NAME, EMAIL) VALUES

('IBM' , 'contact@ibm.com' ),

('Microsoft', 'contact@microsoft.com'),

('Intel' , 'contact@intel.com' );

INSERT INTO TEST_CUSTOMER (NAME, EMAIL) VALUES ('Microsoft','hotline@microsoft.com')

ON CONFLICT (NAME)

DO NOTHING;

[DATABASE/POSTGRESQL] INSERT ON CONFLICT 명령 : 제약 조건 위반시 무시하기

■ INSERT ON CONFLICT 명령을 사용해 제약 조건 위반시 예외 발생을 무시하는 방법을 보여준다. ▶ 예제 코드 (SQL)


DROP TABLE IF EXISTS TEST_CUSTOMER;

CREATE TABLE TEST_CUSTOMER
(
    CUSTOMER_ID SERIAL PRIMARY KEY
   ,NAME        VARCHAR UNIQUE
   ,EMAIL       VARCHAR NOT NULL
   ,ACTIVE      BOOL NOT NULL DEFAULT TRUE
);

INSERT INTO TEST_CUSTOMER (NAME, EMAIL) VALUES
    ('IBM'      , 'contact@ibm.com'      ),
    ('Microsoft', 'contact@microsoft.com'),
    ('Intel'    , 'contact@intel.com'    );

INSERT INTO TEST_CUSTOMER (NAME, EMAIL) VALUES ('Microsoft', 'hotline@microsoft.com')
ON CONFLICT ON CONSTRAINT TEST_CUSTOMER_NAME_KEY
DO NOTHING;

DROP TABLE IF EXISTS TEST_CUSTOMER;

CREATE TABLE TEST_CUSTOMER

(

CUSTOMER_ID SERIAL PRIMARY KEY

,NAME VARCHAR UNIQUE

,EMAIL VARCHAR NOT NULL

,ACTIVE BOOL NOT NULL DEFAULT TRUE

);

INSERT INTO TEST_CUSTOMER (NAME, EMAIL) VALUES

('IBM' , 'contact@ibm.com' ),

('Microsoft', 'contact@microsoft.com'),

('Intel' , 'contact@intel.com' );

INSERT INTO TEST_CUSTOMER (NAME, EMAIL) VALUES ('Microsoft', 'hotline@microsoft.com')

ON CONFLICT ON CONSTRAINT TEST_CUSTOMER_NAME_KEY

DO NOTHING;

[DATABASE/POSTGRESQL] ROW_NUMBER 함수 : 행 번호 표시하기

■ ROW_NUMBER 함수를 사용해 행 번호를 표시하는 방법을 보여준다. ▶ 예제 코드 (SQL)


SELECT (ROW_NUMBER() OVER()) AS ROWNUM, SUBJECT FROM PUBLIC.LIBRARY_DATA LIMIT 10;

SELECT (ROW_NUMBER() OVER()) AS ROWNUM, SUBJECT FROM PUBLIC.LIBRARY_DATA LIMIT 10;

[DATABASE/POSTGRESQL] COALESCE 함수 : 특정 값이 NULL인 경우 지정 값 반환하기

■ COALESCE 함수를 사용해 특정 값이 NULL인 경우 지정 값을 반환하는 방법을 보여준다. ▶ 예제 코드 (SQL)


SELECT COALESCE(DESCRIPTION, '(Empty)') FROM PUBLIC.CUSTOMER;

SELECT COALESCE(DESCRIPTION, '(Empty)') FROM PUBLIC.CUSTOMER;

[C#/WEB API/.NET5] 대용량 데이터를 스트리밍 방식으로 전달받기 (PostgreSQL 연동)

■ 대용량 데이터를 스트리밍 방식으로 전달받는 방법을 보여준다. (PostgreSQL 연동) [TestClient 프로젝트] ▶ Program.cs


using System;
using System.Collections.Generic;
using System.IO;
using System.Net;
using System.Net.Http;
using System.Net.Http.Headers;
using System.Text;

using Newtonsoft.Json;

namespace TestClient
{
    /// <summary>
    /// 프로그램
    /// </summary>
    class Program
    {
        //////////////////////////////////////////////////////////////////////////////////////////////////// Method
        ////////////////////////////////////////////////////////////////////////////////////////// Static
        //////////////////////////////////////////////////////////////////////////////// Private

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

        /// <summary>
        /// 프로그램 시작하기
        /// </summary>
        static void Main()
        {
            string url = "https://localhost:44352/api/data/inquirylist";

            DataSearchConditionModel searchCondition = new DataSearchConditionModel();

            searchCondition.MENU_ID      = "A007A6D5-35E6-46A2-A3BD-1B84843BB76C";
            searchCondition.MEMBER_GRADE = "99";
            searchCondition.LIMIT_AGE    = "99";
            searchCondition.WRITE_DATE1  = new DateTime(2021, 7 , 1 );
            searchCondition.WRITE_DATE2  = new DateTime(2021, 7 , 25);
            searchCondition.CREATE_TIME1 = new DateTime(1   , 1 , 1 );
            searchCondition.CREATE_TIME2 = new DateTime(9999, 12, 31);

            int i = 0;

            var result = GetPOSTData1<DataModel>(url, searchCondition);

            foreach(DataModel photo in result)
            {
                Console.WriteLine("{0} {1} {2}", i++, photo.ID, photo.SUBJECT);
            }
        }

        #endregion

        #region POST 스트림 구하기 - POSTGetStream(serverURL, contentType, encoding, argument, timeOut)

        /// <summary>
        /// POST 스트림 구하기
        /// </summary>
        /// <param name="serverURL">서버 URL</param>
        /// <param name="contentType">컨텐츠 타입</param>
        /// <param name="encoding">인코딩</param>
        /// <param name="argument">인자 문자열</param>
        /// <param name="timeOut">타임 아웃</param>
        /// <returns>POST 스트림</returns>
        private static Stream POSTGetStream(string serverURL, string contentType, Encoding encoding, string argument, int? timeOut = null)
        {
            byte[] argumentArray = encoding.GetBytes(argument);

            HttpWebRequest request = WebRequest.Create(serverURL) as HttpWebRequest;

            request.ProtocolVersion           = HttpVersion.Version11;
            request.AllowAutoRedirect         = true;
            request.AllowWriteStreamBuffering = true;
            request.Method                    = WebRequestMethods.Http.Post;
            request.ContentType               = contentType;
            request.ContentLength             = argumentArray.Length;

            if(timeOut.HasValue)
            {
                request.Timeout = timeOut.Value;
            }

            using(Stream requestStream = request.GetRequestStream())
            {
                requestStream.Write(argumentArray, 0, argumentArray.Length);
            }

            HttpWebResponse response = request.GetResponse() as HttpWebResponse;

            Stream responseStream = response.GetResponseStream();

            return responseStream;
        }

        #endregion

        #region POST 데이터 구하기 1 - GetPOSTData1<TResultItem>(url, source)

        /// <summary>
        /// POST 데이터 구하기 1
        /// </summary>
        /// <typeparam name="TResultItem">결과 항목 타입</typeparam>
        /// <param name="url">URL</param>
        /// <param name="source">소스 객체</param>
        /// <returns>결과 항목 타입 열거 가능형</returns>
        private static IEnumerable<TResultItem> GetPOSTData1<TResultItem>(string url, object source)
        {
            string sourceJSON = JsonConvert.SerializeObject(source);

            HttpRequestMessage requestMessage = new HttpRequestMessage(HttpMethod.Post, url);

            requestMessage.Content = new StringContent(sourceJSON, Encoding.UTF8, "application/json");

            requestMessage.Headers.Accept.Add(new MediaTypeWithQualityHeaderValue("application/json"));

            HttpClient client = new HttpClient();

            HttpResponseMessage responseMessage = client.SendAsync(requestMessage, HttpCompletionOption.ResponseHeadersRead).Result;

            responseMessage.EnsureSuccessStatusCode();

            using(Stream stream = responseMessage.Content.ReadAsStreamAsync().Result)
            {
                using(StreamReader streamReader = new StreamReader(stream))
                {
                    JsonSerializer serializer = new JsonSerializer();

                    using(JsonTextReader jsonTextReader = new JsonTextReader(streamReader))
                    {
                        while(jsonTextReader.Read())
                        {
                            if(jsonTextReader.TokenType != JsonToken.StartArray && jsonTextReader.TokenType != JsonToken.EndArray)
                            {
                                yield return serializer.Deserialize<TResultItem>(jsonTextReader);
                            }
                        }
                    }
                }
            }
        }

        #endregion
        #region POST 데이터 구하기 2 - GetPOSTData2<TResultItem>(url, source)

        /// <summary>
        /// POST 데이터 구하기 2
        /// </summary>
        /// <typeparam name="TResultItem">결과 항목 타입</typeparam>
        /// <param name="url">URL</param>
        /// <param name="source">소스 객체</param>
        /// <returns>결과 항목 타입 열거 가능형</returns>
        private static IEnumerable<TResultItem> GetPOSTData2<TResultItem>(string url, object source)
        {
            string sourceJSON = JsonConvert.SerializeObject(source);

            using(Stream stream = POSTGetStream(url, "application/json; charset=utf-8", Encoding.UTF8, sourceJSON))
            {
                using(StreamReader streamReader = new StreamReader(stream))
                {
                    using(JsonTextReader jsonTextReader = new JsonTextReader(streamReader))
                    {
                        JsonSerializer serializer = new JsonSerializer();

                        while(jsonTextReader.Read())
                        {
                            if(jsonTextReader.TokenType != JsonToken.StartArray && jsonTextReader.TokenType != JsonToken.EndArray)
                            {
                                yield return serializer.Deserialize<TResultItem>(jsonTextReader);
                            }
                        }
                    }
                }
            }
        }

        #endregion
    }
}

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

154

155

156

157

158

159

160

161

162

163

164

165

166

167

168

169

170

171

172

173

174

175

176

177

178

179

using System;

using System.Collections.Generic;

using System.IO;

using System.Net;

using System.Net.Http;

using System.Net.Http.Headers;

using System.Text;

using Newtonsoft.Json;

namespace TestClient

{

/// <summary>

/// 프로그램

/// </summary>

class Program

{

//////////////////////////////////////////////////////////////////////////////////////////////////// Method

////////////////////////////////////////////////////////////////////////////////////////// Static

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

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

/// <summary>

/// 프로그램 시작하기

/// </summary>

static void Main()

{

string url = "https://localhost:44352/api/data/inquirylist";

DataSearchConditionModel searchCondition = new DataSearchConditionModel();

searchCondition.MENU_ID = "A007A6D5-35E6-46A2-A3BD-1B84843BB76C";

searchCondition.MEMBER_GRADE = "99";

searchCondition.LIMIT_AGE = "99";

searchCondition.WRITE_DATE1 = new DateTime(2021, 7 , 1 );

searchCondition.WRITE_DATE2 = new DateTime(2021, 7 , 25);

searchCondition.CREATE_TIME1 = new DateTime(1 , 1 , 1 );

searchCondition.CREATE_TIME2 = new DateTime(9999, 12, 31);

int i = 0;

var result = GetPOSTData1<DataModel>(url, searchCondition);

foreach(DataModel photo in result)

{

Console.WriteLine("{0} {1} {2}", i++, photo.ID, photo.SUBJECT);

}

#endregion

#region POST 스트림 구하기 - POSTGetStream(serverURL, contentType, encoding, argument, timeOut)

/// <summary>

/// POST 스트림 구하기

/// </summary>

/// <param name="serverURL">서버 URL</param>

/// <param name="contentType">컨텐츠 타입</param>

/// <param name="encoding">인코딩</param>

/// <param name="argument">인자 문자열</param>

/// <param name="timeOut">타임 아웃</param>

/// <returns>POST 스트림</returns>

private static Stream POSTGetStream(string serverURL, string contentType, Encoding encoding, string argument, int? timeOut = null)

{

byte[] argumentArray = encoding.GetBytes(argument);

HttpWebRequest request = WebRequest.Create(serverURL) as HttpWebRequest;

request.ProtocolVersion = HttpVersion.Version11;

request.AllowAutoRedirect = true;

request.AllowWriteStreamBuffering = true;

request.Method = WebRequestMethods.Http.Post;

request.ContentType = contentType;

request.ContentLength = argumentArray.Length;

if(timeOut.HasValue)

{

request.Timeout = timeOut.Value;

}

using(Stream requestStream = request.GetRequestStream())

{

requestStream.Write(argumentArray, 0, argumentArray.Length);

}

HttpWebResponse response = request.GetResponse() as HttpWebResponse;

Stream responseStream = response.GetResponseStream();

return responseStream;

}

#endregion

#region POST 데이터 구하기 1 - GetPOSTData1<TResultItem>(url, source)

/// <summary>

/// POST 데이터 구하기 1

/// </summary>

/// <typeparam name="TResultItem">결과 항목 타입</typeparam>

/// <param name="url">URL</param>

/// <param name="source">소스 객체</param>

/// <returns>결과 항목 타입 열거 가능형</returns>

private static IEnumerable<TResultItem> GetPOSTData1<TResultItem>(string url, object source)

{

string sourceJSON = JsonConvert.SerializeObject(source);

HttpRequestMessage requestMessage = new HttpRequestMessage(HttpMethod.Post, url);

requestMessage.Content = new StringContent(sourceJSON, Encoding.UTF8, "application/json");

requestMessage.Headers.Accept.Add(new MediaTypeWithQualityHeaderValue("application/json"));

HttpClient client = new HttpClient();

HttpResponseMessage responseMessage = client.SendAsync(requestMessage, HttpCompletionOption.ResponseHeadersRead).Result;

responseMessage.EnsureSuccessStatusCode();

using(Stream stream = responseMessage.Content.ReadAsStreamAsync().Result)

{

using(StreamReader streamReader = new StreamReader(stream))

{

JsonSerializer serializer = new JsonSerializer();

using(JsonTextReader jsonTextReader = new JsonTextReader(streamReader))

{

while(jsonTextReader.Read())

{

if(jsonTextReader.TokenType != JsonToken.StartArray && jsonTextReader.TokenType != JsonToken.EndArray)

{

yield return serializer.Deserialize<TResultItem>(jsonTextReader);

}

#endregion

#region POST 데이터 구하기 2 - GetPOSTData2<TResultItem>(url, source)

/// <summary>

/// POST 데이터 구하기 2

/// </summary>

/// <typeparam name="TResultItem">결과 항목 타입</typeparam>

/// <param name="url">URL</param>

/// <param name="source">소스 객체</param>

/// <returns>결과 항목 타입 열거 가능형</returns>

private static IEnumerable<TResultItem> GetPOSTData2<TResultItem>(string url, object source)

{

string sourceJSON = JsonConvert.SerializeObject(source);

using(Stream stream = POSTGetStream(url, "application/json; charset=utf-8", Encoding.UTF8, sourceJSON))

{

using(StreamReader streamReader = new StreamReader(stream))

{

using(JsonTextReader jsonTextReader = new JsonTextReader(streamReader))

{

JsonSerializer serializer = new JsonSerializer();

while(jsonTextReader.Read())

{

if(jsonTextReader.TokenType != JsonToken.StartArray && jsonTextReader.TokenType != JsonToken.EndArray)

{

yield return serializer.Deserialize<TResultItem>(jsonTextReader);

}

#endregion

}

[TestServer 프로젝트] ▶ 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"
            }
        },
        "TestServer" :
        {
            "commandName"          : "Project",
            "dotnetRunMessages"    : "true",
            "launchBrowser"        : true,
            "launchUrl"            : "swagger",
            "applicationUrl"       : "https://localhost:5001;http://localhost:5000",
            "environmentVariables" :
            {
                "ASPNETCORE_ENVIRONMENT" : "Development"
            }
        }
    }
}

{

"$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"

}

"TestServer" :

{

"commandName" : "Project",

"dotnetRunMessages" : "true",

"launchBrowser" : true,

"launchUrl" : "swagger",

"applicationUrl" : "https://localhost:5001;http://localhost:5000",

"environmentVariables" :

{

"ASPNETCORE_ENVIRONMENT" : "Development"

}

▶ appsettings.json

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

■ PostgreSQL 데이터베이스를 사용하는 방법을 보여준다. ▶ 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"
            }
        }
    }
}

{

"$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"

}

▶ 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;"
    }
}

{

"Logging" :

{

"LogLevel" :

{

"Default" : "Information",

"Microsoft" : "Warning",

"Microsoft.Hosting.Lifetime" : "Information"

}

"AllowedHosts" : "*",

"ConnectionStrings" :

{

"DefaultConnection" : "Server=localhost;Port=5432;Database=arca;User Id=admin;Password=1234;"

}

▶ 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
    }
}

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
    }
}

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
    }
}

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

154

155

156

157

158

159

160

161

162

163

164

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

}

▶


Server [localhost]:
Database [postgres]:
Port [5432]:
Username [postgres]:
postgres 사용자의 암호:
psql (11.10)
도움말을 보려면 "help"를 입력하십시오.

postgres=#

Server [localhost]:

Database [postgres]:

Port [5432]:

Username [postgres]:

postgres 사용자의 암호:

psql (11.10)

도움말을 보려면 "help"를 입력하십시오.

postgres=#

3. show

[C#/COMMON/.NETCORE] Npgsql 누겟 설치하기

■ Npgsql 누겟을 설치하는 방법을 보여준다. 1. Visual Studio를 실행한다. 2. [도구] / [NuGet 패키지 관리자] / [패키지 관리자 콘솔] 메뉴를 실행한다.

[DATABASE/POSTGRESQL] ALTER TABLE … OWNER TO 명령 : 테이블 소유자 변경하기

■ ALTER TABLE … OWNER TO 명령을 사용해 테이블 소유자를 변경하는 방법을 보여준다. ▶ 예제 코드 (SQL)


ALTER TABLE APPLICATION OWNER TO POSTGRES;
            -----------          --------
            테이블명             계정

ALTER TABLE APPLICATION OWNER TO POSTGRES;

----------- --------

테이블명 계정

[DATABASE/POSTGRESQL] ALTER TABLE … ALTER COLUMN … TYPE … 명령 : 컬럼 데이터 타입 변경하기

■ ALTER TABLE … ALTER COLUMN … TYPE … 명령을 사용해 컬럼 데이터 타입을 변경하는 방법을 보여준다. ▶ 예제 코드 (SQL)


ALTER TABLE APPLICATION ALTER COLUMN FTP_SERVER_ID TYPE VARCHAR;
            -----------              -------------      -------
            테이블명                 컬럼명             변경 데이터 타입

ALTER TABLE APPLICATION ALTER COLUMN FTP_SERVER_ID TYPE VARCHAR;

----------- ------------- -------

테이블명 컬럼명 변경 데이터 타입

[태그:] POSTGRESQL

[DATABASE/POSTGRESQL] DATE_PART 함수 : 날짜 값에서 월 구하기

[DATABASE/POSTGRESQL] EXTRACT 함수 : 날짜 값에서 요일 구하기 (일요일-토요일)

[DATABASE/POSTGRESQL] EXTRACT 함수 : 날짜 값에서 월 구하기

[DATABASE/POSTGRESQL] 윈도우즈에서 디폴트 데이터 디렉토리(PGDATA) 이동하기

[DATABASE/POSTGRESQL] REGEXP_REPLACE 함수 : 정규식을 사용해 문자열에서 모든 공백 제거하기

[DATABASE/POSTGRESQL] CAST 함수 : 데이터 타입 변환하기

[DATABASE/POSTGRESQL] SET STATEMENT_TIMEOUT TO 명령 : 쿼리 실행시 타임아웃 시간 설정하기

[C#/COMMON/IBATIS] ISqlMapper 인터페이스 : QueryForObject 메소드를 사용해 데이터 행 수 구하기

[C#/COMMON/IBATIS] iBatis를 사용해 PostgreSQL 데이터베이스 액세스하기

[DATABASE/POSTGRESQL] “SSL 중지 연결에 대한 설정이 pg_hba.conf 파일에 없습니다” 오류시 조치 사항

[DATABASE/POSTGRESQL] COPY 명령 : CSV 파일 데이터를 테이블에 복사하기

[DATABASE/POSTGRESQL] COPY 명령 : 테이블 데이터를 CSV 파일로 복사하기

[DATABASE/POSTGRESQL] INSERT ON CONFLICT 명령 : 추가 또는 수정하기

[DATABASE/POSTGRESQL] INSERT ON CONFLICT 명령 : 제약 조건 위반시 무시하기

[DATABASE/POSTGRESQL] INSERT ON CONFLICT 명령 : 제약 조건 위반시 무시하기

[DATABASE/POSTGRESQL] ROW_NUMBER 함수 : 행 번호 표시하기

[DATABASE/POSTGRESQL] COALESCE 함수 : 특정 값이 NULL인 경우 지정 값 반환하기

[C#/WEB API/.NET5] 대용량 데이터를 스트리밍 방식으로 전달받기 (PostgreSQL 연동)

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

[C#/ASP.NET MVC/.NET5] Npgsql.EntityFrameworkCore.PostgreSQL.Design 누겟 설치하기

[C#/ASP.NET MVC/.NET5] Npgsql.EntityFrameworkCore.PostgreSQL 누겟 설치하기

[DATABASE/POSTGRESQL] 데이터 디렉토리 변경하기

[C#/COMMON/.NETCORE] Npgsql 누겟 설치하기

[DATABASE/POSTGRESQL] ALTER TABLE … OWNER TO 명령 : 테이블 소유자 변경하기

[DATABASE/POSTGRESQL] ALTER TABLE … ALTER COLUMN … TYPE … 명령 : 컬럼 데이터 타입 변경하기