Small Business Developer

Report Writing On The Cheap - Part 1

For many small business developers purchasing first-class reporting tools is unaffordable or not practical for simple reports.  One alternative is using Excel for reporting, but it requires dealing with COM+ objects and the assumption that the customer has Excel installed.  You could use SpreadsheetML, but it requires knowledge of XML, XSLT, Linq to XML, etc... and still depends on Excel or OpenOffice.  Another option is using the PrintForm component from the Visual Basic Power Packs, but it has limitations (I will do a separate post on using them).  The possibilities go one, but luckily, for those using .Net, Microsoft has the code for you to use.  The System.Drawing.Printing namespace along with GDI+ gives you the tools to make professional looking reports with a little bit of knowledge.  When I was looking for report tool alternatives, I happened across an article by Lee Falin (personal blog http://leefalin.com/blog/) in Visual Studio Magazine (www.visualstudiomagazine.com), April 2005 (locator+ code VS0504GS_T).  In this article, Lee describes how to create a report from a datagrid using GDI+, and I hope everyone reading this blog will take the time to read his article.  With a little understanding you can create reports and not have to worry about 3rd-party software.  Understandable this is not very dynamic, but if you need that type of capability you should be looking at professional software.  What I want to do is expand on his article by going into more depth on the PrintDocument class as well as other functionality.  I am going to break this down into multiple parts with part one focusing on the PrintDocument class and the events you need to handle to print a report.  So let us begin by looking at the PrintDocument class, it's events, and what Microsoft suggests should happen during these events.

PrintDocument Class

The PrintDocument class is going to be the basic building block for our report.  Using the graphics object exposed from the PrintDocument events, we can create the document layout using text, images, and/or graphics just like you would on a Windows Form.  Lee Farlin, in his article, warns that since GDI+ uses unmanaged code, make sure you dispose of any drawing objects you create.  The suggested location is the EndPrint event.  You can add the PrintDocument as a component to your form or create it in the code behind.  In order for the document to be printed, you must handle the PrintDocument events. 

PrintDocument Events 

Below is the sequence of events with snippets to demonstrate how to use them.

1.  PrintDocument.Print method is initiated, starting the chain of events.  I created a simple windows project with one button.  The following code was added to the code behind. 

Private WithEvents myPrintDoc As New PrintDocument 
Private Sub Button1_Click(ByVal sender As System.Object, _
                          ByVal e As System.EventArgs) Handles Button1.Click
    myPrintDoc.Print()
End Sub

2.  As soon as the Print method is executed, the BeginPrint event is raised.  Here you can deal with setting up resources that will be used for the print job.  So you would do things such as initialize fonts, load data, or run validations before the printing process begins.  One example would be to ensure that there are rows in the dataset you are going to use to print.  In that case you could set e.Cancel = true to end the print job if the dataset was empty or had errors.

Private WithEvents myPrintDoc As New PrintDocument 
Private Sub myPrintDoc_BeginPrint(ByVal sender As Object, _
                                  ByVal e As System.Drawing.Printing.PrintEventArgs) _
                                  Handles myPrintDoc.BeginPrint
    If myDataSet.HasErrors Or myDataSet.Tables("MyTable").Rows.Count < 1 Then
        e.Cancel = True
    End If

End Sub

3.  Next, before each page is printed, the QueryPageSettings event is raised and you can use the event to adjust the page settings for the page you are about to print.  This means that each page could use different settings and all you would have to do is modify the QueryPageSettingsEventArgs object, using the PageSettings property.  You also have access to a cancel property to cancel the print job.  The below example sets the first page of the report to be printed as landscape.

Private currentPageCount As Integer
Private Sub myPrintDoc_QueryPageSettings(ByVal sender As Object, _
                        ByVal e As System.Drawing.Printing.QueryPageSettingsEventArgs) _
                        Handles myPrintDoc.QueryPageSettings
    If currentPageCount = 1 Then e.PageSettings.Landscape = True
End Sub

4.  After QueryPageSettings, PrintPage is raised.  Here is where the majority of the work is done.  The event exposes a graphics object, which you can use to draw lines, text, bitmaps, etc... on the page. To allow printing over multiple pages you need to set the HasMorePages (default is false) property to true, which will call this event again (QueryPageSettings is also called).  Keep in mind that you will need to set this back to false, otherwise the PrintPage event will keep being raised.  Like the other events you can still cancel the print job by setting e.Cancel = True.  Additional properties of interest are MarginBounds, which gets the rectangular area inside the margins.  The PageBounds property gets the rectangular area for the whole page.  Finally, PageSettings gets the current page's settings.  The image below shows the bounds property rectangles as well as a sample PrintPage event code to write a line of text.  You can see the effect of writing the string at position 0,0, which has no concept of the page bounds, and this will be something you will need to handle in your code.

Private Sub myPrintDoc_PrintPage(ByVal sender As Object, _
                                 ByVal e As System.Drawing.Printing.PrintPageEventArgs) _
                                 Handles myPrintDoc.PrintPage
    e.Graphics.DrawString("Hello World Printing", New Font("Arial", 12, _
              FontStyle.Regular, GraphicsUnit.Pixel), Brushes.Black, 0, 0)
End Sub

5.  Finally, after all pages have been printed, EndPrint is executed.  Like BeginPrint which you can use to initialize fonts, file streams, resources, in EndPrint you would close dispose of those objects.  

Conclusion

With this basic understanding of the PrintDocument, we can now move to creating a document layout using GDI+ and the above events.  Hopefully, this gets you interested in what can be achieved using the PrintDocument class.  Part two will focus on how to add text, images, graphics, as well a few tips on handling text (word wrap, multiple pages, print a textfile, etc...).  As always comments are welcome and would be helpfully if you want me to dig deeper into a specific topic.

XML and Strongly-Typed Datasets - Part 3

I apologize for how late this post is, but Christmas snuck up on me and I had to rush to meet all the family obligations.  In this post I want to look at using more complex XML files which have parent-child relationships as well as how to validate XML files.  Another customer of mine wanted code to fill a dataset with data from XML, but also needed to enforce some rules.  I used code to validate the document due to a few factors outside my control, but I like to see if there would have been a better way to do this.  So now I have the opportunity to replicate the problem and hopefully do a better job solving it.  As always input is welcome if you have a better way to do this.  Otherwise let us begin.

Setup

Before we being you will need to get a few files from Microsoft.  The XML file, CustomerOrders.xml, is located here - http://msdn.microsoft.com/en-us/library/bb387025.aspx.  Next, create a new windows forms application, adding the XML file to the project.  If you read my last blog post, you know all you need to do is use the XML-to-Schema tool and it will generate the needed xsd file.  After that, set the custom tool property of the xsd file to MSDatasetGenerator.

After that right click on the xsd file in the solution explorer and select "Run Custom Tool."  A new datasource will now show up in the Data Sources tab.  I need to make one change to the dataset since I want to show customers and their orders (the relationship was not automatically made).  Right click on the dataset and select "Open With..."  From there right click on the design surface and select "Add>Relation..."  Set the relationship like the image below:

A one-to-many relationship will be created.   you can now drag items from the Data Sources tab onto the form and modify the look during design time.  Select the "Copy to Output Directory" property and set it to "Copy Always."  I am only doing this for brevity sake, you would be better off to set a configurable location in the app.config file to better handle this.

Create the UI

I created the UI by dragging the Customer table (select Details, not DataGridView) from the Data Sources tab onto the form.  This creates the CustomerBindingNavigator as well as the labels and textboxes. 

The Customer_Id and Customers_Id fields were removed, since I didn't want to display them.  Next, I dragged the Order table that is below the Customer Table in the drop down list and dragged it onto the form.  A datagridview will be created for the Order table which includes the code to display the correct orders when the parent customer is selected.

Validation

Double click on the form and add the following code:

Imports System.Xml
Imports System.Xml.Schema

'The validation code is from the MSDN website - http://msdn.microsoft.com/en-us/library/bb387037.aspx
'so all credit belongs to them.

Public Class Form1

    Dim errors As Boolean = False 'Holds validation state for the XDocument

    'Capture the XSD error event to show what caused the failue
    Private Sub XSDErrors(ByVal o As Object, ByVal e As ValidationEventArgs)
        MessageBox.Show(String.Format("{0}", e.Message))
        errors = True
    End Sub

    Private Sub Form1_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

        'Load the schema
        Dim schemas As XmlSchemaSet = New XmlSchemaSet()
        schemas.Add("", "CustomersOrders.xsd") 'There is no namespace on the xml, so use blank for the first parameter

        'Load the xml
        Dim custOrdDoc As XDocument = XDocument.Load("CustomersOrders.xml")

        'Initialize the error flag
        errors = False

        'Validate the xdocument with the schema
        custOrdDoc.Validate(schemas, AddressOf XSDErrors)

        MessageBox.Show(String.Format("custOrdDoc {0}", IIf(errors, "did not validate", "validated")))

        If errors = False Then
            'Fill the stronly-typed dataset if successful.
            Root.ReadXml(custOrdDoc.CreateReader)
        End If

    End Sub

End Class

Now when you run program it will display the validation status and then display the results.  The UI isn't that exciting, but the ability to create a UI during design time rather than run time as well as validate the loaded XML makes working with XML just that much easier.  Combine this with VB 9's ability to use XML literals and LinqToXML, you have some powerful tools to work with XML in .Net.

XML and Strongly-Typed Datasets - Part 2

I did a bit of research and found another way to allow design-time usage of an XML file.  This one has a few more steps than my previous post, but I think it is more useful.  I am going to provide a simple example in this post, but will follow it up with a more complex example.  You will need the Visual Basic Power Pack XML to Schema tool installed, which you can download from here - http://msdn.microsoft.com/en-us/vbasic/bb840042.aspx.  Hopefully this walkthrough will be helpful.

1. Create a new windows project in VB 2008.
2. Add a new xml file, call it books.xml.  When you see the xml designer you can replace the code with the books.xml code from here - http://msdn.microsoft.com/en-us/library/ms762271.aspx 
3. Set the Copy To Output Directory to "Copy Always" for this example.
4. Now add another new file and select the XML to Schema template.
5. I called the file BooksSchema.xsd and then click add.  Another screen will pop up, click on Add from File.
6. Select the books.xml file you created and click ok. 
7. Click Ok again.
8. Now, you can click on the Data Sources Tab and it will show the catalog dataset.  Drag the Books icon onto the form design surface and it will automatically create a datagridview with the columns from the XML file.
9. Now go the code behind and add this line of code to the Load event.

Private Sub Form1_Load(ByVal sender As Object, ByVal e As System.EventArgs)  _ Handles Me.Load

Catalog.ReadXml("Books.xml")

End Sub

10. In the end it should look like this:
11. Now, if you want to view the schema like you do a dataset, you need to right click on the schema and select Open with... and select Dataset Editor from the list.  Click OK.  A warning will come up, but click yes.  Now you will see the XML schema like you do a regular dataset.

Next week, I will use an example where there are relationships in the XML and use that to create multiple datagridviews with a parent-child relationship.

XML And Strongly-Typed Datasets

     This is another interesting item I ran into from the field.  A customer wants to use XML with a DataGridView, but the issue is that they want to manipulate the look of the data in the datagridview during design time.  My solution was to create a strongly-typed dataset and then read the xml into the dataset.  I need to give credit to Rong-Chun Zhang on the MSDN Forums for helping me solve this problem.  Here is the link to the topic at MSDN - http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=3543590&SiteID=1  The created project is nothing more than a simple Windows Forms Project.

Source XML

Here is the XML file I used (same as the one from the forum topic)

<MyDataSet xmlns="MyDataSetnamespace">

    <Order_Details>

        <OrderID>10248</OrderID>

        <ProductID>11</ProductID>

        <UnitPrice>14.0000</UnitPrice>

        <Quantity>12</Quantity>

        <Discount>0</Discount>

    </Order_Details>

    <Order_Details>

        <OrderID>10249</OrderID>

        <ProductID>11</ProductID>

        <UnitPrice>14.0000</UnitPrice>

        <Quantity>12</Quantity>

        <Discount>0</Discount>

    </Order_Details>

    <Order_Details>

        <OrderID>10250</OrderID>

        <ProductID>11</ProductID>

        <UnitPrice>14.0000</UnitPrice>

        <Quantity>12</Quantity>

        <Discount>0</Discount>

    </Order_Details>

</MyDataSet>

Strongly-Typed Dataset

Next, you need to create a strongly typed dataset.  What you need to do is add a new item to your project and then select dataset.  Give the dataset a meaningful name and click Add.  This will bring you to the dataset designer.  From there you can add a datatable and all the associated columns.  Here is the result that I had:

I saved the changes and now the dataset shows up in the Data Sources Tab.  Now you can add it to your form and manipulate it as you would any other databound datagridview control.

Code Behind

Finally, you need to load the XML into the dataset.  The code is as follows:

Try

   'You need to set the namespace on the dataset to match the one on the
    'xml file.
   MyDataSet1.Namespace = "MyDataSetnamespace"

   MyDataSet1.ReadXml(txtXMLFileLocation.Text)

Catch ex As Exception
    MessageBox.Show(ex.Message)
End Try

This post was really simplistic, but I am going to do more research into how to use more complex XML files with the datagridview, assuming there is any interest. 

Microsoft Small Business Developer Links

Reason To Blog

Welcome.  This is my first attempt at blogging, so bear with me.  What I hope to accomplish is to post code and tips for the small business developer.  Personally, I love the idea of working with small businesses to fulfil their software needs.  They aren’t loaded with money, so as a developer you need to find unique ways to build software that fits their needs and budget.  A good example is report writing.  Typically, a business will use Excel or Access to display their data.  That might be ok now, but there are limitations to what it can do.  Coding is done using VBA, which is a bit old and tired.  An alternative would be to write any business logic in VB.Net/C# and have it output the data into XML (e.g. SpreadSheetML for Excel) for Office 2003/2007, earlier versions will need to use Office Automation.  From there the user can open the data in Excel and manipulate it as they normally do, but the code is done using the latest technology.  So, there are a lot of possibilities.  I hope this will be a useful outlet and all constructive criticism is welcome.

Format Data Bound Textboxes as Currency

Recently, I ran into an issue where a customer wanted to display currencies in data bound textboxes, but there didn’t seem to be an easy way to do this.  Luckily, I found the information in the MSDN forums and I figured I would create a simple project to bring together a bunch of threads on the subject.  Here is the source code - Source Code .

So assuming you already have bound textboxes, here are three approaches I have found to deal with this issue.

1. Set the bindings through code  In this case you set up the binding through code and now you need to modify it to display the data as a currency.  Here is the code I used:

‘Formatting codes found at MSDN - Standard Numeric Format Strings http://msdn.microsoft.com/en-us/library/aa720653.aspx

Me.TextBox4.DataBindings.Add(“text”, PriceBindingSource, “Price”, True, DataSourceUpdateMode.OnPropertyChanged, ” “, “C”)

2.  Format the field during an event  I used the textchanged event, but how you do this is really based on the behavior you want.

Private Sub TextBox2_TextChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles TextBox2.TextChangedMe.TextBox2.Text = FormatCurrency(Me.TextBox2.Text, 2)End Sub

3.  Use Visual Studio Wizard  Finally, you can use the Visual Studio IDE to set the bindings.  What you need to do is select the textbox and expand the (DataBindings) property.  Select the Advanced Binding field and then click on the button that appears in the field.  A window will pop up and from there you can set the formatting for the textbox.

Hopefully this tidbit helps