Of Stack

Detailed explanation of the method of replacing Dapper with SqlSugar ORM

  • 2021-11-29 06:39:34
  • OfStack
Why do you want to write this article Dapper introduces SqlSugar introduces performance comparison migration tutorial

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

Related articles: