I’ve had Bill Reiss’ Silverlight Games 101 blog in my Google reader for a while now, and recently he’s started a new series started on developing the Asteroids-like “Space Rocks” game under Silverlight 3. Between part 3 and part 4 was a post promoting caching dependency property values for performance. I’m not a WPF or Silverlight guru so I can’t really argue with the statement; but it does make sense in the same way that caching static data in a line of business application does.

In part 4 we see this in action, in properties on the Sprite class:

        public double X
        {
            get
            {
                return x;
            }
            set
            {
                if (x != value)
                {
                    x = value;
                    this.SetValue(Canvas.LeftProperty, x - dw);
                }
            }
        }

        public double Y
        {
            get
            {
                return y;
            }
            set
            {
                if (y != value)
                {
                    y = value;
                   this.SetValue(Canvas.TopProperty, y - dh);
                }
            }
        }

I don’t like the duplication of the caching inside of the class itself, especially when dependency properties are likely to be used quite heavily in a game, which is going to be doing things like adjusting position of visual game elements every rendered frame. Surely, this is unnecessary duplication and we can do better? Behold, the DependencyPropertyCache:

using System;
using System.Net;
using System.Windows;
using System.Windows.Controls;
using System.Windows.Documents;
using System.Windows.Ink;
using System.Windows.Input;
using System.Windows.Media;
using System.Windows.Media.Animation;
using System.Windows.Shapes;

namespace SpaceRocks
{
    public class DependencyPropertyCache<T>
    {
        private DependencyProperty _dependencyProperty;
        private DependencyObject _parent;

        public DependencyPropertyCache(DependencyObject parent, DependencyProperty property)
        {
            if (parent == null)
                throw new ArgumentNullException("parent");
            if (property == null)
                throw new ArgumentNullException("property");

            _parent = parent;
            _dependencyProperty = property;
        }

        public T Value { get; private set; }

        public void SetValue(T value)
        {
            if (!Value.Equals(value))
            {
                _parent.SetValue(_dependencyProperty, value);
                this.Value = value;
            }
        }
    }
}

Pretty simple, really. Now we can have cached values exposed by the Value property and update the value using SetValue which does a comparison between old and new, and only updates the actual dependency property if the new value is different. Bill was using the inequality operator and this class is using the Equals method on System.Object, however it wouldn’t be very difficult to modify the cache to constrain T to implementing IComparable<T>, as System.Double does, and/or a constructor overload which might take a custom IComparable<T>. So far in the series, there are only System.Double properties, so I’ve not done that yet.

So now the modified code for the Sprite class from part 4 looks like this: 

using System.Windows.Controls;
using Microsoft.Xna.Framework;
using System.Windows;
using System.Windows.Media;

namespace SpaceRocks
{
    public class Sprite : Control
    {
        double dw = 0;
        double dh = 0; 
        static Vector2 gameSize = new Vector2(640, 480);
        private DependencyPropertyCache<double> _x;
        private DependencyPropertyCache<double> _y;
 
        public Sprite()
        {
            this.DefaultStyleKey = typeof(Sprite);
            this.SizeChanged += new SizeChangedEventHandler(Sprite_SizeChanged);
            _x = new DependencyPropertyCache<double>(this, Canvas.LeftProperty);
            _y = new DependencyPropertyCache<double>(this, Canvas.TopProperty);
        }

        void Sprite_SizeChanged(object sender, SizeChangedEventArgs e)
        {
            Vector2 pos = Position;           
            
            _x.SetValue(double.NaN);
            _y.SetValue(double.NaN);

            Position = pos;
        }

        public override void OnApplyTemplate()
        {
            base.OnApplyTemplate();
            FrameworkElement element = VisualTreeHelper.GetChild(this, 0) as FrameworkElement;
            dw = element.Width / 2;
            dh = element.Height / 2;
            Width = element.Width;
            Height = element.Height;
        }

        public double X
        {
            get
            {
                return _x.Value;
            }
            set
            {
                _x.SetValue(value - dw);                
            }
        }

        public double Y
        {
            get
            {
                return _y.Value;
            }
            set
            {
                _y.SetValue(value - dh);                
            }
        }

        public Vector2 Position
        {
            get
            {
                return new Vector2( (float)_x.Value, (float)_y.Value);
            }
            set
            {
                _x.SetValue(value.X);
                _y.SetValue(value.Y);
            }
        }

        public Vector2 Velocity { get; set; }

        public void Update(double elapsedSeconds)
        {
            Position += Velocity * (float)elapsedSeconds;
            if (Position.X > gameSize.X) Position -= new Vector2(gameSize.X, 0);
            if (Position.X < 0) Position += new Vector2(gameSize.X, 0);
            if (Position.Y > gameSize.Y) Position -= new Vector2(0, gameSize.Y);
            if (Position.Y < 0) Position += new Vector2(0, gameSize.Y);
        }
    }
}

Sweet.

I finally got around to playing with the bits for the Azure SDK, which as of this writing is currently in May 2009 CTP mode. Here are some thoughts.

Pretty good local development experience

Develop against a local cloud, deploy to the real one. The “role” stuff related to the project types in Visual Studio is a little odd at first but once you get past it, it’s the normal Asp.Net development experience we’ve had for a long time now, at least until you want to use the Azure blob/queue/table-storage services.

Hosting services

Literally, File –> New Asp.Net Web Application –> Publish –> (Upload via Azure portal) –> done. Definitely was not expecting this to be so straight forward. Should be really easy to migrate existing Asp.Net sites to the cloud, providing they comply with Azure’s security posture.

There’s a real over-reliance on the StorageClient sample.

Every sample or piece of code for demonstrating developing against Azure is using the StorageClient library, which is one of the sample projects that ships with the Azure SDK. There are numerous posts out there saying it is not to be considered an official API, and that the REST APIs are the real deal. That’s fantastic, but I don’t think any developer is interesting in writing a bunch of HttpWebRequest-heavy code to use Azure or worse, build out their own end to end wrapper to the REST interfaces. Sure, if no official libraries are published, some usable ones will surely pop up on CodePlex but I don’t see how Microsoft would even consider not providing an official .Net API, given what it could potentially do related to adoption of their platform. Microsoft has historically been very pro-RAD in its development platform and tooling, and this is contrary to that, which is puzzling. I mean, one might half-expect to see a TableStorageDataSource control for WebForms. Instead, it sounds like there will be nothing but some docs on MSDN describing the REST calls.

Table Storage != table storage.

Table Storage has a strong connotation for “relational”, and Azure’s Table Storage is anything but. It really should be called Structured Blob Storage, because that’s basically what it is, storing a structured property bag instead of an unstructured blob of ones and zeros. Trying to develop a simple “Post has many Comments” example lead me down a path where I:

1. Chose Guids as object-keys. Since this is off-premises and not bound to a strict schema, there is no options for auto-incremented properties. You either handle it yourself at the application level or find a better way to handle uniqueness among your entities. Guid.NewGuid() to the rescue.
2. Realized I either don’t “get” Table Storage and/or I’m doing it wrong. The LINQ capabilities of table storage extend to 4 LINQ operators.  And of those, only the most basic ones. Better support for Take, combined with support for Skip and OrderBy/OrderByDescending would at least provide the foundation of something usable from a querying perspective. I get that it’s not relational, so joining and grouping are probably overkill, but not being able to get the second set of 50 posts sorted by publish date, without fetching all of them and doing it in-memory? Come on. Taking the effort to develop against table storage to leverage the scalability and availability does not make sense when you have to fetch a whole ton of entities only to do a bunch of manipulation in memory in order to send just the right ones back to the client/browser.

Because of the querying limitations, the only way I think I would use Table Storage in an Azure-centric application with non-trivial storage requirements would be to use it as a structured cache for highly-requested or relatively static data. The rest would come from or something else entirely, possibly Sql Azure, formerly Sql Data Services. Of course, Sql Azure isn’t available in any sort of CTP or preview, so the jury is out on that one.

Blob storage

This is the strongest part of the Azure platform as I see it today. Even with the over-reliance on the StorageClient sample, shoving files up to Azure to get reliable, scalable file hosting is one area I think will really shine.

No development support for Windows XP

This is kind of lame, in my opinion. But it will sting less when Windows 7 is out as I suspect a ton of people will be moving to that directly from XP.

Overall, I really do like the platform from a development standpoint. Looking forward to what comes out surrounding it during PDC later this year.

Of all the controls in the Asp.Net WebForms family I think I like the ObjectDataSource the most. It’s a shiny beacon of hope in a framework otherwise bogged down by the perils of ViewState, heavy and un-testable infrastructure classes, and much to be desired in terms of abstractions. I know everyone else is all about the MVC angle bracket soup these days but for those (stuck or by choice) in WebForms land, ODS is fantastic. What it allows you to do, in short, is to make often awkward data-binding your bitch. Let’s see how.

The essence of all data source controls is allowing you, the developer, to short-circuit traditional architectural techniques like layering and get to having working CRUD in a very just-let-me-get-this-done-and-go-home-what-do-you-mean-unit-testing fashion. SqlDataSource goes direct to a database, XmlDataSource goes to xml, and so on. ObjectDataSource is the only one that lets you break out of the 2-tier forms-over-data model that Microsoft likes to push. But what about LinqDataSource? Well, ODS works by binding to methods on a specified class in your code. Which, since it’s your code, allows you much more freedom than any other the others, including the LinqDataSource. Want to provide a specific instance of the binding class to be consumed by the ODS at runtime by your DI container of choice? Want to invoke custom business logic or validation during the CRUD binding-calls? Want to use constructor injection and make your binding class actually, you know, testable? Want to actually put code in a place other than an web form’s code-behind? Want to leverage server-side paging in Linq to Sql to only fetch and display the data you’re concerned with showing? Want to pipe in some custom data from the http cache? All doable with ODS, the others not so much.

For a class to serve as a binding source for an ObjectDataSource, it needs:

1. One or more methods to return stuff to bind against. Business objects, a DataTable, whatever. These methods can optionally have
   • Two integer parameters for paging (defaulting to ‘maximumRows’ and ‘startRowIndex’) indicating a subset of data to be returned
   • A string parameter for sorting, provided in the form of ColumnName DIRECTION such as ProductName ASC or UnitPrice DESC
2. A public constructor. Unless you subscribe to the ObjectDataSource’s ObjectCreating event in order to provide a custom instance before the binding calls are executed.
3. A public method that returns the total number of rows not including the fetched-page size. This is only necessary if your ODS has EnablePaging set to true.
4. Some attributes on the class or methods that do nothing but tell Visual Studio HEY! SHOW ME AS BINDABLE. Technically these are optional.

Which, in the case of Northwind might look like:

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Linq;
using Northwind.Model;

namespace Northwind.DataBinding
{
    [DataObject(true)]
    public class ProductQuerySource : IDisposable
    {
        private NorthwindDataContext _dataContext;
        private bool _disposeContextWhenDone = false;
        private int _count = 0;

        public ProductQuerySource(NorthwindDataContext dataContext)
        {
            if (dataContext == null)
                throw new ArgumentNullException("dataContext");
            
            _dataContext = dataContext;
        }

        public ProductQuerySource()
            : this(new NorthwindDataContext())
        {
            _disposeContextWhenDone = true;
        }

        [DataObjectMethod(DataObjectMethodType.Select)]
        public IList
FetchAll(int startAt, int pageSize) { _count = _dataContext.Products.Count(); var
query = _dataContext.Products.Skip(startAt).Take(pageSize).ToList(); return query;
} [DataObjectMethod(DataObjectMethodType.Select)] public IList
FetchAll(int startAt, int pageSize, string sortExpression) { _count = _dataContext.Products.Count();
IQueryable
products = _dataContext.Products; if (!string.IsNullOrEmpty(sortExpression)) products
= _dataContext.Products.OrderBy(sortExpression); return products.Skip(startAt).Take(pageSize).ToList();
} public int GetCount() { return _count; } public void Dispose() { if (_disposeContextWhenDone)
_dataContext.Dispose(); } } }

With our ObjectDataSource markup resembling

<asp:ObjectDataSource ID="odsNorthwindProducts" runat="server" 
        OldValuesParameterFormatString="original_{0}" 
        SelectMethod="FetchAll" TypeName="Northwind.DataBinding.ProductQuerySource"
        EnablePaging="true"
        MaximumRowsParameterName="pageSize" 
        StartRowIndexParameterName="startAt" 
        SelectCountMethod="GetCount" SortParameterName="sortExpression">
    </asp:ObjectDataSource>

Link that sucker to a GridView with AllowPaging and AllowSorting both set to “true” and you’ll get a grid displaying Products in which only the products currently being rendered are even returned from the database. Click on a column header to sort? Current paged fetched and sorted in database. Fantastic. Note that standard parameter-binding applies here as well, Fetch() could easily have a categoryid parameter which might limit the results to all Products in that category. Since its all just methods on a class, our binding class can be tested using whatever the your testing strategy of choice happens to be.

Oh, but what about that pesky sorting? We can’t sort a linq query by a string, can we? Well, yeah, normally that would be the case, but not to fear, because but by taking a bit of code based on this post by Pradeep Mishra (which has alot of typos in the code there, beware) we’ll be able to dynamically use those string sort expressions to invoke Linq’s OrderBy and OrderByDescending methods. Which, in the case of Linq to Sql, will result in the respective ORDER BY clauses being added to the query once it is executed against the database.

