Archive | SharePoint

SharePoint 2010, Client OM, jQuery Autocomplete and BCS/External lists

In this previous post, I used jQuery/SOAP/SPServices to access a SharePoint BCS list (an external list). I wanted to do the same thing using the Client Object Model (Some call it Clien OM? Or maybe CSOM?). Below is the code to access contact data in a BCS connection to Microsoft CRM 2011, and use it for autocomplete values. Things to note:

  1. You need to have a method tag in your CAML statement. This tells the list which “Operation” (as it is listed in SharePoint Designer) to use
  2. You need to use the “include method” with the executeQueryAsync method
  3. You need to include the ViewFields tags in the CAML statement, and I think they should match the include statement above
$('#ContactsSearchTextbox').autocomplete({
    source: function( request, response ) {
	    var CAMLQuery= "<View><Method Name='ContactReadList' /><Query><Where><Contains><FieldRef Name='FullName' /><Value Type='Text'>"+request.term+"</Value></Contains></Where></Query><ViewFields><FieldRef Name='FullName' /><FieldRef Name='ContactId' /><FieldRef Name='ParentCustomerIdName' /></ViewFields></View>";
		var context = new SP.ClientContext.get_current();
		var web = context.get_web();
		var list = web.get_lists().getByTitle('Contacts');
		var query = new SP.CamlQuery;
		query.set_viewXml(CAMLQuery);
		allItems = list.getItems(query);
		context.load(allItems, 'Include(FullName,ContactId,ParentCustomerIdName)');
		context.executeQueryAsync(function (){
			var arrayOfResults = new Array();
			var listItemEnumerator = allItems.getEnumerator(); 
			while(listItemEnumerator.moveNext()) {
				var oListItem = listItemEnumerator.get_current();
				arrayOfResults.push(oListItem);
			}
			response($.map(arrayOfResults ,function( item ) {
				return {
				label: item.get_item('FullName') + ' (' + item.get_item('ParentCustomerIdName') + ')',
				value: item.get_item('ContactId'),
				id: item.get_item('FullName')
				}
			}));
		},function(sender, args){
		   alert('Request failed. ' + args.get_message() + '\n' + args.get_stackTrace());
		});        
	},
	minLength: 5,
});

Accessing SharePoint 2010 BCS lists via SOAP/WebServices for use in a jQuery autocomplete

So it seems that you can’t access BCS list data via REST, according to this article. But it seems that you can access the list data through SOAP. I used the code below to query a BCS list that points to a MSCRM 2011 backend (I know I could go right to CRM via REST, but then I would have XSS issues). I then take the results and use them for a jQuery autocomplete for an input box. Obviously in the code below, Contacts is a BCS “external List”

 


$('#ContactSearchTextbox').autocomplete({
source: function( request, response ) {
	$().SPServices({
	operation: "GetListItems",
	async: false,
	listName: "Contacts",
	CAMLViewFields: "<ViewFields><FieldRef Name='FullName' /><FieldRef Name='ContactId' /><FieldRef Name='ParentCustomerIdName' /></ViewFields>",
	CAMLQuery: "<Query><Where><Contains><FieldRef Name='FullName' /><Value Type='Text'>"+request.term+"</Value></Contains></Where></Query>",
	completefunc: function (xData, Status) {
	$('#SearchResults').html(xData.responseText);
	response($.map( $(xData.responseXML).SPFilterNode("z:row"), function( item ) {
	return {
	label: $(item).attr('ows_FullName') + "(" + $(item).attr('ows_ParentCustomerIdName') +")",
	value: $(item).attr('ows_ContactId')
		}
	}));
	}
	});	    
},
minLength: 4
});

PowerShell script to search SharePoint Search WebService via SOAP

I wanted to copy all the files found in a SharePoint Search result for a scope that lived on a file share. So I wrote the following PowerShell code to query a SharePoint Search scope and find the url for each result.

