Creating database structure by using .NET DataSets

Question

What is the easiest way to apply to database changes to the structure (not the data) made to DataSet? Do I need stuff like DataAdaptors? Is it possible to use in this way the DataSet that I configured via Visual Studio wizards? How to access such a DataSet?

Answer 1

Sorry, there's no built-in way to do this.

A DataAdapter adapts between a DataSet and the DML (Data Manipulation Language) of some database provider. There's no correspondence between a DataSet and the DDL (Data Definition Language) of a provider.

The one time I had to do this sort of thing, I wound up interpreting the DataSet myself. I had a program that accepted a bunch of "relational-like" metadata from a server, used it to construct a DataSet, then interpreted the DataSet to create the DDL. It then executed the DDL to create the database, filled the DataSet from the service, then finally used a DataAdapter to write the DataSet to the database.

It wasn't hard, just a bit time-consuming. I started by scripting out the initial database structure, then creating a DataSet that matched the structure. I then figured out which parts of the DataSet came from which parts of the DDL. I then learned how to go from the DataSet back to DDL. When the output from the DataSet matched the original DDL (more or less), then I considered myself done.

Answer 2

You could combine DataSet.GetXmlSchema() with an XLST to generate the schema DDL. Another way would be to write a small program to parse each DataTable, it's DataColumns and DataRelations based on the available meta data (DataColumn.DataType, ColumnName, MaxLength, AllowNull, etc.) to build the DDL.

However, both approaches would only be good for new schemas, if you wanted to generate DDL for schema changes, then to be honest you'd probably save a lot of time by using an existing product.