80、.NET 数据库操作：事务与批量复制实战

.NET 数据库操作：事务与批量复制实战

1. 数据库操作基础示例

在开始深入探讨数据库事务和批量复制之前，先来看一些基础的数据库操作示例代码。首先，引入必要的命名空间：

using AutoLot.Dal.Models;
using AutoLot.Dal.DataOperations;
using AutoLot.Dal.BulkImport;

以下是一系列操作，包括获取所有汽车信息、按颜色获取第一辆车、尝试删除汽车、插入新汽车、查找汽车昵称等：

InventoryDal dal = new InventoryDal();
List<CarViewModel> list = dal.GetAllInventory();
Console.WriteLine(" ************** All Cars ************** ");
Console.WriteLine("Id\tMake\tColor\tPet Name");
foreach (var itm in list)
{
    Console.WriteLine($"{itm.Id}\t{itm.Make}\t{itm.Color}\t{itm.PetName}");
}
Console.WriteLine();
CarViewModel car = dal.GetCar(list.OrderBy(x=>x.Color).Select(x => x.Id).First());
Console.WriteLine(" ************** First Car By Color ************** ");
Console.WriteLine("CarId\tMake\tColor\tPet Name");
Console.WriteLine($"{car.Id}\t{car.Make}\t{car.Color}\t{car.PetName}");
try
{
    //This will fail because of related data in the Orders table
    dal.DeleteCar(5);
    Console.WriteLine("Car deleted.");
}
catch (Exception ex)
{
    Console.WriteLine($"An exception occurred: {ex.Message}");
}
dal.InsertAuto(new Car { Color = "Blue", MakeId = 5, PetName = "TowMonster" });
list = dal.GetAllInventory();
var newCar = list.First(x => x.PetName == "TowMonster");
Console.WriteLine(" ************** New Car ************** ");
Console.WriteLine("CarId\tMake\tColor\tPet Name");
Console.WriteLine($"{newCar.Id}\t{newCar.Make}\t{newCar.Color}\t{newCar.PetName}");
dal.DeleteCar(newCar.Id);
var petName = dal.LookUpPetName(car.Id);
Console.WriteLine(" ************** New Car ************** ");
Console.WriteLine($"Car pet name: {petName}");
Console.Write("Press enter to continue...");
Console.ReadLine();

2. 理解数据库事务

数据库事务是一组数据库操作，它们作为一个整体要么全部成功，要么全部失败。如果其中一个操作失败，所有其他操作都会回滚，就像什么都没发生一样。事务对于确保表数据的安全性、有效性和一致性非常重要。

例如，在银行账户之间转移资金的经典事务场景中，从储蓄账户转移 500 美元到支票账户，应按以下步骤以事务方式进行：
1. 银行应从储蓄账户中扣除 500 美元。
2. 银行应向支票账户中添加 500 美元。

如果因为银行的某些错误，钱从储蓄账户中扣除了但没有转移到支票账户，这将是非常糟糕的情况。但如果这些步骤被包装在一个数据库事务中，数据库管理系统（DBMS）会确保所有相关步骤作为一个单元执行。如果事务的任何部分失败，整个操作将回滚到原始状态；如果所有步骤都成功，事务将被提交。

3. ADO.NET 事务对象的关键成员

所有使用的事务都实现了 IDbTransaction 接口，该接口定义了以下几个成员：

public interface IDbTransaction : IDisposable
{
    IDbConnection Connection { get; }
    IsolationLevel IsolationLevel { get; }
    void Commit();
    void Rollback();
}

• Connection 属性返回发起当前事务的连接对象的引用。
• 当数据库操作都成功时，调用 Commit() 方法，将所有待处理的更改持久化到数据存储中。
• 当发生运行时异常时，调用 Rollback() 方法，通知 DBMS 忽略所有待处理的更改，保持原始数据不变。

此外， SqlTransaction 类型还定义了一个名为 Save() 的成员，允许定义保存点。通过调用 Save() 并指定一个友好的字符串名称，在调用 Rollback() 时可以指定相同的名称进行部分回滚；如果不指定参数，则会回滚所有待处理的更改。

4. 向 InventoryDal 添加事务方法

为了处理信用风险，可以向 InventoryDal 类添加一个新的公共方法 ProcessCreditRisk() ：

