14 January 2011

Accessing SQL Server data from iOS apps

Almost all mobile apps need access to external data to be valuable. With a huge amount of existing business data residing in Microsoft SQL Server databases, and an ever-increasing drive to make more and more available to mobile users, how do you marry the rather separate worlds of Microsoft’s SQL Server and Apple’s iOS devices?

The classic answer: write a web service layer

Look at any of the questions on this topic asked in Internet discussion forums, and you’ll inevitably see the answer, “just write a web service and use that!”. But what does this process gain?

For a well-designed database with a solid security model, and business logic in the database, writing a custom web service on top of this just to access some of the data from a different platform seems inefficient and unnecessary. Desktop applications interact with the SQL Server directly – why should mobile apps be any different?

The better answer: the iSql SDK

iSqlArchitecture-SolidBG_thumb.pngWorking along the lines of “if you do something more than once, make it shared,” we set about coming up with a better solution for the general case. And so the iSql SDK was born: sitting between SQL Server and your iOS apps, it provides the simple API you’re used to if you’ve been developing desktop apps using the Microsoft SQL Native Client.

It turns out a web service remained a sensible idea: HTTP is much more suited to the Big Bad Internet than SQL Server’s native TDS protocol, removing the need for complex configuration, firewall configuration, and the like.

However, rather than writing a web service for every app that needs data access, we made the web service generic, serving only as a proxy between the SQL Server and a client library integrated into the iPhone or iPad app. This client library handles all the network communication, and provides a clean API.

OSQL in 25 lines of code

As an example of how to use the API, I put together a very simple app that allowed the user to enter one or more SQL statements, and displayed the results in a rather primitively formatted text field. The total amount of Objective-C code responsible for doing the work? About 25 lines.

You can see this in action in the demo video.

Beta out now – your chance to give us your suggestions!

We’ve released the iSql SDK as a beta on the MobileFoo website: you’re welcome to download a copy, have a play in your own apps, and let us know what we’ve missed using the Feedback button on the site.

Software development should be fun and rewarding: no-one wants to spend their time writing boiler-plate code over and over again, so stop writing the same web service code, and start doing exciting things in the new world of mobile data!

For more articles like this, sign up to the fortnightly Simple-Talk newsletter.


  • Rate
    [Total: 3    Average: 5/5]

Robert is a software engineer at Red Gate, where he's worked on a wide range of products, including SQL Doc, SQL Data Compare, SQL Log Rescue, SQL Multi Script, and ANTS Profiler. He is currently working on internal systems, and is one of six build-masters in the organisation, responsible for continuous integration and build systems across the business. Outside of work, he enjoys amateur radio, electronics, and of course the usual assortment of computer-related technologies, from hardware all the way through to high-level software.

View all articles by Robert Chipperfield