Select Statement for DataSet with Complex Data Type

I have been very busy with a lot of stuff, I almost forgot that I have a blog. This post will be my first post in 2008.

In the project that I was working with, we never use complex data type for our column in a DataSet (System.Data.DataSet). If you uses Visual Studio DataSet Designer, you will notice that the default DataType for your column are either .NET value type, string, TimSpan, Guid, DateTime.

What if you want to use your won DataType?

Surprising, the dataset designer works with custom data type. For example:

The Column DSTestTable1Key is using a custom data type calls "DataType.Key"

The Key data type is basically similar to a GUID, except it is not a restrictive as a GUID. If you try to create a GUID using random string value, it won't work.

I wrote a number of test to see if I can insert, update and data data row from the table. It works as expected, except how do I do select using this Complex Data Type?

It turns out that there is a function call Covert that you can use: For example:

   1: string l_sColumnName = UIModel.UIDataModel.DSTestTable1.DSTestTable1KeyColumn.ColumnName;

   2: string l_sExpression = "CONVERT([" + l_sColumnName + "], 'System.String')" + " = " +

   3:                        "'" + l_oNewRow.DSTestTable1Key + "'";

   4:  

   5: DataRow[] oResult = UIModel.UIDataModel.DSTestTable1.Select(l_sExpression);

What happen in the above example is that it convert the data type of my column data to System.String and than compare with my Key.ToString() value.

So in conclusion, as long as your complex data type support an override ToString() that is unique, you can use the above example to retrieve value based on your complex data type. To learn how more about the select express, see reference:

Reference: http://msdn2.microsoft.com/en-us/library/system.data.datacolumn.expression(VS.71).aspx