Er Diagram Visual Studio 2012

Reverse Engineering a Database Schema using Microsoft Visual Studio 2012

Microsoft used to offer reverse engineering features in the Visio product line. However starting with Visio 2013 this functionality was removed. Reverse engineering of database schema can still be done using Visual Studio 2012 using the Entity Framework Designer. The main idea here is to provide mappings between an object oriented Visual Basic, C# or C++ application and relational databases. This approach is called theDatabase First workflow in which an existing database is reverse engineered into the Entity Framework'sEntity Domain Model. Here are some blogs about this general approach and a video on this approach.

To complete this tutorial you will need to have a connection to a database with an existing schema. The example shown here will use an Oracle database.

Run Visual Studio 2012
Make a connection to the database. In this case we will use the Server Explorer to create a new database connection. Start by pulling down theView menu and selectingServer Explorer
Right-click on theData Connections item and selectAdd Connection. The Add Connection dialog box will appear. Change the Data Connection to match the target DBMS. The example below uses the ODP.NET Managed Driver for Oracle.
Fill in the database connection information. For this example we are using the Oracle ODP.NET Database Managed Driver
Click theTest Connection button to test the connection to the database.
Once everything is working, click theOK button and the new data connection will be saved
Next we will create a new C# Application. Pull down theFile menu and selectNew and thenProject. Choose a C# Console Application and name it "MyDatabaseApp"
Click theOK button and the new application will be created
Display the Solution Explorer (pull down theView menu and selectSolution Explorer)
Right-click on theMyDatabaseApp entry and chooseAdd from the pop-up menu.
Then selectNew Item from the fly-out menu.
Click on theData category underVisual C# Items on the left side of the dialog box. Then click on theADO.NET Entity Data Model item. Change the name of the model to reflect the underlying database. Here I am usingOrderEntry.edmx as the name. Click theAdd button.
The Entity Data Model wizard will launch. At the first screen, highlight the choice forGenerate from database and click theNext> button
On the "Choose Your Data Connection" screen, select the data connection previously set up, or specify a new connection to a database. Select the option to exclude sensitive data from the connection string. Change the name of the entity connection settings to reflect the data source. In this example I am usingOrderEntryContext. When finished click theNext> button
Select the database objects to be included in the new model. In this case each of the regular (non-nested) tables is selected. Give a new name for the model. In this example I am usingOrderEntryModel. When finished click theFinish button
One the wizard completes the diagram will be displayed. Entities can be re-arranged on the screen to achieve a nice layout.
Be sure to save the diagram and the work.
For the C# coders:
1. TheOrderEntry.Context.cs file contains theDerived context representing the connection to the database through which data will be retrieved and saved. TheDbSet properties map on to each table in the database and provide a means to retrieve and save data to those tables.
2. TheOrderEntry.tt file contains instructions to generate a collection of C# files that correspond to each of the tables in our database. Each file contains a set of properties that map class properties on to database table columns, and navigational properties that represent relationships.
Below is a sample of code to use a LINQ query on the Customers table