OracleParameterCollection Class

An OracleParameterCollection class represents a collection of all parameters relevant to an OracleCommand object and their mappings to DataSet columns.

Class Inheritance

System.Object

  System.MarshalByRefObject

    System.Data.Common.DbParameterCollection

      Oracle.DataAccess.Client.OracleParameterCollection

Declaration

// C#
public sealed class OracleParameterCollection : DbParameterCollection, 
    IDataParameterCollection, IList, ICollection, IEnumerable

Requirements

Provider ODP.NET, Unmanaged Driver ODP.NET, Managed Driver
Assembly Oracle.DataAccess.dll Oracle.ManagedDataAccess.dll
Namespace Oracle.DataAccess.Client Oracle.ManagedDataAccess.Client
.NET Framework 3.5, 4.0, 4.5 4.0, 4.5

Thread Safety

All public static methods are thread-safe, although instance methods do not guarantee thread safety.

Remarks

The position of an OracleParameter added into the OracleParameterCollection is the binding position in the SQL statement. Position is 0-based and is used only for positional binding. If named binding is used, the position of an OracleParameter in the OracleParameterCollection is ignored.

Example

// C#
 
using System;
using System.Data;
using Oracle.DataAccess.Client; 
 
class OracleParameterCollectionSample
{
  static void Main()
  {
    string constr = "User Id=scott;Password=tiger;Data Source=oracle";
    OracleConnection con = new OracleConnection(constr);
    con.Open();
 
    OracleParameter[] prm = new OracleParameter[3];
 
    // Create OracleParameter objects through OracleParameterCollection
    OracleCommand cmd = con.CreateCommand();
 
    cmd.CommandText = "select max(empno) from emp";
    int maxno = int.Parse(cmd.ExecuteScalar().ToString());
 
    prm[0] = cmd.Parameters.Add("paramEmpno", OracleDbType.Decimal, 
      maxno + 10, ParameterDirection.Input);
    prm[1] = cmd.Parameters.Add("paramEname", OracleDbType.Varchar2, 
      "Client", ParameterDirection.Input);
    prm[2] = cmd.Parameters.Add("paramDeptNo", OracleDbType.Decimal, 
      10, ParameterDirection.Input);
    cmd.CommandText = 
      "insert into emp(empno, ename, deptno) values(:1, :2, :3)";
    cmd.ExecuteNonQuery();
 
    Console.WriteLine("Record for employee id {0} has been inserted.", 
      maxno + 10);
 
    // Remove all parameters from OracleParameterCollection
    cmd.Parameters.Clear();
 
    prm[0].Dispose();
    prm[1].Dispose();
    prm[2].Dispose();
    cmd.Dispose();
 
    con.Close();
    con.Dispose();
  }
}

OracleParameterCollection Members

OracleParameterCollection members are listed in the following tables.

OracleParameterCollection Static Methods

OracleParameterCollection static methods are listed in Table 6-103.

Table 6-103 OracleParameterCollection Static Methods

Method Description

Equals

Inherited from System.Object (Overloaded)


OracleParameterCollection Properties

OracleParameterCollection properties are listed in Table 6-104.

Table 6-104 OracleParameterCollection Properties

Property Description

Count

Specifies the number of OracleParameters in the collection

Item

Gets and sets the OracleParameter object (Overloaded)


OracleParameterCollection Public Methods

OracleParameterCollection public methods are listed in Table 6-105.

Table 6-105 OracleParameterCollection Public Methods

Public Method Description

Add

Adds objects to the collection (Overloaded)

AddRange

Adds elements to the end of the OracleParameterCollection

Clear

Removes all the OracleParameter objects from the collection

Contains

Indicates whether or not objects exist in the collection (Overloaded)

CopyTo

Copies OracleParameter objects from the collection, starting with the supplied index to the supplied array

CreateObjRef

Inherited from System.MarshalByRefObject

Equals

Inherited from System.Object (Overloaded)

GetHashCode

Inherited from System.Object

GetLifetimeService

Inherited from System.MarshalByRefObject

GetType

Inherited from System.Object

InitializeLifetimeService

Inherited from System.MarshalByRefObject

IndexOf

Returns the index of the objects in the collection (Overloaded)

