Executereader (), executenonquery (), executescalar (), and executexmlreader ()

Executereader (), executenonquery (), executescalar (), and executexmlreader ()
There are several main types: cmd. executereader (); cmd. executenonquery (); cmd. executescalar (); cmd. executexmlreader ();)

1. executereader (); returns a sqldatareader object or oledbdatareader object.Program. You can use this object to check the query results. It provides a "swimming" execution method, that is, after reading a row from the result and moving it to another row, the previous row cannot be reused. One thing to note is that after execution, the datareader object will be moved to the first row of the result set after the read () method is manually called, And a bool value will be returned for this method, indicates whether the next row is available, true is available, and false is returned to the end of the result set.

Datareader can improve the execution efficiency in two ways.CodePerformance: one is serial number-based search, and the other is to use the appropriate get Method for search. Because the query results are generally not changed, unless the query statement is modified again, you can locate the column to find the record. One problem with this method is that you may know the name of a column without knowing its location. The solution to this problem is to call the getordinal () method of the datareader object, this method receives a column name and returns the column number of the column name. Example:

Int id = reader. getordinal ("categoryname ");
While (reader. Read ())
{
Response. Write (Reader [ID]);
}
Reader. Close ();

The second method is intuitive, for example:

While (reader. Read ())
{
Response. Write (reader. getint32 (0). tostring () + "" + reader. getstring (1). tostring () + "<br> ");
}

Getint32 () and getstring () of datareader return the value of a column by receiving a column number. These two types are the most commonly used, and there are many other types.

(Note: The datareader object closes the connection to the database by calling the close () method. If the second connection is re-opened before it is closed, an exception message is generated)

2. executenonquery () does not return a datareader object, but returns an int value, that is, the number of rows affected by the execution in the database.

Example:

Int affectrows = cmd. executenonquery ();
Response. Write (affectrows + "affected records ");

3. The executescalar () method does not accept any parameters. It only returns the first column of the First row in the query result set, ignoring other rows and columns, and returns an object type, before use, you must forcibly convert it to the required type. If only one data element is returned, you can use this method to improve the Code performance. Example:

String strcon = "Server = localhost; database = northwind; trusted_connection = yes ;";
String strqry = "select count (*) from categories ";
Sqlconnection con = new sqlconnection (strcon );
Con. open ();
Sqlcommand cmd = con. createcommand ();
Int I = convert. toint32 (CMD. executescalar (); // mandatory Conversion

4. executexmlreader () This method is used for XML operations and returns an xmlreader object. Because the system does not reference the system. xml namespace by default, it must be introduced before use. Example:

String strcon = "Server = localhost; database = northwind; trusted_connection = yes ;";
Sqlconnection con = new sqlconnection (strcon );
Con. open ();
Sqlcommand cmd = new sqlcommand ("select * from categories for XML auto, xmldata", con );
Xmlreader xr = cmd. executexmlreader ();
Response. Write (XR. attributecount); // obtain the number of attributes on the current node.
XR. Close ();

After the execution is complete, you must call the close () method explicitly. Otherwise, an exception is thrown.

Use parameterized Query

first read an SQL statement: Select categoryid, description from categories where categoryid =? The question mark is a parameter. However, the name parameter with the @ prefix must be used, because the. NET data provider does not support this generic parameter mark "?". Using parameterized queries can greatly simplify programming, and the execution efficiency is higher than that of directly querying strings. In many cases, you need to change the query string. This method provides convenience, you only need to change the parameter value.