深度优先

这个家伙好懒，除了文章什么都没留下

0%

发表于 分类于
本文字数： 7.7k 阅读时长 ≈ 7 分钟

NLog 记录日志是微软官方推荐使用。

接下来，通过配置日志记录到文件和Sql Server数据库。

第一步：首先添加包NLog.Config （可通过微软添加包命令Install-Package 包名进行添加，也可以通过管理NuGet程序包进行添加），添加成功后会生成NLog.config配置文件。并对该配置文件进行配置。详细配置可参考Git上 NLog说明。

以下是我个人配置：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
<?xml version="1.0" encoding="utf-8" ?>
<nlog xmlns="http://www.nlog-project.org/schemas/NLog.xsd"
      xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
      xsi:schemaLocation="http://www.nlog-project.org/schemas/NLog.xsd NLog.xsd"
      autoReload="true"
      throwExceptions="false"
      internalLogLevel="Warn"   
      internalLogFile="Logs/nlog-internal.log">
  
  <!--internalLogLevel="Off"-->
  <!-- optional, add some variables
  https://github.com/nlog/NLog/wiki/Configuration-file#variables
  -->
  <variable name="myvar" value="myvalue"/>

  <!--
  See https://github.com/nlog/nlog/wiki/Configuration-file
  for information on customizing logging rules and outputs.
   -->
  <targets>

    <!--
    add your targets here
    See https://github.com/nlog/NLog/wiki/Targets for possible targets.
    See https://github.com/nlog/NLog/wiki/Layout-Renderers for the possible layout renderers.
    -->

    <!--
    Write events to a file with the date in the filename.
    <target xsi:type="File" name="f" fileName="${basedir}/logs/${shortdate}.log"
            layout="${longdate} ${uppercase:${level}} ${message}" />
    -->

    <!-- write logs to file -->
    <target xsi:type="File" name="allfile" fileName="Logs/${date:format=yyyyMM}/nlog-all-${shortdate}.log"
             layout="${longdate}|${event-properties:item=EventId.Id}|${uppercase:${level}}|${logger} ${newline}${message} ${exception} ${newline}" />

    <target xsi:type="File" name="ownFile-web" fileName="Logs/${date:format=yyyyMM}/nlog-own-${shortdate}.log"
             layout="${longdate}|${event-properties:item=EventId.Id}|${uppercase:${level}}|${logger} ${newline}${message} ${exception} ${newline} --- |url: ${aspnet-request-url}|action: ${aspnet-mvc-action}" />

    <target xsi:type="Null" name="blackhole" />

    <target xsi:type="Database" name="database">
      <connectionString>${var:connectionString}</connectionString>
      <commandText>
        insert into syslogs (Application,Levels,Operatingtime,Operatingaddress,Userid,Logger,Callsite,Requesturl,Referrerurl,Action,Message,Exception)
        values (@application,@levels,@operatingtime,@operatingaddress,@userid,@logger,@callSite,@requesturl,@referrerurl,@action,@message,@exception);
      </commandText>
      <parameter name="@application" layout="WebApi" />
      <parameter name="@levels" layout="${level}" />
      <parameter name="@operatingTime" layout="${date}" />
      <parameter name="@operatingaddress" layout="${aspnet-Request-IP}" />
      <parameter name="@userid" layout="1" />
      <parameter name="@logger" layout="${logger}" />
      <parameter name="@callSite" layout="${callsite}" />
      <parameter name="@requesturl" layout="${aspnet-request-url}" />
      <parameter name="@referrerurl" layout="${aspnet-request}" />
      <parameter name="@action" layout="${aspnet-mvc-action}" />
      <parameter name="@message" layout="${message}" />
      <parameter name="@exception" layout="${exception:tostring}" />
    </target>
    
  </targets>

  <rules>
    <!-- add your logging rules here -->

    <!--
    Write all events with minimal level of Debug (So Debug, Info, Warn, Error and Fatal, but not Trace)  to "f"
    <logger name="*" minlevel="Debug" writeTo="f" />
    -->

    <!--All logs, including from Microsoft-->
    <!--minlevel 改为Trace 跟踪全部 Error 只捕获异常-->
    <logger name="*" minlevel="Error" writeTo="allfile" />

    <!--Skip Microsoft logs and so log only own logs-->
    <logger name="Microsoft.*" minlevel="Trace" writeTo="blackhole" final="true" />
    <logger name="*" minlevel="Trace" writeTo="ownFile-web" />
    <logger name="*" minlevel="Trace" writeTo="database" />    
    
  </rules>
</nlog>


<!--增加引用
<PackageReference Include="NLog.Extensions.Logging" Version="1.2.1" />
<PackageReference Include="NLog.Web.AspNetCore" Version="4.6.0" />-->

NLog.config

说明：targets 中有一节点为Database，是配置将日志写入数据库中，注意需要在数据库中添加该记录日志表。

1
2
3
4
5
6
7
8
9
10
11
12
13
{
  "ConnectionStrings": {
    "DefaultConnection": "Data Source=192.168.30.133;Initial Catalog=Test;User ID=sa;Password=123456;Trusted_Connection=True;MultipleActiveResultSets=true;Integrated Security=false;"
  },
  "Logging": {
    "LogLevel": {
      "Default": "Warning"
    }
  },
  "AllowedHosts": "*"
}

