数据库增删改查类文件DBHelper

以下是一个基础的DBHelper类文件，包含对数据库的访问、增加、删除、修改、查询等的功能。建议根据具体业务需求再进行适当的修改和优化。

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;

namespace YourNameSpace
{
    public static class DBHelper
    {
        // 数据库连接字符串
        private static string connectionString = "Data Source=localhost;Initial Catalog=YourDatabaseName;Integrated Security=True";

        // 执行查询操作（返回DataTable）
        public static DataTable ExecuteQuery(string sql)
        {
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                connection.Open();
                SqlCommand command = new SqlCommand(sql, connection);
                SqlDataAdapter adapter = new SqlDataAdapter(command);
                DataTable dataTable = new DataTable();
                adapter.Fill(dataTable);
                return dataTable;
            }
        }

        // 执行查询操作（返回指定类型的集合）
        public static List<T> ExecuteQuery<T>(string sql, Func<SqlDataReader, T> func)
        {
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                connection.Open();
                SqlCommand command = new SqlCommand(sql, connection);
                List<T> list = new List<T>();
                SqlDataReader reader = command.ExecuteReader();
                while (reader.Read())
                {
                    T item = func(reader);
                    list.Add(item);
                }
                reader.Close();
                return list;
            }
        }

        // 执行增删改操作
        public static int ExecuteNonQuery(string sql, params SqlParameter[] parameters)
        {
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                connection.Open();
                SqlCommand command = new SqlCommand(sql, connection);
                command.Parameters.AddRange(parameters);
                int count = command.ExecuteNonQuery();
                return count;
            }
        }

        // 执行存储过程（返回DataSet）
        public static DataSet ExecuteStoredProcedure(string name, params SqlParameter[] parameters)
        {
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                connection.Open();
                SqlCommand command = new SqlCommand(name, connection);
                command.CommandType = CommandType.StoredProcedure;
                command.Parameters.AddRange(parameters);
                SqlDataAdapter adapter = new SqlDataAdapter(command);
                DataSet dataSet = new DataSet();
                adapter.Fill(dataSet);
                return dataSet;
            }
        }

        // 执行存储过程（返回指定类型的集合）
        public static List<T> ExecuteStoredProcedure<T>(string name, Func<SqlDataReader, T> func, params SqlParameter[] parameters)
        {
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                connection.Open();
                SqlCommand command = new SqlCommand(name, connection);
                command.CommandType = CommandType.StoredProcedure;
                command.Parameters.AddRange(parameters);
                List<T> list = new List<T>();
                SqlDataReader reader = command.ExecuteReader();
                while (reader.Read())
                {
                    T item = func(reader);
                    list.Add(item);
                }
                reader.Close();
                return list;
            }
        }
    }
}

使用方法示例：

查询：

DataTable dataTable = DBHelper.ExecuteQuery("SELECT * FROM YourTableName");
List<YourModelClass> list = DBHelper.ExecuteQuery("SELECT * FROM YourTableName", reader =>
{
    YourModelClass model = new YourModelClass();
    model.Property1 = reader.GetString(reader.GetOrdinal("ColumnName1"));
    model.Property2 = reader.GetInt32(reader.GetOrdinal("ColumnName2"));
    //...
    return model;
});

增、删、改：

SqlParameter param1 = new SqlParameter("@Param1", value1);
SqlParameter param2 = new SqlParameter("@Param2", value2);
//...
int count = DBHelper.ExecuteNonQuery("INSERT INTO YourTableName (ColumnName1, ColumnName2, ...) VALUES (@Param1, @Param2, ...)", param1, param2, ...);

存储过程：

SqlParameter param1 = new SqlParameter("@Param1", value1);
SqlParameter param2 = new SqlParameter("@Param2", value2);
//...
DataSet dataSet = DBHelper.ExecuteStoredProcedure("YourSPName", param1, param2, ...);
List<YourModelClass> list = DBHelper.ExecuteStoredProcedure("YourSPName", reader =>
{
    YourModelClass model = new YourModelClass();
    model.Property1 = reader.GetString(reader.GetOrdinal("ColumnName1"));
    model.Property2 = reader.GetInt32(reader.GetOrdinal("ColumnName2"));
    //...
    return model;
}, param1, param2, ...);