Scriptstar

Updated : 30th Apr 2010

My favourite Author Matthew MacDonald is publishing a book on Silverlight 4.0. Please check here.

I was trying some silverlight business application that Brad Abrams was describing in his blog and it is an amazing silverlight series. If you want to learn silverlight (not the graphic stuff, dev stuff) then you hang on there for a while.

I am very much interested to integrate Brad’s app with SQL AZURE and it is straight forward but with a slight twist. To try this application, you need to have Silverlight developer tools installed and to see this in cloud (SQL AZURE) you need to have a SQL Azure CTP access.

I changed my database slightly because I struggle to open Brad’s database. Brad was using Super Employee database but I was using my own. Half of this article is lifted shamelessly from the Brad’s article. All I wanted to show you guys how easy it is to connect to SQL Azure. If you want to learn Silverlight and RIA stuff then he is the man.

Open you VS 2008

File -> New Project -> Silverlight Business Applications

Choose project type

Name the project SLSASampleAPP

If you wondering what the name stands for then SL stands for Silverlight, SA stands for SQL Azure.

Once you done, it will create two projects. One is Silverlight front end which is SLSASampleApp and server side project SASLSampleAPP.Web.

Don’t confuse with the name ‘SASLSampleAPP.Web’, this is not your Silverlight front end. The top one is the web front end.

Solution explorer view

Just hit F5 and see the application. It should be fine. If you are getting debugging error then you haven’t installed Silverlight dev tools. You go and get them and install and restart your computer. When you come back, I may not be here; you just bookmark my blog now and restart.

Now you should see just like this.

Browse view

Create a Database in your local SQL Server named as ‘SLSADb’

I assume, you guys can create the above database manually. If not I am copying the code for you. Just copy/paste and run in your SSMS. I am using full blown SQL Server 2005 Dev edition.

USE [master]
GO
/****** Object:  Database [SLSADb]    Script Date: 11/12/2009 12:35:47 ******/
CREATE DATABASE [SLSADb] ON  PRIMARY
( NAME = N'SLSADb', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\SLSADb.mdf' , SIZE = 3072KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
 LOG ON
( NAME = N'SLSADb_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\SLSADb_log.ldf' , SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
GO
EXEC dbo.sp_dbcmptlevel @dbname=N'SLSADb', @new_cmptlevel=90
GO
IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))
BEGIN
EXEC [SLSADb].[dbo].[sp_fulltext_database] @action = 'disable'
END
GO
ALTER DATABASE [SLSADb] SET ANSI_NULL_DEFAULT OFF
GO
ALTER DATABASE [SLSADb] SET ANSI_NULLS OFF
GO
ALTER DATABASE [SLSADb] SET ANSI_PADDING OFF
GO
ALTER DATABASE [SLSADb] SET ANSI_WARNINGS OFF
GO
ALTER DATABASE [SLSADb] SET ARITHABORT OFF
GO
ALTER DATABASE [SLSADb] SET AUTO_CLOSE OFF
GO
ALTER DATABASE [SLSADb] SET AUTO_CREATE_STATISTICS ON
GO
ALTER DATABASE [SLSADb] SET AUTO_SHRINK OFF
GO
ALTER DATABASE [SLSADb] SET AUTO_UPDATE_STATISTICS ON
GO
ALTER DATABASE [SLSADb] SET CURSOR_CLOSE_ON_COMMIT OFF
GO
ALTER DATABASE [SLSADb] SET CURSOR_DEFAULT  GLOBAL
GO
ALTER DATABASE [SLSADb] SET CONCAT_NULL_YIELDS_NULL OFF
GO
ALTER DATABASE [SLSADb] SET NUMERIC_ROUNDABORT OFF
GO
ALTER DATABASE [SLSADb] SET QUOTED_IDENTIFIER OFF
GO
ALTER DATABASE [SLSADb] SET RECURSIVE_TRIGGERS OFF
GO
ALTER DATABASE [SLSADb] SET  ENABLE_BROKER
GO
ALTER DATABASE [SLSADb] SET AUTO_UPDATE_STATISTICS_ASYNC OFF
GO
ALTER DATABASE [SLSADb] SET DATE_CORRELATION_OPTIMIZATION OFF
GO
ALTER DATABASE [SLSADb] SET TRUSTWORTHY OFF
GO
ALTER DATABASE [SLSADb] SET ALLOW_SNAPSHOT_ISOLATION OFF
GO
ALTER DATABASE [SLSADb] SET PARAMETERIZATION SIMPLE
GO
ALTER DATABASE [SLSADb] SET  READ_WRITE
GO
ALTER DATABASE [SLSADb] SET RECOVERY FULL
GO
ALTER DATABASE [SLSADb] SET  MULTI_USER
GO
ALTER DATABASE [SLSADb] SET PAGE_VERIFY CHECKSUM
GO
ALTER DATABASE [SLSADb] SET DB_CHAINING OFF

Create a table Colleague in the SLSADb database.

USE [SLSADb]
GO
/****** Object:  Table [dbo].[Colleague]    Script Date: 11/12/2009 13:22:24 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Colleague](
	[ID] [INT] IDENTITY(1,1) NOT NULL,
	[FirstName] [nvarchar](50) NOT NULL,
	[LastName] [nvarchar](50) NOT NULL,
	[JobTitle] [nvarchar](50) NULL,
	[EmpNumber] [nvarchar](10) NOT NULL,
	[CostCentre] [nvarchar](10) NULL,
	[Department] [nvarchar](50) NOT NULL,
	[LineManagerID] [INT] NULL,
	[PhoneNumber] [nvarchar](25) NULL,
	[MobileNumber] [nvarchar](25) NULL,
 CONSTRAINT [PK_Colleague] PRIMARY KEY CLUSTERED
(
	[ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

Now insert couple of records into the table.

INSERT INTO [SLSADb].[dbo].[Colleague] VALUES ('FName1', 'LastName1', 'Developer Lead', '123456', 'D3455', 'I.T', 2, '012345 678 999', '07712 345 678')
 
INSERT INTO [SLSADb].[dbo].[Colleague] VALUES ('FName2', 'LastName2', 'Manager', '789654', 'D3455', 'I.T', 3, '012345 678 123', '07712 345 111')

Well done. We are half way through. Let me grab some coffee. You go and get one for you, you deserve it.

Common mate, where is SQL Azure. Wait.. it went for a coffee too.

Now back to VS2008. We need some way of talking to our Colleague table. Almost every business app talks to database at somepoint using some technology. Whether it is ADO.net, LINQ2SQL or EntityFramework. Let’s look at how to do this with .NET RIA Services. We will start out in the web project(SLSASampleApp.Web).

For this example I will use an Entity Framework data model, but RIA Services works great with any form of data from plain old CLR objects to an xml file, to web services to Linq to Sql.
Right click on your server project (SLSASampleApp.Web)

select Add -> New Item -> ADO.NET Entity Data Model

Name: SLSASampleApp.edmx

Add Entity framework

Do all the necessary steps with default names and select the colleague table.

Colleagues table

Change the Entity Set name to Colleagues in the properties window. Save and Build the Solution.

These .NET RIA Services model your UI-tier application logic and encapsulate your access to various data sources from traditional relational data to POCO (plain old CLR objects) to cloud services such as Azure, S3, etc via REST, etc. One of the great things about this is that you can move from on premises Sql Server to an Azure hosted data services without having to change any of your UI logic. Simply great.

It is piece of cake to create these .NET RIA services.

Right click on to the server project (SLSASampleApp.Web) Add -> New Item
select the new Domain Service class and name it SLSASampleAppDomainService.cs

Add new Domain Service

It opens up ‘Add New Domain Service class’ window. In the wizard, select your data source. Notice here we could have chosen to use a Linq2Sql class, a POCO class, yada, yada. Check all the checkboxes.

Add new domain service class

In the SLSASampleAppDomainService.cs class we have stubs for all the CRUD method for accessing your data. Just go through this class. Now we are ready to rock.

Build the solution now.

Now, let’s switch the client side (SLSASampleApp). Be sure to build the solution so you can access it from the client directly. These projects are linked because we selected the “ASP.NET enable” in the new project wizard.

Go to Views -> Home.xaml

Drag and drop a DataGrid from the toolbox somewhere near the end of the page. This is a Silverlight XAML control

Go to code behind (Home.xaml.cs)

Now we have to bring the domain service to the client side. So add a using statement on top.

using SLSASampleApp.Web;

By adding this, you can now access the client proxy for the server DomainServices locally.

Copy the below code and paste it in the Home under the IntializeComponent() method.

var context = new SLSASampleAppDomainContext();
colleagueGrid.ItemsSource = context.Colleagues;
context.Load(context.GetColleaguesQuery());

It should exactly look like this.

Code

In line 1, we create our SLSASampleAppDomainContext. This is the client side of the SLSASampleAppDomainService. Think SLSASampleAppDomainContext as a proxy class.

In line 2, we are data binding the grid to the Colleagues.

In line 3 we are loading the Colleagues, that class the GetColleagues () method we defined on the server (go and see SLSASampleAppDomainService.cs). Notice this is all async of course, but we didn’t have to deal with the complexities of the async world.

Hit F5

Mate, Where is SQL Azure? Had she not come from Coffee yet?

The Grand entrance of SQL Azure

Up to now usual stuff, anyone can do this stuff, who knows a bit about Silverlight and RIA.

Now log on to SQL Azure using http://sql.azure.com

Create a database called SLSADb in SQL Azure

SQL Azure

Now you have to create a database in the SQL Azure just like your local sql server schema. For that we need to download a tool called SQL Azure migration Wizard from this place.

http://sqlazuremw.codeplex.com/

download the wizard and install it on your PC.

Run the SQLAzureMW.exe

In ScriptWizard window, Click Connect to Server button and connect to your local SQL server.

Migration Wizard

Oops, I am getting an error here. I think it’s not working for me. If it is working for you then just migrate your local SLSADb to the cloud. It is easy and intutive. If not, ignore this bit and we will do this manully using our old friend SSMS.

Error running Wizard

Do the following steps only when you failed to migrate the local DB to SQL Azure using migration wizard.

Open your SQL server Management Studio

Cancel the intial Connect to Server Dialogue box.

Click on New Query and Connect to Server Dialogue box appears.

Give your SQL Azure details there. See the two screen shots below.

Using SSMS to connect to SQL Azure

Click on Options button on the same screen and give your database name in the connect to database feild. i.e SLSADb (this is the DB that we have created in the SQL Azure as well). Sorry for the confusion, I should have put different database name for the SQLAzure DB.

SSMS options Wizard

Click Connect.

Now copy the above Colleagues SQL table script (not the DB creation Script) and run it in the SQL window with slight modification. See the diagram below.

Colleague table view

Your table creation script should execute fine.

Now issue a select command.

Select * from Colleague

It yields zero records.

Now issue couple of insert statements with different names than in your local database. For your convenience, I created two records, copy, paste and run in SQL Azure.

INSERT INTO [SLSADb].[dbo].[Colleague] VALUES ('Cloud1', 'SQLAzure1', 'Developer Lead', '123456', 'D3455', 'I.T', 2, '012345 678 999', '07712 345 678')
INSERT INTO [SLSADb].[dbo].[Colleague] VALUES ('Cloud2', 'SQLAzure2', 'Manager', '789654', 'D3455', 'I.T', 3, '012345 678 123', '07712 345 111')

Please note we changed our first names and last names in the above.

Now we are ready to show them in our Silverlight application.

Go to VS2008

On the Server project , open the web.config file. In the connectionstring section, you see a long string like this, which is connecting to your local SQL server.

Change this to reflect the SQL Azure storage database with your own credentials

Save the changes and Hit F5.

Upsy daisy. Here we get our stuff from the SQL Azure. See the screen shot.

Final output from SQL Azure

That’s it. In one go, you did both Silverlight RIA stuff and SQL Azure stuff.

I hope you like this article. You can explore SQL Azure and Silver light from here on.

If you have any doubts or questions, please don’t hesitate to ask me.

Cheers,
Narendra