Programmatically populate external data type column in SharePoint

If your list has an External Data Type Column and you want to populate is using code. You can do it as below. The external content we create has methods used by SharePoint BCS.

This code is done using PowerShell, but can be very easily re-written using Server Object Model.

#Note: In this scenario, I have written a function, wherein I am passing Opportunity Number and based on the same, I am pulling data from CRM.

In below code, these points are worth noting :-

1. Creating a Scope Object => $scope = new-object Microsoft.SharePoint.SPServiceContextScope $spContext;Because, default scope is not available in SharePoint Powershell.

   2. Setting "_ID" field, this field is used for binding. And is generated       automatically and an encrypted value is filled in it.

$encodedEntity = [Microsoft.SharePoint.BusinessData.Infrastructure.EntityInstanceIdEncoder]::EncodeEntityInstanceId($objArray); $listItem["OpportunityGroup_ID"] = $encodedEntity; 

function SetOpportunityData($listItem, [string]$oppNumber, [string]$siteUrl, [string]$ectNameSpace, [string]$ectName)
{

   
   $farm = [Microsoft.SharePoint.Administration.SPFarm]::Local;  
   $service = $farm.Services | where -FilterScript {$_.GetType() -eq [Microsoft.SharePoint.BusinessData.SharedService.BdcService]}  
   $spContext = Get-SPServiceContext -Site $siteUrl;  
   $catalog = $service.GetDatabaseBackedMetadataCatalog($spContext);  
   #Scope starts - Very Important  
   $scope = new-object Microsoft.SharePoint.SPServiceContextScope $spContext;    
   #$ectName = "OpportunityGroup";  
   $entity = $catalog.GetEntity($ectNameSpace, $ectName);  
   $LobSysteminstance = $entity.GetLobSystem().GetLobSystemInstances()[0].Value;  
   $filters = $entity.GetDefaultFinderFilters();  
   if(-not([string]::IsNullOrEmpty($oppNumber)))  
   {  
    $wcFilter = [Microsoft.SharePoint.BusinessData.Runtime.WildcardFilter]$filters[0];  
    $wcFilter.Value = $oppNumber;  
   }  
   $enumerator = $entity.FindFiltered($filters, $LobSysteminstance);  
   $dataTable = $entity.Catalog.Helper.CreateDataTable($enumerator);  
   #write-host $dataTable.Rows.Count;  
   #Scope Ends  
   $scope.Dispose();  
   if($dataTable.Rows.Count -lt 1) { write-host "(" $oppNumber ") => Not foound in extrnal system!!"; }  
   else   
   {  
     $handleRow = $dataTable.Rows[0];  
     [Microsoft.SharePoint.SPBusinessDataField]$externalDataField = [Microsoft.SharePoint.SPBusinessDataField]($listItem.Fields["Opportunity Data"]);  
     #Set the "external" [Opportunity Data] field value  
     $listItem[$externalDataField.InternalName] = $oppNumber;  
     $listItem["Opportunity Data: Opportunity Number"] = $oppNumber;   
     [System.Object]$objArray = @($oppNumber);  
     $encodedEntity = [Microsoft.SharePoint.BusinessData.Infrastructure.EntityInstanceIdEncoder]::EncodeEntityInstanceId($objArray);  
     $listItem["OpportunityGroup_ID"] = $encodedEntity;  
     $listItem["Opportunity Data: SEO Number"] = $handleRow["xyz_seonumber"];   
   }  
}

All the Web Services available in SharePoint 2010

SharePoint Foundation 2010 :

Has a range of "WebServices" available, but we often use Lists.asmx and few more.

Though with SharePoint 2013 _api calls have become more important, I am listing down all the available ".asmx" s for future reference.

For SharePoint  Server 2010 there are some more, which i'll list later.

