mysql sqlsugar_SqlSugar: Best ORM Fastest ORM Simple Easy Sqlite orm Oracle ORM Mysql Orm po...

SqlSugar 4.X API

Using SqlSugar is very simple ,And it's powerful.

SqlSugar=One object+One parameter=16 functions,

Support：MySql、SqlServer、Sqlite、Oracle 、 postgresql

Contactinfomation

Email:610262374@qq.com

QQ Group:225982985

Nuget

.net Install：

Install-Package sqlSugar

.net core Install：

Install-Package sqlSugarCore

SqlSugar's 16 Functions

There are 16 methods under SqlSugarClient

Create SqlSugarClient

All operations are based on SqlSugarClient

SqlSugarClient parameter and only one ConnectionConfig

public List GetStudentList()

{

var db= GetInstance();

var list= db.Queryable().ToList();//Search

return list;

}

///

/// Create SqlSugarClient

///

///

private SqlSugarClient GetInstance()

{

SqlSugarClient db = new SqlSugarClient(new ConnectionConfig()

{

ConnectionString = "Server=.xxxxx",

DbType = DbType.SqlServer,

IsAutoCloseConnection = true,

InitKeyType = InitKeyType.Attribute

});

//Print sql

db.Aop.OnLogExecuting = (sql, pars) =>

{

Console.WriteLine(sql + "\r\n" + db.Utilities.SerializeObject(pars.ToDictionary(it => it.ParameterName, it => it.Value)));

Console.WriteLine();

};

return db;

}

public class Student

