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

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:

// 执行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;
}

// 执行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;
});

// 获取当前线程的数据库连接
private static SqlConnection GetThreadConnection()
{
    return connectionHolder.Value;
}

// 执行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;
}

// 执行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);
    // 处理查询结果
});

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

// 等待所有线程执行完毕
// ...

// 关闭数据库连接
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!