Mastering ADO Programming: Accessing and Manipulating Database Data with ADO by Jing Min Quan

Advance topic :Write your program with ADO Jing Min Quan(井民全) National Chiao Tung University

Introduction • ADO = ActiveX Data Objects • Access and manipulate data from a database server • Benefits: • ease of use • high speed • low memory • key features • building client/server • Web-based applications.

Prerequisites • Tool->Options->Directories-> 1. [Show Directories for]: Library files 2. [Directories]: C:\program files\common files\system\ado

Setup the Data Source [系統管理工具]->資料來源 (ODBC) [系統資料來源名稱]-> 新增; Access mdb [選取資料庫]->指定檔案路徑

How to program– Read the Data from Database • 1. Import the ADO library #import <msado15.dll> rename( "EOF", "adoEOF" ) • 2. Initial OLE struct InitOle { InitOle() { ::CoInitialize(NULL); } ~InitOle() { ::CoUninitialize(); } } _init_InitOle_;

How to program– Read the Data from Database • Connect to the Database ADODB::_ConnectionPtr Conn1 = NULL; CREATEINSTANCE(Conn1,ADODB::Connection) _bstr_t bstrAccessConnect ( L"DSN=AdoDemo;UID=;PWD=;" ); Conn1->ConnectionString = bstrAccessConnect; Conn1->Open( bstrEmpty, bstrEmpty, bstrEmpty, -1 ); 設定系統資料庫名稱, username,passwd 開啟一個 synchronously 連線

How to program– Read the Data from Database • Create a Record Object ADODB::_RecordsetPtr rs("ADODB.Recordset"); rs->Open("Authors", //開啟Authors資料表 _variant_t((IDispatch *) Conn1, true), // ADO is a IDispatch interface ADODB::adOpenStatic, // 指定records為原資料的static copy. ADODB::adLockReadOnly, // 無法更改record 資料 ADODB::adCmdTable); // 指定Authors是資料表

How to program– Read the Data from Database rs->MoveFirst(); while ( rs->adoEOF == VARIANT_FALSE ) { long int xx= rs->Fields->GetItem( _variant_t( 0L ) )->Value; _bstr_t KK=rs->Fields->Item[1L]->Value; char *cString=(char*) KK; rs->MoveNext(); } 取得Au_ID欄位取得Name欄位 Move to next record

How to program– Read the Data from Database • Close the Connection rs->Close(); Conn1->Close(); Ex:RecordSet.cpp

How to program– Append the Data into Database • Create a Recordset which can be updated. ADODB::_RecordsetPtr rs("ADODB.Recordset"); rs->Open("Authors", _variant_t((IDispatch *) Conn1, true), ADODB::adOpenStatic, ADODB::adLockReadOnly, // 無法更改record 資料 ADODB::adCmdTable); ADODB::_RecordsetPtr rs("ADODB.Recordset"); rs->Open("Authors", //開啟Authors資料表 _variant_t((IDispatch *) Conn1, true), // ADO is a IDispatch interface ADODB::adOpenStatic, // 指定records為原資料的static copy. ADODB::adLockOptimistic, ADODB::adCmdTable); // 指定Authors是資料表

How to program– Append the Data into Database • Insert the data 看看這個Recordset 是否支援AddNew rs->MoveFirst(); if( rs->Supports(ADODB::adAddNew)) { // 若rs 支援AddNew這個功能 rs->AddNew(); rs->Fields->Item["Au_ID"]->Value=(long)1234; rs->Fields->Item[1L]->Value="自強基金會"; rs->Update(); } rs->Close(); Conn1->Close(); 把資料update到資料庫中實際的寫入資料庫

Reference • Get the Help • http://www.microsoft.com/data/ado/ • Download the MADC 2.6 SDK • http://www.microsoft.com/data/download.htm

Mastering ADO Programming: Accessing and Manipulating Database Data with ADO by Jing Min Quan