Connect Bot Framework to DB [Part 1] | READ data from DB using Entity Framework

This is part 1 of the series “Connect Microsoft Bot Framework to Database“. Before going to Bot framework connection to DB, we will learn how to get data using Entity Framework.

Entity Framework Core is a modern object-database mapper for .NET. It supports LINQ queries, change tracking, updates, and schema migrations. EF Core works with many databases, including SQL Database (on-premises and Azure), SQLite, MySQL, PostgreSQL, and Azure Cosmos DB.

Pre-requisites

Visual Studio
Data Storage and Processing Tool set
SQL Server Management Studio (If 2nd pre-requisite not present)

Visual Studio link can be found in Downloads page.

Video

Creating Blank Solution in Visual Studio

Launch Visual Studio and Create a new Project using the template Blank Solution.

Provide the solution name and create.

We have created our solution with 0 projects in it.

Adding Project in Blank Solution

Let us add projects to our solution. Right click on Solution -> Add -> New Project. Search for Class Library (.NET Core).

Provide the name of the project as DataAccessLayer and create.

Common Web Application Architecture

Source: Micrsoft Documentation: Typical application layers. For more information, visit the official Microsoft Documentation here.

This is the project structure after adding the class library.

Delete the Class1.cs file as we don’t need it. We would be creating our own class file for Data access layer (DAL).

Creating Database in Local Server

We have two ways of creating DB –

Using SSMS (SQL Server Management Studio)
Using SQL Server Object Explorer from Visual Studio

We will see both the approach.

Using SQL Server Management Studio

Open the Microsoft SQL Server Management Studio. Connect to Server dialog box will open. Select Database Engine as Server type and give server name as “(localdb)\MSSQLLocalDB” with Windows Authentication.

Right click on Databases and select new Database.

Give the DB name as EmployeeDB and click OK.

In the Object Explorer, under Databases you will see the newly created database.

Right click on the EmployeeDB and click New Query.

Write the below query to create a Employee Table and feed some data to it.

CREATE TABLE [EMPLOYEE]
(
	EMP_ID VARCHAR(10) CONSTRAINT pk_emp_id PRIMARY KEY,
	EMP_NAME VARCHAR(50) NOT NULL
)
GO

INSERT INTO EMPLOYEE VALUES ('E1001', 'Steeve')
INSERT INTO EMPLOYEE VALUES ('E1002', 'Rob')
INSERT INTO EMPLOYEE VALUES ('E1003', 'Bob')
INSERT INTO EMPLOYEE VALUES ('E1004', 'Jane')

Before executing the query, make sure EmployeeDB is selected from the dropdown of available databases.

Below is the message you will get for successful execution of the query.

You can open the dropdown of EmployeeDB -> Tables -> Columns to see the columns created for your Employee table.

You can also see the data present in your table by right clicking on Employee table and Select the top 1000 rows.

Using SQL Server Object Explorer from Visual Studio

This tool will only be available if you have selected Data Storage and processing toolset workload while installing Visual Studio. Click on View -> SQL Server Object Explorer.

The process is exactly the same for creating the DB and executing the query. Here you have to select the local DB server and create a database and write a query.

Since we have already created the DB and fed the data in it, let us now follow the process of Reverse Engineering.

Scaffolding

Reverse engineering is the process of scaffolding entity type classes and a DbContext class based on a database schema. It can be performed using the Scaffold-DbContext command of the EF Core Package Manager Console (PMC) tools or the dotnet ef dbcontext scaffold command of the .NET Command-line Interface (CLI) tools.

To start with, install the following Nuget Packages –

Microsoft.EntityFrameworkCore.SqlServer
Microsoft.EntityFrameworkCore.Tools
Microsoft.Extensions.Configuration.Json

Open the Package Manager Console (PMC): Tools -> NuGet Package Manager -> Package Manager Console.

Type the Scaffold-DbContext command. Make sure to provide the server name in Data Source and DB name in Initial Catalog.

Scaffold-DbContext -Connection "Data Source =(localdb)\MSSQLLocalDB;Initial Catalog=EmployeeDB;Integrated Security=true" -Provider Microsoft.EntityFrameworkCore.SqlServer -OutputDir Models

Before running, make sure default project is selected as DataAccessLayer.

After successful execution of the command, a new folder is created in the project directory with the name Models. It contains the class of the same name as of table with properties same as columns.

Open the DbContext class and comment the warning message in the OnConfiguring method.

Currently, the connection string is hardcoded. It is always a good practice not to keep the configuration details inside the code. We will use the appsettings.json file to keep these details. Create a file with the above name in the project directory.

Add the below content in appsettings.json file.

{
  "Logging": {
    "LogLevel": {
      "Default": "Warning"
    }
  },
  "AllowedHosts": "*",
  "ConnectionStrings": {
    "DBConnectionString": "data source=(localdb)\\MSSQLLocalDB;initial catalog=EmployeeDB;Integrated Security=true"
  }
}

Modify the OnConfiguring method in DbContext class to read the connection string from above file.

protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            var builder = new ConfigurationBuilder()
                              .SetBasePath(Directory.GetCurrentDirectory())
                              .AddJsonFile("appsettings.json");
            var config = builder.Build();
            var connectionString = config.GetConnectionString("DBConnectionString");

            if (!optionsBuilder.IsConfigured)
            {
                //#warning To protect potentially sensitive information in your connection string, you should move it out of source code. See http://go.microsoft.com/fwlink/?LinkId=723263 for guidance on storing connection strings.
                optionsBuilder.UseSqlServer(connectionString);
            }
        }

Import the following :

using Microsoft.Extensions.Configuration;
using System.IO;

Create a new class file with name UserRepository.cs and add the following code.

using DataAccessLayer.Models;
using System;
using System.Collections.Generic;
using System.Text;
using System.Linq;

namespace DataAccessLayer
{
    public class UserRepository
    {
        EmployeeDBContext context;
        public EmployeeDBContext Context { get { return context; } }
        public UserRepository()
        {
            context = new EmployeeDBContext();
        }

        public Employee FetchEmployeeDetails(string empid)
        {
            Employee employee;
            try
            {
                employee = (from r in Context.Employee
                            where r.EmpId == empid
                          select r).FirstOrDefault();
            }
            catch (Exception)
            {
                throw;
            }
            return employee;
        }
    }
}

Testing the Connection

Add a new project to the solution using Console App (.NET Core) template.

Add the following code to Program.cs file to test the connection by reading the data from the DB. Here we are finding the employee name associated with emp id “E1003” and printing on console.

using DataAccessLayer;
using DataAccessLayer.Models;
using System;

namespace ConsoleApp
{
    class Program
    {
        static void Main(string[] args)
        {
            UserRepository userRepository = new UserRepository();
            Employee employee = userRepository.FetchEmployeeDetails("E1003");
            Console.WriteLine(employee.EmpId + " - " + employee.EmpName);
        }
    }
}

Before running the project, right-click on Console App and set it as a startup project. Run the console app using Cntrl+F5. Below is the output you will get from the console.

The exception is telling that, it is not able to find the appsettings.json file. To solve this, right-click on appsettings.json and click on Properties. Select Copy Always for Copy to Output Directory property.