Reverse and forward engineering databases in Visual Studio 2010

In a previous post I’ve described how you can reverse and forward engineer a database from a Microsoft SQL server to (and from Microsoft Office Visio 2010. But what if you’re using Typed Datasets in Microsoft Visual Studio?

Same as with Visio, reverse engineering an existing database from a SQL database is built-in and therefore fairly easy. So a best-practice is to design the database model in Visio, forward engineer it to a SQL database and import it in Visual Studio.

If you want to forward engineer a Microsoft ADO.NET compatible DataSet Schema File (XSD) to a (Microsoft SQL) database you can use the XSD2DB tool created by Alexsis Smirnov.

In this blog post I will demonstrate how to reverse engineer a database from Microsoft SQL server, and how to forward engineer a XSD back to a Microsoft SQL server.

How to reverse engineer a database from Microsoft SQL to Microsoft Visual Studio 2010

Start Microsoft Visual Studio 2010 and create a new project. For this example I’ll be using a Visual Basic Windows Forms Application, but the procedure is the same for other projects.

 

Rightclick on the project and add a new item (Ctrl+Shift+A). Search for ‘DataSet’ in the ‘Search Installed Templates’ on the right (name the dataset) and create the item.

Now expand the Server Explorer (if the Server is not visible, click on View > Server Explorer), right click Data Connections and add a Data Connection. In the ‘Add Connection’ wizard specify the details of the connection like the server name (not localhost or 127.0.0.1), credentials and database name.

Now you have added the connection to the database you can expand the database and the objects you wish to reverse engineer in your DataSet Schema File. This can easily be done by dragging-and-dropping the objects on the ‘DataSet Designer’.

You now have successfully reverse engineered a SQL database to a DataSet Schema File in Microsoft Visual Studio 2010.

 

 

How to forward engineer a DataSet Schema File from Microsoft Visual Studio 2010 to Microsoft SQL

Please first download and extract the XSD2DB before proceeding..

Open a command prompt and browse to the location where you’ve extracted the XSD2DB tool. In my demo I’ve extracted the files to C:\Scripts\XSD2DB.)

If you wish to save a DataSet Schema File (XSD) to a Microsoft SQL server you should specify the following command:

Where SQLServer is the name of your SQL server, NewDB the name of the new database and C:\scripts\DataSet1.xsd the location of your DataSet Schema File (XSD).

You now have successfully DataSet Schema File (XSD) to a Microsoft SQL server in Microsoft Visual Studio 2010.

 

 

REMARK

Even if the database does not yet exist use the –f [force] parameter, otherwise it will fail.

If you’ll try to connect to 127.0.0.1 ,or did not enable remote login connections to the server, you’ll probably get the following error.

System.Data.SqlClient.SqlException: Login failed. The login is from an untrusted domain and cannot be used with Windows authentication.

More information about this error can be found here.