using System;
using System.Linq.Expressions;

namespace System.Linq
{
    public static class LinqExtensions
    {
        public static IQueryable<TEntity> OrderBy<TEntity>(this IQueryable<TEntity> source, string sortExpression) where TEntity : class
        {
            if (string.IsNullOrEmpty(sortExpression))
                return source; // nothing to sort on

            var entityType = typeof(TEntity);
            string ascSortMethodName = "OrderBy";
            string descSortMethodName = "OrderByDescending";            
            string[] sortExpressionParts = sortExpression.Split(' ');
            string sortProperty = sortExpressionParts[0];
            string sortMethod = ascSortMethodName;

            if (sortExpressionParts.Length > 1 && sortExpressionParts[1] == "DESC")
                sortMethod = descSortMethodName;    

            var property = entityType.GetProperty(sortProperty);
            var parameter = Expression.Parameter(entityType, "p");
            var propertyAccess = Expression.MakeMemberAccess(parameter, property);
            var orderByExp = Expression.Lambda(propertyAccess, parameter);

            MethodCallExpression resultExp = Expression.Call(
                                                typeof(Queryable), 
                                                sortMethod, 
                                                new Type[] { entityType, property.PropertyType },
                                                source.Expression, 
                                                Expression.Quote(orderByExp));

            return source.Provider.CreateQuery<TEntity>(resultExp);
        }
    }
}

So having that in place means we can have SortParameterName=”sortDirection” in our ODS markup as well as the sortDirection string parameter on the binding method. Hello, server-side paging and sorting.

I should note that this sorting and paging isn’t unique to Linq To Sql- it applies to anything queryable by Linq. It’s just that leveraging this approach with Linq To Sql yields for a more elegant sorting and paging solution when working with a database than some other the other switch-based or (god forbid) stored-procedure based approaches.

What, you mean you don’t expose your database metadata via an Ado.Net data service?

Getting at your database metadata is a pain in the ass. In my casual development of NDeavor, I’m also writing some providers for various database platforms, currently focused around MySql because it has good .NET support and it’s not Sql Server, which I actually work with every day (in the professional sense).  It became clear pretty quickly that without a fully-featured API like SMO that using the functionality in .GetSchema() off of an ADO.NET-compliant vendor’s connection class is the best bet all around.

Being that I want a solution I can use for all the providers I end up doing for NDeavor, and be as lightweight as possible, T4 templates was the best choice. Oh, and I want it LINQ-enabled as well. It will basically do the following:
- Call .GetSchema() on a DbConnection instance to get available metadata (as well as specific collections you tell it to).
- Ouput a C# POCO class with properties mapping to each column of the metadata’s DataTable that GetSchema() returns
- Output a DataContext-like class allowing for LINQ goodness.

You might think:

“Really? You’re getting the schema of a DataTable returned from DbConnection.GetSchema() so it can used to create the schema for a C# class which will be used to query the schema of a relational database?”

and you’d be right. So?

The idea of DBConnection.GetSchema(), in Microsoft terms, was to allow querying of vendor-specific metadata about the underlying data source, so as a lowest-common-denominator it returns instances of the ubiquitous DataTable and DataSet. Passing around loosely typed blobs of data won’t cut it, so we’ll be mapping these to strongly types classes, similar to that of an ORM framework. That sounds tedious and like a job for some lightweight code generation, in this case it will be some T4 template magic in Visual Studio 2008. These classes will just be thin wrappers over DataRows, they won’t have the associations/relationship semantics you’re used to seeing with Linq to Sql or Entity Framework, they’re just glue to support building up a larger more api-friendly object model. Any of the joins you do from the resulting object collections will be in-memory, there’s nothing dynamically querying the underlying schema. It’s all smoke and mirrors.

5 Steps to LINQ-ifying to your Db Schema

Download LinqToSchema

1. Add LinqToSchema.tt to your Visual Studio 2008 project, and remove the ‘Custom Tool’ setting – this is not the template itself, just for inclusion inside the ‘host’ template.
2. Create a new T4 template by adding a text file to your project and renaming the extension to .tt
3. Write the template to look similar to this:

   <#@ template language="C#v3.5" hostspecific="True" debug="true" #>
   <#@ output extension="cs" #>
   <#@ include file="LinqToSchema.tt" #>
   <# 
       LinqToSchemaSettings settings = new LinqToSchemaSettings
       {
           ConnectionStringName = "Chinook_MySql",    // name of connection string in local App.Config/Web.Config - must be in same dir as this file
           TargetNamespace = "Chinook.Schema.MySql",    // what namespace to put the generated code in
           ClassNamePrefix = "MySql",                        // what prefix, if any, to put on the generated class names
           ClassVisibility = Visibility.Public                // visibility of the generated classes (Public or Internal)
       };
      
       LinqToSchemaClassesGenerator generator = new LinqToSchemaClassesGenerator(settings);
       generator.Run();
   #>

