.net8系列-07图文并茂手把手教你连接SqlServer数据库使用log4net记录.net日志

前情提要

请先根据.net8系列-05文章配置好log4net日志依赖，再进行下面的步骤：详情请参考：.net8系列-05图文并茂手把手教你.NET Core 下使用 Log4Net 记录日志，配置日志组件log4net

步骤概览

使用 log4net 写入日志通常需要以下几个步骤：

添加 log4net 库引用
在项目的配置文件中添加 log4net 的配置
在 Program.cs 或 Global.asax.cs 文件中初始化 log4net
使用 logger 记录日志

下载依赖

搜索并下载 System.Data.SqlClient

下载安装成功

数据库准备

首先你要拥有，创建好一个sqlServer数据库~

脚本准备

USE [master]
GO
/****** Object:  Database [Xiaojin.AgiletyFramework]    Script Date: 2024/10/05 18:01:23 ******/
CREATE DATABASE [Xiaojin.AgiletyFramework]
 GO
IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))
begin
EXEC [Xiaojin.AgiletyFramework].[dbo].[sp_fulltext_database] @action = 'enable'
end
GO
ALTER DATABASE [Xiaojin.AgiletyFramework] SET ANSI_NULL_DEFAULT OFF 
GO
ALTER DATABASE [Xiaojin.AgiletyFramework] SET ANSI_NULLS OFF 
GO
ALTER DATABASE [Xiaojin.AgiletyFramework] SET ANSI_PADDING OFF 
GO
ALTER DATABASE [Xiaojin.AgiletyFramework] SET ANSI_WARNINGS OFF 
GO
ALTER DATABASE [Xiaojin.AgiletyFramework] SET ARITHABORT OFF 
GO
ALTER DATABASE [Xiaojin.AgiletyFramework] SET AUTO_CLOSE OFF 
GO
ALTER DATABASE [Xiaojin.AgiletyFramework] SET AUTO_SHRINK OFF 
GO
ALTER DATABASE [Xiaojin.AgiletyFramework] SET AUTO_UPDATE_STATISTICS ON 
GO
ALTER DATABASE [Xiaojin.AgiletyFramework] SET CURSOR_CLOSE_ON_COMMIT OFF 
GO
ALTER DATABASE [Xiaojin.AgiletyFramework] SET CURSOR_DEFAULT  GLOBAL 
GO
ALTER DATABASE [Xiaojin.AgiletyFramework] SET CONCAT_NULL_YIELDS_NULL OFF 
GO
ALTER DATABASE [Xiaojin.AgiletyFramework] SET NUMERIC_ROUNDABORT OFF 
GO
ALTER DATABASE [Xiaojin.AgiletyFramework] SET QUOTED_IDENTIFIER OFF 
GO
ALTER DATABASE [Xiaojin.AgiletyFramework] SET RECURSIVE_TRIGGERS OFF 
GO
ALTER DATABASE [Xiaojin.AgiletyFramework] SET  ENABLE_BROKER 
GO
ALTER DATABASE [Xiaojin.AgiletyFramework] SET AUTO_UPDATE_STATISTICS_ASYNC OFF 
GO
ALTER DATABASE [Xiaojin.AgiletyFramework] SET DATE_CORRELATION_OPTIMIZATION OFF 
GO
ALTER DATABASE [Xiaojin.AgiletyFramework] SET TRUSTWORTHY OFF 
GO
ALTER DATABASE [Xiaojin.AgiletyFramework] SET ALLOW_SNAPSHOT_ISOLATION OFF 
GO
ALTER DATABASE [Xiaojin.AgiletyFramework] SET PARAMETERIZATION SIMPLE 
GO
ALTER DATABASE [Xiaojin.AgiletyFramework] SET READ_COMMITTED_SNAPSHOT ON 
GO
ALTER DATABASE [Xiaojin.AgiletyFramework] SET HONOR_BROKER_PRIORITY OFF 
GO
ALTER DATABASE [Xiaojin.AgiletyFramework] SET RECOVERY FULL 
GO
ALTER DATABASE [Xiaojin.AgiletyFramework] SET  MULTI_USER 
GO
ALTER DATABASE [Xiaojin.AgiletyFramework] SET PAGE_VERIFY CHECKSUM  
GO
ALTER DATABASE [Xiaojin.AgiletyFramework] SET DB_CHAINING OFF 
GO
ALTER DATABASE [Xiaojin.AgiletyFramework] SET FILESTREAM( NON_TRANSACTED_ACCESS = OFF ) 
GO
ALTER DATABASE [Xiaojin.AgiletyFramework] SET TARGET_RECOVERY_TIME = 60 SECONDS 
GO
EXEC sys.sp_db_vardecimal_storage_format N'Xiaojin.AgiletyFramework', N'ON'
GO
USE [Xiaojin.AgiletyFramework]
GO
/****** Object:  Table [dbo].[MenuEntity]    Script Date: 2024/10/05 18:01:23 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[MenuEntity](
	[Id] [uniqueidentifier] NOT NULL,
	[ParentId] [uniqueidentifier] NOT NULL,
	[MenuText] [nvarchar](max) NULL,
	[MenuType] [int] NOT NULL,
	[Icon] [nvarchar](max) NULL,
	[WebUrlName] [nvarchar](max) NULL,
	[WebUrl] [nvarchar](max) NULL,
	[VueFilePath] [nvarchar](max) NULL,
	[IsLeafNode] [bit] NOT NULL,
	[OrderBy] [int] NOT NULL,
	[CreateTime] [datetime2](7) NOT NULL,
	[ModifyTime] [datetime2](7) NOT NULL,
	[Status] [int] NOT NULL,
 CONSTRAINT [PK_MenuEntity] 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] TEXTIMAGE_ON [PRIMARY]
GO
/****** Object:  Table [dbo].[RoleEntity]    Script Date: 2024/10/05 18:01:23 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[RoleEntity](
	[RoleId] [int] IDENTITY(1,1) NOT NULL,
	[RoleName] [nvarchar](max) NULL,
	[Status] [int] NOT NULL,
	[CreateTime] [datetime2](7) NOT NULL,
	[ModifyTime] [datetime2](7) NOT NULL,
 CONSTRAINT [PK_RoleEntity] PRIMARY KEY CLUSTERED 
(
	[RoleId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
/****** Object:  Table [dbo].[RoleMenuMapEntity]    Script Date: 2024/10/05 18:01:23 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[RoleMenuMapEntity](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[RoleId] [int] NOT NULL,
	[MenuId] [uniqueidentifier] NOT NULL,
	[CreateTime] [datetime2](7) NOT NULL,
	[ModifyTime] [datetime2](7) NOT NULL,
	[Status] [int] NOT NULL,
 CONSTRAINT [PK_RoleMenuMapEntity] 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
/****** Object:  Table [dbo].[SystemLog]    Script Date: 2024/10/05 18:01:23 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[SystemLog](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[Date] [datetime2](7) NOT NULL,
	[Thread] [nvarchar](max) NULL,
	[Level] [nvarchar](max) NULL,
	[Logger] [nvarchar](max) NULL,
	[Message] [nvarchar](max) NULL,
	[Exception] [nvarchar](max) NULL,
 CONSTRAINT [PK_SystemLog] 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] TEXTIMAGE_ON [PRIMARY]
GO
/****** Object:  Table [dbo].[UserEntity]    Script Date: 2024/10/05 18:01:23 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[UserEntity](
	[UserId] [int] IDENTITY(1,1) NOT NULL,
	[Name] [nvarchar](max) NULL,
	[Password] [nvarchar](max) NULL,
	[UserType] [int] NOT NULL,
	[Phone] [nvarchar](max) NULL,
	[Mobile] [nvarchar](max) NULL,
	[Address] [nvarchar](max) NULL,
	[Email] [nvarchar](max) NULL,
	[QQ] [nvarchar](max) NULL,
	[WeChat] [nvarchar](max) NULL,
	[Sex] [int] NOT NULL,
	[Imageurl] [nvarchar](max) NULL,
	[LastLoginTime] [datetime2](7) NOT NULL,
	[CreateTime] [datetime2](7) NOT NULL,
	[ModifyTime] [datetime2](7) NOT NULL,
	[Status] [int] NOT NULL,
 CONSTRAINT [PK_UserEntity] PRIMARY KEY CLUSTERED 
(
	[UserId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
/****** Object:  Table [dbo].[UserRoleMapEntity]    Script Date: 2024/10/05 18:01:23 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[UserRoleMapEntity](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[UserId] [int] NOT NULL,
	[RoleId] [int] NOT NULL,
	[CreateTime] [datetime2](7) NOT NULL,
	[ModifyTime] [datetime2](7) NOT NULL,
	[Status] [int] NOT NULL,
 CONSTRAINT [PK_UserRoleMapEntity] 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
SET IDENTITY_INSERT [dbo].[UserEntity] ON 
GO
INSERT [dbo].[UserEntity] ([UserId], [Name], [Password], [UserType], [Phone], [Mobile], [Address], [Email], [QQ], [WeChat], [Sex], [Imageurl], [LastLoginTime], [CreateTime], [ModifyTime], [Status]) VALUES (2, N'Richard老师', N'1234', 1, N'18672713698', N'18672713698', N'湖北武汉', N'18672713698@163.com', N'1030499676', N'MrRichard2020', 1, N'iamge', CAST(N'2023-12-20T16:42:21.8556726' AS DateTime2), CAST(N'2023-12-20T16:42:21.8466399' AS DateTime2), CAST(N'2023-12-20T16:42:21.8627641' AS DateTime2), 1)
GO
INSERT [dbo].[UserEntity] ([UserId], [Name], [Password], [UserType], [Phone], [Mobile], [Address], [Email], [QQ], [WeChat], [Sex], [Imageurl], [LastLoginTime], [CreateTime], [ModifyTime], [Status]) VALUES (3, N'Richard老师', N'1234', 1, N'18672713698', N'18672713698', N'湖北武汉', N'18672713698@163.com', N'1030499676', N'MrRichard2020', 1, N'iamge', CAST(N'2023-12-20T16:42:38.5023329' AS DateTime2), CAST(N'2023-12-20T16:42:38.4906967' AS DateTime2), CAST(N'2023-12-20T16:42:38.5111168' AS DateTime2), 1)
GO
SET IDENTITY_INSERT [dbo].[UserEntity] OFF
GO
USE [master]
GO
ALTER DATABASE [Xiaojin.AgiletyFramework] SET  READ_WRITE 
GO

执行脚本，创建所需数据库

这里以DBever数据库软件为案例，连接好数据库以后，开始执行脚本
因为使用这个软件不需要加GO,所以上面代码我全部去掉了GO

创建成功，查看日志表

准备代码

请先根据.net8系列-06，配置好log4net日志依赖，再进行下面的步骤：详情请参考：.net8系列-05图文并茂手把手教你.NET Core 下使用 Log4Net 记录日志，配置日志组件log4net

初始代码

代码位置以及名称：\xiaojinWebApplication\xiaojinWebApplication\configFile\log4net.Config

<?xml version="1.0" encoding="utf-8"?>
<log4net>
	<!-- Define some output appenders -->
	<appender name="rollingAppender" type="log4net.Appender.RollingFileAppender">
		<file value="log4\log.txt" />
		<!--追加日志内容-->
		<appendToFile value="true" />

		<!--防止多线程时不能写Log,官方说线程非安全-->
		<lockingModel type="log4net.Appender.FileAppender+MinimalLock" />

		<!--可以为:Once|Size|Date|Composite-->
		<!--Composite为Size和Date的组合-->
		<rollingStyle value="Composite" />

		<!--当备份文件时,为文件名加的后缀-->
		<datePattern value="yyyyMMdd.TXT" />

		<!--日志最大个数,都是最新的-->
		<!--rollingStyle节点为Size时,只能有value个日志-->
		<!--rollingStyle节点为Composite时,每天有value个日志-->
		<maxSizeRollBackups value="20" />

		<!--可用的单位:KB|MB|GB-->
		<maximumFileSize value="3MB" />

		<!--置为true,当前最新日志文件名永远为file节中的名字-->
		<staticLogFileName value="true" />

		<!--输出级别在INFO和ERROR之间的日志-->
		<filter type="log4net.Filter.LevelRangeFilter">
			<param name="LevelMin" value="ALL" />
			<param name="LevelMax" value="FATAL" />
		</filter>
		<layout type="log4net.Layout.PatternLayout">
			<conversionPattern value="%date [%thread] %-5level %logger - %message%newline"/>
		</layout>
	</appender>

	<!--SqlServer形式-->
	<!--log4net日志配置：http://logging.apache.org/log4net/release/config-examples.html -->
	<appender name="AdoNetAppender_SqlServer" type="log4net.Appender.AdoNetAppender">
		<!--日志缓存写入条数 设置为0时只要有一条就立刻写到数据库-->
		<bufferSize value="0" />
		<connectionType value="System.Data.SqlClient.SqlConnection,System.Data.SqlClient, Version=4.6.1.3, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a" />
		<connectionString value="Data Source=PC-202206030027;Initial Catalog=LogManager;Persist Security Info=True;User ID=sa;Password=sa123" />
		<commandText value="INSERT INTO Log4Net ([Date],[Thread],[Level],[Logger],[Message],[Exception]) VALUES (@log_date, @thread, @log_level, @logger, @message, @exception)" />
		<parameter>
			<parameterName value="@log_date" />
			<dbType value="DateTime" />
			<layout type="log4net.Layout.RawTimeStampLayout" />
		</parameter>
		<parameter>
			<parameterName value="@thread" />
			<dbType value="String" />
			<size value="255" />
			<layout type="log4net.Layout.PatternLayout">
				<conversionPattern value="%thread" />
			</layout>
		</parameter>
		<parameter>
			<parameterName value="@log_level" />
			<dbType value="String" />
			<size value="50" />
			<layout type="log4net.Layout.PatternLayout">
				<conversionPattern value="%level" />
			</layout>
		</parameter>
		<parameter>
			<parameterName value="@logger" />
			<dbType value="String" />
			<size value="255" />
			<layout type="log4net.Layout.PatternLayout">
				<conversionPattern value="%logger" />
			</layout>
		</parameter>
		<parameter>
			<parameterName value="@message" />
			<dbType value="String" />
			<size value="4000" />
			<layout type="log4net.Layout.PatternLayout">
				<conversionPattern value="%message" />
			</layout>
		</parameter>
		<parameter>
			<parameterName value="@exception" />
			<dbType value="String" />
			<size value="2000" />
			<layout type="log4net.Layout.ExceptionLayout" />
		</parameter>
	</appender>
	
	
	<root>

		<!--控制级别，由低到高: ALL|DEBUG|INFO|WARN|ERROR|FATAL|OFF-->
		<!--OFF:0-->
		<!--FATAL:FATAL-->
		<!--ERROR: ERROR,FATAL-->
		<!--WARN: WARN,ERROR,FATAL-->
		<!--INFO: INFO,WARN,ERROR,FATAL-->
		<!--DEBUG: INFO,WARN,ERROR,FATAL-->
		<!--ALL: DEBUG,INFO,WARN,ERROR,FATAL--> 
		<priority value="ALL"/>
		
		<level value="INFO"/>
		<appender-ref ref="rollingAppender" />
		<!-- <appender-ref ref="AdoNetAppender_SqlServer" /> -->
	</root>
</log4net>

配置数据库

配置log4net.config中sqlserver，如果服务器IP是10.251.111.111，端口号是1433，密码是tangdoudou123,数据库名字是Xiaojin.AgiletyFramework，那么你可以修改代码如下：
要在log4net.config中配置使用新的数据库表名SystemLog，您需要更新AdoNetAppender的部分配置。具体来说，需要更改commandText中的表名。

<connectionString value="Data Source=10.251.111.111,1433;Initial Catalog=Xiaojin.AgiletyFramework;Persist Security Info=True;User ID=sa;Password=tangdoudou123" />
<commandText value="INSERT INTO SystemLog ([Date],[Thread],[Level],[Logger],[Message],[Exception]) VALUES (@log_date, @thread, @log_level, @logger, @message, @exception)" />

开启数据库写入日志逻辑

开启日志

builder.Logging.AddLog4Net("configFile/log4net.Config");

运行测试

删除之前的编译文件

重新编译

运行测试

本地日志测试成功

数据库日志测试成功

结语

今天就写到这里啦~
小伙伴们，(￣ω￣(￣ω￣〃 (￣ω￣〃)ゝ我们明天再见啦~~
大家要天天开心哦

欢迎大家指出文章需要改正之处~
学无止境，合作共赢

在这里插入图片描述

欢迎路过的小哥哥小姐姐们提出更好的意见哇~~

posted @ 2024-10-07 00:51 糖~豆豆阅读(79) 评论(0) 编辑收藏举报

刷新页面返回顶部

糖~豆豆

.net8系列-07图文并茂手把手教你连接SqlServer数据库使用log4net记录.net日志

前情提要

步骤概览

下载依赖

下载安装成功

数据库准备

脚本准备

执行脚本，创建所需数据库

创建成功，查看日志表

准备代码

初始代码

配置数据库

开启数据库写入日志逻辑

开启日志

运行测试

删除之前的编译文件

重新编译

运行测试

本地日志测试成功

数据库日志测试成功

结语

欢迎路过的小哥哥小姐姐们提出更好的意见哇~~

公告