Explain why you want to close the database connection, can not shut down the problem of _c language

The first thing to show is that there are limits to the number of connections:

The code is as follows:

Copy Code code as follows:

for (int i = 0; i < 10000; i++)
{
SqlConnection conn = new SqlConnection (@ "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 results of the operation are as follows:

You'll be prompted to open the connection timeout in a few seconds:

You can see that the database connection is limited, if the connection is not closed, and more people use, then the system quickly down.

But sometimes the application may be just a few people for some reason, so someone has designed:

Open the database connection when the application is started and close the database connection when the application shuts down

So what's the problem with using this approach?

First, suppose there is a table nums, and the table is defined as follows:

The main code is as follows:

Copy Code code as follows:

SqlConnection conn = new SqlConnection (@ "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);
});

is to execute ExecuteCommand from 1 to 9999.

The ExecuteCommand code is as follows:

Copy Code code 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:

You can see that the ExecuteNonQuery method throws an exception because the connection is off.

But our connection has always been open, ah, and did not call the Close,dispose and other methods Ah .

So in front of the executecommand to increase the judgment condition:

IF (Conn. State!= System.Data.ConnectionState.Open)
Conn. Open (); Run again:

You can see whether the connection has been closed. Do you know why?

This is caused by a multithreaded environment. So you need to lock it.

Copy Code code as follows:

private static Object syncobj = new Object ();
private static void ExecuteCommand (SqlConnection conn, int id)
{
Lock (Syncobj)
{
IF (Conn. State!= System.Data.ConnectionState.Open)
Conn. Open ();
Console.WriteLine ("Executing ..." + ID);
Thread.Sleep (100);
SqlCommand cmd = new SqlCommand (
String. Format ("Insert into nums values (' {0} ')", ID), conn);
Cmd. ExecuteNonQuery ();
}
}

Run again: You can find the basic no problem.

Modify the maximum value of the parallel.for to test whether it can be executed for a long time.

Copy Code code as follows:

Parallel.For (1, Int32.MaxValue, (ID) =>
{
ExecuteCommand (conn, id);
});

One day, there was nothing wrong with the test.

Conclusion: For some applications that are used by only a few people, you can not close the database connection, but it is best to add the judgment of whether the connection is open when writing code.