Creating A SQL Server Database From Visual Studio With Entity Framework

Introduction

In this article, I will teach you the basics of Entity Framework by creating a simple SQL- SERVER database from VISUAL STUDIO. This article is for those programmers who have some knowledge in ASP.NET applications, but don't have it enough in Entity Framework. I will teach you:

• How to create an Entity Data Model (EDM) in Visual Studio
• How to export that model to a SQL-Server database
• How update EDM from SQL-Server database

Background

There are many sites in internet showing us the basics of Entity Framework through pre-defined SQL scripts. However, there are very little information about how generate those scripts, so I want to teach you how generate SQL scripts in Visual Studio from zero.

Using the code

The application is created using Visual Studio 2015. We are going to create an initial project and I'll show you how to do it step by step.

Create a New Project in Visual Studio

Create a new "Windows Forms Application" as shown below:

Click OK.

Create a New Project inside our Solution

1. Right-click on the Solution name, select Add => New Project.
2. Choose Class Library, type a Name and click OK.

   

Creating an Entity Data Model

We are going to create the entity data model. Follow the steps below:

1. Right-click on the project name, select Add => New Item.
2. Select the Data tab from the left panel, and then select ADO.NET Entity Data Model.
3. Rename it as MyFirstEFProject.edmx. Click Add.
4. Select Empty EF Designer model in the Entity Data Model Wizard. Click Finish.

If you have followed the above steps successfully, you will see your EDM in design mode.

Adding a new Entity

1. Right-click on the EDM design mode, select Add New => Entity...
2. Fill the text box as shown below:

   

3. Click Ok.

   You have created an entity named Client whose Property name is IdClient. In other words, you have created an entity which will become on a table named Client whose Primary Key is IdClient.

Adding new properties:

In this point, we'll see how adding properties to our entity. Notice that those properties will be later new fields on the table.

1. Right-click on the entity Client, select Add New => Scalar Property.
2. Fill the Properties window of the Solution Explorer as shown below:

   

   NOTE: In this case Entity Key Property is false because is a simple field and it is true on IdClient because this will be our Primary Key.

3. Repeat the steps 1 and 2 and create new scalar properties named:
   • LastName
   • NIF
   • Phone

If you have followed the above steps successfully, you will see your EDM in this way:

We have just created a simple EDM in Visual Studio.

Exporting an Entity Data Model to a SQL-Server database

We are going to export the Entity Data Model that I created in the last step to a SQL-Server database:

1. Open the SQL Server Management Studio.
2. Right click on Databases, select New Database
3. Type MyFirstEF on Database name. Click OK.
4. We're going to set the connection properties for your database from Visual Studio:
5. Right-click on the EDM design mode, select Generate Database from Model
6. Click on New Connection
7. Type your SQL-Server host name on Server name field.
8. Select the database you created in Step 3 on the field Select or enter a database name. Click OK.

   

9. Click Next.
10. Choose a version of Entity Framework that you use (if you use two or more). Click Next.

    Congratulations! You have just generated the script to create the database.

    SET QUOTED_IDENTIFIER OFF;
    GO
    USE [MyFirstEF];
    GO
    IF SCHEMA_ID(N'dbo') IS NULL EXECUTE(N'CREATE SCHEMA [dbo]');
    GO
    
    -- --------------------------------------------------
    -- Dropping existing FOREIGN KEY constraints
    -- --------------------------------------------------
    
    
    -- --------------------------------------------------
    -- Dropping existing tables
    -- --------------------------------------------------
    
    
    -- --------------------------------------------------
    -- Creating all tables
    -- --------------------------------------------------
    
    -- Creating table 'Client'
    CREATE TABLE [dbo].[Client] (
        [IdClient] int IDENTITY(1,1) NOT NULL,
        [Name] nvarchar(max)  NOT NULL,
        [LastName] nvarchar(max)  NOT NULL,
        [NIF] nvarchar(max)  NOT NULL,
        [Phone] nvarchar(max)  NOT NULL
    );
    GO
    
    -- --------------------------------------------------
    -- Creating all PRIMARY KEY constraints
    -- --------------------------------------------------
    
    -- Creating primary key on [IdClient] in table 'Client'
    ALTER TABLE [dbo].[Client]
    ADD CONSTRAINT [PK_Client]
        PRIMARY KEY CLUSTERED ([IdClient] ASC);
    GO

    

11. Click Finish.
12. You can copy the previous script and execute it on the SQL-Server Management Studio as a New Query, but I prefer to do that from Visual Studio.

    

13. Click on My FirstEPProject.edmx.sql
14. Click on the icon Execute.

    

15. Type the credentials to connect with your database server. Click Connect.

You have just exported the EDM to a SQL-Server database.

In SQL-Server, you can see the following model:

Updating model from database

Imagine that we decide add one column in our table Client in the SQL-Server database. For example, column Address is added.

If we want update this change in the EDM:

1. Right-click on the EDM design mode, select Update Model from Database
2. Select tab Refresh. Click Finish.

Items found in the database will be refreshed in the model.

Property Address appears at the end of Entity Client.