ExecuteReader (), ExecuteNonQuery (), ExecuteScalar (), ExecuteXmlReader () The difference between the self-study process

The difference between ExecuteReader (), ExecuteNonQuery (), ExecuteScalar (), ExecuteXmlReader ()
There are mainly so many kinds of cmd. ExecuteReader (); cmd. ExecuteNonQuery (); cmd. ExecuteScalar (); cmd. ExecuteXmlReader ();

1,executereader (); Returns a SqlDataReader object or OleDbDataReader object, which you need to do to see your program. This object can be used to examine the results of the query, which provides a "swimming" way to execute, that is, after reading a row from the result and moving to another row, the previous line is no longer available. One thing to note is that after execution, the DataReader object will not move to the first row of the result set until the read () method is manually invoked, and this method also returns a bool value indicating whether the next row is available, returns True, and returns false to the end of the result set.

Using DataReader can improve execution efficiency, and there are two ways to improve the performance of your code: one is an ordinal lookup, and the other is to use the appropriate get method to find it. Because the results of the query are generally unchanged, unless you change the query statement again, you can locate the record by locating the location of the column. The problem with this approach is that you might know the name of a column without knowing where it is, and the solution is to call the DataReader object's GetOrdinal () method, which receives a column name and returns the column number of the column name. Cases:

int Id=reader. GetOrdinal ("CategoryName");
while (reader. Read ())
{
Response.Write (Reader[id]);
}
Reader. Close ();

As for the second way is very intuitive, for example:

while (reader. Read ())
{
Response.Write (reader. GetInt32 (0). ToString () + "" +reader. GetString (1). ToString () + "<br>");
}

DataReader's GetInt32 () and GetString () return a column's value by receiving a column number, two of which are most commonly used, and many other types.

(Note: The DataReader object closes the connection to the database by calling the close () method, and if the second connection is reopened before it is closed, an exception message is generated)

2.,executenonquery () This method does not return a DataReader object, but instead returns a value of type int, that is, the number of rows affected in the database after execution.

Cases:

int Affectrows=cmd. ExecuteNonQuery ();
Response.Write (Affectrows + "record affected");

3,executescalar () This method takes no arguments, returns only the first column of the first row in the query result set, ignores other rows and columns, and returns an object type that must be cast to the desired type before use. If you are returning only a single data element, you can use this method to improve the performance of your code. Cases:

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 ()); Must cast

4,executexmlreader () This method is used for XML operations and returns a XmlReader object, which must be introduced before it is used, because the system does not reference the System.Xml name space by default. Cases:

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); Here gets the number of attributes on the current node
xr. Close ();

The close () method should be called explicitly after execution, otherwise an exception will be thrown.

Using parameterized queries

Let's look at a section of SQL statement: Select categoryid,description from Categories where categoryid=? The question mark is a parameter. However, it must be used with a named parameter with the @ prefix, because. NET data provider does not support this generic parameter tag "? ". Using parameterized queries can greatly simplify programming, and it is more efficient than direct query strings, and in many cases you need to change the query string, which is convenient, simply by changing the value of the parameter.