C# 6 与 .NET Core 1.0 高级编程 - 37 章 ADO.NET

译文，个人原创，转载请注明出处（C# 6 与 .NET Core 1.0 高级编程 - 37 章 ADO.NET），不对的地方欢迎指出与交流。

英文原文：Professional C# 6 and .NET Core 1.0 - 37 ADO.NET

-------------------------------

本章内容

连接数据库
执行命令
调用存储过程
ADO.NET对象模型

Wrox.com 网站关于本章的源代码下载

wrox.com中本章源代码下载位于“Download Code”选项卡www.wrox.com/go/professionalcsharp6。本章分为以下几个主要例子：

ConnectionSamples
CommandSamples
AsyncSamples
TransactionSamples

ADO.NET概述

本章讨论如何使用ADO.NET从C＃程序访问关系数据库，如SQL Server。它显示与数据库的连接与关闭，以及如何使用查询，添加和更新记录。您将学习各种命令对象选项，并了解如何使用SQL Server程序类提供的命令中的选项如何使用；如何用命令对象调用存储过程；以及如何使用事务。
早期版本的ADO.NET提供了不同的数据库提供程序：SQL Server的提供程序和一个用于Oracle的提供程序，OLEDB和ODBC。 OLEDB技术已停产，因此新的应用程序不提倡使用该提供程序。访问Oracle数据库，Microsoft的提供程序也停止了，因为来自Oracle的提供程序（http://www.oracle.com/technetwork/topics/dotnet/）
更适合需求。对于其他数据源（也适用于Oracle），许多第三方提供程序都可用。在使用ODBC提供程序之前，应该使用特定的访问数据源的提供程序。本章中的代码示例基于SQL Server，但是您可以轻松地将其更改为使用不同的连接和命令对象，例如在访问Oracle数据库使用OracleConnection和OracleCommand，而不是SqlConnection和SqlCommand。

注意本章不讨论DataSet在内存中包含表。数据集虽然允许从数据库检索记录，并将内容存储在具有关系的内存数据表中。但我们应该使用Entity Framework，它在第38章“Entity Framework Core”中讨论。Entity Framework能够拥有对象关系而不是基于表的关系。

示例数据库

本章中的示例使用AdventureWorks2014数据库，可以从https://msftdbprodsamples.codeplex.com/下载此数据库。链接可以下载一个zip文件中的AdventureWorks2014数据库的备份。选择推荐的下载 - Adventure Works 2014 Full Database Backup.zip。解压缩文件后，可以使用SQL Server Management Studio恢复数据库备份，如图37.1所示。如果您的系统上没有SQL Server Management Studio，可以从http://www.microsoft.com/downloads下载免费版本。

图 37.1

本章使用的SQL服务器是SQL Server LocalDb。这是作为Visual Studio的一部分安装的数据库服务器。您也可以使用其他任意的SQL Server版本;只需要相应地改变连接字符串。

NuGet包和命名空间

所有ADO.NET示例的代码使用以下依赖项和命名空间：

依赖项

NETStandard.Library
Microsoft.Extensions.Configuration
Microsoft.Extensions.Configuration.Json
System.Data.SqlClient

命名空间

Microsoft.Extensions.Configuration
System
System.Data
System.Data.SqlClient
System.Threading.Tasks
static System.Console

使用数据库连接

访问数据库需要提供连接参数，例如运行数据库的计算机以及可能的登录凭据。可以使用SqlConnection类创建SQL Server的连接。
以下代码段说明如何创建、打开和关闭AdventureWorks2014数据库的连接（代码文件ConnectionSamples / Program.cs）：

public static void OpenConnection()
{
  string connectionString = @"server=(localdb)\MSSQLLocalDB;" +
                  "integrated security=SSPI;" +
                  "database=AdventureWorks2014";
  var connection = new SqlConnection(connectionString);
  connection.Open(); 
  // Do something useful
  WriteLine("Connection opened"); 
  connection.Close();
}

