Using an existing database with Azure mobile app service

Most samples for the azure mobile app server (on the new portal) are for start-from-scratch Todo item list….however what if you already have a database? Perhaps its used by a seperate Azure web app and now you want to add a native app to it.  This article will show the steps needed to make your database work with Azure Mobile Apps.

1) First step turn off EF database initialisation:
In your startup class comment out:

//Database.SetInitializer(new myappInitializer());

and add:

Database.SetInitializer<myappContext>(null);

This will stop entity framework from trying to overwrite data/structure in the database.

2) Modify the database:

Azure needs Id column on each table. If you primary key column has  a different name you will need to rename it. Also the datatype will need to be nvarchar.

3) Finally add in the models into your app service but leave out the Id column. This will be managed by Azure Mobile App service itself.

4) Add an InsertUpdateDelete trigger to each table that will be used by your TableControllers:

CREATE TRIGGER [dbo].[TR_dbo_TABLENAMEHERE_InsertUpdateDelete] ON [dbo].[TABLENAMEHERE] AFTER INSERT, UPDATE, DELETE AS BEGIN UPDATE [dbo].[TABLENAMEHERE] SET [dbo].[TABLENAMEHERE].[UpdatedAt] = CONVERT(DATETIMEOFFSET, SYSUTCDATETIME()) FROM INSERTED WHERE inserted.[Id] = [dbo].[TABLENAMEHERE].[Id] END

All done!

SharePoint 2016 Min Roles

The following table outlines each role that is available:

Server role Description
Front end Service applications, services, and components that serve user requests belong on front-end web servers. These servers are optimized for fast performance.
Application Service applications, services, and components that serve back-end requests, such as background jobs or search crawl requests, belong on Application servers. These servers are optimized for high throughput.
Distributed Cache Service applications, services, and components that are required for a distributed cache belong on Distributed Cache servers. Optionally, you can configure these servers to also load balance farm traffic using the SharePoint Request Manager.
Search Service applications, services, and components that are required for search belong on Search servers.
Custom Custom service applications, services, and components that do not integrate with MinRole belong on Custom servers. The farm administrator has full control over which service instances can run on servers assigned to the Custom role. MinRole will not control which service instances are provisioned on this role.
Single-Server Farm Service applications, services, and components required for a single-machine farm belong on a Single-Server Farm. A Single-Server Farm is meant for development, testing, and very limited production use. A SharePoint farm with the Single-Server Farm role cannot have more than one SharePoint server in the farm.

Important

Important

Important:

The Standalone Install mode is no longer available in SharePoint Server 2016 IT Preview. The Single-Server Farm role replaces the Standalone Install mode available in previous SharePoint Server releases. Unlike Standalone Install, the SharePoint administrator must separately install and prepare Microsoft SQL Server for SharePoint. The SharePoint administrator must also configure the SharePoint farm services and web applications, either manually or by running the Farm Configuration Wizard.

Tutorial: Setup SharePoint 2016 on Azure

Follow this tutorial to setup a development environment for 2016 on Azure.  The setup is essentially the same as it was for 2013 with the exception of Min-Roles (we will discuss this later).  Assuming its the first time for you using Azure, the steps are:

  1. Setup virtual network and DNS
  2. Create a new Cloud Service
  3. Create three VMs
    1. Domain Controller
    2. SQL
    3. SharePoint
  4. Create new AD forest on DC VM and domain join the VMs
  5. Create farm, setup and services accounts and add to SQL server logins and add as Administrators on SharePoint VM
  6. Install SharePoint

 

Setup virtual network and DNS

Login to your Azure portal at manage.windowsazure.com then click Networks and New:

new network

Create a new virtual network with the following settings. As for Location I’ve selected West Europe, choose whatever works for you. Note we have selected to create a new DNS server, if you’ve already got one you can use that.

newvnet

Once created go to Configure and create 3 subnets for AD, SQL and SharePoint and press Save.

subnets

 

Create Cloud Service

For information on cloud services see link.

On the left hand navigation select Cloud Services > New > Quick  create.   Enter a unique url. Ive gone with isharepoint16.cloudapp.net

 

Create three VMs

Go to virtual machines section in azure portal and select New > From gallery.  Create the virtual machines with the following settings:

Domain controller:
OS: Windows Server 2012
VM name: Whatever you like, I chose DC1
Hardware: Standard tier, 2 cores 3.5 GB (A2)
Cloud service: Choose the one created earlier
Virtual network: Choose the one created earlier
Subnet: Choose the AD Subnet

server

SQL:
Machine: SQL Server 2014 SP1 Enterprise, Windows Server 2012 R2
VM name: Whatever you like, I chose sql1
Hardware: Standard tier, 4 cores 7 GB (A3)
Cloud service: Choose the one created earlier
Virtual network: Choose the one created earlier
Subnet: Choose the SQL Subnet

sqlserver

SharePoint 2016:
Machine: SharePoint Server 2016 IT Preview
VM name: Whatever you like, I chose sp1
Hardware: Standard tier, 4 cores  16 GB
Cloud service: Choose the one created earlier
Virtual network: Choose the one created earlier
Subnet: Choose the SharePoint Subnet

spserver

 

Create new AD forest on Domain Controller VM and domain join the VMs

Remote connect to the Domain controller VM, add AD DS features. After it installs promote create new forest and promote machine to domain controller.

Once done create AD accounts as SPFarm, SPSetup, SPServices

 

Add to SQL server logins

Connect to SQL server VM and open SQL management studio. Once open expand logins and add the 3 accounts above with Sysadmin role.

 

Install SharePoint

Connect to the SharePoint VM and add the 3 accounts as administrators on the machine.

Log off and log back in as the setup account.

Run the sharepoint configuration wizard just the same as you would for SP2013, with exception of Min-Roles. We will create single server farm with all the roles however in production environment you would need to select Custom and choose the roles that work for you.

Once complete you have SharePoint 2016 ready to go.  Access it using your cloud service url.

Get a users followed sites using JSOM in SP2013 or SharePoint Online

SP.SOD.executeOrDelayUntilScriptLoaded(loadUserData, ‘SP.UserProfiles.js’);

function loadUserData()

{

var clientContext = SP.ClientContext.get_current();

 

var followingManager;

var followed;

 

 

followingManager = new SP.Social.SocialFollowingManager(clientContext);

 

 

Next we need to get the sites followed by the current user. For this we need to pass the actor ID of 4. For a list of all actor Ids see this.

 

followed = followingManager.getFollowed(4);

clientContext.executeQueryAsync(showFollowed, requestFailed);

​

}
​

function showFollowed() {

for (var i = 0; i < followed.length; i++) {

//get whatever you need from the followed array

}

​}

 

