Foo Theory

Investigating the new Spatial Types in SQL Server 2008 - Part 2

bennie — Wed, 08 Oct 2008 19:32:51 GMT

Introduction

In the previous part of this series, we talked about the importance of spatial data in our everyday lives. We talked about vector and raster data, and we explained that the current version of SQL Server 2008 focuses on two-dimensional vector data. Next, we introduced the new geography and geometry data types in SQL server 2008, and we took a quick detour into some important object-oriented principles.

In this article, we start out by taking a look at the class hierarchies for both the geometry and the geography data types in SQL Server 2008. We will focus on the concrete classes in the class diagram which are:

Point.
LineString
Polygon

We will also take a look at the collection classes in the object hierarchy, which allow us to store multiple instances of each type.

Once we have a good grasp on the geometry and geography class hierarchy we will dive into the details of each concrete class. We will start our investigation by looking into the details of the Point and LineString class, and we will write a T-SQL script that highlights the most important features. In the process we will take a look at the three different data formats that can be used to represent spatial data:

The Well-Known-Binary (WKB) format
The Well-Known-Text (WKT) format
The Geography Markup Language (GML) data format.

All of these formats are published as a standard by the Open Geospatial Consortium (OGC), which is the leading standards body for geospatial and location based services.

The Geometry class hierarchy

Overview

A class diagram showing the Geometry class and all of its sub-classes is shown below (note that the Geography class diagram is basically identical, with the Geography class at the root of the tree):

The darker-blue shaded classes are abstract base classes, so they cannot be directly instantiated in the database engine. The light-blue shaded classes are the concrete classes that we can use directly in our T-SQL code:

Point
LineString
Polygon

Each of these single-instance classes has a collection equivalent that can contain multiple instances:

MultiPoint
MultiLineString
MultiPolygon

The GeomCollection class is an additional collection class that can contain a mixture of instances of any type (Points, LineStrings and Polygons).

Supported Data Formats

All of the classes in the geometry and the geography class hierarchy can be represented by three different formats:

The Well-Known Binary (WKB) format. This is a byte-stream (binary) representation for a geography instance. This format is the preferred serialization format for those applications that need to store geospatial information in a compact, self-contained format.
The Well-Known Text (WKT) format. This is a compact, easy to read representation. Because of its user-friendly format it is the most commonly used representation for ad-hoc geospatial queries.
The Geography Markup Language (GML) format. This is an XML-based representation, which is best suited for including geospatial information in an XML document. This format is very useful for those applications that need to exchange geospatial information by means of an XML Web service.

All of the above formats are published as standards by the OGC. In the code samples that follow, we will be showing multiple examples of each format.

The Point Class

Basic Representation

A Point is a zero-dimensional object which represents a single, exact location. It always contains an X and Y coordinate, and can optionally can contain a Z (elevation) value, and a M (measure) value. The measure value is an arbitrary floating point measurement value that you can associate with the Point instance.

The sample T-SQL script (GeometryPoint.sql)illustrates some of the capabilities of the Point class. Throughout all of our demos, we will be working with a very simple table called GeometryDemo, which allows us to associate a Geometry instance with an integer ID:

   1: CREATE TABLE GeometryDemo

   2: (

   3:     ID    INT NOT NULL,

   4:     Value GEOMETRY NOT NULL

   5: );

In the following code sample, we insert a Point instance, and then perform a select to retrieve the point instance in a number of formats:

   1: -- Insert a simple Point with an

   2: -- X and Y coordinate

   3: INSERT INTO GeometryDemo (ID, Value)

   4: VALUES    (1, 'POINT(50 65)');

5:

   6: -- Get the X and Y coordinates and

   7: -- the most relevant methods

   8: SELECT ID, Value.ToString() AS WKT,

   9:            Value.STX AS [X Coordinate],

  10:            Value.STY AS [Y Coordinate],

  11:            Value.STAsBinary() as [Binary Representation],

  12:            Value.STasText() as [Text Representation]

  13: FROM GeometryDemo

  14: WHERE ID = 1;

In lines 3 and 4, we use the WKT format to insert a Point with an X coordinate of 50 and a Y coordinate of 65. The WKT format for a Point simply requires you to to specify the X and Y coordinates. Note that you should NOT use a comma separator between the two, which might seem counterintuitive at first.

In lines 8 through 14 we select the instance that we just inserted. Since Value is a geometry class instance, we need to use the dot (".") notation to access properties and invoke methods on the object:

In line 8 we invoke the ToString() method. This method will return the Well-Known Text (WKT) representation of the instance.
In lines 9 and 10 we invoke the STX and STY properties to get the X and Y coordinates of our Point.
In line 11 we invoke the STAsBinary() method to get the binary representation of the Point.
Finally, in line 12 we invoke the STAsText() method, which will again return the WKT format of the Point.

One important note: although TSQL by itself is not case sensitive, the CLR methods and properties on the spatial data types are, so make sure that you spell the property and method names correctly! For example if you would spell "Value.STAsText()" as "Value.STasText()" you would get the following error message:

   1: Msg 6506, Level 16, State 10, Line 34

   2: Could not find method 'STasText' for type 'Microsoft.SqlServer.Types.SqlGeometry' in assembly 'Microsoft.SqlServer.Types'

The output of the select query is shown below:

From the above output, we see that the WKT representation for the Point is returned by both the ToString() and the STAsText() methods. We also see the binary WKB representation and the X and Y coordinates of the Point instance.

Using the Z and M coordinates

