当前位置:网站首页>. net 5 ORM eight practical skills dry goods - sqlsugar ORM

. net 5 ORM eight practical skills dry goods - sqlsugar ORM

2020-11-25 08:37:07 itread01

Introduce

sqlsugar Already perfectly compatible in the first place .NET5 And someone is already using it , A lot of people are worried that they can't solve the problems when they use the open source framework , It leads to the loss of previous achievements , Use SqlSugar You can rest assured , In addition to detailed documentation and years of extensive user accumulation ,

SqlSugar It also provides a complete service , Keep your project free of worries

 

Advantages : Easy to use 、 It's fully functional 、 High efficiency 、 Lightweight 、 The service is complete

Support database :MySql、SqlServer、Sqlite、Oracle 、 postgresql、 Reach a dream 、 Renmin Jincang

 

Free service

1、 Basic usage consultation

2、BUG Submit

3、 Adopt suggestions and needs

4、 Open source code Can be used in any commercial project No charge Download

5、qq Exchange group discussion 995692596( Idle ) 654015377(1800/2000 Crowding ) 726648662(( It's full ) 225982985 ( It's full )

 

Practical skills 1 : Performance monitoring

Through this function, we can easily monitor the execution of more than 1 Of a second sql, And you can take To him C# Code files and lines And methods

  SqlSugarClient db = new SqlSugarClient(new ConnectionConfig()
   {
                DbType = DbType.SqlServer,
                ConnectionString = Config.ConnectionString,
                InitKeyType = InitKeyType.Attribute,
                IsAutoCloseConnection = true
               
   });
    db.Aop.OnLogExecuted = (sql, p) =>
    {
         // Execution time exceeds 1 second 
        if (db.Ado.SqlExecutionTime.TotalSeconds > 1) 
        {
// Code CS File name var fileName= db.Ado.SqlStackTrace.FirstFileName; // Lines of code var fileLine = db.Ado.SqlStackTrace.FirstLine; // Method name var FirstMethodName = db.Ado.SqlStackTrace.FirstMethodName;
//sql
var exesql=sql;
// Arguments
var sqlpars=p; //db.Ado.SqlStackTrace.MyStackTraceList[1].xxx Get information about the upper level method } };

 

Practical function 2: Data change monitoring

When our code deleted which specific code , Added specific code , Which column has been modified? If there is no powerful log function, you will not be able to retrieve ,SqlSugar Can easily achieve high security level of data logging

db.Aop.OnDiffLogEvent = it =>
{
                var editBeforeData = it.BeforeData;// Record before operation    contain :  Field description   Name   value   Table name   Table description 
                var editAfterData = it.AfterData;// Record after operation     contain :  Field description   Name   value    Table name   Table description 
                var sql = it.Sql;
                var parameter = it.Parameters;
                var data = it.BusinessData;// This will show what you're passing in 
                var time = it.Time;
                var  diffType=it.DiffType;//enum insert 、update and delete  
                  
                //Write logic
};
  
// newly added 
db.Insertable(new Student() { Name = "beforeName" })
.EnableDiffLogEvent(new {  title=" I'm inserting "}) // Enable logging and add business objects 
.ExecuteReturnIdentity();
  
// modify db.Updateable<Student>(new Student() { Id = id, CreateTime = DateTime.Now, Name = "afterName", SchoolId = 2 }) .EnableDiffLogEvent() // Start log .ExecuteCommand();
// Delete db.Deleteable<Student>(id) .EnableDiffLogEvent()// Start log .ExecuteCommand();

 

Practical function 3:JSON Type perfect support

SqlSugar Not only support PgSql Of Json array Out of type , Even if your database doesn't have JSON Type can be used as well JSON Objects are stored

ublic class UnitJsonTest
{
   [SqlSugar.SugarColumn(IsPrimaryKey = true, IsIdentity = true)]
   public int Id { get; set; }
    [SqlSugar.SugarColumn(ColumnDataType ="varchar(max)", IsJson = true)]
   public Order Order { get; set; }
   public string Name{get;set;}
}
Db.Insertable(new UnitJsonTest() { Name="json1",Order = new Order { Id = 1, Name = "order1" } }).ExecuteCommand();
var list = Db.Queryable<UnitJsonTest>().ToList();

 

Practical function 4: Sub Treasury + table + Multi database transactions

1、 Dynamic database creation

The following code will create db1 and db2 Database

string conn = "server=.;uid=sa;pwd=haosql;database={0}";
var db = new SqlSugarClient(new ConnectionConfig()
{
DbType = SqlSugar.DbType.SqlServer,
ConnectionString = string.Format(conn, "db1"),
InitKeyType=InitKeyType.Attribute
});
db.DbMaintenance.CreateDatabase();
var db2 = new SqlSugarClient(new ConnectionConfig()
{
DbType = SqlSugar.DbType.SqlServer,
ConnectionString = string.Format(conn, "db2")
});
db2.DbMaintenance.CreateDatabase();

2、 Dynamic table building

 The following code will generate Order1 and Order2  Two tables 

// Be careful db It has to be the same context 
var db = new SqlSugarClient(new ConnectionConfig()
{
DbType = SqlSugar.DbType.SqlServer,
ConnectionString = ".;xxx",
InitKeyType=InitKeyType.Attribute // This property must be set this way 

});

db.MappingTables.Add(typeof(Order).Name, typeof(Order).Name + 1);
db.CodeFirst.InitTables(typeof(Order));
db.MappingTables.Add(typeof(Order).Name, typeof(Order).Name + 2);
db.CodeFirst.InitTables(typeof(Order));

3、 Substantial increase 、 Delete 、 check 、 Change

var list= db.Queryable<Order>().AS("Order1").ToList();// Inquire about Order1 Table of 
// increase   Delete   Change usage is the same as query  Inasertable().AS Deleteable().AS Updateable().As

4、 Cross database linked table query

var list = db.Queryable<Order, OrderItem, Custom>((o, i, c) => o.Id == i.OrderId&&c.Id == o.CustomId)
.AS("xx.order")
.AS<OrderItem>("yy.OrderItem")
.AS<Custom>("zz.Custom")
.Select<ViewOrder>()
.ToList();

5、 Multi switch

SqlSugarClient db = new SqlSugarClient(new List<ConnectionConfig>()
{
new ConnectionConfig(){ ConfigId="1", DbType=DbType.SqlServer, 
ConnectionString=Config.ConnectionString,InitKeyType=InitKeyType.Attribute,IsAutoCloseConnection=true },
new ConnectionConfig(){ ConfigId="2", DbType=DbType.MySql, 
ConnectionString=Config.ConnectionString4 ,InitKeyType=InitKeyType.Attribute ,IsAutoCloseConnection=true}
});

// Ku 1
try
{
db.BeginTran();
db.Deleteable<Order>().ExecuteCommand();

db.ChangeDatabase("2");// Use the library 2
db.Deleteable<Order>().ExecuteCommand();


db.CommitTran();
}
catch
{
db.RollbackTran();
}

 

Practical function 5: Infinite levels of cascading inserts

Use sqlsugar Just configure the primary key , Cascading inserts can be implemented without any foreign key relationships configured by entities

// Foreign keys with auto assignment 
db.Insertable(new Order()
{
    Name = " Order  1",
        CustomId = 1,
        Price = 100,
        CreateTime = DateTime.Now,
        Id = 0,// On the column 
        Items = new List<OrderItem>() {
                new OrderItem(){
                    CreateTime=DateTime.Now,
                    OrderId=0,// You need to automatically get the auto increment column of the order 
                    Price=1,
                    ItemId=1
                }
            }
    })
    .AddSubList(it => it.Items.First().OrderId  )// Set item Tabular OrderId The order will be added automatically 
    .ExecuteReturnPrimaryKey();
 

 

Practical function 6: Automatic secondary cache

When we use Redis When waiting for operation , It is very complicated to clean up the cache when updating data ,SqlSugar Support multi table cache , And update one of the tables and clear the cache

Second cache is to cache the result set , When SQL Read data from the cache when the and arguments have not changed , Reduce database read and write operations

ICacheService myCache = new HttpRuntimeCache();

  SqlSugarClient db = new SqlSugarClient(new ConnectionConfig()

  {

                ConnectionString = Config.ConnectionString,

                DbType = DbType.SqlServer,

                IsAutoCloseConnection = true,

                ConfigureExternalServices = new ConfigureExternalServices()

                {

                    DataInfoCacheService = myCache // Configure the cache class we created 

                }

   });

db.Queryable<Student>().Where(it => it.Id > 0).WithCache().ToList();// Set cache default day 

db.Queryable<Student>().WithCache(1000).ToList();// Set specific expiration time 

 

Delete data and update cache , Insert and update are the same

db.Deleteable<Student>().RemoveDataCache().Where(it => it.Id == 1).ExecuteCommand();

//remove All references Student Watch cache , Contains multiple table queries 

 

 

Practical function 7: Supports multiple operations on databases

Name Note Return type
GetDataBaseList Get all database names List
GetViewInfoList Get all views List
GetTableInfoList Get all the tables List
GetColumnInfosByTableName Get the column according to the table name List
GetIsIdentities Get auto increment column List
GetPrimaries Get the primary key List
IsAnyTable Does the table exist bool
IsAnyColumn Does the column exist bool
IsPrimaryKey Whether the primary key exists bool
IsIdentity Whether or not there is self increasing bool
IsAnyConstraint Whether the constraint exists bool
DropTable Delete table bool
TruncateTable Clear the table bool
CreateTable Create columns ( Not recommended , use CodeFirst Build table ) bool
AddColumn New column bool
UpdateColumn Update column bool
AddPrimaryKey Add primary key bool
DropConstraint Delete constraints bool
BackupDataBase Backup Library bool
DropColumn Delete column bool
RenameColumn Rename the column bool
AddTableRemark New table description bool
AddColumnRemark New column description bool
DeleteColumnRemark Delete column description bool
RenameTable A famous watch bool

 

Practical function 8: Dynamic SQL Perfect anti injection solution

var orderField = "order';drop table order";
var orderInfo= db.EntityMaintenance.GetEntityInfo<Order>();
 if (orderInfo.Columns.Any(it => it.DbColumnName != orderField)) 
{
    throw new Exception(" Please don't inject ");
}
 db.Queryable<Order>().OrderBy(orderField).ToList();

 

SqlSugar I've been trying to be better , All features come from customers and have been practiced by users for a long time , In order to give me more motivation

 

Original code download : https://github.com/sunkaixuan/SqlSugar        Just like it , I'm very motivated

 

  

版权声明
本文为[itread01]所创,转载请带上原文链接,感谢
https://chowdera.com/2020/11/202011250834500143.html

随机推荐