SQLHelper

using System;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Threading.Tasks;

public static class SqlHelper
{
    // 读取连接字符串
    private static readonly string connectionString = ConfigurationManager.ConnectionStrings["HBSJYTKYDBConnStr"].ConnectionString;

    /// <summary>
    /// 同步方法，执行查询并返回DataTable
    /// </summary>
    /// <param name="query">SQL查询字符串</param>
    /// <param name="parameters">SQL参数数组</param>
    /// <returns>查询结果的DataTable</returns>
    public static DataTable ExecuteQuery(string query, SqlParameter[] parameters = null)
    {
        using (SqlConnection connection = new SqlConnection(connectionString))
        {
            using (SqlCommand command = new SqlCommand(query, connection))
            {
                if (parameters != null)
                {
                    command.Parameters.AddRange(parameters);
                }

                using (SqlDataAdapter adapter = new SqlDataAdapter(command))
                {
                    DataTable dataTable = new DataTable();
                    adapter.Fill(dataTable);
                    return dataTable;
                }
            }
        }
    }

    /// <summary>
    /// 同步方法，执行非查询（插入、更新、删除）
    /// </summary>
    /// <param name="query">SQL查询字符串</param>
    /// <param name="parameters">SQL参数数组</param>
    /// <returns>受影响的行数</returns>
    public static int ExecuteNonQuery(string query, SqlParameter[] parameters = null)
    {
        using (SqlConnection connection = new SqlConnection(connectionString))
        {
            using (SqlCommand command = new SqlCommand(query, connection))
            {
                if (parameters != null)
                {
                    command.Parameters.AddRange(parameters);
                }

                connection.Open();
                int affectedRows = command.ExecuteNonQuery();
                return affectedRows;
            }
        }
    }

    /// <summary>
    /// 同步方法，执行标量查询
    /// </summary>
    /// <param name="query">SQL查询字符串</param>
    /// <param name="parameters">SQL参数数组</param>
    /// <returns>查询结果的第一行第一列的值</returns>
    public static object ExecuteScalar(string query, SqlParameter[] parameters = null)
    {
        using (SqlConnection connection = new SqlConnection(connectionString))
        {
            using (SqlCommand command = new SqlCommand(query, connection))
            {
                if (parameters != null)
                {
                    command.Parameters.AddRange(parameters);
                }

                connection.Open();
                object result = command.ExecuteScalar();
                return result;
            }
        }
    }

    /// <summary>
    /// 异步方法，执行查询并返回DataTable
    /// </summary>
    /// <param name="query">SQL查询字符串</param>
    /// <param name="parameters">SQL参数数组</param>
    /// <returns>查询结果的DataTable</returns>
    public static async Task<DataTable> ExecuteQueryAsync(string query, SqlParameter[] parameters = null)
    {
        using (SqlConnection connection = new SqlConnection(connectionString))
        {
            using (SqlCommand command = new SqlCommand(query, connection))
            {
                if (parameters != null)
                {
                    command.Parameters.AddRange(parameters);
                }

                using (SqlDataAdapter adapter = new SqlDataAdapter(command))
                {
                    DataTable dataTable = new DataTable();
                    await Task.Run(() => adapter.Fill(dataTable));
                    return dataTable;
                }
            }
        }
    }

    /// <summary>
    /// 异步方法，执行非查询（插入、更新、删除）
    /// </summary>
    /// <param name="query">SQL查询字符串</param>
    /// <param name="parameters">SQL参数数组</param>
    /// <returns>受影响的行数</returns>
    public static async Task<int> ExecuteNonQueryAsync(string query, SqlParameter[] parameters = null)
    {
        using (SqlConnection connection = new SqlConnection(connectionString))
        {
            using (SqlCommand command = new SqlCommand(query, connection))
            {
                if (parameters != null)
                {
                    command.Parameters.AddRange(parameters);
                }

                await connection.OpenAsync();
                int affectedRows = await command.ExecuteNonQueryAsync();
                return affectedRows;
            }
        }
    }

    /// <summary>
    /// 异步方法，执行标量查询
    /// </summary>
    /// <param name="query">SQL查询字符串</param>
    /// <param name="parameters">SQL参数数组</param>
    /// <returns>查询结果的第一行第一列的值</returns>
    public static async Task<object> ExecuteScalarAsync(string query, SqlParameter[] parameters = null)
    {
        using (SqlConnection connection = new SqlConnection(connectionString))
        {
            using (SqlCommand command = new SqlCommand(query, connection))
            {
                if (parameters != null)
                {
                    command.Parameters.AddRange(parameters);
                }

                await connection.OpenAsync();
                object result = await command.ExecuteScalarAsync();
                return result;
            }
        }
    }

    /// <summary>
    /// 异步方法，在事务中执行查询
    /// </summary>
    /// <param name="operations">执行事务的操作</param>
    /// <returns>Task</returns>
    public static async Task ExecuteTransactionAsync(Func<SqlTransaction, Task> operations)
    {
        using (SqlConnection connection = new SqlConnection(connectionString))
        {
            await connection.OpenAsync();
            using (SqlTransaction transaction = connection.BeginTransaction())
            {
                try
                {
                    await operations(transaction);
                    transaction.Commit();
                }
                catch
                {
                    transaction.Rollback();
                    throw;
                }
            }
        }
    }
}