As we mentioned earlier, a Point can optionally contain a Z (Elevation) coordinate and a M (Measurement) value. Note that both the Z and M values should be floats. Below is a T-SQL example:

   1: -- Insert a Point with an X, Y, Z and

   2: -- M coordinate

   3: INSERT INTO GeometryDemo (ID, Value)

   4: VALUES (2, 'POINT(55 70 100.2 50)');

5:

   6: SELECT ID, Value.ToString() AS WKT,

   7:            Value.STX AS [X Coordinate],

   8:            Value.STY AS [Y Coordinate],

   9:            Value.Z AS [Elevation],

  10:            Value.M AS [Measure]

  11: FROM GeometryDemo

  12: WHERE ID = 2;

As you can see, we simply added the Z and M values in the WKT format. The output of the select query is shown below:

OGC-compliant methods and Microsoft Extensions

The attentive observer probably noticed that some method and properties have an ST prefix (such as STX, STY and STAsText()), while other properties and methods do NOT have this prefix (for example: Z, M and ToString()). All methods and properties that are prefixed with ST are OGC-compliant methods, while the other methods and properties are Microsoft extensions to the OGC standard.

In the documentation you will notice that the OGC standard methods and the Microsoft extensions are cleanly separated:

In the above diagram we also see sections for static methods (both OGC and Microsoft extensions). We will take a look at static methods in a later section of this post.

Geography Markup Language (GML)

The only format that we have not worked with so far is the Geographic Markup Language (GML), let's write a query to retrieve both points as GML:

   1: --    Select both Points as GML

   2: --  Note that we are NOT retrieving the Z and M

   3: --  coordinates of the second point

   4: --  because they are NOT OGC compliant!

   5: SELECT ID, Value.AsGml() as [GML]

   6: FROM GeometryDemo;

The AsGml() method can be used to retrieve a geometry instance in GML format.

The output of the query is shown below:

The standard XML namespace for GML will have the URN: http://www.opengis.net/gml.

There is also one important note that we can make from the above output: the Z and M coordinates that we used for the second point are NOT returned in the GML! That is because they are proprietary Microsoft extensions to the OGC standard.

Using the static Point methods

The methods and properties we have used so far were invoked on an instance of the Point class, so we used the "dot" (.) notation:

instanceName.Property

-or-

instanceName.MethodCall(... method arguments here ...)

Instance properties and methods are always tied to a particular object instance, in this case a Point instance. Besides instance methods, the spatial data types in SQL Server 2008 also use static methods and properties. Static methods and properties are tied to the class itself, and can thus be invoked without having an instance around. You use the "::" notation in combination with the class name to invoke a static method or property in T-SQL:

className::Property

-or-

className::MethodCall()

One frequent usage pattern for a static method is the Factory Pattern. The Factory Pattern returns a new instance of the class through a static method. This pattern is used in both geospatial types in SQL Server 2008. Below is a code example from the GeometryPoint.sql T-SQL script:

   1: /* =========================================================================

   2:    ======= Using the Static methods to create a Point Instance =============

   3:    ========================================================================= */

   4: DECLARE @Pnt1 Geometry, @Pnt2 Geometry, @Pnt3 Geometry, @Pnt4 Geometry, @Pnt5 Geometry

   5: DECLARE @xmlSnippet xml;

6:

   7: --    1. Create a Point from an X and Y coordinate and an SRID

   8: SET @Pnt1 = geometry::Point(30, 20, 0);

9:

  10: -- 2. Create a Point using the Parse method

  11: SET @Pnt2 = geometry::Parse('POINT( 90 23 56000 231)');

12:

  13: -- 3. Create a Point using the OGC STPointFromText method

  14: -- Notice the SRID at the end

  15: SET @Pnt3 = geometry::STPointFromText('POINT(20 25)', 0)

16:

  17: -- 4. Create a Point from GML

  18: set @xmlSnippet =

  19:     '<Point xmlns="http://www.opengis.net/gml"> <pos>34 23</pos> </Point>';

  20: SET @Pnt4 = geometry::GeomFromGml(@xmlSnippet, 0);

21:

  22: -- 5. Null Point

  23: SET @Pnt5 = geometry::[Null];

24:

  25: -- Now select all points

  26: SELECT @Pnt1.ToString() AS [From Arguments],

  27:        @Pnt2.ToString() AS [From Parse],

  28:        @Pnt3.ToString() as [From STPointFromText],

  29:        @Pnt4.ToString() AS [From Xml],

  30:        @Pnt5 AS [Null Point];

Following is a discussion of each static method:

In line 8, we use the static geometry::Point() method to create a Point instance by passing in the X, Y, Z and M coordinates.
In line 11, we use the geometry::Parse() method to create a Point instance from Well-Known Text (WKT).
In line 15, we use the geometry::STPointFromText() method to create a Point instance from Well-Known Text. The is the OGC equivalent of the geometry::Parse() method.
In line 18, we have an example of how to create a Point instance from a Geographic Markup Language (GML) snippet, using the static geometry::GeomFromGml() method.
Finally, in line 23 we create a NULL Point instance with the static geometry::[Null] property.

The output of the select statement is shown below:

As we can conclude from the above output, the expected Point instances were created by means of the static methods.

The Spatial Reference Identifier

In the previous code sample, you might have noticed the "0" after the @xmlSnippet parameter:

   1: set @xmlSnippet =

   2:     '<Point xmlns="http://www.opengis.net/gml"> <pos>34 23</pos> </Point>';

   3: SET @Pnt4 = geometry::GeomFromGml(@xmlSnippet, 0);

