WCF Data Service with SQL Azure

No.of Views2545
Bookmarked0 times
Downloads 
Votes0
By  Dhananjay Kumar   On  26 Feb 2011 10:02:52
Tag : WCF , REST Services
In this article we will expose database from cloud or SQL Azure as WCF Data Service. There two steps mainly involved in this,Creating local Database and migrating to SQL Azure and Exposing SQL Azure Database as WCF DATA Service.
email bookmarkadd commentsprint

Images in this article missing? We recently lost them in a site migration. We're working to restore these as you read this. Should you need an image in an emergency, please contact us at info@codegain.com

 

Introduction

In this article we will expose database from cloud or SQL Azure as WCF Data Service.  There two steps mainly involved in this,Creating local Database and migrating to SQL Azure
and Exposing SQL Azure Database as WCF DATA Service.

Step1:  Creating local Database and migrating to SQL Azure

Creating

The first step is to create database. We are going to use School database.  Script of sample School Database copy from here

Generate Database Script for SQL Azure

Right click on School Database and select Tasks. From Tasks select Generate Script. 

Image Loading

From Pop up select Set Scripting option.

 

Image Loading

Give the file name by selecting Save to file option.
Now the main thing to be noticed here is we need to so some change in advanced setting. For that click on Advanced options.

 

Image Loading

And in types of data to script select Script and Data both option.

 

Image Loading

After that click next and Finish. You can see a SQL file is created and we will be using this script to migrate our in house school database to SQL Azure. 

Create School Database in SQL Azure

Login SQL Azure portal with your live credential

https://sql.azure.com/
Click on SQL Azure tab. You will get the project , you have created for yourself.

 

Image Loading

Click on the project. In my case project name is debugmode.  After clicking on project, you will get listed the entire database created in your SQL Azure account.

Image Loading

Here in my account there are two database already created.  They are master and student database.  Master database is default database created by SQL Azure for you.
Click on Create Database

 

Image Loading

Give the name of your database.  Select the edition as Web and specify the max size of database.

Image Loading

You can select other option also for the edition as business
After that click on Create you can see on Databases tab that Demo1 database has been created.

Image Loading

Run the Script in SQL Azure

Open SQL Server management studio

 

Image Loading

You will get Connect to server dialog box. Click cancel on that.

Image Loading

After cancelling the dialog box click on New Query from left top

Image Loading

 On clicking New Query, you will get the connect to server dialog box again. 

Image Loading

Now here you need to provide, Server name of SQL Azure and Login credential of SQL Azure. 
To know what is database server name of SQL Azure portal, login to Windows Azure portal with your live credential and then click on SQL Azure tab

Image Loading

You will get the server name in form of
abc.database.windows.net
Where abc is name of your SQL Azure server.  We need to provide this server name at local sql server management studio. 

Image Loading

Make sure to select SQL Server Authentication and provide login user name and password of your SQL Azure database portal.
After that before clicking Connect click on Option

Image Loading

From Option select School database.

Image Loading

Run the Script
Now once you successfully got connected to School Database in SQL Azure.  Copy the script and Run like below.

Image Loading

After successfully running of script, run the below command and all the tables name will get listed.

Image Loading

In this way you successfully migrated database to SQL AZURE.

Step2: Exposing SQL Azure Database as WCF DATA Service

Create a Web Application
Create a new project and select ASP.Net Web Application project template from Web tab.  Give a meaning full name to the web application.

Image Loading

Create a Data Model

We can create a Data Model, which can be exposed as WCF Data Service in three ways

1.    Using ADO.Net Entity model.
2.    Using LINQ to SQL class.
3.    Custom Data Model.

For our purpose, I am going to use ADO.Net Entity model to create the data model.  So to create an entity model

1.    Right click on web application and add a new item
2.    Select ADO.Net Entity model from Data tab.

 

Image Loading

3.     Since we have table in SQL Azure Dat aBase. So we are going to choose option, select from database.

Image Loading

4.    Choose a new connection.

Image Loading

After clicking on New Connection, this is the more important step. We need to give extra care here.
So provide the information as below,

Image Loading

Click on Test Connection to test connection established successfully or not?  After that you will get prompted as Connection string contains sensitive data, do you want to keep that in configuration file or mange through program. You can select as of your choice and need.

Image Loading

On providing your option click on Next button, you can see all the tables, Views, Stored Procedures’ are available to chosen as part of data model for WCF Data Service.

Image Loading

5.    Select tables, views and stored procedure from data base you want to make as the part of your data model.

Creating WCF Data Service

1.    Right click on Web Application project and add a new item.
2.    Select WCF Data Service from Web tab. Give any meaningful name. I am leaving the default name here.

 

Image Loading

3.     After adding the WCF Data Service, we can see a service file with extension .svc has been added to the solution explorer.
Very first we need to put data source name. To do so uncomment the first commented line and put the data source name. In our case name of the model, which we created in 2nd step is the data source. Our data model  name is SchoolEntities

Image Loading

Now we need to set access rules for entity or entity set.  Since we have only one table, so either we can give name of the table explicitly or  if we want to set the same access rule for all the table in the data model or data source we could put  *.

Image Loading
Image Loading

So we are setting the access rule that, on the entity in data source perform all the operations.

 So finally svc file will look like

Image Loading

Run the WCF Data Service
Just press F5 to run the WCF Data Service. Data Service will be hosted in the default ASP.Net server.
On running you can see, all the table is listed there.

 

Image Loading

Append the URL with People and you will get all the records from People table

Image Loading

Note: If your browser is not showing the expected result, make sure Feed reading of browser is off.  To do from menu of IE and select tool and then Internet Option then Content

Image Loading
Image Loading

So we have exposed data from cloud using WCF Data service. Now any OADTA client can consume the data from cloud by consuming WCF Data Service.
Thanks  for reading , I hope this article was useful .

 
Sign Up to vote for this article
 
About Author
 
Dhananjay Kumar
Occupation-Software Engineer
Company-Infosys Technolgies,Pune
Member Type-Gold
Location-India
Joined date-20 Jul 2009
Home Page-http://dhananjaykumar.net/
Blog Page-http://dhananjaykumar.net/
Dhananjay Kumar is Microsoft MVP on connected system. He blogs at http://dhananjaykumar.net/ . You can follow him http://twitter.com/debugmode_/ and reach him at dhananjay.25july@gmail.com
 
 
Other popularSectionarticles
Comments
There is no comments for this articles.
Leave a Reply
Title:
Display Name:
Email:
(not display in page for the security purphase)
Website:
Message:
Please refresh your screen using Ctrl+F5
If you can't read this number refresh your screen
Please input the anti-spam code that you can read in the image.
^ Scroll to Top