public void ProcessCreditRisk(bool throwEx, int customerId)
{
    OpenConnection();
    // First, look up current name based on customer ID.
    string fName;
    string lName;
    var cmdSelect = new SqlCommand(
        "Select * from Customers where Id = @customerId",
        _sqlConnection);
    SqlParameter paramId = new SqlParameter
    {
        ParameterName = "@customerId",
        SqlDbType = SqlDbType.Int,
        Value = customerId,
        Direction = ParameterDirection.Input
    };
    cmdSelect.Parameters.Add(paramId);
    using (var dataReader = cmdSelect.ExecuteReader())
    {
        if (dataReader.HasRows)
        {
            dataReader.Read();
            fName = (string) dataReader["FirstName"];
            lName = (string) dataReader["LastName"];
        }
        else
        {
            CloseConnection();
            return;
        }
    }
    cmdSelect.Parameters.Clear();
    // Create command objects that represent each step of the operation.
    var cmdUpdate = new SqlCommand(
        "Update Customers set LastName = LastName + ' (CreditRisk) ' where Id = @customerId",
        _sqlConnection);
    cmdUpdate.Parameters.Add(paramId);
    var cmdInsert = new SqlCommand(
        "Insert Into CreditRisks (CustomerId,FirstName, LastName) Values( @CustomerId, @FirstName, @LastName)", _sqlConnection);
    SqlParameter parameterId2 = new SqlParameter
    {
        ParameterName = "@CustomerId",
        SqlDbType = SqlDbType.Int,
        Value = customerId,
        Direction = ParameterDirection.Input
    };
    SqlParameter parameterFirstName = new SqlParameter
    {
        ParameterName = "@FirstName",
        Value = fName,
        SqlDbType = SqlDbType.NVarChar,
        Size = 50,
        Direction = ParameterDirection.Input
    };
    SqlParameter parameterLastName = new SqlParameter
    {
        ParameterName = "@LastName",
        Value = lName,
        SqlDbType = SqlDbType.NVarChar,
        Size = 50,
        Direction = ParameterDirection.Input
    };
    cmdInsert.Parameters.Add(parameterId2);
    cmdInsert.Parameters.Add(parameterFirstName);
    cmdInsert.Parameters.Add(parameterLastName);
    // We will get this from the connection object.
    SqlTransaction tx = null;
    try
    {
        tx = _sqlConnection.BeginTransaction();
        // Enlist the commands into this transaction.
        cmdInsert.Transaction = tx;
        cmdUpdate.Transaction = tx;
        // Execute the commands.
        cmdInsert.ExecuteNonQuery();
        cmdUpdate.ExecuteNonQuery();
        // Simulate error.
        if (throwEx)
        {
            throw new Exception("Sorry!  Database error! Tx failed...");
        }
        // Commit it!
        tx.Commit();
    }
    catch (Exception ex)
    {
        Console.WriteLine(ex.Message);
        // Any error will roll back transaction.  Using the new conditional access operator to check for null.
        tx?.Rollback();
    }
    finally
    {
        CloseConnection();
    }
}

这个方法使用一个布尔参数 throwEx 来模拟是否抛出异常，从而模拟数据库事务失败的情况。在实际应用中，真正的数据库事务方法不会允许调用者随意强制逻辑失败。

5. 测试数据库事务

可以在 Program.cs 文件中添加一个新方法 FlagCustomer() 来测试数据库事务：

void FlagCustomer()
{
    Console.WriteLine("***** Simple Transaction Example *****\n");
    // A simple way to allow the tx to succeed or not.
    bool throwEx = true;
    Console.Write("Do you want to throw an exception (Y or N): ");
    var userAnswer = Console.ReadLine();
    if (string.IsNullOrEmpty(userAnswer) || userAnswer.Equals("N",StringComparison.OrdinalIgnoreCase))
    {
        throwEx = false;
    }
    var dal = new InventoryDal();
    // Process customer 1 – enter the id for the customer to move.
    dal.ProcessCreditRisk(throwEx, 1);
    Console.WriteLine("Check CreditRisk table for results");
    Console.ReadLine();
}

如果选择抛出异常，整个事务将回滚， Customers 表中客户的姓氏不会改变；如果不抛出异常， Customers 表中客户的姓氏将更新，并且该客户将被添加到 CreditRisks 表中。

6. 使用 ADO.NET 执行批量复制

当需要向数据库中加载大量记录时，前面介绍的方法效率较低。SQL Server 有一个名为批量复制的功能，在 ADO.NET 中通过 SqlBulkCopy 类实现。

6.1 探索 SqlBulkCopy 类

SqlBulkCopy 类有一个 WriteToServer() 方法（以及异步版本 WriteToServerAsync() ），它可以更高效地处理记录列表并将数据写入数据库，比使用 Command 对象执行一系列插入语句更高效。 WriteToServer 重载方法可以接受 DataTable 、 DataReader 或 DataRows 数组。这里将使用 DataReader 版本，因此需要创建一个自定义数据读取器。

