Export (0) Print
Expand All

SqlCommand.Prepare Method

Creates a prepared version of the command on an instance of SQL Server.

[Visual Basic]
Public Overridable Sub Prepare() Implements IDbCommand.Prepare
[C#]
public virtual void Prepare();
[C++]
public: virtual void Prepare();
[JScript]
public function Prepare();

Implements

IDbCommand.Prepare

Exceptions

Exception Type Condition
InvalidOperationException The Connection is not set.

-or-

The Connection is not Open.

Remarks

If CommandType is set to StoredProcedure, the call to Prepare should succeed, although it may result in a no-op.

Before you call Prepare, specify the data type of each parameter in the statement to be prepared. For each parameter that has a variable length data type, you must set the Size property to the maximum size needed. Prepare returns an error if these conditions are not met.

If you call an Execute method after calling Prepare, any parameter value that is larger than the value specified by the Size property is automatically truncated to the original specified size of the parameter, and no truncation errors are returned.

Output parameters (whether prepared or not) must have a user-specified data type. If you specify a variable length data type, you must also specify the maximum Size.

Example

[Visual Basic, C#, C++] The following example demonstrates the use of the Prepare method.

[Visual Basic] 
Public Sub SqlCommandPrepare()
    Dim id As Integer = 20
    Dim desc As String = "myFirstRegion"
    Dim rConn As SqlConnection = New SqlConnection("Persist Security Info=False;" & _
                 "Integrated Security=SSPI;database=northwind;server=mySQLServer")
    rConn.Open()
    Dim command As SqlCommand = New SqlCommand("", rConn)

    ' Create and prepare an SQL statement.
    command.CommandText = "insert into Region (RegionID, RegionDescription) values (@id, @desc)"
    command.Parameters.Add("@id", id)
    command.Parameters.Add("@desc", desc)
    command.Prepare() ' Calling Prepare after having set the Commandtext and parameters.
    command.ExecuteNonQuery()

    ' Change parameter values and call ExecuteNonQuery.
    command.Parameters(0).Value = 21
    command.Parameters(1).Value = "mySecondRegion"
    command.ExecuteNonQuery()
End Sub

[C#] 
public void SqlCommandPrepareEx() {
     int  id = 20;
     string  desc = "myFirstRegion" ;
     SqlConnection rConn = new SqlConnection("Persist Security Info=False;Integrated Security=SSPI;database=northwind;server=mySQLServer");
     rConn.Open();
     SqlCommand command    = new SqlCommand(null, rConn);

     // Create and prepare an SQL statement.
     command.CommandText = "insert into Region (RegionID, RegionDescription) values (@id, @desc)" ;
     command.Parameters.Add ( "@id", id) ;
     command.Parameters.Add ( "@desc", desc) ;
     command.Prepare() ;  // Calling Prepare after having set the Commandtext and parameters.
     command.ExecuteNonQuery();

     // Change parameter values and call ExecuteNonQuery.
     command.Parameters[0].Value = 21;
     command.Parameters[1].Value = "mySecondRegion";
     command.ExecuteNonQuery();
}

[C++] 
public:
    void SqlCommandPrepareEx()
    {
        int id = 20;
        String*  desc = S"myFirstRegion" ;
        SqlConnection* rConn = new SqlConnection(S"Persist Security Info=False;Integrated Security=SSPI;database=northwind;server=mySQLServer");
        rConn->Open();
        SqlCommand* command = new SqlCommand(0, rConn);

        // Create and prepare an SQL statement.
        command->CommandText = S"insert into Region (RegionID, RegionDescription) values (@id, @desc)" ;
        command->Parameters->Add (S"@id", __box(id));
        command->Parameters->Add (S"@desc", desc);
        command->Prepare();  // Calling Prepare after having set the CommandText and parameters.
        command->ExecuteNonQuery();

        // Change parameter values and call ExecuteNonQuery.
        command->Parameters->Item[0]->Value = __box(21);
        command->Parameters->Item[1]->Value = S"mySecondRegion";
        command->ExecuteNonQuery();
    };

[JScript] No example is available for JScript. To view a Visual Basic, C#, or C++ example, click the Language Filter button Language Filter in the upper-left corner of the page.

Requirements

Platforms: Windows 98, Windows NT 4.0, Windows Millennium Edition, Windows 2000, Windows XP Home Edition, Windows XP Professional, Windows Server 2003 family, .NET Compact Framework

See Also

SqlCommand Class | SqlCommand Members | System.Data.SqlClient Namespace

Show:
© 2014 Microsoft