Well perhaps I spoke too soon. Maybe there is actually some work being done to bring database programming into this century. Never underestimate the power of the Microsoft Research brain-trust...
I've just been reading about a new C#-derived language, currently dubbed C Omega, (developed by Microsoft Research) that provides native support for SQL and XML right in the C# language, providing a far more natural integration of database programming concepts with modern languages. Read on because that's just the very tip of the iceberg. Here's my understanding from what I read (disclaimer: I'm only going on what I read and could be wrong). I'm paraphrasing from this article which you can read to get more details.
A Quick Summary of C Omega
C Omega introduces some radical changes to the C# language. Most notably are the changes to the type system to provide more alignment between XML schema and classes.
Anyone who understands what XSD is really trying to achieve will understand that trying to tie together schema and classes is impossible because the two are dramatically different, and are in fact trying to achieve different goals. Classes are just lists of fields, properties and methods. XSD, on the other hand, describes documents of data: Hierarchical streams of elements, sub-elements, attributes where you can define in quite some detail what constitutes a valid representation of that document. In essence, classes and documents are completely different beasts.
I'm sure I'm not alone in thinking that this division between real data out there and the data you can program against, which is inherent in developing enterprise applications today, wastes hours of work each day where we're maintaining the plumbing that goes between the two layers.
So in steps C Omega.
C Omega introduces several radically new concepts that allow you to interact with complex data structures as if they were first class citizens of the language. Based on what I've learnt so far about the language, here's a summary of those features:
1. Streams. A stream is an ordered, homogenous collection of zero or more items. Unlike arrays they cannot be accessed randomly, only through iteration constructs such as foreach (and query-operators which I'll cover in a bit). Also streams can be combined easily, where items from one stream are appended to another.
A stream is specified by decorating the type with an asterix. For example:
public string* Names;
..would declare a field 'Names' as a a stream of strings.
When would you use streams rather than arrays? I think streams can be seen as a type of array, but with added functionality such as the ability to query the data, and access data in a scalable way. I say scalable because the stream might only formed as it's being accessed, meaning that some of the data could easily be held on disk while you're iterating the stream.
2. Apply-To-All-Expressions. Lets call these ATAs. These are interesting, they let you run a statement on every item in the stream without having to write a loop. You simply put the statement in curly braces and place it like you were accessing a member. You can use a special "it" keyword to refer to the element of the iteration. For example, if you wanted to write the length of every entry of Names, you could write something like "Names.{ Console.WriteLine(it.Length); }".
3. Choice types. These are a bit like unions in C++. They let you specify that a particular slot in a struct or class can be any ONE of the declared fields. This is defined using the "choice" keyword. Take this example
public class WeatherForecastDay
{
public double Temperature;
public eWeatherType WeatherType;
choice
{
public int SnowAccumulation;
public int RainInches;
}
}
This makes use of nullable value types. Basically, when you set SnowAccumulation you're implicitly setting RainInches to null, and vice versa. This lets you only have one of these fields set at any one time.
Choices are used heavily in XML and XSD to specify lists of elements where only one element should be defined, and so this construct helps to provide better integration between XML and C#.
4. Nullable Types. The next version of C# (2.0) supports nullable value types. It does this using generics (which basically expand to become a flag along with the original data type field, the flag indicating whether the value is really null). C Omega also introduces nullable types, but they are implemented natively in the language and not as generics. In essence the difference is that accessing a value in C# 2 that is null will result in a NullReferenceException being thrown. Accessing a value in C Omega that is null will result in the expression being evaluated to null, and that's perfectly valid for even expressions that would normally result in a numeric value.
To declare a variable that is nullable you use decorate the type with '?', for example:
int? Age;
This defaults Age to null, lets you assign numbers to Age, and also lets you assign null to Age at any time. You can also check Age for null in expressions. No boxing occurs when you do this, it just sets a flag internally to indicate it's null.
5. Anonymous Structs. C Omega also supports the concept of inlining a struct definition, along with values. An anonymous struct is quite different to a regular C# struct:
1. The definition (including initializing it with values) can be inlined with your code and does not need a name.
2. Fields in the structure can be accessed with the array index operator.
3. Fields within the anonymous struct can be anonymous themselves: they do not need a name, instead they can just be a type. (clearly you'll need to use the struct as a stream or use the array index operator to access the field in this case).
4. Not only do fields not need a name, they can share names: Two or more fields can have the same name.
5. Anonymous structs with similar structure (same field types, same order) are compatible and can be assigned to one another, or passed between functions as if they were of the same type.
As you can tell, anonymous structs share many of the same properties as XML documents.
The last point (5) is particularly important, as it bridges a huge gap between loosely-coupled typing and the strict type system that we have in C# and other common languages today. It solves versioning problems. It lets us bring together an excellent programming language and platform, with real enterprise systems and heterogeneous data sources. It's a big deal.
6. Query Operators: SQL and XPath in C#
It seems that streams are primarily accessed through queries, and so C Omega supports querying as a part of its language with new keywords and operators. There's two types of query support in C Omega: XPath style queries and SQL style queries.
XPath queries let you query streams a lot like you'd query XML using XPath. For example, the asterix operator lets you query items in a stream for a specific type. You can use expressions like portfolio.Equity.* to retrieve all objects in the portfolio stream of type Equity. You can also apply filters to queries, by appending an expression at the end, contained in square brackets. The expression is applied to each item of the range, only those that match are included. You can use the 'it' operator in the expression to refer to the object of that particular iteration. For example, portfolio.Equity.*[it.Price>100] would return all equities in the portfolio that have a price greater than 100.
And then there's SQL Queries. Yes, a lot of the SQL language has been built right into C Omega, and seems to integrate directly with streams. The SQL can be used like any other C# expression, and will fail with compile time errors if the SQL expressions contain any badly named columns or other errors. No more waiting till runtime to find out your SQL is broken.
A picture paints a thousand words, and snippet of code paints a few hundred at least, so let's have a look at how it's used:
// define an anonymous struct
struct
{
string Stock;
double Price;
long Quantity;
} * orders;
orders = select Stock, Price, Quantity from OrderStream order by Stock;
orders.{ Console.WriteLine("Stock=" + it.Stock + " Price=" + it.Price + " Quantity=" + it.Quantity); };
Notice the SQL code is embedded in a regular C# assignment. The select operator accesses a stream source called OrderStream. This can be a stream you had defined previously, or can apparently be an ADO.Net data source (which opens up a ton of possibilities). Also notice how there's no strict typing here: as long as the type of the item returned in the stream of the select is schema-compatible (ie. has the same member types in that order) then it is assignable to the "orders" stream. Also note how we're using ATAs to write out each row of the table we're selecting, so this could output even thousands of lines.
The document explains that not all of SQL is supported, but from what I've read it does cover quite a lot, including insert, update, delete and inner/outer joins, along with where, distinct, order by, group by and top clauses.
It also appears (although I'm not 100% sure) that you can join between multiple streams of different sources (eg. join a stream you define with data from a SQL Server).
7. Transactions
A natural progression from integrating SQL into the C Omega language is to incorporate transactions natively. C Omega does this with the addition of the transact, commit and rollback keywords that look a little like exception blocks. The transact block scopes statements into a specified transaction. This is then followed by a commit and/or rollback block, which will be invoked only on a commit or rollback respectively.
Here's an example of how it might look:
transact (myDB)
{
delete from myDB.Orders where stock == "SUNW";
}
commit
{
Console.WriteLine("That felt good.");
}
rollback
{
tryAgain = true; // aborted
}
Whether this allows you to write your own resources that can participate in transactions, the document doesn't say. But I imagine that would be the case.
8. XML Literals Integration
C Omega can construct objects using the XML syntax, that's XML right embedded in your C Omega code. Consider, for example, this class:
class Customer
{
public string Name;
public int Age;
public double Height;
}
You could then instantiate the class using the following syntax:
Customer newCustomer =
<Customer>
<Name>Bob</Name>
<Age>29</Age>
<Height>5.7</Height>
</Customer>;
In order to qualify how members relate to XML constructs, the "attribute" keyword can be used in the declaration of that member to signify that the member will relate to an attribute in XML rather than an element.
Conclusion
So this represents some great progress on the front of integrating solutions to real enterprise problems right into the language semantics of C#. I think it holds the potential to revolutionize database programming.
Taking this one step further, just imagine if you could work with the new System.Data.SqlServer namespace, right in the SQL Server process, working directly with the data just like you would in stored procedures, and returning this using remoting. Combining the power of C Omega, .Net and SQL Server's database engine, this could be the answer I was looking for and a huge leap forward for enterprise development. Could it mean the end of stored procedures?
Either way, lets hope these features make it into C# soon.