Since SharePoint 2013, SharePoint Search features are a lot more powerful than in previous versions. The information discovery potential is enormous, we can connect to external sources (SQL, Web Services, etc) drastically improving the potential of any solution using Search. But sometimes the configuration necessary to connect with external content sources can be painfull (BCS, External Content Types, etc).  In this post we will show you how you can easily index SQL external content without worrying about painfull configurations.

How it works

As we said earlier, instead of going through the usual BCS – External Content Type kind of solution, what we did was to configure the SharePoint Search Service Application to Crawl a Content Source that consisted of a Web Site exposing the items we wanted to index:

Let’s walkthrough the configuration together.

Configure the Search Service Application

First we need to create a new Content Source of the type Web Sites pointing to a web site we will create:

What we are doing here is telling the SharePoint Search Service Application to index the pages it will find at the address we gave. There we will have a page with a list of links pointing to each of the itens we want to index. For that to happen we must also tell the Search Service to follow complex URLs. That is done by creating a Crawl Rule as the following:

The path should be something like http://yourwebsiteurl/*.

After that we must create and provision our Web Site.

Web Site Setup

As we said earlier, we will have a website that will have only 2 functions:

  1. It will have a page with links pointing to the “Details Page” of each item we want to Crawl/Index.
  2. The “Details Page” will query the database for the items content and will write  it as metatags on the header section

What we did here was to create a simple MVC Web Application with two simple views, an Index view with the list of urls and a Details view.

Our Index.cshtml:

@{
    var urls = (List<string>)ViewData["Urls"];

    foreach (var item in urls)
    {
        @Html.ActionLink(item, item);
    <br />
    }
}

And our Detail.cshtml:

@{
    var tags = (List<SqlSearchCrawl.Web.Models.Metatag>)ViewData["Metatags"];
}
@section metatags
{
    @foreach (var t in tags)
    {
        <meta name="@t.Name" content="@t.Content" />
    }
}

We created a controller with two actions (Index and Detail):

public ActionResult Index(int id = 1)
        {
            var spName = ConfigurationManager.AppSettings["storeprocedurename"];
            var pageSize = int.Parse(ConfigurationManager.AppSettings["PageSize"]);

            bool nextLink = false;

            var masterDetail = new SqlDAL().GetResults(spName, id, pageSize, out nextLink);

            List<string> urls = new List<string>();
            var keys = masterDetail.Keys;

            foreach (var k in keys)
            {
                urls.Add(string.Format("/detail/{0}", k));
            }

            if (nextLink)
            {
                id += 1;
                urls.Add(string.Format("/index/{0}", id));
            }

            ViewData["Urls"] = urls;

            return View("Index");
        }

        [HttpGet]
        public ActionResult Detail(int id)
        {
            var spName = ConfigurationManager.AppSettings["storeprocedurename"];
            var pageSize = int.Parse(ConfigurationManager.AppSettings["PageSize"]); 
            bool nextLink = false; 
            var masterDetail = new SqlDAL().GetResults(spName, 0, 0, id, out nextLink); 
            ViewData["Metatags"] = masterDetail[id]; 
            return View("Detail"); 
        }

As you can see in the code, we get our information from a Stored Procedure and in order to keep the solution flexible we stored it in the web.config as an appsetting. We also have a DAL layer that communicates with the SQL Server:

public Dictionary<int, List<Metatag>> GetResults(string procName, int pageNumber, int pageSize, out bool nextLink)
        {
            return this.GetResults(procName, pageNumber, pageSize, 0, out nextLink);
        }
        public Dictionary<int, List<Metatag>> GetResults(string procName, int pageNumber, int pageSize, int id , out bool nextLink)
        {
            nextLink = false;
            SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["Db"].ConnectionString);
            SqlCommand comm = new SqlCommand(procName, con);

            comm.Parameters.Add("@Page", SqlDbType.Int);
            comm.Parameters.Add("@PageSize", SqlDbType.Int);
            comm.Parameters.Add("@id", SqlDbType.Int);
            comm.Parameters.Add("@ReturnValue", SqlDbType.Int);
            comm.Parameters["@ReturnValue"].Direction = ParameterDirection.Output;
            comm.Parameters["@PageSize"].Value = pageSize;
            comm.Parameters["@id"].Value = id;
            comm.Parameters["@Page"].Value = pageNumber;

            comm.CommandType = System.Data.CommandType.StoredProcedure;

            con.Open();
            SqlDataAdapter dap = new SqlDataAdapter(comm);
            DataTable dt = new DataTable();
            dap.Fill(dt);

            List<DataColumn> cols = dt.Columns.Cast<DataColumn>().ToList();

            Dictionary<int, List<Metatag>> response = new Dictionary<int, List<Metatag>>();

            foreach (DataRow dr in dt.Rows)
            {
                List<Metatag> tags = new List<Metatag>();
                foreach (DataColumn col in cols)
                {
                    tags.Add(new Metatag()
                                        {
                                            Name = col.ColumnName,
                                            Content = dr[col].ToString()
                                        });
                }
                response.Add(int.Parse(dr[0].ToString()), tags);
            }

            int spReturnValue = int.Parse(comm.Parameters["@ReturnValue"].Value.ToString());
            bool firstCrawl = Convert.ToBoolean(ConfigurationManager.AppSettings["FirstCrawl"]);
            nextLink =spReturnValue == 1;//check if we have more pages beyond this set of results

            dt.Dispose();
            dap.Dispose();
            con.Close();
            con.Dispose();

            return response;
        }

Here we call the store procedure, either to get a list of items or a specific item.

Creating the Stored Procedures

We decided to use Stored Procedures because of the following reasons:

  • Easy to manipulate in order to add/remove columns to be indexed
  • Allow the possibility of paging results

We can see in the example below that we have the possibility of paging the results as well as returning a specific row:

CREATE PROCEDURE [dbo].[sp_Search_Table]
			(
				@Page int,
				@PageSize int,
                                @id int,
				@ReturnValue int output
			)
            AS
            BEGIN
			DECLARE @FirstRec int, @RowCount int

			SELECT @FirstRec = (@Page - 1) * @PageSize
			SELECT @RowCount = COUNT(*) FROM [dbo].[Table]
		IF @id > 0
		 BEGIN
                    SELECT [Id] as sl_ext_id,
		    FROM [dbo].[Table]
                    WHERE [Id] = @id
                END
            ELSE
                BEGIN
                    SELECT [Id] as sl_ext_id,
		    FROM [dbo].[Table]
		    ORDER BY [Id]
		    OFFSET @FirstRec ROW
		    FETCH NEXT @PageSize ROWS ONLY
                END
			IF @RowCount > (@PageSize * @Page)
				BEGIN
					SET @ReturnValue = 1
					RETURN @ReturnValue
				END
			ELSE
				BEGIN
					SET @ReturnValue = 0
					RETURN @ReturnValue
				END


            END

We have an output value that tell us if we have more results to return after the current set.

In Summary

After that all we need to do is to run a full crawl. The metatag names will be our crawled properties names, you’ll just need to map them to a Managed Property. If you need to add/remove more columns to be indexed all you need to do is add or remove them from the store procedure!

As you can see, putting this all together is really easy but there is a downside, SharePoint Search will read the content as HTML pages. If you are using the FileType property you might find a bit inconvenient to see this results interpreted as HTML pages. You can easily overcome this by using the Content Enrichment Web Service as we described here.


0 Comments

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.