in

ASP.NET Weblogs

Yves Reynhout's Blog

The seagile man

Visiting SQL - Part 1

Apparently, after some months of intensive work, I'm dieing to share (and reflect on) some of the things I've worked on.

Although "pluggable database backends" and "SQL abstraction" were not directly goals of the project (regular OLTP app), I kind of stumbled on them by accident (just don't call me nuts yet). First off, a lot has already been written about "pluggable database backends", achieved using the IDbXYZ and IDataZYX interfaces which any decent .NET data provider implements, so I'm not gonna talk about that.
At work, I tend to use a lot of dynamic SQL because I don't want too much of my business logic "locked" in stored procedures. The whole dynamic SQL versus stored procedures topic is not something I want to delve in now, so we're not gonna talk about that either. Dynamic SQL built in code suffers the problem that as the database schema changes, the "SQL building code" should change along with it. So how do you know which SQL building code is affected by changes in the database schema? If the SQL was somehow typed, the compiler would tell you. Making the SQL typed would mean that there should be some object model in place that, for starters, allows you to represent the database objects (think views, tables, columns, ...).
Datasets, and in particular the typed flavor, come to mind. But they're a little bloated just to represent a schema (and probably limited as to the types of database objects they can represent). I had a look at CodeSmith's SchemaExplorer (which BTW has a slick object model) but couldn't use it because it's use was primarly to discover the database schema at runtime. What I was looking for was a static definition of the database schema in C# code at design time (which I dubbed RelationalSchema). Although I'm not a big proponent of DIY, I really don't think I had much choice at the time. I borrowed some of SchemaExplorer's object model (in redux I might add), and made it xml serializable using one of those fine WSE interfaces, namely IXmlElement(who said you can't reuse wonderful material).
Now that RelationalSchema was born, containing RelationalSchemaDatabase, RelationalSchemaTable, RelationalSchemaView and RelationSchemaColumn, I used CodeSmith to build a template that generates my xml representation of a relational schema. This way I could have an xml representation of any database schema I liked; just a matter of setting the proper connectionstring. Still, this was just a "generic" representation of a database schema. I still wouldn't detect database schema changes this way. Much like datasets definitions can be used to generate typed datasets, I needed a way to generate a typed relational schema. Again, CodeSmith to the rescue. I made a template which creates a typed object model based on the generic object model.
If I was planning on using the "building SQL using strings" approach than my story would be done here. But it isn't...
Published Apr 09 2005, 12:31 AM by yreynhout
Filed under: , ,

Comments

 

TrackBack said:

April 8, 2005 3:38 PM
 

TrackBack said:

April 12, 2005 11:02 AM

Leave a Comment

(required)  
(optional)
(required)  
Add
Terms of Use