This argument is the SRID, or the Spatial Reference Identifier. The SRID corresponds to a spatial reference system based upon the specific ellipsoid used for either flat-earth or round-earth mapping. Different projection systems exist, some of you might be familiar with the Mercator Projection that was used to create the maps that were in most classrooms in recent times. A good discussion of map projections can be found at: http://maps.unomaha.edu/Peterson/gis/notes/MapProjCoord.html.

Each projection is uniquely defined by its own SRID. You can use any projection you want, but you need to be aware that when you are comparing spatial instances using any of the methods (such as STIntersection()) you need to make sure that all objects have the same SRID otherwise you will get a run-time error!

That concludes part 2 of this series. In part three, we take a look at the LineString and the Polygon class, and we will use some visualization tools such as GeoQuery, Spatial Viewer, and SQL Server Management Studio's very own "Spatial Tab" to get a visual representation of our spatial data!

Investigating the new Spatial Types in SQL Server 2008 - Part 1

bennie — Sun, 05 Oct 2008 22:32:05 GMT

Series Abstract

In this post we take a look at the new spatial data type support in SQL server 2008. First, I will make a case for why you would want to integrate spatial support in your applications. Next we will take a look at the two core spatial data types in SQL Server: the Geometry and Geography types. We will explain when to use each type, and we will take a look at the main application domains for the two types.

The spatial types are implemented as CLR types in the database engine. Since some database folks might be unfamiliar with CLR types, we will make a brief detour into SQL Server and CLR types. We will talk about the differences between static and instance methods, and explain the TSQL calling notation for each.

Both the Geometry and the Geography types are really just the top-level types of a rich object hierarchy. We will take a detailed look at the different classes in this hierarchy, and we will explore the methods and properties of each class by means of a number of TSQL scripts. A part of this exploration we will take a look at the three data formats that can be used to represent the spatial types: the SQL Server-native Well-Know-Binary (WKB) format, and the OGC standard data types: the Well-Known-Text (WKT) and the Geography Markup Language (GML) . We will use TSQL scripts for each object to illustrate the different notations.

As always, a picture is worth a thousand words, and nowhere this is more the case as for spatial information. Therefore, I will use both the "Spatial Results" tab in SQL server and a number of third-party rendering tools such as SpatialViewer and GeoQuery to present a spatial query result.

After we have a good understanding of the spatial data types, we will put them to some practical use. We will use Virtual Earth to create applications in which we create mashups of Spatial data with a variety of business data. A large volume of GIS data is available on the public domain (some good sources are the US Census and USGS Web sites), but the format of this data is typically not compatible with SQL Server. Therefore, as part of this series we will create a library that will enable us to convert the "traditional" formats into a SQL Server 2008-compatible format.

The above abstract covers a wide variety of formats, therefore I am planing to spread out this article over a number of different posts. This first post will cover the need for spatial support in our applications, the basics of the Geometry and Geography data types and a quick OO primer.

Why Spatial Data?

These days it is hard to find any data that DOES NOT have a spatial aspect. A number of applications attempt to answer questions like the following:

Where are my customers located?
What cities have the highest accident rates?
What Florida counties have the highest flood risk? (OK, that one's easy to answer: "All of them"!)

As GPS devices become more prevalent, more and more data is geo-tagged. For example, a lot of modern mobile phones have both a camera and a GPS chip build in.

While the above applications use spatial data only as part of their overall data set, there are a number of applications that use mapping and spatial data as their primary output:

Consumer products such as Microsoft's Virtual Earth or Google maps.
The government publishes the census results as spatial data.
Utilities use mapping tools such as ESRI Server to plot the layout of electrical grid lines or underground gas lines.

While the above examples are pretty self-evident, spatial data also plays an import role in applications that we might not think about right away:

When a warehousing application generates a pallet "pick run", it will use geospatial information to calculate the most optimal route.
When an interior architect uses a CAD tool such as AutoDesk to perform modeling of an interior space, he/she is using spatial data quite extensively.
Computer-aided manufacturing tools use spatial data to layout out parts on a piece of sheet metal.
The program in a municipal kiosk uses spatial data and geometric algorithms to predict the arrival times of buses and trains.
A multi-player computer game use spatial coordinates to keep track of the current locations of all game participants.

From the above discussion it is clear that a large number of application have a need to work with spatial data. Some common requirements for spatial data support are:

The ability to store spatial coordinates directly in the database, preferably in the same tables as its associated data.
The data types used for this spatial data should go beyond simple point coordinates. Most geospatial data consists out of a mixture of points, lines (both single-segment and multi segment), and closed shapes (typically referred to as polygons in the literature).
The ability to perform a multitude of operations on this geospatial data. For example, a chip design application might want to assure that certain routes on a chip do not cross, other applications will have a need to calculate the area of a complex polygon etc.

In the next section will take a look at how SQL Server 2008 addresses the above requirements.

Spatial Data Support in SQL Server 2008

Types of Spatial Data

At the highest level, we recognize two major classes of spatial data:

Vector Data. Vector data is data expressed by a set of vertices and their relationship to one another. Common spatial features represented by vector data include:
- Points.
- Lines (where a line can have one or more segments)
- Polygons. Polygons are typically used to represent areas and regions.
Raster Data. Raster data is data expressed as a matrix of cells. We typically recognize raster data as images. Within a spatial or GIS context, we see raster data manifested as:
- Satellite images.
- The Virtual Earth bird's eye images.
- Google "street level" images.

An example of each type of spatial data is shown below

Sample Raster Image (a sample satellite heat map)	Sample Vector (in this case a simple line vector)

SQL Server 2008 does focus exclusively on Spatial Vector data.

The SQL Server 2008 Spatial Data Types

SQL Server 2008 introduces two new data types:

Geometry. The geometry data type is based on the Cartesian coordinate system, based upon a "flat earth" representation model. In this model, a point is represented by an X, Y and optionally a Z coordinate. This usage domain of this data type is either:
- The representation of simple coordinates in a two or three dimensional space. A example is the precise location of a pallet in a warehouse.
- The representation of map coordinates, where distances are limited enough so that they are not affected by the round earth model.
Geography. The geography data point can store points, lines, polygon and collections of each of these, using a "round earth" model as opposed to the "flat earth" model used by the Geometry data type. Instead of using X and Y coordinates, the geography data type will use a latitude/longitude combination to represent a single point. Most of the GIS data available on the Web is latitude/longitude based, so the Geography data type is the data type you should used in most of you GIS applications, especially when you are dealing with longer distances where the shape of the earth becomes relevant.

Geometry : Cartesian coordinate system, "flat earth" model	Geography: Latitude/longitude coordinates, "round earth" model

The geometry data type conforms to the Open Geospatial Consortium (OGC) Simple features for SQL specification version 1.1.0. The OGC is a non-profit, voluntary concensus standards organization which is the leading consortium when it comes to the drafting and ratification of standards for geospatial and location based services. One of the standards published by the OGC is the Well-Know-Text specification (WKT) for spatial data types. We will investigate this format in detail in part 2 of the series.

CLR Objects in the Database Engine and the Database Developer

Both the geometry and geography data types are implemented as user-defined types (UDT's) in the database engine. These UDT's are implemented as .NET Common Language Runtime (CLR) types. Before we take a more detailed look how to use these types, it is important that we make sure that the reader is familiar with some basic OO principles. If you are already familiar with object-orientation and .NET types, please feel free to skip ahead to the next part in this series.

The core abstraction in the object-oriented world is the class. A class can be seen as the blueprint for a particular type. From this blueprint, a client can create any number of actual object instances. A class hides away its implementation details behind a set of publicly accessible properties and methods. This principle is called encapsulation or information hiding. For example, a Car class will encapsulate the behavior of an automobile. It might expose methods such as Start, Stop, SlowDown and SpeedUp, and it might provide properties such as CurrentSpeed and DaysTillNextOilChange. The Car class will hide the details of how it executes these methods and exposes these properties, enabling the client to be "blissfully unaware" of the implementation details of the Car class.

Classes can be related to one another. At a high-level we can recognize the following types of relationships:

The "has-a" relationship. For example, a Car class might have a SteeringWheel and a GasTank. This type of relationship is sometimes referred to as a "uses" relationship, for example the Car class "uses" the SteeringWheel class and "uses" the GasTank class.
The "is-a" relationship. To stay with our Car example, a SportsCar is a specialized type of Car, which is specially equipped. It might have a Spoiler, a HighPerformanceEngine etc.. When classes have such a relationship they will use an OOP concept called inheritance to implement this relationship. In an inheritance implementation, the class from which we inherit is called the base class and the more specialized class is called the sub class. The sub class gets all of the functionality of the base class "for free", so it only needs to worry about implementing it own specialized behavior on top of the functionality of the base class. For example, the SportsCar class only implements the additional functionality that makes it a sports car.

An example of a "uses" ("has a") and an inheritance ("is a") relationship is shown in the figure below:

Some base classes only define a general abstract concepts or entities. These types of classes cannot be directly instantiated by the client, since they are not "feature complete". Such a class is called an abstract class The features defined by an abstract class are implemented by a sub class of the abstract class. Such a sub class that can be instantiated is called a concrete class. We will see in the next section that the geometry and geography classes are indeed defined as abstract classes in the database engine.

In the next part of this series, we will take a look at the class diagrams for both the geography and geometry classes, and we'll start writing some TSQL code!

An Introduction to LINQ to XML

bennie — Wed, 17 Sep 2008 15:35:18 GMT

Note: This post is a work in progress... not yet completed...

Overview

When Visual Studio 2008 and the .NET Framework 3.5 shipped in late 2007, the feature that got by far the most attention was Language Integrated Query (LINQ). This article will provide a quick overview of the LINQ technologies, and will then focus in on LINQ to XML, which is a subset of the LINQ technologies.

This article is structured as follows:

The next section will provide a short overview of LINQ in general. We take a look at what the main drivers were behind the creation of LINQ, and we will start looking at some of the specifics of LINQ to XML.
Once we have established a basic understanding of LINQ to XML, we will take a look at its object model. LINQ to XML introduces an easy to use, flattened object model, especially when compared to the XML DOM object model.
Next, we will take a look at what is in my opinion one of the most important features of LINQ to XML, and that is Functional Construction. Functional construction enables us to create an XML document in a very straightforward way. No longer do we need to use the elaborate XML DOM object model and write 100 lines of complex code, just to create a simple XML document that is just a few lines long.
After we have an understanding of how to create an XML document, we take a look at how we can use the features on the new LINQ to XML object model to query, iterate, manipulate and validate an XML document.
To illustrate how easy it is to leverage the LINQ to XML features in a real-world application, we will take a look at an example that performs two-way binding of a rather complex XML document to a WPF tree control.
To round out our discussion, we will take a look at some practical tips and tricks, which enables us to take full advantage of LINQ to XML, and we will also mentioned some common pitfalls, and inform you how to save time not making the same mistakes as your truly ;-)!

Prerequisites

If you want to run the code samples that are associated with this article, you will need the following pre-requisites:

Since we will be using LINQ, you obviously need to have a version of Visual Studio 2008 installed. If you install Visual Studio, you will automatically also install .NET 3.5 and the C# 3.0 compiler.
We will be showing how you can use LINQ to SQL to create XML documents, so you will need to have SQL Server 2005 or the SQL Server 2008 beta installed. If you have the express edition installed, I recommend you also download SQL Server Management Studio express.
We will be using the AdventureWorks sample database to illustrate how we can create an XML document from a SQL Server database, so you should have the latest version of the AdventureWorks database installed. If you currently don't have this database installed, you can navigate to: http://www.codeplex.com/MSFTDBProdSamples/Release/ProjectReleases.aspx?ReleaseId=4004, and download the appropriate version of the AdventureWorks*.msi file, and follow the instructions to install the database on your SQL server.

LINQ Overview

Background - Problem Description

Most of us write business applications to earn our living. All business applications that I ever worked with deal with some type of data, and quite a number of them involved multiple heterogeneous data sources.

In the early days (1980-1990) all of the data for an application was centralized in one file, and the data access capabilities were directly built into the language (I know that at this point, my friend Pete Miller is thinking back nostalgically about his FoxPro and DBASE days... ;-). The upside of this was that data querying and data manipulation were a core part of the programming experience, but the downside was that every platform (DBASE, FoxPro, FileMaker) would integrate these data access features in a completely different way, tailored to the capabilities of the underlying data access tool. As a result, programmers faced a steep learning curve when moving from one platform to another. Another drawback of these first-generation data access systems was scalability. All platforms were file-based, and therefore had a hard time scaling to multiple users and large data sizes.

In response to this and other issues, (1990s through early 2000s) relational database systems (RDBMS) were created. All of these databases used an emerging query and DML language called SQL to access the data, and database programs migrated from being strictly tied to a particular data access system dialect, to using industry-standard SQL. The query and data manipulation features became again external to the core programming languages, and were typically made available through a set of external libraries (db-Library anyone?). This process was accelerated by the creation of ODBC, which provides a standard means of accessing any database that provides an ODBC driver.

In recent years, we have seen the emergence of Object-Relational Mapping (ORM) tools. The main philosophy behind the ORM movement was the fact that designers and programmers are dealing with objects to represent their data, but each time they have to persist or load these objects from a data store, they have to make a paradigm shift back to SQL and the relational model, and the specific details associated with the database access libraries that are being used. An ORM tool, such as NHibernate takes over the responsibility of persisting and loading objects to the database in a transparent fashion.

While both SQL and their associated data access technologies (such as ADO.NET) and/or the newer ORM tools have simplified things significantly, we still have a number of challenges that remain:

Most real-world applications out there deal with other data types besides relational data. Indeed, in this connected world, we access a variety of XML data sources, we connect to RSS feeds, leverage Web Services using the SOAP or REST protocols, access data in Active Directory or some other LDAP-based data store, and so on. Each of these data sources have their own data access paradigms.
When working with "plain old objects", we have to use a very different API to sort, filter, group our otherwise manipulate our objects, as compared to relational data. Wouldn't it be nice if we could have one standard API for these common tasks?
Often we have to perform complex data transformations and/or data shaping. The way in which these transformations are performed is often dependent on the type of the data. For XML we use XSLT, for relational data we use views or complex joins, for objects we use manual code etc. Again, it would be nice to have access to one standard approach for performing transformations.
What if we want to access data from a dynamic language, such as Ruby, IronPython, PowerShell, F# or any of the future DLR-based languages? Will these languages be able to use the same data access tool as the statically-typed .NET languages such as C# or VB.NET?

Microsoft's Answer: LINQ

At the core, LINQ is really a set of constructs, built into the language, which allow us to work with any type of data, be it relational, XML or plain old objects. LINQ is supported in both C# 3.0 and VB 9.0, which are compilers that shipped with Visual Studio 2008. In a way, LINQ brings us "back to the future", making querying and manipulating data a core programming concept again. The main different with the "old school" languages such as FoxPro is that LINQ is fully independent of the type of data that is being accessed (object, relational data, XML, etc.), and the specific implementation of the data source (SQL Server, Oracle etc.).

The LINQ features in the C# 3.0 compiler are built on top of a number of other language enhancements such as:

Anonymous types
Anonymous methods
Type inference
Lambda expressions
Expression trees
Extension methods
Instance and collection initializers
Partial methods

A number of these features were addressed in some of my previous posts. Please refer to the blog archives for more information.

The .NET 3.5 Framework also provides LINQ support through a number of types, available in the System.*.LINQ namespaces. These types provide additional support on top of the compiler features.

A graphical overview of LINQ is shown below:

LINQ to XML

LINQ to XML is portion of LINQ that allows us to:

Construct
Traverse
Manipulate
Query
Search

XML documents and fragments, using the standard LINQ API.

One of the main goals of LINQ to XML was to address the main shortcomings in the W3C XML DOM API, as implemented in the System.Xml.* .NET 2.0 namespaces, with a focus on the following areas:

Simplify XML tree construction with functional construction.
Eliminate document centricity in favor of element centricity.
Simplify naming by eliminating prefixes from the API.
Simplify Node value extraction.

The above topics have always been a hard area to deal with for any programmer working with XML documents. XML DOM code is unnecessary complex and bloated, and often unintentionally obfuscated. For example, it is not easy to imply the structure of the created XML document, when reading XML DOM document creation code.

Other issues that Microsoft wanted to address with LINQ to XML is to allow a developer to quickly move data extracted from a relational model to an XML representation, or from an object graph to an XML document. This current DOM API does not support constructs such as projections inside a XQuery, LINQ to XML provides an elegant solution to this problem.

In the .NET 3.5 framework, LINQ to XML is implemented in the System.Xml.Linq.dll assembly, and exposed through the System.Xml.Linq namespace.

The dependencies of the System.Xml.Linq.dll assembly is show in the figure below:

LINQ to XML Object Model

LINQ to XML was developed with Language-Integrated Query over XML in mind from the onset. It takes advantage of the standard query operators and adds query extensions specific to XML. Just as significant as the Language-Integrated Query capabilities of LINQ to XML is the fact that LINQ represents a consistent query experience across all LINQ enabled APIs and allows us to combine XML queries from other data sources. So, with one query, you can access data from:

Local objects in memory
An XML Data Source
One or more SQL Server data sources

The core classes that make up the LINQ to XML object model are listed below:

As you can conclude from the object model above, the number of classes involved has been dramatically reduced, resulting in a reduced learning curve. Actually I think that the biggest challenge in working with LINQ to XML is to unlearn some of the bad practices that we had to burn into our brain to make the W3C XML DOM work for us.

Some key issues regarding this object model are listed below:

You can now work in a "document free mode" if you would like to do so. In some scenarios, you simply want to create or load some XML, manipulate and query it, and save it back. With the W3C DOM, you would be forced to create an XML document. In LINQ to XML, this is no longer the case. To perform the task listed, you could simply:
- Create XElements directly (without having an XDocument involved at all)
- Manipulate the XElements or XAttributes directly.
- Save the resulting XML tree directly to a writer.
XML names have been greatly simplified. LINQ to XML goes out of its way to make XML names as straightforward as possible. One can say that the complexity of XML names does not originate in namespaces, but from XML prefixes. XML prefixes can be used for reducing the keystrokes required when inputting XML or making XML easier to read, however prefixes are just shortcuts for using the full XML namespace. On input LINQ to XML resolves all prefixes to their corresponding XML Namespace and prefixes are not exposed at all in the programming API. In LINQ to XML, a XName represents a full XML name consisting of an XNamespace and the local name. Developers will usually find it more convenient to use the XNamespace rather than the namespace URI string.
An attribute (modeled by means of the XAttribute class) is no longer a subclass of the node class). It is now simply a XName-value pair, which is what it always should have been.

Functional Construction

Querying

WPF Data Binding

Tips and Tricks

Conclusion

Notes: Make sure how to construct an XML document from a LINQ database query

Creating Application Shortcuts with Google Chrome

bennie — Sun, 07 Sep 2008 18:46:44 GMT

Introduction

Unless you have been living under a rock for the last week, you probably know that Google shipped the first beta-version of it's new browser, named "Google Chrome". I am not planning to start yet another debate here about why I think Google decided at this point that it needed to get in the Web browser business. You can read plenty of interesting comments on various blog sites, for example Paul Thurrot, from the Windows Supersite had some very interesting feedback on the subject in his Windows Weekly Podcast with Leo Laporte.

Most of the features introduced by Chrome have an equivalent in Microsoft's upcoming IE 8 browser (beta 2 just shipped a couple of weeks ago). I do want to highlight one interesting feature of Chrome and that is the ability to create application shortcuts.

What is a Chrome Application Shortcut?

A Chrome Application Shortcut is basically a quick, streamlined way to access a Web Application's functionality in it's own window. You can create a shortcut on either the desktop, the "Start bar", or the "Quick Launch" toolbar. Once the short cut is created, you can simply double-click the shortcut to launch the application in it's own window. Using shortcuts is a nice alternative to launching the full browser window, and entering the URL (either manually or through a "favorites" entry).

