Enhanced SmartGrid Control

Environment: Visual C++ 6 (SP4), Windows NT 4.0 (SP6)

1. Introduction

I was laid off few weeks ago as a result of the recent downturn in High-Tech. To facilitate job search I decided to write a simple application which main functionality would be storing information about potential employers in a database and automation of applying for available positions there. I chose Microsoft ADO control (Adodc will stand for ADO Data Control
everywhere later in the text) as suitable software component for interactions with my database. Unfortunately when I tried to use Microsoft ADO DataGrid Control from C++ client I encountered some problems (for instance with coordinates reported in Mouse events; or methods ColContaining and RowContaining which do not resolve Column and Row by a coordinate correctly being invoked from C++ client) which I was unable to solve and decided to check for a similar freeware control which can be used in a likely way. Eventually I decided to use SmartGrid control once published on CodeGuru
at http://codeguru.earthweb.com/controls/AlxGrd.shtml

What I was short of were some features that are offered in Microsoft DataGrid, that I decided to add to this SmartGrid.

2. Added functionality

The features I added include:

  • Possibility to use Adodc as a Data Source
  • Update recordset and database on cell edit if Adodc is a Data Source
  • Interactive adding of a new row to the recordset
  • Interactive deletion of an active or selected rows from a recordset
  • Mouse Up/Down/Move events which could be useful for implementing
    a drag-and-drop of a text contained in a SmartGrid cell or just selected part of a text in the cell
  • – Implementing IPerPropertyBrowsing interface (which methods are overridden for DataSource and DataSourceType properties) so that user can choose DataSource and DataSourceType properties values from a combboxes.

I also fixed some bugs, added error handling and made some minor changes
in the internal implementation to make the new features work.

3. The details of what was added and changed

a) There is a new Data Source type

DataSourceType_ADODataControl
   typedef [v1_enum] enum
   {
      DataSourceType_None = 0,
      DataSourceType_ADORecordset,
      DataSourceType_SQLStatement,
      DataSourceType_ADODataControl // new data source type
   } DataSourceType;

Setting DataSourceType property to DataSourceType_ADODataControl means that only Adodc’s recordset may become a Data Source for SmartGrid control. If a user sets DataSourceType in Design time to DataSourceType_ADODataControl then for a data source property he/she is given a choice only from available on the current container Adodcs. In run-time SmartGrid is automatically populated from the chosen as DataSource Adodc’s recordset I decided it could be better to add a new Data Source type to work in such mode then changing all the existing logic for DataSourceType_ADORecordset. The new data type differs from the previously existed DataSourceType_ADORecordset type in that it make user’s work more automated. If DataSourceType_ADODataControl is set then in run-time Adodc opens connection, its recordset is
initialized and populated and then the data from this recordset is supplied into SmartGrid.

b) As far as I added for user an opportunity to set a DataSource
of the kind Adodc in design time I decided to add the interface IPerPropertyBrowsing to the set of interfaces implemented by the class CSmartGrid. This interface is used to specify what strings and values can be displayed for certain properties. I overrode default implementation of IPerPropertyBrowsing methods (GetDisplayString , GetPredefinedStrings , GetPredefinedValue
) for the properties DataSourceType and DataSource.

Now when user edits DataSourceType  from property browser he/she
is given a choice from a combox with the list of predefined types only.

Fig.1 and Fig.2 show how DataSource and
DataSourceType property editing looks like in the Visual

Basic property browser.

Fig. 1

Fig. 2

Things are a little bit more complicated for DataSource property. There user should be given the list of available Adodcs that have to be found . >To find all available Adodcs I added to CSmartGrid class a method
ReEnumContainerControls . This method first calls IOleContainer::EnumObject method to find all objects held on the SmartGrid’s container and check each object whether it is Adodc. The method which checks that is CSmartGrid::IsADO . Currently the check is implemented by means of retrieving of the default interface from the object’s
ITypeInfo (if there is any; if there is not, the object is not considered Adodc) and checking whether it is “IAdodc”. (If somebody knows a better way then they’re welcome to change CSmartGrid::IsADO). All controls that can be considered as Adodc are stored in special CSmartGrid data member m_vecADOControls which is a vector of special Adodc encapsulating items (struct SmartGridADOitem ) that hold the controls IUnknown* and its name.