#$KeywordQuery="Cows"
$SQLQuery="SELECT WorkId,Path,Title,Write,Author from Scope() WHERE `"Scope`"='NarrowScope' AND FREETEXT(defaultproperties,'Cows')"

$CountToReturn = 1000
$xmlDoc = new-object System.Xml.XmlDocument
$QueryPacket = $xmlDoc.CreateElement("QueryPacket")
$QueryPacket.SetAttribute("xmlns", "urn:Microsoft.Search.Query")
$Query = $xmlDoc.CreateElement("Query")
[void]$querypacket.AppendChild($Query)
$Context = $xmlDoc.CreateElement("Context")
[void]$query.AppendChild($Context)
$QueryText = $xmlDoc.CreateElement("QueryText")

#If you are Using the KeyWord method
#$QueryText.SetAttribute("type", "string") 
#$QueryText.set_InnerXMl($KeywordQuery)

#IF you are using SQL method
$QueryText.SetAttribute("type", "MSSQLFT")
$QueryText.set_InnerXMl($SQLQuery)

[void]$context.AppendChild($QueryText)
$Range = $xmlDoc.CreateElement("Range")
[void]$query.AppendChild($Range)
$Count = $xmlDoc.CreateElement("Count")
$Count.set_InnerXMl($CountToReturn)
[void]$range.AppendChild($Count)

$IncludeSpecialTermResults=$xmlDoc.CreateElement("IncludeSpecialTermResults")
$IncludeSpecialTermResults.set_InnerXML("false")
[void]$query.AppendChild($IncludeSpecialTermResults)

$PreQuerySuggestions=$xmlDoc.CreateElement("PreQuerySuggestions")
$PreQuerySuggestions.set_InnerXML("false")
[void]$query.AppendChild($PreQuerySuggestions)

$HighlightQuerySuggestions=$xmlDoc.CreateElement("HighlightQuerySuggestions")
$HighlightQuerySuggestions.set_InnerXML("false")
[void]$query.AppendChild($HighlightQuerySuggestions)

$IncludeRelevantResults=$xmlDoc.CreateElement("IncludeRelevantResults")
$IncludeRelevantResults.set_InnerXML("false")
[void]$query.AppendChild($IncludeRelevantResults)

$IncludeHighConfidenceResults=$xmlDoc.CreateElement("IncludeHighConfidenceResults")
$IncludeHighConfidenceResults.set_InnerXML("false")
[void]$query.AppendChild($IncludeHighConfidenceResults)

$Service = New-WebServiceProxy -UseDefaultCredential -uri http://sharepoint.search.com/Search/_vti_bin/Search.asmx
[ xml ]$Results = $Service.Query($QueryPacket.OuterXml)
write-host "Results=$($Results.ResponsePacket.Response.Range.Count)"
foreach ($Doc in $Results.ResponsePacket.Response.Range.Results.Document){
$PATH=$($Doc.Action.LinkUrl.("#text")).Replace("/","\").Replace("file:","")
write-host $PATH
#here is where you put your copy cmd
cp $PATH c:\Temp
}

PowerShell to get all items in a SharePoint 2007 list via Web Services/SOAP

I wanted to get a list’s contents in a SharePoint 2007 site via PowerShell. I ran into only one issue – how to handle the pagination. When creating the Xml to include the next page, I was running into formatting issues because the text contained a “=”. This link suggested that I create the XML element first then add the innerText after. Worked after that!

TheBelow is my script to get the contents of a SharePoint 2007 list.

 

$listName = "List Name"
$xmlDoc = new-object System.Xml.XmlDocument
$query = $xmlDoc.CreateElement("Query")
$viewFields = $xmlDoc.CreateElement("ViewFields")
$queryOptions = $xmlDoc.CreateElement("QueryOptions")
$rowLimit = "50"
$service = New-WebServiceProxy -UseDefaultCredential -uri http://sharepoint2007.comapny.com/_vti_bin/lists.asmx?WSDL
$nextPage=$true
while($nextPage){
$list = $service.GetListItems($listName, "", $query, $viewFields, $rowLimit, $queryOptions, "")
$list.data.row | select ows_ID,ows_Created,ows_Title
if ($list.data.ListItemCollectionPositionNext){
$nextPage=@"
<Paging ListItemCollectionPositionNext="" />
"@
$queryOptions.set_InnerXml($nextPage)
$queryOptions.ChildNodes[0].Attributes["ListItemCollectionPositionNext"].InnerText = "$($list.data.ListItemCollectionPositionNext)"
}
else {
write-host "done"
$nextPage=$false
}
}

PowerShell to list SharePoint 2007 lists

I wanted to get a quick list of all the lists in our SharePoint 2007 environment. With PowerShell 2, it is easy.

$lists = New-WebServiceProxy -UseDefaultCredential -uri http://sharepoint2007.company.com/_vti_bin/lists.asmx?WSDL
$lists.GetListCollection().List | select Title, Name

PowerShell code to query a table, and put the XML results into a SharePoint Document Library

If you look at this prior post, I showed how you can use an xml file in a document library as a source for an input box’s jQuery autocomplete. I wanted to automate the creation of these XML files and upload them to a SharePoint Document Library. For example, I wanted to query Microsoft CRM for all our contacts, and have them appear as an autocomplete for a search input box. I wrote the following PowerShell script to automate this process:

Function JBM-SQL-XMLQueryResultsToSharePointDocLibrary{
PARAM([parameter(Mandatory = $true)]$SQLServerName,[parameter(Mandatory = $true)]$DatabaseName,[parameter(Mandatory = $true)]$Query,[parameter(Mandatory = $true)]$DocLibraryURL,[parameter(Mandatory = $true)]$FullPathFileName)
$FileName=$FullPathFileName.split("\")[$FullPathFileName.split("\").Count -1]
$xml=Invoke-Sqlcmd -ServerInstance $SQLServerName -database $DatabaseName -query "$Query" | ConvertTo-Xml -NoTypeInformation -As String
[xml]$output=$xml -replace '<Property Name="([^"]+)">([^<]+)</Property>', '<$1>$2</$1>' -replace '<Property Name="([^"]+)"\s*/>','<$1/>'
$output.save("$FullPathFileName")

$webclient = New-Object System.Net.WebClient;
$webclient.UseDefaultCredentials = $true
$webclient.UploadFile("$DocLibraryURL/$FileName","PUT","$FullPathFileName")
}

I had to use this thread’s method to change the XML produced by ConvertTo-Xml. The default output puts the field name in to a Property Name tag.

Example: Default = <Property Name=”Version”>0</Property> . I wanted it to be = <Version>0</Version>

In theory, this can be used for any SQL query.

PowerShell, JSON, oData and CRM 2011 (or SharePoint 2010)

I am working on how to consume data from/to SharePoint 2010 and from/to CRM 2011. I decided to try and see if I can get the data to display in PowerShell, figuring if I can get it there, I should be able to get it anywhere?  Here is the code to loop through all the Contacts in a CRM 2011 deployment.

Took me a while to figure this out. Should work with any oData source?

$url="http://your.crm.server/Instance/XRMServices/2011/OrganizationData.svc/ContactSet"
$assembly = [Reflection.Assembly]::LoadWithPartialName("System.Web.Extensions")
while ($url){
	$webclient = new-object System.Net.WebClient
	$webclient.UseDefaultCredentials = $true
	$webclient.Headers.Add("Accept", "application/json")
	$webclient.Headers.Add("Content-Type", "application/json; charset=utf-8");
	$dataString=$webclient.DownloadString($url)
	$json=new-object System.Web.Script.Serialization.JavaScriptSerializer
	$data=$json.DeserializeObject($dataString)
	foreach ($result in $data.d.results){
		write-host "$($result.FullName) , $($result.EMailAddress1)"
	}
	Write-Host "Press any key to continue ..."
	$x = $host.UI.RawUI.ReadKey("NoEcho,IncludeKeyDown")
	if ($data.d.__next){
		$url=$data.d.__next.ToString()
	}
	else {
		$url=$null
	}
}

To loop through the items of a SharePoint 2010 list, you would change $url to:

$url=”http://sharepoint2010.server.com/_vti_bin/listdata.svc/Announcements”

Not sure if this would be valuable to anyone, but here it is!

How to add custom JavaScript code to all SharePoint 2010 pages (CustomAction)

There are plenty or articles on how to do this. This is more of a note for myself, as I have to “re-learn” this every time I need to customize SharePoint. There are 2 ways (that I know of) that you can add code to every page in SharePoint 2010, 1 by the AdditionalPageHead delegate control, or ,2 by Custom Action. This article is about #2 using a Custom Action. This article is about #1 – Delegate Controls

To add JavaScript to every page via CustomAction:

  1. Start Visual Studio, and create a new Empty SharePoint Project (uncheck the Create Directory for Solution because you salways create the destination directory yourself)
  2. Deploy as a Farm Solution (I have not figured what you can and can’t do with sandboxed solutions yet)
  3. RightCLick the Project and select Add Module and name the Module at the bottom (I name the Module the name of the Document Library where I want to put the file)
  4. Delete Sample.txt and add the javascript file you want (I usually have to go to the correct folder, put the file there, right click the ProjectName and select “Show all files”, then I can include the file in the project).
  5. Chop up the elements.xml file. The final should look like this below:
    1. is changed to (Url is the Doc Library where the file will be added)
    2. is changed to (Remove the path from Url and add the “GhostableInLibrary” part)
<Elements xmlns="http://schemas.microsoft.com/sharepoint/">
  <Module Name="Test" Url="Test">
    <File Path="Test\jquery-1.7.min.js" Url="jquery-1.7.min.js" Type="GhostableInLibrary" />
  </Module>
</Elements>

Now you need to the code to add the file to the top of every page, add the “CustomAction” stuff below

  <CustomAction
    ScriptSrc="~SiteCollection/Test/jquery-1.7.min.js"
    Location="ScriptLink"
    Sequence="11">
  </CustomAction>

Final looks like this:

Right click the project and package it up.

Common PowerShell commands to work with solutions are (you must create the destination Document Library before you try to install this code):

To add/install
Add-SPSolution H:\Path\To\wsp\Test.wsp
Install-SPSolution Test.wsp -GACDeploy
Update-SPSolution -literalpath  H:\Path\To\wsp\Test.wsp -identity Test.wsp -GACDeploy

To remove
Uninstall-SPSolution Test.wsp
Remove-SPSolution Test.wsp

This results in a new Feature in manage features (Active it!)

A new file in the document library, and the following in the source code for the page. Done!:

How to use Visual Studio to package a SharePoint solution if you don’t have SharePoint installed

I re-image my machine often, and when I fire up a Visual Studio 2010 for the first time, I always get an error “To work with this project, either SharePoint Foundation 2010 or SharePoint Server 2010 must be installed on the system” or “A SharePoint server is not installed on this computer. A SharePoint server must be installed to work with SharePoint projects.”

Instead of installing SharePoint on my local machine, I usually just trick Visual Studio into thinking that SharePoint is installed. This link says to export the RegHive from a real SharePoint server and import it. I can never remember this.

To compile your code, you need to copy the “Microsoft.SharePoint.dll” and Microsoft.SharePoint.Security.dll from “C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\14\ISAPI” to your local drive and “Add Reference” to your project (Right Click References)

Haven’t worked with remote debugging to much, but this article describes it.