Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win


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.

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!


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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

609 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