Part II: How Did Google Become a Verb?


Visual Basic Today by Paul Kimmel - March 2, 2004

Introduction

In the 1990s, somewhere in a basement in Berkeley, California, some industrious person with a lot of storage capacity came up with the idea to store snapshots of the World-Wide Web. This site is now owned by Amazon.com and is called Alexa.com. Alexa will permit you to retrieve probably any Web site as far back as 1996. (If they have my original Web site, Alexa probably has yours.) This capability is called the Wayback machine (and now seems to be part of a free toolbar download).

It is the massive amounts of interesting, peculiar, sometimes useful, occasionally trashy data that makes the Internet so compelling. It is the ability to find information that makes the Internet so useful.

The second half of our article completes the answer to the rhetorical question: "How Did Google Become a Verb?" In this half, I will overlap a bit by showing you how to ensure that you have query access to the Index Server we configured in the first half of this article, and then we will turn all of that goodness into a reusable class, a couple of UserControls, and a Web page that can be used on any Web site. By the time you are finished, users should be able to find anything on your site you'd like them to have access to.

Testing Search Support with VS.NET's Dynamic Query Tools

Recapping, in Part I we installed Microsoft's Indexing service and ran a stored procedure to associate the MS Indexing service with SQL Server. The result is that we can use ADO.NET and SQL to send queries to the Indexing service.

If you want to follow along, complete these steps to test your installation and configuration of SQL Server and the Indexing service:

  1. Open Visual Studio .NET.
  2. Open the Server Explorer.
  3. Find the node that contains the name of your computer server that is running SQL Server. (This is the same server that you ran the stored procedure in, in Part I.)
  4. Open any table in any database on that server. This step will show the Query toolbar and give us easy access to the SQL pane.
  5. Enter a query similar to the following and press the Run toolbar button. (This button is the Query|Run shortcut represented by an exclamation symbol on the toolbar.)
