AdventureWorks database for SQL Azure

 

In this post we will walkthrough step by step to create AdventureWorks database for SQL Azure. We will use AdventureWorks database for SQL Azure from codeplex. To create follow the step as below,

Step 1

Go to http://msftdbprodsamples.codeplex.com/

Step 2

Download SQL Azure OLTP from the codeplex site

image

Step 3

After downloading unzip the file in a folder. After unzipping file open command prompt and change directory to unzipped folder. I have unzipped the file in folder names SQLAzurePortal.

image

Step 4

Cross check that your machine IP address is in range of the firewall rules.

image

Step 5

Run the command as following

image

In case you wonder what is yours SQL Azure Server name, you can check that as following. Log in to SQL Azure portal and right panel you can get information about username and server name

image

Your command should be as following

image

After successful installation with data in SQL Azure, you should get following successful message.

image

Now to verify that whether AdventureWorks database got installed successfully, open SQL Azure portal and in database you should able to see that database has been listed there

image

In this way you have successfully installed AdventureWorks database on SQL Azure. I hope this post was useful. Thanks for reading

 

Data from Cloud on Windows Phone

Data from cloud on the phone “, it may appear as a buzzing sentence to you. In this post, I try to minimize complexity of accessing data from cloud to be specific from SQL Azure in Windows Phone. In this post I will walkthrough step by step accessing data from SQL Azure in Windows Phone.

image

I have divided task in three parts.

image

We will expose all the operation on Student database residing in SQL Azure as WCF REST Service. In this case REST is working on JSON data.

Setting up project

Very first let us set up the project. We want to expose operation as WCF Service hosted in Windows Azure. So to do that, you need to create WCF Service Role.

image

Creating Data Model

We are going to create DataModel using LINQ to SQL Class. To create DataModel right click on project then select add new item and choose LINQ to SQL Class form Data tab.

clip_image002

Next you need to choose the option Server explorer and add a new connection. In Add new connection windows provide database information for SQL Azure.

clip_image003

From drop down you choose database of your choice. In this case I am going to select Student database. After selecting database on the designer, I am dragging and dropping table Person. On the designer (dbml) you should have Person class .As of now we have created DataModel. In solution explorer you will find dbml and dbml.cs file has been created.

Creating Service

Data Transfer Object class represents entity from Data Model we want to expose as part of service contract. We need to create Data Transfer Object class. This class will act as Data Contract between Service and Client. To add a Data Transfer Object class go ahead and add a new class to project as below.

PersonDTO.cs


using System.Runtime.Serialization;

namespace RESTJSONStudentsData
{
[DataContract]
public class PersonDTO
{
[DataMember]
public string StudentId { get; set; }
[DataMember]
public string FirstName { get; set; }
[DataMember]
public string LastName { get; set; }

}
}


We have created Data Contract. Now we need to create Service Contract. Service Contract must be attributed to behave as WCF REST Service. I have set request and response format as JSON.

IService1.cs


using System.Collections.Generic;
using System.ServiceModel;
using System.ServiceModel.Web;

