May 27, 2013

[ MFC ] - CDialogbar Example: Diaplaying combobox in Dialog Bar Control

1. Introduction to DialogBar example

In this example, we are going to see how do we create and use DialogBars. The dialog bar acts like a toolbar. Unlike toolbar, a dialog bar can have controls that can be placed in a normal dialog. The dialog bar can be docked like a toolbar. To better explain, think of a toolbar with a radio button, a checkbox and a combo box in it. What you just now imagined can be easily achieved through a dialog bar control.

In this example we are going to design a dialog bar, then we will place that in a Rebar control that will be docked on the Main Frame window of the SDI application.

2. About the Example

The example that we are going to make is shown below:

It is a single document interface (SDI) application. Note that, for the end user the Dialog bar displayed on the example look like a toolbar having the Combo box and Edit Box. There is nothing more to explain here. Let us start the implementation.

3. Create the SDI application

Create the MFC SDI Application without document view support. In the wizard you can disable the Document view support, which is shown below:

1. First “Single document” option is picked
2. Next, the check mark in the “Document/View architecture” support is removed
3. Then “use Unicode libraries” also un-checked.

Next, we can accept all the default options in the wizard to create a SDI Application without any document/view support. Once you are in the IDE, build the project and launch it to make sure everything is perfect.

4. Add DialogBar Resource

The next step is adding the dialog bar resource to the project. To add dialog bar resource, right-click on the project name in the resource pane (looking at the previous article’s video to add a toolbar, you will understand how to add dialog bar), then select add a new resource. From the displayed dialog, select the option IDD_DIALOGBAR and click New. This is shown below:

Once dialog bar resource is added to the project, double click the added resource and start placing the controls. The below given screenshot helps in designing your dialog bar control. Once this designing is done, we will move to writing the code to display the dialog bar.

5. Source Code - Implementation

1) First, declare the dialog bar along with other required MFC Objects in the MainFrm.h. Note that we have declared CReBar instance also. Like the previous article, we are going to host the dialog bar object the CReBar control. Below is the declaration:
//Sample 01: Required declarations
       CToolBar m_toolbar;
       CDialogBar m_dlgbar;
       CReBar m_rebar;

2) int CMainFrame::OnCreate(LPCREATESTRUCT lpCreateStruct) is the function that we are going to modify now. Since we want to display only the dialog bar on the Main window, the code that display MFC’s default toolbar is commented out as shown below:

//Sample 02: No need to create Main Toolbar.
//                  You can even remove it from the
//                  Resource
//if (!m_wndToolBar.CreateEx(this, TBSTYLE_FLAT, WS_CHILD | WS_VISIBLE | CBRS_TOP
//     !m_wndToolBar.LoadToolBar(IDR_MAINFRAME))
//     TRACE0("Failed to create toolbar\n");
//     return -1;      // fail to create

3) Since we commented out the code that displays MFC’s default toolbar, the below-specified code also need to be commented out.

//Sample 03: I will take care of displaying the Dialogbar in the Toolbar
//// TODO: Delete these three lines if you don't want the toolbar to be dockable

4) First, create the MFC’s ReBar Control by passing the “this” pointer as a parameter to the Create Function. The parameter passed in the parent to the rebar control. So the Main Window of our application is the parent for the Rebar control. This code is shown below:

//Sample 04: Create the rebar control. Specify MainFrame window as it's Parent      

5) Next, the Dialog Bar control is created by making the resource edited template denoted by IDD_DLGBR_1. We used the create function of the CDialogBar to create it from the resource id IDD_DLGBR_1. The first parameter specifies that the CReBar control instance is the parent for the CDialogBar instance we are creating now. The Flag CBRS_ALIGN_TOP specifies that Dialog bar control will be aligned on top of the Main Frame window. The last parameter can be a number that acts as the command id. I used same resource id as command id.

//Sample 05: Create the Dialog Bar and specify that m_rebar as it's Parent
m_dlgbar.Create(&m_rebar, IDD_DLGBR_1, CBRS_ALIGN_TOP, IDD_DLGBR_1);

6) Just like how we added the Toolbar controls in the previous example, the dialog bar m_dlgbar is added to the ReBar control. You can refer the previous article to know more about the AddBar function. But from here you can know that the using the AddBar function you can add CToolBar as well as CDialogBar.

