Can I create a database schema from a DataSet and/or .xsd file?

I have some questions about creating SQL Server CE databases. Based on my experiments and what I've read on these forums, it looks like there are a couple ways to create a database schema. I can edit the database schema via the Server Explorer in Visual Studio, or use an external program like SQL Server Management Studio and somehow convert those files to .sdf files.

I find Visual Studio's built in tools to be cumbersome to use and limited in functionality, and using SSMS seems like a roundabout way of approaching the problem. I understand Microsoft will be releasing better tools with Orcas, but in the meantime, I'm wondering if there are alternative ways to generate database schemas.

For instance, I find Visual Studio's DataSet designer fairly easy to use. The DataSet designer generates schema definitions (.xsd files), and an instantiated DataSet can both read and write schema definitions via Read/WriteXMLSchema. Furthermore, DataAdapter's Fill and FillSchema methods can be used to push a schema from a database to a DataSet. So, can I somehow go the other direction and push a schema from a DataSet to a database? It seems like all the tools are there...

For example, if I create the DataSet schema, could I use a small app to create a new .sdf file, instantiate a DataSet, write the schema from the DataSet to the database, and then save the .sdf file? Or given the generated .xsd file, is there any way to create a SQL database from that?

Thanks in advance for any replies.

You can do that by loading schema into DataSet and looping through DataSet's tables, columns, relations, etc., generating respective SQL command and executing them. E.g. you loop through tables in DataSet and execute "Create Table" command for each.

I would not say it’s easier than using designers but by all means you can do it.

|||

Thank you for the reply, Ilya. I agree that doesn't necessarily sound easier than using designers.

If you would indulge me, perhaps you can answer a related and probably more appropriate question. What options are available for creating databases at run time?

The samples I've seen in the SDK and online assume that an application will have access to an existing database. But what if the application is responsible for creating the database? For example, I was using Microsoft Money a few minutes ago, and it occurred to me that such an application would be well suited for SQL Server CE. At its heart, the content is just a relational database. I can imagine Money's .mny files just being rebranded .sdf files. But Money doesn't ship with an existing database. Rather, it allows you to create your own via a traditional File->New command. I imagine the code would look something like the following in CE:

String connString = "Data Source='NewDatabaseFile.sdf'";

SqlCeEngine engine = new SqlCeEngine( connString );

engine.CreateDatabase();

But then what? You have an emtpy file and a database with no schema. If the answer is to run a bunch of CREATE TABLE commands or loop through a DataSet like your example above, then so be it. It would be a shame, however, if there were no way to leverage the database design tools available when creating databases at runtime.

I apologize if I am being naive with these questions. I am new to CE, and am just trying to get a sense of what can be done with it.

Thanks.

|||

Yes, you need to run appropriate SQL command to create schema at runtime after you’ve created the database.

If you need runtime designers then you'd need to implement these designers yourself. They would translate the schema in whatever form to... yes, SQL commands.

sql