注意除了Close方法外，SqlConnection类还使用Dispose方法实现IDisposable接口。两者同样可以释放连接。有了这个，你可以使用using语句关闭连接。

在示例连接字符串中，使用的参数如下（参数由连接字符串中的分号分隔）：

server =（localdb）\ MSSQLLocalDB - 这表示要连接到的数据库服务器。 SQL Server允许许多单独的数据库服务器实例在同一台机器上运行。表示您正在连接到localdb的服务器，MSSQLLocalDB是通过安装SQL Server创建的SQL Server实例。如果使用SQL Server的本地安装，请将此部件更改为server =（local）。连接到SQL Azure，使用关键字Data Source而不是 server，可以设置Data Source = servername.database.windows.net。
database = AdventureWorks2014 - 描述要连接的数据库实例。每个SQL Server进程可以公开几个数据库实例，使用关键字 Initial Catalog，不是database。
集成安全性= SSPI - 这里使用Windows身份验证连接到数据库。另外如果要用SQL Azure，需要设置 User Id 和Password。

注意有关许多不同数据库的连接字符串的详细信息，请访问http://www.connectionstrings.com。

ConnectionSamples示例使用定义的连接字符串打开数据库连接，然后关闭该连接。打开连接后，可以对数据源发出命令;完成后，可以关闭连接。

管理连接字符串

最好从配置文件中读取连接字符串，不要用C＃代码硬编码。对于.NET 4.6和.NET Core 1.0，配置文件可以是JSON或XML格式，也可以从环境变量读取。以下示例从JSON配置文件中读取连接字符串（代码文件ConnectionSamples / config.json）：

{
  "Data": {
    "DefaultConnection": {
      "ConnectionString":
        "Server=(localdb)\\MSSQLLocalDB;Database=AdventureWorks2014;Trusted_Connection=True;"
    }
  }
}

可以使用NuGet包的Microsoft.Framework.Configuration中定义的配置API读取JSON文件。要使用JSON配置文件，需要添加 NuGet包Microsoft.Framework.Configuration.Json。创建ConfigurationBuilder读取配置文件。 AddJsonFile 扩展方法添加JSON文件config.json以读取来自此文件的配置信息（如果它与程序在同一路径中）。要配置不同的路径，可以调用方法SetBasePath。调用ConfigurationBuilder的Build方法从所有添加的配置文件构建配置并返回实现Iconfiguration接口的对象。这样，可以检索配置值，例如Data的配置值：DefaultConnection：ConnectionString（代码文件ConnectionSamples / Program.cs）：

public static void ConnectionUsingConfig()
{
  var configurationBuilder = new ConfigurationBuilder().AddJsonFile("config.json");
  IConfiguration config = configurationBuilder.Build();
  string connectionString = config["Data:DefaultConnection:ConnectionString"];
  WriteLine(connectionString);
}

连接池

几年前完成的两层应用程序中，最好是在应用程序启动时打开连接，并在应用程序关闭时关闭它。但现在这不是一个好主意。这个程序架构的原因是它需要一些时间来打开一个连接。现在，关闭连接不会关闭与服务器的连接。相反，连接将会被添加到一个连接池。当再次打开连接时，可以从池中取出，因此打开连接非常快;它仅在打开第一个连接时需要时间。
连接池可以使用连接字符串中的多个选项来配置。将选项Pooling设置为false将禁用连接池;默认情况下是启用的-Pooling = true。属性 Min Pool Size和Max Pool Size 能够配置池中的连接数。默认情况下，“Min Pool Size”的值为0，“Max Pool Size”的值为100。Connection Lifetime 定义连接在真正释放之前应在池中保持不活动状态的时间。

连接信息

创建连接后，可以注册事件处理程序以获取有关连接的一些信息。 SqlConnection类定义了InfoMessage和StateChange事件。每次从SQL Server返回信息或警告消息时，将触发InfoMessage事件。当连接的状态更改（例如连接已打开或关闭）时会触发StateChange事件（代码文件ConnectionSamples / Program.cs）：