//Sample 06: Add the dialog bar control to the Rebar
m_rebar.AddBar(&m_dlgbar, RGB(255,255,0), RGB(0,0,255), "Dbar 1"


Source Code : Download

May 15, 2013

SQL 2005 - Sub-Queries, Derived Tables, and Common Table Expression (CTE)

1. Introduction

In this article, I will explain some useful select queries that come handy when you want to retrieve data from different tables. There are lot of way you can form select queries that returns same result. For this article, I used Microsoft's Pubs database. Let us explore the queries one by one.

2. Formatting the Query

Let us say you want to create mailing address of Authors from the Pubs database. The problem is that the data stored in the Authors table is not in the mailing address format. The data is spawn among multiple columns. Now, have a look at the below query:

Authors mailing address in different table column
Fig 1. Authors mailing address in different table column

The above query uses the operator + which joins two strings. We use this operator to concatenate special chars, two columns, a constant string etc.  Note that the space(2) function adds two blank spaces to the string formed. Char(13) and Char(10) together forms a Line Feed and Carriage Return. The result of executing the above query is show here:

Output of author's mailing address
Fig 2. Output of author's mailing address

This output looks like a mailing address, right?

3. Case...When...End

The "Case..When" is decision making structure in SQL Queries. To know how it can be used, let us consider an example. The Titles table from the Pubs database has the book titles and it also has the price of it. Now, let us say that when we are querying the books from this table, we need to display title and price of the book along with the cost suggestion like Low, Medium and High; based on some price range. Now look at the below query example:

Case...When...End Struct Example of SQL Server
Fig 3. Case...When...End Struct Example of SQL Server
The portion of query marked in blue box shows how the costing level is done. The case when statement structure is making the decision in “When” condition statement. When the condition evaluates to true the “Then” portion will be executed. For Example, when the price lies between 0 and 3, "Low" will be returned. The else potion comes in combination with “When”. It is like:

When (Condition) Then <true> Else <false>

The "End" keyword is used to tell that we are going to terminate the Case...When conditional construct.

4. Sub Queries

A "Sub-Query" is actually referred by the Main query to decide something. Sub-query can use the data returned by the Main query and in the mean time, the main query cannot have access to the columns retrieved by the sub queries. This is shown in the below picture:

Main Query Don't have access to data of Sub-Query
Fig 4. Main Query Don't have access to data of Sub-Query

Now let us look at the Pubs Database. We already know about the Titles table since we used it in the previous examples. This table contains the book title and it price along with other information. The Sales table contains information about the sales of the book titles along with some other information. 

Let us see, how do we use sub-query to return the Book Titles that is not yet sold even single piece. This means that we have a book in the titles table and no entry for that book in the Sales Table. Have a look at the below Query:

Sub-Query and Main Query Relation
Fig 5. Sub-Query and Main Query Relation

In the above example, Main query is on the Titles table and we want to return the titles, which is not making any sales. The sub Query is on the Sales table which participates in the where clause condition of the Main table. Note that the sub-query used the column returned by the main table as T.Title_Id. The sub-query is called by the main query for every row that it returns.

When the sub Query returns no record, the “Not Exists” statement, which is part of the where condition, evaluates to true there by there by signalling main query to return the row.

5. Derived Tables

In the previous example we saw a select statement embedded inside the Where portion of the main select statement. If similar sub query participates on the “From” clause of the Main select query, we call that as "Derived table". See, the table in the expected form does not exist, and hence the result-set derived on the fly. Have a look at the below example which shows how a Table is derived in the "From" clause of the query:

SQL Server Derived Table Example
Fig 6. SQL Server Derived Table Example

The query takes the data from the Titles table of the Pubs database. The internal select statement (marked as blue box) that looks like a sub-query is the derived table here. The derived table provides the Title_id and total sales made on that particular title by making an aggregate query on the Sales table. 

Note that we are joining the Titles table with the data returned from Derived Table based on the Title_id from the Titles table and Title_Id returned by the Derived Table. A query output that is kept in the FROM portion of the SELECT query makes it as Derived Table. Here, we used the derived table technique to display book titles information along with number of titles sold.

The derived Table is an "in-memory table" and SQL server wipes that from the memory when the query execution is completed. In our example the derived table is TS (Title_id, TotalSold).  This two-column table get removed from memory when the Query execution is over.

6. Common Table Expressions (CTE)

We can achieve the same result given by the Derived Table technique by using the "CTE" technique. In "Common Table Expression" technique, we define the table before starting the actual select query. Once the query is defined, it can be referred in the sub-sequent select query multiple times. Now consider the below example:

Common Table Expression Technique (CTE)
Fig 7. Common Table Expression Technique (CTE)

In the above example, first we defined the Common Table Expression as shown in the above depiction (indication mark 1). The common table Name is TotalByTitleId with two fields TitleId, Total in it. In our example, the mapping of CTE columns TitleId, Total is done by querying a table Sales. The returned result is kept as table construct TotalByTitleId.

The next select query uses this CTE construct as table. The select query retrieves the data from the Titles table and joins that with our CTE to get the TotalSales made by a particular title. You can also use multiple CTE in your query and in that case definition of each CTE will be separated by comma like below:

With <CTEName>(col1, col2, col3) As
( Select Query ),
<CTEName>(col1, col2, col3) As
( Select Query ),
<CTEName>(col1, col2, col3) As
( Select Query )

May 12, 2013

[ MFC ] - Floating Toolbars using ReBar Control

1. What is Rebar?

The Rebar MFC control acts as the hosting container for the MFC Toolbar controls. Using the rebar control you can float around the toolbar control in the same row and you can easily re-arrange the control. The rebar control is not limited to host only the toolbars, it can host control like EditBox, Combobox etc. Have a look at the below depiction:

Here, we have two toolbars housed on the rebar. The Gripper is the one, which allows us to move the toolbar around the rebar. When we use the rebar, the user will have the facility of moving the toolbar using its gripper and even they can move the toolbar one below the other one. In this article, I will show how do you create two toolbars and house that using the MFC Rebar.

2. Example – Using the MFC Rebar Control

The example application that we are going to build in this article is shown in the below picture:

Our example is a Single Document Interface (SDI) application, which displays six toolbar buttons in two ToolBars. These two ToolBars are housed by the Rebar control. Placing the toolbar on the rebar allows the user to move the toolbar over the Rebar control using the Gripper. Using the grippers, we can also place a toolbar in separate rows.

OK. Let us start building this example.

3. Create ToolBar1 and ToolBar2

Create SDI MFC Application. To create the Toolbar we should use the resource Editor. Firstly, switch to the Resource view, then right click the project name to access the New Resource Dialog.

This will open an “Add Resource” dialog as shown below. From the dialog pick the Toolbar and hit the new button. Do this one more time to add next toolbar. Remember we are going to add two toolbars to the rebar.

Once the toolbars are added, Double click on the toolbar from resource view and draw each button. Below is the screen shot of the Second toolbar:

Once drawing the Toolbar is finished, Right click on each toolbar and assign the ID. The Below picture shows, First button in the First Toolbar is selected, then its property is accessed and ID_TB1_CMD1 is typed for the ID Field. Remember to set this property for each toolbar button from left to right. This will give ID number in ascending order, which will later help us in specifying the command range.

If you find difficulties in designing the toolbar, have a look at the below video which demonstrates creating a toolbar through visual studio editor.

4. Create Bitmap resource

To create a bitmap follow the same procedure explained in the previous section. In the “Add Resource” dialog instead of selecting the toolbar, we should select the Bitmap and hit new. This will add bitmap node in the resource explorer. Once the Bitmap is added, edit it using the resource editor. The below screen shot shows the bitmap used as the background for the example application:

Note that the height of the bitmap matches the height of the toolbar buttons which 32 pixels tall. Also, note that the each toolbar size is set to 32 x 32 pixels.  This you can refer from the previous section’s property screen shot. The video shown below explains creating a bitmap using the visual studio resource editor.

5.  Displaying Toolbar in a Rebar control

When you created a "SDI Application", accepting all the defaults in the wizard, MFC leaves default code that we are going to comment out now. First, go to the resource view and delete the default toolbar named as IDR_MAINFRAME.

1) Navigate to the Function CMainFrame::OnCreate. Comment out the code that creates the default toolbar that you deleted from the resource editor. The toolbars, Menus and status bar are created usually in the CMainFrame::OnCreaye. The code snippet shown below:

//Sample 01: Comment out the toolbar creation as we deleted it
//if (!m_wndToolBar.CreateEx(this, TBSTYLE_FLAT, WS_CHILD | WS_VISIBLE | CBRS_TOP
//     !m_wndToolBar.LoadToolBar(IDR_MAINFRAME))
//     TRACE0("Failed to create toolbar\n");
//     return -1;      // fail to create

// TODO: Delete these three lines if you don't want the toolbar to be dockable
//Sample 02: Comment Out

2) In the CMainFrame class, add the below shown private member variables. The two CToolBar objects will be loaded with the toolbars created using the Resource editor. The CBitmap object m_bckBmp will be used to set the background for the Rebar. CReBar object will be used to host the toolbar objects. The entire relationship goes like this: Toolbar buttons are hosted by the toolbar, the toolbar is hosted by the ReBar and the ReBar is hosted by MainFrame. Now you know why we are declaring all the objects here in the CMainFrame class.

//Sample 03: Declaration required for the Toolbar, Bitmap, and Rebar
       CToolBar m_FirstToolBar;
       CToolBar m_SecondToolBar;
       CBitmap m_bckBmp;
       CReBar m_rebar;

3) In the OnCreate function of the CMainFrame, First, we create both the toolbars by calling CreateEx function. Note that we are setting a TBSTYLE_TRANSPARENT flag during the creation of the CToolBar. This is because we want to set the background for the ReBar control and to make it visible we are setting the toolbar as Transparent. You can run this example application without this flag to see how it works. After creating the Toolbar, we are loading the Toolbar buttons by calling the function LoadToolBar and by giving the resource Id for the toolbar.

//Sample 04:Create both the toolbars
m_FirstToolBar.LoadToolBar(IDR_FIRST_TB  );
m_SecondToolBar.CreateEx(this, TBSTYLE_FLAT | TBSTYLE_TRANSPARENT );
m_SecondToolBar.LoadToolBar(IDR_SECOND_TB );

4) Next, we will add a handler function for each toolbar buttons coming from both the toolbars. The handler function declaration is made at the MainFrame.h file. This is shown below:

//Sample 05: Declare a dummy Handler Function for all the toolbar
//                        button
       void SomeFunction(UINT someparam);

5) The handler function definition is shown below. This function does not require any explanation.

//Sample 06: A do nothing toolbar handler
void CMainFrame::SomeFunction(UINT someparam)
       //Do Nothing. In real world there will be dedicated functions
       //for toolbar actions
       AfxMessageBox("Toolbar Clicked");

6) Next, inside the message map entry, Place two entries of ON_COMMAND_RANGE. This macro maps a range of command Ids to a specific handler function. Let us take the first message map entry for the explanation. The first two Ids ID_TB1_CMD1 and ID_TB1_CMD3 define the Range for all three buttons in the first toolbar. This ID number should be in ascending order. You can make sure this by visiting the Resource.h file. The third parameter defined the function mapped to the Command Ids. The code given below shows that all six toolbar buttons coming from two different toolbars mapped to a single handler function named SomeFunction.

//Sample 07: Handler for the toolbar buttons
       ON_COMMAND_RANGE(ID_TB1_CMD1, ID_TB1_CMD3, SomeFunction)
       ON_COMMAND_RANGE(ID_TB2_CMD1, ID_TB2_CMD3, SomeFunction)

7) OK. The toolbars are ready. Next, we load the bitmap resource into the CBitmap object m_bckBmp. The LoadBitmap function takes the bitmap resource id as the parameter and loads the resource-edited bitmap into the bitmap object.

//Sample 08: Create the Bitmap

8) Now we have everything ready. The toolbar is ready; bitmap for setting the background is ready. The code given below loads the toolbar into the ReBar Control. First, the rebar control is created calling Create function with the “this” pointer as a parameter. The pointer specifies CMainFrame as the parent to the m_rebar.

Next, we add the toolbars one by one. The second and third parameter specifies the ForeColor and BackClolor for the ReBar control. While adding the toolbars we asked to display the edges also asked to display the Gripper always. The below code snippet sets a solid color to the rebar control:

//Sample 09: Create the Rebar and Add toolbars to it
m_rebar.AddBar(&m_FirstToolBar, RGB(255,0,0), RGB(0,255,0),"TB 1", RBBS_GRIPPERALWAYS
                    | RBBS_CHILDEDGE);
m_rebar.AddBar(&m_SecondToolBar, RGB(255,0,0), RGB(0,255,0),"TB 2", RBBS_GRIPPERALWAYS
                          | RBBS_CHILDEDGE);

9) The below-commented code shows the rebar with the bitmap as the background.

////Sample 10: Create the Rebar and Add toolbars to it
//m_rebar.AddBar(&m_FirstToolBar, "TB 1", &m_bckBmp, RBBS_GRIPPERALWAYS
//                        | RBBS_CHILDEDGE);
//m_rebar.AddBar(&m_SecondToolBar, "TB 2", &m_bckBmp, RBBS_GRIPPERALWAYS
//                         | RBBS_CHILDEDGE);

The screenshot shows the application’s appearance when the Sample10 is executed and Sample09 is commented out:

The video given below shows the usage of the Rebar.

Source Code : Download

Like this site? Tell it to your Friend :)