通过 ADO.NET 接口简化数据库访问

PIEBALDconsult

4.56/5 (15投票s)

2014年4月2日

CPOL

11分钟阅读

46722

854

使用 ADO.NET 接口构建一个小框架来简化数据库访问

下载源代码 - 12.4 KB

背景

许多开发者在开发使用数据库的应用程序时都遇到过问题。这些开发者阅读过某个 ADO.NET 提供程序（Provider）的 MSDN 文档，但在需求超出简单示例的范围时就会卡住。MSDN 文档中的示例只展示了所讨论的 ADO.NET 提供程序具体的类。实际上，如果你使用接口——IDbConnection、IDbCommand、IDataReader、IDataParameter——来处理 ADO.NET 会更容易。所有 ADO.NET 提供程序都实现了这些接口，因此通过面向接口而不是特定提供程序的具体类进行编码，可以在很大程度上实现提供程序独立性（*SQL 语法差异仍然是一个问题*，但执行 SQL 语句的代码则不必如此）。

我们还收到很多可以通过使用参数简单解决的问题。MSDN 文档并没有引导新手开发者使用参数。新手开发者可能会认为参数很难用，只有复杂的、高级的应用程序才需要它们，而一些误导性的 Code Project 文章（如

"参数化字符串仅应用于通过用户输入生成 SQL 的情况，但这里不是这种情况！"

这类说法）并没有帮助构建健壮可靠的数据访问代码。参数实际上非常容易使用，而且使用参数可以使你的代码更健壮、更易于阅读。虽然有些操作无法通过参数完成，但与那些没有参数就无法完成的操作相比，这微不足道。当你需要在短时间内多次执行同一条语句时，使用参数还可以提高应用程序的效率。我坚信，每个开发者都应该将参数作为将值传递给数据库的首选工具；为此，一个框架必须使参数的使用至少和字符串拼接一样容易。

MSDN 关于参数的说明：http://msdn.microsoft.com/en-us/library/yy6y35y8(v=vs.110).aspx

Microsoft 的数据访问应用程序块 (DAAB)

MSDN *确实* 包含了一些解决了类似问题的内容。这远远超出了我目前的需求，但你可能应该去了解一下。
http://msdn.microsoft.com/en-us/library/dn440726(v=pandp.60).aspx
DAAB 自 .NET 2.0 起就已经存在。

在 CodeProject 上，我通常会看到（并使用）“数据库无关”这个词，但我注意到 DAAB 页面使用了“提供程序独立”这个词。我确信我们说的是同一件事，而且我认为微软的说法更简洁，所以我将在此后努力使用它。

引言

本文在很大程度上是为了能够全面回答 Code Project 上的问题，而不是仅仅重复我常用的说辞“不要使用字符串拼接；使用参数化查询”。我从 .NET 1.1 开始就一直在编写使用 ADO.NET 提供程序的应用程序，并且已经编写了大约六个提供程序独立的框架——我甚至写过关于其中两个框架的文章——这只是另一个。我的主要框架实际上相当庞大和沉重，对于一篇文章来说太大了。这个框架的目标实际上仅仅是作为一个例子，展示可以从我打算在本文中提供的信息中轻松创建什么。

所有示例代码都是 C# 编写的，因为我使用 C#，但 VB.NET 在使用这些接口和生成健壮的数据库访问代码方面同样出色。我还没有真正使用过这段代码，只测试过其中的几个部分，但它确实反映了我十多年使用 ADO.NET 的经验。我希望在未来的项目中使用这段代码，并欢迎任何实际使用过它的朋友提供反馈。

IDbConnection

参考：http://msdn.microsoft.com/en-us/library/system.data.idbconnection(v=vs.110).aspx

为了与数据库通信，你需要一个来自提供程序的连接（Connection）。一个应用程序可以使用多个连接到任意数量的数据库。任意数量的命令（Command）可以使用同一个连接，但一次只有一个命令可以在特定连接上执行（请注意，这也包括当连接上有打开的 DataReader 时）。

实例化连接是唯一需要知道你正在使用哪个提供程序以及使用哪个具体类的时候。（实际上，可以编写一个应用程序，使其甚至不需要知道这一点，但我不会在本篇文章中讨论这一点。）之后，一切都将完全依赖于接口。

而不是

System.Data.SqlClient.SqlConnection con = new System.Data.SqlClient.SqlConnection ( ... )

不如使用：

