Database First Demo

By | October 29, 2018

We will create a simple database table. Then we will use entity framework to generate a domain class based on that table. The whole process is given below-

  • Open Microsoft SQL Server Management Studio.
  • From Object Explorer Right Mouse click on Database folder -> New Database as shown below-

  • Give the Database Name -> Select  Database Storage Path -> Click Ok Button as shown below-

So our database has created that shown as follows-

  • Right mouse click on Table Folder -> Select New Table.

  • Add Table Columns as follows-

Now here is the tricky part of the database first approach. We have this database here locally. But in order to bring a different database like a Test database or a production database to this version we need a mechanism. There are tools out there that can compare to databases and bring them to the same version. But they are not always reliable and can sometimes cause issues due to dependencies between tables and records.

A more reliable, flexible and more manual approach is to create a change script every time we make any change to our database. Then we will save the change script somewhere in the repository and run them on any database to bring them to the current version.

So we will save change script as follows-

  • Right mouse click on the Table -> select Generate Change Script as follows

Save the Change Script in desired location with a given name as follows-

Here in name section we given a sequence no (001) then then given a description of the script (Create Post Table)

  • Then Click on Save Button -> Give the name -> Click OK button of the table as shown below-

Here our table name is Posts

  • Now open visual studio.
  • Go to File Menu -> New -> Project

  • Then from the left pane select Windows.
  • Then from the right pane select Console Application.
  • In Name field enter the name of the project. In our case we given the name DatabaseFirstDemo
  • Click on Browse button and locate where to store the project. In our case our project storage location is E:\Entity Framework\Practices\
  • Finally Click on Ok button as highlighted below-

  • Now our project has been created. Now we’ll install Entity Framework from Package Manager Console. So go to Tools-> NuGet Package Manager -> Package Manager Console as follows-

  • So Package Manager console will open at below of the window as follows-

  • Inside package manager console write following command to install entity framework
PM> install-package EntityFramework

After writing the command press Enter Key

  • So latest version of Entity Framework will install. The installation process will show as below-

  • Now go to Solution Explorer -> Right Click on the Project -> Add -> New Item as follows-

  • From the dialog box select ADO.NET Entity Data Model. This is going to be our conceptual model that represents the mapping between the database tables and our domain classes.

So give it a name. In our case we given the name “BlogModel” -> then click on Add button as follows-

From next window select “EF Designer From Database” because we already have a database.

After click Next button it will go to next step. Now we need to establish a connection. So click on New Connection button from this step as follows-

So another window will open where we will enter following information’s for establishing connection-

Server Name     :               (local)

Authentication  :               SQL Server Authentication

User Name         :               sa

Password            :               password of user sa

Database name :               DatabaseFirstDemo

Then we will Test the connection by clicking o Test Connection button. If connection is succeeded then it will return message Test Connection Succeeded.

  • After clicking OK button it will come to previous form. Click Next Button as follows-

  • After clicking on Next button following window will appear to us-

At this point Entity Framework looks at our database and discover the Tables, Views, Stored Procedures and Functions from the database.

Currently we only have table. Select the table and finally click Finish button.

  • So will generate the Entity Data Model. This model stored in a file with .EDMX extension as shown in follows-

Inside above diagram Post is a class which is exactly like the table that we created earlier.

  • Now go to Solution Explorer -> expand BlogModeledmx as follows-

Here all are designer generated code. There are two important parts here.

  1. Context.tt

One is BlogModel.Context.tt. Here tt stands for T4Template. It’s a way to generate code based on a template. If we expand it we see BlogModel.Context.cs class that entity framework generated for me.  Let’s open the class-

namespace DatabaseFirstDemo
{
    using System;
    using System.Data.Entity;
    using System.Data.Entity.Infrastructure;

    public partial class DatabaseFirstDemoEntities : DbContext
    {
        public DatabaseFirstDemoEntities()
            : base("name=DatabaseFirstDemoEntities")
        {

        }
  
