2 Methods for SqlCommand objects: ExecuteNonQuery method and ExecuteScalar method

1.SqlCommand. ExecuteNonQuery Method

Executes Transact-SQL statements on the connection and returns the number of rows affected.

Syntax: public override int ExecuteNonQuery ()

return value:

Type: System.Int32

Number of rows affected

Attention:

① for UPDATE, INSERT, and DELETE statements, the return value is the number of rows affected by the command.

If there is a trigger on the table that is performing the insert or update operation, the return value includes the number of rows affected by the INSERT or update operation and the number of rows affected by one or more triggers.

For all other types of statements, the return value is-1. If a rollback occurs, the return value is also-1.

② The statement does not work with the SELECT statement.

You must not use the Select SQL statement + cmd when querying a table for (a) data. ExecuteNonQuery (), by judging whether the return value is greater than the max.

Solution:

Ⅰ.sqlcecommand.executescalar method (+ aggregate query)

Executes the query and returns the first column of the first row in the result set returned by the query. Ignore extra columns or rows. For example, an aggregated value.

Cmd.commandtext = "SELECT count (*) from users where id = 10000;" For example, an aggregated value.

int count = (int) cmd. ExecuteScalar ();

Ⅱ. Returning a reader with the Excutereader () method

if (reader. Read () = = False) {...}

2.SqlCommand. ExecuteScalar Method

Executes the query and returns the first column of the first row in the result set returned by the query. Other columns or rows are ignored.

Syntax: public override Object executescalar ()

return value

Type: System. Object

The first column of the first row in the result set, or a null reference if the result set is empty (Nothing in Visual Basic ).

Attention:

Use the ExecuteScalar method to retrieve a single value (such as an aggregated value) from the database. This operation requires less code than using the ExecuteReader method and then using the data returned by SqlDataReader to perform the actions required to generate a single value.

A typical executescalar query can be in a format similar to the following C # example:

cmd.commandtext= "Select COUNT (*) from dbo.region";

Int32 count= (Int32) cmd. ExecuteScalar ();

2 Methods for SqlCommand objects: ExecuteNonQuery method and ExecuteScalar method