CRUD operations on Provider Hosted SharePoint Add-In to Azure SQL Database

Hi All,

This post is about connecting a SharePoint provider hosted add-in deployed on Azure to a Azure SQL DB for performing basic CRUD operations.


  • Microsoft Azure account.
  • Office 365 account
  • Visual Studio 2015/2017 with SharePoint extensions.
  • A empty provider hosted add-in with ACS mode created and deployed on an Azure web app.


  • Create Azure SQL DB:
    • Within Azure portal, click New –> Databases –> SQL Database
    • Enter SQL server details.



  • Update DB server firewall to allow client IP access to the DB
    • Go to list of DBs in Azure, select the current DB – Select ‘Set server firewall’
    • Hit ‘Add client IP’ and then hit Save button.



  • Modify Provider hosted add-in to create model  from newly created DB:
    • Within project, go to Project –> Models –> Right click add new item. Select ADO.NET Entity data model from Data tab.
    • Select EF Designer from Database option to choose model contents
    • Provide correct Azure connection details to establish the correct connection with newly created Azure SQL DB.
  • Modify Provider hosted add-in to create controller from newly created DB:
    • Within project, go to Project –> Controllers –> Right click add controller 


  • Next add link in the layouts page for user to click from the application.


Next when running the SharePoint App, and clicking Customers link, you should be able to see all the data on the screen as shown below:


SharePoint 2013 MySite page opening blank for a single user

Sharing another live project issue that I faced with one of my customers..

A single user when trying to open SharePoint 2013 Mysite page, was getting a blank page. This was happening on some other Mysite pages also.

Also user was sometimes receiving error:

System.ArgumentException: calType
at Microsoft.SharePoint.Utilities.SPIntlCal.GetLocalCalendar(SPCalendarType calType)


  • Go to My Site Settings in Central Administration
  • Edit the user profile for the user having the issue
  • The setting for ‘Set your calendar’ would be blank
  • Set this to a value like – Gregorian
  • Save the profile
  • User would be able to access the My site pages.

SharePoint Crawl DB size increasing @20GB per day

Recently, one of my SharePoint farms faced an issue where the volume utilization of DB server (SharePoint crawl database) went upto 99% within 10 days.

– Looking at the crawl DB log in SharePoint, the incremental crawl was running every 1.5 hours with 0 success and 100% failures.

– There were about 961K errors with each incremental crawl scheduled at every 2 hours.

– There is a temporary folder that Crawl uses to place files temporarily on the server. The path is C:\Users\”SPAdminAccount”\AppData\Local\Temp\gthrsvc_OSearch14\

– I found out that this temporary folder was somehow missing / deleted.


On creating the above mentioned folder manually, the success rate of crawl went high and there were very less failures (as expected)

Post this, the space utilization went about 10 MB a day which is expected during crawl.

CAML Designer 2013

For any SharePoint developer, CAML Query builder has always been a tool used very frequently for development of custom SharePoint solutions.

With SharePoint 2013 CAML Designer:

  • you can build CAML queries for single lists
  • you can build queries that can be executed with SPSiteDataQuery
  • beside the pure CAML queries, you can also get code snippets for the server-side object model, the .NET client-side object model, the JavaScript client-side object model and last but not least code snippets when working with REST.
  • Autogenerate the actual CAML Query
  • Autogenerate Server OM code
  • Autogenerate CSOM .NET code
  • Autogenerate CSOM REST code
  • Autogenerate Web Service code
  • Autogenerate PowerShell code

Map SharePoint library to Network Drive

Sometimes it becomes necessary to map a SharePoint document library as a network drive. This may be a customer requirement, or developer requirement to manage the SharePoint folder structure.

Sharing the steps below for the same:

Step 1:
Note down the document library’s site path which needs to be mapped as a network drive.


Step 2:
Open File explorer, Right click, Select – Add Network Location

Step 3:
Click Next, and select option – Choose a custom network location, select Next.

Step 4:
Enter the SharePoint site URL. Click Next

Step 5:
It should prompt for credentials to connect to the SharePoint site. Enter Administrator credentials along with domain name.
It will ask for a name to be provided for this network drive location. Provide a custom name e.g. Finance Site.

Press Next.

Step 6:
It should now open the mapped drive location along with lots of folders. These folders represent all the lists and libraries contained in this SharePoint site.
You should also be able to view the Document library here.

Create custom page layouts using Custom content type using CAML

At times, we come across a requirement to have custom page layouts and use those page layouts to create pages in the site. This needs to be done using CAML.
Lets take an example where you are creating a site definition for a customer, and the site should have a home page and some internal pages. Admin can create more internal pages with same layout as of the existing internal pages. In this scenario, we can create 2 custom page layouts within the site definition say Home Page & Inner Page, and then create new pages based on these page layouts.
Here is how we achieve this:
Open Visual Studio 2010 and create a new project (Empty SharePoint project)
Step 1:
Create Custom content type derived from Article page content type.
  •    Right click the project and click Add -> New Item
  • Select Content Type from the list.
  • Give a suitable name for the content type.
  • Click Add.
  • Then it will ask, which base content type should your custom content type be inherited from. Select Article page from the list (In our case, it is article page. This can be inherited from any other type based on requirements)
  • Click Finish.
  • A feature with a content type is created in your solution. Open the Elements.xml file and you can update the properties of the content type like Name, Group, Description.
  • Notice the Content type ID generated by Visual Studio. This is a unique ID and will be used when we create page layouts using this content type.
 <?xml version=1.0 encoding=utf-8?>
  <!– Parent ContentType: Article Page (0x010100C568DB52D9D0A14D9B2FDCC96666E9F2007948130EC3DB064584E219954237AF3900242457EFB8B24247815D688C526CD44D) –>
  <ContentType ID=0x010100C568DB52D9D0A14D9B2FDCC96666E9F2007948130EC3DB064584E219954237AF3900242457EFB8B24247815D688C526CD44D00f2f3fc2629204643b44911a79fa95814
               Group=Custom Content Types
               Description=My Custom Content Type
  • Now the content type is created and on deploying this, we can see out custom content type in the content type gallery on the site.
Step 2:
Create custom page layouts based on the custom content type:
  • Add a new module to the solution, and name it PageLayouts. 
  • Delete the Sample.txt file created automatically within the module.
  • Add 2 aspx pages (page layouts) with the name HomePage & InnerPage. The way to do this is by Clicking Add -> New Item -> Text File and then put the name as Home.aspx
  • Similarly add InnerPage.aspx.
  • Register the required namespaces and assemblies and then add web part zones on this page accordingly.
  • You can download these pages here.
  • Update the elements.xml file accordingly as shown below:

<?xml version=1.0 encoding=utf-8?>

  <Module Name=PageLayouts Url=_catalogs/masterpage>
    <File Path=PageLayouts\HomePage.aspx Url=HomePage.aspx Type=GhostableInLibrary >
      <Property Name=Title Value=Home Page />
      <Property Name=ContentType Value=$Resources:cmscore,contenttype_pagelayout_name; />
      <Property Name=PublishingPreviewImage Value=~SiteCollection/PublishingImages/HomePage.png />
      <Property Name=PublishingAssociatedContentType Value=;#$Resources:cmscore,contenttype_articlepage_name;;#0x010100C568DB52D9D0A14D9B2FDCC96666E9F2007948130EC3DB064584E219954237AF3900242457EFB8B24247815D688C526CD44D00f2f3fc2629204643b44911a79fa95814;# />
    <File Path=PageLayouts\InnerPage.aspx Url=InnerPage.aspx Type=GhostableInLibrary >
      <Property Name=Title Value=Inner Page />
      <Property Name=ContentType Value=$Resources:cmscore,contenttype_pagelayout_name; />
      <Property Name=PublishingPreviewImage Value=~SiteCollection/PublishingImages/InnerPage.png />
      <Property Name=PublishingAssociatedContentType Value=;#$Resources:cmscore,contenttype_articlepage_name;;#0x010100C568DB52D9D0A14D9B2FDCC96666E9F2007948130EC3DB064584E219954237AF3900242457EFB8B24247815D688C526CD44D00f2f3fc2629204643b44911a79fa95814;# />
  •  Deploy the solution. Activate the feature. Go to Master page and page layouts gallery. You can see HomePage and InnerPage Page layouts deployed there.

  • These custom page layouts can now be used to create new pages.