struct SmartGridADOitem{
  CComPtr<IUnknown> m_ADOcontrol;
  CComBSTR m_bstrName;   // ADO control
.........
constructors, operator= and destructor
.........
}
// a predicate class for SmartGridADOitem
// search in STL vector
class SmartGridADOitemBSTRpred : private unary_function
                <struct SmartGridADOitem&,int>
{
   public:
   CComBSTR m_bstr;
   int operator()(struct SmartGridADOitem& item) {
       return m_bstr == item.m_bstrName;
   }
};

ReEnumContainerControls is called each time when IPerPropertyBrowsing ::GetPredefinedStrings is called for DataSource property and if the current DataSourceType is DataSourceType_ADODataControl. We really should do it each time because I am not aware of any possible way how to notify a control that the objects collection of the container was changed; i.e. if particular Adodc was removed from the container or a new one was added. Let’s assume reenumeration is not a performance hit in the majority of cases.

c) Another consequence of having Adodc as a DataSource is the necessity to handle persistence of this property. For that reason I had to override IPersistStreamInitImpl<CSmartGrid>::Load and IPersistStreamInitImpl<CSmartGrid>::Save methods. If DataSourceType is   DataSourceType_ADODataControl then this property is stored as a string with the Adodc control name (taken from SmartGridADOitem) and upon load it is resolved to the control (after

container is reenumerated). The resolution of name to the control cannot
be done immediately in the IPersistStreamInitImpl<CSmartGrid>::Load because there we are not guaranteed that all container’s objects are loaded by that moment. So I chose to run the resolution in the first CSmartGrid::OnDraw method and for that I added a special member flag variable – m_bToReEnum to CSmartGrid which if
True indicates that we have to reenumerate container and resolve DataSource name to the control in
OnDraw . After it is done OnDraw resets this flag to False. (The way how the resolution is done looks a little bit awkward and again I would appreciate if anybody can suggest a better way) An alternative to storing the property as a string couldve been retrieval of DataSource IMoniker through its IOleObject:: GetMoniker, and invoking IMoniker::Save, but IMoniker is not garanteed to exist. (And by the way even if it was garanteed we still cannot bind the moniker right away because Adodc might not be loaded yet . So we would’ve still had all this story of the deferred Adodc resolution)

d) If the DataSourceType is DataSourceType_ADODataControl, SmartGrid’s data is synchronized with
the database. It means that editing of a SmartGrid cell invokes ADOrecordset::Update method to store edited data in the database. For this reason I had to make changes in CManageData::UpdateChanges() method which now invokes CManageData::UpdateADO handling ADO recordset update. Another change that I had to do for the correct database update is to change SmartGrid’s Row representation. In the original implementation Row was just a vector of CCell objects. I had to extend by using the following class as Row encapsulation:

class CSmartGridRow {
  public :
    typedef vector< CComObject< CCell >* >::
             iterator iterator;
    CSmartGridRow() : m_varRecordBookmark(-1),
                      m_bIsRowEmpty(FALSE)
    {};

    // this 3 operators are needed for less changes in the
    // previous implementation which actually worked directly
    // with vector< CComObject< CCell >* > m_Row instead
    // of the vector of CSmartGridRow

    operator vector< CComObject< CCell >* >& () {
       return m_Row;
    }

    vector< CComObject< CCell >* >* operator->() {
       return &m_Row;
    }

    const vector< CComObject< CCell >* >* operator->() const
    {
       return &m_Row;
    }

    void setBookmark(CComVariant& ndx) {
      m_varRecordBookmark = ndx;
    }

    const CComVariant& getBookmark() const {
       return m_varRecordBookmark;
    }