4. Save your just-created template for it to be processed by the T4 engine.
5. Compile/write other code that uses the generated classes. Such as:
   

   using (MySqlSchemaContext ctx = new MySqlSchemaContext(@"server=servername;user id=userid;persist security info=True;database=chinook"))
   {
       var query = from tbl in ctx.Tables
                   join col in ctx.Columns on tbl.TableName equals col.TableName
                   where tbl.TableName.ToLower() == "track"
                   select new
                   {
                       tbl.TableCatalog,
                       tbl.TableName,
                       col.ColumnName,
                       col.DataType,
                       col.IsNullable
                   };
       query.ToList().ForEach(p => Console.WriteLine(p));
   }

There are some more options such as explicitly setting the connection string, which are explained in the readme. And if it wasn’t already clear, because this is using DbConnection.GetSchema(), it’s usable on any database platform that returns meaningful metadata using from their platform-specific DbConnection implementation.

For most ORM or data layer utilities there is some component of code generation involved and somewhere along the line retrieving some metadata about the underlying structure of a database is usually involved. Seeing as it relates to NDeavor, I figured I’d write about the ins and outs of getting metadata when running close to the database. Basically, there are a few basic ways to access database specific metadata directly through .Net.

Vendor-specific API.

I only know of one comprehensive API for this and it’s Sql Management Objects (SMO) which is Sql-Server only. A tremendously useful API for any Microsoft-dominated shops, though. Enumerating through basic structures like tables and columns is incredibly easy, if a little slow. Getting the table name, column name, and data type name of every column in every table in the chinook database might look like this:

public void PrintChinookTableColumns()
{
    ServerConnection sc = new ServerConnection(@".\SQLEXPRESS");
    sc.Connect();
    Server server = new Server(sc);
    Database chinookDb = server.Databases["chinook"];
    server.SetDefaultInitFields(typeof(Table), "IsSystemObject");
    foreach (Table table in chinookDb.Tables)
    {
        if (table.IsSystemObject)
            continue;

        string tableName = table.Name;

        foreach (Column col in table.Columns)
        {
            string columnName = col.Name;
            string dataTypeName = col.DataType.Name;

            Console.WriteLine("{0} {1} {2}", tableName, columnName, dataTypeName);
        }
    }
    sc.Disconnect();
}

Querying vendor-specific system tables.

Vendor lock-in at it’s finest, and typically unsupported at that. Usefulness is dependent on what level of information is available and the amount of time you’re willing to spend figuring out what the various columns and values mean. Getting table, column, and data type names in a specific database in Sql Server Express 2005 might look like this:

use chinook
go

SELECT  T.name as TableName, 
        C.name as ColumnName,
        ST.name as DataType
FROM 
    sys.tables T 
    INNER JOIN sys.columns C on T.object_id = C.object_id
    INNER JOIN sys.systypes ST on C.system_type_id = ST.type

Querying standard metadata tables.

Emphasis on ‘standard’, because there really is none. There is a spec as part of the SQL standards that came along sometime around SQL92/SQL99 (not sure) which dictates the INFORMATION_SCHEMA tables which store very basic metadata about the tables, views, columns, procedures, functions, parameters, constraints, etc, of a specific relational database. Support for these is relative to how much the vendor cares about doing so. Sql Server and MySql both have pretty strong support for these. But even then, the spec is very loose and while simple queries to say, get all table names and column names, might be portable, more complex ones likely won’t be. There’s other discrepancies as well, for instance the verbiage around ‘schema’ and ‘catalog’. In MySql terms schema is analogous to a Sql Server database, and this translates down into their implementation of these metadata tables. Back to the example of tables, columns, and data types in the chinook database:

Sql Server: 
use chinook 
go 

SELECT 
    T.TABLE_NAME as TableName,    
    C.COLUMN_NAME as ColumnName, 
    C.DATA_TYPE as DataType 