appsettings.json

第二步：添加包NLog.Web.AspNetCore，在Program.cs中的WebHost加入”.UseNLog()”（该属于程序集NLog.Web，需要添加引用using NLog.Web;）,即为添加nlog.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
using Microsoft.AspNetCore;
using Microsoft.AspNetCore.Hosting;
using NLog.Web;

namespace WebApi
{
    public class Program
    {
        public static void Main(string[] args)
        {
            CreateWebHostBuilder(args).Build().Run();
        }

        public static IWebHostBuilder CreateWebHostBuilder(string[] args) =>
            WebHost.CreateDefaultBuilder(args)
                .UseStartup<Startup>()
                .UseNLog(); //加入nlog日志
    }
}

Program.cs

第三步：在Startup.cs中的Configure方法中添加记日志代码，即需要加载的配置文件和配置日志写入数据库连接字符串代码。注意：为避免中文乱码问题需要添加System.Text.Encoding.CodePages包。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
public void Configure(IApplicationBuilder app, IHostingEnvironment env)
{

    #region Nlog记日志
    //将日志记录到数据库                 config/NLog.config
    NLog.LogManager.LoadConfiguration("nlog.config").GetCurrentClassLogger();       NLog.LogManager.Configuration.Variables["connectionString"] = Configuration.GetConnectionString("DefaultConnection");   Encoding.RegisterProvider(CodePagesEncodingProvider.Instance);  //避免日志中的中文输出乱码
    #endregion

    if (env.IsDevelopment())
        app.UseDeveloperExceptionPage();
    else
        app.UseHsts();
    app.UseHttpsRedirection();
    app.UseMvc();
}

Configure方法

第四步：使用微软推荐的方式在在构造方法中将将日志对象注入。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
public class UsersController : Controller
{
    /// <summary>
    /// 日志对象
    /// </summary>
    private readonly ILogger logger;    
      public UsersController(ILoggerFactory loggerFactory)
    {
        this.logger = loggerFactory.CreateLogger<UsersController>();

        #region 测试日志
        logger.LogTrace("开发阶段调试，可能包含敏感程序数据", 1);
        logger.LogDebug("开发阶段短期内比较有用，对调试有益。");
        logger.LogInformation("你访问了首页。跟踪程序的一般流程。");
        logger.LogWarning("警告信息！因程序出现故障或其他不会导致程序停止的流程异常或意外事件。");
        logger.LogError("错误信息。因某些故障停止工作");
        logger.LogCritical("程序或系统崩溃、遇到灾难性故障！！！");
        #endregion
    }
}
构造方法中注入

所有工作完成，运行程序。在配置NLog路径下生成日志文件，同时，在数据库中生成日志。

)

MySql的问题：

1
2
3
4
5
6
7
8
#1楼 2019-04-30 14:35 3369
你好，我这边使用Pomelo.EntityFrameworkCore.MySql连接mysql，dbProvider="Pomelo.EntityFrameworkCore.MySql"，可以向文件中写入日志，但是无法向数据库中写入日志
支持(0) 反对(0)
  修改 删除#2楼 2019-12-10 18:03 占位符,😐!
@ 3369
还得安装下：MySql.Data
这个还是：dbProvider="MySql.Data.MySqlClient.MySqlConnection, MySql.Data"，
不知道有没有更好的方法

发表于 分类于
本文字数： 2.5k 阅读时长 ≈ 2 分钟

在用angular2调用后台接口的时候，遇到了两个问题1、请求头没有cookies;2、对两次请求，node后端都开了一个新的线程。对于这两个问题，其实我认为解决了请求头cookie的问题，后面的问题自然解决。对于cookie有什么作用，为什么要有这个cookie，看下图前后端请求模式。

clipboard.png

clipboard.png

clipboard.png

不难看出，这个cookie是获取session会话中信息的凭证，只有向后台传送匹配的cookie，才能得到相应的信息，否则将创建新的session。

到这里，来看下XMLHttpRequest.withCredentials这个属性，度娘解释如下

1
2
3
4
5
XMLHttpRequest.withCredentials  属性是一个Boolean类型，它指示了是否该使用类似cookies,authorization headers(头部授权)或者TLS客户端证书这一类资格证书来创建一个跨站点访问控制（cross-site Access-Control）请求。在同一个站点下使用withCredentials属性是无效的。

此外，这个指示也会被用做响应中cookies 被忽视的标示。默认值是false。

如果在发送来自其他域的XMLHttpRequest请求之前，未设置withCredentials 为true，那么就不能为它自己的域设置cookie值。而通过设置withCredentials 为true获得的第三方cookies，将会依旧享受同源策略，因此不能被通过document.cookie或者从头部相应请求的脚本等访问。

angular2中http显然也是基于xml的请求，必定有这个属性。再看下http接口的请求接口说明。默认情况下，一般浏览器的CORS跨域请求都是不会发送cookie等认证信息到服务端的，除非指定了xhr.withCredentials = true，但是只有客户端单方面的设置了这个值还不行，服务端也需要同意才可以，所以服务端也需要设置好返回头Access-Control-Allow-Credentials: true；还有一点要注意的，返回头Access-Control-Allow-Origin的值不能为星号，必须是指定的域，否则cookie等认证信息也是发送不了。

