How can I programatically convert SQL data-types to .Net data-types?

Question

Can anyone show me a way of converting SQL Server data-types (varchar for example) to .Net data-types (String for example). I'm assuming that automatic conversion is not possible? I have an 'EntityProperty' object and would like it to have an appropriate 'Type' property (string, decimal, int32 etc), at the moment this property is just a string - 'int32' for example.

A little background: I'm using SQL DMO in an internal code generation app to query a database and generate a stored procedure based DAL from the database. Being an internal app I can take quite a few shortcuts and make quite a few assumptions. To get the app working at the moment this data-type conversion is handled by a Select Case statement which just converts the types to strings and generates a set of properties based on these strings but I would prefer a little more flexibility in being able to handle the types (use of TypeOf etc).

Anyone worked on something similar?

I know EF, nHibernate, Subsonic etc could do all this for me but in this case, for various reasons, I am having to roll my own. :)

Answer 1

The reason hard coding is a Bad Thing is only because when you put things in code that change, it's annoying (and expensive) - there's no other reason. Things that don't change, like pi, or the list of weekdays, can be hard-coded to your heart's content, and you won't incur any extra development cost as a result.

So this problem isn't so much about not maintaining a manual mapping table - in code if necessary - as it is about only maintaining the mapping table in one place.

We rolled our own data access class, several years ago now. And sure, we convert manually (in a VB.NET Select Case statement) from .NET types to SQL types. I think it changed once, when we had to add Enum types.

That's once, in about four years. We do a release a week, on average - guess how worried we are about the 'overhead' of hard coding the .NET -> SQL type mapping?

Do it in one place. Ensure that everything uses it. And then forget about it. There are other, much tougher problems to solve.