FROM 
    INFORMATION_SCHEMA.TABLES T 
    INNER JOIN INFORMATION_SCHEMA.COLUMNS C ON 
        T.TABLE_NAME = C.TABLE_NAME AND 
        T.TABLE_SCHEMA = C.TABLE_SCHEMA AND 
        T.TABLE_CATALOG = C.TABLE_CATALOG

 

MySql: 
SELECT 
    T.TABLE_NAME as TableName, 
    C.COLUMN_NAME as ColumnName, 
    C.DATA_TYPE as DataType 
FROM 
    INFORMATION_SCHEMA.TABLES T 
    INNER JOIN INFORMATION_SCHEMA.COLUMNS C ON 
        T.TABLE_NAME = C.TABLE_NAME AND 
        T.TABLE_SCHEMA = C.TABLE_SCHEMA 
WHERE T.TABLE_SCHEMA = 'chinook';

Using the schema-related functionality in Ado.Net 2.0.

Introduced in .Net 2.0, the DbConnection base class has a method with a few overloads called GetSchema(). This is supposed to allow implementers of Ado.Net providers a way to provide metadata about the underlying connected data store. Since it’s not strongly typed, and consists of returning DataTables, it’s entirely up to the implementer as far as what information is provided. Typically calling the parameter-less version of GetSchema() would return a DataTable that describes what metadata is available, indicated by a string column called something like MetaDataCollection in which you can pass to subsequent calls to GetSchema(string) to get that specific chunk of metadata. There’s a decent write up of what’s available in the native Ado.Net providers located here. Here’s an extension method to get all the metadata into one dataset for further manipulation, which should work for the built-in Ado.Net providers, as well as MySql.Data.MysqlClient using the latest MySql Net Connector:

using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Linq;

namespace System.Data
{
    public static class DbConnectionExtensions
    {
        public static DataSet GetSchemaSet(this DbConnection connection)
        {
            DataSet result = new DataSet();

            DataTable collections = connection.GetSchema();

            List<string> availableCollectionNames = (from row in collections.AsEnumerable()
                                                     select row.Field<string>("CollectionName"))
                                                     .Distinct()
                                                     .ToList();

            foreach (string collectionName in availableCollectionNames)
            {
                try
                {
                    DataTable schemaTable = connection.GetSchema(collectionName);
                    result.Tables.Add(schemaTable);
                }
                catch { }
                // this is bad form and shouldn't be necessary but SqlClient seems to try to give Sql2008 metadata for 
                // Sql2005 connections which results in unneeded exceptions
            }

            return result;
        }
    }
}

The other mechanism for getting metadata is the GetSchemaTable() method on the System.Data.IDataReader interface, which provides some limited information on the metadata for the columns that exist in a particular instance of IDataReader, typically from a call to DbCommand.ExecuteReader(). Again, GetSchemaTable(), as you might have guessed, returns a DataTable, this time with structural information only relating to the columns of the IDataReader itself. This, combined with DbDataReader.GetName(int) and DbDataReader.GetDataTypeName(int) can give some pretty useful information for table and column structure returned from a select statement. That being said, unless you’re reverse-engineering result sets from stored procedures, you’ll likely find the DbConnection.GetSchema() functionality much more effective.

If you’re using the System.Data.OleDb provider, there’s the GetOleDbSchemaTable method on the OleDbConnection class which provides its own metadata about the underlying database. It works a little differently and takes a System.Guid value which refers to the type of metadata being requested, all of which are static fields in the System.Data.OleDb.OleDbSchemaGuid class.

using System.Collections.Generic;
using System.Data.OleDb;
using System.Linq;
using System.Reflection;

namespace System.Data
{
    public static class OleDbConnectionExtensions
    {
        public static DataSet GetOleDbSchemaSet(this OleDbConnection connection)
        {
            DataSet result = new DataSet();
            List<FieldInfo> guidMembers = typeof(OleDbSchemaGuid).GetFields(BindingFlags.Static | BindingFlags.Public).ToList();

            foreach (FieldInfo field in guidMembers)
            {
                if (field.FieldType == typeof(Guid))
                {
                    Guid val = (Guid)field.GetValue(null);

                    try
                    {
                        DataTable schemaTable = connection.GetOleDbSchemaTable(val, new object[] { });
                        result.Tables.Add(schemaTable);
                    }
                    catch { } // unfortunately not all schema guids supported by all oledb connections necessarily
                }
            }

            return result;
        }
    }
}

Oddities and Errata