Also, if you use Google Gears, you optionally have the ability to access the application's functionality if offline mode (if so desired).

Walkthrough

To create a shortcut, first navigate to the URL of the Web Application. Next, click the Page menu next to the address bar, and select the "Create application shortcuts.." menu item, as is shown below:

In the Google Gears dialog box that comes up, you can select the location(s) for your shortcut. So in my case, I elected to create a shortcut to Gmail, and I opted to have a shortcut on both my desktop and the "Quick Launch" bar.

Note that Chrome will use the appropriate icon for your shortcut.

After creating your shortcut, I can simply double-click the shortcut, to open the selected Web Application as show below:

The application opens up in its own Chrome window, without any toolbar or address bar clutter, as shown below:

Unnecessary Abstractions

donnfelker — Wed, 27 Aug 2008 18:29:24 GMT

Jason Bock had a similar title to this post awhile back … and I found a forehead-slapper today when reviewing some code in a app I’m working on during my free time. I seriously said “What the <ExplativeGoesHere>”.

Here’s the code:

There is absolutely no need to do this. This is exactly WHAT the IPAddress class does. The worst part is that the programmer who developed this knew that the IPAddress Class existed. I’m amazed daily.

Twin Cities Code Camp – Oct 11

donnfelker — Wed, 27 Aug 2008 17:47:05 GMT

I will be presenting at the 5th Twin Cities Code Camp on October 11th.

My session is entitled …

Smooth Operator: Using the Workflow Rules Engine Within WCF

Ever changing business logic requires rule systems that are flexible to business demands. The majority of the time the business requires that these rules systems must be consumable by other applications. Come see how we can utilize the Rules Engine within Windows Workflow Foundation to evaluate our rules and simplify our rules configuration. We will then encompass this functionality within a Windows Communication Foundation Service which will enable consumers to utilize the service.

Visit twincitiescodecamp.com to sign up!

Twin Cities Give Camp Update

donnfelker — Sat, 23 Aug 2008 15:56:56 GMT

The Twin Cities Give Camp site has been updated.

