引言
在 Microsoft NET 应用程序中使用 Microsoft Office 数据时所需的大多数任务与使用任何其他数据时的任务相同 创建连接根据使用数据时的需要创建 DataReader 或 DataAdapter创建一个或多个 DataSet 来封装相关数据的 DataTable等等因此我不打算在此重新编写 ADONET 文档本文的目的在于提供按 Office 数据源编写基础数据检索代码时所需的缺失或广为分布的信息段好像那些知道 Jet 的奇特之处的人士却不是很熟悉 ADONET而大部分的 ADONET 专业人士不是很了解支持 Office 可编程序性的技术因此提供一个快速的概述希望可以解答我所见到的这两方的常见问题
首先让我们讨论一下连接到 Microsoft Excel 和 Microsoft Access 的数据并从中检索数据的基础我认为此领域中存在最大的问题 通常一旦人们能够解决该问题Office 特定问题将会得到解决而且其余问题主要集中于如何使用 ADONET下面我将讨论如何使用 OleDbConnection 对象的 GetOleDbSchemaTable 方法来检索关于运行时 Access 或 Excel 数据的架构信息这样您可以避开关于数据源的硬编码信息数据源非常有用从中可以获取数据
建立连接
如果要连接到 Excel 电子表格或 Access 数据库则需要创建一个 OleDbConnection 对象并向其传递一个带有目标数据源详细信息的连接字符串您需要为 Provider 属性指定 Jet OLE DB 提供程序
连接到 Access
让我们看一看用于 Access 数据库的典型连接字符串
Provider=MicrosoftJetOLEDB;Data Source= C:\Databases\mymusicmdb
这非常简单 它仅指定 Provider 和 Data Source 属性如上所述Provider 是 Jet 提供程序而 Data Source 属性包含数据库的完全限定路径
当然您甚至还需要确保 Access 数据库的安全对不对？这样您还需要指定工作组信息文件（默认情况下为 systemmdw）的路径并提供用户 ID 和密码
Provider=MicrosoftJetOLEDB;Data & _
Source=C:\Databases\mymusicmdb; & _
Jet OLEDB:System database= & _
C:\Databases\systemmdw; & _
User ID=UserX;Password=UserXPassword
请注意指定控制 Access 数据库安全性的 mdw 文件时需要使用 Jet OLE DB 特定的属性如果希望更好地控制数据库的行为则可以浏览 Jet OLE DB 提供程序（该提供程序位于 Appendix B:Microsoft Jet OLE DB Properties Reference）所提供的其他属性来控制锁定行为和如何处理特定类型的失败等等请注意只有那些可以在连接字符串中设置的属性才可以访问指定提供程序的属性之前需要连接处于打开状态否则将无法设置这些属性
可以使用 Microsoft® Visual Studio® NET来获取包括所有的 Jet OLE DB 提供程序设置完整的 Access 连接字符串的模板在服务器资源管理器中创建一个与 Access 数据库的数据连接然后使用工具箱的数据部分创建一个 OleDbConnection 对象得到的对象的 ConnectionString 属性会包含默认的所有 Jet OLE DB 提供程序属性
连接到 Excel
现在让我们看看 Excel 电子表格的典型连接字符串的外观
Provider=MicrosoftJetOLEDB; & _
Data Source=C:\Spreadsheets\calculationsxls; & _
Extended Properties=Excel
在连接到 Excel 文件的情况下可以看到我们必须在连接字符串中使用 Extended Properties 属性以及 Provider 和 Data Source如果是 Excel 和更高版本则使用Excel 设置如果希望获取此属性的其他可接受值的更多信息请参阅 ADO Provider Properties and Settings 的Extended Properties Property Settings部分
您会说啊但是 Excel 中的安全性怎么样？嗯恐怕没有激动人心的消息您无法打开与受密码保护的电子表格的连接除非已手动地在 Excel 中打开此电子表格（有关详细信息请参阅 XL:Could Not Decrypt File Error with Password Protected File）描述的错误出现于 Excel ODBC 提供程序但是在 Jet OLE DB 提供程序中该行为相同其他选项为删除电子表格的密码并使用某些其他安全机制（例如限制文件所在文件夹的权限）来控制访问
不幸的是您也无法使用 Visual Studio NET 获取 Excel 连接字符串的模板进行一些小小的尝试即可创建 Excel 数据连接但是您会发现其属性不可编辑而且 ConnectionString 属性将保留空白 这是 IDE（集成的开发环境）中的奇特之处有关详细信息请参阅 PRB:Cannot Configure Data Connection to Excel Files in Visual Studio NET这样对于 Excel 连接基本上都需要自己进行工作但在这种情况下对连接进行编码就像在用户界面 (UI) 中创建连接一样简单
检索数据
现在已经明白了如何建立与 Office 数据源的连接就让我们看看它是怎样检索数据的出于简单性的考虑我打算使用 OleDbConnection/OleDbCommand/OleDbDataReader 数据检索方案相同的方法在稍稍调整之后可以用于构建 OleDbDataAdapter 并填充 DataSet如果想要大体上了解一下 ADONET请参阅《NET Framework Developers Guide》的 Accessing Data with ADONET 部分它会为您带来阅读的乐趣
检索 Access 数据
编写 Access 数据检索代码时请切记指定 SQL 时必须遵循的语法具有某些特性无法在图形 UI 中创建 Access 查询无法访问 SQL 视图无法复制得到的 SQL 语句并将其粘贴到代码中不这本应非常简单的生成的 SQL 代码通常具有一部分但不是全部的所需语法必须在 Access 开发环境中编写代码的任何人都会知道这一点但是对于一般的 NET 客户端应用程序开发人员而言这算的上是新闻最让人烦心的是条件表达式它需要以某种方式分隔 WHERE 字句中的特定类型的数据日期和时间值必须使用数字记号 (#) 进行分隔文本值必须使用单引号 () 进行分隔例如
SELECT City Neighborhood SalePrice MonthsOnMarket
FROM RealEstate
WHERE ListingDate > #//#
有关这些问题的详细信息请参阅 Date and Time Criteria Expressions
需要记住的另一点看上去好像显而易见但是却使人们感到困惑所以我将讨论这一点请确保 Access 表的列名称不使用保留字可以在 SQL Reserved Words 处查询保留字如果使用任何保留字我想说的是如果可能就重命名列我知道如果您已使用数据库该操作则不太可能所以必要时可以创建查询并将其用作有问题列的表的替换查询仅使用 AS 重命名列就如
SELECT ArtistsArtistName GenresGenre LabelsLabel
TracksPublic AS Track ReleasesReleaseName
FROM (Labels INNER JOIN ((Artists
记住这些要点让我们来看一个示例
Imports System
Imports SystemData
Imports SystemDataOleDb
Public Function GetAccessData(ByVal UID As String _
ByVal pwd As String ByVal artist As String)
Dim conn As New OleDbConnection
Dim musicReader As OleDbDataReader
Dim cmd As New OleDbCommand
Dim connString As String
Dim i As Integer
Try
设置连接字符串
connString = Jet OLEDB:System database= & _
C:\Databases\systemmdw; & _
Data Source=C:\Databases\mymusicmdb; & _
Provider=MicrosoftJetOLEDB; & _
User ID= & UID & ;Password= & pwd
打开连接
connConnectionString = connString
connOpen()
设置命令属性
cmdConnection = conn
cmdCommandText = SELECT * from music & _
WHERE ArtistName = & artist &
获取 OleDbDataReader
并对其进行一些处理
musicReader = _
cmdExecuteReader(CommandBehaviorCloseConnection)
Try
While (musicReaderRead)
处理数据
End While
Finally
musicReaderClose()
End Try
Catch ex As Exception
错误处理
End Try
End Function GetAccessData
检索 Excel 数据
关于列名称Excel 和 Access 一样具有相同的保留字限制一般而言如果记住 SQL 保留字并在创建可能用作数据源的任何对象时避开保留字则会更好还有一点需要考虑
Excel 的语法同样也有奇特之处对代码影响最大的项是用于引用要返回的数据集的语法
注意对于最简单的 Excel 数据检索请使用维护类似表格式的电子表格
第一个选项是指定工作表和该表中的单元格集（可选）需要确保工作表名称后跟美元符号和单元格集（可选）通过使用冒号分隔集合中的起始单元格和终止单元格来指定此单元格集然后使用括号将整个数据标识字符串括起使用此类型语法的 SELECT 语句可能如下所示
SELECT SalesMonth TotalSales PercentageChangeYear
FROM [Sheet$A:E]
另一个选项是在 Excel 中创
