Accessing SharePoint List Data using Linq

Recently I wanted to return a SharePoint list as an List<> of a business object.

I did some scouring on the internet, as there is no point re-inventing the wheel.  But was generally disappointed with the results, as they required a lot of iterations and loops. Eric White’s blog here got me thinking, that we could do it with Linq

So here is what I did Projects my business object looks like this

Property	Data Type
ProjectOwner	string
KPI	string
SKU	string
DateRequired	DateTime?
DueDate	string

And here is the code, which expects a result set from a Sharepoint Web Service GetListItems() method.

Code Snippet

1. private static List<ProjectStatus> GetProjectStatusesFromXmlNode(XmlNode resultNode)
2.     {
3.         var list = (from r in resultNode.GetXElement().Descendants()
4.                     where r.Name.LocalName == "row"
5.                     select new ProjectStatus
6.                     {
7.                         Project = (string)r.Attribute("ows_LinkTitle"),
8.                         DateRequired = DateTime.Parse(((string)r.Attribute("ows_DateRequired"))),
9.                         DueDate = (string)r.Attribute("ows_ECRDueDate"),
10.                         SKU = (string)r.Attribute("ows_SKU"),
11.                         Status = (string)r.Attribute("ows_Status"),
12.                         ProjectOwner = (string)r.Attribute("ows_AssignedTo"),
13.                         KPI = (string)r.Attribute("ows_tKPI")
14.                     }
15.                     ).ToList();
16.         return list;
17.     }

You will also need Eric White’s extension

Code Snippet

1. public static class Extensions
2. {
3.     public static XElement GetXElement(this  XmlNode node)
4.     {
5.         XDocument xDoc = new XDocument();
6.         using (XmlWriter xmlWriter = xDoc.CreateWriter())
7.             node.WriteTo(xmlWriter);
8.         return xDoc.Root;
9.     }
10.  
11. }

Converting a Sharepoint Personal View into a Public View List.

I have the problem where a user has created a view and I need to get access to it and make it public.  I appreciate this is quite a slog, especially in an ideal world the user would make the List public but if he/she has left …

The first problem was identifying the information for the view. After quite a bit of digging I find it in the WebParts table in the appropriate Content Database

Health and Safety warning – do not play with this data, there are some rather large and ugly dragons who are not scared to bite you, which in turn could incur the wrath of you superiors.

The field you are after is tp_view this contains a pseudo xml file showing the view.

To progress further I created a winform application with a textbox and a button, the string from tp_view goes in to the textbox. The button_click performs the magic.

We are going to use the splist.Views.Add function

Code Snippet

1. SPView view = list.Views.Add("newView", strCollViewFields, strQuery, uint.Parse(RowLimit), true, false);

All of the information above is found in the string.

strCollViewFields is populated using, which is relatively straight forward the +13 means we get the end tag as well

Code Snippet

1. private StringCollection GetViewFields()
2.         {
3.             XmlDocument xmlDoc = new XmlDocument();
4.  
5.             //assumes Viewfields is at the beginning of the string.
6.             string xml = definition.Substring(0, definition.IndexOf("</ViewFields>") + 13);
7.             xmlDoc.LoadXml(xml);
8.  
9.             var returnvar = new StringCollection();
10.  
11.             foreach (XmlNode node in xmlDoc.SelectNodes("ViewFields/FieldRef"))
12.             {
13.                 returnvar.Add(node.Attributes[0].Value);
14.             }
15.  
16.             return returnvar;
17.         }

strQuery, here I +7 is on the start tag because I do not want the Query tag, this is a SharePoint thing and I do not know why!

Code Snippet

1. private string GetQuery()
2.         {
3.             string returnVar = string.Empty;
4.  
5.             int queryTagStartLocation = definition.IndexOf("<query>", StringComparison.InvariantCultureIgnoreCase) + 7;
6.             int queryTagEndLocation = definition.IndexOf("</query>", StringComparison.InvariantCultureIgnoreCase);
7.             int queryLength = queryTagEndLocation - queryTagStartLocation;
8.             //All we need is the contents not the actual query tag
9.             returnVar = definition.Substring(queryTagStartLocation, queryLength);
10.  
11.             return returnVar;
12.         }

