SunQuest
 
       Windows Scripting
  Home arrow Windows Scripting arrow MS SQL and Searching MCMS with SharePoint
ASP Free Forums 
.NET  
ASP  
ASP Code  
ASP.NET  
ASP.NET Code  
BrainDump  
C#  
Code Examples  
Database  
Database Code  
IIS  
Microsoft Access  
MS SQL Server  
Visual Basic.NET  
Windows Scripting  
Windows Security  
XML  
ASP Web Hosting  
ASP.NET Web Hosting 
Dedicated Servers 
Actuate Whitepapers 
VeriSign Whitepapers 
Windows Web Hosting
 
IBM® developerWorks 
Sun Developer Network 
Weekly Newsletter
 
Developer Updates  
Free Website Content 
 RSS  Articles
 RSS  Forums
 RSS  All Feeds
Write For Us Get Paid 
Request Media Kit
Contact Us 
Site Map 
Privacy Policy 
Support 
 USERNAME
 
 PASSWORD
 
 
  >>> SIGN UP!  
  Lost Password? 
WINDOWS SCRIPTING

MS SQL and Searching MCMS with SharePoint
By: PACKT Publishing
  • Search For More Articles!
  • Disclaimer
  • Author Terms
  • Rating: 5 stars5 stars5 stars5 stars5 stars / 1
    2006-10-26

    Table of Contents:
  • MS SQL and Searching MCMS with SharePoint
  • Building the Microsoft SQL Full-Text Query
  • Building the MSQuery XML String
  • Summary

  • Rate this Article: Poor Best 
      ADD THIS ARTICLE TO:
      Del.ici.ous Digg
      Blink Simpy
      Google Spurl
      Y! MyWeb Furl
    Email Me Similar Content When Posted
    Add Developer Shed Article Feed To Your Site
    Email Article To Friend
    Print Version Of Article
    PDF Version Of Article
     
     
    ADVERTISEMENT

    Stay one step ahead of the competition. Evaluate and give feedback on some of the hottest web development tools on the market today. Make your opinion heard! Click Here

    MS SQL and Searching MCMS with SharePoint


    (Page 1 of 4 )

    Last week, we discussed building our own search controls and other topics related to searching MCMS with SharePoint. This week, we conclude our discussion. This article is excerpted from chapter five of the book Advanced Microsoft Content Management Server Development, written by Lim Mei Ying et al. (PACKT, 2005; ISBN: 1904811531).

    The Advanced Search and Results Page

    Once we have our search input control built, we need a page that will execute the search against the SPS Query Service Web Service and display the results. In addition, like all other search result pages, we need to add advanced searching options such as limiting our search to the Tropical Green plant catalog.

    Before we can start building the results page, we need to add a web reference to the SPS Query Service Web Service:

    1. In Visual Studio .NET, right-click the TropicalGreen project and select Add Web Reference.
    2. Enter the URL of the web service that will retrieve the search results. The URL of the Query Service is http://[portal]/_vti_bin/search.asmx. For this example, we'll use the portal created in Appendix A, http://portal.tropicalgreen.net/ _vti_bin/search.asmx. Then click the Go button. You will likely be prompted for a user ID and password since this is part of the SharePoint portal virtual server, which isn't configured for anonymous access.
    3. Once the web service loads and the available methods are shown in the Add Web Reference Dialog, click the Add Reference button to add the web service to our project.

    For simplicity, the search results page we will create will not be a CMS template, rather it will be a standard ASP.NET Web Form in the root of the Tropical Green project.

    1. Right-click the project and select Add | Add Web Form.
    2. Give the new page the name SearchResults.aspx.
    3. In Design view, drag and drop the Styles.css file from Solution Explorer onto the form to apply the stylesheet to the page.
    4. Change the page layout to FlowLayout.
    5. Drag the following user controls into the designer:
      •    \UserControls\TopMenu.ascx
      •    \UserControls\RightMenu.ascx

    1. Switch to HTML view and modify the body tag as follows:
      <body topmargin="0" leftmargin="0"> 
    2. Add the following HTML code to the page between the <form> tags, replacing the two controls we just added:
      <form id="Form1" method="post" runat="server">
      <table width="100%" border="0" cellspacing="0" cellpadding="0"> 
        <tr>
          <td width="100%" colspan="2" valign="top" bgcolor="#ffcc00"> 
            <img src="/tropicalgreen/images/Logo.gif">
          </td>
          <td vAlign="top" rowSpan="10">
          </td>
        </tr>
        <tr bgColor="#66cc33">
          <td colSpan="2">
            <uc1:TopMenu id="TopMenu1" runat="server">
            </uc1:TopMenu>
          </td>
        </tr>
        <tr>
          <td vAlign="top" style="PADDING-RIGHT:30px; PADDING-LEFT:30px;
             PADDING-BOTTOM:30px; PADDING-TOP:10px">
            <p>&nbsp;</p>
            <table cellspacing="0" cellpadding="10" border="1"  
                   bordercolor="#669900">
              <tr vAlign="top">
                <td>
                  <b>Tropical Green Search:<b/>
                </td>
              </tr>
              <tr>
                <td vAlign="top"> 
                  <b>Advanced Search</b>
                  <p>
                  <b>Search Results</b>
                </td>
              </tr>
            </table>
          </td>
          <td class="RightMenuBar" width="20%" valign="top" height="100%"
                           align="center" rowspan="2" bgcolor="#669900">
            <uc1:RightMenu id="RightMenu1" runat="server">
            </uc1:RightMenu>
          </td>
        </tr>
      </table>
      </form>

    We now have the basic layout for our advanced search and search results page, which looks similar to the other templates in our site. Let's add some controls for our advanced search.

    1. In Design view, drag a TextBox from the Toolbox and place it under the Advanced Search text.
    2. In Design view, drag a Button from the Toolbox and place it to the right of the TextBox.
    3. The next thing we need to add is a DataGrid to contain the results of the search. In Design view, drag a DataGrid control from the Toolbox to just under the Add Search Results Here text. We'll worry about formatting this control later, for now we just need something to show us our data.
    4. Set the properties of the controls we just added according to the following table:

    PropertyValue
    TextBox

    ID = txtAdvancedSearch

    Button

    ID = btnAdvancedSearch

    Text = Go

     DataGrid

    ID = dgrSearchResults 

    Our advanced search page should now look like this:

    Now it's time to start coding our search logic. First, we need to add an event handler for our advanced search button.

    1. In Design view, double-click the btnAdvancedSearch button to create a click event handler. Visual Studio .NET will add an event handler method to the code-behind file.
    2. Add the following code to the btnAdvancedSearch_Click() event handler: private void btnAdvancedSearch_Click(object sender, System.EventArgs e)
      {
        string keywords = this.txtAdvancedSearch.Text; 
        keywords = HttpUtility.UrlEncode(keywords);
        Response.Redirect(Request.ApplicationPath
                       
      + "/SearchResults.aspx?keywords="
                       
      + keywords);
      }

    Next, we need to check the querystring in the Page_Load() event handler to see if any keywords were passed from our SearchInput.ascx control or the txtAdvancedSearch TextBox.

    Add the following code to check if there are any keywords supplied and execute the search if so:

      private void Page_Load(object sender, System.EventArgs e)
      {
        if (Request.QueryString["keywords"] != null
        && Request.QueryString["keywords"] != String.Empty)
     
        {
          string keywords =  
    Request.QueryString["keywords"];
          DataSet ds = ExecuteSearch(keywords);
          this.dgrSearchResults.Visible = true;
          this.dgrSearchResults.DataSource = ds;
          this.dgrSearchResults.DataBind();

          // autofill the keyword input box with the search keywords 
          this.txtAdvancedSearch.Text = keywords;
       }
       else
       {
        
    this.dgrSearchResults.Visible = false;
       }
     }

    Now we need to create the method that will execute the search against our SPS content index. This method will:

    1. Create an instance of the Query Service Web Service we just added to the project.
    2. Call a method that will build the MSQuery to submit to the Query Service.
    3. Execute the search.
    4. Bind the search results to a DataGrid.

    Import the following namespaces in the SearchResults.aspx.cs file:

      using System.Security.Principal;
      using System.Runtime.InteropServices;
      using System.Net;
      using System.Text;
      using System.Text.RegularExpressions; 
      using Microsoft.ContentManagement.Publishing;

    Add the following method to the SearchResults.aspx.cs file after the Page_Load() event handler. This method will ensure the current thread is running under the original security context regardless of any impersonations that may have been invoked previously:

      // Get reference to the RevertToSelf method
      [DllImport("ADVAPI32.DLL")]
      public static extern int RevertToSelf();

      /// <summary>
      /// Builds the appropriate MSQuery,
      /// submits the query to the SPS Query Service,
      /// and returns the results as a DataGrid.
      /// </summary>
      /// <param name="keywords">String of keywords to search for.</param>
      /// <returns>DataSet of search results.</returns>
      private DataSet ExecuteSearch(string keywords)
      {
       
    // decode the list of keywords
        keywords = HttpUtility.UrlDecode(keywords);

        // create reference to the Query Service Web service
        net.tropicalgreen.portal.QueryService spsQueryService =
                                 new net.tropicalgreen.portal.QueryService();

        // use the current application pool identity to login
        // to the SharePoint Query Service Web service
        WindowsIdentity CurrentUser = WindowsIdentity.GetCurrent();

        try
       
    {
          // use the Application Pool account to do access the
          // SharePoint Search Services
          RevertToSelf();
          spsQueryService.Credentials = CredentialCache.DefaultCredentials;
       
    }
        catch(System.Exception exception)
        {
         
    throw new System.Exception(exception.Message);
        }
        finally
        {
         
    // ensure that the original user is being impersonated again
          CurrentUser.Impersonate();
          CurrentUser = null;
        } 
       
    // build MSQuery XML string to send to the Query Service
        // - change the content source to "CMSChannels" if you used SearchSetup.exe
        string msQuery = BuildMSQuery(keywords, "Tropical Green website");

        // execute the query and return the dataset
        return spsQueryService.QueryEx(msQuery);
      }

    If you used the SearchSetup.exe program to create your content sources, you should use
    the content source group "CMSChannels" instead of "Tropical Green website" in the
    code above.

    Our ExecuteSearch() method calls another method, called BuildMsQuery(), which constructs the MSQuery for sending to the QueryEx() web method. An MSQuery is composed of XML tags that provide instructions to the Query Service, such as the number of results to return in the request and a Microsoft SQL full-text (MSSQLFT) query. Building the MSSQLFT query and MSQuery is likely to be the most complicated task in implementing the SharePoint search. We'll break it into two tasks: building the actual MSSQLFT query and building the MSQuery XML string. We'll first build the full-text query that our MSQuery will use in the construction of the XML string we'll send to the Query Service.

    More Windows Scripting Articles
    More By PACKT Publishing


       · This article is an excerpt from the book "Advanced Microsoft Content Management...
     

    Buy this book now. This article is excerpted from chapter five of the book Advanced Microsoft Content Management Server Development, written by Lim Mei Ying et al. (PACKT, 2005; ISBN: 1904811531). Check it out today at your favorite bookstore. Buy this book now.

    WINDOWS SCRIPTING ARTICLES

    - Generating Outlook Signatures Based on Activ...
    - VBScript: Converting and Formatting with Fun...
    - VBScript: Conversion and Format Functions
    - VBScript: Array Functions
    - VBScript: Strings, You Can`t Function withou...
    - VBScript: More String Functions
    - VBScript: Functioning with Strings
    - Working with the Windows Registry in C++
    - Understanding Objects
    - HTML Applications: Giving WSH a User Interfa...
    - Modifying Computer Objects with Active Direc...
    - Logon Script to Send Email Notifications
    - Securing Computers and Active Directory
    - Moving and Renaming Computers with Active Di...
    - Working with System Processes in WSH





    © 2003-2008 by Developer Shed. All rights reserved. DS Cluster 2 hosted by Hostway