Database First Workflow

In this blog, I am going to show you Database Workflow in action.so I am going to start with creating a simple table and then our entity framework generates a domain class bass on that table.so let’s get started.

Tools I used Written bellow.

1. Visual Studio 2017
2. SQL Server 2017 Developer edition.
3. Code Repo

So here in sequel server management studio, I create a Database called DatabaseFirstDemo and remember you should make StudentId as Primary Key otherwise you get an error. I missed that one on above Picture.

I am going to create a new table and use this table for storing the Student information.

When the table is ready, now here is the tricky part of the database first approach. we have this database here locally but in order to bring different databases like a test database or a production database to this version, we need a mechanism. 

There are tools over there that can compare two databases and bring them to the same version but in my experience that are not always reliable and can some time cause issues due to some dependences of tables and records. It more reliable, flexible but functionally more Manuel approach is to create a change script every time when we make a change in the database. Then we can store this change scripts somewhere in the repository and run them on the database to bring them to the current version.so in this blog I am going with this approach.

1. First Click the Generate Script.
2. New Window will appear having information about script then click Save.

So, I am going create a change Script here and I am going to store this as, here is a sequence number and a description and finally save this table.

Now I finally save the table. The table is ready in my database.

Now I go to the visual studio create a new project and import this database and its tables into my project.

I start with the new project. I am going to stick with the console application for now because we don’t want to be distracted by the complexity of the web application or a WPF application so just keep it simple focus on Entity Framework.

With the new project first step is to install Entity Framework and to do that use package manager console.

1. Click on Tool.
2. Then Click on NuGet Package Manager.
3. At last Click on package manager Console.

Enter the command in package manager Console install – Package EntityFramework

So here I got entity framework version 6.2.0 Now we go to the solution explorer

Now move to the Solution Explorer .

1. Right Click on Project
2. Click on ADD
3. Click on New Item

After Clicking New Item, the new Window will open Where you select ADO.NET Entity Data Model

So, this is the going over a conceptual model that represents the mapping between database tables and Domain classes, So I am giving it the name like student model After this the new window will open.

In this window, I am using EF Designer from Databases because I already have a database and click Next.

Here we need to specify the new Connection to the database.

Here we select The Data source if you not selected and click OK. I am using Microsoft SQL Server.

Here you should give a server Name whatever you set and Select the database from the Dropdown and test the connection and click the Ok button.

Click the Next

At this point Entity framework look at our database discover the tables, views and store procedures and functions we have there. currently, we have only Student Table, so I tick all my tables and leave the rest as it and click finish.

You get the security warning about running this template can postnatally harm your computer.

Don’t worry about it ……just click do not show it again because visual studio tries to run the template to generate some code. I show you just in a second.

Ok there is over entity data model which is stored in the file with the .edmx extension and here you see the class called student which is exactly like the table I created earlier

Let’s go the solution explorer to understand more deeply 

Basically, everything under StudentModel.edmx is Designer generated Code.

 StudentModel.Context.tt: So .tt stands for T for Template. it’s a way to generate a code based on the template when we expand this, we find actual generated Code. basically, it is a Context template

 StudentModel.Context.cs: This is the Actual generated code.

So here is the C# class that entity framework generated for you and this class drive from the DbContext. DbContext is the class that is an abstraction over the database.so it provides the simple API to load the data from or save the data to the database.

We have also a property of type DbSet called Students and DbSet represents a table in the database. So, because in the database we have a table called Students here we have DbSet of type Student Called Students.

Now this Student Class you see here is the main generated code.

StudentModel.tt: so, it is the student model Template. Below is the template code

When we Expand it, we find the Student Class

Student.cs: this is the actual code which is generated or mapped from the Student table in the database

When we look at this class, it has four properties based on the column that I currently have in my database.

So, the key thing here is we started with the database, we created our table and then we imported that to the entity data model. Every time I want to change my model I start with the database and then I come into the .edmx file or entity data model and refresh it.at this point, the entity framework updates my domain classes as you see above

Here is the Database First Flow

Now let me show you how to use DbContext to work with the database and this applied both Database First and Code first.

Now I go to the Program.cs and write some simple code 

First, we create the instance of DbContext Class which is called DatabaseFirstModel Entity.

Second, we create New Student and initialized here. Now In real-world application, most offend we use identity column so we don’t have to specify the StudentID here but for now, it doesn’t matter because I just want to show a workflow

Third, we add this student to the DbSet. At this point, changes are in the memory nothing is committed in the database yet.

Fourth, save the changes and run the application and Done as easy as that.

               Don’t have to write the stored procedures

               Don’t have to work with ADO.NET CLASS like Squeal connection and the sequel

                Entity framework to care of all of this for me

Let’s verified its worked. So, I am going to SQL Management Studio and just have a look

Here we go we got this record in the database. Now reading the data from the database as easy as what you saw, and I will cover it in my Next blog

So, this was the database first workflow in the action.In blog i will show you the code first WorkF