[C#] Firebird 사용하기

C#을 공부하고 있는데 Firebird를 사용해보고 싶어 Firebird .Net Provider 사용에 대한 테스트를 해 보았다.

C#은 참 사용하기 쉽게 만든 언어 같다. Firebird를 C++에서 사용하기 위해 IBPP 나 Firebird C API를 사용해서 Firebird를 이용할 때에는 무척이나 복잡했었다.

Firebird C++ Wrapper도 .Net Provider 처럼 사용하기 어렵지 않은 라이브러리가 있었으면 좋겠다.


C#에서의 테스트는 데이버베이스를 생성하고 테이블을 만들어 Insert, Update 등의 테스트를 하고 테이블과 데이터베이스를 삭제하여 보았다.


아래 코드는 테스트에 사용된 코드이다.

txtResult.Clear();
txtResult.AppendText("Begin Testing...\n");

string connectionString =
    "User=SYSDBA;" +
    "Password=masterkey;" +
    "Database=F:\FirebirdDB\    estdb.fdb;" +
    "DataSource=localhost;" +
    "Charset=UTF8;" +
    "ServerType=0"; // Type: 0 - Server, 1 - Embedded

try
{
    // Create Database
    FbConnection.CreateDatabase(connectionString);
    txtResult.AppendText("Create Database...OK\n");

    // Create Connection object
    FbConnection conFirebird = new FbConnection(connectionString);
    txtResult.AppendText("Create Connection Object...OK["
        + conFirebird.State.ToString() + " : "
        + FbConnection.ConnectionPoolsCount.ToString()
        + "]\n");

    // Connect
    conFirebird.Open();
    txtResult.AppendText("Open Connection...OK[" 
        + conFirebird.State.ToString() + " : "
        + FbConnection.ConnectionPoolsCount.ToString()
        + "]\n");

    FbCommand cmdSQL = new FbCommand();
    cmdSQL.Connection = conFirebird;

    // Create Table
    string strQuery = "CREATE TABLE Member"
        + "("
        + " ID      VARCHAR(20) NOT NULL,"
        + " NAME    VARCHAR(20) NOT NULL,"
        + " ALIAS   VARCHAR(20),"
        + " AGE   INTEGER"
        + ")";

    cmdSQL.CommandText = strQuery;
    cmdSQL.ExecuteNonQuery();
    txtResult.AppendText("Create test Table...OK\n");

    // Insert Data
    FbTransaction transInsert = conFirebird.BeginTransaction();
    cmdSQL.Transaction = transInsert;
    for (int i = 0; i < 5; i++)
    {
        cmdSQL.CommandText = "INSERT INTO Member(ID, NAME, ALIAS, AGE) "
            + "VALUES ("
            + "  'Man" + i.ToString() + "' "
            + " ,'Name" + i.ToString() + "' "
            + " ,'Alias" + i.ToString() + "' "
            + " ," + (i + 10).ToString()
            + ");";
        int nRow = cmdSQL.ExecuteNonQuery();
        txtResult.AppendText("    Insert " + nRow.ToString() + "row(s)...OK\n");
    }
    transInsert.Commit();
    transInsert.Dispose();
    cmdSQL.Transaction = null;
    txtResult.AppendText("    Insertion committed...OK\n");
    txtResult.AppendText("Insert data...OK\n");

    // Select Data
    FbDataAdapter da = new FbDataAdapter("SELECT * FROM Member", conFirebird);
    DataTable dt = new DataTable();
    da.Fill(dt);
    ArrayList lstColumn = new ArrayList();
    foreach (DataColumn col in dt.Columns)
    {
        txtResult.AppendText(col.ColumnName + "    ");
        lstColumn.Add(col.ColumnName);
    }
    txtResult.AppendText("\n");
    foreach (DataRow row in dt.Rows)
    {
        txtResult.AppendText(row[lstColumn[0] as string] + "    "
            + row[lstColumn[1] as string] + "    "
            + row[lstColumn[2] as string] + "    "
            + row[lstColumn[3] as string] + "    "
            + "\n"
            );
    }
    dt.Clear();

    // Update some data
    FbTransaction transUpdate = conFirebird.BeginTransaction();
    txtResult.AppendText("UPDATE Member SET Age = Age + 20 WHERE Age > 11;\n");
    cmdSQL.CommandText = "UPDATE Member SET Age = Age + 20 WHERE Age > 11;";
    cmdSQL.Transaction = transUpdate;
    int nUpdate = cmdSQL.ExecuteNonQuery();
    transUpdate.Commit();
    transUpdate.Dispose();
    cmdSQL.Transaction = null;
    txtResult.AppendText("    " + nUpdate.ToString() + "row(s) updated!\n");
    txtResult.AppendText("Update data...OK\n");

    // Select Data
    da.SelectCommand.CommandText = "SELECT * FROM Member";
    da.Fill(dt);
    foreach (DataColumn col in dt.Columns)
    {
        txtResult.AppendText(col.ColumnName + "    ");
    }
    txtResult.AppendText("\n");
    foreach (DataRow row in dt.Rows)
    {
        txtResult.AppendText(row["ID"] + "    "
            + row["NAME"] + "    "
            + row["ALIAS"] + "    "
            + row["AGE"] + "    "
            + "\n"
            );
    }
    dt.Clear();
    da.Dispose();

    // Delete data
    cmdSQL.CommandText = "DELETE FROM Member;";
    cmdSQL.ExecuteNonQuery();
    txtResult.AppendText("Delete data...OK\n");

    // Drop Table
    cmdSQL.CommandText = "DROP TABLE Member;";
    cmdSQL.ExecuteNonQuery();
    txtResult.AppendText("Drop test Table...OK\n");

    // Close
    conFirebird.Close();
    txtResult.AppendText("Close Connection...OK["
        + conFirebird.State.ToString() + " : "
        + FbConnection.ConnectionPoolsCount.ToString()
        + "]\n");

    // Clear Pool
    FbConnection.ClearPool(conFirebird);
    txtResult.AppendText("Clear Connection pool...OK["
        + conFirebird.State.ToString() + " : "
        + FbConnection.ConnectionPoolsCount.ToString()
        + "]\n");

    // Drop Database
    FbConnection.DropDatabase(connectionString);
    txtResult.AppendText("Drop Database...OK\n");
}
catch (FbException ex)
{
    //MessageBox.Show("Occured FbException \n" + ex.ToString());
    txtResult.AppendText("Occured FbException => Error code : "
      + ex.ErrorCode.ToString() + "\n");
    txtResult.AppendText("-------------------------------------------------\n");
    txtResult.AppendText(ex.ToString() + "\n");
    txtResult.AppendText("-------------------------------------------------\n");
}
catch (Exception ex)
{
    //MessageBox.Show("Occured Exception \n" + ex.ToString());
    txtResult.AppendText("Occured Exception \n");
    txtResult.AppendText("-------------------------------------------------\n");
    txtResult.AppendText(ex.ToString() + "\n");
    txtResult.AppendText("-------------------------------------------------\n");
}

txtResult.AppendText("End Testing.\n");

실행 결과:

Begin Testing...
Create Database...OK
Create Connection Object...OK[Closed : 0]
Open Connection...OK[Open : 1]
Create test Table...OK
    Insert 1row(s)...OK
    Insert 1row(s)...OK
    Insert 1row(s)...OK
    Insert 1row(s)...OK
    Insert 1row(s)...OK
    Insertion committed...OK
Insert data...OK
ID    NAME    ALIAS    AGE    
Man0    Name0    Alias0    10    
Man1    Name1    Alias1    11    
Man2    Name2    Alias2    12    
Man3    Name3    Alias3    13    
Man4    Name4    Alias4    14    
UPDATE Member SET Age = Age + 20 WHERE Age > 11;
    3row(s) updated!
Update data...OK
ID    NAME    ALIAS    AGE    
Man0    Name0    Alias0    10    
Man1    Name1    Alias1    11    
Man2    Name2    Alias2    32    
Man3    Name3    Alias3    33    
Man4    Name4    Alias4    34    
Delete data...OK
Drop test Table...OK
Close Connection...OK[Closed : 1]
Clear Connection pool...OK[Closed : 0]
Drop Database...OK
End Testing.
<

Original Post : http://neodreamer-dev.tistory.com/579