.Net Core API 连接Mysql数据库返回JSON 数据供ActiveReportsJS 使用

登录

示例源码：

操作步骤：

1. 使用Mysql工具创建我们需要的mysql database 表

CREATE TABLE dbo.department(
DepartmentId int AUTO_INCREMENT,
DepartmentName nvarchar(500),
PRIMARY KEY (DepartmentId)
)

复制代码

插入表：

INSERT into dbo.Department (DepartmentName) VALUES ('IT')
INSERT into dbo.Department (DepartmentName) VALUES ('Support')

复制代码

employee表：

create table dbo.Employee(
EmployeeId int AUTO_INCREMENT,
EmployeeName nvarchar(500),
Department nvarchar(500),
DateOfJoining datetime,
PhotoFileName nvarchar(500),
PRIMARY KEY(EmployeeId)
);
insert into dbo.Employee(EmployeeName,Department,DateOfJoining,PhotoFileName)
values ('Bob','IT','2021-06-21','anonymous.png');
select * from dbo.Employee;

复制代码

2. 创建.Net Core Web API 项目

-> launchSettings.json ：文件包含了如何启动项目的细节描述

-> Controllers 文件夹：包含了API controler 方法

-> We generally keep the configuration details such as database details in appSettings.json.

-> program.cs 是程序的主入口 Main() 它还创建了网络主机，基本上可以帮助应用程序监听http请求。

-> startup类 class 类配置了我们的应用程序所需的所有服务。类配置了我们应用程序所需的所有服务。服务基本上是可重复使用的组件，可以使用依赖性在我们的应用程序中使用。

3. 配置允许跨域：

using Microsoft.AspNetCore.Builder;
using Microsoft.AspNetCore.Hosting;
using Microsoft.AspNetCore.HttpsPolicy;
using Microsoft.AspNetCore.Mvc;
using Microsoft.Extensions.Configuration;
using Microsoft.Extensions.DependencyInjection;
using Microsoft.Extensions.Hosting;
using Microsoft.Extensions.Logging;
using Microsoft.OpenApi.Models;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;
namespace WebApplication11
{
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.AddCors(c=> {
c.AddPolicy("AllowOrigin",options=>options.AllowAnyOrigin().AllowAnyMethod().AllowAnyHeader());
});
services.AddControllers();
services.AddSwaggerGen(c =>
{
c.SwaggerDoc("v1", new OpenApiInfo { Title = "WebApplication11", Version = "v1" });
});
}
// 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.UseSwagger();
app.UseSwaggerUI(c => c.SwaggerEndpoint("/swagger/v1/swagger.json", "WebApplication11 v1"));
}
app.UseCors(options => options.AllowAnyOrigin().AllowAnyMethod().AllowAnyHeader());
app.UseHttpsRedirection();
app.UseRouting();
app.UseAuthorization();
app.UseEndpoints(endpoints =>
{
endpoints.MapControllers();
});
}
}
}

复制代码

4. 安装 JSON 序列化nuget 包帮助我们提供JSON 序列化和反序列化的函数

Startup.cs 完整代码：

using Microsoft.AspNetCore.Builder;
using Microsoft.AspNetCore.Hosting;
using Microsoft.AspNetCore.HttpsPolicy;
using Microsoft.AspNetCore.Mvc;
using Microsoft.Extensions.Configuration;
using Microsoft.Extensions.DependencyInjection;
using Microsoft.Extensions.FileProviders;
using Microsoft.Extensions.Hosting;
using Microsoft.Extensions.Logging;
using Microsoft.OpenApi.Models;
using Newtonsoft.Json.Serialization;
using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Threading.Tasks;
namespace WebApplication11
{
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.AddCors(c=> {
c.AddPolicy("AllowOrigin",options=>options.AllowAnyOrigin().AllowAnyMethod().AllowAnyHeader());
});
services.AddControllersWithViews().AddNewtonsoftJson(options =>
options.SerializerSettings.ReferenceLoopHandling = Newtonsoft.Json.ReferenceLoopHandling.Ignore)
.AddNewtonsoftJson(options => options.SerializerSettings.ContractResolver
= new DefaultContractResolver());
services.AddControllers();
services.AddControllers();
services.AddSwaggerGen(c =>
{
c.SwaggerDoc("v1", new OpenApiInfo { Title = "WebApplication11", Version = "v1" });
});
}
// 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.UseSwagger();
app.UseSwaggerUI(c => c.SwaggerEndpoint("/swagger/v1/swagger.json", "WebApplication11 v1"));
}
app.UseCors(options => options.AllowAnyOrigin().AllowAnyMethod().AllowAnyHeader());
app.UseHttpsRedirection();
app.UseRouting();
app.UseAuthorization();
app.UseEndpoints(endpoints =>
{
endpoints.MapControllers();
});
app.UseStaticFiles(new StaticFileOptions
{
FileProvider = new PhysicalFileProvider(
Path.Combine(Directory.GetCurrentDirectory(), "Photos")),
RequestPath = "/Photos"
});
}
}
}