Under .NET 3.5 (maybe SP1 only), if you execute .GetSchema() on a System.Data.SqlClient.SqlConnection connected to a Sql Server 2005, you’ll notice there's a row indicating that the metadatacollection called StructuredTypeMembers is available. However, this is Sql Server 2008-specific and will throw an exception if you call .GetSchema(“StructuredTypeMembers”) on that same Sql 2005 connection. Why Microsoft let it even be exposed during calls to .GetSchema() on non-2008 connections, who knows. They already have a precedent for exposing Sql 2008-specific metadata depending on 2005/2008 server version as indicated here so it seems pretty stupid on their part. That’s the reason for the empty catch statement in the code block above.

Using MySql Connector Net 5.2.5, the schema collection of Foreign Key Columns is available but not indicated by the resulting DataTable returned by MySqlConnection.GetSchema(). I notified Reggie Burnett, who maintains the Net Connector, and he says it’s been fixed, so I’d expect to see that in a future release.

Getting the structure of result sets from stored procedures or functions is even more difficult than any of the basic table/column structure stuff I’ve mentioned. Using Sql Server, you can use the SET FMTONLY ON statement before the stored procedure call, and SET FMTONLY OFF afterwards, in order to get an empty result set from which you can derive schema without executing against live data. However, to do that in code, you’d have to derive their parameters using some other means like GetSchema() and build a statement block with the FMTONLY lines surrounding the procedure call. Oh, and none of this works if the stored procedure uses temporary tables to return that result set. And again, it’s Sql Server specific. For MySql, the only way I’ve figured out is to:

1. Derive the parameter information using MySqlConnection.GetSchema(“Procedure Parameters”)
2. Construct a MySqlCommand with command text equal to the procedure name and CommandType set to CommandType.StoredProcedure
3. Add phony parameters using the previously-derived information and set their values to DBNull.Value.
4. Execute the command via ExecuteReader()
5. Reverse engineer the schema using GetName(int), GetDataTypeName(int) on the MySqlDataReader, in tandem with GetSchemaTable(). Theoretically, if the information in the schema table indicates the column came from a physical table, one could go to the source and look at the values of MySqlConnection.GetSchema(“Columns”) for the source column definitions.

That’s likely how I’ll be doing it in the NDeavor providers where stored procedures are supported like Oracle and Postgres. It’s less than ideal, but if you’re still following this, you’d realize that’s my point!

Adding It Up

There’s a pattern to getting at your metadata and that pattern is it’s a pain in the ass. I’m hoping NDeavor will provide an easy means of getting database schema out of various platforms for the purposes of artifact-generation. But even so, I’m not exactly planning on writing each NDeavor provider as its own one-off implementation because there’s such inconsistency between metadata across platforms. I have a way forward for a more lower-level means of getting at this stuff that solves a lot of the cross-cutting discrepancies, and that will be in part 2.

NDeavor is a hobby project of mine that I’ve been working on for a couple months now on and off. It’s basically a way for me to explore the things I’m interested in but don’t get to explore in my day job, specifically the latest and greatest from Net like LINQ, extensibility patterns like MEF or System.AddIn, working with database metadata, dependency injection, and code generation. It’s a set of libraries and code that puts a relational database schema in a straight forward object model for the purposes of generating some other artifacts. Boring, I know. Been done before, I know. But never done simply enough (and free enough) to where I could say that my take is unnecessary. Extensibility is currently being provided using the Managed Extensibility Framework (MEF) in which I’ve defined some extremely simple contracts for a provider kind of model for support of all the different database platforms out there. I started with System.AddIn then switched to MEF, but that’s a post in and of it’s own.

Object Model

These are the interfaces, they’re purposely simple, and yet to be finalized:

The actual contracts aren’t defined on these, as the interfaces are mostly for the purposes of mocking and testing. That, and they are left over from when everything had to be a contract when I was using System.AddIn. Thinking about ditching them in favor of concrete classes.

That’s great, what does it do?

Well on the surface, it’s just a library where any external developer can implement these two interfaces to support a specific database platform. Of course, I’ll be writing providers for the major platforms as that’s the part I”m most interested in.

Each ISchemaProvider, who’s GetSchema method returns an object that implements IDatabase, can be associated to one IDataTypeProvider, which is used to get more common System.Data.DbType and System.Type mappings from vendor-specific data types. I did say it was simple.

Once you have a provider for NDeavor that supports your database platform of choice, you’ll be able to put it in some directory somewhere and be able to call some code in the NDeavor.Core assembly which will instantiate the plugin, provided you know it’s associated plugin name. After that, you can do anything with the object model that you can do programmatically: use CodeDOM to generate C# or VB code, reverse-engieer SQL scripts, whatever.

