NOTE: This page is about data providers that work with the .NET Framework 1.1, see the DataDirect Connect for ADO.NET product page for information about data providers that support .NET Framework 2.0 and higher.
Since the release of our first ADO.NET managed data providers in June 2002, DataDirect Technologies has been fully committed to boosting operation performance in real-world environments such as DB2.
The material covered here provides code examples that can reduce application development time by providing working C# source code examples showing typical database access tasks in ADO.NET, using the DataDirect Connect for .NET DB2 data provider.
To ensure that these code examples work, you must create the sample tables. In addition, you must modify the connection strings in the examples to work in your environment. Finally, make sure your project includes the "using" directive for System.Data and DDTek.DB2:
using System.Data; using DDTek.DB2;All of the samples in this document use the emp and/or dept tables. You can create the tables in the DB2 command center or using the DB2 data provider.
Copy the following statements in the script window of the DB2 command center and execute them:
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')In the following code, you create the sample tables using the data provider. 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; } 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();
The DataReader provides the fastest but least flexible way to retrieve data from the database. Data is returned as a read-only, forward-only stream of data that is returned one record at a time.
If you need to retrieve many records rapidly, using a DataReader requires less memory than a DataSet, which needs to use large amounts of memory to hold the results.
The following code example, which uses the emp table, shows how to execute a simple query on a DB2 database and read the results using a 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();The following code example, which uses the emp table, shows how to use a local transaction:
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();The following code example uses the emp table with the CommandBuilder to create a SQL statement on DB2:
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();When updating a row at the data source, the DataSet uses the SQL provided in UpdateCommand of the Data Adapter.
The Update statement can use parameters that contain the unique identifier, such as the primary key, and the columns to be updated, as shown in the following example:
[C#] string updateSQL As String = "UPDATE emp SET sal = ?, job = ? + = WHERE empno = ?;The parameterized query statements define the parameters that will be created. Refer to the DataDirect Connect for .NET User's Guide and Reference for more information about using parameters with the DB2 data provider. The following code example uses the emp table to show how to provide an UpdateCommand to a DataAdapter for use in synchronizing changes made to a DataSet with the actual data on the DB2 server. The example uses the Parameters.Add method to create the parameters for the Update statement, fills a DataSet, programmatically makes changes to the DataSet, then synchronizes the changes back to the database.
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();
You call stored procedures using a Command object. When you issue a command on a stored procedure, you must either set the CommandType of the Command object to StoredProcedure, or use ODBC/JDBC escape syntax. For information on using the ODBC/JDBC escape syntax with the data provider, refer to the DataDirect Connect for .NETUser's Guide.
In the following example, you set the CommandType of the Command object to StoredProcedure as you execute a stored procedure on a DB2 database and retrieve the value of an output parameter to the stored procedure. This example requires the emp table.You can create the following stored procedure using either the data provider or the DB2 Command center.
To use the data provider to create the stored procedure, execute the following code:
{ 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; } }
Or, use the DB2 Command Center to create it by executing the following 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
The following code example executes the GetEmpSalary stored procedure using the data provider and retrieves the value of an output parameter:
// 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(); }You can use the ExecuteScalar method of the Command object to return a single value, such as a sum or a count, from the database. The ExecuteScalar method returns the value of the first column of the first row of the result set. If you know the result set has only one row and one column, you can use this method to speed up retrieval of the value.
The following code example shows how to retrieve the count of a specified group from the emp table:
// 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(); }