Building a Glossary

This feature is a short segment from Jeff’s Programming for e-Learning Developers book.

Building a Glossary

A common e-Learning requirement is to be able to click on a hyperlink and display a popup display of the corresponding definition. We’ll implement a simple version of this capability in this chapter. We’ll build on techniques we learned in the Hyperlinking to a URL chapter in order to create the hyperlinks. To make things interesting and to avoid having to create a custom “data store” of the glossary names and definitions for each environment, We’ll add a GetGlossaryDefinition method to our ASP.NET service and call that to retrieve the actual definition.

ASP.NET Web Service

We first created this service in the Sending Email chapter. To add a glossary lookup capability, we first need to decide how to store the glossary entries. A good choice is a database. We’ll use an Access database adapted from our Training Studio product and locate it in the “App_Data” directory of our web site. This is a good choice since ASP.NET will prevent anything in this directory from being downloaded. We’ll use a simple structure where we look up the value based on the GlossaryName column and store the value in the GlossaryDefinition column.

We’ll include a stored query called ReadGlossaryEntries that We’ll use to load the entire table of glossary entries into memory from the web service. This allows us to “cache” the data on the web server. When a glossary request comes in, the web server will only read the database if it is not already in memory. This dramatically improves performance and is why we can get away with using Access rather than a higher-powered database like SQL Server. We don’t have time to get into the details of how the e-Learning developer would add, edit, or delete glossary entries, but there are options ranging from simple forms in Access to a separate editor application (which is what we use in Training Studio).

Here is the implementation code (Visual Basic) for our new GetGlossaryDefinition web method.

Imports System.Data
Imports System.Data.OleDb

<WebMethod()> Public Function GetGlossaryDefinition(ByVal accessKey _
	As String, ByVal glossaryKey As String) As String

	Dim glossaryDefinition As String = accessKeyInvalidString

	If accessKey = Me.WebServiceAuthorizationKey Then
		Dim viewId As DataView = Me.GlossaryDataView

		viewId.RowFilter = String.Format("GlossaryName = '{0}'", _
			glossaryKey.Replace("'", ""))
		If viewId.Count > 0 Then
			glossaryDefinition = viewId(0)("GlossaryDefinition").ToString
		Else
			glossaryDefinition = "Definition not found."
		End If
	End If

	Return glossaryDefinition
End Function

Private ReadOnly Property GlossaryDataView() As DataView
	Get
		Dim viewId As DataView

		If Current.Cache("GlossaryDataView") Is Nothing Then
			Dim conStr As String = _
				String.Format("provider=Microsoft.Jet.OLEDB.4.0; Data 
				Source={0}App_Data\Glossary.mdb", _
				Current.Request.PhysicalApplicationPath)

			Dim conId As New OleDbConnection(conStr)
			Dim commandId As New OleDbCommand("ReadGlossaryEntries", conId)
			Dim adapterId As New OleDbDataAdapter(commandId)
			Dim tableId As New DataTable

			conId.Open()
			commandId.CommandType = CommandType.StoredProcedure
			adapterId.Fill(tableId)
			viewId = tableId.DefaultView
			Current.Cache("GlossaryDataView") = viewId
			conId.Close()
		Else
			viewId = CType(Current.Cache("GlossaryDataView"), DataView)
		End If

		Return viewId
	End Get
End Property

We start by importing two additional namespaces that we need: System.Data (having to do with general database objects) and System.Data.OleDb (having to do with communicating with Access databases). We then mark GetGlossaryDefinition with the <WebMethod()> attribute to denote that it can be called externally and make it Public. As with our previous examples, we validate against our WebServiceAuthorizationKey, which is stored in the web.config file on the web site. From there we read our GlossaryDataView property to get our viewId object, which is of type DataView. A DataView is a representation of a table that can be sorted and, what we are looking for, filtered. To do this, we set its RowFilter property. We use the fact that the column with our “key” is named “GlossaryName.” So if we are looking for the definition of “bass,” the RowFilter would be: GlossaryName = ‘bass’. We put the value in single quotes to account for the situation where it is multiple words (like “lead guitar”). We remove any single quotes in the key to avoid formatting problems. We now check the Count of our viewId. If it is greater than 0 (it should be 1 but we don’t want it to stop working if we get a duplicate entry for some reason), we get the first row (viewId(0)) and then the “GlossaryDefinition” column. Since this could presumably have other types of data, we call its ToString method to get the text version. This is what we return from our method call.

Let’s now look at the GlossaryDataView property. This is ReadOnly, meaning that we only read the data and never set it. It is also Private, which means that it can only be used by methods within the web service (e.g., we couldn’t read it from our ToolBook, Flash, or other applications). We define our viewId return type and then check the Cache to see if it is holding an object with the “GlossaryDataView” key. If not, we read the database. To do this, we start with our “connection string,” which is the conStr variable. We use Request.PhysicalApplicationPath to get our hands on the actual local file path of our web site. Our provider is “Microsoft.Jet.OLEDB.4.0” and our data source is the complete path to the database. We could add a password if needed as well. Once we have a connection string, we use this to build an OleDbConnection object. This is what actually connects to the database. We use this in turn to build an OleDbCommand object, telling it the name of the query, more generally called a “stored procedure,” (“ReadGlossaryEntries”) and passing it the connection. Still not done, we use this command to create an OleDbDataAdapter object. This is what actually executes the query. We want to read the query into a DataTable object, so that is what we create next. We are now ready to go. We call the Open method of our conId connection object. We set the CommandType property of our command object to tell it that what we passed back in its “constructor” was the name of a stored procedure (query). We then call the Fill method of the adapter to execute the query and write the results to our tableId object. From there, we read its DefaultView property to get what we are looking for, a DataView object. So that we don’t have to keep touching the database (which is a relatively slow operation), we add viewId to the Cache. Until the web server needs the memory for something else, future calls the web service will get viewId directly out of memory. Either way, we return viewId to the calling method.

Programming for e-Learning Developers Information

Order Programming for e-Learning Developers ($29.95 with free shipping in the U.S.)

Advertisements

About Jeff Rhodes
Jeff Rhodes is the Chief Technical Officer and owner of Platte Canyon Multimedia Software Corporation, a leader in developing commercial software that Improves the Lives of Training Developers. He graduated at the top of his class at the Air Force Academy, where he earned a Bachelor of Science in Electrical Engineering. Jeff received a Masters degree in Economics from the London School of Economics, which he attended under a British Marshall Scholarship. Jeff is the author of "Programming for e-Learning Developers: ToolBook, Flash, JavaScript, & Silverlight" and "VBTrain.Net: Creating Computer and Web Based Training with Visual Basic .NET." He also co-wrote "The ToolBook Companion." He has had numerous articles on training development published and is a frequent presenter at conferences both in the U.S. and Europe. Jeff lives in Colorado Springs with his wife Sue and sons Derek and Michael.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: