Azure DocumentDB Code Samples: How to use Azure DocumentDB

Hi,

I’ve been working a bit on DocumentDB and thought of posting some sample code on how to use it. It might save people time and energy. I have had to work around some issues and headaches.

 

Notice, there is one thing you should take care of. Define your functions with the async keyword and use the await keyword on async function calls. Failure to do this will result in hanging application code.

Also, make sure that you are not accidentally calling synchronous functions from the Task class or some other place that is related to an async call. This will also hang the application code. Calling the Wait() function is one of the, also calling the Result property in the wrong place will result in the same problem.

A quote on the problem from a site:

“If you call the async method on the SAME thread that you then call Result or Wait() on, you will probably deadlock because once the async task has finished, it will wait to re-acquire the previous thread but it can’t because the thread is blocked on the call to Result/Wait()

you can use async tasks and await to avoid this problem but there is also another clever trick, certainly in newer versions of the .net framework and that is to invoke your async task on another thread, not on the one you are calling your method with. It is as simple as this:

var task = Task.Run(() => myMethodAsync());

which involves the method on a thread from the thread pool. When your calling thread then waits and blocks using Wait() or Result, the async task will NOT need to wait for your thread, it will re-acquire the one from the threadpool, finish and signal your waiting thread to allow it to continue!” http://lukieb.blogspot.fi/2016/07/calls-to-azure-documentdb-hang.html

 


/// <summary>
/// Sample class to be used for object serialization when handling data to the DocumentDB
/// </summary>
public class MyDocumentDBDataContainer
{
public String Title { get; set; }
public byte[] FileData { get; set; }

public String FileName { get; set; }

public class InnerDataContainer
{
public String Title { get; set; }
public int SomeNumber { get; set; }
}

public InnerDataContainer InnerData { get; set; }
}

public partial class Form1 : Form
{
/// <summary>
/// The DocumentDB address, end point where it exists
/// </summary>
private const string EndpointUrl = "https://mydocumentdbtest.documents.azure.com:443/";

/// <summary>
/// This can be the primary key you get from the Azure DocumentDB settings UI
/// </summary>
private const string AuthorizationKey =
"";

/// <summary>
/// A temp object for holding the documentDB database for processing
/// </summary>
private Database database;

/// <summary>
/// Same as above but for a collection
/// </summary>
private DocumentCollection collection;

public Form1()
{
InitializeComponent();
}

private async void button1_Click(object sender, EventArgs e)
{
Stream myStream = null;
OpenFileDialog openFileDialog1 = new OpenFileDialog();

openFileDialog1.InitialDirectory = "c:\\";
openFileDialog1.Filter = "txt files (*.txt)|*.txt|All files (*.*)|*.*";
openFileDialog1.FilterIndex = 2;
openFileDialog1.RestoreDirectory = true;

// Open a file to get some byte data to upload into DocumentDB
if (openFileDialog1.ShowDialog() == DialogResult.OK)
{
try
{
if ((myStream = openFileDialog1.OpenFile()) != null)
{
using (myStream)
{
try
{
MemoryStream ms = new MemoryStream();
myStream.CopyTo(ms);
await CreateDocumentClient(ms, openFileDialog1.FileName);
}
catch (Exception ex)
{
Exception baseException = ex.GetBaseException();
Console.WriteLine("Error: {0}, Message: {1}", ex.Message, baseException.Message);
}
}
}
}
catch (Exception ex)
{
MessageBox.Show("Error: Could not read file from disk. Original error: " + ex.Message);
}
}
}

/// <summary>
/// This is the main work horse here. The function will create a database, a collection and a sample document if they do not exist.
/// NOTICE: This is very important, define you functions with the async keyword and use the await keyword on async function calls. Failure to do this will result in haning application code.
/// Also make sure that you are not accidentally calling synchronous functions from the Task class or some other place that is related to a async call. This will also hang the application code.
/// More on this: http://lukieb.blogspot.fi/2016/07/calls-to-azure-documentdb-hang.html
/// Also notice that documentDB uses "links" to identify things. You will run into DocumentDB objects and a property SelfLink. This seems to just be a way of how things work.
/// </summary>
/// <param name="fileDataStream"></param>
/// <param name="fileName"></param>
/// <returns></returns>
private async Task CreateDocumentClient(MemoryStream fileDataStream, String fileName)
{
// Create a new instance of the DocumentClient
var client = new DocumentClient(new Uri(EndpointUrl), AuthorizationKey);

var databaseID = "myDBTest";
var collectionID = "myCollectionTest";

// Get the database and if it does not exist create it
this.database = this.GetDatabase(client, databaseID);
if (database == null)
{
this.database = await CreateDatabase(client, databaseID);
}

// Get the collection and if it does not exist then create it
this.collection = this.GetDocumentCollection(client, collectionID);
if(this.collection == null)
{
this.collection = await this.CreateCollection(client, collectionID);
}

// Create a temp data container, pass forward to be created in DocumentDB
MyDocumentDBDataContainer data = new MyDocumentDBDataContainer() { Title = fileName, InnerData = new MyDocumentDBDataContainer.InnerDataContainer() { Title = "InnerDataTitle", SomeNumber = 1 }, FileData = fileDataStream.ToArray(), FileName = fileName };
var result = await this.CreateDocument(client, data);

// Get the newly created document. Notice: In these code examples I use a title but you can use any identifier you wish.
var dataFromDocumentDB = this.ReadDocument(client, data.Title);

// Re-Create the file from the byte data from the DocumentDB storage
File.WriteAllBytes(dataFromDocumentDB.FileName, dataFromDocumentDB.FileData);
}



#region DocumentManagement

private async Task<Document> DeleteDocument(DocumentClient client, String documentTitle)
{
var documentToDelete =
client.CreateDocumentQuery<MyDocumentDBDataContainer>(this.collection.SelfLink)
.Where(e => e.Title == documentTitle)
.AsEnumerable()
.First();

Document doc = GetDocument(client, documentToDelete.Title);

var result = await client.DeleteDocumentAsync(doc.SelfLink);
return result.Resource;
}

private async Task<Document> UpdateDocument(DocumentClient client, String documentTitle)
{
// Update a Document

var singleDocument =
client.CreateDocumentQuery<MyDocumentDBDataContainer>(this.collection.SelfLink)
.Where(e => e.Title == documentTitle)
.AsEnumerable()
.First();

Document doc = GetDocument(client, singleDocument.Title);
MyDocumentDBDataContainer employeUpdated = singleDocument;
singleDocument.InnerData.SomeNumber = singleDocument.InnerData.SomeNumber + 1;
var result = await client.ReplaceDocumentAsync(doc.SelfLink, singleDocument);

return result.Resource;
}

private Document GetDocument(DocumentClient client, string id)
{
return client.CreateDocumentQuery(this.collection.SelfLink)
.Where(e => e.Id == id)
.AsEnumerable()
.First();
}

private MyDocumentDBDataContainer ReadDocument(DocumentClient client, String documentTitle)
{
// Read the collection

//var data = client.CreateDocumentQuery<MyDocumentDBDataContainer>(this.collection.SelfLink).AsEnumerable();
//foreach (var item in data)
//{
// Console.WriteLine(item.Title);
// Console.WriteLine(item.FileData);
// Console.WriteLine(item.InnerData.Title);
// Console.WriteLine("----------------------------------");
//}

// Read A Document - Where Name == "John Doe"
var singleDocument =
client.CreateDocumentQuery<MyDocumentDBDataContainer>(this.collection.SelfLink)
.Where(e => e.Title == documentTitle)
.AsEnumerable()
.FirstOrDefault();

return singleDocument;

//Console.WriteLine("-------- Read a document---------");
//Console.WriteLine(singleDocument.Title);
//Console.WriteLine(singleDocument.FileData);
//Console.WriteLine(singleDocument.InnerData.Title);
//Console.WriteLine("-------------------------------");
}

private async Task<Document> CreateDocument(DocumentClient client, object documentObject)
{

var result = await client.CreateDocumentAsync(collection.SelfLink, documentObject);
var document = result.Resource;

Console.WriteLine("Created new document: {0}\r\n{1}", document.Id, document);
return document;
}

#endregion

private async Task<Database> CreateDatabase(DocumentClient client, String databaseID)
{
Console.WriteLine();
Console.WriteLine("******** Create Database *******");

var databaseDefinition = new Database { Id = databaseID };
var result = await client.CreateDatabaseIfNotExistsAsync(databaseDefinition);
var database = result.Resource;

Console.WriteLine(" Database Id: {0}; Rid: {1}", database.Id, database.ResourceId);
Console.WriteLine("******** Database Created *******");

return database;
}

private DocumentCollection GetDocumentCollection(DocumentClient client, String collectionID)
{
var collections = client.CreateDocumentCollectionQuery(database.CollectionsLink,
"SELECT * FROM c WHERE c.id = '" + collectionID + "'").AsEnumerable();
if(collections.Count() > 0)
return collections.First();

return null;
}

private async Task QueryDocumentsWithPaging(DocumentClient client)
{
Console.WriteLine();
Console.WriteLine("**** Query Documents (paged results) ****");
Console.WriteLine();
Console.WriteLine("Quering for all documents");

var sql = "SELECT * FROM c";
var query = client.CreateDocumentQuery(collection.SelfLink, sql).AsDocumentQuery();

while (query.HasMoreResults)
{
var documents = await query.ExecuteNextAsync();

foreach (var document in documents)
{
Console.WriteLine(" Id: {0}; Name: {1};", document.id, document.name);
}
}

Console.WriteLine();
}

private Database GetDatabase(DocumentClient client, String databaseID)
{
//bool databaseExists = false;
Console.WriteLine();
Console.WriteLine();
Console.WriteLine("******** Get Databases List ********");

var databases = client.CreateDatabaseQuery().ToList();

foreach (var database in databases)
{
Console.WriteLine(" Database Id: {0}; Rid: {1}", database.Id, database.ResourceId);
return database;
}

Console.WriteLine();
Console.WriteLine("Total databases: {0}", databases.Count);

return null;
}

private async Task<DocumentCollection> CreateCollection(DocumentClient client, string collectionId, string offerType = "S1")
{

Console.WriteLine();
Console.WriteLine("**** Create Collection {0} in {1} ****", collectionId,
database.Id);

var collectionDefinition = new DocumentCollection { Id = collectionId };
var options = new RequestOptions { OfferType = offerType };
var result = await

client.CreateDocumentCollectionAsync(database.SelfLink,
collectionDefinition, options);
var collection = result.Resource;

Console.WriteLine("Created new collection");
//ViewCollection(collection);

return collection;
}

#region DifferentWaysOfDoingThings
private async Task<Document> CreateDocuments2(DocumentClient client, byte[] fileData)
{
Console.WriteLine();
Console.WriteLine("**** Create Documents ****");
Console.WriteLine();

dynamic document1Definition = new
{
name = "New Customer 1",
address = new
{
addressType = "Main Office",
addressLine1 = "123 Main Street",
location = new
{
city = "Brooklyn",
stateProvinceName = "New York"
},
postalCode = "11229",
countryRegionName = "United States"
},
fileDataBinary = fileData
};

Document document1 = CreateDocument2(client, document1Definition);
Console.WriteLine("Created document {0} from dynamic object", document1.Id);
Console.WriteLine();

return document1;
}

private async Task<Document> CreateDocument2(DocumentClient client, object documentObject)
{

var result = await client.CreateDocumentAsync(collection.SelfLink, documentObject);
var document = result.Resource;

Console.WriteLine("Created new document: {0}\r\n{1}", document.Id, document);
return document;
}

#endregion
}

Advertisements

SQLite Entity Data Model (EDMX) with Entity Framework – Guide

This was a bit tricky for me but these are the steps I took for Visual Studio 2013 Community Edition. Notice that the order is important, especially you should not create the Visual Studio project before the SQLite conenction in Server Explorer is established. For some reason existing VS project wont’t recognize the connection, well at least in my environement.

  1. Download the proper SQLite setup bundle based on your criteria in your environment: http://system.data.sqlite.org/index.html/doc/trunk/www/downloads.wiki
    1. You Visual Studio version
    2. Your Visual Studio target CPU 32bit or 64bit
    3. Your .NET version which you wish to use
  2. Install the setup bundle
    1. Make sure to enable Visual Studio integration and GAC registration during installation.
  3. Once installed open Visual Studio and Server Explorer
  4. From here you should see a new data source named System.Data.SQLite Database File when you create a new connection
  5. In the next windows select your SQLite database file and press OK.
  6. After this your should have a valid connection to your SQLite database file
  7. Next create a new Visual Studio Project.
  8. Add new Nuget Packages
    1. Entity Framework
    2. System.Data.SQLite (x86/x64)
  9. You could build your project at this stage.
  10. After all these steps your should be able to generate an Entity Data Model on your SQLite database based on the Server Explorer connection

 

Other sources of information:

http://erikej.blogspot.fi/2014/11/using-sqlite-with-entity-framework-6.html

https://visualstudiogallery.msdn.microsoft.com/0e313dfd-be80-4afb-b5e9-6e74d369f7a1

How to use: Azure Service Bus – Notification hub, Azure hosted SignalR Hub with .NET clients, Android clients and web JavaScript clients

Huh that is a looong title for a blog post :). It is easier for me to write these all in one post since they are related the code in this case. So here I go, bear with me, it’s going to be alot of stuff and code.

The codes and examples here are from the own personal technology workbench project hosted in Azure. More code and examples how these features and functionality work can be found here.

I will go in the following order with the topics covered in this post:

  • Notification Hub
    • Setting up the Azure Service Bus – Notification Hub
    • Connecting with a .NET Client and sending messages
    • Connecting and listening to messages with an Android device client
  • SignalR Hub
    • Setting up a Azure hosted SignalR hub
    • Connecting and listening to activities with the following clients:
      • Connecting with the .NET Client
      • Connecting with the JavaScript client
      • Connecting with the Android client

Notification Hub

Some links to get you started:

http://azure.microsoft.com/en-us/documentation/articles/mobile-services-dotnet-backend-android-get-started-push/

http://azure.microsoft.com/en-us/documentation/articles/mobile-services-dotnet-backend-android-push-notifications-app-users/

Setting up the Azure Service Bus – Notification Hub

I wont go into much details here. Microsoft has done a great job documenting these steps, I recommend you go to the following link and follow the first part of these instructions:

http://azure.microsoft.com/en-us/documentation/articles/mobile-services-dotnet-backend-android-get-started-push/

What you will need for my code example is a Google Developer account and a API Key from google to be able to send and receive GCM messages to Android devices. Of course you will also need a Azure account to be able to create the notification hub.

Add the following Nugget package:

Microsoft Azure Service Bus

Connecting with a .NET Client and sending messages

The code below will connect from a .NET Client to the Notification Hub. This is done in the class constructor. At the moment this code can only send GCM messages to Android devices. Note that these operations are asynchronous.

https://github.com/lionadi/MyFitnessTracker/blob/master/MyFitnessTrackerLibrary/ServiceBus/NotificationGateway.cs

But you could use a "central" hub from where to send these messages which will handle sending messages to different devices through a WebAPI:
https://github.com/lionadi/MyFitnessTracker/tree/master/FitTrackerHubCentral
https://github.com/lionadi/MyFitnessTracker/blob/master/FitTrackerHubCentral/FitTrackerHubCentral/Controllers/NotificationsController.cs



using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Microsoft.ServiceBus.Notifications;

namespace MyFitnessTrackerLibrary.ServiceBus
{
    // TODO: Replace this with a connection to the notification central hub, do not sent message directly from here in the future!!!!
    public class NotificationGateway
    {
        private NotificationHubClient hub = null;
        private static NotificationGateway _notificationGateway;
        public NotificationGateway()
        {

            hub = NotificationHubClient.CreateClientFromConnectionString(MyFitnessTrackerLibrary.Globals.MyFitAppSettings.NotificationHubConnectionString, MyFitnessTrackerLibrary.Globals.MyFitAppSettings.NotificationHubName);

        }

        ~NotificationGateway()
        {

        }

        public async Task<NotificationOutcome> SendMessage(string message)
        {
            var toast = "{ \"data\" : {\"message\":\"" + "From : " + message + "\"}}";
            return await hub.SendGcmNativeNotificationAsync(toast);
        }

        public static NotificationGateway GetInstance()
        {
            if(_notificationGateway == null)
            {
                _notificationGateway = new NotificationGateway();
            }

            return _notificationGateway;
        }
    }
}

Connecting and listening to messages with an Android device client

The Android side is a bit more complicated and annoying. You have to do more work here.

At the first you need two Microsoft java libraries to be able to connect to a notification hub:

https://github.com/lionadi/MyFitnessTracker/blob/master/MyFitnessAndroidApp/app/libs/notification-hubs-0.4.jar

https://github.com/lionadi/MyFitnessTracker/blob/master/MyFitnessAndroidApp/app/libs/notifications-1.0.1.jar

After this you need to add them to your Android Studio gradle file:

https://github.com/lionadi/MyFitnessTracker/blob/master/MyFitnessAndroidApp/app/build.gradle

dependencies {
 compile fileTree(dir: 'libs', include: ['*.jar'])
 compile 'com.android.support:appcompat-v7:21.0.3'
 compile 'com.google.android.gms:play-services:6.5.87'
 compile 'com.google.code.gson:gson:2.3.1'
 compile files('libs/signalr-client-sdk.jar')
compile files('libs/notifications-1.0.1.jar')
compile files('libs/notification-hubs-0.4.jar')
 compile files('libs/Java-WebSocket-1.3.0.jar')
 compile project(':signalr-client-sdk-android-release')
}

 Please remember to follow these instructions to setup your Android Studio project in a correct manner, more details here:

http://azure.microsoft.com/en-us/documentation/articles/mobile-services-dotnet-backend-android-get-started-push/

http://azure.microsoft.com/fi-fi/documentation/articles/notification-hubs-aspnet-backend-android-notify-users/

The most important piece of code is the class named MyHandler in this case which will handle your notifications once your device is registered to the notification hub:

https://github.com/lionadi/MyFitnessTracker/blob/master/MyFitnessAndroidApp/app/src/main/java/com/example/adriansimionescu/myfitnessandroidapp/MyHandler.java


package com.example.adriansimionescu.myfitnessandroidapp;

import android.app.NotificationManager;
import android.app.PendingIntent;
import android.content.Context;
import android.content.Intent;
import android.os.Bundle;
import android.support.v4.app.NotificationCompat;
import com.microsoft.windowsazure.notifications.NotificationsHandler;

public class MyHandler extends NotificationsHandler {
    public static final int NOTIFICATION_ID = 1;
    private NotificationManager mNotificationManager;
    NotificationCompat.Builder builder;
    Context ctx;

    static public MainActivity mainActivity;

    @Override
    public void onReceive(Context context, Bundle bundle) {
        ctx = context;
        String nhMessage = bundle.getString("message");

        sendNotification(nhMessage);
        mainActivity.DialogNotify("Received Notification",nhMessage);
    }

    private void sendNotification(String msg) {
        mNotificationManager = (NotificationManager)
                ctx.getSystemService(Context.NOTIFICATION_SERVICE);

        PendingIntent contentIntent = PendingIntent.getActivity(ctx, 0,
                new Intent(ctx, MainActivity.class), 0);

        NotificationCompat.Builder mBuilder =
                new NotificationCompat.Builder(ctx)
                        .setContentTitle("Notification Hub Demo")
                        .setStyle(new NotificationCompat.BigTextStyle()
                                .bigText(msg))
                        .setContentText(msg);

        mBuilder.setContentIntent(contentIntent);
        mNotificationManager.notify(NOTIFICATION_ID, mBuilder.build());
    }
}</pre>
<pre>

You also need a class that will register you device to the notification hub:
https://github.com/lionadi/MyFitnessTracker/blob/master/MyFitnessAndroidApp/app/src/main/java/com/example/adriansimionescu/myfitnessandroidapp/RegisterClient.java

 


package com.example.adriansimionescu.myfitnessandroidapp;

import java.io.IOException;
import java.io.UnsupportedEncodingException;
import java.util.Set;

import org.apache.http.HttpResponse;
import org.apache.http.HttpStatus;
import org.apache.http.client.ClientProtocolException;
import org.apache.http.client.HttpClient;
import org.apache.http.client.methods.HttpPost;
import org.apache.http.client.methods.HttpPut;
import org.apache.http.client.methods.HttpUriRequest;
import org.apache.http.entity.StringEntity;
import org.apache.http.impl.client.DefaultHttpClient;
import org.apache.http.util.EntityUtils;
import org.json.JSONArray;
import org.json.JSONException;
import org.json.JSONObject;

import android.content.Context;
import android.content.SharedPreferences;
import android.util.Log;

public class RegisterClient {
    private static final String PREFS_NAME = "ANHSettings";
    private static final String REGID_SETTING_NAME = "ANHRegistrationId";
    private String Backend_Endpoint;
    SharedPreferences settings;
    protected HttpClient httpClient;
    private String authorizationHeader;

    public RegisterClient(Context context, String backendEnpoint) {
        super();
        this.settings = context.getSharedPreferences(PREFS_NAME, 0);
        httpClient =  new DefaultHttpClient();
        Backend_Endpoint = backendEnpoint + "/api/register";
    }

    public String getAuthorizationHeader() {
        return authorizationHeader;
    }

    public void setAuthorizationHeader(String authorizationHeader) {
        this.authorizationHeader = authorizationHeader;
    }

    public void register(String handle, Set<String> tags) throws ClientProtocolException, IOException, JSONException {
        String registrationId = retrieveRegistrationIdOrRequestNewOne(handle);

        JSONObject deviceInfo = new JSONObject();
        deviceInfo.put("Platform", "gcm");
        deviceInfo.put("Handle", handle);
        deviceInfo.put("Tags", new JSONArray(tags));

        int statusCode = upsertRegistration(registrationId, deviceInfo);

        if (statusCode == HttpStatus.SC_OK) {
            return;
        } else if (statusCode == HttpStatus.SC_GONE){
            settings.edit().remove(REGID_SETTING_NAME).commit();
            registrationId = retrieveRegistrationIdOrRequestNewOne(handle);
            statusCode = upsertRegistration(registrationId, deviceInfo);
            if (statusCode != HttpStatus.SC_OK) {
                Log.e("RegisterClient", "Error upserting registration: " + statusCode);
                throw new RuntimeException("Error upserting registration");
            }
        } else {
            Log.e("RegisterClient", "Error upserting registration: " + statusCode);
            throw new RuntimeException("Error upserting registration");
        }
    }

    private int upsertRegistration(String registrationId, JSONObject deviceInfo)
            throws UnsupportedEncodingException, IOException,
            ClientProtocolException {
        HttpPut request = new HttpPut(Backend_Endpoint+"/"+registrationId);
        request.setEntity(new StringEntity(deviceInfo.toString()));
        request.addHeader("Authorization", "Basic "+authorizationHeader);
        request.addHeader("Content-Type", "application/json");
        HttpResponse response = httpClient.execute(request);
        int statusCode = response.getStatusLine().getStatusCode();
        return statusCode;
    }

    private String retrieveRegistrationIdOrRequestNewOne(String handle) throws ClientProtocolException, IOException {
        if (settings.contains(REGID_SETTING_NAME))
            return settings.getString(REGID_SETTING_NAME, null);

        HttpUriRequest request = new HttpPost(Backend_Endpoint+"?handle="+handle);
        request.addHeader("Authorization", "Basic "+authorizationHeader);
        HttpResponse response = httpClient.execute(request);
        if (response.getStatusLine().getStatusCode() != HttpStatus.SC_OK) {
            Log.e("RegisterClient", "Error creating registrationId: " + response.getStatusLine().getStatusCode());
            throw new RuntimeException("Error creating Notification Hubs registrationId");
        }
        String registrationId = EntityUtils.toString(response.getEntity());
        registrationId = registrationId.substring(1, registrationId.length()-1);

        settings.edit().putString(REGID_SETTING_NAME, registrationId).commit();

        return registrationId;
    }
}

After all these steps and setups you can finally go to your activity and add the following pieces of codes to fire up the connection and start listening to messages:
https://github.com/lionadi/MyFitnessTracker/blob/master/MyFitnessAndroidApp/app/src/main/java/com/example/adriansimionescu/myfitnessandroidapp/MainActivity.java

import com.microsoft.windowsazure.messaging.*;
import com.microsoft.windowsazure.notifications.NotificationsManager;

// Define this properties in you activity class</pre>
<pre>private RegisterClient registerClient;
private String SENDER_ID = "";
private GoogleCloudMessaging gcm;
private NotificationHub hub;
private String HubName = "fittracker";
private String HubListenConnectionString = "";</pre>
<pre>
@SuppressWarnings("unchecked")
private void registerWithNotificationHubs() {
 new AsyncTask() {
 @Override
 protected Object doInBackground(Object... params) {
 try {
 String regid = gcm.register(SENDER_ID);
 DialogNotify("Registered Successfully", "RegId : " +
 hub.register(regid).getRegistrationId());
 } catch (Exception e) {
 DialogNotify("Exception",e.getMessage());
 return e;
 }
 return null;
 }
 }.execute(null, null, null);
}

