Explain why you want to close the database connection and can you leave it open

2020-04-01 23:32:44
OfStack

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

The code is as follows:


for (int i = 0; i < 10000; i++)
{
    SqlConnection conn = new SqlConnection(@"Data Source=.SQLEXPRESS;
                AttachDbFilename=""E:DBNORTHWND.mdf"";
                Integrated Security=True;Connect Timeout=30;User Instance=True");
    conn.Open();
    Console.WriteLine(" Open the {0} A connection ", i);
}

The operation results are as follows:

< img style = "border = 0 border - RIGHT - WIDTH: 0 px; DISPLAY: inline. BORDER - TOP - WIDTH: 0 px; BORDER - BOTTOM - WIDTH: 0 px; BORDER - LEFT - WIDTH: 0 px "title = image BORDER = 0 Alt = image SRC =" / / files.jb51.net/file_images/article/201305/2013051311141127.png "WIDTH = 149 height = 362 >

After a while, you will be prompted to open the connection timeout:

< img style = "border = 0 border - RIGHT - WIDTH: 0 px; DISPLAY: inline. BORDER - TOP - WIDTH: 0 px; BORDER - BOTTOM - WIDTH: 0 px; BORDER - LEFT - WIDTH: 0 px "title = clip_image002 BORDER = 0 Alt = clip_image002 SRC =" / / files.jb51.net/file_images/article/201305/2013051311141128.jpg "WIDTH = 558 height = 107 >

You can see that the database connection is limited, and if the connection is not closed and more people are using it, the system will be down quickly.

But sometimes for some reason The application may only be used by a few people So someone designed it:

Open the database connection when the application starts, and close the database connection when the application closes

So what's wrong with this approach?

So let's say I have a table Nums , the table is defined as follows:

The Main code is as follows:


SqlConnection conn = new SqlConnection(@"Data Source=.SQLEXPRESS;
                    AttachDbFilename=""E:DBNORTHWND.mdf"";
                    Integrated Security=True;Connect Timeout=30;User Instance=True");
conn.Open();
Parallel.For(1, 9999, (id) =>
{
    ExecuteCommand(conn, id);
});

That is, from 1 to 9999, the executive command

The ExecuteCommand code is as follows:


private static void ExecuteCommand(SqlConnection conn, int id)
{
    Console.WriteLine(" Being performed ." + id);
    Thread.Sleep(100);
    SqlCommand cmd = new SqlCommand(
       string.Format("Insert into Nums values('{0}') ", id), conn);
    cmd.ExecuteNonQuery();
}

Run:

< img style = "border = 0 border - RIGHT - WIDTH: 0 px; DISPLAY: inline. BORDER - TOP - WIDTH: 0 px; BORDER - BOTTOM - WIDTH: 0 px; BORDER - LEFT - WIDTH: 0 px "title = clip_image002 [5] BORDER = 0 Alt = clip_image002 [5] SRC =" / / files.jb51.net/file_images/article/201305/2013051311141130.jpg "WIDTH = 558 height = 96 >

You can see that the ExecuteNonQuery method throws an exception for this reason The connection is closed.

However, our connection was always open and we didn't call close, dispose or anything like that .

Then add the judgment criteria in front of executive command:

If (conn. State! = System. Data. ConnectionState. Open)
Conn. The Open (); Run again:

< img style = "border = 0 border - RIGHT - WIDTH: 0 px; DISPLAY: inline. BORDER - TOP - WIDTH: 0 px; BORDER - BOTTOM - WIDTH: 0 px; BORDER - LEFT - WIDTH: 0 px "title = clip_image002 [7] BORDER = 0 Alt = clip_image002 [7] SRC =" / / files.jb51.net/file_images/article/201305/2013051311141131.jpg "WIDTH = 557 height = 245 >

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

This is due to the multithreaded environment. So it needs to be locked.


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(" Being performed .." + id);
        Thread.Sleep(100);
        SqlCommand cmd = new SqlCommand(
           string.Format("Insert into Nums values('{0}') ", id), conn);
        cmd.ExecuteNonQuery();
    }
}

Run again: you can see that it is almost fine.

Modify the maximum limit of Parallel.For to test whether it can be executed For a long time.


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

After a day of testing, there were no problems.

Conclusion: for some applications that only a few people use, you can leave the database connection open, but it's best to include an open connection when writing code.