# Introduced
SmartSql = MyBatis + Cache(Memory | Redis) + R/W Splitting +Dynamic Repository + Diagnostics ......
Simple, efficient, high-performance, scalable, monitoring, progressive development!
# How does she work?
SmartSql draws on MyBatis's ideas, uses XML to manage SQL, and provides several filter tags to eliminate various if/else judgment branches at the code level. SmartSql will manage your SQL and filter the tags to maintain your various conditional judgments at the code level to make your code more beautiful.
# Why Choose SmartSql?
The Orm,linq of the DotNet system, which is mostly Linq, is very good, eliminating the developer's reliance on SQL. But it ignores the fact that SQL itself is not complex, and that it is difficult for developers to write Linq to generate good performance SQL in complex query scenarios, and I believe that students who have used EF must have this experience: "I think about how SQL writes, and then I write Linq, It's over. You may also want to see what SQL output of Linq is like. " It was a very bad experience. To be absolutely optimized for SQL, developers must have absolute control over SQL. In addition, SQL itself is very simple, why add a layer of translators?
SmartSql has been out of formal open source for more than two years, in the production environment after several micro-service verification. There are also some businesses that are using SmartSql (if you are also using SmartSql Welcome to submit issue)Who is using SmartSql (opens new window). Has now joined NCC (opens new window)。 The future (Roadmap-2019 (opens new window)) SmartSql will continue to add new features to help developers improve efficiency. Welcome to submit Issue https://github.com/dotnetcore/SmartSql/issues (opens new window).
# So why not Dapper, or DbHelper?
Dapper is really good and good performance, but the code that will be ceded to you is filled with SQL and various judgment branches that will make code maintenance difficult to read and maintain. In addition, Dapper only provides DataReader to Entity anti-serialization function. And SmartSql offers a number of features to improve developer efficiency.
# Feature Overview
# Dynamic Repository
Dynamic Agent Repository (SmartSql.DyRepository) components are SmartSql very unique features that simplify the use of SmartSql. There is little intrusion into the business code. It can be said that using ISqlMapper is the original method, and DyRepository automatically helps you implement these methods.
DyRepository only need to define the Repository interface, through a simple configuration can automatically implement these interfaces and register in the IoC container, when used injection instant acquisition implementation. The principle is to obtain the Scope and SqlId in the XML file of SmartSql through the naming rules of the interface and interface method, use the parameters of the interface method as the Request, and automatically judge the query or perform the operation through the SQL in the XML, and finally realize the ISqlMapper Call.
# 0. Define Repository interfaces
public interface IUserRepository : IRepository<User, long>
{
}
# 1. Injection dependencies
services.AddSmartSql()
.AddRepositoryFromAssembly(options => { options.AssemblyString = "SmartSql.Starter.Repository"; });
# 2. Use
public class UserService
{
IUserRepository userRepository;
public UserService(IUserRepository userRepository)
{
this.userRepository = userRepository;
}
}
# SmartSql Best practices -> SmartCode (opens new window)
By SmartCode (opens new window) Developers simply configure the database connection to build everything that is required for the solution, including, but not limited to:
- Solution Engineering
- Give you a restore.
ReStore:
Type: Process
Parameters:
FileName: powershell
WorkingDirectory: '{{Project.Output.Path}}'
Args: dotnet restore
- Docker
- Building Docker Mirroring & Running Instances
BuildDocker:
Type: Process
Parameters:
FileName: powershell
WorkingDirectory: '{{Project.Output.Path}}'
Args: docker build -t {{Project.Parameters.DockerImage}}:v1.0.0 .
RunDocker:
Type: Process
Parameters:
FileName: powershell
WorkingDirectory: '{{Project.Output.Path}}'
Args: docker run --name {{Project.Parameters.DockerImage}} --rm -d -p 8008:80 {{Project.Parameters.DockerImage}}:v1.0.0 .
- Open a browser by the way
RunChrome:
Type: Process
Parameters:
FileName: C:\Program Files (x86)\Google\Chrome\Application\chrome.exe
CreateNoWindow: false
Args: http://localhost:8008/swagger
# Docker
# Directory structure generated by SmartCode
# Read and write separation
SmartSql Read and write separation is especially easy, just provide a good configuration can be:
<Database>
<DbProvider Name="PostgreSql"/>
<Write Name="WriteDB" ConnectionString="${Master}"/>
<Read Name="ReadDb-1" ConnectionString="${Slave-0}" Weight="100"/>
<Read Name="ReadDb-2" ConnectionString="${Slave-1}" Weight="100"/>
</Database>
# Cache
- Lru Least recently used algorithms
- Fifo Advanced first-out algorithm
- RedisCacheProvider
- Other inherited self-ICacheProvider cache types are available
<Caches>
<Cache Id="LruCache" Type="Lru">
<Property Name="CacheSize" Value="10"/>
<FlushOnExecute Statement="AllPrimitive.Insert"/>
<FlushInterval Hours="1" Minutes="0" Seconds="0"/>
</Cache>
<Cache Id="FifoCache" Type="Fifo">
<Property Name="CacheSize" Value="10"/>
</Cache>
<Cache Id="RedisCache" Type="${RedisCacheProvider}">
<Property Name="ConnectionString" Value="${Redis}" />
<FlushInterval Seconds="60"/>
</Cache>
</Caches>
<Statement Id="QueryByLruCache" Cache="LruCache">
SELECT Top 6 T.* From T_User T;
</Statement>
# Type Handler
The SmartSql is implemented internally DotNet the main types of type handlers, and some types of compatible type conversion processors are provided, as well as more commonly used JsonTypeHanlder.
<TypeHandler PropertyType="SmartSql.Test.Entities.UserInfo,SmartSql.Test" Type="${JsonTypeHandler`}">
<Properties>
<Property Name="DateFormat" Value="yyyy-MM-dd mm:ss"/>
<Property Name="NamingStrategy" Value="Camel"/>
</Properties>
</TypeHandler>
# CUD Code generation
SmartSql also provides CUD extension functions to help developers generate good CUD-SQL for direct developer use without having to write any configuration.
public static TEntity GetById<TEntity, TPrimaryKey>(this ISqlMapper);
public static TPrimaryKey Insert<TEntity, TPrimaryKey>(this ISqlMapper sqlMapper, TEntity entity);
public static int DyUpdate<TEntity>(this ISqlMapper sqlMapper, object entity);
public static int Update<TEntity>(this ISqlMapper sqlMapper, TEntity entity);
public static int DeleteById<TEntity, TPrimaryKey>(this ISqlMapper sqlMapper, TPrimaryKey id);
public static int DeleteMany<TEntity, TPrimaryKey>(this ISqlMapper sqlMapper, IEnumerable<TPrimaryKey> ids);
# Id Generator
# SnowflakeId
<IdGenerators>
<IdGenerator Name="SnowflakeId" Type="SnowflakeId">
<Properties>
<Property Name="WorkerIdBits" Value="10"/>
<Property Name="WorkerId" Value="888"/>
<Property Name="Sequence" Value="1"/>
</Properties>
</IdGenerator>
</IdGenerators>
<Statement Id="Insert">
<IdGenerator Name="SnowflakeId" Id="Id"/>
INSERT INTO T_UseIdGenEntity
(
Id,
Name
)
VALUES
(
@Id,
@Name
);
Select @Id;
</Statement>
var id = SqlMapper.ExecuteScalar<long>(new RequestContext
{
Scope = nameof(UseIdGenEntity),
SqlId = "Insert",
Request = new UseIdGenEntity()
{
Name = "SmartSql"
}
});
# DbSequence
<IdGenerators>
<IdGenerator Name="DbSequence" Type="DbSequence">
<Properties>
<Property Name="Step" Value="10"/>
<Property Name="SequenceSql" Value="Select Next Value For IdSequence;"/>
</Properties>
</IdGenerator>
</IdGenerators>
<Statement Id="InsertByDbSequence">
<IdGenerator Name="DbSequence" Id="Id"/>
INSERT INTO T_UseIdGenEntity
(
Id,
Name
)
VALUES
(
@Id,
@Name
);
Select @Id;
</Statement>
var id = SqlMapper.ExecuteScalar<long>(new RequestContext
{
Scope = nameof(UseIdGenEntity),
SqlId = "InsertByDbSequence",
Request = new UseIdGenEntity()
{
Name = "SmartSql"
}
});
# AOP Transaction
[Transaction]
public virtual long AddWithTran(User user)
{
return _userRepository.Insert(user);
}
# Transaction nesting
When a transaction is nested, the transaction attribute annotation of the child function is no longer turned on, and the transaction that calls the function by the parent is used instead
[Transaction]
public virtual long AddWithTranWrap(User user)
{
return AddWithTran(user);
}
# BulkInsert
using (var dbSession= SqlMapper.SessionStore.Open())
{
var data = SqlMapper.GetDataTable(new RequestContext
{
Scope = nameof(AllPrimitive),
SqlId = "Query",
Request = new { Taken = 100 }
});
data.TableName = "T_AllPrimitive";
IBulkInsert bulkInsert = new BulkInsert(dbSession);
bulkInsert.Table = data;
bulkInsert.Insert();
}
# Skywalking Monitoring
SmartSql currently supports Skywalking monitoring and is enabled by installing the SkyAPM-dotnet (opens new window) agent. The following is a partial screenshot.
# Monitoring execution commands
# View whether the cache is cached and the number of records returned
# View executed SQL statements
# Transaction
# Error
# Exception stack Trace
# Nuget Packages
Guide →