August 30, 2012

[ MFC ] - GetSystemMenu Example in VC++ for Accessing the Window's System Menu

1. Introduction

In this article, we will add our own command to the system menu of the dialog box. You can do the same for SDI as well as MDI applications. The system menu will appear when you click the top left corner of the window. Such system menu is shown below using the windows supplied notepad:

In our sample, we are going to add our system menu item to the dialog based application.

2. Code Explanation

First, a dialog based MFC application named SysCommand is created. Then, a command identifier is added to the resource.h file. The command identifier is given below:

//Sample 01: Add the Command Identifier
#define IDM_SYSCOMMAND_CUSTOM            32771

Next, in the CSysCommand class, the OnInitDialog function is modified so that it will get the system menu and adds our own menu item to it. To get the system menu GetSystemMenu function is called in the OnInitDialog. Once we have a menu, a menu item is added to it using AppendMenu function by supplying the command identifier that we added just now in the header file. Below is the code for it.

// TODO: Add extra initialization here
//Sample 02: Get System Menu and Add your own menu item
CMenu * pSystemMenu = GetSystemMenu(FALSE);
pSystemMenu->AppendMenu(0, IDM_SYSCOMMAND_CUSTOM, _T("Custom Menu"));

In the dialog class’s implementation file (.cpp), search for the function void CSysCommandDlg::OnSysCommand(UINT nID, LPARAM lParam). This function is the handler for all the system menu commands. To know the command source, the nID parameter is compared with the command identifier and once the ID matches the command is handled. In our sample, I just provided a message box. Below is the modified code:

void CSysCommandDlg::OnSysCommand(UINT nID, LPARAM lParam)
       if ((nID & 0xFFF0) == IDM_ABOUTBOX)
              CAboutDlg dlgAbout;
       //Sample 03: Check nID with your Resource Identifier for the
       //                  custom menu command.     
       else if(nID == IDM_SYSCOMMAND_CUSTOM)
              AfxMessageBox(_T("Your Custom System Command Invoked..."));
              CDialog::OnSysCommand(nID, lParam);

3. Running the Sample

Below specified video shows the sample in action.

Source Code : Download

August 29, 2012

SQL 2005 - RANK and DENCE_RANK and NTILE in sql server

1. Introduction

"Ranking functions" are useful to provide ranking on the table columns based on the value stored in it. Say for example, let us say a table is having students id and their total marks as  separate columns. This allows you to apply the ranking function on the total marks column in a meaningful way. With ranking function, you can easily find 2nd maximum or 4th maximum marks easily. The ranking functions are available in SQL 2005 and later versions.

Some useful ranking functions are displayed below:

  2. RANK()
  4. NTILE()

In this article, we will look how we can use those ranking functions by making use of NorthWnd Sample database.

2. Demo View

First we will create a view from the NorthWnd Db and that will help the explanation of ranking functions. Below is the view:

Create View NetSales as
Select Ord.OrderId, Cat.CategoryName, Prod.ProductName,
            Ord.UnitPrice * Ord.Quantity as NetSales
from (Categories cat INNER Join Products prod
            ON cat.CategoryId = Prod.CategoryId)
      INNER Join [Order Details] Ord
            ON ord.ProductId = Prod.ProductId;

In the above view, we joined category, products and Order Details table together to get the required data. Also note that the NetSales computed column is nothing but the total sales of particular product for a given order id. We are going to perform ranking functions on this Netsales view. The result of the view is shown below (Note that view does not have the order by clause as it is shown in the select query)

View used for the article
Fig 1. View used for the article

3. ROW_NUMBER function

"Row_number" applies the unique sequence of numbers for each row. This is useful when you want to delete duplicate rows in a table. Below example shows how the row_number is applied on the ascending ordered NetSales:

Row_Number Example

Fig 2. Row_Number Example

Here, Rows marked under A, B and C has same NetSales values. And note that the Row Number is applied sequentially without any repeats even the NetSales values are same.

4. RANK function

In the below example, we used a "RANK function" to provide the Rank for each product based on the generated NetSales. Note that we applied Rank on NetSales column based on the values it holds in a descending order. So the First Rank is given to the highest net sales and Second Rank is given to Next Highest and so on…

Applying Rank Function
Fig 3. Applying Rank Function

In the above example, Set of Rows marked in A, B and C have same NetSales. As these groups have same netsales value, they have the same rank. Also note the Gaps in the Rank say we do not have Rank 2, as there are two products in Rank 1.

The example below shows having different sorting order for Rank and Select Query’s statement. Note that the ranking is still applied on the descending order of Netsales and the output is ordered based on the Product Name.

Rank Order and Record Order
Fig 4. Rank Order and Record Order

5. DENSE_RANK function

The "DENSE_RANK" function works same as rank function and the only difference is that it avoids the gaps in the rank. The below example shows the dense rank in effect:

Dense Rank usage
Fig 5. Dense Rank usage

Note that even though the Rank 1 is shared between two rows, the next rank given to the NetSales of 10540.00 is 2 not 3. And this is how dense rank differs from the normal rank function.

6. NTILE function

"NTILE function" is normal distribution function. Say for example NTILE (100) means percentile of 100 and when we apply that on the Netsales in Descending orders, the sales is distributed in 100 groups. The top sales in NetSales value are placed on percentile 1 and least sales in value is placed in percentile 100. Say for example if you call NTILE(5) in place of NTILE(100), then you are placing the Netsales in 5 groups stating TOP 5, meaning that RANK 1 group, Rank 2 Group, Rank3 Group… Rank 5 Groups.

In the below example the Netsales is distributed on a 100 percentile:

NTILE Function
Fig 6. NTILE Function

7. Applying Rank Functions within a Group

All the above examples used rank functions for full table of data. But we can use the rank functions for a group and rank will be reset when the group changes. In our example, we can try applying rank function for Netsales within each category. Doing so will rank the NetSales of product within each category. Below is the example for it:

Fig 7. Applying Rand within Group by clause

 For all the Examples, the tables from NorthWnd Db is used. You can download it from here: Download

August 17, 2012

[ MFC ] - Creating Property Sheet wizards in VC++ using CPropertySheet, CPropertyPage

1. Introduction

Property Pages are widely used to accommodate multiple controls in different pages. Each Property Sheet defines a group of controls that together forms logically related information. In this article, we will see how we can create a property page using MFC. With a little change, you can deform the property pages as wizard pages.

2. About the Sample

The sample is a dialog application, which launches the property page dialog. Below is the screen shot of hosting dialog:

The below screen shot is the property page:

Note the sample has two pages and this will sufficient for the reader to add more on their property page dialog. When you click Settings… button in the main dialog, the property page dialog will be opened. Once you change any one of the default value from the displayed dialog, the apply button will be enabled. Clicking the apply button will make you change permanent not considering whether you cancel the dialog or click ok. You can also save the changes by clicking the OK button also. Then what is the use of apply button? In real world if you want to show the changes visually, the button is very useful and the user of the application will look at the visual changes and tune their settings further. Let us go ahead and start creating the sample.

3. How do we Create Property Page Dialog?

The below skeleton diagram explains how do we create the property page dialog.

First, we should create property pages. Then these property pages are attached to the property sheet, which provides the buttons required for property page dialog. OK and Cancel buttons are common for a dialog, and the Apply button is specially provided for property page dialogs. Creating the property pages is almost equal to creating the dialog boxes. In the resource, you can ask for property page and you will get a borderless dialog. In this dialog, you should drop the controls that you want for your property page.

In the above skeleton picture, first, we will create property page1 and page2. Then the required controls are dropped into page1 and pagw2. Finally, through the source code, we will add these pages to the property sheet created at runtime.

4. Create Property Pages

How do you create a dialog? Property page also created similar to that. Creating the first page of the property dialog is shown in the below video link:

Video 1: Watch it

1) From the Resource file add the Property Page
2) Then provide a meaningful ID Name for it
3) Open the Property page visual studio editor
4) From the Toolbox three radio buttons are added to it.

So that’s all we do for creating the pages of the property sheet that create a page template, drop the controls on it. Repeat the same process for all the pages. Once the pages are ready you should create associated class for it. The video provided below shows how do we create a class for the Property page added in the previous video:

Video 2: Watch It

1) The Property page template is opened in visual studio
2) Add class menu option is invoked from the context menu of the Property page template (By Right click)
3) In the class dialog, a class name is chosen, and base class is set to CPropertyPage
4) Created class is shown in the class view

The Second page of the sample is created property page 1 way as shown in video1 and video2. Now we have page1 and pag2 for the property dialog is ready. The design of second property page is shown below:

5. Add Control Variables

Now the Color and Font property page templates are ready. Now we will associate a variable to the controls in these property page templates. First, a variable is associated with the radio buttons. For all the three radio buttons, only one variable is associated and we treat these radio buttons as a single group. First, we should make sure that the tab order (Format->tab Order or Ctrl+d when the dialog is opened in the editor) for all the radio buttons goes consecutively. Then for the first radio button in the tab order, set the group property to true.   The below-specified video shows adding a control variable for the Radio buttons:

Video 3: Watch It

1) From the resource view, Property page for the font is opened
2) Make sure Group property is set to true. If not set it to true
3) Add variable dialog is opened for first radio button
4) Variable category is changed from control to variable
5) A variable of type BOOL is added (Later we will change this as int through the code)

Likewise, we add three more value type variables for each text box control in the property page two. The below screen shot shows an int value variable m_edit_val_Red added for the first edit box. For blue and green also variables are added as shown in the below screen shot.

