How To Persist/Load OLE DB Rowset to/from XML with ATL OLE DB Consumer Template Classes (262447)

MORE INFORMATION

The sample code provided in this section shows How To

• persist an OLE DB rowset to an XML file.
• load OLE DB rowsets from an XML file

The way to persist an OLE DB rowset to an external XML file is shown in the Rowset2Xml() function, which takes a rowset and transfers it to an ADO recordset object. It then uses the Save() method on the recordset object to save the rowset to an external XML file.

Loading of OLE DB rowsets from an external XML file is demonstrated in the Xml2Rowset() function, which creates an ADO recordset object and uses the Open method to open the file. Then it retrieves the rowset out of the recordset object by using the get_Rowset() method of the IADORecordsetConstruction interface.

The code uses a Microsoft SQL Server datasource. To run the sample, you can use the following script to create a table "books" in the "pubs" database.

CREATE TABLE Books (
	id int IDENTITY (1, 1) NOT NULL ,
	Title char(50) ,
	Publisher char(50) 
) ON [PRIMARY]
GO
INSERT INTO books(Title, Publisher) VALUES ('Debugging applications','MS Press')
GO
INSERT INTO books(Title, Publisher) VALUES ('Inside SQL Server 7.0','MS Press')
GO
				

The following is the code for the sample:

#include "atldbcli.h"
#import "C:\Program files\Common Files\System\Ado\msado15.dll" no_namespace  rename("EOF", "ADOEOF")

class CdboBooksAccessor
{
public:
	LONG m_id;
	TCHAR m_Title[51];
	TCHAR m_Publisher[51];

BEGIN_COLUMN_MAP(CdboBooksAccessor)
COLUMN_ENTRY(1, m_id)
COLUMN_ENTRY(2, m_Title)
COLUMN_ENTRY(3, m_Publisher)
END_COLUMN_MAP()
		
DEFINE_COMMAND(CdboBooksAccessor, _T(" \ 
			 SELECT \ 
			 id, \ 
			 Title, \ 
			 Publisher  \ 
			 FROM dbo.Books"))
};

class CdboBooks : public CCommand<CAccessor<CdboBooksAccessor> >
{

	HRESULT Open()
	{
		HRESULT		hr;
		
		hr = OpenDataSource();
		if (FAILED(hr))
			return hr;
		
		return OpenRowset();
	}
	HRESULT OpenDataSource()
	{
		HRESULT		hr;
		CDataSource db;
		CDBPropSet	dbinit(DBPROPSET_DBINIT);
		
		dbinit.AddProperty(DBPROP_AUTH_PERSIST_SENSITIVE_AUTHINFO, false);
		dbinit.AddProperty(DBPROP_AUTH_USERID, OLESTR("sa"));
		dbinit.AddProperty(DBPROP_INIT_CATALOG, OLESTR("pubs"));
		dbinit.AddProperty(DBPROP_INIT_DATASOURCE, OLESTR("vcdb01"));
		dbinit.AddProperty(DBPROP_INIT_LCID, (long)1033);
		dbinit.AddProperty(DBPROP_INIT_PROMPT, (short)4);
		hr = db.Open(_T("SQLOLEDB.1"), &dbinit);
		if (FAILED(hr))
			return hr;
		
		return m_session.Open(db);
	}
	HRESULT OpenRowset()
	{
		return CCommand<CAccessor<CdboBooksAccessor> >::Open(m_session);
	}
	CSession	m_session;
};


HRESULT Rowset2Xml();   //OLEDB rowset -> external xml file
HRESULT Xml2Rowset();   //xml file -> OLEDB rowset
void dump_error(_com_error &e);

void main()
{
	HRESULT hr;
	CoInitialize(NULL);
	
	try
	{
		hr = Rowset2Xml();
		hr = Xml2Rowset();		
	}
	catch(_com_error &e)
	{
		dump_error(e);
	}	
}

HRESULT Rowset2Xml()
{
	HRESULT hr = S_OK;
	
	CdboBooks cmdBooks;
	hr = cmdBooks.Open();
	
	ADORecordsetConstructionPtr spADORsConst;
	_RecordsetPtr spRs(__uuidof(Recordset));
	spADORsConst = spRs;
	
	hr = spADORsConst->put_Rowset((LPUNKNOWN)(cmdBooks.m_spRowset));
	spRs->Save("books.xml", adPersistXML);
	
	return hr;
}
HRESULT Xml2Rowset()
{
	HRESULT hr = S_OK;
	
	CdboBooks cmdBooks;
	hr = cmdBooks.OpenDataSource();
	
	//load the ADO recordset
	_RecordsetPtr spRs(__uuidof(Recordset));
	hr = spRs->Open("books.xml","Provider=MSPersist;",adOpenForwardOnly,adLockReadOnly,adCmdFile);
	
	ADORecordsetConstructionPtr spADORsConst;
	//QI for ADORecordsetConstruction interface
	spADORsConst = spRs;
	
	//Create CCommand
	hr = cmdBooks.CreateCommand(cmdBooks.m_session);
	
	//hook up the rowset with ADO Recordset
	spADORsConst->get_Rowset((LPUNKNOWN*)&(cmdBooks.m_spRowset));
	
	//in order to retrieve data, we have to bind 
	hr = cmdBooks.Bind();
	
	//test the CCommand object out
	while( cmdBooks.MoveNext() == S_OK)
	{
		printf("%d   %s        %s\n", cmdBooks.m_id, cmdBooks.m_Title, cmdBooks.m_Publisher);
	}
	
	return hr;
}

void dump_error(_com_error &e)
{
	_bstr_t bstrSource(e.Source());
	_bstr_t bstrDescription(e.Description());
	
	// Print Com errors.  
	printf("Error\n");
	printf("\tCode = %08lx\n", e.Error());
	printf("\tCode meaning = %s", e.ErrorMessage());
	printf("\tSource = %s\n", (LPCSTR) bstrSource);
	printf("\tDescription = %s\n", (LPCSTR) bstrDescription);	
}

				

Notes concerning the code:

• For simplicity, the code does not check all of the returned HRESULT. It's a good practice to check the HRESULT and use CDBErrorInfo/AtlTraceErrorRecords() to get back more descriptive errors.
• In Rowset2Xml()function,

  hr = cmdBooks.Open();
  						

  is called to create the CCommand and execute the select statement, and then retrieve the CRowset associated with this CCommand. Alternatively, you can explicitly get the IRowset back by calling Execute as follows:

  CCommand<CAccessor<CdboBooksAccessor> > cmd;
  	
  hr = cmd.CreateCommand(session);
  CComBSTR bsSQLsmd=OLESTR("SELECT * FROM Books"); 
  CComPtr<ICommandText> spCommandText;
  hr = cmd.m_spCommand->QueryInterface(&spCommandText);
  
  LONG    cRowsAffected;
  
  if (SUCCEEDED(hr))
    hr = spCommandText->SetCommandText(DBGUID_SQL, bsSQLsmd);
  
  CComPtr<IRowset> pIRowset = NULL;
  hr = cmd.m_spCommand->Execute(NULL, IID_IRowset, NULL, &cRowsAffected, (IUnknown**)&pIRowset);