We must use the database when we write the program. We must know that the connection is a valuable resource, we can get the content of the database and the data we need through this connection. This is still like this in .NET. The first step we have to do when using the database resources is to create a connection, then we can create a Command or DataAdapter object with this connection.
I encountered the page prompt "Connecting pool" and the "connection pool". Seeing it, I must have an open connection that is not closed. In frequent database interactions, GC is not perfect for us. This problem will generally disappear after a while, but will perform the same operation and will appear. I don't know if you encounter such a problem, we can use some simple ways to find these problems, and pay attention to some computing habits can reduce this happening.
I. Problem caused by DataReader
DataReader is an object with high efficiency but very resource (number of connections), which is built from it, and he has been connected with the database, that is, it will take up a connection. In order to use the object (Reader.close ()), you can close the connection release resource, we can use the Commandbehavior.CloseConnection option (one parameter in the constructor) when establishing DataReader. When we use this option, we also close the connection of the object at the same time when we shut down the DataReader.
We know that simple code looks comfortable, but also reflects our programming level, but some simple code will introduce some questions we may not pay attention to, such as: When we use SQLHELPER (Microsoft DataAccess Block), I usually Using it as a direct intersection of the database (I am built on this), I use it to get very rich objects including: DataReader, Dataset, and more. DataAdapter is an object that does not have to control the connection to open or close (because it is filled with a DataSet object). It is easy to ignore the DataReader when DataReader is transferred between the other layers, it is troublesome. For example, the following code is relatively simple:
MyDataGrid.datasource = myObject.getdataraneader (); mydatagrid.databind ();
Although it looks cool, it is very simple, but use the following method safer:
IdataReader Dr = myObject.getdatareader (); mydatagrid.datasource = DR; mydatagrid.database (); Dr.close ();
Second, the query failed
There is also a need to pay attention to what we are in executing query statements, usually we will open the connection first in the query, so it will be prone to problems, what should I do if I have something wrong?
When we perform query statements, there will be such or such a reason such that the query is unsuccessful, I usually practice is to use Catch to capture these errors. For example, we use a method for writing a control binding to getDataSource.
Private DataTable getDataSource () {
String querystring = "select * from table1";
SqlConnection Conn = New SqlConnection (CONECTIONSTRING);
SQLCommand CMD ....
Return cmd.executedata (). TABLES [0];
}
The above method is very dangerous (only some explanation of some explanation), if we perform an error after opening the connection, then this open connection will not be released (if there is a custom error page Then jump to the page). We should usually put the part of the execution query in Try ... catch. This happens when there is a mistake, we still have the opportunity to close these connections, it is best to use try .... catch .... Finally. I have handled all the open connection feels in Finally or safe.
Ok, I said so much, not nonsense, summarize: I should pay special attention when using DataReader, because DataSet is the memory buffer so after the data in his disaster area will automatically disconnect. Pay special attention when executing the query statement, which is a good practice after using the object destruction (Dispose).
