Wednesday, November 24, 2010

Static Vs Dynamic Vs Embedded Vs Entity SQL

Structured Query Language (SQL) is a way to communicate with a relational database that lets you define, query, modify, and control the data. Using SQL syntax, you can construct a statement that extracts records according to criteria you specify (I know, you know that). However, there are certain flavors of SQL, which you should be aware of like Static SQL, Dynamic SQL and Embedded SQL, to better understand and apply them, as and when required.

With .NET you would also end up using Entity SQL, which when used with Entities (as in the Entity Framework), could eventually generate dynamic SQL, executing and returning results as entities.

Static SQL: The most commonly used type of SQL, static SQL, as its name implies allows you to fulfill your data access requirements, which might be known to you at design time of your application. Having static SQL queries can lead to better performance, since the queries are not required to be parsed every time before being executed.

Dynamic SQL: There are instances of applications where the data to be processed cannot be determined at the design time of the application. A typical example would be processing of a spreadsheet, which in turn can contain variable number of columns, and the program is needed to process and store the data into the database. Typically, you would generate a string value with the columns and send across the database to process it.

The following points are to be considered:

  • Since the database engine doesn’t have an access plan, it would parse, compile and generate an access plan. Hence dynamic SQL is slower than static SQL.
  • EXECUTE IMMEDIATE statement can be used to execute the dynamic SQL statement, which in turn passes the SQL to database for compilation and execution.
  • The problem with EXECUTE IMMEDIATE is that the database would be executing each of the 5 steps of processing SQL statement and the overhead could be significant for multiple dynamic statements executed at once.
  • Dynamic SQL offers optimization via prepared execution whereby all the host variables are replaced by a question mark (?), known as parameter marker, which can be substituted later with the host value. The PREPARE statement is used by the program to pass the SQL statement to the DBMS for parsing, validation and optimizing the statement. The EXECUTE statement is used instead of the EXECUTE IMMEDIATE statement, and the parameter values are supplied via a special data structure called the SQLDA or SQL Data Area. By having the execute statement and supplying different parameter values, optimization is gained over the EXECUTE IMMEDIATE statement.
  • It is to be noted that PREPARED execution is still slower than static SQL since with static SQL the first 4 steps of processing takes place at compile time, whereas with PREPARED execution, they are still executed at runtime, however, only for the first time.

Embedded SQL: Because SQL does not use variables and control-of-flow statements, it is often used as a database sublanguage that can be added to a program written in a conventional programming language, such as C or COBOL. This is a central idea of embedded SQL: placing SQL statements in a program written in a host programming language.

The following points are to be considered:

  • Embedded SQL is processed by a special SQL precompiler.
  • Host language variables can be used in place of the constants in the SQL statements.
  • To optimize the SQL which returns a single row as a result, singleton SELECT statements are used.
  • Statements that return and require multiple rows are processed using CURSORS.
  • Errors while executing Embedded SQL are reported to the application programs via SQL Communications Area or SQLCA.

Disclaimer: The following does not directly relate to databases and are features specific to the Microsoft .NET Framework.

Typically you would be developing an application to access the data stored in the databases. Relational databases provide specific added advantages and are widely used in the general business applications domain. Microsoft .NET 3.5 SP1 introduced Entity Framework, which in turn allows you to model your database objects as entities, which may or may not be inter related, and provides an abstracted way to process them via Entity SQL or LINQ to Entities.

Entity SQL: Entity SQL is a SQL-like language that enables you to query conceptual models in the Entity Framework. Conceptual models represent data as entities and relationships, and Entity SQL allows you to query those entities and relationships in a format that is familiar to those who have used SQL.

The following points are to be considered:

  • Entity SQL supports conceptual model features like relationships and inheritance.
  • Collections are treated as first class citizens in Entity SQL, hence set operators like UNION, INTERSECT and EXCEPT, work on collections.
  • Everything in Entity SQL is an expression which in turn enables it to be more composable than Transact SQL (the SQL language of Microsoft SQL Server).
  • Entity SQL doesn’t support the * construct, and hence the count(*) statement is invalid, instead use count(0).
  • Entity SQL supports a subset of Transact-SQL's built in functions and operators and does not provide support for DDL in the current version.

LINQ to Entities provides Language-Integrated Query (LINQ) support that enables developers to write queries against the Entity Framework conceptual model using Visual Basic or Visual C#. Queries against the Entity Framework are represented by command tree queries, which execute against the object context. LINQ to Entities converts Language-Integrated Queries (LINQ) queries to command tree queries, executes the queries against the Entity Framework, and returns objects that can be used by both the Entity Framework and LINQ. MSDN

The above mentioned constitutes literally all the typical types of SQL related concepts that you might encounter in your general database (relational) programming tasks day-in-day-out or in any developer centric interview session.

As always, you are welcome to share any ideas or thoughts about any other flavor of SQL, which you might have encountered.

Prepare > Execute!

Monday, November 15, 2010

WPF – An Overview