public static void ConnectionInformation()
{
  using (var connection = new SqlConnection(GetConnectionString()))
  {
    connection.InfoMessage += (sender, e) =>
    {
      WriteLine($"warning or info {e.Message}");
    };
    connection.StateChange += (sender, e) =>
    {
      WriteLine($"current state: {e.CurrentState}, before: {e.OriginalState}");
    };
    connection.Open();
    WriteLine("connection opened");
    // Do something useful
  }
}

运行应用程序，可以看到StateChange事件触发，已打开状态和已关闭状态：

current state: Open, before: Closed
connection opened
current state: Closed, before: Open

命令

“使用数据库连接”一节简要介绍了针对数据库发出命令的思路。命令是最简单的形式，是一个包含要发出到数据库的SQL语句的文本字符串。命令也可以是存储过程，稍后在本节中显示。
可以通过将SQL语句作为参数传递到Command类的构造函数来构造命令，如本示例所示（代码文件CommandSamples / Program.cs）：

public static void CreateCommand()
{
  using (var connection = new SqlConnection(GetConnectionString()))
  {
    string sql ="SELECT BusinessEntityID, FirstName, MiddleName, LastName" +      "FROM Person.Person";
var command = new SqlCommand(sql, connection);
connection.Open();
    // etc.
  }
}

还可以通过调用SqlConnection的CreateCommand方法将SQL语句分配给CommandText属性来创建命令：

SqlCommand command = connection.CreateCommand();
command.CommandText = sql;

命令经常需要参数。例如，以下SQL语句需要EmailPromotion参数。不要使用字符串连接来建立参数。相反，请使用ADO.NET的参数功能：

string sql ="SELECT BusinessEntityID, FirstName, MiddleName, LastName" +     "FROM Person.Person WHERE EmailPromotion = @EmailPromotion";
var command = new SqlCommand(sql, connection);

有一个简单的方法将参数添加到SqlCommand对象，那就是使用Parameters属性返回SqlParameterCollection和使用AddWithValue方法：

 command.Parameters.AddWithValue("EmailPromotion", 1);

更有效的方法是通过传递名称和SQL数据类型来使用Add方法的重载：

command.Parameters.Add("EmailPromotion", SqlDbType.Int);
command.Parameters["EmailPromotion"].Value = 1;

当然也可以创建一个SqlParameter对象，并将其添加到SqlParameterCollection。

注意不要倾向于使用SQL参数的字符串连接，因为它通常被滥用于SQL注入攻击。使用SqlParameter对象可以禁止这种攻击。

定义命令后，需要执行该命令。有几种方法来发布语句，这取决于什么，如果有什么，你期望从该命令返回。 SqlCommand类提供了以下ExecuteXX方法：

ExecuteNonQuery—执行命令，但不返回任何输出
ExecuteReader—执行命令并返回一个类型化的IDataReader
ExecuteScalar—执行命令，并从任何结果集的第一行的第一列返回值

ExecuteNonQuery

ExecuteNonQuery方法通常用于UPDATE，INSERT或DELETE语句，其中唯一的返回值是受影响的记录数。但是，如果调用具有输出参数的存储过程，则此方法可能返回结果。示例代码在Sales.SalesTerritory表中创建一条新记录。此表的主键TerritoryID是标识列，因此不需要提供此属性来创建记录。此表的所有列都不允许为空（请参见图37.2），但其中某些字段有默认值，例如 sales 和 cost 、rowguid和ModifiedDate 字段。 rowguid列是从函数newid创建的，而ModifiedDate列是由getdate创建的。创建一个新行时，只需要提供Name，CountryRegionCode和Group列。方法ExecuteNonQuery定义SQL INSERT语句，添加参数值，并调用SqlCommand类的ExecuteNonQuery方法（代码文件CommandSamples / Program.cs）：