namespace RESTJSONStudentsData
{
[ServiceContract]
public interface IService1
{
[OperationContract]
[WebGet(UriTemplate="/Persons",
RequestFormat=WebMessageFormat.Json,
ResponseFormat=WebMessageFormat.Json]
List<PersonDTO> GetPersons();
}
}

Now we need to implement the service. In Service implementation, I am querying Person table of Student database using LINQ and constructing List of PersonDTO.

Service1.svc.cs


using System.Collections.Generic;
using System.Linq;

namespace RESTJSONStudentsData
{

public class Service1 : IService1
{

public List<PersonDTO> GetPersons()
{
DataClasses1DataContext context = new DataClasses1DataContext();
List<PersonDTO> result = (from r in context.Persons
select new PersonDTO
{
FirstName = r.FirstName,
LastName = r.LastName,
StudentId = r.PersonID.ToString()
}).ToList<PersonDTO>();
return result;

}
}
}

Next in this section we need to configure service. We need to configure service webHttpBinding to eanble it as REST Service. So in Web.Config we need to do the below changes.

Web.Config


<system.serviceModel>

<behaviors>
<serviceBehaviors>
<behavior name ="servicebehavior">
<serviceMetadata httpGetEnabled="true"/>
<serviceDebug includeExceptionDetailInFaults="false"/>
</behavior>
</serviceBehaviors>
<endpointBehaviors>
<behavior name="restbehavior">
<webHttp/>
</behavior>
</endpointBehaviors>
</behaviors>
<services>
<service name ="RESTJSONStudentsData.Service1" behaviorConfiguration="servicebehavior" >
<endpoint name ="RESTEndPoint"
contract ="RESTJSONStudentsData.IService1"
binding ="webHttpBinding"
address ="rest"
behaviorConfiguration ="restbehavior"/>

</service>
</services>

<serviceHostingEnvironment multipleSiteBindingsEnabled="true" />
</system.serviceModel>


After configuring, Service ready for hosting. We need to host it in Windows Azure Portal. For that right click on Windows Azure project and select Package and Upload this package to one of the hosted service. Up to this step we have created and hosted WCF REST Service returning JSON.

Consuming Service in Windows Phone

To consume REST Service in Windows Phone 7 and then parse JSON, you need to add below references in Windows Phone 7 project.

clip_image001

We need a class to represent PersonDTO class .For that makes a class called Person at client side.

Person.cs


namespace PhoneApp11
{
public class Person
{
public string StudentId { get; set; }
public string FirstName { get; set; }
public string LastName { get; set; }

}
}

As the design of the page I have put a ListBox. Data returned from cloud table will get bind to this ListBox.

MainPage.xaml



<phone:PhoneApplicationPage
x:Class="PhoneApp11.MainPage"
xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"
xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"
xmlns:phone="clr-namespace:Microsoft.Phone.Controls;assembly=Microsoft.Phone"
xmlns:shell="clr-namespace:Microsoft.Phone.Shell;assembly=Microsoft.Phone"
xmlns:d="http://schemas.microsoft.com/expression/blend/2008"
xmlns:mc="http://schemas.openxmlformats.org/markup-compatibility/2006"
mc:Ignorable="d" d:DesignWidth="480" d:DesignHeight="768"
FontFamily="{StaticResource PhoneFontFamilyNormal}"
FontSize="{StaticResource PhoneFontSizeNormal}"
Foreground="{StaticResource PhoneForegroundBrush}"
SupportedOrientations="Portrait" Orientation="Portrait"
shell:SystemTray.IsVisible="True"
Loaded="PhoneApplicationPage_Loaded">

<!--LayoutRoot is the root grid where all page content is placed-->
<Grid x:Name="LayoutRoot" Background="Transparent">
<Grid.RowDefinitions>
<RowDefinition Height="Auto"/>
<RowDefinition Height="*"/>
</Grid.RowDefinitions>

<!--TitlePanel contains the name of the application and page title-->
<StackPanel x:Name="TitlePanel" Grid.Row="0" Margin="12,17,0,28">
<TextBlock x:Name="ApplicationTitle" Text="data from SQL Azure" Style="{StaticResource PhoneTextNormalStyle}"/>
<TextBlock x:Name="PageTitle" Text="students" Margin="9,-7,0,0" Style="{StaticResource PhoneTextTitle1Style}"/>
</StackPanel>

<!--ContentPanel - place additional content here-->
<Grid x:Name="ContentPanel" Grid.Row="1" Margin="12,0,12,0">
<ListBox x:Name="lstPerson" >
<ListBox.ItemTemplate>
<DataTemplate>
<StackPanel Orientation="Vertical">
<TextBlock Text="{Binding LastName}" Style="{StaticResource PhoneTextTitle1Style}" />
<StackPanel Orientation="Horizontal">
<TextBlock Text="{Binding StudentId}" Style="{StaticResource PhoneTextGroupHeaderStyle}" />
<TextBlock Text="{Binding FirstName}" Style="{StaticResource PhoneTextTitle2Style}" />
</StackPanel>
</StackPanel>
</DataTemplate>
</ListBox.ItemTemplate>
</ListBox>

</Grid>
</Grid>

</phone:PhoneApplicationPage>


We need to make a call to the service and on download of JSON data as string; we need to parse JSON using System.RunTime.Serlization.JSON

MainPage.xaml.cs


using System;
using System.Collections.Generic;
using System.Net;
using System.Windows;
using Microsoft.Phone.Controls;
using System.IO;
using System.Runtime.Serialization.Json;
using System.Text;

namespace PhoneApp11
{
public partial class MainPage : PhoneApplicationPage
{
// Constructor
public MainPage()
{
InitializeComponent();
}

private void PhoneApplicationPage_Loaded(object sender, RoutedEventArgs e)
{


WebClient proxy = new WebClient();
proxy.DownloadStringCompleted +=
new DownloadStringCompletedEventHandler(proxy_DownloadStringCompleted);
proxy.DownloadStringAsync(new Uri("http://debugmodesqlazurejson.cloudapp.net/Service1.svc/rest/Persons"));


}

void proxy_DownloadStringCompleted(object sender, DownloadStringCompletedEventArgs e)
{
Stream stream = new MemoryStream(Encoding.Unicode.GetBytes(e.Result));
DataContractJsonSerializer obj = new DataContractJsonSerializer(typeof(List<Person>));
List<Person> result = (List < Person >) obj.ReadObject(stream);
lstPerson.ItemsSource = result;
}

}
}


In above code we are creating DataContractJsonSerializer object and passing List of Person to deseriliaze because service is returning list of PersonDTO. And as mentioned earlier Person class is representing PersonDTO class. Now go ahead and press F5 to run the application. You should be getting details of Person table from School database residing on SQL Azure.

clip_image002[7]

In later post we will explore further to perform other CRUD operations. I hope this post is useful. Thanks for reading.

SQL Azure to Developers: Some Basic Concepts

Objective

In this post we will focus on overview of SQL Azure along with a first look on SQL Azure Management Portal. Essentially we will cover in this part

