Galin Iliev's blog

Software Architecture & Development

Getting started with Astoria (screenshots story)

Today I will lead you through the process of creating Astoria service. It is pretty easy as you’ll see.  In my previous post I described the required packages to run Astoria.

There are mainly to stages:

  • Create and setup Entity Framework data Model
  • Create Astoria Service

Once you have them you can start Visual Studio 2008 Beta 2

Then select form the menu Project -> Add new Item… and select ADO.NET Entity Data Model

Then we follow the EF wizard to Generate Model from database

We select accordingly the connection to MS SQL Server which has AdventureWorks dabatase.

On next screen we select objects that will be added to EF Schema and click Finish button:

And we have the schema ready to use:

Now we are ready to setup Astoria Service:

Select form the menu Project -> Add new Item…

When we have class generated all we have to done is to set Context Object for EF Model. We enter AdventureWorksModel.AdventureWorksEntities and compile.

And this is all! We are ready to use it. It was pretty easy,  wasn’t it?

Now let’s test it. All we have to do is loading AdventureWorks.svc from the browser:

On this screen we see all entities in the schema. We could navigate further with just changing URL:

You could play with some other Astoria specific queries like those (change your host:port to match yours):

There is one very good tool that you can use . On order to use the page you must host it on same application as Astoria service otherwise browser single origin check will stop the requests. So Save the page and add it to your project

In next post I will show you how to consume the data from Javascript

Astoria CTP Refresh for EF Beta 2

Pablo Castro annonced there is CTP Refresh of Astoria bits. Astoria is very cool project based on .NET 3.0 (WFC), Entity Framework to provide pure HTTP access to SQL Server data. This means that we will be able to pull data using... Javascript 

In order to use you need:

I am preparing a (short) article that shows how to leverage data from Javascript and ASP.NET AJAX... Stay tuned and I will annonce it here

Look under the hood of SQL Server Storage engine

We, software engineers, have interesting profesion, don't we? The result of our job should make life of the customers easier. But there is something else that keeps us in front of the screen. And this is to make things work in our way. Especially when we are able to find out how the internals work we feel like in the heaven :).

Beside the sake of cool it is very useful to know what's in under the hood. Knowing this we could create more optimized products, make things flying...

There is a tool called SQL Internals Viewer that allows look into the SQL Server storage engine and see how data is physically allocated, organised and stored. There is no need to say how much we can benefit form this knowledge.

But hey.. that's not all. There is also Resource section in which you can dive and read about internals...


Today I came across new acronym - WAF (Woman/Wife Acceptance Factor). According Wikipedia it relies on the cliché that men are driven by certain geekiness when it comes to acquisitions like home theater or PCs, disregarding the female aspects of aesthetics, design and practical/financial considerations.

Here is some graph exposing WAF for some new gadjets (borrowed from Scott Hanselman)

What do you think? Do you consider this factor (formally and informally) when buying new toys? :)

Visual Studio 2008 links

Scott Guthrie posted a great compilation of links to his blog regarding Visual Studio 2008 features. Here is the list (cross-posted):


VS 2008:

ASP.NET in .NET 3.5:


New Language Features:

Scott, Thank you for all this educational and interesting info :)

XML in SQL 2005 and SqlCommand

Although XML has good support in SQL 2005 I haven't used it heavily 'till today. I had to optimize an application that uses a table with two XML columns. These columns were of type text and filter operations against them was costly.

Let's say query like this one takes about 2 secs for execute (in table with only 2000 rows):

FROM tableName
WHERE xmlColumn like '<AccountID>23</AccountID>'

Although this is not the best way to acheve it it worked... slowly :) So it worths to invest some time to make the changes.

I know the columns should be converted to type XML but I didn't know if this would speed up the process either how to manipulate xml from T-SQL.

in order to explain better how to work with XML we will use the following XML:

node id="1" value="test1" />
node id="2" value="test2" />

Step 1 - Manipulate with XML from T-SQL

There are some good pages in SQL 2005 Books Online called xml Data Type Methods. They explain well how to work with xml.. So briefly:

in order to get value as result from query you'll need query like this one

SELECT xmlCollumn.query('data(/root/node/@id)[1]')
FROM tableName

In order to get value from XML we need two thigs- XQuery that specifies which nodes to be returned (@id specify the attribute) and in square brackets which value to be returned if there are more than one nodes with same name (as in our case).

For filtering by value within XML this query would help

FROM tableName
WHERE xmlCollumn.value('data(/root/AccountID)[1]', 'int') = 23

Note: By changing type from text to xml and modifying SQL statements I was able to execute same query for 0.0001 secs instead of 2 seconds. Seems like good progress...

Step 2 - Using it from .NET Application

So far so good. Let's use it in .NET Application. This one seems easy

   1:  string strXml="";
   2:  SqlConnection cn = new SqlConnection("connection strin here");
   3:  SqlCommand cmd = new SqlCommand(@"SELECT *
   4:  FROM tableName
   5:  WHERE xmlColumn.value('data(/root/AccountID)[1]', 'int') = 2", cn);
   6:  cn.Open();
   7:  try
   8:  {
   9:      SqlDataReader rd = cmd.ExecuteReader();
  10:      if (rd.Read())
  11:      {
  12:          strXml = rd[1].ToString();
  13:      }
  14:      rd.Close();   
  15:  }
  16:  catch (SqlException ex)
  17:  {
  18:      MessageBox.Show(ex.Message);
  19:  }
  20:  finally
  21:  {
  22:      cn.Close();
  23:  }
  24:   this.txtContent.Text = strXml;

I expected this to be all but no luck... I got funny exception:

SELECT failed because the following SET options have incorrect settings: 'ARITHABORT'. Verify that SET options are correct for use with indexed views and/or indexes on computed columns and/or query notifications and/or xml data type methods.

So including "SET ARITHABORT ON;" above every SQL statement used in the code solved the issue.

Overal speed-up in the application was 19 seconds. I started from 20 secs for performing certain operation and same operation was executed for 1 sec. And this was just with converting columns (and queries) to facilitate XML in SQL side... Seems like a good progress

Hope this helps

Entity Framework for DBAs

There is a lot of noise these days around Visual Studio 2008 and fancy LINQ to SQL and Entity Framework and discussions how they will change the way data driven applications is done.

But somethign was missed 'till now - How this would affect database administrators (DBAs)?  Erick Thompson post a good explanation about these technologies that targets DBA and explain what will be changed on their daily job.

Go and read ADO.NET Blog post - it worths.

Silverlight 1.0 released and support for Linix announced

On September 4th, 2007 9 PM PST Microsoft released to web (RTW) Silverlight 1.0 and announced that they will work together with Novell Inc. to enable silverliht applications on Linux.

ScottGu posted what's in silverlight 1.0 release:

  • Built-in codec support for playing VC-1 and WMV video, and MP3 and WMA audio within a browser.  The VC-1 codec is a big step forward for incorporating media within a web experience - since it supports very efficiently playing high-quality, high definition video in the browser.  It is a standards-based media format that is implemented in all HD-DVD and Blueray DVD players, and is supported by hundreds of millions of mobile devices, XBOX 360s, PlayStation 3s, and Windows Media Centers (enabling you to encode content once and run it on all of these devices + Silverlight unmodified).  It enables you to use a huge library of existing video content and provides access to the broad ecosystem of existing Windows Media tools, components, vendors and hardware. 
  • Silverlight supports the ability to progressively download and play media content from any web-server.  You can point Silverlight at any URL containing video/audio media content, and it will download it and enable you to play it within the browser.  No special server software is required, and Silverlight can work with any web-server (including Apache on Linux).  We'll also be releasing an IIS 7.0 media pack that enables rich bandwidth throttling features that you can enable on your web-server for free.
  • Silverlight also optionally supports built-in media streaming.  This enables you to use a streaming server like Windows Media Server on the backend to efficiently stream video/audio (note: Windows Media Server is a free product that runs on Windows Server).  Streaming brings some significant benefits in that: 1) it can improve the end-user's experience when they seek around in a large video stream, and 2) it can dramatically lower your bandwidth costs. 

  • Silverlight enables you to create rich UI and animations, and blend vector graphics with HTML to create compelling content experiences.  It supports a Javascript programming model to develop these.  One benefit of this is that it makes it really easy to integrate these experiences within AJAX web-pages (since you can write Javascript code to update both the HTML and XAML elements together). 

  • Silverlight makes it easy to build rich video player interactive experiences.  You can blend together its media capabilities with the vector graphic support to create any type of media playing experience you want.  Silverlight includes the ability to "go full screen" to create a completely immersive experience, as well as to overlay menus/content/controls/text directly on top of running video content (allowing you to enable DVD like experiences).  Silverlight also provides the ability to resize running video on the fly without requiring the video stream to be stopped or restarted.

There is another RTW Expression Encoder 1.0 release on the web.  Expression Encoder is part of the Microsoft Expression suite of products, and enables designers and content professionals to enhance, encode and publish media content for Silverlight.  You can use it to import media files from a variety of formats (QuickTime, WMV, AVI and more), add leaders and trailers to videos for advertising or roll credits, easily watermark video with corporate logos or brands, and then tune the encoding settings to create optimal web-friendly Silverlight experiences.

Silverlight is used on several MS sites - MSN Podium '08, Halo 3 HD Preview...

Here are some screenshots: