One of the database operation commands executed in ASP. NET 2.0

The command object is used for database command execution. There are three command classes: sqlcommand, oledbcommand, and odbccommand.

The command object is used to run SQL statements such as select, insert, update, or delete. The command object can also call a stored procedure or retrieve records from a specific table.

The datareader object is mainly used to read data results. It is usually faster to read records than to read data from dataset. There are three types of datareader: sqldatareader, oledbdatareader, and odbcdatareader. The datareader object reads records from the database using the commmand object, and the datareader object can only read records forward. It is used to replace the DataSet object in some cases (the DataSet object can store row copies in the database, this copy can be processed when the database connection is disconnected. We will introduce this object in detail in subsequent chapters ).

Note: you cannot use datareader to modify the records in the database. It adopts forward and read-only methods to read the database.

Sqlcommand class

The sqlcommand object is used to execute commands on the SQL Server database. The oledbcommand object is used to execute commands for databases that support oledb, such as Oracle and access. The odbccommand object is used to execute commands on databases that support ODBC. Although the sqlcommand class is for SQL Server, many attributes and methods of this class are similar to events and oledbcommand and odbccommand. This chapter focuses on the specific attributes and methods of sqlcommand. For other command classes, refer to the corresponding help documentation.

Note: To use different command objects, you must import different namespaces. The namespace of oledbcommand is system. Data. oledb. The namespace of sqlcommand is system. Data. sqlclient. The namespace of odbccommand is system. Data. ODBC.

Sqlcommand attributes:

Attribute	Description
Commandtext	The return type is string. Obtain or set the SQL statement, stored procedure, or table to be executed on the data source.
Commandtimeout	The return type is int, which gets or sets the waiting time before the Command Execution Attempt is terminated and an error is generated.
Commandtype	The return type is commandtype. Reading or setting indicates how the commandtext attribute is interpreted. The valid value can be commandtype. text, commandtype. storedprocedur and commandtype. tabledirect, indicating SQL statements, stored procedure calls, or tables to be read. The default value is text.
Connection	The return type is string. Obtain or set the sqlconnection used by this instance of sqlcommand.
Parameters	The return type is sqlparametercollection, and the parameters provided to the Command (if any) are obtained ).

Sqlcommand method:

Method	Description
Cancle ()	Its return type is void, canceling Command Execution
Createparameter ()	The return type is sqlparameter, which is used to create a new instance of the sqlparameter object.
Executenonquery ()	The return type is int. Execute SQL statements that do not return the result set, including insert, update and delete statements, DDL statements, and stored procedure calls that do not return the result set. The returned int value is the number of database rows affected by the command.
Executereader ()	The return type is sqldatareader. It is used to execute select statements, tabledirect commands, or stored procedure calls for returned result sets. Returns the result set in the sqldatareader object.
Executescalar ()	The return type is object. Execute the SELECT statement that returns a single value (any other value will be ignored ). The command result is returned as an object.
Executexmlreader ()	The return type is xmlreader. Execute the SELECT statement that returns XML data and use the xmlreader object to return the result set. It is only applicable to the sqlcommand class.

Generate a sqlcommand object

You can use the constructor to generate a sqlcommand object, or call the createcommand () method of the sqlconnection object to generate a sqlcommand object. The following describes the two methods respectively.

Use constructors to generate sqlcommand objects

The sqlcommand object constructor is as follows:

Sqlcommand () Sqlcommand (string commandtext) Sqlcommand (string commandtext, sqlconnection mysqlconnection)

ProgramCodeNote: In the program code of the preceding syntax example, commandtext contains SQL statements, stored procedure calls, or tables to be read. Mysqlconnection is the corresponding sqlconnection object.

Before using the sqlcommand object, you must first determine a sqlconnection object for data transmission with the SQL Server database.

Mysqlconnection. connectionstring = "Server = localhost; database = northwind; Integrated Security = sspi ";

Then, you can use the following statement to generate a new sqlcommand object:

Sqlcommand mysqlcommand = new sqlcommand ();

Set the connection attribute of the mysqlcommand object to mysqlconnection:

Mysqlcommand. Connection = mysqlconnection;

In this way, the mysqlcommand object can use mysqlconnection to transmit data with the database. Now, the commandtype attribute of the command object determines the command type to be executed. You can use the system. Data. commandtype enumeration value to specify the commandtype attribute.
The following table lists the enumerated commandtype values:

Value	Description
Text	Indicates that the command is an SQL statement and the default value is text.
Storedprocedure	Indicates that the command is called during the storage process.
Tabledirect	The name of the read row and column. Note: sqlcommand objects do not support tabledirect. You must use other command objects.

For example, you can execute an SQL query in the following format:

Sqlcommand mysqlcommand = new sqlcommand (); Mysqlcommand. Connection = mysqlconnection; Mysqlcommand. commandtext = "select * from employees "; // Mysqlcommand. commandtype = commandtype. text;

Program Code Description: In the program code of the preceding syntax example, we set commandtext of the mysqlcommand object as a SELECT query statement and specify the commandtype attribute of the mysqlcommand object as commandtype. text, indicating that the command is an SQL statement. Commandtype. Text is the default commandtype value, so we can translate it.

Another more efficient form is to use one of the constructors of the sqlcommand object:

Sqlcommand mysqlcommand = new sqlcommand ("select * from employees", myconnection );

Program Code Description: In the program code of the preceding syntax example, we can directly use sqlcommand (string commandtext, sqlconnection mysqlconnection) to construct a function, so that the program code is more concise and intuitive.

You can also use the stored procedure to query the required data. We can use the following code:

Sqlcommand mysqlcommand = new sqlcommand ("getempolyees", myconnection ); Mysqlcommand. commandtype = commandtype. storedprocedure;

Program Code Description: In the program code of the preceding syntax example, getempolyees is a stored procedure name used to query information of all employees. Specify the commandtype value as storedprocedure, indicating that the command is called during the storage process.

Use the createcommand () method to generate a sqlcommand object

If you do not need constructors, you can also use the createcommand () method of the sqlconnection object to generate the sqlcommand object. This method returns the new sqlcommand object. For example:

Sqlcommand mysqlcommand = mysqlconnection. createcommand ();

**************************************** *****************************

using (system. data. oracleclient. oracledataadapter da = new oracledataadapter (CMD)
{< br> dataset DS = new dataset ();
da. fill (DS);
cmd. parameters. clear ();
return Ds;
}

Oledbcommand cmd = conn. createcommand ();
Cmd. commandtext = "select max (ID) as maxid from ware ";
Oledbdatareader thisreader = cmd. executereader ();
While (thisreader. Read ())
{
M_id = thisreader. getint16 (0) + 1;

}

Instead of doing this:

If (thisreader. hasrows)
{
M_id = thisreader. getint16 (0) + 1;
// An error occurred while executing the preceding statement: This row or column does not exist. However, it is successful to directly execute select max (ID) as maxid from ware with a record. What went wrong there?
}