Detailed explanation of the method of replacing Dapper with SqlSugar ORM
- 2021-11-29 06:39:34
- OfStack
Why did you write this article
Factor I see that many people use SqlSugar, but they also use Dapper, because SqlSugar is compatible with all API of Dapper, so since you use SqlSugar, there is no need to use two ORM in the same project
So this article is for friends who use SqlSugar or you want to use SqlSugar
Introduction to Dapper
Dapper is a lightweight open source ORM class, he is through the extension of IDbConnection to provide a useful extension method to query your database, so Ado. Net support the database, he can support.
It has the title of "King of Micro ORM" in terms of speed, which is almost as fast as using the original ADO. NET data reader 1. Many people like him deeply after using him for the first time.
Introduction to SqlSugar
SqlSugar is also lightweight and compact, and its functions are more comprehensive. The requirements are all real project requirements from more than 1,000 developers. After more than 5 years of accumulation,
It can perfectly meet all the requirements of enterprise level, especially query function, and supports various types of returns such as DataTable, List, dictionary, etc., and also supports various queries.
For example, querying multiple result sets at once, of course, also supports stored procedures
Performance comparison
On the basic function query, the performance of both parties is quite close, and a large number of tests have been carried out on different CPU computers, and the performance of SqlSugar1 is not inferior or even better.
In the SqlSugar source code has contrast code, here I will not introduce, performance is always self-comparison is true, others than are false
Porting tutorials
1. Sql plus parameter query
Dapper uses Sql plus parameter query
/// <summary>
/// Query list
/// </summary>
/// <param name="sql"> Queried sql</param>
/// <param name="param"> Replacement parameter </param>
/// <returns></returns>
public static List<T> Query(string sql, object param)
{
using (SqlConnection con = new SqlConnection(connectionString))
{
return con.Query<T>(sql, param).ToList();
}
}
SqlSugar queries using Sql plus parameters
/// <summary>
/// Query list
/// </summary>
/// <param name="sql"> Queried sql</param>
/// <param name="param"> Replacement parameter </param>
/// <returns></returns>
public static List<T> Query(string sql, object param)
{
using (SqlSugarClient con =new SqlSugar.SqlSugarClient(connectionConfig))
{
return con.Ado.SqlQuery<T>(sql, param);
}
}
Among them, Object param supports three types
1. new {id=1} Anonymous Object
2. SugarParameter
3. SugarParameter [] or List < SugarParameter >
2. Query a single article according to Sql
Dapper queries a single article according to Sql
/// <summary>
/// Enquiry number 1 Data
/// </summary>
/// <param name="sql"></param>
/// <param name="param"></param>
/// <returns></returns>
public static T QueryFirst(string sql, object param)
{
using (SqlConnection con = new SqlConnection(connectionString))
{
return con.QueryFirst<T>(sql, param);
}
}
SqlSugar queries a single article according to Sql
/// <summary>
/// Query list
/// </summary>
/// <param name="sql"> Queried sql</param>
/// <param name="param"> Replacement parameter </param>
/// <returns></returns>
public static List<T> Query(string sql, object param)
{
using (SqlSugarClient con =new SqlSugar.SqlSugarClient(connectionConfig))
{
return con.Ado.SqlQuerySingle<T>(sql, param);
}
}
3. Add, delete and change
Dapper additions, deletions and modifications
/// <summary>
/// Addition, deletion and modification
/// </summary>
/// <param name="sql"></param>
/// <param name="param"></param>
/// <returns></returns>
public static int Execute(string sql, object param)
{
using (SqlConnection con = new SqlConnection(connectionString))
{
return con.Execute(sql, param);
}
}
SqlSugar addition, deletion and modification
/// <summary>
/// Add, delete and change
/// </summary>
/// <param name="sql"> Queried sql</param>
/// <param name="param"> Replacement parameter </param>
/// <returns></returns>
public static int Query(string sql, object param)
{
using (SqlSugarClient con =new SqlSugar.SqlSugarClient(connectionConfig))
{
return con.Ado.ExecuteCommand(sql, param);
}
}
4. DataReader reads data
Dapper DataReader Read Data
/// <summary>
/// Reader Get data
/// </summary>
/// <param name="sql"></param>
/// <param name="param"></param>
/// <returns></returns>
public static IDataReader ExecuteReader(string sql, object param)
{
using (SqlConnection con = new SqlConnection(connectionString))
{
return con.ExecuteReader(sql, param);
}
}
SqlSugarDataReader Read Data
/// <summary>
/// Get DataReader
/// </summary>
/// <param name="sql"> Queried sql</param>
/// <param name="param"> Replacement parameter </param>
/// <returns></returns>
public static IDataReader Query(string sql, object param)
{
using (SqlSugarClient con =new SqlSugar.SqlSugarClient(connectionConfig))
{
return con.Ado.GetDataReader(sql, param);
}
}
5. Get the first row and first column
Dapper Get first row and first column
/// <summary>
/// Scalar Get data
/// </summary>
/// <param name="sql"></param>
/// <param name="param"></param>
/// <returns></returns>
public static object ExecuteScalar(string sql, object param)
{
using (SqlConnection con = new SqlConnection(connectionString))
{
return con.ExecuteScalar(sql, param);
}
}
SqlSugar Get first row and first column
/// <summary>
/// Get the first row and first column
/// </summary>
/// <param name="sql"> Queried sql</param>
/// <param name="param"> Replacement parameter </param>
/// <returns></returns>
public static object ExecuteScalar(string sql, object param)
{
using (SqlSugarClient con =new SqlSugar.SqlSugarClient(connectionConfig))
{
return con.Ado.GetScalar(sql, param);
}
}
6. Get stored procedures
Dapper Get Stored Procedures
/// <summary>
/// Query list
/// </summary>
/// <param name="sql"> Queried sql</param>
/// <param name="param"> Replacement parameter </param>
/// <returns></returns>
public static List<T> Query(string sql, object param)
{
using (SqlSugarClient con =new SqlSugar.SqlSugarClient(connectionConfig))
{
return con.Ado.SqlQuery<T>(sql, param);
}
}
0
SqlSugar Get Stored Procedure
/// <summary>
/// Query list
/// </summary>
/// <param name="sql"> Queried sql</param>
/// <param name="param"> Replacement parameter </param>
/// <returns></returns>
public static List<T> Query(string sql, object param)
{
using (SqlSugarClient con =new SqlSugar.SqlSugarClient(connectionConfig))
{
return con.Ado.SqlQuery<T>(sql, param);
}
}
1
7. Transaction executes multiple Sql
Dapper transactions perform multiple Sql
/// <summary>
/// Query list
/// </summary>
/// <param name="sql"> Queried sql</param>
/// <param name="param"> Replacement parameter </param>
/// <returns></returns>
public static List<T> Query(string sql, object param)
{
using (SqlSugarClient con =new SqlSugar.SqlSugarClient(connectionConfig))
{
return con.Ado.SqlQuery<T>(sql, param);
}
}
2
An Sugar transaction executes multiple Sql
/// <summary>
/// Affairs 1 - All SQL
/// </summary>
/// <param name="sqlarr"> Multiple SQL</param>
/// <param name="param">param</param>
/// <returns></returns>
public static int ExecuteTransaction(string[] sqlarr)
{
using (SqlSugarClient con = new SqlSugarClient(connectionConfig))
{
con.Ado.BeginTran();
using (var transaction = con.Ado.Transaction)
{
try
{
int result = 0;
foreach (var sql in sqlarr)
{
result += con.Ado.ExecuteCommand(sql);
}
transaction.Commit();
return result;
}
catch (Exception ex)
{
transaction.Rollback();
return 0;
}
}
}
}
Detailed Explanation of SqlSugar Parameters
SqlSugar also supports cursors, Output, ReturnValue
/// <summary>
/// Query list
/// </summary>
/// <param name="sql"> Queried sql</param>
/// <param name="param"> Replacement parameter </param>
/// <returns></returns>
public static List<T> Query(string sql, object param)
{
using (SqlSugarClient con =new SqlSugar.SqlSugarClient(connectionConfig))
{
return con.Ado.SqlQuery<T>(sql, param);
}
}
4
What are the advantages of porting to SqlSugar ORM
Also writes Sql but SqlSugar provides AOP, performance monitoring, returning multiple result sets, SQL paging, 1-to-many, 1-to-1 operations, JSON types, and so on
1. SqlSugar supports AOP and other event operations
/// <summary>
/// Query list
/// </summary>
/// <param name="sql"> Queried sql</param>
/// <param name="param"> Replacement parameter </param>
/// <returns></returns>
public static List<T> Query(string sql, object param)
{
using (SqlSugarClient con =new SqlSugar.SqlSugarClient(connectionConfig))
{
return con.Ado.SqlQuery<T>(sql, param);
}
}
5
In addition to these events, SqlSugar also supports performance monitoring, which can track poor Sql or stored procedures well
/// <summary>
/// Query list
/// </summary>
/// <param name="sql"> Queried sql</param>
/// <param name="param"> Replacement parameter </param>
/// <returns></returns>
public static List<T> Query(string sql, object param)
{
using (SqlSugarClient con =new SqlSugar.SqlSugarClient(connectionConfig))
{
return con.Ado.SqlQuery<T>(sql, param);
}
}
6
Because there are more contents, I can't finish writing, and I don't pay attention to the point of 1 wave of attention
2. Query of Json type
/// <summary>
/// Query list
/// </summary>
/// <param name="sql"> Queried sql</param>
/// <param name="param"> Replacement parameter </param>
/// <returns></returns>
public static List<T> Query(string sql, object param)
{
using (SqlSugarClient con =new SqlSugar.SqlSugarClient(connectionConfig))
{
return con.Ado.SqlQuery<T>(sql, param);
}
}
7
3. Sql paging
var list= db.SqlQueryable<Student>("select * from student").Where("id=@id",new { id=1}).ToPageList(1, 2);
Source download: https://github.com/sunkaixuan/SqlSugar