How to access SQL database from an iPhone app Via RESTful WCF service hosted on IIS 7.5

• Download JsonWcfService.zip

Introduction 

If you are looking for a way to access a Microsoft SQL database from mobile phones like Android or iPhone, and would like an example on how to do it and test it, then may be this will help you. 

Background        

One way to get at SQL data from a mobile app is via a RESTful WCF service. The WCF service will expose the database and return JSON data which can get consumed by an iPhone app using http requests.

This article assumes that you have a basic understanding of WCF, SQL database, and iPhone programming. In this tutorial, I will build a WCF service that exposes a simple SQL database "EmpDB", host the service on IIS 7.5, and return the Employee info in JSON format, which will get consumed in an iPhone Application. This example is built with the following tools: VS2010, SQLServer 2008, .Net Framework 4.0, and iOS 5.   

Why JSON not XML?    

1. Better representation of language-independent Data Structures
2. Simple, well defined, easy to parse, and easy to read.  
3. lightweight payload = reduced bandwidth = faster transfers 
4. iOS has nice native JSON SDKS, which makes it easy to parse and super fast.  

Using the code      

If you already have a SQL database on your machine that you would like to expose, then skip to step #1. Otherwise, create a simple SQL database, and for consistency purposes, you can use a SQL database like mine. Call it "EmpDB", create one table "EmpInfo" with three fields:     

• firstname (nchar(10) type)       
• lastname  (nchar(10)  type)    
• salary  (decimal type)          

As you can see, I have 5 records in my database that I would like to get into my iPhone app. 

Here are five simple steps required to accomplish the goal: 

1. Create a new WCF service  
2. Add Interface Code 
3. Implement Service    
4. TEST the service remotely  
5. Consume JSON from mobile apps like iOS?   

1.Create a new WCF service 

File>New project>C#>WCF Service>"JsonWcfService" 

Delete the automatically generated IService1.cs and Service1.svc files. We will create our own. 

Add WCF service: FILE>new item>C#>WCF service>"GetEmployees.svc".  

This will create two files, the interface "IGetEmployee.svc" and the service implementation file "GetEmployee.svc". 

2. Add Interface Code    

Add the following code to the interface "IGetEmployee file". 

using System.ServiceModel.Web;
 
namespace JsonWcfService
{
    [ServiceContract]
    public interface IGetEmployees
    {
        [OperationContract]
        //attribute for returning JSON format
        [WebInvoke(Method = "GET",
            ResponseFormat = WebMessageFormat.Json,
            BodyStyle = WebMessageBodyStyle.Wrapped,
            UriTemplate = "json/employees")]
        //method
        List<Employee> GetAllEmployeesMethod();
    }
} 

Add a reference to System.ServiceModel.Web. If this framework wasn't available, right click on project > properties > Application Tab> Target> and make sure .Net Framework 4.0 is selected.   

The WebInvoke attribute before the method ensures that data is returned in JSON fornmat. This could have been WebMessageFormat.Xml, if you are interested in returning XML instead.   

The interface has one method "GetAllEmployeesMethod", which returns a list of employees from the database. This method will need to be implemented in our service code in the next step.  

3. Implement Service 
Add the following code to Implementation file "GetEmployees.svc". This service file implements the interface method "GetAllEmployeesMethod". In this method, I simply open a basic connection to a local SQL database "EmpDB" with all defaults to keep it simple. I query the "EmpInfo" table by asking to get a list of all the employees with their firstname, lastname and salary. Make sure to add a reference to System.Data.SqlClient fto expose SQL operations, and System.Runtime.Serialization to expose [DataContract]  attribute like shown in the images below.  

using System.Data.SqlClient;
using System.Runtime.Serialization;
namespace JsonWcfService
{
    public class GetEmployees : IGetEmployees
    {
       public List<Employee> GetAllEmployeesMethod()
        {
             List<Employee> mylist = new List<Employee>();
 
            using (SqlConnection conn = new SqlConnection("server=(local);database=EmpDB;Integrated Security=SSPI;"))
            {
                conn.Open();
 
                string cmdStr = String.Format("Select firstname,lastname,salary from EmpInfo");
                SqlCommand cmd = new SqlCommand(cmdStr, conn);
                SqlDataReader rd = cmd.ExecuteReader();
               
                if (rd.HasRows)
                {
                    while (rd.Read())
                        mylist.Add(new Employee(rd.GetString(0), rd.GetString(1), rd.GetDecimal(2)));
                }
                conn.Close();
            }
 
            return mylist;
        }
    }
 
    [DataContract]
    public class Employee
    {
        [DataMember]
        public string firstname { get; set; }
        [DataMember]
        public string lastname { get; set; }
        [DataMember]
        public decimal salary { get; set; }
        public Employee(string first, string last, decimal sal)
        {
            firstname=first;
            lastname=last;
            salary=sal;
        }
    }
}   

Now, tweak the web.config file within the project's solution explorer like this. Note I used binding="webHttpBinding" and <webHttp/>. Project won't work correctly without these tweaks!     

<services>
      <service name="JsonWcfService.GetEmployees" behaviorConfiguration="EmpServiceBehaviour">
        <endpoint address ="" binding="webHttpBinding" contract="JsonWcfService.IGetEmployees" behaviorConfiguration="web">
        </endpoint>
      </service>
    </services>
 
    <behaviors>
      <serviceBehaviors>
        <behavior name="EmpServiceBehaviour">
          <serviceMetadata httpGetEnabled="true"/>
         <serviceDebug includeExceptionDetailInFaults="false"/>
        </behavior>
      </serviceBehaviors>
      <endpointBehaviors>
        <behavior name="web">
          <webHttp/>
        </behavior>
      </endpointBehaviors>
    </behaviors>

