clarification
This article is part of the OverallAuth 2.0 series of articles, which is updated weekly with an article in the series (Complete System Development from 0 to 1).
I will try to be very detailed in this system article, so that it can be understood by novice and veteran alike.
DESCRIPTION: OverallAuth 2.0 is a simple, easy to understand and powerful Permissions + Visual Process Management system.
Friendly reminder: this article is part of a series of articles, before reading that article, it is recommended to read the previous articles to better understand the structure of the project.
If you are interested, please follow me (*^▽^*).
Follow me. Beat me if you can't learn.
Install Dapper
1. Install the latest version of Dapper where you are using it.
2. In the place of use, install the latest version of the
3. Install the latest version of the
4. Install the latest version where you use it
The final installation package is as follows:
Creating a Database Connection Class
Before we create the database connection class, we have to configure our database connection string in.
SqlConnection": "Server=SQLOLEDB;Data Source=yourssqlServername (of a thing);uid=yours数据库账号;pwd=yours数据库密码;DataBase=yours数据库名字
Below:
After configuring the database connection, we need a class to read the database, so we need to create it in the Infrastructure layer (Infrastructure).
Create the folder DataBaseConnect and then create a class in.
The content is as follows:
using ; using ; using ; using ; namespace { /// <summary> /// database connection class /// </summary> public static class DataBaseConnectConfig { /// <summary> /// Declaring a static connection /// </summary> public static IConfiguration Configuration { get; set; } /// <summary> /// static constructor /// </summary> static DataBaseConnectConfig() { //ReloadOnChange = true Reload when changed Configuration = new ConfigurationBuilder() .Add(new JsonConfigurationSource { Path = "", ReloadOnChange = true }) .Build(); } /// <summary> /// Connecting to the database /// </summary> /// <param name="sqlConnectionStr">Connect to database string</param> /// <returns></returns> public static SqlConnection GetSqlConnection(string? sqlConnectionStr = null) { if (string.IsNullOrWhiteSpace(sqlConnectionStr)) { sqlConnectionStr = Configuration["ConnectionStrings:SqlConnection"]; } var connection = new SqlConnection(sqlConnectionStr); if ( != ) { (); } return connection; } } }
GetSqlConnectionmethod reads the connection configuration in the，and open the database。
Create Warehouse
With the database connection class, we have to start building the underlying storage, the structure is as follows:
Based on the above structure, we create the warehousing interface and the implementation of the warehousing interface respectively
The content is as follows:
/// <summary> /// Warehouse Interface Definition /// </summary> public interface IRepository { } /// <summary> /// Defining a generalized warehousing interface /// </summary> /// <typeparam name="T">Entity type</typeparam> /// <typeparam name="object">Primary Key Type</typeparam> public interface IRepository<T> : IRepository where T : class, new() { /// <summary> /// additional /// </summary> /// <param name="entity">thing that has a material existence (as opposed a conceptual, virtual or online existence)</param> /// <param name="innserSql">Add sql</param> /// <returns></returns> int Insert(T entity, string innserSql); /// <summary> /// modifications /// </summary> /// <param name="entity">thing that has a material existence (as opposed a conceptual, virtual or online existence)</param> /// <param name="updateSql">Update sql</param> /// <returns></returns> int Update(T entity, string updateSql); /// <summary> /// removing /// </summary> /// <param name="deleteSql">Delete sql</param> /// <returns></returns> int Delete(string key, string deleteSql); /// <summary> /// Get model based on primary key /// </summary> /// <param name="key">primary key</param> /// <param name="selectSql">Query sql</param> /// <returns></returns> T GetByKey(string key, string selectSql); /// <summary> /// Get all data /// </summary> /// <param name="selectAllSql">Query sql</param> /// <returns></returns> List<T> GetAll(string selectAllSql); /// <summary> /// Verify data exists based on a unique primary key /// </summary> /// <param name="id">primary key</param> /// <param name="selectSql">Query sql</param> /// <returns>Returns true exists, false does not exist</returns> bool IsExist(string id, string selectSql);
Description 1: The storage interface is commonly used crud (add, delete, modify and check) interface, which applies to all table structures, so that we no longer repeat the same sql statements.
Note 2: This storage interface can be customized with generic interfaces, such as paging queries, batch additions, batch modifications, and so on.
The structure is as follows
/// <summary> /// Storage base class /// </summary> /// <typeparam name="T">Entity type</typeparam> /// <typeparam name="TPrimaryKey">Primary Key Type</typeparam> public abstract class Repository<T> : IRepository<T> where T : class, new() { /// <summary> /// removing /// </summary> /// <param name="deleteSql">Delete sql</param> /// <returns></returns> public int Delete(string key, string deleteSql) { using var connection = (); return (deleteSql, new { Key = key }); } /// <summary> /// Get model based on primary key /// </summary> /// <param name="id">primary key</param> /// <param name="selectSql">Query sql</param> /// <returns></returns> public T GetByKey(string id, string selectSql) { using var connection = (); return <T>(selectSql, new { Key = id }); } /// <summary> /// Get all data /// </summary> /// <param name="selectAllSql">Query sql</param> /// <returns></returns> public List<T> GetAll(string selectAllSql) { using var connection = (); return <T>(selectAllSql).ToList(); } /// <summary> /// additional /// </summary> /// <param name="entity">Additional entities</param> /// <param name="innserSql">Add sql</param> /// <returns></returns> public int Insert(T entity, string innserSql) { using var connection = (); return (innserSql, entity); } /// <summary> /// Verify data exists based on a unique primary key /// </summary> /// <param name="id">primary key</param> /// <param name="selectSql">Query sql</param> /// <returns>Returns true exists, false does not exist</returns> public bool IsExist(string id, string selectSql) { using var connection = (); var count = <int>(selectSql, new { Key = id }); if (count > 0) return true; else return false; } /// <summary> /// update /// </summary> /// <param name="entity">Updated entities</param> /// <param name="updateSql">Update sql</param> /// <returns></returns> public int Update(T entity, string updateSql) { using var connection = (); return (updateSql, entity); } }
This class is an implementation of the Warehousing interface, which inherits from the
Create base sql storage (can be omitted)
After doing the above, our underlying storage has in fact been built, but the blogger has built a basic sql storage in order to manage all the basic sql statements in the project.
Create a class in the Infrastructure root directory.
Write the base statement for sql, the existing statement is as follows.
/// <summary> /// Create inheritance sql storage /// </summary> public class BaseSqlRepository { #region a meter (measuring sth)Sys_user /// <summary> /// sys_user added /// </summary> public static string sysUser_insertSql = @"insert into Sys_User (UserName ,Password ,Age,Sex,IsOpen,DepartmentId,CreateTime,CreateUser) values(@UserName ,@Password ,@Age,@Sex,@IsOpen,@DepartmentId,@CreateTime,@CreateUser)"; /// <summary> /// sys_user update /// </summary> public static string sysUser_updateSql = @"update Sys_User set UserName=@UserName ,Password=@Password ,Age=@Age,Sex=@Sex,DepartmentId=@DepartmentId,CreateTime=@CreateTime,CreateUser=@CreateUser where UserId = @UserId"; /// <summary> /// sys_user query /// </summary> public static string sysUser_selectByKeySql = @" select * from Sys_User where UserId=@Key"; /// <summary> /// sys_user table lookup all statements /// </summary> public static string sysUser_selectAllSql = @" select * from Sys_User"; #endregion }
Create the table Sys_User model
The structure is as follows:
/// <summary> /// user table model /// </summary> public class SysUser { /// <summary> /// User id /// </summary> public int UserId { get; set; } /// <summary> /// user ID /// </summary> public string UserName { get; set; } /// <summary> /// cryptographic /// </summary> public string Password { get; set; } /// <summary> /// (a person's) age /// </summary> public int Age { get; set; } /// <summary> /// distinguishing between the sexes /// </summary> public int Sex { get; set; } /// <summary> /// Whether or not to turn on /// </summary> public bool IsOpen { get; set; } /// <summary> /// Department id /// </summary> public int DepartmentId { get; set; } /// <summary> /// Creation time /// </summary> public DateTime CreateTime { get; set; } /// <summary> /// founder /// </summary> public string CreateUser { get; set; } }
The structure, same as the database table structure (more on that in a minute)
Using Warehousing
Inherit and in the previous and
/// <summary> /// User Services Warehouse Interface /// </summary> public interface ISysUserRepository : IRepository<SysUser> { /// <summary> /// Testing Autofac /// </summary> /// <returns></returns> string TestAutofac(); }
/// <summary> /// User Service Warehouse Interface Implementation /// </summary> public class SysUserRepository : Repository<SysUser>, ISysUserRepository { /// <summary> /// Testing Autofac /// </summary> /// <returns></returns> public string TestAutofac() { return "Autofac used successfully"; } }
After this user storage inherits and , it owns all interfaces under that storage interface.
beta (software)
Having done the above, let's start testing
First, we need to create the database (using sqlServer database) and the user table
The table structure and data code are as follows:
USE [OverallAuth] GO /****** Object: Table [dbo].[Sys_User] Script Date: 2024/10/6 10:38:21 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[Sys_User]( [UserId] [int] IDENTITY(1,1) NOT NULL, [UserName] [varchar](50) NOT NULL, [Password] [varchar](50) NOT NULL, [Age] [int] NULL, [Sex] [int] NULL, [DepartmentId] [int] NOT NULL, [IsOpen] [bit] NULL, [CreateTime] [datetime] NULL, [CreateUser] [varchar](50) NULL, CONSTRAINT [PK_Sys_User] 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] GO SET IDENTITY_INSERT [dbo].[Sys_User] ON INSERT [dbo].[Sys_User] ([UserId], [UserName], [Password], [Age], [Sex], [DepartmentId], [IsOpen], [CreateTime], [CreateUser]) VALUES (1, N'John Doe', N'1', 18, 1, 1, 1, CAST(N'2024-10-06T09:14:13.000' AS DateTime), N'1') INSERT [dbo].[Sys_User] ([UserId], [UserName], [Password], [Age], [Sex], [DepartmentId], [IsOpen], [CreateTime], [CreateUser]) VALUES (2, N'the fourth child in the family', N'1', 19, 1, 1, 1, CAST(N'2024-10-06T09:15:08.000' AS DateTime), N'1') SET IDENTITY_INSERT [dbo].[Sys_User] OFF ALTER TABLE [dbo].[Sys_User] ADD DEFAULT ((0)) FOR [IsOpen] GO EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'user ID' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Sys_User', @level2type=N'COLUMN',@level2name=N'UserName' GO EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'user password' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Sys_User', @level2type=N'COLUMN',@level2name=N'Password' GO EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'user age' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Sys_User', @level2type=N'COLUMN',@level2name=N'Age' GO EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Age of users 1:Male 2:Female' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Sys_User', @level2type=N'COLUMN',@level2name=N'Sex' GO EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Department id (primary key of table Department)' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Sys_User', @level2type=N'COLUMN',@level2name=N'DepartmentId' GO EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Creation time' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Sys_User', @level2type=N'COLUMN',@level2name=N'CreateTime' GO EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'founder' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Sys_User', @level2type=N'COLUMN',@level2name=N'CreateUser' GO EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'staffing table' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Sys_User' GO
Service layer writes interface calls
Note: The following classes, all of which were covered in the previous installment, will not be covered here.
/// <summary> /// User Service Interface /// </summary> public interface ISysUserService { /// <summary> /// Testing Autofac /// </summary> /// <returns></returns> string TestAutofac(); /// <summary> /// Query all users /// </summary> /// <returns></returns> List<SysUser> GetAllUser(); }
/// <summary> /// User Service Interface implementation /// </summary> public class SysUserService : ISysUserService { #region Construct instantiation private readonly ISysUserRepository _sysUserRepository; public SysUserService(ISysUserRepository sysUserRepository) { _sysUserRepository = sysUserRepository; } #endregion /// <summary> /// Testing Autofac /// </summary> /// <returns></returns> public string TestAutofac() { return _sysUserRepository.TestAutofac(); } /// <summary> /// Query all users /// </summary> /// <returns></returns> public List<SysUser> GetAllUser() { return _sysUserRepository.GetAll(BaseSqlRepository.sysUser_selectAllSql); } }
Add the following interface to the controller (SysUserController)
/// <summary> /// Query all users /// </summary> /// <returns></returns> [HttpGet] public List<SysUser> GetAllUser() { return _userService.GetAllUser(); }
Okay, start the project and test it
You can see that the data fetch is successful, to here, we use Dapper to build the underlying storage to connect to the database successfully.
Source code address: /yangguangchenjie/overall-auth2.0-web-api
Preview at http://139.155.137.144:8880/swagger/
Help me Star, please.
If you are interested, please pay attention to my weibo public number (*^▽^*).
Follow me: a full-stack multi-terminal treasure blogger, regularly sharing technical articles, irregularly sharing open source projects. Follow me to bring you to know a different program world!