[C#] Firebird 사용하기

2011. 9. 22. 19:21

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\\testdb.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("\tInsert " + nRow.ToString() + "row(s)...OK\n");
    }
    transInsert.Commit();
    transInsert.Dispose();
    cmdSQL.Transaction = null;
    txtResult.AppendText("\tInsertion 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 + "\t");
        lstColumn.Add(col.ColumnName);
    }
    txtResult.AppendText("\n");
    foreach (DataRow row in dt.Rows)
    {
        txtResult.AppendText(row[lstColumn[0] as string] + "\t"
            + row[lstColumn[1] as string] + "\t"
            + row[lstColumn[2] as string] + "\t"
            + row[lstColumn[3] as string] + "\t"
            + "\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("\t" + 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 + "\t");
    }
    txtResult.AppendText("\n");
    foreach (DataRow row in dt.Rows)
    {
        txtResult.AppendText(row["ID"] + "\t"
            + row["NAME"] + "\t"
            + row["ALIAS"] + "\t"
            + row["AGE"] + "\t"
            + "\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");

실행 결과:

저작자표시 비영리

'Dev Story > Tips' 카테고리의 다른 글

[C#] Firebird Embedded로 사용하기 (0)	2011.09.25
[C#] Firebird 연결문자열을 FbConnectionStringBuilder를 이용하여 만들기 (0)	2011.09.22
[C#] Delay (0)	2011.09.22
[WPF] StatusBar 프로그램 하단에 배치하기 (0)	2011.09.20
[C#] 텍스트 파일 인코딩 정보 알아내기 (1)	2011.08.26