Windows Azure, TDS, WCF, Silverlight and a Few Problems on the Way - Part 1

If you have had a chance to look at the downloadable videos from MIX 09 (http://videos.visitmix.com/MIX09/All), then you will have heard that it will soon be possible to develop Azure hosted applications and have good old Transact SQL as a backend! It was in Nigel Ellis’ session on ‘What's New in Microsoft SQL Data Services’ which gives all the details.

However as of the time of writing this functionality, while due in the next couple of months, is not yet available. All is not lost. I am going to use the local Azure development environment to host an application and point it at my local SQL Server instance until such time that I can host it online.

While writing this application, there were a number of gotchas – some because Azure is beta, some not. The following is how I wrote a small application to show news items, that constantly updates. The backend is a SQL database undated on a regular basis by an Azure Worker Role, then read by a Web Service called from a Silverlight application, itself hosted on an Azure WebRole.

In developing this application, which I will be adapting for a much larger online one I am working on, I will work from the backend to the Silverlight application which makes up the client end.

Creating Our Database

According to the SQL Data Services session access to the database; either locally or from SQL Data Services will be via SQL Management Studio. Once the a database has been created, it is time to create the tables that will store the details of our new stories.

This project is actually part of a much larger one I am creating. For this reason, I want to logically separate parts of the database using schemas. This for example, will allow one database to have two tables called ledger – one in a schema called ‘Sales’ and one called ‘Purchases’. You will notice that all of the database objects created in the following examples of T/SQL are preceded by the word ‘NewsMashup’. To create this schema, execute the following code against the SQL database:

CREATE SCHEMA [NewsMashup] AUTHORIZATION [dbo]

A table to store the sites from which feeds will be taken – this allows us to store a hyperlink to the parent site.

One to store the details of feeds from which stories will be taken:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [NewsMashup].[Feeds](
	[Id] [bigint] IDENTITY(1,1) NOT NULL,
	[Url] [varchar](max) COLLATE Latin1_General_CI_AS NOT NULL,
	[Supplier] [bigint] NULL,
 CONSTRAINT [PK_NewsFeeds] PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [NewsMashup].[Feeds]  WITH CHECK ADD  CONSTRAINT [FK_NewsFeeds_Supplier] FOREIGN KEY([Supplier])
REFERENCES [NewsMashup].[Supplier] ([Id])
GO
ALTER TABLE [NewsMashup].[Feeds] CHECK CONSTRAINT [FK_NewsFeeds_Supplier]

One table to store Stories as they are picked up:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [NewsMashup].[Stories](
	[Id] [bigint] NOT NULL,
	[Headline] [varchar](max) COLLATE Latin1_General_CI_AS NOT NULL,
	[Description] [varchar](max) COLLATE Latin1_General_CI_AS NOT NULL,
	[Url] [varchar](max) COLLATE Latin1_General_CI_AS NOT NULL,
	[Supplier] [bigint] NOT NULL
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF

Creating Stored Procedures to Access and Write Data

In placing all database queries in a stored procedure, you gain a number of advantages. Stored Procedures can be faster than inline code, partly this is because the database is able to partly compile and optimise your query. With inline code, this happens at execution time. For the purposes of an application to display news headlines, there are four stored procedures; the first to Create our Stories:

ALTER Procedure [NewsMashup].[CreateStory]
	@Headline		VarChar(Max),
	@Description	VarChar(Max),
	@Url			VarChar(Max),
	@Supplier		BigInt,
	@PubDateValue	BigInt
As
Begin
	Declare @Counter		BigInt
	
	Set @Counter = (Select Count(*) As PreviousCounter From NewsMashup.Stories 
					Where Url = @Url)

	If @Counter = 0 
	Begin
		INSERT	INTO NewsMashup.Stories
			(Headline, Description, Url, Supplier, Id)
		VALUES	(@Headline,@Description,@Url,@Supplier,@PubDateValue)
	End 
End

It’s fairly simple, this function. We first check to see if the link has already been added to the database. If not, then the story is added. The next two procedures are both involved in retrieving headlines; we need two – one is used to retrieve the latest 10 stories from the database, the other retrieves any subsequent stories that may have been added to the database. This allows new stories to be added to the display as and when they are picked up.

Retrieving the most recent 10 headlines:

ALTER PROCEDURE [NewsMashup].[RetrieveHeadlines]
AS
BEGIN
	
	SET NOCOUNT ON;
	
	SELECT     TOP (10) 	NewsMashup.Stories.Id, 
				NewsMashup.Stories.Headline, 
				NewsMashup.Stories.Description, 
				NewsMashup.Stories.Url, 
				NewsMashup.Supplier.HasDescription, 
		           	NewsMashup.Supplier.Name AS Supplier
	FROM       NewsMashup.Stories INNER JOIN
		  NewsMashup.Supplier ON NewsMashup.Stories.Supplier = NewsMashup.Supplier.Id
	ORDER BY   NewsMashup.Stories.Id DESC
END

Retrieving the latest new headlines:

ALTER PROCEDURE [NewsMashup].[RetrieveHeadlines2]
	@LatestHeadline	BigInt
AS
BEGIN

	SET NOCOUNT ON;

	SELECT     Id, Headline, Description, Url, Supplier
	FROM         NewsMashup.Stories
	WHERE     (Id > @LatestHeadline)
	ORDER BY Id 
END

The final stored procedure retrieves the list of feeds from which headlines will be extracted.

ALTER Procedure [NewsMashup].[RetrieveNewsFeeds]
As
Begin
	Select		NewsMashup.Supplier.Id			As Supplier,
				NewsMashup.Supplier.Name, 
				NewsMashup.Supplier.Url		As HomePage,
				NewsMashup.Feeds.Url
	From		NewsMashup.Feeds 
			INNER JOIN NewsMashup.Supplier ON 
				NewsMashup.Feeds.Supplier = NewsMashup.Supplier.Id
	Order By	NewsMashup.Supplier.Name
End

That just about creates the database structures required to stored the News Headlines picked up by the application. In Part II, I will discuss the .NET code used to access these procedures.