Using Stored Procedures with ATL

Create Procedure spGetImage
@id int
As
SELECT ImageId, ImageDescription, ImagePath
FROM tblImages
WHERE ImageId = @id


Often I see the will card * used rather than typing out the column names. Specify column names will create a tightly defined interface with your database, while using * will cause any changes in the table structure to break the client. So don’t be lazy, type out your SELECT statement.

1. Insert and ATL object and choose Data Consumer

2. Select a datasoure for your object

3. Create a connection string to your database

4. Choose the strored procedure

5. Choose a proper name for your object

The object wizrard will provide you wiht the following code: First a custom class based of the structure spGetImage that is used by the CAccessor Template which in turn is used by the CCommand template class. Notice the Parameter map and define command macros that constitute much of the custom assessor class.

class CspGetImageAccessor
{
public:
	LONG m_RETURNVALUE;
	LONG m_id;
	
BEGIN_PARAM_MAP(CspGetImageAccessor)
	SET_PARAM_TYPE(DBPARAMIO_OUTPUT)
	COLUMN_ENTRY(1, m_RETURNVALUE)
	SET_PARAM_TYPE(DBPARAMIO_INPUT)
	COLUMN_ENTRY(2, m_id)
END_PARAM_MAP()

DEFINE_COMMAND(CspGetImageAccessor, _T("{ ? = CALL dbo.spGetImage;1 (?) }"))

	void ClearRecord()
	{
		memset(this, 0, sizeof(*this));
	}
};

class CspGetImage : public CCommand < CAccessor < CspGetImageAccessor > >
{
public:
	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_INIT_PROMPT, (short)4);
		dbinit.AddProperty(DBPROP_INIT_PROVIDERSTRING, OLESTR("DRIVER=SQL Server;SERVER=****;UID=sa;PWD=pwd;DATABASE=OnlineSchool;Network=DBMSSOCN;Address=****"));
		dbinit.AddProperty(DBPROP_INIT_LCID, (long)1033);
		hr = db.Open(_T("MSDASQL"), &dbinit);
		if (FAILED(hr))
			return hr;

		return m_session.Open(db);
	}
	HRESULT OpenRowset()
	{
		return CCommand < CAccessor < CspGetImageAccessor > > ::Open(m_session);
	}
	CSession m_session;
};

...
	LONG m_RETURNVALUE;
	LONG m_id;

	LONG m_ImageId;	
	TCHAR m_ImageDescription[31];
	TCHAR m_ImagePath[101];	
BEGIN_COLUMN_MAP(CspGetImageAccessor)
	COLUMN_ENTRY(1, m_ImageId)
	COLUMN_ENTRY(2, m_ImageDescription)
	COLUMN_ENTRY(3, m_ImagePath)
END_COLUMN_MAP()

BEGIN_PARAM_MAP(CspGetImageAccessor)
	SET_PARAM_TYPE(DBPARAMIO_OUTPUT)
	COLUMN_ENTRY(1, m_RETURNVALUE)
	SET_PARAM_TYPE(DBPARAMIO_INPUT)
	COLUMN_ENTRY(2, m_id)
END_PARAM_MAP()

...

//IDL

[id(1), helpstring("method GetImageRecord")] HRESULT GetImageRecord([in]  LONG id,  [in, out] BSTR * path, [in, out] BSTR * disc);

//CPP

STDMETHODIMP CIDLTempObj::GetImageRecord(LONG id, BSTR * path, BSTR  *disc)
{
	CspGetImage tempDB;
	HRESULT hr ;
	tempDB.m_id = id;
	hr = tempDB.Open();
	if(FAILED(hr))
	{
		return E_FAIL;
	}
	hr = tempDB.MoveFirst();
	if(FAILED(hr))
	{
		return E_FAIL;
	}
	CComBSTR tempbstr = tempDB.m_ImagePath;
	tempbstr.CopyTo(path);
	tempbstr = tempDB.m_ImageDescription;
	tempbstr.CopyTo(disc); 	
	tempDB.Close(); tempDB.m_session.Close();
	 	
	return S_OK;
}