搭建.NET CORE MVC + EF CORE 的Demo

YANG-Live

已于 2022-03-21 16:15:59 修改

阅读量703

点赞数

分类专栏： .NetCore Linq EF core 文章标签： .net core

于 2022-03-21 14:51:02 首次发布

本文链接：https://blog.csdn.net/qq_38567182/article/details/123626563

版权

.NetCore 同时被 3 个专栏收录

16 篇文章 2 订阅

订阅专栏

EF core

6 篇文章 0 订阅

订阅专栏

Linq

2 篇文章 0 订阅

订阅专栏

本文档详细介绍了APAT公司在SZ站点的部门结构表SY_S_DEPARTMENT及其子表SY_S_GLOBAL_MULTIPLE_LANGUAGE_VALUE的创建过程，包括表结构定义、数据插入和多语言字典的维护。同时展示了如何通过EFCore映射实体和使用SQLHelper进行数据库操作的方法。

摘要由CSDN通过智能技术生成

一、建库建表

USE [Test_Demo]
GO
/****** Object:  Table [dbo].[SY_S_DEPARTMENT]    Script Date: 2022/3/21 8:41:39 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
--树形菜单表SY_S_DEPARTMENT
CREATE TABLE [dbo].[SY_S_DEPARTMENT](
	[FACILITY] [varchar](10) NOT NULL,
	[SITE] [varchar](15) NOT NULL,
	[DEPT_CODE] [varchar](50) NOT NULL,   --当前coed
	[LINE_ID] [int] NULL,                 --行索引
	[UPPER_DEPT] [varchar](50) NULL,      --父级coed
	[CREATOR] [varchar](25) NOT NULL,	  --创建人
	[CREATE_DATE] [datetime] NOT NULL CONSTRAINT [DF_SY_S_DEPARTMENT_CREATE_DATE]  DEFAULT (getdate()), --创建时间
	[MODIFIER] [varchar](25) NULL,		  --修改人
	[MODIFY_DATE] [datetime] NULL,		  --修改时间
 CONSTRAINT [PK_SY_S_DEPARTMENT] PRIMARY KEY CLUSTERED 
(
	[DEPT_CODE] ASC,
	[FACILITY] ASC,
	[SITE] 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 ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[SY_S_GLOBAL_MULTIPLE_LANGUAGE_VALUE]    Script Date: 2022/3/21 8:41:39 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
--字典表
CREATE TABLE [dbo].[SY_S_GLOBAL_MULTIPLE_LANGUAGE_VALUE](
	[LOOKUP_TYPE] [varchar](80) NOT NULL,
	[LOOKUP_VALUE] [varchar](50) NOT NULL,
	[MEANING] [nvarchar](100) NOT NULL,
	[LANGUAGE] [varchar](10) NOT NULL,
	[CREATOR] [varchar](25) NOT NULL,
	[CREATE_DATE] [datetime] NOT NULL,
	[MODIFIER] [varchar](25) NULL,
	[MODIFY_DATE] [datetime] NULL,
 CONSTRAINT [PK_SY_S_GLOBAL_TABLE_LANGUAGE_VALUE] PRIMARY KEY CLUSTERED 
(
	[LOOKUP_TYPE] ASC,
	[LOOKUP_VALUE] ASC,
	[LANGUAGE] 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 ANSI_PADDING OFF
GO
INSERT [dbo].[SY_S_DEPARTMENT] ([FACILITY], [SITE], [DEPT_CODE], [LINE_ID], [UPPER_DEPT], [CREATOR], [CREATE_DATE], [MODIFIER], [MODIFY_DATE]) VALUES (N'APAT', N'SZ', N'-JEB77', 1, N'', N'admin', CAST(N'2021-08-23 17:10:46.603' AS DateTime), NULL, NULL)
INSERT [dbo].[SY_S_DEPARTMENT] ([FACILITY], [SITE], [DEPT_CODE], [LINE_ID], [UPPER_DEPT], [CREATOR], [CREATE_DATE], [MODIFIER], [MODIFY_DATE]) VALUES (N'APAT', N'SZ', N'-KJB89', 2, N'-JEB77', N'admin', CAST(N'2021-08-23 17:10:58.503' AS DateTime), N'admin', CAST(N'2021-08-25 09:26:14.790' AS DateTime))
INSERT [dbo].[SY_S_DEPARTMENT] ([FACILITY], [SITE], [DEPT_CODE], [LINE_ID], [UPPER_DEPT], [CREATOR], [CREATE_DATE], [MODIFIER], [MODIFY_DATE]) VALUES (N'APAT', N'SZ', N'OVU11', 2, N'-JEB77', N'admin', CAST(N'2021-08-23 17:13:10.267' AS DateTime), N'admin', CAST(N'2021-08-25 09:25:58.157' AS DateTime))
INSERT [dbo].[SY_S_DEPARTMENT] ([FACILITY], [SITE], [DEPT_CODE], [LINE_ID], [UPPER_DEPT], [CREATOR], [CREATE_DATE], [MODIFIER], [MODIFY_DATE]) VALUES (N'APAT', N'SZ', N'PVQ80', 4, N'-JEB77', N'admin', CAST(N'2021-08-23 17:18:39.377' AS DateTime), NULL, NULL)
INSERT [dbo].[SY_S_DEPARTMENT] ([FACILITY], [SITE], [DEPT_CODE], [LINE_ID], [UPPER_DEPT], [CREATOR], [CREATE_DATE], [MODIFIER], [MODIFY_DATE]) VALUES (N'APAT', N'SZ', N'PVQ80-MMZ22', 6, N'PVQ80', N'admin', CAST(N'2021-08-23 17:18:51.320' AS DateTime), N'admin', CAST(N'2021-08-24 15:11:06.910' AS DateTime))
INSERT [dbo].[SY_S_DEPARTMENT] ([FACILITY], [SITE], [DEPT_CODE], [LINE_ID], [UPPER_DEPT], [CREATOR], [CREATE_DATE], [MODIFIER], [MODIFY_DATE]) VALUES (N'APAT', N'SZ', N'PVQ80-XFL25', 6, N'PVQ80', N'admin', CAST(N'2021-08-23 17:18:58.800' AS DateTime), N'admin', CAST(N'2021-08-24 15:11:04.877' AS DateTime))
INSERT [dbo].[SY_S_DEPARTMENT] ([FACILITY], [SITE], [DEPT_CODE], [LINE_ID], [UPPER_DEPT], [CREATOR], [CREATE_DATE], [MODIFIER], [MODIFY_DATE]) VALUES (N'APAT', N'SZ', N'TGC68', 7, N'-JEB77', N'admin', CAST(N'2021-08-23 17:13:58.413' AS DateTime), NULL, NULL)
INSERT [dbo].[SY_S_DEPARTMENT] ([FACILITY], [SITE], [DEPT_CODE], [LINE_ID], [UPPER_DEPT], [CREATOR], [CREATE_DATE], [MODIFIER], [MODIFY_DATE]) VALUES (N'APAT', N'SZ', N'XLE66', 8, N'-JEB77', N'admin', CAST(N'2021-08-23 17:17:17.787' AS DateTime), N'admin', CAST(N'2021-08-23 17:17:50.333' AS DateTime))
INSERT [dbo].[SY_S_DEPARTMENT] ([FACILITY], [SITE], [DEPT_CODE], [LINE_ID], [UPPER_DEPT], [CREATOR], [CREATE_DATE], [MODIFIER], [MODIFY_DATE]) VALUES (N'APAT', N'SZ', N'XLE66-EIB40', 9, N'XLE66', N'admin', CAST(N'2021-08-23 17:18:23.443' AS DateTime), N'admin', CAST(N'2021-08-23 17:22:42.563' AS DateTime))
INSERT [dbo].[SY_S_DEPARTMENT] ([FACILITY], [SITE], [DEPT_CODE], [LINE_ID], [UPPER_DEPT], [CREATOR], [CREATE_DATE], [MODIFIER], [MODIFY_DATE]) VALUES (N'APAT', N'SZ', N'XLE66-GOJ97', 10, N'XLE66', N'admin', CAST(N'2021-08-23 17:18:31.330' AS DateTime), NULL, NULL)
INSERT [dbo].[SY_S_DEPARTMENT] ([FACILITY], [SITE], [DEPT_CODE], [LINE_ID], [UPPER_DEPT], [CREATOR], [CREATE_DATE], [MODIFIER], [MODIFY_DATE]) VALUES (N'APAT', N'SZ', N'XLE66-IIO60', 11, N'XLE66', N'admin', CAST(N'2021-08-23 17:17:44.663' AS DateTime), NULL, NULL)
INSERT [dbo].[SY_S_DEPARTMENT] ([FACILITY], [SITE], [DEPT_CODE], [LINE_ID], [UPPER_DEPT], [CREATOR], [CREATE_DATE], [MODIFIER], [MODIFY_DATE]) VALUES (N'APAT', N'SZ', N'XLE66-NBN57', 2, N'-JEB77', N'admin', CAST(N'2021-08-23 17:22:47.197' AS DateTime), N'admin', CAST(N'2021-08-25 09:26:17.657' AS DateTime))
INSERT [dbo].[SY_S_DEPARTMENT] ([FACILITY], [SITE], [DEPT_CODE], [LINE_ID], [UPPER_DEPT], [CREATOR], [CREATE_DATE], [MODIFIER], [MODIFY_DATE]) VALUES (N'APAT', N'SZ', N'XLE66-QUO52', 13, N'XLE66', N'admin', CAST(N'2021-08-23 17:17:37.150' AS DateTime), NULL, NULL)
INSERT [dbo].[SY_S_DEPARTMENT] ([FACILITY], [SITE], [DEPT_CODE], [LINE_ID], [UPPER_DEPT], [CREATOR], [CREATE_DATE], [MODIFIER], [MODIFY_DATE]) VALUES (N'APAT', N'SZ', N'XLE66-USO29', 14, N'XLE66', N'admin', CAST(N'2021-08-23 17:17:28.790' AS DateTime), N'admin', CAST(N'2021-08-23 17:23:07.150' AS DateTime))
INSERT [dbo].[SY_S_DEPARTMENT] ([FACILITY], [SITE], [DEPT_CODE], [LINE_ID], [UPPER_DEPT], [CREATOR], [CREATE_DATE], [MODIFIER], [MODIFY_DATE]) VALUES (N'APAT', N'SZ', N'XUD08', 18, N'-JEB77', N'admin', CAST(N'2021-08-23 17:15:30.067' AS DateTime), NULL, NULL)
INSERT [dbo].[SY_S_DEPARTMENT] ([FACILITY], [SITE], [DEPT_CODE], [LINE_ID], [UPPER_DEPT], [CREATOR], [CREATE_DATE], [MODIFIER], [MODIFY_DATE]) VALUES (N'APAT', N'SZ', N'XUD08-ERW93', 10, N'XUD08', N'admin', CAST(N'2021-08-23 17:16:48.160' AS DateTime), N'QINGYOU.LI', CAST(N'2021-10-25 15:12:42.890' AS DateTime))
INSERT [dbo].[SY_S_DEPARTMENT] ([FACILITY], [SITE], [DEPT_CODE], [LINE_ID], [UPPER_DEPT], [CREATOR], [CREATE_DATE], [MODIFIER], [MODIFY_DATE]) VALUES (N'APAT', N'SZ', N'XUD08-EZV98', 23, N'XUD08', N'admin', CAST(N'2021-08-23 17:17:08.100' AS DateTime), NULL, NULL)
INSERT [dbo].[SY_S_DEPARTMENT] ([FACILITY], [SITE], [DEPT_CODE], [LINE_ID], [UPPER_DEPT], [CREATOR], [CREATE_DATE], [MODIFIER], [MODIFY_DATE]) VALUES (N'APAT', N'SZ', N'XUD08-FID15', 24, N'XUD08', N'admin', CAST(N'2021-08-23 17:16:23.643' AS DateTime), NULL, NULL)
INSERT [dbo].[SY_S_DEPARTMENT] ([FACILITY], [SITE], [DEPT_CODE], [LINE_ID], [UPPER_DEPT], [CREATOR], [CREATE_DATE], [MODIFIER], [MODIFY_DATE]) VALUES (N'APAT', N'SZ', N'XUD08-GVZ39', 25, N'XUD08', N'admin', CAST(N'2021-08-23 17:16:38.343' AS DateTime), NULL, NULL)
INSERT [dbo].[SY_S_DEPARTMENT] ([FACILITY], [SITE], [DEPT_CODE], [LINE_ID], [UPPER_DEPT], [CREATOR], [CREATE_DATE], [MODIFIER], [MODIFY_DATE]) VALUES (N'APAT', N'SZ', N'XUD08-IRM47', 26, N'XUD08', N'admin', CAST(N'2021-08-23 17:16:05.323' AS DateTime), NULL, NULL)
INSERT [dbo].[SY_S_DEPARTMENT] ([FACILITY], [SITE], [DEPT_CODE], [LINE_ID], [UPPER_DEPT], [CREATOR], [CREATE_DATE], [MODIFIER], [MODIFY_DATE]) VALUES (N'APAT', N'SZ', N'XUD08-JUD89', 10, N'XUD08', N'admin', CAST(N'2021-08-23 17:15:47.403' AS DateTime), N'admin', CAST(N'2021-09-15 10:27:02.697' AS DateTime))
INSERT [dbo].[SY_S_DEPARTMENT] ([FACILITY], [SITE], [DEPT_CODE], [LINE_ID], [UPPER_DEPT], [CREATOR], [CREATE_DATE], [MODIFIER], [MODIFY_DATE]) VALUES (N'APAT', N'SZ', N'XUD08-MJH63', 28, N'XUD08', N'admin', CAST(N'2021-08-23 17:16:14.873' AS DateTime), NULL, NULL)
INSERT [dbo].[SY_S_DEPARTMENT] ([FACILITY], [SITE], [DEPT_CODE], [LINE_ID], [UPPER_DEPT], [CREATOR], [CREATE_DATE], [MODIFIER], [MODIFY_DATE]) VALUES (N'APAT', N'SZ', N'YRT64', 29, N'-JEB77', N'admin', CAST(N'2021-08-23 17:19:08.837' AS DateTime), NULL, NULL)
INSERT [dbo].[SY_S_DEPARTMENT] ([FACILITY], [SITE], [DEPT_CODE], [LINE_ID], [UPPER_DEPT], [CREATOR], [CREATE_DATE], [MODIFIER], [MODIFY_DATE]) VALUES (N'APAT', N'SZ', N'YRT64-SOF31', 30, N'YRT64', N'admin', CAST(N'2021-08-23 17:19:24.567' AS DateTime), NULL, NULL)
INSERT [dbo].[SY_S_DEPARTMENT] ([FACILITY], [SITE], [DEPT_CODE], [LINE_ID], [UPPER_DEPT], [CREATOR], [CREATE_DATE], [MODIFIER], [MODIFY_DATE]) VALUES (N'APAT', N'SZ', N'YRT64-YZR91', 27, N'YRT64', N'admin', CAST(N'2021-08-23 17:19:34.083' AS DateTime), N'admin', CAST(N'2021-09-15 08:03:51.747' AS DateTime))
INSERT [dbo].[SY_S_DEPARTMENT] ([FACILITY], [SITE], [DEPT_CODE], [LINE_ID], [UPPER_DEPT], [CREATOR], [CREATE_DATE], [MODIFIER], [MODIFY_DATE]) VALUES (N'APAT', N'SZ', N'ZFL31', 32, N'-JEB77', N'admin', CAST(N'2021-08-23 17:12:18.823' AS DateTime), N'admin', CAST(N'2021-08-23 17:29:29.750' AS DateTime))
INSERT [dbo].[SY_S_DEPARTMENT] ([FACILITY], [SITE], [DEPT_CODE], [LINE_ID], [UPPER_DEPT], [CREATOR], [CREATE_DATE], [MODIFIER], [MODIFY_DATE]) VALUES (N'APAT', N'SZ', N'ZFL31-OKR21', 4, N'ZFL31', N'admin', CAST(N'2021-08-23 17:15:18.357' AS DateTime), N'admin', CAST(N'2022-01-20 15:50:10.040' AS DateTime))
INSERT [dbo].[SY_S_DEPARTMENT] ([FACILITY], [SITE], [DEPT_CODE], [LINE_ID], [UPPER_DEPT], [CREATOR], [CREATE_DATE], [MODIFIER], [MODIFY_DATE]) VALUES (N'APAT', N'SZ', N'ZFL31-ORN15', 4, N'ZFL31', N'admin', CAST(N'2021-08-23 17:14:47.257' AS DateTime), N'admin', CAST(N'2022-01-20 15:50:07.040' AS DateTime))
INSERT [dbo].[SY_S_DEPARTMENT] ([FACILITY], [SITE], [DEPT_CODE], [LINE_ID], [UPPER_DEPT], [CREATOR], [CREATE_DATE], [MODIFIER], [MODIFY_DATE]) VALUES (N'APAT', N'SZ', N'ZFL31-XFF51', 4, N'ZFL31', N'admin', CAST(N'2021-08-23 17:15:04.457' AS DateTime), N'admin', CAST(N'2022-01-20 15:50:01.553' AS DateTime))
INSERT [dbo].[SY_S_GLOBAL_MULTIPLE_LANGUAGE_VALUE] ([LOOKUP_TYPE], [LOOKUP_VALUE], [MEANING], [LANGUAGE], [CREATOR], [CREATE_DATE], [MODIFIER], [MODIFY_DATE]) VALUES (N'SY_S_DEPARTMENT.DEPT_CODE', N'-JEB77', N'深圳市亚派光电器件有限公司', N'zh-cn', N'admin', CAST(N'2021-08-23 17:10:46.603' AS DateTime), NULL, NULL)
INSERT [dbo].[SY_S_GLOBAL_MULTIPLE_LANGUAGE_VALUE] ([LOOKUP_TYPE], [LOOKUP_VALUE], [MEANING], [LANGUAGE], [CREATOR], [CREATE_DATE], [MODIFIER], [MODIFY_DATE]) VALUES (N'SY_S_DEPARTMENT.DEPT_CODE', N'-KJB89', N'财务中心', N'zh-cn', N'admin', CAST(N'2021-08-23 17:10:58.503' AS DateTime), N'admin', CAST(N'2021-08-25 09:26:14.790' AS DateTime))
INSERT [dbo].[SY_S_GLOBAL_MULTIPLE_LANGUAGE_VALUE] ([LOOKUP_TYPE], [LOOKUP_VALUE], [MEANING], [LANGUAGE], [CREATOR], [CREATE_DATE], [MODIFIER], [MODIFY_DATE]) VALUES (N'SY_S_DEPARTMENT.DEPT_CODE', N'KWY41', N'总经办', N'zh-cn', N'xiaomin_xie', CAST(N'2021-08-23 09:12:35.543' AS DateTime), N'admin', CAST(N'2021-08-23 16:46:17.900' AS DateTime))
INSERT [dbo].[SY_S_GLOBAL_MULTIPLE_LANGUAGE_VALUE] ([LOOKUP_TYPE], [LOOKUP_VALUE], [MEANING], [LANGUAGE], [CREATOR], [CREATE_DATE], [MODIFIER], [MODIFY_DATE]) VALUES (N'SY_S_DEPARTMENT.DEPT_CODE', N'OVU11', N'组件业务中心', N'zh-cn', N'admin', CAST(N'2021-08-23 17:13:10.267' AS DateTime), N'admin', CAST(N'2021-08-25 09:25:58.157' AS DateTime))
INSERT [dbo].[SY_S_GLOBAL_MULTIPLE_LANGUAGE_VALUE] ([LOOKUP_TYPE], [LOOKUP_VALUE], [MEANING], [LANGUAGE], [CREATOR], [CREATE_DATE], [MODIFIER], [MODIFY_DATE]) VALUES (N'SY_S_DEPARTMENT.DEPT_CODE', N'PVQ80', N'采购中心', N'zh-cn', N'admin', CAST(N'2021-08-23 17:18:39.377' AS DateTime), NULL, NULL)
INSERT [dbo].[SY_S_GLOBAL_MULTIPLE_LANGUAGE_VALUE] ([LOOKUP_TYPE], [LOOKUP_VALUE], [MEANING], [LANGUAGE], [CREATOR], [CREATE_DATE], [MODIFIER], [MODIFY_DATE]) VALUES (N'SY_S_DEPARTMENT.DEPT_CODE', N'PVQ80-MMZ22', N'采购执行组', N'zh-cn', N'admin', CAST(N'2021-08-23 17:18:51.320' AS DateTime), N'admin', CAST(N'2021-08-24 15:11:06.910' AS DateTime))
INSERT [dbo].[SY_S_GLOBAL_MULTIPLE_LANGUAGE_VALUE] ([LOOKUP_TYPE], [LOOKUP_VALUE], [MEANING], [LANGUAGE], [CREATOR], [CREATE_DATE], [MODIFIER], [MODIFY_DATE]) VALUES (N'SY_S_DEPARTMENT.DEPT_CODE', N'PVQ80-XFL25', N'采购开发组', N'zh-cn', N'admin', CAST(N'2021-08-23 17:18:58.800' AS DateTime), N'admin', CAST(N'2021-08-24 15:11:04.877' AS DateTime))
INSERT [dbo].[SY_S_GLOBAL_MULTIPLE_LANGUAGE_VALUE] ([LOOKUP_TYPE], [LOOKUP_VALUE], [MEANING], [LANGUAGE], [CREATOR], [CREATE_DATE], [MODIFIER], [MODIFY_DATE]) VALUES (N'SY_S_DEPARTMENT.DEPT_CODE', N'TGC68', N'品质管理中心', N'zh-cn', N'admin', CAST(N'2021-08-23 17:13:58.413' AS DateTime), NULL, NULL)
INSERT [dbo].[SY_S_GLOBAL_MULTIPLE_LANGUAGE_VALUE] ([LOOKUP_TYPE], [LOOKUP_VALUE], [MEANING], [LANGUAGE], [CREATOR], [CREATE_DATE], [MODIFIER], [MODIFY_DATE]) VALUES (N'SY_S_DEPARTMENT.DEPT_CODE', N'XLE66', N'研发中心', N'zh-cn', N'admin', CAST(N'2021-08-23 17:17:17.787' AS DateTime), N'admin', CAST(N'2021-08-23 17:17:50.333' AS DateTime))
INSERT [dbo].[SY_S_GLOBAL_MULTIPLE_LANGUAGE_VALUE] ([LOOKUP_TYPE], [LOOKUP_VALUE], [MEANING], [LANGUAGE], [CREATOR], [CREATE_DATE], [MODIFIER], [MODIFY_DATE]) VALUES (N'SY_S_DEPARTMENT.DEPT_CODE', N'XLE66-EIB40', N'模块组', N'zh-cn', N'admin', CAST(N'2021-08-23 17:18:23.443' AS DateTime), N'admin', CAST(N'2021-08-23 17:22:42.563' AS DateTime))
INSERT [dbo].[SY_S_GLOBAL_MULTIPLE_LANGUAGE_VALUE] ([LOOKUP_TYPE], [LOOKUP_VALUE], [MEANING], [LANGUAGE], [CREATOR], [CREATE_DATE], [MODIFIER], [MODIFY_DATE]) VALUES (N'SY_S_DEPARTMENT.DEPT_CODE', N'XLE66-GOJ97', N'中试/测试组', N'zh-cn', N'admin', CAST(N'2021-08-23 17:18:31.330' AS DateTime), NULL, NULL)
INSERT [dbo].[SY_S_GLOBAL_MULTIPLE_LANGUAGE_VALUE] ([LOOKUP_TYPE], [LOOKUP_VALUE], [MEANING], [LANGUAGE], [CREATOR], [CREATE_DATE], [MODIFIER], [MODIFY_DATE]) VALUES (N'SY_S_DEPARTMENT.DEPT_CODE', N'XLE66-IIO60', N'研发管理组', N'zh-cn', N'admin', CAST(N'2021-08-23 17:17:44.663' AS DateTime), NULL, NULL)
INSERT [dbo].[SY_S_GLOBAL_MULTIPLE_LANGUAGE_VALUE] ([LOOKUP_TYPE], [LOOKUP_VALUE], [MEANING], [LANGUAGE], [CREATOR], [CREATE_DATE], [MODIFIER], [MODIFY_DATE]) VALUES (N'SY_S_DEPARTMENT.DEPT_CODE', N'XLE66-NBN57', N'总经办', N'zh-cn', N'admin', CAST(N'2021-08-23 17:22:47.197' AS DateTime), N'admin', CAST(N'2021-08-25 09:36:43.723' AS DateTime))
INSERT [dbo].[SY_S_GLOBAL_MULTIPLE_LANGUAGE_VALUE] ([LOOKUP_TYPE], [LOOKUP_VALUE], [MEANING], [LANGUAGE], [CREATOR], [CREATE_DATE], [MODIFIER], [MODIFY_DATE]) VALUES (N'SY_S_DEPARTMENT.DEPT_CODE', N'XLE66-QUO52', N'通用研发组', N'zh-cn', N'admin', CAST(N'2021-08-23 17:17:37.150' AS DateTime), NULL, NULL)
INSERT [dbo].[SY_S_GLOBAL_MULTIPLE_LANGUAGE_VALUE] ([LOOKUP_TYPE], [LOOKUP_VALUE], [MEANING], [LANGUAGE], [CREATOR], [CREATE_DATE], [MODIFIER], [MODIFY_DATE]) VALUES (N'SY_S_DEPARTMENT.DEPT_CODE', N'XLE66-USO29', N'可靠性组', N'zh-cn', N'admin', CAST(N'2021-08-23 17:17:28.790' AS DateTime), N'admin', CAST(N'2021-08-23 17:23:07.150' AS DateTime))
INSERT [dbo].[SY_S_GLOBAL_MULTIPLE_LANGUAGE_VALUE] ([LOOKUP_TYPE], [LOOKUP_VALUE], [MEANING], [LANGUAGE], [CREATOR], [CREATE_DATE], [MODIFIER], [MODIFY_DATE]) VALUES (N'SY_S_DEPARTMENT.DEPT_CODE', N'XUD08', N'制造中心', N'zh-cn', N'admin', CAST(N'2021-08-23 17:15:30.067' AS DateTime), NULL, NULL)
INSERT [dbo].[SY_S_GLOBAL_MULTIPLE_LANGUAGE_VALUE] ([LOOKUP_TYPE], [LOOKUP_VALUE], [MEANING], [LANGUAGE], [CREATOR], [CREATE_DATE], [MODIFIER], [MODIFY_DATE]) VALUES (N'SY_S_DEPARTMENT.DEPT_CODE', N'XUD08-ERW93', N'计划与外协管理部', N'zh-cn', N'admin', CAST(N'2021-08-23 17:16:48.160' AS DateTime), N'QINGYOU.LI', CAST(N'2021-10-25 15:12:42.890' AS DateTime))
INSERT [dbo].[SY_S_GLOBAL_MULTIPLE_LANGUAGE_VALUE] ([LOOKUP_TYPE], [LOOKUP_VALUE], [MEANING], [LANGUAGE], [CREATOR], [CREATE_DATE], [MODIFIER], [MODIFY_DATE]) VALUES (N'SY_S_DEPARTMENT.DEPT_CODE', N'XUD08-EZV98', N'IT组', N'zh-cn', N'admin', CAST(N'2021-08-23 17:17:08.100' AS DateTime), NULL, NULL)
INSERT [dbo].[SY_S_GLOBAL_MULTIPLE_LANGUAGE_VALUE] ([LOOKUP_TYPE], [LOOKUP_VALUE], [MEANING], [LANGUAGE], [CREATOR], [CREATE_DATE], [MODIFIER], [MODIFY_DATE]) VALUES (N'SY_S_DEPARTMENT.DEPT_CODE', N'XUD08-FID15', N'工程组', N'zh-cn', N'admin', CAST(N'2021-08-23 17:16:23.643' AS DateTime), NULL, NULL)
INSERT [dbo].[SY_S_GLOBAL_MULTIPLE_LANGUAGE_VALUE] ([LOOKUP_TYPE], [LOOKUP_VALUE], [MEANING], [LANGUAGE], [CREATOR], [CREATE_DATE], [MODIFIER], [MODIFY_DATE]) VALUES (N'SY_S_DEPARTMENT.DEPT_CODE', N'XUD08-GVZ39', N'TO车间', N'zh-cn', N'admin', CAST(N'2021-08-23 17:16:38.343' AS DateTime), NULL, NULL)
INSERT [dbo].[SY_S_GLOBAL_MULTIPLE_LANGUAGE_VALUE] ([LOOKUP_TYPE], [LOOKUP_VALUE], [MEANING], [LANGUAGE], [CREATOR], [CREATE_DATE], [MODIFIER], [MODIFY_DATE]) VALUES (N'SY_S_DEPARTMENT.DEPT_CODE', N'XUD08-IRM47', N'模块车间', N'zh-cn', N'admin', CAST(N'2021-08-23 17:16:05.323' AS DateTime), NULL, NULL)
INSERT [dbo].[SY_S_GLOBAL_MULTIPLE_LANGUAGE_VALUE] ([LOOKUP_TYPE], [LOOKUP_VALUE], [MEANING], [LANGUAGE], [CREATOR], [CREATE_DATE], [MODIFIER], [MODIFY_DATE]) VALUES (N'SY_S_DEPARTMENT.DEPT_CODE', N'XUD08-JUD89', N'检验组', N'zh-cn', N'admin', CAST(N'2021-08-23 17:15:47.403' AS DateTime), N'admin', CAST(N'2021-09-15 10:27:02.697' AS DateTime))
INSERT [dbo].[SY_S_GLOBAL_MULTIPLE_LANGUAGE_VALUE] ([LOOKUP_TYPE], [LOOKUP_VALUE], [MEANING], [LANGUAGE], [CREATOR], [CREATE_DATE], [MODIFIER], [MODIFY_DATE]) VALUES (N'SY_S_DEPARTMENT.DEPT_CODE', N'XUD08-MJH63', N'组件车间', N'zh-cn', N'admin', CAST(N'2021-08-23 17:16:14.873' AS DateTime), NULL, NULL)
INSERT [dbo].[SY_S_GLOBAL_MULTIPLE_LANGUAGE_VALUE] ([LOOKUP_TYPE], [LOOKUP_VALUE], [MEANING], [LANGUAGE], [CREATOR], [CREATE_DATE], [MODIFIER], [MODIFY_DATE]) VALUES (N'SY_S_DEPARTMENT.DEPT_CODE', N'YRT64', N'综合管理中心', N'zh-cn', N'admin', CAST(N'2021-08-23 17:19:08.837' AS DateTime), NULL, NULL)
INSERT [dbo].[SY_S_GLOBAL_MULTIPLE_LANGUAGE_VALUE] ([LOOKUP_TYPE], [LOOKUP_VALUE], [MEANING], [LANGUAGE], [CREATOR], [CREATE_DATE], [MODIFIER], [MODIFY_DATE]) VALUES (N'SY_S_DEPARTMENT.DEPT_CODE', N'YRT64-SOF31', N'人力资源组', N'zh-cn', N'admin', CAST(N'2021-08-23 17:19:24.567' AS DateTime), NULL, NULL)
INSERT [dbo].[SY_S_GLOBAL_MULTIPLE_LANGUAGE_VALUE] ([LOOKUP_TYPE], [LOOKUP_VALUE], [MEANING], [LANGUAGE], [CREATOR], [CREATE_DATE], [MODIFIER], [MODIFY_DATE]) VALUES (N'SY_S_DEPARTMENT.DEPT_CODE', N'YRT64-YZR91', N'党群公共事务组', N'zh-cn', N'admin', CAST(N'2021-08-23 17:19:34.083' AS DateTime), N'admin', CAST(N'2021-09-15 08:03:51.747' AS DateTime))
INSERT [dbo].[SY_S_GLOBAL_MULTIPLE_LANGUAGE_VALUE] ([LOOKUP_TYPE], [LOOKUP_VALUE], [MEANING], [LANGUAGE], [CREATOR], [CREATE_DATE], [MODIFIER], [MODIFY_DATE]) VALUES (N'SY_S_DEPARTMENT.DEPT_CODE', N'ZFL31', N'销售中心', N'zh-cn', N'admin', CAST(N'2021-08-23 17:12:18.823' AS DateTime), N'admin', CAST(N'2021-08-23 17:29:29.750' AS DateTime))
INSERT [dbo].[SY_S_GLOBAL_MULTIPLE_LANGUAGE_VALUE] ([LOOKUP_TYPE], [LOOKUP_VALUE], [MEANING], [LANGUAGE], [CREATOR], [CREATE_DATE], [MODIFIER], [MODIFY_DATE]) VALUES (N'SY_S_DEPARTMENT.DEPT_CODE', N'ZFL31-OKR21', N'客户服务组', N'zh-cn', N'admin', CAST(N'2021-08-23 17:15:18.357' AS DateTime), N'admin', CAST(N'2022-01-20 15:50:10.040' AS DateTime))
INSERT [dbo].[SY_S_GLOBAL_MULTIPLE_LANGUAGE_VALUE] ([LOOKUP_TYPE], [LOOKUP_VALUE], [MEANING], [LANGUAGE], [CREATOR], [CREATE_DATE], [MODIFIER], [MODIFY_DATE]) VALUES (N'SY_S_DEPARTMENT.DEPT_CODE', N'ZFL31-ORN15', N'销售组', N'zh-cn', N'admin', CAST(N'2021-08-23 17:14:47.257' AS DateTime), N'admin', CAST(N'2022-01-20 15:50:07.040' AS DateTime))
INSERT [dbo].[SY_S_GLOBAL_MULTIPLE_LANGUAGE_VALUE] ([LOOKUP_TYPE], [LOOKUP_VALUE], [MEANING], [LANGUAGE], [CREATOR], [CREATE_DATE], [MODIFIER], [MODIFY_DATE]) VALUES (N'SY_S_DEPARTMENT.DEPT_CODE', N'ZFL31-XFF51', N'FAE组', N'zh-cn', N'admin', CAST(N'2021-08-23 17:15:04.457' AS DateTime), N'admin', CAST(N'2022-01-20 15:50:01.553' AS DateTime))

树形菜单表SY_S_DEPARTMENT
在这里插入图片描述

二、创建项目
在这里插入图片描述

层级介绍：
Web层 UI 层
Services 业务逻辑接口层
Repository 业务数据处理层
Models 实体映射层
TestServices 业务逻辑单元测试层

设置引用关系：
Web层调用 Models、Services 层
Services层调用 Repository、Models 层
Repository层调用 Models 层
Models层实体层

TestServices 业务逻辑单元测试层调用 Models、Services 层

①Models层创建
EF CORE 映射实体：
安装 NuGet 包
Microsoft.EntityFrameworkCore.SqlServer
Microsoft.EntityFrameworkCore.Design
Microsoft.EntityFrameworkCore.Tools

映射实体
在这里插入图片描述

PM> Scaffold-DbContext -Force “Server=.;database=Test_Demo;Trusted_Connection=True;” Microsoft.EntityFrameworkCore.SqlServer -OutputDir Models
在这里插入图片描述
映射的表和 Context

修改Context OnConfiguring方法

public static string ConnectionString { get; set; }
protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
	if (!optionsBuilder.IsConfigured)
	{
#warning To protect potentially sensitive information in your connection string, you should move it out of source code. You can avoid scaffolding the connection string by using the Name= syntax to read it from configuration - see https://go.microsoft.com/fwlink/?linkid=2131148. For more guidance on storing connection strings, see http://go.microsoft.com/fwlink/?LinkId=723263.
		//optionsBuilder.UseSqlServer("Server=.;database=Test_Demo;Trusted_Connection=True;");
		optionsBuilder.UseSqlServer(ConnectionString);
	}
}

关于一些复杂的SQL语句，EF 实现难度大可以添加 SQLDBHelper

    public class SQLHelper
    {
        static string connectionString = string.Empty;
        public SQLHelper(string ConnectionStrings)
        {
            //连接字符串
            connectionString = ConnectionStrings;
        }
       
        #region 执行查询，返回DataTable对象-----------------------
        public static DataTable GetTable(string strSQL)
        {
            return GetTable(strSQL, null);
        }
        public static DataTable GetTable(string strSQL, SqlParameter[] pas)
        {
            return GetTable(strSQL, pas, CommandType.Text);
        }
        /// <summary>
        /// 执行查询，返回DataTable对象
        /// </summary>
        /// <param name="strSQL">sql语句</param>
        /// <param name="pas">参数数组</param>
        /// <param name="cmdtype">Command类型</param>
        /// <returns>DataTable对象</returns>
        public static DataTable GetTable(string strSQL, SqlParameter[] pas, CommandType cmdtype)
        {
            DataTable dt = new DataTable();
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                SqlDataAdapter da = new SqlDataAdapter(strSQL, conn);
                da.SelectCommand.CommandType = cmdtype;
                if (pas != null)
                {
                    da.SelectCommand.Parameters.AddRange(pas);
                }
                da.Fill(dt);
            }
            return dt;
        }
        #endregion
        #region 执行查询，返回DataSet对象-------------------------
        public static DataSet GetDataSet(string strSQL)
        {
            return GetDataSet(strSQL, null);
        }
        public static DataSet GetDataSet(string strSQL, SqlParameter[] pas)
        {
            return GetDataSet(strSQL, pas, CommandType.Text);
        }
        /// <summary>
        /// 执行查询，返回DataSet对象
        /// </summary>
        /// <param name="strSQL">sql语句</param>
        /// <param name="pas">参数数组</param>
        /// <param name="cmdtype">Command类型</param>
        /// <returns>DataSet对象</returns>
        public static DataSet GetDataSet(string strSQL, SqlParameter[] pas, CommandType cmdtype)
        {
            DataSet dt = new DataSet();
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                SqlDataAdapter da = new SqlDataAdapter(strSQL, conn);
                da.SelectCommand.CommandType = cmdtype;
                if (pas != null)
                {
                    da.SelectCommand.Parameters.AddRange(pas);
                }
                da.Fill(dt);
            }
            return dt;
        }
        #endregion
        #region 执行非查询存储过程和SQL语句-----------------------------
        public static int ExcuteProc(string ProcName)
        {
            return ExcuteSQL(ProcName, null, CommandType.StoredProcedure);
        }
        public static int ExcuteProc(string ProcName, SqlParameter[] pars)
        {
            return ExcuteSQL(ProcName, pars, CommandType.StoredProcedure);
        }
        public static int ExcuteSQL(string strSQL)
        {
            return ExcuteSQL(strSQL, null);
        }
        public static int ExcuteSQL(string strSQL, SqlParameter[] paras)
        {
            return ExcuteSQL(strSQL, paras, CommandType.Text);
        }
        /// 执行非查询存储过程和SQL语句
        /// 增、删、改
        /// </summary>
        /// <param name="strSQL">要执行的SQL语句</param>
        /// <param name="paras">参数列表，没有参数填入null</param>
        /// <param name="cmdType">Command类型</param>
        /// <returns>返回影响行数</returns>
        public static int ExcuteSQL(string strSQL, SqlParameter[] paras, CommandType cmdType)
        {
            int i = 0;
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                SqlCommand cmd = new SqlCommand(strSQL, conn);
                cmd.CommandType = cmdType;
                if (paras != null)
                {
                    cmd.Parameters.AddRange(paras);
                }
                conn.Open();
                i = cmd.ExecuteNonQuery();
                conn.Close();
            }
            return i;
        }
        #endregion
        #region 执行查询返回第一行，第一列---------------------------------
        public static int ExcuteScalarSQL(string strSQL)
        {
            return ExcuteScalarSQL(strSQL, null);
        }
        public static int ExcuteScalarSQL(string strSQL, SqlParameter[] paras)
        {
            return ExcuteScalarSQL(strSQL, paras, CommandType.Text);
        }
        public static int ExcuteScalarProc(string strSQL, SqlParameter[] paras)
        {
            return ExcuteScalarSQL(strSQL, paras, CommandType.StoredProcedure);
        }
        /// <summary>
        /// 执行SQL语句，返回第一行，第一列
        /// </summary>
        /// <param name="strSQL">要执行的SQL语句</param>
        /// <param name="paras">参数列表，没有参数填入null</param>
        /// <returns>返回影响行数</returns>
        public static int ExcuteScalarSQL(string strSQL, SqlParameter[] paras, CommandType cmdType)
        {
            int i = 0;
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                SqlCommand cmd = new SqlCommand(strSQL, conn);
                cmd.CommandType = cmdType;
                if (paras != null)
                {
                    cmd.Parameters.AddRange(paras);
                }
                conn.Open();
                i = Convert.ToInt32(cmd.ExecuteScalar());
                conn.Close();
            }
            return i;
        }
        #endregion
        #region 查询获取单个值------------------------------------
        /// <summary>
        /// 调用不带参数的存储过程获取单个值
        /// </summary>
        /// <param name="ProcName"></param>
        /// <returns></returns>
        public static object GetObjectByProc(string ProcName)
        {
            return GetObjectByProc(ProcName, null);
        }
        /// <summary>
        /// 调用带参数的存储过程获取单个值
        /// </summary>
        /// <param name="ProcName"></param>
        /// <param name="paras"></param>
        /// <returns></returns>
        public static object GetObjectByProc(string ProcName, SqlParameter[] paras)
        {
            return GetObject(ProcName, paras, CommandType.StoredProcedure);
        }
        /// <summary>
        /// 根据sql语句获取单个值
        /// </summary>
        /// <param name="strSQL"></param>
        /// <returns></returns>
        public static object GetObject(string strSQL)
        {
            return GetObject(strSQL, null);
        }
        /// <summary>
        /// 根据sql语句 和 参数数组获取单个值
        /// </summary>
        /// <param name="strSQL"></param>
        /// <param name="paras"></param>
        /// <returns></returns>
        public static object GetObject(string strSQL, SqlParameter[] paras)
        {
            return GetObject(strSQL, paras, CommandType.Text);
        }
        /// <summary>
        /// 执行SQL语句，返回首行首列
        /// </summary>
        /// <param name="strSQL">要执行的SQL语句</param>
        /// <param name="paras">参数列表，没有参数填入null</param>
        /// <returns>返回的首行首列</returns>
        public static object GetObject(string strSQL, SqlParameter[] paras, CommandType cmdtype)
        {
            object o = null;
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                SqlCommand cmd = new SqlCommand(strSQL, conn);
                cmd.CommandType = cmdtype;
                if (paras != null)
                {
                    cmd.Parameters.AddRange(paras);
                }
                conn.Open();
                o = cmd.ExecuteScalar();
                conn.Close();
            }
            return o;
        }
        #endregion
        #region 查询获取DataReader------------------------------------
        /// <summary>
        /// 调用不带参数的存储过程，返回DataReader对象
        /// </summary>
        /// <param name="procName">存储过程名称</param>
        /// <returns>DataReader对象</returns>
        public static SqlDataReader GetReaderByProc(string procName)
        {
            return GetReaderByProc(procName, null);
        }
        /// <summary>
        /// 调用带有参数的存储过程，返回DataReader对象
        /// </summary>
        /// <param name="procName">存储过程名</param>
        /// <param name="paras">参数数组</param>
        /// <returns>DataReader对象</returns>
        public static SqlDataReader GetReaderByProc(string procName, SqlParameter[] paras)
        {
            return GetReader(procName, paras, CommandType.StoredProcedure);
        }
        /// <summary>
        /// 根据sql语句返回DataReader对象
        /// </summary>
        /// <param name="strSQL">sql语句</param>
        /// <returns>DataReader对象</returns>
        public static SqlDataReader GetReader(string strSQL)
        {
            return GetReader(strSQL, null);
        }
        /// <summary>
        /// 根据sql语句和参数返回DataReader对象
        /// </summary>
        /// <param name="strSQL">sql语句</param>
        /// <param name="paras">参数数组</param>
        /// <returns>DataReader对象</returns>
        public static SqlDataReader GetReader(string strSQL, SqlParameter[] paras)
        {
            return GetReader(strSQL, paras, CommandType.Text);
        }
        /// <summary>
        /// 查询SQL语句获取DataReader
        /// </summary>
        /// <param name="strSQL">查询的SQL语句</param>
        /// <param name="paras">参数列表，没有参数填入null</param>
        /// <returns>查询到的DataReader（关闭该对象的时候，自动关闭连接）</returns>
        public static SqlDataReader GetReader(string strSQL, SqlParameter[] paras, CommandType cmdtype)
        {
            SqlDataReader sqldr = null;
            SqlConnection conn = new SqlConnection(connectionString);
            SqlCommand cmd = new SqlCommand(strSQL, conn);
            cmd.CommandType = cmdtype;
            if (paras != null)
            {
                cmd.Parameters.AddRange(paras);
            }
            conn.Open();
            //CommandBehavior.CloseConnection的作用是如果关联的DataReader对象关闭，则连接自动关闭
            sqldr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
            return sqldr;
        }
        #endregion
        #region 批量插入数据---------------------------------------------
        /// <summary>
        /// 往数据库中批量插入数据
        /// </summary>
        /// <param name="sourceDt">数据源表</param>
        /// <param name="targetTable">服务器上目标表</param>
        public static void BulkToDB(DataTable sourceDt, string targetTable)
        {
            SqlConnection conn = new SqlConnection(connectionString);
            SqlBulkCopy bulkCopy = new SqlBulkCopy(conn);
            //用其它源的数据有效批量加载sql server表中
            bulkCopy.DestinationTableName = targetTable;
            //服务器上目标表的名称
            bulkCopy.BatchSize = sourceDt.Rows.Count;   //每一批次中的行数
            try
            {
                conn.Open();
                if (sourceDt != null && sourceDt.Rows.Count != 0)
                    bulkCopy.WriteToServer(sourceDt);   //将提供的数据源中的所有行复制到目标表中
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                conn.Close();
                if (bulkCopy != null)
                    bulkCopy.Close();
            }
        }
        #endregion


        /// <summary>
        /// 连接数据库
        /// </summary>
        /// <param name="connStr">默认为null</param>
        /// <returns>SqlConnection</returns>
        public static SqlConnection CreateSqlConnection(string connStr = null)
        {
            SqlConnection conn = new SqlConnection();

            conn.ConnectionString = connStr == null ? connectionString : connStr;
            conn.Open();
            return conn;
        }

        #region Execute命令

        /// <summary>
        /// 执行带参数的增删改SQL语句或存储过程
        /// </summary>
        /// <param name="safeSql">增删改T-Sql语句或存储过程</param>
        /// <param name="paras">参数</param>
        /// <param name="ct">指定如何解释命令字符串</param>
        /// <param name="connStr">连接字符串</param>
        /// <returns>是否成功</returns>
        public static bool ExecuteNonQuery(string safeSql, SqlParameter[] paras, CommandType ct, string connStr = null)
        {
            using (SqlConnection Connection = new SqlConnection(connStr == null ? connectionString : connStr))
            {
                if (Connection.State != ConnectionState.Open)
                {
                    Connection.Open();
                }

                SqlCommand cmd = new SqlCommand(safeSql, Connection);
                cmd.CommandType = ct;
                if (paras != null)
                {
                    cmd.Parameters.AddRange(paras);
                }
                return Convert.ToInt32(cmd.ExecuteNonQuery()) > 0;
            }
        }

        /// <summary>
        /// 执行带参数的增删改SQL语句
        /// </summary>
        /// <param name="safeSql">增删改T-Sql语句</param>
        /// <param name="connStr">连接字符串</param>
        /// <returns>是否成功</returns>
        public static bool ExecuteNonQuery(string safeSql, string connStr = null)
        {
            using (SqlConnection Connection = new SqlConnection(connStr == null ? connectionString : connStr))
            {
                if (Connection.State != ConnectionState.Open)
                {
                    Connection.Open();
                }

                SqlCommand cmd = new SqlCommand(safeSql, Connection);
                return Convert.ToInt32(cmd.ExecuteNonQuery()) > 0;
            }
        }

        /// <summary>  
        /// 查询结果集中第一行第一列的值  
        /// </summary>  
        /// <param name="safeSql">T-Sql语句</param>  
        /// <returns>结果集T类型</returns>  
        public static object ExecuteScalar(string safeSql, string connStr = null)
        {
            using (SqlConnection Connection = new SqlConnection(connStr == null ? connectionString : connStr))
            {
                if (Connection.State != ConnectionState.Open)
                    Connection.Open();
                SqlCommand cmd = new SqlCommand(safeSql, Connection);
                return ExecuteScalar(safeSql, cmd);
            }
        }

        /// <summary>  
        /// 查询结果集中第一行第一列的值  
        /// </summary>  
        /// <param name="safeSql">T-Sql语句</param>  
        /// <returns>结果集T类型</returns>  
        public static object ExecuteScalar(string safeSql, SqlCommand command)
        {
            SqlCommand cmd = new SqlCommand(safeSql, command.Connection);
            return cmd.ExecuteScalar();
        }

        /// <summary>  
        /// 执行查询T-SQL语句
        /// </summary>  
        /// <param name="safeSql">T-Sql语句</param>  
        /// <returns>结果集DataTable</returns>  
        public static DataTable ExecuteDataTable(string safeSql, string connStr = null)
        {
            using (SqlConnection Connection = new SqlConnection(connStr == null ? connectionString : connStr))
            {
                if (Connection.State != ConnectionState.Open)
                {
                    Connection.Open();
                }

                SqlCommand cmd = new SqlCommand(safeSql, Connection);
                return ExecuteDataTable(safeSql, cmd);
            }
        }

        /// <summary>
        /// 执行查询T-SQL语句
        /// </summary>
        /// <param name="safeSql">T-SQL语句</param>
        /// <param name="Connection">SqlConnection</param>
        /// <returns>结果集DataTable</returns>
        private static DataTable ExecuteDataTable(string safeSql, SqlCommand command)
        {
            DataSet ds = new DataSet();
            SqlDataAdapter da = new SqlDataAdapter(command);
            try
            {
                da.Fill(ds);
            }
            catch (Exception ex)
            {

            }
            return ds.Tables[0];
        }

        /// <summary>
        /// 执行查询T-SQL语句
        /// </summary>
        /// <typeparam name="T">T类型</typeparam>
        /// <param name="safeSql">T-SQL语句</param>
        /// <param name="command">command</param>
        /// <returns>返回List</returns>
        public static List<T> ExecuteToList<T>(string safeSql, SqlCommand command)
        {
            var data = ExecuteDataTable(safeSql, command);
            return ConvertTo<T>(data);
        }

        /// <summary>
        /// 执行查询T-SQL语句
        /// </summary>
        /// <typeparam name="T">T类型</typeparam>
        /// <param name="safeSql">T-SQL语句</param>
        /// <returns>返回List</returns>
        public static List<T> ExecuteToList<T>(string safeSql, string connStr = null)
        {
            var data = ExecuteDataTable(safeSql, connStr);
            return ConvertTo<T>(data);
        }

        /// <summary>
        /// 执行查询T-SQL语句
        /// </summary>
        /// <typeparam name="T">T类型</typeparam>
        /// <param name="safeSql">T-SQL语句</param>
        /// <param name="connStr">连接字符串</param>
        /// <returns>T类型</returns>
        public static T ExecuteFirstOrDefault<T>(string safeSql, string connStr = null)
        {
            var table = ExecuteDataTable(safeSql, connStr);
            T obj = default(T);
            foreach (DataRow item in table.Rows)
            {
                obj = CreateItem<T>(item);
            }
            return obj;
        }

        /// <summary>
        /// 执行查询T-SQL语句
        /// </summary>
        /// <typeparam name="T">T类型</typeparam>
        /// <param name="sql">T-SQL语句</param>
        /// <param name="conn">SqlConnection</param>
        /// <returns>T类型</returns>
        public static T ExecuteFirstOrDefault<T>(string sql, SqlCommand command)
        {
            var table = ExecuteDataTable(sql, command);
            T obj = default(T);
            foreach (DataRow item in table.Rows)
            {
                obj = CreateItem<T>(item);
            }
            return obj;
        }

        /// <summary>
        /// SQL 分页查询
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="sql">SQL语句</param>
        /// <param name="order">排序字段</param>
        /// <param name="page">当前页数</param>
        /// <param name="size">每页总记录数</param>
        /// <param name="desc">排序方式</param>
        /// <returns></returns>
        public static PagedResultInOut<T> SqlPage<T>(string sql, string order, int page, int size, string desc = "DESC")
        {
            try
            {
                string pageSql = $@"SELECT * FROM(
                                    SELECT ROW_NUMBER()OVER(ORDER BY {order} {desc}) NUMBER,* 
                                    FROM({sql})AS [PAGE]) AS NUMBERTABLE WHERE NUMBER>=({size}*{page}-{size}) AND NUMBER<={size}*{page}";
                string countSql = $@"SELECT COUNT(0) FROM ({sql}) AS [COUNT]";
                using (SqlConnection Connection = new SqlConnection(connectionString))
                {
                    if (Connection.State != ConnectionState.Open)
                        Connection.Open();
                    DataSet ds = new DataSet();
                    SqlCommand page_cmd = new SqlCommand(pageSql, Connection);
                    SqlDataAdapter da = new SqlDataAdapter(page_cmd);
                    SqlCommand count_cmd = new SqlCommand(countSql, Connection);
                    int count = Convert.ToInt32(count_cmd.ExecuteScalar());
                    try
                    {
                        da.Fill(ds);
                    }
                    catch (Exception ex)
                    {
                        return new PagedResultInOut<T>() { Msg = ex.Message, Rows = new List<T>(), Total = 0 };
                    }
                    var list = ConvertTo<T>(ds.Tables[0]);
                    return new PagedResultInOut<T>() { Msg = "查询成功", Total = count, Rows = list };
                }
            }
            catch (Exception ex)
            {
                return new PagedResultInOut<T>() { Msg = ex.Message, Rows = new List<T>(), Total = 0 };
            }
        }

        #endregion

        #region 常用类
        public class PagedResultInOut<T>
        {
            /// <summary>
            /// 总条数
            /// </summary>
            public int Total { get; set; }
            /// <summary>
            /// 数据
            /// </summary>
            public List<T> Rows { get; set; }
            /// <summary>
            /// 提示
            /// </summary>
            public string Msg { get; set; }
        }
        /// <summary>
        /// DataTable转Json
        /// </summary>
        /// <param name="table"></param>
        /// <returns></returns>
        public static string DataTableToJson(DataTable table)
        {
            var JsonString = new StringBuilder();
            if (table.Rows.Count > 0)
            {
                JsonString.Append("[");
                for (int i = 0; i < table.Rows.Count; i++)
                {
                    JsonString.Append("{");
                    for (int j = 0; j < table.Columns.Count; j++)
                    {
                        if (j < table.Columns.Count - 1)
                        {
                            JsonString.Append("\"" + table.Columns[j].ColumnName.ToString() + "\":" + "\"" + table.Rows[i][j].ToString() + "\",");
                        }
                        else if (j == table.Columns.Count - 1)
                        {
                            JsonString.Append("\"" + table.Columns[j].ColumnName.ToString() + "\":" + "\"" + table.Rows[i][j].ToString() + "\"");
                        }
                    }
                    if (i == table.Rows.Count - 1)
                    {
                        JsonString.Append("}");
                    }
                    else
                    {
                        JsonString.Append("},");
                    }
                }
                JsonString.Append("]");
            }
            return JsonString.ToString();
        }
        #endregion

        #region 将DataTable转List
        public static List<T> ConvertTo<T>(DataTable table)
        {
            if (table == null)
            {
                return null;
            }
            List<DataRow> rows = new List<DataRow>();
            foreach (DataRow row in table.Rows)
            {
                rows.Add(row);
            }
            return ConvertTo<T>(rows);
        }
        public static List<T> ConvertTo<T>(List<DataRow> rows)
        {
            List<T> list = null;
            if (rows != null)
            {
                list = new List<T>();
                foreach (DataRow row in rows)
                {
                    T item = CreateItem<T>(row);
                    list.Add(item);
                }
            }
            return list;
        }
        public static T CreateItem<T>(DataRow row)
        {
            T obj = default(T);
            if (row != null)
            {
                obj = Activator.CreateInstance<T>();
                foreach (DataColumn column in row.Table.Columns)
                {
                    PropertyInfo prop = obj.GetType().GetProperty(column.ColumnName);
                    try
                    {
                        object value = row[column.ColumnName];
                        prop.SetValue(obj, value, null);
                    }
                    catch
                    { //You can log something here
                      //throw;
                    }
                }
            }
            return obj;
        }
        #endregion
    }

②Repository 层创建（引用Models层）

    public class TheTreeRepository : Test_DemoContext
    {
        Test_DemoContext Contexts = new Test_DemoContext();
        //Contexts.WDPackingListLevels.Where(x => true).ToList();
        SQLHelper sqlhelper = new SQLHelper(Test_DemoContext.ConnectionString);
        public IList<SySDepartment> WDPackingListLevelsRepository()
        {
            return SySDepartments.Where(x => true).ToList();
            /*
             * 
             *  var model = db.WDPackingListLevels.Where(x => true).ToList();
                //获取sql查询
                //1.创建接收实体：ModelName.cs
                //2.添加到数据库上下文：public virtual DbSte<ModelName> modelname {get; set;}
                //3.获取
                //var sql = "SELECT * FROM dbo.W_D_PACKING_LIST_LEVEL";
                //var model = db.Set<WDPackingListLevel>().FromSqlRaw(sql).ToList();
             */

            /*
            using (var db = new iROCK_DEVContext())
            {
                var model = db.WDPackingListLevels.Where(x => true).ToList();
                //获取sql查询
                //1.创建接收实体：ModelName.cs
                //2.添加到数据库上下文：public virtual DbSte<ModelName> modelname {get; set;}
                //3.获取
                //var sql = "SELECT * FROM dbo.W_D_PACKING_LIST_LEVEL";
                //var model = db.Set<WDPackingListLevel>().FromSqlRaw(sql).ToList();
                return View(model);
            }
            */

        }

        public DataTable WDPackingListLevelsRepositoryDT()
        {
            var sql = "SELECT * FROM dbo.W_D_PACKING_LIST_LEVEL";
            var model = Set<SySDepartment>().FromSqlRaw(sql);
            return (DataTable)model;
        }


        public IList<SySDepartment> WDPackingListLevelsRepository3()
        {
            var sql = "SELECT * FROM dbo.W_D_PACKING_LIST_LEVEL";
            var dt = SQLHelper.GetTable(sql);

            var model = Set<SySDepartment>().FromSqlRaw(sql).ToList();
            return model;
        }

        public DataTable GetTreeDataRepository()
        {
            //树形菜单绑定
            string str = $@"
            IF NOT OBJECT_ID(N'Tempdb..#T2') IS NULL
                DROP TABLE #T2;
            SELECT  R.id, DE.DEPT_CODE ,
                    VA.MEANING AS name ,
                    DE.UPPER_DEPT,
		            R1.parentId,
		            LINE_ID,
		            ISNULL(DE.MODIFY_DATE,ISNULL(VA.MODIFY_DATE,ISNULL(R.MODIFY_DATE,ISNULL(R1.MODIFY_DATE,DE.CREATE_DATE)))) AS UP_DATE
            INTO #T2
            FROM    SY_S_DEPARTMENT DE
                    INNER JOIN SY_S_GLOBAL_MULTIPLE_LANGUAGE_VALUE VA ON DE.DEPT_CODE = VA.LOOKUP_VALUE
                    INNER JOIN ( SELECT ROW_NUMBER() OVER ( ORDER BY CREATE_DATE,DEPT_CODE ASC ) AS id ,
                                        DEPT_CODE,MODIFY_DATE
                                    FROM   SY_S_DEPARTMENT
                                ) AS R ON R.DEPT_CODE = DE.DEPT_CODE 
                    LEFT JOIN ( SELECT ROW_NUMBER() OVER ( ORDER BY CREATE_DATE,DEPT_CODE ASC ) AS ParentId ,
                                        DEPT_CODE,MODIFY_DATE
                                    FROM   SY_S_DEPARTMENT
                                ) AS R1 ON R1.DEPT_CODE = DE.UPPER_DEPT
            WHERE   VA.LANGUAGE ='zh-cn' --'zh-cn'
                    AND DE.FACILITY='APAT'
                    AND DE.SITE='SZ'; 
            WITH    menu ( id, name, DEPT_CODE, UPPER_DEPT, parentId,LINE_ID,UP_DATE, Level, px, px2 )
                        AS ( SELECT   id ,
                                    name ,
                                    DEPT_CODE ,
                                    UPPER_DEPT ,
                                    parentId ,
						            LINE_ID,
						            UP_DATE,
                                    0 AS Level ,
                                    id px ,
                                    CAST(id AS NVARCHAR(4000)) px2
                            FROM     #T2
                            WHERE    parentId IS NULL
                            UNION ALL
                            SELECT   A.id ,
                                    A.name ,
                                    A.DEPT_CODE ,
                                    A.UPPER_DEPT ,
                                    A.parentId ,
						            A.LINE_ID,
						            A.UP_DATE,
                                    B.Level + 1 ,
                                    B.px ,
                                    B.px2 + LTRIM(A.id)
                            FROM     #T2 A
                                    INNER JOIN menu B ON A.parentId = B.id
                            )
                SELECT  id ,
                        name ,
                        DEPT_CODE ,
                        UPPER_DEPT ,
                        CASE WHEN parentId IS NULL AND DEPT_CODE='DO' AND id=1 THEN 0
				                WHEN parentId IS NULL AND DEPT_CODE <>'DO'AND id<>1 THEN 1
                                ELSE parentId
                        END AS parentId ,
			            LINE_ID,
                        Level ,
                        px ,
                        px2
                FROM    menu
                ORDER BY LINE_ID ASC,UP_DATE DESC;";
            return SQLHelper.GetTable(str);
        }
    }

