Why do I need to close the database connection?

Source: Internet
Author: User

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 () ;}


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)
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!

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.