Microsoft SharePoint and SQL Queries
December 2, 2008
Have you had a hankerin’ to build your own FullTextQuerySQLQuery Object? Well, fire up VisualStudio and dig in so you understand the cost implications of making SharePoint do your bidding. Oh, you don’t have VisualStudio! Oh, oh, you aren’t a developer! Well, just get yourself a Windows Certified SharePoint Professional and you are ready to go.
The place to start is the Fringe SharePoint Web log here. I have to congratulate the author. Figuring out how to create a FullTextQuerySQLQuery Object was not trivial. You, like me, may be wondering why the search system in SharePoint needs a FullTextQuerySQLQuery Web part. The answer is that the search in SharePoint does not handle certain tasks very well; for example, querying content stored in a SQL Server table. Keep in mind that there are search and content processing vendors that offer systems that support these types of queries. You load these systems and the system does the work.
Not so with SharePoint. You get to build the function, or your developers do. That’s the main reason why Certified SharePoint Professionals like SharePoint so much. It is better than being and Oracle or IBM database administrator. One SharePoint engineer told me in Aarhus, Denmark, in November 2008, “I have a job for as long as I want it.”
Okay.
Now let’s see how complicated this FullTextQuerySQLQuery Object is to create. Fringe SharePoint Web log has a four part series that makes the process understandable.
- Creating a Recent Hire WebPart using FullTextSQLQuery object and Linq (Part 1) is here
- Creating a Recent Hire WebPart using FullTextSQLQuery object and Linq (Part 2) is here
-
Creating a Recent Hire WebPart using FullTextSQLQuery object and Linq (Part 3) is here
- Creating a Recent Hire WebPart using FullTextSQLQuery object and Linq (Part 4) is here
To give you a flavor of the method, here’s a snippet of the explanation:
For the first Field I used a HyperLinkField.Notice that I Hardcoded the Url of the current site. This might not be the way you want to do it, you can dynamically retrieve this field. Notice the AccountName that is part of the object we created. It is mapped to the DataNavigateUrlFormatString. This is how you make that link Clickable!! I think the code speaks for itself. If you have questions on that let me know. The second thing to note is line 101, It has a RowDataBoudn event!! this is for the formatting of the string in the hire date. This event occurs just before it binds to the SPGridView which is great because we want to format it before it is displayed.
What does the code look like? Here’s an example from the fourth post on the subject:
Yep, I had some trouble reading the code snippet as well.
Let’s step back. You have SharePoint. You have SQL Server. You have a need to search the content in the SQL Server table. You have to code a method.
When I put on my accounting hat, I realize that it is difficult to estimate the cost of creating a Web part. I suppose I can plug in a number based on the costs for similar work in the past, but what if this new Web part doesn’t work. Then the costs begin to climb, and as an accountant I only have one way to stop the bleeding. I cut off the funding and then I have complaints about the SharePoint system. Now what do I do?
If I were the hypothetical accountant, I would buy a copy of Beyond Search, read the profiles, and license a system that comes with this function. The fix will be easier to budget in my opinion.
I hope to hear from some SharePoint Certified Professionals to tell me why my hypothetical accountant is off base. Please, PR people, don’t email me directly. Post your comments using the feedback system for this Web log.
Stephen Arnold, December 2, 2008
Comments
One Response to “Microsoft SharePoint and SQL Queries”
-
- Subscribe to Beyond Search
Feature archive
News archive
-
Implementing a full text search on a relational database such as SQL Server is always going to be painful. The only efficient way to perform fast full text search is with an inverted index, stored in a custom database format, i.e. an external system.