Return Value of the dbcommand. executescalar Method

Dbcommand. executescalar Method

Msdn describes the dbcommand. executescalar method as follows:

 
Execute the query and return the first column in the first row of the returned result set. All other columns and rows are ignored.Syntax: Public abstract object executescalar ()Return Value: Type: system. object, the first column in the first row of the result set.Remarks: Use the executescalar method to retrieve a single value (for example, an aggregate value) from the database ). This operation requiresCodeRelatively small. If the first column in the first row of the result set is not found, null reference (nothing in Visual Basic) is returned ). If the value in the database is null, this query returns dbnull. value.
 

Prepare test cases

Let's test it. First, prepare the following create-table-Keywords. SQL:

1 Create TableKeywords (2Keyword_id serialPrimary Key,3KeywordVarchar(40)Not Null,4Unique Key(Keyword)5)
 

The preceding SQL statement comes from Chapter 17th of SQL anti-pattern, poor search engine section 5th solution: use the correct tool (page 159th ).

Then, run the following SQL command in the MySQL 5.5.16 database of opensuse 12.1:

 
Ben @ vbox :~ /Work/SQL-antipatterns>Mysql-U test-ppwd-for-testMysql>Source create-table-Keywords. SQL;Query OK, 0 rows affected (0.18 Sec) mysql>Desc keywords;+ ------------ + Bytes + ------ + ----- + --------- + ---------------- + | FIELD | type | null | key | default | extra | + ------------ + ----------------- + ------ + ----- + ----------- + ------------------ + | keyword_id | bigint (20) unsigned | no | pri | null | auto_increment | keyword | varchar (40) | no | uni | null | + ------------ + --------------------- + ------ + ----- + --------- + ------------------ + 2 rows in SET (0.01 Sec) mysql>Insert into keywords (keyword) values ('crash ');Query OK, 1 row affected (0.10 Sec)
 

Test in mysql client

Then, execute the following three SQL select statements in the mysql client:

 
Mysql>Select keyword_id from keywords where keyword = 'crash ';+ ------------ + | Keyword_id | + ------------ + | 1 | + ------------ + 1 row in SET (0.01 Sec) mysql>Select keyword_id from keywords where keyword = 'aborted ';Empty set (0.00 Sec) mysql>Select max (keyword_id) from keywords where keyword = 'aborted ';+ ----------------- + | Max (keyword_id) | + ------------------- + | null | + ----------------- + 1 row in SET (0.01 Sec) mysql>
 

The first select statement returns an existing keyword_id value. The second SELECT statement returns an empty result set. The third SELECT statement returns a row of data, but its value is null, this is because the SQL max () function is working. Note that because the keyword column of the keywords table has a unique index, the preceding three select statements can return only one row at most and cannot have multiple rows.

Test in ADO. net

We use the following C #ProgramTester. CS to test the preceding three SQL select statements:

 1  Using  System;  2   Using  MySQL. Data. mysqlclient;  3   4   Namespace  Skyiv. Test  5   {  6     Static   Class  Tester  7   { 8       Static   Void  Main ()  9   {  10 Test ( "  Select keyword_id from keywords where keyword = 'crash'  "  );  11 Test ( "  Select keyword_id from keywords where keyword = 'aborted'  " );  12 Test ( "  Select max (keyword_id) from keywords where keyword = 'aborted'  "  );  13   }  14   15       Static   Void Test ( String  SQL)  16   { 17         Using ( VaR Conn = New Mysqlconnection ( "  Server = localhost; user = test; Password = PWD-for-test; database = test  "  ))  18         Using ( VaR Comm = Conn. createcommand ())  19   {  20  Conn. open ();  21 Comm. commandtext = SQL;  22           VaR Result = Comm. executescalar ();  23 Console. writeline ( "  Type: {0,-13} dbnull: {1,-5} NULL: {2,-5} value: [{3}]  "  ,  24 (Result = Null )? " (Null)  "  : Result. GetType (). tostring (),  25 Result Is  Dbnull, result =Null, Result );  26   }  27   }  28   }  29 }

Compile and run mono 2.10.6. The result is as follows:

Ben @ vbox :~ /Work/SQL-antipatterns>DMCS tester. CS-R:/home/BEN/repo/dll/MySQL. Data. dll & mono tester.exeType: system. uint64 dbnull: false NULL: false value: [1] type :( null) dbnull: false NULL: True Value: [] type: system. dbnull: True NULL: false value: [] Ben @ vbox :~ /Work/SQL-antipatterns>
 

From the above running results, we can see that:

• For the second SELECT statement, the return value of the dbcommand. executescalar method is null. The caller must use result = NULL to judge.
• For the third SELECT statement, the return value of the dbcommand. exceutescalar method is dbnull. value. The caller must use result is dbnull or result = dbnull. Value to judge.

For ADO.. Net dbcommand. for callers of the executescalar method, select keyword_id and select max (keyword_id) are equally convenient, however, pay attention to whether to use result = NULL or result is dbnull Based on the SQL statement used to determine whether the query result is null. Therefore, we recommend that you use the select keyword_id SQL statement to reduce one SQL max () call and improve the running efficiency.

Of course, if result = NULL | result is dbnull is used to determine whether the query result is null, it is more secure and can adapt to the writing of these two SQL statements, however, the running efficiency is slightly lower.

If you use the dbcommand. excutereader method to obtain the query result:

• For the second SELECT statement, you need to judge the return value of the dbdatareader. Read method to determine whether the query result is null.
• For the third SELECT statement, the return value of the dbdatareader. Read method is always true. Instead, the dbdatareader. isdbnull method is used to determine whether the query result is null.

Of course, we do not recommend using the dbcommand. excutereader method when the query result contains a maximum of one row and one column. The dbcommand. excutereader method is required only when the query result may have multiple rows or multiple columns in the query result.

Usage in SQL anti-Pattern

On the 159th page of SQL anti-pattern, the max () function is used in the following stored procedures:

 1   Create   Procedure Bugssearch (Keyword Varchar ( 40  ))  2   Begin 3     Set   @ Keyword   =  Keyword;  4     Prepare S1 From   '  Select max (keyword_id) into @ k from keywords where keyword =?  '  ;  5     Execute S1 using @ Keyword  ; 6     Deallocate   Prepare  S1;  7     If ( @ K   Is   Null ) Then  8       --  (Several statements are omitted here)  9     End   If  ; 10     --  (Several statements are omitted here)  11   End 

It seems that the max () function here cannot be omitted. Although the keyword column in The keyswords table has a unique index, there cannot be multiple rows in the query results, but the query results may be empty sets, therefore, you need to use the max () function to convert the null query result to a query result with a row of null values.

References

1. Msdn: dbcommand. executescalar method (system. Data. Common)
2. Msdn: dbcommand. executereader method (system. Data. Common)
3. Msdn: dbdatareader. Read method (system. Data. Common)
4. Msdn: dbdatareader. isdbnull method (system. Data. Common)
5. Msdn: dbnull. Value Field (system)
6. Douban: SQL reverse mode