Win Dev Blog

Become an Expert Part 5: Generating Reports

2010-03-08T15:02:00Z

Part 5 Overview

This is the fifth part in the six-part "Become an Expert" series. In this part we will add a report generation feature to the Sales Management Application. This feature will be implemented simply by using the C1Report control, which is part of ComponentOne Studio for WinForms.

In the previous posts we used the C1FlexGrid and C1Chart controls to visualize our data. Now, we will add a reporting feature which is essential to this type of application. We will use the print feature of each control and the C1PdfDocument component as well. For the reporting we will utilize various features of the rich C1Report component, and we'll show how to print preview and actually print the report using the C1PrintPreviewControl.

Final Result

Here is a preview of the final result. When we click the "Report" button on the main form, we will open a print preview window.

From the print preview screen you can display file output or directly print the document.

So, let's add this reporting feature to our application.

Design a report with C1Report control

The first step is to generate a report definition file (xml) that will define the layout and structure of our report. We will use the C1ReportDesigner application to create this report definition through a WYSIWYG design surface. The C1ReportDesigner application installs alongside Studio for WinForms.

Launch the C1ReportDesigner from the Start menu, under ComponentOne and Reports for .NET.

Select "File" --> "Add New Report" to create a new report.

In the first page of the C1Report wizard will connect to the database. Click on the "Build Connection String" button on the top right to display the "Data Link Properties" dialog.

Enter the server name, user name, password and select the database to create the connection string.

Next, write a SQL query to get the data to be displayed in the report.

We will be getting data from the Sales and Category data tables. The two tables are related through the CategoryCode field. We will get "Date", "Proceeds", "Payments", "GrossMargin", and "GrossMarginRate" from the Sales table and "Category Name" from the Category table.

Please refer to Part 1 and Part 2 of this series for details on the layout of Sales and Category tables. The SQL create statements can be found in the attached samples.

We could also use the SQL builder to create our SQL query. The SQL builder dialog can be accessed by clicking the "Build SQL statement" button on the top right.

You must be careful about predefined keywords in the C1Report control. In our case the word "Date" is a keyword so we use the AS operator to change the name of the column to "SalesDate."　

Once the SQL statement has been created, click on the "Next" button. We will group our data based on "CategoryName". Move "CategoryName" to the "Groups" box and all other fields to the "Detail" box on the C1Report Wizard dialog.

Click "Next" to select the layout. We'll select Outline, Portrait and Adjust Fields to fit page options.

Click "Next" to select a preset report style and enter the title of the report.

Finally, click "Finish" and Voila! The initial settings of your report are done.

Now we will decide where the report will be displayed and set the font for the report. These settings are easy to do by just dragging and dropping or setting some properties. We can also set the date format and currency format for our fields here.

Save the report definition file to complete the report design process.

Load and Print the report design using C1Report

Now, let's use the actual C1Report component in our Sales Management application.

Place the C1Report component on the Form. To get the C1Report control, add the control to the toolbox by right clicking on it and accessing the "Choose Toolbox Items" dialog.　For details on how to add controls refer to previous articles.

Use the Load method to load the report definition file. Set the parameters of the Load method to report definition file path and the name of the report. A single report definition file can contain many reports. In our case, we have only 1 report in our definition file but we still need to provide the report name.

this.c1Report1.Load("Report File Name & Path", "Report Name");

Then, call the Print method of the Document property to print the report.

this.c1Report1.Document.Print();

Preview and Export the Report

Printing starts the moment we click on the Report button because we are calling the Print method. However, it would be better if we get a print preview first and allow the user to change settings before the actual printing takes place. Just like other applications. It would also be great if we can get the output of the report in other formats besides simply printing. That would enhance the usability of this application tremendously. So let's try adding these two features.

Exporting the report as a file is easy; C1Report's RenderToFile method is available for generating outputted formats such as rich text, Html, PDF and Excel.

We can easily generate a preview using the standard PrintPreviewControl or the PrintPreviewDialog control. However, there will be some limitations on paging and zooming if you use the standard controls. So for better performance we will use C1PrintPreviewControl.

C1PrintPreviewControl

The C1PrintPreviewControl is also include in Studio for WinForms. This control is optimized for C1Report and already contains features like preview, printing, export etc. Just a few quick settings are needed to implement these features in our application.

First, place the C1PrintPreviewControl on the screen where we want the report to be displayed. For this sample, we add a new Form window called Print Preview.

Change the settings of C1PrintPreviewControl to decide on the commands to be used or if the navigation panel is to be displayed or not.

// Set the tools to be used.
this.c1PrintPreviewControl1.AvailablePreviewActions = C1PreviewActionFlags.All;
// Hide the navigation panel.
this.c1PrintPreviewControl1.NavigationPanelVisible = false;

Then, assign the Document property of C1Report to the Document property of C1PrintPreviewControl.

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

That's it! Replace the line of code that prints our report from earlier and display our new Print Preview window.

Use the buttons on the Print Preview of the C1PrintPreviewControl to print or get the file output.

Conclusion

In this part, we used the C1ReportDesigner application to create a report. We used the C1Report and C1PrintPreviewControl to load and preview our report in the Sales Management application. Unlike PDF export explained in Part 3, here we are able to export our data to PDF format in a better design.

In the final part, we will use some interesting features provided by various controls and complete the application.

Acknowledgements

Article copyright © 2009 Shogo Takayama ＆ Shoeisha Co., Ltd. http://www.shoeisha.co.jp/
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)

Creating Outlines and Trees with the C1FlexGrid Control

2010-03-03T18:52:00Z

One of the unique and popular features of the C1FlexGrid control is the ability to add hierarchical grouping to regular unstructured data.

To achieve this, the C1FlexGrid introduces the concept of Node rows. Node rows do not contain regular data. Instead, they act as headers under which similar data is grouped, exactly like nodes in a regular TreeView control. Like nodes in a TreeView control, node rows can be collapsed and expanded, hiding or showing the data they contain. Also like nodes in a TreeView control, node rows have a Level property that defines the node hierarchy. Lower level nodes contain higher level nodes.

For example, suppose you had a grid showing customer name, country, city, and sales amounts. This typical grid would normally look like this:

All the information is there, but it's hard to see the total sales for each country or customer. You could use the C1FlexGrid's outlining features to group the data by country (level 0), then by city within each country (level 1), then by customer within each city (level 2). Here is the same grid with after adding the outline:

This grid shows the same information as the previous one (it is bound to the same data source), but it adds a tree where each node contains a summary of the data below it. Nodes can be collapsed to show only the summary, or expanded to show the detail. Note that each node row can show summaries for more than one column (in this case, total units sold and total amount).

In this article, we will walk you through the process of turning a regular grid into a richer outline grid.

Download Visual Studio Sample (C#)

Loading the Data

Loading data into an outline grid is exactly the same as loading it into a regular grid. If your data source is available at design time, you can use the Visual Studio Property Window to set the grid's DataSource property and bind the grid to the data without writing any code.

If the data source is not available at design time, you can set the grid's DataSource property in code. The data binding code typically looks like this:

public Form1()
{
    InitializeComponent();

    // get data
    var fields = @"
        Country, 
        City, 
        SalesPerson, 
        Quantity,
        ExtendedPrice";
    var sql = string.Format("SELECT {0} FROM Invoices ORDER BY {0}", fields);
    var da = new OleDbDataAdapter(sql, GetConnectionString());
    da.Fill(_dt);

    // bind grid to data
    this._flex.DataSource = _dt;
    
    // format ExtendedPrice column
    _flex.Cols["ExtendedPrice"].Format = "n2";
}

The code uses an OleDbDataAdapter to fill a DataTable with data, then assigns the DataTable to the grid's DataSource property.

After running this code, you would get the "regular grid" shown in the first image. To turn this regular grid into the outline grid shown in the second image, we need to insert the node rows that make up the outline.

Creating Node Rows

Node rows are almost identical to regular rows, except for the following:

Node rows are not data bound. When the grid is bound to a data source, each regular row corresponds to an item in the data source. Node rows do not. Instead, they exist to group regular rows that contain similar data.
Node rows can be collapsed or expanded. When a node row is collapsed, all its data and child nodes are hidden. If the outline tree is visible, users can collapse and expand nodes using the mouse or the keyboard. If the outline tree is not visible, then nodes can only be expanded or collapsed using code.

To determine whether a row is a node or not, you can use the IsNode property:

var row = _flex.Rows[rowIndex];
if (row.IsNode)
{
    // row is a node
    var node = row.Node;
    DoSomethingWithTheNode(node);
}
else
{
    // this row is not a node
}

Node rows can be created in three ways:

Use the Rows.InsertNode method. This will insert a new node row at a specified index. Once the node row has been created, you can use it like you would any other row (set the data for each column, apply styles, etc). This is the 'low-level' way of inserting totals and building outlines. It gives the most control and flexibility and is demonstrated below.
Use the Subtotal method. This method scans the entire grid and automatically inserts node rows with optional subtotals at locations where the grid data changes. This is the 'high-level' way of inserting totals and building outlines. It requires very little code, but makes some assumptions about how the data is structured on the grid and what the outline should look like.
If the grid is unbound, then you can turn regular rows into node rows by setting the IsNode property to true. Note that this only works when the grid is unbound. Trying to turn a regular data bound row into a node will cause the grid to throw an exception.

The code below shows how you could implement a GroupBy method that inserts node rows grouping identical values on a given column.

// group on a given column inserting nodes of a given level
void GroupBy(string columnName, int level)
{
    object current = null;
    for (int r = _flex.Rows.Fixed; r < _flex.Rows.Count; r++)
    {
        if (!_flex.Rows[r].IsNode)
        {
            var value = _flex[r, columnName];
            if (!object.Equals(value, current))
            {
                // value changed: insert node
                _flex.Rows.InsertNode(r, level);

                // show group name in first scrollable column
                _flex[r, _flex.Cols.Fixed] = value;

                // update current value
                current = value;
            }
        }
    }
}

The code scans all the columns, skipping existing node rows (so it can be called to add several levels of nodes), and keeps track of the current value for the column being grouped on. When the current value changes, a node row is inserted showing the new in the first scrollable column.

Back to our example, you could use this method to create a two-level outline by calling:

void _btnGroupCountryCity_Click(object sender, EventArgs e)
{
    GroupBy("Country", 0);
    GroupBy("City", 1);
}

Very simple, but there are some caveats. First, the method assumes that the data is sorted according to the outline structure. In this example, if the data were sorted by SalesPerson instead of by Country, the outline would have several level-0 nodes for each country, which probably is not what you want.

Also, the GroupBy method may insert may rows, which would cause the grid to flicker. To avoid this, you would normally set the Redraw property to false before making the updates and set it back to true when done.

To handle these issues, the code that creates the outline should be re-written as follows:

void _btnGroupCountryCity_Click(object sender, EventArgs e)
{
    // suspend redrawing while updating
    using (new DeferRefresh(_flex))
    {
        // restore original sort (by Country, City, etc.)
        ResetBinding();

        // group by Country, City
        GroupBy("Country", 0);
        GroupBy("City", 1);
    }
}

The DeferRefresh class is a simple utility that sets the grid's Redraw property to false and restores its original value when it is disposed. This ensures that Redraw is properly restored even when exceptions happen during the updates. Here is the implementation of the DeferRefresh class:

/// Utility class used to encapsulate grid lengthy operations in a Redraw block.
/// This avoids flicker and ensures the Redraw property is reset properly in case 
/// an exception is thrown during the operation.
class DeferRefresh : IDisposable
{
    C1FlexGrid _grid;
    bool _redraw;
    public DeferRefresh(C1FlexGrid grid)
    {
        _grid = grid;
        _redraw = grid.Redraw;
        grid.Redraw = false;
    }
    public void Dispose()
    {
        _grid.Redraw = _redraw;
    }
}

The BindGrid method ensures that the grid is sorted in the order required by our outline structure. In our example, the sort order is by Country, City, and SalesPerson. The code looks like this:

// unbind and re-bind grid in order to reset everything
void ResetBinding()
{
    // unbind grid
    _flex.DataSource = null;
    
    // reset any custom sorting
    _dt.DefaultView.Sort = string.Empty;

    // re-bind grid
    _flex.DataSource = _dt;
    
    // format ExtendedPrice column
    _flex.Cols["ExtendedPrice"].Format = "n2";
    
    // auto-size the columns to fit their content
    _flex.AutoSizeCols();
}

If you run this code now, you will notice that the node rows are created as expected, but the outline tree is not visible, so you can't expand and collapse the nodes. The outline tree is described in the next section.

Outline Tree

The outline tree is very similar to the one you see in a regular TreeView control. It shows an indented structure with collapse/expand icons next to each node row so the user can expand and collapse the outline to see the desired level of detail.

The outline tree can be displayed in any column, defined by the Tree.Column property. By default, this property is set to -1, which causes the tree not to be displayed at all. To show the outline tree in the example given above, you would use this code:

void _btnTreeCountryCity_Click(object sender, EventArgs e)
{
    using (new DeferRefresh(_flex))
    {
        // group by country and city as before
        _btnGroupCountryCity_Click(this, EventArgs.Empty);

        // show outline tree
        _flex.Tree.Column = 0;

        // autosize to accommodate tree
        _flex.AutoSizeCol(_flex.Tree.Column);

        // collapse detail nodes
        _flex.Tree.Show(1);
    }
}

The code calls the previous method to build the outline, then sets the Tree.Column property to zero in order to show the outline tree in the first column. It also calls the AutoSizeCol method to ensure that the column is wide enough to accommodate the outline tree. Finally, it calls the Tree.Show method to display all level-0 nodes (cities in this case) and hide all the detail.

The Tree property returns a reference to a GridTree object that exposes several methods and properties used to customize the outline tree. The main ones are listed below:

Column: Gets or sets the index of the column that contains the outline tree. Setting this property to -1 causes the outline tree to be hidden from the users.
Indent: Gets or sets the indent, in pixels, between adjacent node levels. Higher indent levels cause the tree to become wider.
Style: Gets or sets the type of outline tree to display. Use this property to determine whether the tree should include a button bar at the top to allow users to collapse/expand the entire tree, whether lines and/or symbols should be displayed, and whether lines should be displayed connecting the tree to data rows as well as node rows.
LineColor: Gets or sets the color of the tree's connecting lines.
LineStyle: Gets or sets the style of the tree's connecting lines.

For example, by changing the code above to include these two lines:

// show outline tree
_flex.Tree.Column = 0;
_flex.Tree.Style = TreeStyleFlags.CompleteLeaf;
_flex.Tree.LineColor = Color.White;
_flex.Tree.Indent = 30;

The outline tree would change as follows:

Notice the buttons labeled "1", "2", and "*" on the top left cell. Clicking these buttons would cause the entire tree to collapse or expand to the corresponding level. Also notice the much wider indentation and the lines connecting the tree to regular rows ("Anne Dodsworth") as well as to node rows.

Adding Subtotals

So far we have covered the creation of node rows and outline trees. To make the outlines really useful, however, the node rows should include summary information for the data they contain.

If you create an outline tree using the Subtotal method, then the subtotals are added automatically. This will be described in a later section.

If you created the outline tree using the Rows.InsertNode method as described above, then you should use the Aggregate method to calculate the subtotals for each group of rows and insert the result directly into the node rows.

The Subtotal method listed below shows how to do this:

// add subtotals to each node at a given level
void AddSubtotals(int level, string colName)
{
    // get column we are going to total on
    int colIndex = _flex.Cols.IndexOf(colName);

    // scan rows looking for nodes at the right level
    for (int r = _flex.Rows.Fixed; r < _flex.Rows.Count; r++)
    {
        if (_flex.Rows[r].IsNode)
        {
            var node = _flex.Rows[r].Node;
            if (node.Level == level)
            {
                // found a node, calculate the sum of extended price
                var range = node.GetCellRange();
                var sum = _flex.Aggregate(AggregateEnum.Sum, 
                    range.r1, colIndex, range.r2, colIndex,
                    AggregateFlags.ExcludeNodes);

                // show the sum on the grid 
                // (will use the column format automatically)
                _flex[r, colIndex] = sum;
            }
        }
    }
}

The AddSubtotals method scans the grid rows looking for node rows. When a node row of the desired level is found, the method uses the Node.GetCellRange method to retrieve the node's child rows. Then it uses the Aggregate method to calculate the sum of the values on the target column over the entire range. The call to Aggregate includes the ExcludeNodes flag to avoid double-counting existing nodes. Once the subtotal has been calculated, it is assigned to the node row's cell with the usual _flex[row, col] indexer.

Note that this does not affect the data source in any way, since node rows are not bound to the data.

Note also that the method can be used to add multiple totals to each node row. In this example, we will add totals for the Quantity and ExtendedPrice columns. In addition to sums, you could add other aggregates such as average, maximum, minimum, etc.

We can now use this method to create a complete outline, with node rows, outline tree, and subtotals:

void _btnTreeCountryCity_Click(object sender, EventArgs e)
{
    using (new DeferRefresh(_flex))
    {
        // restore original sort (by Country, City, SalesPerson)
        ResetBinding();

        // group by Country, City
        GroupBy("Country", 0); // group by country (level 0)
        GroupBy("City", 1);    // group by city (level 1)

        // add totals per Country, City
        AddSubtotals(0, "ExtendedPrice");  // extended price per country (level 0)
        AddSubtotals(0, "Quantity");       // quantity per country (level 0)
        AddSubtotals(1, "ExtendedPrice");  // extended price per city (level 1)
        AddSubtotals(1, "Quantity");       // quantity per city (level 1)

        // show outline tree
        _flex.Tree.Column = 0;
        _flex.AutoSizeCol(_flex.Tree.Column);
        _flex.Tree.Show(1);
    }
}

If you run the project now, you will see a tree with node rows that show the total quantity and amount sold for each country and city. This is very nice, but there is a little problem. If you expand any of the node rows, you will see a lot of duplicate values. All rows under a given city node have the same country and city:

This is correct, but it is also a waste of screen real estate. Eliminating these duplicate values is easy, all you have to do is set the Width of the columns that are being grouped on to zero. When you do that, however, you should remember to set the grid's AllowMerging property to Nodes, so the text assigned to the node rows will spill into the visible columns. (Another option would be to assign the node text to the first visible column, but merging is usually a better solution because it allows you to use longer text for the node rows).

Here is the revised code and the final result:

void _btnTreeCountryCity_Click(object sender, EventArgs e)
{
    using (new DeferRefresh(_flex))
    {
        // restore original sort (by Country, City, SalesPerson)
        ResetBinding();

        // group by Country, City
        GroupBy("Country", 0); // group by country (level 0)
        GroupBy("City", 1);    // group by city (level 1)

        // hide columns that we grouped on 
        // (they only have duplicate values which already appear on the tree nodes)
        // (but don't make them invisible, that would also hide the node text)
        _flex.Cols["Country"].Width = 0;
        _flex.Cols["City"].Width = 0;

        // allow node content to spill onto next cell
        _flex.AllowMerging = AllowMergingEnum.Nodes;

        // add totals per Country, City
        AddTotals(0, "ExtendedPrice");  // extended price per country (level 0)
        AddTotals(0, "Quantity");       // quantity per country (level 0)
        AddTotals(1, "ExtendedPrice");  // extended price per city (level 1)
        AddTotals(1, "Quantity");       // quantity per city (level 1)
        
        // show outline tree
        _flex.Tree.Column = 0;
        _flex.AutoSizeCol(_flex.Tree.Column);
        _flex.Tree.Show(1);
    }
}

The Country and City columns are now invisible, but their values still appear in the node rows. Collapsing the tree shows totals for each country and city.

Using the Subtotal Method

We mentioned earlier that you could also create trees using the C1FlexGrid's Subtotal method. The Subtotal method performs the same tasks as the GroupBy and AddSubtotals methods described above, except it does both things in a single step and is therefore a little more efficient.

The code below shows how you can use the Subtotal method to accomplish the same thing we did before, only a little faster and without using any helper methods:

void _btnTreeCountryCity_Click(object sender, EventArgs e)
{
    using (new DeferRefresh(_flex))
    {
        // restore original sort (by Country, City, SalesPerson)
        ResetBinding();

        // group and total by country and city
        _flex.Subtotal(AggregateEnum.Sum, 0, "Country", "ExtendedPrice");
        _flex.Subtotal(AggregateEnum.Sum, 0, "Country", "Quantity");
        _flex.Subtotal(AggregateEnum.Sum, 1, "City", "ExtendedPrice");
        _flex.Subtotal(AggregateEnum.Sum, 1, "City", "Quantity");

        // hide columns that we grouped on 
        // (they only have duplicate values which already appear on the tree nodes)
        // (but don't make them invisible, that would also hide the node text)
        _flex.Cols["Country"].Width = 0;
        _flex.Cols["City"].Width = 0;
        _flex.AllowMerging = AllowMergingEnum.Nodes;

        // show outline tree
        _flex.Tree.Column = 0;
        _flex.AutoSizeCol(_flex.Tree.Column);
        _flex.Tree.Show(1);
    }
}

The Subtotal method is very convenient and flexible. It has a number of overloads that allow you to specify which columns should be grouped on and totaled on by index or by name, whether to include a caption in the node rows that it inserts, how to perform the grouping, and so on. The summary below describes the overloads available:

Subtotal(AggregateEnum aggType)
This version of the method takes only one aggregate type as a parameter. It is useful only for removing existing subtotals before inserting new ones. In this case, the aggType parameter is set to AggregateEnum.Clear.
Subtotal(AggregateEnum aggType, int groupBy, int totalOn)
Subtotal(AggregateEnum aggType, string groupBy, string totalOn)
These are the most commonly used overloads. The parameters are the type of aggregate to insert and the columns to group by and total on. The columns may be referenced by index or by name. The latter is the one we used in the example above.
Subtotal(AggregateEnum aggType, int groupBy, int totalOn, string caption)
Subtotal(AggregateEnum aggType, string groupBy, string totalOn, string caption)
These overloads add an extra caption parameter. The caption parameter determines the text that is added to the new node rows to identify the value being grouped on. By default, the value being grouped on is shown, so if you are grouping by country, the node rows would show "Argentina", "Brazil", and so on. If you set the caption parameter to a string such as "Country: {0}", then the node rows would show "Country: Argentina" instead.
Subtotal(AggregateEnum aggType, int groupFrom, int groupTo, int totalOn, string caption)
Subtotal(AggregateEnum aggType, string groupFrom, string groupTo, string totalOn, string caption)
These overloads separate the groupBy parameter into two: groupFrom and groupTo. By default, the Subtotal method inserts a node row whenever the value of the groupBy or any previous column changes.
For example, if you a row has the same value in the "City" column as the previous row, but a different value in the "Country" column, then the Subtotal method assumes the rows should be in different groups and inserts a new node row even though the value of the groupBy column is the same. These aggregates let you override that behavior and specify the range of columns that should be considered when identifying a group.

Outline Maintenance

So far we have discussed how to create outlines with trees and totals using the high-level Subtotal method as well as lower-level Rows.InsertNode and Aggregate methods.

At this point, it is important to remember that the outline tree is created based on the data, but is not bound to it in any way, and is not automatically maintained when there are changes to the grid or to the data.

If the user modifies a value in the "ExtendedPrice" column for example, the subtotals will not be automatically updated. If the user sorts the grid, the data will be refreshed and the subtotals will disappear.

There are two common ways to maintain the outlines:

Prevent the user from making any changes that would invalidate the outline. This is the easiest option. You would set the grid's AllowEditing, AllowDragging, and AllowSorting properties to false and prevent any changes that would affect the outline.
Update the outline when there are changes to the data or to the grid. You would attach handlers to the grid's AfterDataRefresh, AfterSort, and AfterEdit events and re-generate the outline appropriately.

Option 2 is usually more interesting since it provides a quick and simple tool for dynamic data analysis. This approach is illustrated by the Analyze sample provided with the C1FlexGrid. The sample creates an initial outline and allows users to reorder the columns. When the column order changes, the sample automatically re-sorts the data and re-creates the outline. The user can easily create simple reports showing sales by country, by product, by salesperson, and so on.

Using the Node class

The Node class provides a number of methods and properties that can be used to create and manage outline trees. Many of these methods and properties are based on the standard TreeView object model, so they should be familiar to most developers.

To obtain a Node object, you can either:

Use the return value of the Rows.InsertNode method:

var node = _flex.Rows.InsertNode(index, level);

Or you can retrieve the node for an existing row using the row's Node property:

var node = _flex.Rows[index].IsNode
    ? _flex.Rows[index].Node
    : null;

Either way, once you have a Node object you can manipulate it using the following properties and methods:

Level: Gets or sets the node level in the outline tree.
Data: Gets or sets the value in the cell defined by Node.Row and the Tree.Column.
Image: Gets or sets the image in the cell defined by Node.Row and the Tree.Column.
Checked: Gets or sets the check state of the cell defined by Node.Row and the Tree.Column.
Collapsed/Expanded: Gets or sets the node's collapsed/expanded state.

You can also explore the outline structure using the following methods:

GetCellRange(): Gets a CellRange object that described the range of rows that belong to this node.
Children: Gets the number of child nodes under this node.
Nodes: Gets a node array containing this node's child nodes.
GetNode: Gets the node that has a given relationship to this node (parent, first child, next sibling, and so on).

The discussion above focused on bound scenarios, where the grid is attached to a data source that provides the data. You can also create trees and outlines in unbound scenarios. Things are actually somewhat simpler in this case, since you can turn any row into a node row by setting its IsNode property to true.

If the grid is unbound, it owns all the data that is displayed, and you do things that are not possible when a data source owns the data. For example, you can move nodes around the tree using the Node.Move method as shown by the TreeNode sample provided with the C1FlexGrid.

Using nodes in an unbound grid is very similar to using nodes in a regular TreeView control.

Download Visual Studio Sample (C#)

Become an Expert Part 4: Adding Charts to Analyze Sales Data

2010-02-17T15:50:00Z

Part 4 Overview

In this series, we will create a Sales Management application to show how easily anyone can "Become an Expert". Up to this point, we have created a sales data list using C1FlexGrid (part of ComponentOne Studio for WinForms), as well as added exporting options including Excel, XML and PDF. We've also refashioned the application window to have the Microsoft Office look-and-feel using C1Ribbon.

*See Part One, Part Two, and Part Three before continuing.

Now in part 4, we will use the C1Chart control (also part of Studio for WinForms) to display different kinds of charts. The data from our C1FlexGrid, will be bound and displayed in the C1Chart control.

So let's continue!

Application Set up

We will use the C1Chart control to display charts. Just like before, we need to add the control to the toolbox. Right click on the toolbox and display "Choose Toolbox Items". For details please refer to Part 1.

First, add a new Form to the project. Change the Form to inherit the C1RibbonForm class to match it with the application we created in Part 3. Set the form title and other properties.

Creating another Ribbon Interface

We need to create another Ribbon interface for the new form using the C1Ribbon control.

On the Home tab create three RibbonGroups:

Home Tab

Display settings for C1Chart

Now let's add the C1Chart control to the form. Drag and drop the C1Chart control onto the new form under the ribbon. Dock the C1Chart control to fill the entire form. The display area for the chart is created!

The next step is to create a BindingSource to use as the data source for the C1Chart control. Place a BindingSource object on the form, named bindingSource1.

We will create a data class in the project called SalesData and place this in a class file called SalesDataList.cs. Members of this data class will be "Date", "Category", "Proceeds", "Payments" and "GrossMargin". Members can be in the form of properties or public variables (Note: In the sample, all the members are properties).

public class SalesData { public SalesData() { } private DateTime _date = DateTime.Now; public DateTime Date { get { return this._date; } set { this._date = value; } } private string _categoryCode = string.Empty; public string CategoryCode { get { return this._categoryCode; } set { this._categoryCode = value; } } private int _proceeds = 0; public int Proceeds { get { return this._proceeds; } set { this._proceeds = value; } } private int _payments = 0; public int Payments { get { return this._payments; } set { this._payments = value; } } private int _grossMargin = 0; public int GrossMargin { get { return this._grossMargin; } set { this._grossMargin = value; } } }

Set this data class as the data source of the BindingSource we just added to the form above (Note: An array has to be set in the data source, so a List is used).

public class SalesDataList : List<SalesData>

Now let's set more specifics for our chart. Launch the chart Wizard from the C1Chart Tasks (the smart tag).

Follow these settings in the wizard:

Select chart type
Set the captions for the chart Header, X Axis and Y Axis
Select the data source to be displayed in the C1Chart control. Select the bindingSource1 BindingSource created earlier.
Select which fields to use for data binding for three each of the three data series (Proceeds, Payments and GrossMargin). Each data series' X values will be bound to the Date field.

That's it! Click Finish and the chart display settings are complete.

Binding C1FlexGrid data to C1Chart

We will bind the C1FlexGrid data to the C1Chart using the SalesDataList created earlier. The SalesData class is populated from the data displayed in C1FlexGrid on the main form. The SalesDataList class is generated as an array to be fed into the C1Chart.

This property below will be used for the get/set of our SalesData class. Add this property to the chart Form's (Form2) code behind.

private SalesDataList _salesDataList = null; public SalesDataList SalesDataList { get { return this._salesDataList; } set { this._salesDataList = value; } }

We will also need a category property for our Form2 class. This property will store the selected category on which to chart data from.

private List<DictionaryEntry> category; public List<DictionaryEntry> Category { get { return this.category; } set { this.category = value; this.initializeCategory(); } }

The initializeCategory method should fill the Ribbon combobox with all possible categories.

Now we need to make a few adjustments to the main form. First, add a Chart button to the ribbon, and in its click event, we need to launch a new chart form.


Form2 chartForm = new Form2();
chartForm.SalesDataList = this.createDataList();
chartForm.Category = this.category;
chartForm.ShowDialog();

We create the SalesDataList pulling data from the C1FlexGrid using this method:

private SalesDataList createDataList() { SalesDataList list = new SalesDataList(); for (int i = 1; i < this.c1FlexGrid1.Rows.Count; i++) { if (this.c1FlexGrid1[i, 0] != null && (this.c1FlexGrid1[i, 2] != null || this.c1FlexGrid1[i, 3] != null)) { SalesData data = new SalesData(); data.Date = DateTime.Parse(this.c1FlexGrid1[i, 0].ToString()); data.CategoryCode = this.c1FlexGrid1[i, 1] == null ? "0" : this.c1FlexGrid1[i, 1].ToString(); data.Proceeds = this.c1FlexGrid1[i, 2] == null ? 0 : int.Parse(this.c1FlexGrid1[i, 2].ToString()); data.Payments = this.c1FlexGrid1[i, 3] == null ? 0 : int.Parse(this.c1FlexGrid1[i, 3].ToString()); data.GrossMargin = this.c1FlexGrid1[i, 4] == null ? 0 : int.Parse(this.c1FlexGrid1[i, 4].ToString()); list.Add(data); } } return list; }

Here, we declare a new SalesDataList and cycle through the C1FlexGrid rows collection filling in the data (so long as it's not null).

Back on Form2, we set the SalesDataList as the data source of the BindingSource.


private void setDataSource(string categoryCode)
{
    this.bindingSource1.DataSource = this._salesDataList.SelectCategory(categoryCode);

Finally, we will show the data based on the category that has been selected. The following code displays only the desired category's data from the data source.

public SalesDataList SelectCategory(string code) { SalesDataList value = new SalesDataList(); List<SalesData> list = this.FindAll(delegate(SalesData data) { return (data.CategoryCode == code); }); value.AddRange(list); return value; }

Now when we run the application, our chart form displays data only from the selected category.

Modifying the chart type

The C1Chart control supports all popular chart types. For this application, will be using the Bar and Line chart types.

We will use some simple code with the Chart Type combobox in the Ribbon to select the chart type. We will add two possible values to the combobox: Line and Bar Graph.

The Ribbon combobox's SelectedIndexChanged event will capture when the user changes chart type.

if (this.ribbonComboBox2.SelectedIndex == 0) this.c1Chart1.ChartGroups[0].ChartType = Chart2DTypeEnum.XYPlot; else this.c1Chart1.ChartGroups[0].ChartType = Chart2DTypeEnum.Bar;

Here we are simply setting the ChartType property to in the C1Chart control. The Chart2DTypeEnum is set to Chart2DTypeEnum.XYPlot for a line chart and Chart2DTypeEnum.Bar for a bar chart.

When we select Bar Graph we will notice the chart displays this type.

All it takes is changing one property to completely change the chart type with C1Chart.

Printing C1Chart

Now that we have been able to display different types of charts, we will add the feature to print them. We just need to call a simple print method in C1Chart control for this.

this.c1Chart1.PrintChart();

In our sample, we've added a Print button to the Miscellaneous RibbonTab in our chart form. When this button is clicked, we call the PrintChart method and the following print dialog is displayed:

Modifying Other Chart Properties

Let's extend the users options for viewing charted data in C1Chart. The C1Chart control also contains 3D effects and stacked charts. We will include these in the display features available on the UI.

The Use3D property is set for the 3D effect feature. Similarly, the Stacked property is used for the stacked display in a chart. Set the following properties when the user checks or unchecks the 3D Effect and Stacked checkboxes we created earlier.

this.c1Chart1.ChartGroups[0].Use3D = "true/false"; this.c1Chart1.ChartGroups[0].Stacked = "true/false";

As you can see, we get a completely different look for the chart just by easily setting these couple properties.

Finally, let's invert the X and Y axes of the chart. We just need to set the Inverted property to true for the ChartArea.

this.c1Chart1.ChartArea.Inverted = "true/false";

In addition to changing the chart type with just one property, we can also modify other chart settings with only a few properties.

Conclusion

In this section we have charted data from our C1FlexGrid control using C1Chart. Data binding was done through some simple coding and we were able to integrate the features of each control into our application. We further modified many chart display settings using only a few simple properties.

In part 5 we will use more features of C1Chart to further improve our application by integrating it with other controls.

Download Part 4 Sample Project

Points to Remember

This application has been created in Visual Studio 2008 and uses SQL Server 2005 or higher. Installation of .NET Framework 3.5 is a prerequisite for running the sample application.

Acknowledgements

Article copyright © 2009 Shogo Takayama ＆ Shoeisha Co., Ltd. http://www.shoeisha.co.jp/
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)

Become an Expert Part 3: Adding a Ribbon Interface and Export Options

2010-02-09T17:14:00Z

Part 3 Overview

In this series, we will use controls from ComponentOne Studio for WinForms to create a Sales Management application and show how easily anyone can "Become an Expert". Keep in mind that up until now* we have created a table to display sales data, implemented features to add/delete data and added a feature to automatically show totals.

* See Part One and Part Two before continuing.

In Part 3, we'll use the C1Ribbon control (part of Studio for WinForms) to change the look and feel of the application. We'll also use the C1PdfDocument control to export a report to a PDF file, and we'll also add Excel export options using the C1FlexGrid control.

So let's get started!

Implementing the Ribbon Interface 1

Modify the menu using the C1Ribbon control:

Up until now we've used basic Windows controls in the Sales Management Application menu and form itself. To enhance the look and feel of the application, we'll incorporate the Ribbon interface using the C1Ribbon control.

In order to use the C1Ribbon control, we need to add it to the toolbox. To add the control, right click on the toolbox and open "Choose Toolbox Items".

Please refer to the previous articles for details regarding this topic.

Modify preview

Let's begin by adding the C1Ribbon control to the display area of the form. It looks very different from MenuStrip. C1Ribbon provides a tabbed interface using menu items which are divided into tabs and groups for better visual effect. In this application we will divide our items into two tabs: one for basic functionality and another for file exporting features.

We have a C1RibbonForm class which is used to get a form with C1Ribbon control on it. So let's shift from the common Form class to the C1RibbonForm class. Use the following code to modify the base Form class (parent class).

Before modification

public partial class Form1 : Form

After modification

using C1.Win.C1Ribbon; // Add ：：（Omitted）： public partial class Form1 : C1RibbonForm

The form has changed completely! We'll use this as a base and add buttons and textboxes to the C1Ribbon control to create the menu.

Implementing the Ribbon Interface 2

Add Tabs and Groups:

Now we will add tabs and groups to the Ribbon. To add tabs, select the Application Menu (round section) from the top left of the screen and click "Actions" and then "Add Tab".

For changing the text of the tab, select the tab to display its context-sensitive toolbar and click on "Text Settings". Text settings and tooltip settings can be done from this popup window.

You can also modify Text settings and add more groups in the same way. Similarly, controls can also be added to groups. Select the default Group and drop-down the actions menu. Select to add a Button.

Now, let's change the icons. To change the Button's icon, click on "Change Image" from its toolbar.

From the Change Image pop-up, select the icon to be changed. Modify "Large Image" for large icons and "Small Image" for small icons. Depending on whether or not you select a large image will determine if the button is large or small. Even if you want a large button, it's also recommended to choose a small image for use in the Quick Access Toolbar.

Try changing icons for all the controls in the same way. Continue to fill out the Ribbon with the following tabs and groups. Some controls are for features we have already implemented and some are for the new features we plan to implement. For PDF export we will have to add a separate C1PdfDocument component. Note: details about this feature will be covered later.

Home Tab

Read
Write

Data Manipulation

Add Data

PDF

PDF Output
Include Password
Password

File Tab

Excel

Output

XML

Export
Import

Adding Excel Export

Now that we have modified the appearance, let's add new features. Usually in business applications, users want data to be exported to a file.

Let's first add a feature for exporting our data to Excel. First, add a RibbonButton event. This can be done by simply double clicking on the RibbonButton, just like a usual button control. We will use the "Output" button in the "Excel" group of the file tab created earlier.

To accomplish the Excel exporting we'll simply use C1FlexGrid's export feature. Use the following code to export C1FlexGrid data to an Excel file format.

this.c1FlexGrid1.SaveExcel("File Name", "Sheet Name", "Output Options");

Data from C1FlexGrid will be displayed in the specified sheet. We have fixed rows (header rows) and fixed columns (header columns) in the Sales Management Application. Specify FileFlags.IncludeFixedCells in the output option to get fixed cells in the Excel file.

If you open the exported Excel file, you'll notice the window outline is fixed for the sections with fixed cells. Also, our cell styles are respected in the Excel format, but our custom OwnerDraw progress bars are not.

Adding XML Export

In addition to Excel export, we can also export C1FlexGrid data to XML. Exporting to Excel limits reusability from the customer's end because it's restricted to the software being used. By exporting to XML we can load and export data in a format more native to the FlexGrid. We will export C1FlexGrid data to an XML file and then read data from this file.

We'll use the "Export" button in the XML group. Use the following code to export C1FlexGrid data to an XML file format.

this.c1FlexGrid1.WriteXml("File Name");

Just write one line of code and that's it!

Data exported to XML

Let's read the data from the XML file back to the grid. Execute the following code to load the XML file and paste the data back to C1FlexGrid. Use the "Import" button of XML group in the Ribbon.

this.c1FlexGrid1.ReadXml("File Name");

And just like file exporting, we only need one line of code here too. Background color has also been read back with the data! So when data is exported to XML, font, background color, and other similar information is also exported to the file.

Adding PDF Export

Finally, since we are creating a Sales Management application, let's try adding a reporting feature. We will use C1PdfDocument component to create a PDF file. C1PdfDocument (part of Studio for WinForms) is used for generating PDF documents.

So let's start creating the PDF file! Add the C1PdfDocument component from the toolbox, just like you did for C1Ribbon. Then add the click event of the "PDF Output" button under the PDF group in the Home tab.

Now let's add some code.

this.c1PdfDocument1.Clear(); this.c1PdfDocument1.DocumentInfo.Title = "Title";

This code initializes the C1PdfDocument component and sets the PDF file title. The title can be verified in the PDF file properties.

RectangleF rcPage = this.c1PdfDocument1.PageRectangle; rcPage.Inflate(-100, -100);

Here we get the page area and determine the display area of the page by setting the top, bottom, left, right margins.

Next we'll set the title (heading) style of the report.

// Get and set the height. rc.Height = this.c1PdfDocument1.MeasureString("Title (Heading)", "Font", rc.Width).Height; // Draw title. this.c1PdfDocument1.DrawString("Title (Heading)", "Font", Brushes.Black, "Drawing area"); // Adjust the drawing location. rc.Offset(0, rc.Height + 5);

The code above set the height of the drawing area using the MeasureString method. We then draw the title using the DrawString method. Then, modify Brushes.Black to change the color of the text. And finally, adjust the drawing location by an amount equal to the height of the title plus the row padding.

The following code will draw the header section. The basic sequence will be the same as drawing the title; however, in this case it will be for multiple columns, so the horizontal width needs to be calculated. (In our application, we are simply dividing the width by the number of columns. Also, for setting the height of the drawing area, we are calculating based on the height of the tallest cell).

// Measure cell width rcCell.Width = rc.Width / fields.Length; // Get the height. Select the highest one and set it. float height = this.c1PdfDocument1.MeasureString("Header String", "Font", rcCell.Width).Height; rcCell.Height = Math.Max(rcCell.Height, height);

Next, fill the drawing area and draw the header string. Adjust the position of the drawing area and our drawing of the header is complete.

// Fill the cell. this.c1PdfDocument1.FillRectangle(Brushes.Black, rcCell); // Draw header. this.c1PdfDocument1.DrawString("Header String", "Font", Brushes.White, rcCell); // Position is set by adjusting the horizontal width only. rcCell.Offset(rcCell.Width, 0);

Finally, we will draw the data section. Drawing the data section is similar to the header section. It contains numeric values, strings and date values. The numeric values will be left aligned and all other data types will be right aligned.

StringFormat sf = new StringFormat(); // Check if the value can be converted to double. double d; sf.Alignment = (double.TryParse("Data", out d)) ? StringAlignment.Far :StringAlignment.Near; // Draw the data in the cell. this.c1PdfDocument1.DrawString("Data", "Font", Brushes.Black, rcCell, sf);

We are also checking if the numeric data type or any other data type can be converted to double to avoid conversion errors. For numeric data, we set StringAlignment to Far, and for other data types set it to Near. Then draw the data string by specifying the string format at runtime while using the DrawString method. Finally, once the file is saved using the Save method of C1PdfDocument, our process is complete. Page breaks have also been implemented in this sample application.

Setting Password in the PDF File

The generated PDF file now contains sales data. This might be confidential information since this is a business application, so let's set a password for the PDF file. This password will be required to open and view the PDF file.

Check the checkbox "Include Password" in the PDF group of Home tab. The following code is implemented when you click on "PDF Output" after entering a string in the "Password" textbox.

this.c1PdfDocument1.Security.UserPassword = "Password String";

Here we just need to use the UserPassword property of the PdfSecurity class to set the password. The security of the PDF file is enhanced this way so the users who do not have the password will not be able to open the PDF file.

Conclusion

In this part we modified the look and feel of the application with the help of the C1Ribbon control. We implemented features like Excel export and XML export and import. We also created a PDF file and set a password for it using the C1PdfDocument component. By making a few code changes to the controls we were able to enhance the features in the application immensely.

In Part 4, we'll add some more interesting features to the application by binding sales data to a chart control and displaying the data in C1FlexGrid in the form of a chart.

Download Part 3 Sample Project

Acknowledgements

Article copyright © 2009 Shogo Takayama ＆ Shoeisha Co., Ltd. http://www.shoeisha.co.jp/
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)

Become an Expert Part 2: Adding Features to the Sales Management Application

2010-02-05T20:46:00Z

Part 2 Overview

Microsoft Visual Studio contains a number of standard controls. ComponentOne Studio Enterprise provides easy-to-use controls with enhanced features which can be used in Visual Studio along with standard controls. In this series, we make use of these controls to create a Sales Management Application. In Part 1; we started building a simple application using C1FlexGrid to display and edit sales data. In this part, we'll power up our application by using features provided by C1FlexGrid.

Add Product Category

You may remember that in the previous section, "Proceeds" and "Payments" were added to the Sales Management Application based on dates. A real life organization, however, sells a number of products and might want to look at aggregate sales figures for each product category. For this, a category item needs to be added so that not just dates but category wise sales figures can also be represented.

First, let's add a column "Product Category" in the grid. Open the "C1FlexGrid Column Editor" by selecting Designer... from the C1FlexGrid's smart tag to add/delete rows at any location in the grid. We will put this new column between the "Date" and "Proceeds" columns. Name it ProductCategory and set AllowEditing to False.

We have a separate CategoryMaster table where the data values are translated into display values using DataMap (use CategoryMaster script included in the sample zip to create the CategoryMaster table, and the Sales script to recreate the Sales table adding a CategoryCode data field). Here the CategoryCode value is specified and the corresponding CategoryName is displayed on the grid. Values in C1FlexGrid have been made non-editable in our application, but DataMaps can also be used where editing is possible in the grid and values are entered in a drop-down list.

ListDictionary dictionary = new ListDictionary(); dictionary.Add("CategoryCode", "CategoryName"); this.c1FlexGrid1.Cols[1].DataType = typeof(int); this.c1FlexGrid1.Cols[1].DataMap = dictionary;

CategoryCode is added as the key and CategoryName is added as the display value in the ListDictionary. With this, we have added a "ProductCategory" column in the grid and completed the settings for the DataMap.

Add a Window to Enter Data

Next, let's change the way we enter data in the grid. In Part 1, we added/modified data directly through C1FlexGrid, but this is very inconvenient in a real application. So we'll create a new input window to enter data. When data is to be entered, a new window will pop up and for simple data input.

First, we will add a Form to the project. This form will contain controls where data related to "Date", "Category", "Proceeds" and "Payments" can be entered.

public object CategoryCode { get { return this.comboBox1.SelectedValue; } set { this.comboBox1.SelectedValue = value; } }

Use the code below to get and set the entered value in the main window.

When new data is entered

this.c1FlexGrid1.AddItem(new object[] { "Date", 　　　　　　　　　　　　　　　　"CategoryCode", 　　　　　　　　　　　　　　　　"Proceeds", 　　　　　　　　　　　　　　　　 "Payment" });

When existing data is updated

this.c1FlexGrid1["rowNum", 0] = "Date"; this.c1FlexGrid1["rowNum ", 1] = "CategoryCode"; this.c1FlexGrid1["rowNum ", 2] = "Proceeds"; this.c1FlexGrid1["rowNum ", 3] = "Payments";

When the popup window is closed, the value entered will be retrieved automatically and reflected in C1FlexGrid. Numeric values can also be entered easily through this window.

Adding Subtotals/Grand Total

Up untill now we have been making changes at the record level, but in a Sales Management Application we would need information about total sales. So let's add a totals row to calculate sales for a month. If there are multiple data rows for the same date, we'll calculate subtotals for that date also.

In order to add Subtotals and Grand Total rows, we need to simply add up the values of each column and add a new row to display the result. But here we'll use the data aggregation feature in C1FlexGrid to display values.

Use the Subtotal method to calculate aggregate value

// Clear subtotal. this.c1FlexGrid1.Subtotal(AggregateEnum.Clear); // Sort the contents of the grid. this.c1FlexGrid1.Sort(SortFlags.Ascending, 0, 1); // Calculate the grandtotal. this.c1FlexGrid1.Subtotal(AggregateEnum.Sum, -1, -1, 2, "Total"); // Calculate the subtotal. this.c1FlexGrid1.Subtotal(AggregateEnum.Sum, 0, 0, 2, "{0:MM/dd} Subtotals");

First let's remove the existing aggregate row. The data in the grid is sorted on two fields, first being date and then classified as per the product category. The first parameter in the SubTotal method, used to calculate the total and subtotal values, is the aggregate function type (function type is Sum in this case). Parameters 2 ~ 4 are outline level, grouping columns and aggregate columns. Instead of a specific column, -1 is used as the grouping column of the GrandTotal row. For subtotals column, it is set to 0 (Date column). The aggregate column in both cases (GrandTotal and Subtotal) is column 2 (Proceeds) in the code above. Finally, set "Total" in the caption of the totals row and a date placeholder in the caption of the subtotals row. That's all we need to do to set aggregate rows in C1FlexGrid!

For the GrandTotal row we will specify the average of the Gross Margin Rage column. Please note that the Gross Margin Rate of the SubTotals row is not calculated.

Let's modify the display style for aggregate rows so that they are highlighted in the grid.

CellStyle cellStyle = c1FlexGrid1.Styles[CellStyleEnum.GrandTotal]; cellStyle.BackColor = Color.Blue; cellStyle.ForeColor = Color.White; cellStyle.Font = new Font(Font, FontStyle.Bold); cellStyle = this.c1FlexGrid1.Styles[CellStyleEnum.Subtotal0]; cellStyle.BackColor = Color.Orchid; cellStyle.ForeColor = Color.White; cellStyle.Font = new Font(Font, FontStyle.Bold);

Here, we specify the cell style to change the display. In this application we are setting the cell background of the totals row to blue and the values to white. These are in bold. Cell styles for subtotals are also set in the same way.

Now let's run the application. Notice the Totals row at the bottom of C1FlexGrid. Values for a particular date will be displayed in the Subtotals rows. We've now demonstrated how it's simple to add aggregate rows to the grid by using C1FlexGrid methods.

Visual Display for Gross Margin Rate

Let's make some more changes. Just like the aggregate rows, we'll change the color of the GrossMarginRate column. We want to show visually where the GrossMarginRate is higher and fill the cell proportionally according to the value in the gross margin rate, sort of like a Progress Bar.

Let's change the DrawMode property of C1FlexGrid from Normal to OwnerDraw. Now the OwnerDrawCell event will be fired when the cell is rendered.

The code for the OwnerDrawCell event is as follows:

// Calculate horizontal width. Rectangle rc = this.c1FlexGrid1.GetCellRect(e.Row, "Column Index"); rc.Width = (int)(this.c1FlexGrid1.Cols[e.Col].WidthDisplay * "Gross Margin Rate"); // Draw the background of the cell. e.DrawCell(DrawCellFlags.Background | DrawCellFlags.Border); // Display bar graph. rc.Inflate(-2, -2); e.Graphics.FillRectangle(Brushes.LimeGreen, rc); rc.Inflate(-1, -1); e.Graphics.FillRectangle(Brushes.PaleGreen, rc); // Draw the contents of the cell. e.DrawCell(DrawCellFlags.Content);

First, we get the size of the cell (cell that displays the GrossMarginRate value). Then, calculate the horizontal width of the cell proportional to the actual Gross Margin Rate. Render the background, border, and so on based on the calculated value and set the colors for border and the fill color of the rectangle to display a horizontal bar graph. Finally, display the cell value (GrossMarginRate).

Set the placeholders "{0}" and "{1}" to display page number and total page numbers respectively.

Conclusion

In Part 2, we used the C1FlexGrid features to add aggregate rows, draw cells, and print the grid data. All this was done by simply calling a few methods. In the Part 3, we'll enhance our application's capabilities further by adding a few more controls to it along with C1FlexGrid and exporting data from the grid to a PDF file.

Download Sample Project

Acknowledgements

Article copyright © 2009 Shogo Takayama ＆ Shoeisha Co., Ltd. http://www.shoeisha.co.jp/
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)

Become an Expert Part 1: Create a Sales Management Application using ComponentOne Studio for WinForms

2010-01-29T20:00:00Z

Introduction

Visual Studio provides a number of standard controls, but every so often you may wish you could do something more. Most of us developers are aware that trying something complicated with standard controls means a lot of time will be spent on coding.

ComponentOne Studio for WinForms (hereafter Studio for WinForms) offers a number of useful components that implement enhanced functionalities which are not included in standard controls. These are useful as standalone components, but with a little bit of effort, one can combine the features of a number of these components and create amazing applications in minutes.

In this series, we will make use of a Sales Management application to show how easily you can "Become an Expert". The first step in this application is creating a table to display sales data. The basic structure of this application will be created using the C1FlexGrid control (included in Studio for WinForms).

Part 1: Create a Sales Management Application using ComponentOne Studio for WinForms
Part 2: Adding Features to the Sales Management Application
Part 3: Adding a Ribbon Interface and Export Options
Part 4: Adding Charts to Analyze Sales Data
Part 5: Generating Reports

Part 1 Overview

Microsoft Visual Studio contains a number of standard controls. ComponentOne Studio provides easy to use controls with enhanced features which can be used in Visual Studio along with standard controls. In this series, we make use of these controls to create a Sales Management Application. In the first part of this series, we create a table to display sales data using C1FlexGrid (a control in ComponentOne Studio for WinForms) as the base.

Target Audience

Those who have created or want to create Windows applications in .NET.
Those who find standard controls somewhat limited in their usage.
Those who have some preliminary knowledge of databases.

Environment

Visual Studio 2005 or 2008
SQL Server 2005

Set-up the Application

Before you start, you’ll need to have ComponentOne Studio for WinForms downloaded and installed. This includes the C1FlexGrid control.

Points to Remember: This application has been created in Visual Studio 2008. Installation of .NET Framework 3.5 is a prerequisite for running the provided sample. We will also be creating a SQL Server database, however you can skip over this section to create an unbound application.

Download Studio for WinForms at http://www.componentone.com/SuperProducts/StudioWinForms/.
Create a WinForms application in Microsoft Visual Studio. Open Microsoft Visual Studio 2008, and select "File | New | Project" from the main menu. For this article, select C# as the language and select .NET Framework 2.0. Name the project "Sales Management App" in the Name field and click the OK button.

Note: C1FlexGrid is supported under the 2.0 or 3.x Frameworks and can be used in either Visual Studio 2005 or 2008 environments. You can choose the Framework and the environment. The code in this tutorial will be in C#.
Drag and drop C1FlexGrid from your Toolbox onto your page.
Drag and drop a DateTimePicker to the form and place it in the top right corner of Form1.
Set the Format property of the DateTimePicker to Custom and set the Custom Format property to “yyyy/MM.”
Set the Size of the DateTimePicker to Width=96, Height=20.
Give Form1 a Title of “Sales Management” by settings its Text property.
In Form1.cs, add the following line of code above the namespace. This will give us direct access to the SqlClient library for connecting to our database.

using System.Data.SqlClient;

Create the User Interface

Now let’s decide on the content of our application. A Sales Management application should contain the following items:

Date
Proceeds
Payments (Cost)
Gross Margin
Gross Margin Rate

Let’s create a table to display these 5 items.

By default, C1FlexGrid consists of 50 rows × 10 columns. Since we need only 5 columns in our application, we will set Cols.Count property (located under the Cols node) to 5 in the FlexGrid property window. We do not need any rows in the grid at the start of the application, so we will set the Rows.Count property to 1.

Setting up the Columns

We need to label each of our five columns. To do this we can simply select a column and modify the Column Caption property using the smart tag. Name the five columns Date, Proceeds, Payments, GrossMargin, and GrossMarginRate.

Setting Input Constraints

Next we want to format the “Proceeds” and “Payments” columns to allow only numeric input. To set input constraints and captions for each column, we can make the desired changes by simply selecting a column and modifying the settings using the smart tag.

Select the Proceeds column, and click the smart tag to open the C1FlexGrid Tasks menu. Locate the Format Strings textbox and click the ellipsis button. This opens the “Format String” dialog box. Select "Currency" as the "Format type".

Repeat the steps above for the Payments column.

The GrossMargin and GrossMarginRate columns will contain computed values; therefore, we won’t allow editing for these columns.

Select the GrossMargin column, and click the smart tag to open the C1FlexGrid Tasks menu. Uncheck the “Allow Editing” option. Repeat the steps for the GrossMarginRate column.

To sum up, only numeric values can be entered in “Proceeds” and “Payments” columns while input is disabled in other columns.

So by simply setting a few properties, we can restrict the entry of certain type values in some columns and prohibit entering any values in other columns.

Calculating Values

Next, let’s calculate the values of GrossMargin and GrossMarginRate columns. To do this, add C1FlexGrid’s AfterEdit event to the application. This event fires after the cell has been edited. Add the following code:

double proceeds = this.c1FlexGrid1[e.Row, 1] == null ? 0 : double.Parse(this.c1FlexGrid1[e.Row, 1].ToString()); double payments = this.c1FlexGrid1[e.Row, 2] == null ? 0 : double.Parse(this.c1FlexGrid1[e.Row, 2].ToString()); double grossMargin = proceeds - payments; this.c1FlexGrid1[e.Row, 3] = grossMargin; this.c1FlexGrid1[e.Row, 4] = proceeds != 0 ? grossMargin / proceeds : 0;

Values of Proceeds and Payments columns are converted to Double to calculate the GrossMargin. Null value is converted to 0. Subtract Payments from Proceeds to get GrossMargin amount (GrossMargin = Proceeds – Payments). GrossMarginRate is calculated by dividing GrossMargin with Proceeds (GrossMarginRate = GrossMargin/Proceeds). When the value in the Proceeds column is 0, GrossMarginRate is also changed to 0.

This is how we calculate GrossMargin and GrossMarginRate after the values in Proceeds and Payments columns have been entered.

Setting Date

Now let's see how we can set dates in the fixed column for each row. Of course it is possible to enter the date every time, but for our convenience, let’s try setting the date automatically. Month and year are set to display the date on a monthly basis.

Let’s use the DateTimePicker to set the date and month. Add the following code to dynamically change the row count of FlexGrid and values of the fixed column to the ValueChanged event of DateTimePicker.

This.setDateCell();

Now we need to add the setDateCell method which deletes all rows excep the fixed row and adds rows with the selected date.

private void setDateCell() { this.c1FlexGrid1.Rows.RemoveRange(1, this.c1FlexGrid1.Rows.Count - 1); DateTime startDate = new DateTime(this.dateTimePicker1.Value.Year, this.dateTimePicker1.Value.Month, 1); DateTime value = startDate; while (value.Month == startDate.Month) { this.c1FlexGrid1.AddItem(value.ToShortDateString()); value = value.AddDays(1); } }

By using this simple piece of code, we can add the date in the heading of each row and also add and delete rows.

Let’s add a couple lines of code to the Form’s Load event so that the C1FlexGrid is displaying some dates before we even select one.

this.dateTimePicker1.Value = DateTime.Now; this.setDateCell();

Loading and Registering Data

Now let’s try registering the entered data and loading this registered data into the grid. A 'Sales' table has to be created for registering the sales data. You can use the DBScript that's included with the downloaded sample to generate the sales table.

Add a button to the form with the text “Register,” and in its click event write the method for registering the data to the database.

The method of registering data to the database and loading it to the grid has been summarized below (implement steps 1-7 in order). The following code should be placed in the Register button’s click event.

Create an instance of SqlConnectionSqlConnection connection = new SqlConnection(this.getConnectionString());
Open database connectionconnection.Open();
Create an instance of SqlCommand

SqlCommand command = new SqlCommand();

Set SQL command command.Connection = connection; command.CommandType = CommandType.Text;

Delete data by executing SQL commands

DateTime selectedMonth = new DateTime(this.dateTimePicker1.Value.Year, this.dateTimePicker1.Value.Month, 1); command.CommandText = string.Format("DELETE FROM Sales WHERE Date >= '{0}' AND Date < '{1}'", selectedMonth, selectedMonth.AddMonths(1)); command.ExecuteNonQuery(); for (int i = 1; i < this.c1FlexGrid1.Rows.Count; i++) { if (this.c1FlexGrid1[i, 1] != null || this.c1FlexGrid1[i, 2] != null) { string insert = string.Format("INSERT Sales (Date, Proceeds, Payments, GrossMargin, GrossMarginRate) VALUES ('{0}', {1}, {2}, {3}, {4})", this.c1FlexGrid1[i, 0], this.c1FlexGrid1[i, 1] == null ? 0 : this.c1FlexGrid1[i, 1], this.c1FlexGrid1[i, 2] == null ? 0 : this.c1FlexGrid1[i, 2], this.c1FlexGrid1[i, 3], this.c1FlexGrid1[i, 4]); command.CommandType = CommandType.Text; command.CommandText = insert; command.ExecuteNonQuery(); } }

Dispose command

command.Dispose();

Close database connection

connection.Close(); connection.Dispose(); MessageBox.Show("Data has been written to the database.");

We used the following SQL command to delete records.

DELETE FROM Sales WHERE Date >= 'Start date of the month' AND Date < 'Start date of the next month'

The range of dates selected in the table for sales data are specified in the WHERE clause. By setting a single month for the dates in the WHERE clause, we prevented registering data on a date that already existed in the table.

We registered the sales data entered in C1FlexGrid using the following SQL command.

INSERT Sales (Date, Proceeds, Payments, GrossMargin, GrossMarginRate) VALUES ('Date', ‘Proceeds’, ‘Payments’, ‘GrossMargin’, ‘GrossMarginRate’)

The data from C1FlexGrid will be set as it is. Null values are acceptable in the “Proceeds” and “Payment” columns. They are registered as 0 in the database.

Getting Records

Let’s check the registered data. We will add another button to our Form and call it “Load.” In the Load button’s click event, add the following code:

SqlConnection connection = new SqlConnection(this.getConnectionString()); // Open database connection. connection.Open(); // Generate an instance of SqlCommand from SqlConnection. SqlCommand command = connection.CreateCommand(); DateTime selectedMonth = new DateTime(this.dateTimePicker1.Value.Year, this.dateTimePicker1.Value.Month, 1); string selectSQL = string.Format("SELECT Date, Proceeds, Payments, GrossMargin, GrossMarginRate FROM Sales" + " WHERE Date >= '{0}' AND Date < '{1}'" + " ORDER BY Date", selectedMonth, selectedMonth.AddMonths(1)); // Set the SQL command to be executed. command.CommandText = selectSQL; // Execute the specified SQL command and create a SQLDataReader. SqlDataReader reader = command.ExecuteReader(); // Dispose command. command.Dispose(); // Move to the next record. while (reader.Read()) { DateTime dbDate = Convert.ToDateTime(reader["Date"]); this.c1FlexGrid1[dbDate.Day, 1] = reader["Proceeds"]; this.c1FlexGrid1[dbDate.Day, 2] = reader["Payments"]; this.c1FlexGrid1[dbDate.Day, 3] = reader["GrossMargin"]; this.c1FlexGrid1[dbDate.Day, 4] = reader["GrossMarginRate"]; } // Close reader. reader.Close(); // Close database connection. connection.Close(); connection.Dispose(); MessageBox.Show("Data has been loaded to the grid.");

Here we are creating a SqlConnection just as we did before when registering the data. We are using the following SQL statement for getting the records.

SELECT Date, Proceeds, Payments, GrossMargin, GrossMarginRate FROM Sales WHERE Date >= ‘Start date of the month' AND Date < 'Start date of the next month' ORDER BY Date

Only the date is added initially, and then the corresponding data for a particular date is added in the other columns. Therefore, data retrieved is based on the sorted date column.

The following screenshot shows the sample application for registering the data.

Conclusion

In Part 1 we have introduced Studio for WinForms and have explained the usage and features of C1FlexGrid. Use C1FlexGrid to create a simple data list display. Although the sample shown here is a Sales Management Application, with a few minor changes it can also be used as an accounting application.

In Part 2, we will use more features from C1FlexGrid and use the grid with other ComponentOne controls to create a feature rich application.

Download Part 1 Sample Project

Acknowledgements

Article copyright © 2009 Shogo Takayama ＆ Shoeisha Co., Ltd. http://www.shoeisha.co.jp/
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)

