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.

Thursday, January 14, 2010

Set an ActiveX as Trusted and make calls to Javascript functions

There comes a time when you need to write an ActiveX for reasons that really don't matter in this post but you know those times.  I personally try to avoid them due to their "Windows Only Support" but in a recent project where we had to integrate a java web based application with an application I wrote in C# that was a Winforms application the need was eminent.  We needed a way to fire up the winforms application from the web, pass it some parameters, and when it closes notify the page as well as give the page some data the user entered.  Well getting a web page to open a windows application is pretty simple however we needed much more than just opening the applciation and forgetting about it.  A problem we were facing was how are we going to:

1. Notify the calling page when the form closes
2. Return user entered data back into the page

The client has a policy on their in-house applications that they only support IE.  So we knew from that we could build an ActiveX and not worry about other browsers.  I mean hey, IE works very well with ActiveX and FireFox not so much.  We needed the code to notify the page via javascript because a postback was not allowed for this application.  It appeared the java web app uses a redirect similar to the way Server.Transfer works in ASP.NET in which the Url stays on the base directory.  If a postback was fired then the user would be taken back to their dashboard and lose what they had entered.  So the idea to write an ActiveX was decided as the approach we would take.  We still needed to know if it was possible to make a call to a Javascript function from an ActiveX.  After much research I found a solution using the IOleClientSite Interface.  This interface allows you to get the container the ActiveX is hosted in.  Then using the IHTMLDocument2 interface we can gain access to the DOM.

Here is an example:

/// <summary>
/// Calls the JScript within the current DOM this activeX is placed in.
/// </summary>
/// <param name="key">The key.</param>
private void CallJScript(string key)
// Get a handle on the current Page
Type typeIOleObject = this.GetType().GetInterface("IOleObject", true);

// Get the ClientSite
object oleClientSite = typeIOleObject.InvokeMember("GetClientSite",
BindingFlags.Instance | BindingFlags.InvokeMethod | BindingFlags.Public,
null, this, null);

// Obtain the container
IOleClientSite oleClientSite2 = oleClientSite as IOleClientSite;
IOleContainer pOleContainer;
oleClientSite2.GetContainer(out pOleContainer);

IHTMLDocument2 pDoc1 = (IHTMLDocument2)pOleContainer;

// Gain access to its DOM
object script = pDoc1.Script;

// Create the arguments that will be passed
// as parameters to the javascript function
object[] args = new object[1];
args[0] = key;

// Invoke the script
script.GetType().InvokeMember("YourJavaScriptFunction", BindingFlags.Instance | BindingFlags.InvokeMethod | BindingFlags.Public, null, script, args);
// TODO: Log Message

This seemed simple enough until we put the ActiveX on the page and discovered it wasn't working.  I was so confused because from everything I had read this is how you do it.  After more research and debugging I discovered the ActiveX didn't have permission to access the DOM; it was being denied by IE.  So once again more research on how to get access to the ActiveX because setting the site as Trusted wasn't enough.  I came across another site (I wish I could find it again to display here) which led me in the right direction on how to get this working.  You have to create an interface named IObjectSafety which will let IE know the ActiveX is safe.  To make this work you create an enum that is Serializable and ComVisible like so:

[Serializable, ComVisible(true)]
public enum ObjectSafetyOptions

Then you need to create an Interface called IObjectSafety like so:

/// <summary> 
/// IObjectSafety lets IE know this ActiveX is safe
/// </summary>
[ComImport(), Guid("CB5BDC81-93C1-11CF-8F20-00805F2CD064")]
public interface IObjectSafety
long GetInterfaceSafetyOptions(ref Guid iid, out int pdwSupportedOptions, out int pdwEnabledOptions);

long SetInterfaceSafetyOptions(ref Guid iid, int dwOptionSetMask, int dwEnabledOptions);

And then you create your control (I used UserControl) which will be used to talk between the Page and the Windows Form. 

public partial class MyClass : UserControl, IObjectSafety

