Properties and Methods of the SqlParameterCollection Class

Item	Description
Properties
Count	Contains the number of items in the parameter collection
Item	Provides an index for the collection
Methods
Add()	Add a new parameter to the parameter collection
Clear()	Removes all items from the collection
Contains()	Enables a developer to check for the existence of a specific parameter in the collection
IndexOf()	Returns the location of a specific parameter in the collection
Insert()	Adds a parameter to the collection at a specific place
Remove()	Removes a parameter from the collection
RemoveAt()	Removes a specific parameter from the collection by ordinal number or name

SqlParameterCollection.Count

Syntax

Int32 Count

Description

Count is a read-only property that contains the number of items in the parameter collection.

Example

SqlConnection conn = new SqlConnection("Data Source=localhost; Initial Catalog=northwind;
        UID=sa;PWD=;");
conn.Open();


//Create Command
SqlCommand cmd = new SqlCommand("AddNewCustomer", conn);
cmd.CommandType = CommandType.StoredProcedure;


//Declare and configure parameter
SqlParameter param = cmd.CreateParameter();
param.ParameterName = "@CustomerName";
param.DbType = SqlDbType.NVarChar;
param.Size = 25;
param.Value = "John";
param.Direction = ParameterDirection.Input;

SqlParameterCollection.Item

Syntax

SqlParameter Item(String parameterName)
SqlParameter Item(Int32 index)

Description

The Item property is used to access a specific SQL parameter in the collection, by name or by index.

Example

SqlConnection conn = new SqlConnection("Data Source=localhost; Initial Catalog=northwind;
        UID=sa;PWD=;");
conn.Open();

//Create Command
SqlCommand cmd = new SqlCommand("AddNewCustomer", conn);
cmd.CommandType = CommandType.StoredProcedure;

//Declare and configure parameter
SqlParameter param = cmd.CreateParameter();
param.ParameterName = "@CustomerName";
param.DbType = SqlDbType.NVarChar;
param.Size = 25;
param.Value = "John";
param.Direction = ParameterDirection.Input;

cmd.Parameters.Add(param);
param2 = cmd.Parameters["@CustomerName"];
param3 = cmd.Parameters[0];

SqlParameterCollection.Add()

Syntax

SqlParameter Add(String name, SqlDbType dbType)
SqlParameter Add(ISqlParameter value)
Int32 Add(Object value)
SqlParameter Add(String name, Object value)
SqlParameter Add(String name, SqlDbType dbType,
                            Int32 size, String sourceColumn)
SqlParameter Add(String name, SqlDbType dbType, Int32 size)

Description

The Add() method adds SQL parameters to the parameter collection.

Example

SqlConnection conn = new SqlConnection("Data Source=localhost; Initial Catalog=northwind;
        UID=sa;PWD=;");
conn.Open();


//Create Command
SqlCommand cmd = new SqlCommand("AddNewCustomer", conn);
cmd.CommandType = CommandType.StoredProcedure;


//Declare and configure parameter
SqlParameter param = cmd.CreateParameter();
param.ParameterName = "@CustomerName";
param.DbType = SqlDbType.NVarChar;
param.Size = 25;
param.Value = "John";
param.Direction = ParameterDirection.Input;

cmd.Parameters.Add(param);

SqlParameterCollection.Clear()

Syntax

Viod Clear()

Description

The Clear() method removes all items from the parameter collection.

Example

SqlConnection conn = new SqlConnection("Data Source=localhost; Initial Catalog=northwind;
        UID=sa;PWD=;");
conn.Open();


//Create Command
SqlCommand cmd = new SqlCommand("AddNewCustomer", conn);
cmd.CommandType = CommandType.StoredProcedure;


cmd.Parameters.Clear();

SqlParameterCollection.Contains()

Syntax

Boolean Contains(String value)
Boolean Contains(Object value)

Description

The Contains() method returns True if the specified items are found in the parameter collection.

Example

SqlConnection conn = new SqlConnection("Data Source=localhost; Initial Catalog=northwind;
        UID=sa;PWD=;");
conn.Open();


//Create Command
SqlCommand cmd = new SqlCommand("AddNewCustomer", conn);
cmd.CommandType = CommandType.StoredProcedure;


//Declare and configure parameter
SqlParameter param = cmd.CreateParameter();
param.ParameterName = "@CustomerName";
param.DbType = SqlDbType.NVarChar;
param.Size = 25;
param.Value = "John";
param.Direction = ParameterDirection.Input;

cmd.Parameters.Add(param);

Bool IsPresent = cmd.Parameters.Contains(param);
Bool IsPresent2 = cmd.Parameters.Contains("@CustomerName");

SqlParameterCollection.IndexOf()

Syntax

Int32 IndexOf(Object parameter)
Int32 IndexOf(String parameterName)

Description

The IndexOf() method returns the index of the specified parameter in the parameter collection.

Example

SqlConnection conn = new SqlConnection("Data Source=localhost; Initial Catalog=northwind;
        UID=sa;PWD=;");
conn.Open();


//Create Command
SqlCommand cmd = new SqlCommand("AddNewCustomer", conn);
cmd.CommandType = CommandType.StoredProcedure;


//Declare and configure parameter
SqlParameter param = cmd.CreateParameter();
param.ParameterName = "@CustomerName";
param.DbType = SqlDbType.NVarChar;
param.Size = 25;
param.Value = "John";
param.Direction = ParameterDirection.Input;

cmd.Parameters.Add(param);

int paramIndex = cmd.Parameters.IndexOf(param);
int paramIndex2 = cmd.Parameters.IndexOf("@CustomerName");

SqlParameterCollection.Insert()

Syntax

Void Insert(Int32 index, Object value)

Description

The Insert() method adds a parameter to the parameter collection at a specific place that is given by index.

Example

SqlConnection conn = new SqlConnection("Data Source=localhost; Initial Catalog=northwind;
        UID=sa;PWD=;");
conn.Open();


//Create Command
SqlCommand cmd = new SqlCommand("AddNewCustomer", conn);
cmd.CommandType = CommandType.StoredProcedure;


//Declare and configure parameter
SqlParameter param = cmd.CreateParameter();
param.ParameterName = "@CustomerName";
param.DbType = SqlDbType.NVarChar;
param.Size = 25;
param.Value = "John";
param.Direction = ParameterDirection.Input;

//insert parameter
cmd.Parameters.Insert(0, param);

SqlParameterCollection.Remove()

Syntax

Void Remove(Object value)

Description

The Remove() method removes a parameter from the collection. The parameter object is passed in as the Value argument.

Example

SqlConnection conn = new SqlConnection("Data Source=localhost; Initial Catalog=northwind;
        UID=sa;PWD=;");
conn.Open();


//Create Command
SqlCommand cmd = new SqlCommand("AddNewCustomer", conn);
cmd.CommandType = CommandType.StoredProcedure;


//Declare and configure parameter
SqlParameter param = cmd.CreateParameter();
param.ParameterName = "@CustomerName";
param.DbType = SqlDbType.NVarChar;
param.Size = 25;
param.Value = "John";
param.Direction = ParameterDirection.Input;

//Remove parameters
cmd.Parameters.Remove(param);

SqlParameterCollection.RemoveAt()

Syntax

Void RemoveAt(String value)
Void RemoveAt(Int32 index)

Description

The RemoveAt() method removes a parameter from the parameter collection, either by name or by index.

Example

SqlConnection conn = new SqlConnection("Data Source=localhost; Initial Catalog=northwind;
        UID=sa;PWD=;");
conn.Open();


//Create Command
SqlCommand cmd = new SqlCommand("AddNewCustomer", conn);
cmd.CommandType = CommandType.StoredProcedure;



//Add a parameter
param = cmd.Parameters.Add(new SqlParameter("@name", SqlDbType.NVarChar, 25));
param.Direction = ParameterDirection.Input;
param.Value = "test";

//Add a parameter
param = cmd.Parameters.Add(new SqlParameter("@company", SqlDbType.NVarChar, 50));
param.Direction = ParameterDirection.Input;
param.Value = "test";

//Remove parameters
cmd.Parameters.RemoveAt(0);
cmd.Parameters.RemoveAt("@company");