This is it for WCF service. Compile in Release and Debug modes. Run by ctrl+F5, this will open a browser that displays GetEmployeeService, like http://localhost:7794/GetEmployees.svc. Well if you want to see the Employees list, you can type this into the browser: http://localhost:7794/GetEmployees.svc/json/employees 

The WCF service returns our employees list from the database in JSON format and is hosted in IIS. But, how can I get the employees list into my iPhone or Android Application and be able to test it? 

What we did so far can only be tested on the same development machine, but can't get to it from a different machine or from an iphone app which is our goal. To do that, we need to host the service using some website. To do this, you need to pick a folder somewhere that will contain the necessary files to run the website.

4. TEST the service remotely  

To create and run the website: 

- Make a new folder C:/JsonWcfService. Two files will go here: a .svc file and web.config file.   

- Under C:/JsonWcfService, open new notepad file, name it "GetEmployees.svc", and place the following line in it:  

<% @ServiceHost Service="JsonWcfService.GetEmployees" %>  

- Open new notepad file, place the following code in it, and name it "web.config". Basically, the endpoints and service behaviors here have to match the web.config from your service in Visual Studio 

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
  <system.serviceModel>
    <services>
      <service name="JsonWcfService.GetEmployees" behaviorConfiguration="EmpServiceBehaviour">
        <endpoint address ="" binding="webHttpBinding" contract="JsonWcfService.IGetEmployees" behaviorConfiguration="web">
        </endpoint>
        <host>
          <baseAddresses>
            <add baseAddress="http://localhost:8000/" />
          </baseAddresses>
        </host>
      </service>
    </services>
    
    <behaviors>
      <serviceBehaviors>
        <behavior name="EmpServiceBehaviour">
          <serviceMetadata httpGetEnabled="true"/>
          <serviceDebug includeExceptionDetailInFaults="false"/>
        </behavior>
      </serviceBehaviors>
      <endpointBehaviors>
        <behavior name="web">
          <webHttp/>
        </behavior>
      </endpointBehaviors>
    </behaviors>
  </system.serviceModel>
</configuration>   

- Make a new subfolder C:/JsonWcfService/bin, and copy "JsonWcfService.dll" from your ~project/bin directory to C:/JsonWcfService/bin.   

- Now, we just need to add the website in IIS7.5 and point to the folder we just created:    

• Open IIS(Internet Innformation Services) manager from cmd prompt or from windows search box  
• Under Connections tree, expand "sites" > Right Click on Default Web Site > Add Application
• use "JsonWcfService" for alias and Browse to folder "C:\JsonWcfService" for physical path
• press ok   

This is it. To get the employees list from same machine, type the following link in your browser: http://localhost/JsonWcfService/GetEmployees.svc/json/employees 

To get the employees list from a remote machine on your intranet: http://"yourIPAddress"/JsonWcfService/GetEmployees.svc/json/employees      

Replace "yourIPAddress" by the IP address of the machine that hosts your service. Get your IP address: cmd prompt>ipConfig.    

e.g: http://192.168.1.104/JsonWcfService/GetEmployees.svc/json/employees       

5.Consume JSON in an iPhone application 

Luckily, iOS5 has native JSON SDKs that will make this task very straight forward.   

In iOS, add a basic simple View app with 2 UIlabels to display the JSON text, and a more human readable format. Wire them up.

@property (weak, nonatomic) IBOutlet UILabel *jsonText;
@property (weak, nonatomic) IBOutlet UILabel *humanText;  

Add the following code to your viewDidLoad method:   

//wcf service
#define WcfSeviceURL [NSURL URLWithString: @"http://192.168.1.102/JsonWcfService/GetEmployees.svc/json/employees"]
 
- (void)viewDidLoad
{
    [super viewDidLoad];
    NSError *error = nil;
    NSData *data = [NSData dataWithContentsOfURL:WcfSeviceURL options:NSDataReadingUncached error:&error];
    jsonText.text = [[NSString alloc] initWithData:data encoding:NSUTF8StringEncoding];
 
    if(!error)
    {
        NSDictionary* json = [NSJSONSerialization 
                              JSONObjectWithData:data 
                              options:NSJSONReadingMutableContainers 
                              error:&error];  
        
        NSMutableArray *array= [json objectForKey:@"GetAllEmployeesMethodResult"];
        
        for(int i=0; i< array.count; i++)
        {
            NSDictionary *empInfo= [array objectAtIndex:i];
            
            NSString *first = [empInfo objectForKey:@"firstname"];
            NSString *last = [empInfo objectForKey:@"lastname"];
            NSString *salary  = [empInfo objectForKey:@"salary"];
            
            //Take out whitespaces from String
            NSString *firstname = [first
                                   stringByReplacingOccurrencesOfString:@" " withString:@""];
            NSString *lastname = [last
                                   stringByReplacingOccurrencesOfString:@" " withString:@""];
            
            humanText.text= [humanText.text stringByAppendingString:[NSString stringWithFormat:@"%@ %@ makes $%@.00 per year.\n",firstname,lastname,salary]];
        }
        
    }
}

Notice the use of NSJSONSerialization class, which turns JSON objects to NSFoundation objects, like NSDictionary, NSArray, NSString. So, NSDictionary* json contains the list of employees. 

So, here is the employees list output displayed in your iPhone app in JSON and human human formats 

r    

Points of Interest  

The article provides you with a taste of how to access data across platforms. With the growing demand in the mobile industry, going across the boundaries becomes a must. It is often the case that mobile apps would like to communicate with other servers and databases. Restful WCF is a nice lightweight solution which can return JSON or xml and allows to be consumed by different technologies, which makes it very flexible for the future. WCF services provide a great alternative for traditional web services.

History  

V1.0 June 18, 2012