|
Introduction
This is the second part in a series of hands-on tutorials that will take you through all the steps to build a complete application using the new SQL Server 2005 database and Visual Studio 2005 IDE tools.
The .NET WebLinks project is a collection of Internet sources for information about .NET 2.0, Visual Studio 2005 and SQL Server 2005. Links to these articles are collected into a searchable database and categorized by subject. The project will allow all the .NET enthusiasts to add to the database, so that it will grow to become a very valuable tool for the .NET community.
Part 1 defined the database using SQL Server 2005 and business logic using datasets.
Part 2 is a Windows interface to the database, allowing individuals to import links from their Favorites directory, edit the links and upload to the master database.
Part 3 is a Web based interface to allow the general community access to search the database for the desired links.
Part 4 will define the Web services for the project, and how to consume them.
Part 2 - Windows User Interface Tutorial
Using Windows Form development environment is also known as building Smart Clients. Visual Studio (VS) 2005 has a significantly
improved Integrated Development Environment (IDE) that allows forms to be created with very little code. For more information,
see my article on Using Visual Studio 2005 RAD tools to build n-tiered applications.
The Data Sources window and the Binding Sources components provide a Rapid Application Development (RAD) environment that, with use of Object Data Sources, will still preserve the n-tiered architecture we are striving for.
Note: This tutorial builds on the work completed in Part 1. If you did not complete the tutorial in Part 1,
you may download the source code for it here and continue with this tutorial.
Create the Windows User Interface
- Open Visual Studio 2005 development environment by selecting the WebLinks.sln solution file created in Part 1. You may also open Visual Studio and then using the File - Open - Project/Solution (Ctrl-Shift-O) and navigating to the WebLinks.sln solution file in your Part 2 file folder.
Add the Windows Project
Add a new project to the solution by making sure the solution is selected and then selecting File, New, Project and give it
the Name Win. Another way is to right click on the solution in Solution Explorer and Add, New Project. In the New Project dialog, select Visual Basic, Windows, Windows Application.
Note: Just for convenience, I tend to use short project names. The namespaces can get rather long to type otherwise.
-
VS will create a default form for you. In Solution Explorer, rename the Form1 to ImportFavorites.vb. If the Properties window is not showing in the right pane of VS, select it from the View menu (or press F4). In the properties panel, change the title of the screen by changing the Text property of the form to "WebLinks - Import Favorites".
-
The first step in creating a data bound form is to show the Data Sources window from the Data menu. This pane usually attaches to the left side of the develop screen and can be collapsed by clicking the pin icon in the title bar.
-
Create a new data source by right-clicking in the Data Sources pane and selecting Add Data Source (or from the top line Data menu). In this case, you want to select Object as the type of Data Source.
-
On the next screen of the Wizard, you will need a reference to the Biz project. If Biz is not listed, click the Add Reference button to the right to add a reference so VS will know where to look for the dataset.
-
On the Add Reference dialog, select the Projects tab, select the Biz project (if it is not listed, you may have forgotten to build the Biz project). Click OK to add the reference.
-
Back on the Data Source Configuration Wizard, expand the Biz entry and select the WebLinks entry. Click Finish to complete the Wizard.
-
Now the dataset should be visible in the Data Sources pane. Click the "+" next to the WebLinks dataset to see a list of tables. Expand the Link table to see the fields in the table. As you select each field, a combo box appears to the right of the field name. Choose the control you would like to use to display the field. Select the LinkID and the LinkTypeID and change them to comboBox. Defaults should suffice for the remaining data columns.
-
Select the Link table and in the dropdown list choose Details. Drag the Link table to the form and the controls will be rendered as specified.
-
The wizard adds several things to your form. A dataset component is added with a reference to the Biz project. This instantiates the dataset for you. In the properties window, rename the dataset to be dsWebLinks to give it a unique name.
-
Also added is a binding source component and a binding navigator for the Link table. After a little re-arranging, deleting the uneeded columns, and changing the Description to Multiline, the form should look something like this:
You are probably wondering about rendering the LinkID field as a combo box. The idea is to make a navigation control where the user can select the link from a list and move to the selected record for editing. By setting the data source to the same binding source as the form and binding to the ID field of the same source, when the user selects a link from the list, the position of the binding source is set to that record and the record will be displayed in the detail fields. Rather than setting these properties, this can be easily accomplished by using the Data Sources window.
-
In the Data Sources window, select the Link table icon with your mouse and drag and drop it on the LinkID combo box. This will set the data source, set the value member to the primary key and set the display member to the first text field. This also moves the binding from text to selected value. (You have to expand the Data Bindings property to see this change.)
Note: Sometimes VS doesn't realize you want to use the same binding source as the form and will load another binding source named LinkBindingSource1. If this happens, modify the Data Source property of the combo box to the LinkBindingSource and delete the new one added.
-
You can set the properties for the Link Type ID combo box in the same way. Drag the LinkType table from the Data Sources window and drop it on the Link Type ID combo box. You will see a new binding source component added for the LinkType table and all of the same properties of the combo box set properly.
-
Now it is time to test our form and make sure it works, but first you need to add one line of code to fill the dataset. Double click on the title bar of the form and VS will create a code stub for the Form_Load event and open the code page for you. In this method, enter the call to fill the dataset.
Private Sub ImportFavorites_Load(ByVal sender As System.Object, _
ByVal e As System.EventArgs) Handles MyBase.Load
dsWebLinks.FillAll()
End Sub
Note: this is just a temporary line of code to show the project works. This will be replaced with a more sophisticated logic soon.
-
One other piece of code needed is to save edits. There is an icon on the Navigation bar that looks like a diskette. Select this and change the Enabled property to True. Then double-click on the icon and VS will create a code stub for the click event. Add the following code to validate the data, save the data from the binding source back to the dataset and from the dataset back to the database:
Private Sub LinkBindingNavigatorSaveItem_Click(ByVal sender As System.Object, _
ByVal e As System.EventArgs) Handles LinkBindingNavigatorSaveItem.Click
Me.Validate()
LinkBindingSource.EndEdit()
dsWebLinks.Link.Update()
End Sub
Note: When using a Database Data Source, this code is generated for you. When using Object Data Sources, VS leaves it to be added by the developer.
-
In Solution Explorer, right click on the Win project and select "Set as Startup project"
-
Right click again on the Win project and select Properties. Under the Applications tab, make sure the Startup form is set to the ImportFavorites you just created.
-
Try running the form by selecting Start Debugging from the Debug menu (F5) or click the green right arrow in the tool bar.
VS will first build (compile) the project and check for errors and prompt you if it finds any. If no errors are found, the form will open and load the data from the database, showing the Link table data in the form. The navigation bar will allow you to switch between the records and the "+", "X", and diskette icons on the navigation bar allow you to Add and Delete records and Save changes.
There are still a lot of things to fix about the form, but it helps to see it actually function. If you drop down the Link ID combo box, you will see a list of titles. If you select one, you will see the record focus of the form change to the selected title. But the titles are not sorted and there are too many to show in a combo box successfully.
-
Close the running form by clicking the large red "X" in the upper right corner. Or you can switch back to the VS window and select Stop Debugging from the Debug menu or click the green square box in the tool bar. This will bring you back to VS.
Adding Functionality
-
Inside the VS design screen, select the tab for form designer which has a "[Design]" tag in the title (instead of the code page).
-
The LinkID navigation combo box shows the same information as the title but you can hide that by sizing it down so that just the down arrow is showing (width=21). Change the DropDownWidth property to 600 so it will show the titles when it is dropped down. Delete the Link ID label and move the combo box just to the right of the Title label, before the Title textbox. The resulting form changes should look like the following form.
Note: this is a convention I employ to minimize the amount of screen space used by the lookup control. It will function just as well if you prefer to leave it wide.
-
You can sort the titles in the list in order by setting the Sort property of the LinkBindingSource component. Set the sort property to "LinkTitle ASC".
-
Change the Date Picker control property for the Format to "Short". Resize the control down to the proper size.
-
The Avg. Rating control is a read-only field that is calculated by the server so textbox is not the appropriate way to render the field. This would be good place to create a user control that shows the number of stars relating to the rating. In the interest of time, you can use the progress bar to show the rating. Delete the AvgRatingTextBox control and replace it by dragging a Progress Bar control from the Toolbox. Edit the properties of the new control and change the Maximum to 5, Style to Blocks, and the width (under Size) to 72 (exactly 5 bars of width). Expand the Data Binding section and change the Value to LinkBindingSource - AvgRating.
-
You need a way to open the link and read the article. Shorten the URL textbox and add a button control to the right of it. In the properties, name the button btnOpenURL. Double-click on the button to add a code stub to handle the Click event. Add the following code:
Private Sub btnOpenURL_Click(ByVal sender As System.Object, _
ByVal e As System.EventArgs) Handles btnOpenURL.Click
System.Diagnostics.Process.Start(URLTextBox.Text)
End Sub
Note: verify the name of the URL Textbox control on your form.
-
One more field needs to be added from the Link table for the Inactive Flag. Open the Data Sources window, expand the Link table, and drag the InActiveFlag field to the form behind the progress bar. Change the label and remove the word Flag.
The form should now have the following layout:
-
One more function is needed to complete the Link table. The purpose of this screen is to allow the user to edit the link information. In the current mode, the user needs to remember to click the save button in the tool bar or their edits will be lost. Unfortunately, this is not as easy as it should be. In a Grid View, it is easy to catch the RowLeave event and check for the IsCurrentRowDirty property. But there is no such event when working with Detail View controls. The only way to intercept when data changes is with the BindingComplete event of the binding source—the BindingCompleteContext will have a value of DataSourceUpdated. The problem is that the Binding Complete event fires every time the binding source passes data to or from the dataset. The following code works even though it is a bit complex. It adds two event handlers to the LinkBindingSource. Add the following code to the code page, just before the End Class statement:
'Track if any changes have been made to the data on this record
Dim _DataEdited As Boolean
Private Sub LinkBindingSource_BindingComplete(ByVal sender As System.Object, _
ByVal e As System.Windows.Forms.BindingCompleteEventArgs) _
Handles LinkBindingSource.BindingComplete
'check for binding source changed some data in Dataset
If e.BindingCompleteContext = BindingCompleteContext.DataSourceUpdate Then
If Not _DataEdited And e.BindingCompleteState = BindingCompleteState.Success _
And e.Binding.Control.Focused Then 'change made by User
_DataEdited = True
End If
End If
End Sub
'When user moves off of a record, check for edited controls and verify saving.
Private Sub LinkBindingSource_PositionChanged(ByVal sender As System.Object, _
ByVal e As System.EventArgs) Handles LinkBindingSource.PositionChanged
If (_DataEdited) Then
'confirm to save changes
Dim msg As String = "Do you want to save changes to this record?"
If (MessageBox.Show(msg, "Save Changes?", MessageBoxButtons.YesNo) = _
Windows.Forms.DialogResult.Yes) Then
LinkBindingNavigatorSaveItem_Click (sender, e)
Else
dsWebLinks.Link.RejectChanges()
End If
End If
'reset the edited flag for next record
_DataEdited = False
End Sub
Topic Table
Since the Topic table is hierarchical, the best way to render it is with a treeview control. The goal of this section is to create a treeview showing the topics and a grid view to show the links that are in the selected topic in the tree (from the TopicLink table).
-
Lengthen the form to make room for the new controls
-
Drag the mouse across all the controls on the form (called lassoing) to select all the controls at once. Drag the controls down toward the bottom of the form, leaving room at the top.
-
Drag a treeview control from the tool box to the top left corner of the screen. Name the control tvTopic and change the FullRowSelect property to True and the HideSelection property to False.
-
From the Data Sources window, make sure the TopicLink table is set to render as a DataGridView (use the drop down to select DataGridView) and drag it to the form to the right of the treeview. VS will add a binding source for TopicLink and set the Data Source property of the grid to it.
Your form should now look something like this:
-
The treeview is not as automatic as some controls and requires some code to make it functional. The method to fill the tree is recursive since the data is hierarchical. The following code will build the tree from the Topic table:
#Region "Treeview"
Private Sub BuildTree()
tvTopic.Nodes.Clear() 'Clear the nodes before building
'get the root node in the Topics table
For Each row As Biz.WebLinksDataSet.TopicRow In _
dsWebLinks.Topic.Select("ParentTopicID is Null")
Dim topNode As TreeNode = tvTopic.Nodes.Add(row.TopicName)
topNode.Tag = row.TopicID 'save ID in tag
'Call recurisve sub for branches
BuildTreeSub(row.TopicID, topNode)
topNode.Expand() 'Show node expanded
If topNode.Nodes.Count > 0 Then topNode.Nodes(0).Expand()
Next
End Sub
Private Sub BuildTreeSub(ByVal TopicID As Integer, _
ByVal ParentNode As TreeNode)
For Each row As Biz.WebLinksDataSet.TopicRow In dsWebLinks.Topic.Select( _
"ParentTopicID =" & TopicID.ToString(), "TopicName")
Dim node As TreeNode = ParentNode.Nodes.Add(row.TopicName)
node.Tag = row.TopicID
BuildTreeSub(row.TopicID, node)
Next
End Sub
#End Region
-
Call the BuildTree method from the Form Load event. Scroll up to the top of the code page to the ImportFavorites_Load method and add a line of code to build the tree.
Private Sub ImportFavorites_Load(ByVal sender As System.Object, _
ByVal e As System.EventArgs) Handles MyBase.Load
dsWebLinks.FillAll()
BuildTree()
End Sub
-
Next you need to filter the grid to show the links for the selected topic in the treeview. Switch back to the Form Design page and double-click on the treeview control to generate a stub for the After Select event. Then add the following code without the sub and end sub (here is where you take advantage of the TopicID stored in the tag property):
Dim IDList As String
Private Sub tvTopic_AfterSelect(ByVal sender As System.Object, _
ByVal e As System.Windows.Forms.TreeViewEventArgs) Handles tvTopic.AfterSelect
dsWebLinks.TopicLink.FillByTopicID(e.Node.Tag)
'Get a list of linkIDs for this topic
IDList = ""
'loop through each data row view in the binding source (as filtered)
For Each row As DataRowView In TopicLinkBindingSource
If IDList.Length > 0 Then IDList &= ","
IDList &= row("LinkID").ToString
Next
'Set the filter for the links to only those under selected topic
If IDList.Length > 0 Then
LinkBindingSource.Filter = "LinkID IN(" & IDList & ")"
Else
LinkBindingSource.Filter = "LinkID=0" 'No records selected
End If
End Sub
You need a way to edit the Topic table and allow the user to add and delete topics. In the interest of screen real estate, these commands could be best implemented as contextual menu accessed by right-clicking on a node.
-
From the Menus and Toolbars section of the Toolbox, drag a ContextMenuStrip to the form (drop it anywhere). This will add a component and a menu and allow you to start editing the menu options. Add the options for "Add Child Topic", "Delete Topic" and Rename Topic.
Once you click off the menu it will disappear. If you need to do further edits, select the component and it will reappear.
Next you need to add the code for each of the menu options. Ideally, it would be nice to create the tree node and then edit it in place, but that would take a custom control, so you will use the Input box instead. Then check to see if the topic is a duplicate of one already entered. If there is a duplicate in the same level (same parent), then you cannot add, otherwise prompt the user to add the duplicate. After adding, select the new node in the tree.
-
Double-click on the left margin of the first menu item to create a code stub for the AddChildTopic click event. Add the following code:
Private Sub AddChildTopicToolStripMenuItem_Click(ByVal sender As System.Object, _
ByVal e As System.EventArgs) Handles AddChildTopicToolStripMenuItem.Click
'add a child node (topic) to the treeview
Dim curTopic As Integer = 0
If Not tvTopic.SelectedNode Is Nothing Then
curTopic = CType(tvTopic.SelectedNode.Tag, Integer)
End If
Dim newTopic As String = InputBox("Enter the new child Topic Name:")
If newTopic.Length > 0 And newTopic.Length < 50 Then
'See if topic already exists
Dim rows() As Biz.WebLinksDataSet.TopicRow = _
dsWebLinks.Topic.Select("TopicName='" & newTopic & "'")
If rows.Length > 0 Then 'Found a match
'Get the parent row
Dim parent As String = ""
For Each row As Biz.WebLinksDataSet.TopicRow In rows
If Not rows(0).IsParentTopicIDNull Then
'is the parent the same as the current node?
'then cannot add (must be unique with siblings)
If row.ParentTopicID = curTopic Then
MessageBox.Show("The topic " & newTopic _
& " already exists at this level")
Return
Else
parent &= " " & dsWebLinks.Topic.FindByTopicID( _
row.ParentTopicID).TopicName
End If
End If
Next
'Confirm to create a duplicate topic for another node
Dim msg As String = "This topic already exists under the topic " _
& parent & ". Do you want to continue adding it to this node?"
If MessageBox.Show(msg, "Confirm duplicate topic", MessageBoxButtons.YesNo) _
= Windows.Forms.DialogResult.No Then Return
End If
'create the topic
Dim newRow As Biz.WebLinksDataSet.TopicRow = dsWebLinks.Topic.NewTopicRow()
newRow.TopicName = newTopic
If curTopic <> 0 Then newRow.ParentTopicID = curTopic
newRow.InActiveFlag = True
newRow.DateCreated = Now()
newRow.CreatedBy = My.User.Name
newRow.EndEdit()
dsWebLinks.Topic.Rows.Add(newRow)
dsWebLinks. Topic.Update ()
'rebuild the treeview
BuildTree()
'position to new node
For Each node As TreeNode In _
tvTopic.Nodes.Find(dsWebLinks.Topic.FindByTopicID(curTopic).TopicName, _
True)
If node.Tag = curTopic.ToString() Then
tvTopic.SelectedNode = node
Return
End If
Next
End If
End Sub
-
Deleting a node requires checking for child topics and calling a recursive function to delete all children. Double-click on the Delete Topic menu to generate another code stub and paste in the following code:
'delete a topic (and sub topics) from the treeview
Private Sub DeleteToolStripMenuItem_Click(ByVal sender As System.Object, _
ByVal e As System.EventArgs) Handles DToolStripMenuItem.Click
If tvTopic.SelectedNode Is Nothing Then
MessageBox.Show("No node is selected - Delete aborted")
Return
End If
Dim curTopic As Integer = CType(tvTopic.SelectedNode.Tag, Integer)
'get the Topic row
Dim row As Biz.WebLinksDataSet.TopicRow = dsWebLinks.Topic.FindByTopicID(curTopic)
'confirm delete
Dim msg As String = "Are you sure you want to delete the Topic " _
+ tvTopic.SelectedNode.Text
'Are there any children topics
If dsWebLinks.Topic.Select("ParentTopicID=" & curTopic).Length > 0 Then
msg &= " and all child (decendent) topics"
End If
msg &= "?"
If MessageBox.Show(msg, "Confirm Delete", MessageBoxButtons.YesNo) = _
Windows.Forms.DialogResult.Yes Then
DeleteTopic(curTopic)
BuildTree()
End If
End Sub
'Delete a topic and all sub topics (recursive)
Private Sub DeleteTopic(ByVal TopicID As Integer)
Cursor = Cursors.WaitCursor
'Are there any sub topics
For Each row As Biz.WebLinksDataSet.TopicRow In _
dsWebLinks.Topic.Select("ParentTopicID=" & TopicID)
DeleteTopic(row.TopicID)
Next
'First delete any related records in TopicLink
For Each related As DataRow In _
dsWebLinks.TopicLink.Select("TopicID=" & TopicID)
related.Delete()
dsWebLinks.TopicLink.Update () 'update the database
Next
'Now delete the topic
dsWebLinks.Topic.FindByTopicID(TopicID).Delete()
dsWebLinks.Topic.Update() 'update the database
Cursor = System.Windows.Forms.Cursors.Default
End Sub
-
The Rename Topic is the easiest method to write. Double click on the menu to generate the code stub and add the following code:
Private Sub RenameTopicToolStripMenuItem_Click(ByVal sender As System.Object, _
ByVal e As System.EventArgs) Handles RenameTopicToolStripMenuItem.Click
If tvTopic.SelectedNode Is Nothing Then Return
'Get the Topic row
Dim rowTopic As Biz. WebLinksDataSet.TopicRow = _
dsWebLinks.Topic.FindByTopicID(tvTopic.SelectedNode.Tag)
'Get the new name
rowTopic.TopicName = InputBox("Enter the new name for the selected topic - " _
& tvTopic.SelectedNode.Text)
rowTopic.EndEdit()
tvTopic.SelectedNode.Text = rowTopic.TopicName
dsWebLinks.Topic.Update()
End Sub
-
Now select the data grid view. The grid is a container for the TopicLink table and what you need to show is the links that are under the selected topic. To translate the TopicID to show the Topic Title, use a combo box control. But first, create a binding source to bind the combo box to the Topic table. Yes you already have a binding source for the Topic table but this is being used, so drag another binding source from the toolbox, name it cboLinkBindingSource, set the Data Source to dsWeblinks (under Other Data Sources, ImportFavorites List Instances), the Data Member to the Link table, and the Sort property to "LinkTitle ASC" (ASC is short for ascending).
-
Select the data grid view and in the upper right corner is a smart tag (small square with a right arrow in it). This context menu allows choosing the data source, editing and adding columns, etc. Select the Edit Columns option.
-
In the Edit Columns dialog, you can choose which columns to include in the grid. Use the Remove button to take away all the fields but the LinkID. For this field, set the following properties:
| Header Text: |
Link Title |
| Column Type: |
DataGridViewComboBoxColumn |
| Data Source: |
cboLinkBindingSource |
| Display Member: |
LinkTitle |
| Value Member: |
LinkID |
| Width: |
400 |
-
The link displayed in the main section needs to be linked to the selected record in the data grid view. By intercepting the Row Enter event of the grid, the position of the binding source for the Link can be synched with the grid. Make sure you have renamed the grid to "dgvTopicLink". Add the following code:
Private Sub dgvTopicLink_RowEnter(ByVal sender As System.Object, _
ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) _
Handles dgvTopicLink.RowEnter
LinkBindingSource.Position = CType(LinkBindingSource.Find( _
"LinkID", dgvTopicLink(0, e.RowIndex).Value), Integer)
dsWebLinks.Link.Clear()
dsWebLinks.Link.Merge(dsWebLinks.Link.GetByLinkID( _
dgvTopicLink(0, e.RowIndex).Value))
End Sub
-
Try running the form again to check for errors and proper function.
Note: If you get an error "Failed to enable constraints", go back to Part 1 and check step 32 on Page 20.
Cross Reference Topics
One of the features of the program is to allow a link to be classified under more than one topic. A data grid view shows the different topics assigned to each link. Now comes the problem—it is hard to display hierarchical data in a grid. One way is to show each topic with its ancestry, like bread crumbs in a web page. For example, the WinForms topic would be displayed as:
DotNet - Version 2.0 - Visual Studio 2005 - WinForms
To do this, you need to construct a query to show the hierarchy for each topic. This is most efficiently done in the back-end as a table function, but since the hierarchy is being defined in the treeview control, it can be captured while the tree is being built and stored in a temp table in the dataset.
-
The following code is an enhancement on the Treeview functions already built. The code will build a temp table in the dataset if it does not already exist and add a line to it for each treeview node as it is generated. Replace the two treeview functions with the following code:
Private Sub BuildTree()
tvTopic.Nodes.Clear() 'Clear the nodes before building
Dim ExpandedName As String
'build temp table in dataset for expanded (hierarchical) Topic name
If dsWebLinks.Tables("ExpandedTopic") Is Nothing Then
Dim newTable As New DataTable("ExpandedTopic")
Dim colID As New DataColumn("ID", System.Type.GetType("System.Int32"))
newTable.Columns.Add(colID)
Dim colName As New DataColumn("Name", System.Type.GetType("System.String"))
newTable.Columns.Add(colName)
dsWebLinks.Tables.Add(newTable)
End If
Me.Cursor = Cursors.WaitCursor
Cursor = System.Windows.Forms.Cursors.Default
'get the root node in the Topics table
For Each row As Biz.WebLinksDataSet.TopicRow In _
dsWebLinks.Topic.Select("ParentTopicID is Null")
Dim topNode As TreeNode = tvTopic.Nodes.Add(row.TopicName)
topNode.Tag = row.TopicID
ExpandedName = row.TopicName
'add expanded name to temp table for combo box
Dim newRow As DataRow = dsWebLinks.Tables("ExpandedTopic").NewRow()
newRow("ID") = row.TopicID
newRow("Name") = ExpandedName
dsWebLinks.Tables("ExpandedTopic").Rows.Add(newRow)
BuildTreeSub(row.TopicID, topNode, ExpandedName)
topNode.Expand()
If topNode.Nodes.Count > 0 Then topNode.Nodes(0).Expand()
Next
'LookupTopicComboBox.DataSource = dsWebLinks.Tables("ExpandedTopic")
'LookupTopicComboBox.DisplayMember = "Name"
'LookupTopicComboBox.ValueMember = "ID"
Cursor = System.Windows.Forms.Cursors.Default
tvTopic.Focus()
tvTopic.SelectedNode = tvTopic.Nodes(0)
End Sub
Private Sub BuildTreeSub(ByVal TopicID As Integer, ByVal ParentNode As TreeNode, _
ByVal ExpandedName As String)
For Each row As Biz.WebLinksDataSet.TopicRow In dsWebLinks.Topic.Select( _
"ParentTopicID =" & TopicID.ToString(), "TopicName")
Dim node As TreeNode = ParentNode.Nodes.Add(row.TopicName)
node.Tag = row.TopicID
Dim ExpName As String = ExpandedName & "--" & row.TopicName
BuildTreeSub(row.TopicID, node, ExpName)
'add expanded name to temp table for combo box
Dim newRow As DataRow = dsWebLinks.Tables("ExpandedTopic").NewRow()
newRow("ID") = row.TopicID
newRow("Name") = ExpName
dsWebLinks.Tables("ExpandedTopic").Rows.Add(newRow)
Next
End Sub
-
Drag a combobox from the Toolbox to the form below the Description textbox and give it the name LookupTopicComboBox. Extend it to be the same length as the Description. Since the data table does not exist at design time, set the properties in code. Near the end of the BuildTree method, there are three lines of code commented out. Uncomment the lines of code to assign the datasource.
LookupTopicComboBox.DataSource = dsWebLinks.Tables("ExpandedTopic")
LookupTopicComboBox.DisplayMember = "Name"
LookupTopicComboBox.ValueMember = "ID"
-
Now you have a tool for adding cross references, you need some more code to add the TopicLink record each time a Topic is selected. Add the following code:
Private Sub LookupTopicComboBox_SelectedIndexChanged(ByVal sender As System.Object, _
ByVal e As System.EventArgs) Handles LookupTopicComboBox.SelectedIndexChanged
If Not LookupTopicComboBox.SelectedValue Is Nothing _
& Not LinkIDComboBox.SelectedValue Is Nothing Then
'check to see if it exists to avoid duplicates
Try
Dim rows() As DataRow = dsWebLinks.TopicLink.Select( _
"LinkID=" & LinkIDComboBox.SelectedValue & " AND TopicID=" _
& LookupTopicComboBox.SelectedValue.ToString)
If rows.Length = 0 Then
'save the current link and topic selections.
Dim row As Biz.WebLinksDataSet.TopicLinkRow = _
dsWebLinks.TopicLink.NewTopicLinkRow()
row.LinkID = LinkIDComboBox.SelectedValue
row.TopicID = Me.LookupTopicComboBox.SelectedValue()
row.EndEdit()
dsWebLinks.TopicLink.Rows.Add(row)
dsWebLinks.TopicLink.Update()
End If
Catch
'ignore the error for now
End Try
End If
End Sub
Now you have the capability of adding cross references, it would be nice to see all of the topics that reference this link. You could add another grid to show the topics referenced for this topic, but since you already have a grid, why not make it do double duty. This is not recommended for average users, but since this application is targeted for developers, the efficient use of space might be appreciated. So the grid will be converted from showing the links for a Topic, to showing the topic for a given Link.
-
Back on the grid, edit the Columns and add a new column for the TopicID field. Render it with a DataGridViewComboBox control and give it a special name of TopicIDcol and width of 400. While editing columns, make sure the LinkID column is named LinkIDcol. The names are important as you will be referencing them in code.
-
Add a button to the form near the bottom of the form to the right of the cross reference combo box and name it btnTopicList. Double click on the button to open the code stub for the click event. Add the following code to switch the grid from showing Links to showing Topics.
Private Sub btnTopicList_Click(ByVal sender As System.Object, _
ByVal e As System.EventArgs) Handles btnTopicList.Click
LinkIDcol.Visible = False
TopicIDcol.Visible = True
TopicLinkBindingSource.Filter = "LinkID=" & LinkIDComboBox.SelectedValue
End Sub
-
When the treeview is clicked, the columns need to be switched back. Add the following lines of code to the end of the tvTopic_AfterSelect method.
'Set the visible column in the grid
LinkIDcol.Visible = True
TopicIDcol.Visible = False
Searching on Key Words and Voting on the Rating
In this section, you will implement the Stored Procedure to search for articles by key words. This function will find records in the Link table based on words in the Title or Description fields. The SP returns the IDs to the records in the form of the TopicLink table so it can be displayed in the grid.
-
Lengthen the form and move all the controls down so there is a space for the search textbox at the top of the screen.
-
Add (drag from the toolbox) a label, textbox, and button controls. Align them along the top of the form, above the grid. Change the text property of the label to "Search by Key Words:", the name of the textbox to "KeyWordsTextBox", the name of the button to "GoButton" and the text of the button to "Go".
-
Double click on the Go button to build a code stub for the Click event and add the following code:
Private Sub GoButton_Click(ByVal sender As System.Object, _
ByVal e As System.EventArgs) Handles GoButton.Click
If Me.KeyWordsTextBox.Text.Length > 0 Then
dsWebLinks.TopicLink.Clear()
dsWebLinks.TopicLink.GetByKeyWords(KeyWordsTextBox.Text)
End If
End Sub
-
Add another label with the text "Rate this Article" and a combo box with the name RatingComboBox. In the Items collection property of the combobox, add the following:
1 - Poor
2 - Fair
3 - Average
4 - Good
5 - Excellent
-
Double click on the combobox control to create a code stub for the
Private Sub RatingComboBox_SelectedIndexChanged(ByVal sender As System.Object, _
ByVal e As System.EventArgs) Handles RatingComboBox.SelectedIndexChanged
If RatingComboBox.Text.Length >0 Then
'Get the first char and convert to integer
Dim rating As Integer = Integer.Parse(RatingComboBox.Text.Substring(0, 1))
dsWebLinks.SubmitRating(LinkIDComboBox.SelectedValue, rating)
Me.Refresh()
End If
End Sub
Importing Favorites
One of the primary reasons for having a Smart Client interface to this application is so it can interact with your PC and import the favorites you have collected. This function will prompt the user for the path of the favorites to import. Using a root node of the user name of the current user (if the user did not log in, this is read from the system), a Topic structure is created from the structure of the file folders in the imported favorites folder. (If the user has done a good job organizing the structure of the favorites, the Topics should be organized also.) All of the links are imported to the database and assigned to the corresponding Topic.
-
The function needs to query the user for the folder to import. From the Dialogs section of the Toolbox, drag a FolderBrowserDialog component to the form. In the Properties, change the RootFolder property to Favorites.
-
Add a button to the form with the title Import Favorites and give it a name of btnImport. Double click on the button to start a code stub for the click event and add the following code:
#Region "Import routines"
''' Import the links saved in the favorites path to the Links table
''' and the folder names to the topic table under the Username node.
Private Sub btnImport_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnImport.Click
Me.FolderBrowserDialog1.ShowDialog()
Dim FilePath As String = Me.FolderBrowserDialog1.SelectedPath
If Not My.Computer.FileSystem.DirectoryExists(FilePath) Then
MessageBox.Show("Invalid Path ")
Return
End If
'get the username from the path
Dim UserName As String = My.User.Name
'create a root topic for the username
Dim rows() As Biz.WebLinksDataSet.TopicRow, _
row As Biz.WebLinksDataSet.TopicRow
rows = dsWebLinks.Topic.Select("ParentTopicID is Null AND TopicName='" _
& UserName & "'")
'take first row of array returned by select.
If rows.Length > 0 Then row = rows(0)
'see if the Topic already exists
If row Is Nothing Then
'create a root topic for user
row = dsWebLinks.Topic.NewTopicRow()
row.TopicName = UserName
row.DateCreated = Date.Now()
row.InActiveFlag = True
row.CreatedBy = UserName
row.EndEdit()
dsWebLinks.Topic.Rows.Add(row)
dsWebLinks.Topic.Update()
End If
'Call recursive folder import
Cursor = Cursors.WaitCursor 'System.Windows.Forms.Cursors.
Dim cnt As Integer = ImportLinks(FilePath, row, UserName)
Cursor = Cursors.Default
BuildTree()
MessageBox.Show("Finished importing " & cnt & " new Links")
End Sub
Private Function ImportLinks(ByVal Path As String, _
ByVal ParentRow As Biz.WebLinksDataSet.TopicRow, _
ByVal UserName As String) As Integer
Dim cnt As Integer = 0 'counter for number of links
'Create a topic for the current folder
Dim Folder As String = Path.Substring(Path.LastIndexOf("\") + 1)
Dim rows() As Biz.WebLinks.TopicRow, row As Biz.WebLinks.TopicRow
rows = dsWebLinks.Topic.Select("ParentTopicID=" & ParentRow.TopicID.ToString _
& " AND TopicName='" & Folder & "'")
'take first row of array returned by select.
If rows.Length > 0 Then row = rows(0)
'see if the Topic already exists
If row Is Nothing Then
'create the new row
row = dsWebLinks.Topic.NewTopicRow()
row.TopicName = Folder
row.ParentTopicID = ParentRow.TopicID
row.CreatedBy = UserName
row.DateCreated = Date.Now
row.InActiveFlag = True
row.EndEdit()
dsWebLinks.Topic.Rows.Add(row)
dsWebLinks.Topic.Update()
End If
'Recurse through each directory
For Each dir As String In My.Computer.FileSystem.GetDirectories(Path)
cnt = cnt + ImportLinks(dir, row, UserName)
Next
'Now get the link files
For Each file As String In My.Computer.FileSystem.GetFiles(Path)
'Get the URL out of the file
Dim url As String = My.Computer.FileSystem.ReadAllText(file)
Dim start As Integer = url.IndexOf("URL=") + 4
url = url.Substring(start, url.IndexOf(vbCrLf, start + 1) - start).Trim
Dim title = My.Computer.FileSystem.GetName(file)
'Drop the extention
title = title.Substring(0, title.LastIndexOf("."))
Dim linkRow As Biz.WebLinks.LinkRow
'check to see if the link already exists in the table
Dim linkRows() As Biz.WebLinks.LinkRow
linkRows = dsWebLinks.Link.Select("URL='" & url & "'")
If linkRows.Length = 0 Then 'Any rows returned?
'Add each filename to the links table
linkRow = dsWebLinks.Link.NewLinkRow()
linkRow.LinkTitle = title
linkRow.URL = url
linkRow.ArticleDate = _
My.Computer.FileSystem.GetFileInfo(file).CreationTime.ToShortDateString
linkRow.DateCreated = Date.Now()
linkRow.ModifiedBy = UserName
linkRow.EndEdit()
dsWebLinks.Link.Rows.Add(linkRow)
dsWebLinks.Link.Update()
cnt += 1
Else
linkRow = linkRows(0)
End If
'check for entry already in TopicLink table
If dsWebLinks.TopicLink.Select("TopicID=" & row.TopicID & _
" AND LinkID=" & linkRow.LinkID).Length = 0 Then
'Add an entry to TopicLink table using the dataset quick add method
Dim tlRow As Biz.WebLinks.TopicLinkRow = _
dsWebLinks.TopicLink.AddTopicLinkRow(row, linkRow, "", True, _
Date.Now(), UserName, linkRow.LinkTitle)
dsWebLinks.TopicLink.Update()
End If
Next
End Function
#End Region
-
Compile and run the finished application.
About the Author
David Catherman - CMI Solutions
Email: DCatherman (at) CMiSolutions (dot) com
David Catherman has 20+ years designing and developing database applications with specific concentration for the last 4-5 years on Microsoft .NET and SQL Server. He is currently Application Architect and Senior Developer at CMI Solutions using Visual Studio 2005 and SQL Server 2005. He has several MCPs in .NET and is pursuing MCSD.
|
