Using Parameter Markers as Arguments to Stored Procedures
When calling stored procedures, always use parameter markers for the argument markers instead of using literal arguments.
.NET data providers can call stored procedures on the database server either by executing the procedure the same way as any other SQL query, or by optimizing the execution by invoking a Remote Procedure Call (RPC) directly into the database server. When you execute the stored procedure as a SQL query, the database server parses the statement, validates the argument types, and converts the arguments into the correct data types.
Remember that SQL is always sent to the database server as a character string, for example, "getCustName (12345)". In this case, even though the application programmer might assume that the only argument to getCustName is an integer, the argument is actually passed inside a character string to the server. The database server parses the SQL query, consults database metadata to determine the parameter contract of the procedure, isolates the single argument value 12345, then converts the string '12345' into an integer value before finally executing the procedure as a SQL language event.
Invoking an RPC inside the database server avoids the overhead of using a SQL character string. Instead, a .NET data provider constructs a network packet that contains the parameters in their native data type formats, and executes the procedure remotely.
To use stored procedures correctly, set the CommandText property of the Command object to the name of the stored procedure. Then, set the CommandType property of the command to StoredProcedure. Finally, pass the arguments to the stored procedure using parameter objects. Do not physically code the literal arguments into the CommandText.
Example 1
OpenAccessCommand DBCmd = new OpenAccessCommand("getCustName", Conn);
OpenAccessDataReader = myDataReader;
myDataReader = DBCmd.ExecuteReader();
In this example, the stored procedure cannot be optimized to use a server-side RPC. The database server must treat the SQL request as a normal language event which includes parsing the statement, validating the argument types, and converting the arguments into the correct data types before executing the procedure.
Example 2
OpenAccessCommand DBCmd = new OpenAccessCommand("getCustName", Conn);
In this example, the stored procedure can be optimized to use a server-side RPC. Because the application avoids literal arguments and calls the procedure by specifying all arguments as parameters, the .NET data provider can optimize the execution by invoking the stored procedure directly inside the database as an RPC. This example avoids SQL language processing on the database server and the execution time is greatly improved.
