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

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

2020-11-25 01:05:18 Sun Kaixuan

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 , Lead to the abandonment of previous achievements , Use SqlSugar You can rest assured , In addition to detailed documentation and a large number of users over the years ,

SqlSugar It also provides a complete service , Let your project have no worries

 

advantage : Simple and easy to use 、 The function is all ready 、 High performance 、 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 It can be used in any commercial project There is no charge download

5、qq Exchange group discussion 995692596( Free ) 654015377(1800/2000 crowded ) 726648662(( Is full ) 225982985 ( Is 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 exceeded 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;
            //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 the people you're sending in 
                var time = it.Time;
                var  diffType=it.DiffType;//enum insert 、update and delete  
                  
                //Write logic
};
  
// add to 
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 Object to store

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 transaction

1、 Create a database dynamically

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 the 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}
});

// library 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 key 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 up item Tabular OrderId It is equal to the order auto adding column 
    .ExecuteReturnPrimaryKey();
 

 

Practical function 6: Fully automatic L2 caching

When we use Redis When waiting for operation , It can be very complicated to clean up the cache when updating data ,SqlSugar Support multi table caching , 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 parameters 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 the default cache 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 Table caching , Contains multiple table queries 

 

 

Practical function 7: Support multiple operations on the database

name remarks Return type
GetDataBaseList Get all database names List
GetViewInfoList Get all views List
GetTableInfoList Get all tables List
GetColumnInfosByTableName Get the column according to the table name List
GetIsIdentities Get auto increment column List
GetPrimaries Get 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 there are constraints bool
DropTable Delete table bool
TruncateTable Clear the table bool
CreateTable Create column ( Not recommended , use CodeFirst Build table ) bool
AddColumn Add columns bool
UpdateColumn Update column bool
AddPrimaryKey Add primary key bool
DropConstraint Delete constraints bool
BackupDataBase Backup Library bool
DropColumn Delete column bool
RenameColumn To be ranked high bool
AddTableRemark Add table description bool
AddColumnRemark Add column description bool
DeleteColumnRemark Delete column description bool
RenameTable rename table 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 functions come from customers and have been practiced by users for a long time , In order to give me more motivation

 

Download the original code : https://github.com/sunkaixuan/SqlSugar        Just like it , I'm very motivated

 

  

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

随机推荐