How to use multi-threads to access the database concurrently in C# development

In C# development, multi-threads concurrent access to the database is a common requirement. Using multi-threading can improve the efficiency of database operations, but you also need to pay attention to issues such as thread safety and database connection management. This article will introduce how to use multi-threading to access the database concurrently in C# and provide specific code examples.

1. Create a database connection

Before using multi-threads to concurrently access the database, you first need to create a database connection. Normally, we use the SqlConnection class provided by ADO.NET to create a database connection. The specific code is as follows:

using System.Data.SqlClient;

string connectionString = "Data Source=your_server;Initial Catalog=your_database;User ID=your_username;Password=your_password";
SqlConnection connection = new SqlConnection(connectionString);
connection.Open();

2. Create database operation method

When multiple threads access the database concurrently, we usually encapsulate the database operation into a method for threads to call. This method is responsible for opening a database connection, performing database operations and returning results. The specific code is as follows:

// 執(zhí)行SQL查詢
public static DataTable ExecuteQuery(string sql)
{
    SqlCommand command = new SqlCommand(sql, connection);
    SqlDataAdapter adapter = new SqlDataAdapter(command);
    DataTable dataTable = new DataTable();
    adapter.Fill(dataTable);
    
    return dataTable;
}

// 執(zhí)行SQL非查詢操作
public static int ExecuteNonQuery(string sql)
{
    SqlCommand command = new SqlCommand(sql, connection);
    return command.ExecuteNonQuery();
}

3. Create multi-threaded concurrent access to the database

Before using multi-threaded concurrent access to the database, we need to create a thread-safe database connection. This can be achieved using thread local storage (ThreadLocal). The specific code is as follows:

using System.Threading;

private static ThreadLocal<SqlConnection> connectionHolder = new ThreadLocal<SqlConnection>(() =>
{
    SqlConnection threadConnection = new SqlConnection(connectionString);
    threadConnection.Open();
    return threadConnection;
});

// 獲取當(dāng)前線程的數(shù)據(jù)庫連接
private static SqlConnection GetThreadConnection()
{
    return connectionHolder.Value;
}

// 執(zhí)行SQL查詢
public static DataTable ExecuteQueryThreadSafe(string sql)
{
    SqlCommand command = new SqlCommand(sql, GetThreadConnection());
    SqlDataAdapter adapter = new SqlDataAdapter(command);
    DataTable dataTable = new DataTable();
    adapter.Fill(dataTable);
    
    return dataTable;
}

// 執(zhí)行SQL非查詢操作
public static int ExecuteNonQueryThreadSafe(string sql)
{
    SqlCommand command = new SqlCommand(sql, GetThreadConnection());
    return command.ExecuteNonQuery();
}

4. Using multi-threads to access the database concurrently

When using multi-threads to access the database concurrently, you can create multiple threads to perform database operations simultaneously. The specific code is as follows:

ThreadPool.QueueUserWorkItem((state) =>
{
    string querySql = "SELECT * FROM your_table";
    DataTable result = ExecuteQueryThreadSafe(querySql);
    // 處理查詢結(jié)果
});

ThreadPool.QueueUserWorkItem((state) =>
{
    string updateSql = "UPDATE your_table SET your_column = value";
    int affectedRows = ExecuteNonQueryThreadSafe(updateSql);
    // 處理更新結(jié)果
});

// 等待所有線程執(zhí)行完畢
// ...

// 關(guān)閉數(shù)據(jù)庫連接
connection.Close();

The above is the specific example code for using multi-threads to access the database concurrently. Using multi-threading can improve the efficiency of database operations, but you also need to pay attention to issues such as thread safety and database connection management to avoid concurrent access conflicts and resource leaks. I hope this article will help you use multi-threaded concurrent access to the database in C# development.

The above is the detailed content of How to use multi-threads to concurrently access the database in C# development. For more information, please follow other related articles on the PHP Chinese website!