概要

この記事では、IBM DB2アプリケーション開発の効率化にDataDirect Connect for .NET DB2 Data Providerを使用し、ADO.NET での典型的なデータアクセスタスクを例示したC# のソースコードを記述します。

例示コードの動作にはサンプルのテーブルが必要です。また、環境にあわせて接続文字列の修正と、プロジェクトで System.Data と DDTek.DB2 に対してusingディレクティブを指定ください。

using System.Data; using DDTek.DB2;

コードで利用するテーブル

この記事中全てのコード例は、emp 、dept テーブルを使用するので、DB2コマンドセンターまたはDB2 Data Providerで作成ください。

DB2コマンドセンターでサンプルテーブルを作成するには、スクリプトウィンドウで次のコードを実行します。

CONNECT TO <databasename> USER <username>
USING <password>
CREATE TABLE emp (
empno INT PRIMARY KEY NOT NULL,
ename VARCHAR(10) NOT NULL,
job VARCHAR(9) NOT NULL,
mgr INT,
hiredate DATE NOT NULL,
sal NUMERIC(7,2) NOT NULL,
comm NUMERIC(7,2),
dept INT NOT NULL)
insert into emp values
    (1,'JOHNSON','ADMIN',6,'12-17-1990',18000,NULL,4)
insert into emp values
    (2,'HARDING','MANAGER',9,'02-02-1998',52000,300,3)
insert into emp values
    (3,'TAFT','SALES I',2,'01-02-1996',25000,500,3)
insert into emp values
    (4,'HOOVER','SALES I',2,'04-02-1990',27000,NULL,3)
insert into emp values
    (5,'LINCOLN','TECH',6,'06-23-1994',22500,1400,4)
insert into emp values
    (6,'GARFIELD','MANAGER',9,'05-01-1993',54000,NULL,4)
insert into emp values
    (7,'POLK','TECH',6,'09-22-1997',25000,NULL,4)
insert into emp values
    (8,'GRANT','ENGINEER',10,'03-30-1997',32000,NULL,2)
insert into emp values
    (9,'JACKSON','CEO',NULL,'01-01-1990',75000,NULL,4)
insert into emp values
    (10,'FILLMORE','MANAGER',9, '08-09-1994',56000,NULL,2)
insert into emp values
    (11,'ADAMS','ENGINEER',10,'03-15-1996',34000,NULL,2)
insert into emp values
    (12,'WASHINGTON','ADMIN',6,'04-16-1998',18000,NULL,4)
insert into emp values
    (13,'MONROE','ENGINEER',10, '12-03-2000',30000,NULL,2)
insert into emp values
    (14,'ROOSEVELT','CPA',9,'10-12-1995',35000,NULL,1)
;
;
CREATE TABLE dept (
deptno INT NOT NULL,
dname VARCHAR(14),
loc VARCHAR(13))
insert into dept values (1,'ACCOUNTING','ST LOUIS')
insert into dept values (2,'RESEARCH','NEW YORK')
insert into dept values (3,'SALES','ATLANTA')
insert into dept values (4, 'OPERATIONS','SEATTLE')

次のコードで、サンプルテーブルをDB2 Data Providerで作成します。

