DB 매개변수 관리¶

Fox DB Access 는 DB 매개변수를 설정하고 관리하기 위해 FoxDbParamCollection 추상 클래스와 그 파생 클래스들을 제공합니다. FoxDbAccess 추상 클래스와 그 파생 클래스들과의 관계와 비슷하게 FoxDbParamCollection 추상 클래스는 데이터베이스 종류에 따라서 FoxNpgsqlParameterCollection, FoxOracleParameterCollection 등의 파생 클래스들을 갖습니다.

FoxSqlParameterCollection

MS SQL Server 의 DB 매개변수 설정을 위해 SqlParameter 객체들에 대한 컬렉션을 제공합니다. NeoDEEX.Data.SqlClient 패키지를 참조하면 사용할 수 있습니다.
FoxOracleDbAccess

Oracle 의 DB 매개변수 설정을 위해 OracleParameter 객체들에 대한 컬렉션을 제공합니다. NeoDEEX.Data.OracleClient 패키지를 참조하면 사용할 수 있습니다.
FoxNpgsqlDbAccess

PostgreSQL 의 DB 매개변수 설정을 위해 NpgsqlParameter 객체들에 대한 컬렉션을 제공합니다. NeoDEEX.Data.NpgsqlClient 패키지를 참조하면 사용할 수 있습니다.
FoxMySqlDbAccess

MySQL 의 DB 매개변수 설정을 위해 MysqlParameter 객체들에 대한 컬렉션을 제공합니다. 이 클래스를 사용하기 위해서는 NeoDEEX.Data.MySqlClient 패키지를 참조해야 합니다.
FoxOdbcDbAccess

ODBC 의 DB 매개변수 설정을 위해 OdbcParameter 객체들에 대한 컬렉션을 제공합니다. 이 클래스를 사용하기 위해서는 NeoDEEX.Data.Odbc 패키지를 참조해야 합니다.

이 문서와 관련된 예제 코드는 다음 예제를 참조 하십시요.

Fox Db Access Overview 예제 의 db_parameters 프로젝트

Why FoxDbParamCollection?¶

전통적인 데이터액세스 코드에서 DB 매개변수 컬렉션은 Command 객체의 Parameters 속성을 사용해야 합니다. 이 속성 타입은 데이터베이스 종류에 따라 SqlParameterCollection, OracleParameterCollection, NpgsqlParameterCollection 등 입니다. DB 매개변수 컬렉션 타입들은 공용 생성자(public constructor)를 제공하지 않기 때문에 DB 매개변수 컬렉션을 생성하기 위해서는 반드시 Command 객체를 생성해야 합니다. DB 매개변수 객체(SqlParameter, OracleParameter, NpgsqlParameter 등)가 특정 Command 객체에 바인드(bind) 되고 다른 Command 객체에 재사용할 수 없기 때문에 매우 당연한 작동방식 처럼 보입니다. 하지만 데이터액세스 코드를 작성하다보면 특정 Command 에 종속된 DB 매개변수 객체가 불편한 경우가 많습니다.

예를 들어, 2 건의 데이터를 INSERT 하는 전통적인 데이터액세스 코드는 다음과 같습니다. 동일한 SQL 문장을 사용하므로 하나의 Command 객체를 생성하고 DB 매개변수 값을 2회 지정하면 됩니다. 이때 첫번째 INSERT 에서 사용한 DB 매개변수는 이미 이전 수행에 바인드되었기 때문에 재사용이 불가합니다. 따라서 Clear 메서드를 호출하여 컬렉션에서 기존 DB 매개변수들을 제거하고 새로운 DB 매개변수를 추가해 주어야 합니다. 만약 스키마가 변경되어 컬럼 타입이 바뀐다면 아주 많은 코드를 수정해야 할 수도 있습니다.

using NpgsqlConnection conn = new("... your connection string ...");
string query = "INSERT INTO my_demo_table VALUES(:id, :str, :int)";
using NpgsqlCommand cmd = new(query, conn);
cmd.Parameters.AddWithValue("id", NpgsqlDbType.Integer, 97);
cmd.Parameters.AddWithValue("str", NpgsqlDbType.Varchar, "str97");
cmd.Parameters.AddWithValue("int", NpgsqlDbType.Integer, 97);
conn.Open();
try
{
    cmd.ExecuteNonQuery();
    cmd.Parameters.Clear();
    cmd.Parameters.AddWithValue("id", NpgsqlDbType.Integer, 98);
    cmd.Parameters.AddWithValue("str", NpgsqlDbType.Varchar, "str98");
    cmd.Parameters.AddWithValue("int", NpgsqlDbType.Integer, 98);
    cmd.ExecuteNonQuery();
}
finally
{
    conn.Close();
}

Fox DB Access 를 사용한 데이터액세스 코드는 일반적으로 Command 객체를 직접 생성하지 않습니다. 대다수의 Execute- 메서드들은 내부적으로 Command 객체를 생성하므로 DB 매개변수 컬렉션에 접근하기 어렵습니다. 따라서 Fox DB Access 는 Command 객체에 독립적인 DB 매개변수 컬렉션으로 FoxDbParamCollection 을 제공합니다.

using FoxDbAccess dbAccess = FoxDbAccess.CreateDbAccess();
string query = "INSERT INTO my_demo_table VALUES(:id, :str, :int)";
FoxDbParameterCollection parameters = dbAccess.CreateParamCollection();
parameters.AddWithValue("id", DbType.Int32, 97);
parameters.AddWithValue("str", DbType.String, "str97");
parameters.AddWithValue("int", DbType.Int32, 97);
dbAccess.Open();
try
{
    dbAccess.ExecuteSqlNonQuery(query, parameters.Clone());
    parameters["id"]!.Value = 98;
    parameters["str"]!.Value = "str98";
    parameters["int"]!.Value = 98;
    dbAccess.ExecuteSqlNonQuery(query, parameters.Clone());
}
finally
{
    dbAccess.Close();
}

FoxDbParamCollection 추상 클래스는 데이터베이스에 의존성을 갖지 않기 때문에 단일 API 로 다양한 데이터액세스 코드를 작성할 수 있으며 Command 객체와 무관하게 FoxDbAccess.CreateParamCollection 메서드를 호출하여 컬렉션 생성이 가능합니다. FoxDbParamCollection 컬렉션에 추가된 DB 매개변수 객체는 바인딩이 아직 되지 않았기 때문에 유연성이 높습니다. Clone 메서드를 통해 DB 매개변수 컬렉션을 복제할 수 있기 때문에 한번의 설정으로 여러 Command 에 적용할 수도 있습니다. 이외에도 null 값을 DBNull.Value 값으로 자동으로 변환해주며 DB 매개변수 이름 중복 확인 등의 기능을 추가로 제공합니다.

FoxDbParamCollection 객체 생성 및 DB 매개변수 설정¶

FoxDbAcces 클래스는 CreateParamCollection 메서드를 제공합니다. 이 메서드는 데이터베이스 종류에 따라 FoxSqlParameterCollection, FoxOracleParameterCollection, FoxNpgsqlParameterCollection 등 구체 클래스(concrete class)의 인스턴스를 생성하여 반환합니다. 하지만 데이터베이스 독립성을 유지하기 위해 반환 타입은 FoxDbParamCollection 입니다.

using FoxDbAccess dbAccess = FoxDbAccess.CreateDbAccess(connName);
FoxDbParameterCollection parameters = dbAccess.CreateParamCollection();
Console.WriteLine($"parameters type: {parameters.GetType().FullName}");
// result output:
// parameters type: NeoDEEX.Data.NpgsqlClient.FoxNpgsqlParameterCollection

FoxDbParamCollection 컬렉션(객체)에 DB 매개변수를 추가하기 위해서는 Add 메서드 혹은 AddWithValue 메서드를 사용할 수 있습니다. 이들 메서드에 의해 추가되는 DB 매개변수 객체는 데이터베이스에 따라 SqlParameter, OracleParameter, NpgsqlParameter 등의 구체적인 클래스 객체 입니다.

Add 메서드 : DB 매개변수 이름, DB 타입, 길이 등의 정보를 설정하여 객체를 생성하고 컬렉션에 추가하지만 DB 매개변수의 값을 설정하지 않습니다.
AddWithValue : DB 매개변수 이름, DB 타입, 길이 등의 정보와 더불어 매개변수 값까지 설정하여 객체를 생성하고 컬렉션에 추가 합니다.

Information

MS-SQL 과 PostgreSQL 은 FoxDbParamCollection 과 동일하게 Add 메서드와 AddWithValue 메서드를 구별하여 제공하지만 Oracle 은 둘을 구분하지 않고 Add 메서드 만을 제공합니다. FoxDbParamCollection 를 사용하면 데이터베이스에 관계 없이 일관된 방법으로 DB 매개변수를 추가하는 코드를 작성할 수 있습니다.

FoxDbParamCollection 컬렉션에 포함된 DB 매개변수 객체(SqlParameter, OracleParameter, NpgsqlParameter 등)는 아직 특정 Command 객체에 바인드되지 않은 상태이므로 임의의 Command 객체에 사용할 수 있습니다. 하지만 Command 객체에 할당되어 사용된 이후에는 재사용이 불가합니다. 예를 들어 동일한 쿼리에 여러 매개변수 값을 적용하고자 하는 경우를 생각해 봅시다. FoxDbParamCollection 컬렉션을 생성하고 DB 매개변수 설정을 수행한 후 첫번째 쿼리를 수행하고 동일한 DB 매개변수 설정하에서 값만을 변경하여 두번째 쿼리를 수행하면 InvalidOperationException 예외를 유발합니다.

