SqlConnection object and Database Connection Pool

The first code is concise, but many people, including myself, may have a few important questions:

1. How do I share only one connection object for multiple database tutorials in parallel?

2. A Lock is applied to the parallel processing, and each database operation must be locked. (I don't think this gives full play to the advantages of multi-thread parallel processing, I personally think it is not as fast as a single thread.) Is this really necessary?

3. How can I transmit parameters to the same database connection string using the database connection object SqlConnection? Isn't explicit Open and Close better? Generally, isn't it all about using?
Next let's look at the code first
String sqlConnString = @ "Data Source =. SQLEXPRESS;
AttachDbFilename = "" E: DBNORTHWND. mdf "";
Integrated Security = True; Connect Timeout = 30; User Instance = True ";
SqlConnection conn = new SqlConnection (sqlConnString );
Conn. Open ();
Parallel. For (1, Int32.MaxValue, (id) =>
{
ExecuteCommand (conn, id );
});
The specific implementation of ExecuteCommand is as follows:

Private static object syncObj = new object ();
Private static void ExecuteCommand (SqlConnection conn, int id)
{
Lock (syncObj)
{
If (conn. State! = 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 ();
}
}

 

After a simple thought, I improved the implementation code on the local machine for testing, as shown below:

Parallel. For (1, Int32.MaxValue, (id) =>
{
ExecuteCommand (id );
});
The ExecuteCommand method no longer accepts the SqlConnection object as the parameter. Removing Lock makes database operations look like a database operation and opening a database connection to ensure thread safety:

Private static void ExecuteCommand (int id)
{
Using (SqlConnection conn = new SqlConnection (sqlConnString ))
{
Conn. Open ();
Console. WriteLine ("executing." + id );
Thread. Sleep (100 );
SqlCommand cmd = new SqlCommand (
String. Format ("Insert into Nums values ('{0}')", id), conn );
Cmd. ExecuteNonQuery ();
}
}
Of course, ExecuteCommand can also be processed through explicit Open and Close:

Private static void ExecuteCommand (int id)
{
SqlConnection conn = new SqlConnection (sqlConnString );
Conn. Open ();
Console. WriteLine ("executing." + id );
Thread. Sleep (100 );
SqlCommand cmd = new SqlCommand (
String. Format ("Insert into Nums values ('{0}')", id), conn );
Cmd. ExecuteNonQuery ();
Conn. Close ();
}

According to my local machine test, the two improved statements above indicate that the database connection object is normal and thread-safe, and the performance is significantly higher than the locking operation database, if you are interested, try again.