Click here to Skip to main content
Click here to Skip to main content
Technical Blog

Windows Azure, TDS, WCF, Silverlight and a few problems on the way Part I

, 13 May 2009 CPOL
Rate this:
Please Sign up or sign in to vote.
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 Mi

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 gotcha’s – some because Azure is beta, some not. The follow 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 Tables 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. Part II I will discuss the .Net code used to access these procedures.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Paul S. Chapman
Software Developer (Senior) Simplicita Online
United Kingdom United Kingdom
UK based IT Consultant. Started in 1985 selling home computers such as the Sinclair ZX Spectrum, BBC Model B and Commodore 64, and in 1987 moved into development, starting first with Torch Computers, developing software for the XXX UNIX Workstation.
 
Currently developing a new Azure/Silverlight based website/desktop applications for a new startup, hoping to launch in late 2009/early 2010

Comments and Discussions

 
-- There are no messages in this forum --
| Advertise | Privacy | Terms of Use | Mobile
Web03 | 2.8.141223.1 | Last Updated 13 May 2009
Article Copyright 2009 by Paul S. Chapman
Everything else Copyright © CodeProject, 1999-2014
Layout: fixed | fluid