Execute database operation command 2 in ASP. NET 2.0

Sqldatareader class

You can use the sqldatareader class object to read rows from the SQL Server database; Use the oledbdatareader class object to read rows from databases that support OLE DB, such as Oracle and access; use the odbcdatareader class object to read rows from databases that support ODBC.

The datareader object allows you to read data in a forward, read-only manner. Sometimes the datareader object is also called a hose cursor. The datareader object adopts a simplified data reading method, but it improves performance and sacrifices many features. For example, sorting and paging are supported in dataset. These functions will be described in detail in subsequent sections.

Sqldatareader attributes

Attribute	Description
Depth	The return type is int, which indicates the current row Embedding depth.
Fieldcount	The return type is int, and the number of columns in the current row is obtained.
Iscolsed	The return type is bool, and a Boolean value is obtained, indicating whether to disable Data Reading.
Recordsaffected	The return type is int, and the number of rows that are added, modified, or deleted by executing the SQL statement is obtained.

Sqldatareader Method

Method	Description
Reader ()	The return type is bool. It moves the data reader to the next row of the result set and reads the row. The Boolean value returned by this method indicates whether there are multiple rows in the result set.
Getvalue ()	The returned type is object, and the value of the specified column is returned.
Getvalues ()	The return type is int, which copies the values of all columns in the current row to the specified object array. The Int value returned by this method is the number of array elements.
Nextresult ()	The return type is bool. The data reader is moved to the next row of the result set. The Boolean value returned by this method indicates whether there are multiple rows in the result set.
Close () Close	Sqldatareader object
Getint32 (), getchar (), Gatedatatime (), get ××× ()	Returns the value of the specified column, and the returned type is the corresponding data type. For example, getint32 () returns the integer value. Note: If you assign the return value to a variable with mismatched types, an invalidcastexception will be thrown.

Execute the query using executereader () method

The following is an example of executing a SELECT statement using the executereader () method. This method uses the datareader object to return the result set, and then you can use this object to read the rows returned by the database.

ExampleProgramCodeAs follows:

01 public partial class _ default: system. Web. UI. Page 02 { 03 protected void page_load (Object sender, eventargs E) 04 { 05 string connectionstring = 06 configurationmanager. connectionstrings ["northwind"]. connectionstring; 07 sqlconnection con = new sqlconnection (connectionstring ); 08 string SQL = "select top 5 mermerid, companyName, contactname, address 09 from customers "; 10 sqlcommand cmd = new sqlcommand (SQL, con ); 11 con. open (); 12 sqldatareader reader = cmd. executereader (); 13 stringbuilder htmlstr = new stringbuilder (""); 14 While (reader. Read ()) 15 { 16 htmlstr. append ("customerid:" + reader ["customerid"] + "<br> "); 17 htmlstr. append ("companyName:" + reader ["companyName"] + "<br> "); 18 htmlstr. append ("contactname:" + reader. getstring (2) + "<br> "); 19 htmlstr. append ("Address:" + reader. getstring (3) + "<br> "); 20 htmlstr. append ("<HR> "); 21} 22 reader. Close (); 23 con. Close (); 24 htmlcontent. Text = htmlstr. tostring (); 25} 26}

Program Code Description: In the program code of the preceding syntax example, lines 5th to 12 generate the desired object and execute the SELECT statement to read the first five records from the MERs table. The result set returned by CMD is stored in the reader object, and you can use the reader () method to read the records of the reader object. This method returns the Boolean true value when there is another readable row, otherwise it returns the Boolean false value. You can read the column values of a record from the reader object. You only need to input the column names in square brackets. As shown in rows 16th and 17, we use reader ["mermerid"] to read the contents of the customerid column. You can also directly input numeric values in square brackets to specify the index of the desired column. As shown in lines 18th and 19, since we use select to query data, contactname and address are in columns 3rd and 4th respectively, the corresponding index values are 2 and 3, so we can use reader. getstring (2) and reader. getstring (3) reads data from the contactname and address columns. As shown in the Code in line 14th, we can use the reader () method in the while loop to read each record one by one.