Ever since Windows Vista (sorry Microsoft, for reminding you of that product again) and .NET Framework 3.0 were released, the Windows client developer (the one who is intentionally unaware and exceedingly pleasured by ignoring JS, HTML, DHTML and other useless irrelevant terms in technology) was puzzled with an (frankly) unwanted choice that never existed for him before: continue using the ultimately productive Windows Forms framework, that has been the cornerstone of Windows client applications ever since Visual Basic (classic), or learn, explore and rewrite the apps in the WPF framework for building (what’s now become an overly exploited term in software development) next generation user experiences.

Microsoft has always assured (many consider it a curse) its developers of continued abundance of options and tooling support for all the major line of business applications that one develops. While this has obvious and much desired advantages, the real problem comes in at the start of the adoption phase of the new technology or framework. The earlier releases of the framework don’t (in almost all cases) provide a complete replacement of the previous technology and there is a very steep learning curve involved, with an exceedingly high cost and risk factor in recreating the missing bits, by extending the not so feature complete releases.

My personal approach while dealing with this new-technology-every-PDC kind of releases is fairly simple:

Version Action

Alpha/Beta/CTP or

Just be aware, a new (yet again awesome, silver bullet kind of) technology has arrived!


Seems interesting, lets look at the real benefits.


Use it (OMG, its actually awesome and the silver bullet for everything a MS developer ever needed)

WPF (codename Avalon) was launched with .NET Framework 3.0 and at its time of release, had a substandard support in terms of tooling. Mr. Reader, be informed, that this is not a WPF tutorial series on how to get started with WPF. The attempt is to dive (as deep as possible) into the architecture of WPF and to understand why things are the way they are in WPF.

Windows & Graphics: The primary technologies behind many Windows-based user interfaces, GDI (graphics device interface) and USER subsystems, were introduced with Windows 1.0 in 1985. The next major support for graphics came with OpenGL (created by Silicon Graphics) in early 1990s for doing advanced 2-D and 3-D graphics on both Windows and non-Windows based systems. In 1995, Microsoft introduced DirectX, for providing a new high performance alternative for 2-D graphics, input, sound, communications and eventually 3-D (with DirectX 2 in 1996). With Windows XP, GDI+ was introduced by adding support for alpha blending and gradient brushes, but ended up being slower due to its complexity and lack of hardware acceleration.

With the release of .NET (and the managed world) in 2002, Windows Forms (built on top of GDI+) became the primary way for a C# or Visual Basic developer, to create rich and compelling user interfaces for Windows based systems. Windows Forms has proved itself as a productive and successful technology, but it still suffers from the limitations of GDI+ and USER subsystems, when it comes to graphics, layouts and rendering.

Major Components of WPF

In the adjoining image, the major code for WPF are highlighted in red, and its interesting to note, that out of the three components (PresentationFramework, PresentationCore and MilCore), only MilCore is unmanaged. Milcore is written in unmanaged code in order to enable tight integration with DirectX. All display in WPF is done through the DirectX engine, allowing for efficient hardware and software rendering. WPF also required fine control over memory and execution. The composition engine in milcore is extremely performance sensitive, and required giving up many advantages of the CLR to gain performance.

The Dispatcher:The DispatcherObject acts as a base class for most objects in WPF and encapsulates the basic constructs for concurrency and threading. The Dispatcher is the messaging system of WPF, which acts similar to the Win32 message pump and uses User32 messages for performing cross thread calls.

Dependency Object and richer Property system: In WPF, properties are preferred over methods or events and this constitutes one of the primary philosophies of the WPF architecture. The property system in WPF is based on the DependencyObject which enables tracking of dependency properties and revalidating values when changes occur. Another feature of properties in WPF is the notion of “attached properties”, which enables composition and component reuse, one of the primary goals of WPF. With attached properties, any object can now specify the properties of other objects, enabling tighter composition.

Routed Events: WPF introduces Routed Events, which from an implementation perspective, is an object backed by an instance of the RoutedEvent class and processed by the event system. From a functional perspective, it is a type of event that can invoke handlers on multiple listeners in an element tree, rather than just on the object that raised the event. Since WPF enables richer composition model, it was essential for the event system to be able to “bubble up” events, generally upward through the element tree, until it reaches the root. It is to be noted that in WPF, literally any control can act as a container control, unlike Windows Forms where container controls inherited from a different base class. To enable routed events, or event bubbling in Windows Forms, you would have to attach the same event to multiple elements, while in WPF, you could do that by attaching them to a single element.

XAML (Xml Application Markup Language): The current trend in programming languages (specifically Microsoft technologies) has been towards declarative rather than imperative, and there are underlying benefits to it. XAML typically allows you to create the entire application declaratively, enabling the decoupling of the UI with the logic, supporting unprecedented designer-developer collaboration.
XAML directly represents the instantiation of objects in a specific set of backing types defined in assemblies. This is unlike most other markup languages, which are typically an interpreted language without such a direct tie to a backing type system. XAML enables a workflow where separate parties can work on the UI and the logic of an application, using potentially different tools.” – MSDN

The intent of this post is to provide a starting point in understanding the complexities and terminologies associated with the WPF architecture. I wish and hope, the initial hesitation associated with moving to and understanding WPF, is eased after going through this post. As with any framework, the best way to learn and leverage it still remains to be writing applications using it!