数据库编程总结.doc

数据库编程总结当前各种主流数据库有很多，包括Oracle, MS SQL Server, Sybase, Informix, MySQL, DB2, Interbase / Firebird, PostgreSQL, SQLite, SAP/DB, TimesTen, MS ACCESS等等。数据库编程是对数据库的创建、读写等一列的操作。数据库编程分为数据库客户端编程与数据库服务器端编程。数据库客户端编程主要使用ODBC API、ADO、ADO.NET、OCI、OTL等方法；数据库服务端编程主要使用OLE DB等方法。数据库编程需要掌握一些访问数据库技术方法，还需要注意怎么设计高效的数据库、数据库管理与运行的优化、数据库语句的优化。1访问数据库技术方法数据库编程分为数据库客户端编程与数据库服务器端编程。数据库客户端编程主要使用ODBC API、ADO、ADO.NET、OCI、OTL等方法；数据库服务端编程主要使用OLE DB等方法。1.1几种是数据库访问方法比较ODBC API是一种适合数据库底层开发的编程方法,ODBC API提供大量对数据源的操作,ODBC API能够灵活地操作游标,支持各种帮定选项,在所有ODBC相关编程中,API编程具有最高的执行速度。DAO提供了很好的数据库编程的对象模型.但是,对数据库的所有调用以及输出的数据都必须通过Access/Jet数据库引擎,这对于使用数据库应用程序,是严重的瓶颈。OLE DB提供了COM接口，与传统的数据库接口相比，有更好的健壮性和灵活性，具有很强的错误处理能力,能够同非关系数据源进行通信。ADO最主要的优点在于易于使用、速度快、内存支出少和磁盘遗迹小。ADO.NET 是利用数据集的概念将数据库数据读入内存中，然后在内存中对数据进行操作，最后将数据集数据回写到源数据库中。OTL 是 Oracle, Odbc and DB2-CLI Template Library 的缩写，是一个C+编译中操控关系数据库的模板库， OTL中直接操作Oracle主要是通过Oracle提供的OCI接口进行，进行操作DB2数据库则是通过CLI接口来进行，至于MS的数据库和其它一些数据库，则OTL只提供了ODBC来操作的方式。当然Oracle和DB2也可以由OTL间接使用ODBC的方式来进行操纵。具有以下优点：跨平台；运行效率高，与C语言直接调用API相当；开发效率高，起码比ADO.net使用起来更简单，更简洁；部署容易，不需要ADO组件，不需要.net framework 等。1.2 VC数据库编程几种方法VC数据库编程几种方法，包括ODBC连接、MFC ODBC连接、DAO连接、OLE DB、OLE DB Templates连接、ADO、Oracle专用方法(OCI(Oracle Call Interface)访问、Oracle Object OLE C+ Class Library )。<1.>通用方法 1.2.1 ODBC连接 ODBC(Open DataBase Connectivity)是MSOA的一部分,是一个标准数据库接口。它提供对关系数据库访问的统一接口,实现对异构数据源的一致访问。ODBC数据访问由以下部分组成: <1>句柄(Handles):ODBC使用句柄来标识ODBC环境、连接、语句和描述器. <2>缓存区(Buffers): <3>数据类型(Data types) <4>一致性级别(Conformance levels) 用ODBC设计客户端的一般步骤: <1>分配ODBC环境 <2>分配连接句柄 <3>连接数据源 <4>构造和执行SQL语句 <5>获得查询结果 <6>断开数据源的连接 <7>释放ODBC环境 ODBC API是一种适合数据库底层开发的编程方法,ODBC API提供大量对数据源的操作,ODBC API能够灵活地操作游标,支持各种帮定选项,在所有ODBC相关编程中,API编程具有最高的执行速度.因此,ODBC API编程属于底层编程。 1.2.2 MFC ODBC连接 MFC ODBC是MFC对ODBC进行的封装,以简化对ODBC API的 调用,从而实现面向对象的数据库编程接口. MFC ODBC的封装主要开发了CDatabase类和CRecordSet类 (1) CDatabase类 CDatabase类用于应用程序建立同数据源的连接。CDatabase类中包含一个m_hdbc变量,它代表了数据源的连接句柄。如果要建立CDatabase类的实例,应先调用该类的构造函数,再调用Open函数,通过调用,初始化环境变量,并执行与数据源的连接。在通过Close函数关闭数据源。 CDatabase类提供了对数据库进行操作的函数及事务操作。 (2) CRecordSet类 CRecordSet类定义了从数据库接收或者发送数据到数据库的成员变量,以实现对数据集的数据操作。 CRecordSet类的成员变量m_hstmt代表了定义该记录集的SQL语句句柄,m_nFields为记录集中字段的个数,m_nParams为记录集所使用的参数个数。 CRecordSet的记录集通过CDatabase实例的指针实现同数据源的连接,即CRecordSet的成员变量m_pDatabase. MFC ODBC编程更适合于界面型数据库应用程序的开发,但由于CDatabase类和CRecordSet类提供的数据库操作函数有限,支持的游标类型也有限,限制了高效的数据库开发。在编程层次上属于高级编程。 应用实例：1.打开数据库CDatabase database;database.OpenEx( _T( "DSN=zhuxue" ),CDatabase:noOdbcDialog);/zhuxue为数据源名称2.关联记录集 CRecordset recset(&database);3.查询记录 CString sSql1="" sSql1 = "SELECT * FROM tablename" ; recset.Open(CRecordset:forwardOnly, sSql1, CRecordset:readOnly); int ti=0; CDBVariant var;/var可以转换为其他类型的值 while (!recset.IsEOF() /读取Excel内部数值 recset.GetFieldValue("id",var); jiangxiangti.id=var.m_iVal; recset.GetFieldValue("name", jiangxiangti.name); ti+; recset.MoveNext(); recset.Close();/关闭记录集4.执行sql语句 CString sSql="" sSql+="delete * from 院系审核"/清空表 database.ExecuteSQL(sSql); sSql也可以为Insert ,Update等语句5.读取字段名 sSql = "SELECT * FROM Sheet1" ; /读取的文件有Sheet1表的定义,或为本程序生成的表. / 执行查询语句 recset.Open(CRecordset:forwardOnly, sSql, CRecordset:readOnly); int excelColCount=recset.GetODBCFieldCount();/列数 CString excelfield30; /得到记录集的字段集合中的字段的总个数 for( i=0;i<excelColCount;i+) CODBCFieldInfo fieldinfo; recset.GetODBCFieldInfo(i,fieldinfo); excelfieldi.name =fieldinfo.m_strName;/字段名 6.打开excel文件 CString sDriver = "MICROSOFT EXCEL DRIVER (*.XLS)" / Excel安装驱动 CString sSql,sExcelFile; /sExcelFile为excel的文件路径 TRY / 创建进行存取的字符串 sSql.Format("DRIVER=%s;DSN=''FIRSTROWHASNAMES=1;READONLY=FALSE;CREATE_DB="%s"DBQ=%s",sDriver, sExcelFile, sExcelFile); / 创建数据库 (既Excel表格文件) if( database.OpenEx(sSql,CDatabase:noOdbcDialog) ) /可以把excel作为一个数据库操作 catch(e) TRACE1("Excel驱动没有安装: %s",sDriver); AfxMessageBox("读取失败,请检查是否定义数据区Sheet1"); 1.2.3 DAO连接 DAO(Data Access Object)是一组Microsoft Access/Jet数据库引擎的COM自动化接口.DAO直接与Access/Jet数据库通信.通过Jet数据库引擎,DAO也可以同其他数据库进行通信。DAO还封装了Access数据库的结构单元,通过DAO可以直接修改Access数据库的结构,而不必使用SQL的数据定义语言(DDL)。 DAO的体系结构如下: DAO封装的类： (1)CdaoWorkspace:对DAO工作区(数据库处理事务管理器)的封装 (2)CdaoDatabase:对DAO数据库对象的封装,负责数据库连接. (3)CdaoRecordset:对DAO记录集对象的封装,代表所选的一组记录. (4)CdaoTableDef:对表定义对象的封装,代表基本表或附加表定义. (5)CdaoQueryDef:对查询对象的封装,包含所有查询的定义. (6)CdaoException:DAO用于接收数据库操作异常的类. (7)CDaoFieldExchange DAO提供了很好的数据库编程的对象模型.但是,对数据库的所有调用以及输出的数据都必须通过Access/Jet数据库引擎,这对于使用数据库应用程序,是严重的瓶颈。 DAO相对于ODBC来说，属于高层的数据库接口. 1.2.4 OLE DB连接 OLE DB对ODBC进行了两方面的扩展:一是提供了数据库编程的OLE接口即COM，二是提供了一个可用于关系型和非关系型数据源的接口。 OLE DB提供了COM接口，与传统的数据库接口相比，有更好的健壮性和灵活性，具有很强的错误处理能力,能够同非关系数据源进行通信。 与ODBC API一样,OLE DB也属于底层的数据库编程接口,OLE DB结合了ODBC对关系数据库的操作功能，并进行扩展，可以访问非关系数据库。 OLE DB访问数据库的原理如下: OLE DB程序结构: OLE DB由客户(Consumer)和服务器(Provider)。客户是使用数据的应用程序，它通过OLE DB接口对数据提供者的数据进行访问和控制。OLE DB服务器是提供OLE DB接口的软件组件。根据提供的内容可以分为数据提供程序(Data Provider)和服务提供程序(Service Provider)。 程序结构原理图如下: <1>数据提供程序 数据提供程序拥有自己的数据并把数据以表格的形式呈现给使用者使用. <2>服务提供程序 服务提供程序是数据提供程序和使用者的结合。它是OLE DB体系结构中的中间件,它是OLE DB数据源的使用者和数据使用程序的提供者 <3>数据使用程序 数据使用程序对存储在数据提供程序中的数据进行使用和控制. OLE DB开发程序的一般步骤: <1>初始化COM环境 <2>连接数据源 <3>打开对话 <4>执行命令 <5>处理结果 <6>清除对象 1.2.4.1应用实例：使用OLEDB编写数据库应用程序1概述OLE DB的存在为用户提供了一种统一的方法来访问所有不同种类的数据源。OLE DB可以在不同的数据源中进行转换。利用OLE DB，客户端的开发人员在进行数据访问时只需把精力集中在很少的一些细节上，而不必弄懂大量不同数据库的访问协议。OLE DB是一套通过COM接口访问数据的ActiveX接口。这个OLE DB接口相当通用，足以提供一种访问数据的统一手段，而不管存储数据所使用的方法如何。同时，OLE DB还允许开发人员继续利用基础数据库技术的优点，而不必为了利用这些优点而把数据移出来。2使用ATL使用OLE DB数据使用程序由于直接使用OLE DB的对象和接口设计数据库应用程序需要书写大量的代码。为了简化程序设计，Visual C+提供了ATL模板用于设计OLE DB数据应用程序和数据提供程序。利用ATL模板可以很容易地将OLE DB与MFC结合起来，使数据库的参数查询等复杂的编程得到简化。MFC提供的数据库类使OLE DB的编程更具有面向对象的特性。Viual C+所提供用于OLE DB的ATL模板可分为数据提供程序的模板和数据使用程序的模板。使用ATL模板创建数据应用程序一般有以下几步骤：1）、 创建应用框架2）、 加入ATL产生的模板类3）、 在应用中使用产生的数据访问对象3 不用ATL使用OLE DB数据使用程序利用ATL模板产生数据使用程序较为简单，但适用性不广，不能动态适应数据库的变化。下面我们介绍直接使用MFC OLE DB类来生成数据使用程序。模板的使用OLE DB数据使用者模板是由一些模板组成的，包括如下一些模板，下面对一些常用类作一些介绍。1）、 会话类CDataSource类CDataSource类与OLE DB的数据源对象相对应。这个类代表了OLE DB数据提供程序和数据源之间的连接。只有当数据源的连接被建立之后，才能产生会话对象，可以调用Open来打开数据源的连接。CSession类CSession所创建的对象代表了一个单独的数据库访问的会话。一个用CDataSource类产生的数据源对象可以创建一个或者多个会话，要在数据源对象上产生一个会话对象，需要调用函数Open()来打开。同时，会话对象还可用于创建事务操作。CEnumeratorAccessor类CEnumeratorAccessor类是用来访问枚举器查询后所产生的行集中可用数据提供程序的信息的访问器，可提供当前可用的数据提供程序和可见的访问器。2）、 访问器类CAcessor类CAccessor类代表与访问器的类型。当用户知道数据库的类型和结构时，可以使用此类。它支持对一个行集采用多个访问器，并且，存放数据的缓冲区是由用户分配的。CDynamicAccessor类CDynamicAccessor类用来在程序运行时动态的创建访问器。当系统运行时，可以动态地从行集中获得列的信息，可根据此信息动态地创建访问器。CManualAccessor类CManualAccessor类中以在程序运行时将列与变量绑定或者是将参数与变量捆定。3）、 行集类CRowSet类CRowSet类封装了行集对象和相应的接口，并且提供了一些方法用于查询、设置数据等。可以用Move（）等函数进行记录移动，用GetData()函数读取数据，用Insert()、Delete()、SetData()来更新数据。CBulkRowset类CBulkRowset类用于在一次调用中取回多个行句柄或者对多个行进行操作。CArrayRowset类CArrayRowset类提供用数组下标进行数据访问。4）、 命令类CTable类CTable类用于对数据库的简单访问，用数据源的名称得到行集，从而得到数据。CCommand类CCommand类用于支持命令的数据源。可以用Open()函数来执行SQL命令，也可以Prepare（）函数先对命令进行准备，对于支持命令的数据源，可以提高程序的灵活性和健壮性。在stdafx.h头文件里，加入如下代码。#include <atlbase.h>extern CComModule _Module;#include <atlcom.h>#include <atldbcli.h>#include <atldbsch.h> / if you are using schema templates在stdafx.cpp文件里，加入如下代码。#include <atlimpl.cpp>CComModule _Module;决定使用何种类型的存取程序和行集。获取数据在打开数据源，会话，行集对象后就可以获取数据了。所获取的数据类型取决于所用的存取程序，可能需要绑定列。按以下步骤。1、 用正确的命令打开行集对象。2、 如果使用CManualAccessor，在使用之前与相应列进行绑定。要绑定列，可以用函数GetColumnInfo,如下所示：/ Get the column informationULONG ulColumns = 0;DBCOLUMNINFO* pColumnInfo = NULL;LPOLESTR pStrings = NULL;if (rs.GetColumnInfo(&ulColumns, &pColumnInfo, &pStrings) != S_OK)AfxThrowOLEDBException(rs.m_pRowset, IID_IColumnsInfo);struct MYBIND* pBind = new MYBINDulColumns;rs.CreateAccessor(ulColumns, &pBind0, sizeof(MYBIND)*ulColumns);for (ULONG l=0; l<ulColumns; l+)rs.AddBindEntry(l+1, DBTYPE_STR, sizeof(TCHAR)*40, &pBindl.szValue, NULL, &pBindl.dwStatus);rs.Bind();3、 用while循环来取数据。在循环中，调用MoveNext来测试光标的返回值是否为S_OK，如下所示：while (rs.MoveNext() = S_OK) / Add code to fetch data here / If you are not using an auto accessor, call rs.GetData()4、 在while循环内，可以通过不同的存取程序获取数据。1） 如果使用的是CAccessor类，可以通过使用它们的数据成员进行直接访问。如下所示：2） 如果使用的是CDynamicAccessor 或CDynamicParameterAccessor 类，可以通过GetValue或GetColumn函数来获取数据。可以用GetType来获取所用数据类型。如下所示：while (rs.MoveNext() = S_OK) / Use the dynamic accessor functions to retrieve your / data ULONG ulColumns = rs.GetColumnCount(); for (ULONG i=0; i<ulColumns; i+) rs.GetValue(i); 3） 如果使用的是CManualAccessor,可以指定自己的数据成员，绑定它们。就可以直接存取。如下所示：while (rs.MoveNext() = S_OK) / Use the data members you specified in the calls to / AddBindEntry. wsprintf("%s", szFoo);决定行集的数据类型在运行时决定数据类型，要用动态或手工的存取程序。如果用的是手工存取程序，可以用GetColumnInfo函数得到行集的列信息。从这里可以得到数据类型。4 总结由于现在有多种数据源，想要对这些数据进行访问管理的唯一途径就是通过一些同类机制来实现，如OLE DB。高级OLE DB结构分成两部分：客户和提供者。客户使用由提供者生成的数据。就像其它基于COM的多数结构一样，OLE DB的开发人员需要实现很多的接口，其中大部分是模板文件。当生成一个客户对象时，可以通过ATL对象向导指向一个数据源而创建一个简单的客户。ATL对象向导将会检查数据源并创建数据库的客户端代理。从那里，可以通过OLE DB客户模板使用标准的浏览函数。当生成一个提供者时，向导提供了一个很好的开端，它们仅仅是生成了一个简单的提供者来列举某一目录下的文件。然后，提供者模板包含了OLE DB支持的完全补充内容。在这种支持下，用户可以创建OLE DB提供者，来实现行集定位策略、数据的读写以及建立书签。1.2.4.2应用案例：Visual C+中使用OLE DB读写SQL Server在需要对数据库进行操作时，OLE DB总是被认为是一种效率最高但最难的方法。但是以我最近使用OLE DB的经验看来，OLE DB的效率高则高矣，但却一点都不难。说它难恐怕主要是因为可参考的中文资料太少，为了帮助以后需要接触OLE DB的同行，我撰写了这篇文章。本文包含如下内容：1. OLE DB写数据库；2. OLE DB读数据库；3. OLE DB对二进制数据(text、ntext、image等)的处理。首先来看看对SQL Server进行写操作的代码，有一定VC基础的读者应该可以很顺利地看懂。OLE DB写数据库，就是这么简单！注: 1.以下代码中使用的模板类EAutoReleasePtr<T>与ATL中的CComPtr<T>类似，是一个在析构时自动调用Release的类。CComPtr<T>的代码在ATLBASE.H中定义。2.以下代码均在UNICODE环境下编译，因为执行的SQL语句必须是UNICODE的。设置工程为UNICODE的方法是:首先在project->settings->C/C+的属性页中的Preprocessor中，删除_MBCS写入UNICODE,_UNICODE。然后在link属性页中Category中选择output，在Entry-Point symbol 中添加wWinMainCRTStartup。EAutoReleasePtr<IDBInitialize> pIDBInitialize;HRESULT hResult = ConnectDatabase( &pIDBInitialize, _T("127.0.0.1"), _T(“sa”), _T("password") );if( FAILED( hResult ) ) /失败，可能是因为数据库没有启动、用户名密码错等等 return;EAutoReleasePtr<IOpenRowset> pIOpenRowset;hResult = CreateSession( pIDBInitialize, &pIOpenRowset );if( FAILED( hResult ) ) /出错 return;EAutoReleasePtr<ICommand> pICommand;EAutoReleasePtr<ICommandText> pICommandText;hResult = CreateCommand( pIOpenRowset, &pICommand, &pICommandText );if( FAILED( hResult ) ) /出错 return;hResult = ExecuteSQL( pICommand, pICommandText, _T("USE PBDATA") );if( FAILED( hResult ) ) /如果这里失败，那就是SQL语句执行失败。在此处，就是PBDATA还未创建 return;/ 创建表ExecuteSQL( pICommand, pICommandText, _T("CREATE TABLE 2005_1(Volume real NOT NULL,ID int NOT NULL IDENTITY)") );/ 添加记录ExecuteSQL( pICommand, pICommandText, _T("INSERT INTO 2005_1 VALUES(100.0)") );/.其中几个函数的代码如下:HRESULT ConnectDatabase( IDBInitialize* ppIDBInitialize, LPCTSTR pszDataSource, LPCTSTR pszUserID, LPCTSTR pszPassword ) ASSERT( ppIDBInitialize != NULL && pszDataSource != NULL && pszUserID != NULL && pszPassword != NULL ); UINT uTimeout 15U; / 连接数据库超时（秒） TCHAR szInitStr1024; VERIFY( 1023 >= wsprintf( szInitStr, _T("Provider=SQLOLEDB;Data Source=%s;Initial Catalog=master;User Id=%s;Password=%s;Connect Timeout=%u"), pszDataSource, pszUserID, pszPassword, uTimeout ) ); /Initial Catalog=master指明连接成功后，"USE master"。 EAutoReleasePtr<IDataInitialize> pIDataInitialize; HRESULT hResult = :CoCreateInstance( CLSID_MSDAINITIALIZE, NULL, CLSCTX_INPROC_SERVER, IID_IDataInitialize, ( void* )&pIDataInitialize ); if( FAILED( hResult ) ) return hResult; EAutoReleasePtr<IDBInitialize> pIDBInitialize; hResult = pIDataInitialize->GetDataSource( NULL, CLSCTX_INPROC_SERVER, ( LPCOLESTR )szInitStr, IID_IDBInitialize, ( IUnknown* )&pIDBInitialize ); if( FAILED( hResult ) ) return hResult; hResult = pIDBInitialize->Initialize( ); if( FAILED( hResult ) ) return hResult; * ppIDBInitialize = pIDBInitialize.Detach( ); return S_OK;HRESULT CreateSession( IDBInitialize* pIDBInitialize, IOpenRowset* ppIOpenRowset ) ASSERT( pIDBInitialize != NULL && ppIOpenRowset != NULL ); EAutoReleasePtr<IDBCreateSession> pSession; HRESULT hResult = pIDBInitialize->QueryInterface( IID_IDBCreateSession, ( void* )&pSession ); if( FAILED( hResult ) ) return hResult; EAutoReleasePtr<IOpenRowset> pIOpenRowset; hResult = pSession->CreateSession( NULL, IID_IOpenRowset, ( IUnknown* )&pIOpenRowset ); if( FAILED( hResult ) ) return hResult; * ppIOpenRowset = pIOpenRowset.Detach( ); return S_OK;HRESULT CreateCommand( IOpenRowset* pIOpenRowset, ICommand* ppICommand, ICommandText* ppICommandText ) ASSERT( pIOpenRowset != NULL && ppICommand != NULL && ppICommandText != NULL ); HRESULT hResult; EAutoReleasePtr<ICommand> pICommand; EAutoReleasePtr<IDBCreateCommand> pICreateCommand; hResult = pIOpenRowset->QueryInterface( IID_IDBCreateCommand, ( void* )&pICreateCommand ); if( FAILED( hResult ) ) return hResult; hResult = pICreateCommand->CreateCommand( NULL, IID_ICommand, (IUnknown*)&pICommand ); if( FAILED( hResult ) ) return hResult; EAutoReleasePtr<ICommandText> pICommandText; hResult = pICommand->QueryInterface( &pICommandText ); if( FAILED( hResult ) ) return hResult; * ppICommand = pICommand.Detach( ); * ppICommandText = pICommandText.Detach( ); return S_OK;HRESULT ExecuteSQL( ICommand* pICommand, ICommandText* pICommandText, LPCTSTR pszCommand, LONG* plRowsAffected ) ASSERT( pICommand != NULL && pICommandText != NULL && pszCommand != NULL && pszCommand0 != 0 ); HRESULT hResult = pICommandText->SetCommandText( DBGUID_DBSQL, ( LPCOLESTR )pszCommand ); if( FAILED( hResult ) ) return hResult; LONG lAffected; hResult = pICommand->Execute( NULL, IID_NULL, NULL, plRowsAffected = NULL ? &lAffected : plRowsAffected, ( IUnknown* )NULL ); return hResult;以上就是写数据库的全部代码了，是不是很简单呢？下面再来读的。/ 先用与上面代码中一样的步骤获取pICommand，pICommandText。此处省略HRESULT hResult = pICommandText->SetCommandText( DBGUID_DB