Web Service	Url for Calling the WebService	Description
WebSvcAdmin	http://Site/_vti_bin/Admin.asmx	Provides methods for managing a deployment of SharePoint Foundation, such as for creating or deleting sites.
WebSvcAlerts	http://Site/_vti_bin/Alerts.asmx	Provides methods for working with alerts for list items in a SharePoint Foundation site.
WebSvcAuthentication	http://Site/_vti_bin/Authentication.asmx	Provides classes for logging on to a SharePoint Foundation site that is using forms-based authentication.
WebSvcBdcAdminService	http://Site/_vti_bin/BdcAdminService.asmx	Provides methods that can be used to import and export Business Data Connectivity Services (BDC) models.
WebSvcCellStorage	http://Site/_vti_bin/CellStorage.asmx	Enables client computers to synchronize changes made to shared files that are stored on a server.
WebSvcCopy	http://Site/_vti_bin/SvcCopy.asmx	Provides methods for copying items between locations in SharePoint Foundation.
WebSvcdiagnostics	http://Site/_vti_bin/diagnostics.asmx	Enables client computers to submit diagnostic reports that describe application errors that occur on the client.
WebSvcDspSts	http://Site/_vti_bin/DspSts.asmx	Provides a method for performing queries against lists in SharePoint Foundation.
WebSvcDWS	http://Site/_vti_bin/DWS.asmx	Provides methods for managing Document Workspace sites and the data they contain.
WebSvcForms	http://Site/_vti_bin/Forms.asmx	Provides methods for returning forms used in the user interface when working with the contents of a list.
WebSvcImaging	http://Site/_vti_bin/Imaging.asmx	Provides methods that enable you to create and manage picture libraries.
WebSvcLists	http://Site/_vti_bin/Lists.asmx	Provides methods for working with lists and list data.
WebSvcMeetings	http://Site/_vti_bin/Meetings.asmx	Provides methods that enable you to create and manage Meeting Workspace sites.
WebSvcPeople	http://Site/_vti_bin/People.asmx	Provides methods for working with security groups.
WebSvcPermissions	http://Site/_vti_bin/Permissions.asmx	Provides methods for working with the permissions for a site or list.
WebSvcSharedAccess	http://Site/_vti_bin/SharedAccess.asmx	Provides a method that determines whether a document is being coauthored.
WebSvcsharepointemailws	http://Site/_vti_bin/sharepointemailws.asmx	Provides methods for remotely managing distribution groups.
WebSvcSiteData	http://Site/_vti_bin/SiteData.asmx	Provides methods that return metadata or list data from sites or lists in SharePoint Foundation.
WebSvcsites	http://Site/_vti_bin/sites.asmx	Provides methods for working with Web sites.
WebSvcspsearch	http://Site/_vti_bin/spsearch.asmx	Provides methods for remotely performing searches within a SharePoint Foundation deployment.
WebSvcUserGroup	http://Site/_vti_bin/UserGroup.asmx	Provides methods for working with users and groups.
WebSvcVersions	http://Site/_vti_bin/Versions.asmx	Provides methods for managing file versions.
WebSvcviews	http://Site/_vti_bin/views.asmx	Provides methods for working with list views.
WebSvcwebpartpages	http://Site/_vti_bin/webpartpages.asmx	Provides methods to send and retrieve Web Part information to and from Web services.
WebSvcWebs	http://Site/_vti_bin/Webs.asmx	Provides methods for working with Web sites and content types.

Impersonation for Windows User c#

Problem Statement:
Often we have to execute a code with elevated rights as the current user(Windows Account) might not have access to the resource. One scenario could be a console application trying to update a Sql Server database but the user might not have appropriate access.

The solution to this is WindowsIdentity.Impersonate() function available. But it turns out that it's not that easy to use. You might run into issue which make it tough to implement.

Solution: 
I have a class for you which will take care of all the implementation issues.

Class: 
public class ImpersonateUser
    {
        // Fields
        private WindowsImpersonationContext _impersonatedUser;
        private IntPtr _tokenHandle = new IntPtr(0);

        // Methods
        [DllImport("kernel32.dll", CharSet = CharSet.Auto)]
        public static extern bool CloseHandle(IntPtr handle);

        [PermissionSet(SecurityAction.Demand, Name = "FullTrust")]
        public void Impersonate(NetworkCredential credentials)
        {
            this.Impersonate(credentials.Domain, credentials.UserName, credentials.Password);
        }

        [PermissionSet(SecurityAction.Demand, Name = "FullTrust")]
        public void Impersonate(string domainName, string userName, string password)
        {
            this._tokenHandle = IntPtr.Zero;
            if (!LogonUser(userName, domainName, password, 2, 0, ref this._tokenHandle))
            {
                throw new Win32Exception(Marshal.GetLastWin32Error());
            }
            this._impersonatedUser = new WindowsIdentity(this._tokenHandle).Impersonate();
        }

        [DllImport("advapi32.dll", SetLastError = true)]
        public static extern bool LogonUser(string lpszUsername, string lpszDomain, string lpszPassword, int dwLogonType, int dwLogonProvider, ref IntPtr phToken);

        public void Undo()
        {
            if (this._impersonatedUser != null)
            {
                this._impersonatedUser.Undo();
            }
            if (this._tokenHandle != IntPtr.Zero)
            {
                CloseHandle(this._tokenHandle);
            }
        }
    }

==============================================================

How To Use This Class :
Step 1. Create Impersonate Class object

            ImpersonateUser impersonate = new ImpersonateUser();

