High performance data import scheme & table filter & one to many support filtering -. Net sqlsugar ORM


1、 What are the difficulties of data import

It can be said that 100% of developers will encounter this function, and it is very common, such as batch operation. Do you know which data is wrong?

You don’t even know, the customer doesn’t know


1. Data classification

You need to classify and summarize the insert, update, ignore and error data, and finally return them to the customer. If there is no good design, it is very difficult to put these operations in place in one step

2. High performance

For insert or update, you can’t just insert or update. You must batch operate or use blubcopy operation

3. Data validation

The wrong data should be assembled and returned to the customer to let the customer know which fields and which data are wrong


2、 Using ﹣ storageable to solve problems

We can use the storageable function in sqlsugar ORM to solve the above problems. As an old ORM framework, sqlsugar has been innovating and updating


1. Getting started example

How to implement simple insert or update with storageable

Example 1: import a list, when the ID and other 0 are inserted, the ID > 0 is updated (ID is the primary key)

List list2 = new List();
list2.Add(new UinitBlukTable() { Id = 1, Name = "a", Create = DateTime.Now });
list2.Add(new UinitBlukTable() { Id = 2, Name = "a", Create = DateTime.Now });
list2.Add(new UinitBlukTable() { Id = 0, Name = "a", Create = DateTime.Now });

var x = Db.Storageable(list2)
.SplitUpdate(it => it.Item.Id > 0)
.SplitInsert(it => it.Item.Id == 0).ToStorage();


Example 2: import a list, when ID exists, update the database, otherwise insert

var x = Db.Storageable(list2)
                            .SplitUpdate(it =>  it.Any (y=>y.Id== it.Item.Id ))//There is an update in the database
                            . splitinsert (it = > true). Tostorage(); // insert the rest
                            x. AsInsertable.ExecuteCommand (); // you can also use blukcopy to refer to the usage of sqlsugar blukcopy

If the entity does not have a primary key, we can use

var x = Db.Storageable(list2)
                            .SplitUpdate(it => it.Any(y=>y.Id==it.Item.Id))
                            .SplitInsert(it => it.NotAny(y => y.Id == it.Item.Id))
                            .WhereColumns(it=> it.Id ). tostorage(); // takes ID as the unique column of the database. Of course, multiple new columns are supported{ it.id , it.name }


2. Data validation and statistics

Prepare 4 test data

List list2 = new List();
list2.Add(new UinitBlukTable() { Id = 1, Name = "a", Create = DateTime.Now });
list2.Add(new UinitBlukTable() { Id = 2, Name = "a", Create = DateTime.Now });
list2.Add(new UinitBlukTable() { Id = 3, Name = "a", Create = DateTime.Now.AddYears(-2) });
list2.Add(new UinitBlukTable() { Id = 4, Name ="", Create = DateTime.Now.AddYears(-2) });

Write code to classify error data, insertable data and updatable data

var x = Db.Storageable(list2)
                                      .SplitError(it =>  string.IsNullOrEmpty ( it.Item.Name ), name cannot be empty)
                                      .SplitError(it =>  it.Item.Create  it.Any (y=>y.Id== it.Item.Id ))//There are updates
                                      . splitinsert (it = > true) // remaining inserts
Console.WriteLine ("insert {0} update {1} error data {2} do not calculate data {3} delete data {4}, total {5})",

Execution code output result:


We can see that one can be inserted, one can be updated, and two error data

Output error details:

foreach (var item in x.ErrorList)
        Console.WriteLine ("ID equals"+ item.Item.Id +" : "+ item.StorageMessage );


We can see that the output ID3 and ID4 are wrong, and the specific error details can be output

Perform updates and inserts



3、 Using table filters

Sqlsugar used to support global filters, but it’s not easy to use. Most users are used to implementing filters in the form of tables. The usage is as follows

1. Create table filter

SqlSugarClient db = new SqlSugarClient(new ConnectionConfig() { DbType = DbType.SqlServer, ConnectionString = Config.ConnectionString, IsAutoCloseConnection = true });
 db.QueryFilter.Add (new TableFilterItem(it =>  it.Name.Contains ("a")); // only the table order will take effect

2. Valid query statement

After using the table filter, as long as the query statement with the order table, a condition will be automatically added

            //SELECT [Id],[Name],[Price],[CreateTime],[CustomId] FROM [Order]  WHERE  ([Name] like '%'[email protected]+'%') 

 db.Queryable((i, o) => i.OrderId == o.Id)
                .Where(i => i.OrderId != 0)
            //SELECT i.* FROM [OrderDetail] i  ,[Order]  o  WHERE ( [i].[OrderId] = [o].[Id] )  AND ( [i].[OrderId] <> @OrderId0 )  AND  ([o].[Name] like '%'[email protected]+'%')

3. Disable global filter

db.Queryable().Filter(null, false).ToList();
 //SELECT [Id],[Name],[Price],[CreateTime],[CustomId] FROM [Order]


4、 One to many queries support conditional filtering

Sqlsugar also supports navigation queries better and better. Here is an example of filtering after one to many queries

var list7= Db.Queryable()
.Mapper(it => it.Items, it => it.Items.First().OrderId)
.Where(it =>  it.Items.Any (y = > y.itemid = = 1)) // previously only. Any ()


5、 Summary

All the functions of sqlsugar come from customers, and they are the common needs of multiple customers. In fact, I didn’t do anything innovative. I just designed the functions they want on the basis of customers. If they are not satisfied with their use, I will use them on this basis

Slowly modify


Source code download:

https://github.com/donet5/SqlSugarSqlsugar has been continuously updated for six years and is becoming more and more perfect. If EF or other ORM is not updated, then one more open source is one more choice