Click here to Skip to main content
15,867,686 members
Articles / Web Development / IIS

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

Rate me:
Please Sign up or sign in to vote.
4.88/5 (25 votes)
19 Jun 2012CPOL5 min read 185.8K   2.6K   38   41
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. 

Image 1

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" 

Image 2

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".  

Image 3

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"

C#
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.  

 Image 4 

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.  

Image 5

Image 6

C#
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!     

C#
<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 

Image 7

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" %>  

Image 8

- 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   

 Image 9

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 

Image 10

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       

Image 11

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.

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

Add the following code to your viewDidLoad method:   

C#
//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 

Image 12r    

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)


Written By
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

 
QuestionThank You Pin
bprashant2616-Sep-15 4:33
bprashant2616-Sep-15 4:33 
QuestionHow to create an android app to consume the WCF service created here Pin
Member 116684269-May-15 22:14
Member 116684269-May-15 22:14 
GeneralMy vote of 5 Pin
Humayun Kabir Mamun25-Feb-15 17:03
Humayun Kabir Mamun25-Feb-15 17:03 
QuestionProblem in remotly connection Pin
Michał Rędziński28-Dec-14 15:41
Michał Rędziński28-Dec-14 15:41 
I test everything but it is still not working.

I have another test function:
C#
public string GetData(string value)
        {
            return string.Format("You entered number: {0}", value);
        }


and it's working good

but this function:
C#
public List<wsSensors> GetAllSensors()
        {
            WeatherStationServiceDataContext dc = new WeatherStationServiceDataContext();
            List<wsSensors> results = new List<wsSensors>();

            foreach (Sensor cust in dc.Sensors)
            {
                results.Add(new wsSensors(cust.ID, cust.Name));
            }

            return results;
        }


had some problem. It work under IIS Express, but when I deploy it to local IIS I have error:
Serwer napotkał błąd podczas przetwarzania żądania. Więcej informacji zawierają dzienniki serwera.

In english:
The server encountered an error processing the request. See server logs for more details.


Solved, problem was in Connection String. I was using Windows authentication and everything was fine becasue Visual Studio and IIS Express inherits privileges and permissions after my windows account but when I start this service in local IIS it inherit permissions after network service and then any sql request can't be realised.
Solution: Create new login to database for service and use in coonnection string SQL Server authentication using this login.

modified 29-Dec-14 15:04pm.

QuestionI've met some error at step4 remotly Connection Pin
kimjoonsub19-Nov-14 18:42
kimjoonsub19-Nov-14 18:42 
Questionwhite screen Pin
Member 964742917-May-14 2:10
Member 964742917-May-14 2:10 
QuestionMy vote of 5* Pin
Mas1115-Dec-13 0:18
Mas1115-Dec-13 0:18 
Questionnice article - may need some changes Pin
bdmize3-Dec-13 17:29
bdmize3-Dec-13 17:29 
Questionget error while passing error Pin
kamal kumar1527-Nov-13 1:22
kamal kumar1527-Nov-13 1:22 
QuestionCan I use wcf service on IIS 6.0 Pin
Htun Lin Aung11-Jun-13 4:42
Htun Lin Aung11-Jun-13 4:42 
AnswerRe: Can I use wcf service on IIS 6.0 Pin
Samer M. Abdallah25-Oct-13 19:39
Samer M. Abdallah25-Oct-13 19:39 
QuestionCan I use webservice to connect to iPhone app with sql server. Pin
Htun Lin Aung9-Jun-13 19:07
Htun Lin Aung9-Jun-13 19:07 
QuestionGreat Article! Pin
KrutiCh5-Jun-13 23:33
KrutiCh5-Jun-13 23:33 
AnswerRe: Great Article! Pin
Samer M. Abdallah25-Oct-13 19:43
Samer M. Abdallah25-Oct-13 19:43 
GeneralRe: Great Article! Pin
mikesneider25-Jun-15 11:13
mikesneider25-Jun-15 11:13 
Questionhow to setting IIS Pin
mvtt.uit9120-Mar-13 23:52
mvtt.uit9120-Mar-13 23:52 
AnswerRe: how to setting IIS Pin
Samer M. Abdallah25-Oct-13 19:46
Samer M. Abdallah25-Oct-13 19:46 
QuestionA best way to success this Article Pin
nickykiet8331-Dec-12 21:04
nickykiet8331-Dec-12 21:04 
Questionupdate or insert data from iPhone Pin
Mizanur Rahman Milon27-Dec-12 19:47
Mizanur Rahman Milon27-Dec-12 19:47 
SuggestionA lot of setting needs to be done Pin
dongsuyangflkl4-Oct-12 10:52
dongsuyangflkl4-Oct-12 10:52 
QuestionHTTP Error 404.0 - Not Found - works now ! Pin
Member 775694126-Sep-12 12:45
Member 775694126-Sep-12 12:45 
QuestionAdding Parameters Pin
Member 939115829-Aug-12 2:40
Member 939115829-Aug-12 2:40 
AnswerRe: Adding Parameters Pin
H49213-Nov-12 3:56
H49213-Nov-12 3:56 
QuestionIIS set up Pin
Superspec21-Aug-12 23:04
Superspec21-Aug-12 23:04 
Questionit works...but how to send json back from iphone to wcf? Pin
sscorpio4-Aug-12 3:01
sscorpio4-Aug-12 3:01 

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

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