Insert

Inserts the supplied OracleParameter to the collection at the specified index

Remove

Removes objects from the collection

RemoveAt

Removes objects from the collection by location (Overloaded)

ToString

Inherited from System.Object


OracleParameterCollection Static Methods

The OracleParameterCollection static method is listed in Table 6-106.

Table 6-106 OracleParameterCollection Static Method

Method Description

Equals

Inherited from System.Object (Overloaded)


OracleParameterCollection Properties

OracleParameterCollection properties are listed in Table 6-107.

Table 6-107 OracleParameterCollection Properties

Property Description

Count

Specifies the number of OracleParameters in the collection

Item

Gets and sets the OracleParameter object (Overloaded)


Count

This property specifies the number of OracleParameter objects in the collection.

Declaration

// C#
public override int Count {get;}

Property Value

The number of OracleParameter objects.

Implements

ICollection

Remarks

Default = 0

Item

Item gets and sets the OracleParameter object.

Overload List:

  • Item[int]

    This property gets and sets the OracleParameter object at the index specified by the supplied parameterIndex.

  • Item[string]

    This property gets and sets the OracleParameter object using the parameter name specified by the supplied parameterName.

Item[int]

This property gets and sets the OracleParameter object at the index specified by the supplied parameterIndex.

Declaration

// C#
public object Item[int parameterIndex] {get; set;}

Property Value

An object.

Implements

IList

Exceptions

IndexOutOfRangeException - The supplied index does not exist.

Remarks

The OracleParameterCollection class is a zero-based index.

Item[string]

This property gets and sets the OracleParameter object using the parameter name specified by the supplied parameterName.

Declaration

// C#
public OracleParameter Item[string parameterName] {get; set;};

Property Value

An OracleParameter.

Implements

IDataParameterCollection

Exceptions

IndexOutOfRangeException - The supplied parameter name does not exist.

OracleParameterCollection Public Methods

OracleParameterCollection public methods are listed in Table 6-108.

Table 6-108 OracleParameterCollection Public Methods

Public Method Description

Add

Adds objects to the collection (Overloaded)

AddRange

Adds elements to the end of the OracleParameterCollection

Clear

Removes all the OracleParameter objects from the collection

Contains

Indicates whether or not objects exist in the collection (Overloaded)

CopyTo

Copies OracleParameter objects from the collection, starting with the supplied index to the supplied array

CreateObjRef

Inherited from System.MarshalByRefObject

Equals

Inherited from System.Object (Overloaded)

GetHashCode

Inherited from System.Object

GetLifetimeService

Inherited from System.MarshalByRefObject

GetType

Inherited from System.Object

InitializeLifetimeService

Inherited from System.MarshalByRefObject

IndexOf

Returns the index of the objects in the collection (Overloaded)

Insert

Inserts the supplied OracleParameter to the collection at the specified index

Remove

Removes objects from the collection

RemoveAt

Removes objects from the collection by location (Overloaded)

ToString

Inherited from System.Object


Add

Add adds objects to the collection.

Overload List:

Add(object)

This method adds the supplied object to the collection.

Declaration

// C#
public override int Add(object obj);

Parameters

  • obj

    The supplied object.

Return Value

The index at which the new OracleParameter is added.

Implements

IList

Remarks

InvalidCastException - The supplied obj cannot be cast to an OracleParameter object.

Add(OracleParameter)

This method adds the supplied OracleParameter object to the collection.

Declaration

// C#
public OracleParameter Add(OracleParameter paramObj);

Parameters

  • paramObj

    The supplied OracleParameter object.

Return Value

The newly created OracleParameter object which was added to the collection.

Add(string, object)

This method adds an OracleParameter object to the collection using the supplied name and object value

Declaration

// C#
public OracleParameter Add(string name, object val);

Parameters

  • name

    The parameter name.

  • val

    The OracleParameter value.

Return Value

The newly created OracleParameter object which was added to the collection.

Add(string, OracleDbType)

This method adds an OracleParameter object to the collection using the supplied name and database type.

Declaration

// C#
public OracleParameter Add(string name, OracleDbType dbType);

Parameters

  • name

    The parameter name.

  • dbType

    The data type of the OracleParameter.

