SqlParameterCollection Class

Definition

Namespace:

System.Data.SqlClient

Assembly:

System.Data.SqlClient.dll

Package:

System.Data.SqlClient v4.9.0

Source:

System.Data.SqlClient.notsupported.cs

Caution

Use the Microsoft.Data.SqlClient package instead.

Represents a collection of parameters associated with a SqlCommand and their respective mappings to columns in a DataSet. This class cannot be inherited.

public ref class SqlParameterCollection sealed : System::Data::Common::DbParameterCollection

[System.Obsolete("Use the Microsoft.Data.SqlClient package instead.")]
public sealed class SqlParameterCollection : System.Data.Common.DbParameterCollection

[<System.Obsolete("Use the Microsoft.Data.SqlClient package instead.")>]
type SqlParameterCollection = class
    inherit DbParameterCollection

Public NotInheritable Class SqlParameterCollection
Inherits DbParameterCollection

Inheritance

Object

DbParameterCollection

SqlParameterCollection

Attributes

ObsoleteAttribute

Examples

The following example creates multiple instances of SqlParameter through the SqlParameterCollection collection. The parameters are used to select data within the data source and populate the DataSet. This code assumes that a DataSet and a SqlDataAdapter have already been created with the appropriate schema, commands, and connection.

public void AddSqlParameters()
{
// ...
// create categoriesDataSet and categoriesAdapter
// ...

  categoriesAdapter.SelectCommand.Parameters.Add(
    "@CategoryName", SqlDbType.VarChar, 80).Value = "toasters";
  categoriesAdapter.SelectCommand.Parameters.Add(
    "@SerialNum", SqlDbType.Int).Value = 239;
  categoriesAdapter.Fill(categoriesDataSet);
}

Public Sub AddSqlParameters()
    ' ...
    ' create categoriesDataSet and categoriesAdapter
    ' ...
    categoriesAdapter.SelectCommand.Parameters.Add( _
        "@CategoryName", SqlDbType.VarChar, 80).Value = "toasters"
    categoriesAdapter.SelectCommand.Parameters.Add( _
        "@SerialNum", SqlDbType.Int).Value = 239
    
    categoriesAdapter.Fill(categoriesDataSet)
End Sub

Remarks

If the command contains an ad hoc SQL statement, as opposed to a stored-procedure name, the number of the parameters in the collection must be equal to the number of parameter placeholders within the command text, or SQL Server raises an error. With a stored procedure, all the parameters declared in the stored procedure without a default value must be provided. Parameters declared with a default value are optional. This lets you specify a value other than the default.

For more information with additional sample code demonstrating how to use parameters, see Commands and Parameters.

Properties

Count	Returns an Integer that contains the number of elements in the SqlParameterCollection. Read-only.
IsFixedSize	Gets a value that indicates whether the SqlParameterCollection has a fixed size.
IsReadOnly	Gets a value that indicates whether the SqlParameterCollection is read-only.
IsSynchronized	Specifies whether the collection is synchronized. (Inherited from DbParameterCollection)
Item[Int32]	Gets the SqlParameter at the specified index.
Item[String]	Gets the SqlParameter with the specified name.
SyncRoot	Gets an object that can be used to synchronize access to the SqlParameterCollection.

Methods

Add(Object)	Adds the specified SqlParameter object to the SqlParameterCollection.
Add(SqlParameter)	Adds the specified SqlParameter object to the SqlParameterCollection.
Add(String, SqlDbType, Int32, String)	Adds a SqlParameter to the SqlParameterCollection with the parameter name, the data type, and the column length.
Add(String, SqlDbType, Int32)	Adds a SqlParameter to the SqlParameterCollection, given the specified parameter name, SqlDbType and size.
Add(String, SqlDbType)	Adds a SqlParameter to the SqlParameterCollection given the parameter name and the data type.
AddRange(Array)	Adds an array of values to the end of the SqlParameterCollection.
AddRange(SqlParameter[])	Adds an array of SqlParameter values to the end of the SqlParameterCollection.
AddWithValue(String, Object)	Adds a value to the end of the SqlParameterCollection.
Clear()	Removes all the SqlParameter objects from the SqlParameterCollection.
Contains(Object)	Determines whether the specified Object is in this SqlParameterCollection.
Contains(SqlParameter)	Determines whether the specified SqlParameter is in this SqlParameterCollection.
Contains(String)	Determines whether the specified parameter name is in this SqlParameterCollection.
CopyTo(Array, Int32)	Copies all the elements of the current SqlParameterCollection to the specified one-dimensional Array starting at the specified destination Array index.
CopyTo(SqlParameter[], Int32)	Copies all the elements of the current SqlParameterCollection to the specified SqlParameterCollection starting at the specified destination index.
Equals(Object)	Determines whether the specified object is equal to the current object. (Inherited from Object)
GetEnumerator()	Returns an enumerator that iterates through the SqlParameterCollection.
GetHashCode()	Serves as the default hash function. (Inherited from Object)
GetParameter(Int32)	Returns the DbParameter object at the specified index in the collection. (Inherited from DbParameterCollection)
GetParameter(String)	Returns DbParameter the object with the specified name. (Inherited from DbParameterCollection)
GetType()	Gets the Type of the current instance. (Inherited from Object)
IndexOf(Object)	Gets the location of the specified Object within the collection.
IndexOf(SqlParameter)	Gets the location of the specified SqlParameter within the collection.
IndexOf(String)	Gets the location of the specified SqlParameter with the specified name.
Insert(Int32, Object)	Inserts an Object into the SqlParameterCollection at the specified index.
Insert(Int32, SqlParameter)	Inserts a SqlParameter object into the SqlParameterCollection at the specified index.
MemberwiseClone()	Creates a shallow copy of the current Object. (Inherited from Object)
Remove(Object)	Removes the specified SqlParameter from the collection.
Remove(SqlParameter)	Removes the specified SqlParameter from the collection.
RemoveAt(Int32)	Removes the SqlParameter from the SqlParameterCollection at the specified index.
RemoveAt(String)	Removes the SqlParameter from the SqlParameterCollection at the specified parameter name.
SetParameter(Int32, DbParameter)	Sets the DbParameter object at the specified index to a new value. (Inherited from DbParameterCollection)
SetParameter(String, DbParameter)	Sets the DbParameter object with the specified name to a new value. (Inherited from DbParameterCollection)
ToString()	Returns a string that represents the current object. (Inherited from Object)

Extension Methods

See also

• Commands and Parameters (ADO.NET)
• DataAdapter Parameters (ADO.NET)
• Using the .NET Framework Data Provider for SQL Server
• ADO.NET Overview