Linq To SharePoint...within a SQL-CLR Stored Proc...

Posted on 2011-09-20
Medium Priority
Last Modified: 2012-05-12
I'd like to see sample code and get learnings from anyone who has experience with using Linq to SQL from within a SQL-CLR stored procedure.

The end-goal is to have stored procedures and views on my SQL 2005 box that can query SharePoint 2010 lists effortlessly.

This kind of solution would get me:

o  Datastructure agnosticism -- the linq syntax could be set up to select * from the list, so that any column additions or deletions inside SharePoint would not affect my sp's or views.

o  No dealing with the weird table structures in SharePoint's SQL back-end that house its list data.  No having to use ; know GUIDs or whatever IDs to access the list data.

o  Reusable -- could pass in the name of my list to the SQL-CLR procedure.  No hard-coding list names or column names.

o  No third-party tools.

I see how Linq to SharePoint works.  There are examples all over the web.  And I know SQL CLR.  But I do *not* see anyone doing Linq to SharePoint within SQL-CLR...which seems to me to be the only way to achieve all these goals.

Thanks for sharing your experience.
Question by:bamapie
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
LVL 60

Expert Comment

by:Kevin Cross
ID: 36863987
Very interesting. Yes, you likely won't see this as most folks probably create a stored procedure on the SharePoint database server side and then you can just call the stored procedure there. No need to encapsulate it in LINQ as the system hosting SharePoint by definition knows its structure. The remote systems now are even more loosely coupled as they do not even have any code connecting it to the SharePoint server specifically in its CLR and can point to any SQL server with a stored procedure of the same name.

Beyond that point, I did not believe this is supported which is probably the bigger reason you have not seen it. In VS 2010, I can see reference for System.Xml.Linq that can be added; no Linq-to-Sql, so I got curious and went checking for confirmation.



Author Comment

ID: 36976838
Thanks for this helpful info.  I'm a little lost on this:

>most folks probably create a stored procedure on the SharePoint
>database server side and then you can just call the stored procedure there
>No need to encapsulate it in LINQ as the system hosting SharePoint by definition knows its structure.

Do you mean that somehow you can write an stored proc on the SharePoint DB that does not have to know that "Address" is stored in field "VarChar17"?  Is the SharePoint database structured that differently now, that I wouldn't have to know the underlying structure?  I'm just not tracking you on this point at all, sorry.

Sorry, also, for it being so incredibly long since I got back to you on your reply.
LVL 60

Expert Comment

by:Kevin Cross
ID: 36977374
Not quite what I meant. Sorry I phrased it that way as I re-read my comment and I see what you are asking. You are asking about encapsulating SharePoint on a remote system so that it is loosely coupled with SharePoint's underlying structure. My point is WHY would the remote system program this. Why not go the SharePoint database where the structure is known and program the encapsulating procedure there. In other words, the SharePoint DBA / Developer has to know the quirky structure, but once the interface is built there -- in theory, no one else does.

My point was more that instead of trying to create a LINQ to Sharepoint in a remote SQL Server CLR that you could simple create a procedure on the SharePoint SQL Server itself and call to it from the remote SQL instances.

Does that make more sense?

Maybe I am unclear on what you wanted to do in the first place.

Office 365 Training for Admins - 7 Day Trial

Learn how to provision tenants, synchronize on-premise Active Directory, implement Single Sign-On, customize Office deployment, and protect your organization with eDiscovery and DLP policies.  Only from Platform Scholar.


Author Comment

ID: 36977559
The problem is, sure, you can go write a stored procedure that says SELECT VARCHAR17, VARCHAR22, FROM X WHERE GUID = 'OMGOMGOMGOMG...', sure, but it's that first bulleted point in my question that cuts the knees out of this approach:

"any column additions or deletions inside SharePoint would not affect my sp's or views"

It's so nice to simply not have to deal with those column names.  To me, having to have a stored procedure that is this linked to your list's bizarre under-structure is the worst kind of coupling.  It's like some kind of binary compatibility on my database structure.

But hey, this has been enlightening.  I think there's still a use for what I'm after.  I think there's a way to use Linq to SharePoint in this way that removes the inelegance of having to code against the SharePoint List database structures.

LVL 60

Accepted Solution

Kevin Cross earned 2000 total points
ID: 36977575
But you are doing this on the server that is part of the SharePoint system. It is already there. i.e., you already coupled to that. That is the proper place to create an interface to the outside world that allows loose coupling elsewhere. Otherwise, all the remote systems doing programming have to know the sharepoint structure and come up with their own abstraction layer. Think of it in code. If you are writing a web app and a client app. Using CLR on remote SQL Server is like using LINQ in both applications. Yes, it is abstraction, but you now have to maintain this twice. Whereas if you have a common layer that both apps communicate with then you benefit. That is the point here. Don't try to get SQL CLR to do something that does not meet good practice anyway. Go to the source. A SharePoint database by definition HAS to know about the SharePoint database structure. If I have to write any code that is specific to that structure, I would much rather do that there. Make the public interface to that say a stored procedure that says give me row for 'John Smith'. The procedure could rationalize that 'John Smith' is '8203F6B3-0CE0-4468-B924-1AACE148DB63' and pass that in remainder of code. The caller will only know the column names passed back. So you add columns, you have the power to show or not show that column and what alias it is shown as.

Anyway, as I said I must be missing what you are after.

You can use the 'Request Attention' and see if the Moderators can bring more Experts in as maybe they will read something differently and offer "fresh" ideas.

Author Closing Comment

ID: 37070300
Close enough.  I'm looking ahead a couple of months anyway.  I'll burn this bridge when I get there.

Believe me, I'm not crazy about the idea of putting Linq to SharePoint inside CLR.  But to me, the point where my "cludge" indicator burns most brightly, is when I think about writing that stored procedure...then one of my SharePoint *users* with enough permissions mojo decides to remove a column or two and leaves my stored proc hitting columns that either don't exist (I doubt that, actually) or (more likely) are empty.

Linq to SharePoint would (should) be able to enumerate the columns in a list.  It would know that those columns are gonzo now.

That's really what I'm after, in a nutshell, if it's attainable.

But listen, your answers have been very valuable, and they actually give me some relief, in that I now could actually write a stored proc that is as bound to the SP data structure as you describe, and not go home and hate myself and start cutting or something.  Thanks.

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how the fundamental information of how to create a table.

800 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question