load offline recordset to database via ADO

// Code snippet to demonstrate loading offline recordset into database

// By Onega (www.fruitfruit.com)

// 2006/05/30

// VC++ 2005, MSDE 2000 SP4, Windows XP SP2, ADO 2.8

// reference

// http://support.microsoft.com/kb/q231351/

// DOC: ADO VC Tutorial in MSDN Has Compile/Run-Time Errors



#include "stdafx.h"



/////////////need to put the following lines in stdafx.h



////ADO 2.6 type library

//#import "libid:00000206-0000-0010-8000-00AA006D2EA4" named_guids, rename ("EOF", "EndOfFile")

//#include <sstream>

//#include <string>

//#include <iostream>

////////////end modification to stdafx.h

struct InitOle

{

InitOle() { ::CoInitialize(NULL); }

~InitOle() { ::CoUninitialize(); }

} _init_InitOle_;

void PrintProviderError(ADODB::_ConnectionPtr pConnection);

using namespace ADODB;

int _tmain(int argc, _TCHAR* argv[])

{

ADODB::_ConnectionPtr Conn1;

ADODB::_RecordsetPtr Rs1;

_variant_t vtEmpty (DISP_E_PARAMNOTFOUND, VT_ERROR);

_variant_t vtEmpty2 (DISP_E_PARAMNOTFOUND, VT_ERROR);

_bstr_t bstrConnect( L"Provider=SQLOLEDB;Server=(local);Database=sc20;UID=sa;PWD=Onega;" );

LPCTSTR xml_file_name = _T("c:\\Onega_tmp_file.xml");

_bstr_t current_key;

try

{

_bstr_t bstrEmpty;

Conn1.CreateInstance( __uuidof( ADODB::Connection ) );

Conn1->ConnectionString = bstrConnect;

Conn1->Open( bstrConnect, bstrEmpty, bstrEmpty, -1 );

Rs1.CreateInstance(__uuidof(ADODB::Recordset));



//_tremove(xml_file_name);

Rs1->put_CursorLocation(ADODB::adUseClient);

Rs1->Open( _T("select Name, Value, TimeStamp, (DeleteFlag+1) as deleteflag from config2 order by name"),

_variant_t((IDispatch *) Conn1, true),

ADODB::adOpenStatic,

ADODB::adLockReadOnly,

ADODB::adCmdText);

Rs1->Save(xml_file_name,adPersistXML);

Rs1->Close();

// load xml to recordset

_RecordsetPtr ptr_rst_in;

ptr_rst_in.CreateInstance(__uuidof(Recordset));

_variant_t vtMissing((long)DISP_E_PARAMNOTFOUND,VT_ERROR);

HRESULT hr=ptr_rst_in->Open(xml_file_name,vtMissing,adOpenStatic,adLockBatchOptimistic,adCmdFile);

_RecordsetPtr ptr_rst_update;

ptr_rst_update.CreateInstance(__uuidof(Recordset));

ptr_rst_update->CursorLocation = adUseClient;

ptr_rst_update->Open( _T("select * from config2"),

_variant_t((IDispatch *) Conn1, true),

ADODB::adOpenDynamic,

ADODB::adLockBatchOptimistic,

ADODB::adCmdText);

//key1_name, key2_name are name of primary key fields

_bstr_t key1_name = _T("Name");

_bstr_t key2_name = _T("Value");

bool db_eof = false;

std::basic_stringstream<TCHAR> ts;

while(!(ptr_rst_in->EndOfFile))

{

//insert

current_key = ptr_rst_in->Fields->Item[key1_name]->Value;

ts.str(_T(""));

if(key1_name.length()>0)

{

ts <<(LPCTSTR)key1_name <<_T("='") << (LPCTSTR)(_bstr_t)ptr_rst_in->Fields->Item[key1_name]->Value<< _T("' ");

}

if(ts.str().length()>0)

ts<<_T(" AND ");

if(key2_name.length()>0)

{

ts<<(LPCTSTR)key2_name <<_T("='") << (LPCTSTR)(_bstr_t)ptr_rst_in->Fields->Item[key2_name]->Value<< _T("' ");

}

_bstr_t filter = ts.str().c_str();

ptr_rst_update->Filter = filter;

FieldsPtr update_fields =ptr_rst_update->Fields;

FieldsPtr in_fields =ptr_rst_in->Fields;



long nFieldCount = 0;

//initial all fields to null, because recordset load from xml may not use all fields

if(ptr_rst_update->EndOfFile)

{

ptr_rst_update->AddNew();

printf("add new record:%s\n", (LPCSTR)current_key);

}

else

{

printf("update record:%s\n", (LPCSTR)current_key);

nFieldCount = update_fields->GetCount();

_variant_t var_empty;

for(long i = 0; i< nFieldCount; i++)

{

update_fields->Item[i]->Value = var_empty;

}

}

//copy value to database

nFieldCount = in_fields->GetCount();

for(long i = 0; i < nFieldCount; i++)

{

_bstr_t field_name = in_fields->Item[i]->Name;

_variant_t field_value = in_fields->Item[i]->Value;

update_fields->Item[field_name]->Value = field_value;

}

ptr_rst_update->Update();

ptr_rst_in->MoveNext();

}

ptr_rst_update->Filter = (long) adFilterNone;

ptr_rst_update->UpdateBatch(adAffectAll);

ptr_rst_in->Close();

ptr_rst_update->Close();

Conn1->Close();

}

catch(_com_error &e)

{

_bstr_t bstrSource(e.Source());

_bstr_t bstrDescription(e.Description());

printf("exception code:%08x\n", e.Error());

printf(("\nCOM error occurred, Source : %s \n Description : %s \n"),(LPCSTR)bstrSource,(LPCSTR)bstrDescription);



PrintProviderError(Conn1);

}

printf( "Program finished, welcome to www.fruitfruit.com");

printf(" Press any key to exit");

system("pause");

return 0;

}



VOID PrintProviderError(ADODB::_ConnectionPtr pConnection)

{

// Print Provider Errors from Connection object.

// pErr is a record object in the Connection's Error collection.

ADODB::ErrorPtr pErr = NULL;

long nCount = 0;

long i = 0;



if( (pConnection->Errors->Count) > 0)

{

nCount = pConnection->Errors->Count;

// Collection ranges from 0 to nCount -1.

for(i = 0; i < nCount; i++)

{

pErr = pConnection->Errors->GetItem(i);

printf(("\n\t Error number: %x\t%s"), pErr->Number, (LPCSTR)pErr->Description);

}

}

}