Saturday, May 29, 2010

Using Sql to generate C# properties

I am working on a project which entails myself and another developer Todd Wood to build our business objects with properties (a small piece of the project) matching the Oracle table column names.  Surely this is not hard to accomplish however, very tedious considering the table/column count.  My first thoughts were to use CodeRush to generate the properties faster than hand typing each of them.  However, Todd in all his brilliance decided to take another approach.  Instead he wrote a set of queries which will read the table's metadata and generate the properties for us.  Not only does it generate the properties, it takes nullable into consideration and generates nullable types as well.  Another item he took into account was we needed to read the data into the properties at some point, so naturally he wrote a script to generate the reader code for us as well.
As Genius Beer makers would say, Brilliant, just Brilliant!
So how did he do this? lets take a look at the Sql:

-- Data Reader populate

select replace(initcap(column_name), '_') || ' = ' || decode(data_type, 'NUMBER', 'Convert.ToInt32(reader["' || column_name || '"]),', 'DATE', '(!reader.IsDBNull(' || to_char(column_id - 1) || ')) ? Convert.ToDateTime(reader["' || column_name || '"]) : (DateTime?)null,', 'VARCHAR2', 'reader["' || column_name || '"] as String,', 'CHAR', 'Convert.ToChar(reader["' || column_name || '"]),' )
from user_tab_columns
where table_name = '{Your Table Name}'
order by column_id

-- Get / Set

select 'public ' || decode(data_type, 'NUMBER', 'int'|| decode(nullable, 'Y','?') , 'DATE', 'DateTime'|| decode(nullable, 'Y','?') , 'VARCHAR2', 'string', 'CHAR', 'char'|| decode(nullable, 'Y','?') ) || ' ' || replace(initcap(column_name), '_') || ' { get; set; }'
from user_tab_columns
where table_name = '{Your Table Name}'
order by column_id;

Now keep in mind this was designed to run against an Oracle database, however it can be converted to your DB of choice with some tweaks here and there (Don't quote me on this as I am no DBA by any means ;), but "It should probably work"). 
So what happens when this is run against the DB?

When you run the Reader script your output will look something like this when run against the USER_TAB_COLUMNS table in Oracle:

TableName = reader["TABLE_NAME"] as String,
ColumnName = reader["COLUMN_NAME"] as String,
DataType = reader["DATA_TYPE"] as String,
DataTypeMod = reader["DATA_TYPE_MOD"] as String,
DataTypeOwner = reader["DATA_TYPE_OWNER"] as String,
DataLength = Convert.ToInt32(reader["DATA_LENGTH"]),
DataPrecision = Convert.ToInt32(reader["DATA_PRECISION"]),
DataScale = Convert.ToInt32(reader["DATA_SCALE"]),
Nullable = reader["NULLABLE"] as String,
ColumnId = Convert.ToInt32(reader["COLUMN_ID"]),
DefaultLength = Convert.ToInt32(reader["DEFAULT_LENGTH"]),
NumDistinct = Convert.ToInt32(reader["NUM_DISTINCT"]),
Density = Convert.ToInt32(reader["DENSITY"]),
NumNulls = Convert.ToInt32(reader["NUM_NULLS"]),
NumBuckets = Convert.ToInt32(reader["NUM_BUCKETS"]),
LastAnalyzed = (!reader.IsDBNull(18)) ? Convert.ToDateTime(reader["LAST_ANALYZED"]) : (DateTime?)null,
SampleSize = Convert.ToInt32(reader["SAMPLE_SIZE"]),
CharacterSetName = reader["CHARACTER_SET_NAME"] as String,
CharColDeclLength = Convert.ToInt32(reader["CHAR_COL_DECL_LENGTH"]),
GlobalStats = reader["GLOBAL_STATS"] as String,
UserStats = reader["USER_STATS"] as String,
AvgColLen = Convert.ToInt32(reader["AVG_COL_LEN"]),
CharLength = Convert.ToInt32(reader["CHAR_LENGTH"]),
CharUsed = reader["CHAR_USED"] as String,
V80FmtImage = reader["V80_FMT_IMAGE"] as String,
DataUpgraded = reader["DATA_UPGRADED"] as String,
Histogram = reader["HISTOGRAM"] as String,

Notice how the script writes C# code for you ready to be put to work.  Take a look at LastAnalyzed, its smart enough to handle the tertiary code as well! Now let's see what the property script will output for the same table:

public string TableName { get; set; }
public string ColumnName { get; set; }
public string DataType { get; set; }
public string DataTypeMod { get; set; }
public string DataTypeOwner { get; set; }
public int DataLength { get; set; }
public int? DataPrecision { get; set; }
public int? DataScale { get; set; }
public string Nullable { get; set; }
public int? ColumnId { get; set; }
public int? DefaultLength { get; set; }
public DataDefault { get; set; }
public int? NumDistinct { get; set; }
public LowValue { get; set; }
public HighValue { get; set; }
public int? Density { get; set; }
public int? NumNulls { get; set; }
public int? NumBuckets { get; set; }
public DateTime? LastAnalyzed { get; set; }
public int? SampleSize { get; set; }
public string CharacterSetName { get; set; }
public int? CharColDeclLength { get; set; }
public string GlobalStats { get; set; }
public string UserStats { get; set; }
public int? AvgColLen { get; set; }
public int? CharLength { get; set; }
public string CharUsed { get; set; }
public string V80FmtImage { get; set; }
public string DataUpgraded { get; set; }
public string Histogram { get; set; }

And there you have it folks, a nicely generated set of properties and readers to populate the properties based on your tables columns/names.  Many thanks to Todd Wood for these great scripts.

Creative Commons License
Blogged Information and Code is licensed under a Creative Commons Attribution-Noncommercial-Share Alike 3.0 United States License.