Working with command parameters - Object-oriented programming

Working with command parameters

When executing a command to a database, it is often necessary to specify parameters, and the values ​​of some parameters often become known only at runtime. For example, in the inventory application in a bookstore, you should provide a function for searching for books by name, which you can implement by querying the database using the following SQL statement:

SELECT * FROM Books WHERE (Title LIKE [value]).

The value must be specified by the user and is not known in advance. In this regard, you need to have some way to pass the entered values ​​to the SQL statement and stored procedures at run time.

Parameters are values ​​that are filled by the substitution fields entered in the command text at design time. Parameters are objects of the Parameter class that must be added to the Parameters property of the Command object. During the execution period, the parameter values ​​are read from this collection and substituted into the SQL statement or passed to the stored procedure. Objects of the Parameter class of the appropriate type (for example, SqIParameter and OleDbParameter) should be stored in the Parameters collection. Objects of the Parameters collection can be referenced by the index, or by the name specified by the ParameterName property. Here are two ways to set the value of the first account parameter named myParameter:

// set the parameter value by the index

DleDbCommand1.Parameters [0] .Value = Ivanov AI & quot ;;

// set the value of the parameter by name

OleObCommand1.Parameters [ myParameter ]. Value = group 8551 & quot ;;

The OleDbParameter class has the associated properties of DbType and OleDbType. The first describes the type of the parameter in the .NET platform, and the second - how it is represented in the database; This is necessary because not all databases are compatible with platform types. The Parameter object performs the conversion of parameters from the type used in the application to the type used in the database. Because these properties are interrelated, if you change the value of one of them, the value of the other automatically changes and is converted to the corresponding supported type. The properties DbType and SqlType of SqIParameter objects are related in the same way, the SqlType property indicates the type of SQL database represented by this parameter.

The Direction property of the Parameter class indicates whether this parameter is an input or output parameter. The possible values ​​for this property are: Input, Output, InputOutput (input and output), or RetumValue (returned as a result).

The properties of Precision, Scale and Size specify the accuracy and size of the parameter values. The Precision and Scale properties are applied with numeric and decimal parameters and determine the width and length of the fractional part of the Value property, respectively, and the Size property is applied with binary and string parameters and represents the maximum length of this field. The Value parameter property contains the value of the parameter.

If the CommandType property of the Command object is set to Text, you must provide wildcard fields for all parameters of the SQL statement.

For OleDbCommand objects, the substitution fields are denoted by the ? quot ;, for example:

SELECT Empld, Title, FirstName, LastName FROM Employees WHERE (Title =?)

You can specify several parameters:

SELECT Empld, Title, FirstName, LastName

FROM Employees WHERE (FirstName =?) AND (LastName =?)

In this case, the order of filling parameters is determined by the order of the elements of the Parameters collection.

Objects of class SqlCommand use named parameters. To create a field for the substitution of a named parameter, you must specify the name of the parameter (because it is specified by the ParameterName property), preceded by the @ symbol. The following is an example of an SQL statement that declares a field for a named parameter named Title:

string Cmd = "SELECT Empld, Title, FirstName, LastName FROM Employees WHERE (Title = @Title)";

SqIParameter parameter = new SqlParameterQ; parameter.ParameterName = @ Title & quot ;; parameter.SqlDbType = SqlDbType.NVarChar; parameter.Direction = ParameterDirection. Input;

Also We Can Offer!

Ошибка в функции вывода объектов.