DB2 Version 10.1 for Linux, UNIX, and Windows

DB2Parameter Class

Represents a parameter to a DB2Command and, optionally, its mapping to a DataColumn.

Namespace:: IBM®.Data.DB2®
Assembly:: IBM.Data.DB2 (in IBM.Data.DB2.dll)

.NET Framework 2.0, 3.0, 3.5, and 4.0 inheritance hierarchy

System.Object
   System.MarshalByRefObject
      System.Data.Common.DbParameter
         IBM.Data.DB2.DB2Parameter

.NET Framework 2.0, 3.0, 3.5, and 4.0 syntax

[Visual Basic]
NotInheritable Public Class DB2Parameter
   Inherits DbParameter
   Implements IDbDataParameter, IDataParameter, ICloneable
[C#]
public sealed class DB2Parameter : DbParameter,
   IDbDataParameter, IDataParameter, ICloneable
[C++]
public __gc __sealed class DB2Parameter : public
   DbParameter, IDbDataParameter, IDataParameter, ICloneable
[JScript]
public class DB2Parameter extends DbParameter implements
   IDbDataParameter, IDataParameter, ICloneable

Remarks

You can assign two types of values to parameters:

Values that you assign to a DB2Type enumerated type
Values in IBM.Data.DB2Types namespace classes and structures

The parameter names are not case sensitive.

Example

[Visual Basic, C#] The following examples create multiple instances of the DB2Parameter class by using the DB2ParameterCollection within the DB2DataAdapter. These parameters are used to select data from the database and place the data in the DataSet. It is assumed that a DataSet and a DB2DataAdapter have already been created with the appropriate schema, commands, and connection.

[Visual Basic]
Public Sub 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)
End Sub 'AddDB2Parameters

[C#]
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);
 }

The following example demonstrates the use of string literals in DATE columns. You can use a similar method for TIME and TIMESTAMP columns.

[Visual Basic]
Public Sub InsertDateDB2Parameters()
    ' ...
    ' create DB2Connection myConn
    ' ...
    Dim myStatement As String = "INSERT INTO myTable (dateCol1, dateCol2, dateCol3) 
values (?, ?, ?)"
    Dim myCommand As New DB2Command(myStatement, myConnection)
    Dim param1 As New DB2Parameter("dateCol1", DB2Type.Date) ' 
Date for DATE column dateCol1
    param1.Value = DateTime.Parse("06/23/1982 08:52:43 -5:00")
    Dim param2 As New DB2Parameter("dateCol2", DB2Type.VarChar) 	' 
VarChar for DATE column dateCol2
    param2.Value = "1982-06-23-8:52:43.000000"
    Dim param3 As New DB2Parameter("dateCol3", "1982-06-23-8:52:43.000000")
  ' String for DATE column dateCol3
    myCommand.Parameters.Add(param1))  ' added parameter of type Date
    myCommand.Parameters.Add(param2))  ' added parameter of type VarChar
    myCommand.Parameters.Add(param3))  ' added parameter of type String
    myCommand.ExecuteNonQuery()
End Sub ' InsertDateDB2Parameters

[C#]
public void InsertDateDB2Parameters()
 {
 // ...
 // create DB2Connection myConn
 // ...
   DB2Command myCommand = myConnection.CreateCommand();
   myCommand.CommandText = "INSERT INTO myTable (dateCol1, dateCol2, dateCol3)
 values (?, ?, ?)";
   DB2Parameter param1 = new DB2Parameter("dateCol1", DB2Type.Date);
	// Date for DATE column 'dateCol1'
   param1.Value = DateTime.Parse("06/23/1982 08:52:43 -5:00");
   DB2Parameter param2 = new DB2Parameter("dateCol2", DB2Type.VarChar);	
 // VarChar for DATE column 'dateCol2'
   param2.Value = "1982-06-23-8:52:43.000000";
   DB2Parameter param3 = new DB2Parameter("dateCol3", "1982-06-23-8:52:43.000000");
 // String for DATE column 'dateCol3';
   myCommand.Parameters.Add(param1));  // added parameter of type Date
   myCommand.Parameters.Add(param2));  // added parameter of type VarChar
   myCommand.Parameters.Add(param3));  // added parameter of type String
   myCommand.ExecuteNonQuery();

Assuming that the procedure signature of addEmp looks like this:

CREATE PROCEDURE addEMP(

IN empNo INTEGER DEFAULT 100,
IN empName VARCHAR(20) DEFAULT 'nothing',
INOUT empDeptNo	INTEGER DEFAULT 2,
IN empAddr VARCHAR(100)DEFAULT 'San Jose, CA'
) ...

The addEmp procedure is then called by using a named argument along with its value in a CALL statement:

CALL addEmp (empNo => 5, empName => 'John', empDeptNo =>
2, empAddr => 'San Jose, CA')

Query limitation

You cannot use a positioned parameter marker (?) and a named parameter marker (for example, : paramEmpAddr) in the same query.

Thread safety

Public static (Shared in Visual Basic) members of this type are safe for multithreaded operations. Instance members are not guaranteed to be thread safe.

Version information

Last update: This topic was last updated for IBM DB2 Version 9.7, Fix Pack 2.
.NET Framework version: Supported in 2.0, 3.0, 3.5, and 4.0
IBM Data Server Client: Supported in IBM DB2 for Linux, UNIX, and Windows, Version 8.1.2, or later