IPad SQLite Push and Pull Data from external MS SQL Server DB

This carries on from my previous post (http://stackoverflow.com/questions/4182664/ipad-app-pull-and-push-relational-data). My plan is that when the ipad application starts I am going to pull data (config data i.e. Departments, Types etc etc relational data that is used acr开发者_JS百科oss the system) from a webhosted MS SQL Server DB via a webservice and populate it into an SQL Lite DB on the IPad. Then when I load a listing I will pull the data over the line again via a webservice and populate it into the SQL Lite db on the ipad (than just run select commands to populate the listing).

My questions are: 1. What is the most efficient way to transfer data across the line via the web? Everyone seems to do it a different way. My idea is that I will have a webService for each type of data pull (e.g. RetrieveContactListing) that will query the db and than convert that data into "something" to send across the line. My question really is what is the "something" that it should be converting into? 2. Everyone talks about odata services. Is this suited for applications where complex read and writes are needed?

Ive created a simple iphone app before that talked to an sql server db (i just sent my own structured xml across the line) but now with this app the data calls are going to be a lot larger so efficiency is key.

I'll give my "standard" answer for this type of question (which always gets good ratings) - worked around your specific issue a bit:

Forget about the fact that you're "moving records from one database to another". You're really just reading data out of one database - sending it over the network - parsing it - and writing it to the iPhone Database.

To do this:

1. On your web server running your MS SQL server, write a web page (perl, ASP, whatever you want) which based upon some web query (or not), reads the applicable contents from the MS SQL database, and outputs it in XML format.

2. On your iPad, use the NSXMLParser class to request and parse the XML data. The nice thing about NSXMLParser, is it does all the work of actually opening the HTTP connection, fetching the data across the network, and parsing the XML all in one call, the:

   • (id)initWithContentsOfURL:(NSURL *)url

3. Use the NSXMLParser calls to get the data you need from the parsed XML Document, and write it to your local iPhone database with the standard Core Data services.

I would suggest that your server side code returns JSON not XML as this is simpler to work with on the iOS side. (and relatively simple to set up on the server side)

Also from the app, send a header field that contains the date the app last queried for data and get your server side code to only return data that has changed since that date. Use a date column on SQL Server to keep a record of rows that have been updated.

NSMutableURLRequest *request = [NSMutableURLRequest requestWithURL:[NSURL URLWithString:requestURL]];
[request setValue:lastCallTime forHTTPHeaderField:@"If-Modified-Since"];

The lastCallTime is an entry from an NSDictionary that holds the Date from the response header...

[response allHeaderFields][@"Date"]

Here's an article on how to get the If-Modified-Since value on the server, http://madskristensen.net/post/use-if-modified-since-header-in-aspnet

Which you can then use in your queries

One final thing is to also ensure that the server is returning the data compressed as this will help with performance and iOS uncompresses it by default when it arrives