DB2 Version 9.7 for Linux, UNIX, and Windows

Executing SQL statements from an application using the IBM Data Server Provider for .NET

When using the IBM Data Server Provider for .NET, the execution of SQL statements is done through a DB2Command class using its methods ExecuteReader() and ExecuteNonQuery(), and its properties CommandText, CommandType and Transaction.

About this task

For SQL statements that produce output, the ExecuteReader() method should be used and its results can be retrieved from a DB2DataReader object. For all other SQL statements, the method ExecuteNonQuery() should be used. The Transaction property of the DB2Command object should be initialized to a DB2Transaction object. A DB2Transaction object is responsible for rolling back and committing database transactions.

Executing an UPDATE statement in C#:

// assume a DB2Connection conn
DB2Command cmd = conn.CreateCommand();
DB2Transaction trans = conn.BeginTransaction();
cmd.Transaction = trans;
cmd.CommandText = "UPDATE staff " +
                  "  SET salary = (SELECT MIN(salary) " +
                  "                  FROM staff " +
		  "                  WHERE id >= 310) " +
                  "  WHERE id = 310";

cmd.ExecuteNonQuery();

Executing an UPDATE statement in Visual Basic .NET:

' assume a DB2Connection conn
DB2Command cmd = conn.CreateCommand();
DB2Transaction trans = conn.BeginTransaction();
cmd.Transaction = trans;
cmd.CommandText = "UPDATE staff " +
                  "  SET salary = (SELECT MIN(salary) " +
                  "                  FROM staff " +
		  "                  WHERE id >= 310) " +
                  "  WHERE id = 310";
cmd.ExecuteNonQuery();

Executing a SELECT statement in C#:

// assume a DB2Connection conn
DB2Command cmd = conn.CreateCommand();
DB2Transaction trans = conn.BeginTransaction();
cmd.Transaction = trans;
cmd.CommandText = "SELECT deptnumb, location " +
                  "  FROM org " +
                  "  WHERE deptnumb < 25";

DB2DataReader reader = cmd.ExecuteReader();

Executing a SELECT statement in Visual Basic .NET:

' assume a DB2Connection conn
Dim cmd As DB2Command = conn.CreateCommand()
Dim trans As DB2Transaction = conn.BeginTransaction()
cmd.Transaction = trans
cmd.CommandText = "UPDATE staff " +
                  "  SET salary = (SELECT MIN(salary) " +
                  "                  FROM staff " +
		  "                  WHERE id >= 310) " +
                  "  WHERE id = 310";

cmd.ExecuteNonQuery()

After your application performs a database transaction, you must either roll it back or commit it. This is done through the Commit() and Rollback() methods of a DB2Transaction object.

Rolling back or committing a transaction in C#:

// assume a DB2Transaction object conn
trans.Rollback();
...
trans.Commit();

Rolling back or committing a transaction in Visual Basic.NET:

' assume a DB2Transaction object conn
trans.Rollback()
...
trans.Commit()

In Version 9.7 Fix Pack 2 and later fix packs, DB2® for Linux, UNIX, and Windows servers support returning result sets from the execution of anonymous blocks. In Version 9.7 Fix Pack 6 and later fix packs, the .NET data provider supports a application to retrieve the result sets from execution of anonymous blocks by using DB2DataReader or DB2ResultSet classes. For the .NET data provider to retrieve the result sets from anonymous block execution, the database server must support PL/SQL statements and the database must be enabled to process PL/SQL statements. The .NET data provider must declare cursors for the results sets that are returned from anonymous block execution by using the BEGIN statement, and not theBEGIN COMPOUND statement.

Retrieving a single result set from execution of anonymous block by using the DB2DataReader class in C#:

...
cmd.CommandText = "begin " +
                  "declare cursor1 cursor with return to client with hold for select c1 from t1; " + 
                  "open cursor1; " + 
                  "end;";
//Returns a result set by opened cursor cursor1
DB2DataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
    //Process read data if required
}
dr.Close();
...

Retrieving a single result set from execution of anonymous block by using the DB2ResultSet class in C#:

...
cmd.CommandText = "begin " +
                  "declare cursor1 cursor with return to client with hold for select c1 from t1; " + 
                  "open cursor1; " + 
                  "end;";
//Returns a result set by opened cursor cursor1
DB2ResultSet ds = cmd.ExecuteResultSet(DB2CursorType.ForwardOnly);
while (ds.Read())
{
    //Process read data if required
}
ds.Close();
...

Retrieving multiple result sets from execution of anonymous block by using the DB2DataReader class in C#:

...
cmd.CommandText = " begin " + 
                  "declare cursor1 cursor with return to client with hold for select c1 from t1; " + 
                  "declare cursor2 cursor with return to client for select c2 from t2; " +
                  "open cursor1; " + 
                  "open cursor2; " +
                  "end;";
//Returns multiple result sets by opened cursors
DB2DataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
    //Process read data if required from cursor1
}
dr.NextResult(); //Get next result set
while (dr.Read())
{
    //Process read data if required from cursor2
}
dr.Close();
...

Retrieving multiple result sets from execution of anonymous block by using the DB2ResultSet class in C#:

...
cmd.CommandText = " begin " + 
                  "declare cursor1 cursor with return to client with hold for select c1 from t1; " + 
                  "declare cursor2 cursor with return to client for select c2 from t2; " +
                  "open cursor1; " + 
                  "open cursor2; " +
                  "end;";
//Returns multiple result sets by opened cursors
DB2ResultSet ds = cmd.ExecuteResultSet(DB2CursorType.ForwardOnly);
while (ds.Read())
{
    //Process read data if required from cursor1
}
ds.NextResult(); //Get next result set
while (ds.Read())
{
    //Process read data if required from cursor2
}
ds.Close();
...