Ado. NET QuickStart--returning DataReader as a function return value

Related knowledge:

1. In many cases, access to the database may be encapsulated in a function that returns a DataReader object to the caller. For example, define a function: SqlDataReader Returndr (), and then call it in the main function.
2. But here's the problem: Where do I close a database connection?
   • When you close connection in the Returndr () function, you will not be able to get each row of data in the main function;
   • The connection is closed in the main function, but the main function cannot access the SqlConnection object defined inside the Returndr () function (unless the SqlConnection object is declared as a member variable of the class, but doing so will complicate the class)
3. In fact, the ExecuteReader function provides an overloaded way to Allows you to specify the association behavior of DataReader and connection objects through parameters CommandBehavior.CloseConnection: Once the DataReader object is closed, the connection object associated with the object is automatically closed.

Example code:

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 namespaceConsoleApplication2Ten { One     class Program A     { -         //functions that return DataReader -         StaticSqlDataReader Returndr () the         { -             stringstrconn =@"Server=joe-pc;database=adventureworks_wroxssrs2012;uid=sa;pwd=root"; -SqlConnection conn =NewSqlConnection (strconn); -  +             stringStrcmd ="SELECT productcategoryid,name from Production.productcategory"; -SqlCommand cmd =NewSqlCommand (STRCMD, conn); +  A Conn. Open (); at  -SqlDataReader dr =cmd. ExecuteReader (); -  -             returnDr; -         } -  in         //called in main -         Static voidMain (string[] args) to         { +SqlDataReader dr =Returndr (); -              while(Dr. Read ()) the             { *Console.WriteLine ("{0}:{1}", dr["ProductCategoryID"], dr["Name"]); $             }Panax Notoginseng Dr. Close (); -             //the ExecuteReader function provides an overloaded form the             //allows you to specify the association behavior of DataReader and connection objects through parameters commandbehavior.closeconnection: +             //Once the DataReader object is closed, the connection object associated with the object is also automatically closed A         } the     } +}

Ado. NET QuickStart--returning DataReader as a function return value