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;
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
and finally one table to store the sites from which the feeds will be taken
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
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.
CREATE SCHEMA [NewsMashup] AUTHORIZATION [dbo]
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 libraries used to access these procedures.