Why do I need to close the database connection?

The first thing to note is that the number of connections is limited:

CodeAs follows:

 
For(IntI = 0; I <10000; I ++) {sqlconnection conn =NewSqlconnection (@ "Data Source =. \ sqlexpress; attachdbfilename =""E: \ dB \ northwnd. MDF""; Integrated Security = true; Connect timeout = 30; user instance = true"); Conn. open (); console. writeline ("{0} connections opened", I );}
 

The running result is as follows:

After a while, the system will prompt that the connection has timed out:

 

We can see that there are limits on database connections. If the connection is not closed and many users are used, the system will soon be down.

 

But sometimes for some reasonApplicationProgramIt may be used by only a few people.So someone designed it:

Open the database connection when the application starts, and close the database connection when the application is closed.

So what are the problems with using this method?

Assume that there is a tableNumsThe table is defined as follows:

The main code is as follows:

 
Sqlconnection conn =NewSqlconnection (@ "Data Source =. \ sqlexpress; attachdbfilename =""E: \ dB \ northwnd. MDF""; Integrated Security = true; Connect timeout = 30; user instance = true"); Conn. open (); parallel. For (1, 9999, (ID) =>{ executecommand (Conn, ID );});
 

Execute executecommand from 1 to 9999.

 

The executecommand code is as follows:

  private   static   void  executecommand (sqlconnection Conn,  int  ID) {console. writeline ( "executing. " + id); thread. sleep (100); sqlcommand cmd =  New  sqlcommand ( string . format ( "insert into Nums values ('{0}')" , ID), Conn); cmd. executenonquery () ;}

Run:

We can see that the executenonquery method throws an exception becauseThe connection is closed.

 

However, our connections are always open, and we didn't call methods like close and dispose..

Therefore, the judgment conditions are added before executecommand:

If(Conn. State! = System. Data. connectionstate. Open) Conn. open ();
 

Run again:

We can see that the connection is closed.Do you know why?

 

This is caused by a multi-threaded environment. So the lock is required.

Private Static ObjectSyncobj =New Object();
 
 
 
 
Private Static VoidExecutecommand (sqlconnection Conn,IntID)
 
{
 
 
Lock(Syncobj)
 
 
{
 
 
If(Conn. State! = System. Data. connectionstate. open)
 
 
Conn. open ();
 
 
 
Console. writeline ("Executing .."+ Id );
 
 
 
 
Thread. Sleep (100 );
 
 
 
Sqlcommand cmd =NewSqlcommand (
 
 
String. Format ("Insert into Nums values ('{0 }')", ID), Conn );
 
 
 
 
Cmd. executenonquery ();
 
}
 
 
}
 
 
 
 
Run again: you can find that there is no problem.
 
 
Modify the maximum value of parallel. For to test whether execution can be performed for a long time.
 
 
Parallel. For (1, int32.maxvalue, (ID) =>
 
 
{
 
Executecommand (Conn, ID );
 
 
});
 
 
 
 
 
 
 
 
After one day of testing, there were no problems.
 
 
 
 
 
Conclusion: For some applications that only a few people use, you can not close the database connection, but it is best to add the connection to determine whether to open when writing code.
 
 
 
 
 
What's your opinion? Stay here!