Author : HASSAN MD TAREQ

MSSQLLocalDB

For simplicity, gonna use MSSQLLocalDB

  • MSSQLLocalDB is a named instance of LocalDB inside Visual Studio
  • MSSQLLocalDB is on-demand Database, that means it will sutomatically start when needed and will automatically be shutdown when there is no active connection
  • Project13 is also similar to MSSQLLocalDB but used by Visual Studio Data Tools where MSSQLLocalDB is used by app

Create database in MSSQLLocalDB

  • Menu > View > SQL Server Object Explorer
  • (localdb)\MSSQLLocalDB > Databases > Right Click
  • New Database > Create EmployeeDB

EF core data project

See: modify the DbContext class so that connection string can be configured in Startup.ConfigureServices()

ASP.Net core project

  • See first: </entity-framework-core/using-data-layer-in-application#asp-net-core-app>
  • Create an ASP.Net core project to which we will use IRepository
  • IRepository implementation will use EF Core Data Project (i.e. MyApi.Data is EF core data project that contains all Entity classes and DbContext Class)
  • ASP.Net core has built-in DI container
  • we will register DbContext to DI container so that DbContext is injected into IRepository implementation

Connection string in appsettings appsettings.json

{

  "ConnectionStrings": {
    "EmployeeDBConnection": "Server=(localdb)\\mssqllocaldb;Database=EmployeeDB;AttachDbFileName=%CONTENTROOTPATH%\\AppData\\EmployeeDB.mdf;Trusted_Connection=True;MultipleActiveResultSets=true"
  },
  
  
}

Notes:

  • Remove AttachDbFileName=%CONTENTROOTPATH%\\AppData\\EmployeeDB.mdf portion if you don’t want to attach existing .mdf
  • I have an existing EmployeeDB.mdf with sample data and therefore using AttachDbFileName in connection string

DataUtils

public static class DataUtils
{

	public const string CONNECTION_STRING_KEY = "EmployeeDBConnection";

	public const string CONTENT_ROOT_PLACE_HOLDER = "%CONTENTROOTPATH%";

	public static string ResolveDbConnectionString(IConfiguration Configuration, string contentRootPath, string connectionStringKey = CONNECTION_STRING_KEY) { 
	
		var connectionString = Configuration.GetConnectionString(connectionStringKey);

		if (connectionString.Contains(CONTENT_ROOT_PLACE_HOLDER))
		{
			connectionString = connectionString.Replace(CONTENT_ROOT_PLACE_HOLDER, contentRootPath);
		}

		return connectionString;
	}
}

Repository

  • EmployeeDbContext and Entity classes are in EF core data project (i.e. MyApi.Data)
  • DbContext will be injected into EmployeeRepository by ASP.Net DI Container

IEmployeeRepository.cs

public interface IEmployeeRepository
{
	long Create(Employee newEntity);
	Employee Read(long id);
	void Update(Employee modifiedEntity);
	void Delete(Employee entityToDelete);
}

EmployeeRepository.cs

public class EmployeeRepository : IEmployeeRepository
{

	private readonly EmployeeDbContext _context;

	public EmployeeRepository(EmployeeDbContext context) // Constructor DI Injection
	{
		_context = context;
	}

	public long Create(Employee newEmployee)
	{
		_context.Employees.Add(newEmployee);
		_context.SaveChanges();

		return newEmployee.Id;
	}
	
	public Employee Read(long id)
	{
		return _context.Employees.Find(id);
	}

	public void Update(Employee employeeToUpdate)
	{
		_context.Employees.Update(employeeToUpdate);
		_context.SaveChanges();
	}

	public void Delete(Employee employeeToDelete)
	{
		_context.Employees.Remove(employeeToDelete);  //_context.Remove<Employee>(new Employee { Id = id });
		_context.SaveChanges();
	}
}

DI injection setup

Startup.cs (in ASP.Net core project)

public class Startup
{
	public IConfiguration Configuration { get; }
	private readonly string _contentRootPath;

	public Startup(IConfiguration configuration, IHostingEnvironment env)
	{
		Configuration = configuration;
		_contentRootPath = env.ContentRootPath;
	}

	public void ConfigureServices(IServiceCollection services)
	{
		var dbConnectionString = DataUtils.ResolveDbConnectionString(Configuration, _contentRootPath);
		services.AddDbContextPool<EmployeeDbContext>(options => options.UseSqlServer(dbConnectionString));

		services.AddScoped<IEmployeeRepository, EmployeeRepository>();
		services.AddScoped<IEmployeeService, EmployeeService>();
		
		services.AddAutoMapper(typeof(Startup));

		// ... ... ...

	}
	
	// ... ... ...
}

Using repository

  • since repository is registered to DI container, EmployeeDbContext will be injected automatically and IEmployeeRepository instance will be created by DI container
  • Service class uses constructor injection to get IEmployeeRepository
  • Controller uses IEmployeeService in the same way (constructor injection)

EmployeeService.cs

public class EmployeeService : IEmployeeService
{
	private readonly IEmployeeRepository _employeeRepository;
	private readonly IMapper _autoMapper;

	public EmployeeService(IEmployeeRepository employeeRepository, IMapper autoMapper)
	{
		_employeeRepository = employeeRepository;
		_autoMapper = autoMapper;
	}
	
	// ... ... ...
}