复制代码

以下代码是指定图像上传的地址：

{
FileProvider = new PhysicalFileProvider(
Path.Combine(Directory.GetCurrentDirectory(), "Photos")),
RequestPath = "/Photos"
});

复制代码

5. 为了连接Mysql 数据库，先安装MysqlData nuget包

6. 添加数据模型 Model:

Department.cs:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;
namespace WebApplication1.Models
{
public class Department
{
public int DepartmentId { get; set; }
public string DepartmentName { get; set; }
}
}

复制代码

Employee.cs:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;
namespace WebApplication1.Models
{
public class Employee
{
public int EmployeeId { get; set; }
public string EmployeeName {get; set; }
public string Department { get; set; }
public string DateOfJoining { get; set; }
public string PhotoFileName { get; set; }
}
}

复制代码

7. 在appSettings.json 文件中添加数据库连接字符串：

8. 添加对应Model的controller:
DepartmentController.cs:

using Microsoft.AspNetCore.Http;
using Microsoft.AspNetCore.Mvc;
using Microsoft.Extensions.Configuration;
using MySql.Data.MySqlClient;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Threading.Tasks;
using WebApplication1.Models;
namespace WebApplication1.Controllers
{
[Route("api/[controller]")]
[ApiController]
public class DepartmentController : ControllerBase
{
private readonly IConfiguration _configuration;
public DepartmentController(IConfiguration configuration)
{
_configuration = configuration;
}
[HttpGet]
public JsonResult Get()
{
string query = @"
select DepartmentId,DepartmentName from
dbo.Department
";
DataTable table = new DataTable();
string sqlDataSource = _configuration.GetConnectionString("EmployeeAppCon");
MySqlDataReader myReader;
using(MySqlConnection mycon=new MySqlConnection(sqlDataSource))
{
mycon.Open();
using(MySqlCommand myCommand=new MySqlCommand(query, mycon))
{
myReader = myCommand.ExecuteReader();
table.Load(myReader);
myReader.Close();
mycon.Close();
}
}
return new JsonResult(table);
}
[HttpPost]
public JsonResult Post(Department dep)
{
string query = @"
insert into dbo.Department (DepartmentName) values
(@DepartmentName);
";
DataTable table = new DataTable();
string sqlDataSource = _configuration.GetConnectionString("EmployeeAppCon");
MySqlDataReader myReader;
using (MySqlConnection mycon = new MySqlConnection(sqlDataSource))
{
mycon.Open();
using (MySqlCommand myCommand = new MySqlCommand(query, mycon))
{
myCommand.Parameters.AddWithValue("@DepartmentName", dep.DepartmentName);
myReader = myCommand.ExecuteReader();
table.Load(myReader);
myReader.Close();
mycon.Close();
}
}
return new JsonResult("Added Successfully");
}
[HttpPut]
public JsonResult Put(Department dep)
{
string query = @"
update dbo.Department set
DepartmentName =@DepartmentName
where DepartmentId=@DepartmentId;
";
DataTable table = new DataTable();
string sqlDataSource = _configuration.GetConnectionString("EmployeeAppCon");
MySqlDataReader myReader;
using (MySqlConnection mycon = new MySqlConnection(sqlDataSource))
{
mycon.Open();
using (MySqlCommand myCommand = new MySqlCommand(query, mycon))
{
myCommand.Parameters.AddWithValue("@DepartmentId", dep.DepartmentId);
myCommand.Parameters.AddWithValue("@DepartmentName", dep.DepartmentName);
myReader = myCommand.ExecuteReader();
table.Load(myReader);
myReader.Close();
mycon.Close();
}
}
return new JsonResult("Updated Successfully");
}
[HttpDelete("{id}")]
public JsonResult Delete(int id)
{
string query = @"
delete from dbo.Department
where DepartmentId=@DepartmentId;
";
DataTable table = new DataTable();
string sqlDataSource = _configuration.GetConnectionString("EmployeeAppCon");
MySqlDataReader myReader;
using (MySqlConnection mycon = new MySqlConnection(sqlDataSource))
{
mycon.Open();
using (MySqlCommand myCommand = new MySqlCommand(query, mycon))
{
myCommand.Parameters.AddWithValue("@DepartmentId", id);
myReader = myCommand.ExecuteReader();
table.Load(myReader);
myReader.Close();
mycon.Close();
}
}
return new JsonResult("Deleted Successfully");
}
}
}

复制代码

EmployeeController.cs:

using Microsoft.AspNetCore.Hosting;
using Microsoft.AspNetCore.Http;
using Microsoft.AspNetCore.Mvc;
using Microsoft.Extensions.Configuration;
using MySql.Data.MySqlClient;
using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using System.Threading.Tasks;
using WebApplication1.Models;
namespace WebApplication1.Controllers
{
[Route("api/[controller]")]
[ApiController]
public class EmployeeController : ControllerBase
{
private readonly IConfiguration _configuration;
private readonly IWebHostEnvironment _env;
public EmployeeController(IConfiguration configuration,IWebHostEnvironment env)
{
_configuration = configuration;
_env = env;
}
[HttpGet]
public JsonResult Get()
{
string query = @"
select EmployeeId,EmployeeName,Department,
DATE_FORMAT(DateOfJoining,'%Y-%m-%d') as DateOfJoining,
PhotoFileName
from
dbo.Employee
";
DataTable table = new DataTable();
string sqlDataSource = _configuration.GetConnectionString("EmployeeAppCon");
MySqlDataReader myReader;
using (MySqlConnection mycon = new MySqlConnection(sqlDataSource))
{
mycon.Open();
using (MySqlCommand myCommand = new MySqlCommand(query, mycon))
{
myReader = myCommand.ExecuteReader();
table.Load(myReader);
myReader.Close();
mycon.Close();
}
}
return new JsonResult(table);
}
[HttpPost]
public JsonResult Post(Employee emp)
{
string query = @"
insert into dbo.Employee
(EmployeeName,Department,DateOfJoining,PhotoFileName)
values
(@EmployeeName,@Department,@DateOfJoining,@PhotoFileName) ;
";
DataTable table = new DataTable();
string sqlDataSource = _configuration.GetConnectionString("EmployeeAppCon");
MySqlDataReader myReader;
using (MySqlConnection mycon = new MySqlConnection(sqlDataSource))
{
mycon.Open();
using (MySqlCommand myCommand = new MySqlCommand(query, mycon))
{
myCommand.Parameters.AddWithValue("@EmployeeName", emp.EmployeeName);
myCommand.Parameters.AddWithValue("@Department", emp.Department);
myCommand.Parameters.AddWithValue("@DateOfJoining", emp.DateOfJoining);
myCommand.Parameters.AddWithValue("@PhotoFileName", emp.PhotoFileName);
myReader = myCommand.ExecuteReader();
table.Load(myReader);
myReader.Close();
mycon.Close();
}
}
return new JsonResult("Added Successfully");
}
[HttpPut]
public JsonResult Put(Employee emp)
{
string query = @"
update dbo.Employee set
EmployeeName =@EmployeeName,
Department =@Department,
DateOfJoining =@DateOfJoining,
PhotoFileName =@PhotoFileName
where EmployeeId=@EmployeeId;
";
DataTable table = new DataTable();
string sqlDataSource = _configuration.GetConnectionString("EmployeeAppCon");
MySqlDataReader myReader;
using (MySqlConnection mycon = new MySqlConnection(sqlDataSource))
{
mycon.Open();
using (MySqlCommand myCommand = new MySqlCommand(query, mycon))
{
myCommand.Parameters.AddWithValue("@EmployeeId", emp.EmployeeId);
myCommand.Parameters.AddWithValue("@EmployeeName", emp.EmployeeName);
myCommand.Parameters.AddWithValue("@Department", emp.Department);
myCommand.Parameters.AddWithValue("@DateOfJoining", emp.DateOfJoining);
myCommand.Parameters.AddWithValue("@PhotoFileName", emp.PhotoFileName);
myReader = myCommand.ExecuteReader();
table.Load(myReader);
myReader.Close();
mycon.Close();
}
}
return new JsonResult("Updated Successfully");
}
[HttpDelete("{id}")]
public JsonResult Delete(int id)
{
string query = @"
delete from dbo.Employee
where EmployeeId=@EmployeeId;
";
DataTable table = new DataTable();
string sqlDataSource = _configuration.GetConnectionString("EmployeeAppCon");
MySqlDataReader myReader;
using (MySqlConnection mycon = new MySqlConnection(sqlDataSource))
{
mycon.Open();
using (MySqlCommand myCommand = new MySqlCommand(query, mycon))
{
myCommand.Parameters.AddWithValue("@EmployeeId", id);
myReader = myCommand.ExecuteReader();
table.Load(myReader);
myReader.Close();
mycon.Close();
}
}
return new JsonResult("Deleted Successfully");
}
[Route("SaveFile")]
[HttpPost]
public JsonResult SaveFile()
{
try
{
var httpReuest = Request.Form;
var postedFile = httpReuest.Files[0];
string filename = postedFile.FileName;
var physicalPath = _env.ContentRootPath + "/Photos/" + filename;
using(var stream=new FileStream(physicalPath, FileMode.Create))
{
postedFile.CopyTo(stream);
}
return new JsonResult(filename);
}
catch (Exception)
{
return new JsonResult("anonymous.png");
}
}
}
}

复制代码

运行编译即可启动 WebAPI 并在其他项目中调用了。

		自动登录	找回密码
密码			立即注册

.Net Core API 连接Mysql数据库返回JSON 数据供ActiveReportsJS 使用

本帖子中包含更多资源

0 个回复

活字格认证

讲师达人

悬赏达人

微信认证勋章

元老葡萄