Introduction
This article is Part 1 of a three part series showing how to build a custom data source class
for use with the Ultimate Grid control.
The custom data source will use Oracle Call Interface (OCI) to use tables in an
Oracle database to provide the data for the grid.
In Part 1, we will build the Ultimate Grid control as an external DLL so it can
be included in the data source class.
Part 2 will build the data source class with the emphasis on setting up the OCI
environment, and defining a class with the ability to obtain metadata from the
database so it can access any table regardless of the number of columns or the
data types.
In Part 3, we will put it all together in a sample application that will use the
grid control DLL built in part 1, and the data source class built in part 2, to
develop an application capable of displaying any table in an Oracle database.
Background
The Ultimate Grid is a very powerful grid control capable of displaying data from a variety of sources. There are several data
sources included in the distribution. However, my application required accessing an Oracle database. The Ultimate Grid documentation
shows how to build a custom data source. My concern was the size of the tables I would be accessing so I decided to use the
Oracle Call Interface (OCI) package included with the Oracle database. This gave me the control and flexibility I needed
to ensure memory and server resources were used efficiently.
Points of Interest
When building the Ultimate Grid DLL for this article, I decided to try it on my recently built Windows 8.1 machine using Visual Studio 2013.
There was no Visual Studio 2013 project provided, but I used the 2012 project and did the upgrade when prompted and it worked fine.
There is one other complication when using VS2013, the multi byte character set (MBCS) libraries are no longer installed with Visual Studio
because they have been deprecated. The Ultimate Grid project properties specify MBCS for its character set. I had no desire to try to
convert all the source from MBCS to Unicode. Fortunately for me, I had already run into this problem with the first project I tried to convert
over from my Windows 7 PC running Visual Studio 2010.
A quick search on the MSDN site revealed the libraries are still available for download. There is a prerequisite to have patch
KB2883200, but again I lucked out, the version of Windows 8.1 I installed already had this patch.
The article explaining this situation can be found here.
The libraries can be downloaded from here.
Using the Code
In Part 1, we will build the Ultimate Grid control into an external DLL for use by the data source class and the final application.
The first thing to do is get the Ultimate Grid package if you haven't already.
It can be obtained from Code Project.
Download the zip file and extract to a location of your choice. On my Windows 8.1 PC, I used D:\apps\ so the install directory
for me becomes D:\apps\Ultimate Grid\. Subsequent references to file locations will assume this location of the Ultimate Grid components.
Next, run Visual Studio and open the BuildDLL project. In my case, it can be found at D:\apps\UltimateGrid\BuildDLL.
As I mentioned above, I used the Visual Studio 2012 project and converted it to 2013. However, I have also successfully
built the DLL using the Visual Studio 2008 project on a 32-bit Windows 7 PC, and the Visual Studio 2010 project
on a 64-bit Windows 7 PC.
My installation of Windows 8.1 was 64-bit, so I needed to build a 64-bit DLL. To do this, you have to change the current configuration
to x64. With my installation of Visual Studio, Win 32 comes up as the default. Click on the solution platforms list box and select
Configuration Manager...
On the Configuration Manager Dialog, click on the Active Solutions Platform list box, and select "New".
On the New Solution Platform Dialog, be sure the new platform says x64. I copied the settings from Win32, which was the default.
I also left Create new project platform checked.
In order to build a 64-bit DLL, we need two preprocessor definitions. Click on Project on the menu bar and click properties.
In the Configuration Properties dialog, expand the configuration properties and expand C/C++ and select Preprocessor. On the
Preprocessor Definitions dialog, click the list box and select Edit. Add these two definitions: _WIN64 and _BUILD_UG_INTO_EXTDLL.
The _BUILD_UG_INTO_EXTDLL is of particular interest. The header file ugdefine.h contains the following code:
#ifndef UG_CLASS_DECL
#ifdef _BUILD_UG_INTO_EXTDLL
#define UG_CLASS_DECL AFX_CLASS_EXPORT
#elif defined _LINK_TO_UG_IN_EXTDLL
#define UG_CLASS_DECL AFX_CLASS_IMPORT
#else
#define UG_CLASS_DECL
#endif
#endif
If you look at the source code for the grid, you will notice the modifier UG_CLASS_DECL on the class definitions in the header files
found in D:\apps\UltimateGrid\Include. The authors of Ultimate Grid did this so the same header files could be used to build the DLL,
as well as in a project where the DLL will be used. This is important because I copied this technique when I constructed my header file
for the custom data source, as we will see in Part 2.
If you build the project at this point, the debug DLL UGMFCD.DLL should be produced in D:\apps\UlitmateGrid\x64\Debug.
I copied the DLL to D:\apps\UltimateGrid\DLLs, which is where the lib file was placed. I then added this directory to the PATH
environment variable, so it can be found by any application using the DLL, as we will see next.
A Quick Test Application
The sample project included with this article builds a quick application just to ensure the DLL we built is working.
Since I had used VS2013 to build the DLL, I did a VS2013 project but I have also included a VS2010 version.
This test application does not use a data source, but is modeled after the demo programs included with the
Ultimate Grid package. The OnSetup() function is used to hard code some data from the Oracle EMP table
found in the Scott schema.
I started by building a new Visual Studio project, selecting MFC as the type, and using the document/view
architecture. This little app will not use the document class, but when we get to part 3, we will use
the document class to control the data source, so I'm showing how to use the grid here in a document/view
application.
Again I had to go into Project->Properties and change the character set to MBCS.
There is also code in the MainFrm.h and MainFrm.cpp plus files to use the registry to store and
retrieve windows position settings to persist the main application window. This is code I've been
using since MFC 2.0.
In Mainfrm.h, I add a member variable. My intent was to make this a menu option, but I never wanted to turn it off,
so I never got around to making the menu item.
public:
BOOL m_bSaveSettings;
The following code goes in the PreCreateWindow function of MainFrm.cpp. It uses the registry key
HKEY_CURRENT_USER\SOFTWARE\MyStuff\UGApp1\Settings to store the values.
BOOL CMainFrame::PreCreateWindow(CREATESTRUCT& cs)
{
DWORD ulOptions = REG_OPTION_NON_VOLATILE;
char SubKey[] = "Software\\MyStuff\\UGApp1\\Settings";
HKEY hWinlogon;
LONG result;
DWORD dwTotSize = 0;
DWORD dwDisp;
DWORD dwType = REG_DWORD;
DWORD dwSavePos = 1;
VALENT ValEntry[5];
char DataBuf[5 * sizeof(DWORD)];
char top[] = "top";
char left[] = "left";
char height[] = "height";
char width[] = "width";
char savepos[] = "savepos";
char RegClass[] = "REG_DWORD";
char szBuffer[100];
ValEntry[0].ve_valuename = top;
ValEntry[1].ve_valuename = left;
ValEntry[2].ve_valuename = height;
ValEntry[3].ve_valuename = width;
ValEntry[4].ve_valuename = savepos;
dwTotSize = 5 * sizeof(DWORD);
result = RegCreateKeyEx(HKEY_CURRENT_USER, SubKey, 0, RegClass, ulOptions,
KEY_ALL_ACCESS, NULL, &hWinlogon, &dwDisp);
if (result == ERROR_SUCCESS)
{
if (dwDisp == REG_OPENED_EXISTING_KEY)
{
result = RegQueryMultipleValues(hWinlogon, ValEntry, 5, DataBuf, &dwTotSize);
if (result == ERROR_SUCCESS)
{
cs.cx = *((LPDWORD)ValEntry[3].ve_valueptr);
cs.cy = *((LPDWORD)ValEntry[2].ve_valueptr);
cs.x = *((LPDWORD)ValEntry[1].ve_valueptr);
cs.y = *((LPDWORD)ValEntry[0].ve_valueptr);
if (*((LPDWORD)ValEntry[4].ve_valueptr) == 0)
m_bSaveSettings = FALSE;
else
m_bSaveSettings = TRUE;
}
else
{
sprintf(szBuffer,
"Reg Query Failed!\nCode: %ld\nDefault values will be used.", result);
AfxMessageBox(szBuffer);
cs.cx = 724;
cs.cy = 582;
cs.x = 136;
cs.y = 90;
m_bSaveSettings = TRUE;
}
}
else {
cs.cx = 724; cs.cy = 582; cs.x = 136; cs.y = 85; RegSetValueEx(hWinlogon, top, 0, dwType, (CONST BYTE *)&cs.y, sizeof(DWORD));
RegSetValueEx(hWinlogon, left, 0, dwType, (CONST BYTE *)&cs.x, sizeof(DWORD));
RegSetValueEx(hWinlogon, height, 0, dwType, (CONST BYTE *)&cs.cy, sizeof(DWORD));
RegSetValueEx(hWinlogon, width, 0, dwType, (CONST BYTE *)&cs.cx, sizeof(DWORD));
RegSetValueEx(hWinlogon, savepos, 0, dwType, (CONST BYTE *)&dwSavePos, sizeof(DWORD));
m_bSaveSettings = TRUE;
}
RegCloseKey(hWinlogon);
}
else
{
sprintf(szBuffer, "Registry Open Failed!\nCode: %ld", result);
AfxMessageBox(szBuffer);
m_bSaveSettings = FALSE;
}
if (CFrameWnd::PreCreateWindow(cs) == 0)
return FALSE;
cs.style &= ~(LONG)FWS_ADDTOTITLE;
return TRUE;
}
The RegCreateKeyEx function will open an existing key, or create it if it doesn't exist. On the first
time running the application, or if an error occurs, I use some default settings.
The cs.style &= ~(LONG)FWS_ADDTOTITLE; statement is used so we can modify the main window caption later, in the view class,
after we have determined the table name used as the source for the grid.
We need to add a message handler for the WM_CLOSE message and add this code in order to save any changes to window
positions back to the registry.
void CMainFrame::OnClose()
{
DWORD dwDataSize = (DWORD)sizeof(DWORD);
DWORD ulOptions = 0;
char SubKey[] = "Software\\MyStuff\\UGApp1\\Settings";
HKEY hWinlogon;
LONG result;
DWORD dwX = 0;
DWORD dwY = 0;
DWORD dwWidth = 0;
DWORD dwHeight = 0;
DWORD dwSave = 0;
DWORD dwTotSize = 0;
CRect WindRect;
char top[] = "top";
char left[] = "left";
char height[] = "height";
char width[] = "width";
char savepos[] = "savepos";
char szBuffer[100];
if (m_bSaveSettings)
{
dwSave = 1;
GetWindowRect(WindRect);
dwX = WindRect.left;
dwY = WindRect.top;
dwWidth = WindRect.right - WindRect.left;
dwHeight = WindRect.bottom - WindRect.top;
result = RegOpenKeyEx(HKEY_CURRENT_USER, SubKey, ulOptions,
KEY_WRITE, &hWinlogon);
if (result == ERROR_SUCCESS)
{
RegSetValueEx(hWinlogon, left, ulOptions, REG_DWORD,
(CONST BYTE*)&dwX, dwDataSize);
RegSetValueEx(hWinlogon, top, ulOptions, REG_DWORD,
(CONST BYTE*)&dwY, dwDataSize);
RegSetValueEx(hWinlogon, width, ulOptions, REG_DWORD,
(CONST BYTE*)&dwWidth, dwDataSize);
RegSetValueEx(hWinlogon, height, ulOptions, REG_DWORD,
(CONST BYTE*)&dwHeight, dwDataSize);
RegSetValueEx(hWinlogon, savepos, ulOptions, REG_DWORD,
(CONST BYTE*)&dwSave, dwDataSize);
RegCloseKey(hWinlogon);
}
else
{
sprintf(szBuffer, "Open Registry to save settings Failed!\nCode: %ld", result);
AfxMessageBox(szBuffer);
}
}
CFrameWnd::OnClose();
}
The project properties also need to be modified. Add D:\apps\Ultimate Grid\Include to the VC++ Include directories,
and D:\apps\Ultimate Grid\DLLs to the VC++ Library directories.
Add the preprocessor define _LINK_TO_UG_IN_EXTDLL.
In the linker input property, add the additional dependency UGMFCD.lib.
The Ultimate Grid package includes a skeleton grid class implementation. Two files, MyCug.h and MyCug.cpp are located
in the D:\apps\Ultimate Grid\Skel directory. These files get copied to the project directory and added to the project
and makes a very easy way to get started.
All we need to do is add some code to the OnSetup function and we have data
for our grid to present. I also show some of the ways you can customize the appearance of the grid.
There is a lot of flexibility and control available, but all I'm doing here is changing the default
colors for the headings and column cells.
void MyCug::OnSetup()
{
int x;
COLORREF cBlue = RGB(56, 40, 200);
COLORREF cYellow = RGB(255, 255, 0);
COLORREF cHeading = RGB(192, 192, 192);
CString temp;
CUGCell cell;
SetNumberRows(3);
SetNumberCols(8);
GetHeadingDefault(&cell);
cell.SetBackColor(cHeading);
SetHeadingDefault(&cell);
for (x = 0; x<8; x++)
{
GetColDefault(x, &cell);
cell.SetBackColor(cBlue);
cell.SetTextColor(cYellow);
SetColDefault(x, &cell);
}
GetCell(0, -1, &cell);
cell.SetText("EMPNO");
SetCell(0, -1, &cell);
GetCell(1, -1, &cell);
cell.SetText("ENAME");
SetCell(1, -1, &cell);
GetCell(2, -1, &cell);
cell.SetText("JOB");
SetCell(2, -1, &cell);
GetCell(3, -1, &cell);
cell.SetText("MGR");
SetCell(3, -1, &cell);
GetCell(4, -1, &cell);
cell.SetText("HIREDATE");
SetCell(4, -1, &cell);
GetCell(5, -1, &cell);
cell.SetText("SAL");
SetCell(5, -1, &cell);
GetCell(6, -1, &cell);
cell.SetText("COMM");
SetCell(6, -1, &cell);
GetCell(7, -1, &cell);
cell.SetText("DEPTNO");
SetCell(7, -1, &cell);
cell.SetText("7369");
SetCell(0, 0, &cell);
cell.SetText("SMITH");
SetCell(1, 0, &cell);
cell.SetText("CLERK");
SetCell(2, 0, &cell);
cell.SetText("7902");
SetCell(3, 0, &cell);
cell.SetText("12/17/1980");
SetCell(4, 0, &cell);
cell.SetNumber(800);
SetCell(5, 0, &cell);
cell.SetNumber(0);
SetCell(6, 0, &cell);
cell.SetNumber(20);
SetCell(7, 0, &cell);
cell.SetText("7499");
SetCell(0, 1, &cell);
cell.SetText("ALLEN");
SetCell(1, 1, &cell);
cell.SetText("SALESMAN");
SetCell(2, 1, &cell);
cell.SetText("7698");
SetCell(3, 1, &cell);
cell.SetText("02/20/1981");
SetCell(4, 1, &cell);
cell.SetNumber(1600);
SetCell(5, 1, &cell);
cell.SetNumber(300);
SetCell(6, 1, &cell);
cell.SetNumber(30);
SetCell(7, 1, &cell);
cell.SetText("7499");
SetCell(0, 2, &cell);
cell.SetText("WARD");
SetCell(1, 2, &cell);
cell.SetText("SALESMAN");
SetCell(2, 2, &cell);
cell.SetText("7698");
SetCell(3, 2, &cell);
cell.SetText("02/22/1981");
SetCell(4, 2, &cell);
cell.SetNumber(1250);
SetCell(5, 2, &cell);
cell.SetNumber(500);
SetCell(6, 2, &cell);
cell.SetNumber(30);
SetCell(7, 2, &cell);
BestFit(0, 7, 3, UG_BESTFIT_TOPHEADINGS);
}
In order to use the grid, we need to define an instance of the MyCug class in the view. We need to
#include "MyCug.h" in the view.cpp file, and in the app.cpp file because it has a reference
to the view class. We add the class member to the view.cpp file.
public:
MyCug m_grid;
We need to add message handlers for WM_CREATE and WM_SIZE, and code an override for OnInitialUpdate.
Here is the required code:
int CUGApp1View::OnCreate(LPCREATESTRUCT lpCreateStruct)
{
if (CView::OnCreate(lpCreateStruct) == -1)
return -1;
m_grid.CreateGrid(WS_CHILD | WS_VISIBLE, CRect(0, 0, 0, 0), this, 9999);
return 0;
}
void CUGApp1View::OnInitialUpdate()
{
CView::OnInitialUpdate();
m_grid.OnSetup();
}
void CUGApp1View::OnSize(UINT nType, int cx, int cy)
{
CView::OnSize(nType, cx, cy);
m_grid.MoveWindow(0, 0, cx, cy);
}
The OnDraw function is modified to set the main window caption:
void CUGApp1View::OnDraw(CDC* )
{
CUGApp1Doc* pDoc = GetDocument();
ASSERT_VALID(pDoc);
if (!pDoc)
return;
pDoc->SetTitle("EmpDemo");
}
So if we build and run the application, and the Ultimate Grid DLL was built properly and our
application can find it, we see a screen looking like this:
Once you are sure everything is working, you can build the release versions of the Ultimate Grid DLL
and the test application.
When building the release version of the test application, you need to change the linker
dependency to the release version of the grid lib file, UGMFC.lib.
With the grid compiled into an external DLL, we are now ready for Part 2,
building the data source class.
History
- 5/18/2014: Initial release
