Value returned by GetColumnByName does not match the properties of the actual column in the database (SQL Server 2008, DATETIME column)

[GoogleCodeExporter]

What steps will reproduce the problem?
1. Create a table in a SQL Server 2008 database with a DATETIME column
2. Use GetColumnByName() to retrieve this column as a Column object
3. The Type property of the column is DbType.String (NVARCHAR)

What is the expected output?
   I would expect the Type property of the Column to be DbType.DateTime

What do you see instead?
   The Type property is DbType.String

What version of the product are you using?
   0.8

On what operating system?
   Reproduced on Windows XP SP3 and Windows 7

With what .NET implementation/version?
   .NET 3.5

What database and version are you seeing this issue on?
   SQL Server 2008 (Express Edition)

Original issue reported on code.google.com by jshol...@gmail.com on 30 Sep 2009 at 2:22

[GoogleCodeExporter]

In TransformationProvider.cs in the GetColumns method, the Type property is 
always
set to DBType.String. See code:

Column column = new Column(reader.GetString(0), DbType.String);

This is not expected behaviour for a user.

//Daniel Lee

Original comment by dan.lime...@gmail.com on 9 Feb 2010 at 6:55

[GoogleCodeExporter]

To determine the types of columns you need to implement mapping values returned 
by the 
DBMS on the set of elements DbType. I think the benefits of this functional 
possibility 
is not worth the time it takes to realize it.

Can you give examples of problems for which it is important the possibility of 
the 
determine column type ?

Original comment by dima117a@gmail.com on 10 Feb 2010 at 6:15

[GoogleCodeExporter]

How about any time you wish to change the properties of an existing column, 
other 
than the data type? For instance, changing the NULL constraint or name. I think 
these 
are both perfectly valid, non-fringe, use cases. Why else would the 
ITransformationProvider.ChangeColumn() method exist if not to allow for such 
changes?

It's my opinion that the object returned by ChangeColumn() should properly 
represent 
the actual column that it's meant to model. This means that all the properties, 
including Type, should be set based on the properties of the actual column in 
the 
database. This also means that a user should be able to immediately pass the 
return 
value of GetColumnByName() to ChangeColumn() and no changes should be made to 
the 
column. At present, this is not always the case, because of the aforementioned 
bug.

Sure, you could use plain old SQL alter statements, but doesn't that defeat the 
purpose? If a method is going to be available for retrieving Column objects 
that 
represent actual columns of actual tables, shouldn't the returned Column 
object's 
properties correspond to those of the actual column that it's meant to 
represent?

I feel the current implementation of GetColumnByName is confusing and 
misleading, as 
it doesn't really return a Column object that truly represents the actual 
column in 
the database table.

If it's not feasible, or practical, to use some introspection method to 
determine all 
the properties of a column, then why not remove GetColumnByName() and 
ChangeColumn() 
and replace them with  other methods that allow the user to change only certain 
properties of a column without having to use a Column object?

Original comment by jshol...@gmail.com on 10 Feb 2010 at 1:21

[GoogleCodeExporter]

If you use the migrator, your database is versioned. In this case, you know 
exactly 
settings of your column of the table when writing code for each migration. 
Accordingly, 
the method GetColumnByName hardly ever will you really need. 
I think that this is the reason why the method GetColumnByName remained not 
fully 
realized.

Original comment by dima117a@gmail.com on 11 Feb 2010 at 5:44

[GoogleCodeExporter]

I completely understand that the method is not necessarily complete, and I 
don't have 
an issue with that. I think migrator.net is a great tool, and my hat goes off 
to 
everyone who has contributed to it. All I'm saying is that if the 
GetColumnByName 
method does not work as intended, then perhaps it should be documented 
somewhere that 
this is the case, or perhaps the method should be deprecated if there are no 
plans to 
implement it in the future? For me, it was a bit misleading and didn't behave 
as I 
expected.

Original comment by jshol...@gmail.com on 11 Feb 2010 at 1:45