Solved

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

Posted on 2011-09-20
6
531 Views
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.
0
Comment
Question by:bamapie
[X]
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
6 Comments
 
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.

http://social.msdn.microsoft.com/Forums/en-US/linqtosql/thread/8fed8fc5-aee5-43a6-abb8-806382b34195/

Kevin
0
 

Author Comment

by:bamapie
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.
0
 
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.

Kevin
0
The Ultimate Checklist to Optimize Your Website

Websites are getting bigger and complicated by the day. Video, images, custom fonts are all great for showcasing your product/service. But the price to pay in terms of reduced page load times and ultimately, decreased sales, can lead to some difficult decisions about what to cut.

 

Author Comment

by:bamapie
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.

0
 
LVL 60

Accepted Solution

by:
Kevin Cross earned 500 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.
0
 

Author Closing Comment

by:bamapie
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.
0

Featured Post

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

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

I thought I'd write this up for anyone who has a request to create an anonymous whistle-blower-type submission form created using SharePoint 2010 (this would probably work the same for 2013). It's not 100% fool-proof but it's as close as you can get…
A recent project that involved parsing Tableau Desktop and Server log files to extract reusable user queries for use in other systems. I chose to use PowerShell to gather the data, and SharePoint to present it...
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

724 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