DB2 10.5 for Linux, UNIX, and Windows

Issuing SQL statements from a .NET application

You can issue SQL statements through a DB2Command class with its methods ExecuteReader() and ExecuteNonQuery(), and its properties CommandText, CommandType, and Transaction.

About this task

For SQL statements that produce output, you can use the ExecuteReader() method, and retrieve the results from a DB2DataReader object. For all other SQL statements, you can use the ExecuteNonQuery() method. You can initialize the Transaction property of the DB2Command object to a DB2Transaction object. A DB2Transaction object is responsible for rolling back and committing database transactions.

Issuing 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 &gt;= 310) " +
                  "  WHERE id = 310";

cmd.ExecuteNonQuery();

Issuing 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();

Issuing 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 &lt 25";

DB2DataReader reader = cmd.ExecuteReader();

Issuing 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. The commit and rollback operation 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()

The .NET data provider supports an 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();
…