Dynamic Connection Strings

2010-01-26T17:01:00Z

Dynamic Connection Strings

This blog post details a solution to the common problem of dynamically setting connection strings. The approach used allows end-users to change database connections at runtime through a friendly user interface, and save the connection for future uses. Provided with this post is source code for the connection form which accesses all SQL servers and databases on a computer and its networks, builds a connection string based upon user selection, and also saves this to an xml file in local data storage (see bottom of post for download).

Scenario:

You create a project in Visual Studio which utilizes a backend database. Typically, you first create your database, and then create a connection string in Visual Studio to link to your database, and then you complete the rest of your project. You store the database connection string in the settings of your project.

The Problem:

Once you are happy with the application you package and distribute it to your end users. This is when the fun starts as your end users start to say that they can't make a connection to the database on their server. The reason for this is really quite simple. The connection string that you have created has Application scope and is strongly typed. You need to allow the connection string to be set on the users end.

Because the connection string is strongly typed, you can do something like:

[C#] string myString = Properties.Settings.Default.MyConnectionString;

[VB.NET] Dim MyString As String = My.Settings.MyConnectionString

But if you try and reverse the logic:

[C#] Properties.Settings.Default.MyConnectionString = "abc";

[VB.NET] My.Settings.MyConnectionString = "abc"

You get an error.

The problem, of course, lies in the strongly typed connection string that you created when you designed the application. The real problem is that it is strongly typed.

So how can you get around this? It's something that you would think has a ready solution available, but strangely there isn't one. A web search for ‘How do I dynamically reassign the connection string' will return hundreds of results. The realization is that you are not alone.

The Solution:

Well, it turns out that you can actually play with the settings, but to do this you need to access the settings events. If you return to the Settings page of your project you'll see a button that allows you to view the code.

Now, you still can't directly assign the connection string here in the code page (because it's still strongly typed), but you can make use of late binding. The following code is possible (in the Settings Load event):

[C#] this["MyConnectionString"] = "abc";

[VB.NET] Me.Item("MyConnectionString") = "abc"

This is the key to solving the problem. And now that you know that you can change the connection string at runtime, you can expand this process by letting the user build the connection string rather than you, the developer, always being responsible for knowing it. To do this you can create a property in your Settings.cs or Settings.vb page.

[C#] public string RuntimeSqlConnectionString {
	set
	{
		this["MyConnectionString"] = value;
	}
}

[VB.NET] Public WriteOnly Property RuntimeSqlConnectionString() As String
	Set(ByVal value As String)
		My.Settings("MyConnectionString") = value
	End Set
End Property

The rest, as they say, is just a case of applying a little logic to your new found freedom. The easiest approach (for you) is just to present the end user with a text box and have them fill in their connection string and assign that value. But to avoid all those future support calls, you should give them as much help as possible. To achieve this you should display a form that actually searches the end user's computer/network for active SQL Servers, and when they select one, automatically list all of the databases available.

When they have made a successful test connection you write the information out to an xml file in the user's application data directory. This approach allows each user to have their own connection string (each user could have different passwords!).

Finally, on application startup, you read the xml file and dynamically assign the connection string to the property we created earlier. It's that simple.

The same logic can be used for other database connection strings, such as OleDb if you're using C1Reports. Just add another connection string setting and another property in your Settings code.

Download Sample:

Download source for this sample:

ConnectionWizard.zip [C# - VS2008]
ConnectionWizardVB.zip [VB.NET - VS2008]

This sample provides the code for accessing all SQL servers and databases on a computer and its networks. It builds a connection string with the option for a password, and also saves the connection string to an xml file in local data storage. It may not look like a "wizard" but you could certainly take the logic used and apply it within a wizard of your own.

Thanks to Dom Sinclair (http://www.viewtolearn.net/) for submitting the content of this article.

Visual Studio 2010 beta Compatibility

2010-01-15T17:54:00Z

If you convert an existing project or solution to Visual Studio 2010 beta there should be no problem. But if you are creating new projects within VS2010 beta there are a couple things you need to know when using ComponentOne WinForms controls.

You cannot target .NET 4.0 Client Profile (which is the default for any new WindowsApplication.)
The ISupportInitialize BeginInit and EndInit methods do not get generated automatically when you drop a new C1 control on the form.

Workarounds

1. Targeting .NET 4.0 Framework

To use ComponentOne WinForms controls under .NET 4, you must target the full .NET 4.0 Framework. By default, new projects are set to target the .NET 4.0 Client Profile. ComponentOne controls depend on the full framework to run properly. If your application is targeting the Client Profile, ComponentOne controls will not appear in the toolbox, and you’ll receive errors regarding System.Design.dll.

To change this setting for C# applications: go to the project's properties (from the Visual Studio menu, select Project | Properties...) and from the Application tab change the Target framework to ".NET Framework 4".

To change this setting for VB applications: go to the project's properties (from the Visual Studio menu, select Project | Properties...) and from the Compile tab click the Advanced Compile Options…. From this window change the Target framework to ".NET Framework 4".

Upon changing this you will be prompted to close and reopen the project.

2. Adding New Controls to the Form

When you add a new C1 control onto your form in Visual Studio 2010 beta, everything should appear fine until you go to run the form. The control will be missing. This is because of a bug in Visual Studio where the ISupportInitialize’s BeginInit and EndInit methods are not added automatically.

The fix is real simple. Just open up your designer-generated code and add these missing method calls.

For C# applications, open up <YourForm>.Designer.cs and add the following two lines of code above the form’s SuspendLayout and ResumeLayout methods (replace c1Chart1 with the name of your control).

((System.ComponentModel.ISupportInitialize)(this.c1Chart1)).BeginInit();
this.SuspendLayout();

...

((System.ComponentModel.ISupportInitialize)(this.c1Chart1)).EndInit();
this.ResumeLayout(false);

For VB applications, click the “Show All Files” button from the Solution Explorer and then open <YourForm>.Designer.vb. Add the following two lines of code above the form’s SuspendLayout and ResumeLayout methods (replace C1Chart1 with the name of your control).

CType(Me.C1Chart1, System.ComponentModel.ISupportInitialize).BeginInit()
Me.SuspendLayout()

...

CType(Me.C1Chart1, System.ComponentModel.ISupportInitialize).EndInit()
Me.ResumeLayout(False)

You will need to do this for each C1 control added to your form.

The good news is this bug will be fixed in the official release of VS2010.

Studio for WPF

Regarding ComponentOne WPF controls...if you convert your existing project to VS2010 beta you should have no problem. We have reported errors of adding C1Chart and C1DataGrid to the toolbox and dragging them to a new form. These will be addressed for the official release.

Reduce Data-entry Form Development Time by 60%

2010-01-06T18:23:00Z

Creating data-entry forms can be very time consuming and tedious, and maintaining them can be a nightmare. However, with ComponentOne InputPanel™ for WinForms creating and maintaining data-entry forms is no problem at all. The control removes all redundant tasks such as control alignment, spacing, data-binding, labeling, accelerator key generation, and tab ordering.

This article demonstrates a two-part comparison of creating and maintaining the exact same data-entry form using standard toolbox controls versus using ComponentOne InputPanel. To best illustrate the development experiences, the actual demonstrating will be shown in a series of four videos.

Part 1: Creating the Data-Entry Form

Creating data-entry forms is perhaps one of the easiest tasks a .NET developer ever takes on in his career. The task primarily involves dragging and positioning many label and text box controls on the form. The interactive design surface in Microsoft Visual Studio makes this task easy enough for anyone who can click and move a mouse. It does not typically involve any coding. Even a data bound form, depending on how you set up your data source, can be completely created without writing much code.

But when creating a data bound entry form that has many fields, the task becomes a bit more complex. It doesn’t get any faster or easier the more forms you have to create. Additional tasks you will most likely complete for each form include:

Bind each individual input control to its data source
Add a separate Label control for each input control or field
Strategically assign accelerator keys to each field’s label so that none repeat
Configure the TabIndex property for each control so that they go in order (unless fields are initially added to the form in the final, desired order)
Position and align all label and input controls perfectly so that the form looks organized and professional

Each task above is performed for each field on your form. So a form with 20 fields will take 10 times longer to create than one with 2 fields.

Visual Studio can help you accomplish these tasks to a certain degree. You can setup data binding through the Properties grid and it can be as easy as setting a single property, no code; however, you still have to do this for every single field on your form. As you add controls onto your form, the TabIndex property for each auto-increments itself for you. So the TabIndex property will be 0 for the first control added, 9 for the tenth control added, and so on. But in most cases the order in which you add the controls will not be the final desired order. You will eventually be reconfiguring your tab indexes. (Note: more on this in part two.) Visual Studio also provides an assortment of alignment and spacing buttons on the Layout toolbar.

When using the C1InputPanel control these tasks are all handled automatically for you. You simply set one data source property at the top level of the control and all labels and appropriate input controls are created and aligned for you. C1InputPanel also has built-in tab ordering and an intelligent keyboard accelerator editor that generates unique keys for each field (accelerator keys are what allow users to jump to a specific field by pressing ALT+key).

Watch the two videos below to see how we can create the same data-entry form with and without C1InputPanel.

Video 1: Creating a data-entry form with C1InputPanel

Watch this video to learn how to create a navigational data-entry form using the C1InputPanel control. This demo is a bit more advanced than any of the samples that ship with the control because it uses a master-detail relationship and works with the C1TrueDBGrid control. The development time spent in this video is roughly 3:30 minutes.

Video 2: Creating a data-entry form without C1InputPanel

In this video, see how to create the exact same data-entry form as the previous video using only the standard toolbox controls that come with Visual Studio. This can be a great educational video for anyone who has never created navigational, data bound windows forms. The video skips setting up the data source, as this would be identical with or without C1InputPanel. The development time spent in this video is roughly 8 minutes when we eliminate the chit-chat.

Note that the time saved using the C1InputPanel control in this example is relatively small, about 4:30 minutes, but this is more than half of the total time spent without C1InputPanel. Imagine the time savings across many forms with reducing the development time more than 50%. We also cut several corners in the 2nd video, such as not naming labels and input controls, and the controls were never aligned perfectly. The accelerator keys were just copied from the first video so we let C1InputPanel do all the work for this task. The time spent on these shortcuts should also be considered.

Part 2: Maintaining the Data-Entry Form

The developer doesn’t necessarily get the final say in the order and layout of a form. End-users may later request that some fields be moved higher in the order due to frequency of updates, or lower in the order, or they may even later request that certain fields be moved together. All of these requests would require maintenance to the forms you’ve already created. A typical data-entry form could go through many layout tweaks before the end users are finally satisfied.

When using standard toolbox controls the maintenance procedure is very manual. If you need to rearrange the fields then to do this you will literally be picking them up and moving them piece by piece. Once you rearrange controls you then have to reconfigure tab ordering, layout spacing, sizing and sometimes you may want to reconfigure accelerator keys.

When using C1InputPanel, maintenance is almost non-existent. When you first create the data-entry form with C1InputPanel you will inevitably make a few tweaks here and there, such as breaking the fields into multiple columns, or moving a field or two. These tweaks are easily handled in the Item Collection editor (see Video #1). Further maintenance to your data-entry form is no more difficult as you just open up the Item Collection editor to move fields and set column breaks to match the new desired layout. The difference between this approach and the manual approach is that here you are just setting a few properties and clicking a few buttons, which is a lot quicker than dragging controls around while trying to keep them perfectly aligned. C1InputPanel will also automatically update the tab order when you rearrange controls, so you never have to worry about updating the TabIndex property for every input control on the form.

Watch the two videos below to see the benefits of using C1InputPanel to make modifications to the data-entry forms created in the previous videos. We will assume that these changes below have been requested and we’ll apply the same changes to both forms.

Video 3: Making changes to a data-entry form created with C1InputPanel

In this video we make the suggested changes shown on the sticky note to the app we just created in Video 1 using C1InputPanel. The development time spent in this video is approximately 45 seconds.

Video 4: Making changes to a data-entry form created without C1InputPanel

In this video we make the same changes from the sticky note to the app created in Video 2, using only standard toolbox controls. The development time spent in this video is approximately 4:15 minutes.

Conclusion

When we combine the time saved between the two sets of videos we see about 60% less development time when using C1InputPanel to create and maintain the same data-entry form. Of course, the alleviated work using C1InputPanel is tedious and time consuming; nothing overly difficult in the spectrum of developer tasks.

Also keep in mind in this example we had to hold C1InputPanel back in terms of features and functionality so that it somewhat matched the competing sample. There are more features that you get with C1InputPanel that you wouldn’t otherwise have out-of-the-box. Consider these the icing on the cake:

3 Microsoft Office Visual Styles that apply to the control itself and all embedded input controls
Validation and Error handling: flag invalid input with a red box around the editor and optionally display error tooltips
19 native controls including split buttons and rich labels
Rich tooltips for all embedded controls
Check out the InputPanel product page for more features

Enhanced Printing and Previewing with C1FlexGrid

2009-12-28T17:00:00Z

Traditional Printing with the C1FlexGrid Control

The C1FlexGrid control (part of ComponentOne FlexGrid for WinForms) has always supported printing and previewing with the PrintGrid method. The PrintGrid method takes parameters that allow you to customize the print/preview process, including the ability to show a print preview dialog, scale the grid, provide headers and footers, and so on.

The code below shows how you can create a print preview dialog for a C1FlexGrid using the PrintGrid method. It only takes one line of code:

_flex.PrintGrid(
    "Standard",                         // document title
    PrintGridFlags.ShowPreviewDialog,   // options
    "\tNorthWind Invoices",             // header
    "\t\tPage {0}");                    // footer

The PrintGridFlags parameter allows you to specify whether the method should simply print the grid immediately, whether it should display a print preview or printer setup dialog, and various scaling options. In this example, the parameter is being used to invoke the print preview dialog.

The strings used to specify the document's header and footer contains tabs. The tabs break each string into three parts, which are aligned to the left, center, and right of the page. The header and footer strings may also contain a "{0}" placeholder that gets replaced automatically with the current page number.

After calling this method, you would see a preview dialog like the one shown below:

As you can see, the PrintGrid method is extremely simple. It works very well for common scenarios that only require basic printing support.

However, this simplicity comes at a cost. The built-in preview dialog is the .NET PrintPreviewDialog class, which suffers from several limitations, including its dated appearance, the fact that the entire document must be generated before it can be previewed, and the lack of export options.

Also, the PrintGrid method creates a PrintDocument object where the grid is rendered. This is a simple design, but it means the grid "owns" the document, which makes adding other elements to the document a little difficult (imagine, for example, that you want to render a document with a title, three grids, and a couple of charts).

Advanced Printing with the C1FlexGrid Control

To address the limitations of the PrintGrid method listed above, we introduced the PrintDocumentGridRenderer class. This class is included in the C1FlexGrid assembly and provides methods for rendering scaled parts of the grid into the pages of regular PrintDocument objects.

Using the PrintDocumentGridRenderer involves the following steps:

Create an instance of the PrintDocumentGridRenderer class, passing it a grid that you want to render.
Create a PrintDocument object that will contain the grid and any other content you want.
Attach handlers to the PrintDocument object's BeginPrint, PrintPage, and EndPrint events.
In the PrintPage event handler, call the PrintDocumentGridRenderer class to render parts of the grid one page at a time.
Print or preview the document using a PrintPreviewDialog or the document's Print method.

This is a lot more complex than simply calling PrintGrid, but these steps are the standard way to create .NET print documents, and they aren’t too complicated. The benefits you get from the added complexity are significant.

Using this approach, you have total control over the document. You could, for example, create several PrintDocumentGridRenderer objects and use them to render several grids into the same document.

This approach also lets you use any PrintPreview dialog you want, including the C1PrintPreview dialog described in a separate article called Enhanced PrintPreviewDialog Class with PDF Output (also available on http://www.codeproject.com/KB/printing/CoolPrintPreviewDialog.aspx). The C1PrintPreview dialog has the following advantages over the standard .NET PrintPreviewDialog:

It has a much nicer UI (ToolStrip-based, better mouse/keyboard support, and so on).
It displays pages as they are generated (no need to wait for the whole document to render).
It has PDF export built-in.

The image below shows the same grid, this time rendered by a C1PrintPreview dialog:

Notice the clean toolstrip with much easier-to-use paging controls, and the PDF export button on the left. PDF export is an extremely valuable feature, provided for free by the C1PrintPreview (internally, the PDF export is implemented using our C1Pdf control).

Also notice the document title, rendered in bold above the grid. This illustrates the ability we now have to add external elements (text, images, charts, and so on) to the document. This would not have been possible using the PrintGrid method.

Another important feature is not shown on the image: the document has a "Page N of M" header. This is easy to accomplish because the PrintDocumentGridRenderer provides CurrentPage and PageCount properties that specify how many pages the grid will require (if you were rendering multiple grids on a single document you would have to add the individual page counts yourself).

To implement this in your applications, you should start by copying the C1PrintPreview code into your project. To keep my projects organized, I put this code in a new folder within the project.

Now that you have a class to show the document and export it to PDF, you need to create the document itself. Here's the code that does that:

   // create regular PrintDocument, attach event handlers
  var doc = new PrintDocument();
  doc.DocumentName = "Advanced";
  doc.BeginPrint += doc_BeginPrint;
  doc.PrintPage += doc_PrintPage;
  doc.EndPrint += doc_EndPrint;

  // show new document in Custom print preview dialog
  using (var dlg = new C1PrintPreview.C1PrintPreviewDialog())
  {
    dlg.Document = doc;
    dlg.ShowDialog();
  }

The code creates a new PrintDocument, attaches the event handlers required, and then shows the document in a C1PrintPreview dialog. This is all straightforward .NET printing, nothing new at all.

The interesting part is in the implementation of the event handlers. That is shown below:

   PrintDocumentGridRenderer _pdr;
  void doc_BeginPrint(object sender, PrintEventArgs e)
  {
    // create PrintDocumentGridRenderer to render the grid into this document
    _pdr = new PrintDocumentGridRenderer(_flex);

    // initialize any options you want
    _pdr.Options = PrintGridFlags.ActualSize;
  }

When the document begins printing, we create a new PrintDocumentGridRenderer that will take care of rendering the grid into the document. The grid to be printed is passed as a parameter in the constructor.

Next comes the code that gets executed when the document renders each page:

   void doc_PrintPage(object sender, PrintPageEventArgs e)
  {
    // print header on first page
    if (_pdr.CurrentPage == 0)
    {
      using (var font = new Font("Verdana", 14, FontStyle.Bold))
      {
        // render the title
        var title = "NorthWind Invoices";
        e.Graphics.DrawString(title, font, Brushes.Black, e.MarginBounds.Location);
  
        // push the grid rendering down to make room for the title
        _pdr.FirstPageY =
          e.MarginBounds.Location.Y +
          (int)e.Graphics.MeasureString(title, font).Height;
      }
    }
  
    // print the current page of the grid
    _pdr.PrintPage(e);
  
    // print a header on all pages
    var header = string.Format("NorthWind Invoices - Page {0}/{1}",
      _pdr.CurrentPage,
      _pdr.PageCount);
    var rc = new Rectangle(e.MarginBounds.X, 0, e.MarginBounds.Width, e.MarginBounds.Bottom);
    var fmt = new StringFormat();
    fmt.Alignment = StringAlignment.Far;
    fmt.LineAlignment = StringAlignment.Center;
    using (var font = new Font("Verdana", 10, FontStyle.Bold))
    {
      e.Graphics.DrawLine(Pens.Black, rc.X, rc.Bottom, rc.Right, rc.Bottom);
      e.Graphics.DrawString(header, font, Brushes.Black, rc, fmt);
    }
  
    // keep printing if there are more pages
    e.HasMorePages = _pdr.CurrentPage < _pdr.PageCount;
  }

This looks like a lot of code, but it's all very simple. The first part of the code renders the big bold title that appears before the grid. This is done using straight GDI+ code. After rendering the title, the code sets the FirstPageY property on the grid renderer to a value that specifies the vertical position where the grid should start rendering. This prevents the grid renderer from painting the grid over the title we just created.

After rendering the title, we render the part of the grid that fits on this page. This is done with a single call to the renderer's PrintPage method (shown above in bold). This call retrieves the part of the grid that fits on the current page, taking into account any scaling you may have specified when the renderer was created, and renders the grid on the page.

Finally, the code adds a header to the page. Again, this is straight GDI+ code. The only interesting part here is when we specify the header text using the renderer's CurrentPage and PageCount properties to create a "Page N of M" header. This would have been hard to do without the PrintDocumentGridRenderer class.

Finally, the EndPrint event handler simply sets the renderer variable back to null. This is not strictly necessary, but is always good practice to clean up after yourself:

   void doc_EndPrint(object sender, PrintEventArgs e)
  {
    // done, we no longer need the PrintDocumentGridRenderer.
    _pdr = null;
  }

That's all there is to it. Feel free to play with the sample project that accompanies this article, and have fun adding all this great new functionality to your C1FlexGrid applications.

Download the C1FlexGrid sample project: C1FlexGridPrintPreview_SampleProject.zip

Charting in Reports for WinForms

2009-12-18T18:33:00Z

Charting in Reports for WinForms

This article describes Aggregate Charting, one of the cool new features introduced in ComponentOne Reports for WinForms in the 2009 v3 release.

ComponentOne Reports for WinForms has always supported chart fields using its extensible custom field architecture. The Chart field is implemented as a custom field in the C1.Win.C1Report.CustomFields.2.dll assembly, which is installed with the report designer application and is also included as a sample with full source code (CustomFields). In this article, you’ll see how you can customize chart fields in reports using the C1ReportDesigner application. The C1ReportDesigner application is installed with both ComponentOne Reports for WinForms and ComponentOne Reports for WPF.

Charts in Flat Reports

Creating a simple chart is very easy. Here are the steps required:

Open the C1ReportDesigner application and create or open a report definition file.
Add a Chart field to the report, and then select it to show its properties in the designer's property window.
Set the chart's DataX property to the name of the field whose values should be displayed in the X axis (chart categories).
Set the chart's DataY property to the name of the field whose values should be displayed in the Y axis (chart values).
Optionally set additional properties such as ChartType and DataColor.

For example, the chart below was created based on the NorthWind Products table. In this case, the following properties were set:

DataX = "ProductName"
DataY = "UnitPrice"

Note that for this chart type (Bar), the value axis (where the DataY field is displayed) is the horizontal one, and the category axis is the vertical one.

In this case, a filter was applied to the data in order to limit the number of values shown. Without the filter, the chart would contain too many values and the vertical axis would not be readable.

Other Useful Chart Properties

In addition to the DataX and DataY properties mentioned above, the Chart object provides a few other properties that are commonly used:

ChartType: This property allows you to select the type of chart to display. There are six options: Bar (horizontal bars), Column (vertical columns), Scatter (X-Y values), Line, Area, and Pie.
DataColor: This property selects the color used to draw the bars, columns, areas, scatter symbols, and pie slices. If the chart contains multiple series, then the Chart field automatically generates different shades of the selected color for each series. If you want to select specific colors for each series, use the Palette property instead, and set its value to a semi-colon separated list containing the colors to use (for example "Red;Green;Blue").
FormatY, FormatX: These properties determine the format used to display the values along each axis. For example, setting FormatY to "c" causes the Chart field to format the values along the Y axis as currency values. This is analogous to the Format property in regular report fields.
XMin, XMax, YMin, YMax: These properties allow you to specify ranges for each axis. Setting any of them to -1 cause the Chart to calculate the range automatically. For example, if you set the YMax property to 100, then any values higher than 100 will be truncated and won't appear on the chart.

These properties apply to all chart types. There are a few additional properties that only apply to Pie charts:

ShowPercentages: Each pie slice has a legend that shows the X value for the slice. If the ShowPercentages property is set to true, the legend will also include a percentage value that indicates the size of the slice with respect to the pie. The percentage is formatted using the value specified by the FormatY property. For example, if you set FormatY to "p2", then the legends will include the X value and the percentage with two decimal points (for example "North Region (15.23%)").
RadialLabels: This property specifies that instead of showing a legend on the right side of the chart, labels with connecting lines should be attached to each slice. This works well for pies with few slices (up to about ten).

The Chart field is actually a wrapper for a C1Chart control, which provides all the charting services and has an extremely rich object model of its own. If you want to customize the Chart field even further, you can use the ChartControl property to access the inner C1Chart object using scripts.

For example, the Chart field does not have a property to control the position of the legend. But the C1Chart control does, and you can access this property through the ChartControl property. For example, the script below causes the chart legend to be positioned below the chart instead of on the right:

' place legend below the chart chartField.ChartControl.Legend.Compass = "South"

If you assign this script to the report's OnLoad property, the chart will look like the image below:

The other properties used to create this chart are as follows:

ChartType = Pie
FormatY = "p1"
ShowPercentage = true
Palette = "Red;Gold;Orange;Beige;DarkGoldenrod;Goldenrod;"

Charts with Multiple Series

To create charts with multiple series, simply set the DataY property to a string that contains the names of each data field you want to chart, separated by semi-colons. For example, to create a chart showing product prices and discounts you would set the DataY property as shown below:

DataY = "UnitPrice;Discount"

If you want to specify the color used to display each series, set the Palette property to a list of colors separated by semi-colons. For example, the value displayed below would cause the chart to show the UnitPrice" series in red and the "Discount" series in blue:

Palette = "Red;Blue"

Series with Calculated Values

The DataY property is not restricted to field names. The strings that specify the series are actually treated as full expressions, and are calculated like any regular field in the report.

For example, to create a chart showing the actual price of each field you could set the DataY property to the value shown below:
DataY = "UnitPrice * (1 - Discount)"

Charts in Grouped Reports

Reports for WinForms allows you to create reports with multiple groups. For example, instead of listing all products in a single flat report, you could group products by category. Each group has a header and a footer section that allow you to display information about the group, including titles and subtotals, for example.

If you add a chart to a group header, the chart will display only the data for the current group. By contrast, adding a chart to the report header or footer would include all the data in the report.

To illustrate this, here is a diagram depicting a report definition as shown in the report designer and showing the effect of adding a Chart field to the report header and to a group header:

Continuing with the example mentioned above, if you added a chart to the group header and set the DataX property to "ProductName" and the DataY property to "UnitPrice", the final report would contain one chart for each category, and each chart would display the unit prices for the products in that category.

The images below show screenshots of the report described above with the group headers, the charts they contain, and a few detail records to illustrate:

Chart showing unit prices for products in the "Beverages" category

Chart showing unit prices for products in the "Condiments" category

DataX = "Product Name"
DataY = "Unit Price"

Because the chart automatically selects the data based on the scope of the section that contains it, creating charts in grouped reports is very easy.

Aggregate Charts

The Chart field included with the 2009 v3 release of Reports for WinForms has a powerful new feature called "aggregated charting". This feature allows you to create charts that automatically aggregate data values (DataY) that have the same category (DataX) using an aggregate function of your choice (sum, average, standard deviation, and so on).

To illustrate this feature, consider an "Invoices" report that groups data by country, customer, and order ID. The general outline for the report is as follows:

Now imagine that you would like to add a chart to each Country header displaying the total value of all orders placed by each customer in the current country.

You would start by adding a Chart field to the "Country" header section and then setting the DataX and DataY properties as follows:

DataX = "CustomerName"
DataY = "ExtendedPrice"

This would not work. The data for each country usually includes several records for each customer, and the chart would create one data point for each record. The chart would not be able to guess that you really want to add the values for each customer into a single data point.

To address this scenario, we added an Aggregate property to the Chart field. This property tells the chart how to aggregate values that have the same category into a single point in the chart. The Aggregate property can be set to perform any of the common aggregation functions on the data: sum, average, count, maximum, minimum, standard deviation, and variance.

Continuing with our example, we can now simply set the chart's Aggregate property to "Sum". This will cause the chart to add all "ExtendedPrice" values for records that belong to the same customer into a single data point. The result is shown below:

Notice how each customer appears only once. The values shown on the chart correspond to the sum of the "ExtendedPrice" values for all fields with the same "Customer".

Because the chart appears in the "Country" header field, it is repeated for each country, showing all the customers in that country.

If you place the chart in the report header section, it will aggregate data over the entire report. For example, suppose you want to start the "Invoices" report with a chart that shows the total amount ordered by each salesperson. To accomplish this, you would add a Chart field to the report header section and would set the following properties:

DataX = "Salesperson"
DataY = "ExtendedPrice"
Aggregate = "Sum"

The image below shows the resulting chart:

Since the chart is placed in the report header section, the values displayed include all countries and all customers. If you moved the chart field from the report header to the "Country" group header, you would obtain a similar chart for each country, showing the total amounts sold by each salesperson in that country.

Aggregate charting is a powerful, yet simple and easy-to-use feature. We hope you enjoy it and use it to create spectacular reports!

Download

ComponentOne Reports for WinForms is part of ComponentOne Studio for WinForms. If you have not already, download the latest trial version at http://www.componentone.com/SuperProducts/StudioWinForms/ so you can start creating your reports.

A Visual SQL Query Designer

2009-11-06T15:29:00Z

A Visual SQL Query Designer

Introduction

This article describes the implementation of a QueryDesignerDialog class that allows users to create SQL queries based on a given OleDb connection string.

The designer is similar to the ones found in database tools such as the SQL Server Management Studio and Microsoft Access. It allows end users to build SQL queries with support for sorting, grouping, and filtering.

The main limitation of the QueryDesignerDialog is that it does not parse existing SQL statements. This is a one-way tool, you can use it to create new queries but not to edit existing ones. Also, it only supports OleDb data sources, which includes Sql Server and Access. Future versions may address these limitations.

Background

The first version of the QueryDesignerDialog was written for use with a Report Designer application. I could not find a tool (free or commercial) to do the job the way I wanted, so I decided to write it myself. After that, I re-used it in a few other applications and thought it might be useful to others as well.

Using the Code

The QueryDesignerDialog has two main properties:

ConnectionString: Gets or sets the OleDb connection string used to retrieve the database schema with the list of tables, views, fields, and relations from which the query will be built.
SelectStatement: Gets the SQL statement designed by the user. For now, this is a read-only property. The dialog cannot be used to edit existing SQL statements. Perhaps this will be added in future versions.

The code snippet below shows the QueryDesignerDialog is typically used. You assign it a connection string, show the dialog, and read back the SQL query:

// create the QueryDesignerDialog
using (var dlg = new QueryDesignerDialog())
{
  // set the connection string
  dlg.ConnectionString = ConnectionString;
  
  // show the dialog
  if (dlg.ShowDialog(this) == DialogResult.OK)
  {
    // get the new Sql query and do something with it
    string newSql = dlg.SelectStatement;
    DoSomething(newSql);
  }
}

Implementation

The QueryDesignerDialog relies on two important helper classes:

OleDbSchema: This class extends the ADO.NET DataSet class. It takes an OleDb connection string and fills the DataSet with all the tables, views, columns, relations, and constraints defined in the database (it does not retrieve any data). Applications can then expose these elements in a visual UI to preview the data, create and edit queries, and so on. The OleDbSchema class also provides utility methods for checking table types, encoding their names with brackets when necessary, managing stored procedure parameters, and so on.
QueryBuilder: This class uses the OleDbSchema class and maintains a list of query fields with properties that define sorting, grouping, etc. It is also responsible for building SQL statements based on the query fields and on the database schema.

Selecting a Connection String

In order to use the QueryDesignerDialog, you need an OleDb connection string. Some applications may use a list of pre-defined connection strings, others allow the user to create the connection string at run time. The sample included with this article falls in the second category.

The OleDbConnString class included in the code provides utilities for dealing with connection strings. The main methods in that class are GetConnectionString and EditConnectionString, both of which show the DataLinks dialog used to create or edit connection strings:

string newConnString = OleDbConnString.GetConnectionString(this);

string editConnString = OleDbConnString.EditConnectionString(this, editConnString);

These methods rely on the following system assemblies which must be referenced by the project:

OLEDB32.dll: Contains the DataLinks class (which used to be in MSDASC.DLL). This file can be found at C:\Program Files\Common Files\System\Ole DB\OLEDB32.DLL.
ADODB.dll: This is required to read the COM object passed back from DataLinks. This file can be found at C:\Program Files\Microsoft.NET\Primary Interop Assemblies\ADODB.DLL.

Once the user has picked a connection string, most applications will save it in a list for later re-use. Because the connection strings tend to be quite long, showing them to users may be challenging, so the OleDbConnString class provides a TrimConnectionString method that shortens the connection strings for display purposes, keeping only the provider and data source parts. The sample application provided with the article uses TrimConnectionString to display recently used connection strings in an owner-drawn ComboBox.

Retrieving the database Schema

When you assign a connection string to a QueryDesignerDialog, it starts by retrieving the database schema so it can show the user a list of the tables and views available for use in the query. This job is done by the OleDbSchema class mentioned above. The code looks like this:

// get schema for the new connection string
OleDbSchema schema = OleDbSchema.GetSchema(connectionString);

The OleDbSchema class extends the ADO.NET DataSet class. You can use it to enumerate the elements available in the database, including tables, views, stored procedures, fields, relations, and constraints. If the connection string is invalid, or if some error occurs while getting the schema, then GetSchema returns null.

Some of the information retrieved from the database is stored in the ExtendedProperties property of the tables and fields. For example, views and stored procedures in the database are represented by DataTable objects and can be identified by their ExtendedProperties[TABLE_TYPE] value. The OleDbSchema class provides helper methods that deal with this so callers don't have to. For example, the GetTableType method returns a value that indicates whether a DataTable in an OleDbSchema represents a regular table, a view, or a stored procedure.

The implementation of the OleDbSchema class is based on the OleDbConnection.GetOleDbSchemaTable method. This method allows you to retrieve tables, views, stored procedures, relations, and constraints defined for the connection. Once you have a table, the OleDbDataAdapter.FillSchema method is used to retrieve the fields. If you are interested in the details, please refer to the source code.

The example below shows how the QueryDesignerDialog class populates a TreeView control with the tables and views available in the database:

// update table tree to reflect new connection string
void UpdateTableTree()
{
  // initialize table tree
  TreeNodeCollection nodes = _treeTables.Nodes;
  nodes.Clear();
  var ndTables = new TreeNode(Properties.Resources.Tables, 0, 0);
  var ndViews = new TreeNode(Properties.Resources.Views, 1, 1);

  // populate using current schema
  if (Schema != null)
  {
    // populate the tree
    _treeTables.BeginUpdate();
    foreach (DataTable dt in Schema.Tables)
    {
      // create new node, save table in tag property
      var node = new TreeNode(dt.TableName);
      node.Tag = dt;

      // add new node to appropriate parent
      switch (OleDbSchema.GetTableType(dt))
      {
        case TableType.Table:
          ndTables.Nodes.Add(node);
          node.ImageIndex = node.SelectedImageIndex = 0;
          AddDataColumns(node, dt);
          break;
        case TableType.View:
          ndViews.Nodes.Add(node);
          node.ImageIndex = node.SelectedImageIndex = 1;
          AddDataColumns(node, dt);
          break;
        }
      }

      // add non-empty nodes to tree
      foreach (TreeNode nd in new TreeNode[] { ndTables, ndViews })
      {
        if (nd.Nodes.Count > 0)
        {
          nd.Text = string.Format("{0} ({1})", nd.Text, nd.Nodes.Count);
          nodes.Add(nd);
        }
      }

      // expand tables node
      ndTables.Expand();

      // done
      _treeTables.EndUpdate();
  }
}
void AddDataColumns(TreeNode node, DataTable dt)
{
  foreach (DataColumn col in dt.Columns)
  {
    var field = node.Nodes.Add(col.ColumnName);
    field.Tag = col;
    field.ImageIndex = 2;
    field.SelectedImageIndex = 2;
  }
}

The OleDbSchema class is used internally by the QueryDesignerDialog class, but it is public and can also be used directly by applications that need access to schemas. The sample application included with the article uses it to populate a list with all the tables, views, and stored procedures in the database.

Building SQL Queries

Once the database schema is available, we can use it to build queries. This is done by the QueryBuilder class, which maintains a list of QueryField objects and generates SQL statements. The main properties of the QueryBuilder class are:

ConnectionString: Used to retrieve the database schema. The schema defines the tables and fields used in the query, and also the relations between the tables, which are needed to build the SQL JOIN statements.
QueryFields: A collection of QueryField objects used to build the query. Each QueryField object represents a database field or an expression, and has properties that define sorting, grouping, aliasing, and so on. This is a bindable collection suitable for display in a DataGridView control for example.
Sql: The SQL select statement built to represent the current state of the QueryFields collection.

The Sql property is read-only. It builds the Sql query in parts, based on the QueryFields collection.

First, the SELECT clause is built by scanning the fields and outputting the proper table/view and column names. Next, the QueryFields collection is analyzed to determine how the tables are connected. This allows the QueryBuilder to create the FROM clause with the required JOIN statements, which is by far the most complicated and interesting part of the class. Finally, the ORDER BY and WHERE clauses are built based on the properties of the QueryField objects.

To build the FROM clause of the SQL statement, the QueryBuilder class starts by building a list of tables so that each table on the list is related to the next one. This is accomplished by the InsertRelatedTable method. Next, it scans the list to find the relation that connects each table with the following one. Each relation is then used to build the corresponding JOIN statement.

The QueryField class contains the information that defines each field in the query. It has the following properties:

Column: String that specifies the name of a column within a table (e.g. "FirstName") or an expression (e.g. "LEFT(FirstName, 2)".

Alias: Optional string used to identify the field instead of the Column value. If provided, this value is used as the column name on the query results table. For example, if the Column property is set to "LEFT(FirstName, 1)" and the Alias property is set to "FirstInitial", the output table will contain a column named "FirstInitial".

Table: String that identifies the table that contains the column. This property is read-only; it is provided only for binding purposes (so the table names appear on the field grid described later).

Output: Boolean value that determines whether the field should be included in the output table. This is used to hide fields that are needed to define the query but should not appear on the output (e.g. fields used in calculations or to provide connections between related tables).

GroupBy: Aggregate to use when grouping the field. This column is used only if the QueryBuilder.GroupBy property is set to true. In this case, the original data records are grouped and represented by an aggregate such as a sum or average. For example, to create a query showing the average product price per category, you would use two fields: "CategoryName", with GroupBy set to GroupBy; and "ProductPrice", with GroupBy set to Average.

Sort: Specifies whether the field should be used to sort the output in ascending or descending order.

Filter: A filter expression used to select the records included in the output. If provided, the expression should be of the format [OPERATOR] [VALUE] or BETWEEN [VALUE1] AND [VALUE2]. To reduce the possibility of syntax errors when specifying the Filter value, the QueryDesignerDialog class provides an editor for this property instead of allowing users to type the expressions directly.

Designing SQL Queries

The QueryDesignerDialog class provides the user interface for creating queries. It uses the QueryBuilder class described above and adds the following UI elements:

Table/View tree: This is a TreeView control containing all the tables and views in the data source and all the fields within each table/view. Fields can be double-clicked or dragged onto the QueryField grid to be added to the query. The TreeView has a context menu that allows users to remove specific tables/views from the list or navigate to tables that are related to the one that is currently selected.

QueryField grid: This is a DataGridView control that shows the fields currently included in the query and their properties. Users may reorder the fields by dragging the grid rows, remove fields by deleting rows, and edit fields directly on the grid.

Sql view: This is a TextBox control that shows the SQL statement as the user adds and edits individual fields on the grid. The TextBox is read-only. Users can see the SQL statement and copy it to the clipboard, but they cannot change it by typing on the TextBox.

ToolStrip: The ToolStrip on top of the dialog contains buttons for toggling the query's GroupBy property, editing query properties, checking the SQL syntax, previewing the query results, and for clearing the query.

The Table/View tree is the element that deviates the most from the traditional query design tools available in tools such as SQL Server Management Studio and Microsoft Access. It shows all the tables by default, instead of asking users to add and remove them individually. Tables and views are shown as tree nodes, rather than as small floating lists. The tree-based UI is substantially simpler. The main drawback it has is that the connections between tables are not readily visible to the user. This is somewhat alleviated by the context menu that shows related tables on demand.

The image below shows the QueryDesignerDialog in action, creating a query against the popular AdventureWorks database:

QueryField grid implementation

The QueryField grid is a DataGridView that displays a list of QueryField objects. By default, the DataGridView uses text boxes for editing non-boolean cells and check boxes for booleans. This is not ideal for editing values that are enumerations, such as the Sort and GroupBy properties of the QueryField class. Those are much easier to edit with combo box controls instead. Also, we wanted to use a custom editor for the Filter field, which is of type string but has formatting requirements that are better handled with a custom editor.

These requirements seem very common, so I think showing the code here might be useful to some developers. The code below shows how you can replace regular DataGridView columns with combo box ones for fields that are enumerations.

// replace regular grid columns combo box columns for enum types
void FixGridColumns()
{
  for (int i = 0; i < _grid.Columns.Count; i++)
  {
    var col = _grid.Columns[i];
    if (col.ValueType.IsEnum)
    {
      // create combo column for enum types
      var cmb = new DataGridViewComboBoxColumn();
      cmb.ValueType = col.ValueType;
      cmb.Name = col.Name;
      cmb.DataPropertyName = col.DataPropertyName;
      cmb.HeaderText = col.HeaderText;
      cmb.DisplayStyleForCurrentCellOnly = true;
      cmb.DataSource = Enum.GetValues(col.ValueType);
      cmb.Width = col.Width;

      // replace original column with new combo column
      _grid.Columns.RemoveAt(i);
      _grid.Columns.Insert(i, cmb);
    }
  }
}

The sample also replaces the "Filter" column with one that shows a button instead of a text box. Clicking the button brings up a filter editor dialog which can be used to edit the filter value. I would rather give users a choice, allowing them to type filter values directly into the cell or click a button on the right of the cell to show the editor dialog. That is in the to-do list for a future version.

Sample Application

The sample application included with this article is a dialog that allows users to select a connection string, see all the tables, views, and stored procedures in the corresponding database, create queries against the database, and see the corresponding data. It could be used as a data source selection tool in applications such as report designers (which is actually why was written in the first place).

The image below shows the sample application in action:

The application has a ToolStrip along the top of the main dialog.

The ToolStrip contains a ComboBox that provides a list of recently used connection strings and allows users to type or paste connection string values.

The dropdown part of the ComboBox is owner-drawn to show a trimmed version of the connections strings which are easier to read than the full version. The owner-draw code uses the TrimConnectionString method in the OleDbConnString class as shown below:

// trim items in combo box (they're very long)
void cmb_DrawItem(object sender, DrawItemEventArgs e)
{
  var fmt = new StringFormat();
  fmt.LineAlignment = StringAlignment.Center;
  fmt.Trimming = StringTrimming.EllipsisPath;

  var text = (string)_cmbConnString.Items[e.Index];
  text = OleDbConnString.TrimConnectionString(text);

  var brush = (e.State & DrawItemState.Selected) != 0
    ? SystemBrushes.HighlightText
    : SystemBrushes.WindowText;

  e.DrawBackground();
  e.Graphics.DrawString(text, _cmbConnString.Font, brush, e.Bounds, fmt);
  e.DrawFocusRectangle();
}

The list of recent connection strings is saved as an application setting so it can be reused across sessions.

The button next to the ComboBox allows users to create new connection strings using the familiar "DataLink" dialog. The button uses the EditConnectionString method in the OleDbConnString class as shown below:

// pick a new connection
void _btnConnPicker_Click(object sender, EventArgs e)
{
  // release mouse capture to avoid wait cursor
  _toolStrip.Capture = false;

  // get starting connection string
  // (if empty or no provider, start with SQL source as default)
  string connString = _cmbConnString.Text;
  if (string.IsNullOrEmpty(connString) || 
      connString.IndexOf("provider=", StringComparison.OrdinalIgnoreCase) < 0)
  {
    connString = "Provider=SQLOLEDB.1;";
  }

  // let user change it
  ConnectionString = OleDbConnString.EditConnectionString(this, connString);
}

This code invokes the "DataLink" dialog seen below:

The next button (with a magic wand image) invokes the QueryDesignerDialog which allows users to design SQL queries. Once the query is ready, it is shown in a TextBox on the second tab of the main form. The code that invokes the QueryDesignerDialog looks like this:

// invoke SQL builder
void _btnSqlBuilder_Click(object sender, EventArgs e)
{
  using (var dlg = new QueryDesignerDialog())
  {
    dlg.Font = this.Font;
    dlg.ConnectionString = ConnectionString;
    if (dlg.ShowDialog(this) == DialogResult.OK)
    {
      _txtSql.Text = dlg.SelectStatement;
      _tab.SelectedTab = _pgSql;
      UpdateUI();
    }
  }
}

The code creates a QueryDesignerDialog, initializes its ConnectionString property, then shows the dialog and retrieves the results by reading the dialog's SelectStatement property.

The last button (with a preview image) loads the data from the currently selected source (table, view, stored procedure, or SQL statement) into a DataTable and shows the table on a modal dialog. The implementation is given below:

// preview data for currently selected node
void PreviewData()
{
  // create table to load with data and display
  var dt = new DataTable("Query");

  // if a table/view is selected, get table name and parameters
  if (_tab.SelectedTab == _pgTables)
  {
    // get table/view name
    var table = _treeTables.SelectedNode.Tag as DataTable;
    dt.TableName = table.TableName;

    // get view parameters if necessary
    var parms = OleDbSchema.GetTableParameters(table);
    if (parms != null && parms.Count > 0)
    {
      var dlg = new ParametersDialog(parms);
      dlg.Font = Font;
      if (dlg.ShowDialog(this) != DialogResult.OK)
      {
        return;
      }
    }
  }

  // get data
  try
  {
    using (var da = new OleDbDataAdapter(SelectStatement, ConnectionString))
    {
      // get data
      da.Fill(0, MAX_PREVIEW_RECORDS, dt);

      // show the data
      using (var dlg = new DataPreviewDialog(dt, Font, Size))
      {
        dlg.ShowDialog(this);
      }
    }
  }
  catch (Exception x)
  {
    Warning(Properties.Resources.ErrGettingData, x.Message);
  }
}

The first part of the code handles the case where the TreeView page is selected. It gets the name of the table that is currently selected and uses the ParametersDialog helper class to prompt the user for any required parameters. The parameters entered by the user are stored as extended properties of the selected table.

Next, the code builds an OleDbDataAdapter to read the actual data. The parameters are given by the SelectStatement and ConnectionString properties. The SelectStatement is a SQL string that reflects the current user selection. It could be either the node currently selected on the TreeView or the custom SQL generated with the QueryDesignerDialog.

This is the code that implements the SelectStatement property:

public string SelectStatement
{
  get
  {
    // table/view/sproc
    if (_tab.SelectedTab == _pgTables)
    {
      var nd = _treeTables.SelectedNode;
      return nd == null || nd.Tag == null || _schema == null
        ? string.Empty
        : OleDbSchema.GetSelectStatement(nd.Tag as DataTable);
    }
    else // explicit sql statement
    {
      return _txtSql.Text;
    }
  }
}

The implementation uses the GetSelectStatement method of the OleDbSchema class. This method returns a string that depends on the type of table passed as a parameter. If the table is a regular table or view, the method returns a select statement. If the table represents a stored procedure, the method returns an exec statement including the name of the stored procedure and the parameter values stored as extended properties. In our case, the parameter values were set by the ParametersDialog helper used earlier.

Below the ToolStrip, there is a TabControl with two pages. The first contains a TreeView that lists all the tables, views, and stored procedures found in the database defined by the current connection string. Users may preview the data by double-clicking the tree nodes or by selecting a node and clicking the preview button.

The second tab page contains a TextBox that contains the SQL statement generated by the QueryDesignerDialog. This TextBox is read/write, so users can cut, paste, or edit the SQL statement manually if they choose to do so.

Limitations

The main limitation in this initial version is the fact that it can generate new SQL statements, but it cannot edit existing ones. To overcome this limitation, the next version will need a SQL parser that will take an existing SQL string apart and generate the corresponding QueryField objects. This may be easy to do in some cases, but SQL is a rich and flexible language, so the task is not trivial and that is why it is not included here.

Another limitation is the fact that the whole implementation relies on OleDb connection strings. This is not a serious limitation since OleDb is a flexible data source, supporting Sql Server, Oracle, ODBC, Access (Jet), and many others. However, native implementations may be more efficient than the corresponding OleDb version, and the SQL may require syntax adjustments. I have not looked into this very much at all, so if you have feedback in this area I am very interested.

Finally, although the UI was loosely based on traditional tools like SQL Server Management Studio and Access, it deviates from that in the way tables and views are presented to the users. Personally, I like the approach used here, using a simple TreeView that is complete and easy to navigate, and configure by expanding and collapsing nodes with the mouse or keyboard. But I am sure many people will prefer the more traditional approach showing a pane with tables represented by floating lists and lines showing the connections between the tables. I am interested in your feedback in this area as well.

Conclusion

Thanks for your interest. I hope you enjoy the QueryDesignerDialog class and would love to get your feedback.

Spell-check OleDb Databases

2009-10-14T15:37:00Z

Introduction

This article describes the implementation of C1DBSpellChecker, a utility that performs spell-checking on OleDb databases including SqlServer and Access.

After reading this article, you will be able to specify a connection string, and then select a table and one or more string fields on the table to spell-check. Typing errors will then be displayed in a grid where they can be corrected. When all corrections have been made, the changes will be saved back to the database.

Background

Spell-checking is an important tool used by most people when creating documents of all kinds, from formal reports to simple e-mail messages. Many popular applications provide built-in spell-checking that makes this task easy and almost automatic.

Unfortunately, not all applications have built-in spell-checking. Visual Studio is a good example. Developers use Microsoft Visual Studio to create applications, web pages, and documentation. Unless they have a package such as ComponentOne IntelliSpell, it is likely that spelling mistakes will creep into their work and eventually surface on a website or commercial application.

Even developers that use ComponentOne IntelliSpell often rely on content that is stored in databases and hasn't been properly spell-checked. For example, the popular AdventureWorks database contains typos such as:

alluminum, alumunim (Aluminum)
comparible (Comparable)
securly (Securely)
funtionality (functionality)
manuverability (maneuverability)
responsivness (responsiveness)
you'l find (you'll find)

Spelling errors like this aren't very professional on a catalog or website. Surprisingly, there are few or no tools for spell-checking databases. The C1DBSpellChecker application was designed to fill this need.

Application Structure

The C1DBSpellChecker application performs the following tasks:

Allow the user to select a connection string.
Get the schema for the selected connection (list of tables and fields).
Allow the user to pick a table and one or more string fields.
Load the data and keep only rows that contain spelling errors.
Show the spelling errors on a grid and allow the user to fix them.
Save the changes back to the database.

These tasks are described in the following sections.

Select a connection string

The first step required to spell-check a database is selecting the database to use. This is done by specifying a connection string. We use the ADODB and MSDASC libraries to accomplish this. These libraries are provided by Microsoft and can be freely distributed. They provide a user interface for creating and editing OleDb connection strings.

We decided to use the OleDb data provider because it provides great flexibility, allowing connections to Sql Server, Access, and many others.

The code used to get and edit the connection strings is simple (the version below omits error checking code for clarity; please refer to the source for a more complete version):

// prompt user for a connection string
string PromptConnectionString(string connString)
{
  // create objects we'll need
  var dlinks = new MSDASC.DataLinksClass();
  var conn = new ADODB.ConnectionClass();

  // show connection picker dialog
  object obj = conn;
  dlinks.hWnd = (int)Handle;
  if (dlinks.PromptEdit(ref obj))
  {
    connString = conn.ConnectionString;
  }

  // done
  return connString;
}

Connection strings created by the user are added to a ComboBox and saved as part of the application settings, so they can be reused across sessions. The code that performs this task is listed below:

// form loaded: load recently used connection strings
protected override void OnLoad(EventArgs e)
{
  var mru = Properties.Settings.Default.RecentConnections;
  if (mru != null)
  {
    foreach (string connString in mru)
    {
      _cmbConnString.Items.Add(connString);
    }
  }
  base.OnLoad(e);
}

// form closing: save recently used connection strings
protected override void OnFormClosing(FormClosingEventArgs e)
{
  var mru = new System.Collections.Specialized.StringCollection();
  foreach (string item in _cmbConnString.Items)
  {
    mru.Add(item);
  }
  Properties.Settings.Default.RecentConnections = mru;
  Properties.Settings.Default.Save();
  base.OnFormClosing(e);
}

Because the connection strings are fairly long, the application uses the owner-draw feature of the ComboBox control to trim the connection strings when they are displayed in the drop down. This makes it a lot easier for users to find the connections they are looking for.

The owner-draw code for the ComboBox is as follows:

public Form1())
{
  InitializeComponent();

  // make combo owner-drawn
  var cmb = _cmbConnString.ComboBox;
  cmb.DrawMode = DrawMode.OwnerDrawFixed;
  cmb.DrawItem += cmb_DrawItem;
}

// trim items in combo using ellipsis (they're very long)
void cmb_DrawItem(object sender, DrawItemEventArgs e)
{
  var fmt = new StringFormat();
  fmt.LineAlignment = StringAlignment.Center;
  fmt.Trimming = StringTrimming.EllipsisPath;

  var text = (string)_cmbConnString.Items[e.Index];
  text = TrimConnectionString(text);

  var brush = (e.State & DrawItemState.Selected) != 0
    ? SystemBrushes.HighlightText
    : SystemBrushes.WindowText;

  e.DrawBackground();
  e.Graphics.DrawString(text, _cmbConnString.Font, brush, e.Bounds, fmt);
  e.DrawFocusRectangle();
}

// trim connection string for display
string[] _keys = new string[] { "Provider", "Initial Catalog", "Data Source" };
string TrimConnectionString(string text)
{
  var sb = new StringBuilder();
  foreach (var item in text.Split(';'))
  {
    foreach (var key in _keys)
    {
      if (item.IndexOf(key, StringComparison.InvariantCultureIgnoreCase) > -1)
      {
        if (sb.Length > 0)
        {
          sb.Append("...");
        }
        sb.Append(item.Split('=')[1].Trim());
      }
    }
  }
  return sb.ToString();
}

The code works by splitting the connection string into key/value pairs and then keeping only the parts that help identify the connection. Configuration options are removed for clarity. For example, here is a typical OleDb connection string in all its glory:

Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=AdventureWorks;Data Source=spock\sqlexpress

And here is the trimmed version:

SQLOLEDB.1...AdventureWorks...spock\sqlexpress

The trimmed version shows only the information that is relevant for selecting an entry from a long list.

Get the database schema

Once the user picks a connection string, the next step is to use the connection to obtain a database schema. The schema is a list of the tables, fields, and relations in the database. It describes the structure of the database.

We retrieve the database schema using an auxiliary class called OleSDbSchema. This class extends the system DataSet class with a ConnectionString property. Setting this property populates the OleSDbSchema with tables that have the same structure as the tables in the database (but no data).

The code below shows how the OleSDbSchema class obtains the database schema (this version is simplified for clarity; please refer to the source code for a complete version, including code that retrieves constraints, relations, and stored procedures):

// Gets or sets the connection string used to fills the schema.
public string ConnectionString
{
  get { return _connString; }
  set
  {
    if (value != _connString)
	{
	  _connString = value;
	  GetSchema();
    }
  }
}
void GetSchema()
{
  // initialize this DataSet
  this.Reset();

  // go get the schema
  EnforceConstraints = false;
  using (var conn = new OleDbConnection(connString))
  {
    conn.Open();
	GetTables(conn);
	conn.Close();
  }
}
void GetTables(OleDbConnection conn)
{
  // add tables
  var dt = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
  foreach (DataRow dr in dt.Rows)
  {
    // get type (table/view)
    var type = (string)dr[TABLE_TYPE];
	if (type != TABLE && type != VIEW && type != LINK)
    {
	  continue;
    }

    // create table
    var name = (string)dr[TABLE_NAME];
    var table = new DataTable(name);
    table.ExtendedProperties[TABLE_TYPE] = type;

    // save definition in extended properties
    foreach (DataColumn col in dt.Columns)
    {
      table.ExtendedProperties[col.ColumnName] = dr[col];
    }

    // get table schema and add to collection
    try
    {
      var select = GetSelectStatement(table);
      var da = new OleDbDataAdapter(select, conn);
      da.FillSchema(table, SchemaType.Mapped);
      Tables.Add(table);
    }
    catch { }
  }
}

The code shows how tables and columns are created. Notice that the ExtendedProperties property is used to store additional information about each element. This allows us, for example, to distinguish between regular tables, views, and stored procedures since all these elements are represented by DataTable objects within the OleDbSchema.

The OleDbSchema class is described in detail in a separate article. We only use its basic features in this project.

Select table and fields to spell-check

Once the schema has been obtained, it is used to populate a TreeView control. The TreeView has nodes that represent the tables in the database, and each node has child nodes that represent the fields. Only string fields are included since they are the only ones eligible for spell-checking.

This is the code that populates the TreeView control:

// update table tree to reflect new connection string
void UpdateTableTree()
{
  // initialize table tree
  TreeNodeCollection nodes = _treeTables.Nodes;
  nodes.Clear();

  // populate using current schema
  _treeTables.BeginUpdate();
  foreach (DataTable dt in _schema.Tables)
  {
    if (_schema.GetTableType(dt) == TableType.Table)
    {
      // create new node, save table in tag property
      var node = new TreeNode(dt.TableName);
      node.Tag = dt;

      // add string fields to node
      foreach (DataColumn col in dt.Columns)
      {
        if (col.DataType == typeof(string))
        {
          var ndCol = node.Nodes.Add(col.ColumnName);
          ndCol.Tag = col;
        }
      }

      // add new node to the tree
      if (node.Nodes.Count > 0)
      {
        nodes.Add(node);
      }
    }
  }

  // done
  _treeTables.Sort();
  _treeTables.EndUpdate();
}

The TreeView has check boxes next to each table and field. The check boxes require a fair amount of code to work in an intuitive, automatic manner. The code must ensure that only one table is selected, and is must handle the check boxes next to tables and fields. Specifically, checking a table automatically checks all its fields and un-checks all other tables. Un-checking a table will un-check all its fields. And checking a field automatically checks the parent table and un-checks all other tables.

Here is the code that manages the check boxes:

// handle check boxes
void _treeTables_AfterCheck(object sender, TreeViewEventArgs e)
{
  if (_updatingTree)
  {
    return;
  }

  // start updating...
  _updatingTree = true;

  // get node that was clicked
  var n = e.Node;

  // clicked on table node
  if (n.Tag is DataTable)
  {
    // apply check state to all child nodes (fields)
    SetCheck(n, n.Checked);

    // if this table is checked, uncheck all other tables
    if (n.Checked)
    {
      foreach (TreeNode c in n.TreeView.Nodes)
      {
        if (c != n)
        {
          SetCheck(c, false);
        }
      }
    }
  }

  // clicked on column node
  if (n.Tag is DataColumn)
  {
    // update parent node state
    bool check = false;
    foreach (TreeNode c in n.Parent.Nodes)
    {
      check |= c.Checked;
    }
    n.Parent.Checked = check;

    // if this node is checked, uncheck all other tables
    if (n.Checked)
    {
      foreach (TreeNode c in n.TreeView.Nodes)
      {
        if (c != n.Parent)
        {
          SetCheck(c, false);
        }
      }
    }
  }

  // done updating...
  _updatingTree = false;
}
void SetCheck(TreeNode n, bool check)
{
  n.Checked = check;
  foreach (TreeNode c in n.Nodes)
  {
    c.Checked = check;
  }
}

Load and spell-check the selected data

Once the user has selected one or more fields to spell-check, we need to read the data, spell-check each field, and keep only the records that have spelling errors in them. We use an OleDbReader to read the records and a C1SpellChecker to spell-check each one. Records that contain spelling errors are stored in a DataTable to be edited by the user.

Here is the code that reads the data and performs the spell-checking:

// get data table with the rows that contain spelling errors
DataTable GetSpellingErrors(OleDbDataAdapter da, List<string> columns)
{
  // create table and adapter
  var dt = new DataTable();

  // get table schema
  da.FillSchema(dt, SchemaType.Mapped);

  // make columns not being spell-checked read-only
  foreach (DataColumn col in dt.Columns)
  {
    if (!columns.Contains(col.ColumnName))
    {
      col.ReadOnly = true;
    }
  }

  // read rows with DataReader
  var cmd = da.SelectCommand;
  cmd.Connection.Open();
  using (var reader = cmd.ExecuteReader())
  {
    while (reader.Read())
    {
      // read a row
      var dr = dt.NewRow();
      foreach (DataColumn col in dt.Columns)
      {
        var index = col.Ordinal;
        dr[index] = reader.GetValue(index);
      }

      // check for errors
      bool hasErrors = false;
      foreach (string col in columns)
      {
        var text = dr[col] as string;
        if (!string.IsNullOrEmpty(text))
        {
          var errors = _spell.CheckText(text);
          if (errors.Count > 0)
          {
            hasErrors = true;
          }
        }
      }

      // keep rows that have errors
      if (hasErrors)
      {
        dt.Rows.Add(dr);
      }
    }
  }

  // table has no changes
  dt.AcceptChanges();

  // done
  return dt;
}

A simpler but less efficient alternative would be to read all the data into the DataTable first, then spell-check it and remove the rows that don't contain any spelling mistakes. The version listed above is better because rows without errors are discarded immediately and never added to the DataTable.

Show errors and allow editing

Now that we have a DataTable with all the errors, the next step is to show the table to the user so he can review and fix them. We use a C1FlexGrid control to do that. We use the grid's owner-draw feature to mark the typos with the familiar red wavy underlines, and attach a C1SpellChecker to the grid editor so the user gets a nice context menu with spelling suggestions while editing the cells.

Here is the code that binds the grid to the table containing the errors and implements the owner-draw logic:

// bind the grid and enable owner-draw to show the errors
_flex.DataSource = _dtErrors;
_flex.DrawMode = C1.Win.C1FlexGrid.DrawModeEnum.OwnerDraw;
_flex.OwnerDrawCell += _flex_OwnerDrawCell;

// draw wavy read lines under spelling errors
void _flex_OwnerDrawCell(object sender, C1.Win.C1FlexGrid.OwnerDrawCellEventArgs e)
{
  // spell-check editable cells (unless we're just measuring)
  if (!e.Measuring && 
      _flex.Cols[e.Col].AllowEditing && 
      e.Row >= _flex.Rows.Fixed)
  {
    var text = _flex.GetDataDisplay(e.Row, e.Col);
    var errors = _spell.CheckText(text);

    if (errors.Count > 0)
    {
      // draw cell as usual
      e.Style = _errorStyle;
      e.DrawCell();

      // build list with error ranges
      var ranges = new CharacterRange[errors.Count];
      for (int i = 0; i < errors.Count; i++)
      {
        ranges[i] = new CharacterRange(
          errors[i].Start, errors[i].Length);
      }

      // create StringFormat to locate the error ranges
      var sf = new StringFormat(e.Style.StringFormat);
      try
      {
        sf.SetMeasurableCharacterRanges(ranges);
      }
      catch { }
      
      // locate the error ranges
      var rc = e.Style.GetTextRectangle(e.Bounds, null);
      var rgns = e.Graphics.MeasureCharacterRanges(
        text, e.Style.Font, rc, sf);

      // draw wavy red underline for each range
      foreach (var rgn in rgns)
      {
        rc = Rectangle.Truncate(rgn.GetBounds(e.Graphics));
        for (Point pt = new Point(rc.X, rc.Bottom); 
          pt.X + 2 < rc.Right; 
          pt.X += 4)
        {
          e.Graphics.DrawLines(Pens.Red, new Point[] 
          { 
            new Point(pt.X, pt.Y), 
            new Point(pt.X + 2, pt.Y - 2), 
            new Point(pt.X + 4, pt.Y) 
          });
        }
      }
    }
  }
}

And here is the code that connects a C1SpellChecker with the grid editor so the user can see the underlines while editing the cell and get a nice context menu with spelling suggestions and commands:

// connect event handler to customize the grid editor
_flex.SetupEditor += _flex_SetupEditor;

// enable spell checker in cell editor
void _flex_SetupEditor(object sender, RowColEventArgs e)
{
    var tb = _flex.Editor as TextBox;
    if (tb != null)
    {
        _spell.SetActiveSpellChecking(tb, true);
    }
}

The form also contains a button that performs a modal (dialog-based) spell-check over the entire grid. That can be more convenient and comfortable than searching for the errors by inspecting each grid cell visually.

Implementing the modal check option is easy:

// perform modal check on the grid
private void _btnSpell_Click(object sender, EventArgs e)
{
  var cols = new List<string>();
  foreach (Column col in _flex.Cols)
  {
    if (col.AllowEditing)
    {
      cols.Add(col.Name);
    }
  }
  var speller = new FlexGridSpellChecker(_flex, cols.ToArray());
  _spell.CheckControl(speller);
}

The code starts by building a list with the columns that are to be spell-checked. Then is uses the list to build a FlexGridSpellChecker object that implements the spell-checking interface required by the C1SpellChecker control on behalf of the grid. The FlexGridSpellChecker implementation is not especially interesting, so we won't reproduce it here. Please refer to the source code if you are interested in the details.

Save changes back to the database

Once the user has reviewed and corrected the errors, the only task remaining is saving the changes back into the database. To do this, we need an OleDbDataAdapter object with select and update commands. The select command is used to retrieve the data, and the update command is used to write changes back into the database.

Here is the code that creates the OleDbDataAdapter and writes the changes back into the database:

// save changes in the data table
void SaveChanges()
{
  try
  {
    // build DataAdapter with select and update commands
    var sql = _schema.GetSelectStatement(table);
    var da = new OleDbDataAdapter(sql, ConnectionString);
    var cmdBuilder = new OleDbCommandBuilder(da);
    da.UpdateCommand = cmdBuilder.GetUpdateCommand();
    
    // save the changes
    da.Update(_dtErrors);

    // mark table as clean
    _dtErrors.AcceptChanges();

    // disable save changes button until there are more changes
    _btnSaveChanges.Enabled = false;
  }
  catch (Exception x)
  {
    // something went wrong? inform the user
    var dr = MessageBox.Show(
      this,
      string.Format(Properties.Resources.FailedToSave, x.Message),
      Application.ProductName,
      MessageBoxButtons.OK,
      MessageBoxIcon.Error);
  }
}

This concludes the last part of the application. We walked over the steps required to select the database, spell-check its content, display the errors, allow users to fix them, and save the corrections back to the database. It was a long and fun journey!

Conclusion

We developed the C1DBSpellChecker application with two objectives in mind:

To create a useful tool that fills a real need. Many sites and applications are driven by databases that contain spelling errors, and fixing them without a proper tool is difficult.
To show some useful techniques required to implement real applications in .NET. The techniques covered include database management, owner-draw controls, application settings and resources, and more.

We hope both objectives have been achieved and you find them useful. If you have requests or suggestions for improving this document or the application, please post on our site. Thanks in advance.

Download the full project with source code: C1DBSpellChecker.zip

Enhanced PrintPreviewDialog Class with PDF Output

2009-10-06T18:53:00Z

Introduction

This article describes the implementation of an enhanced PrintPreviewDialog class that provides PDF output in addition to the standard print and preview capabilities.

Background

The PrintPreviewDialog is convenient and easy to use. All you need to do is create an instance of the dialog class, assign your PrintDocument object to the Document property, and call the ShowDialog method.

However, PrintPreviewDialog has some shortcomings, including the following:

The entire document must be rendered before the preview appears. This is annoying for long documents.
There are no options for choosing the printer, adjusting the page layout, or selecting specific pages to print.
The dialog looks outdated. It hasn't changed since .NET 1.0, and it wasn't exactly cutting-edge even back then.
The dialog allows little or no customization.
There is no option to export the document to other formats such as PDF.
Page images are cached in the control, which limits the size of the documents that can be previewed.

The CoolPrintPreviewDialog class presented here addresses these shortcomings. It is just as easy to use as the standard PrintPreviewDialog, but has the following enhancements:

Pages can be previewed as soon as they are rendered. The first page is shown almost instantly and subsequent pages become available while the user browses the first ones.
The "Print" button shows a dialog that allows users to select the printer and page ranges to print. A "Page Layout" button is also available so users can change page size, orientation, and margins.
The dialog uses a ToolStrip control instead of the old toolbar.
You have the source and can customize everything from appearance to behavior.
The control creates a list of images which can be exported to other formats including PDF.

Using the Code

Using the CoolPrintPreviewDialog is as easy as using the traditional PrintPreviewDialog. You instantiate the control, set the Document property to the PrintDocument you want to preview, then call the dialog's Show method.

If you have code that uses the PrintPreviewDialog class, switching to the CoolPrintPreviewDialog only requires changing one line of code. For example:

// using a PrintPreviewDialog
using (var dlg = new PrintPreviewDialog())
{
  dlg.Document = this.printDocument1;
  dlg.ShowDialog(this);
}

// using a CoolPrintPreviewDialog
using (var dlg = new CoolPrintPreview.CoolPrintPreviewDialog())
{
  dlg.Document = this.printDocument1;
  dlg.ShowDialog(this);
}

Generating the Preview Images

The core of the CoolPrintPreviewDialog class is a CoolPreviewControl that generates and shows the page previews.

The PrintDocument object has a PrintController property that specifies an object responsible for creating the Graphics objects where the document is rendered. The default print controller creates Graphics objects for the default printer and is not interesting in this case. But .NET also defines a PreviewPrintController class that creates metafiles instead. These remain available to the caller to be shown in the preview area.

The CoolPreviewControl works by temporarily replacing the document's original print controller with a PreviewPrintController, calling the document's Print method, and getting the page images while the document is rendered. The images represent pages in the document, and are scaled and displayed in the control just like any regular Image object.

The code that creates the page previews looks like this (this code is simplified for clarity, refer to the source for a better version):

// list of page images
List<Image> _imgList = new List<Image>();

// generate page images
public void GeneratePreview(PrintDocument doc)
{
  // save original print controller
  PrintController savePC = doc.PrintController;

  // replace it with a preview print controller  
  doc.PrintController = new PreviewPrintController();
  
  // hook up event handlers
  doc.PrintPage += _doc_PrintPage;
  doc.EndPrint += _doc_EndPrint;

  // render the document
  _imgList.Clear();
  doc.Print();

  // disconnect event handlers
  doc.PrintPage -= _doc_PrintPage;
  doc.EndPrint -= _doc_EndPrint;
  
  // restore original print controller
  doc.PrintController = savePC;
}

The code installs the controller and hooks up the event handlers, then calls the Print method to generate the pages, and cleans up when it's done.

When the Print method is invoked, the document starts firing events. The PrintPage and EndPrint event handlers capture the pages as soon as they are rendered and add them to an internal image list.

The event handlers also call the Application.DoEvents method to keep the dialog responsive to user actions while the document renders. This allows users to switch pages, adjust the zoom factor, or cancel the document generation process. Without this call, the dialog would stop operating until the whole document finishes rendering.

This is the code that does all this:

void _doc_PrintPage(object sender, PrintPageEventArgs e)
{
  SyncPageImages();
  if (_cancel)
  {
    e.Cancel = true;
  }
}
void _doc_EndPrint(object sender, PrintEventArgs e)
{
  SyncPageImages();
}
void SyncPageImages()
{
  // get page previews from print controller
  var pv = (PreviewPrintController)_doc.PrintController;
  var pageInfo = pv.GetPreviewPageInfo();
  
  // add whatever images are missing from our internal list
  for (int i = _img.Count; i < pageInfo.Length; i++)
  {
    // add to internal list
    _img.Add(pageInfo[i].Image);

    // fire event to indicate we have more pages
    OnPageCountChanged(EventArgs.Empty);
    
    // if the page being previewed changed, refresh to show it
    if (StartPage < 0) StartPage = 0;
    if (i == StartPage || i == StartPage + 1)
    {
      Refresh();
    }
    
    // keep application responsive
    Application.DoEvents();
  }
}

This is the core of the preview code. The rest is concerned with housekeeping tasks such as scaling the preview images, updating the scrollbars, handling navigation buttons, mouse, keyboard, and so on. Please refer to the source code for the implementation details.

Updating the Page Layout

The preview dialog allows users to update the print layout. This is very easy to implement, thanks to the .NET PageSetupDialog class. Here is the code that gets called when users click the "Page Layout" button:

void _btnPageSetup_Click(object sender, EventArgs e)
{
  using (var dlg = new PageSetupDialog())
  {
    dlg.Document = Document;
    if (dlg.ShowDialog(this) == DialogResult.OK)
    {
      // user changed the page layout, refresh preview images
      _preview.RefreshPreview();
    }
  }
}

The code shows a PageSetupDialog that allows the user to change the paper size, orientation, and margins. Changes made by the user are reflected in the document's DefaultPageSettings property.

If the user clicks OK, then we assume that the page layout has been modified, and call the RefreshPreview method on the preview control. This method regenerates all preview images using the new settings, so the user can see the changes applied to margins, page orientation, and so on.

Printing the Document

When the user clicks the "Print" button, the dialog shows a PrintDialog so the user can select the printer, page range, or change his mind and cancel the printing.

Unfortunately, page range selections are not honored if you simply call the Print method directly on the document. To remedy this, the dialog calls the Print method on the enhanced preview control instead. That implementation uses the page images already stored in the control, and honors page ranges defined in the document's PrinterSettings properties.

This is the code that gets called when the user clicks the "Print" button:

void _btnPrint_Click(object sender, EventArgs e)
{
  using (var dlg = new PrintDialog())
  {
    // configure dialog
    dlg.AllowSomePages = true;
    dlg.AllowSelection = true;
    dlg.Document = Document;

    // show allowed page range
    var ps = dlg.PrinterSettings;
    ps.MinimumPage = ps.FromPage = 1;
    ps.MaximumPage = ps.ToPage = _preview.PageCount;

    // show dialog
    if (dlg.ShowDialog(this) == DialogResult.OK)
    {
      // print selected page range
      _preview.Print();
    }
  }
}

The Print method in the preview control starts by determining the range of pages that should be rendered. This may be the full document, a specific range, or the current selection (page being previewed). Once the page range has been determined, the code creates a DocumentPrinter helper class to perform the actual printing:

public void Print()
{
  // select pages to print
  var ps = _doc.PrinterSettings;
  int first = ps.MinimumPage - 1;
  int last = ps.MaximumPage - 1;
  switch (ps.PrintRange)
  {
    case PrintRange.CurrentPage:
      first = last = StartPage;
      break;
    case PrintRange.Selection:
      first = last = StartPage;
      if (ZoomMode == ZoomMode.TwoPages)
        last = Math.Min(first + 1, PageCount - 1);
      break;
    case PrintRange.SomePages:
      first = ps.FromPage - 1;
      last = ps.ToPage - 1;
      break;
    }

    // print using helper class
    var dp = new DocumentPrinter(this, first, last);
    dp.Print();
  }
}

The DocumentPrinter class is simple. It inherits from PrintDocument and overrides the OnPrintPage method to print only the pages selected by the user:

internal class DocumentPrinter : PrintDocument
{
  int _first, _last, _index;
  List<Image> _imgList;

  public DocumentPrinter(CoolPrintPreviewControl preview, int first, int last)
  {
    // save page range and image list
    _first = first;
    _last = last;
    _imgList = preview.PageImages;
    
    // copy page and printer settings from original document
    DefaultPageSettings = preview.Document.DefaultPageSettings;
    PrinterSettings = preview.Document.PrinterSettings;
  }

  protected override void OnBeginPrint(PrintEventArgs e)
  {
    // start from the first page
    _index = _first;
  }
  protected override void OnPrintPage(PrintPageEventArgs e)
  {
    // render the current page and increment the index
    e.Graphics.PageUnit = GraphicsUnit.Display;
    e.Graphics.DrawImage(_imgList[_index++], e.PageBounds);
    
    // stop when we reach the last page in the range
    e.HasMorePages = _index <= _last;
  }
}

This implementation renders the page images assuming all pages have the same size and orientation, which is the case for most documents. If the document contains pages of different sizes, or with different orientation, this simple implementation will not work correctly. To fix this, we would have to check that the current paper size and orientation match the preview image size before printing each page and adjust the printer settings if necessary. That is left as an exercise for the reader.

Exporting to PDF

The PDF format is extremely popular because it is compact and portable. PDF files can be posted on the web, distributed by e-mail, and viewed or printed almost anywhere.

Once a PrintDocument has been rendered into a series of images, we can use the C1PdfDocument component to render the images into a PDF document. Using this approach, any application that uses the PrintDocument class to provide printing and previewing can add a PDF export feature with minimal effort.

To implement the PDF export, we start by implementing the event handler that gets invoked when the user presses the export to PDF button on the PrintPreviewDialog:

void _btnPdf_Click(object sender, EventArgs e)
{
  using (var dlg = new SaveFileDialog())
  {
    dlg.Filter = "Portable Document File (*.pdf)|*.pdf";
    dlg.DefaultExt = ".pdf";
    if (dlg.ShowDialog() == DialogResult.OK)
    {
      pdfGenerator = new PrintDocumentPdfExporter(_pdf);
      if (pdfGenerator.RenderDocument(Document, true))
      {
        _pdf.Save(dlg.FileName);
      }
    }
  }
}

The code uses a SaveFileDialog to prompt the user for the name of the PDF file to save and a PrintDocumentPdfExporter to generate the PDF file from the preview images.

The PrintDocumentPdfExporter class can be used independently of the C1PrintPreviewDialog class. For example, you may want to provide a PDF export button on the main application, that does not cause a preview dialog to appear at all.

The implementation of the PrintDocumentPdfExporter class is shown below:

class PrintDocumentPdfExporter
{
  // ** fields
  PrintDocument _doc;
  C1PdfDocument _pdf;
  PreviewPrintController _previewController;
  int _pageCount;
  bool _cancel;

  // ** ctor
  public PrintDocumentPdfExporter(C1PdfDocument pdf)
  {
    // save reference to pdf component
    _pdf = pdf;
  }

The constructor simply saves a reference to the C1PdfDocument that will be used to generate the PDF file.

The main public method in the class is called RenderDocument. This method starts by assigning a PreviewPrintController to the document, which causes pages to be rendered into metafile images. The controller used can be a plain PreviewPrintController or a PrintControllerWithStatusDialog wrapper, depending on the value of the showProgressDialog parameter. Both of these classes are part of the .NET framework.

Once the controller has been installed, the code connects event handlers for the PrintPage and EndPrint events. These event handlers are responsible for rendering the page images into the PDF document. Once the event handlers are installed, the code invokes the Print method to render the document.

The method returns a boolean value that indicates whether the document was fully generated (and should be saved) or whether the user canceled the process by pressing the "Cancel" button on the optional progress dialog.

  // ** object model
  public bool RenderDocument(PrintDocument doc, bool showProgressDialog)
  {
    // save reference to document
    _doc = doc;

    // initialize pdf document
    _pdf.Clear();
    _pdf.Landscape = false;

    // prepare to render
    var savePC = _doc.PrintController;
    _previewController = new PreviewPrintController();
    _doc.PrintController = showProgressDialog
      ? new PrintControllerWithStatusDialog(_previewController)
      : (PrintController)_previewController;
    _pageCount = 0;
    _cancel = false;

    // render
    try
    {
      _doc.PrintPage += _doc_PrintPage;
      _doc.EndPrint += _doc_EndPrint;
      _doc.Print();
    }
    finally
    {
      _doc.PrintPage -= _doc_PrintPage;
      _doc.EndPrint -= _doc_EndPrint;
      _doc.PrintController = savePC;
    }

    // done
    return !_cancel;
  }

The document event handlers are listed below. As each page is rendered, the handlers call the DrawPage method to render the page images (metafiles) into the PDF document. This is done using the C1PdfDocument.DrawImage method.

The C1PdfDocument.DrawImage method enumerates the GDI commands in the metafile and converts each one to the corresponding PDF vector command. The metafile is never converted into a raster image, which means the content remains scalable, searchable, and compact.

  // ** PrintDocument event handlers
  void _doc_PrintPage(object sender, PrintPageEventArgs e)
  {
    var pages = _previewController.GetPreviewPageInfo();
    while (_pageCount < pages.Length - 1)
    {
      DrawPage(pages, _pageCount++);
    }
    if (e.Cancel)
    {
      _cancel = true;
    }
  }
  void _doc_EndPrint(object sender, PrintEventArgs e)
  {
    var pages = _previewController.GetPreviewPageInfo();
    DrawPage(pages, _pageCount);
    if (e.Cancel)
    {
      _cancel = true;
    }
  }
  void DrawPage(PreviewPageInfo[] pages, int index)
  {
    // skip to next page
    if (index > 0)
    {
      _pdf.NewPage();
    }

    // get preview page info
    var pi = pages[index];

    // adjust page size
    var ps = pi.PhysicalSize;
    _pdf.PageSize = new SizeF(ps.Width / 100f * 72, ps.Height / 100f * 72);

    // draw image
    var img = pi.Image;
    _pdf.DrawImage(img, _pdf.PageRectangle);
  }
}

Previewing Really Long Documents

After I posted the first version of this project, I got some great feedback from other CodeProject users. One of them mentioned a problem I also had a while ago. If the document contains several thousand pages, caching all those images may cause problems. Windows has a limit of 10,000 GDI objects, and each page image represents at least one. If you use too many GDI objects, your application may crash, or cause other apps to crash. Not nice...

One easy way to solve this problem is to convert the page images into streams. You can then store the streams and create images on demand, only when they are needed for previewing or printing.

The code below shows a PageImageList class that does the job. You can use it much like a regular List, except when you get or set an image, it is automatically converted to and from a byte array. This way, the images stored in the list are not GDI objects and don't use up the system resources.

// This version of the PageImageList stores images as byte arrays. It is a little
// more complex and slower than a simple list, but doesn't consume GDI resources.
// This is important when the list contains lots of images (Windows only supports
// 10,000 simultaneous GDI objects!)
class PageImageList
{
  // ** fields
  var _list = new List<byte[]>();

  // ** object model
  public void Clear()
  {
    _list.Clear();
  }
  public int Count
  {
    get { return _list.Count; }
  }
  public void Add(Image img)
  {
    _list.Add(GetBytes(img));

    // stored image data, now dispose of original
    img.Dispose();
  }
  public Image this[int index]
  {
    get { return GetImage(_list[index]); }
    set { _list[index] = GetBytes(value); }
  }

  // implementation
  byte[] GetBytes(Image img)
  {
    // clone image since GetEnhMetaFileBits is destructive
    var clone = img.Clone() as Metafile;

    // use interop to get the metafile bits
    var enhMetafileHandle = clone.GetHenhmetafile().ToInt32();
    var bufferSize = GetEnhMetaFileBits(enhMetafileHandle, 0, null);
    var buffer = new byte[bufferSize];
    GetEnhMetaFileBits(enhMetafileHandle, bufferSize, buffer);

    // done with clone (already destroyed by call to GetEnhMetaFileBits)
    clone.Dispose();

    // return bits
    return buffer;
  }
  Image GetImage(byte[] data)
  {
    MemoryStream ms = new MemoryStream(data);
    return Image.FromStream(ms);
  }

  [System.Runtime.InteropServices.DllImport("gdi32")]
  static extern int GetEnhMetaFileBits(int hemf, int cbBuffer, byte[] lpbBuffer);
}

Note that the Add method disposes of the image after storing it. Normally we would not do it this way since the caller owns the image and should be responsible for disposing of it. But in this project, this arrangement allows us to swap the PageImageList implementation with a regular List, which is convenient for testing and benchmarking.

Note also that the GetBytes uses the GetHenhMetaFileBits API. This API makes the metafile invalid, so the image cannot be used after this method is called. To avoid destroying the original image, the method creates a clone first, then gets the bits from the clone.

In case you are concerned about performance, the extra conversion work causes a performance hit of about 10% while generating the document. I think this is a small price to pay for the benefit if your documents have a few hundred or a few thousand pages.

And in case you are concerned about memory usage, consider compressing the byte arrays when you store them. It is easy to do using C1Zip, and metafiles tend to compress really well. Of course, there would be a small additional performance penalty involved.

Conclusion

We implemented an enhanced PrintPreviewDialog class that provides PDF output in addition to the standard print and preview capabilities. If you have requests or suggestions for improving this document or the application, please post on our site.

Download the full source code: C1PrintPreview.zip

Welcome to WinDev!

2009-09-29T17:48:00Z

Welcome to the Win Dev Blog. In this blog you'll find interesting code examples and articles for desktop application development - mostly Windows Forms and some WPF.

Regarding myself, I am currently the desktop dev-tools product manager for ComponentOne. I've done a lot of work with all .NET technologies. If you're a .NET developer like me then you're probably familiar with WinForms and WPF development. I'll be sharing many code samples that I've written, samples from other .NET developers, and even cool tips and tricks that I've stumbled upon. So stay tuned!