Stop Syncing Record Deletes with Sync Framework

I had a requirement where I wanted to sync records from source to destination, in the destination there will be some updates and then I wanted to sync those records back from destination to source. Of course this was a very common requirement and Sync Framework definitely supports these kind of situations and I was using the SyncDirectionOrder.UploadAndDownload in my SyncOrchestrator. I was monitoring my source database and my sync was perfectly working and I was happy until I saw some record deletes in my source database which I cannot accept. I examined the case and actually there is nothing wrong with the sync. What happened was records in the destination database got deleted accidently and those changes were just synced back to source.

Now I needed to stop syncing record deletes and this is only from destination to source, that is if a source record gets deleted, it should be deleted in the destination, but if a destination record gets deleted, it should not be deleted in the source. June Tabadero has wrote a nice post about MANIPULATING THE CHANGE DATASET IN SYNC FX and by manipulating the change dataset, I did able to accomplish what I wanted. In addition to things mentioned in June Tabadero's post, thought to add some new things. 

Before starting manipulating the change dataset, I think it’s best to understand the event order of SqlSyncProviders. I have two SqlSyncProviders which are localProvider and remoteProvider. localProvider is for the source and remoteProvider is for the destination.

Event Order

So here since we are doing a UploadAndDownload, all these events will be fired in the above order, first Upload events and then the Download events. But for my requirement (stop destination deletes cascaded to source), I am mainly interested in two events. That’s remoteProvider_ChangesSelected and localProvider_ApplyingChanges. To access deleted rows on the destination and to manipulate the change dataset, I can use above mentioned two events which will get fired when remote provider has completed selecting changes and when local provider is trying to apply those changes.

remoteProvider.ChangesSelected

void remoteProvider_ChangesSelected(object sender, DbChangesSelectedEventArgs e)

{

    for (int i = 0; i < e.Context.DataSet.Tables.Count; i++)

    {

        var dataTable = e.Context.DataSet.Tables[i];

        for (int j = 0; j < dataTable.Rows.Count; j++)

        {

            var row = dataTable.Rows[j];

            if (row.RowState == DataRowState.Deleted)

            {

                dataTable.Rows.Remove(row);

                j--;

            }

        }

    }

}

localProvider.ApplyingChanges

void localProvider_ApplyingChanges(object sender, DbApplyingChangesEventArgs e)

{

    for (int i = 0; i < e.Context.DataSet.Tables.Count; i++)

    {

        var dataTable = e.Context.DataSet.Tables[i];

        for (int j = 0; j < dataTable.Rows.Count; j++)

        {

            var row = dataTable.Rows[j];

            if (row.RowState == DataRowState.Deleted)

            {

                dataTable.Rows.Remove(row);

                j--;

            }

        }

    }

}

In these two events, the method body is the same. Here I am iterating through all the tables in the change dataset. If you want to stop syncing deletes only for a specific table, you can do that too. I can use any event from above two events to remove the rows in change dataset which are marked as deleted. Please note that when deleting a row which is marked as deleted in the change dataset I am decrementing the value of “j” by 1. That is because for an example if a row is deleted from the 0th position of the data table, I have to move back to new row which is at 0th position now and check whether that row is marked as deleted.

I have created a sample and I am uploading it to my skydrive with database backup files. If you test this out, you can see that when the destination records gets deleted it will not be reflected in the source. But here there is a another problem, that is when the destination records gets deleted, they will not get synced back from the source. That is because source has no idea that destination records are deleted. For that you will have to do a dummy update on source records, so they will get synced back to destination.

Hope this helps.

Happy Coding.

Regards,

Jaliya

Connecting to SSAS Cube using Silverlight

Last week when I was watching a presentation of a third party reporting product, I saw some nice animated charts. I wanted to create something similar to those charts and when I checked on SSRS, unfortunately what I wanted isn't possible with SSRS. So I moved into Silverlight Charts and I was really surprised with what I can do with Silverlight chart control. But today I am not going to write about Silverlight Chart control, I am going to write about how to grab data from a SSAS Cube using Silverlight and then we can display on any Silverlight reporting control. And today I will be displaying data through a Silverlight chart control.

I will be using ADOMD.NET and sometimes back I wrote a post about ADOMD.NET. So I will not be covering the basics on ADOMD.NET in this post.

I will start off by creating a Silverlight application. Please make sure to install Silverlight toolkit. As I know Silverlight chart control was initially release with Silverlight 3 toolkit. The version I am using is Silverlight 5 with it's toolkit. After creating the Silverlight project, now I have a solution which has two projects, one is Silverlight project and the other is a web project to host my Silverlight application.

As you might already know I can’t use ADOMD.NET in the Silverlight project. Because I can’t add a reference to dlls which was not built against the Silverlight runtime and ADOMD.NET dlls are not build against Silverlight runtime. So for that what I can do is add needed ADOMD.NET references to my web project and somehow use them from my Silverlight project. Since I can only add reference to Silverlight projects from my Silverlight project, what I am going to do is I am going to create a WCF service in my web application and I am going to expose functions there, so my Silverlight project can consume them. Hope you all got a overall idea of what I am going to do here.

I am using ADOMD.NET Client Programming and I have added a reference to “Microsoft.AnalysisServices.AdomdClient.dll” which is located at,

C:\Program Files\Microsoft.NET\ADOMD.NET\100

Now I am creating a WCF service named “CubeConnector”.

ICubeConnector.cs

using System.Collections.Generic;

using System.ServiceModel;

namespace SilverlightApplication1.Web

{

    [ServiceContract]

    public interface ICubeConnector

    {

        [OperationContract]

        IEnumerable<Dictionary<string, object>> GetData(string query);

    }

}

CubeConnector.svc

using System;

using System.Collections.Generic;

using System.Data;

using System.Linq;

using Microsoft.AnalysisServices.AdomdClient;

namespace SilverlightApplication1.Web

{

    public class CubeConnector : ICubeConnector

    {

        public IEnumerable<Dictionary<string, object>> GetData(string query)

        {

            var table = GetDataTable(query);

            var columns = table.Columns.Cast<DataColumn>();

            return table.AsEnumerable().Select(r => columns.Select(c => new { Column = c.ColumnName, Value = r[c.ColumnName] })

                             .ToDictionary(i => i.Column, i => i.Value != DBNull.Value ? i.Value : null));

        }

        private DataTable GetDataTable(string query)

        {

            AdomdConnection conn = new AdomdConnection("Data Source=localhost; Catalog=MyCube");

            AdomdDataAdapter adapter = new AdomdDataAdapter();
            adapter.SelectCommand = new AdomdCommand(query, conn);
            var table = new DataTable();
            conn.Open();

            try

            {

                adapter.Fill(table);

            }

            finally

            {

                conn.Close();

            }

            return table;

        }

    }

}

I have exposed a operation contract which is GetData and it returns a “IEnumerable<Dictionary<string, object>>” and accepts a query of type “string”.

Now in my Silverlight application I have added a Service reference to the created WCF service. I have also added a Chart control to the MainPage.xaml. To my GetData operation contract in WCF Service, I need to pass a query. I am passing a MDX query to query the SSAS cube. In here my query will give me the following result and it’s what I wanted in the chart.

Query Result

In the chart Y axis, I need to show the amount as a currency. So I am adding some styling to the “Y axis” of my chart and to the tooltip (I am writing down the styling code, to make the example complete.).

<UserControl.Resources>

    <Style x:Name="CustomDataPoint" TargetType="toolkit:ColumnDataPoint">

        <Setter Property="DependentValueStringFormat" Value="{}{0:C}"/>

    </Style>

    <Style x:Name="CustomAxisLabel" TargetType="toolkit:AxisLabel">

        <Setter Property="StringFormat" Value="{}{0:C}"/>

    </Style>

</UserControl.Resources>

This is my chart with styles applied.

<toolkit:Chart Name="MyChart" Title="Amount by Account Types" HorizontalAlignment="Left" Margin="10,10,0,0" VerticalAlignment="Top" Height="479" Width="714" >

    <toolkit:ColumnSeries Title="Amount" DependentValueBinding="{Binding SeriesValue}" IndependentValueBinding="{Binding SeriesName}" AnimationSequence="FirstToLast" IsSelectionEnabled="True" DataPointStyle="{StaticResource CustomDataPoint}" RenderTransformOrigin="0.481,0.467"/>

    <toolkit:Chart.Axes>

        <toolkit:LinearAxis ShowGridLines="True" Orientation="Y" AxisLabelStyle="{StaticResource CustomAxisLabel}"></toolkit:LinearAxis>

    </toolkit:Chart.Axes>

</toolkit:Chart>

Now moving back to code behind, I am passing my MDX query and I am calling the WCF service.

using System;

using System.Collections.Generic;

using System.Windows.Controls;

using System.Windows.Controls.DataVisualization.Charting;

namespace SilverlightApplication1

{

    public partial class MainPage : UserControl

    {

        public MainPage()

        {

            InitializeComponent();

            svcCubeConnector.CubeConnectorClient client = new svcCubeConnector.CubeConnectorClient();

            client.GetDataCompleted += client_GetDataCompleted;

            client.GetDataAsync("SELECT NON EMPTY { [Measures].[AMOUNT] } ON COLUMNS, NON EMPTY { ([Account Type].[ACCOUNT DESC].[ACCOUNT DESC].ALLMEMBERS ) } DIMENSION PROPERTIES MEMBER_CAPTION ON ROWS FROM [MyCube]");

        }

        void client_GetDataCompleted(object sender, svcCubeConnector.GetDataCompletedEventArgs e)

        {

            IEnumerable<Dictionary<string, Object>> result = e.Result;

            List<ChartClass> chartCollection = new List<ChartClass>();

            object seriesName;

            object seriesValue;

            foreach (Dictionary<String, Object> _item in result)

            {

                // I am querying Dictionary with the Key

                _item.TryGetValue("[Account Type].[ACCOUNT DESC].[ACCOUNT DESC].[MEMBER_CAPTION]", out seriesName);

                _item.TryGetValue("[Measures].[AMOUNT]", out seriesValue);

                chartCollection.Add(new ChartClass { SeriesName = seriesName.ToString(), SeriesValue = Convert.ToDouble(seriesValue) });

            }

            ((ColumnSeries)MyChart.Series[0]).ItemsSource = chartCollection;

        }

    }

    public class ChartClass

    {

        public string SeriesName { get; set; }

        public double SeriesValue { get; set; }

    }

}

Here I am calling the GetData method using event-based asynchronous pattern (For more information on  event-based asynchronous pattern and Asynchronous Operations in WCF, read Asynchronous Operations in WCF). In GetDataCompleted event, I am querying the result and I am binding the Silverlight Chart to a collection (here I am querying the result using hard coded keys which is not a good practise).

That’s all. Finally I am getting the following chart.

Chart

I have uploaded the full example to my SkyDrive. Appreciate your feedback.



Happy Coding.

Regards,
Jaliya

Apps for SharePoint : Map App (Bing Maps inside SharePoint) v1.0.0.2

Map App version 1.0.0.2 has been released. This version will contain the following new enhancements and new features in addition to previous version’s features.

Enhancements

• Updated UI – Inherits the site design

New Features

• Introducing Directions Module
• Get Directions
• Drag and Customize the Routes

Updated UI

Updated UI

Updated UI

Updated UI - Directions Module

Updated UI - Directions Module (Drag)

Enjoy the app in your SharePoint 2013 environment and appreciate your feedback.

Happy Coding.

Regards,
Jaliya