    const BOOL IsRowEmpty() const {
       return m_bIsRowEmpty;
    }

    void setRowEmpty(const BOOL bIsRowEmpty) {
      m_bIsRowEmpty = bIsRowEmpty;
    }

  private:
    vector< CComObject< CCell >* > m_Row ; // the row itself
                                          //  is actually a vector
                                          // of CCell objects
    CComVariant   m_varRecordBookmark;    // row's bookmark
    BOOL          m_bIsRowEmpty;          // row is empty means
                                          // that it can be used
                                          // to add a new one to
                                          // a recordset
};

As one can see in addition to the vector of CCels row now holds information about the recordset
bookmark corresponding to that row and a flag isRowEmpty which will be explained later. So now upon finish of the cell editing we can access the right record in the recordset (keep in mind that SmartGrid rows can be sorted by any of columns, so we cannot use row’s ordinal number for that) by its bookmark and store  data there.

e) A couple of other new useful operations are AddRow and DeleteRow. For adding a new row I add an extra empty row (that’s what CSmartGridRow ::m_bIsRowEmpty  is for) to SmartGrid which is reserved to make a new row. If user edits any of cells in that row, the row becomes non-empty, if DataSourceType is DataSourceType_ADODataControl , row is added to the recordset and later updated into the database, and new empty row is added to SmartGrid for future row adds. Fig.3 depicts how the row used for Adding looks like:


Fig. 3

User can also delete a row from SmartGrid by pressing DEL key. What is deleted will be either currently selected rows or active row.

f)To implement database update correctly I also had to add a
flag which indicates whether the SmartGrid row has to be requeried after update. It could be needed if there are autonumbered fields which are assigned by the database automatically. In that case if lets say we added a new row we should show the right values in such fields. To check whether row should be requeried I added a method CSmartGrid::CheckColumnProps which stores info whether field has to be requeried in ColumnDefinition structure

g) I added class CSmartGrid::CSmartGridErrorHandler which
handles exceptions and store information about the errors using IErrorInfo interface. I also added error handling at least there where I made changes. Besides SmartGrid fires Error event (like  MFC stock error event with id DISPID_ERROREVENT; keep in mind it is ATL implemented control) now in case of exception.

h) I added Mouse Up/Down/Move events which can be useful for implementing drag-and-drop. If one wants to drag-and-drop data from currently edited cell he/she would probably need edit box window handle. I added that as SmartGrid Read-Only property HWndEditor. Besides one can implement drag-and-drop of just a selected value inside currently edited cell. For that I also made a mouse pointer change policy more intuitive.

Fig.4 shows mouse pointer over selected text and Fig.5 – over non-selected
one.


Fig. 4


Fig. 5

i) Some minor changes also include methods Column_For_X and Row_For_Y which return a column and row for particular coordinates. The coordinates used for these methods and fired in the Mouse events are measured in pixels relatively to SmartGrid top-left corner.

j) I also added SelectedRows property that returns safearray of currently selected rows numbers

k) Added method IColumn::ChangeDataType in case if one would like to make a restrict a field update to choices from a combobox only on the fly.

l) Some minor bugs fixes that are commented in the code

 

3. Conclusion

I understand that probably there is nothing new in what I did. I only wanted to have something like Microsoft DataGrid with source code and therefore more freedom in using that. I tried to comment all the changes I made as much as possible.

If my changes span more than one line then usually the first comment starts from

// ED

and last one is

//ED

If it is just one line then it is // ED only in the same line.

I ran a test using Microsoft Visual Basic 6.0. A couple of VB projects
used for testing are included in the archive. To try DataSourceType_ADODataControl mode one should know how to configure a provider for Adodc. I recommend using adUseServer as Cursor Location for Adodc

 

Downloads

Download demo projects – 286 Kb

Download source – 139 Kb

More by Author

Get the Free Newsletter!

Subscribe to Developer Insider for top news, trends & analysis

Must Read