Extended properties in MS_Description?

Hi All,

I've been looking over a few posts regarding different issues and customisation requests, and I think I may have something that could be worked into a solution.  I'm thinking that we could probably used the MS_Description for individual columns and/or tables, either combined with the mapping.config file or by themselves, to affect the code generation to incorporate more "custom" features.

For example, take the 4 requests / suggestions / issues below:

1.  Localization: http://community.codesmithtools.com/forums/t/7191.aspx

2.  Oracle NUMBER vs Int32, Single, Double, Decimal: http://community.codesmithtools.com/forums/t/6971.aspx

3.  Overriding the text for edit control labels: http://community.codesmithtools.com/forums/p/7002/26684.aspx#26684

4.  Specifying/Handling/Ignoring readonly columns: http://community.codesmithtools.com/forums/p/2004/8256.aspx#8256

I'm thinking we could come up with some simple format for adding this control data to the Description fields for columns and/or tables, and use that control data to influence how the code is generated.

For example, maybe the MS_Description for a column could be set to something like this:

DataType=Decimal;FriendlyName=Amount;ReadOnly=true

During parsing of the database and column description, this string could be split appropriately, and a hashtable of key/value combinations created.  Other parts of the code could check for appropriate keys, and generate code depending on the values. 

This should work for the column description field in Oracle as well, giving us a way to forcibly specify the .NET datatype we want to use for an Oracle NUMBER column.

The example above should be pretty clear.  Imagine we have an Oracle table of invoices, with an INVOICE_ORIGINAL column of datatype NUMBER.  The above string would force .netTiers to generate this as a Decimal datatype, using "Amount" as the FriendlyName (pumped to mapping.config?), and the ReadOnly=true means the column would be included in SELECTs, but not in UPDATEs, and create the on-screen representation of that field as read-only.

It would be easier to keep this stuff in sync if it was in the database rather than a separate mapping file, and there's also the possibility of using the fields in database documentation tools as well, to enhance the documentation process.

I think the important thing would be a agree on a format for the Description field (SQL Server, Oracle or whatever).  I've used semi-colon delimited key/value pairs in the example above ... someone else might have a different / better idea.  Maybe a shorthand XML format?

Any comments?  Has something like this already been implemented, and I've missed it? :-D

T

I went that route before once. It wasn't with NetTiers, but my own entity layer who's codgen parsed the MS_Description on columns. It became a bit unwieldly. I eventually moved to a seperate "MetaData" database. A simple little DB with an Entity table and EntityField table. On the EntityField table as your common Metadata stuff (DisplayName, ReadOnly, DisplayFormat, ToolTip, Help, Description, Sample value, ForeColor, BackColor and many others).

The way I hooked that into my entity layer is something I plan on doing with NetTiers. Basically, in .Net 1.0, when I did this,  I had my own ITypedList GetProperties override on my entities. Through through that interface I took the time to lookup metdata for the entity (which was cached for something like 5mins on avg). Most of the metadata values would come from the database, but I had Attributes for every metdata property as well. So any custom properties added to the entity that didn't come from a database column could be marked up in code as well as specified in the back-end metadata database. This was pretty usefull in testing.

The question really is how do you access the information. For my purposes I exposed "EntityField" objects during GetProperties/binding source time. Basically, a value type object that added access to the field level metadata. So basically, I could bind to "Person.FirstName" but instead of getting the standard get/set accessor for the string field, I got a special adapter class which set add get/set for FirstName, but also a MetaField child object that pointed to all the metadata properties for this column.

Having this bind normally with bindable data-source took some work. And in the end for all features (like Readonly, Custom Editor Control, Fore-color/back color, display format...etc) to work on bindable source, I had to make custom adapters for each widget. So I had a UltraWinGridAdapter, that could handle the bind and make sure during databinding that all the rich metadata made it's way to whatever implementation the UltraGrid had for such a feature.

I must say, as often as my clients change column order, display formats, descriptions, header names, etc, it easily paid for itself. Eventually local administrators handled keeping all that metdata up to date. And even had some fancy things like Abbreviated column names that would automatically get used on narrow grids.

It worked, it was great, but I'm not sure how I would do it in a abstract 'shareable' way. I think .Net 2 added some better capabilities to allow this. In the future extension methods i think makes this pretty simple.

I think what's really missing is a contract between data providers and GUI data consumers on some of this rich metadata. The fact that some grids honor System.ComponentModel.Browsable(False) or System.ComponentModel.DisplayName, and others don't is annoying.