How to correctly use SqlConnection

I have seen someone write code like this before:
Copy codeThe Code is as follows: public class Service1: IService1
{
Private SqlConnection conn = new SqlConnection ();
Public void Method1 ()
{
// Do something with conn;
}
Public void Method2 ()
{
// Do something with conn;
}
Public void Method3 ()
{
// Do something with conn;
}
Public void Method4 ()
{
// Do something with conn;
}
}

In the service class, create a global conn object and use the conn object to operate the database.
Of course, there are some different versions, such:Copy codeThe Code is as follows: private SqlConnection conn = new SqlConnection ();
Private static SqlConnection sconn = new SqlConnection ();
Private SqlConnection Conn
{
Get {return new SqlConnection ();}
}

If someone asks you which method is better, what would you do?

First, verify that a Connection is used in a multi-threaded environment:
Create a console program:
The Main code is as follows:Copy codeThe Code is as follows: public static void Main ()
{
String connectionString = @ "Data Source =. \ SQLEXPRESS;
AttachDbFilename = "" E: \ DB \ NORTHWND. mdf "";
Integrated Security = True;
Connect Timeout = 30; User Instance = True ";
String connectionStringNoPooling = connectionString + "; Pooling = 'false '";
SqlConnection conn = new SqlConnection (connectionString );
New Thread () =>{ ExecuteCommand (conn) ;}) {Name = "t1"}. Start ();
New Thread () =>{ ExecuteCommand (conn) ;}) {Name = "t2"}. Start ();
}
Public static void ExecuteCommand (SqlConnection conn)
{
Console. WriteLine ("Thread: {0}, {1}", Thread. CurrentThread. Name, DateTime. Now );

Conn. Open ();

SqlCommand command = new SqlCommand ("select * from MERs", conn );
Command. ExecuteNonQuery ();
Command. Dispose ();
Thread. Sleep (5000); // simulate time-consuming queries
Conn. Close ();
Console. WriteLine ("Thread: {0} finished, {1}", Thread. CurrentThread. Name, DateTime. Now );
}

The code is simple,Simulate two threads to execute ExecuteCommand at the same time. Method. The result is as follows:

It can be seen that it is unsafe to use a Connection to execute SQL statements in a multi-threaded environment,

Modify the Main function as follows:Change one Connection to multiple connections.

Copy codeThe Code is as follows: public static void Main ()
{
String connectionString = @ "Data Source =. \ SQLEXPRESS;
AttachDbFilename = "" E: \ DB \ NORTHWND. mdf "";
Integrated Security = True;
Connect Timeout = 30; User Instance = True ";
String connectionStringNoPooling = connectionString + "; Pooling = 'false '";
// SqlConnection conn = new SqlConnection (connectionString );
// New Thread () =>{ ExecuteCommand (conn) ;}) {Name = "t1"}. Start ();
// New Thread () =>{ ExecuteCommand (conn) ;}) {Name = "t2"}. Start ();
SqlConnection conn1 = new SqlConnection (connectionString );
SqlConnection conn2 = new SqlConnection (connectionString );
New Thread () =>{ ExecuteCommand (conn1) ;}) {Name = "t1"}. Start ();
New Thread () =>{ ExecuteCommand (conn2) ;}) {Name = "t2"}. Start ();
Console. ReadLine ();
}

The running result is as follows:

Since multiple connections are better than one Connection,

Why is the above method used to create a Connection?

I think they may think it is time-consuming to create multiple connections, and multiple connections occupy the memory and affect the performance ..

Test data can be used to describe this point:

Test data comes from:Connection-Pooling vs. Reusing one connection

Run #	NCP	CP	OC
1	4073	374	237
2	4032	341	298
3	3985	353	242
4	4085	348	269
5	3964	369	256
6	4203	330	207
7	4055	341	359
8	4071	357	286
9	3968	363	356
10	4023	349	359
AVG	4046	353	287

Run #: 1 indicates 1000 queries, and 2 indicates 2000 queries.

NCP: Not Connection Pool, database Connection Pool Not enabled

CP: Connection Pool, enabling the database Connection Pool

OC: One Connection, a Connection object

From the chart, we can find that the connection pool is enabled, which is not as slow as a single connection.

But in terms of stability and program robustness, the CP method is obviously better than OC.

Therefore, you can use this function for the next service implementation or query.

Copy codeThe Code is as follows: public SqlConnection Connection
{
Get
{
Return new SqlConnection (@"...");
}
}

Instead
Private SqlConnection conn = new SqlConnection (connectionString );