  • What is Cloud Database
  • What is SQL Azure
  • Create and Manage Database in SQL Azure
  • Fire Wall in SQL Azure

What is Cloud Database?

For a developer a better perspective on database is always better and it helps to write different layers of application in effective and efficient way. Database in cloud is new buzz and very much appreciative technology. There are two words constitute CLOUD DATABASE. We are very much aware of term DATABASE whereas CLOUD may be newer or ambiguous to us.

In very broader term, Cloud can be termed as next generation of Internet. On a normal scenario you find on which server your database is residing. You have very minute level administrative control on database server and you have a physical sense of database server. Whereas imagine if you are not aware of where your database is residing? , you access and perform all operation on database via Internet. Scalability, Manageability and all other administration task on database is performed by some third party. You only pay for the amount of data residing on the database provided by third party. You work on use and pay model. In that case you can say database is in cloud.

There are many cloud service providers like Microsoft, Amazon etc. to name a few. All vendors do have their own cloud servicing model and pricing. Microsoft cloud platform is known as Windows Azure. Essentially Windows Azure is cloud operating system offered by Microsoft. As part of Windows Azure, there are five services offered by Microsoft

  1. Windows Azure
  2. SQL Azure
  3. Office 365
  4. App Fabric and Caching services
  5. Marketplace

In this three part article, we are going to focus our discussion to SQL Azure. We will focus on essential aspects need to know about SQL Azure as dot net developer. To be very precise on SQL Azure, we can say “SQL Server in cloud is known as SQL Azure “. When you have chosen to create relational database in Microsoft data center then you can say your database is in cloud or in SQL Azure.

In this article, we will cover

  • What is SQL Azure?
  • A first look on SQL Azure Management portal
  • SQL Azure Database edition
  • Firewall setting for SQL Azure

What is SQL Azure?

 

 

 

SQL Azure is cloud based service from Microsoft. It allows you to create your Database in one of the Microsoft Data center. In a very generic statement we can say SQL Azure allows creating Database on the cloud. It provides highly available database. It is based on SQL Server. It supports built in Fault tolerance and no physical administration is required. It supports TSQL and SSMS.

Advantage of using SQL Azure

  • In built Fault tolerance support
  • No Physical Administration required
  • Very high availability
  • Multitenant
  • Pay as you go pricing
  • Support of TSQL
  • Highly scalable

We have done enough of theoretical discussion, now let us login to SQL Azure portal and create a database in the cloud.

Create and Manage Database in SQL Azure

To create Database in SQL Azure, You need to follow below steps

Step1

Login SQL Azure portal with your live credential

https://sql.azure.com/

Step 2

Click on SQL Azure tab and select Project

image

Step3

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

Here in this account there are two database already created. They are master and student database. Master database is default database created by SQL Azure.

image

Step 4

Click on Create Database

image

Step 5

Give the name of Database. Select the edition as Web or Business and specify the max size of database.

image

Step 6

After that click on Create you can see on Databases tab that Demo1 database has been created.

image

 

Different types of SQL Azure Database edition

At time of creating Database you might have seen, there were two Database editions

image

Web Edition Relational Database includes:

  • Up to 5 GB of T-SQL based relational database
  • Self-managed DB, auto high availability and fault tolerance
  • Support existing tools like Visual Studio, SSMS, SSIS, BCP
  • Best suited for Web application, Departmental custom apps

Business Edition DB includes:

  • Up to 150 GB of T-SQL based relational database*
  • Self-managed DB, auto high availability and fault tolerance
  • Additional features in the future like auto-partition, CLR, fanouts etc
  • Support existing tools like Visual Studio, SSMS, SSIS, BCP
  • Best suited for Saas ISV apps, custom Web application, Departmental apps

While creating Database in SQL Azure you can choose among either of two options

image

If we choose Web Edition then Maximum size we can choose is 5 GB

image

If we choose Business Edition then Maximum size we can choose is 150 GB

SQL Azure portal is having very effective Silverlight based user interactive UI and many more operations can be performed through the UI. There is new database manager and it allows us to perform operations at table and row level.

Now we can perform many more operations through Database option of new Windows azure portal.

  • Create a database
  • Create/ delete a table
  • Create/edit/delete rows of table.
  • Create/edit stored procedure
  • Create/edit views
  • Create / execute queries etc. . . .

You can manage database using Data Base Manager.

image

Accept given term and conditions and click Ok. you will get popup asking password to connect to database. Provide password and click on Connect.

image

After logon you will get Mange user interface as below,

 

 

image

And in left hand panel you will have options as below,

image

For more on this watch the video of the post.

Firewall in SQL Azure

In this way, you can perform almost all the basic operations from new SQL Azure Data Base Manager.

SQL Azure provides security via Firewall. By default Database created on SQL Azure is blocked by firewall for the security reason. Any try to external access or access from any other Azure application is blocked by firewall.

image

Image taken from MSDN

Connection Strings

You can copy Connection string from SQL Azure portal as well.

image

Connecting from Local system

When we want to connect SQL Azure portal from network system or local system then we need to configure firewall at local system. We need to create an exception for port 1433 at local firewall

Connecting from Internet

The entire request to connect to SQL Azure from Internet is blocked by SQL Azure firewall. When a request comes from Internet

  1. SQL Azure checks the IP address of system making the request
  2. f IP address is in between the range of IP address set as firewall rule of SQL Azure portal then connection get established.

Firewall rules can be Added, Updated and Deleted in two ways

  1. Using SQL Azure Portal
  2. Using SQL Azure API

Manipulating Firewall rules using SQL Azure Portal

After login to Windows Azure portal, click on Database option and select Database server from left tab. You can see firewall rules listed there.

image

A new rule can be added by clicking on Add button.

image

To connect from other Windows Azure application for same subscription check the check box

image

An existing firewall rules can be edited and deleted also by selecting Edit and delete option respectively.

Conclusion

In this part we discussed various elementary concepts of cloud database and SQL Azure. In further part we will go deep to understand other essential concepts needed to know as developer.

Fetching Data from SQL Azure table in Windows 7.1 [Mango] Phone using WCF Data Service

In this article, we will discuss about how to consume data from cloud [SQL Azure] in a Windows 7.1 [Mango] phone. SQL Azure and Windows 7.1 [Mango] phones are two common terms that you can hear about from many tech-savvy people. So I thought it is the right time to integrate these technologies. In this article, I have tried my best not to focus on the theoretical aspects. Instead, I will present a step-by-step approach on how to consume Data from cloud in a Windows 7 phone application.

What we intend to achieve is given below:

image

 

In this article, we will cover

  1. Migrating local Database to SQL Azure
  2. Creating Data Model on Database in Cloud
  3. Expose Data Model as WCF Data Service
  4. Consumption of Data from cloud in Windows 7.1 or Mango phone through WCF Data Service.

 

The block diagram for the same is given below. We have a School Database on our local (In premise) SQL Server. We will be moving the local school database along with the schema and data to SQL Azure. Then, we will create a Data Model on the school database in the cloud and expose that Data model as WCF Data Service. Once the data from cloud is exposed as WCF Data Service, we will fetch that in the Windows 7.1 phone

 

image

So, actually, there are two major steps involved,

  1. Exposing Data from cloud as WCF Data Service
  2. Consuming WCF Data Service in Windows 7 Phone

 

Creating local Database and migrating to SQL Azure

Creating Database

The first step is to create the 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

From the Pop up, select Set Scripting option.

image

 

Give the file name by selecting Save to file option.

Now the main thing to be noticed here is that we need to so some change in the advanced setting. For that, click on advanced options.

 

image

 

And in types of data to script, select Script and Data – both options.

image

 

After that, click next and Finish. You can see that 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 the SQL Azure portal with your live credential

https://sql.azure.com/

SQL Azure can be explored by selecting Database option from the left panel.

image

To create School Database, select Data base server and from Database section, select Create option.

image

Provide Database name, Edition and Maximum Size.

image

 

Run the Script in SQL Azure

Open the SQL Server management studio

clip_image001

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

clip_image002

After cancelling the dialog box, click on New Query (top left corner).

clip_image003

On clicking New Query, the Connect to Server dialog box appears again.

clip_image004

Now, here you need to provide the Server name of SQL Azure and Login credential of SQL Azure

You can get fully qualified server name in Properties tab of SQL Azure portal

image

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 the local SQL server management studio.

image

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

From Option, select School database.

image

Run the Script

Now once you have successfully connected to School Database in SQL Azure, copy the script and Run as given below.

 

image

 

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

 

clip_image001[6]

In this way, you have successfully migrated database to SQL AZURE Smile

 

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 the Web tab. Give a meaningful name to the web application.

image

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.

Here, I am going to use ADO.Net Entity model to create the data model. So to create an entity model, do the following:

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

image

3. Since we have table in SQL Azure DataBase, we are going to choose the option Select from database

image

 

4. Choose a new connection.

image

After clicking on New Connection, this is the important step. We need to give extra care here Smile

So provide the information as below,

image

Click on Test Connection to test if the connection established successfully or not? After this, you will get prompted if the Connection string contains sensitive data regarding if you want to keep that in configuration file or mange through program. You can select any one of these options according to what you need.

image

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

image

5. Select tables, views and stored procedure from the database 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

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, the name of the model which we created in the 2nd step is the data source. Our data model name is SchoolEntities

 

image

 

Now we need to set access rules for the 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 can put *.

 

image

 

image

 

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

So finally, the svc file will look like

 

image

Run WCF Data Service

Just press F5 to run the WCF Data Service. The Data Service will be hosted in the default ASP.Net server.

On running, you can see all the table is listed there.

clip_image002

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

clip_image004

Note: If your browser is not showing the expected result, make sure that the Feed reading of the browser is off. To do so, from the menu of IE, select Tools-> Internet Options -> Content

clip_image005

clip_image006

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

 

Consuming WCF Data Service in Windows 7 Phone

First, we need to do is to create proxy of WCF Data service for Windows 7 phone. So to do this,

  1. Open command prompt in administrator mode.
  2. Navigate to folder C:\Windows\Microsoft.Net\FrameWork\V4.0.30319 Version of framework may vary depending on .Net framework you are working on.
  3. Once navigated to this particular folder, run the below command to create proxy class. We will add this proxy class in Windows 7 mobile application.

 

image

Explanation of command

  • There is space between all /out, /uri and /version
  • We are redirecting the output to D drive and Temp folder. So make sure that the Temp folder exists in the D drive.
  • School.cs is name of proxy class. You can give any name of your choice here. Once the command runs successfully, you will get School.cs class in Temp folder of D drive.
  •  After uri tag, give the uri of WCF data service.

 

Now after the execution of this command, we will have School.cs in Temp folder of D Drive.  Smile

Download the ODATA client library for Windows 7 phone from the below link and save the dll on local drive. We will add this DLL in Windows 7 phone application.

Download OData client library for Windows 7 Phone from here

Create a windows 7.1 phone application. Open Visual Studio and select the Windows phone Application project template from Silverlight for Windows Phone tab.

 

image

Choose  Windows Phone 7.1

image

Right click on the project and add existing item. Then navigate to Temp folder where you have created the WCF Data Service proxy class. In our case, it is School.cs. We created this in the previous step.

clip_image001[8]

Now we need to add ODATA client library reference in the Windows 7 Phone application. Right click on the project and add reference. Navigate to the folder where you saved System.Data.Service.Clientdll

clip_image002[5]

Display Data from Person Table in Windows 7 Phone application

We are going to display records of Person table in the list box. So first, let us create an entity class at client side. Right click and add a class called Person

Person.cs

 



namespace PhoneClient
{
    public class Person
    {

        public string PersonID { get; set; }
        public string LastName { get; set; }
        public string FirstName { get; set; }


    }
}

 

Create a list box and we will bind the data in this list box. On click event of the Button, Data will get bound to the list box.

In below XAML ,

  1. We are creating a Button . On click event of Button , Data would be fetched from SQL Azure table.
  2. In List Box , we are binding the returned result

 

MainPage.xaml

 



<phone:PhoneApplicationPage
    x:Class="PhoneClient.MainPage"
    xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"
    xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"
    xmlns:phone="clr-namespace:Microsoft.Phone.Controls;assembly=Microsoft.Phone"
    xmlns:shell="clr-namespace:Microsoft.Phone.Shell;assembly=Microsoft.Phone"
    xmlns:d="http://schemas.microsoft.com/expression/blend/2008"
    xmlns:mc="http://schemas.openxmlformats.org/markup-compatibility/2006"
    mc:Ignorable="d" d:DesignWidth="480" d:DesignHeight="768"
    FontFamily="{StaticResource PhoneFontFamilyNormal}"
    FontSize="{StaticResource PhoneFontSizeNormal}"
    Foreground="{StaticResource PhoneForegroundBrush}"
    SupportedOrientations="Portrait" Orientation="Portrait"
    shell:SystemTray.IsVisible="True">

    <!--LayoutRoot is the root grid where all page content is placed-->
    <Grid x:Name="LayoutRoot" Background="Transparent">
        <Grid.RowDefinitions>
            <RowDefinition Height="Auto"/>
            <RowDefinition Height="*"/>
        </Grid.RowDefinitions>

        <!--TitlePanel contains the name of the application and page title-->
        <StackPanel x:Name="TitlePanel" Grid.Row="0" Margin="12,17,0,28">
            <TextBlock x:Name="ApplicationTitle" Text="Mango with SQL Azure" Style="{StaticResource PhoneTextNormalStyle}"/>

            <Button x:Name="btnGetData" Height="100" Width="449" Content="Get Data From SQL Azure Table" Background=" blue" />
        </StackPanel>

        <!--ContentPanel - place additional content here-->
        <Grid x:Name="ContentPanel" Grid.Row="1" Margin="12,0,12,0">

            <ListBox x:Name="lstDataFromCloud"
                 ItemsSource="{Binding}" >
                <ListBox.ItemTemplate>
                    <DataTemplate>
                        <StackPanel Orientation="Horizontal" >
                            <TextBlock Text="{Binding PersonID}" Margin="20" />
                            <TextBlock Text="{Binding LastName}" Margin="20" />
                            <TextBlock Text="{Binding FirstName }" Margin="20" />
                        </StackPanel>
                    </DataTemplate>
                </ListBox.ItemTemplate>
            </ListBox>


        </Grid>
    </Grid>



</phone:PhoneApplicationPage>


 

 

Now on click event of the button, we need to call the WCF Data Service and bind the list box . We are making an asynchronous call here .

MainPage.xaml.cs


using System;
using System.Collections.Generic;
using System.Linq;
using System.Windows;
using Microsoft.Phone.Controls;
using System.Data.Services.Client;
using SchoolModel;


namespace PhoneClient
{
    public partial class MainPage : PhoneApplicationPage
    {
        // Constructor
        SchoolEntities schoolEntity;
        List<Person> lstPersonClient;
        List<SchoolModel.Person> lstpersonWcf;


        public MainPage()
        {
            InitializeComponent();
            schoolEntity = new SchoolEntities(new Uri("http://localhost:27274/WcfDataService1.svc/"));
            btnGetData.Click += new RoutedEventHandler(btnGetData_Click);

            lstPersonClient = new List<Person>();
            lstpersonWcf = new List<SchoolModel.Person>();

        }

