Xamarin Offline Data Sync

Seth Isaacks 11

Hello

I have an Xamarin cross platform application. I have a local sqlite database that I want to sync with an Azure SQL Database
I have been doing a lot of googling and watching videos.
I was watching some of the Xamarin University videos about Xamarin Azure Mobile Apps but all that seems to be outdated.
I am absolutely confused with the abundance of outdated information.

I found this link below for syncing data, however, I can't find any examples other than what is in this tutorial and it doesn't explain much.
https://learn.microsoft.com/en-us/azure/developer/mobile-apps/azure-mobile-apps/quickstarts/xamarin-android/offline

I am not sure if it would be wiser to write my own api for CRUD and sync the data myself between the sever and sqlite or use Azure Mobile Applications. However I am not entirely sure how to use azure mobile applications due to all the old/new information out there. I am not sure if I can add my existing app to Azure mobile apps.

Any help would be much appreciated

3 answers

Alberto Morillo 32,986 Reputation points MVP

2022-05-17T20:18:10.153+00:00

A year ago I answered a similar question here. Let me share the answer with you now and hopefully you can find it helpful too.

You can use Microsoft Sync Framework to Sync SQLite and SQL Azure/SQL Server as explained here. Syn Framework provides cross-platform data synchronization.

This synchronization framework published on GitHub may be a possible solution for you.

Alternatively, you can create your own Web Api in front of the SQL server/SQLAzure to handle SQL DML statements. Please refer to this forum thread for detailed information.
Please sign in to rate this answer.
Seth Isaacks 11 Reputation points

2022-05-17T20:47:34.893+00:00

@Alberto Morillo I was told that the first one you recommended is no longer supported? Would that be wise to use?

Alberto Morillo 32,986 Reputation points MVP

2022-05-17T21:02:53.73+00:00

There is a free tool named SQL Data Sync that can be used for syncing SQL Data Sync (hub database) with member databases (SQL Server or Azure SQL databases), and that tool currently available here is based on sync framework. I don't have any information at this time that SQL Data Sync will be deprecated.

Seth Isaacks 11 Reputation points

2022-05-17T21:16:52.117+00:00

Im going from Sqlite to SqlServer. So the first one you mentioned is what I need. I however am not a DBA, I know very little about the concept of syncing databases and what all needs to be done. The example provided is kind of sparse and leaves a lot of open ended questions. Like how do you deal with deletes that are on the local db and delete them off of the server. For example say the user is offline and deletes something....the next time they come online, they push/pull from the server, wouldn't they get back the deleted record from the server?

Seth Isaacks 11 Reputation points

2022-05-17T22:06:11.66+00:00

@Alberto Morillo in the first example: https://www.devart.com/dotconnect/sqlite/docs/SyncFramework.html#:~:text=Note%20that%20Sync%20Framework%20is%20supported%20only%20in,following%20main%20classes%20are%20used%20for%20data%20synchronization%3A

I dont see a synce between sqlite and sql...just two sqlite databases. Is there not a way to sync between sqlite and sql?
so frustrating to understand...probably the abundance of old and new info

Alberto Morillo 32,986 Reputation points MVP

2022-05-18T01:28:59.807+00:00

Does a commercial software that hides all those unknown things work for you? Would you try a software like this one?

Seth Isaacks 11 Reputation points

2022-05-18T13:40:21.89+00:00

@Alberto Morillo
Do you know or have any data on this: https://learn.microsoft.com/en-us/azure/developer/mobile-apps/azure-mobile-apps/quickstarts/xamarin-android/offline
The examples are not the best.

Oury Ba-MSFT 16,731 Reputation points Microsoft Employee

2022-05-19T23:15:47.143+00:00

@Seth Isaacks If you think the document should be improved. You can create a git hub issue by clicking on the submit feedback at the bottom of the page

Regards,
Oury

Seth Isaacks 11 Reputation points

2022-05-19T23:18:35.247+00:00

@Oury Ba-MSFT Well turns out the example is for .net 4.2 not .net 6.0 which would explain my issue. I didnt see that on the documentation anywhere. I ended up finding that out on another site.
Sign in to comment
Adrian Hall (MSFT) 156 Reputation points Microsoft Employee

2022-05-20T17:26:48.39+00:00

Hi,

I'm the maintainer of Azure Mobile Apps, so can give the latest information.

The tutorial for each platform sits alongside a HOW TO, which is in-depth documentation on both the client and the server. In addition, we operate in the open at the GitHub repository https://github.com/azure/azure-mobile-apps, including a vibrant discussion board on how to build apps. The server is now ASP.NET 6 (which is the latest stuff) and it's really easy to build an backend with all the bells and whistles (like per-user authorization, pre-calculated fields, real-time notifications) to support mobile sync. The client is .NET Standard 2.0 and supports the latest Xamarin.iOS, Xamarin.Android, and Xamarin.Forms. The only reason I haven't included MAUI yet is because I'm only just trying it today.

Unfortunately, you have reached the tutorials during a "publish block period" - check after the BUILD conference is over - we should have published all the new tutorials by then. In the interim, I've published some in-depth information on the wiki in the github site, and I'm happy to answer questions for you in the discussion boards.
Please sign in to rate this answer.
Oury Ba-MSFT 16,731 Reputation points Microsoft Employee

2022-05-20T17:37:09.337+00:00

@Seth Isaacks Please see the answer @Adrian Hall (MSFT) has provided above and let us know if you have further questions.

Regards,
Oury

Seth Isaacks 11 Reputation points

2022-05-20T19:49:23.467+00:00

@Adrian Hall (MSFT)

I think you and I spoke last year and you told me some new technology was coming. Our app is Xamarin like I mentioned above, our app will primarily be used offline. I have an onboard sqlite database. However when they come online I want to sync data bi directionally with the Azure Sql Sever I have set up. That was easy enough to set up.

What I am struggling with and have not found a good example is how to due the off line sync. What needs to be on the client, what needs to be on the sever. A solid example of how that all ties together. How does the client know when to use the offline vs online (granted I know you can check using Xamarin essentials for online connectivity) but I am not exactly sure how to set up the client and server.

I have found this link: https://learn.microsoft.com/en-us/azure/developer/mobile-apps/azure-mobile-apps/quickstarts/xamarin-forms/offline but this doesn't really tie together how to use offline and online. If it does I am missing it. Do you have a completed example in Github?

I also found this for the server: https://learn.microsoft.com/en-us/azure/developer/mobile-apps/azure-mobile-apps/howto/server/dotnet-core but I am not sure how to create table controller with Entity Framework Code first....I know how to create an API controller.....but obviously there is some difference.

Any help would be much appreciated.

Adrian Hall (MSFT) 156 Reputation points Microsoft Employee

2022-05-20T21:27:12.407+00:00

That HOW TO is very explicit about how to set up the controller. We don't tell you how to set up Entity Framework Core, but that's deliberate - it's kind of a fundamental thing (and many other people cover it). We do tell you how to set up the entity, and how to build the controller.

Please wait until AFTER BUILD (which is next week) and the end-to-end tutorials will be uploaded. The samples are already available in the GitHub repository, and I'll be doing a bunch more demo applications once I've completed the tutorials.

Seth Isaacks 11 Reputation points

2022-05-21T00:56:33.637+00:00

@Adrian Hall (MSFT) I would not say its very specific about how to set up the controller, there is one line of code. I am not a web guy, trying to learn...and where there are API controllers, Table Controllers, MVC Controllers, Empty Controllers. Each one seems to act or be set up a little different. So when learning, or coming into Web API's new, its hard to know what to use and why. I am looking forward to the information coming, that gives me some hope this will work.

I do have a couple questions though, does it matter that my Sqlite will be the primary database? Meaning my tables will be there first and any new columns will be there first.
Is there any magic I will have to do sever side if new columns are added regarding the synchronization process?

thank you for your responses.

Adrian Hall (MSFT) 156 Reputation points Microsoft Employee

2022-05-23T15:14:33.643+00:00

The basic controller is indeed one line of code - set up the repository and you are done. You can do quite a bit more, but it's optional and depending on need.

Sqlite is not supported as a primary database. The problem there is that the Date object is not stored with ms precision, which is a requirement for incremental sync and conflict resolution when using data synchronization. You need to use a better database for the service side. MySQL, PostgreSQL, Azure SQL, CosmosDB, and others all work fine. Anything with a fully fledged Entity Framework Core driver will work just fine.

To add new columns, you will have to add the new columns, then (importantly) give each record a new value. Normally, the EF Core driver will set things up so that the UpdatedAt and Version fields get updated. If they don't for some reason (I've seen it on some databases), then you will have to update those. On the client side, you will need to update your model. If the change is purely additive, then you don't need to do anything else - the table will automatically be updated and every record in that table will be downloaded because you updated the UpdatedAt field. If you change the definition of a field or remove a field, then you will have to wipe the table and re-download everything.

Seth Isaacks 11 Reputation points

2022-05-23T15:32:54.753+00:00

@Adrian Hall (MSFT) When I stated the Sqlite is the primary database.....that is the MOBILE onboard Xamarin Database. The database in the cloud is Sql Azure. Essentially when the user comes back online we want to be able to give them the ability to sync to the server (Azure SQL). We want bidirectional because they may have more than one device.....meaning the server will have data from both devices and you may want to have the information on both devices. Again, out clients, when using the app, will be offline more often than not. Is that going to be an issue for syncing being I am using an onboard Sqlite Database? Again this is not for sharing data for multiple people, its only per user and each user could have more than one device.

Seth Isaacks 11 Reputation points

2022-05-23T18:15:57.34+00:00

@Adrian Hall (MSFT) Adrian one more question, you mentioned above when adding a new column to the sever database I have to populate that column with data? I know entityframe adds the column but how do you add data too at the same time?

Adrian Hall (MSFT) 156 Reputation points Microsoft Employee

2022-05-23T19:34:44.323+00:00

You populate through SQL statements when doing the migration.

Seth Isaacks 11 Reputation points

2022-05-26T03:27:07.387+00:00

@Adrian Hallett took some trial and error...well lots of it...but its working for a basic one table example....not to introduce some more complex scenarios.
Thank you for your assistance.
I do have some ideas that may make the tutorial more helpful to newbies

Seth Isaacks 11 Reputation points

2022-05-26T15:43:00.567+00:00

@Adrian Hall (MSFT) I got everything working...successful sink
I went through steps to add a column on the server DB
Worked fine
However now I cannot sync from the client anymore I keep getting an error that is not very helpful

{"type":"https://tools.ietf.org/html/rfc7231#section-6.6.1","title":"An error occurred while processing your request.","status":500,"traceId":"00-2324ac47ca84d68f2125236838aea85d-49f03b0d5eb0a4a0-00"}

For some reason when I try to await vehicleTable.PushItemsAsync(); I get the error above. Not sure what is gong on or why.

Adrian Hall (MSFT) 156 Reputation points Microsoft Employee

2022-05-26T16:06:40.1+00:00

Great to see you making progress.

A 500 is explicitly sent by the server and says "something went wrong". You need to go through the normal ASP.NET Core debugging. Basically, the ASP.NET Core service is throwing an exception somewhere - that is trapped and produces the 500 error.

On another note, this is not where "the people who know azure mobile apps hang out" - there is a larger community (than just one person) over on the azure/azure-mobile-apps discussion area. I would recommend asking questions on this product over there.

Seth Isaacks 11 Reputation points

2022-05-26T16:22:24.963+00:00

@Adrian Hall (MSFT)
Okay I will ask over there.

I want to explain what I did and maybe you will spot what I did wrong.

I ran the migration on the sever, worked perfectly

I added the property to my model in the client

I think tried to push and pull and thats when I got the error.

I then dropped the server database and remigrated...and still getting the same error.

everything worked fine until I tried to add a column

Oury Ba-MSFT 16,731 Reputation points Microsoft Employee

2022-05-31T22:30:14.81+00:00

@Seth Isaacks Please do not forget to mark as accept answer wherever information provided was helpful.

Regards,
Oury
Sign in to comment
Basma Yousef 1 Reputation point

2022-05-31T23:08:59.88+00:00

There is a free tool named SQL Data Sync that can be used for syncing SQL Data Sync (hub database) with member databases (SQL Server or Azure SQL
Please sign in to rate this answer.
Seth Isaacks 11 Reputation points

2022-05-31T23:34:14.787+00:00

I am using Xamarin application (MOBILE)...I need a way to bi directionaly sync the mobile database and the azure database. Sync works....Howver when the schema is updated, I get an error and it stops working I have yet to figure out how to fix this.
Sign in to comment

Share via

Xamarin Offline Data Sync

3 answers