public static void ExecuteNonQuery
{
  try
  {
    using (var connection = new SqlConnection(GetConnectionString()))
    {
      string sql ="INSERT INTO [Sales].[SalesTerritory]"  + "([Name], [CountryRegionCode], [Group])" + "VALUES (@Name, @CountryRegionCode, @Group)";
      var command = new SqlCommand(sql, connection);
      command.Parameters.AddWithValue("Name","Austria");
      command.Parameters.AddWithValue("CountryRegionCode","AT");
      command.Parameters.AddWithValue("Group","Europe");

      connection.Open();
      int records = command.ExecuteNonQuery();
      WriteLine($"{records} inserted");
    }
  }
  catch (SqlException ex)
  {
    WriteLine(ex.Message);
  }
}

图 37.2

ExecuteNonQuery将命令影响的行数作为int返回。当第一次运行该方法时，将插入一个记录。第二次运行相同的方法时，由于唯一的索引冲突会产生异常。Name 定义为唯一索引，因此一个Name值不会在表中出现多次。要再次运行该方法，需要首先删除创建的记录。

ExecuteScalar

在许多情况下需要从SQL语句返回一个结果值，例如指定表中的记录计数或服务器上的当前日期/时间。这种情况下可以使用ExecuteScalar方法：

public static void ExecuteScalar()
{
  using (var connection = new SqlConnection(GetConnectionString()))
  {
    string sql ="SELECT COUNT(*) FROM Production.Product";
    SqlCommand command = connection.CreateCommand();
    command.CommandText = sql;
    connection.Open();
    object count = command.ExecuteScalar();
    WriteLine($&rdquo;counted {count} product records&rdquo;);
  }
}

该方法返回一个对象，必要情况下可以将其转换为适当的类型。如果要调用的SQL仅返回一列，使用ExecuteScalar比其他提取该列的方法更优。这也适用于返回单个值的存储过程。

ExecuteReader

ExecuteReader方法执行命令并返回数据读取器对象，返回的对象可以用于遍历返回的记录。下面代码片段中的ExecuteReader示例显示SQL INNER JOIN子句使用。此SQL INNER JOIN子句用于获取单个产品的价格历史记录。价格历史存储在表Production.ProductCostHistory中，产品名称在数据表Production.Product。SQL语句中产品标识符需要一个参数（代码文件CommandSamples / Program.cs）：

private static string GetProductInformationSQL() =>
  "SELECT Prod.ProductID, Prod.Name, Prod.StandardCost, Prod.ListPrice," +     "CostHistory.StartDate, CostHistory.EndDate, CostHistory.StandardCost" +  "FROM Production.ProductCostHistory AS CostHistory  " +   "INNER JOIN Production.Product AS Prod ON" + "CostHistory.ProductId = Prod.ProductId" +   "WHERE Prod.ProductId = @ProductId";

调用SqlCommand对象的方法ExecuteReader时，将返回SqlDataReader。请注意SqlDataReader需要在使用后进行释放处理。另外注意这次SqlConnection对象在方法的结尾没有显式地释放。将参数CommandBehavior.CloseConnection传递给ExecuteReader方法会在关闭阅读器时自动关闭连接。如果不提供此设置，需要手动关闭连接。

为了从数据读取器读取记录，在while循环中调用Read方法。第一次调用Read方法将光标移动到返回的第一条记录。当再次调用读取时，光标位于下一个记录 - 只要有可用的记录。如果在下一个位置没有记录可用，则Read方法返回false。访问列的值时，调用不同的GetXXX方法，例如GetInt32，GetString和GetDateTime。这些方法是强类型的，因为它们返回所需的特定类型，如int，string和DateTime。传递到这些方法的索引对应于使用SQL SELECT语句检索的列，即使数据库结构更改索引也保持不变。需要注意从数据库返回null的值，使用强类型的GetXXX方法时，GetXXX方法会抛出异常。在检索到数据时，只有CostHistory.EndDate可以为null;所有其他列不能为数据库模式定义的空值。为了避免这种异常情况，C＃条件语句 ? : 用于检查SqlDataReader.IsDbNull方法的值是否为空。在这种情况下null被分配给可空的DateTime。仅当值不为null时，DateTime才能被GetDateTime方法访问（代码文件CommandSamples / Program.cs）：

