Click here to Skip to main content
Click here to Skip to main content

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

, 19 Jun 2012 CPOL
Rate this:
Please Sign up or sign in to vote.
Access SQL database from iPhone Via WCF and JSON hosted on IIS 7.5

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

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Samer M. Abdallah
Technical Lead
United States United States
I am a Software Developer and application analyst with over 12 years of experience in OOD, Embedded real-time systems design and development, GUI, databases, mobile development, and back-end web development.

My skill Set: ASIC chip design, Sockets,I/O, Assembly, OOD, C/C++, C#,Java, ADO.NET, WCF, Databases.
Education: Master in computer Engineering, Mathematics, Oracle 10g certification.

Comments and Discussions

 
QuestionI've met some error at step4 remotly Connection Pinmemberkimjoonsub19-Nov-14 19:42 
Questionwhite screen PinmemberMember 964742917-May-14 3:10 
QuestionMy vote of 5* PinmemberMas1115-Dec-13 1:18 
Questionnice article - may need some changes Pinmemberbdmize3-Dec-13 18:29 
Questionget error while passing error Pinmemberkamal kumar1527-Nov-13 2:22 
QuestionCan I use wcf service on IIS 6.0 PinmemberHtun Lin Aung11-Jun-13 5:42 
AnswerRe: Can I use wcf service on IIS 6.0 PinmemberSamer M. Abdallah25-Oct-13 20:39 
QuestionCan I use webservice to connect to iPhone app with sql server. PinmemberHtun Lin Aung9-Jun-13 20:07 
QuestionGreat Article! PinmemberKrutiCh6-Jun-13 0:33 
AnswerRe: Great Article! PinmemberSamer M. Abdallah25-Oct-13 20:43 
Questionhow to setting IIS Pinmembermvtt.uit9121-Mar-13 0:52 
AnswerRe: how to setting IIS PinmemberSamer M. Abdallah25-Oct-13 20:46 
QuestionA best way to success this Article Pinmembernickykiet8331-Dec-12 22:04 
Questionupdate or insert data from iPhone Pinmembermilon_bd27-Dec-12 20:47 
SuggestionA lot of setting needs to be done [modified] Pinmemberdongsuyangflkl4-Oct-12 11:52 
QuestionHTTP Error 404.0 - Not Found - works now ! PinmemberMember 775694126-Sep-12 13:45 
QuestionAdding Parameters PinmemberMember 939115829-Aug-12 3:40 
AnswerRe: Adding Parameters PinmemberH49213-Nov-12 4:56 
QuestionIIS set up PinmemberSuperspec22-Aug-12 0:04 
Questionit works...but how to send json back from iphone to wcf? Pinmembersscorpio4-Aug-12 4:01 
AnswerRe: it works...but how to send json back from iphone to wcf? PinmemberSamer M. Abdallah10-Aug-12 6:54 
GeneralRe: it works...but how to send json back from iphone to wcf? PinmemberEric KY Law14-Nov-12 15:19 
GeneralRe: it works...but how to send json back from iphone to wcf? Pinmembernickykiet832-Jan-13 0:06 
GeneralRe: it works...but how to send json back from iphone to wcf? Pinmemberbenhadi2-Jan-14 7:54 
GeneralIOS project Pinmembergeisy2-Aug-12 14:00 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Terms of Use | Mobile
Web04 | 2.8.141216.1 | Last Updated 19 Jun 2012
Article Copyright 2012 by Samer M. Abdallah
Everything else Copyright © CodeProject, 1999-2014
Layout: fixed | fluid