ado.net--using connection to connect to the database, using DataReader to access the database and return multiple rows of data

Use connection to connect to the database, use DataReader to access the database, and return multiple rows of data.

Related steps:

2. Need to introduce two namespaces

   using System.Data; using System.Data.SqlClient;

4. To access a database by using DataReader
   • Create SqlConnection object, specify connection string
   • Create a SqlCommand object, specify the connection object associated with it, and the Database Operations Command text (CommandText)
   • Open connection
   • Call the ExecuteReader () method of the SqlCommand object to return the SqlDataReader object
   • Call the Read () function of the SqlDataReader object to read a row of data from the database into the program
   • Access each field in the row data by SqlDataReader the [] operation of the object
   • Loop calls read (), accessing each row of data at once
   • When read () returns False, indicates that all rows have been read
   • Calling the close () function of the SqlDataReader object closes the
   • Call the close () function of the SqlConnection object to close the database connection
6. DataReader Mode of operation
   • DataReader object is working with a "forward read-only (forwardonly)" cursor
   • Before calling the read () function, all data is located on the database server, and each time read () is called, a data is downloaded from the server
   • The connection to the database cannot be disconnected until the DataReader has finished processing the data. Once disconnected, DataReader is no longer able to download data from the database
   • The advantage of this mode is that there is less memory required in the program (because only one piece of data is saved at a time) and read-only cursor access is fast
   • The disadvantage of this mode is that it needs to remain connected to the database and consumes more resources from the database.

code example:

1 usingSystem;2 usingSystem.Collections.Generic;3 usingSystem.Linq;4 usingSystem.Text;5 usingSystem.Threading.Tasks;6 usingSystem.Data;7 usingSystem.Data.SqlClient;8 9 namespaceConsoleApplication1Ten { One     class Program A     { -         Static voidMain (string[] args) -         { the             //1. Create a Connection object -             stringstrconn =@"Server=joe-pc;database=adventureworks_wroxssrs2012;uid=sa;pwd=root"; -SqlConnection conn =NewSqlConnection (strconn); -  +             //2. Create a Command object -             stringStrcmd ="SELECT productcategoryid,name from Production.productcategory"; +SqlCommand cmd =NewSqlCommand (strcmd,conn); A  at             //3. Open the database connection - Conn. Open (); -  -             //4. Execution of orders -SqlDataReader dr =cmd. ExecuteReader (); -  in             //5. Read each line of data -              while(Dr. Read ()) to             { +                 //use the [] operator to get the value of the specified field (object type, convertible to other desired type) -Console.WriteLine ("{0}:{1}", dr["ProductCategoryID"], dr["Name"]); the             } *  $             //6. Close the DataReader objectPanax Notoginseng             //Dr. Cloase (); -  the             //7. Close the database connection +             //Even if the DR is not called. Close (), Conn. Close () also forces Dr to close A Conn. Close (); the         } +     } -}

PS: Database connection Management

Most databases only support a limited number of connections, and to avoid resource consumption, the connection should be closed in a timely manner after the operation of the database is completed. Therefore, you must determine the Open and Close methods in pairs to invoke the connection object. In order to effectively manage the opening and closing of a database connection, you can also use the following two methods.

• Using the try...catch...finally statement block

In the try...catch...finally statement block, use the Try statement to open the database connection, catch the exception using a Catch statement, and use the finally statement to ensure that the connection to the database is closed.

1             stringstrconn =@"Server=joe-pc;database=adventureworks_wroxssrs2012;uid=sa;pwd=root";2SqlConnection conn =NewSqlConnection (strconn);3 4             Try5             {6 Conn. Open ();7                 //Execute Database Operations Command8             }9             Catch(Exception ex)Ten             { One                 //exception Handling If an exception occurs while opening the connection A             } -             finally -             { the Conn. Close (); -}

• Using a using statement block

For more efficient management of connections to databases, C # provides a using statement block that automates the management of database connections. When data access is complete, the using statement first automatically closes the data source connection and then releases the connection object, thus greatly simplifying programming.

 1  string  strconn = @ " server=joe-pc;database=adventureworks_wroxssrs2012;uid=sa;pwd=root   ;  2  us ing  (SqlConnection conn = new    { 
     
      5 
       Conn. Open ();  6   Execute database Operations Command  7 } 

ado.net--using connection to connect to the database, using DataReader to access the database and return multiple rows of data