function requestFailed(sender, args) {

$(‘#message’).html(‘Error: ‘ + args.get_message());

}

Configure SharePoint 2013 for App Development by setting up isolated domain

If your not sure what needs to be configured for app development on ​SP2013 on premise or you receive the “Apps are disabled on this site” error, then follow these steps:

  1. Ensure the user profile service application is running and has at least one profile in it
  2. Ensure the application management service app is running.
  3. Ensure timer service is running: net start sptimerv4
  4. Ensure you have created an isoloated domain

SharePoint hosted apps run must run in their own domain. For Visual Studio to be able to deploy apps you need to first setup the domain. To do this run the following powershell scripts in the SharePoint Management Shell. Hopefully they should be self explanatory:

Set-SPAppDomain “apps.isharepoint.com”

Get-SPServiceInstance | where{$_.GetType().Name -eq “AppManagementServiceInstance

Get-SPServiceInstance | where{$_.GetType().Name -eq “AppManagementServiceInstance” -or $_.GetType().Name -eq “SPSubscriptionSettingsServiceInstance”}

 

The below scripts are to setipt the application pools. You need to provide a sharepoint managed account.

$account = Get-SPManagedAccount “domain\user”
$appPoolSubSvc = New-SPServiceApplicationPool -Name SettingsServiceAppPool -Account $account
$appPoolAppSvc = New-SPServiceApplicationPool -Name AppServiceAppPool -Account $account
$appSubSvc = New-SPSubscriptionSettingsServiceApplication –ApplicationPool $appPoolSubSvc –Name SettingsServiceApp –DatabaseName SettingsServiceDB $proxySubSvc = New-SPSubscriptionSettingsServiceApplicationProxy –ServiceApplication $appSubSvc
$appAppSvc = New-SPAppManagementServiceApplication -ApplicationPool $appPoolAppSvc -Name AppServiceApp -DatabaseName AppServiceDB
$proxyAppSvc = New-SPAppManagementServiceApplicationProxy -ServiceApplication $appAppSvc

 

Specify your tenant name by typing the following code in the SharePoint Management Shell:

Set-SPAppSiteSubscriptionName -Name “app” -Confirm:$false

Finally your isolated app domain to your bypass list in Internet Explorer

  1. In Internet Explorer, go to Tools.
  2. Choose Internet options.
  3. On the Connections tab, choose the LAN Settings
  4. Clear the Automatically detect settings check box.
  5. Select the Use a proxy server for your LAN check box.
  6. Choose the Advanced button, and then add *.YourAppDomain.com to the Exceptions
  7. Choose the OK
  8. Choose the OK button to close the Local Area Network (LAN) Settings dialog box.
  9. Choose the OK button to close the Internet Options dialog box.

You may get a 401 when launching you app. For this you’ll need to disable loop back checking:

To set the DisableLoopbackCheck registry key, follow these steps:

  1. Set the

DisableStrictNameChecking

registry entry to 1. For more information about how to do this, click the following article number to view the article in the Microsoft Knowledge Base:

281308

(http://support.microsoft.com/kb/281308/ )

Connecting to SMB share on a Windows 2000-based computer or a Windows Server 2003-based computer may not work with an alias name

  1. Click Start, click Run, type regedit, and then click OK.
  2. In Registry Editor, locate and then click the following registry key:

HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Lsa

  1. Right-click Lsa, point to New, and then click DWORD Value.
  2. Type DisableLoopbackCheck, and then press ENTER.
  3. Right-click DisableLoopbackCheck, and then click Modify.
  4. In the Value data box, type 1, and then click OK.
  5. Quit Registry Editor, and then restart your computer.

 

Using Launcher.LaunchFileAsync to launch file from Isolated File Storage on the Window Phone 8 SDK

Assuming you have saved your file to the Isolated File Storage like so:

using (IsolatedStorageFile ISF = IsolatedStorageFile.GetUserStoreForApplication())

{

//create a local file with the same name using the input stream and saving through the output stream

using (IsolatedStorageFileStream outputStream = ISF.CreateFile(fileName))

{

try

{

byte[] bytes = new byte[inputStream.Length];

inputStream.Read(bytes, 0, (int)inputStream.Length);

outputStream.Write(bytes, 0, bytes.Length);

outputStream.Flush();

}

catch

{}

}

loadingProgressBar.IsVisible = false;

 

//have saved our file now lets launch it

LaunchFile(fileName);

}

 

private async void LaunchFile(string fileName)

{

var file = await ApplicationData.Current.LocalFolder.GetFileAsync(fileName);

await Windows.System.Launcher.LaunchFileAsync(file);

}

Use C# CSOM ClientContext to download file from SharePoint document library or OneDrive for Business

This post will show how to download a file from any document library.

 

Using SharePoint SDK in Windows Phone 8 and the ISF (Isolated File Storage):

The steps:

  1. Get the relative url of the file
  2. Use the ClientContext object to download the file to the ISF
  3. Open the file in the phones office apps

The code:

Assuming you have the Item object:

string fileRef =

Stream inputStream;

private void BMSStackPanel_Tap(object sender, System.Windows.Input.GestureEventArgs e)

{

loadingProgressBar.IsVisible = true;

//get the relative url of the file

string fileRef = item[“FileRef”].ToString();

//get just the filename part

string fileName = fileRef.Substring(fileRef.LastIndexOf(“/”)+1);

 

Microsoft.SharePoint.Client.File.OpenBinaryDirect(Context, fileRef,

(object eventSender, OpenBinarySucceededEventArgs eventArgs) =>

{

if (eventArgs.Stream != null)

inputStream = eventArgs.Stream; //instantiating stream using the sharepoint file stream

Deployment.Current.Dispatcher.BeginInvoke(() =>

{

using (IsolatedStorageFile ISF = IsolatedStorageFile.GetUserStoreForApplication())

{

//create a local file with the same name using the input stream and saving through the output stream

using (IsolatedStorageFileStream outputStream = ISF.CreateFile(fileName))

{

try

{

byte[] bytes = new byte[inputStream.Length];

inputStream.Read(bytes, 0, (int)inputStream.Length);

outputStream.Write(bytes, 0, bytes.Length);

outputStream.Flush();

 

}

catch (Exception ex2)

{

}

}

loadingProgressBar.IsVisible = false;

LaunchFile(fileName);

 

 

}

});

}, null); //im passing null for the failed event. this should be handled properly though

}

private async void LaunchFile(string fileName)

{

var file = await ApplicationData.Current.LocalFolder.GetFileAsync(fileName);

await Windows.System.Launcher.LaunchFileAsync(file);

}

 

 

Using Client OM in C#:

Assuming you have the Item object:

//get the URL of the file you want:

var fileRef = item[“FileRef”];

//get the file contents:

FileInformation fileInfo = File.OpenBinaryDirect(context, fileRef.ToString());

using (var memory = new MemoryStream()) {

byte[] buffer = new byte[1024 * 64];

int nread = 0;

while ((nread = fileInfo.Stream.Read(buffer, 0, buffer.Length)) > 0) {

memory.Write(buffer, 0, nread);

}

memory.Seek(0, SeekOrigin.Begin);

// … here you have the contents of your file in memory,

// do whatever you want

}

 

In JavaScript:

  1. Globally declare the content and web objects as shown below.

    var context = SP.ClientContext.get_current(); //gets the current context

  2. var web = context.get_web(); //gets the web object

Now write the function to read a file in a Document Library.
function readFile() {

context.load(web);

context.executeQueryAsync(OnreadServerRelativeURL, readFileFailure);

}

function OnreadServerRelativeURL() {

var fileUrl = web.get_serverRelativeUrl() + “/MyLibrary/MyTextFile.txt”;

$.ajax({

url: fileUrl,

type: “GET”

})

.done(Function.createDelegate(this, readFileSuccess))

.error(Function.createDelegate(this, readFileFailure));

}

function readFileSuccess(data) {

//do whatever you need to with the data

alert(data);

}

function readFileFailure(sender, args) {

debugger;

alert(‘Failed to read a file. Error:’ + args.get_message());

}

 

Use CSOM ClientContext to fetch documents from users personal OneDrive for Business

Say I want to provide the user a list of their personal files in their OneDrive for Business site – this is how to do it.  Note: this is for OneDrive for Business, not the standard OneDrive – for that see this article.

In essence each users personal files are in their own site collection. So we need to get the web url. You can see what the web url should be by browsing to your own OneDrive in the browser.  In my example Im going to use:

https://isharepoint-my.sharepoint.com/personal/luqman_isharepoint_onmicrosoft_com

So that makes sense but what we actually need is the users own personal url, not mine.  For this we need to get the current user object then fetch their email address.  Replace any periods and @ symbols characters with underscores.  For this we can use the main tenant url as we dont current know what the personal site collection url is:

ClientContext  Context = new ClientContext(https://isharepoint.sharepoint.com);​

Authenticator at = new Authenticator();

at.CookieCachingEnabled = true;

Context.Credentials = at;

web = Context.Web;

Context.Load(web);

currentUser = web.CurrentUser;

Context.Load(currentUser);

Context.ExecuteQuery();

string email = currentUserEmail;

 

The code above has been changed a bit from the actual to make it a bit clearer but you should get the idea of whats going on. Also its for windows phone 8 app, for a sharepoint you wont need to worry about the authentication.

OK, now that we have the email address we need to replace the characters to make it the same as users personal folder url:

email = email.Replace(“.”, “_”).Replace(“@”, “_”);

 

Next we get a ClientContext to the users personal site collection of:

https://isharepoint-my.sharepoint.com/personal/”+email+”/”);

Once we have that we need to access the Documents library. We do this jus the same way we would with any other document library and there are hundreds of articles online showing how to query list and libraries.

Develop Windows Phone 8 (WP8) SDK to fetch data from SharePoint Online or 2013

So you want to have a go at developing native windows phone apps that interact with SharePoint Online or even SharePoint 2013 on-premise (as long as your device browser has access to the site)….lets begin!

Pre-reqs:

  • OS – Windows 8/8.1 Pro or better
  • Windows Phone 8 or 8.1 SDK
    • If you go with the Windows Phone 8 SDK please ensure you update it to the current Update 4.  The older versions have issues debugging.
  • SharePoint SDK for Windows Phone

 

Create the project

Open Visual Studio Express for Windows Phone.

Click File > New Project > Visual C Sharp > Windows Phone > Empty SharePoint Project

 

I find it easier going with an empty SharePoint project and adding what you need rather than the SharePoint list project and then having to remove unnecessary stuff.

 

Explore the created files and folders

In particular App.xaml, the assets icons and the AppManifest in the properties.

 

Make the UI

OK so we get a rather plain looking xaml screen (MainPage.xaml) to start with. Lets make it look better by adding a pivot control or panorama. If you don’t know what they are check out this msdn blog.  My personal preference is a pivot control as i think the screen look a lot cleaner with it.  Im going to add 4 pivot items to the pivot control: Home, News, Documents, Sales thats because i have this data in sharepoint and want to show it in the app on a user interface of my choice rather than the standard boring sharepoint app which just shows standard lists.

<phone:Pivot Title=”My app” x:Name=”MainPivot” SelectionChanged=”MainPivot_SelectionChanged” >

<phone:PivotItem Header=”Home”>

<phone:PivotItem Header=”News”>

<phone:PivotItem Header=”Docs”>

<phone:PivotItem Header=”Sales”>

</phone:Pivot>

Im not going to go into the details of creating the UI but in essence the home pivot will have a nice looking grid with buttons and icons to switch the pivots. In the code above ive highlighted the SelectionChanged event. Since we have a lot of data in sharepoint we dont want to load all the tabs at once instead they will be loaded on demand.  Ive also added a progress indication to the xaml which will be set with IsVisible=true in the codebehind when loading content:

<shell:SystemTray.ProgressIndicator>

<shell:ProgressIndicator IsIndeterminate=”True” IsVisible=”True” Text=”Loading data …”

x:Name=”loadingPr

ogressBar” />

</shell:SystemTray.ProgressIndicator>

 

Id strongly recommend switch between Blend and VS when creating the UI. Blend is a lot easier.

 

 

Authenticate with SharePo

int

I want to login the authentication piece out of the way for the user right at the start.

 

For this ive added a Context property to the code.

 

private ClientContext m_Context;

public ClientContext Context

{

get

{

if (m_Context != null)

return m_Context;

m_Context = new ClientContext(“https://isharepoint.sharepoint.com”);

Authenticator at = new Authenticator();

at.CookieCachingEnabled = true; //Allows authenticator to save cookies for future usage

m_Context.Credentials = at;

return m_Context;

}

}

Ive added a Loaded event:

private void TitlePanel_Loaded_1(object sender, RoutedEventArgs e)

{

web = Context.Web;

Context.Load(web);

//you cannot authentication on the main thread, so call a new thread

//so you push the clientcontext loading into a new thread

System.Threading.ThreadPool.QueueUserWorkItem(new System.Threading.WaitCallback(LoginThreadCallback), Context);

}

private void LoginThreadCallback(object s)

{

var context = (ClientContext)s;

context.ExecuteQuery();//start thread work to authenticate and load sp lists

_syncContext.Post(

new SendOrPostCallback(

delegate(object state)

{

LoginThreadComplete();

}), null);

}

private void LoginThreadComplete()

{

loadingProgressBar.IsVisible = false;

}

 

Binding Data

Now that we have the Context object authenticated when the MainPivot_SelectionChanged event is triggered we can use that Context to load the data and bind it onto out PivotItems.

For example on my News pivotitem ive added a longlistselector with an itemtemplate of how i want each article to appear:

<phone:LongListSelector x:Name=”NewsList” ItemsSource=”{Binding ListData}”  >

<phone:LongListSelector.ItemTemplate>

<DataTemplate>

<StackPanel Margin=”0,5″>

<StackPanel Orientation=”Horizontal” Background=”#80FFFFFF”>

<Image Margin=”5,5″ Width=”120″ Height=”80″ Source=”{Binding ImageSrc}” />

<StackPanel>

<TextBlock Text=”{Binding Title}” Width=”300″ TextWrapping=”Wrap” FontSize=”{StaticResource PhoneFontSizeLarge}” />

<TextBlock Text=”{Binding ArticleDate}” />

</StackPanel>

</StackPanel>

</StackPanel>

</DataTemplate>

</phone:LongListSelector.ItemTemplate>

</phone:LongListSelector>

 

Next create a class (Model) to represent a News article:

public class Article

{

public string Title { get; set; }

public BitmapImage ImageSrc { get; set; }

public string ArticleDate { get; set; }

}

Thats our model complete.  In the below code we fetch the data and bind it to the longlistselector with the x:Name of NewsList.  Each article will also have an image from the PublishingImages folder which will have to be downloaded the Isolated File Storage and then added to the xaml.  Ive skipped out that part but theres lots of online resources for it.

private void LoadNewsArticles()

{

loadingProgressBar.IsVisible = true;

web = Context.Web;

spNewsList = web.Lists.GetByTitle(newsList);

Context.Load(spNewsList);

CamlQuery query = new CamlQuery();

query.ViewXml = “<View><Query><Where><IsNotNull><FieldRef Name=’HomePageText’ /></IsNotNull></Where><OrderBy><FieldRef Name=’Modified’ Ascending=’FALSE’ /></OrderBy></Query><ViewFields><FieldRef Name=’HomePageText’ /><FieldRef Name=’PublishingRollupImage’ /><FieldRef Name=’Modified’ /></ViewFields><RowLimit>10</RowLimit></View>”;

spNewsItems = spNewsList.GetItems(query);

 

Context.Load(spNewsItems);

 

System.Threading.ThreadPool.QueueUserWorkItem(new System.Threading.WaitCallback(NewsThreadCallback), Context);

}

 

private void NewsThreadCallback(object s)

{

var context = (ClientContext)s;

context.ExecuteQuery();

//send the data to the main thread so that it can update the UI

_syncContext.Post(

new SendOrPostCallback(

delegate(object state)

{

SetNewsData();

}), null);

}

 

protected void SetNewsData()

{

if (spNewsItems.Count > 0)

{

//create list of news articles

List<Article> source = new List<Article>();

foreach (ListItem item in spNewsItems)

{

if (item[“PublishingRollupImage”] != null)

{

string imagesrc = item[“PublishingRollupImage”].ToString();

imagesrc = imagesrc.Substring(imagesrc.IndexOf(“/”));

imagesrc = imagesrc.Substring(0, imagesrc.IndexOf(“\””));

string filename = imagesrc.Substring(imagesrc.LastIndexOf(“/”)+1);

 

DownloadFile(Context, imagesrc, filename);//download image to isolated from storage

BitmapImage image = ThumbImageFromIFS(filename);//getting image file from Isolated File Store

 

string homepagetext = item[“HomePageText”].ToString();

if (homepagetext.Length > 35)

{

homepagetext = homepagetext.Substring(0, 34) + “…”;

}

//add news article to the news articles list

source.Add(new Article() { Title = homepagetext, ImageSrc = image, ArticleDate = DateTime.Parse(item[“Modified”].ToString()).ToShortDateString() });

 

}

}

//set the long list selector source

NewsList.ItemsSource = source;

loadingProgressBar.IsVisible = false;

}

}

 

Thats all folks! Hopefully that all makes sense, if not leave a comment below and ill try to respond.