Become an Expert Part 6: Data Manipulation and UI Enhancement

March 15 2010

Part 6 Overview

In this final part of the "Become an Expert" series, we complete the Sales Management Application further by making it more user friendly. Up to this point we have used various controls from ComponentOne Studio for WinForms such as C1FlexGrid, C1Chart, C1Report and others to create a Sales Management Application. This sample project includes most of the basic features required in a sales application. So far we've covered 5 parts of creating this sales application:

To complete this application we will enhance the database connection and reporting features implemented earlier, and also improve the UI a bit.

Specifying Database Connection

In the previous samples we hard coded the database connection in our application every time. The problem with this approach is that you need to modify the code every time the connection changes. Let's create a settings file in the application so that we can easily change this like a global property.

Here are the steps to create an application settings file (app.config file):

Open the Properties folder in the Solution Explorer and double click on Setting.settings.
Set Property Name.
Set Property Value.
Once the value is set, app.config file is created as the project configuration file.

Enter "ConnectionString" for the Property Name and set the value to the connection string itself.

When you compile the project, a "Executable file name.config" file will be created in the executable folder (For our sample, this would be "WindowsFormsApplication1.exe.config" file). Then we just need to use the value set here as the connection string in code.

// Generate a connection.  
SqlConnection connection = new SqlConnection();  

// Set the value in the settings file as the connection string.  
connection.ConnectionString = Properties.Settings.Default.ConnectionString;

When the application is run, the value set in the config file will be retrieved. Even when the connection is changed, we just need to modify the config file. There is no need to compile the project again (Note: Please set the connection string as per your local connection settings before running the sample application).

Setting output conditions for the report generation

Next, let's enhance the report generation to accept parameters. In the previous samples the data queried to the C1Report control was based on the RecordSource property in the report's definition file. The SQL Statement used had no parameters. To enhance this, let's set conditions for the SQL statement dynamically based upon user input.

The source code should reflect this.

// Display the screen to set conditions  
// Create SQL statement based on the conditions.  
// Set SQL statement in RecordSource property.  
this.c1Report1.DataSource.RecordSource = "SQL statement";  

// Display document preview.  
this.c1PrintPreviewControl1.Document = this.c1Report1.Document;

To use the parameterized query feature of the C1Report control, we need to set the parameters along with the SQL statement in the C1ReportDesigner.

The following SQL statement had been set earlier (without any parameters)

We will now change it to include BeginningDate and EndingDate parameters.

We first added the WHERE clause in the SQL statement to specify the range of dates as the condition. And we put the parameter fields in brackets.

WHERE Sales.Date Between [BeginningDate] And [EndingDate]

We must also specify the "Parameter name", "Data Type", and "Default Value" for BeginningDate and EndingDate parameters. For this we use a PARAMETERS clause before the SQL query.

PARAMETERS [Beginning Date] DateTime 1/1/2008,[Ending Date] DateTime 1/1/2010;  
SELECT  
   ...  
FROM  
   ...  
WHERE Sales.Date Between [Beginning Date] And [Ending Date]  
   ...

Now, when the application runs we get prompted to enter these parameters. The Parameter input dialog comes automatically generated for us!

The "Enter Report Parameters" dialog box will open before the preview screen is displayed. Click on the "OK" button after the parameters have been entered.

So, instead of setting the parameters directly in the RecordSource property of the C1Report control, we set the conditions dynamically at runtime. Moreover, the report parameters dialog box is generated dynamically based on the number of parameters and the data type.

Changes can be made to the data source of the connection by setting the value of the settings file in the Connection string, just like we did in the previous section above.

// Set the setting value in the ConnectionString property.  
this.c1Report1.DataSource.ConnectionString = "Value of the settings file";

Automatically adjust control size on resizing the Form

Typically with Windows Forms there are many instances where you need to resize the form and not all controls resize how you want them too, or sometimes they get hidden as a result of resizing. ComponentOne includes Sizer for WinForms in its toolkit to handle these situations.

We will use C1SizerLight component to avoid resizing issues. You need to add C1SizerLight component to the toolbox for this. For details on how to add a component to the toolbox please refer to previous articles.

