(This tutorial is the rewriting of the codeplex documentation tutorial part 3)

In this sample, we will create a first application with all the required features to be able to synchronize your device with an on premise database.

Here is the source code (Source and database backup) : SampleSync.zip

First of all, we need to implement the full Synchronization mechanism. You need to follow the steps from the first and second chapter of the documentation :

  1. Download and install Sync Framework and Sync Toolkit for WinRT
  2. Generate the code for your database, your server and your client application
  3. Create a fully functional application
  4. Create a filtered application based on a template scope
  5. Handle conflicts

The Server side

The server side is quite simple, if you have correctly implemented what we see in the previous chapters.

image[16]

In this sample, we will manage some blob object (customer images) so I have configured my server for batch mode :

public class DefaultScopeSyncService : SyncService<DefaultScopeOfflineEntities> {
        
    public static void InitializeService(ISyncServiceConfiguration config) {

        config.ServerConnectionString = ConfigurationManager.ConnectionStrings["Fabrikam"].ConnectionString;
        config.SetEnableScope("DefaultScope");

        config.SetDefaultSyncSerializationFormat(SyncSerializationFormat.ODataJson);
        config.SetConflictResolutionPolicy(ConflictResolutionPolicy.ServerWins);

        // for debug purpose, I need the whole error details
        config.UseVerboseErrors = true;

        // Because my json may be huge, i enable the batch mode
        config.SetDownloadBatchSize(2 * 1024);

        config.UseVerboseErrors = true;
    }
}

The client side application

This application is a simple master details application :

image5

image11 You can create, update or delete any items, and of course, you can synchronize your local database with your on premise database.

image[15]

This sample is developed with Visual Studio 2013 for Windows 8.1 application. You need to follow the steps described here to get the correct assemblies : Installation for Windows 8.1

Synchronization process

image18

The synchronization is straightforward, don’t forget to check the connectivity.

The Synchronization process is included in the DataService class :

public async Task<CacheRefreshStatistics> Sync()
{
    try
    {
        var result = await this.SyncContext.SynchronizeAsync();

        // For debug time
        if (result.Error != null)
            Debug.WriteLine(result.Error.Message);

        return result;

    }
    catch (Exception e)
    {
        Debug.WriteLine(e.Message);
        throw;
    }
}

In this sample, you will find a Context class, where i check the connectivity.

I used a MVVM pattern, so my Sync process is included in a RelayCommand. This command is included in my ItemsPageViewModel

Then I used MessageDialog and ProgressBar to make a good user experience :

image35

public RelayCommand SyncCommand
{
    get
    {
        if (syncCommand != null) return syncCommand;

        syncCommand = new RelayCommand(async () =>
        {
            try
            {
                this.IsSyncing = true;

                var attenteTask = Task.Delay(1000);

                var stats = await DataService.Current.Sync();

                await attenteTask;

                if (stats.Error != null)
                {
                    var messageDialog = new MessageDialog("An error occured during sync session : " + 
Environment.NewLine + stats.Error.Message); await messageDialog.ShowAsync(); } else { if (stats.TotalDownloads > 0) await GetServiceTickets(); var messageDialog = new MessageDialog( "Sync session completed: " + Environment.NewLine + "Total downloads :" + stats.TotalDownloads + Environment.NewLine + "Total uploads :" + stats.TotalUploads + Environment.NewLine + "Total conflicts :" + stats.TotalSyncErrors + Environment.NewLine + "Ellapsed Time :" + stats.EndTime.Subtract(stats.StartTime).TotalSeconds + " s."); await messageDialog.ShowAsync(); } this.IsSyncing = false; } catch (Exception ex) { Debug.WriteLine(ex.Message); throw; } }, () => { var connectionIsOk = Context.Current.ConnectivityLevel == NetworkConnectivityLevel.InternetAccess; var isNotSyncing = !this.IsSyncing; return (connectionIsOk && isNotSyncing); }); return syncCommand; } }

Checking the connectivity is pretty StraightForward :

public Context()
{
    NetworkInformation.NetworkStatusChanged += NetworkInformation_NetworkStatusChanged;
    this.UpdateConnectionProfile();
}
private static Context current;
public static Context Current
{
    get
    {
        if (current == null)
            current = new Context();

        return current;
    }
}

 
private NetworkConnectivityLevel connectivityLevel;

public NetworkConnectivityLevel ConnectivityLevel
{
    get
    {
        return connectivityLevel;
    }
    set
    {
        if (value == connectivityLevel)
            return;

        connectivityLevel = value;

        base.RaisePropertyChanged("ConnectivityLevel");
    }
}

private void UpdateConnectionProfile()
{
    var connectionProfile = NetworkInformation.GetInternetConnectionProfile();

    if (connectionProfile != null)
        this.ConnectivityLevel = connectionProfile.GetNetworkConnectivityLevel();
    else
        this.ConnectivityLevel = NetworkConnectivityLevel.None;

}

private void NetworkInformation_NetworkStatusChanged(object sender)
{
    Context.Current.UpdateConnectionProfile();
}

Querying the local database

In the Windows 8.1 packages, you have the SQLite Winrt package too. I used this wrapper to query my local database. Again you will find those methods in the DataService class.

I used a SettingFlyout object (See this blog article to see how I manage a SettingFlyout item) to show a complete form with all my service ticket fields:

image36

 Select sample :

public async Task<List<ServiceTickets>> GetTickets()
{

    List<ServiceTickets> serviceTickets = new List<ServiceTickets>();
    try
    {
        using (Database connection = new Database(this.DatabaseStorageFolder, this.DatabaseName))
        {
            await connection.OpenAsync(SqliteOpenMode.OpenRead);

            const string commandText = "SELECT * FROM ServiceTickets";

            using (var stmt = await connection.PrepareStatementAsync(commandText))
            {
                while (await stmt.StepAsync())
                {
                    ServiceTickets ticket = FillTicketFromReader(stmt);
                    serviceTickets.Add(ticket);
                }
            }
        }
    }
    catch (Exception ex)
    {
        var result = Database.GetSqliteErrorCode(ex.HResult);
        Debug.WriteLine(result);
        throw;
    }


    return serviceTickets;
}

 Insert sample :

internal async Task InsertServiceTickets(List<ServiceTickets> serviceTickets)
{
    Database connection = null;
    try
    {
        using (connection = new Database(this.DatabaseStorageFolder, this.DatabaseName))
        {
            await connection.OpenAsync(SqliteOpenMode.OpenReadWrite);

            await connection.ExecuteStatementAsync("Begin Transaction");

            const string commandText = "Insert Into ServiceTickets " +
                                        "(Title, Description, StatusValue, EscalationLevel, Opened, Closed, CustomerID, ServiceTicketID) " +
                                        "Values (?, ?, ?, ?, ?, ?, ?, ?)";

            using (var stmt = await connection.PrepareStatementAsync(commandText))
            {
                foreach (var serviceTicket in serviceTickets)
                {
                    BindServiceTicketParameter(stmt, serviceTicket);

                    await stmt.StepAsync();

                    stmt.Reset();
                }
            }
            await connection.ExecuteStatementAsync("Commit Transaction");

        }

    }
    catch (Exception ex)
    {
        var result = Database.GetSqliteErrorCode(ex.HResult);
        Debug.WriteLine(result);

        if (connection != null)
            connection.ExecuteStatementAsync("Rollback Transaction").AsTask().Wait();
        throw;
    }
}

Update sample :

internal async Task UpdateServiceTickets(List<ServiceTickets> serviceTickets)
{
    Database connection = null;
    try
    {
        using (connection = new Database(this.DatabaseStorageFolder, this.DatabaseName))
        {
            await connection.OpenAsync(SqliteOpenMode.OpenReadWrite);

            await connection.ExecuteStatementAsync("Begin Transaction");

            const string commandText = "Update ServiceTickets Set " +
                                        "Title = ?, Description = ?, StatusValue = ?, EscalationLevel = ?, " +
                                        "Opened = ?, Closed = ?, CustomerID = ? " +
                                        "Where ServiceTicketID = ?";

            using (var stmt = await connection.PrepareStatementAsync(commandText))
            {
                foreach (var serviceTicket in serviceTickets)
                {
                    BindServiceTicketParameter(stmt, serviceTicket);

                    await stmt.StepAsync();

                    stmt.Reset();
                }
            }
            await connection.ExecuteStatementAsync("Commit Transaction");

        }

    }
    catch (Exception ex)
    {
        var result = Database.GetSqliteErrorCode(ex.HResult);
        Debug.WriteLine(result);

        if (connection != null)
            connection.ExecuteStatementAsync("Rollback Transaction").AsTask().Wait();
        throw;
    }
}

Delete sample:

public async Task<bool> DeleteTicket(Guid id)
{

    try
    {
        using (Database connection = new Database(this.DatabaseStorageFolder, this.DatabaseName))
        {
            await connection.OpenAsync(SqliteOpenMode.OpenReadWrite);

            const string commandText = "DELETE FROM ServiceTickets Where ServiceTicketID = ?";

            using (var stmt = await connection.PrepareStatementAsync(commandText))
            {
                stmt.BindTextParameterAt(1, id.ToString());

                if (await stmt.StepAsync())
                    return true;
            }
        }

        return false;
    }
    catch (Exception ex)
    {
        var result = Database.GetSqliteErrorCode(ex.HResult);
        Debug.WriteLine(result);
        throw;
    }

}

Managing Images (and blobs)

In this sample, you can see that we manage some images. They come from the on premise database.

image43

There is no specific server code, but just a quick reminder on how I have insert those thumbnails in my server database :

(this code is not included in the sample zip package)

static void Main(string[] args)
{
    foreach (var imgFile in Directory.EnumerateFiles(@"C:\Users\spertus\Pictures\Persons"))
    {
        var imgFileInfo = new FileInfo(imgFile);
        var img = System.Drawing.Bitmap.FromFile(imgFile);

        using (MemoryStream msImg = new MemoryStream())
        {
            img.Save(msImg, img.RawFormat);

            using (SqlConnection connection = 
new SqlConnection(ConfigurationManager.ConnectionStrings["Fabrikam"].ConnectionString)) { SqlCommand command = new SqlCommand("Insert into Images (ID, Name, Image, ImageFormat,
Length, Width, Height) Values
(@ID, @Name, @Image, @ImageFormat,
@Length, @Width, @Height) "
); command.Connection = connection; SqlParameter p = new SqlParameter("@Id", System.Data.SqlDbType.UniqueIdentifier); p.Value = Guid.NewGuid(); command.Parameters.Add(p); p = new SqlParameter("@Image", System.Data.SqlDbType.VarBinary); p.Value = msImg.ToArray(); command.Parameters.Add(p); p = new SqlParameter("@Name", System.Data.SqlDbType.VarChar, 255); p.Value = imgFileInfo.Name; command.Parameters.Add(p); p = new SqlParameter("@ImageFormat", System.Data.SqlDbType.VarChar, 50); p.Value = GetImageFormat(img.RawFormat, msImg.GetBuffer()); command.Parameters.Add(p); p = new SqlParameter("@Length", System.Data.SqlDbType.Int); p.Value = msImg.Length; command.Parameters.Add(p); p = new SqlParameter("@Width", System.Data.SqlDbType.Int); p.Value = img.Width; command.Parameters.Add(p); p = new SqlParameter("@Height", System.Data.SqlDbType.Int); p.Value = img.Height; command.Parameters.Add(p); connection.Open(); command.ExecuteNonQuery(); connection.Close(); } } } }

From the client point of view, querying the local SQLite database and display the image is, again, straightforward :

Getting the Images object from the local SQLite database:

public async Task<List<Images>> GetImages()
{

    List<Images> imgs = new List<Images>();
    try
    {
        using (Database connection = new Database(this.DatabaseStorageFolder, this.DatabaseName))
        {
            await connection.OpenAsync(SqliteOpenMode.OpenRead);

            const string commandText = "SELECT * FROM Images";

            using (var stmt = await connection.PrepareStatementAsync(commandText))
            {
                while (await stmt.StepAsync())
                {
                    Images img = FillImageFromReader(stmt);
                    imgs.Add(img);
                }
            }
        }
    }
    catch (Exception ex)
    {
        var result = Database.GetSqliteErrorCode(ex.HResult);
        Debug.WriteLine(result);
        throw;
    }


    return imgs;
}

Create a BitmapImage from the Byte[] object :

internal async Task<BitmapImage> GetBitmapImageFromImageEntity(Images img)
{
    var bitmapImage = new BitmapImage();

    var stream = new InMemoryRandomAccessStream();
    await stream.WriteAsync(img.Image.AsBuffer());
    stream.Seek(0);

    bitmapImage.SetSource(stream);
    return bitmapImage;

}

In the next chapter, we will see how to manage a filtered synchronization, based on this sample.