6.2 创建自定义数据读取器

首先，在 AutoLot.Dal 项目中创建一个名为 BulkImport 的新文件夹，在该文件夹中创建一个新的接口类 IMyDataReader.cs ，实现 IDataReader 接口：

namespace AutoLot.Dal.BulkImport;
public interface IMyDataReader<T> : IDataReader
{
    List<T> Records { get; set; }
}

然后，创建一个新的类 MyDataReader.cs ，实现 IMyDataReader 接口：

public sealed class MyDataReader<T> : IMyDataReader<T>
{
    public List<T> Records { get; set; }
    public MyDataReader(List<T> records)
    {
        Records = records;
    }
}

需要实现一些关键方法，如下表所示：
| 方法 | 含义 |
| ---- | ---- |
| Read | 获取下一条记录；如果还有记录则返回 true ，如果到达列表末尾则返回 false |
| FieldCount | 获取数据源中字段的总数 |
| GetValue | 根据序号位置获取字段的值 |
| GetSchemaTable | 获取目标表的架构信息 |

以下是 Read() 方法的实现示例：

public class MyDataReader<T> : IMyDataReader<T>
{
    private int _currentIndex = -1;
    public bool Read()
    {
        if (_currentIndex + 1 >= Records.Count)
        {
            return false;
        }
        _currentIndex++;
        return true;
    }
}

GetValue() 方法的示例（以 Car 类为例）：

public object GetValue(int i)
{
    Car currentRecord = Records[_currentIndex] as Car;
    return i switch
    {
        0 => currentRecord.Id,
        1 => currentRecord.MakeId,
        2 => currentRecord.Color,
        3 => currentRecord.PetName,
        4 => currentRecord.TimeStamp,
        _ => string.Empty,
    };
}

为了使代码更通用，可以使用反射和 LINQ to Objects 来优化。更新构造函数和相关方法，以获取模型的属性信息和目标表的字段信息：

private readonly PropertyInfo[] _propertyInfos;
private readonly Dictionary<int, string> _nameDictionary;
private readonly SqlConnection _connection;
private readonly string _schema;
private readonly string _tableName;
public MyDataReader(List<T> records, SqlConnection connection, string schema, string tableName)
{
    Records = records;
    _propertyInfos = typeof(T).GetProperties();
    _nameDictionary = new Dictionary<int, string>();
    _connection = connection;
    _schema = schema;
    _tableName = tableName;
    DataTable schemaTable = GetSchemaTable();
    for (int x = 0; x<schemaTable?.Rows.Count;x++)
    {
        DataRow col = schemaTable.Rows[x];
        var columnName = col.Field<string>("ColumnName");
        _nameDictionary.Add(x,columnName);
    }
}
public DataTable GetSchemaTable()
{
    using var schemaCommand = new SqlCommand($"SELECT * FROM {_schema}.{_tableName}", _connection);
    using var reader = schemaCommand.ExecuteReader(CommandBehavior.SchemaOnly);
    return reader.GetSchemaTable();
}
public int FieldCount => _propertyInfos.Length;
public object GetValue(int i)
    => _propertyInfos
        .First(x=>x.Name.Equals(_nameDictionary[i],StringComparison.OrdinalIgnoreCase))
        .GetValue(Records[_currentIndex]);

其余必须存在但未实现的方法如下：

public string GetName(int i) => throw new NotImplementedException();
public int GetOrdinal(string name) => throw new NotImplementedException();
public string GetDataTypeName(int i) => throw new NotImplementedException();
public Type GetFieldType(int i) => throw new NotImplementedException();
public int GetValues(object[] values) => throw new NotImplementedException();
public bool GetBoolean(int i) => throw new NotImplementedException();
public byte GetByte(int i) => throw new NotImplementedException();
public long GetBytes(int i, long fieldOffset, byte[] buffer, int bufferoffset, int length)
    => throw new NotImplementedException();
public char GetChar(int i) => throw new NotImplementedException();
public long GetChars(int i, long fieldoffset, char[] buffer, int bufferoffset, int length)
    => throw new NotImplementedException();