1
2
3
4
5
6
7
8
9
10
Interface Details

url : string
method : string|RequestMethod
search : string|URLSearchParams|{[key: string]: any | any[]}
params : string|URLSearchParams|{[key: string]: any | any[]}
headers : Headers
body : any
withCredentials : boolean
responseType : ResponseContentType

问题找到了，就是这货！再修改下请求代码

clipboard.png

再来看下请求头信息

clipboard.png

clipboard.png

显然，请求头都已经被加上了cookie，而且这个cookie都是匹配的，看似没什么问题，但是再看看请求接口的response，没有任何信息，但后台明显有返回信息，而且这个response并非每次都不返回任何信息，存在偶然性。

clipboard.png

到这里，明显就出现了另一个问题，就是跨域，看看浏览器的console信息就知道了

1
XMLHttpRequest cannot load http://neil.com:8090/api/send. The value of the 'Access-Control-Allow-Origin' header in the response must not be the wildcard '*' when the request's credentials mode is 'include'. Origin 'http://localhost:4200' is therefore not allowed access. The credentials mode of requests initiated by the XMLHttpRequest is controlled by the withCredentials attribute.

到这里，问题清晰了，只要解决跨域问题，整个流程就跑通。网上也有很多办法处理跨域，但如果不想用jsonp呢，那只能从服务器上动刀子。查了些资料，最靠普的无非在服务端加上request头部设置

1
2
3
4
5
6
7
8
9
//设置跨域访问
app.all('*', function (req, res, next) {
    res.header("Access-Control-Allow-Origin", "http://neil.com:4200");   //设置跨域访问
    res.header('Access-Control-Allow-Credentials', 'true');
    res.header('Access-Control-Allow-Methods', 'GET, POST, PUT, DELETE, OPTIONS');
    res.header('Access-Control-Allow-Headers', 'Origin, No-Cache, X-Requested-With, If-Modified-Since, Pragma, Last-Modified, Cache-Control, Expires, Content-Type, X-E4M-With');
    res.header("Content-Type", "application/x-www-form-urlencoded");
    next();
});

这里说明下，‘Access-Control-Allow-Origin’必须设置请求侧的域名，不然无法跨域，不能解决问题。

clipboard.png

现在，问题已经全部处理。

发表于 分类于
本文字数： 29k 阅读时长 ≈ 26 分钟

数据库