public static void ExecuteReader(int productId)
{
  var connection = new SqlConnection(GetConnectionString());

  string sql = GetProductInformationSQL();
  var command = new SqlCommand(sql, connection);
  var productIdParameter = new SqlParameter("ProductId", SqlDbType.Int);
  productIdParameter.Value = productId;
  command.Parameters.Add(productIdParameter);
  connection.Open();
  using (SqlDataReader reader =     command.ExecuteReader(CommandBehavior.CloseConnection))
  {
    while (reader.Read())
    {
      int id = reader.GetInt32(0);
      string name = reader.GetString(1);
      DateTime from = reader.GetDateTime(4);
      DateTime? to = reader.IsDBNull(5) ? (DateTime?)null: reader.GetDateTime(5);
      decimal standardPrice = reader.GetDecimal(6);
      WriteLine($"{id} {name} from: {from:d} to: {to:d};" + $"price: {standardPrice}");
    }
  }
}

当运行应用程序并将产品ID 717传递给ExecuteReader方法时，可以看到以下输出：

717 HL Road Frame—Red, 62 from: 5/31/2011 to: 5/29/2012; price: 747.9682
717 HL Road Frame—Red, 62 from: 5/30/2012 to: 5/29/2013; price: 722.2568
717 HL Road Frame—Red, 62 from: 5/30/2013 to:; price: 868.6342

有关产品ID的可能值，请检查数据库的内容。使用SqlDataReader，可以使用返回对象的非类型化索引器而不必使用类型化的方法GetXXX，但需要转换为相应的类型：

int id = (int)reader[0];
string name = (string)reader[1];
DateTime from = (DateTime)reader[2];
DateTime? to = (DateTime?)reader[3];

SqlDataReader的索引器还允许使用字符串传递列名而不仅是int。这是这些不同选项中最慢的方法，但它可能满足您的需求。与进行服务调用所花费的时间相比，访问索引器所需的额外时间可以忽略：

int id = (int)reader["ProductID"];
string name = (string)reader["Name"];
DateTime from = (DateTime)reader["StartDate"];
DateTime? to = (DateTime?)reader["EndDate"];

调用存储过程

使用命令对象调用存储过程只关系到存储过程的名称，为该存储过程的每个参数添加定义，然后使用上一节中介绍的方法之一执行命令。
以下示例调用存储过程uspGetEmployeeManagers以获取员工的所有经理。此存储过程接收一个参数，使用递归查询返回所有管理器的记录：

CREATE PROCEDURE [dbo].[uspGetEmployeeManagers]
    @BusinessEntityID [int]
AS
—...

要查看存储过程的实现，请检查AdventureWorks2014数据库。
为了调用存储过程，需将SqlCommand对象的CommandText设置为存储过程的名称，并将CommandType设置为CommandType.StoredProcedure。除此之外，该命令的调用方式与之前看到的方式类似。该参数是使用SqlCommand对象的CreateParameter方法创建的，但也可以使用早期的其他方法创建参数。使用参数需填充SqlDbType，ParameterName和Value属性。由于存储过程返回记录，所以通过调用方法ExecuteReader来调用它（代码文件CommandSamples / Program.cs）：

 private static void StoredProcedure(int entityId)
{
  using (var connection = new SqlConnection(GetConnectionString()))
  {
    SqlCommand command = connection.CreateCommand();
    command.CommandText ="[dbo].[uspGetEmployeeManagers]";
    command.CommandType = CommandType.StoredProcedure;
    SqlParameter p1 = command.CreateParameter();
    p1.SqlDbType = SqlDbType.Int;
    p1.ParameterName ="@BusinessEntityID";
    p1.Value = entityId;
    command.Parameters.Add(p1);
    connection.Open();
    using (SqlDataReader reader = command.ExecuteReader())
    {
      while (reader.Read())
      {
        int recursionLevel = (int)reader["RecursionLevel"];
        int businessEntityId = (int)reader["BusinessEntityID"];
        string firstName = (string)reader["FirstName"];
        string lastName = (string)reader["LastName"];
        WriteLine($"{recursionLevel} {businessEntityId}" +
          $"{firstName} {lastName}");
      }
    }
  }
}

运行应用程序并传递实体ID 251时，可以获得此员工的经理，如下所示：

0 251 Mikael Sandberg
1 250 Sheela Word
2 249 Wendy Kahn

根据存储过程的返回类型，需要使用ExecuteReader，ExecuteScalar或ExecuteNonQuery调用存储过程。
使用包含输出参数的存储过程，需要指定SqlParameter的Direction属性。通常情况下方向为ParameterDirection.Input：

var pOut = new SqlParameter();
pOut.Direction = ParameterDirection.Output;

异步数据访问

访问数据库可能需要一些时间，这里不应该限制用户交互。 ADO.NET类通过提供异步方法以及同步方法来提供基于任务的异步编程。以下代码片段与使用SqlDataReader的上一个代码片段类似，但它使用Async方法调用。连接用SqlConnection.OpenAsync打开，读取器从方法SqlCommand.ExecuteReaderAsync返回，同时检索记录使用SqlDataReader.ReadAsync。通过所有这些方法，调用线程不会被阻塞，这样可以在获取结果之前进行其他工作（代码文件AsyncSamples / Program.cs）：

public static void Main()
{
  ReadAsync(714).Wait();
}

public static async Task ReadAsync(int productId)
{
  var connection = new SqlConnection(GetConnectionString());
  string sql =   
 "SELECT Prod.ProductID, Prod.Name, Prod.StandardCost, Prod.ListPrice," +      "CostHistory.StartDate, CostHistory.EndDate, CostHistory.StandardCost" +    "FROM Production.ProductCostHistory AS CostHistory  " +    "INNER JOIN Production.Product AS Prod ON" +      "CostHistory.ProductId = Prod.ProductId" +    "WHERE Prod.ProductId = @ProductId";
  var command = new SqlCommand(sql, connection);
  var productIdParameter = new SqlParameter("ProductId", SqlDbType.Int);
  productIdParameter.Value = productId;
  command.Parameters.Add(productIdParameter);
  await connection.OpenAsync();
  using (SqlDataReader reader = await command.ExecuteReaderAsync(CommandBehavior.CloseConnection))
  {
    while (await reader.ReadAsync())
    {
      int id = reader.GetInt32(0);
      string name = reader.GetString(1);
      DateTime from = reader.GetDateTime(4);
      DateTime? to = reader.IsDBNull(5) ? (DateTime?)null:
        reader.GetDateTime(5);
      decimal standardPrice = reader.GetDecimal(6);
      WriteLine($"{id} {name} from: {from:d} to: {to:d};" +$"price: {standardPrice}");
    }
  }
}

使用异步方法调用不仅有利于Windows应用程序，在服务器端同时进行多个调用也很有用。 ADO.NET API的异步方法有重载以支持CancellationToken早期停止长时间运行的方法。
注意有关异步方法调用和CancellationToken的更多信息，请参阅第15章“异步编程”。

事务

默认情况下单个命令在事务内运行。如果需要发出多个命令，并且所有这些命令都发生或者都没有发生，那么可以显式地启动和提交事务。
事务由术语ACID描述。 ACID是原子性，一致性，隔离性和持久性四个词的首字母缩写：

原子性 - 表示一个工作单元。使用事务，完整的工作单元成功或没有任何更改。
一致性 - 事务开始之前和事务完成后的状态必须有效。在事务期间状态可以具有临时值。
隔离性 - 并发的事务同时发生，但事务期间更改的状态会被隔离。事务A在事务完成之前无法看到事务B的临时状态。
持久性 - 事务完成后，必须以持久方式存储。这意味着如果电源关闭或服务器崩溃，则必须在重新引导时恢复状态。

