How to disable database connection when using sqldatareader in Asp.net

This article explains how to use sqldatareader to close the database connection:

For example, you can encapsulate all database operations into a class, but sqldatareader can only close the database when reading is complete, so that the library connection cannot be closed in the class. If the function is disabled, the system prompts 'read attempts are invalid when the reader is disabled '.

Of course Microsoft thought of this. The following method is used: DR = cmd. executereader (commandbehavior. closeconnection);, commandbehavior in msdn. closeconnection is interpreted as 'when executing this command, if the associated datareader object is closed, the associated connection object will also be closed. '

But..., please refer to the following questions:

Execute the operation in the function, return the datareader, and read the data outside. After reading the data, close the datareader externally. Will the connetion in the function be automatically closed?

Instance display:

I encapsulate all the operations on the database into a class and writeProgramDirectly call a function of this class to return the result! (Learning Network-weareleran.net)

The following is a general class used to return the datareader object,

Public sqldatareader getreader (string SQL)

{

Sqlconnection conn;

Conn = new sqlconnection (strconn );

Conn. open ();

Sqlcommand cmd;

Cmd = createcmd (SQL, Conn );

Sqldatareader Dr;

Try

{

Dr = cmd. executereader (commandbehavior. closeconnection );

}

Catch

{

Throw new exception (SQL );

}

Cmd. Dispose ();

Return Dr;

}

 

When the program needs it, I directly construct the SQL statement and call this function as follows:

 
String SQL = "select * form student ";



Sqldatareader DR = mydate. getreader (SQL); // call the getreader function in the common database operation class and return sqldatareader



If (dr. Read ())

{

... Read the data and assign the value to the page control .....

}

Dr. Close (); // after the operation is completed, close datareader



 

I can read and retrieve the data normally. However, one of my concerns is that the connection object is closed, because it is a connection object created in the function, it cannot be closed externally or in the function, if the function is disabled, the system prompts 'read attempts are invalid when the reader is disabled'

But I used this line in the function: DR = cmd. executereader (commandbehavior. closeconnection);, commandbehavior in msdn. closeconnection is interpreted as 'when executing this command, if the associated datareader object is closed, the associated connection object will also be closed. '

That is to say, after I close datareader, the associated connection will be closed automatically. I have also tested and tested it.CodeAs follows:

String strconn = configurationsettings. etettings ["sqldatabase"];

Sqlconnection Cn = new sqlconnection (strconn );

CN. open ();

String SQL = "select * form student ";

Sqlcommand CM = new sqlcommand (SQL, CN );

Sqldatareader DR = cm. executereader (commandbehavior. closeconnection );

Sqldatareader DR = mydate. runprocgetreader (SQL );

If (dr. Read ())

{

... Read data

}

Dr. Close (); // close datareader

Response. Write (CN. State); // output the connection status

 

The output is closed. The connection is automatically closed! However, as I mentioned above, my coonection is built in the function. After performing the operation in the function, I return the datareader and read the data outside. After reading the data, I close the datareader outside, will the connection in this function be automatically closed?

 

 

I encapsulate all operations on the database into a class. When writing a program, I directly call a function of this class to return the result!

The following is a general class used to return the datareader object,

Public sqldatareader getreader (string SQL)

{

Sqlconnection conn;

Conn = new sqlconnection (strconn );

Conn. open ();

Sqlcommand cmd;

Cmd = createcmd (SQL, Conn );

Sqldatareader Dr;

Try

{

Dr = cmd. executereader (commandbehavior. closeconnection );

}

Catch

{

Throw new exception (SQL );

}

Cmd. Dispose ();

Return Dr;

}

When the program needs it, I directly construct the SQL statement and call this function as follows:

String SQL = "select * form student ";

Sqldatareader DR = mydate. getreader (SQL); // call the getreader function in the common database operation class and return sqldatareader

If (dr. Read ())
{
... Read the data and assign the value to the page control .....
}
Dr. Close (); // after the operation is completed, close datareader



I can read and retrieve the data normally. However, one of my concerns is that the connection object is closed, because it is a connection object created in the function, it cannot be closed externally or in the function, if the function is disabled, the system prompts 'read attempts are invalid when the reader is disabled'

But I used this line in the function: DR = cmd. executereader (commandbehavior. closeconnection);, commandbehavior in msdn. closeconnection is interpreted as 'when executing this command, if the associated datareader object is closed, the associated connection object will also be closed. '

That is to say, after I close datareader, the associated connection will be closed automatically. I have also tested the test code as follows:

String strconn = configurationsettings. etettings ["sqldatabase"];

Sqlconnection Cn = new sqlconnection (strconn );

CN. open ();

String SQL = "select * form student ";

Sqlcommand CM = new sqlcommand (SQL, CN );

Sqldatareader DR = cm. executereader (commandbehavior. closeconnection );

Sqldatareader DR = mydate. runprocgetreader (SQL );

If (dr. Read ())

{

... Read data

}

Dr. Close (); // close datareader

Response. Write (CN. State); // output the connection status

The output is closed. The connection is automatically closed! However, as I mentioned above, my coonection is built in the function. After performing the operation in the function, I return the datareader and read the data outside. After reading the data, I close the datareader outside, will the connection in this function be automatically closed?

I made a test myself: (we learn network -www.wearelearn.net)

Public sqlconnection conn;

Protected void page_load (Object sender, eventargs E)

{

Conn = new sqlconnection (DAL. sqlhelper. conn_string );

Show ();

}


// Simple Encapsulation Method
Protected static sqldatareader redatareader (sqlconnection conn)

{

Sqlcommand cmd = new sqlcommand ("select top 10 * from article", Conn );

Conn. open ();

Sqldatareader DR = cmd. executereader (commandbehavior. closeconnection );

Return Dr;

}
// Application Encapsulation Method
Protected sqldatareader RedR ()

{

Sqldatareader DR = redatareader (conn );

Return Dr;

}
// Call class, simulating a layer-3 architecture.
Protected void show ()

{

Sqldatareader DR = RedR ();

If (dr. hasrows)

{

While (dr. Read ())

{



}

Response. Write (conn. state. tostring ());

Dr. Dispose ();

Response. Write (conn. state. tostring ());

}

}

 
 
 

The result output is openclosed.

from: http://hi.baidu.com/westfruit/blog/item/6ed56f1911927b4f43a9ad87.html