Step 2. Call Impersonate Method (This will change the current WindowsIdentity)
            impersonate.Impersonate(new NetworkCredential("userid", "password"));

            OR
           

 impersonate.Impersonate("domainName", "userid", "password"));

Step 4. //Operation on your database.


Step 4. Undo the Impersonation After JOB is done
               if (impersonate != null)
               { impersonate.Undo(); }

Note: If You want to check the WindowsIdentity in between these steps : Use below code.
 using (WindowsIdentity identity = WindowsIdentity.GetCurrent())
 {
       string windowsLoginName = identity.Name;
       Console.WriteLine(string.Format("Current User Login Name: {0}", windowsLoginName));
  }
================================================================
I am sure you will find this class useful in your implementation.
Happy Coding :)

Powershell to upload files to SharePoint document library

function UploadFileInLibrary           
{           
    [CmdletBinding()]           
    Param([Parameter(Mandatory=$true,ValueFromPipeline=$true)][string]$webUrl,[Parameter(Mandatory=$true)][string]$ImportLibTitle,[Parameter(Mandatory=$true)][string]$FilePath)               
  
    Start-SPAssignment -Global
    
    #Get handle of PLM CSS web object
    $spSite = Get-SPSite -Identity $webUrl           
    $spWeb = $spSite.OpenWeb()

   try
   {         
     $spWeb.AllowUnsafeUpdates = $true;           

     #Get List handle
     $List = $spWeb.Lists.TryGetList($ImportLibTitle) 
    
     #Get File handle       
     $FileName = $FilePath.Substring($FilePath.LastIndexOf("\")+1)           
     $File= Get-ChildItem $FilePath     
    
     #Read File Stream                           
     $fileStream = ([System.IO.FileInfo] (Get-Item $File.FullName)).OpenRead()
              
     #Add file           
     write-host -NoNewLine -f yellow "Copying file " $File.Name " to " $List.RootFolder.ServerRelativeUrl "..."           
     [Microsoft.SharePoint.SPFile]$spFile = $List.RootFolder.Files.Add($List.RootFolder.Url + "/" + $File.Name, [System.IO.Stream]$fileStream, $true)           
     write-host -f Green "...Success!"
              
     #Close file stream           
     $fileStream.Close()
    
     #Update Item         
     write-host -NoNewLine -f yellow "Added file " $spFile.Name "... to the library "  $ImportLibTitle         
     $spFile.Item.Update() 
    
     #Success         
     write-host -f Green "...Success!"               
   }
   catch
   {
     write-host -f Red "...Failure!"
     $ErrorMessage = $_.Exception.Message
     Write-Host $ErrorMessage -BackgroundColor Red
   } 
   finally
   {
     $spWeb.AllowUnsafeUpdates = $false;
     $spWeb.Dispose()
     $spSite.Dispose()
     Stop-SPAssignment -Global
   }         
}

#Begin Code
cls

asnp "*sh*" #This will add all SnapIns that contain "sh". In most cases, this is just the Microsoft.SharePoint.PowerShell one.

#Read params passed to the powershell
$WebUrl = $args[0] #example: http://*****/sites/YourSite/
$FilePath = $args[1] #example: C:\Users\User\Desktop\FillerFiles\Example.csv
$ImportLibTitle = $args[2] #example: "Title"

#call Import function
UploadFileInLibrary $WebUrl $ImportLibTitle $FilePath

Capturing and Modifying SharePoint Alerts

In SharePoint when we overwrite Alert emails. We have to also register our Alert Template file, which is deployed to SharePoint hive.

Usually, we are provided with "stsadm" commands to register this alert template file.

But we can also do it with c#.

Write a feature which is "Site" Scope

 var AlertTemplateFile = SPUtility.GetCurrentGenericSetupPath(@"TEMPLATE\XML\YourFolder\YourAlertTemplates.xml");

                SPSite oSPSite = properties.Feature.Parent as SPSite;

                SPWebApplication oSPWebApplication = SPWebApplication.Lookup(new Uri(oSPSite.Url));

                SPWebService oSPWebService = (SPWebService)oSPWebApplication.Parent;

                new SPAlertTemplateCollection(oSPWebService).InitAlertTemplatesFromFile( AlertTemplateFile );

PowerShell to update content type in SharePoint

function LoadSharePointSnapin
{
  $snapin = "Microsoft.SharePoint.PowerShell"

  if ((get-pssnapin $snapin -ea "silentlycontinue") -eq $null)
  {
    Write-Host -ForeGround Green "Loading SharePoint Snap-In $snapin."
    Add-PSSnapin $snapin
  }
}

try
{
  write-host -ForeGround Green "** Start Updating Content Type **"
  LoadSharePointSnapin
  Start-SPAssignment -Global

  $spSiteUrl = read-host "Enter Your Site Collection Url  "
  $spSite =  Get-SPSite -Identity $spSiteUrl
  $rootWeb = $spSite.RootWeb

  $MyListContentTypes = $rootWeb.ContentTypes                                                         
  if ($MyListContentTypes -ne $null)
    {
         $ct = $rootWeb.ContentTypes["Content Type Name"]                                     
         if($ct)                                                           
         {
             $ct.FieldLinks[$ct.Fields["Field1"].Id].Required = $False
      $ct.FieldLinks[$ct.Fields["Field2"].Id].Required = $False
      $ct.FieldLinks[$ct.Fields["Field3"].Id].Required = $False
             $ct.Update($true)
         }     
    }     
  write-host -ForeGround Green "** End **"
  Stop-SPAssignment -Global
}
catch
{
  Write-Host ""

  If ($_.FullyQualifiedErrorId -ne $null)
  {
    Write-Error $_.Exception.Message
  }
}

_spPageContextInfo in SharePoint

This is a simple object that you will find on every SharePoint page. It will give you below information.

sample code:

if (_spPageContextInfo != null) {
            if (_spPageContextInfo.pageListId != null) {
                var pageListId = _spPageContextInfo.pageListId;
            }
            if (_spPageContextInfo.siteAbsoluteUrl && _spPageContextInfo.siteAbsoluteUrl != '') {
               var siteAbsoluteUrl = _spPageContextInfo.siteAbsoluteUrl
            }
        }

Rest API Implementation Basics in SharePoint

REST API is a great new addition in SharePoint. Let's assume a scenario for the implementation.

Scenario: 

Suppose on a page/webpart you have a button which you want to show to users belonging to a particular group only. How to implement this in REST. 

$(document).ready(function(){

    HandleButtonHiding();

});

var hideDelete = true;

function  HandleButtonHiding () {
    $.ajax({
        url: _spPageContextInfo.webAbsoluteUrl + "/_api/web/CurrentUser",
        method: "GET",
        headers: { "Accept": "application/json; odata=verbose" },
        success: function (data) {
            //Now Get all Groups to iterate
            getCurrentUserGroupColl(data.d.Id);
        },
        error: function (data) {
            failure(data);
        }
    });

}

function getCurrentUserGroupColl(UserID) {
    $.ajax({
        url: _spPageContextInfo.webAbsoluteUrl + "/_api/web/GetUserById(" + UserID + ")/Groups",
        method: "GET",
        headers: { "Accept": "application/json; odata=verbose" },
        success: function (data) {
            /* get all group's title of current user. */
            var results = data.d.results;
            for (var i = 0; i < results.length; i++) {
                var grpTitle = results[i].Title;
                if (grpTitle.indexOf("Your Group Title") != -1) {
                       hideDelete = false;
                    break;
                }
            }
               if(hideDelete){
                  $('button#xyz').css("display","none");
               }
        }
    });

SPGridView Multi Column Filter and Sort

By default , the SPGridView doesn’t allow you to implement filters on multiple columns. Applied filter gets removed, if we apply filter on any other column. Similar, issues are there for sorting.

Points taken care in below code:

•          Multi Column Filter/Sort
•          Multiple SPGridViews on the same page
•          Clear Filter only clears the filter on one column, rest all filters remain
•          Clear Filter is enabled, for all filtered columns
•          Incase of multiple SPGridViews on same page, cross sorting/filtering issue solved, even if column names are same.

Download code from here - https://gallery.technet.microsoft.com/Multi-Column-Filter-and-6765a863

EXECUTE permission was denied on the object 'proc_putObjectTVP'

Below information helped me a lot.  Worth sharing.

one or more of the following might be the cause:

• The service account to which SharePoint is set does not have sufficient permissions to the database to which it is trying to connect.
• The service account is not set up properly in SharePoint.
• When using least privilege setup of the Farm.

Please perform the steps below and test the issue again:

1. Expand Databases then expand the SharePoint_Config Database.
2. Expand Security -> Roles -> Database Roles
3. Find WSS_Content_Application_Pools role, right click it, and select Properties
4. Click on Securables and click Search
5. Next click Specific objects and click OK
6. Click Object Types and select Stored Procedures. Click OK
7. Add the Stored Procedure 'proc_putObjectTVP' and click OK (if it does not automatically grant it exec permission; you need to click the checkbox on "execute" and save it)

SharePoint Server Object Model Class Diagram

This diagram will help you visualize the classes in SharePoint server object model.