ASP.net 2.0 Execute database Operations Command bis

SqlDataReader class

You can read rows from a SQL Server database with the SqlDataReader class object, and read rows from a database that supports OLE DB with the OleDbDataReader class object, such as Oracle and access ; Read rows from an ODBC-enabled database using the OdbcDataReader class object.

The DataReader object allows you to read data in a forward, read-only way, and sometimes the DataReader object is also called a hose cursor. The DataReader object uses a simplified way of reading data, but improves performance while sacrificing a lot of features. such as sorting, paging, and so on that are supported in the dataset. These features will be described in more detail in a later section.

Properties of SqlDataReader

Property	Description
Depth	whose return type is int, gets the value that represents the current line's embedded depth
FieldCount	The return type is int to get the number of columns in the current row
Iscolsed	The return type is bool, obtaining a Boolean value indicating whether to turn off data reading
RecordsAffected	The return type is int, and gets the number of rows that were added, modified, or deleted by the execution SQL statement.

The method of SqlDataReader

Method	Description
Reader ()	The return type is bool, 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 ()	Whose return type is object, returns the value of the specified column
GetValues ()	The return type is int, and the values of all columns in the current row are copied to the specified object array. The int that this method returns is the number of elements in the array
NextResult ()	The return type is bool, and 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 () off	SqlDataReader objects
GetInt32 (), GetChar (), Gatedatatime (), getxxx ()	Returns the value of the specified column, and the returned type is the appropriate data type. For example, GetInt32 () returns the numeric value of an integral type. Note that if you assign a return value to a variable that does not match a type, a InvalidCastException exception is thrown

　　 executing a query with the ExecuteReader () method

The following is an example of executing a SELECT statement with the ExecuteReader () method. This method returns the result set with the DataReader object and can then read the rows returned by the database with this object.

The sample program code is as follows:

Partial class _default:system.web.ui.page 02 { protected void Page_Load (object sender, EventArgs e) 04 { A String connectionString = configurationmanager.connectionstrings["Northwind"]. ConnectionString; Modified SqlConnection con = new SqlConnection (connectionString); ' sql = ' select Top 5 customerid,companyname,contactname,address From Customers "; SqlCommand cmd = new SqlCommand (sql, con); One con. Open (); SqlDataReader reader = cmd. ExecuteReader (); StringBuilder htmlstr = new StringBuilder (""); While reader. Read ()) 15 { Htmlstr.append ("CustomerID:" + reader["CustomerID"] + "<br>"); Htmlstr.append ("CompanyName:" + reader["CompanyName"] + "<br>"); Htmlstr.append ("ContactName:" + reader.) GetString (2) + "<br>"); Htmlstr.append ("Address:" + reader.) GetString (3) + "<br>"); Htmlstr.append ("21} Reader. Close (); Con. Close (); Htmlcontent.text = Htmlstr.tostring (); 25} 26}

　　 Program code Description: In the program code of the syntax example above, the 5th to 12th line of code generates the desired object and executes the SELECT statement, reading the first 5 records from the Customers table. The result set returned by CMD is stored in the reader object, and you can then read the reader object's record using the Reader () method. This method returns a Boolean true value when there is another readable row, otherwise it returns a bool. You can read the individual column values of a record from the reader object, as long as you pass in the column names in brackets. As shown in lines 16th and 17, we read the contents of the CustomerID column with reader["CustomerID". You can also pass a numeric value directly in brackets to specify the index you want the column to be. As shown in lines 18th and 19, the ContactName and address are in columns 3rd and 4th respectively, and the corresponding index values are 2 and 3, so we can use reader, as we are querying the data using SELECT. GetString (2) and reader. GetString (3) reads data from ContactName and address columns. As the 14th line of code shows, we can read each record in the while loop with reader () method one by one.

Execution results:

Each time a program executes a command, it passes the corresponding command over the network to the database, executes it in the database, and returns the results to the program, resulting in a large amount of network traffic. We can use the ExecuteReader () method to simultaneously execute multiple SELECT statement queries to reduce duplication of data delivery.

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

The sample program code is as follows:

Partial class _default:system.web.ui.page 02 { protected void Page_Load (object sender, EventArgs e) 04 { A String connectionString = configurationmanager.connectionstrings["Northwind"]. ConnectionString; Modified SqlConnection con = new SqlConnection (connectionString); SqlCommand cmd = con. CreateCommand (); Cmd.commandtext = "SELECT Top 3 productid,productname From the products order by ProductID; "+ One "SELECT Top 3 Customerid,companyname From Customers order by CustomerID; "+ "SELECT Top 3 Orderid,customerid From Orders by OrderID; "; Con. Open (); SqlDataReader reader = cmd. ExecuteReader (); StringBuilder htmstr=new StringBuilder (""); int i = 0; Do 20 { Htmstr.append ("result set"); Htmstr.append (i.ToString ()); Htmstr.append ("<br>"); While reader. Read ()) 25 { Htmstr.append ("reader[0]=" + reader[0]); Htmstr.append ("<br>"); Htmstr.append ("reader[1]=" + reader[1]); Htmstr.append ("<br><br>"); 30} Htmstr.append ("i++; While reader. NextResult ()); Reader. Close (); Con. Close (); Htmlcontent.text = Htmstr.tostring (); 37} 38}

　　 Program code Description: In the program code of the syntax example above, 3 query statements are defined in lines 9th through 14th, separated by semicolons. Line 16th calls the ExecuteReader () method, returns the SqlDataReader object, and returns a result set for each of the three different select statements. To read the first result set, you can use the reader () method of the SqlDataReader object. The Reader () method returns a value of false when no other readable row is available. When all the records of a result set are read, you can call the SqlDataReader object's NextResult () method, and then when you read the next result set, return a value of false when there are no more result sets.

Tip: The outer loop do...while the reader at the end of the test. The return value of the NextResult (). Because this condition is detected at the end of the Do...while loop, this ensures that the Do...while loop executes at least once. The NextResult () method is called at the end because it allows you to move the SqlDataReader object to the next result set first before returning a Boolean result that indicates whether the next result set is still available. If you are using a while loop, it is possible to skip the first result set directly, resulting in an error.

To execute a SELECT statement with the ExecuteScalar () method

Executes a SELECT statement with the ExecuteScalar () method, returns a single value, and ignores any other read results. The result returned by the ExecuteScalar () method is an object. ExecuteScalar () is used primarily to execute SELECT statements, or to execute SQL statements that include aggregate functions.

Below we will use the program below to read the number of record bars in the Products table and use the count () aggregate function in the corresponding query statement.

The sample program code is as follows:

Partial class _default:system.web.ui.page 02 { protected void Page_Load (object sender, EventArgs e) 04 { A String connectionString = configurationmanager.connectionstrings["Northwind"]. ConnectionString; Modified SqlConnection con = new SqlConnection (connectionString); SqlCommand cmd = con. CreateCommand (); Cmd.commandtext = "Select COUNT (*) from the products"; Ten con. Open (); one int returnvalue = (int) cmd. ExecuteScalar (); Htmlcontent.text = "All Products table in total" + returnvalue.tostring () + "record"; 13} 14}

Program code Description: In the program code of the syntax example above, line 9th is an SQL statement that queries the number of records in the Products table. The 11th line of code executes the SELECT statement with the ExecuteScalar () method. Note that because the result of the ExecuteScalar () method is an object, we want to force the type conversion and then give the corresponding variable.

Execution results: