System.Data.SqlClient Namespace SqlCommand Class
Sets or retrieves a value indicating how the CommandText property is to be interpreted.
| Script |
SqlCommand.CommandType [ = enumValue ] |
The property is read/write with a default value of Text.
IDbCommand.CommandType
When you set the CommandType property to StoredProcedure, you should set the CommandText property to the name of the stored procedure. The command executes this stored procedure when you call one of the Execute methods.
The SQL Server.NET Data Provider does not support the question mark ( ? ) placeholder for passing parameters to an SQL Statement or a stored procedure called with a CommandType of Text. In this case, named parameters must be used. For example:
SELECT * FROM Customers WHERE CustomerID = @CustomerID
For more information see Using Stored Procedures with a Command.
The following example illustrates how the CommandType property can be set to call a procedure stored in an SQL Server™ database.
SqlConnection myConn = new SqlConnection
( "Data Source=localhost; Integrated Security=SSPI;" +
"Initial Catalog=northwind" );
SqlCommand salesCmd = new SqlCommand
( "SalesByCategory", myConn );
salesCmd.CommandType = CommandType.StoredProcedure;
SqlParameter myParam = salesCmd.Parameters.Add
( "@CategoryName", SqlDbType.NVarChar, 15 );
myParam.Value = "Beverages";
myConn.Open ( );
SqlDataReader myReader = salesCmd.ExecuteReader ( );
Response.Write ( myReader.GetName ( 0 ) + ", " +
myReader.GetName ( 1 ) + "<br>" );
while ( myReader.Read ( ) ) {
Response.Write ( myReader.GetString ( 0 ) + ", " +
myReader.GetDecimal ( 1 ) + "<br>" );
}
myReader.Close ( );
myConn.Close ( );
Dim myConn As SqlConnection = New SqlConnection _
( "Data Source=localhost; Integrated Security=SSPI;" & _
"Initial Catalog=northwind" )
Dim salesCmd As SqlCommand = New SqlCommand _
( "SalesByCategory", myConn )
salesCmd.CommandType = CommandType.StoredProcedure
Dim myParam As SqlParameter = salesCmd.Parameters.Add _
( "@CategoryName", SqlDbType.NVarChar, 15 )
myParam.Value = "Beverages"
myConn.Open ( )
Dim myReader As SqlDataReader = salesCmd.ExecuteReader ( )
Response.Write ( myReader.GetName ( 0 ) & ", " & _
myReader.GetName ( 1 ) & "<br>" )
Do While myReader.Read ( )
Response.Write ( myReader.GetString ( 0 ) & ", " & _
myReader.GetDecimal ( 1 ) & "<br>" )
Loop
myReader.Close ( )
myConn.Close ( ) |
| |
C# |
VB |
SqlCommand Members CommandText CommandTimeout Connection