SELECT * FROM OPENQUERY(FileSystem, 'SELECT FileName,
                        Characterization FROM SCOPE()
         WHERE FREETEXT(''footer'') > 0')

The key to success is to substitute some text that might yield results where I used the word footer. If you are successful, your VS.NET desktop should look something like mine (shown in Figure 1). Results will be based on what the Indexing service returns and any other docked windows you may have open.

Figure 1: The Server explorer left, SQL Pane top right, and the Results pane shown bottom right.

If you get some returned data, everything is ready and the rest of our work can be done with ASP.NET and Visual Basic .NET. (Keep in mind that if you want a lot of good material on ASP.NET programming, pick up a copy of Stephen Walther's book Sams ASP.NET Unleashed.)

Part II: How Did Google Become a Verb?

Defining the Search Class

It is a good practice to separate solutions from user interfaces, also called business rules and presentation layers. In response, we can separate the ability to specify search criteria and obtain a dataset from any other aspect of our program. Even though we know this solution will be used for the Web, compartmentalizing is a great way to divide and conquer problems. To that end, I implemented the search capability as a distinct class, separate from any UserControls or Web pages.

The search class has a very simple interface: It contains one method, GetResults, that accepts a string and returns a DataTable containing the results of the search. As you will see in the code that follows, all of the plumbing—such as the connection string and lines of code that put ADO.NET to work for us—is concealed inside of the Search class and GetResults method (see Listing 1).

Listing 1: The Search class uses a very simple interface, concealing all of the ADO.NET plumbing internally.

Imports System
Imports System.Data
Imports System.Data.SqlClient
Imports System.Configuration

Public Class Search
  Private Shared ReadOnly FQuery As String = _
    "SELECT * FROM OPENQUERY(FileSystem, 'SELECT RANK, FileName, _
    " + _
    "VPath, Characterization FROM SCOPE() WHERE FREETEXT(''{0}'') _
     > 0 " + _
    "ORDER BY RANK DESC' )"

  Private Shared ReadOnly FConnectionString As String = _
    "User Id=user_name;password=password;Initial _
     Catalog=Web;Data Source=server_name;"

  Private Shared ReadOnly Property ConnectionString() As String
  Get
    Return FConnectionString
  End Get
  End Property

  Public Shared Function GetResults(ByVal FreeText As String) _
         As DataTable
    Dim sql As String = String.Format(FQuery, FreeText)
    Dim connection As SqlConnection = _
        New SqlConnection(ConnectionString)
    connection.Open()

    Try
      Dim adapter As SqlDataAdapter = New SqlDataAdapter(sql, _
                                          ConnectionString)
      Dim data As DataTable = New DataTable("Results")
      adapter.Fill(data)
      Return data
    Finally
      connection.Close()
    End Try

  End Function

End Class

The example is syntactically correct, but you will have to provide your own valid user name, password, and server name.

A good practice is to user a user name and password with the least permissions required. A second good rule of thumb is to treat things such as connection strings as secrets and protect these secrets; you might use the CryptoAPI for encryption in the example. (Although encryption is beyond the scope of this article, an excellent way to pick up best practices, like encrypting secrets, is to attend events such as the upcoming DevDays.)

Part II: How Did Google Become a Verb?

Creating a SearchItem and SearchControl UserControl

UserControls are a great way to piece together solutions. For our example, we'll want to list all of the items returned by the selection criteria, and we may want to support re-use by re-using a search control in more than one application. To this end, I created a SearchItem and a SearchControl UserControl. The SearchItem is a Web UserControl that contains one row from our results DataTable, and the SearchControl provides a means of inputting search criteria and viewing all of the results.

Building the SearchItem UserControl

The SearchItem control is a UserControl that contains an HTML table for managing layout, a bullet, two labels, and a hyperlink. The basic idea is that for each row in the DataTable (returned by Search.GetResults) we show the rank, the characterization—a summary of the results—and a link to that information on the Web site.

The visual implementation of SearchItem is shown in Figure 2. The HTML behind the UserControl is shown in Listing 2, and the code-behind in VB.NET is provided in Listing 3.

Figure 2: The SearchItem UserControl is an HTML table with a few simple controls organized as shown in the designer in the figure.

Listing 2: The ASP supporting the UserControl shown in Figure 1.

<%@ Control Language="vb" AutoEventWireup="false"
    Codebehind="SearchItem.ascx.vb"
    Inherits="softconcepts_vb.SearchItem"
    TargetSchema="http://schemas.microsoft.com/intellisense/ie5" %>
<TABLE id="Table1" cellSpacing="0" cellPadding="0" width="100%"
       border="0" align="left"
  runat="server" class="SearchContent" style="TABLE-LAYOUT: fixed;
                 OVERFLOW: hidden; CLIP: rect(auto 75% auto auto)">
  <TR>
    <TD width="10" vAlign="top" align="center"><li></li>
    </TD>
    <TD>
      <asp:Label id="LabelRank" runat="server"></asp:Label>
      <asp:HyperLink id="HyperLinkPath" runat="server">
      </asp:HyperLink>
    </TD>
  </TR>
  <tr>
    <TD></TD>
    <td><asp:Label id="LabelCharacterization" runat="server"
                   Width="75%"></asp:Label></td>
  </tr>
</TABLE>

Listing 3: The code-behind for the SearchItem UserControl.

Imports System
Imports System.Data
Imports System.Drawing
Imports System.Web
Imports System.Web.UI.WebControls
Imports System.Web.UI.HtmlControls

Public Class SearchItem
    Inherits System.Web.UI.UserControl

  Protected FData As DataRowView = Nothing

[ Web Form Designer Generated Code ]

    Private Sub Page_Load(ByVal sender As System.Object, _
            ByVal e As System.EventArgs) Handles MyBase.Load
      BindData()
    End Sub

    Private Sub Binddata()

      If (data Is Nothing) Then Return
      LabelRank.Text = Rank
      LabelCharacterization.Text = Characterization
      HyperLinkPath.NavigateUrl = Path
      HyperLinkPath.Text = Path

    End Sub
      
    Private ReadOnly Property Path() As String
    Get
      If (Not (data Is Nothing) And Not (data("VPATH") _
                                         Is Nothing)) Then
        Return data("VPATH").ToString()
      Else
        Return String.Empty
      End If
    End Get
    End Property

    Private ReadOnly Property Characterization() As String
    Get
      If (Not (Data Is Nothing) And Not (Data("CHARACTERIZATION") _
                                         Is Nothing)) Then
        Return Data("CHARACTERIZATION").ToString()
      Else
        Return String.Empty
      End If
    End Get
    End Property

    Private ReadOnly Property Rank() As String
    Get
      Return GetFormattedRank()
    End Get
    End Property

    Private Function GetFormattedRank() As String
      Try
        Dim rank As Integer = Convert.ToInt32(Data("RANK"))
        Return String.Format("({0}%)", rank / 10)
      Catch
        Return "0%"
      End Try
    End Function

    Public Property Data() As DataRowView
    Get
      Return FData
    End Get
    Set(ByVal Value As DataRowView)
      FData = Value
      Binddata()
    End Set
    End Property

End Class

The code-behind for the SearchItem UserControl exposes a DataRowView property, Data, and includes some friendly properties for easily retrieving data from the DataTable. When we build the SearchControl, we will need to bind the DataList to our DataTable and for each SearchItem control created, we will need to initialize it with a DataRowView object.

Part II: How Did Google Become a Verb?

Building the SearchControl UserControl

The SearchControl contains an HTML table for managing layout, a Label, a TextBox for inputting search criteria, a Button to request the result set, and a DataList. The DataList's ItemTemplate contains a SearchItem control. For each row in the DataTable—returned by GetResults—the DataList will create a SearchItem control, and we will bind a row of data to each SearchItem control.

Figure 3 shows the designer view of the SearchControl. Listing 4 shows the ASP for the control, and Listing 5 shows the code-behind.

Figure 3: The design-time view of the SearchControl.ascx UserControl.

Listing 4: The ASP view of the SearchControl.

<%@ Register TagPrefix="uc1" TagName="SearchItem"
             Src="SearchItem.ascx" %>
<%@ Control Language="vb" AutoEventWireup="false"
    Codebehind="SearchControl.ascx.vb"
    Inherits="softconcepts_vb.SearchControl"
    TargetSchema="http://schemas.microsoft.com/intellisense/ie5" %>
<TABLE id="Table1" height="100%" cellSpacing="0" cellPadding="0"
       width="75%" border="0">
  <TR>
    <TD style="HEIGHT: 38px" colSpan="1" rowSpan="1">
      <asp:Label id="Label1" runat="server">Search for:
      </asp:Label></TD>
  </TR>
  <TR>
    <TD style="HEIGHT: 44px" colSpan="1">
      <asp:TextBox id="TextBoxSearch" runat="server" Width="291px">
      </asp:TextBox>
      <asp:Button id="Button1" runat="server" Text="Search">
      </asp:Button><BR>
      <HR width="100%" SIZE="2">
    </TD>
  </TR>
  <TR>
    <TD vAlign="top" align="left" id="Results" runat="server"
        width="100%">
      <asp:DataList id="DataListResults" runat="server"
                    Width="100%">
        <ItemTemplate>
          <uc1:SearchItem id=SearchItem1 runat="server"
                          Data="<%# Container.DataItem %>">
          </uc1:SearchItem>
        </ItemTemplate>
      </asp:DataList></TD>
  </TR>
</TABLE>

The ASP is pretty straight-forward. The only real catch is that we use block script to bind a row of data to the SearchItem.Data property in the code-behind. (The block script is shown in bold font.) Here is the code-behind for the SearchControl itself.

Listing 5: The code-behind for the SearchControl.ascx UserControl.

Imports System
Imports System.Data
Imports System.Diagnostics
Imports System.Drawing
Imports System.Web
Imports System.Web.UI.WebControls
Imports System.Web.UI.HtmlControls
Imports softconcepts_vb


Public Class SearchControl
    Inherits System.Web.UI.UserControl

[ Web Form Designer Generated Code ]

  Private Sub Page_Load(ByVal sender As System.Object, _
    ByVal e As System.EventArgs) Handles MyBase.Load
    'Put user code to initialize the page here
  End Sub

  Private Sub Button1_Click(ByVal sender As System.Object, _
    ByVal e As System.EventArgs) Handles Button1.Click

    FetchResults(SearchText)

  End Sub

  Private ReadOnly Property SearchText() As String
  Get
    Return TextBoxSearch.Text
  End Get
  End Property

  Private Sub FetchResults(ByVal text As String)
    DataListResults.DataSource = Search.GetResults(text)
    DataListResults.DataBind()
  End Sub
    
End Class

As you can see, the code is quite simple. The user enters some text, clicks the button (shown in Figure 3), and the results are fetched and bound to the DataList.

Completing the Search Control Web Page

The last step is to drop the SearchControl.ascx control on a Web page and test it. There is no code required to use the SearchControl at this point. Find the SearchControl in the solution explorer, drag and drop it onto a Web page, and you are ready to go. For my Web site, I have several other controls, images, and a stylesheet that yields a total result (see Figure 4).

Figure 4: The completed search page incorporated into my company's Web site.

Summary

Building a well-constructed Web site is a function of good design, and layering complexity gradually. Building a visually attractive Web site is more subjective and perhaps harder to do. However, making the most of tools at your disposal, like Microsoft's Indexing service, will yield super-charged results.

In this article, I glossed over security and some of the micro steps necessary to build ASP.NET applications. For that information, I will have to ask your indulgence for another day and another article, but in this article you learned how to pass a query to the indexing service, implement some user controls, and a Web page that will help those who browse to your page find what they are looking for.

About the Author

Paul Kimmel is the VB Today columnist for codeguru.com and developer.com and has written several books on object-oriented programming, including the recently released Visual Basic .NET Power Coding from Addison-Wesley and the upcoming Excel VBA 2003: Programmer's Reference from Wiley. He is the chief architect for Software Conceptions and is available to help design and build your next application.

As an aside, many of you may not know that Michigan is in the top ten in IT spending in the US and perhaps the world. The dollar amount is astronomical, way into the billions of dollars. Resultantly, we get great shows like Microsoft's DevDays—during the first week of March 2004 this year—in Detroit. The cost is less than $100, making this show accessible to everyone. (Register early and the cost is $75.) You will have the opportunity to make a lot of great contacts, learn about killer Microsoft technologies, get a paid day or two out of the office, and as a presenter, I will get the opportunity for a lot of you to tell me what I still don't know.

Finally, the Lansing, Michigan area has a great opportunity to form a .NET Users Group. A well-run group offers great learning and networking opportunities and occasionally some free pizza and door prizes. Contact me at pkimmel@softconcepts.com if you are interested in participating.

# # #



Comments

  • There are no comments yet. Be the first to comment!

Leave a Comment
  • Your email address will not be published. All fields are required.

Top White Papers and Webcasts

  • In this on-demand webcast, Oracle ACE and Toad Product Architect Bert Scalzo discusses 10 powerful and hidden features in Toad® that help increase your productivity and DB performance. Watch this webcast today.

  • Mobile is introducing sweeping changes throughout your workplace. As a senior stakeholder driving mobile initiatives in your organization, you may be lost in a sea of technologies and claims from vendors promising rapid delivery of applications to your employees, customers, and partners. To help explain some of the topics you will need to be aware of, and to separate the must-haves from the nice-to-haves, this reference guide can help you with applying a mobile strategy in the context of application …

Most Popular Programming Stories

More for Developers

Latest Developer Headlines

RSS Feeds