C# 操作数据库（以 SQL Server 为例）

实现基本的增删改查以及事务功能的类示例代码，留存备用

class Program
{
    static void Main()
    {
        // 替换为你实际的数据库连接字符串
        string connectionString = "Data Source=YOUR_SERVER_NAME;Initial Catalog=YOUR_DATABASE_NAME;User ID=YOUR_USERNAME;Password=YOUR_PASSWORD";
        DatabaseHelper databaseHelper = new DatabaseHelper(connectionString);

        // 插入操作示例
        string insertSql = "INSERT INTO YourTable (Column1, Column2) VALUES (@Value1, @Value2)";
        SqlParameter[] insertParameters =
        {
            new SqlParameter("@Value1", "SomeValue1"),
            new SqlParameter("@Value2", "SomeValue2")
        };
        int insertResult = databaseHelper.ExecuteNonQuery(insertSql, insertParameters);
        Console.WriteLine($"插入操作影响行数: {insertResult}");

        // 查询操作示例
        string selectSql = "SELECT * FROM YourTable";
        DataTable resultTable = databaseHelper.ExecuteQuery(selectSql);
        foreach (DataRow row in resultTable.Rows)
        {
            Console.WriteLine($"Column1: {row["Column1"]}, Column2: {row["Column2"]}");
        }

        // 更新操作示例
        string updateSql = "UPDATE YourTable SET Column1 = @NewValue1 WHERE Column2 = @ValueToMatch";
        SqlParameter[] updateParameters =
        {
            new SqlParameter("@NewValue1", "UpdatedValue1"),
            new SqlParameter("@ValueToMatch", "SomeValue2")
        };
        int updateResult = databaseHelper.ExecuteNonQuery(updateSql, updateParameters);
        Console.WriteLine($"更新操作影响行数: {updateResult}");

        // 删除操作示例
        string deleteSql = "DELETE FROM YourTable WHERE Column1 = @ValueToDelete";
        SqlParameter[] deleteParameters =
        {
            new SqlParameter("@ValueToDelete", "UpdatedValue1")
        };
        int deleteResult = databaseHelper.ExecuteNonQuery(deleteSql, deleteParameters);
        Console.WriteLine($"删除操作影响行数: {deleteResult}");

        // 事务操作示例（假设同时插入两条记录作为事务操作示例）
        var operation1 = ("INSERT INTO YourTable (Column1, Column2) VALUES (@Value3, @Value4)",
            new SqlParameter[]
            {
                new SqlParameter("@Value3", "TransactionValue1"),
                new SqlParameter("@Value4", "TransactionValue2")
            });
        var operation2 = ("INSERT INTO YourTable (Column1, Column2) VALUES (@Value5, @Value6)",
            new SqlParameter[]
            {
                new SqlParameter("@Value5", "TransactionValue3"),
                new SqlParameter("@Value6", "TransactionValue4")
            });
        try
        {
            databaseHelper.ExecuteTransaction(operation1, operation2);
            Console.WriteLine("事务操作成功提交");
        }
        catch (Exception ex)
        {
            Console.WriteLine($"事务操作失败，已回滚。原因: {ex.Message}");
        }

    }
}

using System;
using System.Data;
using System.Data.SqlClient;

namespace DatabaseAccess
{
    public class DatabaseHelper
    {
        private string connectionString;

        // 构造函数，用于传入数据库连接字符串
        public DatabaseHelper(string connectionString)
        {
            this.connectionString = connectionString;
        }

        // 打开数据库连接
        private SqlConnection OpenConnection()
        {
            SqlConnection connection = new SqlConnection(connectionString);
            connection.Open();
            return connection;
        }

        // 关闭数据库连接
        private void CloseConnection(SqlConnection connection)
        {
            if (connection!= null && connection.State == ConnectionState.Open)
            {
                connection.Close();
            }
        }

        // 通用的执行非查询语句的方法（用于增、删、改操作）
        public int ExecuteNonQuery(string sql, SqlParameter[] parameters = null)
        {
            using (SqlConnection connection = OpenConnection())
            {
                using (SqlCommand command = new SqlCommand(sql, connection))
                {
                    if (parameters!= null)
                    {
                        command.Parameters.AddRange(parameters);
                    }

                    return command.ExecuteNonQuery();
                }
            }
        }

        // 执行查询语句并返回结果集（以DataTable形式返回，方便后续处理）
        public DataTable ExecuteQuery(string sql, SqlParameter[] parameters = null)
        {
            DataTable dataTable = new DataTable();
            using (SqlConnection connection = OpenConnection())
            {
                using (SqlCommand command = new SqlCommand(sql, connection))
                {
                    if (parameters!= null)
                    {
                        command.Parameters.AddRange(parameters);
                    }

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

        // 开启事务并执行一系列操作（示例中简单地传入多个SQL语句和对应参数数组）
        public void ExecuteTransaction(params (string sql, SqlParameter[] parameters)[] operations)
        {
            using (SqlConnection connection = OpenConnection())
            {
                SqlTransaction transaction = connection.BeginTransaction();
                try
                {
                    using (SqlCommand command = connection.CreateCommand())
                    {
                        command.Transaction = transaction;
                        foreach (var operation in operations)
                        {
                            command.CommandText = operation.sql;
                            if (operation.parameters!= null)
                            {
                                command.Parameters.AddRange(operation.parameters);
                            }
                            command.ExecuteNonQuery();
                        }
                        transaction.Commit();
                    }
                }
                catch (Exception ex)
                {
                    transaction.Rollback();
                    throw ex;
                }
                finally
                {
                    CloseConnection(connection);
                }
            }
        }

    }
}