ASP. NET executescalar, executenonquery

Executescalar and executenonquery

If you want to return a single value like Count (*), sum (price), or AVG (Quantity), you can use command.Executescalar. Executescalar returns the value of the first column of the First row and returns the result set as a scalar value. Executescalar is simplified because it can be completed in one step.CodeAnd improves the performance. If datareader is used, two steps are required (that is, executereader + value ).

When using SQL statements that do not return rows, such as modifying data (such as insert, update, or delete) or returning only output parameters or return values, useExecutenonquery. This avoids any unnecessary processing for creating an empty datareader.

Test null

If columns in a table (in a database) can be empty, you cannot test whether the parameter value is "equal to" null. On the contrary, you need to write a where clause to test whether the columns and parameters are empty. The following SQL statement returns some rows whose lastname column is equal to the value assigned to the @ lastname parameter, or the lastname column and @ lastname parameters are empty.

Select * from MERs
Where (lastname = @ lastname) or (lastname is null and @ lastname is null ))

The sqlcommand. executenonquery method executes a Transact-SQL statement on the connection and returns the number of affected rows.
For update, insert, and delete statements, the returned value is the number of rows affected by the command. For all other types of statements, the return value is-1. If rollback occurs, the returned value is-1.
Therefore, when querying whether a table contains (a certain) data, you must not use the select SQL statement + cmd. executenonquery () to determine whether the return value is greater than 0.
Solution:
1.

Sqlcecommand. executescalar method (+ Aggregate Query)

Execute the query and return the first column in the first row of the returned result set. Ignore extra columns or rows. For example, an aggregate value.

Cmd. commandtext = "select count (*) from users where id = 10000 ;"

Int COUNT = (INT) cmd. executescalar ();

2. Use the excutereader () method to return a reader

If (reader. Read () = false ){...}