C ා data operation series – 15 sqlsugar

Time:2020-8-1

Preface

Following the previous article and the last one, we have a general understanding of sqlsugar, but this is not perfect, because those are theoretical knowledge and cannot describe the actual situation in our engineering development. In this article, I will lead my friends to write a template class that can be used in engineering.

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) =>
        {
            Console.WriteLine(sql);
        };
    }

    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 ORM frameworks, insertion is the foundation of all sources. So let’s take a look at the insertion of sqlsugar

2.1 simple insertion mode

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

These are 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 insert 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 playing methods

Sqlsugar also has an insert 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 normal insert mode of simpleclient. It does not directly execute the insert action. It needs to call and execute the insert action manually

int ExecuteCommand();

Execute the action, and then 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 sample.

long ExecuteReturnBigIdentity();
int ExecuteReturnIdentity();

Performs the action and returns the primary key value without updating the entity.

There is one thing that deserves special attention:

All 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 the primary key information cannot be updated into the entity

All of the above are full column inserts. Sqlsugar also provides two modes of inserting only partial columns and ignoring some columns

Iinsertable < T > insertcolumns (expression < func < T, Object > > columns); // inserts that meet the conditions are not inserted into other columns
Iinsertable < T > insertcolumns (params string [] columns); // inserts the specified column name
Iinsertable < T > ignorecolumns (expression < func < T, Object > > columns); // ignore the columns that meet the conditions and 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. As the saying goes, no updated data is a pool of stagnant water. Only with updated data can there be changes. So let’s take a look at how to update data gracefully:

3.1 simple mode

Let’s start with the two simplest ones:

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 corresponding 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 satisfy the where expression

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 to set the content to be updated in columns.

3.2 advanced mode

Similarly, enable the advanced mode through asupdatable

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

Then you can do more for 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 so that the data satisfies the lambda expression. The lambda expression 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.

  • Some columns are not updated
Iupdatable < T > ignorecolumns (params string [] columns); // ignore the passed in column names
Iupdatable < T > ignorecolumns (expression < func < T, Object > > columns); // use anonymous objects to indicate the column names to be ignored
Iupdatable < T > ignorecolumns (bool ignoreallnullcolumns, bool isoffidentity = false, bool ignorealldefaultvalue = false); // sets whether to ignore null columns, whether to force the update of primary keys, and whether to ignore all default value columns
  • 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);

Let’s have a simple guess. What do these mean?

It can be said that there are several simple and clear condition setting modes, lambda means to filter and update data, and the field value determines the condition update.

The value of conditiontype is recommendedConditionalTypeEnumeration.

3.3 update or insert

In the actual development, we may encounter the method of inserting or updating, so we need to find a method that can directly update or insert. 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();

However, this method is in sugarclient. We can do this by:

public ISqlSugarClient AsSugarClient();

Get the sugar client object associated with it in simpleclient.

The criterion 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

Deletion is a very important function point in the actual development process, so how to quickly and effectively delete data is also a very important thing. So, let’s take a look at how to perform the deletion:

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 no other need to pay attention to the deletion. The first is conditional deletion, and all that meet the conditions should be deleted. The second one removes a single object, and the next two delete objects based on 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 asdeleteable method for deletion. In particular, the ideletable interface provides the method of deleting according to SQL statements. There is nothing else to pay attention to.

5. Query

A good ORM framework, at least five parts of the effort in the query, how to faster and more accurate query has become the requirements of ORM framework development. At the same time, simple and easy to use is the programmer’s expectation of orm.

Well, let’s take a look at sqlsugar’s query capabilities

Public bool isany (expression < func < T, bool > > whereexpression); // query whether there is qualified data
Public int count (expression < func < T, bool > > where expression); // get the number of satisfied conditions
Public t getbyid ([dynamic] dynamic ID); // get an instance according to the primary key
Public bool isany (expression < func < T, bool > > whereexpression); // returns an object that meets the conditions
Public list < T > getlist(); // returns all data in the form of list
Public list < T > GetList (expression < func < T, bool > > where expression); // returns all data that meet the criteria

Paging to get 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 used to define the specification query specification. In fact, the class is used:

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 the conditiontype and define various judgment criteria:

public enum ConditionalType
{
    Equal=0,
    Like=1,
    GreaterThan =2,
    GreaterThanOrEqual = 3,
    LessThan=4,
    LessThanOrEqual = 5,
    In=6,
    NotIn=7,
    LikeLeft=8,
    LikeRight=9,
    NoEqual=10,
    IsNullOrEmpty=11,
    IsNot=12,
    NoLike = 13,
}

Let’s take a brief look at the effect of paging with iconditionmodel

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

Generate the following SQL statements:

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

We can see that there is no difference between the two, just different query habits.

6. Summary

According to the previous custom, it should be over by now. However, there are still some important things about sqlsugar that have not been introduced, so we will add a preview for the next issue

The next article will analyze some more advanced content of sqlsugar, including advanced query mode, transaction and batch operation

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 development of actual business requirements. Well, give me a compliment.

There are three more pieces of content “C ා data operation series” will be finished. From the next series, we will step into the most important technology stack in our work Asp.net Core。 This can be written into your resume. Well, that’s right. The next series of plans will be introduced in the form of actual combat asp.net Core knowledge points and settings.

Please pay more attention to my blog Mr. Gao’s cabin

C ා data operation series - 15 sqlsugar