Synchronize with Parameter Based Filters - Microsoft Sync Framework

Let’s imagine a scenario where you want to sync data based on a filter. For an example your server database has an “EMPLOYEE” master table and it has all the employee records of all your branches. Now you have two branch office databases for your two branches, and you want to download relevant employee data to each of these branch databases.

This is my “EMPLOYEE” table in the Server database.

Employee table in the Server

My requirement is download all employee information to relevant branch databases and that is all employees who has branch id as “B0001” to New York database and all employees who has branch id as “B0002” to Boston database.

Now let’s see how to implement such a synchronization using Microsoft Sync Framework. Here a filter is used to restrict the data that is being synchronized between each branch and the server. With the release of Sync Framework 2.1 Microsoft has introduced dynamic parameter based filtering support and I am going to use dynamic parameter based filtering for this.

string filterTemplate = "filter_template";

DbSyncScopeDescription scopeDesc = new DbSyncScopeDescription(filterTemplate);
scopeDesc.UserComment = "Template for filtering based on branch id.";
DbSyncTableDescription EMPLOYEE = SqlSyncDescriptionBuilder.GetDescriptionForTable("EMPLOYEE", serverConn);
scopeDesc.Tables.Add(EMPLOYEE);

//creating a provisioning template
SqlSyncScopeProvisioning serverProvisionTemplate = new SqlSyncScopeProvisioning(serverConn, scopeDesc, SqlSyncScopeProvisioningType.Template);
serverProvisionTemplate.Tables["EMPLOYEE"].AddFilterColumn("BRANCH_ID");
serverProvisionTemplate.Tables["EMPLOYEE"].FilterClause = "[side].[BRANCH_ID] = @branchId";
SqlParameter param = new SqlParameter("@branchId", SqlDbType.VarChar, 5);
serverProvisionTemplate.Tables["EMPLOYEE"].FilterParameters.Add(param);
if (!serverProvisionTemplate.TemplateExists(filterTemplate))
{
    serverProvisionTemplate.Apply();
}

Here first I am doing is creating a provisioning template. Creating a provisioning template is same as creating a provisioning scope, except the only thing is the template cannot be used for synchronization. In my provisioning template on the server I am mentioning my “EMPLOYEE” table has filter column which is “BRANCH_ID” and the filter clause is where “BRANCH_ID” is equal to parameterized “branchid”.

Once the template is created you can see two new records added to “scope_config” and “scope_templates” tables in Server database. In the row created in “scope_config” table, you can see config data of the template which is an XML. Here you can see things we mentioned in the code such as parameter name, filter clause, filter column etc. in the XML.

<SqlSyncProviderScopeConfiguration xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" IsTemplate="true">

  <Adapter Name="[EMPLOYEE]" GlobalName="[EMPLOYEE]" TrackingTable="[EMPLOYEE_tracking]" SelChngProc="[EMPLOYEE_selectchanges]" SelRowProc="[EMPLOYEE_selectrow]" InsProc="[EMPLOYEE_insert]" UpdProc="[EMPLOYEE_update]" DelProc="[EMPLOYEE_delete]" InsMetaProc="[EMPLOYEE_insertmetadata]" UpdMetaProc="[EMPLOYEE_updatemetadata]" DelMetaProc="[EMPLOYEE_deletemetadata]" BulkTableType="[EMPLOYEE_BulkType]" BulkInsProc="[EMPLOYEE_bulkinsert]" BulkUpdProc="[EMPLOYEE_bulkupdate]" BulkDelProc="[EMPLOYEE_bulkdelete]" InsTrig="[EMPLOYEE_insert_trigger]" UpdTrig="[EMPLOYEE_update_trigger]" DelTrig="[EMPLOYEE_delete_trigger]">

    <Col name="EMPLOYEE_ID" type="int" param="@P_1" pk="true" />

    <Col name="FIRST_NAME" type="varchar" size="50" null="true" param="@P_2" />

    <Col name="LAST_NAME" type="varchar" size="50" null="true" param="@P_3" />

    <Col name="BRANCH_ID" type="varchar" size="5" null="true" param="@P_4" />

    <FilterParam name="@branchId" />

    <FilterClause>[side].[BRANCH_ID] = @branchId</FilterClause>

    <FilterCol>BRANCH_ID</FilterCol>

  </Adapter>

</SqlSyncProviderScopeConfiguration>

Next based on this template I am provisioning the server. I am supplying a parameter value and some comment and I am doing the provisioning.

string branchId = "B0001";

string branchFilteredScope = string.Format("{0}_Scope", branchId);
SqlSyncScopeProvisioning serverProvision = new SqlSyncScopeProvisioning(serverConn, scopeDesc);
serverProvision.PopulateFromTemplate(branchFilteredScope, filterTemplate);
serverProvision.Tables["EMPLOYEE"].FilterParameters["@branchId"].Value = branchId;
serverProvision.UserComment = string.Format("Only for {0} branch.", branchId);
if (!serverProvision.ScopeExists(branchFilteredScope))
{
   serverProvision.Apply();
}

When the provisioning is completed, a scope created on the server. Now when this step is completed if you examine the tables in “scope_info” and “scope_parameters”, again you can see two new rows created. “scope_info” will contain a row including the details of the particular provision we have done now and “scope_parameters” will contain a row including the details of parameters for the above provision.

Now I am going to provision the branch database. It’s basic provision; I am getting the relevant scope from the server and applying it on the client.

DbSyncScopeDescription serverScopeDescForBranch = SqlSyncDescriptionBuilder.GetDescriptionForScope(branchFilteredScope, null, serverConn);

SqlSyncScopeProvisioning branchProvision = new SqlSyncScopeProvisioning(branchConn, serverScopeDescForBranch);
if (!branchProvision.ScopeExists(branchFilteredScope))
{

    branchProvision.Apply();

}

We have done the difficult part and the next step is to do the synchronization. Once the synchronization is completed, I can see the following result on two branch databases.

Result

I am uploading a full sample with database backups, so you can play around in anyway you want. Appreciate your feedback.

Happy Coding.

Regards,
Jaliya

Synchronize Tables with Different Table Names - Microsoft Sync Framework

Let’s say you want to sync two tables which have two different names with Microsoft Sync Framework. In my Remote database I have a table named “CUSTOMER” and in my Local database I have two tables named “CUSTOMER” and “NEW_CUSTOMER.” I want table “CUSTOMER” in remote database to be downloaded in to table “NEW_CUSTOMER”.

GlobalName property of DbSyncTableDescription is used for this. When provisioning the server I am setting up the GlobalName property of my table “CUSTOMER” to “NEW_CUSTOMER”.

Here is where I am setting GlobalName to the “Customer” tables’ DbSyncTableDescription when provisioning the server,

SqlConnection serverConn = new SqlConnection("Data Source=.; Initial Catalog=SyncDBServer; Integrated Security=True");

DbSyncScopeDescription scopeDesc = new DbSyncScopeDescription("MySyncScope");
DbSyncTableDescription cusTableDesc = SqlSyncDescriptionBuilder.GetDescriptionForTable("CUSTOMER", serverConn);
cusTableDesc.GlobalName = "NEW_CUSTOMER";
scopeDesc.Tables.Add(cusTableDesc);

There is something important to note when provisioning the client. Most of the times when provisioning the client what we are doing is getting the scope description which we have applied on the server and then applying it on the client. Please remember that, here we can’t do it. We should create the provision scope in the client in the following way.

SqlConnection clientConn = new SqlConnection("Data Source=.; Initial Catalog=SyncDBClient; Integrated Security=True");

DbSyncScopeDescription scopeDesc = new DbSyncScopeDescription("MySyncScope");
DbSyncTableDescription cusTableDesc = SqlSyncDescriptionBuilder.GetDescriptionForTable("NEW_CUSTOMER", clientConn);
scopeDesc.Tables.Add(cusTableDesc);

Result

I am uploading a full sample to my sky drive.

Happy Coding.

Regards,
Jaliya

Database Synchronization with Microsoft Sync Framework

Microsoft Sync Framework is a comprehensive synchronization platform enabling collaboration and offline for applications, services and devices. This is actually shipped with Visual Studio 2008 for the first time and now the current stable version is Microsoft Sync Framework 2.1. There are number of providers in Sync Framework which supports many common data sources.The following are the providers included,

• Database synchronization providers: Synchronization for ADO.NET-enabled data sources.
• File synchronization provider: Synchronization for files and folders.
• Web synchronization components: Synchronization for FeedSync feeds such as RSS and ATOM feeds.

I am not going to write much intro about Microsoft Sync Framework as you can find many articles on internet. Let’s see how database synchronization works with Microsoft Sync Framework by a simple example.

I have two Microsoft SQL Server 2008R2 Databases which are “SyncDBServer” and “SyncDBClient”. Unfortunately I am having them on the same server and actually what I want to demonstrate is data synchronization between two remote database servers. Since I don’t have such environment I am using the same server, because it will not do any harm to the logic I am using here.

In “SyncDBServer”, I have three tables which are CUSTOMER, PRODUCT and ORDER. I have filled up these tables with sample data and “SyncDBClient” database has no tables. What I am going to do today is synchronize only the CUSTOMER and PRODUCT tables of “SyncDBServer” with “SyncDBClient”. In this case Microsoft Sync Framework will create CUSTOMER and PRODUCT tables in “SyncDBClient” for me.

“SyncDBServer” and “SyncDBClient”

First I need to prepare or provision these two databases for the synchronization. For that I will have to write some codes. Let’s create a console application for provisioning “SyncDBServer” and I am naming it as “ProvisionServer”. I am adding following references (I am using Microsoft Sync Framework 2.1 Software Development Kit (SDK)).

• Microsoft.Synchronization.Data : Version 3.1.0.0
• Microsoft.Synchronization.Data.SqlServer: Version 3.1.0.0

using System.Data.SqlClient;
using Microsoft.Synchronization.Data;
using Microsoft.Synchronization.Data.SqlServer;

namespace ProvisionServer
{
    class Program
    {
        static void Main(string[] args)
        {
            // connect to server database
            SqlConnection serverConn = new SqlConnection("Data Source=.; Initial Catalog=SyncDBServer; Integrated Security=True");

            // define a new scope named MySyncScope
            DbSyncScopeDescription scopeDesc = new DbSyncScopeDescription("MySyncScope");

            // get the description of the CUSTOMER & PRODUCT table from SERVER database
            DbSyncTableDescription cusTableDesc = SqlSyncDescriptionBuilder.GetDescriptionForTable("CUSTOMER", serverConn);
            DbSyncTableDescription prodTableDesc = SqlSyncDescriptionBuilder.GetDescriptionForTable("PRODUCT", serverConn);

            // add the table description to the sync scope definition
            scopeDesc.Tables.Add(cusTableDesc);
            scopeDesc.Tables.Add(prodTableDesc);

            // create a server scope provisioning object based on the MySyncScope
            SqlSyncScopeProvisioning serverProvision = new SqlSyncScopeProvisioning(serverConn, scopeDesc);

            // skipping the creation of table since table already exists on server
            serverProvision.SetCreateTableDefault(DbSyncCreationOption.Skip);

            // start the provisioning process
            serverProvision.Apply();

            Console.WriteLine("Server Successfully Provisioned.");
            Console.ReadLine();
        }
    }
}

Here I am accessing “SyncDBServer” database and specified my synchronization scope. Now when I run this project, I can see the message “Server Successfully Provisioned.” and most importantly I can see some new tables created in  “SyncDBServer” database.

“Server Successfully Provisioned.”

“SyncDBServer”

Now I am creating a console application for provisioning “SyncDBClient” and I am naming it as “ProvisionClient”. Again I am adding the same references.

using System;
using System.Data.SqlClient;
using Microsoft.Synchronization.Data;
using Microsoft.Synchronization.Data.SqlServer;

namespace ProvisionClient
{
    class Program
    {
        static void Main(string[] args)
        {
            // create a connection to the client database
            SqlConnection clientConn = new SqlConnection(@"Data Source=.; Initial Catalog=SyncDBClient; Integrated Security=True");

            // create a connection to the server database
            SqlConnection serverConn = new SqlConnection("Data Source=.; Initial Catalog=SyncDBServer; Integrated Security=True");

            // get the description of SyncScope from the server database
            DbSyncScopeDescription scopeDesc = SqlSyncDescriptionBuilder.GetDescriptionForScope("MySyncScope", serverConn);

            // create server provisioning object based on the SyncScope
            SqlSyncScopeProvisioning clientProvision = new SqlSyncScopeProvisioning(clientConn, scopeDesc);

            // starts the provisioning process
            clientProvision.Apply();

            Console.WriteLine("Client Successfully Provisioned.");
            Console.ReadLine();
        }
    }
}

Here I am accessing both “SyncDBServer” and “SyncDBClient”. I am getting the synchronization scope from “SyncDBServer” and applying it on the “SyncDBClient”. Now when I run this project, I can see the message “Client Successfully Provisioned.” and here also I can see some new tables created in “SyncDBClient" database including CUSTOMER and PRODUCT tables. But there will be no data in these two tables.

“Client Successfully Provisioned.”

“SyncDBClient"

Now finally what’s left to do is the synchronization. For that I am creating a console application and I am naming it as “ExecuteSync”. I am adding following references.

• Microsoft.Synchronization: Version 2.1.0.0
• Microsoft.Synchronization.Data : Version 3.1.0.0
• Microsoft.Synchronization.Data.SqlServer: Version 3.1.0.0

using System;
using System.Data.SqlClient;
using Microsoft.Synchronization;
using Microsoft.Synchronization.Data;
using Microsoft.Synchronization.Data.SqlServer;

namespace ExecuteSync
{
    class Program
    {
        static void Main(string[] args)
        {
            SqlConnection clientConn = new SqlConnection(@"Data Source=.; Initial Catalog=SyncDBClient; Integrated Security=True");

            SqlConnection serverConn = new SqlConnection("Data Source=.; Initial Catalog=SyncDBServer; Integrated Security=True");

            // create the sync orhcestrator
            SyncOrchestrator syncOrchestrator = new SyncOrchestrator();

            // set local provider of orchestrator to a sync provider associated with the 
            // MySyncScope in the client database
            syncOrchestrator.LocalProvider = new SqlSyncProvider("MySyncScope", clientConn);

            // set the remote provider of orchestrator to a server sync provider associated with
            // the MySyncScope in the server database
            syncOrchestrator.RemoteProvider = new SqlSyncProvider("MySyncScope", serverConn);

            // set the direction of sync session to Upload and Download
            syncOrchestrator.Direction = SyncDirectionOrder.UploadAndDownload;

            // subscribe for errors that occur when applying changes to the client
            ((SqlSyncProvider)syncOrchestrator.LocalProvider).ApplyChangeFailed += new EventHandler<DbApplyChangeFailedEventArgs>(Program_ApplyChangeFailed);

            // execute the synchronization process
            SyncOperationStatistics syncStats = syncOrchestrator.Synchronize();

            // print statistics
            Console.WriteLine("Start Time: " + syncStats.SyncStartTime);
            Console.WriteLine("Total Changes Uploaded: " + syncStats.UploadChangesTotal);
            Console.WriteLine("Total Changes Downloaded: " + syncStats.DownloadChangesTotal);
            Console.WriteLine("Complete Time: " + syncStats.SyncEndTime);
            Console.WriteLine(String.Empty);
            Console.ReadLine();
        }

        static void Program_ApplyChangeFailed(object sender, DbApplyChangeFailedEventArgs e)
        {
            // display conflict type
            Console.WriteLine(e.Conflict.Type);

            // display error message 
            Console.WriteLine(e.Error);
        }
    }
}

Here I am mentioning the synchronization direction(Upload or Download or UploadAndDownload or DownloadAndUpload) and doing the synchronization according to my specified scope. When I run this, I am getting the following output.

Synchronization Completed.

“SyncDBClient" after Synchronization

That's it. I am uploading sample databases and project files to my SkyDrive. You can download and play around.

Happy Coding.

Regards,
Jaliya

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

After the initial release of “Map App”, with some major enhancements version 1.0.0.1 was released today. In this version the map control is powered with the latest Bing Maps design. In addition you will get the following new features in addition to previous version’s features.

New Features

• Go to your favorite locations and save it in a SharePoint list.
• View your saved locations in the map.
• Manage locations in SharePoint List.

Map App

Search Locations

Add Locations

View Added Locationson Map

View Added Locations on List

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

Happy Coding.

Regards,
Jaliya

Map App Privacy Policy

Map App Privacy Policy

The "Map App" SharePoint App Software Privacy policy protects the information collected from you via "Map App". Your privacy is extremely important to me. As a result, I have implemented this Privacy Policy to ensure the information that I collect from you over the Web is secure.

Personal Data

Through the "Map App", I will not collect any personal data about you (e.g. your name, address, telephone number or e-mail address).

Thank You.

Jaliya Udagedara

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

I wanted to create an App for Office Store and Apps for sometime and thought to create an App for SharePoint which I named as “Map App”. “Map App” is mainly for searching and storing geographic locations while sitting inside SharePoint.

No offense Google, but Bing™ Maps is my favorite online mapping service and this app is developed using Bing Maps AJAX Control, Version 7.0. As this is the first version, this will only support searching for a particular location and getting the current location. Hoping to provide more features with the coming versions.

Map App v1.0.0.0 Features

Map App (New )

Map App Home

Get My Location

My Location

Search Location

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

Happy Coding.

Regards,

Jaliya

Forms Authentication with Multiple Login Pages

Have you ever come across a situation where you want to have multiple login pages with Forms Authentication.  Forms Authentication directly supports one login page, but there is a work around. Today we will see how to achieve this.

For an example let’s say you want two login pages where one is for administrators and other is for customers.

Default.aspx

When the user clicks on Admin, he should be redirected to administrators’  content where the user will have to authenticate himself as an administrator. When the user clicks on Customer, he will be redirected  to customers’ content again where the user will have to authenticate himself as a customer. Administrators login page will have some different content and customers login page will have some other.

Admin Login Page

Customer Login Page

I have a web application and I have two folders inside my project. One is for Administrators and the other is for Customers. I will create a login page and a sample page inside Admin folder and create another login page and a sample page inside Customer folder. Here is what I have after completing previous steps.

Folders inside Solution Explorer

For demonstration purposes in my admin login page I have a drop down where I can select which type of credentials I am going to try in admin login. If login with admin credentials I should be able to see admin content. If I login with customer credentials, I should prompted with admin login again.

Admin Login Page

In my admin login button click event I have following code.

protected void Button1_Click(object sender, EventArgs e)

{
    string userData = "";
    string userName = "";

    if (DropDownList1.SelectedIndex == 0) // admin
    {
        userData = "Admin"; // set userData
        userName = "Admin User Name"; 
    }
    else if (DropDownList1.SelectedIndex == 1) //customer
    {
        userData = "Customer"; // set userData
        userName = "Customer User Name";
    }

    // initialize FormsAuthentication
    FormsAuthentication.Initialize();

    // create a new ticket used for authentication
    FormsAuthenticationTicket authTicket = new FormsAuthenticationTicket(1, userName, DateTime.Now, DateTime.Now.AddMinutes(15), false, userData);

    // encrypt the cookie using the machine key for secure transport
    string encTicket = FormsAuthentication.Encrypt(authTicket);

    // create and add the cookies to the list for outgoing response
    HttpCookie faCookie = new HttpCookie(FormsAuthentication.FormsCookieName, encTicket);

    Response.Cookies.Add(faCookie);

    Response.Redirect("/Admin/WebForm1.aspx");
}

Next I am modifying the global.asax file. I am adding the “Application_AuthenticateRequest” event.

protected void Application_AuthenticateRequest(object sender, EventArgs e)

{
    if (HttpContext.Current.User != null)
    {
        if (HttpContext.Current.User.Identity.IsAuthenticated)
        {
            if (HttpContext.Current.User.Identity is FormsIdentity)
            {
                //HttpCookie cookie = HttpContext.Current.Request.Cookies["UserRole"];
                FormsIdentity id = (FormsIdentity)HttpContext.Current.User.Identity;
                FormsAuthenticationTicket ticket = id.Ticket;

                // get the stored user-data, in this case it's our users' role information
                string userData = ticket.UserData;
                string[] roles = userData.Split(',');
                HttpContext.Current.User = new GenericPrincipal(id, roles);
            }
        }
    }
}

Now I am modifying the “web.config” file as follows.

<authentication mode="Forms">

   <forms name="LoginCookie" loginUrl="Login.aspx" protection="None" path="/" defaultUrl="Login.aspx" timeout="30" />
</authentication>
<authorization>
   <deny users="?" />
</authorization>

Now I need to secure directories with role based Forms Authentication. For that again I am modifying the “web.config” file as follows.

<!--for admin folder allow users having the role 'Admin' and deny other-->

<location path="Admin" allowOverride="true">
    <system.web>
        <authorization>
            <allow roles="Admin" />
            <deny users="*"/>
        </authorization>
    </system.web>
</location>

<!--for content folder allow all-->
<location path="Content" allowOverride="true">
    <system.web>
        <authorization>
            <allow users="*" />
        </authorization>
    </system.web>
</location>

<!--for scripts folder allow all-->
<location path="Scripts" allowOverride="true">
    <system.web>
        <authorization>
            <allow users="*" />
        </authorization>
    </system.web>
</location>

<!--for images folder allow all-->
<location path="Images" allowOverride="true">
    <system.web>
        <authorization>
            <allow users="*" />
        </authorization>
    </system.web>
</location>

<!--for login.aspx allow all-->
<location path="Login.aspx" allowOverride="true">
    <system.web>
        <authorization>
            <allow users="*"/>
        </authorization>
    </system.web>
</location>

<!--for default.aspx allow all-->
<location path="Default.aspx" allowOverride="true">
    <system.web>
        <authorization>
            <allow users="*"/>
        </authorization>
    </system.web>
</location>

<!--for Admin/Login.aspx allow all-->
<location path="Admin/Login.aspx" allowOverride="true">
    <system.web>
        <authorization>
            <allow users="*"/>
        </authorization>
    </system.web>
</location>

<!--for Customer/Login.aspx allow all-->
<location path="Customer/Login.aspx" allowOverride="true">
    <system.web>
        <authorization>
            <allow users="*"/>
        </authorization>
    </system.web>
</location>

As you saw above loginUrl in the forms element can be only one. So I have created a page inside root called “Login” and I am not interested in the design of that page. Just in Page_Load event I am writing the following code. In here what I am doing is, when the main login page is loading it will identify the request type (admin or customer) by analysing the url and redirect to the relevant login page.

protected void Page_Load(object sender, EventArgs e)

{
    char[] character = { '/' };

    if (Request["ReturnUrl"] != null)
    {
        // Request["ReturnUrl"].ToString() -> 
        // http://localhost:1965/Admin/Login.aspx;
        // http://localhost:1965/Customer/Login.aspx

        string[] strs = Request["ReturnUrl"].Split(character);

        // if the second part is Admin go to admin login
        if (strs[1] == "Admin")
        {
            Response.Redirect(@"/Admin/Login.aspx");
        }
        // if the second part is Customer go to customer login
        else if (strs[1] == "Customer")
        {
            Response.Redirect(@"/Customer/Login.aspx");
        }
    }
}

Now I am all set. In my Default.aspx, I have two hyperlinks for admin content and customer content.

<asp:HyperLink ID="HyperLink1" runat="server" NavigateUrl="~/Admin/WebForm1.aspx">Admin</asp:HyperLink>

<br />
<asp:HyperLink ID="HyperLink2" runat="server" NavigateUrl="~/Customer/WebForm1.aspx">Customer</asp:HyperLink>

So in the admin login when I logged in with admin credentials I can see the admin content. If I logged in with customer credentials I will be redirected to admin login page again.

Admin Can View Admin Content

Customer Can't View Admin Content

I am not going to show you how to do this for the customer’s login page. The concept is the same. You can download the sample project from my skydrive and try this out.

Hope this helps.

Happy Coding.

Regards,
Jaliya