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.)
Platte Canyon/Jeff Rhodes Blog 