using FoxDbAccess dbAccess = FoxDbAccess.CreateDbAccess(connName);
FoxDbParameterCollection parameters = dbAccess.CreateParamCollection();
parameters.AddWithValue("product_id", 5);
dbAccess.ExecuteSqlNonQuery("UPDATE ....", parameters);
parameters["product_id"].Value = 6;
dbAccess.ExecuteSqlNonQuery("UPDATE ....", parameters);
// result output:
// Unhandled exception. System.InvalidOperationException: The parameter already belongs to a collection
//   at Npgsql.ThrowHelper.ThrowInvalidOperationException(String message)

FoxDbParamCollection 클래스는 동일한 DB 매개변수 설정을 사용하지만 매개변수 값만 변경하여 사용할 수 있도록 Clone 메서드를 제공합니다. 이 메서드는 DB 매개변수 컬렉션을 복제하여 새로운 컬렉션을 가진 FoxDbParamCollection 객체를 반환해 줍니다. 이렇게 복제된 DB 매개변수 컬렉션을 사용하면 동일한 설정에 값만 변경하여 여러 쿼리에 재사용이 가능합니다.

using FoxDbAccess dbAccess = FoxDbAccess.CreateDbAccess(connName);
FoxDbParameterCollection parameters = dbAccess.CreateParamCollection();
parameters.AddWithValue("product_id", 5);
dbAccess.ExecuteSqlNonQuery("UPDATE ....", parameters.Clone());
parameters["product_id"].Value = 6;
dbAccess.ExecuteSqlNonQuery("UPDATE ....", parameters);

Note

위 예제에서 마지막 Execute- 메서드 호출에 Clone 을 호출하지 않았음에 주목하십시요. 더 이상 재활용이 될 필요가 없는 경우에는 메모리 낭비를 줄이기 위해 Clone 호출을 하지 않는 것이 좋습니다.

DBNull.Value 변환 ¶

.net 환경의 null 과 데이터베이스의 NULL 은 그 의미가 같지 않습니다. 따라서 데이터베이스 액세스 코드에서 NULL 값은 DBNull.Value 이라는 상수 값이 사용됩니다. 예를 들어 NULL 을 INSERT 하는 Npgsql 기반의 전통적인 코드에서 null 값을 사용하면 InvalidOperationException 이 발생합니다. 따라서 DB 매개변수의 값을 명시적으로 DBNull.Value 로 지정해 주어야 합니다. Web API 와 같은 환경에서 클라이언트는 API 를 호출할 때 null 값을 사용할 것이므로 데이터액세스 코드는 null 값을 확인하고 DBNull.Value 값으로 전환해야 할 수도 있습니다.

using NpgsqlCommand cmd = new(query, conn);
cmd.Parameters.AddWithValue("id", NpgsqlDbType.Integer, 97);
// null 을 사용하면 InvaldOperationException 이 발생함
cmd.Parameters.AddWithValue("str", NpgsqlDbType.Varchar, DBNull.Value);
cmd.Parameters.AddWithValue("int", NpgsqlDbType.Integer, 97);

Information

DBNull.Value 가 아닌 null 을 DB 매개변수 값으로 사용이 허용되는 경우도 있습니다. SQL Server 의 저장 프로시저는 optional 매개변수를 가질 수 있습니다. 닷넷 클라이언트는 optional 매개변수에 대해 null 값을 사용함으로써 저장 프로시저의 optional 매개변수의 디폴트 값을 사용하도록 지시합니다. DB 매개변수의 값으로 null 은 데이터베이스마다 그 용도가 다르므로 해당 데이터베이스의 데이터 프로바이더 관련 문서를 참고하십시요.

FoxDbParamColleciton 클래스는 DB 매개변수에 null 값이 전달되면 자동으로 DBNull.Value 로 전환해주는 기능을 제공합니다. 이 기능은 기본적으로 활성화되어 있으며 FoxDbParamCollection 의 생성자 매개변수나 UseDBNull 속성을 통해 제어가 가능합니다.

FoxDbParameterCollection parameters = dbAccess.CreateParamCollection();
parameters.UseDBNull = false;

FoxDbParamCollection 클래스가 null 값을 DBNull.Value 값으로 변환하는 시점은 Add/AddWithValue 메서드가 호출되어 DB 매개변수가 컬렉션에 추가될 때 입니다.

FoxDbParameterCollection parameters = dbAccess.CreateParamCollection();
parameters.Add(new NpgsqlParameter("p0", null));    // DBNull.Value 로 변환 수행
parameters.AddWithValue("p1", null);                // DBNull.Value 로 변환 수행

SetNullToDBNull 메서드는 수동으로 모든 DB 매개변수의 null 값을 DBNull.Value 값으로 변환합니다. 일반적으로 UseDBNull 속성을 false 로 지정하여 DB 매개변수들을 구성하고 일괄적으로 null 을 DBNull.Value 로 변경할 때 사용합니다.

FoxDbParameterCollection parameters = dbAccess.CreateParamCollection(false);
parameters.AddWithValue("p0", 1);
parameters.AddWithValue("p1", null);
parameters.AddWithValue("p2", null);
parameters.SetNullToDBNull();

중복 매개변수 확인¶

Command.Parameters 속성을 통해 제공되는 DB 매개변수 컬렉션은 Dictionary 와 달리 DB 매개변수 객체를 구분하지 않습니다. 즉, 동일한 이름을 가진 DB 매개변수를 추가할 수 있습니다. 이는 ODBC 와 같이 DB 매개변수 이름을 사용하지 않는 데이터 프로바이더도 존재하기 때문입니다.

using NpgsqlCommand cmd = new(query, conn);
cmd.Parameters.AddWithValue("id", 97);
cmd.Parameters.AddWithValue("str", "str97");
cmd.Parameters.AddWithValue("str", DBNull.Value);     // 어떤 예외도 발생하지 않음

하지만 대다수의 상황에서 매개변수 이름을 사용하여 DB 매개변수를 구분하는 것이 직관적이며 유지보수가 용이합니다. 특히 output 매개변수를 확인할 때 용이하며 새로운 매개변수가 추가되거나 기존 매개변수를 제거할할 때 이름 기반의 DB 매개변수는 오류 가능성을 크게 낮추어 줄 것입니다. ODBC 와 같이 DB 매개변수 이름을 사용하지 않는 경우, parameters[2] 와 같이 필연적으로 인덱스에 의존해야 하지만 매개변수가 추가되거나 제거될 때 인덱스가 변경되어야 할 것입니다.

Information

PostgreSQL 은 클라이언트가 쿼리를 수행할 때 매개변수로 이름 대신 $1, $2 와 같은 서수를 사용합니다. 하지만 데이터 프로바이더인 Npgsql 을 사용하면 DB 매개변수 이름을 사용할 수 있습니다. 이는 Npgsql 프로바이더가 내부적으로 SQL 문장에 사용된 DB 매개변수의 이름을 서수 형태로 자동으로 변환해 주기 때문입니다. 상세한 내용은 Npgsql Basic Usage 문서를 참고 하십시요.

FoxDbParamCollection 클래스는 기본적으로 DB 매개변수 이름을 사용하여 중복 매개변수가 존재하는지 확인합니다. 이 기능은 UseDuplicateCheck 속성을 사용하여 제어할 수 있습니다. 이 속성 값이 true 이면 동일한 이름을 가진 DB 매개변수가 컬렉션에 존재하면 예외(FoxDbException)가 발생합니다.

FoxDbParameterCollection parameters = dbAccess.CreateParamCollection();
parameters.AddWithValue("p0", 1);
parameters.AddWithValue("p1", null);
parameters.AddWithValue("p1", "some value");        // FoxDbException 발생

UseDuplicateCheck 속성이 false 인 경우, DB 매개변수의 구분은 수행되지 않습니다. DB 매개변수 이름을 사용하여 컬렉션을 검색하는 경우 해당 이름을 가진 첫번째 DB 매개변수를 찾습니다. 이러한 작동방식은 Command.Parameters 컬렉션의 작동방식과 동일합니다.

FoxDbParameterCollection parameters = dbAccess.CreateParamCollection();
parameters.AddWithValue("p0", 1);
parameters.AddWithValue("p1", 2);
parameters.AddWithValue("p1", 3);
Console.WriteLine(parameters["p1"].Value);
Console.WriteLine(parameters[2].Value);
// result output:
// 2
// 3

Summary¶

FoxDbParamCollection 추상 클래스와 그 파생 클래스들은 FoxDbAccess 를 통해 데이터베이스에 접근하는 코드에서 효율적이고 편리한 DB 매개변수 컬렉션 관리에 도움을 줍니다. FoxDbParamCollection 객체가 제공하는 DB 매개변수 컬렉션은 Execute- 메서드들을 호출하기 전까지는 Command 객체에 바인딩 되지 않기 때문에 재사용에 용이할 뿐만 아니라 null 값을 DBNull.Value 값으로 전환하는 기능과 DB 매개변수 이름 중복 확인 기능을 제공합니다.