System.Data.IDbConnection con = new System.Data.SqlClient.SqlConnection ( ... )

以及一个接受连接作为参数的方法

public int DoSomething ( System.Data.IDbConnection con ) { ... }

确实*存在*需要知道对象实际类型的时候，但这些情况并不常见，而且很可能可以隐藏在应用程序之外。

IDbCommand

参考：http://msdn.microsoft.com/en-us/library/system.data.idbcommand(v=vs.110).aspx

到目前为止，你可能想知道如何在不知道实际具体类型的情况下实例化一个命令。快速的答案是“你做不到”——你应该让连接来帮你完成，它知道要使用什么类型。让框架来帮助你。IDbConnection 接口有一个名为 `CreateCommand` 的方法，它可以实例化并返回一个正确类型的命令。然后，你可以根据需要设置 `CommandText` 和其他属性。所以请使用

System.Data.IDbCommand cmd = con.CreateCommand()

和

public int DoSomething ( System.Data.IDbCommand cmd ) { ... }

IDbCommand 还具有你可能熟悉使用的三个主要执行方法。

ExecuteScalar
ExecuteNonQuery
ExecuteReader

应用程序可以对命令执行这些方法中的任何一个，而无需知道使用的是哪种具体的数据库类型。（我再说一遍，是的，确实存在语法方面的考虑，但数据访问类对此并不真正关心。）

IDataReader

参考：http://msdn.microsoft.com/en-us/library/system.data.idatareader(v=vs.110).aspx

说到 `ExecuteReader`，它返回一个 IDataReader 实例。所以使用

System.Data.IDataReader rdr = cmd.ExecuteReader()

和

public int DoSomething ( System.Data.IDataReader rdr ) { ... }

此外，`IDataReader` 实现 `IDataRecord`，因此在需要对 DataReader 的单个结果执行某个操作时，可以考虑使用它。

public int DoSomething ( System.Data.IDataRecord rec ) { ... }

http://msdn.microsoft.com/en-us/library/system.data.idatarecord(v=vs.110).aspx

IDataParameter

参考：http://msdn.microsoft.com/en-us/library/system.data.idataparameter(v=vs.110).aspx

不要将其与 `IDbDataParameter` 混淆。（我之所以这么说，是因为我深知你现在可能会将它们混淆。）

就像连接可以为你实例化一个命令一样，命令也可以为你实例化一个参数。

System.Data.IDataParameter prm = cmd.CreateParameter()

然后，你可以设置 `ParameterName` 和 `Value`，并将参数添加到命令的 `ParameterCollection` 中。

好的，我听到有人喊“AddWithValue!”
`AddWithValue` 是*某些*实现 IDataParameterCollection 接口的类（如 `SqlParameterCollection` 和 `OleDbParameterCollection`）的一个非常方便的成员，但它*不是* IDataParameterCollection 接口的成员，因此当你想要提供程序独立性时，你不能指望它在那里。而且它在 .NET 2.0 之前不存在。

在我结束关于参数的话题之前，我还想提到，有些开发者在实例化参数时会花费大量精力来设置 DbType。这是浪费精力，因为提供程序在设置 Value 时会自行推断 DbType——有时甚至会覆盖指定的 DbType。只需设置 Value，如果你确实需要，然后再更改 DbType。

IDataParameterCollection

参考：http://msdn.microsoft.com/en-us/library/system.data.idataparametercollection(v=vs.110).aspx

命令有一个 Parameters 属性，它提供了对 `IDataParameterCollection` 的访问。我之所以提及这个接口，是因为它有一个小缺点。请记住，这些接口是在 .NET 2.0 之前创建的，因此没有泛型。这导致此集合将其成员视为 `object` 而不是 IDataParameters。其结果是，当你访问集合中的参数时，你需要进行强制类型转换。这不会对效率产生太大影响，但如果你需要频繁执行此操作，它会让你非常头疼；最好将其隐藏在一个框架内部。

IDisposable

参考：http://msdn.microsoft.com/en-us/library/system.idisposable(v=vs.110).aspx

MSDN 文档中的示例没有展示 `using` 语句的使用，因此许多初学者没有学会使用它，导致他们的代码质量不高。`using` 语句是提高代码健壮性和可维护性最简单的方法之一。

将它们放在一起，但不太理想

为了讨论方便，假设你收到一个 Excel 文件，并想将其中一些数据加载到 SQL Server 数据库中。诚然，市面上有很多工具可以做到这一点，但这里的重点是展示 ADO.NET 可以做什么。我甚至会（*就为你*）省略我通常的许多格式设置。

using ( IDbConnection
  srccon = new OleDbConnection ( excelconnectionstring )
, dstcon = new SqlConnection   ( sqlserverconnectionstring )
)
{
  int lastcopiedID = -1 ;

  // Get lastcopiedID from destination
  using ( IDbCommand dstcmd = dstcon.CreateCommand() )
  {
    dstcmd.CommandText = sqlserverquerystatement ;

    dstcon.Open() ;

    object tmp = dstcmd.ExecuteScalar() ;

    dstcon.Close() ;

    if ( ( tmp != null ) && ( tmp != DBNull.Value ) ) lastcopiedID = (int) tmp ;
  }

  // Copy the data
  using ( IDbCommand
    srccmd = srccon.CreateCommand()
  , dstcmd = dstcon.CreateCommand()
  )
  {
    IDataParameter prm ;

    srccmd.CommandText = excelquerystatement ; // Let's assume a WHERE ID>@ID clause

    // Repeat for each parameter
    prm = srccmd.CreateParameter() ;
    prm.ParameterName = "@ID" ;
    prm.Value = lastcopiedID ;
    srccmd.Parameters.Add ( prm ) ;

    dstcmd.CommandText = sqlserverinsertstatement ;

    // Repeat for each destination column
    prm = dstcmd.CreateParameter() ;
    prm.ParameterName = "@ID" ;
    dstcmd.Parameters.Add ( prm ) ;

    scrcon.Open() ;

    using ( IDataReader scrrdr = srccmd.ExecuteReader() )
    {
      dstcon.Open() ;

      while ( srcrdr.Read() )
      {
        for ( int i = 0 ; i < srcrdr.FieldCount ; i++ )
          prm = ((IDataParameter) dstcmd.Parameters ( i )).Value = srcrdr [ i ] ?? DBNull.Value ;

        dstcmd.ExecuteNonQuery() ;
      }

      dstcon.Close() ;
    }
  }
}

这不是很令人兴奋，它提供的少数改进之一是所需的 `using` 语句更少，因为我们可以合并连接和命令。缺点是连接的打开/关闭状态处理得不太好，而且参数的处理工作量更大。

将它们放在一起，在一个小型通用框架中

我在这里提供的代码是专门作为示例编写的，展示了拥抱 ADO.NET 接口强大功能可以实现什么。你可以随意修改代码或编写自己的代码——真的，我希望你这样做。使用代码就像

using ( PIEBALD.Data.IDbConnection con = PIEBALD.Data.SqlServer.Connect ( servername , databasename ) )
{
  using ( PIEBALD.Data.IDbCommand cmd = con.CreateCommand
  ( @"SELECT fizz , buzz FROM foo WHERE bar=@Param0"
  , 42
  ) )
  {
    using ( System.Data.IDataReader rdr = cmd.ExecuteReader() ) { ... }
  }
}

我从不存储实际的连接字符串；存储创建连接字符串所需的各个部分可以使应用程序更具通用性。也可以考虑接收命令行信息的控制台应用程序——用户是否希望输入连接字符串？

接口

为了实现这种简洁性并便于扩展，我将首先定义两个接口，用于连接（Connection）和命令（Command），其中只包含我需要的内容。

  public interface IDbConnection : System.IDisposable
  {
    IDbConnection Clone() ;
    PIEBALD.Data.IDbCommand CreateCommand ( string CommandText , params object[] ParameterList ) ;
  }

  public interface IDbCommand : System.IDisposable
  {
    int ParameterCount { get ; }
    System.Data.IDataParameter Parameter ( int Index ) ;
    System.Data.IDataParameter Parameter ( string Name ) ;
    T ExecuteScalar<T> ( T IfNull ) ;
    int ExecuteNonQuery() ;
    System.Data.IDataReader ExecuteReader() ;
  }

你可以根据需要添加其他成员。我没有包含事务处理；你可能想添加它。

DbCommand

`DbCommand` 是 `System.Data.IDbCommand` 的一个包装器，它隐藏了许多细节，尤其是与参数相关的细节。如果你需要，可以添加 `CommandType` 和 `CommandTimeout` 属性。你可以从 `DbCommand` 派生，但如果你这样做，你可能还需要派生一个新的 `DbConnection`。一种更简单的添加成员的方式是通过部分类文件——拥抱语言提供的工具。

