Introduction
This article is Part 3 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 built the Ultimate Grid control as an external DLL, so it could
be used by other applications.
In Part 2, we built the data source class. We set up the OCI environment,
defining a class with the ability to obtain metadata from the database,
opening a connection to the database, obtaining the select list and making it
available for use by the Ultimate Grid control.
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 sample application included with this article accesses the EMP table from the scott/tiger schema.
Using the Visual Studio project, also provided, I will show how the code can be modified to display
data from any Oracle schema.
Points of Interest
Dressing up the Grid's Appearance
The Ultimate Grid is a very flexible and versatile control, one of the things I wanted to show
in this sample application, is how the appearance of the grid can be customized. All of these customizations
are done in the OnSetup()function of the MyCug.cpp implementation file.
In the sample application, I define and use several different colors. Colors are defined using the RGB macro.
While developing my original application, I experimented with several different color schemes
and have included all the colors I defined, even though they are not all used in the sample program.
Here are the colors I defined:
COLORREF cHeading = GetSysColor(COLOR_BTNFACE);
COLORREF cBlack = RGB(0, 0, 0);
COLORREF cBlue = RGB(0, 0, 128);
COLORREF cGreen = RGB(0, 128, 0);
COLORREF cMilitaryGreen = RGB(64, 128, 128);
COLORREF cLtBlue = RGB(110, 180, 255);
COLORREF cWine = RGB(128, 0, 0);
COLORREF cYellow = RGB(255, 255, 0);
COLORREF cRed = RGB(255, 0, 0);
COLORREF cBuff = RGB(250, 230, 176);
COLORREF cGold = RGB(240, 194, 16);
COLORREF cWhite = RGB(255, 255, 255);
These color definitions are used to set the default column heading text and background colors,
as well as the data cell text and background colors.
When setting the heading colors, first a cell object is defined, then the default heading
attributes are placed in this cell object by a call to GetHeadingDefault(&cell).
The desired colors are then set using SetTextColor and SetBackColor and
then setting the heading defaults. Here is the code:
CUGCell cell;
GetHeadingDefault(&cell);
cell.SetBackColor(cWine);
cell.SetTextColor(cYellow);
SetHeadingDefault(&cell);
Setting the colors for the data cells is very similar. First, the number of columns
is obtained from the data source. Then the column defaults are retrieved into the cell
object. Then we loop through the columns and set the colors for text and background, and
set the column defaults.
int cols = GetNumberCols();
for (x=0; x<cols; x++)
{
GetColDefault(x, &cell);
cell.SetTextColor(cYellow);
cell.SetBackColor(cBlue);
SetColDefault(x, &cell);
}
Another statement I use in the setup is SetUniformRowHeight. This function
is used to force all the rows to be the same height, even if the user resizes a row in the
grid. The default is "off", so I turn it on in the OnSetup() routine.
The final customization in the sample is setting the column width. When I first displayed
the EMP table in the grid, The JOB and HIREDATE columns were cutting off a few pixels of the
data fields. This is fixed by calling the BestFit() function.
The signature for BestFit looks like this:
int CUGCtrl::BestFit(int startCol, int endCol, int calcRange, int flag);
There are two options for calculating the best fit; basing it on the column headings, or
calculating the average width based on the data contained in a specified number of rows. Using
the column headings requires setting the flag parameter to UG_BESTFIT_TOPHEADINGS. Setting the flag
to UG_BESTFIT_AVERAGE, and supplying a value for the CalcRange parameter causes the grid to
base the column width on an average for the number of rows specified. The documentation points
out the larger number of rows, the better the fit will be. But there could be a performance
cost.
This is how the grid looked without using BestFit():
Here is the code to size the columns appropriately:
BestFit(0, 0, cols, UG_BESTFIT_TOPHEADINGS);
BestFit(1, 6, cols, UG_BESTFIT_AVERAGE);
BestFit(7, 7, cols, UG_BESTFIT_TOPHEADINGS);
With the sizing done, the grid now looks like this:
When I just did the averaging, the EMPNO and DEPTNO fields were too narrow to see much
of the column headings, since the data values were much smaller than the column headings.
I used the UG_BESTFIT_TOPHEADINGS for those two columns and used UG_BESTFIT_AVERAGE
for all others.
This customization was hard coded in the sample application, because I knew I would be showing
the EMP table. If you modify the project code to display other tables, you will need to either
adjust these lines, or eliminate them completely.
Date Formats
I mention this in part two, but it's worth repeating, the data source class I built is
expecting the Oracle DATE field and the Oracle TIMESTAMP field to be formatted in a specific
way. This is done by setting the environment variable NLS_DATE_FORMAT and NLS_TIMESTAMP_FORMAT.
I used a date format of 'MM/DD/YYYY' and a timestamp format of 'MM/DD/YYYY HH24:MI:SS.FF'.
If you use a different date format, you may have to modify the amount of memory allocated
for these two data values. If your date format is greater than 10, or your timestamp greater
than 30, then you will have to increase the count in the malloc() calls of the
get_result_list() function in the OciDtSrc.cpp implementation file. See Part 2
for a discussion of how to build the data source class.
Window Persistence
I use the code to persist the main window size and location, described in Part 1,
in this sample application as well. It is in the MainFrm.h and MainFrm.cpp files,
in the calls to PreCreateWindow() and OnClose().
Using the Code
Using the Sample Application
The zip file included with this article contains three files; UGMFC.dll, the release
version of the grid control built in part 1, OciDtSrc.dll, the release version of the
data source class built in part 2, and UGApp2.exe, the sample application currently being
discussed.
Unzip these files into the same directory and run UGApp2.exe. It should display the EMP
table from the scott/tiger schema, if your service name is ORCL.
If your service name is not ORCL, you can still use the sample by making a copy of the
service you do have, if it accesses the instance of the Oracle database containing the
scott/tiger schema. Then modify the address name to ORCL and it should work.
For example, if the entry for your Oracle instance with the scott schema installed,
in your TNSNAMES.ORA file looks like this:
MYDB =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = myhost)(PORT = 1521))
(CONNECT_DATA =
(SERVICE_NAME = myservice)
)
)
Then make a copy of that entry and change the address name to ORCL, as shown below,
and the sample application should work without modification.
ORCL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = myhost)(PORT = 1521))
(CONNECT_DATA =
(SERVICE_NAME = myservice)
)
)
Your second option would be to modify the code and rebuild the project, as shown below.
You would probably want to do this anyhow, to display an Oracle table with more meaning for you.
Using the Project Files
This article also includes a zip file containing the Visual Studio 2013 project files, and
one with the Visual Studio 2010 project files. If you unzip and open the solution file with the
appropriate version of Visual Studio, you can modify the code to suit your needs.
This sample application is an SDI program, using MFC's document/view architecture.
The code to instantiate the Ultimate Grid control is in the CView implementation and is
identical to the approach used in Part 1 of this series.
The area of particular interest will be the document class, because it is here you
can modify the User ID, password, service name, and table name to be accessed.
If you look at UGApp2Doc.cpp, in the OnNewDocument()routine, this is where
those parameters are set and passed to the data source.
BOOL CUGApp2Doc::OnNewDocument()
{
if (!CDocument::OnNewDocument())
return FALSE;
int rc = 0;
CString msg;
DtSrc.SetPassword("scott", "tiger");
rc = DtSrc.Open("orcl", "emp");
if (rc != OCI_SUCCESS)
{
if (rc == OCI_ERROR)
msg = DtSrc.GetOciError();
else
if (rc < 9)
msg.Format("Missing Argument: %d", rc);
else
if (rc == 16)
msg.Format("Couldn't allocate memory for value buffers!");
else
msg.Format("Unknown Error!");
AfxMessageBox(msg);
return FALSE;
}
return TRUE;
}
An instance of the data source class is defined in UGApp2Doc.h, like this:
public:
COci DtSrc;
In order to access a different service, schema or table, change the appropriate parameters
in the call to SetPassword() or Open() and you should be able to
access any Oracle table for which you have authorization.
One thing I plan to do in the future is to build a much more flexible user interface. It
would not be that difficult to use a dialog to obtain user Id, password and service name. The
Oracle DescribeAny() call could then be used to populate a combobox with the
table names contained in the selected schema.
Summary
Once again, I have only scratched the surface of the capabilities of the Ultimate Grid control
and Oracle OCI product. Another project I may undertake, would be to do an MDI application in which
multiple document objects could be used, each containing a different table's data. There are also
many more customizations the grid control supports. There is an easily implemented feature to display
a context menu when the user right-clicks in the grid. I have thought about using this feature to
pull up a related record from the database. For example, right click on a row in the EMP table to
display the related DEPT record.
In any event, I hope you have enjoyed reading this series of articles as much as I have enjoyed
working on them.
History
- May 18, 2014: Original release