Return Value

The newly created OracleParameter object which was added to the collection.

Add(string, OracleDbType, ParameterDirection)

This method adds an OracleParameter object to the collection using the supplied name, database type, and direction.

Declaration

// C#
public OracleParameter Add(string name, OracleDbType dbType,
   ParameterDirection direction);

Parameters

  • name

    The parameter name.

  • dbType

    The data type of the OracleParameter.

  • direction

    The OracleParameter direction.

Return Value

The newly created OracleParameter object which was added to the collection.

Add(string, OracleDbType, object, ParameterDirection)

This method adds an OracleParameter object to the collection using the supplied name, database type, parameter value, and direction.

Declaration

// C#
public OracleParameter Add(string name, OracleDbType dbType, object val,
    ParameterDirection dir);

Parameters

  • name

    The parameter name.

  • dbType

    The data type of the OracleParameter.

  • val

    The OracleParameter value.

  • dir

    The ParameterDirection value.

Return Value

The newly created OracleParameter object which was added to the collection.

Example

// C#
 
using System;
using System.Data;
using Oracle.DataAccess.Client;
 
class AddSample
{
  static void Main()
  {
    OracleCommand cmd = new OracleCommand();
 
    // Add parameter to the OracleParameterCollection
    OracleParameter prm = cmd.Parameters.Add(
      "MyParam", OracleDbType.Decimal, 1, ParameterDirection.Input);
 
    // Prints "cmd.Parameters.Count = 1"
    Console.WriteLine("cmd.Parameters.Count = " + cmd.Parameters.Count);
 
    prm.Dispose();
    cmd.Dispose();
  }
}

Add(string, OracleDbType, int, object, ParameterDirection)

This method adds an OracleParameter object to the collection using the supplied name, database type, size, parameter value, and direction.

Declaration