        void btnGetData_Click(object sender, RoutedEventArgs e)
        {

            MessageBox.Show("Hi");

            var query = schoolEntity.CreateQuery<SchoolModel.Person>("People");
            query.BeginExecute(ar =>
            {
                DataLoad(ar);
            }, query);

        }

                private void DataLoad(IAsyncResult result)
        {
            DataServiceQuery<SchoolModel.Person> query = result.AsyncState as DataServiceQuery<SchoolModel.Person>;
            lstpersonWcf = query.EndExecute(result).ToList();
            Dispatcher.BeginInvoke(() =>
                   {
                       foreach (var r in lstpersonWcf)
                       {
                           lstPersonClient.Add(new Person {PersonID = r.PersonID.ToString() , LastName= r.LastName , FirstName = r.FirstName});
                       }
                       this.lstDataFromCloud.DataContext = lstPersonClient ;
                   }


                );
        }
    }
}




 

On running you should get output as below,  Smile

image

 

If this post was useful to you then buy me a coffee Smile .  Thanks for reading.

 

 

Billing in SQL Azure

1. Web edition databases are billed at the 1GB rate for databases below 1GB of total data or at 5GB rate for databases between 1GB and 5GB size.

2. Business edition databases are billed at 10GB increments (10GB, 20, 30, 40 and 50GB).

In a particular day billing is done on the pick DB size. To understand billing better let us take an example, suppose there is Web Edition DB with maximum size 5 GB. On a particular day if database usages are 800MB then you will be charged for 1 GB on that day. If next day DB usage increases to 2 GB then you would be charge for 5 GB. In Business edition charging window is of 10 GB.

SQL Azure introduces two dynamic management views database_usage and bandwidth_usage. These views can be used in TSQL for billing and bandwidth information.

image

If you want to calculate pricing for any particular type of Database edition probably you would have to do simple mathematical calculations.

Let us say,

R = Monthly Rate of Data base edition. Monthly rate for both web and business edition is different.

D = Number of Day in Month

N = Number of Databases

U = Unit consumption in a given day

Price for Month = (R /D)*U*N

So if

R = 10 $ per month

D = 30 days

N = 1 number of Database

U = 5 unit

Price for Day = (10/30)*1*4 = 1.34 $

TSQL to get current month prices

image

 


select sku,
    sum (
    case when sys.database_usage.sku = 'web'
            then (quantity*10/31)
        when sys.database_usage.sku = 'business'
            then (quantity*100/31)
    end ) as 'cost'
    from sys.database_usage
    where datepart(yy,time) = datepart(yy, getutcdate()) and
    datepart(mm,time) = datepart(mm, getutcdate())
    group by sku

In above SQL query

1. 100 $ is monthly charge for business edition and 10 $ is for Web Edition [Assumption]

2. Number of Days is 31

3. Query is running against Master Database.

If you want to list out different units with their edition below query can do the task

image

Above query will return time, edition and quantity.

select time,sku,quantity   from sys.database_usage    where datepart(yy,time) = datepart(yy, getutcdate()) and   datepart(mm,time) = datepart(mm, getutcdate())

WCF Data Service with SQL Azure

In this article we will expose database from cloud or SQL Azure as WCF Data Service.

There two steps mainly involved in this

1. Creating local Database and migrating to SQL Azure

2. Exposing SQL Azure Database as WCF DATA Service

Step1: Creating local Database and migrating to SQL Azure

Creating Database

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.

clip_image001

From Pop up select Set Scripting option.

clip_image003

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.

clip_image004

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

clip_image005

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.

clip_image007

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.

clip_image009

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

clip_image011

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

clip_image012

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.

clip_image014

Run the Script in SQL Azure

Open SQL Server management studio

clip_image015

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

clip_image016

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

clip_image017

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

clip_image018

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

clip_image020

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.

clip_image021

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

clip_image022

From Option select School database.

clip_image023

Run the Script

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

clip_image025

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

clip_image026

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.

clip_image028

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.

clip_image030

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

clip_image031

4. Choose a new connection.

clip_image032

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

So provide the information as below,

clip_image033

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.

clip_image034

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.

clip_image035

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.

clip_image037

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

clip_image039

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

clip_image041

clip_image042

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

clip_image044

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.

clip_image046

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

clip_image048

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

clip_image049

clip_image050

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 .