250 MySQL Support - chempkovsky/CS82ANGULAR GitHub Wiki
- Create Lookup WebApi project
- Create main WebApi project
- Modify appsettings json
- Modify Program file
- MySql Remote connect
- We moved all WebApi controllers to separate projects
- thus, we need to add only two projects with a few lines of code
- in the solution folder run the commands:
dotnet new webapi -o LpPhBkMySqlWebApp
dotnet sln PhonebookSolution.sln add LpPhBkMySqlWebApp/LpPhBkMySqlWebApp.csproj --solution-folder Server
dotnet add LpPhBkMySqlWebApp/LpPhBkMySqlWebApp.csproj package LinqKit.Microsoft.EntityFrameworkCore
dotnet add LpPhBkMySqlWebApp/LpPhBkMySqlWebApp.csproj package Microsoft.AspNetCore.Authentication.JwtBearer
dotnet add LpPhBkMySqlWebApp/LpPhBkMySqlWebApp.csproj package MassTransit.RabbitMQ
dotnet add LpPhBkMySqlWebApp/LpPhBkMySqlWebApp.csproj package Pomelo.EntityFrameworkCore.MySql
dotnet add LpPhBkMySqlWebApp/LpPhBkMySqlWebApp.csproj reference LpPhBkContext/LpPhBkContext.csproj
dotnet add LpPhBkMySqlWebApp/LpPhBkMySqlWebApp.csproj reference LpPhBkViews/LpPhBkViews.csproj
dotnet add LpPhBkMySqlWebApp/LpPhBkMySqlWebApp.csproj reference LpPhBkControllers/LpPhBkControllers.csproj
- in the solution folder run the commands:
dotnet new webapi -o PhBkMySqlWebApp
dotnet sln PhonebookSolution.sln add PhBkMySqlWebApp/PhBkMySqlWebApp.csproj --solution-folder Server
dotnet add PhBkMySqlWebApp/PhBkMySqlWebApp.csproj package LinqKit.Microsoft.EntityFrameworkCore
dotnet add PhBkMySqlWebApp/PhBkMySqlWebApp.csproj package Microsoft.AspNetCore.Authentication.JwtBearer
dotnet add PhBkMySqlWebApp/PhBkMySqlWebApp.csproj package Microsoft.AspNetCore.Identity.EntityFrameworkCore
dotnet add PhBkMySqlWebApp/PhBkMySqlWebApp.csproj package MassTransit.RabbitMQ
dotnet add PhBkMySqlWebApp/PhBkMySqlWebApp.csproj package Pomelo.EntityFrameworkCore.MySql
dotnet add PhBkMySqlWebApp/PhBkMySqlWebApp.csproj reference PhBkContext/PhBkContext.csproj
dotnet add PhBkMySqlWebApp/PhBkMySqlWebApp.csproj reference PhBkViews/PhBkViews.csproj
dotnet add PhBkMySqlWebApp/PhBkMySqlWebApp.csproj reference PhBkControllers/PhBkControllers.csproj
-
We need to change only connection strings
-
for LpPhBkMySqlWebApp the
appsettings.json
will be as follows
Click to show the file
{
"Logging": {
"LogLevel": {
"Default": "Information",
"Microsoft.AspNetCore": "Warning"
}
},
"AllowedHosts": "*",
"ConnectionStrings": {
"LpPhnPhBkConnection": "Server=192.168.100.3;Port=3306;Database=LpPhnPhBkDbDef;User Id=rt;Password=myPss@wrd",
"LpEmpPhBkConnection": "Server=192.168.100.3;Port=3306;Database=LpEmpPhBkDbDef;User Id=rt;Password=myPss@wrd",
"LpPhBkConnection": "Server=192.168.100.3;Port=3306;Database=LpPhBkDbDef;User Id=rt;Password=myPss@wrd"
},
"PhbkDivisionViewExtForLkUpConf": {
"HostName": "192.168.100.3",
"Username": "admin",
"Password": "admin",
"VirtualHostName": "phbkhost",
"ClusterIpAddresses": []
},
"PhbkEmployeeViewExtForLkUpConf": {
"HostName": "192.168.100.3",
"Username": "admin",
"Password": "admin",
"VirtualHostName": "phbkhost",
"ClusterIpAddresses": []
},
"PhbkPhoneViewExtForLkUpConf": {
"HostName": "192.168.100.3",
"Username": "admin",
"Password": "admin",
"VirtualHostName": "phbkhost",
"ClusterIpAddresses": []
},
"JWT": {
"ValidAudience": "PhBkAudience",
"ValidIssuer": "PhBkIssuer",
"Secret": "JWTAuthenticationHIGHsecuredPasswordVVVp1OH7Xzyr"
}
}
- for PhBkMySqlWebApp the
appsettings.json
will be as follows
Click to show the file
{
"Logging": {
"LogLevel": {
"Default": "Information",
"Microsoft.AspNetCore": "Warning"
}
},
"AllowedHosts": "*",
"ConnectionStrings": {
"PhBkConnection": "Server=192.168.100.3;Port=3306;Database=PhBkDbDef;User Id=rt;Password=myPss@wrd",
"AuthConnection": "Server=192.168.100.3;Port=3306;Database=PhBkDbAuth;User Id=rt;Password=myPss@wrd",
"AspNetRegConnection": "Server=192.168.100.3;Port=3306;Database=PhBkAspNet;User Id=rt;Password=myPss@wrd"
},
"JWT": {
"ValidAudience": "PhBkAudience",
"ValidIssuer": "PhBkIssuer",
"Secret": "JWTAuthenticationHIGHsecuredPasswordVVVp1OH7Xzyr"
},
"PhbkDivisionViewExtForLkUpConf": {
"HostName": "192.168.100.3",
"Username": "admin",
"Password": "admin",
"VirtualHostName": "phbkhost",
"ClusterIpAddresses": []
},
"PhbkEmployeeViewExtForLkUpConf": {
"HostName": "192.168.100.3",
"Username": "admin",
"Password": "admin",
"VirtualHostName": "phbkhost",
"ClusterIpAddresses": []
},
"PhbkPhoneViewExtForLkUpConf": {
"HostName": "192.168.100.3",
"Username": "admin",
"Password": "admin",
"VirtualHostName": "phbkhost",
"ClusterIpAddresses": []
}
}
-
We only need to change the code related to Dbcontexts
-
for LpPhBkMySqlWebApp the
Program.cs
will be as follows
Click to show the file
using LpPhBkContext.PhBk;
using LpPhBkControllers.Consumers;
using LpPhBkViews.PhBk;
using MassTransit;
using Microsoft.AspNetCore.Authentication.JwtBearer;
using Microsoft.EntityFrameworkCore;
using Microsoft.IdentityModel.Tokens;
using System.Text;
var builder = WebApplication.CreateBuilder(args);
ConfigurationManager configuration = builder.Configuration;
builder.Services.AddDbContext<LpPhbkDbContext>(options =>
options.UseMySql(builder.Configuration.GetConnectionString("LpPhBkConnection"), ServerVersion.AutoDetect(builder.Configuration.GetConnectionString("LpPhBkConnection"))));
builder.Services.AddDbContext<LpEmpPhBkContext>(options =>
options.UseMySql(builder.Configuration.GetConnectionString("LpEmpPhBkConnection"), ServerVersion.AutoDetect(builder.Configuration.GetConnectionString("LpEmpPhBkConnection"))));
builder.Services.AddDbContext<LpPhnPhBkContext>(options =>
options.UseMySql(builder.Configuration.GetConnectionString("LpPhnPhBkConnection"), ServerVersion.AutoDetect(builder.Configuration.GetConnectionString("LpPhnPhBkConnection"))));
#region MassTransit config
var phbkDivisionViewExtForLkUpConf = new PhbkDivisionViewExtForLkUpConf();
configuration.GetSection(PhbkDivisionViewExtForLkUpConf.ConfName).Bind(phbkDivisionViewExtForLkUpConf);
builder.Services.AddMassTransit(x => {
x.AddConsumer<PhbkDivisionViewExtForLkUpMsgConsumer>(typeof(PhbkDivisionViewExtForLkUpMsgConsumerDefinition));
//.Endpoint(e => {
// e.Name = "phbk-division-view";
//});
x.UsingRabbitMq((context, configurator) => {
configurator.Host(phbkDivisionViewExtForLkUpConf.HostName, phbkDivisionViewExtForLkUpConf.VirtualHostName, h => {
h.Username(phbkDivisionViewExtForLkUpConf.Username);
h.Password(phbkDivisionViewExtForLkUpConf.Password);
if (phbkDivisionViewExtForLkUpConf.ClusterIpAddresses != null)
{
if (phbkDivisionViewExtForLkUpConf.ClusterIpAddresses.Length > 0)
{
h.UseCluster((configureCluster) => {
for (int i = 0; i < phbkDivisionViewExtForLkUpConf.ClusterIpAddresses.Length; i++)
{
configureCluster.Node(phbkDivisionViewExtForLkUpConf.ClusterIpAddresses[i]);
}
});
}
}
// h.PublisherConfirmation = true;
// h.ConfigureBatchPublish(configure => { });
});
configurator.ConfigureEndpoints(context);
//
// Quorum Queue settings
//
// configurator.SetQuorumQueue(3);
//
});
});
var phbkEmployeeViewExtForLkUpConf = new PhbkEmployeeViewExtForLkUpConf();
configuration.GetSection(PhbkEmployeeViewExtForLkUpConf.ConfName).Bind(phbkEmployeeViewExtForLkUpConf);
builder.Services.AddMassTransit<IBusLpPhbkEmployee>(x => {
x.AddConsumer<PhbkEmployeeViewExtForLkUpMsgConsumer>(typeof(PhbkEmployeeViewExtForLkUpMsgConsumerDefinition));
//.Endpoint(e => {
// e.Name = "phbk-division-view";
//});
x.UsingRabbitMq((context, configurator) => {
configurator.Host(phbkEmployeeViewExtForLkUpConf.HostName, phbkEmployeeViewExtForLkUpConf.VirtualHostName, h => {
h.Username(phbkEmployeeViewExtForLkUpConf.Username);
h.Password(phbkEmployeeViewExtForLkUpConf.Password);
if (phbkEmployeeViewExtForLkUpConf.ClusterIpAddresses != null)
{
if (phbkEmployeeViewExtForLkUpConf.ClusterIpAddresses.Length > 0)
{
h.UseCluster((configureCluster) => {
for (int i = 0; i < phbkEmployeeViewExtForLkUpConf.ClusterIpAddresses.Length; i++)
{
configureCluster.Node(phbkEmployeeViewExtForLkUpConf.ClusterIpAddresses[i]);
}
});
}
}
// h.PublisherConfirmation = true;
// h.ConfigureBatchPublish(configure => { });
});
configurator.ConfigureEndpoints(context);
//
// Quorum Queue settings
//
// configurator.SetQuorumQueue(3);
//
});
});
var phbkPhoneViewExtForLkUpConf = new PhbkPhoneViewExtForLkUpConf();
configuration.GetSection(PhbkPhoneViewExtForLkUpConf.ConfName).Bind(phbkPhoneViewExtForLkUpConf);
builder.Services.AddMassTransit<IBusLpPhbkPhone>(x => {
x.AddConsumer<PhbkPhoneViewExtForLkUpMsgConsumer>(typeof(PhbkPhoneViewExtForLkUpMsgConsumerDefinition));
//.Endpoint(e => {
// e.Name = "phbk-division-view";
//});
x.UsingRabbitMq((context, configurator) => {
configurator.Host(phbkPhoneViewExtForLkUpConf.HostName, phbkPhoneViewExtForLkUpConf.VirtualHostName, h => {
h.Username(phbkPhoneViewExtForLkUpConf.Username);
h.Password(phbkPhoneViewExtForLkUpConf.Password);
if (phbkPhoneViewExtForLkUpConf.ClusterIpAddresses != null)
{
if (phbkPhoneViewExtForLkUpConf.ClusterIpAddresses.Length > 0)
{
h.UseCluster((configureCluster) => {
for (int i = 0; i < phbkPhoneViewExtForLkUpConf.ClusterIpAddresses.Length; i++)
{
configureCluster.Node(phbkPhoneViewExtForLkUpConf.ClusterIpAddresses[i]);
}
});
}
}
// h.PublisherConfirmation = true;
// h.ConfigureBatchPublish(configure => { });
});
configurator.ConfigureEndpoints(context);
//
// Quorum Queue settings
//
// configurator.SetQuorumQueue(3);
//
});
});
#endregion
#region authentification
builder.Services.AddAuthentication(options => {
options.DefaultAuthenticateScheme = JwtBearerDefaults.AuthenticationScheme;
options.DefaultChallengeScheme = JwtBearerDefaults.AuthenticationScheme;
options.DefaultScheme = JwtBearerDefaults.AuthenticationScheme;
}).AddJwtBearer(options => {
options.SaveToken = true;
options.RequireHttpsMetadata = true;
options.TokenValidationParameters = new TokenValidationParameters() {
ValidateIssuer = true,
ValidateAudience = true,
ValidAudience = configuration["JWT:ValidAudience"],
ValidIssuer = configuration["JWT:ValidIssuer"],
IssuerSigningKey = new SymmetricSecurityKey(Encoding.UTF8.GetBytes(configuration["JWT:Secret"]))
};
});
builder.Services.AddHttpContextAccessor();
#endregion
// Add services to the container.
builder.Services.AddControllers();
// Learn more about configuring Swagger/OpenAPI at https://aka.ms/aspnetcore/swashbuckle
builder.Services.AddEndpointsApiExplorer();
builder.Services.AddSwaggerGen();
builder.Services.AddCors(options => {
options.AddDefaultPolicy(
builder => {
builder.AllowAnyOrigin().AllowAnyMethod().AllowAnyHeader();
});
});
var app = builder.Build();
app.UseCors();
// Configure the HTTP request pipeline.
if (app.Environment.IsDevelopment())
{
app.UseSwagger();
app.UseSwaggerUI();
}
app.UseHttpsRedirection();
app.UseAuthentication();
app.UseAuthorization();
app.MapControllers();
app.Run();
- for PhBkMySqlWebApp the
Program.cs
will be as follows
Click to show the file
using LpPhBkViews.PhBk;
using MassTransit;
using Microsoft.AspNetCore.Authentication.JwtBearer;
using Microsoft.AspNetCore.Identity;
using Microsoft.EntityFrameworkCore;
using Microsoft.IdentityModel.Tokens;
using PhBkContext.AspNetReg;
using PhBkContext.Auth;
using PhBkContext.PhBk;
using PhBkControllers.MassTansitBuses;
using System.Text;
var builder = WebApplication.CreateBuilder(args);
ConfigurationManager configuration = builder.Configuration;
builder.Services.AddDbContext<PhbkDbContext>(options =>
options.UseMySql(builder.Configuration.GetConnectionString("PhBkConnection"), ServerVersion.AutoDetect(builder.Configuration.GetConnectionString("PhBkConnection"))));
builder.Services.AddDbContext<aspnetchckdbcontext>(options =>
options.UseMySql(builder.Configuration.GetConnectionString("AuthConnection"), ServerVersion.AutoDetect(builder.Configuration.GetConnectionString("AuthConnection"))));
#region authentification
builder.Services.AddDbContext<AspNetRegistrationDbContext>(options =>
options.UseMySql(builder.Configuration.GetConnectionString("AspNetRegConnection"), ServerVersion.AutoDetect(builder.Configuration.GetConnectionString("AspNetRegConnection"))));
builder.Services.AddIdentity<IdentityUser, IdentityRole>()
.AddEntityFrameworkStores<AspNetRegistrationDbContext>().AddDefaultTokenProviders();
builder.Services.AddAuthentication(options => {
options.DefaultAuthenticateScheme = JwtBearerDefaults.AuthenticationScheme;
options.DefaultChallengeScheme = JwtBearerDefaults.AuthenticationScheme;
options.DefaultScheme = JwtBearerDefaults.AuthenticationScheme;
}).AddJwtBearer(options => {
options.SaveToken = true;
options.RequireHttpsMetadata = true;
options.TokenValidationParameters = new TokenValidationParameters() {
ValidateIssuer = true,
ValidateAudience = true,
ValidAudience = configuration["JWT:ValidAudience"],
ValidIssuer = configuration["JWT:ValidIssuer"],
IssuerSigningKey = new SymmetricSecurityKey(Encoding.UTF8.GetBytes(configuration["JWT:Secret"]))
};
});
builder.Services.AddHttpContextAccessor();
#endregion
#region MassTransit config
var phbkDivisionViewExtForLkUpConf = new PhbkDivisionViewExtForLkUpConf();
configuration.GetSection(PhbkDivisionViewExtForLkUpConf.ConfName).Bind(phbkDivisionViewExtForLkUpConf);
builder.Services.AddMassTransit(x => {
x.AddRequestClient<IPhbkDivisionViewExtForLkUpMsg>();
x.UsingRabbitMq((context, configurator) => {
configurator.Host(phbkDivisionViewExtForLkUpConf.HostName, phbkDivisionViewExtForLkUpConf.VirtualHostName, h => {
h.Username(phbkDivisionViewExtForLkUpConf.Username);
h.Password(phbkDivisionViewExtForLkUpConf.Password);
if (phbkDivisionViewExtForLkUpConf.ClusterIpAddresses != null)
{
if (phbkDivisionViewExtForLkUpConf.ClusterIpAddresses.Length > 0)
{
h.UseCluster((configureCluster) => {
for (int i = 0; i < phbkDivisionViewExtForLkUpConf.ClusterIpAddresses.Length; i++)
{
configureCluster.Node(phbkDivisionViewExtForLkUpConf.ClusterIpAddresses[i]);
}
});
}
}
// h.PublisherConfirmation = true;
// h.ConfigureBatchPublish(configure => { });
});
//
// Quorum Queue settings
//
// configurator.SetQuorumQueue(3);
//
});
});
var phbkEmployeeViewExtForLkUpConf = new PhbkEmployeeViewExtForLkUpConf();
configuration.GetSection(PhbkEmployeeViewExtForLkUpConf.ConfName).Bind(phbkEmployeeViewExtForLkUpConf);
builder.Services.AddMassTransit<IBusLpPhbkEmployee>(x => {
x.AddRequestClient<IPhbkEmployeeViewExtForLkUpMsg>();
x.UsingRabbitMq((context, configurator) => {
configurator.Host(phbkEmployeeViewExtForLkUpConf.HostName, phbkEmployeeViewExtForLkUpConf.VirtualHostName, h => {
h.Username(phbkEmployeeViewExtForLkUpConf.Username);
h.Password(phbkEmployeeViewExtForLkUpConf.Password);
if (phbkEmployeeViewExtForLkUpConf.ClusterIpAddresses != null)
{
if (phbkEmployeeViewExtForLkUpConf.ClusterIpAddresses.Length > 0)
{
h.UseCluster((configureCluster) => {
for (int i = 0; i < phbkEmployeeViewExtForLkUpConf.ClusterIpAddresses.Length; i++)
{
configureCluster.Node(phbkEmployeeViewExtForLkUpConf.ClusterIpAddresses[i]);
}
});
}
}
// h.PublisherConfirmation = true;
// h.ConfigureBatchPublish(configure => { });
});
//
// Quorum Queue settings
//
// configurator.SetQuorumQueue(3);
//
});
});
var phbkPhoneViewExtForLkUpConf = new PhbkPhoneViewExtForLkUpConf();
configuration.GetSection(PhbkPhoneViewExtForLkUpConf.ConfName).Bind(phbkPhoneViewExtForLkUpConf);
builder.Services.AddMassTransit<IBusLpPhbkPhone>(x => {
x.AddRequestClient<IPhbkPhoneViewExtForLkUpMsg>();
x.UsingRabbitMq((context, configurator) => {
configurator.Host(phbkPhoneViewExtForLkUpConf.HostName, phbkPhoneViewExtForLkUpConf.VirtualHostName, h => {
h.Username(phbkPhoneViewExtForLkUpConf.Username);
h.Password(phbkPhoneViewExtForLkUpConf.Password);
if (phbkPhoneViewExtForLkUpConf.ClusterIpAddresses != null)
{
if (phbkPhoneViewExtForLkUpConf.ClusterIpAddresses.Length > 0)
{
h.UseCluster((configureCluster) => {
for (int i = 0; i < phbkPhoneViewExtForLkUpConf.ClusterIpAddresses.Length; i++)
{
configureCluster.Node(phbkPhoneViewExtForLkUpConf.ClusterIpAddresses[i]);
}
});
}
}
// h.PublisherConfirmation = true;
// h.ConfigureBatchPublish(configure => { });
});
configurator.ConfigureEndpoints(context);
//
// Quorum Queue settings
//
// configurator.SetQuorumQueue(3);
//
});
});
#endregion
// Add services to the container.
builder.Services.AddControllers();
// Learn more about configuring Swagger/OpenAPI at https://aka.ms/aspnetcore/swashbuckle
builder.Services.AddEndpointsApiExplorer();
builder.Services.AddSwaggerGen();
builder.Services.AddCors(options => {
options.AddDefaultPolicy(
builder => {
builder.AllowAnyOrigin().AllowAnyMethod().AllowAnyHeader();
});
});
var app = builder.Build();
app.UseCors();
// Configure the HTTP request pipeline.
if (app.Environment.IsDevelopment())
{
app.UseSwagger();
app.UseSwaggerUI();
}
app.UseHttpsRedirection();
app.UseAuthentication();
app.UseAuthorization();
app.MapControllers();
app.Run();
- By default, you can only connect to localhost
- to connect to MySql remotely run the following Sql commands:
CREATE USER 'rt'@'%' IDENTIFIED BY '*********';
GRANT ALL PRIVILEGES ON *.* TO 'rt'@'%' WITH GRANT OPTION;