③Services 层创建 (引用 Models、Repository 层)
在这里插入图片描述
Interface接口层

public interface ITheTreeServices
{
    //DataTable IndexFilter(string whitelistdto, string param);
    DataTable GetCodeDescByCodeNamet();
    IList<SySDepartment> WDPackingListLevelsRepository1();
    DataTable GetTreeData();
}

Implementation接口调用层

public class TheTreeServices : TheTreeRepository, ITheTreeServices
{
    public TheTreeServices() { }
    public DataTable GetCodeDescByCodeNamet()
    {
        DataTable data = WDPackingListLevelsRepositoryDT();
        return data;
    }

    public IList<SySDepartment> WDPackingListLevelsRepository1()
    {
        return WDPackingListLevelsRepository3();
    }

    public DataTable GetTreeData()
    {
        return GetTreeDataRepository();
    }
}

④Web层 Controllers 调用 Implementation 方法

using Microsoft.AspNetCore.Mvc;
using Microsoft.Extensions.Logging;
using Models.Models;
using Services.Implementation;
using System.Diagnostics;
using System.Linq;
using TREE.Models;

namespace TREE.Controllers
{
    public class HomeController : Controller
    {
        TheTreeServices _thetreeservices = new TheTreeServices();
        private readonly ILogger<HomeController> _logger;
        public HomeController(ILogger<HomeController> logger)
        {
            _logger = logger;
        }

        public IActionResult Index()
        {
            //var model="";
            var dt = _thetreeservices.GetTreeData();

            var model = _thetreeservices.WDPackingListLevelsRepository();

            return View(model);
        }
    }
}

配置文件：
appsettings.json

{
  "ConnectionString": {
    "SqlServerConnectionString": "Server=.;database=Test_Demo;Trusted_Connection=True;" 
      //"Server=192.168.0.0000;Database=datename;User ID=iROCK;Password=12###;"
  },
  "Logging": {
    "LogLevel": {
      "Default": "Information",
      "Microsoft": "Warning",
      "Microsoft.Hosting.Lifetime": "Information"
    }
  },
  "AllowedHosts": "*"
}

Startup.cs

// This method gets called by the runtime. Use this method to add services to the container.
public void ConfigureServices(IServiceCollection services)
{
    Test_DemoContext.ConnectionString = Configuration["ConnectionString:SqlServerConnectionString"];
    services.AddControllersWithViews();
}