If you would like to volunteer, you can sign up on the volunteer page and if you or your company would like to sponsor the event, you can sign up on the sponsors page.

If you have any questions, please email us from the contact us page.

Side note: I have been having an intermittent problem with Grafffiti loading properly on the site. At times when you visit you will receive the graffiti cms error page. If you try to access the site again, it will go away. If you get an error, please let me know as I’m trying to sort it out with the host. Thanks!

Insane Flight Prices

donnfelker — Fri, 22 Aug 2008 12:11:00 GMT

I was on my favorite travel pricing site today and I was looking up how much flights were from MPLS to Des Moines. Now I'd probably never-ever fly to Des Moines because I believe its only about 3.5 hours away via car. But with gas prices these days you have to check it out. When I used to live in Phoenix, a group of friends and I would always go to Vegas every year. We drove a couple of times but we eventually found out that flying was actually cheaper (back then - 6-7 years ago). A round trip ticket would cost us 80 dollars. Talk about DEAL!!!!

So, wondering if I could score the same for another short flight such as Des Moines would be awesome.

YEAH. RIGHT.

Look at this scree capture:

Seriously. Over $700 dollars for a ticket? YOU GOTTA TO BE CRAZY! - or as the New Yorkers in the Bronx where I lived for awhile would say it "Yo dawg, dat price is banana's!"

Its apparent the airlines no longer have deals (at least in this case). If I do go, I'll be driving. :)

The Twin Cities Give Camp

donnfelker — Wed, 13 Aug 2008 03:13:14 GMT

I’ve presented at code camps and given presentations to technical audiences and institutes and I will continue to do so. I have a passion for giving back to the community that has helped me become the what I am today. I’ve decided that I want to give back to the community even further. After doing some research to find someone (or a group) who might already be setting up a Give Camp I’ve come to the conclusion that they don’t exist here in the Twin Cities.

Therefore, I’ve decided to start up the Twin Cities Give Camp (site not complete). The web site is www.twincitiesgivecamp.org.

I’ll be updating the site this week with all relevant info in regards to the Give Camp. No dates have been set, but as soon as we have one the site will be updated.

What is a Give Camp?

A Give Camp is a weekend-long event where software developers, designers, and database administrators donate their time to create custom software for non-profit organizations. This custom software could be a new web site for the nonprofit organization, a small data-collection application to keep track of members, or a application for the Red Cross that automatically emails a blood donor three months after they’ve donated blood to remind them that they are now eligible to donate again. The only limitation is that the project should be scoped to be able to be completed in a weekend.

…

At the Give Camp, there is an expectation of “What Happens at Give Camp, Stays at Give Camp”. Therefore, all source code must be turned over to the charities at the end of the weekend (developers cannot ask for payment) and the charities are responsible for maintaining the code moving forward (charities cannot expect the developers to maintain the codebase).

Source

More in depth info can be found here: www.givecamp.org

Do you want to be a part of this awesome experience?

We are in need of volunteers, developers, designers, DBA’s, volunteers, Project Managers, Charities, Prizes, Giveaways, sponsors and more volunteers. If you would like to participate or sponsor this event, please let me know. I will update this blog as soon as www.twincitiesgivecamp.org is updated. I will be in contact with the local PMI, IIBA, and ISSA, AITP and local .NET User Groups.

More info to come soon!

DiscountASP vs. Me

donnfelker — Fri, 08 Aug 2008 13:25:00 GMT

DiscountASP just won. Argh! I wrote a long blog post (because I dont have access to Live Writer on this PC) and then clicked Publish and it logged me out. Dont understand what I'm talking about? Read on ..

Unfortunately, what DiscountASP.NET does not tell you is that due to Community Server 2007’s large memory footprint (~100 megabytes with little to no activity), DiscountASP.NET has to recycle the application pool frequently, sometimes as often as every 5 minutes. The DiscountASP.NET policy is to recycle an individual website’s entire pool once it reaches a size of 100 megabytes.

Here are the consequences of being forced to recycle the application pool on a CS 2007 server:

* All logged-in users are logged out;
* any posts that you may have been writing will be lost if you try to press the submit button;
* the entire website is brought to a crawl with slow load times;
* and in essence it makes Community Server 2007 unusable.
Source - http://www.marketing-ninja.com/hosting/discountaspnet-and-community-server-do-not-mix-despite-what-discountaspnet-advertises/

*Shakes fist violently*

Is Premature Optimization Really Evil?

donnfelker — Thu, 24 Jul 2008 04:41:31 GMT

Photo Courtesy of: rosendahl

My first initial answer is … it depends. (Isn’t that the case for everything?) :)

Today I had conversation with a colleague and we were discussing how application developers don’t plan ahead.

This eventually brought up the question/topic:

“Is premature optimization pure evil?”

My colleague stated that all optimization should be done up front, while I on the other hand, disagree. In my opinion all development should be done first and then lets figure out where we might have some problems, with some exceptions (which we both agreed upon).

So, what did we agree upon?

Lets look at a classic example:

Developer Joe and his team were asked to create an order management system that could handle up to 10,000 orders a day. This doesn’t sound like that big of an issue, and Joe and his team also agreed. It shouldn’t be a big issue. A fast database, a fast web server, and a few third party controls to interact with. No problem! Heck, this is pretty straightforward they thought. In reality, the requirements were very straight forward, no hidden features, no scope creep, nothing like that. Heck, it was a dream project come true. With that said - development ensued, and two months later Joe’s team delivered the product and everyone was excited to see the product run from end to end. (By the way, this was not done TDD or with any Agile Methods – which IMO would have caught any of our upcoming issues, if the tests were written correctly. But then again “If ‘if’ was a fifth then we’d all be drunk”).

