Data sync from SQL DB to the application local storage

jeevithajeevitha USMember

Hi All,
I am very new to Xamarin and We are developing a cross platform app (iOS, Android) for contact management (Listing, add, edit) and WCF service for connecting the applications with the SQL DB. We are using SQLite DB for local storage. Our requirement is to enable Datasync functionality from the server to the Local storage of the users mobile in every 24 hours. everyday night at 12 AM, data sync operation should be triggered and pass the data from the service [from my SQL DB] and store it in the SQLite DB of all the users devices. So when the user uses his app in the mobile, it will fetch the data from SQLite DB.

Please advise what is the best way of achieve this functionality. If possible please share the sample code

Thanks
Jeevitha

Posts

  • JonDouglasJonDouglas USXamarin Team, University, Developer Group Leader Xamurai

    One way off the top of my head would be to create a "SyncEntity" that your models will inherit from that will take properties such as "LastSynced", "IsSynced", etc. You can then use your properties to manage whether or not you have the most recent record when going back up to your WCF Service.

    You would then take advantage of each platform's startup/backgrounded services to run your "Scheduled Sync". You may need to implement your own "Scheduled Sync" on each platform via the abstraction pattern in a PCL or via file linking. Though this is quite an advanced topic, you may find some resources from how @JamesMontemagno‌ worked his into Azure Mobile Services:

    https://github.com/jamesmontemagno/MyExpenses

  • jeevithajeevitha USMember

    Hi Jon
    Thanks for your reply. we are designing our Contact Entity with the properties LastUpdated,DBRecordid and have the same fields in SQL DB also. So if the LastUpdated date and RecordId matches in the Localstorage, it will not update the record.

    Currently we dont have any idea on how we are going to host our service. We are planning to host in our Dev server and disable firewall and will be always available in internet. Not sure whether we need to host in azure.

    Another Big problem is how to enable this data sync feature in the application to connect to the service in a specific time and do a silent sync to the local storage. And also this is not a one way update. User can add any data from his mobile app. it will be saved in the local storage. When the data sync is started at 12 AM. it should merge the data back to the DB also

    I am very new to this mobile app development and your help will be really appreciated

    Thanks
    Jeevitha

  • JonDouglasJonDouglas USXamarin Team, University, Developer Group Leader Xamurai

    Depending on how much "Control" you want to have within your sync, it might be best to use an existing framework that already handles this for you. Some great suggestions would be:

    Microsoft Sync Framework - http://msdn.microsoft.com/en-us/sync/bb736753.aspx

    Azure Mobile Services (Offline Sync) - Great video of what is coming soon/in preview: http://channel9.msdn.com/Events/Build/2014/3-623

    iFacr - http://ifactr.com/platform/data-sync-engine

    Bidirectional syncs can be quite tough to make and manage, and in my opinion it's better to let some of these frameworks handle the heavy lifting for you. Azure Mobile Services have some great things on the horizon that are really fun to play with. You can get a small introduction from a blog post I wrote here: http://www.jon-douglas.com/2014/04/to-the-cloud/

    I personally prefer having the control on my backend(Web API), and thus I create a ASP.NET Web API for my Azure Mobile Service, and let the data structures that Azure Mobile Services provide to obtain my bidirectional sync. In that video I linked above, he somewhat goes over how that is accomplished and how it works when a device is offline.

  • jeevithajeevitha USMember

    Thanks a lot Jon,
    I will go thorough the links and let you know if i have any question.
    We have not used Azure mobile services yet. Do we need to host our WCF service in azure in order to get it work ?
    These are our current infrastructure. Do we need to change anything in architectural level to achieve this functionality
    1. Cross platform Xamarin applicartion [iOS, Android, Core library for SQLite DB]
    2. WCF service hosted in our dev server for now
    3. SQL server 2012 DB for the back end

    4. Created WCF proxy and consumed in our cross platform app [referred this link to enable endpoint connectivity]

  • JonDouglasJonDouglas USXamarin Team, University, Developer Group Leader Xamurai

    You will most likely have to put your WCF Service as a "Worker Role" in Azure to get what you're after. I know there are plenty of resources out there, but I personally like anything that Yavor has done: http://hashtagfail.com/post/81743306236/build2014-mobile-services

    You shouldn't have to change much other than where your service is located, and using the Worker Role to integrate it into Azure Mobile Services to obtain the results you're after.

    I believe there's another work around which is to have a new ASP.NET Web API act as a wrapper for your WCF Service and use that as your Azure Mobile Service backend.

    I hope this helps! Maybe somebody with more WCF + AMS experience can help out in this thread.

  • PeterDavisPeterDavis USMember ✭✭✭

    You don't necessarily need Azure. Your phone needs to be able to communicate with your WCF server. So if it's on a phone network (as opposed to local wifi) or an external wifi, then you'll need a public facing web server to host the WCF service.

    If you're doing stuff internally (say on a wifi network) then as long as the phone and web server can see each other on the network, that should be fine.

    If you don't have a public facing web server or don't want one, you could host on Azure instead.

  • jeevithajeevitha USMember

    Thank you both for your inputs. Can i use iFactor framework with my existing xamarin code ? [if i dont want to go with azure service]? . Is there any other framework or libraries that handles Data sync in my existing Xamarin code? Please advise with some approaches with sample code.

  • JonDouglasJonDouglas USXamarin Team, University, Developer Group Leader Xamurai

    @jeevitha‌ I have never used IFactr, and I would suggest you ask their sales team that question. As for data syncing, those are the ones I'm mainly aware of.

  • jeevithajeevitha USMember

    Ok Jon. I will do that. I am seeing some client SDK which provide inbuilt offline support ansd data sync functionality
    http://components.xamarin.com/gettingstarted/zumero Do you have any idea abou ti

  • Hi I have a similar solution to be implemented. What path did we go to solve the problem, and what the tools we used. Our environment summary is:
    1. Host Restful API on public domain (in house data center), and access the same in Mobile Devices,
    2. The project targets all 3 platforms using Forms.
    3. App uses SqlLite
    4. Central Server uses Sql Server 2015.
    5. App is mainly Offline. And when we have an active and strong Signal, data has to be synced with central Db in the background.

  • Jerome0626Jerome0626 BEMember

    Hello,

    I'm looking for a similar solution. I don't want to work with Azure service as we already have all infrastructure.

    Can you send me any feedback on solution you were looking on @jeevitha or @SridharGarapati ?

    Thanks

  • fasi1208fasi1208 PKMember

    I can't find a single tutorial on how to connect the sql server with xamarin android, all i can find are the parts of my code. I am stuck from 2 days in it, also i am new to mobile development and i am familiar with c# .net. Can any one please help me out find a sample tutorial/sample code on how to connect sql server with xamarin android?

  • @Jerome0626 and @jeevitha , I am Stilling Looking at a solution for this. And this has become a big Hurdle for my Product. I don't want to use any cloud service or Third party component at this point of time as data sensitivity and servicing people using the app are mostly in tunnels and basements. where signals are far weak than expected. I am looking at App Extensions where then can talk to Host App when apps are in the same group. No idea if this correct approach or not. Advice me if you found a solution. For now we are using a Custom sync code as a timed thread which runs in the background of the app.

  • JohnHadzimaJohnHadzima USMember ✭✭

    I am also looking for a solution very similar to what @SridharGarapati has described. I already have a SQL Server database with the Restful API in place. I'd like to add mobile clients to this architecture with the ability to sync to the SQL Server database when they are online. I don't really want to re-architecture my current solution since I have a MVC web application already using it.

  • LuisMatosLuisMatos USMember ✭✭

    @fasi1208 @JohnHadzima @SridharGarapati @Jerome0626

    You can start by consuming the resources of a databese with WebService .ASMX in ASP.NET by IIS and add to your project in xamarin android a web references.

  • We Started doing Push pull Methodology to post and Get data Restful service, and have a inhouse transaction logic that does do all checks required in the app and on the service, before it replicates with SqlServer.

  • JorgeDiegoJorgeDiego USMember ✭✭

    I have the same problem.
    I have a xamarin forms (iOS and Android) app with a sqlite local database, an SQL AZURE data base and REST services.
    The user works offline and the syncronization is made on demand (when the user press a button), but I want the syncronization to be a background process and the user feel like he is working against the server all the time

  • I have a similar problem. I come from a HEAVY C# WinForms and SQL server background.
    All applications that we do are huge apps wit BIG SQL database in the backend.

    I am working on first Xamarin app and I also use Azure.
    I am torn between using ADO.net or ORM...
    I guess that using ADO.net would make me feel right at home, but the ORM path looks exciting!

    My decision would also depend on offline - online syncing of Data.
    Would you recommend having a SQL server DB on Azure with a sqlite db on each device?
    Or should one have a SQLlite database on the device and on Azure?

  • lnnaielnnaie USMember ✭✭

    This is an emerging pattern in mobile. MS built it for their backend Azure. Maybe it's time for an OS project that takes github azure-mobile-apps-net-client and creates the necessary abstractions to target other storage backends: sql, mongo, etc.

Sign In or Register to comment.