每个 Execute 方法都会确保连接是打开的，并且没有其他方法正在使用该连接*。 `Dispose` 方法将确保连接关闭。 `DbCommand` 提供了一个泛型版本的 `ExecuteScalar`，它隐藏了将返回值转换为适当数据类型的强制类型转换。

* 开发者需要确保连接上没有活动的 DataReader。

namespace PIEBALD.Data
{
  public partial class DbCommand : IDbCommand
  {
    protected System.Data.IDbCommand Command { get ; private set ; }

    protected internal DbCommand ( System.Data.IDbCommand Command )
    {
      this.Command = Command ;

      return ;
    }

构造函数允许你指定参数的值。这*确实*要求你使用参数名称 `Param0`...`Paramn`，但考虑到你无需手动创建它们，这似乎是微不足道的代价。另一种选择是为每个参数传递一个 `Tuple<string,object>*`，但我这样做已经很久了，而且我不介意。
如果你提供参数值，那么 `CommandText` 和 `Parameters` 将通过 `System.String.Format` 进行传递；这允许你指定无法通过实际参数指定的项。此功能可能会被滥用，但我相信你会只在真正需要时才使用它。

* 参考：http://msdn.microsoft.com/en-us/library/dd268536(v=vs.110).aspx；Tuple 在 .NET 4.0 之前不存在。

    protected internal DbCommand
    ( System.Data.IDbCommand Command
    , string                 CommandText
    , params object[]        ParameterList
    )
    : this ( Command )
    {
      if ( ( ParameterList == null ) || ( ParameterList.Length == 0 ) )
      {
        this.Command.CommandText = CommandText ;
      }
      else
      {
        this.Command.CommandText = System.String.Format ( CommandText , ParameterList ) ;

        for ( int i = 0 ; i < ParameterList.Length ; i++ )
        {
          System.Data.IDataParameter prm = this.Command.CreateParameter() ;

          prm.ParameterName = System.String.Format ( "Param{0}" , i ) ;
          prm.Value = ParameterList [ i ] ?? System.DBNull.Value ;

          this.Command.Parameters.Add ( prm ) ;
        }
      }

      return ;
    }

    public virtual void
    Dispose()
    {
      lock ( this.Command.Connection )
      {
        if ( this.Command.Connection.State == System.Data.ConnectionState.Open )
          this.Command.Connection.Close() ;
      }

      this.Command.Dispose() ;

      return ;
    }

对命令的参数集合的唯一访问是通过这些成员。

    public virtual int
    ParameterCount { get { return ( this.Command.Parameters.Count ) ; } }

    public virtual System.Data.IDataParameter
    Parameter ( int Index ) { return ( this.Command.Parameters [ Index ] as System.Data.IDataParameter ) ; }

    public virtual System.Data.IDataParameter
    Parameter ( string Name ) { return ( this.Command.Parameters [ Name ] as System.Data.IDataParameter ) ; }

`ExecuteNonQuery` 和 `ExecuteReader` 展示了执行命令时的一些良好实践。

锁定连接实例——为了线程安全，以防万一。
打开连接。
简单的错误处理。
- 捕获异常
- 向异常添加数据。
- 重新抛出异常。

    public virtual int
    ExecuteNonQuery()
    {
      lock ( this.Command.Connection )
      {
        if ( this.Command.Connection.State != System.Data.ConnectionState.Open )
          this.Command.Connection.Open() ;

        try
        {
          return ( this.Command.ExecuteNonQuery() ) ;
        }
        catch ( System.Exception err )
        {
          err.Data [ "CommandText" ] = this.Command.CommandText ;

          for ( int i = 0 ; i < this.ParameterCount ; i++ )
          {
            System.Data.IDataParameter prm = this.Parameter ( i ) ;

            err.Data [ prm.ParameterName ] = prm.Value ;
          }

          throw ;
        }
      }
    }

    public virtual System.Data.IDataReader
    ExecuteReader()
    {
      lock ( this.Command.Connection )
      {
        if ( this.Command.Connection.State != System.Data.ConnectionState.Open )
          this.Command.Connection.Open() ;

        try
        {
          return ( this.Command.ExecuteReader() ) ;
        }
        catch ( System.Exception err )
        {
          err.Data [ "CommandText" ] = this.Command.CommandText ;

          for ( int i = 0 ; i < this.ParameterCount ; i++ )
          {
            System.Data.IDataParameter prm = this.Parameter ( i ) ;

            err.Data [ prm.ParameterName ] = prm.Value ;
          }

          throw ;
        }
      }
    }
  }
}

