Usage of SQL Server database Methods

The specific usage of the three methods has been unclear.

Differences between the executenonquery method and the executescalar method and the executereader Method
 

(1) executenonquery (): Execute the SQL statement of the command object and return an int type variable, if an SQL statement is used to operate database records (such as adding, deleting, and updating records), the method returns the number of records affected by the operation.

(2) executescalar (): Execute the SQL statement of the command object. If the SQL statement is a SELECT query, only 1st rows and 1st columns in the query result set are returned, ignore other rows and columns. The result returned by this method is of the object type, and must be converted to the required type before use. If the SQL statement is not a SELECT query, the returned results are useless.

 

------ [Analysis]
The executenonquery () method of the command object is used to execute any SQL operation commands that do not return result sets from the database, including insert into, update, delete statements, stored procedures without returned values, DDL statements such as create table and createindex. The executenonquery method can also be used to perform directory operations, such as querying the database structure or creating database objects such as tables. The executenonquery method returns the number of rows affected by the command for the update, insert into, and delete statements. For all other types of statements, the return value is-1. If a rollback occurs, the returned value is-1.
Assume that CMD is the command object (sqlcommand) of the SQL server data provider, CN is the connection object (keep open), and The executenonquery method is called, as shown in the following code.
Sqlcommand cmd = new sqlcommand ();
Cmd. Connection = cn;
// Set the command text of CMD. In this example, delete the record with the ID field value greater than 1 in table 1.
Cmd. commandtext = "delete from Table1 where [ID]> 1 ";
Int COUNT = cmd. executenonquery ();
In the above Code, the command object cmd of the sqlcommand type is created, and the connection object CN is set, and the SQL command text is an SQL statement (generally add, delete, and modify commands ). After executing the executenonquery method, the command text is connected to the database by connecting objects. The returned value count is the number of affected data records in the database.
The executescalar () method of the command object is often used to execute Aggregate functions because it can only return 1st rows and 1st columns of a dataset. Assume that CMD is the command object (sqlcommand) of the SQL server data provider, CN is the connection object (keep open), and The executescalar method is called as shown in the following code.
Sqlcommand cmd = new sqlcommand ();
Cmd. Connection = cn;
// Set the command text of CMD. In this example, the number of records in table 1 is queried.
Cmd. commandtext = "select count (*) from Table1 ";
Int COUNT = (INT) cmd. executescalar ();

In the preceding code, the command object cmd of the sqlcommand type is created, and the connection object CN is set, and the SQL command text is an SQL statement. Because the return value is of the object type, forced conversion is required. The Count variable stores the number of records in the required data table.

 

(3) executereader (): we usually use the recordset object in ASP to read data from the database and use
Loop statements to read data one by one, but in our ADO. net, we use
The executereader () method is used to list data, and we use this executereader () method to display data.
Is the fastest way, because when we use the datareader object in executereader () method for data
In the construction of the website, he can only read forward one by one and cannot return, that is, like the ADO method in ASP
The same as the movenext of the recordset object in, it does not have a return method like move-1.
NOTE: If no data can be operated, you can only use the executereader method of the command object to return a data reader (datareader object ). Because when executenonquery () and executescalar () are called when there is no data, the error "the object is not instantiated" will occur. Therefore, when determining whether data exists, call the read () method of the data reader to detect the data.