A data system means there could be massive and various data and also focus on data. We want to design the system to control the logic for user authentication and data input. It's a common part of all kinds of information systems. I would like to explore any possibility to have some standards for designing it.
You will find it's challenging to design a system from zero during the junior and intermediate stages of your career, no way to start or confused by different kinds of patterns or options. How to choose a programming language? How to choose the database? How to adapt to expansion and different scales? You need hands-on experiences, but without a valuable guide before it will be a nightmare. The code is getting bloated, and the cost of refactoring is getting bigger and bigger. Until the day of giving up, you don’t necessarily understand why this happens.
Is coding language important? Yes but not the key. Everyone will follow his or her strengths, as a senior role, you should understand the language neutral, just like the natural languages around the world, they all play well with the communication role. You could choose any language that exists, or develop language if you want (it's a joke). Frankly speaking, during a real project, you need to make the decision on your or the team's strengths or favorites. In recent years, Microsoft has been running in the right direction. .NET 5.0 will be a milestone. I am sure C# will be a good choice in the coming years. Another tech giant Google has developed the realm of development language through Node.js and Golang which also are good candidates. I have little positive comments for Java but I don't mean refuse to accept it.
Which database solution is better? SQL Server is the best choice under Microsoft stacks. MySQL is also good if you like it. I once used the Oracle, it's powerful but the question is why I need so much. How about MongoDB? Under stable structured data circumstances, not a good idea. Structured Query Language (SQL) is a wide applied standard. Any relational database could be the choice if you like. NoSQL database in specific cases like dealing with storing JSON-like documents is a winner. There is a wide range of developer ecology, which is very important, because no matter whether the product is good or not, it needs people to work on it.
OK, let's start to design the system with C# and SQL Server. There are 3 points need to be considered with any system. First is the configuration, make sure the system is configurable. The second is storage, local hard drive storage is common, cloud storage is also an option. The last is the database and the data layer interface. Maybe more but they are very typical and enough for illustration.
Configuration or options are common to be designed with read-only properties of a class object. A builder pattern is useful to initialize the properties as showed below:
/// <summary>
/// Common configuration for application
/// 程序对象的通用配置
/// </summary>
public abstract class ConfigurationBase : ICoreConfiguration
{
/// <summary>
/// Fluent builder base
/// 液态基础构建器
/// </summary>
public abstract class BuilderBase<T> where T : ConfigurationBase
{
/// <summary>
/// Configuration base
/// 基础配置对象
/// </summary>
private T Configuration;
/// <summary>
/// Constructor
/// 构造函数
/// </summary>
/// <param name="configuration">Configuration</param>
protected BuilderBase(T configuration)
{
Configuration = configuration;
}
/// <summary>
/// Build the configuration
/// 创建配置对象
/// </summary>
/// <returns>Configuration</returns>
public T Build()
{
return Configuration;
}
/// <summary>
/// Whether model is validated
/// 模块是否已经通过验证
/// </summary>
/// <param name="validated">Validated</param>
/// <returns>Builder</returns>
public BuilderBase<T> ModelValidated(bool validated)
{
Configuration.ModelValidated = validated;
return this;
}
/// <summary>
/// Set private deployment
/// 设置私有化部署
/// </summary>
/// <param name="id">Private delopyment id, to avoid same encription result with same private key</param>
/// <returns>Builder</returns>
public BuilderBase<T> PrivateDeployment(string id)
{
Configuration.PrivateDeploymentId = id;
return this;
}
/// <summary>
/// Set service user
/// 设置服务账号
/// </summary>
/// <param name="id">Service user id</param>
/// <returns>Builder</returns>
public BuilderBase<T> ServiceUser(string id)
{
Configuration.ServiceUserId = id;
return this;
}
/// <summary>
/// Set keys
/// 设置键
/// </summary>
/// <param name="privateKey">Private key for encrption</param>
/// <param name="symmetricKey">Symmetric security key, for exchange</param>
/// <returns>Builder</returns>
public BuilderBase<T> SetKeys(string privateKey, string symmetricKey)
{
Configuration.PrivateKey = privateKey;
Configuration.SymmetricKey = symmetricKey;
return this;
}
}
/// <summary>
/// Flag for identification, default is 'e', like stored procedure name will start with it
/// 标识值，默认值为 'e'，比如存储过程会以该字母打头
/// </summary>
public virtual string Flag
{
get { return "e"; }
}
/// <summary>
/// Model DataAnnotations are validated, true under Web API 3 to avoid double validation
/// 模块数据标记已验证，在Web API 3下可以设置为true以避免重复验证
/// </summary>
public bool ModelValidated { get; private set; }
private string privateDeploymentId;
/// <summary>
/// Private delopyment id, to avoid same encription result with same private key
/// 私有部署编号，避免多个平台相同加密私匙导致加密结果一样
/// </summary>
public string PrivateDeploymentId
{
get { return privateDeploymentId; }
private set
{
if (value == null)
value = string.Empty;
privateDeploymentId = value;
}
}
/// <summary>
/// Private key for encrption
/// 加密私匙
/// </summary>
public string PrivateKey { get; private set; }
/// <summary>
/// Service user id
/// 服务用户编号
/// </summary>
public string ServiceUserId { get; private set; }
/// <summary>
/// Symmetric security key, for exchange
/// 对称安全私匙，用于交换
/// </summary>
public string SymmetricKey { get; private set; }
}
Designed with abstract class and support the generic builder, is target to be extended easily:
/// <summary>
/// Main configuration
/// 扩展的配置
/// </summary>
public class MainConfiguration : ConfigurationBase
{
/// <summary>
/// Main configuration builder
/// 扩展的配置器
/// </summary>
public class Builder : BuilderBase<MainConfiguration>
{
/// <summary>
/// Constructor
/// 构造函数
/// </summary>
public Builder() : base(new MainConfiguration())
{
}
}
}
There is always a Application, holds the configuration, database and storage resources, could be initialized in singleton or as a static object. An interface of the application demonstrated below:
/// <summary>
/// Application interface
/// 程序接口
/// </summary>
public interface IApplication
{
/// <summary>
/// Configuration
/// 配置
/// </summary>
ICoreConfiguration Configuration { get; }
/// <summary>
/// Database
/// 数据库
/// </summary>
ICommonDatabase Database { get; }
/// <summary>
/// Storage
/// 存储
/// </summary>
IStorage Storage { get; }
}
For data access layer, Entity Framework (EF) is somehow easier but will lose some performance. I prefer to deal with traditional ADO.NET way.
/// <summary>
/// SQL Server Database
/// SQL Server 数据库
/// </summary>
public class SqlServerDatabase : CommonDatabase
{
/// <summary>
/// New connection
/// 新链接对象
/// </summary>
public SqlConnection NewConnection
{
get { return new SqlConnection(ConnectionString); }
}
/// <summary>
/// Constructor
/// 构造函数
/// </summary>
/// <param name="connectionString">Database connection string</param>
public SqlServerDatabase(string connectionString) : base(connectionString)
{
}
/// <summary>
/// Add parameters to command
/// DBNull.Value for non-empty NULL
/// 给命令添加参数
/// </summary>
/// <param name="command">Command</param>
/// <param name="paras">Parameters</param>
public void AddParameters(SqlCommand command, IDictionary<string, dynamic> paras)
{
if (paras == null)
return;
command.Parameters.AddRange(paras.Where(item => item.Value != null).Select(item =>
{
if (item.Value is SqlParameter p)
return p;
else
return new SqlParameter(item.Key, item.Value);
}).ToArray());
}
/// <summary>
/// Create EF Database Context
/// 创建EF数据库上下文
/// </summary>
/// <typeparam name="M">Model class</typeparam>
/// <returns>Database Context</returns>
public override CommonDbContext<M> CreateContext<M>()
{
return new SqlServerDbContext<M>(ConnectionString);
}
/// <summary>
/// Execute SQL Command, return rows affacted
/// 执行SQL命令，返回影响的行数
/// </summary>
/// <param name="sql">SQL Command</param>
/// <param name="paras">Parameters</param>
/// <param name="isStoredProcedure">Is stored procedure</param>
/// <returns>Rows affacted</returns>
public override int Execute(string sql, IDictionary<string, dynamic> paras, bool? isStoredProcedure = false)
{
using (var connection = NewConnection)
using (var command = new SqlCommand(sql, connection))
{
// Add parameters
AddParameters(command, paras);
// Command type
if (isStoredProcedure == null)
command.Prepare();
else if (isStoredProcedure.Value)
command.CommandType = CommandType.StoredProcedure;
// Open connection
connection.Open();
// Execute
var result = command.ExecuteNonQuery();
// Close
connection.Close();
// Return
return result;
}
}
}
But still provide the EF support with the method CreateContext with SqlServerDbContext below. Please do not make confused with Common* things, I just added multiple databases support, like SQLite or MySQL:
/// <summary>
/// SQL Server EF Database Context
/// SQL Server EF 数据库上下文
/// </summary>
public class SqlServerDbContext<M> : CommonDbContext<M> where M : class
{
private string connectionString;
/// <summary>
/// Constructor
/// 构造函数
/// </summary>
/// <param name="connectionString">Connection string</param>
public SqlServerDbContext(string connectionString)
{
this.connectionString = connectionString;
}
/// <summary>
/// Override OnConfiguring to setup
/// 重写配置初始化
/// </summary>
/// <param name="optionsBuilder">Options builder</param>
protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
optionsBuilder.UseSqlServer(this.connectionString);
}
}
How to deal with actions or the business logic? For example, the user's login and CRUD. I always put them into a user service. A service is a realm of particular requirements, linked with the application and current user. For example, for the login action:
/// <summary>
/// User service
/// 用户服务
/// </summary>
public sealed class UserSerivce : LoginService
{
/// <summary>
/// Create user service
/// 创建用户服务
/// </summary>
/// <param name="app">Application</param>
/// <param name="user">Current user</param>
/// <returns>User service</returns>
public static UserSerivce Create(IMainApp app, ICurrentUser user)
{
var service = new UserSerivce();
service.Application = app;
service.User = user;
return service;
}
/// <summary>
/// Private constructor to prevent initialization
/// 私有的构造函数防止实例化
/// </summary>
private UserSerivce()
{
}
private OperationData GetLoginData(LoginModel model)
{
// Create operation data
var data = CreateOperationData("login");
// Parameterize modeal
model.Parameterize(data, this);
// Return
return data;
}
/// <summary>
/// Async user login
/// 异步用户登录
/// </summary>
/// <param name="model">Data model</param>
/// <returns>Action result</returns>
public async Task<OperationResult> LoginAsync(LoginModel model)
{
// Validate model
var result = ValidateModel(model);
// Invalid result return anyway
if (!result.OK)
return result;
// Access database to valid
return await ExecuteAsync(GetLoginData(model), false);
}
}
Use private constructor to hide the initialization directly and provide a static method to connect the application and user when creation. Model holds the data and validation rules, method Parameterize just collect the data into database parameters then submit to database side stored procedure to process, for the login model:
/// <summary>
/// Login model
/// https://docs.microsoft.com/en-us/dotnet/api/system.componentmodel.dataannotations?view=netframework-4.8
/// 登录模型
/// </summary>
public class LoginModel : LoginService.DataModel
{
[StringLength(8, MinimumLength = 4)]
/// <summary>
/// Veryfication code
/// 验证码
/// </summary>
public string Code { get; set; }
/// <summary>
/// Id, like number id, mobile, or email
/// 编号，像数字编号，手机号码或邮箱
/// </summary>
[Required]
[StringLength(256, MinimumLength = 4)]
public string Id { get; set; }
/// <summary>
/// Id type
/// 编号类型
/// </summary>
public LoginIdType? IdType { get; set; }
/// <summary>
/// Language cid, like Simplified Chinese zh-CN
/// 语言编号，如简体中文 zh-CN
/// </summary>
[RegularExpression(@"^[a-z]{2}(-[A-Z]{2})?$")]
public string LanguageCid { get; set; }
/// <summary>
/// Current organization id
/// 当前限定的登录机构
/// </summary>
public int? Org { get; set; }
/// <summary>
/// Raw password
/// 原始密码
/// </summary>
[Required]
[StringLength(30, MinimumLength = 4)]
public string Password { get; set; }
/// <summary>
/// Is save login
/// 是否保存登录
/// </summary>
public bool? Save { get; set; }
private void CalculateIdType()
{
if (IdType == null || IdType == LoginIdType.Unknown)
{
if (Id.Contains("@"))
{
IdType = LoginIdType.Email;
}
else
{
if (Regex.IsMatch(Id, @"^\d+$"))
{
// Starting with zero or its length more than 10 presents mobile phone number
// 以0开头，或者长度大于10位，已经不方便通过数字编号记忆，识别为移动手机号码
if (Id.StartsWith("0") || Id.Length >= 10)
{
IdType = LoginIdType.Mobile;
}
else
{
IdType = LoginIdType.Id;
}
}
else
{
IdType = LoginIdType.Cid;
}
}
}
}
/// <summary>
/// Override to collect parameters
/// 重写收集参数
/// </summary>
/// <param name="data">Data</param>
/// <param name="service">Service</param>
public override void Parameterize(OperationData data, IService<int> service)
{
base.Parameterize(data, service);
// Calculate id type
CalculateIdType();
// Add parameters
var paras = data.Parameters;
// Verification code
paras.Add("has_code", true);
paras.Add("id", Id);
paras.Add("id_type", (byte)IdType);
paras.Add("ip", service.User.ClientIp.ToString());
paras.Add("language_cid", LanguageCid ?? service.User.LanguageCid);
// Login method, 1 = Default
paras.Add("method", 1);
paras.Add("org", Org);
// Hash password
paras.Add("password", service.Application.HashPassword(Password));
paras.Add("save_login", Save.GetValueOrDefault());
}
}
In the database side, there is a stored procedure to deal with parameters and execute to provide a result:
CREATE PROCEDURE [dbo].[ep_user_login]
-- ================================================
--
-- User login，Error：90006
--
-- ================================================
@id_type tinyint, -- Id type
@id varchar(256), -- User number id, email or mobile
@password varchar(256), -- Password encrypted
@has_code bit, -- Is recaptu
@save_login bit, -- Save login
@language_id smallint = NULL, -- Language id
@language_cid varchar(5) = NULL, -- Language cid
@org int = NULL, -- Organization limited
@origin varchar(256) = NULL, -- CORS origin
@service_user_id int = NULL, -- Service user id running
@ip varchar(45), -- IP
@method tinyint -- Login method
AS
BEGIN
SET NOCOUNT ON;
-- Error code
DECLARE @error_code int = 90006;
-- Default language id
DECLARE @default_language_id smallint = dbo.ef_get_login_language_id(@language_id, @language_cid, 0);
-- If there is no user, return initialization tip
IF NOT EXISTS(SELECT * FROM e_system_setting WHERE initialized = 1)
BEGIN
SELECT @error_code AS [error_code], 'initialization' AS m_id, dbo.ef_get_translation('initialization', @default_language_id) AS [message];
RETURN;
END
....
END
GO
In order to save energies with the core system and Web API integration. I would like to share the model. The API side code:
/// <summary>
/// Login for authentication
/// 登录授权
/// </summary>
/// <param name="model">Data model</param>
/// <returns>Result</returns>
[AllowAnonymous]
[HttpPost("Login")]
public async Task Login(LoginModel model)
{
// Act
var result = await Service.LoginAsync(model);
if (result.OK)
{
// Logined user id
var userId = result.Data.Get("token_user_id", 0);
// User role
var role = result.Data.Get("role", UserRole.User);
// Hold the token value and then return to client
result.Data["authorization"] = CreateToken(userId, role);
// Suggested refresh seconds
result.Data["refresh_seconds"] = 300;
}
// Output
await ResultContentAsync(result);
}
In order to speed up all its best, any 'new' generic constraint related to Activator.CreateInstance avoided. Put logic heavily on the database side with the power of stored procedures will benefit data manipulation. Code snippets like hills stop you from viewing the panorama.
Two issues I met listed here:
- Web deploy publishes wrong version of assembly, always lower than debug version (https://stackoverflow.com/questions/32657241/web-deploy-publishes-wrong-version-of-assembly/33223619#comment110408941_33223619). With VS 2019, .Net Core 3.1, delete the folder 'obj\Release\netcoreapp3.1\win-x64\R2R'.
- SQL Server json truncated (https://stackoverflow.com/questions/51087037/sql-server-json-truncated-even-when-using-nvarcharmax)
I draw a diagram here for your reference:
I hope it will provide you some valuable ideas when designing a system from zero.
Top comments (0)