`ExecuteScalar` 具有与其他 Execute 方法相同的特性，但它还需要转换返回值。`ExecuteScalar` 的复杂性在于允许在将数据库中的值转换为代码中使用的数据类型时进行一些灵活的强制类型转换和转换。我主要使用它来将数据库中的数字值转换为代码中的枚举值。

    public virtual T
    ExecuteScalar<T> ( T IfNull )
    {
      object result ;

      lock ( this.Command.Connection )
      {
        if ( this.Command.Connection.State != System.Data.ConnectionState.Open )
          this.Command.Connection.Open() ;

        try
        {
          result = this.Command.ExecuteScalar() ;
        }
        catch ( System.Exception err )
        {
          err.Data [ "CommandText" ] = this.Command.CommandText ;

          for ( int i = 0 ; i < this.ParameterCount ; i++ )
          {
            System.Data.IDataParameter prm = this.Parameter ( i ) ;

            err.Data [ prm.ParameterName ] = prm.Value ;
          }

          throw ;
        }
      }

      if ( ( result == null ) || ( result == System.DBNull.Value ) )
      {
        result = IfNull ;
      }
      else if ( ! ( result is T ) )
      {
        System.Type t = typeof(T) ;

        if ( t.IsEnum )
        {
          if ( result is string )
          {
            result = System.Enum.Parse ( t , (string) result ) ;
          }
          else
          {
            result = System.Convert.ChangeType ( result , System.Enum.GetUnderlyingType ( t ) ) ;
          }
        }
        else if ( result is System.IConvertible )
        {
          result = System.Convert.ChangeType ( result , t ) ;
        }
      }

      return ( (T) result ) ;
    }

DbConnection<T>

这是一个 `System.Data.IDbConnection` 的抽象泛型包装器的定义。

namespace PIEBALD.Data
{
  public abstract partial class DbConnection<T> : IDbConnection
  where T : System.Data.IDbConnection
  {
    protected System.Data.IDbConnection Connection { get ; private set ; }

    protected DbConnection ( T Connection )
    {
      this.Connection = Connection ;

      return ;
    }

    public virtual void
    Dispose()
    {
      this.Connection.Dispose() ;

      return ;
    }

    public abstract IDbConnection Clone() ;

按原样编写的 `CreateCommand` 方法仅适用于 `DbCommand`；如果你派生了一个新的 `DbCommand`，那么你还需要派生一个新的 `DbConnection`，并使其 `CreateCommand` 方法返回该新类型的命令。或者，也许 `CreateCommand` 可以泛型化，随便吧，自己动手。

    public virtual PIEBALD.Data.IDbCommand
    CreateCommand
    ( string          CommandText
    , params object[] ParameterList
    )
    {
      return ( new PIEBALD.Data.DbCommand
      ( this.Connection.CreateCommand()
      , CommandText
      , ParameterList
      ) ) ;
    }
  }
}

SqlServer.Connection

要使用这些类，你需要为你想使用的提供程序派生特定的 `DbConnections`。在这里，我将展示我为 SQL Server 编写的那个。

namespace PIEBALD.Data.SqlServer
{
  public partial class Connection : PIEBALD.Data.DbConnection<System.Data.SqlClient.SqlConnection>
  {
    protected Connection
    ( System.Data.SqlClient.SqlConnection Connection )
    : base ( Connection )
    {
      return ;
    }

    protected Connection
    ( string ConnectionString )
    : this ( new System.Data.SqlClient.SqlConnection ( ConnectionString ) )
    {
      return ;
    }

    public override PIEBALD.Data.IDbConnection
    Clone()
    {
      return ( new Connection ( this.Connection.ConnectionString ) ) ;
    }

我使用一个静态方法来构建 `ConnectionString` 并调用构造函数。你可以根据你的需要创建类似的方法，但这提供了基础。

    public static Connection
    Connect ( string Server
    , string Database
    )
    {
      return ( new Connection ( System.String.Format
      ( "Server={0};Database={1};Trusted_Connection=yes"
      , Server
      , Database
      ) ) ) ;
    }
  }
}

zip 文件还包括 Access 和 Excel 的 DbConnections。如果你查看这三个提供程序特定的类，你应该可以轻松地看到添加对新提供程序支持只需要编写多少代码；这一切都归功于接口的力量。

历史

2014-04-01 首次发布