Execution result:

Each time a program executes a command, it must pass the corresponding command to the database through the network, execute the command in the database, and then return the result to the program, this produces a large number of network communication streams. We can use the executereader () method to execute multiple SELECT statement queries at the same time to reduce repeated data transmission.

The following example uses the executereader () method to query data in three tables at the same time, and displays the returned three result sets on the page.

The sample code is as follows:

01 public partial class _ default: system. Web. UI. Page 02 { 03 protected void page_load (Object sender, eventargs E) 04 { 05 string connectionstring = 06 configurationmanager. connectionstrings ["northwind"]. connectionstring; 07 sqlconnection con = new sqlconnection (connectionstring ); 08 sqlcommand cmd = con. createcommand (); 09 cmd. commandtext = "select top 3 productid, productname 10 from products order by productid; "+ 11 "select top 3 customerid, companyName 12 from MERs order by customerid; "+ 13 "select top 3 orderid, customerid 14 from orders order by orderid ;"; 15 con. open (); 16 sqldatareader reader = cmd. executereader (); 17 stringbuilder htmstr = new stringbuilder (""); 18 int I = 0; 19 do 20 { 21 htmstr. append ("result set "); 22 htmstr. append (I. tostring ()); 23 htmstr. append ("<br> "); 24 while (reader. Read ()) 25 { 26 htmstr. append ("Reader [0] =" + reader [0]); 27 htmstr. append ("<br> "); 28 htmstr. append ("Reader [1] =" + reader [1]); 29 htmstr. append ("<br> "); 30} 31 htmstr. append ("<HR> "); 32 I ++; 33} while (reader. nextresult ()); 34 reader. Close (); 35 con. Close (); 36 htmlcontent. Text = htmstr. tostring (); 37} 38}

Program Code Description: In the program code of the preceding syntax example, three query statements are defined in rows 9th to 14th, and each statement is separated by a semicolon. Line 3 calls the executereader () method, returns the sqldatareader object, and returns a result set for each of the three select statements. To read the first result set, you can use the reader () method of the sqldatareader object. The reader () method returns a false value when there are no other readable rows. After all records in a result set are read, you can call the nextresult () method of the sqldatareader object and then read the next result set without any other result sets, returns a false value.

Tip: do... The return value of reader. nextresult () at the end of the while test. Because do... This condition is detected at the end of the while loop to ensure that do... The while loop must be executed at least once. The nextresult () method is called at the end, because the sqldatareader object can be moved to the next result set first, and then a boolean result indicating whether the next result set exists. If the while loop is used, the first result set may be skipped directly to generate an error.

Execute the SELECT statement using the executescalar () method

Execute the SELECT statement using the executescalar () method, return a single value, and ignore any other read results. The result returned by the executescalar () method is an object. Executescalar () is mainly used to execute select statements or execute SQL statements including Aggregate functions.

Next we will use the following program to read the number of records in the products table, and use the count () Aggregate Function in the corresponding query statement.

The sample code is as follows:

01 public partial class _ default: system. Web. UI. Page 02 { 03 protected void page_load (Object sender, eventargs E) 04 { 05 string connectionstring = 06 configurationmanager. connectionstrings ["northwind"]. connectionstring; 07 sqlconnection con = new sqlconnection (connectionstring ); 08 sqlcommand cmd = con. createcommand (); 09 cmd. commandtext = "select count (*) from products "; 10 con. open (); 11 int returnvalue = (INT) cmd. executescalar (); 12 htmlcontent. Text = "A total of" + returnvalue. tostring () + "records" in the products table "; 13} 14}

Program Code Description: In the program code of the preceding syntax example, the first line is the SQL statement used to query the number of records in the products table. The first line of code uses the executescalar () method to execute the SELECT statement. Note that because the result of the executescalar () method is an object, we need to force type conversion and then assign the corresponding variable.

Execution result: