Step 1: Create the Project for the Federated Search SQL Server Connector

Before you can use the sample, you need to create the Web site project with the ASPX page for the Federated Search SQL Server Connector.

You can download the complete code for this sample from the Federated Search SQL Server Connector Sample release. The release is located on the Microsoft Office SharePoint Server 2007 SDK Search Samples resource page of the MSDN Code Gallery.

To create the Web Site project

1. In Visual Studio 2008, on the File menu, point to New, and then click Web Site.

2. In Visual Studio installed templates, click ASP.NET Web Site.

3. In Location, click File System and, if necessary, click Browse to find the correct location for this Web site. This location can be the standard location for your Visual Studio projects and Web sites, or the location under your Web site's virtual directory where you will deploy this Web application.

4. In Language, click Visual C#, and then click OK.

To create the ASPX page

1. In Solution Explorer, right-click Default.aspx, and then click Delete to remove the default ASPX page created with the project.

2. On the Website menu, click Add New Item.

3. In the Add New Item dialog box, click Web Form, and then type searchresellers.aspx.

4. Check Place code in separate file, and then click Add.

5. In Solution Explorer, double-click searchresellers.aspx.cs.

6. In searchresellers.aspx.cs, add the following using statements to the namespace directives near the top of the code.

   using System.Text;
   using System.Data.SqlClient;
   

7. Add the following code below the class declaration.

   string query;
   string vendor;
   string format;
   string connectionString = "Data Source=(local);Integrated Security=SSPI;Initial Catalog=AdventureWorksDW;";
   

8. Override the Render method by using the following code.

   protected override void Render(HtmlTextWriter writer)
   {
       query = Request.QueryString["q"];
       vendor = Request.QueryString["v"];
       format = Request.QueryString["f"];
       if (vendor != null && vendor.Length > 0)
       {
           writer.Write(GetVendorHTML(vendor));
       }
       else
       {
           if (format == "htm")
           {
               writer.Write(GetResultsHTML(query));
           }
           else
           {
               StringBuilder sb = new StringBuilder();
               Response.ContentType = "text/xml";
               writer.Write(GetResultsXML(query));
           }
       }
   }
   

9. Add the code for the GetResultsXML method. This method executes a SELECT statement with the query term against the AdventureWorksDW database, and then converts the results to RSS format.

   private string GetResultsXML(string queryTerm)
   {
       using (SqlConnection connect = new SqlConnection(connectionString))
       {
           connect.Open();
           string strCommand = string.Format("select ResellerName,BusinessType,ProductLine,AnnualRevenue from DimReseller WHERE ResellerName like '%{0}%'", queryTerm);
           SqlCommand command = new SqlCommand(strCommand, connect);
           SqlDataReader sqlReader = command.ExecuteReader();
           StringBuilder resultsXML = new StringBuilder();
           resultsXML.Append("<?xml version=\"1.0\" encoding=\"utf-8\"?>");
           resultsXML.Append("<rss version=\"2.0\">");
           resultsXML.AppendFormat("<channel><title><![CDATA[Adventure Works: {0}]]></title><link/><description/><ttl>60</ttl>", queryTerm);
           while (sqlReader.Read())
           {
               resultsXML.AppendFormat("<item><title><![CDATA[{0}]]></title><link><![CDATA[{4}?v={0}&q={5}]]></link><description><![CDATA[{1} {2} {3}]]></description></item>", sqlReader[0], sqlReader[1], sqlReader[2], sqlReader[3], Request.Path.ToString(), query);
           }
           resultsXML.Append("</channel></rss>");
           return resultsXML.ToString();
       }
   }
   

10. Add the code for the GetResultsHTML method. This method executes a SELECT statement with the query term against the AdventureWorksDW database, and then displays the results in HTML.

    private string GetResultsHTML(string queryTerm)
    {
        StringBuilder resultsHTML = new StringBuilder();
        resultsHTML.Append("<html><head><title>More Results HTML Page</title></head><body>");
    
        using (SqlConnection connect = new SqlConnection(connectionString))
        {
            connect.Open();
            string strCommand = string.Format("select ResellerName,BusinessType,ProductLine,AnnualRevenue from DimReseller WHERE ResellerName LIKE '%{0}%'", query);
            SqlCommand command = new SqlCommand(strCommand, connect);
            SqlDataReader sqlReader = command.ExecuteReader();
    
            if (sqlReader.HasRows)
            {
                int i = 0;
                while (sqlReader.Read())
                {
                    if (i > 14)
                    {
                        break;
                    }
                    else
                    {
                        resultsHTML.AppendFormat("<p><a href='{4}?v={0}'>{0}</a><br>{1} {2} {3}</p>", sqlReader[0], sqlReader[1], sqlReader[2], sqlReader[3], Request.Path.ToString());
                        i++;
                    }
                }
            }
            else
            {
                resultsHTML.AppendFormat("<p>No results for query: <b>{0}</b>", query);
            }
    
            if (!sqlReader.IsClosed)
            {
                sqlReader.Close();
            }
        }
    
        resultsHTML.Append("</body></html>");
        return resultsHTML.ToString();
    }
    

Next Steps

Step 2: Deploy and Test the Federated Search SQL Server Connector

See Also

Concepts

Federated Search SQL Server Connector Sample
Architecture Guidance for Building Federated Search Connectors