DB2Command.Parameters Property

Namespace:

IBM.Data.DB2

Assembly:

IBM.Data.DB2 (in IBM.Data.DB2.dll)

Syntax

[Visual Basic]
Public ReadOnly Property Parameters As DB2ParameterCollection
[C#]
public DB2ParameterCollection Parameters {get;}
[C++]
public: __property DB2ParameterCollection * get_Parameters();
[JScript]
public function get Parameters() : DB2ParameterCollection;

Property value

The parameters of the SQL statement or stored procedure. The default is an empty collection.

Remarks

When passing parameters to SQL statements or stored procedures called by a DB2®Command, the IBM® Data Server Provider for .NET supports host variables, named parameters, and positioned parameters using parameter markers. You cannot use a combination of host variables, named parameters or positioned parameters in the same SQL statement.

Host variables

When using host variables in a DB2ParameterCollection, specify the name of the parameter object in the SQL statement. For example:

SELECT * FROM EMPLOYEE 
  WHERE FIRSTNME = :firstname 
  AND LASTNAME = :lastname 
  AND WORKDEPT = :workdept

Parameter names are case-insensitive, must be prefixed by the symbol ':', and can be made up of any symbol that can be used as part of an SQL identifier. For details regarding SQL identifiers, see the topic: Identifiers in the DB2 Information Center.

Support for host variables, prefixed by a colon ':', is disabled by default. To enable host variable support the HostVarParameters property must be set to TRUE in the connection string.

Named parameters

When using named parameters in a DB2ParameterCollection, specify the name of the parameter object in the SQL statement. For example:

SELECT * FROM EMPLOYEE 
  WHERE FIRSTNME = @firstname 
  AND LASTNAME = @lastname 
  AND WORKDEPT = @workdept

Parameter names are case-insensitive, must be prefixed by the symbol '@', and can be made up of any symbol that can be used as part of an SQL identifier. For details regarding SQL identifiers, see the topic: Identifiers in the DB2 Information Center.

Positioned Parameters

When using positioned parameters, use the question mark (?) parameter marker. For example:

SELECT * FROM EMPLOYEE WHERE EMPNO = ?

The order in which DB2Parameter objects are added to the DB2ParameterCollection must directly correspond to the position of the question mark placeholder for the parameter in the command text.

Note: If the parameters in the collection do not match the requirements of the query to be executed, an error may result.

Example

[Visual Basic, C#] The following examples create a DB2Command and displays its parameters. To accomplish this, the method is passed a DB2Connection , a query string that is an SQL SELECT statement, and an array of DB2Parameter objects. For these examples, this array contains a single DB2Parameter object, with the name "@param1".

[Visual Basic]
'Using host variables
Public Sub CreateMyDB2Command(myConnection As DB2Connection, _
 mySelectQuery As String, myParamArray() As DB2Parameter)
     Dim myCommand As New DB2Command(mySelectQuery, myConnection)
     myCommand.CommandText = "SELECT ID, NAME FROM STAFF WHERE ID = :param1"
     myCommand.Parameters.Add(myParamArray)
     Dim j As Integer
     For j = 0 To myCommand.Parameters.Count - 1
        myCommand.Parameters.Add(myParamArray(j))
     Next j
     Dim myMessage As String = ""
     Dim i As Integer
     For i = 0 To myCommand.Parameters.Count - 1
         myMessage += myCommand.Parameters(i).ToString() + ControlChars.Cr
     Next i
     Console.WriteLine(myMessage)
 End Sub

'Using named parameters
Public Sub CreateMyDB2Command(myConnection As DB2Connection, _
 mySelectQuery As String, myParamArray() As DB2Parameter)
     Dim myCommand As New DB2Command(mySelectQuery, myConnection)
     myCommand.CommandText = "SELECT ID, NAME FROM STAFF WHERE ID = @param1"
     myCommand.Parameters.Add(myParamArray)
     Dim j As Integer
     For j = 0 To myCommand.Parameters.Count - 1
        myCommand.Parameters.Add(myParamArray(j))
     Next j
     Dim myMessage As String = ""
     Dim i As Integer
     For i = 0 To myCommand.Parameters.Count - 1
         myMessage += myCommand.Parameters(i).ToString() + ControlChars.Cr
     Next i
     Console.WriteLine(myMessage)
 End Sub

'Using positioned parameters
Public Sub CreateMyDB2Command(myConnection As DB2Connection, _
 mySelectQuery As String, myParamArray() As DB2Parameter)
     Dim myCommand As New DB2Command(mySelectQuery, myConnection)
     myCommand.CommandText = "SELECT ID, NAME FROM STAFF WHERE ID = ?"
     myCommand.Parameters.Add(myParamArray)
     Dim j As Integer
     For j = 0 To myCommand.Parameters.Count - 1
        myCommand.Parameters.Add(myParamArray(j))
     Next j
     Dim myMessage As String = ""
     Dim i As Integer
     For i = 0 To myCommand.Parameters.Count - 1
         myMessage += myCommand.Parameters(i).ToString() + ControlChars.Cr
     Next i
     Console.WriteLine(myMessage)
 End Sub


[C#]
//Using host variables
public void CreateMyDB2Command(DB2Connection myConnection,
 string mySelectQuery, DB2Parameter[] myParamArray) {
    // assume myConnection has HostVarParameters set to TRUE
    DB2Command myCommand = new DB2Command(mySelectQuery, myConnection);
    myCommand.CommandText = "SELECT ID, NAME FROM STAFF WHERE ID = :param1";
    myCommand.Parameters.Add(myParamArray);
    for (int j=0; j<myParamArray.Length; j++)
    {
       myCommand.Parameters.Add(myParamArray[j]) ;
    }
    string myMessage = "";
    for (int i = 0; i < myCommand.Parameters.Count; i++)
    {
       myMessage += myCommand.Parameters[i].ToString() + "\n";
    }
    MessageBox.Show(myMessage);
 }

//Using named parameters
public void CreateMyDB2Command(DB2Connection myConnection,
 string mySelectQuery, DB2Parameter[] myParamArray) {
    DB2Command myCommand = new DB2Command(mySelectQuery, myConnection);
    myCommand.CommandText = "SELECT ID, NAME FROM STAFF WHERE ID = @param1";
    myCommand.Parameters.Add(myParamArray);
    for (int j=0; j<myParamArray.Length; j++)
    {
       myCommand.Parameters.Add(myParamArray[j]) ;
    }
    string myMessage = "";
    for (int i = 0; i < myCommand.Parameters.Count; i++)
    {
       myMessage += myCommand.Parameters[i].ToString() + "\n";
    }
    MessageBox.Show(myMessage);
 }

//Using positioned parameters
public void CreateMyDB2Command(DB2Connection myConnection,
 string mySelectQuery, DB2Parameter[] myParamArray) {
    DB2Command myCommand = new DB2Command(mySelectQuery, myConnection);
    myCommand.CommandText = "SELECT ID, NAME FROM STAFF WHERE ID = ?";
    myCommand.Parameters.Add(myParamArray);
    for (int j=0; j<myParamArray.Length; j++)
    {
       myCommand.Parameters.Add(myParamArray[j]) ;
    }
    string myMessage = "";
    for (int i = 0; i < myCommand.Parameters.Count; i++)
    {
       myMessage += myCommand.Parameters[i].ToString() + "\n";
    }
    MessageBox.Show(myMessage);
 }