{

[SugarColumn(IsPrimaryKey = true, IsIdentity = true]

public int Id { get; set; }

public int? SchoolId { get; set; }

public string Name { get; set; }

}

1. Queryable

We use it to query

Here are some examples

//easy

var getAll = db.Queryable().ToList();

var getAllNoLock = db.Queryable().With(SqlWith.NoLock).ToList();

var getByPrimaryKey = db.Queryable().InSingle(2);

var sum = db.Queryable().Sum(it=>it.Id);

var isAny = db.Queryable().Where(it=>it.Id==-1).Any();

var isAny2 = db.Queryable().Any(it => it.Id == -1);

var getListByRename = db.Queryable().AS("Student").ToList();

var getByWhere = db.Queryable().Where(it => it.Id == 1 || it.Name == "a").ToList();

var getByFuns = db.Queryable().Where(it => SqlFunc.IsNullOrEmpty(it.Name)).ToList();

var group = db.Queryable().GroupBy(it => it.Id).Select(it =>new { id = SqlFunc.AggregateCount(it.Id) }).ToList();

//Page

var page = db.Queryable().ToPageList(pageIndex, pageSize, ref totalCount);

//page join

var pageJoin = db.Queryable((st, sc) =>new JoinQueryInfos(JoinType.Left,st.SchoolId==sc.Id))

.ToPageList(pageIndex, pageSize, ref totalCount);

//top 5

var top5 = db.Queryable().Take(5).ToList();

//join Order By (order by st.id desc,sc.id desc)

var list4 = db.Queryable((st, sc) =>new JoinQueryInfos(JoinType.Left,st.SchoolId==sc.Id))

.OrderBy(st=>st.Id,OrderByType.Desc)

.OrderBy((st,sc)=>sc.Id,OrderByType.Desc)

.Select().ToList();

2. Updateable

We use it to Update

//update reutrn Update Count

var t1= db.Updateable(updateObj).ExecuteCommand();

//Only update Name

var t3 = db.Updateable(updateObj).UpdateColumns(it => new { it.Name }).ExecuteCommand();

//Ignore Name and TestId

var t4 = db.Updateable(updateObj).IgnoreColumns(it => new { it.Name, it.TestId }).ExecuteCommand();

//update List

var t7 = db.Updateable(updateObjs).ExecuteCommand();

//Where By Expression

var t9 = db.Updateable(it=>new class() { name="a",createtime=p }).Where(it => it.Id == 1).ExecuteCommand();

3. Insertable

We use it to Insert

//Insert reutrn Insert Count

var t2 = db.Insertable(insertObj).ExecuteCommand();

//Insert reutrn Identity Value

var t3 = db.Insertable(insertObj).ExecuteReutrnIdentity();

//Only insert Name

var t4 = db.Insertable(insertObj).InsertColumns(it => new { it.Name,it.SchoolId }).ExecuteReutrnIdentity();

//Ignore TestId

var t5 = db.Insertable(insertObj).IgnoreColumns(it => new { it.Name, it.TestId }).ExecuteReutrnIdentity();

//Insert List

var s9 = db.Insertable(insertObjs).InsertColumns(it => new { it.Name }).ExecuteCommand();

4. Deleteable

We use it to Delete

//by entity

db.Deleteable().Where(new Student() { Id = 1 }).ExecuteCommand();

//by primary key

db.Deleteable().In(1).ExecuteCommand();

//by primary key array

db.Deleteable().In(new int[] { 1, 2 }).ExecuteCommand();

//by expression

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

5. SqlQueryable

var list = db.SqlQueryable("select * from student").ToPageList(1, 2);

var list2 = db.SqlQueryable("select * from student").Where(it=>it.Id==1).ToPageList(1, 2);

var list3= db.SqlQueryable("select * from student").Where("id=@id",new { id=1}).ToPageList(1, 2);

6. SaveQueues

Perform multiple operations together with transactions

var db = GetInstance();

db.Insertable(new Student() { Name = "a" }).AddQueue();

db.Insertable(new Student() { Name = "b" }).AddQueue();

db.SaveQueues();

db.Insertable(new Student() { Name = "a" }).AddQueue();

db.Insertable(new Student() { Name = "b" }).AddQueue();

db.Insertable(new Student() { Name = "c" }).AddQueue();

db.Insertable(new Student() { Name = "d" }).AddQueue();

var ar = db.SaveQueuesAsync();

db.Queryable().AddQueue();

db.Queryable().AddQueue();

db.AddQueue("select * from student where id=@id", new { id = 1 });

var result2 = db.SaveQueues();

7.Ado

db.Ado.MethodName，Look at the following example

var dt=db.Ado.GetDataTable("select * from table where id=@id and name=@name",new List(){

new SugarParameter("@id",1),

new SugarParameter("@name",2)

});

var dt=db.Ado.GetDataTable("select * from table where id=@id and name=@name",new{id=1,name=2});

//Use Stored Procedure

var dt2 = db.Ado.UseStoredProcedure().GetDataTable("sp_school",new{name="张三",age=0});// GetInt SqlQuery 等等都可以用

var nameP= new SugarParameter("@name", "张三");

var ageP= new SugarParameter("@age", null, true);//isOutput=true

var dt2 = db.Ado.UseStoredProcedure().GetDataTable("sp_school",nameP,ageP);

8.Saveable

Insert or Update

db.Saveable(entity).ExecuteReturnEntity();

db.Saveable(new Student() { Name = "" })

.InsertColumns(it=>it.Name)

.UpdateColumns(it=>new { it.Name,it.CreateTime }

.ExecuteReturnEntity();

9.EntityMain

var entityInfo=db.EntityMaintenance.GetEntityInfo();

foreach (var column in entityInfo.Columns)

{

Console.WriteLine(column.ColumnDescription);

}

10.DbMain

var tables = db.DbMaintenance.GetTableInfoList();

foreach (var table in tables)

{

Console.WriteLine(table.Description);

}

11.Aop

db.Aop.OnLogExecuted=(sql,pars)=>//SQL executed event

{

};

db.Aop.OnLogExecuting=(sql,pars)=>//SQL executing event (pre-execution)

{

};

db.Aop.OnError=(exp)=>//SQL execution error event

{

};

db.Aop.OnExecutingChangeSql=(sql,pars)=>//SQL executing event (pre-execution,SQL script can be modified)

{

returnnewKeyValuePair(sql,pars);

};

12.QueryFilter

//gobal filter

var db = GetInstance();

var sql = db.Queryable().ToSql();

//SELECT [ID],[SchoolId],[Name],[CreateTime] FROM [STudent] WHERE isDelete=0

public static SqlSugarClient GetInstance()

{

SqlSugarClient db = new SqlSugarClient(new ConnectionConfig() {xxx);

db.QueryFilter.Add(new SqlFilterItem()

{

FilterValue = filterDb =>

{

return new SqlFilterResult() { Sql = " isDelete=0" };

}

});

return db;

}

13.DbFirst

var db = GetInstance();

//Create all class

db.DbFirst.CreateClassFile("c:\\Demo\\1");

//Create student calsss

db.DbFirst.Where("Student").CreateClassFile("c:\\Demo\\2");

//Where(array)

//Mapping name

db.MappingTables.Add("ClassStudent", "Student");

db.MappingColumns.Add("NewId", "Id", "ClassStudent");

db.DbFirst.Where("Student").CreateClassFile("c:\\Demo\\3");

//Remove mapping

db.MappingTables.Clear();

//Create class with default value

db.DbFirst.IsCreateDefaultValue().CreateClassFile("c:\\Demo\\4", "Demo.Models");

//Mapping and Attribute

db.MappingTables.Add("ClassStudent", "Student");

db.MappingColumns.Add("NewId", "Id", "ClassStudent");

db.DbFirst.IsCreateAttribute().Where("Student").CreateClassFile("c:\\Demo\\5");

14.CodeFirst

db.CodeFirst.SetStringDefaultLength(100).BackupTable().InitTables(typeof(CodeTable),typeof(CodeTable2)); //change entity backupTable

db.CodeFirst.SetStringDefaultLength(100).InitTables(typeof(CodeTable), typeof(CodeTable2));

15.Utilities

var list = db.Utilities.DataTableToList(datatable);

16.SimpleClient

var db = GetInstance();

var sdb = db.GetSimpleClient();

sdb.GetById(1);

sdb.GetList();

sdb.DeleteById(1);

sdb.Update(obj);

Code generator

More APi 中文文档：