第 15 章用 ADO 操作 SQL Server 数据库

第15章用ADO操作SQL Server数据库 学习导读本章主要介绍通过ADO访问SQL Server数据库的方法。通过本章的学习，应掌握以下内容： • 了解ADO • 用VB进行ADO编程的方法

第15章用ADO操作SQL Server数据库 访问Microsoft数据库的方式： • 直接利用网络编程实现对SQL Server命名管道或者TCP/IP Socket • ODBC • DAO（数据访问对象），DAO使用了一个独立的数据库引擎Jet，主要面向于桌面应用 • RDO（远程数据对象），用于访问远程数据库 • ADO（ActiveX数据对象），利用统一对象模型进行信息访问，其核心是基于ActiveX技术的新的编程接口

15.1 ODBC简介 开放ODBC（Open DataBase Connectivity，ODBC）是一个用于访问数据库的统一界面标准。ODBC实际上是一个数据库访问库，可以使应用程序直接操作数据库中的数据，具有数据库的独立性。它主要是为访问关系型数据库创建的。 ODBC提供给应用程序一组操作数据库的统一调用接口（API），ODBC API与具体某种DBMS的API之间的映射过程由ODBC完成。 Oracle 驱动程序提供的API 应用程序1 ODBC API ODBC DB2 应用程序2 …… 应用程序n SQL Server

ODBC简介 ODBC通过驱动程序提供了很好的数据库独立性，驱动程序与具体的DBMS相关。通过ODBC开发的应用程序具有很好的可移植性。使用ODBC进行开发的优点： (1) ODBC的数据库独立性使得应用程序的可移植性好； (2) 可以缩短开发时间，先在单机上开发，再通过调整数据源集成； (3) 可以统一数据库的访问接口，使开发人员更加专注于应用逻辑的处理。

ODBC简介 15.1.1 ODBC的结构 ODBC主要通过由各数据库厂商提供的驱动程序来提供数据库的独立性。驱动程序用以支持ODBC API函数调用，通常是一个或者几个DLL文件。驱动程序管理器（Driver Manager）用于管理ODBC API与驱动程序中提供的API之间的绑定（Binding），驱动程序管理器一般包含在ODBC.DLL或ODBC32.DLL中，可以连接到所有的ODBC应用程序中。

ODBC结构示意图 第1层：应用程序调用ODBC API向数据库发送SQL语句并处理结果 ODBC应用程序 ODBC驱动程序管理器第2层：管理和装载驱动，将ODBC API绑定到实际的数据库API Oracle的ODBC驱动程序 DB2的ODBC驱动程序 …… SQL Server的ODBC驱动程序第3层：处理数据库的API调用 Oracle 数据源1 Oracle 数据源2 DB2 数据源1 DB2 数据源2 SQL Server 数据源1 SQL Server 数据源2 第4层：数据源 Oracle1 Oracle2 DB2 -1 DB2 - 2 SQL Server 1 SQL Server 2

ODBC数据源的创建 15.1.2 ODBC数据源的创建在Windows 98/NT下，ODBC数据源管理器位于控制面板中。在2000/XP中位于控制面板的管理工具中。创建ODBC数据源的步骤： (1)打开“ODBC数据源管理器”对话框

ODBC数据源的创建 ODBC数据源管理器： “用户DSN”、“系统DSN”、“文件DSN”用来创建数据源。 • 用户DSN：创建的数据源只有创建用户可以使用 • 系统DSN：创建过程与前者相同，但是整个系统中的用户均可以使用 • 文件DSN：把数据源信息保存在一个文件中 • 驱动程序：列举系统中已经安装的ODBC驱动 • 跟踪：可以建立使用数据源的日志，用于调试 • 连接池：提供数据源连接句柄的重用

ODBC数据源的创建 (2) 进入“系统DSN”页面，可以通过“添加”、“删除”、“配置”按钮进行数据源的操作。

ODBC数据源的创建 (3)创建新数据源，对话框中列举了系统已安装的驱动程序。

ODBC数据源的创建 (4)选择SQL Server驱动，进入“创建到SQL Server的新数据源”对话框。需要输入数据源的名称、描述以及提供数据库服务的主机名，可以直接输入也可以在下拉列表中选择。

ODBC数据源的创建 (5)下一步会出现SQL Server认证的输入框，选择准备使用的登录验证方式并按要求输入用户名和密码。