/**
 * A modal AlertDialog for displaying a message on the UI thread
 * when theres an exception or message to report.
 *
 * @param title Title for the AlertDialog box.
 * @param message The message displayed for the AlertDialog box.
 */
public void DialogNotify(final String title,final String message)
{
 final AlertDialog.Builder dlg;
 dlg = new AlertDialog.Builder(this);

 runOnUiThread(new Runnable() {
 @Override
 public void run() {
 AlertDialog dlgAlert = dlg.create();
 dlgAlert.setTitle(title);
 dlgAlert.setButton(DialogInterface.BUTTON_POSITIVE,
 (CharSequence) "OK",
 new DialogInterface.OnClickListener() {
 public void onClick(DialogInterface dialog, int which) {
 dialog.dismiss();
 }
 });
 dlgAlert.setMessage(message);
 dlgAlert.setCancelable(false);
 dlgAlert.show();
 }
 });
}</pre>
<pre>

So the first function will register you device and the next one will create a notification with a message in your device. Next you create the connection with this piece of code:


<pre>MyHandler.mainActivity = this;
NotificationsManager.handleNotifications(this, SENDER_ID, MyHandler.class);
gcm = GoogleCloudMessaging.getInstance(this);
hub = new NotificationHub(HubName, HubListenConnectionString, this);
registerWithNotificationHubs();</pre>

Notice how you pass your activity instance you the myhandler class. This is important.

And that’s it :D. Simple yet alot of work. Luckily Microsoft has made a good job documenting these steps. If in trouble don’t hesitate to look up on some documentation.

SignalR Hub

Implementing SignalR on the .NET side is rather easy BUT there is so much automation that it simply feel weird :). Everything seems to work if you just follow the instuctions but as I noticed one you poke around custom authentication and authorization you can really mess thing up. For example I wanted to add to my SignalR hun web project EDM mapping to a database or a custom authentication, well I made the mistake of choosing to use Entity Framework version 6.0 which uses a different version of Newtonsoft.json library which caused all sorts of problems. Another issue which I ran into was that I create a connection identification by client ID to be able to notify the client devices and services of changes within my system. At one point I did a simple mistake of forgeting to pass on the identification information which lead to weird errors on the client side browser such as IE and Chrome. The errors had nothing to do with the fact that the connection failed on the server side because the user ID was missing. The browsers expressed errors related to CORS which made no sense since I configured CORS support. So just be careful.Setting up a

Azure hosted SignalR hub

Start by looking at this source:

http://www.asp.net/signalr/overview/getting-started/tutorial-getting-started-with-signalr

To host my SignalR hub in Azure I simply created an empty web application and followed the instructions in the link above. Sample code:

https://github.com/lionadi/MyFitnessTracker/blob/master/SignalRGateway/SignalRGateway/ChatHub.cs

https://github.com/lionadi/MyFitnessTracker/blob/master/SignalRGateway/SignalRGateway/index.html

Also include the following SignalR nuget package:

Microsoft ASP .NET SignalR (to be able to host)

Microsoft ASP .NET Cross-Origin Support

Windows Azure Storage

Lets look at bit more closely at the ChartHub:


using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Threading.Tasks;
using Microsoft.AspNet.SignalR;
using SignalRGateway.AzureTableStorage;
using System.Configuration;

using Microsoft.WindowsAzure;
using Microsoft.WindowsAzure.Storage;
using Microsoft.WindowsAzure.Storage.Table;
using MyFitnessTrackerLibrary.Globals;

namespace SignalRGateway
{
 public class ChatHub : Hub
 {

 public void Send(string name, string message)
 {
 // Call the broadcastMessage method to update clients.
 var queryResult = this.SendMessageTo(name, message);
 foreach (var entity in queryResult)
 {
 Clients.Client(entity.RowKey).broadcastMessage(name, message);
 }
 }

 public void IsDataUpdateRequiredForWeb(string name, bool isRequired, string message)
 {
 var queryResult = this.SendMessageTo(name, message);
 foreach (var entity in queryResult)
 {
 Clients.Client(entity.RowKey).isDataUpdateRequiredForWeb(name, isRequired, message);
 }
 Clients.All.isDataUpdateRequiredForWeb(name, isRequired, message);
 }

 public void IsDataUpdateRequiredForMobileClient(string name, bool isRequired, string message)
 {
 var queryResult = this.SendMessageTo(name, message);
 foreach (var entity in queryResult)
 {
 Clients.Client(entity.RowKey).isDataUpdateRequiredForMobileClient(name, isRequired, message);
 }
 }

 private List<ConnectionEntity> SendMessageTo(String who, String message)
 {
 //var name = Context.User.Identity.Name;
 var name = this.GetConnectionUser();

 if (!String.IsNullOrEmpty(name))
 {
 var table = GetConnectionTable();

 // Notice that the partition keys are stored in azure storage as lower case
 var query = new TableQuery<ConnectionEntity>()
 .Where(TableQuery.GenerateFilterCondition(
 "PartitionKey",
 QueryComparisons.Equal,
 who.ToLowerInvariant()));

 var queryResult = table.ExecuteQuery(query).ToList();
 if (queryResult.Count == 0)
 {
 Clients.Caller.showErrorMessage("The user is no longer connected.");
 }
 else
 {
 // Load only once the host application connections to display the data there
 if(queryResult.Count(o=>o.PartitionKey.Equals(Constants.SignalR_HostApplicationUserName.ToLowerInvariant())) <= 0)
 queryResult.AddRange(this.SendMessageTo(Constants.SignalR_HostApplicationUserName, message));

 return queryResult;
 }
 }

 return new List<ConnectionEntity>();
 }

 // This assumes that "normmaly" all others clients than the host SignalR web application (this app) will use header named as username for user identification. The SignalR web app will user querystring.
 private String GetConnectionUser()
 {
 var name = Context.Headers[Constants.SignalR_HeaderID_Username];

 if (String.IsNullOrEmpty(name))
 {
 name = Context.QueryString[Constants.SignalR_HeaderID_Username];
 }
 if (String.IsNullOrEmpty(name))
 return null;

 // Notice that the partition keys are stored in azure storage as lower case
 return name.ToLowerInvariant();
 }

 public override Task OnConnected()
 {
 //var name = Context.User.Identity.Name;
 var name = this.GetConnectionUser();

 if(!String.IsNullOrEmpty(name))
 {
 var table = GetConnectionTable();
 var created = table.CreateIfNotExists();

 var entity = new ConnectionEntity(
 name.ToLower(),
 Context.ConnectionId);
 var insertOperation = TableOperation.InsertOrReplace(entity);
 table.Execute(insertOperation);
 }

 return base.OnConnected();
 }

 public override Task OnDisconnected(bool stopCalled)
 {
 //var name = Context.User.Identity.Name;
 var name = this.GetConnectionUser();

 if (!String.IsNullOrEmpty(name))
 {
 var table = GetConnectionTable();

 var deleteOperation = TableOperation.Delete(
 new ConnectionEntity(name, Context.ConnectionId) { ETag = "*" });
 table.Execute(deleteOperation);
 }

 return base.OnDisconnected(stopCalled);
 }

 private CloudTable GetConnectionTable()
 {

 var storageAccount =
 CloudStorageAccount.Parse(
 MyFitnessTrackerLibrary.Globals.MyFitAppSettings.AzureTableStorageConnectionString);
 var tableClient = storageAccount.CreateCloudTableClient();
 var table = tableClient.GetTableReference("connection");

 return table;
 }
 }
}

In my code example connections are stored and managed in Azure Table Storage(check the link below how to create one):
http://azure.microsoft.com/en-us/documentation/articles/storage-dotnet-how-to-use-tables/

When you connect to the hub you will enter the OnConnected() function. Here my implementation seeks for the username header value or QueryString username value(this is to just go around a problem in JavaScript client which I did not want to spend to much time on). After this we connect to the Azure Table Storage and add a new connection to the table.

When disconnecting the reverse is done to the Azure Storage Table.

The GetConnectionTable() function will open a connection to the storage table(check from azure management web console for your connection data).

The SignlarR has threee function which will send information to listening clients based on connection IDs:

  • Send
  • IsDataUpdateRequiredForWeb
  • IsDataUpdateRequiredForMobileClient

The SendMessageTo() function is used to get all of the connection for a user name which needs to be notified of updates.

The code is pretty simple an easy. Microsoft has done a great job documenting this: http://www.asp.net/signalr

Last thing which I recommend to do is to configure CORS support:

https://github.com/lionadi/MyFitnessTracker/blob/master/SignalRGateway/SignalRGateway/Startup.cs


// Branch the pipeline here for requests that start with "/signalr"
 app.Map("/signalr", map =>
 {
 // Setup the CORS middleware to run before SignalR.
 // By default this will allow all origins. You can
 // configure the set of origins and/or http verbs by
 // providing a cors options with a different policy.
 map.UseCors(CorsOptions.AllowAll);
 var hubConfiguration = new HubConfiguration
 {
 // You can enable JSONP by uncommenting line below.
 // JSONP requests are insecure but some older browsers (and some
 // versions of IE) require JSONP to work cross domain
 // EnableJSONP = true
 };
 // Run the SignalR pipeline. We're not using MapSignalR
 // since this branch already runs under the "/signalr"
 // path.
 map.RunSignalR(hubConfiguration);
 });

Simply copy&paste the code above to get it to work but notice that it will allow requests from all possible connections.
http://www.asp.net/signalr/overview/security/introduction-to-security#csrf

Connecting and listening to activities with the following clients:

You will need to import the following libraries in Visual Studio to get you clients to work on SignalR:

Microsoft ASP .NET SignalR .NET Client

Microsoft ASP .NET SignalR JavaScript Client

Connecting with the .NET Client

The .NET Client code is pretty easy to understand:


using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using Microsoft.AspNet.SignalR.Client;
using System.Threading.Tasks;
using MyFitnessTrackerLibrary.ServiceBus;
using MyFitnessTrackerLibrary.Globals;

namespace MyFitnessTrackerLibrary.SignalRLogic
{
 public class HubGateway
 {

 private String hubLocation = MyFitAppSettings.SignalRHubHostLocation;
 private static HubGateway _hubGateway = null;
 private String hubProxyName = MyFitAppSettings.SignalRHubProxy;
 private IHubProxy hubProxy = null;
 private HubConnection hubConnection = null;
 private String sourceID = "NO ID";

 public IHubProxy HubProxyPoint
 {
 get { return this.hubProxy; }
 }

 public String SourceID
 {
 get
 {
 return this.sourceID;
 }

 set

 {
 this.sourceID = value;
 }
 }

 public HubGateway()
 {
 hubConnection = new HubConnection(this.hubLocation);
 hubProxy = hubConnection.CreateHubProxy(hubProxyName);
 }

 ~HubGateway()
 {
 this.Stop();
 }

 public async Task SendNormalMessage(String name, String message)
 {
 await this.Start(name);
 await this.HubProxyPoint.Invoke("Send", name, message + " #Source ID: " + this.sourceID);
 }

 public async Task IsDataUpdateRequiredForWeb(String name, bool isRequired, String message)
 {
 await this.Start(name);
 await this.HubProxyPoint.Invoke("IsDataUpdateRequiredForWeb", name, isRequired, message + " #Source ID: " + this.sourceID);
 await NotificationGateway.GetInstance().SendMessage("New data was added. Your UI is updated/updating.");
 }

 public async Task IsDataUpdateRequiredForMobileClient(String name, bool isRequired, String message)
 {
 await this.Start(name);
 await this.HubProxyPoint.Invoke("IsDataUpdateRequiredForMobileClient", name, isRequired, message + " #Source ID: " + this.sourceID);
 await NotificationGateway.GetInstance().SendMessage("New data was added. Your UI is updated/updating.");
 }

 public static HubGateway GetInstance()
 {
 if( _hubGateway == null)
 {
 _hubGateway = new HubGateway();
 }

 return _hubGateway;
 }

 public async Task Start(String userName)
 {
 if (hubConnection.State == ConnectionState.Disconnected)
 {
 if(!this.hubConnection.Headers.ContainsKey(Constants.SignalR_HeaderID_Username))
 this.hubConnection.Headers.Add(new KeyValuePair<string, string>(Constants.SignalR_HeaderID_Username, userName));

 await hubConnection.Start();
 }
 }

 public void Stop()
 {
 hubConnection.Stop();
 }
 }
}

The important part in this code in when you call the Invoke() function to invoke in the SignalR hub the needed function and notify registered clients of updates.

The second important part is the Start() function. We add the username data in the connection, this way the hub knows where to send the messages/updates requests. Yes I know there is a bug in the code above, only one user will receive messages from this piece of code. I haven’t got around to fix this in my original project but it’s a simple matter.

Connecting with the JavaScript client

</pre>
<pre>
var connection = $.hubConnection(Constants.SignalRGatewayLocation);
 connection.qs = { "username": CookieHelper.UserName };
 var contosoChatHubProxy = connection.createHubProxy(Constants.SignalRHubProxyName);

 contosoChatHubProxy.on(Constants.SignalRHubMethod_IsDataUpdateRequiredForWeb, function (name, isRequired, message) {
 // Html encode display name and message.
 var encodedName = $('<div />').text(name).html();
 //var encodedMsg = $('<div />').text("isDataUpdateRequiredForWeb is Update Required: " + isRequired + " Message: " + message).html();
 var encodedMsg = $('<div />').text("Updating UI. New data from the mobile app.").html();
 // Add the message to the page.
 $('#notifications').append('<ul><li><strong>' + encodedName
 + '</strong>:&nbsp;&nbsp;' + encodedMsg + '</li></ul>');
 highChartsController.LoadProperChartByUserSelection();
 });

 connection.start()
 .done(function () {
 console.log('Now connected, connection ID=' + connection.id
 );
 })
 .fail(function () {
 console.log('Could not connect');
 });

The code above is rather simple. You create a connection, define the hub name, register to a function on the SignalR hub and start the connection.
The only “weird” part is that the username is passed in the QueryString and not in the header. This was due to a problem which I could not fix and had to go around. There might be a better solution out there.

Connecting with the Android client

Now here comes the hard part. Getting SignalR to work on android was a pain in the but :). Lots of weird problems and lack or proper documentation.

To start with you will need this library downloaded and compiled in Android Studio(or some other Java development tool you are using).

https://github.com/SignalR/java-client

You also might need the following library:

http://java-websocket.org/

Then a good place to go next would be:

https://whathecode.wordpress.com/2014/03/20/getting-started-with-the-java-signalr-sdk/

BUT the example above did not work for me as it was stated in the example. Here what I had to do:

Add to your Android project these libraries:

signalr-client-sdk.jar

Java-WebSocket-1.3.0.jar

signalr-client-sdk-android-release.aar

The following libraries can be added from Android Studio UI: File -> Project Structure. Then add a new library from the plus icon and in the new popup select the “import .JAR or .AAR Package”.

Your gradle file should look something like this:

dependencies {
 compile fileTree(dir: 'libs', include: ['*.jar'])
 compile 'com.android.support:appcompat-v7:21.0.3'
 compile 'com.google.android.gms:play-services:6.5.87'
 compile 'com.google.code.gson:gson:2.3.1'
 compile files('libs/signalr-client-sdk.jar')
compile files('libs/notifications-1.0.1.jar')
compile files('libs/notification-hubs-0.4.jar')
 compile files('libs/Java-WebSocket-1.3.0.jar')
 compile project(':signalr-client-sdk-android-release')
}

The next step is to start to create a background service in Android that will be able to communicate with your desired activity.

We Start this by defining a interface which is implemented in the activity:

https://github.com/lionadi/MyFitnessTracker/blob/master/MyFitnessAndroidApp/app/src/main/java/com/example/adriansimionescu/myfitnessandroidapp/ServiceCallbacks.java


package com.example.adriansimionescu.myfitnessandroidapp;

public interface ServiceCallbacks {
    void updateUI();
}</pre>
<pre>// To implement it in your activity:</pre>
<pre>public class MainActivity extends ActionBarActivity implements ServiceCallbacks {
...
}

Next we create the background service:
https://github.com/lionadi/MyFitnessTracker/blob/master/MyFitnessAndroidApp/app/src/main/java/com/example/adriansimionescu/myfitnessandroidapp/SignalRService.java


package com.example.adriansimionescu.myfitnessandroidapp;

import android.app.IntentService;
import android.app.Service;
import android.content.Intent;
import android.os.Binder;
import android.os.IBinder;
import android.util.Log;
import android.widget.Toast;

import java.util.concurrent.ExecutionException;

import microsoft.aspnet.signalr.client.SignalRFuture;
import microsoft.aspnet.signalr.client.hubs.HubConnection;
import microsoft.aspnet.signalr.client.hubs.HubProxy;
import microsoft.aspnet.signalr.client.hubs.SubscriptionHandler1;
import microsoft.aspnet.signalr.client.hubs.SubscriptionHandler2;
import microsoft.aspnet.signalr.client.hubs.SubscriptionHandler3;
import microsoft.aspnet.signalr.client.transport.ClientTransport;
import microsoft.aspnet.signalr.client.transport.ServerSentEventsTransport;

public class SignalRService extends Service {

    // Binder given to clients
    private final IBinder binder = new LocalBinder();
    // Registered callbacks
    private ServiceCallbacks serviceCallbacks;

    // Class used for the client Binder.
    public class LocalBinder extends Binder {
        SignalRService getService() {
            // Return this instance of MyService so clients can call public methods
            return SignalRService.this;
        }
    }

    @Override
    public IBinder onBind(Intent intent) {
        return binder;
    }

    @Override
    public void onCreate() {
        super.onCreate();
    }

    public void setCallbacks(ServiceCallbacks callbacks) {
        this.serviceCallbacks = callbacks;
    }

    @SuppressWarnings("deprecation")
    @Override
    public void onStart(Intent intent, int startId) {
        super.onStart(intent, startId);
        Toast.makeText(this, "Service Start", Toast.LENGTH_LONG).show();

        String server = Constants.SignalRGateway;
        HubConnection connection = new HubConnection(server);
        connection.getHeaders().put("username", UserDataContainer.LoginData.userName);
        HubProxy proxy = connection.createHubProxy(Constants.SignalRHubName);

        //SignalRFuture<Void> awaitConnection = connection.start();

// This was added to get around a websocket problem with Android devices to the SignalR hub hosted in Azure
        ClientTransport transport = new ServerSentEventsTransport(connection.getLogger());

        SignalRFuture<Void> awaitConnection = connection.start(transport);
        try {
            awaitConnection.get();
            proxy.subscribe(this );
        } catch (InterruptedException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } catch (ExecutionException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }

        //--------------------------------------------------------------------------------
    }

    public void Send( String name, String message )
    {
        final String fmessage = message;
        final String fname = name;

    }

    public void IsDataUpdateRequiredForMobileClient( String name, boolean isRequired, String message ) {
        final String fmessage = message;
        final String fname = name;
        final boolean fisrequired = isRequired;
        if (serviceCallbacks != null) {
            serviceCallbacks.updateUI();
        }
    }

    @Override
    public void onDestroy() {
        super.onDestroy();
    }

}

There are a few important function.

onBind() => Use this to bind the actual instance of the service from your activity
setCallbacks() => use this to create a connection to the activity class interface so that we can call a desired method in the activity when a singalr message is received.

To bind to the SignalR function and message you need to define methods that use the same names as in the hub.
IsDataUpdateRequiredForMobileClient() and Send()

After the connection in made you need to call the subscribe method in the proxy class and pass the service class as a parameter. This will allow the binding between the defined methods above with the one in the SignalR hub.

The last part of the puzzle is that we call the interface function updateUI() which will trigger the same function in the activity to trigger and allow you to perform something in the activity.

Then all you have to do is to create the service instance in the activity, bind it and start it:
https://github.com/lionadi/MyFitnessTracker/blob/master/MyFitnessAndroidApp/app/src/main/java/com/example/adriansimionescu/myfitnessandroidapp/MainActivity.java

private SignalRService signalRService;</pre>
<pre>@Override
protected void onStart() {
    super.onStart();
    // Bind to LocalService

        Intent intent = new Intent(this, SignalRService.class);
        bindService(intent, serviceConnection, Context.BIND_AUTO_CREATE);
    // Do this to avoid starting multiple service, only one is needed
    if(this.signalRService == null) {
        this.startService(intent);
    }
}

@Override
protected void onStop() {
    super.onStop();

    // Unbind from service
    if (bound) {
        this.signalRService.setCallbacks(null); // unregister
        unbindService(serviceConnection);
        bound = false;
    }
}

/** Callbacks for service binding, passed to bindService() */
private ServiceConnection serviceConnection = new ServiceConnection() {

    @Override
    public void onServiceConnected(ComponentName className,
                                   IBinder service) {
        // cast the IBinder and get MyService instance
        SignalRService.LocalBinder binder = (SignalRService.LocalBinder) service;
        signalRService = binder.getService();
        bound = true;
        signalRService.setCallbacks(MainActivity.this); // register
    }

    @Override
    public void onServiceDisconnected(ComponentName arg0) {
        bound = false;
    }
};

/* Defined by ServiceCallbacks interface */
@Override
public void updateUI() {
// update your UI here
}

In the OnStart() and OnStop() functions we bind and start the service.

In the serviceConnection class instance we do the actual binding and notice how the code maps the activity to the service to be able to call the updateUI() function below.

 

The End

Huh, a long post but I hope you got the idea how to use notification hub, azure table storage and singnalr to communicate between different service and devices in different manner. SignalR is pretty cool what you can do with it. Especially with games and backend stuff. Cool 🙂

Good to know: SQL Server 2012 – Part 1

Hi,

It’s been awhile since my last post but here comes my next one on SQL Server. I been gathering SQL Server related links that might come in handy on the most important topics( that I know 🙂 ). Anyway here are the links. Hope it helps some one, enjoy :).

ALTER INDEX (Transact-SQL) http://msdn.microsoft.com/en-us/library/ms188388.aspx
ALTER TABLE (Transact-SQL) http://msdn.microsoft.com/en-us/library/ms190273.aspx
ALTER VIEW (Transact-SQL) http://msdn.microsoft.com/en-us/library/ms173846.aspx
binary and varbinary (Transact-SQL) http://msdn.microsoft.com/en-us/library/ms188362.aspx
Built-in Functions (Transact-SQL) http://msdn.microsoft.com/en-us/library/ms174318%28v%3DSQL.110%29.aspx
Clustered and Nonclustered Indexes Described http://technet.microsoft.com/en-us/library/ms190457.aspx
COLUMNS_UPDATED (Transact-SQL) http://msdn.microsoft.com/en-us/library/ms186329.aspx
Contained Databases http://msdn.microsoft.com/en-us/library/ff929071.aspx
CONTAINS (Transact-SQL) http://msdn.microsoft.com/en-us/library/ms187787.aspx
Create Foreign Key Relationships http://msdn.microsoft.com/en-us/library/ms189049.aspx
CREATE FUNCTION (Transact-SQL) http://msdn.microsoft.com/en-us/library/ms186755.aspx
CREATE INDEX (Transact-SQL) http://msdn.microsoft.com/en-us/library/ms188783.aspx
Create Indexed Views http://msdn.microsoft.com/en-us/library/ms191432.aspx
CREATE PROCEDURE (Transact-SQL) http://msdn.microsoft.com/en-us/library/ms187926.aspx
CREATE SEQUENCE (Transact-SQL) http://msdn.microsoft.com/en-us/library/ff878091.aspx
CREATE SYNONYM (Transact-SQL) http://msdn.microsoft.com/en-us/library/ms177544.aspx
CREATE TABLE (Transact-SQL) http://msdn.microsoft.com/en-us/library/ms174979.aspx
CREATE VIEW (Transact-SQL) http://msdn.microsoft.com/en-us/library/ms187956.aspx
CREATE XML SCHEMA COLLECTION (Transact-SQL) http://msdn.microsoft.com/en-us/library/ms176009.aspx
Data Compression http://msdn.microsoft.com/en-us/library/cc280449.aspx
Data Type Precedence (Transact-SQL) http://msdn.microsoft.com/en-us/library/ms190309.aspx
Data Types (Transact-SQL) http://msdn.microsoft.com/en-us/library/ms187752(v=SQL.110).aspx
Database Engine Error Severities http://msdn.microsoft.com/en-us/library/ms164086.aspx
Date and Time Data Types and Functions http://msdn.microsoft.com/en-us/library/ms186724%28v%3DSQL.110%29.aspx
Date and Time Data Types and Functions (Transact-SQL) http://msdn.microsoft.com/en-us/library/ms186724
Date and Time Types http://msdn.microsoft.com/en-us/library/ff848733.aspx
DATEADD (Transact-SQL) http://msdn.microsoft.com/en-us/library/ms186819.aspx
datetimeoffset (Transact-SQL) http://msdn.microsoft.com/en-us/library/bb630289.aspx
DECLARE CURSOR (Transact-SQL) http://msdn.microsoft.com/en-us/library/ms180169.aspx
Detecting and Ending Deadlocks http://msdn.microsoft.com/en-us/library/ms178104%28SQL.105%29.aspx
Deterministic and Nondeterministic Functions http://msdn.microsoft.com/en-us/library/ms178091.aspx
Difference Between Union vs. Union All – Optimal Performance Comparison http://blog.sqlauthority.com/2009/03/11/sql-server-difference-between-union-vs-union-all-optimal-performance-comparison/
Diving into T-SQL Grouping Sets http://www.grapefruitmoon.net/diving-into-t-sql-grouping-sets/
Dynamic Management Views and Functions (Transact-SQL) http://msdn.microsoft.com/en-us/library/ms188754.aspx
ENCRYPTBYCERT (Transact-SQL) http://technet.microsoft.com/en-us/library/ms188061.aspx
ENCRYPTBYPASSPHRASE (Transact-SQL) http://technet.microsoft.com/en-us/library/ms190357.aspx
EOMONTH (Transact-SQL) http://msdn.microsoft.com/en-us/library/hh213020.aspx
Example @@ERROR (Transact-SQL) http://msdn.microsoft.com/en-us/library/ms188790.aspx
Example @@TRANCOUNT (Transact-SQL) http://msdn.microsoft.com/en-us/library/ms187967.aspx
Example: Retrieving Product Model Information as XML http://msdn.microsoft.com/en-us/library/bb510464.aspx
EXCEPT and INTERSECT (Transact-SQL) http://msdn.microsoft.com/en-us/library/ms188055.aspx
EXECUTE AS Clause (Transact-SQL) http://msdn.microsoft.com/en-us/library/ms188354.aspx
Executing Remote Stored Procedure – Calling Stored Procedure on Linked Server http://blog.sqlauthority.com/2007/10/06/sql-server-executing-remote-stored-procedure-calling-stored-procedure-on-linked-server/
Features Supported by the Editions of SQL Server 2012 http://msdn.microsoft.com/en-us/library/cc645993(SQL.110).aspx
FileTables (SQL Server) http://msdn.microsoft.com/en-us/library/ff929144.aspx
FOR XML (SQL Server) http://msdn.microsoft.com/en-us/library/ms178107.aspx
FORMAT (Transact-SQL) http://msdn.microsoft.com/en-us/library/hh213505.aspx
Formatting Types http://msdn.microsoft.com/en-us/library/26etazsy.aspx
FROM (Transact-SQL) http://msdn.microsoft.com/en-us/library/ms177634.aspx
Graphical Execution Plan Icons (SQL Server Management Studio) http://msdn.microsoft.com/en-us/library/ms175913%28v%3DSQL.105%29.aspx
GROUP BY (Transact-SQL) http://msdn.microsoft.com/en-us/library/ms177673.aspx
Hardware and Software Requirements for Installing SQL Server 2012 http://msdn.microsoft.com/en-us/library/ms143506
IN (Transact-SQL) http://msdn.microsoft.com/en-us/library/ms177682.aspx
Join Hints (Transact-SQL) http://msdn.microsoft.com/en-us/library/ms173815.aspx
LAG (Transact-SQL) http://msdn.microsoft.com/en-us/library/hh231256.aspx
LEAD (Transact-SQL) http://msdn.microsoft.com/en-us/library/hh213125.aspx
LIKE (Transact-SQL) http://msdn.microsoft.com/en-us/library/ms179859
Locking Hints http://technet.microsoft.com/en-us/library/ms189857.aspx
MERGE (Transact-SQL) http://technet.microsoft.com/en-us/library/bb510625.aspx
Microsoft Office 2010 Filter Packs http://www.microsoft.com/en-us/download/details.aspx?id=17062
money and smallmoney (Transact-SQL) http://msdn.microsoft.com/en-us/library/ms179882.aspx
optimize for ad hoc workloads Server Configuration Option http://msdn.microsoft.com/en-us/library/cc645587.aspx
OUTPUT Clause (Transact-SQL) http://msdn.microsoft.com/en-us/library/ms177564.aspx
Page Compression Implementation http://msdn.microsoft.com/en-us/library/cc280464.aspx
PATINDEX (Transact-SQL) http://msdn.microsoft.com/en-us/library/ms188395
Plan Caching in SQL Server 2008 http://msdn.microsoft.com/en-us/library/ee343986%28SQL.100%29.aspx
Query Hints (Transact-SQL) http://msdn.microsoft.com/en-us/library/ms181714.aspx
Ranking Functions (Transact-SQL) http://msdn.microsoft.com/en-us/library/ms189798.aspx
Row Compression Implementation http://msdn.microsoft.com/en-us/library/cc280576.aspx
Search Condition (Transact-SQL) http://msdn.microsoft.com/en-us/library/ms173545.aspx
Semantic Search (SQL Server) http://technet.microsoft.com/en-us/library/gg492075.aspx
SET ARITHABORT (Transact-SQL) http://msdn.microsoft.com/en-us/library/ms190306.aspx
SET IMPLICIT_TRANSACTIONS (Transact-SQL) http://msdn.microsoft.com/en-us/library/ms187807.aspx
SET TRANSACTION ISOLATION LEVEL (Transact-SQL) http://msdn.microsoft.com/en-us/library/ms173763.aspx
SET XACT_ABORT (Transact-SQL) http://msdn.microsoft.com/en-us/library/ms188792.aspx
Showplan Logical and Physical Operators Reference http://msdn.microsoft.com/en-us/library/ms191158.aspx
sp_estimate_data_compression_savings (Transact-SQL) http://msdn.microsoft.com/en-us/library/cc280574.aspx
sp_sequence_get_range http://msdn.microsoft.com/en-us/library/ff878352.aspx
Specify Computed Columns in a Table http://technet.microsoft.com/en-us/library/ms188300.aspx
SQL Injection http://msdn.microsoft.com/en-us/library/ms161953%28SQL.105%29.aspx
SQL Server Encryption http://technet.microsoft.com/en-us/library/bb510663.aspx
String Functions (Transact-SQL) http://msdn.microsoft.com/en-us/library/ms181984
sys.dm_tran_database_transactions http://msdn.microsoft.com/en-us/library/ms186957(v=sql.90).aspx
Table Hints (Transact-SQL) http://msdn.microsoft.com/en-us/library/ms187373.aspx
Table Hints (Transact-SQL) http://msdn.microsoft.com/en-us/library/ms187373.aspx
The Data Loading Performance Guide http://msdn.microsoft.com/en-us/library/dd425070.aspx
Troubleshooting Poor Query Performance: Cardinality Estimation http://technet.microsoft.com/en-us/library/ms181034.aspx
TRY_PARSE (Transact-SQL) http://msdn.microsoft.com/en-us/library/ms188792.aspx
Unicode Compression Implementation http://msdn.microsoft.com/en-us/library/ee240835.aspx
UNION (Transact-SQL) http://msdn.microsoft.com/en-us/library/ms180026.aspx
UPDATE STATISTICS (Transact-SQL) http://msdn.microsoft.com/en-us/library/ms187348.aspx
Use AUTO Mode with FOR XML http://msdn.microsoft.com/en-us/library/ms188273.aspx
Use EXPLICIT Mode with FOR XML http://msdn.microsoft.com/en-us/library/ms189068.aspx
Using @@ERROR http://msdn.microsoft.com/en-us/library/ms190193.aspx
Using APPLY http://msdn.microsoft.com/en-us/library/ms175156.aspx
Using Partitioned Views http://msdn.microsoft.com/en-us/library/ms190019.aspx
WHERE (Transact-SQL) http://msdn.microsoft.com/en-us/library/ms188047.aspx
Views http://msdn.microsoft.com/en-us/library/ms190174.aspx
Write-Ahead Transaction Log http://msdn.microsoft.com/en-us/library/ms186259%28SQL.105%29.aspx
XACT_STATE (Transact-SQL) http://msdn.microsoft.com/en-us/library/ms189797.aspx
XQuery Functions against the xml Data Type http://msdn.microsoft.com/en-us/library/ms189254.aspx

Tips: SQL Server – Constraints and Functions

If you are planning to make a table constraint that is more complex than a simple check against a field or two then I recommend using functions. By creating a function you can make complex logic by which you can return True Or False states that indicate to a constraint if the data is what you need it to be. You can also add parameters to a function giving you the dynamic possibility to process data being processed.

Example:

Lets say that you have function that wants to check if a ID value is a certain numeric ID and then you want to make sure that a row being inserted does not have a NULL value in a certain field.

Here is some sample code:

CREATE FUNCTION [dbo].[CheckForNullByID]

(

   — Add the parameters for the function here

   @someID int,

   @someFieldNotNULL char(11)

)

RETURNS bit

AS

BEGIN

   DECLARE @retValue bit = 0

   IF @someID = 40

   BEGIN

          IF @someFieldNotNULL IS NOT NULL

          BEGIN

                 SET @retValue = 1

          END

          ELSE

                 SET @retValue = 0

   END

   ELSE

          SET @retValue = 1

   RETURN @retValue

END

GO

Then you call the function in your constraint like the following expression:

([dbo].[CheckForNullByID]([rowIDValue],[someFieldValueThatIsNotToBeNULL])>(0))

Thats it :)!!!

Hope this helps 🙂

Lost files in Sharepoint Recycle Bin that can not be restored

This time I am writing a post about a problem that a client of mine had.

The problem is rather simple BUT required allot of time and energy to fix the problem or rather in my case to recover the items:

The client at some point by someone deleted files which ended up in the recycle bin. Later the client wanted to restore these files BUT was unable to. Sharepoint was telling that the restore operation failed even though you where able to clearly see that the files where available to restore in the Recycle bin of the site.

NOTICE: Before you start to try any of the things written in this post take notice TO NOT DO these things in you client product/live environment. If you chose to do so you will cause your client to lose their MS support. MS does not support manipulation of the content databases directly to SQL Server. So take a database backup, restore it in a test or development environment and fix you problem there.

NOTICE: Use this code at your own risk. The code does some crazy stuff directly to SharePoint content database. I will not take any responsibility on anything that you do with the application and/or any part of it and it’s code.

Here are the steps which I ended up taking and a more detailed explanation on them:

  1. Trying to restore the items through the object model
  2. Investigating the Sharepoint content database
  3. Trying to restore the files by fixing content database Recycle Bin table missing information in the files
  4. Trying to recover the files directly from the AllDocsStream table

Lets start from the first item and advance from there:

1. Trying to restore the items through the object model

This is basically the same as the UI BUT I decided to give it a quick try since nothing from the UI worked and sometimes you can do things through code that you can not do through the UI.

Long story short the object model did not offer much help and any restore operation failed again or nothing happened. For this a created a forms application(code is included at the end of this post).

Here is how the final version of the application looks like: appppp

In this first step I create the functionality in the “Blue” area. These where the object model fixes which did not help.

There basic functionality are provided which are in the “Blue” area:

  1. Restore the selected items from recycle bin
  2. Delete selected items
  3. And move them to the Site Collection Recycle Bin

2. Investigating the Sharepoint content database

At this point because the object model and the UI did not do what they are supposed to I simply gave up on using the “preferred” way of Microsoft and went directly into the content database. My first destination was the “Recycle Bin” table.

After a few moments of careful examination of the clients content database table I noticed that the documents that can not be restored have 4 table fields which are null while other data row in the table all have data in them.

The fields which where null where:

  1. ListId
  2. ListItemId
  3. AuthorId
  4. ListDirName

So I decided to create a code that would restore the missing values. I decided that the AuthorId and ListItemId would be defined by me during the code(since they could be anything any existing user and any integer number that is large enough not to conflict with existing ID enumeration in the target list) and the ListId and ListDirName are to be retrieved through the object model. This had to be done by retrieving the values of the following fields WebId, SiteId and DirName. In the code used these values to get the site, web and the list. After I got the list it was easy to just update the table with the list ID and the rootfolder ServerRelative URL into the ListDirName field.

You can find the code for this in the button named: “Repair Recycle Bin Items(DB)”

3. Trying to restore the files by fixing content database Recycle Bin table missing information in the files

Unfortunately what I did in step two was not enough because the sites and lists did not exist anymore for these deleted files that needed to be restored from the bin.

The next step was to try to update the following tables so that the document/file would be restored to a different web and list that actually exists.

I had to make modifications to the following tables:

  1. AllDocs = Data regarding the file/document itself, where it is, it’s parent, state etc
  2. AllDocStreams = The actual binary data of the file
  3. RecycleBin = The data of a deleted file that has ended up in the recycle bin

By retrieving the DocId value from RecycleBin table from a file that can not be restored you can correlate to the documents information in the AllDocs and AllDocStream.

This step is very complex and I due to time constraints I did not get it to work properly. Some piece of data was missing to get the file restored to another location. I managed to get the file restored BUT while the list showed that it had files in it you could not access the file. So something was missing from somewhere.

You can check the code for this in the button: “Repair Recycle Bin items(DB) to inserted URL”

4. Trying to recover the files directly from the AllDocsStream table

Finally the last step was to go directly into the AllDocsStream with ADO .NET, read the binary data, retrieve the documents real name from the RecycleBin table and write that data into the hard drive.

This was SUCCESSFUL!!! It worked like a charm and the client was happy to get their files restored.

In the AllDocsStrems table the most important field is the “Content” field. This is the actual binary data that you need to retrieve and use it to write it into your hard drive.

From the RecycleBin table you need the value from the LeafName field.

Using these two piece of information you can reconstruct the file.

Here is the code that solved my problem, you can find it behind the button: “Recover files from DB”

            

String connectionString = ConfigurationSettings.AppSettings[“DBConnectionString”];

            SqlDataReader rdr = null;

            this.textBox2.Text += “Opening connection to DB”;

            using (SqlConnection connection = new SqlConnection(connectionString))

            {

                connection.Open();

                this.textBox2.Text += “Connection opened”;

                List<RecycleBinDBItem> items = new List<RecycleBinDBItem>();

                try

                {

                    // Get all such items which can not be restored properly from the UI or by object model

                    SqlCommand cmd = new SqlCommand(“SELECT * FROM RecycleBin WHERE ListDirName IS NULL”, connection);

 

                    rdr = cmd.ExecuteReader();

                    this.textBox2.Text += “Select clause executed”;

 

                    while (rdr.Read())

                    {

                        this.textBox2.Text += “Reading line: \n”;

                        this.textBox2.Text += rdr[“DirName”].ToString();

                        items.Add(new RecycleBinDBItem(rdr[“DocId”].ToString(), rdr[“LeafName”].ToString()));

                    }

                }

                finally

                {

                    // close the reader

                    if (rdr != null)

                    {

                        rdr.Close();

                    }

                    int itemIDCounter = 5000;

                    int authorID = 1;

                    // Loop through every recycle bin items that is missing needed field data from the RecycleBin table to be restored properly

                    foreach (RecycleBinDBItem item in items)

                    {

                        try

                        {

                            SqlCommand cmd = new SqlCommand(String.Format(“SELECT Content FROM AllDocStreams WHERE Id = ‘{0}'”, item.docID ), connection);

 

                            // 2. Call Execute reader to get query results

                            byte [] bArray = (byte[])cmd.ExecuteScalar();

                            // Craete the file and write the binary data from the Database

                            FileStream fs = File.Create(item.fileName, (int)bArray.Length);

                            fs.Write(bArray, 0, bArray.Length);

                            fs.Flush();

                            fs.Close();

                        }

                        catch (SqlException sqlex)

                        {

                            this.textBox2.Text += sqlex.Message + ” #######\n” + sqlex.StackTrace;

                        }

                        catch (Exception ex)

                        {

                            this.textBox2.Text += ex.Message + ” #######\n” + ex.StackTrace;

                        }

                        finally

                        {

                            if (rdr != null)

                            {

                                rdr.Close();

                            }

                        }

                    }

                }

            }

            this.textBox2.Text += “DB operations done!”;

Final words;

Hope this might help someone else who is suffering from this problem. The application and it’s code is not the prettiest BUT I did what I could in the time that was given to me and also what the client is ready to spend to solve this problem. It’s only a tool and should be treated as such.

FINAL NOTICE: Use this code at your own risk. The code does some crazy stuff directly to SharePoint content database. I will not take any responsibility on anything that you do with the application and/or any part of it and it’s code.

Check out the CodePlex Project for the code:

https://recyclebinrestoreapp.codeplex.com/