How to correctly use SqlConnection

Source: Internet
Author: User

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

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.