Create DB table from dataset table

Question

Is it possible (in Vb.Net 2005), without manually parsing the dataset table properties, to create the table and add it to the database?

We have old versions of our program on some machines, which obviously has our old database, and we are looking for a way to detect if there is a missing table and then generate the table based on the current status of the table in the dataset. We were re-scripting the table every time we released a new version (if new columns were added) but we would like to avoid this step if possible.

Answer 1

See this MSDN Forum Post: Creating a new Table in SQL Server from ADO.net DataTable.

Here the poster seems to be trying to do the same thing as you, and provides code that generates a Create Table statement using the schema contained in a DataTable.

Assuming this works as it should, you could then take that code, and submit it to the database through SqlCommand.ExecuteNonQuery() in order to create your table.

Answer 2

Nice find. That is actually pretty similar to what I have already written. I was about 2/3rd of the way through writing the same thing before I decided to ask if anyone else knew of a better way. Combining the SMO with what I have already written seems like a better way to go, however.