Conn = new DB2Connection
    ("host=ncphantom;port=50000;User ID=TEST01;
Password=TEST01;Database=test");
try {
Conn.Open();
Console.WriteLine ("Connection successful!");
}
catch (DB2Exception ex) {
// Connection failed Console.WriteLine(ex.Message); return;
}
string[] DropTableSQL = {"drop table emp", "drop table dept"};
for (int x=0; x<=1; x++) {
try {
// Drop the tables, don't care if they don't exist
DB2Command DBCmd = new DB2Command(DropTableSQL[x], Conn);
DBCmd.ExecuteNonQuery();
}
catch (DB2Exception ex) { }
}
// Create the tables
string CreateEmpTableSQL = "CREATE TABLE emp (
empno INT PRIMARY KEY NOT NULL," +"ename VARCHAR(10) NOT NULL,"+ "
job VARCHAR(9) NOT NULL, " +"mgr INT," +"hiredate DATE NOT NULL,"
+ "sal NUMERIC(7,2) NOT NULL," +"comm NUMERIC(7,2)," +"
dept INT NOT NULL)";
string CreateDeptTableSQL = "CREATE TABLE dept (" +
"deptno INT NOT NULL," +"dname VARCHAR(14)," +
"loc VARCHAR(13))";
try {
DB2Command DBCmd = new DB2Command(CreateEmpTableSQL, Conn);
DBCmd.ExecuteNonQuery();
DBCmd.CommandText = CreateDeptTableSQL;
DBCmd.ExecuteNonQuery();
}
catch (Exception ex) {
//Create tables failed Console.WriteLine (ex.Message); return; }
// Now insert the records
string[] InsertEmpRecordsSQL = {
"insert into emp values
    (1,'JOHNSON','ADMIN',6,'12-17-1990',18000,NULL,4)",
"insert into emp values
    (2,'HARDING','MANAGER',9,'02-02-1998',52000,300,3)",
"insert into emp values
    (3,'TAFT','SALES I',2,'01-02-1996',25000,500,3)",
"insert into emp values
    (4,'HOOVER','SALES I',2,'04-02-1990',27000,NULL,3)",
"insert into emp values
    (5,'LINCOLN','TECH',6,'06-23-1994',22500,1400,4)",
"insert into emp values
    (6,'GARFIELD','MANAGER',9,'05-01-1993',54000,NULL,4)",
"insert into emp values
    (7,'POLK','TECH',6,'09-22-1997',25000,NULL,4)",
"insert into emp values
    (8,'GRANT','ENGINEER',10,'03-30-1997',32000,NULL,2)",
"insert into emp values
    (9,'JACKSON','CEO',NULL,'01-01-1990',75000,NULL,4)",
"insert into emp values
    (10,'FILLMORE','MANAGER',9,'08-09-1994',56000,NULL,2)",
"insert into emp values
    (11,'ADAMS','ENGINEER',10,'03-15-1996',34000,NULL,2)",
"insert into emp values
    (12,'WASHINGTON','ADMIN',6,'04-16-1998',18000,NULL,4)",
"insert into emp values
    (13,'MONROE','ENGINEER',10,'12-03-2000',30000,NULL,2)",
"insert into emp values
    (14,'ROOSEVELT','CPA',9, '10-12-1995',35000,NULL,1)"};
string[] InsertDeptRecordsSQL = {
"insert into dept values (1,'ACCOUNTING','ST LOUIS')",
"insert into dept values (2,'RESEARCH','NEW YORK')",
"insert into dept values (3,'SALES','ATLANTA')",
"insert into dept values (4, 'OPERATIONS','SEATTLE')"};
// Insert dept table records first
for (int x = 0; x < InsertDeptRecordsSQL.Length; x++){
try {
DB2Command DBCmd = new DB2Command(InsertDeptRecordsSQL[x], Conn);
DBCmd.ExecuteNonQuery();
}
catch (Exception ex) {
Console.WriteLine (ex.Message); return;
}
}
// Now the emp table records
for (int x = 0; x < InsertEmpRecordsSQL.Length; x++) {
try {
DB2Command DBCmd = new DB2Command(InsertEmpRecordsSQL[x], Conn);
DBCmd.ExecuteNonQuery();
}
catch (Exception ex) {
Console.WriteLine (ex.Message); return;
}
}
// Close the connection
Conn.Close();

DataReaderでデータを取得する

DataReader は最も速くデータベースからデータを取得できますが、最も自由度が低い方法です。データはリードオンリーで、一方通行のストリームデータとして1回につき1レコードが返されます。

多数の複数レコードを高速に取得する必要がある場合は、DataReader はDataSet より少メモリーで済みます。DataSet は結果の保持に多くのメモリーを必要とします。

次に、DB2で簡単なクエリーを実行し、DataReader でクエリ結果の読み取り方を示します。

// Open connection to DB2 database
DB2Connection Conn;
Conn = new DB2Connection
("host=ncphantom;port=50000;User ID=TEST01;
Password=TEST01;Database=test");
try {
Conn.Open();
Console.WriteLine ("Connection successful!");
}
catch (DB2Exception ex) {
// Connection failed Console.WriteLine(ex.Message);
return;
}
// Create a SQL command
string strSQL = "SELECT ename FROM emp WHERE sal>50000";
DB2Command DBCmd = new DB2Command(strSQL, Conn);
DB2DataReader myDataReader;
myDataReader = DBCmd.ExecuteReader();
while (myDataReader.Read()) {
Console.WriteLine("High salaries: "
+ myDataReader["ename"].ToString());
}
myDataReader.Close();
// Close the connection
Conn.Close();

ローカルトランスレーションコード例

次に、ローカル・トランザクションの使い方を示します。

DB2Connection DBConn;
DBConn = new DB2Connection
    ("host=ncphantom;port=50000;User ID=TEST01;
Password=TEST01;Database=test");
DB2Command DBCmd = new DB2Command();
DB2Transaction DBTxn = null;
try {
DBConn.Open();
DBTxn = DBConn.BeginTransaction();
// Set the Connection property of the Command object
DBCmd.Connection = DBConn;
// Set the text of the Command to the INSERT statement
DBCmd.CommandText =
"insert into emp VALUES
    (15,'HAYES','ADMIN',6, {d'2002-04-17'},18000,NULL,4)";
// Set the transaction property of the Command object
DBCmd.Transaction = DBTxn;
// Execute the statement with ExecuteNonQuery, because we are
// not returning results DBCmd.ExecuteNonQuery();
// Now commit the transaction
DBTxn.Commit();
} catch (Exception ex) {
// Display any exceptions Console.WriteLine (ex.Message);
// If anything failed after the connection was opened,
// roll back the transaction
if (DBTxn != null) {
DBTxn.Rollback();
}
}
// Close the connection
DBConn.Close();

CommandBuilder コード例

DB2のempテーブルに対し、CommandBuilder でコードを作成します。

DB2Connection DBConn;
DBConn = new DB2Connection
("host=ncphantom;port=50000; User ID=TEST01;
Password=TEST01;Database=test");
DB2DataAdapter myDataAdapter = new DB2DataAdapter();
DB2Command DBCmd = new DB2Command("select * from emp",DBConn);
myDataAdapter.SelectCommand = DBCmd;
// Set up the CommandBuilder
DB2CommandBuilder CommBuild =
new DB2CommandBuilder(myDataAdapter);
DataSet myDataSet = new DataSet();
try {
DBConn.Open();
myDataAdapter.Fill(myDataSet);
// Now change the salary of the first employee
DataRow myRow;
myRow = myDataSet.Tables["Table"].Rows[0]; myRow["sal"] = 95000;
// Tell the DataAdapter to resync with the DB2 server.
// Without the CommandBuilder, this line would fail.
myDataAdapter.Update(myDataSet);
} catch (Exception ex) {
// Display any exceptions
Console.WriteLine (ex.Message);
}
// Close the connection
DBConn.Close();

DataSetでDB2のデータを更新する

行を更新する場合、DataSet はデータアダプタのUpdateCommand で提供されるSQLを使用します。

以下のように、Update 文はプライマリ―キーや更新したい列などのユニークな識別子を含むパラメータを使用できます。

[C#]

string updateSQL As String =
"UPDATE emp SET sal = ?, job = ? + = WHERE empno = ?;

パラメータクエリでは作成時のパラメータを定義します。DB2 Data Providerのパラメータについてはこちらを参照ください。次のコード例は、Parameters.Add メソッドを使用し、Update 文のパラメータを作成し、DataSet に入力し、プログラムでDataSet を変更し、変更をデータベースに書き込み同期します。

DB2Connection con = new DB2Connection("host=ncphantom;port=50000;
User ID=TEST01;Password=TEST01;Database=test");
try {
string selectText = "select sal, job, empno from emp";
string updateText =
    "update emp set sal = ?, job = ? where empno = ?";
DB2DataAdapter adapter = new DB2DataAdapter(selectText, con);
DB2Command updateCommand = new DB2Command(updateText, con);
adapter.UpdateCommand = updateCommand;
updateCommand.Parameters.Add("sal", DB2DbType.Decimal, 7, "sal");
updateCommand.Parameters.Add("job", DB2DbType.VarChar, 9, "job");
updateCommand.Parameters.Add("empno", DB2DbType.Integer,
    15, "empno");
DataSet myDataSet = new DataSet("emp");
adapter.Fill(myDataSet, "emp");
// Give employee number 12 a promotion and a raise
DataRow changeRow = myDataSet.Tables["emp"].Rows[11];
changeRow["sal"] = "35000";
changeRow["job"] = "MANAGER";
// Send back to database
adapter.Update(myDataSet, "emp");
myDataSet.Dispose();
} catch (Exception ex) {
// Display any exceptions
Console.WriteLine (ex.Message); }
// Close the connection con.Close();

DB2 のストアドプロシージャを呼び出す

Command オブジェクトを使用して、ストアドプロシージャを呼び出します。ストアドプロシージャを呼び出すには、Command オブジェクトのCommandType にストアドプロシージャをセットするか、ODBC/JDBCエスケープ文字を使用します。

以下はCommand オブジェクトのCommandType にストアドプロシージャをセットし、DB2でストアドプロシージャを実行。ストアドプロシージャの出力パラメータの値を取得する例です（要empテーブル）。

ストアドプロシージャの作成

Data ProviderまたはDB2コマンドセンターのいずれかで、次のストアドプロシージャを作成できます。

Data Providerでストアドプロシージャを作成するには、次のコードを実行します。

{ DB2Connection Conn = new DB2Connection
    ("host=ncphantom;port=50000;User ID=TEST01;
Password=TEST01;Database=test");
try {
Conn.Open();
Console.WriteLine ("Connection successful!");
} catch (DB2Exception ex) {
// Connection failed
Console.WriteLine(ex.Message);
return;
}
string DropProcSQL = "DROP PROCEDURE GetEmpSalary";
try {
DB2Command DBCmd = new DB2Command(DropProcSQL, Conn);
DBCmd.ExecuteNonQuery();
} catch (Exception ex) {
// Do nothing. Don't care if the drop fails.
}
string CreateProcSQL =
"CREATE PROCEDURE GetEmpSalary (in inempno int ,out outsal char(7))"
+" Language SQL reads SQL data BEGIN " +" SELECT CAST(sal AS CHAR(7))
INTO outsal from emp where empno = inempno;" +"END";
try {
DB2Command DBCmd = new DB2Command(CreateProcSQL, Conn);
DBCmd.ExecuteNonQuery();
} catch (Exception ex) {
//Create procedure failed
Console.WriteLine (ex.Message);
return;
} }

ストアドプロシージャの実行

DB2コマンドセンターで次のSQLを実行します。

CREATE PROCEDURE GetEmpSalary (in inempno int ,out outsal char(7)) Language SQL reads SQL data SELECT char(sal) INTO outsal from emp where empno = inempno Executing the stored procedure

以下はData ProviderでGetEmpSalaryストアドプロシージャを実行し、出力パラメータの値を取得する例です。

// Open connection to DB2 database
DB2Connection Conn;
Conn = new DB2Connection
("host=ncphantom;port=50000;User ID=TEST01;Password=TEST01;Database=test");
try { Conn.Open();
Console.WriteLine ("Connection successful!");
} catch (DB2Exception ex) {
// Connection failed Console.WriteLine(ex.Message);
return;
}
// Make a command object for the stored procedure
// You can set the CommandType of the Command object
// to StoredProcedure or use escape syntax
DB2Command DBCmd = new DB2Command("GetEmpSalary",Conn);
DBCmd.CommandType = CommandType.StoredProcedure;
DBCmd.Parameters.Add("inempno", DB2DbType.Integer, 7, "inempno");
DBCmd.Parameters[0].Value = 1;
DBCmd.Parameters.Add("outsal", DB2DbType.Char, 7, "outsal");
DBCmd.Parameters[1].Direction = ParameterDirection.Output;
DB2DataReader myReader;
try {
myReader = DBCmd.ExecuteReader();
while (myReader.Read()){}
Console.WriteLine
    ("Salary: " + Convert.ToString(DBCmd.Parameters[1].Value));
} catch (Exception ex) {
// Display any exceptions
Console.WriteLine (ex.Message);
}
// Close the connection
Conn.Close();
}

スカラ値を取得するコード例

Command オブジェクトのExecuteScalar メソッドを使用して、sum やcount など1件の値をデータベースから返すことができます。ExecuteScalar メソッドは、result set の最初の行における最初の列を返します。Result set に1行、1列しかないことが既に判明している場合は、このメソッドで値取得のスピードを上げられます。
以下はempテーブルから指定されたグループの件数を取得する例です。

// Open connection to DB2 database
DB2Connection DBConn;
DBConn = new DB2Connection("host=ncphantom;port=50000;User ID=TEST01;
Password=TEST01;Database=test");
try {
DBConn.Open();
Console.WriteLine ("Connection successful!");
} catch (DB2Exception ex) {
// Connection failed
Console.WriteLine(ex.Message);
return;
}
// Make a command object
DB2Command salCmd = new DB2Command("select count(sal) from emp
where sal>50000",DBConn);
try {
int count = (int)salCmd.ExecuteScalar();
Console.WriteLine("Count of Salaries >$50,000 : "
+ Convert.ToString(count));
} catch (Exception ex) {
// Display any exceptions Console.WriteLine (ex.Message);
}
// Close the connection
DBConn.Close();
}

参考記事
・Autonomous REST Connector

・SAS/ACCESS Interface to JDBC

・SAS/ACCESSソフトウェア

・yelp Developers

・Get started with the Yelp Fusion API

以上