DB2Command.ArrayBindCount Property

Syntax

[Visual Basic]
Public Property ArrayBindCount As Integer
[C#]
public int ArrayBindCount { get; set;}
[C++]
public: __property int get_ArrayBindCount();
public: __property void set_ArrayBindCount(int);
[JScript]
public function get ArrayBindCount() : int;
public function set ArrayBindCount(int);

Property value

An integer value that specifies the number of entries in the array to be bound as a parameter. Default setting is 0.

Remarks

When specifying multiple rows of data in a single parameter for each column using an array, DB2Command.ArrayBindCount property must be explicitly set to the number of entries in the array to be bound as a parameter. If the array that is being added to the parameter collection have fewer elements than the elements specified by ArrayBindCount property, the missing elements are assumed to have NULL values. If no values are set for the array that are being added to the parameter collection, NULL value is assumed for every array element. Multidimensional arrays are not supported and an exception would be thrown if multidimensional array is specified.

Once the ArrayBindCount property is explicitly set, the only way to reset the behavior back to non-array mode is either by setting the property back to the default value of 0, or by changing the DB2Command.CommandText property.

Example

[C#] The following example perform multi-row insert using the ArrayBindCount property

[C#]
public static void MyArrayBindSample(String ConnStr)
{
    Console.WriteLine(ConnStr);

    DB2Connection conn = new DB2Connection(ConnStr);
    conn.Open();

    object[] myArrayC1 = new object[3] { 10, NULL, 30 };
    object[] myArrayC2 = new object[3] { DB2Parameter.Default, "test", DB2Parameter.Unassigned };
    int[] myArrayC3 = new int[2] = { 1, 2 };

    DB2Command cmd = conn.CreateCommand();
    cmd.CommandText = "INSERT INTO T1 ( C1, C2, C3, C4 ) values ( ?, ?, ?, ? )";

    cmd.ArrayBindCount = 3;

    DB2Parameter Parm1 =  new DB2Parameter();
    Parm1.DB2Type = DB2Type.Integer;
    Parm1.Direction = ParameterDirection.Input;
    Parm1.Value = myArrayC1;
    cmd.Parameters.Add(Parm1);

    DB2Parameter Parm2 = new DB2Parameter();
    Parm2.Direction = ParameterDirection.Input;
    Parm2.Value = myArrayC2;		 
    cmd.Parameters.Add(Parm2);        //See note 1

    DB2Parameter Parm3 = new DB2Parameter();
    Parm3.DB2Type = DB2Type.Integer;
    Parm3.Direction = ParameterDirection.Input;
    Parm3.Value = myArrayC3;          //See note 2
    cmd.Parameters.Add(Parm3);

    DB2Parameter Parm4 = new DB2Parameter();
    Parm4.DB2Type = DB2Type.Char;
    Parm4.Direction = ParameterDirection.Input;
    cmd.Parameters.Add(Parm4);        //See note 3

    cmd.ExecuteNonQuery();

    conn.Close();
    conn.Dispose();
}