One of the database operations commands in ASP.NET 2.0

The command object is used when database commands are executed. There are three kinds of command classes: SqlCommand, OleDbCommand and OdbcCommand.

The command object is used primarily to run SQL statements such as SELECT, INSERT, update, or delete. The command object can also call a stored procedure or obtain a record from a particular table.

The DataReader object is used primarily to read data results, and it is usually faster to read records than from a dataset. There are three types of DataReader: SqlDataReader, OleDbDataReader and OdbcDataReader. The DataReader object reads records from the database with the Commmand object, and the DataReader object can only read the records forward, and in some cases replaces the DataSet object (the DataSet object can store the row copies in the database). This copy can be processed when the connection to the database is disconnected, which we will detail in a later section.

Note: You cannot modify the records in the database using DataReader, which reads the database in a forward, read-only manner.

　　 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 OLE DB, 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 of the properties, methods, and events of this class are similar to those of OleDbCommand and OdbcCommand. This chapter will focus on SqlCommand specific properties and methods, and other command classes you can refer to the appropriate Help documentation.

Note: You need to import different namespaces using a different command object. The OleDbCommand namespace is System.Data.OleDb. The SqlCommand namespace is System.Data.SqlClient. The OdbcCommand namespace is System.Data.Odbc.

SqlCommand properties:

Property	Description
CommandText	The return type is string, gets or sets the SQL statement, stored procedure, or table to execute on the data source.
CommandTimeout	The return type is int, gets or sets the wait time before terminating the attempt to execute the command and generating an error.
CommandType	Its return type is CommandType, reading or setting a value that represents how the CommandText property will be interpreted, and its valid values can be CommandType.Text, Commandtype.storedprocedur and CommandType.TableDirect, respectively, representing SQL statements, stored procedure calls, or tables to read, default to text.
Connection	The return type is string, gets or sets the SqlConnection used by this instance of SqlCommand.
Parameters	The return type is sqlparametercollection and gets the arguments provided to the command, if any.

SqlCommand method:

Method	Description
Cancle ()	The return type is void, canceling the execution of the command
CreateParameter ()	The return type is SqlParameter, used to create a new instance of the SqlParameter object.
ExecuteNonQuery ()	The return type is int, executing an SQL statement that does not return a result set, including INSERT, UPDATE and DELETE statements, DDL statements, and stored procedure calls that do not return a result set. The int value returned is the number of database rows that the command affects.
ExecuteReader ()	Its return type is SqlDataReader, executing a SELECT statement, TableDirect command, or a stored procedure call that returns a result set. Returns the result set in the SqlDataReader object.
ExecuteScalar ()	The return type is object, and the SELECT statement that returns a single value is executed (any other values are ignored). This command result is returned as an object.
ExecuteXmlReader ()	The return type is XmlReader, executes the SELECT statement that returns the XML data, returns the result set with the XmlReader object, and applies only to the SqlCommand class

Generating SqlCommand objects

We can use the constructor to generate the SqlCommand object, or we can call the SqlConnection object's CreateCommand () method to generate the SqlCommand object, which is described in the following two ways.

To generate a SqlCommand object with a constructor

The constructor for the SqlCommand object looks like this:

SqlCommand () SqlCommand (String commandtext) SqlCommand (String commandtext,sqlconnection mysqlconnection)

Program code Description: In the program code of the syntax example above, CommandText contains SQL statements, stored procedure calls, or tables to read. Mysqlconnection is the corresponding SqlConnection object.

Before using the SqlCommand object, you first need to identify a SqlConnection object for data transfer with the SQL Server database.

Mysqlconnection.connectionstring= "Server=localhost;database=northwind; Integrated SECURITY=SSPI ";

You can then generate a new SqlCommand object with the following statement:

SqlCommand mysqlcommand=new SqlCommand ();

Then set the Connection property of the Mysqlcommand object to mysqlconnection:

Mysqlcommand.connection= mysqlconnection;

This allows the Mysqlcommand object to be used for data delivery with the Mysqlconnection database. The CommandType property of the Command object now determines the type of command to execute. You can specify the CommandType property with the System.Data.CommandType enumeration value.
The enumeration values for CommandType are shown in the following table:

Numerical	Description
Text	Indicates that the command is an SQL statement and the default value is text
StoredProcedure	Indicates that the command is a stored procedure call
TableDirect	Represents the table name of the row and column being read. Note: The SqlCommand object does not support TableDirect, and you want to use an object from another command class.

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

SqlCommand mysqlcommand=new SqlCommand (); Mysqlcommand.connection=mysqlconnection; mysqlcommand.commandtext= "SELECT * from Employees"; Mysqlcommand.commandtype=commandtype.text;

Program code Description: In the above syntax example of the program code, we set the Mysqlcommand object CommandText as a select query statement, And the CommandType property of the Mysqlcommand object is specified as CommandType.Text, which indicates that the command is an SQL statement. Since CommandType.Text is the default CommandType value, we can translate the note.

There is also a more efficient form of using one of the constructors of the SqlCommand object:

SqlCommand mysqlcommand=new SqlCommand ("SELECT * from Employees", MyConnection);

Program code Description: In the above syntax examples of the program code, we can directly use the SqlCommand (string commandtext,sqlconnection mysqlconnection) constructor, so that the program code more concise and intuitive.

You can also use the stored procedure to query for the data that you need, and we can take the following code form:

SqlCommand mysqlcommand=new SqlCommand ("Getempolyees", MyConnection); Mysqlcommand.commandtype=commandtype.storedprocedure;

Program code Description: In the above syntax example of the program code, getempolyees for a stored procedure name, used to implement all employee information inquiries. And the CommandType value is specified as StoredProcedure, which indicates that the command is a stored procedure call.

To generate a SqlCommand object using the CreateCommand () method

You can also use the CreateCommand () method of the SqlConnection object to generate SqlCommand objects without constructors. This method returns the new SqlCommand object. For example:

SqlCommand Mysqlcommand=mysqlconnection.createcommand ();