Commandbehavior. closeconnection

Due to the characteristics of stream mode reading the database, it is difficult to determine when the database connection will be closed in a specific application, because the read operation is continuous, the following is a common static method at the data access layer:

/// <Summary> /// Common methods for obtaining sqldatareader /// This method is usually provided at the data access layer. /// </Summary> Static sqldatareader getreader () { // Obtain the connection through the connection string Sqlconnection con = new sqlconnection (conn_string ); Try { // Open the connection and execute the query // And return 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 you disable con. Close ();, Sqldatareader will be useless because of its // The dependent connection is closed. // If con. Close () is not executed here, the connection will be connected after the result is returned. It will never be closed because the caller cannot // Obtain the connection object } }

As described in the code comment, such a method neither closes the connection nor keeps the connection open. To solve this dilemma, many systems can only discard the data source in reader mode or hand over the connection object to the method caller for closure.

The commandbehavior. closeconnection function is designed to avoid similar embarrassment. It can ensure that when the sqldatareader object is closed, the connection on which it depends will be automatically closed. Code 9-2 demonstrates the differences between commandbehavior. closeconnection and commandbehavior. closeconnection.

The following uses sqldatareader as an example to describe the functions of xxxdatareader objects in other namespaces.

To demonstrate the function, Code 9-2 contains two static sqldatareader return methods, one of which is passed in the commandbehavior. closeconnection method when executing the executereader method.

Code 9-2 Use commandbehavior. closeconnection: usecommandbehavior. CS

Partial class usecommandbehavior { // View the connection string in the database Const string conn_string = "Server = localhost; Integrated Security = true; database = nettest "; Const string SQL = "select * From DBO. Revoke cost "; /// <Summary> /// Use commandbehavior. closeconnection /// </Summary> /// <Param name = "con"> input a connection object for test purposes </param> Static sqldatareader getreader_closeconnection (sqlconnection con) { Try { // Open the connection and execute the query // And return sqldatareader Con. open (); Sqlcommand cmd = con. createcommand (); Cmd. commandtext = SQL; Sqldatareader DR = cmd. executereader (Commandbehavior. closeconnection ); Return Dr; } Finally { // Commandbehavior. closeconnection, // You do not need to close the connection here // Con. Close (); } } /// <Summary> /// Do not use commandbehavior. closeconnection /// </Summary> /// <Param name = "con"> input a connection object for test purposes </param> Static sqldatareader getreader_nocloseconnection (sqlconnection con) { Try { // Open the connection and execute the query // And return 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 (); } } }

We can see that no matter whether commandbehavior. closeconnection is used or not, the two methods do not close the connection, but they do not close the connection for the same reason. After preparing the two methods, call the two methods in the main method to test the methods. whether the sqldatareader object returned by the closeconnection method is automatically closed when it is closed, as shown in code 9-3.

Code 9-3 Use 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 { // Test the commandbehavior. closeconnection method. Console. writeline ("commandbehavior. Closeconnection method :"); Sqldatareader SDR = getreader_closeconnection (CON ); While (SDR. Read ()){} SDR. Close (); Console. writeline ("connection status after reading:" + con. state. tostring ()); // Test whether commandbehavior. closeconnection is used. Console. writeline ("commandbehavior is not used in the test. Closeconnection method :"); Sqldatareader sdr1 = getreader_nocloseconnection (CON ); While (sdr1.read ()){} Sdr1.close (); Console. writeline ("connection status after reading:" + Con. state. tostring ()); Console. Read (); } Finally { // Make sure the connection is closed If (con. State! = Connectionstate. Closed) Con. Close (); } } }

The following is the code execution result:

Test the commandbehavior. closeconnection method:

Connection status after reading: Closed

To test whether commandbehavior. closeconnection is used:

Connection status after reading: Open

As you can see, commandbehavior is used. when the sqldatareader object obtained by closeconnection is closed, it will automatically close the database connection object it depends on. This feature solves the dilemma in writing the data access layer.

Answer

Commandbehavior. closeconnection solves the problem that the database connection cannot be effectively closed in the stream read data mode. When a xxxdatareader object uses commandbehavior. closeconnection during production, the database connection will be automatically closed when the xxxdatareader object is closed.