我们先在SQL Server数据库中建立一个Book表：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
 CREATE TABLE [dbo].[Book](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [BookName] [nvarchar](50) NULL,
    [BookDescription] [nvarchar](50) NULL,
    [ISBN] [nvarchar](20) NULL,
    [CreateTime] [datetime] NULL,
 CONSTRAINT [PK_Book] PRIMARY KEY CLUSTERED 
(
    [ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[Book] ADD  CONSTRAINT [DF_Book_CreateTime]  DEFAULT (getdate()) FOR [CreateTime]
GO

然后插入如下数据：

1
2
3
4
5
6
7
8
9
10
INSERT [dbo].[Book] ([BookName], [BookDescription], [ISBN]) VALUES (N'Chinese', N'Chinese', N'0001')
GO
INSERT [dbo].[Book] ([BookName], [BookDescription], [ISBN]) VALUES (N'English', N'English', N'0002')
GO
INSERT [dbo].[Book] ([BookName], [BookDescription], [ISBN]) VALUES (N'Japanese', N'Japanese', N'0003')
GO
INSERT [dbo].[Book] ([BookName], [BookDescription], [ISBN]) VALUES (N'Russian', N'Russian', N'0004')
GO
INSERT [dbo].[Book] ([BookName], [BookDescription], [ISBN]) VALUES (N'Italian', N'Italian', N'0005')
GO

查询Book表的数据，如下图所示：

现在我们使用EF Core将Book表映射到.NET Core控制台项目中的Book实体上，Book实体如下所示：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
using System;
using System.Collections.Generic;

namespace EFCoreDB.Entities
{
    public partial class Book
    {
        public int Id { get; set; }
        public string BookName { get; set; }
        public string BookDescription { get; set; }
        public string Isbn { get; set; }
        public DateTime? CreateTime { get; set; }
    }
}

不使用事务

然后我们在.NET Core控制台项目Program类的Main方法中，使用DbContext（也就是FinanceDigitalToolContext）读取BookName为”Chinese”的Book实体，然后使用DbContext.SaveChanges方法两次更改其BookDescription属性的值，再从数据库中将其查询出来显示，代码如下：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
using EFCoreDB.Entities;
using System;
using System.Linq;
using System.Linq.Expressions;

namespace EFCoreDB
{
    class Program
    {
        static void Main(string[] args)
        {
            using (FinanceDigitalToolContext dbContext = new FinanceDigitalToolContext())
            {
                Expression<Func<Book, bool>> bookExpression = b => b.BookName == "Chinese";//构造查询条件，来查询BookName为Chinese的Book

                var chineseBook = dbContext.Book.First(bookExpression);//获取BookName为Chinese的Book实体chineseBook

                chineseBook.BookDescription = "This is a Chinese book";//更改chineseBook的BookDescription属性
                dbContext.SaveChanges();//用DbContext.SaveChanges方法保存更改到数据库

                chineseBook.BookDescription = "This is a very good Chinese book";//再次更改chineseBook的BookDescription属性
                dbContext.SaveChanges();//用DbContext.SaveChanges方法保存更改到数据库

                chineseBook = dbContext.Book.First(bookExpression);//重新获取BookName为Chinese的Book实体chineseBook

                //显示当前chineseBook的当前BookDescription属性值
                Console.WriteLine(chineseBook.BookName + " book has description: \"" + chineseBook.BookDescription + "\"");
            }

            Console.WriteLine("Press key to quit....");

            Console.ReadLine();
        }
    }
}

执行上面的代码，我们使用EF Core的日志功能，输出每次DbContext访问数据库时的后台日志信息：

首先在执行：

1
var chineseBook = dbContext.Book.First(bookExpression);//获取BookName为Chinese的Book实体chineseBook

时，EF Core的日志如下所示：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
=============================== EF Core log started ===============================
Opening connection to database 'FinanceDigitalTool' on server 'CNGDCAAITSQL01'.
=============================== EF Core log finished ===============================
=============================== EF Core log started ===============================
Opened connection to database 'FinanceDigitalTool' on server 'CNGDCAAITSQL01'.
=============================== EF Core log finished ===============================
=============================== EF Core log started ===============================
Executing DbCommand [Parameters=[], CommandType='Text', CommandTimeout='30']
SELECT TOP(1) [b].[ID], [b].[BookDescription], [b].[BookName], [b].[CreateTime], [b].[ISBN]
FROM [Book] AS [b]
WHERE [b].[BookName] = N'Chinese'
=============================== EF Core log finished ===============================
=============================== EF Core log started ===============================
Executed DbCommand (129ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
SELECT TOP(1) [b].[ID], [b].[BookDescription], [b].[BookName], [b].[CreateTime], [b].[ISBN]
FROM [Book] AS [b]
WHERE [b].[BookName] = N'Chinese'
=============================== EF Core log finished ===============================
=============================== EF Core log started ===============================
A data reader was disposed.
=============================== EF Core log finished ===============================
=============================== EF Core log started ===============================
Closing connection to database 'FinanceDigitalTool' on server 'CNGDCAAITSQL01'.
=============================== EF Core log finished ===============================
=============================== EF Core log started ===============================
Closed connection to database 'FinanceDigitalTool' on server 'CNGDCAAITSQL01'.
=============================== EF Core log finished ===============================
=============================== EF Core log started ===============================
Context 'Book' started tracking 'FinanceDigitalToolContext' entity. Consider using 'DbContextOptionsBuilder.EnableSensitiveDataLogging' to see key values.
=============================== EF Core log finished ===============================

从日志中我们可以看出来，dbContext.Book.First(bookExpression)在数据库中开启了一个数据库连接，并使用SQL语句做了查询，然后关闭了数据库连接。

然后在执行下面的代码

1
2
chineseBook.BookDescription = "This is a Chinese book";//更改chineseBook的BookDescription属性
dbContext.SaveChanges();//用DbContext.SaveChanges方法保存更改到数据库

时，EF Core的日志如下所示：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
=============================== EF Core log started ===============================
SaveChanges starting for 'FinanceDigitalToolContext'.
=============================== EF Core log finished ===============================
=============================== EF Core log started ===============================
DetectChanges starting for 'FinanceDigitalToolContext'.
=============================== EF Core log finished ===============================
=============================== EF Core log started ===============================
Unchanged 'Book.BookDescription' detected as changed and will be marked as modified. Consider using 'DbContextOptionsBuilder.EnableSensitiveDataLogging' to see property values.
=============================== EF Core log finished ===============================
=============================== EF Core log started ===============================
An 'Book' entity tracked by 'FinanceDigitalToolContext' changed from 'Unchanged' to 'Modified'. Consider using 'DbContextOptionsBuilder.EnableSensitiveDataLogging' to see key values.
=============================== EF Core log finished ===============================
=============================== EF Core log started ===============================
DetectChanges completed for 'FinanceDigitalToolContext'.
=============================== EF Core log finished ===============================
=============================== EF Core log started ===============================
Opening connection to database 'FinanceDigitalTool' on server 'CNGDCAAITSQL01'.
=============================== EF Core log finished ===============================
=============================== EF Core log started ===============================
Opened connection to database 'FinanceDigitalTool' on server 'CNGDCAAITSQL01'.
=============================== EF Core log finished ===============================
=============================== EF Core log started ===============================
Beginning transaction with isolation level 'ReadCommitted'.
=============================== EF Core log finished ===============================
=============================== EF Core log started ===============================
Executing DbCommand [Parameters=[@p1='?' (DbType = Int32), @p0='?' (Size = 50)], CommandType='Text', CommandTimeout='30']
SET NOCOUNT ON;
UPDATE [Book] SET [BookDescription] = @p0
WHERE [ID] = @p1;
SELECT @@ROWCOUNT;
=============================== EF Core log finished ===============================
=============================== EF Core log started ===============================
Executed DbCommand (19ms) [Parameters=[@p1='?' (DbType = Int32), @p0='?' (Size = 50)], CommandType='Text', CommandTimeout='30']
SET NOCOUNT ON;
UPDATE [Book] SET [BookDescription] = @p0
WHERE [ID] = @p1;
SELECT @@ROWCOUNT;
=============================== EF Core log finished ===============================
=============================== EF Core log started ===============================
A data reader was disposed.
=============================== EF Core log finished ===============================
=============================== EF Core log started ===============================
Committing transaction.
=============================== EF Core log finished ===============================
=============================== EF Core log started ===============================
Closing connection to database 'FinanceDigitalTool' on server 'CNGDCAAITSQL01'.
=============================== EF Core log finished ===============================
=============================== EF Core log started ===============================
Closed connection to database 'FinanceDigitalTool' on server 'CNGDCAAITSQL01'.
=============================== EF Core log finished ===============================
=============================== EF Core log started ===============================
Disposing transaction.
=============================== EF Core log finished ===============================
=============================== EF Core log started ===============================
An 'Book' entity tracked by 'FinanceDigitalToolContext' changed from 'Modified' to 'Unchanged'. Consider using 'DbContextOptionsBuilder.EnableSensitiveDataLogging' to see key values.
=============================== EF Core log finished ===============================
=============================== EF Core log started ===============================
SaveChanges completed for 'FinanceDigitalToolContext' with 1 entities written to the database.
=============================== EF Core log finished ===============================

同样从日志中我们可以看出来在执行dbContext.SaveChanges方法时，EF Core在数据库中开启了一个数据库连接，并使用SQL语句做了数据库更改，然后关闭了数据库连接。然后chineseBook这个Book实体的EntityState从Modified变为了Unchanged。

然后执行代码

1
chineseBook = dbContext.Book.First(bookExpression);//重新获取BookName为Chinese的Book实体chineseBook

时，EF Core的日志如下所示：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
=============================== EF Core log started ===============================
Opening connection to database 'FinanceDigitalTool' on server 'CNGDCAAITSQL01'.
=============================== EF Core log finished ===============================
=============================== EF Core log started ===============================
Opened connection to database 'FinanceDigitalTool' on server 'CNGDCAAITSQL01'.
=============================== EF Core log finished ===============================
=============================== EF Core log started ===============================
Executing DbCommand [Parameters=[], CommandType='Text', CommandTimeout='30']
SELECT TOP(1) [b].[ID], [b].[BookDescription], [b].[BookName], [b].[CreateTime], [b].[ISBN]
FROM [Book] AS [b]
WHERE [b].[BookName] = N'Chinese'
=============================== EF Core log finished ===============================
=============================== EF Core log started ===============================
Executed DbCommand (21ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
SELECT TOP(1) [b].[ID], [b].[BookDescription], [b].[BookName], [b].[CreateTime], [b].[ISBN]
FROM [Book] AS [b]
WHERE [b].[BookName] = N'Chinese'
=============================== EF Core log finished ===============================
=============================== EF Core log started ===============================
A data reader was disposed.
=============================== EF Core log finished ===============================
=============================== EF Core log started ===============================
Closing connection to database 'FinanceDigitalTool' on server 'CNGDCAAITSQL01'.
=============================== EF Core log finished ===============================
=============================== EF Core log started ===============================
Closed connection to database 'FinanceDigitalTool' on server 'CNGDCAAITSQL01'.
=============================== EF Core log finished ===============================

从日志中我们可以看出来，dbContext.Book.First(bookExpression)在数据库中还是开启了一个数据库连接，并使用SQL语句做了查询，然后关闭了数据库连接。

总结下来，上面的代码和日志发生的事情如下：

  • dbContext.Book.First(bookExpression)开启和关闭了一个数据库连接，查询BookName为Chinese的Book实体chineseBook
  • 第一个dbContext.SaveChanges()开启和关闭了一个数据库连接，更改chineseBook的BookDescription属性值”This is a Chinese book”到数据库
  • 第二个dbContext.SaveChanges()开启和关闭了一个数据库连接，更改chineseBook的BookDescription属性值”This is a very good Chinese book”到数据库
  • dbContext.Book.First(bookExpression)开启和关闭了一个数据库连接，重新查询BookName为Chinese的Book实体chineseBook 所以综上所述DbContext一共开启了和关闭了四个数据库连接。

使用事务

现在我们更改Program类Main方法中的代码，将两次DbContext.SaveChanges方法的调用都放在一个TransactionScope事务范围中，所以现在两次DbContext.SaveChanges方法提交的SQL都会在同一个数据库事务中，代码如下所示：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
using EFCoreDB.Entities;
using System;
using System.Linq;
using System.Linq.Expressions;
using System.Transactions;

namespace EFCoreDB
{
    class Program
    {
        static void Main(string[] args)
        {
            using (FinanceDigitalToolContext dbContext = new FinanceDigitalToolContext())
            {
                Expression<Func<Book, bool>> bookExpression = b => b.BookName == "Chinese";//构造查询条件，来查询BookName为Chinese的Book

                var chineseBook = dbContext.Book.First(bookExpression);//获取BookName为Chinese的Book实体chineseBook

                //使用TransactionScope事务范围来开启一个数据库事务
                using (TransactionScope transactionScope = new TransactionScope(TransactionScopeOption.RequiresNew))
                {
                    chineseBook.BookDescription = "This is a Chinese book";//更改chineseBook的BookDescription属性
                    dbContext.SaveChanges();//用DbContext.SaveChanges方法保存更改到数据库，由于TransactionScope事务范围的存在，所以DbContext.SaveChanges方法提交的SQL语句都存在于TransactionScope的事务当中

                    chineseBook.BookDescription = "This is a very good Chinese book";//再次更改chineseBook的BookDescription属性
                    dbContext.SaveChanges();//用DbContext.SaveChanges方法保存更改到数据库，由于TransactionScope事务范围的存在，所以DbContext.SaveChanges方法提交的SQL语句都存在于TransactionScope的事务当中

                    transactionScope.Complete();//提交TransactionScope事务范围中的SQL语句到数据库，数据库事务结束
                }

                chineseBook = dbContext.Book.First(bookExpression);//重新获取BookName为Chinese的Book实体chineseBook

                //显示当前chineseBook的当前BookDescription属性值
                Console.WriteLine(chineseBook.BookName + " book has description: \"" + chineseBook.BookDescription + "\"");
            }

            Console.WriteLine("Press key to quit....");

            Console.ReadLine();
        }
    }
}

执行上面的代码，我们还是使用EF Core的日志功能，输出每次DbContext访问数据库时的后台日志信息：

首先执行

1
var chineseBook = dbContext.Book.First(bookExpression);//获取BookName为Chinese的Book实体chineseBook

时，EF Core的日志如下所示：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
=============================== EF Core log started ===============================
Opening connection to database 'FinanceDigitalTool' on server 'CNGDCAAITSQL01'.
=============================== EF Core log finished ===============================
=============================== EF Core log started ===============================
Opened connection to database 'FinanceDigitalTool' on server 'CNGDCAAITSQL01'.
=============================== EF Core log finished ===============================
=============================== EF Core log started ===============================
Executing DbCommand [Parameters=[], CommandType='Text', CommandTimeout='30']
SELECT TOP(1) [b].[ID], [b].[BookDescription], [b].[BookName], [b].[CreateTime], [b].[ISBN]
FROM [Book] AS [b]
WHERE [b].[BookName] = N'Chinese'
=============================== EF Core log finished ===============================
=============================== EF Core log started ===============================
Executed DbCommand (139ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
SELECT TOP(1) [b].[ID], [b].[BookDescription], [b].[BookName], [b].[CreateTime], [b].[ISBN]
FROM [Book] AS [b]
WHERE [b].[BookName] = N'Chinese'
=============================== EF Core log finished ===============================
=============================== EF Core log started ===============================
A data reader was disposed.
=============================== EF Core log finished ===============================
=============================== EF Core log started ===============================
Closing connection to database 'FinanceDigitalTool' on server 'CNGDCAAITSQL01'.
=============================== EF Core log finished ===============================
=============================== EF Core log started ===============================
Closed connection to database 'FinanceDigitalTool' on server 'CNGDCAAITSQL01'.
=============================== EF Core log finished ===============================
=============================== EF Core log started ===============================
Context 'Book' started tracking 'FinanceDigitalToolContext' entity. Consider using 'DbContextOptionsBuilder.EnableSensitiveDataLogging' to see key values.
=============================== EF Core log finished ===============================

从日志中我们可以看出来，dbContext.Book.First(bookExpression)在数据库中开启了一个数据库连接，并使用SQL语句做了查询，然后关闭了数据库连接。

然后执行

1
2
using (TransactionScope transactionScope = new TransactionScope(TransactionScopeOption.RequiresNew))
{

TransactionScope的事务范围开始，此时数据库事务已经开始，EF Core没有输出日志

然后执行

1
2
chineseBook.BookDescription = "This is a Chinese book";//更改chineseBook的BookDescription属性
dbContext.SaveChanges();//用DbContext.SaveChanges方法保存更改到数据库，由于TransactionScope事务范围的存在，所以DbContext.SaveChanges方法提交的SQL语句都存在于TransactionScope的事务当中

时，EF Core的日志如下所示：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
=============================== EF Core log started ===============================
SaveChanges starting for 'FinanceDigitalToolContext'.
=============================== EF Core log finished ===============================
=============================== EF Core log started ===============================
DetectChanges starting for 'FinanceDigitalToolContext'.
=============================== EF Core log finished ===============================
=============================== EF Core log started ===============================
Unchanged 'Book.BookDescription' detected as changed and will be marked as modified. Consider using 'DbContextOptionsBuilder.EnableSensitiveDataLogging' to see property values.
=============================== EF Core log finished ===============================
=============================== EF Core log started ===============================
An 'Book' entity tracked by 'FinanceDigitalToolContext' changed from 'Unchanged' to 'Modified'. Consider using 'DbContextOptionsBuilder.EnableSensitiveDataLogging' to see key values.
=============================== EF Core log finished ===============================
=============================== EF Core log started ===============================
DetectChanges completed for 'FinanceDigitalToolContext'.
=============================== EF Core log finished ===============================
=============================== EF Core log started ===============================
Opening connection to database 'FinanceDigitalTool' on server 'CNGDCAAITSQL01'.
=============================== EF Core log finished ===============================
=============================== EF Core log started ===============================
Opened connection to database 'FinanceDigitalTool' on server 'CNGDCAAITSQL01'.
=============================== EF Core log finished ===============================
=============================== EF Core log started ===============================
Enlisted in an ambient transaction with isolation level 'Serializable'.
=============================== EF Core log finished ===============================
=============================== EF Core log started ===============================
Executing DbCommand [Parameters=[@p1='?' (DbType = Int32), @p0='?' (Size = 50)], CommandType='Text', CommandTimeout='30']
SET NOCOUNT ON;
UPDATE [Book] SET [BookDescription] = @p0
WHERE [ID] = @p1;
SELECT @@ROWCOUNT;
=============================== EF Core log finished ===============================
=============================== EF Core log started ===============================
Executed DbCommand (79ms) [Parameters=[@p1='?' (DbType = Int32), @p0='?' (Size = 50)], CommandType='Text', CommandTimeout='30']
SET NOCOUNT ON;
UPDATE [Book] SET [BookDescription] = @p0
WHERE [ID] = @p1;
SELECT @@ROWCOUNT;
=============================== EF Core log finished ===============================
=============================== EF Core log started ===============================
A data reader was disposed.
=============================== EF Core log finished ===============================
=============================== EF Core log started ===============================
An 'Book' entity tracked by 'FinanceDigitalToolContext' changed from 'Modified' to 'Unchanged'. Consider using 'DbContextOptionsBuilder.EnableSensitiveDataLogging' to see key values.
=============================== EF Core log finished ===============================
=============================== EF Core log started ===============================
SaveChanges completed for 'FinanceDigitalToolContext' with 1 entities written to the database.
=============================== EF Core log finished ===============================

从日志中我们可以看出来在执行dbContext.SaveChanges方法时，EF Core在数据库中开启了一个数据库连接，并使用SQL语句做了数据库更改，但是没有关闭数据库连接。然后chineseBook这个Book实体的EntityState从Modified变为了Unchanged。

然后执行

1
2
chineseBook.BookDescription = "This is a very good Chinese book";//再次更改chineseBook的BookDescription属性
dbContext.SaveChanges();//用DbContext.SaveChanges方法保存更改到数据库，由于TransactionScope事务范围的存在，所以DbContext.SaveChanges方法提交的SQL语句都存在于TransactionScope的事务当中

时，EF Core的日志如下所示：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
=============================== EF Core log started ===============================
SaveChanges starting for 'FinanceDigitalToolContext'.
=============================== EF Core log finished ===============================
=============================== EF Core log started ===============================
DetectChanges starting for 'FinanceDigitalToolContext'.
=============================== EF Core log finished ===============================
=============================== EF Core log started ===============================
Unchanged 'Book.BookDescription' detected as changed and will be marked as modified. Consider using 'DbContextOptionsBuilder.EnableSensitiveDataLogging' to see property values.
=============================== EF Core log finished ===============================
=============================== EF Core log started ===============================
An 'Book' entity tracked by 'FinanceDigitalToolContext' changed from 'Unchanged' to 'Modified'. Consider using 'DbContextOptionsBuilder.EnableSensitiveDataLogging' to see key values.
=============================== EF Core log finished ===============================
=============================== EF Core log started ===============================
DetectChanges completed for 'FinanceDigitalToolContext'.
=============================== EF Core log finished ===============================
=============================== EF Core log started ===============================
Executing DbCommand [Parameters=[@p1='?' (DbType = Int32), @p0='?' (Size = 50)], CommandType='Text', CommandTimeout='30']
SET NOCOUNT ON;
UPDATE [Book] SET [BookDescription] = @p0
WHERE [ID] = @p1;
SELECT @@ROWCOUNT;
=============================== EF Core log finished ===============================
=============================== EF Core log started ===============================
Executed DbCommand (22ms) [Parameters=[@p1='?' (DbType = Int32), @p0='?' (Size = 50)], CommandType='Text', CommandTimeout='30']
SET NOCOUNT ON;
UPDATE [Book] SET [BookDescription] = @p0
WHERE [ID] = @p1;
SELECT @@ROWCOUNT;
=============================== EF Core log finished ===============================
=============================== EF Core log started ===============================
A data reader was disposed.
=============================== EF Core log finished ===============================
=============================== EF Core log started ===============================
An 'Book' entity tracked by 'FinanceDigitalToolContext' changed from 'Modified' to 'Unchanged'. Consider using 'DbContextOptionsBuilder.EnableSensitiveDataLogging' to see key values.
=============================== EF Core log finished ===============================
=============================== EF Core log started ===============================
SaveChanges completed for 'FinanceDigitalToolContext' with 1 entities written to the database.
=============================== EF Core log finished ===============================

从日志中我们可以看出来在执行dbContext.SaveChanges方法时，EF Core在数据库中并没有开启新的数据库连接，而是沿用了上一个DbContext.SaveChanges方法开启的数据库连接来提交SQL语句到数据库。然后chineseBook这个Book实体的EntityState从Modified变为了Unchanged。

然后执行

1
2
transactionScope.Complete();//提交TransactionScope事务范围中的SQL语句到数据库，数据库事务结束
}

提交TransactionScope的事务到数据库，此时数据库事务结束，TransactionScope的事务范围也结束，EF Core没有输出日志

然后执行

1
chineseBook = dbContext.Book.First(bookExpression);//重新获取BookName为Chinese的Book实体chineseBook

时，EF Core的日志如下所示：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
=============================== EF Core log started ===============================
Executing DbCommand [Parameters=[], CommandType='Text', CommandTimeout='30']
SELECT TOP(1) [b].[ID], [b].[BookDescription], [b].[BookName], [b].[CreateTime], [b].[ISBN]
FROM [Book] AS [b]
WHERE [b].[BookName] = N'Chinese'
=============================== EF Core log finished ===============================
=============================== EF Core log started ===============================
Executed DbCommand (24ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
SELECT TOP(1) [b].[ID], [b].[BookDescription], [b].[BookName], [b].[CreateTime], [b].[ISBN]
FROM [Book] AS [b]
WHERE [b].[BookName] = N'Chinese'
=============================== EF Core log finished ===============================
=============================== EF Core log started ===============================
A data reader was disposed.
=============================== EF Core log finished ===============================
=============================== EF Core log started ===============================
Closing connection to database 'FinanceDigitalTool' on server 'CNGDCAAITSQL01'.
=============================== EF Core log finished ===============================
=============================== EF Core log started ===============================
Closed connection to database 'FinanceDigitalTool' on server 'CNGDCAAITSQL01'.
=============================== EF Core log finished ===============================

从日志中我们可以看出来，dbContext.Book.First(bookExpression)在数据库中也没有开启新的数据库连接，而是继续沿用了第一个DbContext.SaveChanges方法开启的数据库连接，使用SQL语句做了查询，之后关闭了第一个DbContext.SaveChanges方法开启的数据库连接，所以可以看到第一个DbContext.SaveChanges方法开启的数据库连接，现在才被关闭，这和不使用事务时是完全不一样的。

总结下来，使用事务后的代码和日志发生的事情如下：

  • dbContext.Book.First(bookExpression)开启和关闭了一个数据库连接，查询BookName为Chinese的Book实体chineseBook
  • using (TransactionScope transactionScope = new TransactionScope(TransactionScopeOption.RequiresNew)){开启了TransactionScope事务范围，数据库事务开始
  • 第一个dbContext.SaveChanges()开启一个数据库连接，但是没有关闭数据库连接，更改chineseBook的BookDescription属性值”This is a Chinese book”到数据库
  • 第二个dbContext.SaveChanges()没有开启新的数据库连接，而是沿用了上一个DbContext.SaveChanges方法开启的数据库连接，更改chineseBook的BookDescription属性值”This is a very good Chinese book”到数据库
  • transactionScope.Complete()提交TransactionScope事务范围的数据库事务到数据库，数据库事务结束
  • }TransactionScope事务范围结束
  • dbContext.Book.First(bookExpression)没有开启新的数据库连接，而是沿用了第一个DbContext.SaveChanges方法开启的数据库连接，重新查询BookName为Chinese的Book实体chineseBook，最后关闭了第一个DbContext.SaveChanges方法开启的数据库连接。 所以综上所述DbContext这次一共只开启和关闭了两个数据库连接，并且第二个数据库连接执行了三次数据库操作(两次DbContext.SaveChanges更改数据，一次Book.First查询数据)才被关闭。

由此我们可以看到EF Core在处于事务中时，会优化底层开启和关闭数据库连接的机制，因为EF Core觉得两次DbContext.SaveChanges方法提交的SQL语句既然都在同一个事务中，所以就没有必要每次都开启和关闭一个数据库连接，而是沿用了第一次DbContext.SaveChanges方法开启的数据库连接，所以这和没有使用事务的时候是完全不一样的。

在efcore平台时，你使用TransactionScope将会出现异常，微软会提示你去查看相关资料，这回资料挺准！https://docs.microsoft.com/en-us/ef/core/saving/transactions

本文章主要说了几点内容

  1. 默认的事务－savechanges依旧是一个事务
  2. 单个上下文实现事务
  3. 不同上下文之间实现事务 
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    using (var context = new BloggingContext())
    {
        using (var transaction = context.Database.BeginTransaction())
        {
            try
            {
                context.Blogs.Add(new Blog { Url = "http://blogs.msdn.com/dotnet" });
                context.SaveChanges();
                context.Blogs.Add(new Blog { Url = "http://blogs.msdn.com/visualstudio" });
                context.SaveChanges();
                var blogs = context.Blogs
                    .OrderBy(b => b.Url)
                    .ToList();
                // Commit transaction if all commands succeed, transaction will auto-rollback
                // when disposed if either commands fails
                transaction.Commit();
            }
            catch (Exception)
            {
                // TODO: Handle failure
            }
        }
    }