Data type conversion question

[Sam Malone]

I'm writing code (in VB.NET/2005) to create a SQL Server database from some
"other" database (user selected). So I "connect" to it and then use the
GetSchema method options to retrieve its characteristics to create/define
the new SQL Server database and (optionally) populate it with data from the
old database.

Using the GetSchema("Columns", restrictions) method, I get a data table back
showing the datatypes of the columns in the selected (old) table. They show
up as a number (e.g. a "12" is a varchar) and a name. Then there's the size,
number of decimals etc.

Question is:
Is there a way to use this information to specify the SMO datatype without
having to go through a whole whack of "Select Case" statements to deal with
each separate incoming datatype?

I'm trying to avoid a whole series of statements like this:
Select Case <input datatype number>
Case 12
col.DataType =
Microsoft.SqlServer.Management.Smo.DataType.VarChar(COLUMN_SIZE)

This does work but it's a lot of coding and there's too great a potential
for missing one (or more) valid case(s)

 

[tommaso.gastaldi]

Hi Sam,

an idea could be giving the possibility to the user to remap fields. In
fact, if your purpose is somehow to pass data from a database to
another, very often the user will not restrict himself to tables, but
he may want to make queries. In general you might provide a panel wher
the user can see the "origin" types and the possible "target" types and
for each origin type he can indicate the target type. This is also
useful because given an "origin" type the user can choose different
target types on diffrent taget databases... (btw is your origin
arbitrary or necessarily sqlserver?)

What do you think?

tommaso

 

[Sam Malone]

Thanks for the suggestions. I appreciate you taking the time.
What I'm doing is writing a utility that a person can use to copy a database
from one technology to another, be that SQL Server (Express or "full"),
MySQL, Oracle or MS Access. By copy, I mean tables (DDL and data), Indecies,
Views, whatever - so that (insofar as the two technologies support certain
features) they'd end up with a duplicate of the "from" database on the "to"
technology..
I can do it as in my original post but was trying to simplify the copying of
the column definition - data types so that I didn't have a couple of dozen
"Case" statements such as the one illustrated in my original post.

 

[tommaso.gastaldi]

That's interesting. I have done something similar, but more in a
perspective of data integration,
instead of database cloning. I had to place all the "cases" because I
was also providing the user
with the possibility to do changes when copying. Further I had to deal
with arbitrary joins done
on the origin database. However the problems are quite similar.

If you wish to share more I am available. I have accumulated some
experience on shufflying data
from a platform to another... thare are tons of subtleties, and
probably I have discovered only
a small part of them ...

-tom