注意事务和有效的状态可以简单地形容为婚礼。一对新婚夫妇站在事务协调员面前，事务协调员问这对夫妇中的第一个：“你愿意和你身边的这个人结婚吗？”如果第一个同意，第二个会被问：“你愿意和这个人结婚吗 ”如果第二个拒绝，第一个接收回滚。此事务的有效状态只是两者都已婚，或都没有结婚。如果两者都同意，则交易被提交并且两者都处于已婚状态。只要有一个拒绝，交易被中止，并且都保持在未婚状态。无效的状态是一个已婚，另一个未婚。事务能保证结果永远不会处于无效状态。

ADO.NET可以通过调用SqlConnection的BeginTransaction方法来启动事务。事务总是与一个连接相关联，不能通过多个连接创建事务。方法BeginTransaction会返回一个SqlTransaction，后者又需要与在同一事务下运行的命令一起使用（代码文件TransactionSamples / Program.cs）：

public static void TransactionSample()
{
  using (var connection = new SqlConnection(GetConnectionString()))
  {
    await connection.OpenAsync();
    SqlTransaction tx = connection.BeginTransaction();
    // etc.
  }
}

注意，实际上可以创建跨多个连接的事务。这样，Windows操作系统将使用分布式事务处理协调器。可以使用TransactionScope类创建分布式事务。然而，这个类是完整的.NET框架中的一个功能，并没有整合.NET Core中，因此它不是这本书的内容。如果您需要了解有关TransactionScope的更多信息，请参阅本书的前一版本，例如《Professional 5 and.NET 4.5.1》。

示例代码在Sales.CreditCard表中创建一条记录。使用SQL子句INSERT INTO添加一条记录。 CreditCard表定义了自增标识符，第二个SQL语句SELECT SCOPE_IDENTITY（）返回已创建的标识符。实例化SqlCommand对象后，通过设置Connection属性来分配连接，并设置Transaction属性来分配事务。使用ADO.NET事务，不能将事务分配给使用不同连接的命令。但是可以使用同一个与事务无关的连接创建命令：

public static void TransactionSample()
{
  // etc.
    try
    {
      string sql ="INSERT INTO Sales.CreditCard" + "(CardType, CardNumber, ExpMonth, ExpYear)" + "VALUES (@CardType, @CardNumber, @ExpMonth, @ExpYear);" + "SELECT SCOPE_IDENTITY()";

      var command = new SqlCommand();
      command.CommandText = sql;
      command.Connection = connection;
      command.Transaction = tx;
  // etc.
}

在定义参数并填充值之后，调用ExecuteScalarAsync方法来执行命令。本次ExecuteScalarAsync方法与INSERT INTO子句一起使用，因为完整的SQL语句返回单个结果后结束：创建的标识符从SELECT SCOPE_IDENTITY（）返回。如果在WriteLine方法之后设置断点并检查数据库中的结果，则不会在数据库中看到新记录，尽管已返回创建的标识符，其中的原因是事务尚未提交：

public static void TransactionSample()
{
  // etc.

      var p1 = new SqlParameter("CardType", SqlDbType.NVarChar, 50);
      var p2 = new SqlParameter("CardNumber", SqlDbType.NVarChar, 25);
      var p3 = new SqlParameter("ExpMonth", SqlDbType.TinyInt);
      var p4 = new SqlParameter("ExpYear", SqlDbType.SmallInt);
      command.Parameters.AddRange(new SqlParameter[] { p1, p2, p3, p4 });
      command.Parameters["CardType"].Value ="MegaWoosh";
      command.Parameters["CardNumber"].Value ="08154711123";
      command.Parameters["ExpMonth"].Value = 4;
      command.Parameters["ExpYear"].Value = 2019;
      object id = await command.ExecuteScalarAsync();
      WriteLine($"record added with id: {id}");
      // etc.
}

现在可以在同一事务中创建另一个记录。示例代码使用同一个连接和事务仍然关联的命令，只是在再次调用ExecuteScalarAsync之前，命令参数值已被更改。还可以创建一个新的SqlCommand对象访问同一个数据库中不同的表。调用SqlTransaction对象的Commit方法提交该事务。提交后，可以在数据库中看到新的记录：

public static void TransactionSample()
{
      // etc.
      command.Parameters["CardType"].Value ="NeverLimits";
      command.Parameters["CardNumber"].Value ="987654321011";
      command.Parameters["ExpMonth"].Value = 12;
      command.Parameters["ExpYear"].Value = 2025;

      id = await command.ExecuteScalarAsync();
      WriteLine($"record added with id: {id}");

      // throw new Exception("abort the transaction");


      tx.Commit();
    }
    // etc.
}

