Share via

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

  • Article

Applies to: SharePoint Server 2010

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 resource page in the MSDN Code Gallery.

To create the Web Site project

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

  2. In Installed templates, click Visual C#, and then click ASP.NET Empty 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. Click OK.

To create the ASPX page

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

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

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

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

  5. 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;

  6. 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;";

  7. 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));
        }
    }
}

  8. 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();
    }
}

  9. 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();
}

  10. Add the code for the GetVendorHTML method. This method returns the vendor data for a single result.

    private string GetVendorHTML(string vendor)
    {
        StringBuilder vendorHTML = new StringBuilder();
        string strCommand = string.Format("select ResellerName,BusinessType,ProductLine,AnnualRevenue from DimReseller WHERE ResellerName='{0}'", vendor);
        vendorHTML.Append("<html><head><title>More Results HTML Page</title></head><body>");
        using (SqlConnection connect = new SqlConnection(connectionString))
        {
            connect.Open();
            SqlCommand command = new SqlCommand(strCommand, connect);
            SqlDataReader sqlReader = command.ExecuteReader();
            if (sqlReader.HasRows)
            {

                while (sqlReader.Read())
                {
                    vendorHTML.AppendFormat("<p><b>{0}</b><br>{1} {2} {3}</p>", sqlReader[0], sqlReader[1], sqlReader[2], sqlReader[3]);


                }


            }
            else
            {
                vendorHTML.AppendFormat("No vendor found for: <b>{0}</b>", vendor);
            }

            if (!sqlReader.IsClosed)
            {
                sqlReader.Close();
            }
            vendorHTML.AppendFormat("<p><a href={0}?q={1}&f=htm>Return to Vendor List</a></p>", Request.Path,query);
        }
        return vendorHTML.ToString();
    }

Next Steps

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

See Also

Concepts

Code Sample: Federated Search SQL Server Connector

Architecture Guidance for Building Federated Search Connectors