tag:blogger.com,1999:blog-55093117017217309652018-03-05T08:15:05.079-08:00SPMetaSharePoint development at a higher level of abstraction.Matt Blodgetthttp://www.blogger.com/profile/06115180196173160813noreply@blogger.comBlogger6125tag:blogger.com,1999:blog-5509311701721730965.post-6256912772645240352014-05-08T13:43:00.000-07:002017-04-02T13:44:07.464-07:00Deploying 3rd-Party Assemblies With Your SharePoint Project<br /><h2>Why would I need to do this?</h2>You have a 3rd-party assembly (DLL) you want to take advantage of in your SharePoint project. You need to make sure that when your WSP is deployed, this 3rd-party assembly goes along for the ride. I'll demonstrate how I accomplish this, by using my <a href="http://spmetanorthwind.codeplex.com/" target="_blank">SPMeta-Northwind</a> project as an example.<br /><br /><br /><h2>Solution Folders</h2>I got into the habit years ago of placing 3rd-party assemblies in a <a href="http://msdn.microsoft.com/en-us/library/haytww03.aspx" target="_blank">solution folder</a>. This is a fine way of ensuring that the assemblies are included with your solution when a member of your team "gets latest" from your source control system of choice (TFS, Git, etc.). If you have multiple projects in your solution that need the 3rd-party assembly, they can all reference the one location in the solution folder.<br /><br />The initial setup of solution folders can be a little tricky, so I'll show step by step how to do it (in Visual Studio 2012). The trick is to create a directory structure on your file system, and then mirror it with solution folders in Visual Studio.<br /><br />In Windows Explorer, browse to the directory where the solution exists. We're going to manually create a directory called <i>SolutionItems</i>.<br /><br /><div class="separator" style="clear: both; text-align: center;"><a href="http://i.imgur.com/5IZJ6Gs.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="http://i.imgur.com/5IZJ6Gs.png" /></a></div><br />Drill into that directory and create another directory called <i>Assemblies</i>.<br /><br /><div class="separator" style="clear: both; text-align: center;"><a href="http://i.imgur.com/vEflyh2.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="http://i.imgur.com/vEflyh2.png" /></a></div><br />Since SPMeta-Northwind relies on <a href="http://camlex.codeplex.com/" target="_blank">Camlex.NET</a>, I'm going to place the <i>Camlex.NET.dll</i> assembly here.<br /><br /><div class="separator" style="clear: both; text-align: center;"><a href="http://i.imgur.com/mljIt52.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="http://i.imgur.com/mljIt52.png" /></a></div><br />Right-click on the solution, and choose <i>Add</i> -&gt; <i>New Solution Folder</i>.<br /><br /><div class="separator" style="clear: both; text-align: center;"><a href="http://i.imgur.com/tTwX9QO.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="422" src="http://i.imgur.com/tTwX9QO.png" width="640" /></a></div><br />Name the solution folder <i>SolutionItems</i>.<br /><br /><div class="separator" style="clear: both; text-align: center;"><a href="http://i.imgur.com/4MdPC1g.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="http://i.imgur.com/4MdPC1g.png" /></a></div><br />Right-click on <i>SolutionItems</i> and again choose <i>Add</i> -&gt; <i>New Solution Folder</i>.<br /><br /><div class="separator" style="clear: both; text-align: center;"><a href="http://i.imgur.com/EPtxR0c.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="304" src="http://i.imgur.com/EPtxR0c.png" width="640" /></a></div><br />Name this solution folder <i>Assemblies</i>.<br /><br /><div class="separator" style="clear: both; text-align: center;"><a href="http://i.imgur.com/hNZf3mv.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="http://i.imgur.com/hNZf3mv.png" /></a></div><br />Right-click on <i>Assemblies</i> and choose <i>Add</i> -&gt; <i>Existing Item</i>.<br /><br /><div class="separator" style="clear: both; text-align: center;"><a href="http://i.imgur.com/UkUDHQo.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="323" src="http://i.imgur.com/UkUDHQo.png" width="640" /></a></div><br />Browse to the <i>Assemblies</i> directory where we placed the <i>Camlex.NET.dll</i> assembly earlier, and add the file.<br /><br /><div class="separator" style="clear: both; text-align: center;"><a href="http://i.imgur.com/udSfq4N.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="323" src="http://i.imgur.com/udSfq4N.png" width="640" /></a></div><br /><br /><h2>Package.package</h2>The next step is to update the <i>Package.package</i> file in each of your projects that depend on the 3rd-party assembly. Since SPMeta-Northwind, at present, only has one project, we just have one package to update.<br /><br />Expand the <i>Package</i> folder, and double-click on <i>Package.package</i>.<br /><br />Switch to the <i>Advanced</i> tab, and click on the <i>Add</i> button to the right, and choose <i>Add Existing Assembly</i>.<br /><br />In the <i>Add Existing Assembly</i> dialog, for the <i>Source Path</i>, browse to the location of the <i>Camlex.NET.dll</i> file. Click <i>OK</i> to close the dialog.<br /><br /><div class="separator" style="clear: both; text-align: center;"><a href="http://i.imgur.com/VDR0rsw.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="257" src="http://i.imgur.com/VDR0rsw.png" width="640" /></a></div><br />Now the Camlex.NET assembly is included in our package and will be deployed to the <a href="http://msdn.microsoft.com/en-us/library/yf1d93sz(v=vs.110).aspx" target="_blank">GAC</a> when our WSP is deployed.<br /><br /><div class="separator" style="clear: both; text-align: center;"><a href="http://i.imgur.com/NMaD3HB.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="122" src="http://i.imgur.com/NMaD3HB.png" width="640" /></a></div><br /><br /><h2>References</h2>The last step is to add a reference to the assembly in each project that needs it.<br /><br />Under the <i>Northwind.Common</i> project, right-click on <i>References</i> and choose <i>Add Reference</i>. In the dialog, click <i>Browse...</i>, locate the <i>Camlex.NET.dll </i>file in our <i>Assemblies</i> folder, and click <i>Add</i>.<br /><br /><div class="separator" style="clear: both; text-align: center;"><a href="http://i.imgur.com/zeRTokc.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="328" src="http://i.imgur.com/zeRTokc.png" width="640" /></a></div><br />Click <i>OK</i> to close the dialog.<br /><br />Now the project has a reference to the assembly, and you're ready to go.<br /><br /><div class="separator" style="clear: both; text-align: center;"><a href="http://i.imgur.com/IcLnnyL.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="http://i.imgur.com/IcLnnyL.png" /></a></div><br /><br /><h2>Closing</h2>I've found in practice that this is a great way to manage 3rd-party assemblies. You ensure that every developer on your team will get the same file in the same place when they get latest from source control. You ensure that all the projects that reference the assembly have resilient relative paths that are guaranteed not to break across machines. And you ensure that when you build a WSP from your project, it will contain the assembly, and the assembly will be deployed to the GAC when the WSP is deployed.<br /><br />Matt Blodgetthttp://www.blogger.com/profile/06115180196173160813noreply@blogger.com3tag:blogger.com,1999:blog-5509311701721730965.post-55241352488783671502014-04-16T19:11:00.000-07:002017-04-02T13:30:17.552-07:00Clean URLs for Your ASPX Pages with a Document Library and Module<br /><h2>Intro</h2>Which URL is nicer? This one:<br /><br /><code style="padding-left: 25px;">/_layouts/northwind/hr/Default.aspx</code><br /><br />Or this one:<br /><br /><code style="padding-left: 25px;">/hr/Default.aspx</code><br /><code><br /></code>If you're like me, you notice the friendliness of URLs in web applications. I picked up a sense of URL friendliness during my time in the <a href="http://rubyonrails.org/" target="_blank">Ruby on Rails</a> community, where they have a predilection for short, semantic URLs.<br /><br />I'm going to discuss a technique for organizing and deploying custom ASPX pages to your SharePoint environment that will leave you with nicer URLs.<br /><div><br /><br /><h2>Deploying Pages to /_layouts/</h2></div><div><div>The first URL listed above indicates a page that was deployed to the "layouts" folder in the SharePoint "<a href="http://habaneroconsulting.com/insights/The-SharePoint-Hive" target="_blank">hive</a>". I'm going to assume that all SharePoint developers are familiar with this process. If you put a page in the "layouts" folder, it's going to be addressed with a URL starting with <code>/_layouts/</code>.</div><div><br /></div><div>If you want to be a good citizen of your SharePoint farm, you're going to make a subdirectory for the files related to your application to live to keep things tidy. Using my <a href="http://spmetanorthwind.codeplex.com/" target="_blank">SPMeta-Northwind</a> project as a reference, it would probably make sense to call this subdirectory something like "northwind".</div><div><br /></div><div>Let's say that Northwind has some custom pages that are related to the Human Resources (HR) department of the company. Again, for purposes of tidiness, we'll group these pages together in a subdirectory called "hr".</div><div><br /></div><div>So all of our HR-related pages will get a URL that starts with <code>/_layouts/northwind/hr/</code>.</div><div><br /></div><div>We can do better!<br /><br /><br /><h2>Deploying Pages to a Document Library</h2></div></div><div><div>Here's another way to deploy our HR-related pages that will result in nicer URLs. It involves a document library and a <a href="http://msdn.microsoft.com/en-us/library/ee231567.aspx" target="_blank">module</a>.</div><div><br /></div><div>The SPMeta-Northwind project has a <a href="http://spmetanorthwind.codeplex.com/SourceControl/latest#Northwind.Common/Lists/" target="_blank">"Lists" folder</a>. Let's add a document library list instance to it called "HR Pages".</div></div><div><br /></div><div class="separator" style="clear: both; text-align: center;"><a href="http://i.imgur.com/I8gmx8X.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="219" src="http://i.imgur.com/I8gmx8X.png" width="320" /></a></div><div class="separator" style="clear: both; text-align: center;"><br /></div><div class="separator" style="clear: both; text-align: left;">Under the Northwind.Common project, we'll add a module called "HRPages". Now let's add a few pages to the module. Note: If you try to add a page to the module by choosing the "Application Page" item, Visual Studio freaks out and adds files completely outside of the module. I always add a "Text file" and manually change the extension to ASPX. After adding our pages, there are some tweaks to make to the module's Elements.xml.</div><div><br /></div><div class="separator" style="clear: both; text-align: center;"><a href="http://i.imgur.com/5VR5mzd.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="112" src="http://i.imgur.com/5VR5mzd.png" width="320" /></a></div><div class="separator" style="clear: both; text-align: center;"><br /></div><div class="separator" style="clear: both; text-align: left;">Let's also go ahead and take a preemptive measure to ensure that if we add new pages to the module in the future, they'll be pushed out during <a href="http://www.spmeta.net/2014/03/a-model-for-upgradeable-sharepoint-sites.html" target="_blank">feature upgrading</a>. Specifically, we need to reference our module inside an <code><a href="http://msdn.microsoft.com/en-us/library/office/ff595313.aspx" target="_blank">ApplyElementManifests</a></code> tag.</div><div><br /></div><div class="separator" style="clear: both; text-align: center;"><a href="http://i.imgur.com/PsLyZuD.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="237" src="http://i.imgur.com/PsLyZuD.png" width="320" /></a></div><div><br /></div><div>After activating/upgrading our feature, I'll navigate to <code>/hr</code> in a browser. ASP.NET will look for a Default.aspx file, and since we deployed one, that's where we'll end up.<br /><br /><div class="separator" style="clear: both; text-align: center;"><a href="http://i.imgur.com/k0FdbOI.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="218" src="http://i.imgur.com/k0FdbOI.png" width="320" /></a></div><br />If you go with the document library + module approach for your custom pages, a nice bonus you get is that you can easily hook into <span id="goog_1327601680"></span>SharePoint's <a href="http://office.microsoft.com/en-us/sharepoint-server-help/edit-permissions-for-a-list-library-or-individual-item-HA101805400.aspx" target="_blank">item-level security</a><span id="goog_1327601681"></span> to secure your pages. Since each page is now an item in a list, you can apply permissions to them just like any other list item in SharePoint. Specify which groups/users have read permissions on the list item, and SharePoint will give its standard "access denied" message to unauthorized users that try to navigate to the corresponding page.<br /><br /><br /><h2>Closing</h2></div><div>I prefer the document library + module approach for deploying custom ASPX pages. It's a great way to get clean, short URLs. And you get SharePoint's built-in security enforcement for free!</div><div><br /></div>Matt Blodgetthttp://www.blogger.com/profile/06115180196173160813noreply@blogger.com0tag:blogger.com,1999:blog-5509311701721730965.post-44239562329379782352014-03-27T13:03:00.000-07:002017-04-02T13:24:43.614-07:00Taming CAML<div class="separator" style="clear: both; text-align: center;"><br /></div><div class="separator" style="clear: both; text-align: center;"><a href="http://i.imgur.com/s4EtAMU.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="265" src="http://i.imgur.com/s4EtAMU.jpg" width="400" /></a></div><div class="separator" style="clear: both; text-align: center;"><br /></div>As a SharePoint developer, <a href="http://msdn.microsoft.com/en-us/library/office/ms467521.aspx" target="_blank">CAML</a> is a fact of life. It can be a pain to deal with, but there are ways to make your life easier. I'm going to discuss a few ways of constructing CAML queries. Some are better than others.<br /><div><br /><br /></div><h2>Producing a String of CAML</h2><div>There are many ways to produce a string of CAML that you can include in your codebase. You can write it by hand or use a tool to spit out a string of CAML that you can copy/paste.</div><div><br /><br /></div><h3>Writing By Hand</h3><div>I recommend against writing CAML by hand. As it's based on XML, which is not exactly the basis for a succinct query language, strings of CAML get long and unwieldy. I mean, this is gross, right? Who wants to write this by hand?</div><div><br /></div><div><pre class="prettyprint">activeProductsByCategory.Query = "&lt;GroupBy Collapse='FALSE' GroupLimit='30'&gt;&lt;FieldRef Name='Category' /&gt;&lt;/GroupBy&gt;&lt;OrderBy&gt;&lt;FieldRef Name='Title' /&gt;&lt;/OrderBy&gt;&lt;Where&gt;&lt;Eq&gt;&lt;FieldRef Name='Discontinued' /&gt;&lt;Value Type='Boolean'&gt;0&lt;/Value&gt;&lt;/Eq&gt;&lt;/Where&gt;";<br /></pre></div><div><br /></div><div>We can do better.</div><div><br /><br /></div><h3>U2U CAML Query Builder</h3><div>There are several free third-party CAML builder tools that allow you to interactively construct queries via a GUI designer. <a href="http://www.u2u.be/Software" target="_blank">U2U's tool</a> is probably the most well known as it's been around the longest.</div><div><br /><br /></div><div class="separator" style="clear: both; text-align: center;"><a href="http://i.imgur.com/y2sckBb.png" imageanchor="1"><img border="0" height="189" src="http://i.imgur.com/y2sckBb.png" width="320" /></a></div><div class="separator" style="clear: both; text-align: center;"><br /></div><div class="separator" style="clear: both; text-align: center;"><br /></div><div class="separator" style="clear: both;">Tools like these are certainly much better than hand-rolling CAML queries. I'll break out U2U CAML Query Builder once in a while for quick, simple scenarios, but there's a much more comprehensive interactive CAML builder that I turn to time and time again. And this one's made by Microsoft themselves.</div><div><br /><br /></div><h3>SharePoint's Web Form</h3><div><div>It's easy to forget that the vast majority of the things we do as SharePoint developers are also possible by end users via SharePoint's built-in web forms. There's no shame in prototyping your ideas via SharePoint's own screens, and then reproducing the prototype in code. I do this often.</div></div><div><br /></div><div>Let's say we need a new view to be added to a list. As a developer, for repeatability's sake, we want to create this view <a href="http://msdn.microsoft.com/en-us/library/ms475170.aspx" target="_blank">via code</a> in a feature. But we can lean on SharePoint's out-of-the-box view creation screen to write the CAML query for us.</div><div><br /><br /></div><div class="separator" style="clear: both; text-align: center;"><a href="http://i.imgur.com/tX8GtuA.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="239" src="http://i.imgur.com/tX8GtuA.png" width="320" /></a></div><div class="separator" style="clear: both; text-align: center;"><br /></div><div class="separator" style="clear: both; text-align: center;"><br /></div><div class="separator" style="clear: both; text-align: left;">Once we've designed the view, we can fire up <a href="https://spm.codeplex.com/" target="_blank">SharePoint Manager</a> (or the SharePoint Management Shell) and copy the CAML that resulted.</div><div class="separator" style="clear: both; text-align: left;"><br /></div><div class="separator" style="clear: both; text-align: left;"><br /></div><div class="separator" style="clear: both; text-align: center;"><a href="http://i.imgur.com/Ka2otyi.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="192" src="http://i.imgur.com/Ka2otyi.png" width="320" /></a></div><div class="separator" style="clear: both; text-align: left;"><br /></div><div class="separator" style="clear: both; text-align: left;"><br /></div><div>I think this is the best way to produce a string of CAML that you can paste into your code, as you have the full power of Microsoft's own view design interface.</div><div><br /><br /></div><h2>No CAML</h2><div><br /></div><div>What if you tired of having unsightly blobs of CAML littered throughout your codebase? Outside of the SharePoint world, CAML is replaced by SQL. And for years people have avoided having blobs of SQL in their codebases by using tools that generate SQL dynamically. Why not use a tool that generates your CAML dynamically?</div><div><br /><br /></div><h3>LINQ to SharePoint</h3><div><div>I touched on <a href="http://msdn.microsoft.com/en-us/library/office/ee538250(v=office.14).aspx" target="_blank">LINQ to SharePoint</a> in a <a href="http://www.spmeta.net/2014/02/sharepoint-as-relational-database.html" target="_blank">previous post</a>. Just as <a href="http://weblogs.asp.net/scottgu/archive/2007/05/19/using-linq-to-sql-part-1.aspx" target="_blank">LINQ to SQL</a> takes a "snapshot" of your database schema and generates .NET classes that map to it, LINQ to SharePoint takes a snapshot of the lists in a site and generates .NET classes against it.</div><div><br /></div><div>LINQ to SharePoint was very exciting to me when I first heard about it, as I was familiar with tools like LINQ to SQL and how convenient they made querying for data from code. Unfortunately, that excitement faded as I tried to press LINQ to SharePoint into action in a real-world application.</div><div><br /></div><div>I'll invite you to read <a href="http://blog.furuknap.net/why-linq-to-sharepoint-is-a-bad-idea" target="_blank">Bjørn Furuknap's post</a> (especially "The Ugly" section), as he does a pretty good job of running down the practical concerns of using LINQ to SharePoint. For projects I've worked on, the biggest problem is that LINQ to SharePoint is just too static for a dynamic environment like SharePoint. It's too easy for the generated code to go out of sync with the reality of a particular site. I've always worked on SharePoint projects in teams of multiple developers, and when we've tried to use LINQ to SharePoint, we ran into constant problems with the sites in our local development machines going out of sync with the LINQ to SharePoint model and causing blow-ups at runtime. This situation is compounded when you have different developers working on different SharePoint features that make list changes and that may or may not be active on any given site.</div><div><br /></div><div>LINQ to SharePoint may be great for some scenarios, but from my own personal experience, it's never been a practical choice.</div></div><div><br /><br /></div><h3>Camlex.NET</h3><div><div>My current favorite is a fantastic open source project called <a href="https://camlex.codeplex.com/" target="_blank">Camlex.NET</a>. This project hits a sweet spot for me in that it alleviates the need to have large CAML strings in my codebase, while still being dynamic.</div><div><br /></div><div>Here I'm setting up the query for a view on the Products list from <a href="https://spmetanorthwind.codeplex.com/SourceControl/latest#Northwind.Common/Lists/Products.cs" target="_blank">SPMeta-Northwind</a> called "Active Products By Category":</div><div><br /></div><div><pre class="prettyprint">activeProductsByCategory.Query = "&lt;GroupBy Collapse='FALSE' GroupLimit='30'&gt;&lt;FieldRef Name='Category' /&gt;&lt;/GroupBy&gt;&lt;OrderBy&gt;&lt;FieldRef Name='Title' /&gt;&lt;/OrderBy&gt;&lt;Where&gt;&lt;Eq&gt;&lt;FieldRef Name='Discontinued' /&gt;&lt;Value Type='Boolean'&gt;0&lt;/Value&gt;&lt;/Eq&gt;&lt;/Where&gt;";<br /></pre></div><div><br /></div><div>And here I'm setting up the same query with Camlex.NET:</div><div><br /></div><div><pre class="prettyprint">activeProductsByCategory.Query = Camlex.Query().Where(x =&gt; (bool)x["Discontinued"] == false).OrderBy(x =&gt; x["Title"]).GroupBy(x =&gt; x["Category"], false, 30).ToString();<br /></pre></div><div><br /></div><div>Although, to my eye, the Camlex.NET version here is much nicer, this is a pretty basic example of its power. Imagine situations where you're dynamically building up a <a href="http://msdn.microsoft.com/en-us/library/office/ms414805.aspx" target="_blank">WHERE</a> clause containing an arbitrary number of <a href="http://msdn.microsoft.com/en-us/library/office/ms196939.aspx" target="_blank">AND</a>s/<a href="http://msdn.microsoft.com/en-us/library/office/ms472196.aspx" target="_blank">OR</a>s based on variable conditions. It's times like that when you're concatenating large strings of CAML and playing <a href="http://msdn.microsoft.com/en-us/library/system.string.format.aspx" target="_blank">String.Format()</a> bingo that you really see the power of a tool like Camlex.NET.</div><div><br /></div><div>As a bonus, the authors of Camlex.NET have a <a href="http://camlex-online.org/" target="_blank">website</a> where you can paste in CAML strings and see how you'd build their equivalents with Camlex.NET. It's pretty handy when you're first learning the tool.</div><div><br /><br /></div><div class="separator" style="clear: both; text-align: center;"><a href="http://i.imgur.com/QsUIOaX.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="222" src="http://i.imgur.com/QsUIOaX.png" width="320" /></a></div><div><br /><br /></div></div><h2>Closing</h2><div><div>As a SharePoint developer, you're going to need CAML queries. You can either go the path of having strings of CAML in your codebase, or having a tool dynamically generate CAML.</div><div><br /></div><div>If you don't mind having blobs of CAML intermingled with your C#/VB code, then I recommend using Microsoft's own add/edit view form to build your CAML.</div><div><br /></div><div>If you're like me, and you prefer to have your CAML built dynamically, then I recommend the amazing Camlex.NET project, which I believe is a more practical choice over LINQ to SharePoint.</div></div>Matt Blodgetthttp://www.blogger.com/profile/06115180196173160813noreply@blogger.com1tag:blogger.com,1999:blog-5509311701721730965.post-30490760590743164782014-03-13T18:19:00.000-07:002017-04-02T13:16:45.912-07:00A Model for Upgradeable SharePoint Sites<br /><h2>Intro</h2><div><br /></div><div><div>It's not necessarily obvious how to handle changes to a deployed SharePoint-based application. I'm going to lay out a proven system for upgradeable SharePoint sites that I've successfully used on multiple projects.</div><div><br /></div><div>To illustrate some of my points, I'm going to reference an open source project I created called <a href="https://spmetanorthwind.codeplex.com"><span id="goog_1820941100"></span>SPMeta-Northwind<span id="goog_1820941101"></span></a>. It's a reimagining of Microsoft's classic <a href="https://northwinddatabase.codeplex.com/" target="_blank">Northwind database schema</a> as a SharePoint <a href="http://msdn.microsoft.com/en-us/library/ff798425.aspx" target="_blank">farm solution</a>. I developed SPMeta-Northwind for SharePoint 2010 in Visual Studio 2012. If you're using SharePoint 2013 or a different version of Visual Studio, I believe the general concepts will still apply.</div></div><div><br /></div><div><br /></div><h2>Feature Upgrading</h2><div><br /></div><div>SharePoint 2010 introduced the concept of <a href="http://msdn.microsoft.com/en-us/library/office/aa544511(v=office.14).aspx" target="_blank">feature upgrading</a>. This capability provides a great way forward for pushing changes out to existing SharePoint sites. My <a href="https://spmetanorthwind.codeplex.com/SourceControl/latest" target="_blank">Northwind solution</a> (pictured below) contains one upgradeable feature, called Northwind.Common.</div><div><br /></div><div class="separator" style="clear: both; text-align: center;"><a href="http://i.imgur.com/Z4mdXsp.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="320" src="http://i.imgur.com/Z4mdXsp.png" width="228" /></a></div><div><br /></div><h3>Setting up the feature properties</h3><div>There are a few settings we'll need to configure in the Properties window for the Northwind.Common feature.</div><div><br /></div><div class="separator" style="clear: both; text-align: center;"><a href="http://i.imgur.com/DKT3Cg1.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="176" src="http://i.imgur.com/DKT3Cg1.png" width="320" /></a></div><div><br /></div><div><div>We need to configure which assembly ('Upgrade Actions Receiver Assembly') and class ('Upgrade Actions Receiver Class') will be invoked when the feature is upgraded. We'll set it to use the same assembly and class that are used on feature activation.</div><div><br /></div><div>Decide on a beginning version number for the feature. I tend to go with '1.0.0.1'.</div></div><div><br /></div><h3>Setting up the feature manifest</h3><div>To make the feature upgrade-aware, we'll need to make a small edit to the manifest.</div><div><br /></div><div class="separator" style="clear: both; text-align: center;"><a href="http://i.imgur.com/4HVt57m.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="230" src="http://i.imgur.com/4HVt57m.png" width="320" /></a></div><div><br /></div><div><div>Add an 'UpgradeActions' element with a 'CustomUpgradeAction' element inside it. For our purposes, the 'Name' attribute really doesn't matter--I usually just use 'Upgrade'.</div><div><br /></div><div>There is another edit you may want to make to the feature manifest if you're using <a href="http://msdn.microsoft.com/en-us/library/ee231524(v=vs.110).aspx" target="_blank">modules</a>, to ensure that new files added to the module get deployed on upgrade, but I'll cover that topic in a future post.</div></div><div><br /></div><h3>Deploying feature upgrades</h3><div>My Northwind solution includes some <a href="http://msdn.microsoft.com/en-us/library/office/ee536539(v=office.14).aspx" target="_blank">PowerShell</a> scripts for deploying upgrades. I can go over them in depth in a future post, but for the purposes of this post, I'll just point out a couple lines from <a href="https://spmetanorthwind.codeplex.com/SourceControl/latest#SolutionItems/Scripts/FeatureUtils.ps1" target="_blank">FeatureUtils.ps1</a> directly related to feature upgrading.</div><div><br /></div><pre class="prettyprint">if ($feature.Version.CompareTo($feature.Definition.Version) -lt 0)<br />{<br /> # ...snip...<br /><br /> $exceptions = $feature.Upgrade($false)<br /> <br /> # ...snip...<br />}<br /></pre><div><br />In code, you can compare the version of an activated feature against the version of its installed feature definition to tell if it needs to be upgraded. If so, you can call <a href="http://msdn.microsoft.com/en-us/library/microsoft.sharepoint.spfeature.upgrade(v=office.14).aspx" target="_blank">SPFeature.Upgrade()</a> to bring the feature up to date. <br /><br /><br /><h2>Idempotent Feature Receivers</h2></div><div><br /></div><div><a href="http://msdn.microsoft.com/en-in/library/ms469501(v=office.14).aspx" target="_blank">Feature receivers</a> represent a powerful hook for running code against your SharePoint environment. My philosophy around feature receivers includes the concept of <a href="http://en.wikipedia.org/wiki/Idempotence#Computer_science_meaning" target="_blank">idempotence</a>.</div><div><br /></div><h3>Not relying on version numbers</h3><div>When my journey with SharePoint 2010 and its upgradeable feature concept began, I quickly learned that maintaining different code paths for specific feature versions was unsustainable. Since features are aware of which specific version number they had at the time of their last upgrade, you could be tempted to write conditional logic in your feature receiver that checks that version number and takes divergent paths based on it. Not only do I discourage you from doing this sort of thing, I discourage you from distinguishing between feature <i>activation </i>and feature <i>upgrading</i>.</div><div><br /></div><h3>FeatureActivated and FeatureUpgrading identical</h3><div>Let's take a look at the <a href="https://spmetanorthwind.codeplex.com/SourceControl/latest#Northwind.Common/Features/Northwind.Common/Northwind.EventReceiver.cs" target="_blank">Northwind.Common feature receiver</a>.</div><div><br /></div><pre class="prettyprint">public class NorthwindCommonEventReceiver : SPFeatureReceiver<br />{<br /> // ...snip...<br /><br /> public override void FeatureActivated(SPFeatureReceiverProperties properties)<br /> {<br /> SPSite site = properties.Feature.Parent as SPSite;<br /><br /> Ensure(site.RootWeb);<br /> }<br /><br /> public override void FeatureUpgrading(SPFeatureReceiverProperties properties, string upgradeActionName, IDictionary&lt;string, string&gt; parameters)<br /> {<br /> SPSite site = properties.Feature.Parent as SPSite;<br /><br /> Ensure(site.RootWeb);<br /> }<br /><br /> // ...snip...<br />}<br /></string></pre><div><br />The contents of <a href="http://msdn.microsoft.com/en-us/library/microsoft.sharepoint.spfeaturereceiver.featureactivated(v=office.14).aspx" target="_blank">FeatureActivated</a> and <a href="http://msdn.microsoft.com/en-us/library/microsoft.sharepoint.spfeaturereceiver.featureupgrading(v=office.14).aspx" target="_blank">FeatureUpgrading</a> are identical. I design my feature receivers to not only ignore which version they're upgrading from, but to not even care whether they're upgrading or activating. The resulting state will be the same regardless. This approach requires thinking about changes to your SharePoint environment as being "ensured." <br /><br /><br /><h2>"Ensuring"</h2></div><div><br /></div><div>The term I use to indicate idempotence in my code is "ensure." I was inspired to use this term by the SharePoint API's own <a href="http://msdn.microsoft.com/en-us/library/microsoft.sharepoint.spweb.ensureuser(v=office.14).aspx" target="_blank">SPWeb.EnsureUser()</a>. If you're not familiar with this method, it simply does what it needs to do internally to make sure that a user with a particular login name exists and returns the user. It doesn't matter if you're calling it for the first time or the fiftieth time--the result is the same.</div><div><br /></div><h3>IEnsurableList</h3><div><div>As I discussed in my <a href="http://www.spmeta.net/2014/02/sharepoint-as-relational-database.html" target="_blank">last post</a>, one of the ways I conceive of SharePoint is as a relational database system. And one of the most common upgrades I find myself doing is making "schema" changes, i.e., structural changes to SharePoint lists. This includes adding fields and views to lists and changing their various properties.</div><div><br /></div><div>My approach to "ensuring" lists and their later schema changes is by maintaining one .NET class per list. Microsoft's famous Northwind database schema has 13 tables, and my Northwind solution has 11 "list classes" as I call them (I was able to eliminate 2 tables via SharePoint's unique functionality).</div></div><div><br /></div><table align="center" cellpadding="0" cellspacing="0" class="tr-caption-container" style="margin-left: auto; margin-right: auto; text-align: center;"><tbody><tr><td style="text-align: center;"><a href="http://i.imgur.com/1QhyaHJ.png" imageanchor="1" style="margin-left: auto; margin-right: auto;"><img border="0" height="160" src="http://i.imgur.com/1QhyaHJ.png" width="320" /></a></td></tr><tr><td class="tr-caption" style="text-align: center;">Microsoft's <i>Northwind </i>database schema</td></tr></tbody></table><br /><table align="center" cellpadding="0" cellspacing="0" class="tr-caption-container" style="margin-left: auto; margin-right: auto; text-align: center;"><tbody><tr><td style="text-align: center;"><a href="http://i.imgur.com/2qqcfXA.png" imageanchor="1" style="margin-left: auto; margin-right: auto;"><img border="0" height="320" src="http://i.imgur.com/2qqcfXA.png" width="228" /></a></td></tr><tr><td class="tr-caption" style="text-align: center;">One "list class" per SharePoint list</td></tr></tbody></table><div><br /></div><div>What all lists have in common is that they can be ensured (FeatureActivated &amp; FeatureUpgrading) or torn down (FeatureDeactivating). I express this commonality via a C# interface called <a href="https://spmetanorthwind.codeplex.com/SourceControl/latest#Northwind.Common/Lists/Base/IEnsurableList.cs" target="_blank">IEnsurableList</a>.</div><div><br /></div><pre class="prettyprint">namespace Northwind.Common.Lists.Base<br />{<br /> public interface IEnsurableList<br /> {<br /> void Ensure(SPWeb web);<br /> void TearDown(SPWeb web);<br /> }<br />}<br /></pre><div><br />All of my list classes implement this interface. This allows each list to be treated identically by my <a href="https://spmetanorthwind.codeplex.com/SourceControl/latest#Northwind.Common/Lists/Base/ListManager.cs" target="_blank">ListManager</a> class.<br /><br /><h3>ListManager</h3></div><div>The ListManager takes a collection of IEnsurableLists and knows how to ensure them and tear them down in the proper order.</div><div><br /></div><pre class="prettyprint">public class NorthwindCommonEventReceiver : SPFeatureReceiver<br />{<br /> private readonly ListManager listManager = new ListManager(new List&lt;IEnsurableList&gt;<br /> {<br /> new CustomerDemographics(),<br /> new Customers(),<br /> new Employees(),<br /> new Shippers(),<br /> new Orders(),<br /> new Suppliers(),<br /> new Categories(),<br /> new Products(),<br /> new Regions(),<br /> new Territories(),<br /> new OrderDetails()<br /> });<br /><br /> // ...snip...<br />}<br /></pre><div><br />I like to declare a ListManager instance right at the top of my feature receiver, so that you can see clearly which lists the feature ensures and in what order.<br /><br /><h3>SPFieldCollection.Ensure&lt;T&gt;() </h3></div><div>The Northwind solution contains several <a href="http://msdn.microsoft.com/en-us/library/bb383977(v=vs.110).aspx" target="_blank">extension methods</a> that augment the SharePoint API with "ensure" behavior. I'll talk about the most important one, <a href="https://spmetanorthwind.codeplex.com/SourceControl/latest#Northwind.Common/Extensions/SPFieldCollectionExtensions.cs" target="_blank">SPFieldCollection.Ensure&lt;T&gt;()</a>.</div><div><br /></div><pre class="prettyprint">public static T Ensure&lt;T&gt;(this SPFieldCollection fields, string fieldDisplayName) where T : SPField<br />{<br /> if (typeof(T) == typeof(SPFieldLookup))<br /> {<br /> throw new ArgumentException("Lookups are not supported");<br /> }<br /><br /> SPFieldType fieldType = SharePointHelper.GetFieldType&lt;T&gt;();<br /><br /> if (fields.ContainsField(fieldDisplayName))<br /> {<br /> SPField field = fields.GetField(fieldDisplayName);<br /><br /> if (field.Type == fieldType)<br /> {<br /> return field as T;<br /> }<br /><br /> field.Delete();<br /> }<br /><br /> fields.Add(fieldDisplayName, fieldType, false);<br /><br /> return fields.GetField(fieldDisplayName) as T;<br />}<br /></pre><div><br />Ensuring fields is so common that I tried to distill the process into its tersest form, reducing noise in the code, and maintaining maximum readability. I'll get more into depth about the design decisions behind my various extension methods in a later post, but for now, I just want to show how the concept of "ensuring" applies to fields. Below is a snippet from <a href="https://spmetanorthwind.codeplex.com/SourceControl/latest#Northwind.Common/Lists/Employees.cs" target="_blank">Employees.cs</a>, where I'm ensuring the fields for the Employees list.<br /><br /></div><pre class="prettyprint">private void EnsureFields(SPList list)<br />{<br /> SPFieldText lastName = list.Fields.Ensure&lt;SPFieldText&gt;("Last Name");<br /> lastName.Required = true;<br /> lastName.MaxLength = 20;<br /> lastName.Update();<br /><br /> SPFieldText firstName = list.Fields.Ensure&lt;SPFieldText&gt;("First Name");<br /> firstName.Required = true;<br /> firstName.MaxLength = 10;<br /> firstName.Update();<br /><br /> // ...snip...<br /><br /> SPFieldDateTime birthDate = list.Fields.Ensure&lt;SPFieldDateTime&gt;("Birth Date");<br /> birthDate.DisplayFormat = SPDateTimeFieldFormatType.DateOnly;<br /> birthDate.Update();<br /><br /> // ...snip...<br /><br /> SPFieldUrl photo = list.Fields.Ensure&lt;SPFieldUrl&gt;("Photo");<br /> photo.DisplayFormat = SPUrlFieldFormatType.Image;<br /> photo.Update();<br /><br /> list.Fields.Ensure&lt;SPFieldMultiLineText&gt;("Notes");<br /><br /> // ...snip...<br /><br /> SPFieldCalculated displayName = list.Fields.Ensure&lt;SPFieldCalculated&gt;("Display Name");<br /> displayName.Formula = "=[First Name]&amp;\" \"&amp;[Last Name]";<br /> displayName.Update();<br /><br /> // ...snip...<br />}<br /></pre><div><br /><br /><h2>Closing</h2></div><div><br /></div><div><div>I've tried to provide a clear way forward for upgradeable SharePoint sites. We've looked at how to set up a SharePoint feature to be upgradeable, how to deploy upgrades via a PowerShell script, how to write idempotent feature receivers, and the concept of "ensuring."&nbsp;</div><div><br /></div><div>The SPMeta-Northwind project is a working example of my approach, and if you're a "show me the code" kind of person (like I am), then I hope you'll find it helpful.</div><div><br /></div><div>See you next time.</div></div>Matt Blodgetthttp://www.blogger.com/profile/06115180196173160813noreply@blogger.com0tag:blogger.com,1999:blog-5509311701721730965.post-49679904512272486112014-02-22T11:53:00.000-08:002017-04-02T13:08:32.024-07:00SharePoint as a Relational DatabaseEvery web application has a database, right? If you're developing an ASP.NET application, your first choice is probably SQL Server, Microsoft's relational database product.<br /><br />What if you're building an application on top of SharePoint? If you come from an ASP.NET background, you might reflexively reach for SQL Server and go nuts creating your tables, columns, views, etc.<br /><br />If you're starting fresh, with no existing database to integrate with, I suggest that you consider taking advantage of SharePoint's out-of-the-box capabilities to be your data store. There are some serious advantages to this approach that I'll get into later. You may not need (or want) to reach for SQL Server after all.<br /><br /><br /><h2>If You Squint Hard Enough</h2><div><br /></div><div><div>SharePoint can start to take on the look of a web-based <a href="http://en.wikipedia.org/wiki/SQL_Server_Management_Studio">SQL Server Management Studio</a> if you cock your head at a certain angle. I'm going to reference the venerable <a href="http://northwinddatabase.codeplex.com/" target="_blank"><i>Northwind</i></a> database to illustrate the similarities between SharePoint and a relational database like SQL Server.&nbsp;</div><div><br /></div><table align="center" cellpadding="0" cellspacing="0" class="tr-caption-container" style="margin-left: auto; margin-right: auto; text-align: center;"><tbody><tr><td style="text-align: center;"><a href="http://i.imgur.com/YGJxLZ2.png" imageanchor="1" style="margin-left: auto; margin-right: auto;"><img border="0" height="320" src="http://i.imgur.com/YGJxLZ2.png" width="640" /></a></td></tr><tr><td class="tr-caption" style="text-align: center;">The world famous <i>Northwind</i> database</td></tr></tbody></table><div><br /><br /></div><div><h3>Table ⇔ List</h3></div></div><div>We need a Customers table? How about a Customers <i>list</i>?<br /><br /><table align="center" cellpadding="0" cellspacing="0" class="tr-caption-container" style="margin-left: auto; margin-right: auto; text-align: center;"><tbody><tr><td style="text-align: center;"><a href="http://i.imgur.com/TVKaJVN.png" imageanchor="1" style="margin-left: auto; margin-right: auto;"><img border="0" height="120" src="http://i.imgur.com/TVKaJVN.png" width="640" /></a></td></tr><tr><td class="tr-caption" style="text-align: center;">The Customers table becomes the Customers <i>list</i></td></tr></tbody></table><br /><br /></div><div><h3>Column ⇔ Column/Field</h3></div><div>SharePoint lists have columns (called "fields" in the <a href="http://msdn.microsoft.com/en-us/library/microsoft.sharepoint.splist.fields.aspx" target="_blank">API</a>) just like database tables have columns. The Customers table in the Northwind database has a ContactName column with a data type of nvarchar(30). Let's add a column to our Customers list. To fit the requirements, we'll specify that the column is a "Single line of text" and the maximum number of characters is 30.<br /><br /><table align="center" cellpadding="0" cellspacing="0" class="tr-caption-container" style="margin-left: auto; margin-right: auto; text-align: center;"><tbody><tr><td style="text-align: center;"><a href="http://i.imgur.com/8MJcmKH.png" imageanchor="1" style="margin-left: auto; margin-right: auto;"><img border="0" height="218" src="http://i.imgur.com/8MJcmKH.png" width="640" /></a></td></tr><tr><td class="tr-caption" style="text-align: center;">Adding the Contact Name column to the Customers list</td></tr></tbody></table><br /><table align="center" cellpadding="0" cellspacing="0" class="tr-caption-container" style="margin-left: auto; margin-right: auto; text-align: center;"><tbody><tr><td style="text-align: center;"><a href="http://i.imgur.com/60NDSp4.png" imageanchor="1" style="margin-left: auto; margin-right: auto;"><img border="0" height="48" src="http://i.imgur.com/60NDSp4.png" width="400" /></a></td></tr><tr><td class="tr-caption" style="text-align: center;">Limit the number of characters in the column just like an <i>nvarchar </i>column in SQL Server</td></tr></tbody></table><br />SharePoint columns can accommodate any data you could throw at them. I'll discuss the different data types in more detail in a future post.<br /><br />You often want a primary key column on your table that auto-increments, right? SharePoint automatically includes a column in every list called ID, that does exactly that. I'll add the ID column to my <i>view </i>(more on those in a minute).<br /><br /><table align="center" cellpadding="0" cellspacing="0" class="tr-caption-container" style="margin-left: auto; margin-right: auto; text-align: center;"><tbody><tr><td style="text-align: center;"><a href="http://i.imgur.com/BOlUKvu.png" imageanchor="1" style="margin-left: auto; margin-right: auto;"><img border="0" src="http://i.imgur.com/BOlUKvu.png" /></a></td></tr><tr><td class="tr-caption" style="text-align: center;">SharePoint includes an auto-incrementing primary key column called <i>ID</i> on every list</td></tr></tbody></table><div class="separator" style="clear: both; text-align: center;"><br /></div><div class="separator" style="clear: both; text-align: left;">The Customers table has a CompanyName column. I might want to put some constraints on it. It would probably make sense to require a value for that column, as what good is a Customers record without the customer's name? Just as SQL Server allows you to decide if a column accepts nulls or not, in SharePoint, we can set "Require that this column contains information" to "Yes".</div><div class="separator" style="clear: both; text-align: left;"><br /></div><div class="separator" style="clear: both; text-align: left;">You might also want to put a uniqueness constraint on the CompanyName column. In SharePoint, we have the "Enforce unique values" setting.</div><div class="separator" style="clear: both; text-align: left;"><br /></div><div class="separator" style="clear: both; text-align: left;">We might even want to specify a default value for the Country field, perhaps defaulting it to "USA" if our customers are primarily American companies. SQL Server has default constraints, and SharePoint has the "Default value" setting.</div><div class="separator" style="clear: both; text-align: left;"><br /></div><table align="center" cellpadding="0" cellspacing="0" class="tr-caption-container" style="margin-left: auto; margin-right: auto; text-align: center;"><tbody><tr><td style="text-align: center;"><a href="http://i.imgur.com/v098YQ7.png" imageanchor="1" style="margin-left: auto; margin-right: auto;"><img border="0" height="184" src="http://i.imgur.com/v098YQ7.png" width="640" /></a></td></tr><tr><td class="tr-caption" style="text-align: center;">SharePoint columns can have constraints familiar to SQL Server users</td></tr></tbody></table><div class="separator" style="clear: both; text-align: center;"><br /></div><div class="separator" style="clear: both; text-align: left;">For performance reasons, you can even index a column.</div><div class="separator" style="clear: both; text-align: left;"><br /></div><table align="center" cellpadding="0" cellspacing="0" class="tr-caption-container" style="margin-left: auto; margin-right: auto; text-align: center;"><tbody><tr><td style="text-align: center;"><a href="http://i.imgur.com/gCnqntb.png" imageanchor="1" style="margin-left: auto; margin-right: auto;"><img border="0" height="126" src="http://i.imgur.com/gCnqntb.png" width="400" /></a></td></tr><tr><td class="tr-caption" style="text-align: center;">SharePoint columns can be indexed</td></tr></tbody></table><div class="separator" style="clear: both; text-align: left;"><br /></div><div class="separator" style="clear: both; text-align: left;"><br /></div><h3>Foreign Key ⇔ Lookup</h3></div><div>The Northwind database has an Orders table with a foreign key to the Customers table because each order has a corresponding customer that placed the order.</div><div><br /></div><div>SharePoint's answer to the foreign key is the <i>lookup </i>column. Since tables are like lists, we could create an Orders list and add a lookup column.</div><div><br /></div><table align="center" cellpadding="0" cellspacing="0" class="tr-caption-container" style="margin-left: auto; margin-right: auto; text-align: center;"><tbody><tr><td style="text-align: center;"><a href="http://i.imgur.com/98GnHna.png" imageanchor="1" style="margin-left: auto; margin-right: auto;"><img border="0" src="http://i.imgur.com/98GnHna.png" width="640" /></a></td></tr><tr><td class="tr-caption" style="text-align: center;">Adding a lookup column on the Orders list</td></tr></tbody></table><table align="center" cellpadding="0" cellspacing="0" class="tr-caption-container" style="margin-left: auto; margin-right: auto; text-align: center;"><tbody><tr><td style="text-align: center;"><a href="http://i.imgur.com/XVm8rSY.png" imageanchor="1" style="margin-left: auto; margin-right: auto;"><img border="0" src="http://i.imgur.com/XVm8rSY.png" width="640" /></a></td></tr><tr><td class="tr-caption" style="text-align: center;">Just as the Orders table has a foreign key to the Customers table, the Orders&nbsp;<i>list</i>&nbsp;has a&nbsp;<i>lookup</i>&nbsp;to the Customers list</td></tr></tbody></table><div><br />You can even enforce referential integrity through cascade constraints. We could tell SharePoint that we want it to delete any orders that go with a customer we've just deleted.<br /><br /><table align="center" cellpadding="0" cellspacing="0" class="tr-caption-container" style="margin-left: auto; margin-right: auto; text-align: center;"><tbody><tr><td style="text-align: center;"><a href="http://i.imgur.com/rfabt9p.png" imageanchor="1" style="margin-left: auto; margin-right: auto;"><img border="0" height="98" src="http://i.imgur.com/rfabt9p.png" width="640" /></a></td></tr><tr><td class="tr-caption" style="text-align: center;">You can set up cascading delete behavior, just like in SQL Server</td></tr></tbody></table><br /><br /><h3>View ⇔ View</h3></div><div>SharePoint lists can have views, analogous to the views you'd define in SQL Server.</div><div><br /></div><table align="center" cellpadding="0" cellspacing="0" class="tr-caption-container" style="margin-left: auto; margin-right: auto; text-align: center;"><tbody><tr><td style="text-align: center;"><a href="http://i.imgur.com/Igk0p5p.png" imageanchor="1" style="margin-left: auto; margin-right: auto;"><img border="0" src="http://i.imgur.com/Igk0p5p.png" width="640" /></a></td></tr><tr><td class="tr-caption" style="text-align: center;">Adding a view to the Customers list</td></tr></tbody></table><div><br />These views can have sorts, filters, group bys and other features you'd expect (even <a href="http://sharepointificate.blogspot.com/2012/12/joins-and-projections-in-sharepoint-2010.html" target="_blank">joins</a>).<br /><br /><br /><h3>Trigger ⇔ Event Receiver</h3></div><div>In SQL Server, you might want to attach a trigger to a database table to run some custom code when a record is being inserted, updated, or deleted.</div><div><br /></div><div>SharePoint's answer to the trigger is the <i><a href="http://msdn.microsoft.com/en-us/library/office/ff408183.aspx" target="_blank">event receiver</a></i>. You can run custom .NET code when certain events occur. For example, when an item is being added, updated, or deleted in a list.<br /><br />Event receivers have so many uses that I plan to elaborate on them in a separate blog post.<br /><br /><br /><h3>SQL ⇔ CAML</h3></div><div>When it's time to write some queries against your SQL Server database, you turn to its namesake, SQL. SharePoint has its own language for expressing queries called <i>CAML</i>.</div><div><br /></div><div>Being a dialect of XML, <a href="http://msdn.microsoft.com/en-us/library/office/ms467521.aspx" target="_blank">CAML is quite verbose</a> and can be unwieldy to write by hand. There are tools that can make CAML easier to manage, and I discuss them in my post <i><a href="http://www.spmeta.net/2014/03/taming-caml.html" target="_blank">Taming CAML</a></i>.</div><div><br /><br /></div><div><h3>SQL Server Management Studio ⇔ SharePoint Manager</h3></div><div>When you want to inspect your SQL Server databases, you fire up SQL Server Management Studio and crack open the Object Explorer. It lays out the whole structure of your SQL Server installation and lets you view the data in your tables.<br /><br /><table align="center" cellpadding="0" cellspacing="0" class="tr-caption-container" style="margin-left: auto; margin-right: auto; text-align: center;"><tbody><tr><td style="text-align: center;"><a href="http://i.imgur.com/KjwV8N0.jpg" imageanchor="1" style="margin-left: auto; margin-right: auto;"><img border="0" height="320" src="http://i.imgur.com/KjwV8N0.jpg" width="266" /></a></td></tr><tr><td class="tr-caption" style="text-align: center;">Browsing your tables in SQL Server Management Studio</td></tr></tbody></table><br />There's an indispensable open source project called <a href="http://spm.codeplex.com/" target="_blank">SharePoint Manager</a> that accomplishes roughly the same objectives in the SharePoint world. SharePoint Manager provides a nice GUI for browsing the site collections in your SharePoint farm, and inspecting every list, column, view, event receiver, and every other SharePoint object that exists.<br /><br /><table align="center" cellpadding="0" cellspacing="0" class="tr-caption-container" style="margin-left: auto; margin-right: auto; text-align: center;"><tbody><tr><td style="text-align: center;"><a href="http://i.imgur.com/ruZ2HQS.png" imageanchor="1" style="margin-left: auto; margin-right: auto;"><img border="0" height="236" src="http://i.imgur.com/ruZ2HQS.png" width="320" /></a></td></tr><tr><td class="tr-caption" style="text-align: center;">Browsing your lists in SharePoint Manager</td></tr></tbody></table><div class="separator" style="clear: both; text-align: center;"><br /></div><div class="separator" style="clear: both; text-align: center;"><br /></div><div class="separator" style="clear: both; text-align: center;"><br /></div><h2>Querying Data from Code</h2><div><br /></div>If you've been in the .NET world for long enough, or if you're working with a legacy code base, you may be familiar with using "raw" <a href="http://msdn.microsoft.com/en-us/library/dw70f090(v=vs.110).aspx#_SqlClient" target="_blank">ADO.NET</a> classes to query for data in your database. If you're working in a .NET code base of a more recent vintage, you're probably familiar with <a href="http://en.wikipedia.org/wiki/Object-relational_mapper" target="_blank">object-relational mappers</a>, like <a href="http://weblogs.asp.net/scottgu/archive/2007/05/19/using-linq-to-sql-part-1.aspx" target="_blank">LINQ to SQL</a>, that provide a nice strongly-typed object model for your database interactions.<br /><br /><br /><h3>Raw ADO.NET&nbsp;⇔ SPListItem, SPQuery, etc.</h3></div><div>The SharePoint API contains classes for getting data out of lists in a weakly-typed fashion. If you're familiar with classes from ADO.NET like <a href="http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.aspx" target="_blank">SqlCommand</a>&nbsp;and <a href="http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqldatareader.aspx" target="_blank">SqlDataReader</a>, then SharePoint API classes like <a href="http://msdn.microsoft.com/en-us/library/microsoft.sharepoint.spquery.aspx" target="_blank">SPQuery</a>&nbsp;and <a href="http://msdn.microsoft.com/en-us/library/microsoft.sharepoint.splistitem.aspx" target="_blank">SPListItem</a> will seem very familiar.</div><div><br /><br /></div><div><h3>LINQ to SQL ⇔ LINQ to SharePoint</h3></div><div>Object-relational mappers like LINQ to SQL provide a much more natural, object-oriented interface to your data. LINQ to SQL includes a tool called <a href="http://msdn.microsoft.com/en-us/library/bb386987.aspx" target="_blank">SqlMetal </a>that can be run against an existing SQL Server database and will generate C# or VB classes that represent the tables in your database.</div><div><br /></div><div>LINQ to SharePoint has <a href="http://msdn.microsoft.com/en-us/library/office/ee538255.aspx" target="_blank">SPMetal</a>. You point SPMetal at your SharePoint site and it generates C# or VB classes that represent the lists in that site. LINQ to SharePoint has some drawbacks to consider, which I discuss in my post <i><a href="http://www.spmeta.net/2014/03/taming-caml.html" target="_blank">Taming CAML</a></i>.</div><div><br /><br /><br /></div><h2>What's in It for Me?</h2><div><br /></div><div>Sure, SharePoint <i>can </i>be the relational database for your application. But why <i>should </i>it be?</div><div><br /><br /></div><h3>Scaffolding for Free</h3><div>Speaking from my own experience, I can say that there's nothing more tedious than writing the <a href="http://en.wikipedia.org/wiki/Create,_read,_update_and_delete" target="_blank">CRUD</a>&nbsp;forms that wrap a database table.</div><div><br /></div><div>When <a href="http://rubyonrails.org/" target="_blank">Ruby on Rails</a> came along, one of the things that blew me away was this feature it had called <i><a href="http://docs.railsbridge.org/intro-to-rails/CRUD_with_scaffolding" target="_blank">scaffolding</a></i>. Once you had your database schema in place, Rails would automatically create working forms for you to create, read, update, and delete records in your database. This was revolutionary! I <i>hated </i>writing that stuff by hand! And it took up <i>so</i> much time.<br /><br /><div class="separator" style="clear: both; text-align: center;"><iframe width="560" height="315" src="https://www.youtube.com/embed/Gzj723LkRJY" frameborder="0" allowfullscreen></iframe></div><br /></div><div>If you're storing your data in SharePoint lists, you've got something that looks an awful lot like scaffolding. SharePoint automatically provides forms for shuffling around the data in your list. Once we defined our Customers list above, SharePoint magically gave us all the forms for manipulating our customer records. Sweet!<br /><br /><table align="center" cellpadding="0" cellspacing="0" class="tr-caption-container" style="margin-left: auto; margin-right: auto; text-align: center;"><tbody><tr><td style="text-align: center;"><a href="http://i.imgur.com/0y4FWcZ.png" imageanchor="1" style="margin-left: auto; margin-right: auto;"><img border="0" height="286" src="http://i.imgur.com/0y4FWcZ.png" width="640" /></a></td></tr><tr><td class="tr-caption" style="text-align: center;">SharePoint automatically generates all the forms needed to perform CRUD actions on our Customer data</td></tr></tbody></table><br />This auto-magic CRUD functionality in SharePoint is deep, and I plan to elaborate on it in another blog post.<br /><br /><br /><h3>Deep Security Integration</h3></div><div>SharePoint has a deeply integrated security model. There's a built-in security system for applying permissions to your lists and even <i>individual</i> <i>records</i> in your lists. Once you have your permissions configured, SharePoint automatically trims its UI based on the particular logged in user, and even restricts actions performed by methods in its <a href="http://msdn.microsoft.com/en-us/library/microsoft.sharepoint.aspx" target="_blank">API</a>.</div><div><br /></div><table align="center" cellpadding="0" cellspacing="0" class="tr-caption-container" style="margin-left: auto; margin-right: auto; text-align: center;"><tbody><tr><td style="text-align: center;"><a href="http://i.imgur.com/aysptWO.png" imageanchor="1" style="margin-left: auto; margin-right: auto;"><img border="0" height="206" src="http://i.imgur.com/aysptWO.png" width="640" /></a></td></tr><tr><td class="tr-caption" style="text-align: center;">SharePoint's screen for declaring the permissions on a list</td></tr></tbody></table><div><br /></div><div>For example, you could configure the security on your Customers list so that certain groups or users only have <i>read </i>permissions. In this case, SharePoint will automatically hide any of its UI elements that involve adding an item to the Customers list when these certain users were logged in. Even if you're running custom code that calls into <a href="http://msdn.microsoft.com/en-us/library/ee586483.aspx" target="_blank">SharePoint's API to add an item</a> to the Customers list, it will pick up the currently logged in user's permissions and refuse to add the item.</div><div><br /></div><div>SharePoint's security model is extremely deep and is woven into every aspect of the product. I plan to cover it in depth in a future blog post.</div><div><br /><br /></div><h3>Easy Schema Changes</h3><div>One of the biggest headaches that comes with depending on a custom database is the difficulty of pushing out schema changes.</div><div><br /></div><div>Developer A needs to add a new column to a table. He fires up SQL Server Management Studio on his local machine and adds the column. Now what? Developers B, C, D, E, and F all need that column added on their machines, too. So Developer A might shoot an email to the other developers telling them what the column is called, its data type, whether it allows nulls, etc. and ask them to manually make the change on their machines. If he's a nice guy, he might even write a snippet of <a href="http://technet.microsoft.com/en-us/library/ms190238.aspx#TsqlProcedure" target="_blank">T-SQL</a> they can run and send that over.&nbsp;</div><div><br /></div><div>But you also have a production server, a staging server, and a QA server. So now someone has to log into those machines and take the same manual steps to get that new column added. What if there are new tables or new views? This method of keeping your database schema up to date across machines quickly becomes a time-consuming and error-prone nightmare.</div><div><br /></div><div>SharePoint has a comprehensive API for "schema" changes. Want to <a href="http://msdn.microsoft.com/en-us/library/ms472869.aspx" target="_blank">add a column</a> to a list? <a href="http://msdn.microsoft.com/en-us/library/ms413986.aspx" target="_blank">Add a new list</a>? <a href="http://msdn.microsoft.com/en-us/library/ms475170.aspx" target="_blank">Add a view</a>?&nbsp;</div><div><br /></div><div>SharePoint <i><a href="http://msdn.microsoft.com/en-us/library/office/ms460318.aspx" target="_blank">features</a> </i>provide the opportunity to make your schema changes in code and have them automatically applied when your application is deployed or upgraded. This is another deep topic, and one I elaborate on in my post <i><a href="http://www.spmeta.net/2014/03/a-model-for-upgradeable-sharepoint-sites.html" target="_blank">A Model for Upgradeable SharePoint Sites</a></i>.</div><div><br /><br /><br /></div><h2>Closing</h2><div>In my career, I've seen teams working on SharePoint projects reflexively reach for a custom SQL Server database when it was time to store some data. I hope that in this post I was able to show that SharePoint can be your database, and there are definite advantages to using it in this way.</div><div><br /></div><div>As I've noted throughout, several of the topics require elaboration. When I cover them in future posts, I'll come back here and link them up as appropriate.</div><div><br /></div><div>See you next time.<br /><br /></div>Matt Blodgetthttp://www.blogger.com/profile/06115180196173160813noreply@blogger.com6tag:blogger.com,1999:blog-5509311701721730965.post-29824447132113031862014-02-06T18:39:00.000-08:002017-04-02T12:35:15.012-07:00Welcome to SPMetaSharePoint is a <i>beast</i>, isn't it? I remember my early days coming up to speed from my background with vanilla ASP.NET, and being so confused (and angry) in this new landscape I found myself in. In those days I wasn't only going against the grain, I didn't even realize there <i>was</i>&nbsp;a grain.<br /><div><br /></div><div class="separator" style="clear: both; text-align: center;"><a href="http://i.imgur.com/XPBas99.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="http://i.imgur.com/XPBas99.jpg" /></a></div><div><br /></div><div>After spending several years of my life working day in and day out with SharePoint, starting from scratch, learning <i>everything</i> the hard way, and in the process architecting and building multiple products using SharePoint as an application platform, I've come to the point where I'd like to stop and write down some of what I've learned.</div><div><br /></div><div>My goal with SPMeta is to discuss some of the conceptual underpinnings of SharePoint, and, if I'm lucky, shed some light on what can be a very complex and intimidating product.<br /><br />I have a rough outline of topics I plan to discuss, and I'm eager to get started. See you next time!</div>Matt Blodgetthttp://www.blogger.com/profile/06115180196173160813noreply@blogger.com0