        protected override void OnModelCreating(DbModelBuilder modelBuilder)
        {
            throw new UnintentionalCodeFirstException();
        }

        public virtual DbSet<Post> Posts { get; set; }

    }

  }

This class is derived from DbContext.

DbContext is a class that is an abstraction over the database. So it provides a simple API to load the data from database and also save data to the database.

We also have a property named Posts of type DbSet.

A DbSet represents a Table in the database. So because in our database we have a table called Posts so here we have a DbSet of type Post which is named Posts.

The Post class also generated code.

2. BlogModel.tt

Let’s expand BlogModel.tt. We see Post.cs class. Let’s open the class. The class definition is as follows-

       public partial class Post      
       {   
         public int PostId { get; set; }                     
         public System.DateTime DatePublished { get; set; }        
         public string Title { get; set; }        
         public string Body { get; set; }      
       }

This class has four properties based on the Columns of Posts table of the database.

  • So the key thing here is
  • We started with the database
  • We created our Table into the database
  • Then we imported the table into our entity data model

Every time we want to make any change in our model we will start with the database. Then we will come back to our EDMX file / entity data model and refresh it. So entity framework will update our domain classes

  • This is all about the database first workflow.

Now we will see how we can use a DbContext to work with the database- This is applicable for both Database First and Code First.

  • So from Solution Explorer Open Program.cs and write following code-
class Program
{
    static void Main(string[] args)
    {
            var dbContext = new DatabaseFirstDemoEntities();
            var post = new Post()
            {
                 Body = "Body",
                 DatePublished = DateTime.Now,
                 Title = "Title" 
            };
           dbContext.Posts.Add(post);
           dbContext.SaveChanges();
     }
}

Here we created an instance of DatabaseFirstDemoEntities DbContext class. The name of the instance is dbContext.

Then create an object of Post class named post. As PostId is the identity column inside the database so we didn’t assigned any value for this property of post object.

Then we added the post object inside Posts DbSet.

Finally we committed the post object to DbSet by calling the SaveChanges() method.

  • Now run the application.
  • Data Saved Successfully to the database as shown follows-

So we saw the code first workflow. It is very easy. We didn’t had to write any stored procedure, we didn’t had to work with ADO.NET classes like SqlConnection, SqlCommand etc. Entity Framework took care of all of these for me.

13 Total Views 1 Views Today
Md. Mojammel Haque

CSM, CSPO, CSD, CSP-SM, CSP-PO (ScrumAlliance.org)
Certification Profile Link-
https://www.scrumalliance.org/community/profile/mhaque13

Currently working as Lead Team (Application Architecture) at Raven Systems Ltd. Passion for software development especially agile practices such as TDD with in depth knowledge of Object Oriented Programming, SOLID Principles, Gang of Four Design Patterns, Some Enterprise Application Architectural Patterns. Over 8 years of software development experience ASP.NET. Has the ability to understand and transform complex business requirements into software ensuring applications are delivered on time. Also experience in non Microsoft .NET technologies such as Dapper.Net, Git, Structure Map & Angular, Bootstrap, HTML-5, CSS-3 etc.

Category: Entity Framework
Md. Mojammel Haque

About Md. Mojammel Haque

CSM, CSPO, CSD, CSP-SM, CSP-PO (ScrumAlliance.org) Certification Profile Link- https://www.scrumalliance.org/community/profile/mhaque13 Currently working as Lead Team (Application Architecture) at Raven Systems Ltd. Passion for software development especially agile practices such as TDD with in depth knowledge of Object Oriented Programming, SOLID Principles, Gang of Four Design Patterns, Some Enterprise Application Architectural Patterns. Over 8 years of software development experience ASP.NET. Has the ability to understand and transform complex business requirements into software ensuring applications are delivered on time. Also experience in non Microsoft .NET technologies such as Dapper.Net, Git, Structure Map & Angular, Bootstrap, HTML-5, CSS-3 etc.

Leave a Reply

Your email address will not be published. Required fields are marked *