[Solved] My questions about Data Storage.

[Rajesh]

Hi,

I have a lot of questions regarding the Data Storage Component that ships with XAML Suite for Windows 8. After reading the documentation I experimented with retrieval of data from a local storage database.

To start with.

I referenced the Telerik Data Storage for Windows 8.1 and Telerik UI for Windows 8.1
I copied an existing SQLite database to the application and copied it to the application run time folder.
And I used the following code to retrieve data from the database

var dbpath = new Telerik.Storage.Extensions.Context("demo.db", DatabaseLocation.Local);
var stringSQL = string.Format(@"select userid, firstname, lastname from users"); 
var p = dbpath.Get<Users>(stringSQL);
var k = p.Count;

this.SummaryGrid.ItemsSource = new List<Users>();

where Users is the class defined for the objects in the users table and SummaryGrid is a Telerik RADGrid control.

I am getting this error "Class 'Users' used as persistent class has more than one primary key columns. Each table may have at most one Primary Key." and then it is impossible to populate the grid. But I am getting the number of rows from the table.

My questions for Data Storage are

1. If I use a SQLite database and then use the Telerik Data Storage for Windows 8.1 are they compatible. Will it cause any problems.
2. When creating classes from tables in SQLite we use this [SQLite.PrimaryKey] for the column in the classes and it works when updating tables.
 What is the equivalent when using Telerik Data Storage. 
3. The primary reason I am trying out Telerik Data Storage is we can pass SQL Statements for updating whereas in SQLite we can pass only the object name.

And for your information, I was able to populate the Telerik RAD Grid from a SQLite database when using the sqlite-net libraries and the SQLite library for Windows 8.1.

Thanks,

Rajesh.

[Bulent]

Hello Rajesh,

Thank you for your interest regarding Telerik Data Storage for XAML.
As I understood, you use an existing SQLite database and try to access it via component's API.
This is so called database first approach which is not supported by Telerik Data Storage. This  corresponds to your first question. 
To specify a property as a primary key you should use [System.ComponentModel.DataAnnotations.Key] annotation on only one property, since complex primary keys are not supported by SQLite databases.
For more detailed information please refer to the component's documentation here.

Let me know if you need further assistance.

Regards,
Bulent
Telerik

 

 

[Rajesh]

Hello Bulent,

Thanks a lot for the reply and I would like to know if there is any technique to create a blank database on the client side and then download data from a REST Service. How would this data base compare the structure with the tables to classes written in code.

Also is there any mechanism to implement push and pull for a Data Storage database that resides in a Windows 8.1 Metro XAML application.

Thanks,

Rajesh.

[Bulent]

Hello Rajesh,

You shouldn't worry about creation of database file since it is created internally by Data Storage engine on demand. You can just send your request to RESTful service, receive data in supported format, preferably in JSON, and then materialize it to your entity class instances. Having materialized data on hand you can use Data Storage context for CRUD operations.

It is not clear enough for me what kind of push/pull mechanism do you want to implement. Would you please clarify it a little bit.

Regards,
Bulent
Telerik

 

 

[Rajesh]

Hi,

My scenario of push and pull involves the following steps. 

Pull
1. Create a blank empty database on the Windows 8.1 XAML application through code-behind. This is done when the application initializes for the first time.
2. The path of the REST URL will be saved in preferences of the application so that the app will know where to download the data from.
3. Once a connection is made to the REST URL, the schema of the table is downloaded and the table created on the client side in the blank database that was created in Step 1.
4. Once the table is created in the database, data can be downloaded and populated in the table.
5. Similarly for all the tables in the server for the first time so that the application can use the database and work offline.

PUSH
1. The data that was modified on the Windows 8.1 XAML application is saved in the Data Storage database on the device.
2. We specify what tables need to be pushed in preferences.
3. At the time of pushing, the database pushes the relevant transaction tables (only with modified data) and updates the equivalent tables on the remote server.

This is similar to what RDA did between SQLCE on a mobile device and SQL Server on a remote server.

Example of Pull with RDA (http://technet.microsoft.com/en-us/library/aa257443(v=sql.80).aspx)
Example of Push with RDA (http://technet.microsoft.com/en-us/library/aa257446(v=sql.80).aspx)

I would like to know how to achieve this with Data Storage and SQL Server through code behind. Would be grateful if you can provide some hints. Also there should be a mechanism in case of network issues so that the data is rendered useless in case of a failed sync. It should be able to resume the sync once the network connection resumes.

Thanks,

Rajesh.

 

[Bulent]

Hi Rajesh,

Synchronization of local data with cloud storage is not an easy task and there are lots of thinks to be considered. The workflows you described look right, but I will share with you some hints about how to use DataStorage in appropriate way.
At step 3 of Pull workflow you wrote that the schema will be downloaded from the server. This step is not applicable for DataStorage, since it provides ORM functionality and requires preliminary defined entity class types. Of course you can implement your workflow, but then you should handle all of the sql query composition and execution tasks, something that will require usage of raw SQLIte  APIs and programming model  and will increase significantly the development time of your application. I'll share with you what I would do in order to facilitate this process using features of DataStorage.
At the very beginning I would prepare the data model that will be kept in sync. Having the entity classes I will not bother about table creation during first pull execution, DataStorage engine will do this for me out of the box. I would materialize JSON/XML data from responses as entity class instances and will apply CRUD operations to local database using DataStorage ORM functionality.
I'll share with you some hints about the synchronization algorithm too. You should track some kind of history for database changes. You can add an extra column to local tables with modification state or you can maintain an extra system table with history log. This is an easy task if you use DataStorage ORM functionality. Then getting only modified data from local database as real entity class instances you will be able to prepare the body & headers of RESTful  push requests to the server. You should keep in mind that you will have same records modified locally and remotely simultaneously and this will require merge and conflict resolution handling in your code.
Another thing to consider is synchronization of racing changes to the same table on the cloud. Consider timestamp usage for changes too. As you see implementation of synchronization between local and remote data is a complex task and is difficult to be implemented for generic cases.

I hope this helps. 

 

Regards,
Bulent
Telerik