DB2Command.CommandText Property

Namespace:

IBM.Data.DB2

Assembly:

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

Syntax

[Visual Basic]
Public Property CommandText As String
[C#]
public string CommandText {get; set;}
[C++]
public: __property String* get_CommandText();
public: __property void set_CommandText(String*);
[JScript]
public function get CommandText() : String;
public function set CommandText(String);

Property value

The SQL statement, XQuery expression, or stored procedure to execute. The default value is an empty string ("").

Stored procedure remarks

When the CommandType property is set to StoredProcedure, the CommandText property should be set to the name of the stored procedure. Using CommandType.StoredProcedure is the recommended method of invoking a stored procedure.

A procedure is an executable object stored at the database. Generally, it is one or more SQL statements that have been precompiled. The syntax for calling a procedure with CommandType.Text is

{[?=]call procedure-name[([parameter][,[parameter]]...)]}

where procedure-name specifies the name of a procedure and parameter specifies a procedure parameter. The escape sequence (the curly brackets) must be specified when "?=" is used for the return parameter. Otherwise, the escape sequence is optional.

The command executes this stored procedure when you call one of the Execute methods (for example, ExecuteReader or ExecuteNonQuery).

SQL statement remarks

You cannot set the Connection, CommandType and CommandText properties if the current connection is performing an execute or fetch operation.

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

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

 DB2Command cmd = new DB2Command( "SELECT * FROM EMPLOYEE 
  WHERE LASTNAME = @lastname AND WORKDEPT = @workdept", conn );
 cmd.Parameters.Clear();
 cmd.Parameters.Add( "@workdept", DB2Type.SmallInt );
 cmd.Parameters.Add( "@lastname", DB2Type.VarChar, 15 );
 DB2Reader reader = cmd.ExecuteReader();

Parameter marker names are case-insensitive, must be prefixed by the symbol '@'or by a colon ':' for host variables, 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® data server documentation. Using more than one type of parameter within the same statement is not supported. This means that a statement using positioned parameter markers cannot contain named parameter markers or host variables. Likewise, a statement using named parameter markers cannot contain host variables or positioned parameter markers

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. Host variable support has been added for compatibility with applications that already contain them. Developing new applications with host variables is not recommended.

SELECT * FROM Customers WHERE CustomerID = ?

As a result, 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.

If a parameter contains a null value, the IBM Data Server Provider for .NET will bind that parameter as a null value. For example, the DB2ParameterCollection:

{1, null, 2}

passed into the CommandText property:

 call sp(?, ?, ?) 

results in the IBM Data Server Provider for .NET binding the first parameter to the value 1, the second parameter to the null value, and the third parameter to the value 2.

XQuery expression remarks

XQuery expressions in CommandText must be prefixed with the following characters: XQUERY

Example

[Visual Basic, C#] The following example creates a DB2Command and sets some of its properties.

[Visual Basic]
Public Sub CreateMyDB2Command()
    Dim myCommand As New DB2Command()
    myCommand.CommandText = "SELECT * FROM EMPLOYEE ORDER BY EMPNO"
    myCommand.CommandTimeout = 20
End Sub

[C#]
public void CreateMyDB2Command()
{
   DB2Command myCommand = new DB2Command();
   myCommand.CommandText = "SELECT * FROM EMPLOYEE ORDER BY EMPNO";
   myCommand.CommandTimeout = 20;
}

Data server restrictions

All data servers other than DB2 Version 9 and later

XQuery expressions are supported only on DB2 Version 9 and later.