如果发生错误，Rollback方法会撤消同一事务中的所有SQL命令，并且状态被重置为在事务开始之前。可以通过在提交之前取消注释异常简单地模拟回滚：

public static void TransactionSample()
{
    // etc. 
    catch (Exception ex)
    {
      WriteLine($"error {ex.Message}, rolling back");
      tx.Rollback();
    }
  }
}

如果在调试模式下运行程序时断点活动时间过长，事务将被中止，原因是事务超时。事务并不意味着事务处于活动状态时允许用户输入。增加用户输入的事务超时时长也没有用，因为事务活动会导致数据库内加锁。根据读取和写入的记录，可能发生行锁，页锁或表锁。创建事务时可以设置用隔离级别来决定锁定，从而影响数据库的性能。然而，这也影响事务的ACID属性 - 例如，不是一切都是孤立的。
事务的隔离级别默认为ReadCommitted。可以设置的不同选项如下表所示。

隔离级别	说明
ReadUncommitted	事务不彼此隔离。使用此级别时不会等待其他事务锁定的记录。未提交的数据可以从其他事务读取 - 脏读。此级别通常只用于读取记录，如果读取临时更改（例如报告）也无关紧要。
ReadCommitted	等待其他事务写锁定的记录。这种情况下脏读不会发生。此级别会为读取的当前记录设置读锁，并为正在写入的记录设置写锁，直到事务完成。在读取一系列的记录期间，读取新记录之前会解锁之前加了读锁的记录。这就是不可重复读取可能发生的原因。
RepeatableRead	保留读取的记录的锁定，直到事务完成。这样避免了不可重复读取的问题。但幻读(Phantom Reads)仍然可以发生。
Serializable	保持范围锁定。事务正在运行时，不能添加属于该事务读取范围数据的新记录。
Snapshot	使用此级别从实际数据完成快照。此级别减少了复制修改行时的锁定。这样其他事务仍然可以读取旧数据而无需等待释放锁。
Unspecified	意味着提供程序使用的隔离级别无法识别IsolationLevel枚举定义。
Chaos	此级别与ReadUncommitted类似，但除了执行ReadUncommitted值的操作之外，Chaos不会锁定被更新的记录。

下表总结了由于设置最常用的事务隔离级别而可能发生的问题。

隔离级别	脏读	不可重复读	幻读
ReadUncommitted	Y	Y	Y
ReadCommitted	N	Y	Y
RepeatableRead	N	N	Y
Serializable	Y	Y	Y

总结

本章中可以了解ADO.NET的核心基础。首先接触了SqlConnection对象打开SQL Server的连接。了解了如何从配置文件检索连接字符串。
本章解释了如何正确使用连接，尽早关闭它们从而节省宝贵的资源。所有连接类实现了IDisposable接口，对象可以在using语句中时调用，那么有一件事情可以从本章中删除，就是尽早关闭数据库连接的重要性(译者：using 结束后会自动释放资源)。
使用命令传递参数，获取单个返回值，并使用SqlDataReader检索记录。还了解了如何使用SqlCommand对象调用存储过程。
类似于框架的其他部分，其中的处理可能需要一些时间，ADO.NET实现了基于任务的异步模式。还了解了如何使用ADO.NET创建和使用事务。
下一章是关于ADO.NET实体框架，它通过数据库和对象层次关系之间的映射来提供数据访问的对象模型，并在访问关系数据库时在后台使用ADO.NET类。

posted @ 2017-02-08 10:03 沐汐Vicky 阅读(3519) 评论(0) 编辑收藏举报

刷新页面返回顶部

沐汐 vicky

我想改变这个世界，但我没有源代码