Skill in exeucting SQL statements and working with the results

[Note]

DB2Parameter

DB2Parameter class represents a parameter to a DB2Command and, optionally, its mapping to a DataColumn.

Parameter names are NOT case-sensitive.

Parameters can be assigned two kinds of values:

The following C# example creates multiple instances of DB2Parameter through the DB2ParameterCollection within the DB2DataAdapter. These parameters are used to select data from the database and place the data in the DataSet. This example assumes that a DataSet and a DB2DataAdapter have already been created with the appropriate schema, commands, and connection:

public void AddDB2Parameters()
{
   // ...
   // create myDataSet and myDataAdapter
   // ...
   myDataAdapter.SelectCommand.Parameters.Add("CategoryName", DB2Type.VarChar, 80).Value = "toasters";
   myDataAdapter.SelectCommand.Parameters.Add("SerialNum", DB2Type.Integer).Value = 239;
   myDataAdapter.Fill(myDataSet);
}
					

When using named parameters (prefixed with @) or host variables (prefixed with :) in a DB2ParameterCollection, you must set ParameterName before executing a DB2Command that relies on parameters. If positioned parameters are used, any parameter names will be ignored during parameter object binding.

Parameter marker names are case-insensitive, must be prefixed by the symbol '@'or by a colon ':', and can be made up of any symbol that can be used as part of an SQL identifier. Using more than one type of parameter marker within the same statement is not supported. This means that a statement using positioned parameters cannot contain named parameters or host variables. Likewise, a statement using named parameters cannot contain host variables or positioned parameters.

The following example assumes that the database has a table name MyTable and a stored procedure named MyProc, that is defined as:

create table MyTable (col1 int, col2 double, col3 decimal)
create proc MyProc (p1 int, p2 double, p3 decimal) language sql LABEL1: 
    begin insert into MyTable values (p1, p2, p3); end
					
The C# example creates parameters and calls the MyProc stored procedure:
public void CallMyProc()
{
    // Create and open DB2Connection myConnection.
    DB2Command myCommand = myConnection.CreateCommand();
    myCommand.CommandText = "call MyProc(@param1,@param2,@param3)";
    Dim p1 As New DB2Parameter();
    p1.ParameterName = "@param1";
    p1.DB2Type = DB2Type.Integer;
    p1.Value = 1;
    Dim p2 As New DB2Parameter();
    p2.ParameterName = "@param2";
    p2.DB2Type = DB2Type.Double;
    p2.Value = 2;
    Dim p3 = new DB2Parameter();
    p3.ParameterName = "@param3";
    p3.DB2Type = DB2Type.Decimal;
    p3.Value = 3;
    // Add parameters to the myCommand.Parameters collection
    // and then execute the command.
}
					

DB2DataReader

DB2DataReader class provides a way of reading a forward-only stream of data rows from a database.

To create a DB2DataReader, you must call the DB2Command.ExecuteReader method of the DB2Command object, rather than directly using a constructor.

You can concurrently access data from multiple DB2DataReader instances that use the same DB2Connection instance. Each DB2DataReader instance must be associated with its own DB2Command instance.

Changes made to a result set by another process or thread while data is being read may be visible to the user of the DB2DataReader. However, the precise behavior is timing dependent.

If your application needs to scroll through result sets in multiple directions, or insert, update, and delete rows, you can use a DB2ResultSet instance.

IsClosed and RecordsAffected are the only properties that you can call after the DB2DataReader is closed. In some cases, you must call Close before you can call RecordsAffected.

The following C# example creates a DB2Connection, a DB2Command, and a DB2DataReader. The example reads through the data, writing it out to the console. Finally, the example closes the DB2DataReader, then the DB2Connection:

public void ReadMyData(string myConnString) {
   string mySelectQuery = "SELECT SALES, SALES_PERSON FROM SALES";
   DB2Connection myConnection = new DB2Connection(myConnString);
   DB2Command myCommand = new DB2Command(mySelectQuery,myConnection);
   myConnection.Open();
   DB2DataReader myReader;
   myReader = myCommand.ExecuteReader();
   // Always call Read before accessing data.
   while (myReader.Read()) {
      Console.WriteLine(myReader.GetInt32(0) + ", " + myReader.GetString(1));
   }
   // always call Close when done reading.
   myReader.Close();
   // Close the connection when done with it.
   myConnection.Close();
}
					

Professional hosting     Belorussian informational portal         Free SCWCD 1.4 Study Guide     Free SCDJWS 1.4 Study Guide     SCDJWS 1.4 Quiz     Free IBM Certified Associate Developer Study Guide     IBM Test 000-287. Enterprise Application Development with IBM WebSphere Studio, V5.0 Study Guide     SCDJWS 5.0 Quiz