ODBC数据源的创建 (6)选择登录后的默认数据库。

ODBC数据源的创建 (7)在ODBC设置对话框，可保持默认选项。

ODBC数据源的创建 (8)点击“完成”会进入ODBC设置的确认对话框，可以在这里进行数据源的连接测试。

ODBC数据源的创建 (9)点击“确定”后返回到“系统DSN”可以看到新创建的数据源。

15.2 ADO 微软公司的ADO (ActiveX Data Objects) 是一个用于存取数据源的COM组件。它提供了编程语言和统一数据访问方式OLE DB的一个中间层。允许开发人员编写访问数据的代码而不用关心数据库是如何实现的，而只用关心到数据库的连接。访问数据库的时候，关于SQL的知识不是必要的，但是特定数据库支持的SQL命令仍可以通过ADO中的命令对象来执行。微软也为ODBC提供了一种OLE DB供应器，使得ADO可以访问ODBC数据源。

ADO的模型结构 15.2.1 ADO的模型结构 ADO中包含了一系列的对象和集合。从2.0版本后，ADO模型结构中引入了事件概念，事件（Events）是将要发生或已经发生的操作的通知。用户可以利用事件来进行异步任务的编程。

ADO对象 ADO Connection：链接对象，提供与数据库的连接信道，允许数据交换； Command：命令对象，嵌入一条SQL，用来执行一个操作； Error：ADO产生的错误对象； Field：字段，记录对象中的一列； Parameter：SQL语句中的参数； Property：属性对象，表示ADO对象的某种特性； RecordSet：结果集； Record：代表一行。 Connection Errors Error Properties Property Command Parameters Parameter Properties Property RecordSet Fields Field Properties Property Record Fields Field

ADO集合 ADO模型中的集合

ADO对象编程的步骤 ADO的目标是访问、编辑和更新数据源，为完成这一目标所需的步骤如下： (1)连接到数据源（Connection对象），并可以有选择地开始一个事务； (2)创建Command对象来执行SQL命令； (3)可以在SQL命令中指定列、表和值作为变量参数（Parameter对象）； (4)执行命令（Command、Connection、RecordSet）； (5)如果命令产生按行返回的记录集，则将行存储在缓存中（RecordSet对象）；

ADO对象编程的步骤 (6)可以选择创建缓存视图，对数据进行排序、筛选和定位（RecordSet对象）； (7)通过添加、删除或更改行和列编辑数据（RecordSet对象）； (8)在适当情况下，通过在缓存中更改内容来更新数据源（RecordSet对象）； (9)如果使用了事务，可以接受或者拒绝在完成事务期间所作的更改。结束事务（Connection对象）。 ADO中的各个对象包含了大量的属性、方法和事件，这些属性、方法和事件在ADO的编程中有着重要的作用。

Connection对象 15.2.2 ADO中的Connection对象 1. Connection对象的常用属性 Connection对象是ADO对象模型中的顶层对象，表示与数据源的连接。对于数据的访问都是建立在Connection对象之上的。 (1) Attribute属性用于设置Connection的特性。并非所有的数据提供者都支持这种属性。取值： • adXactCommitRetaining：执行保留的提交，即通过自动调用CommitTrans启动新事务； • adXactAbortRetaining：执行保留的中止，即通过自动调用RollbackTrans启动新事物。

Connection对象的常用属性 (2) CommandTimeOut属性该属性作用于Connection和Command对象。表示执行一个命令最长可以等待多少时间（以秒计算），超过这个时间限制会终止执行并报告错误，默认值为30。如果设置为0，将会无限期等待。 (3) ConnectionString属性连接数据库的字符串，包含连接到一个数据源的信息，该字符串包含一系列由分号分隔的argument=value语句。ADO支持其中的4个参数，其他参数不经处理直接传给供应器。这4个参数为： • Provider=：指定连接所用的供应器名称； • File Name=：指定包含预设连接信息的特定供应器的文件名； • Remote Provider=：指定打开客户端连接时使用的供应器名称； • Remote Server=：指定打开客户端连接时使用的服务器的路径名。 ConnectionString在连接关闭时是可读写的，在连接打开时是只读的。

ADO连接数据库 通过ADO连接数据库有两种方法： 1)通过DSN建立连接在建立好DSN之后，通过Connection对象建立数据库的连接，一个DSN连接需要的参数：DSN数据源名称、用户名和口令。使用格式如下： ConnectionString=“DSN=数据源名称; UID=用户名; PWD=口令“ 其中各参数的含义如下： • DSN：指出数据源名称； • UID：指出登录数据库的用户名，也可以使用UserID指出用户名； • PWD：指出登录数据库的口令，也可以使用Password指出口令。

ADO连接数据库的例子 例15.1假设DSN数据源名称为“SchoolDBS”，用户名为“licb”，口令为“640219”，则建立连接并返回一个记录集的代码如下： ‘声明并初始化一个ADO的Connection对象 Set Conn = New ADODB.Connection ‘设置Connection对象的ConnectionString属性 Conn.ConnectionString= “DSN=SchoolDBS;UID=licb;PWD=640219” ‘使用Connection对象的Open方法连接数据库 Conn.Open ‘利用Connection对象的Execute方法返回数据库的特定数据集 Set Rs = Conn.Execute(“select * from student where class=‘95031’”)

ADO连接数据库的例子 也可以使用以下等价代码： ‘声明并初始化一个ADO的Connection对象 Set Conn = New ADODB.Connection ‘使用Connection对象的Open方法连接数据库 Conn.Open “DSN=SchoolDBS;UID=licb;PWD=640219” ‘利用Connection对象的Execute方法返回数据库的特定数据集 Set Rs = Conn.Execute(“select * from student where class=‘95031’”) 在VB中需要引用一些ActiveX控件才能使用上述代码： VB For Applications VB runtime objects and procedures VB objects and procedures OLE Automation Microsoft ActiveX Data Object 2.6 Library

ADO连接数据库 2)直接使用数据库文件名或数据源名称建立连接对于Access等可以直接使用文件名，SQL Server等可以使用数据源名，其使用格式如下： ConnectionString=“Provider=提供者名称; DSN=数据源名称; DRIVER=驱动程序名称; SERVER=服务器名称; DATABASE=数据库名称; UID=用户名; PWD=口令” 其中各参数的含义如下： • Provider：数据提供者，默认为MSDASQL，为微软OLE DB，通常省略； • DSN：要打开的数据库对应的ODBC数据源，是可选参数； • DRIVER：要打开的数据库所用的驱动程序名称； • SERVER：要打开的数据库所在的服务器名，本机可用local，可选； • DATABASE：要打开的数据库名称，可选； • UID：用户名，用来访问数据库，可选； • PWD：用户口令或密码，用来访问数据库，可选。

ADO连接数据库的例子 例15.2要建立到school数据库的连接并返回一个记录集，采用ADO中不需要ODBC设置的连接方式： ‘声明并初始化一个ADO的Connection对象 Set Conn = New ADODB.Connection ‘设置Connection对象的ConnectionString属性 Conn.ConnectionString= “Provider=MSDASQL; DRIVER={SQL Server}; SERVER=NO1; UID=licb; PWD=640219; DATABASE=school” ‘使用Connection对象的Open方法连接数据库 Conn.Open ‘利用Connection对象的Execute方法返回数据库的特定数据集 Set Rs = Conn.Execute(“select * from student where class=‘95031’”)

ADO连接数据库的例子 也可以使用以下等价代码： ‘声明并初始化一个ADO的Connection对象 Set Conn = New ADODB.Connection ‘使用Connection对象的Open方法连接数据库 Conn.Open “Provider=MSDASQL; DRIVER={SQL Server}; SERVER=NO1; UID=licb; PWD=640219; DATABASE=school” ‘利用Connection对象的Execute方法返回数据库的特定数据集 Set Rs = Conn.Execute(“select * from student where class=‘95031’”)

Connection对象的常用属性 (4) ConnectionTimeOut属性该属性指示在连接成功之前最多可以等待的时间（以秒计），默认值为15秒。 (5) CursorLocation属性这个属性是Connection对象和RecordSet对象都具有的。该属性用来指定游标引擎的位置。该属性对于连接都是可读写的；对于关闭的记录集而言是可读写的；对于打开的记录集是只读的。 • adUseNone：没有使用游标服务（只用于向后兼容）； • adUseClient：使用由本地游标库提供的客户端游标。本地游标服务通常允许使用的许多功能是驱动程序提供的游标无法使用的； • adUseServer：默认值。使用数据提供者的或驱动程序提供的游标。这些游标有时非常灵活，对于其他用户对数据源所作的更改具有额外的敏感性。当CursorLocation用于客户端RecordSet或Connection对象时，只能将其设置为adUseClient。

