What is the role of commandbehavior.closeconnection

"Original address: http://www.cnblogs.com/agian/articles/1648835.html"

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>

Static SqlDataReader Getreader ()

{

Getting a connection from a connection string

SqlConnection con = new SqlConnection (conn_string);

Try

{

Open a connection, execute a query

and return to SqlDataReader

Con. Open ();

SqlCommand cmd = con. CreateCommand ();

Cmd.commandtext = SQL;

SqlDataReader dr = cmd. ExecuteReader ();

Return Dr;

}

Finally

{

The code here is in a dilemma.

If this is done close: con. Close (); that returns the

SqlDataReader 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, in order to demonstrate the functionality, code 9-2 contains two static methods for returning SqlDataReader, one of which passed the CommandBehavior.CloseConnection method when the ExecuteReader method was executed.

Code 9-2 using CommandBehavior.CloseConnection:UseCommandBehavior.cs
Partial class Usecommandbehavior

{

Database View Connection string

Const String conn_string =

"Server=localhost;integrated security=true;database=nettest";

Const String SQL = "SELECT * FROM dbo." Departcost ";

<summary>

Using CommandBehavior.CloseConnection

</summary>

<param name= "Con" > Incoming connection object for testing needs </param>

Static SqlDataReader getreader_closeconnection (SqlConnection con)

{

Try

{

Open a connection, execute a query

and return to SqlDataReader

Con. Open ();

SqlCommand cmd = con. CreateCommand ();

Cmd.commandtext = SQL;

SqlDataReader dr = cmd. ExecuteReader

(commandbehavior.closeconnection);

Return Dr;

}

Finally

{

Because of the use of commandbehavior.closeconnection,

There is no need to close the connection

Con. Close ();

}

}

<summary>

Do not use CommandBehavior.CloseConnection

</summary>

<param name= "Con" > Incoming connection object for testing needs </param>

Static SqlDataReader getreader_nocloseconnection (SqlConnection con)

{

Try

{

Open a connection, execute a query

and return to SqlDataReader

Con. Open ();

SqlCommand cmd = con. CreateCommand ();

Cmd.commandtext = SQL;

SqlDataReader dr = cmd. ExecuteReader ();

Return Dr;

}

Finally

{

To make the returned SqlDataReader available, the connection cannot be closed here

Con. 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.

Code 9-3 using CommandBehavior.CloseConnection:UseCommandBehavior.cs
Partial class Usecommandbehavior

{

<summary>

Test method

</summary>

static void Main (string[] args)

{

Establish a connection

SqlConnection con = new SqlConnection (conn_string);

Try

{

The test uses the CommandBehavior.CloseConnection method

Console.WriteLine ("The test used CommandBehavior.

Method of CloseConnection: ");

SqlDataReader SDR = Getreader_closeconnection (con);

while (SDR. Read ()) {}

Sdr. Close ();

Console.WriteLine ("Connection Status after read:" + con.) State.tostring ());

Testing methods that do not use commandbehavior.closeconnection

Console.WriteLine ("The test does not use CommandBehavior.

Method of CloseConnection: ");

SqlDataReader sdr1 = Getreader_nocloseconnection (con);

while (SDR1. Read ()) {}

Sdr1. Close ();

Console.WriteLine ("Connection Status after read:" +

Con. State.tostring ());

Console.read ();

}

Finally

{

Make sure the connection is turned off

if (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.

What is the role of commandbehavior.closeconnection