RowLimit  and Paged ( I have set it to true above)

Code Snippet

1. private string GetRowLimit(out bool Paged)
2.     {
3.         //<RowLimit Paged="TRUE">100</RowLimit>
4.         
5.         string value = string.Empty;
6.         string paged = string.Empty;
7.        
8.         XmlDocument xmlDoc = new XmlDocument();
9.         xmlDoc.LoadXml(GetXMLString("RowLimit"));
10.  
11.         foreach (XmlNode node in xmlDoc.SelectNodes("RowLimit"))
12.         {
13.             paged = node.Attributes[0].Value;
14.             value = node.InnerXml;
15.         }
16.  
17.         Paged = bool.Parse(paged);
18.  
19.         return value;
20.     }

Here I have created a new function GetXMLString() which I am sure you could parameterise and deprecate GetQuery()

Code Snippet

1. private string GetXMLString(string tag )
2. {
3.    string startTag = "<" + tag ;
4.    string endTag = "</" + tag + ">";
5.  
6.  
7.    int queryTagStartLocation = definition.IndexOf(startTag, StringComparison.InvariantCultureIgnoreCase);
8.    int queryTagEndLocation = definition.IndexOf(endTag, StringComparison.InvariantCultureIgnoreCase) + endTag.Length;
9.     int queryLength = queryTagEndLocation - queryTagStartLocation;
10.     //All we need is the contents not the actual query tag
11.     return definition.Substring(queryTagStartLocation, queryLength);
12. }

The List I had also has some aggregations which we can get from GetXMLString() function. 

All in all the main function looks like this

Code Snippet

