Creating console application will remain same as earlier version with slight changes in flow, because in visual studio 2019, we still can create non .net core application
Here we create .net core console application and then use entity framework core to perform database related operations.
Key elements to learn in this entity framework in .net core console application example.
Open your database, create the table you want. for example here i have created a student table. i will use this table to work with entity framework in my console application.
CREATE TABLE [dbo].[tbStudent]( [StuId] [bigint] IDENTITY(1,1) NOT NULL, [Firstname] [varchar](50) NOT NULL, [Lastname] [varchar](50) NULL, [Email] [varchar](50) NULL, [Mobile] [varchar](50) NULL, [StreamId] [int] NULL, [Score] [int] NULL, [Address] [varchar](50) NULL, [Subjects] [varchar](50) NULL, PRIMARY KEY CLUSTERED ( [StuId] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY]
Now create class for creating database connection string, so we can use in multiple places easily.
public class DbConnection { public static string ConnectionString { get { return ($"Server={ServerName};Database={DatabaseName};User ID={UserName};Password={Password};Trusted_Connection=False;MultipleActiveResultSets=true;"); } } }
To read database configuration details from appsetting.json in .net core console application we need to install following three Configuration libraries using nuget package manager.
using Microsoft.Extensions.Configuration; using Microsoft.Extensions.Configuration.FileExtensions; using Microsoft.Extensions.Configuration.Json;
Here is the complete tutorial how to read appSettings.json in .Net Core Console application, Please refer the link; I will skip the configuration reading details in this example.
If you have previous experience of working with entity framework, then probably you are familiar with DbContext file, and here you may learn few additional features.
The DbContext class must be inherited from Microsoft.EntityFrameworkCore.DbContext
class.
using Microsoft.EntityFrameworkCore; using Microsoft.Extensions.Configuration; using Microsoft.Extensions.Options; using System; using System.Data.SqlClient; public class EFContext : DbContext { public EFContext() : base() { } protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder) { if (!optionsBuilder.IsConfigured) { optionsBuilder.UseSqlServer(DbConnection.ConnectionString1); } base.OnConfiguring(optionsBuilder); } protected override void OnModelCreating(ModelBuilder modelBuilder) { base.OnModelCreating(modelBuilder); } }
Notice, how database connection string is being set using option builder extension method UseSqlServer optionsBuilder.UseSqlServer(DbConnection.ConnectionString1);
You may read more about Entity Framework Core DbContext Class
Now we create business entity that will talk to database with the help of dbContext class, ideally the object structure should match the database structure, but not necessarily always, the business entity can have different number of fields and name in business class, but the type must be same as the field we want to match in database table.
We learn more in details about business entity design, but now let’s look at simple example of reading and adding data in database.
, so here is the student class that will deal with tbStudent
table in database
public class Student { public Student() { } public long StuId { get; set; } public string Firstname { get; set; } = "Not Set"; public string Lastname { get; set; } = "Not Set"; public int? StreamId { get; set; } public int? Score { get; set; } = 0; public string Address { get; set; } public string Mobile { get; set; } public string Email { get; set; } public string Subjects { get; set; } = "All"; }
After creating each business entity class we need to add register that class in dbcontext class,
so let’s open the above EFContext class and create a dbset property DbSet<Student> tbStudent { get; set; }
then add the entity in OnModelCreating method
like mb.Entity<Student>().HasKey(s=>s.StuId)
public class EFContext : DbContext { protected override void OnModelCreating(ModelBuilder modelBuilder) { modelBuilder.Entity<Student>().HasKey(s=>s.StuId); base.OnModelCreating(modelBuilder); } public DbSet<Student> tbStudent { get; set; } }
Notice, in above code the HasKey property .HasKey(s=>s.StuId);
is the primary key of database table.
Everything is ready, now we write an DTO class (data transfer object), initially may look like why do we need that, why not directly write code using dbcontext object where we need to make database call, yes we can do that too! But the disadvantage would be whenever you want to make small changes, you have to do it in multiple places.
Think of situation like getting the student list or adding a new student, those are common functionality can be consumed in multiple location, so if we have a DTO class, then whenever any new change comes, we have to do that in once place, which will be less time consuming and easy to test.
So, let's create dto class to read and write data with the help of dbcontext class we have already created.
namespace WebTrainingRoom.DTO { public class StudentDTO : IDisposable { public Student AddStudent(Student s) { using (EFContext context = new EFContext()) { context.tbStudent.Add(s); context.SaveChanges(); } return s; } public List<Student> GetAllStudents() { List<Student> list = new List<Student>(); using (var context = new EFContext()) { list = context.tbStudent .ToList<Student>(); } return list; } } }
Now let's call the above method in your console code, Here is how you can call the dto method to print all students name in your database table tbStudent
List<Student> list = null; using (StudentDTO dto = new StudentDTO()) { list = dto.GetAllStudents(); } foreach (Student s in list) { Console.WriteLine(s.Firstname); }
While developing entity framework functionalities, make sure you have implemented right error handling mechanism, you may be interested to read following posts!