Solved

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

Posted on 2011-09-20
6
512 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
  • 3
  • 3
6 Comments
 
LVL 59

Expert Comment

by:Kevin Cross
Comment Utility
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
Comment Utility
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 59

Expert Comment

by:Kevin Cross
Comment Utility
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
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 

Author Comment

by:bamapie
Comment Utility
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 59

Accepted Solution

by:
Kevin Cross earned 500 total points
Comment Utility
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
Comment Utility
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

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Join & Write a Comment

We had a requirement to extract data from a SharePoint 2010 Customer List into a CSV file and then place the CSV file into a directory on the network so that the file could be consumed by an AS400 system. I will share in Part 1 how to Extract the Da…
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
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.

743 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now