Connection对象的常用属性 (6) DefaultDatabase属性 Connection对象的默认数据库名称。 (7) Mode属性这个属性用于指示可以获得的数据修改权限： • adModeUnknown：默认值，权限尚未设置或无法确定； • adModeRead：只读； • adModeWrite：只写； • adModeReadWrite：读/写； • adModeShareDenyRead：防止其他用户读； • adModeShareDenyWrite：防止其他用户写； • adModeShareExclusive：防止其他用户打开连接； • adModeShareDenyNone：防止其他用户以任何权限打开连接。

Connection对象的常用属性 (8) State属性这个属性用于指示Connection的当前状态： • adStateClosed：默认值，对象关闭； • adStateOpen：对象打开； • adStateConnection：对象正在连接； • adStateExecuting：对象正在执行一个命令； • adStateFetching：RecordSet对象正在获取所需的行。

Connection对象 2. Connection对象的常用方法 (1) BeginTrans、CommitTrans和RollbackTrans方法这3个方法用于处理事务。BeginTrans启动一个新事务；CommitTrans保存发生的任何变化并结束当前事务，它也可能启动一个新事务；RollbackTrans取消当前事务所做的任何变化并结束该事务，它也可能启动一个新事务。使用格式如下： connection对象.BeginTrans connection对象.CommitTrans connection对象.RollbackTrans 注意：并非所有的提供者都支持事务。对于支持嵌套事务的提供者，在一个打开的事务中调用BeginTrans会打开一个新的嵌套事务，返回值能表明嵌套级别。如果是1，表示顶级事务。

Connection对象 (2) Cancel方法该方法可以终止用Execute或Open方法启动的异步任务。如果想终止的方法不是异步的，Cancel方法会返回一个运行时错误。使用方法： connection对象.Cancel (3) Close方法该方法用于关闭与数据库提供者之间的连接。关闭对象并不会把对象从内存中清除，以后还可以再被打开。使用方法： connection对象.Close

Connection对象 (4) Execute方法该方法用于执行指定的查询、SQL语句或者存储过程，返回一个RecordSet对象。该方法也是Command对象的一个方法。使用方法： connection对象.Execute CommandText, RecordsAffected, Options 其中各参数的含义如下： • CommandText：一个命令字符串，其中包含SQL语句、存储过程、表名或者其他数据提供者所定义的将要被执行的命令； • RecordsAffected：将会随机返回一个带有受该命令影响的记录个数； • Options：可选，指示提供者应如何为CommandText参数赋值。

Connection对象 Options参数的取值： • adCmdText：CommandText是一个SQL命令； • adCmdTable：CommandText是一个数据库表名； • adCmdTableDirect：CommandText是一个数据库表名，仅仅在RecordSet.open或requery时可用。使用seek方法必须以此种方式打开； • adCmdStoredProc：CommandText是一个存储过程。

Connection对象 (5) Open方法该方法用于建立一个与数据源的物理连接。使用方法： connection对象.Open ConnectionString, UserID, Password, Options 其中各参数的含义如下： • ConnectionString：连接字符串； • UserID：用户名； • Password：密码； • Options：可选： • adAsyncConnect：异步打开连接。当连接可用时，ConnectComplete事件被触发； • adConnectUnspecified：同步打开连接（默认）。这些参数都是可选的。

Connection对象 3. Connection对象的常用事件当连接事务开始被提交或被回退；或当命令执行时及连接开始或结束时，Connection事件发生。事件由事件句柄例程处理，这些例程在某个操作开始前或该操作结束后被调用。其中，有一些成对出现的事件，成对的事件中，在操作开始前被调用的事件带有前缀Will-，而操作结束后的事件带有后缀-Complete。 (1) BeginTransComplete、CommitTransComplete和RollbackTransComplete事件这些事件都是在对应方法以异步方式运行完成时被调用。它们被Connection对象关联的操作执行完成后被调用，内部调用的语法是： BeginTransCompleteTransactionLevel, pError, adStatus, pConnection CommitTransCompletepError, adStatus, pConnection RollbackTransCompletepError, adStatus, pConnection 各参数的含义： • TransactionLevel：引发该事件的BeginTrans的新事务级别 • pError：是一个Error对象，说明当EventStatusEnum值为adStatusErrorsOccurred时发生的错误，否则将不对它进行设置

Connection对象 • adStatus：表明引发事件的操作的状态，如果操作成功，会将该参数设置为adStatusOK，失败是adStatusErrorsOccurred。如果在该事件返回前设置为adStatusUnwantedEvent，可以避免该事件通知继续向下传递 • pConnection：引发该事件的Connection对象

Connection对象 (2) ConnectComplete和Disconnect事件分别在连接开始后、连接结束后被调用，内部调用的语法是： ConnectCompletepError, adStatus, pConnection Disconnect adStatus, pConnection 各参数的含义： • pError、adStatus：与BeginTransComplete相同 • pConnection：对于ConnectComplete，它是与Connect方法相关联的Connection对象的一个引用。对于Disconnect，它是与CommitTrans方法相关联的Connection对象的一个引用

Connection对象 (3) ExecuteComplete事件在Execute方法已经以异步方式运行完成时被调用，内部调用的语法是： ExecuteCompleteRecordsAffected, pError, adStatus, pCommand, pRecordSet, pConnection 各参数的含义： • RecordsAffected：命令所影响的记录数目 • pError、adStatus、pConnection：与BeginTransComplete相同 • pCommand：Command对象的一个引用 • pRecordSet：命令执行结果集的一个引用

Connection对象 (4) InfoMessage事件在当前连接收到警告信息时被调用，内部调用的语法是： InfoMessagepError, adStatus, pConnection (5) WillConnect事件在连接启动之前被调用。当调用该事件时，ConnectionString、UserID、Password和Options参数设置为引发该事件的操作所建立的值。当取消该事件时，将调用ConnectComplete，并将adStatus设置为adStatusErrorsOccurred。 WillConnectConnectionString, UserID, Password, Options, adStatus, pConnection

Connection对象 各参数的含义： • adStatus：如果引发事件的操作无法请求取消，设置为adStatusCantDeny。将该参数设置为adStatusCancel以请求引起取消该通知的连接操作。 (6) WillExecute事件在对连接执行挂起命令之前调用，并允许用户检查和修改挂起的执行参数。该事件可返回取消挂起命令的请求。 WillExecute Source, CursorType, LockType, Options, adStatus, pCommand, pRecordSet, pConnection

Connection对象 各参数的含义： • Source：SQL命令或存储过程名称或执行的命令 • CursorType：指出记录集中使用的游标类型，取值参见RecordSet对象的对应属性。如果为adOpenUnspecified，则不可对其进行修改 • LockType：指定锁类型，用于将要打开的记录集。如果设置为adLockUnspecified，则不可进行修改 • adStatus、pCommand、pRecordSet、pConnection：与WillConnect相同

Command对象 15.2.3 ADO中的Command对象 Command对象用来执行命令并返回结果集。可使用Command对象的集合、方法、属性进行下列操作： • 使用CommandText属性定义命令 • 通过Parameter对象和集合定义参数化查询或者存储过程参数 • 使用Execute方法执行命令并返回结果集 • 执行前应使用CommandType属性指定命令类型以优化性能 • 使用CommandTimeout设置等待时间 • 设置ActiveConnection关联连接对象 • 设置Name将Command标识为与Connection对象关联的方法 • 将Command对象传送给RecordSet的Source属性以便获取数据

Command对象 1. Command对象的常用属性 (1) ActiveConnection属性该属性指示指定的Command对象当前所属的Connection对象。 (2) CommandText属性用于设置命令文本。 (3) CommandTimeout属性用于设置等待时间。默认30秒。

Command对象 (4)CommandType属性用于说明执行命令的类型。

Command对象 (5) Name属性该属性指出对象的名称。 (6) Parameters属性包含要传递给存储过程或参数化查询的参数。 (7) Prepared属性取值为True时，表明命令执行前将被优化。 (8) State属性 Command对象的当前状态。

Command对象 2. Command对象的常用方法 (1) Cancel方法取消由Execute方法引起的异步任务。 (2) CreateParameter方法创建新的Parameter对象并加入到Parameters集合中。需要指定参数的名称、类型、说明、大小和值。其语法如下： Command对象.CreateParameter(Name, Type, Direction, Size, Value)

第 15 章用 ADO 操作 SQL Server 数据库