public Guid GetGuid(int i) => throw new NotImplementedException();
public short GetInt16(int i) => throw new NotImplementedException();
public int GetInt32(int i) => throw new NotImplementedException();
public long GetInt64(int i) => throw new NotImplementedException();
public float GetFloat(int i) => throw new NotImplementedException();
public double GetDouble(int i)  => throw new NotImplementedException();
public string GetString(int i) => throw new NotImplementedException();
public decimal GetDecimal(int i) => throw new NotImplementedException();
public DateTime GetDateTime(int i) => throw new NotImplementedException();
public IDataReader GetData(int i) => throw new NotImplementedException();
public bool IsDBNull(int i) => throw new NotImplementedException();
object IDataRecord.this[int i] => throw new NotImplementedException();
object IDataRecord.this[string name] => throw new NotImplementedException();
public void Close() => throw new NotImplementedException();
public DataTable GetSchemaTable() => throw new NotImplementedException();
public bool NextResult() => throw new NotImplementedException();
public int Depth { get; }
public bool IsClosed { get; }
public int RecordsAffected { get; }

6.3 执行批量复制

在 BulkImport 文件夹中添加一个新的公共静态类 ProcessBulkImport.cs ，处理连接的打开和关闭：

private const string ConnectionString =
    @"Data Source=.,5433;User Id=sa;Password=P@ssw0rd;Initial Catalog=AutoLot;Encrypt=False;";
private static SqlConnection _sqlConnection = null;
private static void OpenConnection()
{
    _sqlConnection = new SqlConnection
    {
        ConnectionString = ConnectionString
    };
    _sqlConnection.Open();
}
private static void CloseConnection()
{
    if (_sqlConnection?.State != ConnectionState.Closed)
    {
        _sqlConnection?.Close();
    }
}

添加 ExecuteBulkImport 方法来执行批量复制：

public static void ExecuteBulkImport<T>(IEnumerable<T> records, string tableName)
{
    OpenConnection();
    using SqlConnection conn = _sqlConnection;
    SqlBulkCopy bc = new SqlBulkCopy(conn)
    {
        DestinationTableName = tableName
    };
    var dataReader = new MyDataReader<T>(records.ToList(),_sqlConnection, "dbo",tableName);
    try
    {
        bc.WriteToServer(dataReader);
    }
    catch (Exception ex)
    {
        //Should do something here
    }
    finally
    {
        CloseConnection();
    }
}

6.4 测试批量复制

在 Program.cs 文件中添加一个新方法 DoBulkCopy() 来测试批量复制：

void DoBulkCopy()
{
    Console.WriteLine(" ************** Do Bulk Copy ************** ");
    var cars = new List<Car>
    {
        new Car() {Color = "Blue", MakeId = 1, PetName = "MyCar1"},
        new Car() {Color = "Red", MakeId = 2, PetName = "MyCar2"},
        new Car() {Color = "White", MakeId = 3, PetName = "MyCar3"},
        new Car() {Color = "Yellow", MakeId = 4, PetName = "MyCar4"}
    };
    ProcessBulkImport.ExecuteBulkImport(cars, "Inventory");
    InventoryDal dal = new InventoryDal();
    List<CarViewModel> list = dal.GetAllInventory();
    Console.WriteLine(" ************** All Cars ************** ");
    Console.WriteLine("CarId\tMake\tColor\tPet Name");
    foreach (var itm in list)
    {
        Console.WriteLine(
            $"{itm.Id}\t{itm.Make}\t{itm.Color}\t{itm.PetName}");
    }
    Console.WriteLine();
}

虽然添加四辆新车不能充分体现 SqlBulkCopy 类的优势，但想象一下加载数千条记录的情况，使用 SqlBulkCopy 可以将加载时间从数小时缩短到几秒。这是 .NET 中一个非常有用的工具，在需要时可以发挥重要作用。

.NET 数据库操作：事务与批量复制实战

7. 总结与应用场景分析

前面详细介绍了 .NET 中数据库操作的基础示例、事务处理以及批量复制的相关知识。下面对这些内容进行总结，并分析不同操作的应用场景。

7.1 基础数据库操作

基础数据库操作包括获取数据、插入数据、删除数据和查找数据等。这些操作适用于日常的简单数据处理，例如：
- 获取数据 ：当需要展示数据库中的数据时，如显示所有汽车信息或按条件筛选出特定的汽车信息。
- 插入数据 ：在新增业务数据时，如添加一辆新的汽车到库存表中。
- 删除数据 ：当某些数据不再需要时，如删除一辆已售出的汽车记录。
- 查找数据 ：根据特定条件查找数据，如查找某辆车的昵称。

7.2 数据库事务

数据库事务确保一组数据库操作要么全部成功，要么全部失败，保证了数据的一致性和完整性。适用于以下场景：
- 涉及多个表的操作 ：当一个业务操作需要同时对多个表进行数据修改时，如在处理客户信用风险时，需要同时更新 Customers 表和插入数据到 CreditRisks 表。
- 数据一致性要求高的场景 ：如银行转账操作，必须确保资金的扣除和添加操作同时成功或失败，否则会导致数据不一致。

