正确执行数据库操作
本文讨论了成功执行数据库操作的一些基本要求，例如错误处理、参数和事务的使用。
引言
本文旨在展示在创建访问和修改数据库的代码时应始终考虑的五件事。我使用了 WPF 作为用户界面技术，但有关数据库的代码原则与使用 ASP.NET、WPF、Windows Forms 等技术时是相同的。
本文涵盖的主题包括：
版本 0：初始代码
让我们直接看代码。代码示例很简单。基于用户输入，代码将在数据库中添加一个订单和三个订单项（在循环中）。用户界面如下所示：
要使用该应用程序，您需要提供指向 SQL Server 数据库的正确连接字符串。在很多情况下，只需更改服务器名称、实例名称和数据库名称就足够了。如果您使用 SQL Server 身份验证或想修改连接字符串的其他属性，请参阅 SqlConnection.ConnectionString 属性。
在定义了正确的连接字符串后，可以使用“创建表”按钮创建代码运行所需的表。之后，您可以将值输入到订单数据字段中，并选择要使用的操作版本。通过按下“执行语句”按钮，将运行选定的代码版本。
在第二个选项卡上，您可以浏览表中的数据，并在需要时截断表。
那么代码是什么样的呢？
namespace ProperlyExecutingSqlCommands_CSharp {
/// <summary>
/// Class to execute version 0
/// </summary>
internal class Version0 : VersionBase, IVersion {
// used to hold the once opened connection
private static System.Data.SqlClient.SqlConnection connection;
/// <summary>
/// Add the order to the database
/// </summary>
/// <param name="connectionString">Connection string to use</param>
/// <param name="orderNumber">Order number as text from the user interface</param>
/// <param name="price">Price as text from the user interface</param>
/// <param name="product">Product from the user interface</param>
/// <param name="orderDate">Order date as text from the user interface</param>
/// <param name="generateError">Is an errorneous statement executed in the end</param>
/// <param name="errorText">Explanation for an error (if any)</param>
/// <returns>True if succesful</returns>
public override bool AddOrder(string connectionString, string orderNumber, string price
, string product, string orderDate, bool generateError
, out string errorText) {
string sql;
System.Data.SqlClient.SqlCommand command;
errorText = null;
// Check if the connection is created and if not create and open
if (connection == null) {
connection = new System.Data.SqlClient.SqlConnection(connectionString);
connection.Open();
}
sql = "INSERT INTO PurchaseOrder (OrderNumber, OrderDate) VALUES ("
+ orderNumber + ",'" + orderDate + "')";
command = new System.Data.SqlClient.SqlCommand(sql, connection);
command.ExecuteNonQuery();
for (int counter = 1; counter <= 3; counter++) {
sql = "INSERT INTO OrderItem (OrderNumber, OrderRow, Product, Price) VALUES ("
+ orderNumber + "," + counter + ", '" + product + "'," + price + ")";
command = new System.Data.SqlClient.SqlCommand(sql, connection);
command.ExecuteNonQuery();
}
if (generateError) {
this.GenerateError(connection);
}
return true;
}
/// <summary>
/// Returns the version number
/// </summary>
public override int VersionNumber {
get {
return 0;
}
}
/// <summary>
/// Returns an explanation for a version
/// </summary>
public override string Explanation {
get {
return
@"This is the basic version of the execution. The code will insert an order and three order items in a loop based on the user input in the text boxes.
If ""Cause an SQL execution error in the end"" is selected then an incorrect statement is executed after adding the orders and order item. This is for testing error handling.";
}
}
}
}
'*** Class to execute version 0
Public Class Version0
Inherits VersionBase
Implements IVersion
' used to hold the once opened connection
Private Shared connection As System.Data.SqlClient.SqlConnection
'*** Add the order to the database
Public Overrides Function AddOrder(connectionString As String, orderNumber As String _
, price As String, product As String, orderDate As String _
, generateError As Boolean, ByRef errorText As String) As Boolean _
Implements IVersion.AddOrder
Dim sql As String
Dim command As System.Data.SqlClient.SqlCommand
errorText = Nothing
' Check if the connection is created and if not create and open
If (connection Is Nothing) Then
connection = New System.Data.SqlClient.SqlConnection(connectionString)
connection.Open()
End If
sql = "INSERT INTO PurchaseOrder (OrderNumber, OrderDate) VALUES (" _
& orderNumber & ",'" & orderDate & "')"
command = New System.Data.SqlClient.SqlCommand(sql, connection)
command.ExecuteNonQuery()
For counter As Integer = 1 To 3 Step 1
sql = "INSERT INTO OrderItem (OrderNumber, OrderRow, Product, Price) VALUES (" _
& orderNumber & "," & counter & ", '" & product & "'," & price & ")"
command = New System.Data.SqlClient.SqlCommand(sql, connection)
command.ExecuteNonQuery()
Next counter
If (generateError) Then
Me.GenerateError(connection)
End If
Return True
End Function
'*** Returns the version number
Public Overrides ReadOnly Property VersionNumber() As Integer _
Implements IVersion.VersionNumber
Get
Return 0
End Get
End Property
'*** Returns an explanation for a version
Public Overrides ReadOnly Property Explanation() As String _
Implements IVersion.Explanation
Get
Return _
"This is the basic version of the execution. The code will insert an order and three order items in a loop based on the user input in the text boxes. " & vbCrLf & _
"" & vbCrLf & _
"If ""Cause an SQL execution error in the end"" is selected then an incorrect statement is executed after adding the orders and order item. This is for testing error handling."
End Get
End Property
End Class
AddOrder 方法以文本形式接收来自用户界面的输入，就像最初输入的那样。在此初始版本中，连接是一个静态变量，如果尚未打开，则会打开一次。检查连接后，会创建一个 SqlCommand 来添加 PurchaseOrder 行。添加订单后，将使用新的 SqlCommands 插入三行 OrderItem。
您可能已经注意到，此代码存在大量问题。然而，不幸的是，现实世界中确实存在类似的错误代码。关键在于理解问题所在以及如何解决它们。我们将在每个版本中进行讲解。
AddOrder 末尾的错误生成部分主要用于测试更高版本。版本 0 中最不可能需要错误生成，因为如果您能首次顺利运行它，就已经很幸运了。:)
版本 1：添加错误处理
从用户的角度来看，版本 0 最令人讨厌的地方在于，如果发生错误，程序就会崩溃。所有修改都会丢失，您需要从头开始。但这不仅仅对用户而言。作为程序员，您会希望获得有关发生的错误的更多信息以便修复它。
如果我定义了正确的连接字符串，然后按“执行语句”而不提供任何数据，我会得到以下屏幕，程序会崩溃。
因此，第一个修改是添加适当的错误处理，换句话说，使用 try…catch。代码现在看起来是这样的：
public override bool AddOrder(string connectionString, string orderNumber, string price
, string product, string orderDate, bool generateError
, out string errorText) {
string sql;
System.Data.SqlClient.SqlCommand command;
bool returnValue = false;
errorText = null;
try {
// Check if the connection is created and if not create and open
if (connection == null) {
connection = new System.Data.SqlClient.SqlConnection(connectionString);
connection.Open();
}
sql = "INSERT INTO PurchaseOrder (OrderNumber, OrderDate) VALUES ("
+ orderNumber + ",'" + orderDate + "')";
command = new System.Data.SqlClient.SqlCommand(sql, connection);
command.ExecuteNonQuery();
for (int counter = 1; counter <= 3; counter++) {
sql = "INSERT INTO OrderItem (OrderNumber, OrderRow, Product, Price) VALUES ("
+ orderNumber + "," + counter + ", '" + product + "'," + price + ")";
command = new System.Data.SqlClient.SqlCommand(sql, connection);
command.ExecuteNonQuery();
}
if (generateError) {
this.GenerateError(connection);
}
returnValue = true;
} catch (System.Data.SqlClient.SqlException sqlException) {
errorText = string.Format("Error {0} in line {1}:\n{2}",
sqlException.Number,
sqlException.LineNumber,
sqlException.Message);
} catch (System.Exception exception) {
errorText = exception.Message;
}
return returnValue;
}
Public Overrides Function AddOrder(connectionString As String, orderNumber As String _
, price As String, product As String, orderDate As String _
, generateError As Boolean, ByRef errorText As String) As Boolean _
Implements IVersion.AddOrder
Dim sql As String
Dim command As System.Data.SqlClient.SqlCommand
Dim returnValue As Boolean = False
errorText = Nothing
Try
' Check if the connection is created and if not create and open
If (connection Is Nothing) Then
connection = New System.Data.SqlClient.SqlConnection(connectionString)
connection.Open()
End If
sql = "INSERT INTO PurchaseOrder (OrderNumber, OrderDate) VALUES (" _
& orderNumber & ",'" & orderDate & "')"
command = New System.Data.SqlClient.SqlCommand(sql, connection)
command.ExecuteNonQuery()
For counter As Integer = 1 To 3 Step 1
sql = "INSERT INTO OrderItem (OrderNumber, OrderRow, Product, Price) VALUES (" _
& orderNumber & "," & counter & ", '" & product & "'," & price & ")"
command = New System.Data.SqlClient.SqlCommand(sql, connection)
command.ExecuteNonQuery()
Next counter
If (generateError) Then
Me.GenerateError(connection)
End If
returnValue = True
Catch sqlException As System.Data.SqlClient.SqlException
errorText = String.Format("Error {0} in line {1}:\n{2}", _
sqlException.Number, _
sqlException.LineNumber, _
sqlException.Message)
Catch exception As System.Exception
errorText = exception.Message
End Try
Return returnValue
End Function
代码基本相同，但现在所有操作都在 try 块内完成。如果抛出异常，catch 块会处理该情况。我将 SqlExceptions 与其他异常分开，因为在 SqlException 中，Number 和 LineNumber 属性包含额外的信息，而其他异常没有这些属性。错误和行号有助于您定位问题，尤其是在处理大型 SQL 语句时。
异常信息被添加到 errorText 参数中并返回给调用者。该方法的返回值为 Boolean，以便调用方可以轻松地找出方法是否成功，并向用户显示适当的消息。
如果我现在再次按下“执行语句”按钮而不提供任何数据，我会获得更多信息，并且程序会继续运行。
从用户的角度来看，这个错误文本没什么意义，但对于程序员来说，这是修复程序至关重要的信息。因此，您可能希望将错误写入日志文件、Windows 事件日志或类似文件中，而不是仅仅显示错误文本。
版本 2：关闭并释放数据库对象
在之前的版本中，连接只打开一次，并且会一直保持打开状态，直到应用程序关闭。另外请注意，SqlCommands 已创建，但并未在任何地方得到妥善处理。SqlConnection 和 SqlCommand 都使用非托管资源，因此应尽快释放它们以节省资源。
当然，您可以编写代码在不再需要对象时处理它，但这比将其放在 using 块中要容易得多。对于 SqlCommand 对象，在此版本中，它们仅在 using 块中定义。这样，即使发生异常，对象也会在所有情况下都被处理。
SqlConnection 也被包装在 using 块中，但这需要更多的讨论。在初始版本中，一次打开的连接会存储到应用程序的生命周期，因此没有理由重新打开连接。现在，连接放在 using 块中，当代码退出该块时，连接会被关闭并处理。
那么性能如何呢？现在每次添加订单时都会打开和关闭连接。这会不会严重影响应用程序的性能？您说的对，可以说是。
诚然，打开连接是一个缓慢的操作，需要与数据库服务器进行几次往返。但是，SQL Server ADO NET 提供程序提供了连接池来解决此问题。当池化启用且应用程序请求打开的连接时，它将从池中返回给调用应用程序。如果池中没有打开的连接且 Max pool size 尚未达到，则会创建一个新连接并返回。
当应用程序关闭连接时，它实际上并没有关闭，而是被重置并返回到池中，等待下一个请求。此行为确保不断重新打开连接不会降低性能。
现在代码看起来是这样的：
public override bool AddOrder(string connectionString, string orderNumber, string price
, string product, string orderDate, bool generateError
, out string errorText) {
string sql;
bool returnValue = false;
errorText = null;
using (System.Data.SqlClient.SqlConnection connection
= new System.Data.SqlClient.SqlConnection()) {
try {
connection.ConnectionString = connectionString;
connection.Open();
sql = "INSERT INTO PurchaseOrder (OrderNumber, OrderDate) VALUES ("
+ orderNumber + ",'" + orderDate + "')";
using (System.Data.SqlClient.SqlCommand command
= new System.Data.SqlClient.SqlCommand(sql, connection)) {
command.ExecuteNonQuery();
}
for (int counter = 1; counter <= 3; counter++) {
sql = "INSERT INTO OrderItem (OrderNumber, OrderRow, Product, Price) VALUES ("
+ orderNumber + "," + counter + ", '" + product + "'," + price + ")";
using (System.Data.SqlClient.SqlCommand command
= new System.Data.SqlClient.SqlCommand(sql, connection)) {
command.ExecuteNonQuery();
}
}
if (generateError) {
this.GenerateError(connection);
}
returnValue = true;
} catch (System.Data.SqlClient.SqlException sqlException) {
errorText = string.Format("Error {0} in line {1}:\n{2}",
sqlException.Number,
sqlException.LineNumber,
sqlException.Message);
} catch (System.Exception exception) {
errorText = exception.Message;
}
if (connection.State == System.Data.ConnectionState.Open) {
connection.Close();
}
}
return returnValue;
}
Public Overrides Function AddOrder(connectionString As String, orderNumber As String _
, price As String, product As String, orderDate As String _
, generateError As Boolean, ByRef errorText As String) As Boolean _
Implements IVersion.AddOrder
Dim sql As String
Dim returnValue As Boolean = False
errorText = Nothing
Using connection As System.Data.SqlClient.SqlConnection _
= New System.Data.SqlClient.SqlConnection()
Try
connection.ConnectionString = connectionString
connection.Open()
sql = "INSERT INTO PurchaseOrder (OrderNumber, OrderDate) VALUES (" _
& orderNumber & ",'" & orderDate & "')"
Using command As System.Data.SqlClient.SqlCommand _
= New System.Data.SqlClient.SqlCommand(sql, connection)
command.ExecuteNonQuery()
End Using
For counter As Integer = 1 To 3 Step 1
sql = "INSERT INTO OrderItem (OrderNumber, OrderRow, Product, Price) VALUES (" _
& orderNumber & "," & counter & ", '" & product & "'," & price & ")"
Using command As System.Data.SqlClient.SqlCommand _
= New System.Data.SqlClient.SqlCommand(sql, connection)
command.ExecuteNonQuery()
End Using
Next counter
If (generateError) Then
Me.GenerateError(connection)
End If
returnValue = True
Catch sqlException As System.Data.SqlClient.SqlException
errorText = String.Format("Error {0} in line {1}:\n{2}", _
sqlException.Number, _
sqlException.LineNumber, _
sqlException.Message)
Catch exception As System.Exception
errorText = exception.Message
End Try
If (connection.State = System.Data.ConnectionState.Open) Then
connection.Close()
End If
End Using
Return returnValue
End Function
版本 3：始终使用参数
这是一个我怎么强调都不为过的主题。
- 使用参数。
- 始终使用参数。
- 切勿将用户输入直接添加到 SQL 语句中。
SQL 注入
为什么这如此重要？原因有几个，但最重要的是 SQL 注入。与到目前为止的所有版本一样，用户输入被直接连接到 SQL 语句。这意味着用户直接影响 SQL 语句在语法上的外观。这也意味着用户可以通过注入额外的命令来影响 SQL 语句，例如。
SQL 注入攻击允许攻击者伪造身份、篡改现有数据、引起反悔问题（例如取消事务或更改余额）、允许完全披露系统上的所有数据、销毁数据或使其不可用，以及成为数据库服务器的管理员。
让我们测试一下。如果我在表单中输入以下数据：
- 订单号：
1 - 订单日期：
8/18/2015'); DROP TABLE OrderItem; -- - 产品：
A - 价格：
1
然后点击“执行语句”按钮，我会收到以下错误消息：
OrderItem 表已不存在。添加订单时实际执行的语句是：
INSERT INTO PurchaseOrder (OrderNumber, OrderDate) VALUES (1,'8/18/2015'); DROP TABLE OrderItem; --')
因此，用户通过将命令添加到输入字段之一就可以删除数据库对象。您可以再次按下“创建表”按钮来重新创建丢失的表。
如果我使用参数化版本重复测试，我会收到一个错误“Failed to convert parameter value from a String to a DateTime.”（从字符串到日期时间转换参数值失败），因为现在无法将用户输入转换为日期。当值作为参数提供时，用户就无法更改语法。这样您就可以免受 SQL 注入的侵害，也可以避免值中的无意错误。
为了让程序表现得更健壮，我在方法开头添加了参数值检查。每个参数都转换为正确的基础数据类型，如果转换失败，用户将收到通知。
类型转换
使用参数的另一个好处是转换。以前，我需要以数据库能够理解的格式将所有值输入到文本字段中。由于值直接添加到 SQL 语句中，并且语句原样发送到数据库服务器，因此我需要使用数据库服务器期望的日期格式和小数分隔符。
作为应用程序的用户，我怎么知道正确的格式是什么？或者我是否应该关心数据库服务器的格式，因为我想使用我熟悉的格式？
几个例子
- 日期格式需要是
mm/dd/yyyy才能使插入成功。如果使用芬兰语格式（dd.mm.yyyy），由于隐式转换错误，语句将失败。 - 小数也存在类似的问题。当值连接到 SQL 语句时，我必须使用点 (
.) 作为小数点分隔符。同样，如果我使用芬兰语格式和逗号 (,) 作为小数点分隔符，SQL 语句将失败，因为它比列列表多了额外的项。例如，1,23将被解释为1作为一列的值，23作为另一列的值。 - 第三件事是
NULL值。无论通过何种方式向语句提供值，NULL值都必须单独考虑。这是因为 C# 中的null（VB 中的Nothing）与 SQL 中的NULL不同。如果参数的值为null，提供程序会认为未提供该值并抛出异常。要将NULL值正确添加到列中，需要为参数设置System.DBNull.Value。使用参数，这更简单，因为我们只是设置值，而不改变语句的语法。例如，对于字符串值，您需要用引号括起该值，除非该值被设置为NULL。 - 另外，对于字符串，您不必担心转义特殊字符。例如，如果文本包含引号，将其设置为参数值是完全可以的。如果您将其连接到语句中，则需要转义它。
现在代码看起来是这样的：
public override bool AddOrder(string connectionString, string orderNumber, string price
, string product, string orderDate, bool generateError
, out string errorText) {
string sql;
bool returnValue = false;
int orderNumber_validated;
decimal price_intermediate;
decimal? price_validated = null;
System.DateTime orderDate_validated;
errorText = null;
// Data validations
if (!int.TryParse(orderNumber, out orderNumber_validated)) {
errorText = "Invalid order number";
return false;
}
if (!string.IsNullOrEmpty(price)) {
if (!decimal.TryParse(price, out price_intermediate)) {
errorText = "Invalid price";
return false;
}
price_validated = price_intermediate;
}
if (!System.DateTime.TryParse(orderDate, out orderDate_validated)) {
errorText = "Invalid order date";
return false;
}
// Insert the data
using (System.Data.SqlClient.SqlConnection connection
= new System.Data.SqlClient.SqlConnection()) {
try {
connection.ConnectionString = connectionString;
connection.Open();
sql = "INSERT INTO PurchaseOrder (OrderNumber, OrderDate) VALUES (@orderNumber, @orderDate)";
using (System.Data.SqlClient.SqlCommand command
= new System.Data.SqlClient.SqlCommand(sql, connection)) {
command.Parameters.AddWithValue("@orderNumber", orderNumber_validated);
command.Parameters.AddWithValue("@orderDate", orderDate_validated);
command.ExecuteNonQuery();
}
for (int counter = 1; counter <= 3; counter++) {
sql = "INSERT INTO OrderItem (OrderNumber, OrderRow, Product, Price) VALUES (@orderNumber, @orderRow, @product, @price)";
using (System.Data.SqlClient.SqlCommand command
= new System.Data.SqlClient.SqlCommand(sql, connection)) {
command.Parameters.Add(new System.Data.SqlClient.SqlParameter("@orderNumber",
System.Data.SqlDbType.Int));
command.Parameters.Add(new System.Data.SqlClient.SqlParameter("@orderRow",
System.Data.SqlDbType.Int));
command.Parameters.Add(new System.Data.SqlClient.SqlParameter("@product",
System.Data.SqlDbType.VarChar, 100));
command.Parameters.Add(new System.Data.SqlClient.SqlParameter("@price",
System.Data.SqlDbType.Decimal));
command.Parameters["@orderNumber"].Value = orderNumber_validated;
command.Parameters["@orderRow"].Value = counter;
command.Parameters["@product"].Value = product;
command.Parameters["@price"].Value = price_validated.HasValue
? (object)price_validated
: System.DBNull.Value;
command.ExecuteNonQuery();
}
}
if (generateError) {
this.GenerateError(connection);
}
returnValue = true;
} catch (System.Data.SqlClient.SqlException sqlException) {
errorText = string.Format("Error {0} in line {1}:\n{2}",
sqlException.Number,
sqlException.LineNumber,
sqlException.Message);
} catch (System.Exception exception) {
errorText = exception.Message;
}
if (connection.State == System.Data.ConnectionState.Open) {
connection.Close();
}
}
return returnValue;
}
Public Overrides Function AddOrder(connectionString As String, orderNumber As String _
, price As String, product As String, orderDate As String _
, generateError As Boolean, ByRef errorText As String) As Boolean _
Implements IVersion.AddOrder
Dim sql As String
Dim returnValue As Boolean = False
Dim orderNumber_validated As Integer
Dim price_intermediate As Decimal
Dim price_validated As Decimal? = Nothing
Dim orderDate_validated As System.DateTime
errorText = Nothing
' Data validations
If (Not Integer.TryParse(orderNumber, orderNumber_validated)) Then
errorText = "Invalid order number"
Return False
End If
If (Not String.IsNullOrEmpty(price)) Then
If (Not Decimal.TryParse(price, price_intermediate)) Then
errorText = "Invalid price"
Return False
End If
price_validated = price_intermediate
End If
If (Not System.DateTime.TryParse(orderDate, orderDate_validated)) Then
errorText = "Invalid order date"
Return False
End If
' Insert the data
Using connection As System.Data.SqlClient.SqlConnection _
= New System.Data.SqlClient.SqlConnection()
Try
connection.ConnectionString = connectionString
connection.Open()
sql = "INSERT INTO PurchaseOrder (OrderNumber, OrderDate) VALUES (@orderNumber, @orderDate)"
Using command As System.Data.SqlClient.SqlCommand _
= New System.Data.SqlClient.SqlCommand(sql, connection)
command.Parameters.AddWithValue("@orderNumber", orderNumber_validated)
command.Parameters.AddWithValue("@orderDate", orderDate_validated)
command.ExecuteNonQuery()
End Using
For counter As Integer = 1 To 3 Step 1
sql = "INSERT INTO OrderItem (OrderNumber, OrderRow, Product, Price) VALUES (@orderNumber, @orderRow, @product, @price)"
Using command As System.Data.SqlClient.SqlCommand _
= New System.Data.SqlClient.SqlCommand(sql, connection)
command.Parameters.Add(New System.Data.SqlClient.SqlParameter("@orderNumber",
System.Data.SqlDbType.Int))
command.Parameters.Add(New System.Data.SqlClient.SqlParameter("@orderRow",
System.Data.SqlDbType.Int))
command.Parameters.Add(New System.Data.SqlClient.SqlParameter("@product",
System.Data.SqlDbType.VarChar, 100))
command.Parameters.Add(New System.Data.SqlClient.SqlParameter("@price",
System.Data.SqlDbType.Decimal))
command.Parameters("@orderNumber").Value = orderNumber_validated
command.Parameters("@orderRow").Value = counter
command.Parameters("@product").Value = product
command.Parameters("@price").Value = If(price_validated.HasValue,
price_validated, System.DBNull.Value)
command.ExecuteNonQuery()
End Using
Next counter
If (generateError) Then
Me.GenerateError(connection)
End If
returnValue = True
Catch sqlException As System.Data.SqlClient.SqlException
errorText = String.Format("Error {0} in line {1}:\n{2}", _
sqlException.Number, _
sqlException.LineNumber, _
sqlException.Message)
Catch exception As System.Exception
errorText = exception.Message
End Try
If (connection.State = System.Data.ConnectionState.Open) Then
connection.Close()
End If
End Using
Return returnValue
End Function
如您所见，我使用了两种提供参数值的方法。对于 PurchaseOrder 行，我使用了 AddWithValue 方法，这是设置参数值最简单的方法。对于 OrderItem，我单独创建了参数，然后设置了值。下一版本将更详细地讨论这一点。
版本 4：对重复语句使用 Prepare
下一个版本更改处理的情况是，相同的语句重复执行多次，但值不同。例如，考虑用户可以在 GridView 中修改多行的情况。当这些修改更新到数据库时，您需要为每个修改过的行重复相同的更新。在此示例中，在一行订单中，循环中添加了三个项目。
该版本如下所示：
public override bool AddOrder(string connectionString, string orderNumber, string price
, string product, string orderDate, bool generateError
, out string errorText) {
string sql;
bool returnValue = false;
int orderNumber_validated;
decimal price_intermediate;
decimal? price_validated = null;
System.DateTime orderDate_validated;
errorText = null;
// Data validations
if (!int.TryParse(orderNumber, out orderNumber_validated)) {
errorText = "Invalid order number";
return false;
}
if (!string.IsNullOrEmpty(price)) {
if (!decimal.TryParse(price, out price_intermediate)) {
errorText = "Invalid price";
return false;
}
price_validated = price_intermediate;
}
if (!System.DateTime.TryParse(orderDate, out orderDate_validated)) {
errorText = "Invalid order date";
return false;
}
// Insert the data
using (System.Data.SqlClient.SqlConnection connection
= new System.Data.SqlClient.SqlConnection()) {
try {
connection.ConnectionString = connectionString;
connection.Open();
sql = "INSERT INTO PurchaseOrder (OrderNumber, OrderDate) VALUES (@orderNumber, @orderDate)";
using (System.Data.SqlClient.SqlCommand command
= new System.Data.SqlClient.SqlCommand(sql, connection)) {
command.Parameters.AddWithValue("@orderNumber", orderNumber_validated);
command.Parameters.AddWithValue("@orderDate", orderDate_validated);
command.ExecuteNonQuery();
}
sql = "INSERT INTO OrderItem (OrderNumber, OrderRow, Product, Price) VALUES (@orderNumber, @orderRow, @product, @price)";
using (System.Data.SqlClient.SqlCommand command
= new System.Data.SqlClient.SqlCommand(sql, connection)) {
command.Parameters.Add(new System.Data.SqlClient.SqlParameter("@orderNumber",
System.Data.SqlDbType.Int));
command.Parameters.Add(new System.Data.SqlClient.SqlParameter("@orderRow",
System.Data.SqlDbType.Int));
command.Parameters.Add(new System.Data.SqlClient.SqlParameter("@product",
System.Data.SqlDbType.VarChar, 100));
command.Parameters.Add(new System.Data.SqlClient.SqlParameter("@price",
System.Data.SqlDbType.Decimal, 10));
command.Parameters["@price"].Precision = 10;
command.Parameters["@price"].Scale = 2;
command.Prepare();
for (int counter = 1; counter <= 3; counter++) {
command.Parameters["@orderNumber"].Value = orderNumber_validated;
command.Parameters["@orderRow"].Value = counter;
command.Parameters["@product"].Value = product;
command.Parameters["@price"].Value = price_validated.HasValue
? (object)price_validated
: System.DBNull.Value;
command.ExecuteNonQuery();
}
}
if (generateError) {
this.GenerateError(connection);
}
returnValue = true;
} catch (System.Data.SqlClient.SqlException sqlException) {
errorText = string.Format("Error {0} in line {1}:\n{2}",
sqlException.Number,
sqlException.LineNumber,
sqlException.Message);
} catch (System.Exception exception) {
errorText = exception.Message;
}
if (connection.State == System.Data.ConnectionState.Open) {
connection.Close();
}
}
return returnValue;
}
Public Overrides Function AddOrder(connectionString As String, orderNumber As String _
, price As String, product As String, orderDate As String _
, generateError As Boolean, ByRef errorText As String) As Boolean _
Implements IVersion.AddOrder
Dim sql As String
Dim returnValue As Boolean = False
Dim orderNumber_validated As Integer
Dim price_intermediate As Decimal
Dim price_validated As Decimal? = Nothing
Dim orderDate_validated As System.DateTime
errorText = Nothing
' Data validations
If (Not Integer.TryParse(orderNumber, orderNumber_validated)) Then
errorText = "Invalid order number"
Return False
End If
If (Not String.IsNullOrEmpty(price)) Then
If (Not Decimal.TryParse(price, price_intermediate)) Then
errorText = "Invalid price"
Return False
End If
price_validated = price_intermediate
End If
If (Not System.DateTime.TryParse(orderDate, orderDate_validated)) Then
errorText = "Invalid order date"
Return False
End If
' Insert the data
Using connection As System.Data.SqlClient.SqlConnection _
= New System.Data.SqlClient.SqlConnection()
Try
connection.ConnectionString = connectionString
connection.Open()
sql = "INSERT INTO PurchaseOrder (OrderNumber, OrderDate) VALUES (@orderNumber, @orderDate)"
Using command As System.Data.SqlClient.SqlCommand _
= New System.Data.SqlClient.SqlCommand(sql, connection)
command.Parameters.AddWithValue("@orderNumber", orderNumber_validated)
command.Parameters.AddWithValue("@orderDate", orderDate_validated)
command.ExecuteNonQuery()
End Using
sql = "INSERT INTO OrderItem (OrderNumber, OrderRow, Product, Price) VALUES (@orderNumber, @orderRow, @product, @price)"
Using command As System.Data.SqlClient.SqlCommand _
= New System.Data.SqlClient.SqlCommand(sql, connection)
command.Parameters.Add(New System.Data.SqlClient.SqlParameter("@orderNumber",
System.Data.SqlDbType.Int))
command.Parameters.Add(New System.Data.SqlClient.SqlParameter("@orderRow",
System.Data.SqlDbType.Int))
command.Parameters.Add(New System.Data.SqlClient.SqlParameter("@product",
System.Data.SqlDbType.VarChar, 100))
command.Parameters.Add(New System.Data.SqlClient.SqlParameter("@price",
System.Data.SqlDbType.Decimal, 10))
command.Parameters("@price").Precision = 10
command.Parameters("@price").Scale = 2
command.Prepare()
For counter As Integer = 1 To 3 Step 1
command.Parameters("@orderNumber").Value = orderNumber_validated
command.Parameters("@orderRow").Value = counter
command.Parameters("@product").Value = product
command.Parameters("@price").Value = If(price_validated.HasValue, _
price_validated, System.DBNull.Value)
command.ExecuteNonQuery()
Next counter
End Using
If (generateError) Then
Me.GenerateError(connection)
End If
returnValue = True
Catch sqlException As System.Data.SqlClient.SqlException
errorText = String.Format("Error {0} in line {1}:\n{2}", _
sqlException.Number, _
sqlException.LineNumber, _
sqlException.Message)
Catch exception As System.Exception
errorText = exception.Message
End Try
If (connection.State = System.Data.ConnectionState.Open) Then
connection.Close()
End If
End Using
Return returnValue
End Function
如您所见，在进入循环之前，所有参数都已定义，并且调用了 Prepare 方法。这不会执行任何操作，因此不会在数据库中进行任何修改，但它会创建该语句的执行计划，并在内部将语句句柄返回给提供程序。现在，当 ExecuteNonQuery 被重复调用时，所有执行都使用相同的语句句柄。我们只需要在每次迭代中更改参数的值。
这似乎是一个很小的改动，从代码的角度来看确实如此。但从数据库的角度来看，这大大减轻了执行负担。每次执行新的、未准备好的语句时，数据库都需要执行以下任务：
- 检查语句的语法
- 解析对象和列
- 检查权限，调用者是否有适当的操作权限
- 运行多个排列以决定最佳执行计划
- 编译执行计划
- 设置变量值
- 执行语句
当重复语句被准备好并且所有执行都使用相同的语句句柄时，后续执行可以省略前五个步骤。所以毫无疑问，这会节省时间。对于非常复杂的语句，准备阶段可能需要几秒钟。老实说，即使语句不是事先准备好的，其中一些步骤也可以避免，但这完全是另一回事。
使用 Prepare 时需要注意一件事。必须显式设置参数的大小。例如，在代码中可以看到，我为价格设置了 precision 和 scale。这是强制性的，因为数据库需要知道它需要分配多少空间才能成功运行所有即将到来的执行。请记住，数据库还不知道我们将提供的所有值。
版本 5，使用事务 (SqlTransaction)
最后两个版本涵盖了事务的使用。第一个版本使用 SqlTransaction 将操作正确地包装在事务中。
如果您玩过该应用程序，您可能会注意到，在第一个语句成功执行而第二个语句失败的情况下，插入的订单行仍然保留在数据库中。换句话说，即使整个执行失败，部分数据仍然被保存，导致数据库处于逻辑不一致的状态；您有订单但没有订单项……
事务可以处理这些情况。通过使用事务，可以成功保存所有内容，或者什么都不保存。这是遵循 ACID 修改原则的关键技术。
public override bool AddOrder(string connectionString, string orderNumber, string price
, string product, string orderDate, bool generateError
, out string errorText) {
string sql;
bool returnValue = false;
int orderNumber_validated;
decimal price_intermediate;
decimal? price_validated = null;
System.DateTime orderDate_validated;
errorText = null;
// Data validations
if (!int.TryParse(orderNumber, out orderNumber_validated)) {
errorText = "Invalid order number";
return false;
}
if (!string.IsNullOrEmpty(price)) {
if (!decimal.TryParse(price, out price_intermediate)) {
errorText = "Invalid price";
return false;
}
price_validated = price_intermediate;
}
if (!System.DateTime.TryParse(orderDate, out orderDate_validated)) {
errorText = "Invalid order date";
return false;
}
// Insert the data
using (System.Data.SqlClient.SqlConnection connection
= new System.Data.SqlClient.SqlConnection()) {
try {
connection.ConnectionString = connectionString;
connection.Open();
using (System.Data.SqlClient.SqlTransaction transaction
= connection.BeginTransaction("AddOrder")) {
sql = "INSERT INTO PurchaseOrder (OrderNumber, OrderDate) VALUES (@orderNumber, @orderDate)";
using (System.Data.SqlClient.SqlCommand command
= new System.Data.SqlClient.SqlCommand(sql, connection, transaction)) {
command.Parameters.AddWithValue("@orderNumber", orderNumber_validated);
command.Parameters.AddWithValue("@orderDate", orderDate_validated);
command.ExecuteNonQuery();
}
sql = "INSERT INTO OrderItem (OrderNumber, OrderRow, Product, Price) VALUES (@orderNumber, @orderRow, @product, @price)";
using (System.Data.SqlClient.SqlCommand command
= new System.Data.SqlClient.SqlCommand(sql, connection, transaction)) {
command.Parameters.Add(new System.Data.SqlClient.SqlParameter("@orderNumber",
System.Data.SqlDbType.Int));
command.Parameters.Add(new System.Data.SqlClient.SqlParameter("@orderRow",
System.Data.SqlDbType.Int));
command.Parameters.Add(new System.Data.SqlClient.SqlParameter("@product",
System.Data.SqlDbType.VarChar, 100));
command.Parameters.Add(new System.Data.SqlClient.SqlParameter("@price",
System.Data.SqlDbType.Decimal, 10));
command.Parameters["@price"].Precision = 10;
command.Parameters["@price"].Scale = 2;
command.Prepare();
for (int counter = 1; counter <= 3; counter++) {
command.Parameters["@orderNumber"].Value = orderNumber_validated;
command.Parameters["@orderRow"].Value = counter;
command.Parameters["@product"].Value = product;
command.Parameters["@price"].Value = price_validated.HasValue
? (object)price_validated
: System.DBNull.Value;
command.ExecuteNonQuery();
}
}
if (generateError) {
this.GenerateError(connection, transaction);
}
transaction.Commit();
}
returnValue = true;
} catch (System.Data.SqlClient.SqlException sqlException) {
errorText = string.Format("Error {0} in line {1}:\n{2}",
sqlException.Number,
sqlException.LineNumber,
sqlException.Message);
} catch (System.Exception exception) {
errorText = exception.Message;
}
if (connection.State == System.Data.ConnectionState.Open) {
connection.Close();
}
}
return returnValue;
}
Public Overrides Function AddOrder(connectionString As String, orderNumber As String _
, price As String, product As String, orderDate As String _
, generateError As Boolean, ByRef errorText As String) As Boolean _
Implements IVersion.AddOrder
Dim sql As String
Dim returnValue As Boolean = False
Dim orderNumber_validated As Integer
Dim price_intermediate As Decimal
Dim price_validated As Decimal? = Nothing
Dim orderDate_validated As System.DateTime
errorText = Nothing
' Data validations
If (Not Integer.TryParse(orderNumber, orderNumber_validated)) Then
errorText = "Invalid order number"
Return False
End If
If (Not String.IsNullOrEmpty(price)) Then
If (Not Decimal.TryParse(price, price_intermediate)) Then
errorText = "Invalid price"
Return False
End If
price_validated = price_intermediate
End If
If (Not System.DateTime.TryParse(orderDate, orderDate_validated)) Then
errorText = "Invalid order date"
Return False
End If
' Insert the data
Using connection As System.Data.SqlClient.SqlConnection _
= New System.Data.SqlClient.SqlConnection()
Try
connection.ConnectionString = connectionString
connection.Open()
Using transaction As System.Data.SqlClient.SqlTransaction _
= connection.BeginTransaction("AddOrder")
sql = "INSERT INTO PurchaseOrder (OrderNumber, OrderDate) VALUES (@orderNumber, @orderDate)"
Using command As System.Data.SqlClient.SqlCommand _
= New System.Data.SqlClient.SqlCommand(sql, connection, transaction)
command.Parameters.AddWithValue("@orderNumber", orderNumber_validated)
command.Parameters.AddWithValue("@orderDate", orderDate_validated)
command.ExecuteNonQuery()
End Using
sql = "INSERT INTO OrderItem (OrderNumber, OrderRow, Product, Price) VALUES (@orderNumber, @orderRow, @product, @price)"
Using command As System.Data.SqlClient.SqlCommand _
= New System.Data.SqlClient.SqlCommand(sql, connection, transaction)
command.Parameters.Add(New System.Data.SqlClient.SqlParameter("@orderNumber",
System.Data.SqlDbType.Int))
command.Parameters.Add(New System.Data.SqlClient.SqlParameter("@orderRow",
System.Data.SqlDbType.Int))
command.Parameters.Add(New System.Data.SqlClient.SqlParameter("@product",
System.Data.SqlDbType.VarChar, 100))
command.Parameters.Add(New System.Data.SqlClient.SqlParameter("@price",
System.Data.SqlDbType.Decimal, 10))
command.Parameters("@price").Precision = 10
command.Parameters("@price").Scale = 2
command.Prepare()
For counter As Integer = 1 To 3 Step 1
command.Parameters("@orderNumber").Value = orderNumber_validated
command.Parameters("@orderRow").Value = counter
command.Parameters("@product").Value = product
command.Parameters("@price").Value = If(price_validated.HasValue, _
price_validated, System.DBNull.Value)
command.ExecuteNonQuery()
Next counter
End Using
If (generateError) Then
Me.GenerateError(connection, transaction)
End If
transaction.Commit()
End Using
returnValue = True
Catch sqlException As System.Data.SqlClient.SqlException
errorText = String.Format("Error {0} in line {1}:\n{2}", _
sqlException.Number, _
sqlException.LineNumber, _
sqlException.Message)
Catch exception As System.Exception
errorText = exception.Message
End Try
If (connection.State = System.Data.ConnectionState.Open) Then
connection.Close()
End If
End Using
Return returnValue
End Function
基本原理与命令类似。我将这两个执行都包装在定义事务的 using 块中。如果所有操作都成功完成，则在最后通过调用 Commit 方法提交事务。这将使更改永久化并对其他用户可见。如果任何操作失败，事务块将不会提交，因此事务会自动回滚，这意味着实际上没有进行任何更改。
使用 SqlTransaction 时，需要通过设置 Transaction 属性将每个命令绑定到事务。您可以勾选“在最后引发 SQL 执行错误”来测试事务的功能。在第二个选项卡上，您可以看到在发生错误的情况下数据是否被插入。
版本 6：使用事务 (TransactionScope)
最后一个版本与前一个版本非常相似，目的是相同的；使用事务。但是，有一些区别。SqlTransaction 仅用于数据库操作。TransactionScope 定义了一个从调用方开始并“升级”到数据库的事务。当 SQL 执行被包装在事务范围内时，事务管理器会自动将操作登记到事务中。因此，事务范围非常易于使用。
public override bool AddOrder(string connectionString, string orderNumber, string price
, string product, string orderDate, bool generateError
, out string errorText) {
string sql;
bool returnValue = false;
int orderNumber_validated;
decimal price_intermediate;
decimal? price_validated = null;
System.DateTime orderDate_validated;
errorText = null;
// Data validations
if (!int.TryParse(orderNumber, out orderNumber_validated)) {
errorText = "Invalid order number";
return false;
}
if (!string.IsNullOrEmpty(price)) {
if (!decimal.TryParse(price, out price_intermediate)) {
errorText = "Invalid price";
return false;
}
price_validated = price_intermediate;
}
if (!System.DateTime.TryParse(orderDate, out orderDate_validated)) {
errorText = "Invalid order date";
return false;
}
// Insert the data
using (System.Transactions.TransactionScope transactionScope
= new System.Transactions.TransactionScope()) {
using (System.Data.SqlClient.SqlConnection connection
= new System.Data.SqlClient.SqlConnection()) {
try {
connection.ConnectionString = connectionString;
connection.Open();
sql = "INSERT INTO PurchaseOrder (OrderNumber, OrderDate) VALUES (@orderNumber, @orderDate)";
using (System.Data.SqlClient.SqlCommand command
= new System.Data.SqlClient.SqlCommand(sql, connection)) {
command.Parameters.AddWithValue("@orderNumber", orderNumber_validated);
command.Parameters.AddWithValue("@orderDate", orderDate_validated);
command.ExecuteNonQuery();
}
sql = "INSERT INTO OrderItem (OrderNumber, OrderRow, Product, Price) VALUES (@orderNumber, @orderRow, @product, @price)";
using (System.Data.SqlClient.SqlCommand command
= new System.Data.SqlClient.SqlCommand(sql, connection)) {
command.Parameters.Add(new System.Data.SqlClient.SqlParameter("@orderNumber",
System.Data.SqlDbType.Int));
command.Parameters.Add(new System.Data.SqlClient.SqlParameter("@orderRow",
System.Data.SqlDbType.Int));
command.Parameters.Add(new System.Data.SqlClient.SqlParameter("@product",
System.Data.SqlDbType.VarChar, 100));
command.Parameters.Add(new System.Data.SqlClient.SqlParameter("@price",
System.Data.SqlDbType.Decimal, 10));
command.Parameters["@price"].Precision = 10;
command.Parameters["@price"].Scale = 2;
command.Prepare();
for (int counter = 1; counter <= 3; counter++) {
command.Parameters["@orderNumber"].Value = orderNumber_validated;
command.Parameters["@orderRow"].Value = counter;
command.Parameters["@product"].Value = product;
command.Parameters["@price"].Value = price_validated.HasValue
? (object)price_validated
: System.DBNull.Value;
command.ExecuteNonQuery();
}
}
if (generateError) {
this.GenerateError(connection);
}
transactionScope.Complete();
returnValue = true;
} catch (System.Data.SqlClient.SqlException sqlException) {
errorText = string.Format("Error {0} in line {1}:\n{2}",
sqlException.Number,
sqlException.LineNumber,
sqlException.Message);
} catch (System.Exception exception) {
errorText = exception.Message;
}
if (connection.State == System.Data.ConnectionState.Open) {
connection.Close();
}
}
}
return returnValue;
}
Public Overrides Function AddOrder(connectionString As String, orderNumber As String _
, price As String, product As String, orderDate As String _
, generateError As Boolean, ByRef errorText As String) As Boolean _
Implements IVersion.AddOrder
Dim sql As String
Dim returnValue As Boolean = False
Dim orderNumber_validated As Integer
Dim price_intermediate As Decimal
Dim price_validated As Decimal? = Nothing
Dim orderDate_validated As System.DateTime
errorText = Nothing
' Data validations
If (Not Integer.TryParse(orderNumber, orderNumber_validated)) Then
errorText = "Invalid order number"
Return False
End If
If (Not String.IsNullOrEmpty(price)) Then
If (Not Decimal.TryParse(price, price_intermediate)) Then
errorText = "Invalid price"
Return False
End If
price_validated = price_intermediate
End If
If (Not System.DateTime.TryParse(orderDate, orderDate_validated)) Then
errorText = "Invalid order date"
Return False
End If
' Insert the data
Using transactionScope As System.Transactions.TransactionScope _
= New System.Transactions.TransactionScope()
Using connection As System.Data.SqlClient.SqlConnection _
= New System.Data.SqlClient.SqlConnection()
Try
connection.ConnectionString = connectionString
connection.Open()
sql = "INSERT INTO PurchaseOrder (OrderNumber, OrderDate) VALUES (@orderNumber, @orderDate)"
Using command As System.Data.SqlClient.SqlCommand _
= New System.Data.SqlClient.SqlCommand(sql, connection)
command.Parameters.AddWithValue("@orderNumber", orderNumber_validated)
command.Parameters.AddWithValue("@orderDate", orderDate_validated)
command.ExecuteNonQuery()
End Using
sql = "INSERT INTO OrderItem (OrderNumber, OrderRow, Product, Price) VALUES (@orderNumber, @orderRow, @product, @price)"
Using command As System.Data.SqlClient.SqlCommand _
= New System.Data.SqlClient.SqlCommand(sql, connection)
command.Parameters.Add(New System.Data.SqlClient.SqlParameter("@orderNumber",
System.Data.SqlDbType.Int))
command.Parameters.Add(New System.Data.SqlClient.SqlParameter("@orderRow",
System.Data.SqlDbType.Int))
command.Parameters.Add(New System.Data.SqlClient.SqlParameter("@product",
System.Data.SqlDbType.VarChar, 100))
command.Parameters.Add(New System.Data.SqlClient.SqlParameter("@price",
System.Data.SqlDbType.Decimal, 10))
command.Parameters("@price").Precision = 10
command.Parameters("@price").Scale = 2
command.Prepare()
For counter As Integer = 1 To 3 Step 1
command.Parameters("@orderNumber").Value = orderNumber_validated
command.Parameters("@orderRow").Value = counter
command.Parameters("@product").Value = product
command.Parameters("@price").Value = If(price_validated.HasValue, _
price_validated, System.DBNull.Value)
command.ExecuteNonQuery()
Next counter
End Using
If (generateError) Then
Me.GenerateError(connection)
End If
transactionScope.Complete()
returnValue = True
Catch sqlException As System.Data.SqlClient.SqlException
errorText = String.Format("Error {0} in line {1}:\n{2}", _
sqlException.Number, _
sqlException.LineNumber, _
sqlException.Message)
Catch exception As System.Exception
errorText = exception.Message
End Try
If (connection.State = System.Data.ConnectionState.Open) Then
connection.Close()
End If
End Using
End Using
Return returnValue
End Function
与以前一样，事务是在 using 块中定义的。这次，而不是在最后调用 Commit，而是执行 Complete 方法来指示事务成功并使更改永久化。重要的是要注意，SqlCommand 没有明确绑定到事务。不需要设置事务属性，也不需要将事务对象传递给在单个事务中执行 SQL 语句的其他方法。从编码的角度来看，这大大简化了情况。
结论
本文的目的是展示成功数据库处理的一些关键概念。我希望它能阐明为什么需要做某些事情，以及它们如何使您的软件更加健壮。
我希望您也能注意到，正确做事实际上并不需要太多。并且作为奖励，这使得代码更容易处理和阅读。
当然，非常欢迎所有讨论。
参考文献
以下页面提供更多信息：
- SqlConnection 类
- SqlConnection.ConnectionString 属性
- SqlCommand 类
- try-catch
- using 语句
- SqlParameter 类
- SqlTransaction 类
- TransactionScope 类
历史
- 2015 年 8 月 19 日：创建
- 2015 年 8 月 20 日：添加了 VB 版本
- 2015 年 10 月 4 日：强调了重要的代码更改，VB 代码格式化方面进行了少量可读性更改