Smum County IO
一个 ADO.NET 工具箱，旨在作为基于 Access、Microsoft SQL Server 或 Oracle 数据库的应用程序的最低级别数据库 IO 层。
引言
Smum County IO 类是一个 ADO.NET 工具箱，旨在作为基于 Access、Microsoft SQL Server 或 Oracle 数据库的应用程序的最低级别数据 IO 层。它为 ADO.NET 的复杂性提供了一个程序员友好的接口，让用户可以专注于编码，而不是试图记住如何填充 DataSet。该类实现了以下基本数据访问功能：
- 读取数据
- 写入数据
- 管理事务
- 打开/关闭 ADO 连接
此外，该类旨在隐藏 Access、Microsoft SQL Server 和 Oracle 数据库访问之间的差异。如果一致地使用该类执行数据库 IO，我们可以创建一个与这些数据库中的任何一个兼容而无需编写数据库特定代码的应用程序。
存储过程与动态 SQL
任何数据库程序员都会告诉你，使用存储过程进行数据库访问比在后端执行 SQL 语句要快得多。然而，此处未使用存储过程，以实现期望的数据库中立性。
DBTools
DBTools 是我创建的一个接口，用于组织 IO 类中特定于数据库的代码。而不是在 IO 类中每次出现数据库后端代码与其他数据库不同的情况时进行分支，我而是调用 DBTools 接口的一个方法来处理代码。然后，我为每个支持的后端（Access、MS SQL Server 和 Oracle）创建了实现 DBTools 接口的类。这有助于保持 IO 类代码的整洁，同时更容易将此项目扩展到其他数据库。DBTools 类中的一些方法用于为通用 ADO.NET 接口实例化特定于数据库的类，而其他方法则简单地包装特定于数据库的函数。这些方法将在本文的其余部分讨论。
连接到数据源
在使用 IO 类之前，有必要指明要访问的数据库类型以及连接到数据库所使用的连接参数。这在类的构造函数中完成。
public IO(DBType dbType, string dataSource, string userID,
string password, string initialCatalog)
构造函数的重载方法允许您在不使用 initialCatalog 和 userID、password 的情况下指定连接参数。只有在连接到 MS SQL Server 时才需要 initialCatalog 来指明要连接的数据库，而连接到没有密码保护的 Access 数据库时则不需要 userID 和 password。
所以，要初始化对象，只需这样做：
io = new Smum.IO(Smum.IO.DBType.MSSQL, "ServerName", "UserID", "Password", "Database");
读取方法
一旦 IO 对象被实例化，从数据库读取数据就像调用一个重载的 Read 方法一样简单。单独的 Read 方法允许您读取标量值、DataSet、DataTable、DataRow 或 DataRowCollection。在任何情况下，除读取标量值的那个之外，所有 Read 方法最终都会调用 DataSet 的 Read 方法。那么，让我们仔细看看这个方法：
public ReturnStatus Read(ref DataSet dataSet, string sql, string tableName)
{
try
{
if (dataSet == null) dataSet = new DataSet();
//Keep track of whether this table is new to this dataset.
bool newTable = (tableName == "" || dataSet.Tables[tableName] == null);
FillDataSet(ref dataSet, sql, tableName);
//If no table name was specified, get the name
//of the table as indicated by the dataset.
if (tableName == "")
tableName = dataSet.Tables[dataSet.Tables.Count - 1].TableName;
//If this table is new to this dataset, if any columns
//are AutoIncrement columns, set the
//AutoIncrementSeed and AutoIncrementStep to -1
//so that automatically assigned values in the
//DataSet will be negative while the ones in the database
//will be positive, thus avoiding conflicts.
if (newTable)
{
foreach (DataColumn column in dataSet.Tables[tableName].Columns)
{
if (column.AutoIncrement)
{
column.AutoIncrementStep = -1;
column.AutoIncrementSeed = -1;
}
}
}
//Return Success or NotFound.
if (dataSet.Tables[tableName].Rows.Count == 0)
return ReturnStatus.NotFound;
else
return ReturnStatus.Success;
}
catch (System.Exception e)
{
throw new Smum.Exception("Error occurred while reading data using: '" + sql + "'", e);
}
}
读取数据的实际工作都将在 FillDataSet 过程中完成，我将在下面讨论。在此过程中，您应该注意到，您可以传递一个已包含数据的 DataSet 来进行填充。tableName 参数允许您指明要在 DataSet 中填充从读取数据中获得的数据的表的名称。
请注意，如果您正在填充 DataSet 中的新表，并且该表包含自动增量列，我正在初始化这些列的种子和步长，以便添加到表中的所有新行都将具有负值，并且不应与数据库中已有的行的值（应该是正数）冲突。如果不这样做，并且其中一列具有唯一索引，当您将 DataSet 绑定到允许创建新行的 DataGrid 时，您将很快遇到麻烦。DataSet 可能会为新行中的自动增量列创建一个值，该值与 DataSet 中已存在行的列值冲突，从而生成错误。最后，此方法返回 Success 或 NotFound 的状态，这对于所有 Read 方法都是标准的。
现在来看 FillDataSet。
private void FillDataSet(ref DataSet dataSet, string sql, string tableName)
{
IDbCommand command =
dbTools.CreateCommand(sql, Connection, Transaction.Transaction);
IDbDataAdapter adapter = dbTools.CreateDataAdapter(command);
if (tableName == "")
{
//Fill the schema for the table.
adapter.FillSchema(dataSet, SchemaType.Source);
//Fill the dataset.
adapter.Fill(dataSet);
}
else
{
//Fill the schema for the table if the table does not yet have any data.
if (dataSet.Tables[tableName] == null ||
dataSet.Tables[tableName].Rows.Count == 0)
dbTools.FillSchema(adapter, dataSet, tableName);
//Fill the dataset.
dbTools.FillDataSet(adapter, dataSet, tableName);
}
}
这基本上都是标准的 ADO.NET 代码。我实例化一个 IDbCommand 对象，传入 SQL 语句、当前连接和当前事务（稍后将讨论事务）。然后，我实例化一个 IDbDataAdapter 对象，并使用它来填充读取数据的模式并读取数据。为了使这段标准代码特定于数据库，我依赖于 dbTools 对象，该对象之前已被实例化为处理当前数据库后端的正确类型。该类的 CreateCommand 方法创建特定于当前数据库后端的 IDbCommand 对象，而 CreateDataAdapter 方法对 IDbDataAdapter 对象执行相同的操作。
请注意，如果在读取时指定了表名，我将调用 dbTools 对象的一个方法来填充模式并读取数据。这是必要的，因为 IDbDataAdapter 接口的 FillSchema 和 Fill 方法不允许您指定表名，但特定于数据库的 IDbDataAdapter 实现允许这样做。因此，为了为 FillSchema 和 Fill 指定表名，我将 IDbDataAdapter 对象强制转换为特定数据库的正确数据适配器类型，然后在该强制转换的对象上调用 FillSchema 或 Fill 方法。由于此代码特定于数据库，因此我将其放在后端特定的 DBTools 类中。例如，这是 FillSchema 的 SqlTools 实现：
public void FillSchema(IDbDataAdapter adapter, DataSet dataSet, string tableName)
{
SqlDataAdapter nativeAdapter = adapter as SqlDataAdapter;
if (nativeAdapter != null)
nativeAdapter.FillSchema(dataSet, SchemaType.Source, tableName);
else
adapter.FillSchema(dataSet, SchemaType.Source);
}
请注意，我根本不会显式打开到数据库的连接。我将其留给 DataAdapter 来处理。如果在调用 Fill 方法时连接已关闭，DataAdapter 将打开连接，读取数据，然后关闭连接。如果在调用 Fill 方法时连接已打开，DataAdapter 将读取数据并保持连接打开。这正是我想要的。
DataTable、DataRow 和 DataRowCollection 读取方法都调用此 DataSet 读取方法，然后返回读取的 DataTable、DataRow 或 DataRowCollection。如果您想使用 Smum.IO 类的 Write 方法将更改写回数据库，那么重要的是，每个要写入的 DataTable、DataRow 和 DataRowCollection 都只能包含单个表的数据，并且在读取数据时您需要将此表的名称传递给 Read 方法。这就是 Write 方法知道要将数据写回哪个表的方式。如果您不打算更改读取的数据，或者不打算使用 Write 方法更新数据库，那么传递给 Read 方法的表名无关紧要。
写入方法
这就是 Smum.IO 类大显身手的地方。在通过 Read 方法对读取的数据进行更改后，您只需调用一个重载的 Write 方法即可将数据写回数据库。但是有一个注意事项：为了使用这些方法写出数据，必须指定要写入的每个 DataTable 的 TableName，并且 DataTable 必须有一个或多个主键。这就是这些写入方法识别要写入的表以及表中要更新的行的识别方式。
每个 Read 方法都有一个对应的 Write 方法，允许您从 DataSet、DataTable、DataRow 或 DataRowCollection 写入数据。这些例程中的每一个都实际上路由到 DataRow 的 Write 方法。DataSet 写入调用 DataSet 中每个表的 DataTable 写入，后者调用其 DataRowCollection 的 DataRowCollection 写入，后者为集合中的每个行调用 DataRow 写入。
让我们来检查一下 DataRow 写入方法。
public void Write(DataRow dataRow)
{
bool transactionBegun = false;
try
{
if (dataRow.RowState != DataRowState.Unchanged)
{
//Insure that the table name of the datarow
//was previously set. If not, throw an exception.
if (dataRow.Table.TableName == "Table" || dataRow.Table.TableName == "")
throw new Smum.Exception("TableName of DataSet Table has not been set.");
DataColumnCollection dataColumns = dataRow.Table.Columns;
DataColumn[] keyColumns = dataRow.Table.PrimaryKey;
if (keyColumns.Length == 0)
throw new Smum.Exception("No primary key has been defined.");
string sql = "";
IDbCommand command = dbTools.CreateCommand("", Connection,
Transaction.Transaction);
bool autoIncrementedKey = false;
string tableName = dbTools.GetSqlTableName(dataRow.Table.TableName);
//If the row is not attached to a table, add it to its parent table.
if (dataRow.RowState == DataRowState.Detached)
dataRow.Table.Rows.Add(dataRow);
switch (dataRow.RowState)
{
case DataRowState.Added:
<See code snippet for DataRowState.Added below>
case DataRowState.Deleted:
<See code snippet for DataRowState.Deleted below>
case DataRowState.Modified:
<See code snippet for DataRowState.Modified below>
}
//If we need to read the value of an autoincremented
//key after an insert, begin a transaction.
if (autoIncrementedKey)
{
Transaction.Begin();
transactionBegun = true;
}
if (sql != "")
{
command.CommandText = sql;
ExecuteSQL(command);
}
if (autoIncrementedKey)
{
object newKey = null;
Read(ref newKey, "Select @@Identity");
Transaction.Commit();
transactionBegun = false;
dataRow.Table.Columns[keyColumns[0].ColumnName].ReadOnly = false;
dataRow[keyColumns[0].ColumnName] = newKey;
}
//Accept the row changes so that the RowState will be Unchanged.
if (dataRow.RowState != DataRowState.Deleted) dataRow.AcceptChanges();
}
}
catch (System.Exception e)
{
if (transactionBegun) Transaction.Rollback();
throw new Smum.Exception("Error occurred while writing data.", e);
}
}
简而言之，此例程确定行是已添加、已删除还是已修改，并生成正确的 SQL 语句以相应地更改数据库。ADO 命令对象用于执行 SQL 语句。
首先，我们测试以查看行是否未更改。不需要更新未更改的行。然后，我们确保属于 DataRow 的表的 TableName 已设置，并找出该表的主键字段。这是通过检查关联 DataTable 的 PrimaryKey 属性来完成的。这就是为什么我们必须在 Read 例程中填充 DataSet 的模式。如果我们没有这样做，这些信息将不可用。如果表中没有键字段，我们将抛出异常。
然后，我们通过调用 dbTools 对象（就像我们之前读取数据时一样）的 CreateCommand 方法来实例化特定于后端数据库的命令对象。
在将一个分离的 DataRow 添加到其关联表后，我们就开始构建 SQL 语句。以下是一个添加的 DataRow 的代码片段：
case DataRowState.Added:
sql = "Insert Into " + tableName;
string columns = "(";
string values = "(";
for (int i = 0; i < dataColumns.Count; i++)
{
string column = dataColumns[i].ColumnName;
//Don't include this column in the insert if it is an autoincrement column whose
//value has not yet been set. A negative value will indicate that the value was
//set by the DataSet and not by the database.
if (!(dataColumns[i].AutoIncrement && Tools.ToInt32(dataRow[i], 0) <= 0))
{
//Add command parameter for value.
values += dbTools.GetSqlParamName(column) + ", ";
command.Parameters.Add(dbTools.CreateDataParameter(column, dataRow[i]));
columns += dbTools.GetSqlColumnName(column) + ", ";
}
else
{
//If this autoincrement column is the primary key, we will read its value
//from the database after inserting.
if (column == keyColumns[0].ColumnName)
autoIncrementedKey = true;
}
}
//Remove the trailing commas.
columns = columns.Remove(columns.Length - 2, 2);
values = values.Remove(values.Length - 2, 2);
sql += " " + columns + ") Values " + values + ")";
break;
当然，对于添加的 DataRow，我们会创建一个 Insert 语句。SQL 语句中的列值将使用命令参数占位符来指定，同时我们将一个命令参数添加到命令对象以指示占位符的值。例如，我们的 Insert 语句看起来会像这样：
Insert into Customers (FirstName, LastName) Values (@FirstName, @LastName)
在上面，@FirstName 和 @LastName 是列值的占位符，它们对应于我们添加到命令对象的同名命令参数。对于占位符，我使用列名，前面加上特定于后端的字符。对于 MS SQL Server 和 Access，此字符必须是 @，而对于 Oracle，则必须是冒号。由于这些字符是数据库特定的，我使用 dbTools 对象的一个方法 GetSqlParamName 来将它们添加到列名前面。而且，由于命令参数对象也是数据库特定的，因此它们是使用 dbTools 对象的 CreateDataParameter 方法实例化的。
在 SQL 语句中使用命令参数，而不是直接在 SQL 字符串中包含值，其优点在于无需担心如何为特定后端数据库格式化列值。每个数据库在日期值等方面有不同的语法，通过为这些值使用命令参数，我们可以让 ADO.NET 来处理这些细微的差异。此外，这是将二进制大型对象写入数据库的唯一方法。
SQL 语句中使用的列名是使用 dbTools 对象的 GetSqlColumnName 方法指定的。这个特定于数据库的方法允许我将所有 Access 列名包装在方括号中。使用 GetSqlTableName 对表名执行相同的操作。这允许使用 Access 中的某些保留字作为表名和列名。例如，如果没有方括号括起表名，我们就无法将数据写入名为 User 的表中。
请注意，我没有费心写出自动增量列的值，这些列的值小于 0。这些是底层 DataSet 已设置临时值的列，当写入行时，我们应该让数据库分配它们的值。但是，我正在跟踪当自动增量列也是主键列时。在这种情况下，在插入行后，我将从数据库中读取键列的值，并用这个新值更新 DataRow。这在此类中没有用途，但对于调用应用程序来说非常重要，如果应用程序以后需要这个新的主键值作为另一个要写入的 DataRow 的外键。
这是已删除 DataRow 的代码：
case DataRowState.Deleted:
sql = "Delete From " + tableName + " Where";
foreach (DataColumn keyColumn in keyColumns)
{
string column = keyColumn.ColumnName;
sql += " " + dbTools.GetSqlColumnName(column) + " = " +
dbTools.GetSqlParamName(column);
command.Parameters.Add(dbTools.CreateDataParameter(column,
dataRow[column, DataRowVersion.Original]));
}
break;
如果我们正在写出一个已删除的 DataRow，我们实际上将执行一个 Delete 语句来从数据库中删除该行。在这种情况下，我使用 DataRow 的主键列来标识要删除的行。同样，SQL 语句中的列值由命令参数占位符表示，我们将相应的命令参数添加到命令对象中。
这是已修改 DataRow 的代码：
case DataRowState.Modified:
//Create an update statement.
for (int i = 0; i < dataColumns.Count; i++)
{
//Only update the column if the data has changed.
if (!dataRow[i].Equals(dataRow[i, DataRowVersion.Original]))
{
string column = dataColumns[i].ColumnName;
sql += dbTools.GetSqlColumnName(column) + " = " +
dbTools.GetSqlParamName(column) + ", ";
command.Parameters.Add(dbTools.CreateDataParameter(column, dataRow[i]));
}
}
if (sql != "")
{
//Add where clause for key columns.
sql = sql.Remove(sql.Length - 2, 2); //Remove trailing comma.
sql = "Update " + tableName + " Set " + sql + " Where ";
foreach (DataColumn keyColumn in keyColumns)
{
string column = keyColumn.ColumnName;
sql += dbTools.GetSqlColumnName(column) + " = " +
dbTools.GetSqlParamName(column);
command.Parameters.Add(dbTools.CreateDataParameter(column,
dataRow[column, DataRowVersion.Original]));
}
}
break;
如果 DataRow 已被修改，我将创建一个 Update SQL 语句。在这种情况下，我将遍历 DataRow 中的每个列，并且仅当列的值发生更改时才将其包含在 Update 语句中。我知道该列已更改，如果该列的值不等于该列的原始值。该列的原始值保存在 DataRow 中，可以通过将 DataRowVersion.Original 参数传递给 DataRow 的索引器来访问。同样，我使用占位符在 SQL 语句中指定列的值，并将适当的命令参数添加到命令对象中。
一旦创建了正确的 SQL 语句，剩下的就是执行 SQL 语句。这是通过将命令对象的 CommandText 设置为要执行的 SQL 并调用 ExecuteSQL 例程来完成的。ExecuteSQL 例程只是确保数据库连接已打开，然后通过调用 ExecuteNonQuery 方法来执行命令。
在调用 ExecuteSQL 之前和之后都有代码，以便处理我们需要检索刚刚创建的自动增量主键值的情况。要做到这一点，我们需要在命令执行之前启动一个事务。在命令执行后，我们可以通过读取 Select @@Identity 返回的标量值来获取新的主键值。检索此值后，我们将提交我们的事务并更新 DataRow 中的主键值。如果我们没有将我们的写入和读取包装在事务中，其他进程可能会在我们有机会读取新的主键之前向我们的表添加一个新行。使用事务可以防止任何其他进程在我们提交之前向我们的表写入新行。由于这几乎是立即完成的，因此数据库响应性不应该有任何可感知的延迟。请注意，我们还需要确保在尝试更新主键列之前不要读取它。
请注意，Oracle 中的自动增量列是通过使用 Oracle 序列和触发器来实现的，而不是通过为列指定特定数据类型来实现的。因此，上面用于获取新自动增量主键值的代码将不适用于 Oracle。
最后，我们对 DataRow 调用 AcceptChanges，以便 DataRow 的 DataRowState 将更改为 Unchanged，并且所有列的原始值将等于其当前值。请注意，之前如果 DataRow 是 Detached，我们已经将其添加到其父表中。这是必需的，因为 AcceptChanges 方法在 DataRow 分离时会抛出异常。
事务处理
ADO.NET 中的事务处理是通过将 Transaction 对象传递给应该参与事务的数据库读写来实现的。这个 Transaction 对象是通过调用要开始事务的连接对象的 BeginTransaction 方法获得的。然后，提交或回滚事务只需调用 Transaction 对象的 Commit 或 Rollback 方法即可。
为了提供一个用户友好的接口来处理此过程，我创建了一个 IOTransaction 类，它包装了一个单独的 IDBTransaction 对象。Smum.IO 类持有一个 IOTransaction 对象的引用，该对象在 Smum.IO 构造函数中实例化，并通过 Smum.IO 的 Transaction 属性公开。
IOTransaction 类包含用于在关联连接上开始、提交和回滚事务的方法。因此，如果您想在给定的 Smum.IO 连接上启动一个事务，就像这样简单：
smumIO.Transaction.Begin();
同样，提交或回滚 Smum.IO 连接上的当前事务可以这样完成：
smumIO.Transaction.Commit();
或者
smumIO.Transaction.Rollback();
关于事务处理，除了处理嵌套事务之外，没有太多需要注意的。MS SQL 的 SqlConnect 对象不支持嵌套事务。Access 的 OleDbConnection 对象也不支持，Oracle 的 OracleConnection 对象也不支持。这并不意味着这些数据库不支持嵌套事务。这仅仅意味着连接对象没有实现此功能。在任何一个连接上尝试启动一个事务，而另一个事务尚未完成，都会导致关于不支持并行事务的错误消息。
通常，我不需要嵌套事务，但在创建需要事务的例程时，能够简单地启动一个事务而不必担心调用例程是否已启动一个事务，这是很好的。在这种情况下，我通常不希望我的事务被提交，除非调用例程的事务被提交。为了实现这种功能，我在 IOTransaction 类中引入了一个 nestCount 变量。此变量开始时为 0，每次调用 Begin 方法时增加 1，每次调用 Commit 或 Rollback 方法时减少 1。只有当 nestCount 为 0 时，才会在当前连接上开始一个事务，并且只有当 nestCount 为 0 时，才回滚或提交一个活动的事务。通过这种方式，我可以多次调用 IOTransaction 上的 Begin 方法，只有第一次调用才会真正开始一个事务。随后的 Commit 或 Rollback 调用除了与初始 Begin 匹配的那个之外，都没有效果。例如：
//nestCount = 0
smumIO.Transaction.Begin(); //Transaction begun. nestCount = 1
smumIO.Transaction.Begin(); //Nothing happens. nestCount = 2
smumIO.Transaction.Commit(); //Nothing happens. nestCount = 1
smumIO.Transaction.Commit(); //Transaction committed. nestCount = 0
当然，这些并不是真正的嵌套事务，但它们满足了我的需求。重要的是要注意，您必须将每个事务的 Begin 调用用 Commit 或 Rollback 括起来，并且整个事务将根据第一个 Begin 是用 Commit 还是 Rollback 括起来而被提交或回滚。否则，您的数据将不会被提交。另请注意，此方案允许您调用内部的 Rollback，然后调用结束的 Commit，从而导致提交您认为已回滚的更改。显然，这是一个危险，并且仅表明这些并非真正的嵌套事务。
其他方法
Smum.IO 类中包含的这些其他方法也是可用的，并且上面没有讨论过。
InitializeNewRow
此方法将 DataRow 初始化为指定表的“新行”，以便它可以与 Write 方法一起使用来创建新行。例如：
DataRow row = null;
smumIO.InitializeNewRow(ref row, "Employees");
row["FirstName"] = "John";
row["LastName"] = "Doe";
smumIO.Write(row); //Creates employee John Doe.
FillSchema
此方法将使用给定 SQL 语句的模式填充 DataSet。这由 InitializeNewRow 使用。
Read（用于标量）
这是一个简单的 Read 方法，它将读取单个数据，通常是从单个行中读取单个列。我上面没有讨论它，因为它不像其他 Read 方法那样工作。它使用 ExecuteScalar 方法来执行指定的 SQL 语句，以从数据库读取单个标量值。
Write（用于标量）
这是 Read 标量方法的对应 Write 方法。当指明表名、列名和 Where 子句时，这将向数据库写入单个值到单个列。由于 Where 子句可能包含多个行，因此可以一次更新多个行的单个列。
Smum.Exception
这个自定义异常类继承自 System.Exception 类，只是为了更清晰地格式化错误消息。此类中的 ToString 方法将显示当前异常对象的 Message 属性以及任何内部异常的 Message 属性。任何是 System.Exception 对象的内部异常都将以“[System Error]”前缀，以区分您的应用程序错误和 .NET 错误。错误消息将按顺序显示，最外层的错误首先出现。请查看随附的示例项目，了解其外观示例。
结论
我写这篇文章是为了演示一种在 .NET 中处理数据库 IO 的方法。它功能不多，但涵盖了基础知识。希望对您有所帮助。将来，我计划发布一篇关于我如何利用这些基本功能来创建一个支持创建业务逻辑类的框架的文章。
历史
- 提交：2006 年 4 月 3 日。