Joe’s team fired up the application for a demo and ran some orders through it and it worked great. They even ran 10 orders through the system in one minute (~14,400 Orders a day). They had gone above and beyond the call of duty (original requirement being 10,000 orders a day)! They were due for a raise! Life couldn't be better! The system was released and the company started to use it. Orders were flowing through the system without failure and the business was happy.

Until the company decided to sell an exclusive hot item two weeks later …

Then SHTF… All of a sudden, customers were experiencing time outs, orders were being duplicated, and the customer service line was being rung off the hook. Essentially, all hell broke loose. After looking into the system, the developers noticed that the system had only accepted 6,000 orders. What gives? This system should handle 14,400 according to their tests. *everyone is scratching their head at this point*

Scalability is Relative

Scalability in this case, is relative. While looking at the stats, Joe’s team realized that those 6000 orders came in in about 1.5 hours. This was the first 1.5 hours that this product was available. At the end of the day the company eventually sold under 10,000 units, so why did this issue happen?

Lets take a look at some stats:

	Orders Per Minute	Time Between Orders (seconds)
Original Spec (10,000 Per day)	~7	8.64
Current State (6000 in 90 Minutes)	~67	0.9

Orders Per Minute Equation: Orders / Minutes Minutes: (HoursInDay * MinutesInHour)

Orders Per Second Equation: Seconds / Orders Seconds: (HoursInDay * MinutesInHour * SecondsInMinute)

Do we see the problem here?

Again, scalability is relative. In this case, analysis and optimization was not done ahead of the time to anticipate the flood of orders at a peak period. The spec was straight forward: The system is to be designed to handle an order rate of 10,000 orders a day. The system was designed exactly to the spec, and in this case, the system was designed incorrectly.

Now, I have a confession… 6 years ago, I was Joe. Yes, I made this very mistake. I was a hero for a couple of weeks, then SHTF. :) Live and learn. Live and learn.

When We Agreed

My colleague and I both agreed that in certain instances premature optimization should be done, such as this case. The developers should optimize the order process to handle issues at peak times. Perhaps implementing a order service, based on MQ, or implementing a service bus for this type of high-availability process would be the best. In this case, we agree. All of this analysis and optimization should be done up front because this type of thing WILL be a problem at any peak utilization times. We both agree that neglecting to analyze the problem domain can and will lead to performance problems down the road. Plain and simple.

When We Disagreed

My colleague and I still disagree on the statement of “all code should be optimized up front”. In my opinion this is a BDUF approach that I tend to shy away from. I don’t necessary mean that no design should happen up front, but I do think it should be limited. In an agile environment things tend to change, and we need to be able to adapt quickly. Wasting months in front of a whiteboard, with of reams of documentation, and endless meetings only slows the process down if something has to change down the road. Re-updating project baselines, and adjusting documentation and so forth is a huge hassle. I would like to state that there is a place for this type of development though. Some companies do require BDUF approaches because of huge regulatory compliance issues, so sometimes there is not much you can do about it. However process driven approaches such as ITIL and Agile can work together if done properly. With that said, I tend to shy away from BDUF approaches because of a simple example that is also exemplified by Jeff Atwood in a previous post of his (and I agree with him and the many other experts in the field). Jeff adds a third rule to M.A. Jacksons Optimization Rules that I like agree with the most:

And I would add a third: don't optimize work that doesn't have to be done. - Jeff Atwood

This can be demonstrated as a sort routine. Lets say you have a menu that will be populated via user selections and the maximum number of values you can have in the menu is 100. You can either implement IComparable and utilize the List<T>.Sort() method to sort the class (which utilizes the Quicksort Algorithm) which would be SUPER easy but may not be the “Fastest” method of sorting, or you could write an extension method to create your own sort routine to sort the 100 items. At this point we’re splitting hairs. A millisecond, or even 10 milliseconds, wont matter at this point in UI response. We’re only sorting a menu that will never grow beyond 100 items. At this point, pre-mature optimization is evil. There is NO need to rewrite something that that does the job well enough. Can we do it better? Yes! Is there a need to? NO. We wont get any value out of it. The time spend on development is wasted compared to the benefit to the application.

On the other hand, if the menu had no upper bound limit, then we would want to implement the best sort routine we could because some routines are exponentially faster than others. In this case we would want to optimize early because the possibility of a bottleneck can and might occur in certain situations.

It all boils down to: “It Depends”

At the end of the day, the true answer to “Is Premature optimization evil?” is “It Depends”.

It depends on the problem. It depends on the architecture and design. It depends on analysis of the domain. It depends on the factors which “could” come into play.

Finally, what it really boils down to is: It depends on MONEY. We can optimize all day and gobs of the companies money in development/optimization costs. This is why deep analysis into the problems need to take place. As architects we need to define the business process that could impact our architecture design and implementation and development of code. At some point we need the business to interject into this analysis and optimization party to say “Listen, I’m willing to deal with X, but I’m not willing to deal with Y”. At that point, our jobs as Architects is to define how much X costs compared to how much Y costs. Only then will the business truly know what should be done juxtaposed with what should not be done.

To sum it up in my own personal opinion, premature optimization can be evil. We, as architects and developers need to think about the problem at hand before we write a line of code. As I stated above, there is no need for a full 90 page document on the process, but at least whiteboard with your colleagues. Are you a solo developer and need someone to bounce ideas off of? Join your local user group, join some groups online, visit the MSDN architecture forum and ask questions.