6. OnApply Message Map for Property pages

To follow the code explanation with me, search for the comment //Sample in the solution and in the search result follow the Order 01,02,03 etc

ON_MESSAGE_VOID is a nice handler for dealing with custom messages that do not require passing any arguments. In out sample we are going to use this handler for dealing with WM_APPLY user define message. Below is the code change required for the dialog-based project.

1) First, a required header is included in the dialog class header file

//Sample 01: Include the header required for OnMessageVoid
#include <afxpriv.h>

2) In the same header file declaration for the "void message"
 handler is given.

//Sample 02: Declare the Message Handler function
afx_msg void OnApply();

3) Next in the CPP file, ON_MESSAGE_VOID Macro is added in between Begin Message Map and End Message Map. The OnApply is not yet defined, so you will get a compiler error when you compile the program at present. To avoid this provide a dummy implementation for OnApply like void CPropPageSampleDlg::OnApply() {}

//Sample 03: Provide Message map entry for the Apply button click

4) WM_APPLY is not yet defined. So declare that user defined massage in the stdAfx.h. WM_USER macro is useful to define a user defines the message in a safe way. That is the WM_APPLY does not clash with any existing user-defined message as we use it safely like WM_USER+1

//Sample 04: Define the user defined message
#define WM_APPLY WM_USER + 1

7. Change Radio Button Variable

In video 3, we added a Boolean type variable for the radio buttons group. It will be very useful if we change this variable type from BOOL to integer type. When a user makes a radio button selection, the data exchange mechanism will automatically set the variable to denote the currently selected radio button. You will get more clarity when we write the code for radio check state later. For now, we will just change Boolean variable type to integer.

1) In the PropPageFont.h file, the variable type is changed from Boolean to Integer

//Sample 05: Change the variable type to Int
int m_ctrl_val_radio_font;

2) Next in the constructor of the CPropPageFont, the variable is initialized to –1. This value denotes that none of the radio buttons are initially checked.

//Sample 06: Set the Combo value variable to -1
              : CPropertyPage(CPropPageFont::IDD)
              , m_ctrl_val_radio_font(-1)


8. CPropPageSampleDlg Dialog class

We know that the class CPropPageSampleDlg is created by the application wizard. Moreover, we are going to launch the Property page dialog from this dialog as a child dialog. The CPropPageSampleDlg will take the settings from the property page and caches it. When the property page is opened for next time, the settings cached by this parent dialog are supplied back to the property pages.

1) First, the variables required to cache settings are declared in the class declaration, which is in the header file

//Sample 07: Add Member variables to keep track of settings
int m_selected_font;
int m_blue_val;
int m_red_val;
int m_green_val;

2) Next in the OnInitDialog, these variables are initialized based on what the property page should show on very first display.

//Sample 08: Initialize the member variables
m_selected_font = -1;
m_red_val = 0;
m_green_val = 0;
m_blue_val = 0;

9. Create Property Dialog and Display it

From the dialog class, the property page dialog is created and displayed as a modal dialog. Once this property page dialog is closed by the user, the settings set by him/her is read back and cached inside the parent dialog.

9.1 Create PropertySheet

In the button click handler, first, we create a property sheet with a dialog title Settings. The second parameter passed is referred by the property sheet as its parent.

//Sample 09: Create Property Pages, Attach it to the sheet and Lauch it
void CPropPageSampleDlg::OnBnClickedButtonSettings()
              //Sample 9.1: Create Property Sheet
              CPropertySheet sheet(_T("Settings") , this);

9.2 Declaring CPropertyPages 

Next, we declare the property pages to store it in the heap later. First, we add required header file of the dialog class, then we declare the required variables in the class with a private scope. Code is below

//Sample 9.2: Include Property pages
#include "PropPageFont.h"
#include "PropPageColor.h"

//Sample 07: Add Member variables to keep track of settings
              int m_selected_font;
              int m_blue_val;
              int m_red_val;
              int m_green_val;
           CPropPageFont* m_page1_font;
           CPropPageColor* m_page2_color;

9.3 Creating Property Pages and Adding it to Property Sheet

1) In the implementation file (Look at section 9.1), after creating the property sheet with title settings, we create both the property pages (i.e.) Font and Color pages.

//Sample 9.3: Create Property Pages
m_page1_font = new CPropPageFont();
m_page2_color = new CPropPageColor();

2) Once the pages are available, we set the dialog-cached values to the controls on the property pages

//Sample 9.4: Pass the previous settings to property pages
m_page1_font->m_ctrl_val_radio_font = m_selected_font ;
m_page2_color->m_edit_val_Red = m_red_val;
m_page2_color->m_edit_val_Green = m_green_val;
m_page2_color->m_edit_val_Blue = m_blue_val;

3) Then the property pages are attached to the property sheet. Once this step is complete, the property dialog is ready with two pages. The title of each tab is taken from its caption property that you set when you designed the Property Page.

//Sample 9.5: Add Property Pages to Property Sheet

9.4 Display Property Sheet

When the property dialog is closed, we check the return value and cache (Copy) the settings provided in the pages to the calling dialog’s member variables. These variables are used to initialize the property page dialog when it is opened for next time. Note that during the button click, we create the pages on the heap, copy the dialog members to the pages, add the pages to a sheet and display it as a modal dialog and when it closed before deleting the pages from heap we copy the settings into the local members.

//Sample 9.6: Display the property sheet and call on_apply when the sheet is closed
if (sheet.DoModal() == IDOK)
delete m_page1_font;
delete m_page2_color;

10. Set Modified Flag to Enable Apply Button

The "apply" button in the property dialog is enabled when the UI elements in the pages are changed. Say for example typing the new red value in the text box will enable the apply button. Once you click the apply button, the changes are informed to the parent. In our case we send the data entered or changed by the user so for, to the parent dialog that launched this property page. In real world, the apply button will immediately apply the settings to the application. So before clicking OK, user can observe the effect of the changed settings just by clicking the apply button.

So now, we need to track the changes done in the property dialog. For that, we will handle the BN_CLICKED event for the Radio buttons in the Font property page and EN_CHANGE event for the text boxes in the Color property page. The event BN_CLICKED will appear when somebody clicked the radio button and the event EN_CHANGE will appear when the content of the text is changed.

The below video shows providing the handler for the Radio button click:

Video 4: Watch it

1) FONT property page is opened
2) First, Radio button in the group is clicked
3) In the properties pane, navigation moved to control events
4) BN_CLICKED event is double clicked (You will enter the code editor)
5) The process is repeated for other two radio buttons.

The same way the EN_CHANGED event for all the three text boxes is provided. The screen shot below shows the request for the event handler for the control event EN_CHANGED:

1) In the handler provided by the Radio buttons, we set the flag to enable the "apply" button by calling the function SetModified.

// CPropPageFont message handlers
//Sample 10: Call Set Modified to Enable Apply Button.
void CPropPageFont::OnBnClickedRadio1()

void CPropPageFont::OnBnClickedRadio2()

void CPropPageFont::OnBnClickedRadio3()

2) The same way we set the modified flag for the text boxes also. Below is the handler code:

//Sample 11: Call Set Modified to Enable Apply Button.
void CPropPageColor::OnEnChangeEdit1()

void CPropPageColor::OnEnChangeEdit2()

void CPropPageColor::OnEnChangeEdit3()

11. Sending WM_APPLY through OnApply Override of PropertyPage

We had a dummy handler for the user-defined message WM_APPLY (Refer Section 6 of this article) and we will implement that now. The property page will send the notification to this dialog when the user clicks the apply button of the property page. Have a look at the implementation below:

//Sample 12: Provide handler for Applying the property sheet changes
void CPropPageSampleDlg::OnApply()
              m_selected_font = m_page1_font->m_ctrl_val_radio_font;
              m_red_val = m_page2_color->m_edit_val_Red;
              m_green_val = m_page2_color->m_edit_val_Green;
              m_blue_val = m_page2_color->m_edit_val_Blue;

The parent dialog will take the data from both the property pages and stores that internally. Also, note that the property pages are wiped out from the memory after use and new instances of property pages are created when we display it. Now refer the code at section 9.4, you will get an idea of how the data flow of the settings will happen.
1) When the Parent about to display the property page it copies the cached data to the property pages
2) When the user clicks the OK button, this OnApply is called. Refer section 9.6
3) When user clicks the Apply button, WM_APPLY user message is sent to the CPropPageSampleDlg

The below code will send the WM_APPLY message to the parent dialog:

BOOL CPropPageFont::OnApply()
              //Sample 13: Set the Modified flag to false, and send message to dialog class
              CPropertySheet* pSheet = (CPropertySheet*) GetParent(); 
              return CPropertyPage::OnApply();

Note that the OnApply is overridden in the property page class for Fonts. Moreover, the OnApply overridden function (For all the Property page that overrode OnApply) is called by the MFC Frame work when the user clicks the apply button. As we are just going to send the message to the parent dialog of the property page when Apply button is clicked by the user, providing the overridden version of the function in either Font or Color page is sufficient. The below video shows adding the OnApply override:

Video 5: Watch It

1) Property page for CPropPageFont is opened
2) In the Property Page, Overrides toolbar icon is selected
3) Then, OnApply Override is added to the source code.

Video 6: Watch It

The above video shows the sample in Action.

 Source Code : DownLoad

Like this site? Tell it to your Friend :)