7.3 批量复制

批量复制适用于需要向数据库中加载大量记录的场景，如：
- 数据迁移 ：将大量历史数据从一个数据源迁移到另一个数据库中。
- 初始化数据 ：在系统上线前，向数据库中批量插入大量的初始数据。

8. 操作步骤总结

为了方便大家更好地应用这些知识，下面总结了各个操作的具体步骤。

8.1 基础数据库操作步骤

1. 引入命名空间 ：

using AutoLot.Dal.Models;
using AutoLot.Dal.DataOperations;
using AutoLot.Dal.BulkImport;

2. 创建 InventoryDal 实例 ：

InventoryDal dal = new InventoryDal();

3. 执行具体操作 ：
   • 获取所有汽车信息：

List<CarViewModel> list = dal.GetAllInventory();

- 插入新汽车：

dal.InsertAuto(new Car { Color = "Blue", MakeId = 5, PetName = "TowMonster" });

- 删除汽车：

dal.DeleteCar(carId);

- 查找汽车昵称：

var petName = dal.LookUpPetName(carId);

8.2 数据库事务操作步骤

1. 在 InventoryDal 类中添加 ProcessCreditRisk 方法 ：

public void ProcessCreditRisk(bool throwEx, int customerId)
{
    // 具体方法实现见前文
}

2. 在 Program.cs 中调用 FlagCustomer 方法进行测试 ：

void FlagCustomer()
{
    // 具体方法实现见前文
}

8.3 批量复制操作步骤

1. 创建自定义数据读取器 ：
   • 创建 IMyDataReader.cs 接口：

namespace AutoLot.Dal.BulkImport;
public interface IMyDataReader<T> : IDataReader
{
    List<T> Records { get; set; }
}

- 创建 `MyDataReader.cs` 类并实现接口：

public sealed class MyDataReader<T> : IMyDataReader<T>
{
    // 具体类实现见前文
}

2. 在 BulkImport 文件夹中添加 ProcessBulkImport.cs 类 ：

private const string ConnectionString =
    @"Data Source=.,5433;User Id=sa;Password=P@ssw0rd;Initial Catalog=AutoLot;Encrypt=False;";
private static SqlConnection _sqlConnection = null;
private static void OpenConnection()
{
    // 具体方法实现见前文
}
private static void CloseConnection()
{
    // 具体方法实现见前文
}
public static void ExecuteBulkImport<T>(IEnumerable<T> records, string tableName)
{
    // 具体方法实现见前文
}

3. 在 Program.cs 中调用 DoBulkCopy 方法进行测试 ：

void DoBulkCopy()
{
    // 具体方法实现见前文
}

9. 流程图展示

下面使用 mermaid 格式的流程图展示数据库事务和批量复制的操作流程。

9.1 数据库事务操作流程

graph TD;
    A[开始] --> B[打开连接];
    B --> C[查询客户信息];
    C --> D{是否有记录};
    D -- 是 --> E[创建插入和更新命令];
    D -- 否 --> F[关闭连接];
    E --> G[开始事务];
    G --> H[将命令加入事务];
    H --> I[执行插入和更新命令];
    I --> J{是否抛出异常};
    J -- 是 --> K[回滚事务];
    J -- 否 --> L[提交事务];
    K --> M[关闭连接];
    L --> M[关闭连接];
    F --> N[结束];
    M --> N[结束];

9.2 批量复制操作流程

graph TD;
    A[开始] --> B[打开连接];
    B --> C[创建 SqlBulkCopy 实例];
    C --> D[设置目标表名];
    D --> E[创建自定义数据读取器];
    E --> F[执行批量复制];
    F --> G{是否出错};
    G -- 是 --> H[处理异常];
    G -- 否 --> I[无操作];
    H --> J[关闭连接];
    I --> J[关闭连接];
    J --> K[结束];

10. 总结与展望

通过本文的介绍，我们学习了 .NET 中数据库操作的基础示例、数据库事务的使用以及批量复制的实现。这些知识在实际开发中非常有用，可以帮助我们更好地处理数据库操作，提高数据处理的效率和数据的一致性。

在未来的开发中，我们可以进一步优化这些操作，例如：
- 事务优化 ：合理设置事务的隔离级别，避免事务之间的冲突，提高系统的并发性能。
- 批量复制优化 ：根据不同的数据源和数据库类型，选择更合适的批量复制方式，进一步提高数据加载的速度。

希望大家能够将这些知识应用到实际项目中，提升自己的开发能力和解决问题的能力。