本文介绍了如何使用ASP.NET Core创建一个新的Web API项目，并添加Swagger UI，连接到SQL Server数据库，实现日志记录，启用CORS，以及添加Excel导出功能。此外，还涵盖了项目Docker化并在本地运行Docker镜像的步骤。教程适合具备C#和Web开发经验的开发者。
swagger 导出api
介绍 (Introduction)
There's been a breathtaking amount of progress on ASP.NET Core since it was released back in 2016, and each release seems to get better, friendlier and more developer-friendly.
自2016年发布以来，ASP.NET Core取得了惊人的进展，而且每个版本似乎都变得更好，更友好且对开发人员更友好。
Web APIs are a huge improvement on the old WCF services (and let's not even talk about SOAP...), and there are a wealth of "getting started" resources out there. Rather than reinventing that wheel, this article is just a "cheatsheet" of the steps you need to do, to do the following:
Web API是对旧的WCF服务的巨大改进(并且甚至不谈论SOAP ...)，并且那里有大量的“入门”资源。 本文只是重新编写轮子而已，而不仅仅是完成以下操作的必要步骤：
- Create a new Web API project 创建一个新的Web API项目
- Add Swagger to your project 将Swagger添加到您的项目
- Connect your project to your SQL Server database 将项目连接到SQL Server数据库
Add logging to your project, so that any exceptions or log messages get saved in a SQL Server table (rather than being dumped in an IIS .txt log file somewhere)
将日志添加到项目中，以便所有异常或日志消息都保存在SQL Server表中(而不是转储到IIS .txt日志文件中的某个位置)
Add CRUDE to your project. Nope, that's not a typo. We're going to have our regular Create-Read-Update-Delete functions and... just to show how easy it is.... Export. We're going to add an endpoint to export our data to a real .xlsx file, using the Open XML libraries, just for the hell of it.
将CRUDE添加到您的项目。 不，那不是错字。 我们将拥有常规的Create-Read-Update-Delete函数，以及...只是为了展示它的简单性....导出。 我们将添加一个端点，使用Open XML库将数据导出到真实的.xlsx文件，仅此而已。
- Deploy the Web API to Azure as a Docker Image, then run the image both locally and in an Azure container. 将Web API作为Docker映像部署到Azure，然后在本地和Azure容器中运行映像。
With the exception of the fifth item, these are the 4 steps I go through with each of my Web APIs, so I thought it'd be good to just document the steps you need to go through, as quickly and painlessly as possible, so we have more time to concentrate on doing the real work!
除了第五项之外，这些是我使用每个Web API都要执行的4个步骤，因此我认为最好尽可能快速，轻松地记录您需要执行的步骤，以便我们有更多时间专注于完成实际工作！
Now, I have provided a MikesBank.zip file containing the example code from this article, but I strongly advise you not to use it.
现在，我已经提供了包含从本文的示例代码MikesBank.zip文件，但我强烈建议你不要使用它。
Visual Studio 2017 and ASP.NET Core are changing almost every month... it is a far better idea to create your own project from scratch and follow these instructions, using whatever new templates Microsoft is providing when you read this, than using the example I've provided, which is likely to be half out-of-date by the time you've finished reading this paragraph.
Visual Studio 2017和ASP.NET Core几乎每个月都在变化...与使用示例相比，使用Microsoft在阅读本文时提供的任何新模板，从头开始创建自己的项目并遵循这些说明是一个更好的主意。我已经提供了，到您阅读完本段时，它可能已经过期了一半。
Happy reading!
祝您阅读愉快！
要求 (Requirements)
To follow this article, you will need:
要阅读本文，您将需要：
- knowledge of C# C＃知识
- a copy of Visual Studio 2017 Visual Studio 2017的副本
- the .NET Core v2.2 SDK (or later) installed 安装的.NET Core v2.2 SDK(或更高版本)
- SQL Server Management Studio (or you can modify the connection strings, to point to your own flavour of database) SQL Server Management Studio(或者您可以修改连接字符串，以指向您自己的数据库风格)
I would recommend that you update your copy of VS2017 and .NET Core before following these instructions. Whilst writing this article, I tried to import a "nuget" package, which VS2017 accepted quite happily... but then threw lots of compilation errors as the "nuget" package was more up-to-date than my version of .NET Core, and refused to work with my older version.
我建议您先按照以下说明更新 VS2017和.NET Core的副本。 在撰写本文时，我尝试导入一个“ nuget ”包，VS2017很高兴地接受了该包……但是由于“ nuget ”包比我的.NET Core版本更新，因此引发了许多编译错误。 ，并拒绝使用我的旧版本。
1.创建一个新的Web API项目 (1. Creating a New Web API Project)
This has been covered so many times, and I'm sure you all know how to do this.
这个问题已经讨论了很多次了，我相信大家都知道该怎么做。
If you're using Visual Studio 2017:
如果您使用的是Visual Studio 2017：
- Fire up Visual Studio 2017, and select File \ New \ Project. 启动Visual Studio 2017，然后选择File \ New \ Project。
Under Visual C# \ .NET Core, select "ASP.NET Core Web Application", and give your project a name (I'm going to call mine
MikesBank), then hit OK.在Visual C＃\ .NET Core下，选择“ ASP.NET Core Web应用程序 ”，并为您的项目命名(我将其命名为
MikesBank)，然后单击OK 。
On the next screen, make sure you have "API" selected, that it's a .NET Core application, and hit OK.
在下一个屏幕上，确保已选择“ API ”，它是一个.NET Core应用程序，然后单击“ 确定” 。
If you're using Visual Studio 2019:
如果您使用的是Visual Studio 2019：
- Click on File \ New \ Project 单击文件\新建\项目
On the first screen, click on "Create a new project".
在第一个屏幕上，单击“ 创建新项目 ”。
On the second screen, click on "ASP.NET Core Web Application".
在第二个屏幕上，单击“ ASP.NET Core Web应用程序 ”。
On the third screen, let's type in a Project Name of MikesBank, then click on Create.
在第三个屏幕上，让我们输入MikesBank的项目名称，然后单击Create 。
On the forth screen, select "API".
在第四屏幕上，选择“ API ”。
Note that Visual Studio 2019 no longer asks you if you want to create a new git repository by default. However, on the bottom right of the VS2019 window, there is an "Add to Source Control" button you can click on. If you select this, then select "Git", you can now get VS2019 to push your source code into a DevOps friendly project in Azure. This is a great starting place for adding Continuous Integration, Pipelines, a Work Items board, excellent. It's awesome....
请注意，Visual Studio 2019不再询问您是否默认情况下要创建新的git存储库。 但是，在VS2019窗口的右下角，可以单击“ 添加到源代码管理 ”按钮。 如果选择此选项，然后选择“ Git ”，则现在可以获取VS2019将源代码推送到Azure中对DevOps友好的项目中。 这是添加持续集成，流水线，工作项板的绝佳起点。 这很棒....
So... Visual Studio will create for you a basic API project, which returns some hardcoded data.
因此，... Visual Studio将为您创建一个基本的API项目，该项目将返回一些硬编码的数据。
If you run the project in Chrome, you'll see a couple of items of JSON data. Wonderful.
如果您在Chrome中运行该项目，则会看到几项JSON数据。 精彩。
However, if you're still using Internet Explorer, you might get a strange message asking "Do you want to open or save values.json (19 bytes) from localhost?" Yeah, it's 2019, and they're still doing this. I don't know why.
但是，如果您仍在使用Internet Explorer，则可能会收到一条奇怪的消息，询问“ 您是否要从本地主机打开或保存values.json(19个字节)？ ”是的，这是2019年，而他们仍在这样做。 我不知道为什么
As a developer, you can (and should) fix this, by adding the registry entries described in this StackOverflow article from 2010. This will make Internet Explorer actually display the JSON data, rather than nagging us about it.
作为开发人员，您可以(并且应该)通过添加此StackOverflow从2010年开始的文章中描述的注册表项来解决此问题。这将使Internet Explorer实际上显示JSON数据，而不是困扰我们。
So, we now have a starting point for our Web API. Now, let's make it better !
因此，我们现在有了Web API的起点。 现在，让我们变得更好！
2.将Swagger添加到项目 (2. Add Swagger to the Project)
Given how developer-friendly Visual Studio is, I'm always surprised that "Create a Swagger page for my API" isn't provided as an option when we tell it that we're creating a Web API project. However, it's easy enough to add.
考虑到Visual Studio对开发人员的友好程度，当我们告诉我们正在创建Web API项目时，没有提供“ 为我的API创建Swagger页面 ”作为选项，我总是感到惊讶。 但是，添加起来很容易。
To add Swagger to your project:
要将Swagger添加到您的项目中：
Right-click on your project name, and select "Manage NuGet packages..."
右键单击您的项目名称，然后选择“ 管理NuGet软件包... ”
Click on the Browse tab, then search for, and install, "
swashbuckle.aspnetcore"单击浏览选项卡，然后搜索并安装“
swashbuckle.aspnetcore”- You can now close this "nuget package manager" screen. 现在，您可以关闭此“ nuget软件包管理器”屏幕。
Open up the "startup.cs" file, and make the following changes to the
Configure()function:打开“ startup.cs ”文件，并对
Configure()函数进行以下更改：app.UseSwagger(); app.UseSwaggerUI(c => { c.SwaggerEndpoint("/swagger/v1/swagger.json","MikesBank API"); c.RoutePrefix = string.Empty; });Notice that
RoutePrefixline... that will make the Swagger page our default page. So, when I'm debugging, the Swagger page will appear, when the URL points to the root path, e.g.:请注意，
RoutePrefix行...将使Swagger页面成为默认页面。 因此，当我调试时，URL指向根路径时，将显示Swagger页面，例如：https://localhost:44350/Next, I'm going to modify the constructor sightly, and add a new "
env" variable:接下来，我将修改构造函数，并添加一个新的“
env”变量：public Startup(IHostingEnvironment env, IConfiguration configuration) { Configuration = configuration; this.env = env; } private IHostingEnvironment env { get; }With this in place, we can make the changes to the
ConfigureServices()function:有了这个，我们可以对
ConfigureServices()函数进行更改：public void ConfigureServices(IServiceCollection services) { services.AddMvc().SetCompatibilityVersion(CompatibilityVersion.Version_2_1); var pathIncludeXmlComments = $@"{env.ContentRootPath}\{env.ApplicationName}.xml"; services.AddSwaggerGen(c => { c.SwaggerDoc("v1", new Swashbuckle.AspNetCore.Swagger.Info { Version = "v1", Title = "MikesBank API", Description = "For CodeProject" }); if (System.IO.File.Exists(pathIncludeXmlComments)) c.IncludeXmlComments(pathIncludeXmlComments); }); }This is a little bloated, but gets around a problem with Swagger. It's often useful to have comments in our Web API controllers, which Swagger will display in its webpage. However, if this .xml file doesn't exist, it will completely crash our application, so I am taking care to only include the comments if this .xml file does exist. Update (March 2020): With recent versions of Swagger, you need to define the
SwaggerDocusing:这有点肿，但是可以解决Swagger的问题。 在我们的Web API控制器中添加注释通常非常有用，Swagger将在其网页中显示这些注释。 但是，如果此.xml文件不存在，它将完全使我们的应用程序崩溃，因此，我很注意仅在此.xml文件确实存在的情况下包括注释。 更新(2020年3月)：对于最新版本的Swagger，您需要使用以下方法定义
SwaggerDoc：services.AddSwaggerGen(c => { c.SwaggerDoc("v1", new OpenApiInfo { Title = "MikesBank API", Version = "v1" }); if (System.IO.File.Exists(pathIncludeXmlComments)) c.IncludeXmlComments(pathIncludeXmlComments); });The code changes are all done, but now, we just need to make some changes to the project settings. Right-click on your project name again, and select "Properties". Then, select the "Build" tab.
代码更改都已完成，但是现在，我们只需要对项目设置进行一些更改。 再次右键单击您的项目名称，然后选择“ 属性 ”。 然后，选择“ 构建 ”选项卡。
In the "Errors and Warnings" section, add "
;1591" to the list of warnings to ignore. If we have chosen to add comments in our Web API controllers, great! But I don't like Visual Studio underlining our code, if we haven't added comments yet. This isn't a bug/warning as such, so I ignore this warning to ignore such warnings.在“ 错误和警告 ”部分，将“
;1591”添加到要忽略的警告列表中。 如果我们选择在Web API控制器中添加注释，那就太好了！ 但是，如果我们还没有添加注释，我不喜欢Visual Studio为我们的代码添加下划线。 这不是错误/警告，因此我忽略此警告以忽略此类警告。1701;1702;1591Still on this screen, in the "Output" section, tick the box for "XML Documentation file:"
仍在此屏幕上的“ 输出 ”部分中，选中“ XML文档文件： ”框。
Now, hop across to the "Debug" tab, and remove the "api/values" from the "Launch browser", textbox to leave it blank.
现在，跳至“ 调试 ”选项卡，然后从“ 启动浏览器 ”文本框中删除“ api / values ”，使其保持空白。
If you now run the project, you'll see the Swagger website, with the list of example endpoints which VS2017 has created for us.
如果现在运行项目，您将看到Swagger网站，以及VS2017为我们创建的示例端点的列表。
Looking good! If you wanted to run the simple "GET all values" function, you could click on the first GET line, click on the "Try it out" button, then the "Execute" button, and you'll see the Response body with the two hardcoded values, as before.
看起来不错！ 如果要运行简单的“ 获取所有值 ”功能，则可以单击第一行GET行，单击“ 试用 ”按钮，然后单击“ 执行 ”按钮，然后将看到带有与以前一样，两个硬编码的值。
Okay, it's not as sophisticated as Postman or Fiddler, but it's free, friendly, and really useful.
好的，它不像Postman或Fiddler那样复杂，但是它是免费的，友好的并且非常有用。
And getting Swagger to include comments on this page is as simple as appending a summary or remarks section above your endpoint:
让Swagger在此页面上包含评论就像在端点上方添加summary或remarks部分一样简单：
/// <summary>
/// This is the Summary, describing the endpoint
/// </summary>
/// <remarks>
/// These are the Remarks for the endpoint
/// </remarks>
[HttpGet]
public ActionResult<IEnumerable<string>> Get()
{
return new string[] { "value1", "value2" };
}
Just bear in mind that Swagger itself does get updated regularly, and (once again), by the time you read this, or if you're Googling for problems when your Swagger code doesn't build/display, do check the latest documentation.
请记住，Swagger本身确实会定期更新，并且(一次)，在您阅读本文时，或者在无法构建/显示Swagger代码时谷歌搜索问题时，请检查最新文档。
3.将项目连接到SQL Server数据库 (3. Connect Your Project to Your SQL Server Database)
Okay, now let's link our Web API to a SQL Server database. To do this, go into "nuget package manager" again, search for, and install these three packages:
好的，现在让我们将Web API链接到SQL Server数据库。 为此，请再次进入“ nuget软件包管理器 ”，搜索并安装以下三个软件包：
Microsoft.EntityFrameworkCore.SqlServerMicrosoft.EntityFrameworkCore.SqlServerMicrosoft.EntityFrameworkCore.ToolsMicrosoft.EntityFrameworkCore.ToolsMicrosoft.EntityFrameworkCore.SqlServer.DesignMicrosoft.EntityFrameworkCore.SqlServer.Design
If you prefer cut'n'pasting rather than searching, an alternative way of doing this is to click on Tools \ Nuget Package Manager \ Package Manager Console, and run the following 3 commands:
如果您喜欢剪切而不是搜索，那么另一种方法是单击Tools \ Nuget Package Manager \ Package Manager Console ，然后运行以下3个命令：
install-package Microsoft.EntityFrameworkCore.SqlServer
install-package Microsoft.EntityFrameworkCore.Tools
install-package Microsoft.EntityFrameworkCore.SqlServer.Design
Either way, this will add the 3 packages you need for connecting to SQL Server.
无论哪种方式，这都会添加连接到SQL Server所需的3个程序包。
October 2019 update
2019年10月更新
I did warn you that this stuff moves fast... Since publishing this article (last month !) things have changed. If you try to download the latest version of these three packages, you'll be downloading versions which are only compatible with .Net Core 3.x. And this version of .Net Core only works if you're using VS2019 (not VS2017). So - if you're running Visual Studio 2017, make sure you choose slightly earlier versions of these packages, as these will be compatible with .Net Core 2.x.
我确实警告过您，这些东西进展很快...自从发表本文(上个月！)以来，情况已经发生了变化。 如果您尝试下载这三个软件包的最新版本，则将下载仅与.Net Core 3.x兼容的版本。 并且此版本的.Net Core仅在使用VS2019( 而不是 VS2017)时才有效。 因此-如果您正在运行Visual Studio 2017，请确保选择这些软件包的稍早版本，因为它们将与.Net Core 2.x兼容。
(Microsoft: it would be helpful to change the NuGet Package Manager screen to specifically just show packages which are compatible with the version of .Net Core which the user has chosen to write their app in... rather than suggesting to them versions which your own Dependencies section already shows won't be compatible with their project...)
(微软：这将是有帮助的NuGet包管理器屏幕切换到专门只给出了与.net核心的版本，用户已经选择了写自己的应用程序中......而不是暗示他们的版本兼容的包，你的自己的Dependencies部分已经显示出与他们的项目不兼容...)
Now, I prefer using the "Database First" approach, where I already have a database "live and kicking", and then link it to my Web API. For this article, I have created a SQL Server database on my localhost server called "Southwind", and it contains four tables, Location, Department, Employee and Logging.
现在，我更喜欢使用“ 数据库优先 ”方法，在该方法中，我已经有了一个“实时运行”的数据库，然后将其链接到我的Web API。 在本文中，我在本地服务器“ Southwind ”上创建了一个SQL Server数据库，它包含四个表： Location ， Department ， Employee和Logging 。
If you want to follow along, I have provided a "Southwind.sql" script which will create this database, the tables, and the data for you.
如果您想继续学习，我提供了一个“ Southwind.sql ”脚本，它将为您创建该数据库，表和数据。
We can actually get Visual Studio to create the classes for us, based on the structure of these tables. To do this, open up the Package Manager Console (click on Tools \ Nuget Package Manager \ Package Manager Console), and enter the following command, replacing my connection string with a connection string for your own database:
实际上，我们可以使Visual Studio根据这些表的结构为我们创建类。 为此，请打开软件包管理器控制台(单击工具\ Nuget软件包管理器\软件包管理器控制台 )，然后输入以下命令，将您的数据库的连接字符串替换为我的连接字符串：
Scaffold-DbContext "Server=localhost;Database=Southwind;
Trusted_Connection=True;" Microsoft.EntityFrameworkCore.SqlServer -OutputDir Models
This will magically create a "SouthwindContext" class for us, as well as four classes, one for each of the database tables. Note that, if you wanted, you could've asked for just some (but not all) of your database tables to have classes created for them, by using the -Tables parameter, then listing the table names.
这将为我们神奇地创建一个“ SouthwindContext ”类，以及四个类，每个数据库表一个。 请注意，如果需要，可以通过使用-Tables参数，然后列出表名称，来要求某些 (但不是全部)数据库表为其创建类。
Scaffold-DbContext "Server=localhost;Database=Southwind;
Trusted_Connection=True;" Microsoft.EntityFrameworkCore.SqlServer
-OutputDir Models -Tables "Course","Lesson","User"
Note also that this Scaffold-DbContext command will not work if your code doesn't currently build successfully beforehand. So, check that your code is building okay before running that command.
另请注意，如果您的代码当前尚未预先成功构建，则此Scaffold-DbContext命令将无法工作 。 因此，在运行该命令之前，请检查您的代码是否可以正常构建。
You can check out the other options here.
您可以在此处查看其他选项。
Next, we need to tell our appsettings.json file about our database connection, so open this file, and add this to the top:
接下来，我们需要告知我们的appsettings.json文件有关数据库连接的信息，因此请打开此文件，并将其添加到顶部：
"ConnectionStrings": {
"SouthwindDatabase": "Server=.;Database=Southwind;Trusted_Connection=True;"
},
Now, open up startup.cs, and add this to the ConfigureServices() function:
现在，打开startup.cs ，并将其添加到ConfigureServices()函数中：
var ConnectionString = Configuration.GetConnectionString("SouthwindDatabase");
services.AddDbContext<SouthwindContext>(options =>
options.UseSqlServer(ConnectionString)
);
Before we proceed, we need to make sure this code is building successfully, but right now, it won't, as it's missing two using statements. You can either add them manually at the top of Startup.cs:
在继续之前，我们需要确保此代码成功构建，但是现在不会，因为它缺少两个using语句。 您可以在Startup.cs顶部手动添加它们：
using Microsoft.EntityFrameworkCore;
using MikesBank.Models;
...or you can click on SouthwindContext and UseSqlServer in the code above, and use "CTRL" + "." to get Visual Studio to add the using statements for you.
...或者您可以在上面的代码中单击SouthwindContext和UseSqlServer ，然后使用“ CTRL” +“”。 使Visual Studio为您添加using语句。
If you try to Build again, it should now build successfully, and we can continue.
如果您尝试再次构建，那么它现在应该可以成功构建，我们可以继续。
We now have all the building blocks to add database CRUD operations to our Web API.
现在，我们具有将数据库CRUD操作添加到Web API的所有构造块。
Delete the ValuesController.cs file in the Controllers folder, then right-click on the Controllers folder, select Add \ Controller, select the last option "API Controller with actions, using Entity Framework", and click on Add.
删除Controllers文件夹中的ValuesController.cs文件，然后右键单击Controllers文件夹，选择Add \ Controller ，选择最后一个选项“ 使用实体框架进行操作的API Controller ”，然后单击Add 。
Now, you can select one of your models, and get Visual Studio to create a set of endpoints for you. I'm going to select the Employee model:
现在，您可以选择一个模型，然后获取Visual Studio为您创建一组端点。 我将选择Employee模型：
Then click on the "Add" button, and, just like that, we have a set of CRUD endpoints for one of our tables.
然后单击“ 添加 ”按钮，就这样，我们为其中一个表设置了一组CRUD端点。
We can even go into the controller file ("EmployeeController.cs", in my example), and modify the comments, to make it more Swagger-friendly. Just delete the existing comment, then, with the cursor on an empty line just above [HttpGet], type ///, and Visual Studio will provide you with placeholders to type in your comments.
我们甚至可以进入控制器文件(在我的示例中为“ EmployeeController.cs ”)，并修改注释，以使其对Swagger更友好。 只需删除现有注释，然后将光标放在[HttpGet]上方的空白行上，键入/// ，Visual Studio将为您提供占位符以键入您的注释。
/// <summary>
/// Load a list of all employee records from the database
/// </summary>
/// <returns>
/// An enumerable list of Employee records
/// </returns>
[HttpGet]
public async Task<ActionResult<IEnumerable<Employee>>> GetEmployee()
{
return await _context.Employee.ToListAsync();
}
If we run our project now, the Swagger page will appear, we can select the GET endpoint for Employees, and get a list of all employees.
如果我们现在运行项目，将显示Swagger页面，我们可以为Employees选择GET端点，并获取所有employees的列表。
One small change I (personally) would make at this point: normally, when I'm getting a list of Employee records, I don't want the entire hierarchy included for each record (which department each employee belongs to, and which location that department is in), I just want the Employee record, nothing more.
此时，我(个人)将做一个小更改：通常，当我获取Employee记录列表时，我不希望为每个记录包括整个层次结构(每个employee所属的department以及该location department )，我只想要Employee记录，仅此而已。
You can prevent this entire hierarchy from being serialized by opening up the Employee.cs file, and adding a [JsonIgnore] before the virtual fields (you'll also need to use the CTRL + "." trick to get Visual Studio to add a using statement for this):
您可以通过打开Employee.cs文件并在virtual字段之前添加[JsonIgnore]来防止对整个层次结构进行序列化(您还需要使用CTRL +“。”技巧来使Visual Studio添加一个为此using语句)：
[JsonIgnore]
public virtual Department Dep { get; set; }
[JsonIgnore]
public virtual Employee EmpManager { get; set; }
[JsonIgnore]
public virtual ICollection<Employee> InverseEmpManager { get; set; }
You can also change the AddMvc() settings in Startup.cs, to prevent JSON.Net getting a little carried away with fetching too many parents/owners of each Employee record:
您还可以在Startup.cs中更改AddMvc()设置，以防止JSON.Net因获取每个Employee记录的太多父母/所有者而有些生气：
services.AddMvc()
.SetCompatibilityVersion(CompatibilityVersion.Version_2_2)
.AddJsonOptions(
options => options.SerializerSettings.ReferenceLoopHandling =
Newtonsoft.Json.ReferenceLoopHandling.Ignore
);
With this change in place, I just receive a list of Employee records, nothing else.
进行此更改后，我仅收到一个Employee记录列表，仅此而已。
And that's how we make our Web API connect to, and read/write data to our SQL Server database. I'd love to take credit for this, but as you've seen, Visual Studio does (nearly) all the work for us. We just have to remember the steps to take.
这就是我们使Web API连接到SQL Server数据库以及向SQL Server数据库读取/写入数据的方式。 我很乐意为此功劳，但是正如您所看到的，Visual Studio为我们做了(几乎)所有工作。 我们只需要记住要采取的步骤。
4.登录到SQL Server (4. Logging to SQL Server)
Now, I really don't like it when an error/exception occurs, and the error string just goes to some random .txt file stored somewhere on the IIS server. It's far more useful to have the message sent to a Logging table on SQL Server, so we can track such problems, perhaps list them on a "Log viewer" screen for our Admins to keep track of, save the Stack Trace, and so on.
现在，当发生错误/异常时，我真的不喜欢它，错误字符串只是进入存储在IIS服务器上某个位置的一些随机.txt文件。 将消息发送到SQL Server上的Logging表要有用得多，因此我们可以跟踪此类问题，也许将它们列出在“ 日志查看器 ”屏幕上，以供管理员跟踪，保存堆栈跟踪等等。 。
Of course, there is a gotcha: if the exception is thrown because we can't connect to our database, then... well.... the exception message surely isn't going to get stored in the database, as it can't find it !
当然，有一个陷阱：如果由于无法连接数据库而引发了异常，那么……好吧……肯定不会将异常消息存储在数据库中，因为它可以找不到！
Of course, we could reach out to a third-party, like nLog, to handle our logging, but personally, I prefer to do it myself.
当然，我们可以联系第三方，例如nLog ，来处理日志，但是就我个人而言，我更喜欢自己做。
First, as you've seen, I have a Logging table in my SQL Server database.
首先，如您所见，我SQL Server数据库中有一个Logging表。
There's nothing over-complicated about this. The Log_Severity, the (exception) Log_Message and the Log_StackTrace fields will all come from whatever exception has just occurred, and I have a "Log_Source" field, which we could populate to say which area of the application threw the exception.
没有什么复杂的。 Log_Severity ，(例外) Log_Message和Log_StackTrace字段都来自刚刚发生的任何异常，而我有一个“ Log_Source ”字段，我们可以填充该字段来说明应用程序的哪个区域引发了异常。
Oh, and my "Update_Time" fields (I have one in each of my tables) always contain the date time in UTC timezone. We might well have users in different countries, who'll want to know when an exception occurred, in their local time.
哦，我的“ Update_Time ”字段(每个表中都有一个) 始终包含UTC timezone的日期时间。 我们很可能在不同国家/地区拥有用户，他们想在当地时间知道何时发生异常。
To use this (or any other) table structure in our code, here's what we need to do.
要在我们的代码中使用此(或任何其他)表结构，这是我们需要做的。
Create a new folder in our project called "LogProvider".
在我们的项目中创建一个名为“ LogProvider ”的新文件夹。
I have provided a LogProvider.zip file, extract the 4 files from this .zip into this folder.
我提供了一个LogProvider.zip文件，将.zip文件中的4个文件解压缩到该文件夹中。
In each of these files, the namespace is currently set to "
MikesBank.LogProvider". You will need to change this to your own namespace (depending on what you named your project).在每个文件中，名称空间当前都设置为“
MikesBank.LogProvider”。 您将需要将其更改为自己的名称空间(取决于您为项目命名的名称)。In the SqlHelper.cs and DBLogger.cs files, are code specific to my Logging database table. If your table has a different name, or fields, you will need to change this code.
在SqlHelper.cs和DBLogger.cs文件中，特定于我的Logging数据库表的代码。 如果表具有不同的名称或字段，则需要更改此代码。
In Startup.cs, add the line "
using MikesBank.LogProvider;"在Startup.cs中 ，添加“
using MikesBank.LogProvider;”行using MikesBank.LogProvider;In the
Configure()function, we need to inject an extra dependency:在
Configure()函数中，我们需要注入一个额外的依赖项：
public void Configure(IApplicationBuilder app,
IHostingEnvironment env, ILoggerFactory loggerFactory)
We can now add the following lines to the Configure() function:
现在，我们可以将以下行添加到Configure()函数中：
loggerFactory.AddConsole(Configuration.GetSection("Logging"));
// The following "AddContext" comes from our DBLoggerExtensions class.
// We will log any errors of Information of higher.
// (Trace=0, Debug=1, Information=2, Warning=3, Error=4, Critical=5, None=6)
loggerFactory.AddContext(LogLevel.Information,
Configuration.GetConnectionString("SouthwindDatabase"));
At this point, if you try to build your project, you'll probably get an error saying that ILoggerFactory doesn't contain a definition for "AddContext". To fix this, we need to tell it where our extension method is. At the top of Startup.cs, add this line:
此时，如果尝试构建项目，则可能会收到一条错误消息， ILoggerFactory不包含“ AddContext ”的定义。 要解决此问题，我们需要告诉它扩展方法在哪里。 在Startup.cs的顶部，添加以下行：
using MikesBank.LogProvider;
Now, let's give this a go.
现在，让我们开始吧。
In the EmployeesController.cs, I can now add logging. To do this, I need to add a new variable:
现在，在EmployeesController.cs中 ，可以添加日志记录。 为此，我需要添加一个新变量：
private readonly ILogger logger;
...as well as a new using statement....
以及新的using语句。
using Microsoft.Extensions.Logging;
And then, I can modify the constructor:
然后，我可以修改构造函数：
public EmployeesController(SouthwindContext context, ILoggerFactory loggerFactory)
{
_context = context;
logger = loggerFactory.CreateLogger<EmployeesController>();
}
And that's it!
就是这样！
You can now happily slip is as many LogInformation, LogWarning or LogErrors as you want. For example:
现在，您可以根据需要LogInformation地滑动LogInformation多个LogInformation ， LogWarning或LogErrors 。 例如：
[HttpGet]
public async Task<ActionResult<IEnumerable<Employee>>> GetEmployee()
{
logger.LogInformation("Loading a list of Employee records");
return await _context.Employee.ToListAsync();
}
Just one annoying problem though. After running this code, and calling the GET endpoint, I do get the "Loading a list of Employee records" message in my Logging table, but I also get a load of messages from behind the scenes. Personally, I find that these make it incredibly hard to find the Log messages which I actually am interested in, and prefer to turn these off.
只是一个烦人的问题。 运行此代码，并调用后GET终点，我就在我拿到“ 加载员工的列表记录 ”消息Logging表，但我也从幕后获取消息的负载 。 我个人认为，这些使查找我真正感兴趣的Log消息变得异常困难，并且更喜欢将其关闭。
To do this, you can go into the appsettings.Development.json file, and modify which type of log messages will be included from the Microsoft and System libraries. If you change these to "Warning", then your log won't fill up with all of these extra Entity Framework messages.
为此，您可以进入appsettings.Development.json文件，并修改Microsoft和System库中将包括的日志消息类型。 如果将它们更改为“ Warning ”，则您的日志将不会填满所有这些额外的Entity Framework消息。
{
"Logging": {
"LogLevel": {
"Default": "Debug",
"System": "Warning",
"Microsoft": "Warning"
}
}
}
Obviously, this is optional, and perhaps you will want to see such verbose information in, say, the Development build. It's up to you.
显然，这是可选的，也许您会希望在开发版本中看到这些详细信息。 由你决定。
One last thing.
最后一件事。
One of my biggest irritations with some APIs is the dreaded "HTTP Response 500: Internal Server Error". When your own API throws this exception, this is often because something has gone wrong, and your code hasn't bothered to catch the problem, or handle it gracefully.
我对某些API的最大烦恼之一是可怕的“ HTTP响应500：内部服务器错误”。 当您自己的API引发此异常时，通常是因为出了点问题，您的代码也没有费心去解决问题或优雅地处理它。
And, of course, because you haven't caught the exception, you certainly won't have tried to send it to the log, so that your developers and support team can look into the cause. So, please, wrap each of your endpoints in a try...catch, and make sure any exception messages end up in your Logging table.
而且，当然，因为您没有发现异常，所以您当然不会尝试将其发送到日志，以便您的开发人员和支持团队可以调查原因。 因此， 请将每个端点包装在try...catch ，并确保所有异常消息最终出现在Logging表中。
It's so easy to do, but will save a lot of hair-pulling later.
这很容易做到，但是以后会省很多拉头发。
public async Task<ActionResult<IEnumerable<Employee>>> GetEmployee()
{
try
{
logger.LogInformation("Loading a list of Employee records");
return await _context.Employee.ToListAsync();
}
catch (Exception ex)
{
logger.LogError(ex, "An exception occurred in the GetEmployee() endpoint");
return new BadRequestObjectResult(ex.Message);
}
}
Obviously, in a Production release, you might not want to return the full exception message, as above, and you can modify this as you find suitable.
显然，在生产版本中，您可能不希望如上所述返回完整的异常消息，并且可以根据需要进行修改。
5.启用CORS (5. Enabling CORS)
If you're developing software, chances are you'll want your Angular/JavaScript apps to call your Web API endpoints while they're still hosting on localhost, Unfortunately, CORS will prevent this, and throw lots of errors for you.
如果您正在开发软件，则很可能希望Angular / JavaScript应用程序在仍托管在localhost上的同时调用Web API端点，不幸的是，CORS会阻止这种情况，并给您带来很多错误。
Now, there are plenty of walkthroughs out there showing how to setup CORS correctly, but if you just want to get rid of these errors (on all of your Controllers) while you're developing, here's what to do:
现在，这里有很多演练，展示了如何正确设置CORS，但是，如果您只是想在开发时摆脱这些错误(在所有Controller上)，请执行以下操作：
Add the
Microsoft.AspNetCore.Corsnuget package.添加
Microsoft.AspNetCore.Corsnuget包。In Startup.cs, go into the
ConfigureServices()method add add this line:在Startup.cs中 ，进入
ConfigureServices()方法，添加以下行：
services.AddCors();
Also in Startup.cs, go into the
Configure()method add add this line:同样在Startup.cs中 ，进入
Configure()方法，添加以下行：
app.UseCors(builder =>
builder.AllowAnyOrigin().AllowAnyHeader().AllowAnyMethod()
);
Again, this is a shortcut, purely for getting rid of these CORS errors during development. When deploying to a live application, you will want to create your own policies, and perhaps just apply them to certain Controllers or endpoints.
同样，这是一个捷径，纯粹是为了在开发过程中摆脱这些CORS错误。 部署到实时应用程序时，您将需要创建自己的策略，也许只是将其应用于某些Controller或端点。
But these two changes will at least prevent you from being blocked during development.
但是，这两项更改至少将防止您在开发过程中受阻。
6.添加“导出到Excel” (6. Adding "Export to Excel")
Yeah, I know... chances are, none of us are going to add a Web API endpoint which returns a raw Excel file, containing all the data from one of your tables. But, we've got this far, and because it's so damn easy, let's just see how we would do this. If nothing else, this is useful for your other ASP.NET Core projects.
是的，我知道...很可能，我们没人会添加Web API端点，该端点返回一个原始Excel文件，其中包含来自一个表的所有数据。 但是，我们已经走了很远，而且因为它是如此简单，所以让我们看看我们将如何做到。 如果没有其他要求，这对您的其他ASP.NET Core项目很有用。
First, we need to go into "NuGet Package Manager" one last time, and install the "DocumentFormat.OpenXml" package. This lets us create Excel files (*.xlsx) even if we don't have Excel on our server.
首先，我们需要最后一次进入“ NuGet软件包管理器 ”，并安装“ DocumentFormat.OpenXml ”软件包。 即使我们在服务器上没有Excel，这也使我们可以创建Excel文件( * .xlsx )。
Next, create a folder in your project called Helpers and save the attached CreateExcelFile.cs into this folder.
接下来，在您的项目中创建一个名为Helpers的文件夹，并将附加的CreateExcelFile.cs保存到该文件夹中。
This C# library was the "Export to Excel" library that I wrote back in 2014, and you can read more about it in my CodeProject article.
这个C＃库是我在2014年写的“ 导出到Excel ”库，您可以在我的CodeProject文章中阅读有关它的更多信息。
With this file in place, we're ready to go.
有了此文件后，我们就可以开始了。
To add an Export endpoint to our controller is as simple as loading our data, then calling the StreamExcelDocument function, passing it the data to be exported, and the filename to use:
向我们的控制器添加Export端点就像加载我们的数据一样简单，然后调用StreamExcelDocument函数，将要导出的数据以及要使用的文件名传递给它：
[HttpGet("ExportToExcel")]
public async Task<IActionResult> ExportEmployeesToExcel()
{
try
{
List<Employee> employees = await _context.Employee.ToListAsync();
FileStreamResult fr = ExportToExcel.CreateExcelFile.StreamExcelDocument
(employees, "Employees.xlsx");
return fr;
}
catch (Exception ex)
{
return new BadRequestObjectResult(ex);
}
}
How simple is that !!
多么简单！
As someone who has worked in the financial industry, let me tell you, having a simple, reusable "Export to Excel" function is golden. It's the first function that my clients would ask for, every single time... they love their Excel !
正如有人谁已经在金融行业工作，让我告诉你，有一个简单的，可重复使用的“ 导出到Excel”功能是金 。 这是我的客户每次都要求的第一个功能……他们喜欢自己的Excel！
7.对Docker的支持 (7. Support for Docker)
When I first started looking into Docker support, I really thought this'd be straightforward. After all, when you create your project in Visual Studio 2017 or 2019, it asks if you want Docker support. From there, it's really easy to use "Publish" to publish the application as a Docker image to Azure. So this must be really easy.
当我第一次开始研究Docker支持时，我真的以为这很简单。 毕竟，当您在Visual Studio 2017或2019中创建项目时，它会询问您是否需要Docker支持。 从那里开始，使用“ Publish ”将应用程序作为Docker映像发布到Azure真的很容易。 因此，这必须非常容易。
It's not.
不是。
First of all, when you create your Project and say that you do want Docker support, you're likely to say that you want it for Windows (rather than Linux). This creates a file called Dockerfile for you, but (at the time of writing) the version of .NET Core which it gives you isn't supported in Azure. As such, when you Publish to Azure, you'll see your app list in your Azure Portal as a new "Container Registry".... but Azure won't be able to run it.
首先，当您创建项目并说您确实想要Docker支持时，您可能会说您想要Windows(而不是Linux)支持它。 这会为您创建一个名为Dockerfile的文件，但是(在撰写本文时)Azure 不支持它提供的.NET Core版本。 这样，当您发布到Azure时，您将在Azure门户中看到作为新的“容器注册表”的应用列表....但是Azure将无法运行它。
There is actually an option to Run an instance of this Registry, but actually discovering this option is not so clear.
实际上，有一个选项可以运行此注册表的实例 ，但是实际上发现这个选项还不是很清楚。
Worst still, if something goes wrong, the error message is all but useless.
更糟糕的是，如果出现问题，错误消息几乎毫无用处。
What the heck does that mean?
这是什么意思？
To find out what caused the error, you need to click on the ">" button at the top-right of the Azure window, and run the following command:
要找出导致错误的原因，您需要单击Azure窗口右上方的“>”按钮，然后运行以下命令：
az group deployment operation list
--resource-group <YourResourceGroup> --name Microsoft.ContainerInstance
This will then show you a lengthy JSON message, containing the error:
然后，这将向您显示冗长的JSON消息，其中包含错误：
(I PhotoShopped this lengthy error message onto two lines, so you can easily read it.)
(我将这条冗长的错误消息购物了两行，因此您可以轻松阅读它。)
Ah, okay. So, Visual Studio has created a Container Registry for us, but using a version of Windows which it doesn't actually support. I'm sure the usability of this could be improved....
啊好吧。 因此，Visual Studio为我们创建了一个容器注册表，但是使用了它实际上不支持的Windows版本。 我确信可以提高此功能的可用性。
The cause of this problem seems to be the Dockerfile file which Visual Studio created for us. It mentions versions of the dotnet core SDK and runtime which are higher than what Azure supports.
造成此问题的原因似乎是Visual Studio为我们创建的Dockerfile文件。 它提到的dotnet核心SDK和运行时版本高于Azure支持的版本。
We can fix this by changing the first lines to:
我们可以通过将第一行更改为以下内容来解决此问题：
FROM mcr.microsoft.com/dotnet/core/runtime:2.2-nanoserver-sac2016 AS base
WORKDIR /app
EXPOSE 80
EXPOSE 443
FROM mcr.microsoft.com/dotnet/core/sdk:2.2-nanoserver-sac2016 AS build
WORKDIR /src
COPY ["MikesBank/MikesBank.csproj", "MikesBank/"]
... etc ...
With this change in place, you can re-publish to Azure, and run an instance of this new container registry.
完成此更改后，您可以重新发布到Azure，并运行此新容器注册表的实例。
在本地运行Docker映像 (Running the Docker Image Locally)
We are also able to run the Docker Registry on our own local copy of Windows. To do this, make sure you have Docker For Windows and the Azure CLI installed, then follow these steps:
我们还能够在我们自己的Windows本地副本上运行Docker Registry。 为此，请确保已安装Docker For Windows和Azure CLI ，然后执行以下步骤：
- Tell the Azure CLI the GUID of the subscription where your Container Registry is stored. 将存储容器注册表的订阅的GUID告诉Azure CLI。
- Log into the subscription. 登录到订阅。
- Log into the Azure Container Registry. 登录到Azure容器注册表。
Use the Docker "pull" command to download a copy of the image to your laptop/server.
使用Docker“ pull ”命令将映像的副本下载到您的笔记本电脑/服务器。
- Run the local copy of this image. 运行此图像的本地副本。
- Get a list of all running containers, to obtain the ID of our new container. 获取所有正在运行的容器的列表，以获取新容器的ID。
Run the Docker "inspect" command to find out the IP address which you need to open in a browser to open your app.
运行Docker的“ inspect ”命令，找出需要在浏览器中打开的IP地址才能打开您的应用程序。
So, overall the commands would look something like the following (obviously, you'll need to specify the names Azure has created for your own copy of the Registry):
因此，总体而言，这些命令如下所示(显然，您需要指定Azure为您自己的注册表副本创建的名称)：
az account set --subscription <subscription_id>
az login
az acr login --name MikesBank20190925022604
docker pull mikesbank20190925022604.azurecr.io/mikesbank:latest
docker run mikesbank:latest
docker image ls
docker inspect -f "{{ .NetworkSettings.Networks.nat.IPAddress }}" <container_id>
Phew. At the end of all that, you will have the IP address which you can open in your browser, and see the Swagger page. Nice!
ew 最后，您将获得可在浏览器中打开的IP地址，并查看Swagger页面。 真好！
摘要 (Summary)
And that's it!
就是这样！
We now have a nice ASP.NET Core Web API project, with a friendly Swagger page, a SQL Server connection, and Logging. Plus "Export to Excel", if we really want to impress at our next job interview, and to be able to tell them that your APIs support "CRUDE".
现在，我们有了一个不错的ASP.NET Core Web API项目，它具有友好的Swagger页面，SQL Server连接和日志记录。 如果我们真的想在下一次工作面试中留下深刻的印象，并且能够告诉他们您的API支持“ CRUDE”，那么还可以加上“导出到Excel”。
Feel free to get in touch, and leave comments/suggestions.
随时保持联系，并留下评论/建议。
翻译自: https://www.codeproject.com/Articles/5205692/Web-API-Adding-Swagger-SQL-Server-Logging-CORS-Exp
swagger 导出api
3977
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