// C#
public OracleParameter Add(string name, OracleDbType dbType, int size, 
    object val, ParameterDirection dir;

Parameters

  • name

    The parameter name.

  • dbType

    The data type of the OracleParameter.

  • size

    The size of OracleParameter.

  • val

    The OracleParameter value.

  • dir

    The ParameterDirection value.

Return Value

The newly created OracleParameter object which was added to the collection.

Add(string, OracleDbType, int)

This method adds an OracleParameter object to the collection using the supplied name, database type, and size.

Declaration

// C#
public OracleParameter Add(string name, OracleDbType dbType, int size);

Parameters

  • name

    The parameter name.

  • dbType

    The data type of the OracleParameter.

  • size

    The size of OracleParameter.

Return Value

The newly created OracleParameter object which was added to the collection.

Example

// C#
 
using System;
using Oracle.DataAccess.Client;
 
class AddSample
{
  static void Main()
  {
    OracleCommand cmd = new OracleCommand();
 
    // Add parameter to the OracleParameterCollection
    OracleParameter prm = cmd.Parameters.Add(
      "MyParam", OracleDbType.Varchar2, 10);
 
    // Prints "cmd.Parameters.Count = 1"
    Console.WriteLine("cmd.Parameters.Count = " + cmd.Parameters.Count);
 
    prm.Dispose();
    cmd.Dispose();
  }
}

Add (string, OracleDbType, int, string)

This method adds an OracleParameter object to the collection using the supplied name, database type, size, and source column.

Declaration

// C#
public OracleParameter Add(string name, OracleDbType dbType, int size, 
    string srcColumn);

Parameters

  • name

    The parameter name.

  • dbType

    The data type of the OracleParameter.

  • size

    The size of OracleParameter.

  • srcColumn

    The name of the source column.

Return Value

An OracleParameter.

Add(string, OracleDbType, int, ParameterDirection, bool, byte, byte, string, DataRowVersion, object)

This method adds an OracleParameter object to the collection using the supplied name, database type, size, direction, null indicator, precision, scale, source column, source version, and parameter value.

Declaration

// C#
public OracleParameter Add(string name, OracleDbType dbType, int size,
    ParameterDirection dir, bool isNullable, byte precision, 
    byte scale, string srcColumn, DataRowVersion version, object val);

Parameters

  • name

    The parameter name.

  • dbType

    The data type of the OracleParameter.

  • size

    The size of OracleParameter.

  • dir

    The ParameterDirection value.

  • isNullable

    An indicator that specifies if the parameter value can be null.

  • precision

    The precision of the parameter value.

  • scale

    The scale of the parameter value.

  • srcColumn

    The name of the source column.

  • version

    The DataRowVersion value.

  • val

    The parameter value.

Return Value

The newly created OracleParameter object which was added to the collection.

Exceptions

ArgumentException - The type of supplied val does not belong to the type of Value property in any of the ODP.NET Types.

AddRange

This method adds elements to the end of the OracleParameterCollection.

Declaration

// C#
public override void AddRange(Array paramArray );

Parameters

paramArray

An array of OracleParameter objects.

Exceptions

ArgumentNullException - The input parameter is null.

Clear

This method removes all the OracleParameter objects from the collection.

Declaration

// C#
public override void Clear();

Implements

IList

Example

// C#
 
using System;
using Oracle.DataAccess.Client;
 
class ClearSample
{
  static void Main()
  {
    OracleCommand cmd = new OracleCommand();
 
    // Add parameter to the OracleParameterCollection
    OracleParameter prm = cmd.Parameters.Add("MyParam", OracleDbType.Decimal);
 
    // Prints "cmd.Parameters.Count = 1"
    Console.WriteLine("cmd.Parameters.Count = " + cmd.Parameters.Count);
 
    // Clear all parameters in the OracleParameterCollection
    cmd.Parameters.Clear();
 
    // Prints "cmd.Parameters.Count = 0"
    Console.WriteLine("cmd.Parameters.Count = " + cmd.Parameters.Count);
 
    prm.Dispose();
    cmd.Dispose();
  }
}

Contains

Contains indicates whether or not the supplied object exists in the collection.

Overload List:

  • Contains(object)

    This method indicates whether or not the supplied object exists in the collection.

  • Contains(string)

    This method indicates whether or not an OracleParameter object exists in the collection using the supplied string.

Contains(object)

This method indicates whether or not the supplied object exists in the collection.

Declaration

// C#
public override bool Contains(object obj)

Parameters

  • obj

    The object.

Return Value

A bool that indicates whether or not the OracleParameter specified is inside the collection.

Implements

IList

Exceptions

InvalidCastException - The supplied obj is not an OracleParameter object.

Remarks

Returns true if the collection contains the OracleParameter object; otherwise, returns false.

Example

// C#
 
using System;
using Oracle.DataAccess.Client;
 
class ContainsSample
{
  static void Main()
  {
    OracleCommand cmd = new OracleCommand();
 
    // Add parameter to the OracleParameterCollection
    OracleParameter prm1 = cmd.Parameters.Add("MyParam", OracleDbType.Decimal);
 
    // Check if the OracleParameterCollection contains prm1
    bool bContains = cmd.Parameters.Contains(prm1);
 
    // Prints "bContains = True"
    Console.WriteLine("bContains = " + bContains);
 
    OracleParameter prm2 = new OracleParameter();
 
    // Check if the OracleParameterCollection contains prm2
    bContains = cmd.Parameters.Contains(prm2);
 
    // Prints "bContains = False"
    Console.WriteLine("bContains = " + bContains);
 
    prm1.Dispose();
    prm2.Dispose();
    cmd.Dispose();
  }
}

Contains(string)

This method indicates whether or not an OracleParameter object exists in the collection using the supplied string.

Declaration

// C#
public override bool Contains(string name);

Parameters

  • name

    The name of OracleParameter object.

Return Value

Returns true if the collection contains the OracleParameter object with the specified parameter name; otherwise, returns false.

Implements

IDataParameterCollection

Example

// C#
 
using System;
using Oracle.DataAccess.Client;
 
class ContainsSample
{
  static void Main()
  {
    OracleCommand cmd = new OracleCommand();
 
    // Add parameter to the OracleParameterCollection
    OracleParameter prm = cmd.Parameters.Add("MyParam", OracleDbType.Decimal);
 
    // Check if the OracleParameterCollection contains "MyParam"
    bool bContains = cmd.Parameters.Contains("MyParam");
 
    // Prints "bContains = True"
    Console.WriteLine("bContains = " + bContains);
 
    // Check if the OracleParameterCollection contains "NoParam"
    bContains = cmd.Parameters.Contains("NoParam");
 
    // Prints "bContains = False"
    Console.WriteLine("bContains = " + bContains);
 
    prm.Dispose();
    cmd.Dispose();
  }
}

CopyTo

This method copies OracleParameter objects from the collection, starting with the supplied index to the supplied array.

Declaration

// C#
public override void CopyTo(Array array, int index);

Parameters

  • array

    The specified array.

  • index

    The array index.

Implements

ICollection

IndexOf

IndexOf returns the index of the OracleParameter object in the collection.

Overload List:

  • IndexOf(object)

    This method returns the index of the OracleParameter object in the collection.

  • IndexOf(String)

    This method returns the index of the OracleParameter object with the specified name in the collection.

IndexOf(object)

This method returns the index of the OracleParameter object in the collection.

Declaration

// C#
public override int IndexOf(object obj);

Parameters

  • obj

    The specified object.

Return Value

Returns the index of the OracleParameter object in the collection.

Implements

IList

Exceptions

InvalidCastException - The supplied obj cannot be cast to an OracleParameter object.

Remarks

Returns the index of the supplied OracleParameter obj in the collection.

IndexOf(String)

This method returns the index of the OracleParameter object with the specified name in the collection.

Declaration

// C#
public override int IndexOf(String name);

Parameters

  • name

    The name of parameter.

Return Value

Returns the index of the supplied OracleParameter in the collection.

Implements

IDataParameterCollection

Insert

This method inserts the supplied OracleParameter object to the collection at the specified index.

Declaration

// C#
public override void Insert(int index, object obj);

Parameters

  • index

    The specified index.

  • obj

    The OracleParameter object.

Implements

IList

Remarks

An InvalidCastException is thrown if the supplied obj cannot be cast to an OracleParameter object.

Remove

This method removes the supplied OracleParameter from the collection.

Declaration

// C#
public override void Remove(object obj);

Parameters

  • obj

    The specified object to remove.

Implements

IList

Exceptions

InvalidCastException - The supplied obj cannot be cast to an OracleParameter object.

Example

// C#
 
using System;
using Oracle.DataAccess.Client;
 
class RemoveSample
{
  static void Main()
  {
    OracleCommand cmd = new OracleCommand();
 
    // Add 2 parameters to the OracleParameterCollection
    OracleParameter prm1 = cmd.Parameters.Add("MyParam1", OracleDbType.Decimal);
    OracleParameter prm2 = cmd.Parameters.Add("MyParam2", OracleDbType.Decimal);
 
    // Prints "cmd.Parameters.Count = 2"
    Console.WriteLine("cmd.Parameters.Count = " + cmd.Parameters.Count);
 
    // Remove the 1st parameter from the OracleParameterCollection
    cmd.Parameters.Remove(prm1);
 
    // Prints "cmd.Parameters.Count = 1"
    Console.WriteLine("cmd.Parameters.Count = " + cmd.Parameters.Count);
 
    // Prints "cmd.Parameters[0].ParameterName = MyParam2"
    Console.WriteLine("cmd.Parameters[0].ParameterName = " + 
      cmd.Parameters[0].ParameterName);
 
    prm1.Dispose();
    prm2.Dispose();
    cmd.Dispose();
  }
}

RemoveAt

RemoveAt removes the OracleParameter object from the collection by location.

Overload List:

  • RemoveAt(int)

    This method removes from the collection the OracleParameter object located at the index specified by the supplied index.

  • RemoveAt(String)

    This method removes from the collection the OracleParameter object specified by the supplied name.

RemoveAt(int)

This method removes from the collection the OracleParameter object located at the index specified by the supplied index.

Declaration

// C#
public override void RemoveAt(int index);

Parameters

  • index

    The specified index from which the OracleParameter is to be removed.

Implements

IList

RemoveAt(String)

This method removes from the collection the OracleParameter object specified by the supplied name.

Declaration

// C#
public override void RemoveAt(String name);

Parameters

  • name

    The name of the OracleParameter object to be removed from the collection.

Implements

IDataParameterCollection