The IObjectSafety member was implemented as:

#region IObjectSafety Members
private ObjectSafetyOptions m_options =

/// <summary>
/// Gets the interface safety options.
/// </summary>
/// <param name="iid">The iid.</param>
/// <param name="pdwSupportedOptions">The PDW supported options.</param>
/// <param name="pdwEnabledOptions">The PDW enabled options.</param>
/// <returns></returns>
public long GetInterfaceSafetyOptions(ref Guid iid, out int pdwSupportedOptions, out int pdwEnabledOptions)
pdwSupportedOptions = (int)m_options;
pdwEnabledOptions = (int)m_options;
return 0;

/// <summary>
/// Sets the interface safety options.
/// </summary>
/// <param name="iid">The iid.</param>
/// <param name="dwOptionSetMask">The option set mask.</param>
/// <param name="dwEnabledOptions">The enabled options.</param>
/// <returns></returns>
public long SetInterfaceSafetyOptions(ref Guid iid, int dwOptionSetMask, int dwEnabledOptions)
return 0;


This is all it took to make the ActiveX have the permission it needed to gain access to the DOM and make DOM calls.  The use of the IObjectSafety makes an ActiveX that was once "untrusted" become "trusted".  I hope this helps you out and saves you the time I spent trying to get this working on our project.  As always, Happy Coding.

Wednesday, January 13, 2010

My personal experience with Alienware PC Maker

    Well on March 26th 2009 I purchased a Laptop from Alienware which I was very excited about after reading about the high performance components they use in their laptops.  I got my Laptop and quickly opened it and was ready to conquer my development tasks… well probably not quite like that but you get the point.  All was going great, I was installing Visual Studio 2008 and got that done; then went on to install DevExpress but before I got a chance to install it the laptop froze up.  The screen stayed on the way I left it and the cursor would not move.  I immediately called Technical Support since these things shouldn’t be happening and I happened to have purchased the extended warranty.  Technical Support had me reboot, which consisted of holding the power button down for 3-5 seconds.  Then they asked me to download a third-party program (SpeedFan) so we could check if the PC was getting hot while a memory check was run using both chips installed.  Then we ran a Video Card test and monitored the PC heat.  All seemed to be doing well until we tested Memory using a single chip at a time and some hiccups happened.  First attempt froze the machine and subsequent attempts seemed to be fine.  We could not figure out why the PC was freezing.  So we gave up and went on thinking it must be drivers or something.  I updated the drivers to the latest versions and even flashed the bios with a new upgrade that was available.  The PC kept freezing about once a week, then once a day and I was getting flustered.  I called Support again and once again we ran a memory check (Extensive Check) on what I now have installed Windows 7.  No results found, the memory passed.  So then we ran a chkdisk with the options to repair bad clusters checked and of course no issues with the exception of 2 bad clusters which windows reports it fixed.  After hours with a Technician and no idea why this pc is freezing he puts me on hold and another guy gets on the line.  The new guy gets on just to tell me that because I upgraded to Windows 7 my warranty is no longer going to support the problem I am experiencing with my laptop and that if I want to upgrade my support a charge will be required.  I asked if I paid this upgrade fee could they guarantee I would get the laptop fixed since of course it has had this problem since day 1 and I could go about my business.  I was told that they could not guarantee it would be fixed but they would try.  So basically what it boils down to is the following:

1. Paid for extended support – Not any good to me now since I upgraded to Windows 7
2. They want to charge me more – Cannot guarantee it will be fixed
3. I’m stuck with a laptop that keeps freezing and has been literally the day I got it

I was furious, I asked the guy if they test their equipment before sending it to the customer, he exclaimed they only test that it installed the OS and not for any other issues (Hardware or Software).  All in all I have learned a valuable lesson, I for one will not purchase extended warranties again on PC’s and I am done with Alienware products.  Not to say your experience will not be an enjoyable one but for me it has been a nightmare.  If you too have had a bad experience with Alienware I would like to hear from you and your experience.

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