09-Excel VBA 学习总结 - 通用ADO数据访问模型.docx

Excel VBA 学习总结 - 通用ADO数据访问模型ADO是基于OLE DB的数据访问技术。它不直接与数据交互，而是把这个任务交给了OLE DB，这么做带来了相当好的可扩展性和适应性。它提供了编程语言和统一数据访问方式；它允许开发人员编写访问数据的代码而不用关心数据库是如何实现的，而只用关心数据库的连接。由于ADO是基于COM实现的，所以基本上任何语言都可以使用这种数据访问技术，VBA也不例外。一、ADO对象模型ADO对象库中主要有9个对象，即：Connection、Command、Recordset、Record、Field、Error、Property、Parameter和Stream。呈现的形式基本是5大可以独立创建的基本对象，4大对象集合，如下图所示。这9个对象中以Connection、Command、Recordset是最为常用的，很多情况下只需要这三个对象即可完成数据的读取和操作，对于一些很简单的应用，甚至使用它们中的任何一个就可以独立完成任务。下面是各个对象/集合的简略说明：Connection：代表与数据源的连接与操作环境，基本任何的操作都是针对特定的Connection完成的。常用属性：ConnectionString(最重要)，ConnectionTimeout，State(连接的状态)，Errors常用方法：Open，Execute，Close常用事件：ExecuteComplete，ConnectComplete虽然Command对象与Recordset对象都可以在需要的时候自己隐式的创建一个Connection对象，但是对于需要多次执行命令或查询的场景，还是需要提供一个公共的Connection对象(这个对象的创建与销毁都是需要时间的)来共用。而且对于同一个连接字符串，ADO会采用连接池(存放Connection对象)的方式减少资源的浪费。ConnectionString中主要需要设置Provider，Data Source，Initial Catalog，User ID，Password，Integrated Security等值，这些大家应该都很熟悉了。当然有些值(像Provider，Mode这种)也可以在Connection的相关属性中设置。Connection需要先执行Open方法打开后，才能Execute一些命令，最后需要使用Close关闭(通常为了保险起见，需要先检查State，再关闭)以释放资源。Command：代表执行的添加、修改，删除、查询数据源的命令。常用属性：ActiveConnection(设置连接字符串)，CommandText(核心属性)，CommandType，Parameters(一般是存储过程的参数)常用方法：CreateParameter，Execute(可以有返回值)。Recordset：代表执行查询命令后的结果集。常用属性：ActiveConnection(设置连接字符串)，BOF，EOF，CursorLocation，Filter，Sort，State，Fields(返回数据的类型)常用方法：Open，Close，Move，MoveFirst，MoveNext，Find，NextRecordset通常配合Range对象的CopyFromRecordset方法获取数据，但是这个方法只能获得值，一般需要匹配Fields属性获取列信息。如果需要精确控制每行每列的值，可以使用RecordCount，Fields以循环的形式获取结果集中的每个值。Fori=1Torst.RecordCountForj=0Torst.Fields.Count-1Sheet1.Cells(i+1,j+1)=rst.Fields(j)Nextjrst.MoveNextNexti如果返回的结果集为空，则BOF与EOF都为True。通常它们也可以配合Fields精确控制结果集中的每个值。Find方法基本上是支持与SQL中Where语句基本相同的语法。Error与Errors：代表与数据源相关的操作的详细错误信息，Errors是Connection对象的属性。Parameter与Parameters：代表基于参数化查询或存储过程的 Command 对象相关联的参数或自变量，Parameters是Command对象的属性。Field与Fields：代表使用普通数据类型的数据的列，Fields是Recordset对象的属性。Property与Properties：代表ADO 对象的描述或控制对象的行为，分为内置属性(通过对象直接调用)和动态属性(通过集合使用 MyObject.Properties(0) 或 MyObject.Properties(Name) 语法来引用)。Record：代表记录集中的一行、或文件系统的一个文件或一个目录。Stream：用于读写以及处理二进制数据或文本流。二、ADO编程模型ADO 的目标是访问、编辑和更新数据源，而编程模型体现了为完成该目标所必需的系列动作的顺序。ADO提供类和对象完成以下活动： 连接到数据源 (Connection对象)，并可选择开始一个事务。 可选择创建对象来表示 SQL 命令 (Command)。 可选择在 SQL 命令中指定列、表和值作为变量参数 (Parameter)。 执行命令 (使用Command、Connection 或 Recordset对象完成)。 如果命令按行返回，则将行存储在缓存中 (使用Recordset对象)。 可选择创建缓存视图，以便能对数据进行排序、筛选和定位 (使用Recordset对象)。 通过添加、删除或更改行和列编辑数据 (使用Recordset对象)。 在适当情况下，使用缓存中的更改内容来更新数据源 (使用Recordset对象)。 如果使用了事务，则可以接受或拒绝在完成事务期间所作的更改；结束事务 (Connection)。 释放相关对象(通常是把对象设为Nothing，别忘了Set关键字)。不管是否显式的使用了Connection对象，这个对象在整个访问数据库的过程中，是始终存在的。其实除了连接字符串以及数据库特有的一些特性(比如SqlServer支持存储过程)外，访问各种数据库的基本流程和处理的语句，包括SQL语句都是差不多的。三、ADO实践ADO对象创建对于COM对象的创建方式，大家应该很熟悉了，有两种方式：后期绑定：使用CreateObject方法。DimcnnAsObject,rstAsObjectSetcnn=CreateObject(ADODB.Connection)Setrst=CreateObject(ADODB.Recordset)前期绑定：先引用“Microsoft ActiveX Data Objects 2.x Library”(尽量选择高版本)，然后直接就可以使用了。下面两种写法都可以：Dimcnn1AsADODB.ConnectionSetcnn1=NewADODB.ConnectionDimcnn2AsNewADODB.Connection前期绑定能更好的利用VBE(或者说是VBIDE)的Intellisense。使用ADO访问各种类型的数据库，基本上除了连接字符串不同，专有特性不同，使用ADO的其它过程基本都差不多，所以下面几种类型数据文件的操作，重点都是介绍各自不同的地方。使用ADO访问Access中的数据连接字符串示例：Provider和DataSource是必须的Mode控制访问数据库方式，本例中是排他访问PublicConstAccessConnectionAsString=_Provider=Microsoft.ACE.OLEDB.12.0;&_DataSource=C:FilesNorthwind2007.accdb;&_Mode=ShareExclusive;&_UserID=Admin;&_Password=password在本例中，使用的是Access 2007，所以使用的Provider是“Microsoft.ACE.OLEDB.12.0”。下面是一个伪代码例子，基本上涵盖了所有的步骤：PublicSubPlainTextQuery()省去了定义的部分sConnect=Provider=Microsoft.ACE.OLEDB.12.0;&_DataSource=C:FilesNorthwind2007.accdbsSQL=SELECT.SetrsData=NewADODB.RecordsetrsData.OpensSQL,sConnect,adOpenForwardOnly,adLockReadOnly,adCmdTextIfNotrsData.EOFThenSheet1.Range(A2).CopyFromRecordsetrsDatarsData.CloseWithSheet1.Range(A1:B1).Value=Array(Company,ContactName).Font.Bold=TrueEndWith调整列宽Sheet1.UsedRange.EntireColumn.AutoFitElsersData.CloseMsgBoxError:Norecordsreturned.,vbCriticalEndIf销毁对象SetrsData=NothingEndSub像上面所说那样，解决一个问题，可以使用不同的对象。这里仅使用Recordset就解决了所有的问题，但是当需要使用Connection或者Command对象的时候，毫不犹豫的使用它们。时刻谨记，对象的创建是需要开销的，当可以重用一个对象的时候(例如Command对象，当执行不同的命令时，只需要把CommandText换掉就可以了)，毫不犹豫的使用它。使用Insert，Update，Delete语句后，可以查询影响的行数来确定操作是否成功了，如果失败了，可以使用Err.Raise来引发一个错误。Access中支持在数据库中产生和存储SQL语句，想了解更多的可以参看其它资料。SQL语句中，可以使用IIF简单的进行一些替换处理。使用ADO访问SqlServer中的数据连接字符串示例：常见的两种SqlServer连接字符串形式PublicConstSQLConnection1AsString=_Provider=SQLOLEDB;&_DataSource=ComputerNameSQLServerName;&_InitialCatalog=Northwind;&_UserID=User;Password=password;&_NetworkLibrary=dbmssocnPublicConstSQLConnection2AsString=_Provider=SQLOLEDB;&_DataSource=ComputerNameSQLServerName;&_InitialCatalog=Northwind;&_IntegratedSecurity=SSPI除了连接字符串外，Sqlserver由于有存储过程(Stored Procedures)，所以在Command对象的CommandText中就可以放入存储过程的名字，然后使用CreateParameter方法创建参数，并通过Parameters.Append方法加入到参数集合中使用。这种调用方式效率比较高，当多次需要调用一个存储过程的时候，推荐这么使用。SetgobjCmd=NewADODB.CommandSetgobjCmd.ActiveConnection=gobjConn与文本形式的SQL语句最大的不同就是这里gobjCmd.CommandText=spInsertShippersgobjCmd.CommandType=adCmdStoredProc添加参数，注意第一个参数总是返回值gobjCmd.Parameters.Append_gobjCmd.CreateParameter(RETURN_VALUE,adInteger,_adParamReturnValue,0)gobjCmd.Parameters.Append_gobjCmd.CreateParameter(CompanyName,adVarWChar,_adParamInput,40)gobjCmd.Parameters.Append_gobjCmd.CreateParameter(Phone,adVarWChar,_adParamInput,24)给参数赋值gobjCmd.Parameters(CompanyName).Value=AirCarriersgobjCmd.Parameters(Phone).Value=(206)555-1212执行存储过程gobjCmd.ExecuteRecordsaffected:=lNumAffected,Options:=adExecuteNoRecords验证信息IflNumAffected1ThenErr.RaiseNumber:=vbObjectError+1024,_Description:=ErrorexecutingCommandobject.获取返回的主键信息lKeyValue=gobjCmd.Parameters(RETURN_VALUE).ValueDebug.PrintThekeyvalueofthenewrecordis:&CStr(lKeyValue)由于存储过程会作为Connection的动态方法存在，所以简单使用存储过程，特别是只使用很少次的时候，也可以像下面例子这样，直接调用，不过这种方式效率比较低，还是推荐使用上面Command包装的调用方法。objConn.spGetCustomerNamesUK,rsData需要注意的是无论何种情况下，Parameters集合的第一个元素都是代表存储过程的返回值，不管实际上这个存储过程有没有返回值。Sqlserver是支持返回多个结果集的，下面是典型的结果集操作：假设这个存储过程只返回2个结果集DoWhileNotrsData.EOF执行相关赋值rsData.MoveNextLoop切换不同的数据集SetrsData=rsData.NextRecordsetDoWhileNotrsData.EOF执行相关赋值，例如给下拉列表赋值ddShippers.AddItemrsData.Fields(1).ValuersData.MoveNextLoop这里注意使用NextRecordset关闭和销毁了Recordset，不需要额外的操作了SetrsData=rsData.NextRecordset使用Connection的时候，通常的目标是尽快访问数据库，尽快再关闭连接。然而很多时候这些数据保存在Recordset中一直使用是可以提高程序效率的，这个时候我们需要的是非连接型的Recordset。这个在ADO中是支持的，你可以从数据库中获得Recordset，然后关闭连接，在内存中保存Recordset并一直使用，然后也可以重新连接更新数据，甚至可以保存Recordset中的数据到别的地方。下面描述的就是一个非连接型Recordset的设置方式：szSQL=SELECTCustomerID,CompanyName,ContactNameFROMCustomers创建非连接型Recordset的步骤1)创建Recordset对象.SetgrsData=NewADODB.Recordset2)游标位置设为Client.grsData.CursorLocation=adUseClient3)游标类型设为Static.grsData.CursorType=adOpenStatic4)锁的类型设为batchoptimistic.grsData.LockType=adLockBatchOptimistic5)打开记录集.grsData.OpenszSQL,gobjConn,adCmdText6)把Recordset的ActiveConnection设为Nothing.SetgrsData.ActiveConnection=NothingSheet1.Range(A1).CopyFromRecordsetgrsData注意上面的grsData是定义为Public类型的：Public grsData As ADODB.Recordset。获得到这个对象以后，就可以使用Filter，Sort等手段，获取需要的数据。不用怀疑，利用ADO去处理下面两种非关系型数据(但是是格式化的数据)那是非常方便的，因为SQL语句是强大的，可以完成很多的查询，筛选功能。使用ADO访问Excel中的数据连接字符串示例：ExcelConnect=Provider=Microsoft.ACE.OLEDB.12.0;&_DataSource=C:FilesSales.xlsx;&_ExtendedProperties=Excel12.0;注意访问Access与Excel的连接字符串中，Provider是相同的；但是Excel需要多加上Extended Properties，这里的版本号随着Excel的版本不同而不同。使用的SQL语句与Access中基本相同，除了以下几点：使用Sheet名字作为表名的时候需要加$符号和方括号如果Sheet名字中有空格的，名字两边可以用单引号括起来，例如：MySheet$sSQL1=SELECT*FROMSheet1$如果表名是Sheet级别的名字，可以把这个表名接在$符号后面sSQL2=SELECT*FROMSheet1$SheetLevelName;如果表名是一个Range范围，也直接加在$符号后面sSQL3=SELECT*FROMSales$A1:E89;