Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

James Serra's Blog

James is currently a Senior Business Intelligence Architect/Developer and has over 20 years of IT experience. James started his career as a software developer, then became a DBA 12 years ago, and for the last five years he has been working extensively with Business Intelligence using the SQL Server BI stack (SSAS, SSRS, and SSIS). James has been at times a permanent employee, consultant, contractor, and owner of his own business. All these experiences along with continuous learning has helped James to develop many successful data warehouse and BI projects. James has earned the MCITP Business Developer 2008, MCITP Database Administrator 2008, and MCITP Database Developer 2008, and has a Bachelor of Science degree in Computer Engineering. His blog is at .

Entity Data Modeling with Visual Studio

While there are a lot of tools out there for data modeling, there is one option that is free if you have Visual Studio.  It’s called the Entity Data Model Designer.  It works well if you need a quick-and-dirty way to create a logical model of tables and have the DDL automatically created to build the physical tables in one step.  Or if you want to create a logical model from existing physical tables.

This solution may also solve your needs if you have been using Visio for your data modeling.  With Visio 2010 it’s possible to extract a database schema from an existing SQL Server database (“reverse engineer”) as well as a way to build CREATE TABLE scripts from your resulting diagram (via the workaround Create SQL Server scripts with Visio 2010).  But with Visio 2013 these options are gone (see the Deprecations section at UML and Database diagrams in the new Visio).

For the steps below I am using Visual Studio 2012, but other versions should be similar.  The purpose of the Entity Data Model Designer is to allow you to build logical tables from scratch, then create the DDL that can be run in SQL Server to build the physical tables.  Or, you can generate the logical tables from an existing SQL Server database, modify them, and update the physical tables.  One other feature is that it creates the .NET code to interact with the tables if you are building an application (hence the template name “ADO.NET Entity Data Model”).  In our case, we do not need that so well skip it.

When installing Visual Studio 2012, make sure to choose to install either Microsoft SQL Server Data Tools or Microsoft Web Developer Tools in order to get the Entity Data Model Designer (info).

  1. Open Visual Studio
  2. Create a project or open an existing project (must be Visual Basic, Visual C# project, or Console Application)
  3. Right-click the project and choose Add -> New Item…
  4. Under Visual C# Items select “Data”
  5. Select the template “ADO.NET Entity Data Model”
  6. Give it a name and click “Add”
  7. Select “Generate from database” or “Empty model”
  8. If “Generate from database” selected enter connection info, choose the database objects and done!

The model is stored as a “.edmx” file.

When you complete the model, right-click and choose “Validate” to make sure there are no errors.

If you want to create the DDL, right-click and choose “Generate Database from Model…”.

If there were changes to the physical tables after you created the model, right-click and choose “Update Model from Database…”.

If this solution is not to your liking, the other free option is to use the built-in Database Diagram tool in SSMS (see Getting started with SQL Server database diagrams and video Creating a Database Diagram in SSMS – SQL School Video).

If you don’t mind paying for a tool, some of the more popular one’s are: CA ERwin, ER/Studio, SAP Sybase PowerDesignerDeZign for Databases, and dbForge Studio.

More info:

ADO.NET Entity Data Model Designer

Creating ADO.NET Entity Data Model in VS 2012

Introduction to Model First development using Entity Framework

Video Creating an Entity Data Model from a Database

Comments

Leave a comment on the original post [www.jamesserra.com, opens in a new window]

Loading comments...