The current example of putting NDeavor to work that I’ll be providing is invoking NDeavor via T4 Templates. I wrote a custom T4 Directive Processor which allows you to put a bit of angle bracket text in a T4 template which adds a couple properties to the generated template-class, allowing you to manipulate the model in the ‘code-behind’ of the template itself. For example, this bit of T4 template text:

<#@ template debug="True" language="C#v3.5" #>
<#@ output extension="cs" #>
<#@ assembly name="System.Data" #>
<#@ assembly name="C:\Program Files\SubSonic\SubSonic 2.1 Final\SubSonic\SubSonic.dll" #>
<#@ import namespace="System.Linq" #>
<#@ import namespace="System.Diagnostics" #>
<#@ import namespace="System.Data" #>
<#@ import namespace="SubSonic" #>
<#@ import namespace="System.Collections.Generic" #>
<#@ include file="TemplateInclude.tt" #>
<#@ DataModel Processor="DataModelDirectiveProcessor" Database="chinook" Name="Chinook" ConnectionStringName="Chinook_MySql" SchemaProvider="NDeavor.SchemaProviders.MySql" #>
<#  IDatabase db = this.ChinookDataModel; 
    IDataTypeProvider prov = this.ChinookDataTypeProvider; #>
using System;
using System.Collections.Generic;

namespace Model
{
<#foreach(ITable table in db.Tables) {#>
    public class <#=GetClassName(table)#>
    {        
<#foreach(IColumn col in table.Columns) {#>
        public <#=prov.GetSystemType(col).FullName#> <#=GetSingularPropertyName(col)#> { get; set; } 
<#}#>
    }
    
<#}#>
}

Generates a bunch of code like this:

    public class Customer
    {        
        public System.Int32 CustomerId { get; set; } 
        public System.String FirstName { get; set; } 
        public System.String LastName { get; set; } 
        public System.String Company { get; set; } 
        public System.String Address { get; set; } 
        public System.String City { get; set; } 
        public System.String State { get; set; } 
        public System.String Country { get; set; } 
        public System.String PostalCode { get; set; } 
        public System.String Phone { get; set; } 
        public System.String Fax { get; set; } 
        public System.String Email { get; set; } 
    }
    
    public class Employee
    {        
        public System.Int32 EmployeeId { get; set; } 
        public System.String LastName { get; set; } 
        public System.String FirstName { get; set; } 
        public System.String Title { get; set; } 
        public System.Int32 ReportsTo { get; set; } 
        public System.DateTime BirthDate { get; set; } 
        public System.DateTime HireDate { get; set; } 
        public System.String Address { get; set; } 
        public System.String City { get; set; } 
        public System.String State { get; set; } 
        public System.String Country { get; set; } 
        public System.String PostalCode { get; set; } 
        public System.String Phone { get; set; } 
        public System.String Fax { get; set; } 
        public System.String Email { get; set; } 
    }

Now that’s what I’m talking about. No querying system tables, no custom vendor-specific api, it’s all done for you in that bit of angle brackets. Booyah. Oh, and I’m using SubSonic solely for the Inflector class to singularlize class names, it’s totally not required for NDeavor. I may provide my own ‘extra’s like that with the T4 functionality, not sure.

Tentative To-Do List

• Get object model to more polished and developer-friendly state. Includes ditching some of the POCO-ness in favor of read-only collections backed by specific Add/Remove methods, or custom collections all together, I haven’t nailed that down yet.
• Get 2 providers written. I’m writing the MySql provider in tandem, first as a break from Sql Server which I use daily, and second as a way to get a view of how another platform does things (you flag an integer column to make it unsigned? seriously?), and how those things translate into a generalized framework like this.
• Figure out configuration of standard plugin directory and how to implement subdirectory watching using MEF.
• Test. Test. Test.
• Blog about the ways to access metadata in various database platforms. (MySql 5.x has good, yet incomplete, support of the INFORMATION_SCHEMA ‘standard’, yet I’m using the .GetSchema() methods on the MySqlDataConnection class, via MySql Net Connector 5.2. )
• Publish to CodePlex when all’s good. (License?)

That’s it for now. More to come!

So this is my blog. I can’t believe this domain was available. This will be another nerdy developer/.NET blog like the billions others.

TODO:

- Customize dasblog theme

- Get SyntaxHighlighter all hooked up

- Produce content

- Find better hosting. Godaddy is awful. Excuse for a vps, maybe?

public class Foo
{
    public Foo()
    {
        Console.WriteLine("what?");
    }
}