CommandBehavior.CloseConnection use

It is used in ExecuteReader (c), cannot close the database connection before returning the object, must use the commandbehavior.closeconnection;

This is a question about the actual knowledge point, the interviewer examines the candidate database access programming experience. This section will carry out a specific analysis of this issue. For this kind of specific knowledge points of the problem, the reader should pay attention to accumulation in peacetime, so that in the interview can calmly answer.

The points of knowledge involved

Use of CommandBehavior.CloseConnection

Analyze problems

Because of the characteristics of the streaming mode read database, it is difficult to determine when the database connection can be closed because the read action is continuous, and the following is a static method of a common data access layer:

/// <summary>///common ways to get SqlDataReader///The usual data access layer will provide this method/// </summary>StaticSqlDataReader Getreader () {//getting a connection from a connection stringSqlConnection con=NewSqlConnection (conn_string); Try        {                //Open a connection, execute a query//and return to SqlDataReadercon.                Open (); SqlCommand cmd=con.                CreateCommand (); Cmd.commandtext=SQL; SqlDataReader Dr=cmd.                ExecuteReader (); returnDr; }        finally        {                //the code here is in a dilemma.//If this is done close: con. Close (); that returns theSqlDataReader will be of no avail, for its//dependent connections are closed//if con is not executed here. Close (); The connection after the return//will never be closed because the caller cannot//Get the Connection object            }}

As described in the code comments, such a method can neither close the connection nor keep the connection open. In order to solve this dilemma, many systems can only discard data sources that use reader mode, or give the connection object to the method caller for closure.

And CommandBehavior.CloseConnection's function is precisely to avoid a similar awkward situation, it can ensure that when the SqlDataReader object is closed, its dependent connection will be automatically closed. Code 9-2 shows the difference between using commandbehavior.closeconnection and not using CommandBehavior.CloseConnection.

This is illustrated here as an example of SqlDataReader, whose function is similar for Xxxdatareader objects under other namespaces.

First, to demonstrate the functionality, code 9-2 contains two static methods for returning SqlDataReader, one of which passed the CommandBehavior.CloseConnection method class when the ExecuteReader method was executed. Usecommandbehavior

 { //  const  string conn_string =  server=localhost;integrated security=true;database=nettest   " ;  const  String Sql =  select * FROM dbo.       Departcost   ; 
   
  

//<summary>/// use commandbehavior.closeconnection //</summary>///<param Name= "Con" > for testing needs, incoming connection object </param>static  SqlDataReader getreader_closeconnection (SqlConnection con) { Try  {//Open connection, execute Query//and return SqlDataReader  con. Open (); SqlCommand cmd =  con. CreateCommand (); cmd.commandtext =  Sql; SqlDataReader dr =  cmd. ExecuteReader (commandbehavior.closeconnection); return  Dr;} Finally  {//Because commandbehavior.closeconnection,//is used here does not need to close the connection//con. Close (); }}          

do not use commandbehavior.closeconnection//</summary>///<param name= "con" > Incoming connection object for test needs </ Param>static SqlDataReader getreader_nocloseconnection (SqlConnection con) {try{//Open connection, execute Query// and returns SqlDataReadercon. Open (); SqlCommand cmd = con. CreateCommand (); cmd.commandtext = Sql; SqlDataReader dr = cmd. ExecuteReader (); return Dr;} Finally{//In order to make the returned SqlDataReader available, the connection//con cannot be closed here. Close (); }}}

As you can see, two methods do not end the connection at all, regardless of whether you use CommandBehavior.CloseConnection, but they do not close the connection for the same reason. After two methods have been prepared, the two methods are called from the Main method to test each other. To see if the SqlDataReader object returned from the method using commandbehavior.closeconnection automatically closes the connection while it is closed, as shown in code 9-3.

Write test methods to test:

classusecommandbehavior{/// <summary>///test Method/// </summary>Static voidMain (string[] args) {//Establish a connectionSqlConnection con=NewSqlConnection (conn_string);Try{//the test uses the CommandBehavior.CloseConnection methodConsole.WriteLine ("the test uses CommandBehavior.methods of CloseConnection:");SqlDataReader SDR=getreader_closeconnection (con); while(SDR). Read ()) {}SDR. Close (); Console.WriteLine ("the connection status after reading is complete:"+con. State.tostring ());//testing methods that do not use commandbehavior.closeconnectionConsole.WriteLine ("The test did not use CommandBehavior.methods of CloseConnection:");SqlDataReader SDR1=getreader_nocloseconnection (con); while(SDR1. Read ()) {}sdr1. Close (); Console.WriteLine ("the connection status after reading is complete:"+con. State.tostring ()); Console.read ();}finally{//Make sure the connection is turned offif(Con. State! =connectionstate.closed) con. Close (); }}}

Here is the result of the code execution:

The test uses the CommandBehavior.CloseConnection method:

Connection status after read: Closed

The test does not use the CommandBehavior.CloseConnection method:

Connection status after read: Open

As the reader has seen, using the SqlDataReader object obtained by CommandBehavior.CloseConnection will automatically close its dependent database connection object while shutting down, which solves the dilemma of data access layer writing.

Answer

commandbehavior.closeconnection resolves a situation in which the database connection cannot be closed effectively in streaming read data mode. When a Xxxdatareader object is built with CommandBehavior.CloseConnection, the database connection is automatically closed when the Xxxdatareader object is closed.

CommandBehavior.CloseConnection use