C # ADO Database connection pooling issues

1, the database connection will remain open until SqlDataReader is closed, so when using SqlDataReader, you should call Sqldatareader.close () to close it immediately.

2, a connection can only be used by a SqlDataReader, which is why to close the SqlDataReader as early as possible reasons.
3. After using SqlDataReader, you can close the connection by calling the Close method of the database connection object that is displayed in the program. You can also pass CommandBehavior.CloseConnection This enumeration variable when invoking the ExecuteReader method of the Command object so that the database connection is automatically closed when the Close method of the SqlDataReader is called.
4, use SqlDataReader when possible to use and database field type matching method to obtain the corresponding value, such as for the shape of the field using GetInt32, the character type of the field use GetString. This reduces the additional type conversion operations that are added because of inconsistent types.
5, when using SqlDataReader to get multiple records, if you do not have access to the end of the fetch record to close SqlDataReader, you should first call the Command object's Cancel method, and then call the SqlDataReader Close method. The Cancel method of the Command object causes the database to no longer send the data that is not accessed in SqlDataReader to the caller, and if you do not call this method to close SqlDataReader directly, The database sends and SqlDataReader non-accessed data such as long, empty data streams to the caller.
6. If you want to get the return value or output parameter of a stored procedure by SqlCommand's ExecuteReader method, you must first call the Close method of SqlDataReader to get the value of the output parameter or the return value.
7. If you use SqlDataReader to return only one record, when you call the command's ExecuteReader method, specify
Commandbehavior.singlerow parameter, the use of this parameter has no effect on SQL Server. NET data provider, but when you use OLE DB. NET data provider, when you specify this parameter, Instead of using a relatively resource-intensive IRowset interface, the Dataprivider internally uses the IRow interface.

This part comes from other online sources, but the test shows that it is not the case.

I do this test, the database operation class to do a long connection, that is, the entire client program with only one database connection. Client

 /// <summary>    ///Database Operations Classes/// </summary>    classDBHelper {//whether the long connection is open        BOOLConnet =false;  Public  stringConnStr ="Data SOURCE=ORCL;UID=ADMIN;PWD=JXDFLP"; //Database Link Object        PrivateOracleConnection Conn =NULL;  PublicDBHelper () {initconnection (); Thread TR=NewThread (TESTCONNCET); Tr. IsBackground=true; Tr.        Start (); }              //Initializing database links        Private  voidinitconnection () {Try            {                //If the connection object does not exist, create a connection                if(Conn = =NULL) Conn=NewOracleConnection (CONNSTR); //If the connection object is closed, open the connection                if(Conn.state = =connectionstate.closed) Conn.Open (); //If the connection is interrupted, restart the connection                if(Conn.state = =connectionstate.broken) {conn.close ();                Conn.Open (); } connet=true; }            Catch(Exception ex) {Classvar.writeerrorlog (ex).            ToString ()); }        }               //Testing Long Connection Threads        Private voidTestconncet () { while(true)            {                Try                {                    stringsql ="Select 1 from dual"; OracleDataReader Read=getdatareader (SQL); if(read. Read ()) {connet=true; }                    Else{connet=false;                    Initconnection (); }                }                Catch(Exception ex) {Classvar.writeerrorlog (ex).                ToString ()); } thread.sleep ( -); }        }         //query, get DataReader         PublicOracleDataReader Getdatareader (stringsqlstr) {OracleDataReader Read=NULL; Try{oraclecommand cmd=NewOracleCommand (Sqlstr, Conn); Read=cmd.            ExecuteReader (); }            Catch(Exception ex) {Classvar.writeerrorlog (ex).            ToString ()); }            returnRead; }                }

1   Public Partial classForm1:form2     {3 4 DBHelper db;5          PublicForm1 ()6         {7 InitializeComponent ();8 9db =NewDBHelper ();TenThread TR =NewThread (test); OneTr. IsBackground =true; A tr. Start (); -  -Thread TR1 =NewThread (test2); theTR1. IsBackground =true; - TR1. Start (); -  -Thread TR2 =NewThread (test3); +TR2. IsBackground =true; - TR2. Start (); +         } A  at         -         Private voidTest () -         { -              while(true) -             { -                 stringsql ="SELECT * from Jzfwxx t"; inOracleDataReader read=db. Getdatareader (SQL); -Thread.Sleep (5); to             } +         } -  the         Private voidtest2 () *         { $              while(true)Panax Notoginseng             { -                 stringsql ="SELECT * from Jzrxx t"; theOracleDataReader Read =db. Getdatareader (SQL); +Thread.Sleep (5); A             } the         } +  -         Private voidtest3 () $         { $              while(true) -             { -                 stringsql ="SELECT * from Mjsbxx t"; theOracleDataReader Read =db. Getdatareader (SQL); -Thread.Sleep (5);Wuyi             } the         } -}

C # ADO Database connection pooling issues