In this post, we will see how to connect a Docker Web API Solution with a SQL Server Database on premises and on Cloud.
DB ON PREMISES
From our system, we open SSMS, we create a DB called TestDocker1 and then we run some sql scripts, in order to create a table called TabUser and insert some data:
USE [TestDocker1]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[TabUser](
[Id] [int] IDENTITY(1,1) NOT NULL,
[UserName] [nvarchar](50) NOT NULL,
[Password] [nvarchar](50) NOT NULL,
[DateCreation] [datetime] NOT NULL,
CONSTRAINT [PK_User] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
SET IDENTITY_INSERT [dbo].[TabUser] ON
GO
INSERT [dbo].[TabUser] ([Id], [UserName], [Password], [DateCreation]) VALUES (1, N'Username10', N'Password10', CAST(N'2020-04-15T23:25:39.873' AS DateTime))
GO
INSERT [dbo].[TabUser] ([Id], [UserName], [Password], [DateCreation]) VALUES (2, N'Username11', N'Password11', CAST(N'2020-04-14T23:26:32.013' AS DateTime))
GO
INSERT [dbo].[TabUser] ([Id], [UserName], [Password], [DateCreation]) VALUES (3, N'Username12', N'Password12', CAST(N'2020-04-13T23:30:16.103' AS DateTime))
GO
INSERT [dbo].[TabUser] ([Id], [UserName], [Password], [DateCreation]) VALUES (4, N'Username13', N'Password13', CAST(N'2020-04-13T23:30:16.107' AS DateTime))
GO
INSERT [dbo].[TabUser] ([Id], [UserName], [Password], [DateCreation]) VALUES (5, N'Username14', N'Password14', CAST(N'2020-04-12T23:30:16.107' AS DateTime))
GO
INSERT [dbo].[TabUser] ([Id], [UserName], [Password], [DateCreation]) VALUES (6, N'Username15', N'Password15', CAST(N'2020-04-13T23:30:16.107' AS DateTime))
GO
INSERT [dbo].[TabUser] ([Id], [UserName], [Password], [DateCreation]) VALUES (7, N'Username16', N'Password16', CAST(N'2020-04-10T23:30:16.107' AS DateTime))
GO
INSERT [dbo].[TabUser] ([Id], [UserName], [Password], [DateCreation]) VALUES (8, N'Username17', N'Password17', CAST(N'2020-04-07T23:30:16.107' AS DateTime))
GO
INSERT [dbo].[TabUser] ([Id], [UserName], [Password], [DateCreation]) VALUES (9, N'Username18', N'Password18', CAST(N'2020-03-25T23:30:16.110' AS DateTime))
GO
SET IDENTITY_INSERT [dbo].[TabUser] OFF
GO
Now, we open Visual Studio 2019, we create a Web API project called WebAPIDocker (we remember to check the Docker option) and then we install, in the Package Manager Console, these two libraries: Microsoft.EntityFrameworkCore.SqlServer and Automapper.
Then, we will add all components, in order to create a complete Web API service to manage the table TabUser:
BE
[TABUSER.CS]
using System;
namespace WebAPIDocker.BE
{
public class TabUser
{
public int Id { get; set; }
public string UserName { get; set; }
public string Password { get; set; }
public DateTime DateCreation { get; set; }
}
}
DAL
[IGENERICREPOSITORY.CS]
using System.Collections.Generic;
using System.Threading.Tasks;
namespace WebAPIDocker.DAL.Interface
{
public interface IGenericRepository<TEntity> where TEntity : class
{
Task<IEnumerable<TEntity>> GetAll();
Task<TEntity> GetById(int id);
Task<bool> Create(TEntity entity);
bool Update(TEntity entity);
Task<bool> Delete(int id);
}
}
[IUSERREPOSITORY.CS]
using System.Collections.Generic;
using System.Threading.Tasks;
using WebAPIDocker.BE;
namespace WebAPIDocker.DAL.Interface
{
public interface IUserRepository
{
Task<IEnumerable<TabUser>> GetAllUsers();
Task<TabUser> GetUserById(int id);
Task<bool> AddUser(TabUser objUser);
Task<bool> EditUser(TabUser objUser);
Task<bool> DeleteUser(int idUser);
}
}
[IUNITOFWORK.CS]
using System;
using System.Threading.Tasks;
namespace WebAPIDocker.DAL.Interface
{
public interface IUnitOfWork : IDisposable
{
IUserRepository Users { get; }
Task Save();
}
}
[GENERICREPOSITORY.CS]
using Microsoft.EntityFrameworkCore;
using System;
using System.Collections.Generic;
using System.Threading.Tasks;
using WebAPIDocker.DAL.Interface;
namespace WebAPIDocker.DAL
{
public abstract class GenericRepository<TEntity> : IGenericRepository<TEntity> where TEntity : class
{
private readonly DataContext context;
public GenericRepository(DataContext dbContext)
{
this.context = dbContext;
}
public async Task<IEnumerable<TEntity>> GetAll()
{
return await context.Set<TEntity>().AsNoTracking().ToListAsync();
}
public async Task<TEntity> GetById(int id)
{
return await context.Set<TEntity>().FindAsync(id);
}
public async Task<bool> Create(TEntity entity)
{
try
{
await context.Set<TEntity>().AddAsync(entity);
return true;
}
catch (Exception)
{
return false;
}
}
public async Task<bool> Delete(int id)
{
try
{
var objEntity = await GetById(id);
context.Set<TEntity>().Remove(objEntity);
return true;
}
catch (Exception)
{
return false;
}
}
public bool Update(TEntity entity)
{
try
{
context.Set<TEntity>().Update(entity);
return true;
}
catch (Exception)
{
return false;
}
}
}
}
[USERREPOSITORY.CS]
using System.Collections.Generic;
using System.Threading.Tasks;
using WebAPIDocker.BE;
using WebAPIDocker.DAL.Interface;
namespace WebAPIDocker.DAL
{
public class UserRepository: GenericRepository<TabUser>, IUserRepository
{
private readonly DataContext context;
public UserRepository(DataContext dbContext) : base(dbContext)
{
this.context = dbContext;
}
public async Task<bool> AddUser(TabUser objUser)
{
return await Create(objUser);
}
public async Task<bool> DeleteUser(int idUser)
{
return await Delete(idUser);
}
public async Task<bool> EditUser(TabUser objUser)
{
var inputUser = await GetById(objUser.Id);
if (inputUser == null)
{
return false;
}
inputUser.UserName = objUser.UserName;
inputUser.Password = objUser.Password;
inputUser.DateCreation = objUser.DateCreation;
return Update(inputUser);
}
public async Task<IEnumerable<TabUser>> GetAllUsers()
{
return await GetAll();
}
public async Task<TabUser> GetUserById(int id)
{
return await GetById(id);
}
}
}
[UNITOFWORK.CS]
using System.Threading.Tasks;
using WebAPIDocker.DAL.Interface;
namespace WebAPIDocker.DAL
{
public class UnitOfWork : IUnitOfWork
{
private readonly DataContext _context;
public UnitOfWork(DataContext context)
{
_context = context;
Users = new UserRepository(_context);
}
public IUserRepository Users { get; private set; }
public async Task Save()
{
await _context.SaveChangesAsync();
}
public void Dispose()
{
_context.Dispose();
}
}
}
[DATACONTEXT.CS]
using Microsoft.EntityFrameworkCore;
using WebAPIDocker.BE;
namespace WebAPIDocker.DAL
{
public class DataContext:DbContext
{
public DbSet<TabUser> TabUser { get; set; }
public DataContext(DbContextOptions<DataContext> options)
: base(options)
{
}
}
}
BE_UI:
[USER.CS]
namespace WebAPIDocker.BE_UI
{
public class User
{
public int Id { get; set; }
public string UserName { get; set; }
public string Password { get; set; }
public string CreatedAt { get; set; }
}
}
BLL
[IUSERCORE.CS]
using System.Collections.Generic;
using System.Threading.Tasks;
using WebAPIDocker.BE_UI;
namespace WebAPIDocker.BLL.Interface
{
public interface IUserCore
{
Task<List<User>> GetAllUsers();
Task<User> GetUserById(int id);
Task<bool> AddUser(User objUser);
Task<bool> EditUser(User objUser);
Task<bool> DeleteUser(int idUser);
}
}
[USERCORE.CS]
using AutoMapper;
using System;
using System.Collections.Generic;
using System.Threading.Tasks;
using WebAPIDocker.BE;
using WebAPIDocker.BE_UI;
using WebAPIDocker.BLL.Interface;
using WebAPIDocker.DAL.Interface;
namespace WebAPIDocker.BLL
{
public class UserCore : IUserCore, IDisposable
{
private IUnitOfWork _unitOfWork;
private IMapper iMapper;
public UserCore(IUnitOfWork inputUnitOfWork)
{
_unitOfWork = inputUnitOfWork;
ConfigurationAutomapper();
}
private void ConfigurationAutomapper()
{
var config = new MapperConfiguration(cfg => {
cfg.CreateMap<TabUser, User>()
.ForMember(destination => destination.Id, opts => opts.MapFrom(source => source.Id))
.ForMember(destination => destination.UserName, opts => opts.MapFrom(source => source.UserName))
.ForMember(destination => destination.Password, opts => opts.MapFrom(source => source.Password))
.ForMember(destination => destination.CreatedAt, opts => opts.MapFrom(source => source.DateCreation.ToShortDateString()))
.ReverseMap();
});
iMapper = config.CreateMapper();
}
public void Dispose()
{
_unitOfWork.Dispose();
}
public async Task<List<User>> GetAllUsers()
{
var lstUsers = await _unitOfWork.Users.GetAllUsers();
return iMapper.Map<IEnumerable<TabUser>, List<User>>(lstUsers);
}
public async Task<User> GetUserById(int id)
{
var objUser = await _unitOfWork.Users.GetUserById(id);
if (objUser == null)
{
return null;
}
else
{
return iMapper.Map<TabUser, User>(objUser);
}
}
public async Task<bool> AddUser(User objUser)
{
var inputUser = iMapper.Map<User, TabUser>(objUser);
var result = await _unitOfWork.Users.AddUser(inputUser);
if(result)
{
await _unitOfWork.Save();
}
return result;
}
public async Task<bool> EditUser(User objUser)
{
var inputUser = iMapper.Map<User, TabUser>(objUser);
var result = await _unitOfWork.Users.EditUser(inputUser);
if (result)
{
await _unitOfWork.Save();
}
return result;
}
public async Task<bool> DeleteUser(int idUser)
{
var result = await _unitOfWork.Users.DeleteUser(idUser);
if (result)
{
await _unitOfWork.Save();
}
return result;
}
}
}
WEB API CONTROLLER
[USERCONTROLLER.CS]
using Microsoft.AspNetCore.Mvc;
using System.Collections.Generic;
using System.Threading.Tasks;
using WebAPIDocker.BE_UI;
using WebAPIDocker.BLL.Interface;
namespace WebAPIDocker.Controllers
{
[Route("users")]
[ApiController]
public class UsersController : ControllerBase
{
private readonly IUserCore _coreUser;
public UsersController(IUserCore coreUser)
{
_coreUser = coreUser;
}
[HttpGet]
public async Task<ActionResult<IEnumerable<User>>> GetAllUsers()
{
var result = await _coreUser.GetAllUsers();
if (result == null || result.Count == 0)
{
return NotFound();
}
return Ok(result);
}
[HttpGet("{id}")]
public async Task<ActionResult<User>> GetUserById(int id)
{
var result = await _coreUser.GetUserById(id);
if (result == null)
{
return NotFound();
}
return Ok(result);
}
[HttpDelete("{id}")]
public async Task<ActionResult> DeleteUserById(int id)
{
var result = await _coreUser.DeleteUser(id);
if (result == false)
{
return NotFound();
}
return Ok();
}
[HttpPost]
public async Task<ActionResult> AddUser(User inputUser)
{
var result = await _coreUser.AddUser(inputUser);
if (result == false)
{
return NotFound();
}
return Ok();
}
[HttpPut]
public async Task<ActionResult> EditUser(User inputUser)
{
var result = await _coreUser.EditUser(inputUser);
if (result == false)
{
return NotFound();
}
return Ok();
}
}
}
[APPSETTINGS.JSON]
{
"Logging": {
"LogLevel": {
"Default": "Information",
"Microsoft": "Warning",
"Microsoft.Hosting.Lifetime": "Information"
}
},
"AllowedHosts": "*",
"ConnectionStrings": {
"DbConnectionForDocker": "Server=192.168.88.128;Database=TestDocker1;User Id=xx;Password=xxxx"
}
}
[STARTUP.CS]
using Microsoft.AspNetCore.Builder;
using Microsoft.AspNetCore.Hosting;
using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.Configuration;
using Microsoft.Extensions.DependencyInjection;
using Microsoft.Extensions.Hosting;
using WebAPIDocker.BLL;
using WebAPIDocker.BLL.Interface;
using WebAPIDocker.DAL;
using WebAPIDocker.DAL.Interface;
namespace WebAPIDocker
{
public class Startup
{
public Startup(IConfiguration configuration)
{
Configuration = configuration;
}
public IConfiguration Configuration { get; }
// This method gets called by the runtime. Use this method to add services to the container.
public void ConfigureServices(IServiceCollection services)
{
services.AddDbContext<DataContext>(options => {
options.UseSqlServer(Configuration["ConnectionStrings:DbConnectionForDocker"]);
});
services.AddScoped<IUserCore, UserCore>();
services.AddScoped<IUnitOfWork, UnitOfWork>();
services.AddScoped<IUserRepository, UserRepository>();
services.AddControllers();
}
// This method gets called by the runtime. Use this method to configure the HTTP request pipeline.
public void Configure(IApplicationBuilder app, IWebHostEnvironment env)
{
if (env.IsDevelopment())
{
app.UseDeveloperExceptionPage();
}
app.UseHttpsRedirection();
app.UseRouting();
app.UseAuthorization();
app.UseEndpoints(endpoints =>
{
endpoints.MapControllers();
});
}
}
}
Now, if we run the application, this will be the result:
DB IN CLOUD
First of all, we add the connection string for the Azure SQL SERVER created in the post (for this post, I have updated the data) Azure – How to create an Azure SQL Database:
[APPSETTINGS.JSON]
{
"Logging": {
"LogLevel": {
"Default": "Information",
"Microsoft": "Warning",
"Microsoft.Hosting.Lifetime": "Information"
}
},
"AllowedHosts": "*",
"ConnectionStrings": {
"DbConnectionForDocker": "Server=192.168.88.128;Database=TestDocker1;User Id=xx;Password=xxxx",
"DbAzureConnection": "Server=tcp:dockertestapi.database.windows.net,1433;Initial Catalog=dockertest;Persist Security Info=False;User ID=xx;Password=xxxxxx;MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;"
}
}
[STARTUP.CS]
using Microsoft.AspNetCore.Builder;
using Microsoft.AspNetCore.Hosting;
using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.Configuration;
using Microsoft.Extensions.DependencyInjection;
using Microsoft.Extensions.Hosting;
using WebAPIDocker.BLL;
using WebAPIDocker.BLL.Interface;
using WebAPIDocker.DAL;
using WebAPIDocker.DAL.Interface;
namespace WebAPIDocker
{
public class Startup
{
public Startup(IConfiguration configuration)
{
Configuration = configuration;
}
public IConfiguration Configuration { get; }
// This method gets called by the runtime. Use this method to add services to the container.
public void ConfigureServices(IServiceCollection services)
{
services.AddDbContext<DataContext>(options => {
options.UseSqlServer(Configuration["ConnectionStrings:DbAzureConnection"]);
});
services.AddScoped<IUserCore, UserCore>();
services.AddScoped<IUnitOfWork, UnitOfWork>();
services.AddScoped<IUserRepository, UserRepository>();
services.AddControllers();
}
// This method gets called by the runtime. Use this method to configure the HTTP request pipeline.
public void Configure(IApplicationBuilder app, IWebHostEnvironment env)
{
if (env.IsDevelopment())
{
app.UseDeveloperExceptionPage();
}
app.UseHttpsRedirection();
app.UseRouting();
app.UseAuthorization();
app.UseEndpoints(endpoints =>
{
endpoints.MapControllers();
});
}
}
}
Now, we save everything and then, we will push our Docker image into Docker Hub:
Then, we open a browser and we go to Azure Portal where, using the “Firewall Settings” of our Azure SQL Database, we will authorise the ip client, where we want to run the docker image, to use the Azure SQL Database.
Finally, we run the Docker image, using the command docker run -p 8080:80 commander2020/webapidocker, and then, we go to http://localhost:8080/users, in order to check everything works fine:
Hi therе mates, its wonderful article about teachingand fully defined, keep it up ɑll the time.
Good article! We are linking to this particularly great content on our website.
Keep up the great writing.
Hello there, You’ve done an excellent job. I will certainly digg it
and personally suggest to my friends. I’m confident
they will be benefited from this web site.