C ා data operation series - 15 SqlSugar addition, deletion, modification, query details

0. Preface

We have a general understanding of SqlSugar in the last and last articles, but this is not perfect, because those are theoretical knowledge, unable to describe the actual situation in our engineering development. In this article, I will lead my friends to try to write a template class that can be used in the project.

1. Create a Client

SqlSugar needs a Client to manage the database connection and operate the database. So we write a DbContext to create a Client:

public class DefaultContext
    public SqlSugarClient Client { get; }

    public DefaultContext(string connectionString, DbType dbType)
        Client = new SqlSugarClient(new ConnectionConfig
            ConnectionString = connectionString,//"Data Source=./demo.db",
            DbType = dbType,
            IsAutoCloseConnection = true,
            InitKeyType = InitKeyType.Attribute
        Client.CodeFirst.InitTables(typeof(Dept), typeof(Person), typeof(Employee));
        Client.Aop.OnLogExecuting = (sql, paramters) =>

    public SimpleClient<T> CreateClient<T>() where T : class, new()
        return Client.GetSimpleClient<T>();

SqlSugar provides a SimpleClient, which has many methods that can be used directly, and this is a generic class. That is to say, we can use it to operate on a single entity class, which is very important in development.

2. Insert data

For a program, data is as important as blood. For the ORM framework, insertion is the foundation of all sources. So let's first look at how SqlSugar is inserted:

2.1 simple insertion mode

public bool Insert(T insertObj);
public bool InsertRange(T[] insertObjs);
public bool InsertRange(List<T> insertObjs);

This is the two default insertion methods provided by SqlSugar in SimpleClient. One is to insert a single entity object, and the other is to insert a group of objects.

By default, SqlSugar insertion does not return the primary key to the data. If the primary key of the current data is required for subsequent operations, another method can be called:

public int InsertReturnIdentity(T insertObj);

With this method, you can get a default int type primary key value.

2.2 advanced play

SqlSugar also has an insertion mode, which returns an IInsertable generic interface through AsInsertable:

public IInsertable<T> AsInsertable(T insertObj);
public IInsertable<T> AsInsertable(T[] insertObjs);
public IInsertable<T> AsInsertable(List<T> insertObjs);

This mode is different from the ordinary insertion mode of SimpleClient. It does not directly execute the insertion action, but needs to call and execute the insertion action manually:

int ExecuteCommand();

Perform the action and return the number of rows affected.

bool ExecuteCommandIdentityIntoEntity();

Execute the action, and then insert the primary key into the entity object to return the insertion result. After execution, the primary key data is saved to the entity example.

long ExecuteReturnBigIdentity();
int ExecuteReturnIdentity();

Perform the action and return the primary key value without updating the entity.

One thing deserves special attention:

All the inserts that will return the primary key are only for a single data. If multiple data are inserted at one time, the primary key information will not be returned and cannot be updated into the entity.

All of the above are full column inserts. SqlSugar also provides two modes: insert only partial columns and ignore some columns:

IInsertable<T> InsertColumns(Expression<Func<T, object>> columns);// If the condition is satisfied, other columns will not be inserted
IInsertable<T> InsertColumns(params string[] columns);//Insert specified column name
IInsertable<T> IgnoreColumns(Expression<Func<T, object>> columns);// Ignore columns that meet conditions, insert other columns
IInsertable<T> IgnoreColumns(params string[] columns);// Ignore these columns
IInsertable<T> IgnoreColumns(bool ignoreNullColumn, bool isOffIdentity = false);//Specifies whether Null columns are ignored and whether primary keys are forced to be inserted

3. Update or insert

After introducing the insert, let's introduce the update. Just so-called, no update data is a pool of stagnant water, with the update data to change. So let's take a look at how to update data gracefully:

3.1 simple mode

Let's start with two of the simplest:

public bool Update(T updateObj);
public bool UpdateRange(T[] updateObjs);
public bool UpdateRange(List<T> updateObjs);

It is important to note that this update mode only needs to ensure that the primary key has a value and corresponds to it.

public bool Update(Expression<Func<T, T>> columns, Expression<Func<T, bool>> whereExpression);

This is another conditional update, which will update all elements that meet the whereExpression. Update example:

personClient.Update(p=>new Person
    Age = 1
}, p=>p.Id == 1);

Columns needs to return a property column of the object to be updated, that is, set the content to be updated in columns.

3.2 advanced mode

Similarly, enable advanced mode through asupdatable:

public IUpdateable<T> AsUpdateable(T[] updateObjs);
public IUpdateable<T> AsUpdateable(T updateObj);
public IUpdateable<T> AsUpdateable(List<T> updateObjs);

You can then do more of these today:

int ExecuteCommand();

Returns the number of lines affected by command execution

bool ExecuteCommandHasChange();

Returns whether there is a change, that is, whether the number of rows is greater than 0.

  • Update only some columns:
IUpdateable<T> SetColumns(Expression<Func<T, bool>> columns);

Update example:

personClient.AsUpdateable(d).SetColumns(t=>t.Age ==2).ExecuteCommand();

Pass in a lambda expression to make the data satisfy the lambda expression. It is required that lambda expressions can only use = = to determine whether a column is equal to a value.

IUpdateable<T> SetColumns(Expression<Func<T, T>> columns);
IUpdateable<T> UpdateColumns(params string[] columns);
IUpdateable<T> UpdateColumns(Expression<Func<T, object>> columns);

Pass in the actual column name to update. The object is used to connect an anonymous object, and the property name is the value to be updated.

  • Do not update some columns
IUpdateable<T> IgnoreColumns(params string[] columns);// Ignore incoming column names
IUpdateable<T> IgnoreColumns(Expression<Func<T, object>> columns);// Use anonymous objects to represent the column names to be ignored
IUpdateable<T> IgnoreColumns(bool ignoreAllNullColumns, bool isOffIdentity = false, bool ignoreAllDefaultValue = false);// Set whether Null columns are ignored, primary key is forced to be updated, and all default value columns are ignored
  • Condition update
IUpdateable<T> Where(Expression<Func<T, bool>> expression);
IUpdateable<T> Where(string fieldName, string conditionalType, object fieldValue);
IUpdateable<T> Where(string whereSql, object parameters = null);
IUpdateable<T> WhereColumns(Expression<Func<T, object>> columns);
IUpdateable<T> WhereColumns(string columnName);
IUpdateable<T> WhereColumns(string[] columnNames);

Come on, just guess what these mean?

It can be said that there are several simple and clear condition setting modes. lambda represents filtering and updating data, and field value judges condition updating.

Where the value of conditionType, the value of ConditionalType enumeration is recommended.

3.3 update or insert

In the actual development, we may encounter a way to insert or update, so we need to find a way to update or insert directly. SqlSugar provides a solution for this:

ISaveable<T> Saveable<T>(T saveObject) where T : class, new();
ISaveable<T> Saveable<T>(List<T> saveObjects) where T : class, new();

But this method is in SugarClient. We can use:

public ISqlSugarClient AsSugarClient();

Get the SugarClient object associated with it in SimpleClient.

The criteria for updating or inserting is whether the primary key has a value. If the primary key has a value and the record exists in the database, the update is performed, otherwise the insert is performed.

4. Delete

Deleting is a very important function point in the actual development process, so how to delete data quickly and effectively is also a very important thing. So, let's see how to delete:

public bool Delete(Expression<Func<T, bool>> whereExpression);
public bool Delete(T deleteObj);
public bool DeleteById([Dynamic] dynamic id);
public bool DeleteByIds([Dynamic(new[] { false, true })] dynamic[] ids);

There is nothing else to be noticed in deleting. The first is deleting conditions. All that meet the conditions should be deleted. The second delete a single object, and the latter two delete objects according to the primary key.

Quietly make complaints about the primary key, and object will be better because the dynamic object will increase the process of boxing and unboxing.

Of course, there is also an asdeletable method for deletion. In particular, the IDeleteable interface provides methods to delete according to sql statements. There is nothing else to pay attention to.

5. Query

A good ORM framework, at least five of the power in the query, how faster and more accurate query has become the requirements of the current development of the ORM framework. At the same time, simple and easy to use is what programmers expect from orm.

Let's take a look at SqlSugar's query power:

public bool IsAny(Expression<Func<T, bool>> whereExpression);// Query whether there is qualified data
public int Count(Expression<Func<T, bool>> whereExpression);// Get the number of conditions
public T GetById([Dynamic] dynamic id);//Get an instance based on the primary key
public bool IsAny(Expression<Func<T, bool>> whereExpression);//Returns an object that meets the criteria
public List<T> GetList();// Return all data as a List
public List<T> GetList(Expression<Func<T, bool>> whereExpression);//Return all qualified data

Paging data:

public List<T> GetPageList(Expression<Func<T, bool>> whereExpression, PageModel page);
public List<T> GetPageList(Expression<Func<T, bool>> whereExpression, PageModel page, Expression<Func<T, object>> orderByExpression = null, OrderByType orderByType = OrderByType.Asc);
public List<T> GetPageList(List<IConditionalModel> conditionalList, PageModel page);
public List<T> GetPageList(List<IConditionalModel> conditionalList, PageModel page, Expression<Func<T, object>> orderByExpression = null, OrderByType orderByType = OrderByType.Asc);

IConditionModel is an empty interface to define the specification query specification. In fact, it uses classes:

public class ConditionalModel: IConditionalModel
    public ConditionalModel()
        this.ConditionalType = ConditionalType.Equal;
    public string FieldName { get; set; }
    public string FieldValue { get; set; }
    public ConditionalType ConditionalType { get; set; }
    public Func<string,object> FieldValueConvertFunc { get; set; }

Then, let's take a look at ConditionType, which defines various judgment criteria:

public enum ConditionalType
    GreaterThan =2,
    GreaterThanOrEqual = 3,
    LessThanOrEqual = 5,
    NoLike = 13,

Let's take a look at the effect of using IConditionModel for paging

var list = personClient.GetPageList(new List<IConditionalModel>
    new ConditionalModel
        FieldName = "Age",
        FieldValue = "3",
        ConditionalType = ConditionalType.LessThan
}, pageModel);

Generate the following SQL statement:

SELECT COUNT(1) FROM (SELECT `Id`,`Name`,`Age` FROM `Person`  WHERE   Age < @ConditionalAge0  ) CountTable 
SELECT `Id`,`Name`,`Age` FROM `Person`   WHERE   Age < @ConditionalAge0      LIMIT 0,2

It can be seen that there is no difference between the two, just different query habits.

6. Summary

According to the previous habit, it should be over by now. But SqlSugar still has some important points not introduced, so I will add a notice for the next issue

The next chapter will analyze some more advanced content of SqlSugar, advanced mode, transaction and batch operation of query

Well, to summarize this article, we see the highlights of SqlSugar in addition, deletion, modification and query, which can be said to be more suitable for the actual business requirements. Well, give me a quiet compliment.

There are three more pieces of content "C data operation series" is about to be completed. From the next series, we will step into the most important technology stack in our work: Asp.net Core. This can be written in the resume. Well, that's right. The next series of plans will be introduced in the form of actual combat asp.net Knowledge points and settings of core.

Please pay attention to more My blog Mr. Gao's Cabin

Tags: C# SQL Lambda Database Attribute

Posted on Sun, 24 May 2020 09:49:26 -0400 by CorfuVBProgrammer