Create dataset first, or SQL database?

[_eddie_]

I need to translate a (proprietary format) data file and store the
data in both a dataset and an SQL database. It seems that the 'best'
way to do this may be to:

1 Create the SQL database tables/schemas first
2 Create the dataset internals from those
3 Load the datasets with the new data
4 Store the dataset back to the SQL db.

Is there a preferred method for doing this? If there is a good way to
automate creation of the SQL db from the dataset, it would save me
from having to write lots of SQL code.

 

[William Ryan [eMVP]]

DTS is usually the preferred way to handle this sort of stuff, but it'll
depend on the file format. As far as creating the table, you could walk
through the Columns Collection building a SQL Statement as you go and just
fire it as a DDL statement against the DB. You'll need permissions but
you'll only need to do it once and it's quite easy to do this. With that
said, ADO.NET is meant to do all kinds of things, whereas DTS is
specifically made to move data around and transform it, so if at all
possible, it's probably easier to do with DTS>

HTH,

Bill

 

[_eddie_]

DTS is usually the preferred way to handle this sort of stuff, but it'll
depend on the file format. As far as creating the table, you could walk
through the Columns Collection building a SQL Statement as you go and just
fire it as a DDL statement against the DB.

Damn...I forgot to mention that I need to do everything
programmatically within a C# file. By "SQL", I meant embedded SQL
commands (even less fun than normal SQL for me). Even considering
this, I was going to create the DB and tables using SQL within the C#
source, etc (steps 1-4 above).

Is there a more elegant way to do this? (I'm not worried about the
format translation. That's coded already)