This chapter is from the book
This chapter is from the book
This chapter is from the book
Practical Example—PETRAS Report Tool .NET
PETRAS Report Tool .NET is a practical case study that demonstrates a more complex VB.NET application than is possible to cover in a single chapter. In Chapter 25, the tool is converted into a managed COM add-in for Excel. The tool is a standalone, fully functional reporting solution. It retrieves data from a SQL Server database (created in Chapter 19, "Programming with Access and SQL Server") based on the user selection in the main Windows Form. It then populates predefined Excel report templates with the data. It can export reports either to Excel or to XML files. The solution can be found on the companion CD in \Applications\Ch24 - Excel & VB.NET\PETRAS Report Tool.NET. Please read the Read Me First.txt file located in the \Applications\Ch24 - Excel & VB.NET\ folder. You will find it helpful to open this solution in the VB IDE so that you can reference it while reading this section.
When the tool starts up, it first tries to establish a connection to the database. A custom Windows Form is displayed while the tool is trying to connect. If the connection attempt is successful, the main Windows Form shown in Figure 24-22 is displayed. If the connection attempt fails, an error message is displayed.
)
Figure 24-22 PETRAS Report Tool .NET user interface
Use the following steps to create a report in the main form:
- Select a Client.
- Select a Project.
- Select the reporting time period by entering a Start date and an End date.
- Uncheck or keep the fields Activities and Consultants.
- Click on the Create Report button to preview the report in the DataGrid.
- Click the appropriate button to export to an Excel report or to an XML file.
- If export to Excel is selected, Excel is launched and a copy of one of the four predefined report templates is created.
- If export to XML is selected, a Save File dialog is displayed so you can specify a filename and location where the XML file should be saved.
- If the export is successful, the selections you made become the new default values for all controls on the Windows Form. It is possible to clear these settings by selecting the Clear Settings button.
- To close the Windows Form, click the Close button.
The .NET Solution
Although we only use one main Windows Form, our .NET solution includes some additional modules and files. Table 24-4 shows a summary of what the solution contains.
Table 24-4. Contents for the PETRAS Report Tool.NET Solution
|
Module Name |
Type and Function |
|
app.config |
XML configuration file containing the connection string |
|
frmConnecting.vb |
Windows Form displayed while connecting to the database |
|
frmMain.vb |
Windows Form that is the main form for the solution |
|
MCommonFunctions.vb |
Standard module containing general functions for the tool |
|
MDataReports.vb |
Standard module containing all database functions |
|
MExportExcel.vb |
Standard module containing all the functions required to export data to Excel |
|
MExportXML.vb |
Standard module containing all the functions required to export data to XML files |
|
MSolutions Enumerations Variables.vb |
Standard module containing all the enumerations used in the solution |
|
MStartUp.vb |
Standard module containing the Main procedure for the solution |
As you can see in Table 24-4, the solution does not include any class modules. Creating well-designed class modules is covered in Chapter 25. In addition to the components shown in Table 24-4, the solution uses four different Excel report templates. Depending on the user selections, one of them is used to create the requested report:
- PETRAS Report Activities.xlt—Used when only the Activities control is checked
- PETRAS Report Activities Consultants.xlt—Used when both the Activities and Consultants controls are checked
- PETRAS Report Consultants.xlt—Used when only the Consultants control is checked
- PETRAS Report Summary.xlt—Used when neither the Activities nor the Consultants controls are unchecked
If we click the Show All Files button in the Solution Explorer toolbar, it displays an expanded tree view. If we then expand the References item in the tree view we can see all references for the solution, as shown in Figure 24-23. Most hidden files are system files that we rarely need to work with, but it's a good exercise to explore all the files included in the solution.
)
Figure 24-23 The tree view in Solution Explorer
In any non-trivial real-world application where we initially load a Windows Form, we usually need to ensure that certain conditions are met before loading it. In VB.NET we can use the same approach as with Classic VB. We create a Main subroutine in a standard code module that is used as the startup subroutine.
But in VB.NET, we need to change some additional settings in the solution before this will work correctly. After creating the new Windows Forms application, open the solution Properties window, and select the Application tab. Figure 24-24 shows the original startup settings for the PETRAS Report Tool.NET solution.
)
Figure 24-24 Default settings for the solution
We add a standard code module to the solution that we name MStartup.vb. We add the Main subroutine and its code to this module, as shown in Listing 24-32.
Listing 24-32. Code for the Main Subroutine
Sub Main()
'Enable Windows XP's style.
Application.EnableVisualStyles()
'Declare and instantiate the Windows Form.
Dim frm As New frmMain
'Set the position of the main Windows Form.
frm.StartPosition = FormStartPosition.CenterScreen
'Show the main Windows Form.
Application.Run(mainForm:=frm)
'Releases all resources the variable has consumed from
'the memory.
frm.Dispose()
'Release the reference the variable holds and prepare it
'to be collected by the Garbage Collector when it
'comes around.
frm = Nothing
End Sub
Now we return to the Application tab of the solution Properties window, where we uncheck the option Enable application framework and change the Startup object to the Main subroutine as shown in Figure 24-25.
)
Figure 24-25 Modified startup settings
Unchecking the Enable application framework option implicitly removes the option to use Windows XP styles. Therefore, we need enable this option manually in the startup code, which is done in the first line of our Main procedure in Listing 24-32.
The Main subroutine is also a good place to put code to position the Windows Form before it is loaded. The Main subroutine is also an acceptable place to put code for connecting to a database, but in the PETRAS Report Tool.NET we use a different approach that is covered soon. When the user closes the main Windows Form we dispose its class and set the variable to nothing.
Windows Forms Extender Providers
The .NET Framework provides so-called extender providers to Windows Forms. These components can only be used with visual controls. By adding them to our Windows Forms we get additional properties to work with. Extender providers are added to a Windows Form in exactly the same way as regular controls. However, the extender providers appear in the form's Component Tray rather than on the surface of the form itself.
Figure 24-26 shows the Component Tray for the main form of the PETRAS Report Tool.NET. The components used are the ErrorProvider, HelpProvider, and ToolTip components, for the main Windows Form, the BackgroundWorker component, which we cover later, and the SaveFileDialog component that was introduced earlier in the chapter.
)
Figure 24-26 Extender providers in the PETRAS Report Tool.NET
The first extender provider in use is the ErrorProvider, which provides us with the option to set validation errors. It can be used with one or more controls on the Windows Form as each of them have the Validating event.
When a control's input is not valid the ErrorProvider places an error icon next to the control and displays an error message when the user hovers the mouse over the icon. Listing 24-33 shows how this is implemented in the PETRAS Report Tool.NET solution. As the code shows, we can create a single event that hooks the Validating events of all the targeted controls on the form.
Listing 24-33. The Validating Event Subroutine for Several Controls
Private Sub Client_Project_Validating(ByVal sender As Object, _
ByVal e As System.ComponentModel.CancelEventArgs) _
Handles cboClients.Validating, _
cboProjects.Validating
Const sMESSAGECLIENTERROR As String = _
"You need to select a client."
Const sMESSAGEPROJECTERROR As String = _
"You need to select a project."
Dim Ctrl As Control = CType(sender, Control)
If Ctrl.Text = "" Then
Select Case Ctrl.Name
Case "cboClients"
Me.ErrorProvider1.SetError(control:=Ctrl, _
value:=sMESSAGECLIENTERROR)
Case Else
Me.ErrorProvider1.SetError(control:=Ctrl, _
value:=sMESSAGEPROJECTERROR)
End Select
Else
Me.ErrorProvider1.SetError(control:=Ctrl, value:="")
End If
End Sub
If one of the controls being validated has the focus when the user clicks the Clear Settings button, the validation handling code is executed. To prevent this we must add one line of code to the load event of the main Windows Form. This is shown in Listing 24-34.
Listing 24-34. Code to Prevent Validation when the Clear Settings Button Is Clicked
Me.cmdClearSettings.CausesValidation = False
We can prevent the entry of bad data into a control by writing handlers for the key press event as well.
Looking more closely at the code in Listing 24-33 may raise the question of why we do not use a control array as we would in Classic VB. This is because VB.NET does not currently support control arrays, and it does not appear as if this feature will be implemented in any future version. The solution shown is the closest workaround in VB.NET. The second extender provider, HelpProvider, is used to associate a help file (either a .chm or .htm file) with our application. Whenever our application is running and has focus, the HelpProvider associates the F1 button with our application's help file. For the PETRAS Report Tool.NET we use a simple form-based help system, meaning that we associate the help file with our main Windows Form. It is much easier to set this up using Windows Form properties manually at design time than to do it at runtime with code. The design-time property settings required to create a form-based help system are the following:
- Set the HelpKeyword property on HelpProvider1 to the value About.htm.
- Set the HelpNavigator property on HelpProvider1 to the value Topic.
One property of the HelpProvider that should be set in code is the HelpNameSpace property. Doing this provides us with a more flexible solution because we can change the location of the help file dynamically. Listing 24-35 shows the code in the main Windows Form load event required to set the HelpNameSpace property.
Listing 24-35. Setting the Path and Name to the Help File
'The help file in use. Const sHELPNAMESPACE As String = "PETRAS_Report_Tool.chm" 'Setting the helpfile to the HelpProvider component. Me.HelpProvider1.HelpNamespace = swsPath + sHELPNAMESPACE
The swsPath is a global enumeration member that holds the path to the application EXE file for the PETRAS Report Tool.NET.
The third extender provider is the ToolTip component. It provides us with the option to add a Tooltip to each control in a Windows Form. Whenever the user hovers over a control with the mouse the control's Tooltip is displayed.
Threading
With .NET we can leverage multithreading to create more powerful solutions. It is beyond the scope of this chapter to cover multithreading in detail, but we demonstrate a simple example. The .NET Framework includes an extender provider, BackgroundWorker, which allows us to run code on a separate, dedicated thread, meaning we can run our project in multithreading mode. This extender provider is normally used for time-consuming operations, but as this case shows, we can use it for other tasks as well.
In the PETRAS Report Tool.NET, we use the BackgroundWorker component to run the code that connects to the database. By using two of its events, BackgroundWorker1_DoWork and BackgroundWorker1_RunWorkerCompleted, we attempt to connect to the database in the background and be notified about the outcome. Listing 24-36 shows the code for the load event of the main Windows Form followed by the code for the two events of the BackgroundWorker component.
Listing 24-36. Code in Use for the BackgroundWorker
Private Sub Form1_Load(ByVal sender As System.Object, _
ByVal e As System.EventArgs) _
Handles MyBase.Load
'...
'Settings for the BackgroundWorker component.
With Me.BackgroundWorker1
'Makes it possible to cancel the operation.
.WorkerSupportsCancellation = True
'Start the background execution.
.RunWorkerAsync()
End With
'Change the cursor while waiting to BackgroundWorker
'component has been finished.
Me.Cursor = Cursors.WaitCursor
End Sub
Private Sub BackgroundWorker1_DoWork(ByVal sender As Object, _
ByVal e As System.ComponentModel.DoWorkEventArgs) _
Handles BackgroundWorker1.DoWork
'Instantiate a new instance of the connecting
'Windows Form.
mfrmConnecting = New frmConnecting
'Position the Windows Form and display it.
With mfrmConnecting
.StartPosition = FormStartPosition.CenterScreen
.Show()
End With
'Can we connect to the database?
If MDataReports.bConnect_Database() = False Then
'OK, we cannot establish a connection to the
'database so we cancel the background operation.
Me.BackgroundWorker1.CancelAsync()
'Let us tell it for the other backgroundWorker
'event - the RunWorkerCompleted.
mbIsConnected = False
Else
'Let us tell it for the other backgroundWorker
'event - the RunWorkerCompleted.
mbIsConnected = True
End If
'Close the connecting Windows Form.
mfrmConnecting.Close()
'Releases all resources the variable has consumed
'from the memory.
mfrmConnecting.Dispose()
'Release the reference the variable holds and prepare
'it to be collected by the Garbage Collector (GC) when
'it next time comes around.
mfrmConnecting = Nothing
End Sub
Private Sub BackgroundWorker1_RunWorkerCompleted _
(ByVal sender As Object, _
ByVal e As System.ComponentModel. _
RunWorkerCompletedEventArgs) _
Handles BackgroundWorker1.RunWorkerCompleted
'If we have managed to connect to the database then we can continue.
If mbIsConnected Then
'...
End If
'Restore the cursor.
Me.Cursor = Cursors.Default
End Sub
On its surface, the use of the BackgroundWorker component may look attractive. However, multithreaded application development is complex and easy to get wrong, so it should only be used in situations where it is absolutely necessary to run code outside the main process.
Retrieving the Data
A database connection string can be created using several different methods. For the PETRAS Report Tool.NET we create a solutionwide connection string using an application setting. This is accomplished in the Settings tab of the solution Properties windows, as shown in Figure 24-27.
)
Figure 24-27 A solution wide connection string
We first create a name for the setting and then select the type (Connection string). The scope is now automatically set to Application. After placing the cursor in the Value field a button appears on the right side. Clicking this button displays a very useful built-in wizard for creating connection strings.
If we look in the Solution Explorer window, we notice that a new app.config XML file has been created and added to the solution. The app.config file will not be compiled into the executable file when we develop standalone applications like the PETRAS Report Tool.NET. Instead, it is a separate XML file that is installed alongside the PETRAS Report Tool.NET executable. This allows us to easily update the connection string by simply opening and editing the XML file. When we compile the solution the VS IDE creates an XML file based on the solution name, PETRAS Report Tool .NET.exe.xml, for example, instead of using the name app.config.
When creating a DLL, the app.config file is compiled into the DLL, which makes it more difficult to update the connection string. This is addressed in Chapter 25. Listing 24-37 shows how to read the connection string setting from within our application code.
Listing 24-37. Reading the Application Setting for the Connection String
'Read the connection string into a module variable. Private ReadOnly msConnection As String = _ My.Settings.SQLConnection.ToString()
Next we use it to initialize a new SqlConnection object, as shown in Listing 24-38.
Listing 24-38. Function to Create New SqlConnection
Friend Function sqlCreate_Connection() As SqlConnection
Return New SqlConnection(connectionString:=msConnection)
End Function
All functions that retrieve data using disconnected mode expect the DataSet object to contain one DataTable at the time. We use a module-level DataTable variable to populate the DataGridView control. If the user decides to either create an Excel report or export the data to an XML file, the same DataTable is used as an argument to one of the export functions.
Exporting Data
The MExportExcel.vb module contains all the functions required to export data to Excel using one of the four predefined Excel templates described earlier. The main export function, shown in Listing 24-39, takes several arguments. Since the query has already been executed we can get the results as a DataTable from the DataGridView control on the main Windows Form. The other arguments provide information about the options specified by the user when the data was retrieved from the database.
Listing 24-39. The Main Export to Excel Function
Friend Function bExport_Excel(_
ByVal dtTable As DataTable, _
ByVal sClient As String, _
ByVal sProject As String, _
ByVal sStartDate As String, _
ByVal sEndDate As String) As Boolean
Because the PETRAS Report Tool.NET is a standalone application not related to Excel, we first need to determine whether Excel exists and if so, determine which version of Excel is available. To accomplish this we examine the value of a critical Excel-related registry entry and use it to determine the current Excel version.
The lowest version of Excel that we can support is version 2002, meaning the tool cannot be used if version 2000 is installed. The function uses an enumeration of Excel versions, which is defined in the MSolutions Enumerations Variables.vb code module. To provide access to the .NET Framework functions that allow us to read the Windows registry, we import the namespace Microsoft.Win32. We also use regular expressions to complete this task, so the namespace System.Text.RegularExpressions also is imported into the code module. Listing 24-40 shows the code for the function.
Listing 24-40. Determine Which Version of Excel Is Available
'At the top of the module.
'To read the Windows Registry subkey.
Imports Microsoft.Win32
'To use regular expressions.
Imports System.Text.RegularExpressions
Friend Function shCheck_Excel_Version_Installed() As Short
Const sERROR_MESSAGE As String = _
"An unexpected error has occurred " + _
"when trying to read the registry."
'The subkey we are interested in is located in the
'HKEY_CLASSES_ROOT Class.
'The subkey's value looks like the following:
'Excel.Application.10
Const sXL_SUBKEY As String = "\Excel.Application\CurVer"
Dim rkVersionkey As RegistryKey = Nothing
Dim sVersion As String = String.Empty
Dim sXLVersion As String = String.Empty
'The regular expression which is interpreted as:
'Look for integer values in the interval 8-9
'in the end of the retrieved subkey's string value.
Dim sRegExpr As String = "[8-9]$"
Dim shStatus As Short = Nothing
Try
'Open the subkey.
rkVersionkey = Registry.ClassesRoot.OpenSubKey _
(name:=sXL_SUBKEY, writable:=False)
'If we cannot open the subkey then Excel is not available.
If rkVersionkey Is Nothing Then
shStatus = xlVersion.NoVersion
End If
'Excel is installed and we can retrieve the wanted
'information.
sXLVersion = CStr(rkVersionkey.GetValue(name:=sVersion))
'Compare the retrieved value with our defined regular
'expression.
If Regex.IsMatch(input:=sXLVersion, pattern:=sRegExpr) Then
'Excel 97 or Excel 2000 is installed.
shStatus = xlVersion.WrongVersion
Else
'Excel 2002 or later is available.
shStatus = xlVersion.RightVersion
End If
Catch Generalexc As Exception
'Show the customized message.
MessageBox.Show(text:=sERROR_MESSAGE, _
caption:=swsCaption, _
buttons:=MessageBoxButtons.OK, _
icon:=MessageBoxIcon.Stop)
'Things didn't worked out as we expected so we set the
'return variable to nothing.
shStatus = Nothing
Finally
If rkVersionkey IsNot Nothing Then
'We need to close the opened subkey.
rkVersionkey.Close()
'Release the reference the variable holds and prepare it
'to be collected by the Garbage Collector (GC) when it
'comes around.
rkVersionkey = Nothing
End If
End Try
'Inform the calling procedure about the outcome.
Return shStatus
End Function
The module MExportExcel.vb also contains a function to verify that the Excel templates exist in the same folder as the executable file.
The function that exports data to an XML file also creates the Schema file for it. Listing 24-41 shows the two lines of code required to generate these files. We actually use the methods of the DataTable object to generate the XML files. This is because ADO.NET uses XML as its underlying data representation scheme. Both of these XML files can be opened and studied in more detail.
Listing 24-41. Creating XML and Schema Files
...
'Write the data to the XML file.
dtTable.WriteXml(fileName:=sFileName)
'Create the Schema file for the XML file.
dtTable.WriteXmlSchema(fileName:=Strings.Left( _
sFileName, Len(sFileName) - 4) & ".xsd")
...