Once the component has been added to the toolbox, drag and drop C1SizerLight on the Register Sales input form. And that's all you have to do to use the C1SizerLight control; no coding or configuration is required.

Now run the application. Before resizing the form would look like this:

When the form is resized you will notice each control resizes itself to maintain proportional. You can resize the form real small and no control will ever disappear from view.

Without C1SizerLight, resizing the form would look like this:

When C1SizerLight is used, control sizes as well as font sizes are adjusted automatically. If you don't want the font size to change, set the ResizeFonts property to False. For more advanced resizing capabilities, check out the C1Sizer control instead.

Next, we will add a very simple yet very useful feature of displaying grid data in a tooltip. A tooltip can be used to display grid data when the mouse hovers over a cell, and it can contain unclipped text or additional data related to that cell.

Using the standard tooltip component you can only show a single sentence in plain text. We will use the C1SuperTooltip instead. With C1SuperTooltip we can display plain text as well as HTML text. Using HTML gives us the freedom to display any amount of rich text desired, including tables and images.

The basic usage of C1SuperTooltip is similar to a standard tooltip. Let's drag and drop C1SuperTootip on the Form. In code, use the SetTooltip method to set the control for which the tooltip is set to and the tooltip text.

this.c1SuperTooltip1.SetToolTip( "Control to be used with", "Tooltip text");

For this sample, let's show the tooltip on our C1FlexGrid. Add C1FlexGrid's MouseMove event and set the tootip text according to the position where the mouse is placed with this code:

string toolTip = string.Empty;  
// Set caption.  
toolTip = string.Format("**{0}**", this.c1FlexGrid1[0, col]);  
// Set data.  
toolTip  = string.Format("{0:C}

", this.c1FlexGrid1[row, col]);  
this.c1SuperTooltip1.SetToolTip( this.c1FlexGrid1, toolTip);****

What we're doing in this code is setting the column caption as the heading of the tooltip and then we set the actual data below it.

At runtime, this is what we observe:

Displaying rich tooltips are easy with C1SuperTooltip. When the IsBalloon property is set to True, the tooltip can be displayed as a balloon.

Next, Let's modify the tooltip's display style for the aggregate and subtotal rows.

// For aggregate row  
if (this.c1FlexGrid1.Rows[row].Style != null)  
{  
   // For subtotals row  
    if (this.c1FlexGrid1.Rows[row].Style.Name == this.c1FlexGrid1.Styles[CellStyleEnum.Subtotal0].Name)  
    {  
        toolTip = string.Format("**{0}**", this.c1FlexGrid1[row, 1]);  
        toolTip  = string.Format("{0:C}", this.c1FlexGrid1[row, 2]);  
        toolTip  = string.Format("{0:C}", this.c1FlexGrid1[row, 3]);  
        toolTip  = string.Format("{0:C}

Total Sales Proceeds

Total Payments

Total Gross Margin

", this.c1FlexGrid1[row, 4]);  
    }  
   ...  
   ...  
   ...  
}****

Here, we are setting specific styles to differentiate between aggregate rows and other rows. This code displays the aggregate values of each item when the mouse is on an aggregate row.

So far we've been modifying the tooltip text in code, but C1SuperTooltip also contains a graphical editor at design time. The editor is useful when you don't need to modify the tooltip contents dynamically in code as you can type WYSIWYG rather than raw HTML.

Conclusion

In this series we have used a combination of controls and components from ComponentOne Studio for WinForms suite to create a Sales Management Application. These controls have attractive features that are not available in standard controls. In total we've used 9 ComponentOne controls:

C1FlexGrid
C1Chart
C1Report
C1PrintPreviewControl
C1PdfDocument
C1Ribbon
C1StatusBar
C1SizerLight
C1SuperTooltip

There are over 30 other feature-packed controls not shown in this series. All could be used to enhance our Sales Management application, depending on what demands are placed by end-users and developers alike.

Download Full Sample Source Code

Acknowledgements

http://codezine.jp/
These contents were made by GrapeCity inc., Japan. http://www.grapecity.com/japan/
Translated by Vidhi Kapoor (GrapeCity India Pvt. Ltd.)
Edited by Greg Lutz (ComponentOne)