Selecting .NET Objects and Methods

The guidelines in this section will help you to optimize system performance when selecting and using .NET objects and methods.

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.

When you set the CommandText property in the Command object to the stored procedure name, do not physically code the literal arguments into the CommandText. For example, do not use literal arguments such as:

{call expense (3567, 'John', 987.32)}

ADO.NET data providers can call stored procedures on the database server by executing the procedure as any other SQL query. Executing the stored procedure as a SQL query results in the database server parsing the statement, validating the argument types, and converting the arguments into the correct data types.

In the following example, the application programmer might assume that the only argument to getCustName is the integer 12345:

{call getCustName (12345)}

However, SQL is always sent to the database server as a character string. When the database server parses the SQL query and isolates the argument value, the result is still a string. The database server must then convert the string ’12345’ into the integer 12345. Using a parameter marker eliminates the need to convert the string and reduces the amount of processing by the server:

{call getCustName (?)}