1. try
2.             {
3.                 definition = textBox1.Text;
4.  
5.                 bool paged;
6.                 var strCollViewFields = GetViewFields();
7.                 var strQuery = GetQuery();
8.                 var RowLimit = GetRowLimit(out paged);
9.                 var aggregations = GetXMLString("Aggregations"); ;
10.  
11.                 SPSecurity.RunWithElevatedPrivileges(delegate()
12.                 {
13.  
14.                     using (var site = new SPSite("http://srvJupiter:6000/"))
15.                     {
16.                         using (var web = site.AllWebs[0])
17.                         {
18.  
19.                             var list = web.Lists["Projects"];
20.  
21.                             SPView view = list.Views.Add("newView", strCollViewFields, strQuery, uint.Parse(RowLimit), true, false);
22.  
23.                             view.Aggregations = aggregations;
24.                             view.Update();
25.  
26.                         }
27.                     }
28.                 }
29.                 );

As usual test anything in a test system first.

Dundas Charts – Accessing Primary Key Information for Drill Down

I had the situation where I want to show Countries along the x (horizontal) axis, but for the drill down on the click event I wanted to use the Id for the item. The solution is surprisingly simple although did cause some head scratching for an hour or so.

We are going to store the additional information in an additional Y axis. To do so we need to tell the chart we are going to have an additional Y Value. The example below is for a Line chart, for other charts this might be different.

Code Snippet

1. Chart.Series["Series"].YValuesPerPoint = 2;

Then I am binding using Points.DataBind to bind the data.  I have a dataset (items) with Destination which is my Country Name, DestinationId it’s primary key then PercentageErrorRate is my point on the graph. 

Code Snippet

1. PercentErrorRateVTargetbyRAREgion.Series["ErrorRate"].YValuesPerPoint = 2;
2. PercentErrorRateVTargetbyRAREgion.Series["ErrorRate"].Points.DataBind(items, "Destination", "PercentErrorRate,DestinationId", "");

On the y axis we are defining "PercentErrorRate,DestinationId" this list is comma delimited the first is the point shown on the graph then the rest can be accessed separately.

I am accessing this information on a click event, and have the following function.

Code Snippet

1. public static void SetAttributes(Chart chart, int year, string month, string countries)
2. {
3.  
4.     foreach (Series series in chart.Series)
5.     {
6.         //I know this could be in one string but this is easier to read.
7.         var onclick = "Onclick=\"javascript:DundasHandler('" + chart.ID + "','#VALY2','" + year + "','" + month + "','" + countries + "','#SERIESNAME');\"";
8.         var onMouseOver = "OnMouseOver=\"javascript:DundasHandIn('" + chart.ID + "', 'pointer'); \"";
9.         var onMouseOut = "OnMouseOut=\"javascript:DundasHandOut('" + chart.ID + "'); \"";
10.  
11.         series.MapAreaAttributes = onclick + onMouseOut + onMouseOver;
12.     }
13. }

The bit we are interested in is #VALY2 this tells Dundas to insert the y2 value here, in this case DestinationId. I then have a javascript function which opens a new window and builds the URL.  I am using Telerik controls which is why I have radopen.

Code Snippet

1. function DundasHandler(chartName, point, year, month, country, seriesName) {
2.     var ownd = radopen("GridDialog.aspx?chartName=" + chartName + '&point=' + point + '&month=' + month + '&year=' + year + '&country=' + country + '&point2=' + seriesName, "RadWindow1");
3. }

There you go  I hope it helps.

Sys.WebForms.PageRequestManagerParserErrorException in Sharepoint

I have just tripped over this (again), the reason;

<add name="ScriptModule" type="System.Web.Handlers.ScriptModule, System.Web.Extensions, Version=1.0.61025.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35" />

had not been added to the httpModules section of the web.config file.

Programmatically Exporting Data from SharePoint into SQL Server using SQLBulkCopy

 

Those who have read my previous posts will know I was planning on using the SSIS SharePoint Connectors for extracting data from SharePoint to SQL Server.  All was going swimmingly well until I deployed them onto the server, then for some reason (and I spent a day scratching my head) they did not work.  Unfortunately on this server I have limited debugging resources, and had to make a risk based decision.  Do I put in debugging, find out why it does not work then fix it or try another solution.  I have never struggled a whole day on a problem like that before, and at 5 in the morning (whilst asleep) I had a blinding idea!

The basic idea is to use the ADO.Net SqlBulkCopy  to insert the data, but my main challenge is to make this configurable so that any SharePoint list can be inserted into any SQL Server table.  To do so I have created a number of objects which store the required data.   These are described below, but on a very basic level it mimics the SharePoint hierarchy.

Class	Properties
SharepointSite	string SiteURL
	string AuditList
	List<SharepointList> Lists
SharepointList	string ListName
	string SQLTableName
	List<SharepointField> Fields
SharepointField	string FieldName
	string SQLFieldName

Then using  the method below we can insert the data into database,SharepointList is the object model above, the DataTable is the SharePoint list data in form of a DataSet table and the connectionstring is self explanatory.

Code Snippet

1. private static void InsertIntoDatabase(SharepointList sharepointList, DataTable data, string connectionString)
2.         {
3.             //connectionstring
4.             using (var connection = new SqlConnection(connectionString))
5.             {
6.                 var bulkCopy = new SqlBulkCopy(connection)
7.                                    {
8.                                        DestinationTableName = sharepointList.SQLTableName
9.                                    };
10.                 foreach (var field in sharepointList.Fields)
11.                 {
12.                     var mapping = new SqlBulkCopyColumnMapping
13.                                       {
14.                                           DestinationColumn = field.SQLFieldName,
15.                                           SourceColumn = field.FieldName
16.                                       };
17.                     bulkCopy.ColumnMappings.Add(mapping);
18.  
19.                 }
20.                 connection.Open();
21.  
22.                 bulkCopy.WriteToServer(data);
23.             }
24.         }

The clever bit is using Serialization for the configuration.  By Serializing and De-serializing the data into and out of XML we can now use any combination of Sharepoint Lists and SQL Tables.  The code below shows how to do this.  You will need to mark classes as Serializable see here.

Code Snippet

1. private static SharepointSite GetSharepointSitefromSettings()
2.         {
3.             var mappingFile = ConfigurationSettings.AppSettings["MappingFile"];
4.  
5.             var ser = new XmlSerializer(typeof(SharepointSite));
6.             var xmlReader = new XmlTextReader(mappingFile);
7.  
8.             var sharepointSite = (SharepointSite)ser.Deserialize(xmlReader);
9.             xmlReader.Close();
10.             return sharepointSite;
11.         }

And this is a snippet of the XML

<SharepointSite xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
  <SiteURL>http://srvJupiter</SiteURL>
    <Lists>
    <SharepointList>
      <ListName>Projects</ListName>
            <SQLTableName>ProjectsStaging</SQLTableName>
      <Fields>
        <SharepointField>
          <FieldName>Title</FieldName>
          <SQLFieldName>Title</SQLFieldName>
        </SharepointField>
        <SharepointField>
          <FieldName>ID</FieldName>
          <SQLFieldName>ID</SQLFieldName>
        </SharepointField>
         </Fields>
    </SharepointList>   
  </Lists>
</SharepointSite>

Conclusion

I hope this gives an insight on how I solved my problem. 

Obviously this is not the complete solution, as that is too large to go into detail;

•   I made this as a SharePoint Timer Job, which requires a OWSTimer.exe.config file for the settings.  I stored the mappings in a separate file, which allows you to keep the xml format.
• The timer job also runs stored procedures before and after the import, which allows data transformations to happen.
• Audit data is written to a SharePoint list as well.

But there is enough code here to create a POC.

Creating SharePoint Views

As usual a little background, as I do not want you to take it out of context.

I am using SSIS with the SharePoint connectors to extract data out of a SharePoint list, to do so I wanted to create a custom view, this allows me to get all of the columns and to select the rows I need.  Also I like to do these things programmatically as I personally will not be doing the release.

To create a View we need to use SPList.Views.Add, which is slightly unusual because we cannot add an object. see http://msdn.microsoft.com/en-us/library/microsoft.sharepoint.spviewcollection.add.aspx

So this is the code I have used.

string viewName = "ExportView";

var strCollViewFields = new StringCollection();

foreach (SPField field in site.Lists[projectsListName].Fields)
{
    if (field.Hidden == false)
    {
        strCollViewFields.Add(field.InternalName);
    }
}
var strQuery = "<Where><Eq><FieldRef Name=\"Status\" /><Value Type=\"Choice\">Complete</Value></Eq></Where>";


site.Lists[projectsListName].Views.Add(viewName, strCollViewFields, strQuery, 100,true,false, SPViewCollection.SPViewType.Html, false);

The StringCollection is of type System.Collections.Specialized.StringCollection and is a list of the internal field name, this is usually the text on the field. With "_x0020_" instead of spaces.

StrQuery is a CAML query I use CAML Builder (by U2U) but remember to remove the <query> tag, or it will fail and all items will be returned.


100 is the number of rows, when imported this is ignored


true is whether to show pagination.


false is whether this is the default view


The next shows how to display the screen take a look at http://msdn.microsoft.com/en-us/library/microsoft.sharepoint.spviewcollection.spviewtype.aspx


And the last is whether this is a personal view or not.

Using WSPBuilder

I have been using WSPBuilder for a while and suddenly noticed that the documentation on the Codeplex site is a bit poor. 

For those who do not know WSPBuilder is a tool and a set of extensions which facilitates the making, deploying and debugging of SharePoint features.

Creating a WSPBuilder Project

Select WSPBuilder from the project types, then select either “WSPBuilder Project” or “WSPBuilder Project with Workflow”

 

Then we need to add a project item to do so select Project | Add New Item (or your preferred method) from the Categories select WSPBuilder and select the most appropriate item for your project.

 

 

 

Complete this dialog, this sets the information which is found in the Manage Features section of your SharePoint site. Scope is quite important as this sets the visibility. See http://msdn.microsoft.com/en-us/library/ms476615.aspx

 

You will now notice that the Feature directory layout has been created. Along with the elements.xml and Feature.xml.

Finally the real magic! By right clicking on the project you get the WSPBuilder Menu. This menu has the following items.

· Build WSP – This creates a WSP File

· Deploy – Deploys the WSP to the server
· Upgrade – Upgrades existing feature
· Uninstall – Removes the WSP from the server
· Copy to 12 hive – Does not deploy but rather copies the information
· Copy to GAC – Copies assemblies to the GAC
· Recycle AppPools
· Recycle Services Timer
· Create Deployment Folder – Creates a setup.exe
· Attach to IIS Worker Process – this attaches the code to the correct W3Wp.exe process (invaluable)

WSP Feature Deployment and Uninstalling

I need to deploy / uninstall some features which I have created and not having access to the servers and not wanting to create complicated release notes, I came up with the following batch file

it is called with 2 or 3 parameters; the first is the wsp name, the second the server and the third “U” or “u” if you want to uninstall it.

I am sure everyone will have their own bit they want to do, but it is a start.

ECHO OFF

SET wspname=%1
SET wspname=%wspname:.wsp=%

If [%1] == [] GOTO USAGE
If [%2] == [] GOTO USAGE
If [%3] == [U] GOTO UNINSTALL
If [%3] == [u] GOTO UNINSTALL

ECHO ************** INSTALL %1 ***************
ECHO ON

stsadm -o addsolution -filename %1
stsadm -o execadmsvcjobs
stsadm -o deploysolution -name %1 -immediate -allowGacDeployment -force
stsadm -o execadmsvcjobs
stsadm -o activatefeature -filename "%wspname%\feature.xml" -url %2
GOTO END

:UNINSTALL

ECHO ************** UNINSTALL %1 ***************
ECHO ON
stsadm -o deactivatefeature -filename "%wspname%\feature.xml" -url %2
stsadm -o retractsolution -name %1 -immediate
stsadm -o execadmsvcjobs
stsadm -o deletesolution -name %1 -override
stsadm -o execadmsvcjobs

GOTO END

:USAGE
ECHO Parameter 1 = wsp filename, for example myfeature.wsp
ECHO Parameter 2 = site, for example http://myserver:1000
ECHO Parameter 3  u or U = uninstall

:END
ECHO OFF
ECHO Done please check for errors

SharePoint Tools I have found useful

This is another one of those, i need to make a note of these tools and this is a “good place as any” posts.

WSPBuilder – this is the tool for creating VS.Net projects much better than VSE WSS.

SharePoint Manager (also has link to 2007 version)- This shows the SharePoint Hierarchy and allows you to do stuff

CAML Builder – because CAML is a bugger. Although do not forget to remove the <query> tag, I always do

SQL Server Integration Services Adapter – For 2005 and 2008 allows you to integrate SSIS to SharePoint.

Events Manager – This allows you to see all of the EventHandlers attached to a list.

Also this blog is great for the command line on how to install / uninstall wsp files. (something I always forget).

Restoring and moving a content database Sharepoint 2007

For no other reason than I have had to do this a couple of times this week and I have to keep looking it up!

Restoring a database from a backup file to another database name

RESTORE DATABASE WSS_Content_ArtworksTest
FROM DISK = N'C:\test\WSS_content_artworks.bak'
WITH FILE = 1,
MOVE N'WSS_Content_Artworks' TO N'C:\Program Files\Microsoft Office Servers\12.0\Data\MSSQL.5\MSSQL\Data\WSS_Content_ArtworksTest.mdf',
MOVE N'WSS_Content_Artworks_log' TO N'C:\Program Files\Microsoft Office Servers\12.0\Data\MSSQL.5\MSSQL\Data\WSS_Content_ArtworksTest_log.LDF',
NOUNLOAD,
STATS = 10,
REPLACE
GO

Changing the contentdb.

The original one has been removed using Central Admin. AssignNewDatabaseID is because I already have this database running another site.

stsadm -o addcontentdb -url http://url:6001 -databasename wss_content_ArtworksTest -assignnewdatabaseid D8833462-FD
59-464f-89F3-55CF7AAA6206

Which IIS 7.0 Worker Process (w3wp.exe) shall I attach the debugger

 

We have all had the problem, which IIS Worker process shall I attach to when debugging. This morning I have stumbled across perhaps the easiest way of doing this!

Fire up IIS, make sure the server is selected

Then select “Worker Processes”

et Voila!, just find the Application Pool for the correct Item.