sqlsugar 增删改查 demo

1.sqlsugar 增删改查 demo

entity query:
var getAll = db.Queryable().ToList();
var getAll2 = db.Queryable().Select(x => x.CreateTime.Date).ToList();
var getAll3 = db.Queryable().Select(x =>Convert.ToDateTime(x.CreateTime)).ToList();
var getAll4 = db.Queryable().Select(x=>x.CreateTime.AddDays(1)).ToList();
var getAll5 = db.Queryable().Select(x => x.CreateTime.AddMonths(1)).ToList();
var getAll6 = db.Queryable().Select(x => SqlFunc.DateAdd(x.CreateTime, 1)).ToList();
var getAll7 = db.Queryable().Select(x => x.Name.ToString()).ToList();
var getAll8 = db.Queryable().Select(x => x.CreateTime.ToString("yyyy-MM-dd")).ToList();
var getAll9 = db.Queryable().Select(x => x.CreateTime.ToString("yyyy-MM-dd HH:mm:ss")).ToList();
var getAl20 = db.Queryable().Select(x => x.CreateTime.Year).ToList();
var getOrderBy = db.Queryable().OrderBy(it => it.Name,OrderByType.Desc).ToList();
var getOrderBy2 = db.Queryable().OrderBy(it => it.Id).OrderBy(it => it.Name, OrderByType.Desc).ToList();
var getOrderBy3 = db.Queryable().OrderBy(it =>new { it.Name,it.Id}).ToList();
var getRandom = db.Queryable().OrderBy(it => SqlFunc.GetRandom()).First();
var getByPrimaryKey = db.Queryable().InSingle(2);
var getSingleOrDefault = db.Queryable().Where(it => it.Id == 1).Single();
var getFirstOrDefault = db.Queryable().First();
var getByWhere = db.Queryable().Where(it => it.Id == 1 || it.Name == "a").ToList();
var getByWhere2 = db.Queryable().Where(it => it.Id == DateTime.Now.Year).ToList();
var getByFuns = db.Queryable().Where(it => SqlFunc.IsNullOrEmpty(it.Name)).ToList();
var getByFuns2 = db.Queryable().GroupBy(it => it.Name).Select(it => SqlFunc.AggregateDistinctCount(it.Price)).ToList();

sql query:

	var list = db.SqlQueryable<Order>("select * from \"Order\"").ToPageList(1, 2, ref total);
	//by expression
	var list2 = db.SqlQueryable<Order>("select * from \"Order\"").Where(it => it.Id == 1).ToPageList(1, 2);
	//by sql
	var list3 = db.SqlQueryable<Order>("select * from \"Order\"").Where("Id=@id", new { id = 1 }).ToPageList(1, 2);

update :
//update all columns by primary key
var result = db.Updateable(updateObj).ExecuteCommand();//update single
var result2 = db.Updateable(updateObjs).ExecuteCommand();//update List

        //Ignore  Name and Price
        var result3 = db.Updateable(updateObj).IgnoreColumns(it => new { it.CreateTime, it.Price }).ExecuteCommand();

        //only update Name and CreateTime 
        var result4 = db.Updateable(updateObj).UpdateColumns(it => new { it.Name, it.CreateTime }).ExecuteCommand();

        //If there is no primary key
        var result5 = db.Updateable(updateObj).WhereColumns(it => new { it.Id }).ExecuteCommand();//update single by id
        var result6 = db.Updateable(updateObjs).WhereColumns(it => new { it.Id }).ExecuteCommand();//update List<Class> by id

delete:
//by entity
db.Deleteable().Where(new Order() { Id = 1111 }).ExecuteCommand();

        //by primary key
        db.Deleteable<Order>().In(1111).ExecuteCommand();

        //by primary key array
        db.Deleteable<Order>().In(new int[] { 1111, 2222 }).ExecuteCommand();

        //by expression
        db.Deleteable<Order>().Where(it => it.Id == 11111).ExecuteCommand();

insert or update:
//insert or update
db.Saveable(new Order() { Id=1, Name="jack" }).ExecuteReturnEntity();

        //insert or update
        db.Saveable<Order>(new Order() { Id = 1000, Name = "jack", CreateTime=DateTime.Now })
              .InsertColumns(it => new { it.Name,it.CreateTime, it.Price})//if insert  into name,CreateTime,Price
              .UpdateColumns(it => new { it.Name, it.CreateTime })//if update set name CreateTime
              .ExecuteReturnEntity();

filter：
//single table query gobal filter
db.QueryFilter.Add(new SqlFilterItem()
{
FilterValue = filterDb =>
{
//Writable logic
return new SqlFilterResult() { Sql = " isDelete=0" };//Global string perform best
}
});

orm operation:
DbContext context = new DbContext();

        context.Db.CodeFirst.InitTables<Order, OrderItem,Custom>();//Create Tables
        ;
        var orderDb = context.OrderDb;
		
        //Select
        var data1 = orderDb.GetById(1);
        var data2 = orderDb.GetList();
        var data3 = orderDb.GetList(it => it.Id == 1);
        var data4 = orderDb.GetSingle(it => it.Id == 1);
        var p = new PageModel() { PageIndex = 1, PageSize = 2 };
        var data5 = orderDb.GetPageList(it => it.Name == "xx", p);
        Console.Write(p.TotalCount);
        var data6 = orderDb.GetPageList(it => it.Name == "xx", p, it => it.Name, OrderByType.Asc);
        Console.Write(p.TotalCount);
        List<IConditionalModel> conModels = new List<IConditionalModel>();
        conModels.Add(new ConditionalModel() { FieldName = "id", ConditionalType = ConditionalType.Equal, FieldValue = "1", FieldValueConvertFunc=it=>Convert.ToInt32(it) });//id=1
        var data7 = orderDb.GetPageList(conModels, p, it => it.Name, OrderByType.Asc);
        orderDb.AsQueryable().Where(x => x.Id == 1).ToList();

        //Insert
        orderDb.Insert(insertObj);
        orderDb.InsertRange(InsertObjs);
        var id = orderDb.InsertReturnIdentity(insertObj);
        orderDb.AsInsertable(insertObj).ExecuteCommand();


        //Delete
        orderDb.Delete(insertObj);
        orderDb.DeleteById(11111);
        orderDb.DeleteById(new int[] { 1111, 2222 });
        orderDb.Delete(it => it.Id == 1111);
        orderDb.AsDeleteable().Where(it => it.Id == 1111).ExecuteCommand();

        //Update
        orderDb.Update(insertObj);
        orderDb.UpdateRange(InsertObjs);
        orderDb.Update(it => new Order() { Name = "a", }, it => it.Id == 1);
        orderDb.AsUpdateable(insertObj).UpdateColumns(it => new { it.Name }).ExecuteCommand();

2.SqlSugarClient.cs

using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using SqlSugar;
namespace OrmTest
{
public class Demo0_SqlSugarClient
{

    public static void Init()
    {
        SqlSugarClient();//Create db
        DbContext();//Optimizing SqlSugarClient usage
        SingletonPattern();//Singleten Pattern
        //DistributedTransactionExample();
        MasterSlave();//Read-write separation 
        CustomAttribute(); 
    }

    private static void MasterSlave()
    {
        Console.WriteLine("");
        Console.WriteLine("#### MasterSlave Start ####");
        SqlSugarClient db = new SqlSugarClient(new ConnectionConfig()
        {
            ConnectionString = Config.ConnectionString,//Master Connection
            DbType = DbType.Dm,
            InitKeyType = InitKeyType.Attribute,
            IsAutoCloseConnection = true,
            SlaveConnectionConfigs = new List<SlaveConnectionConfig>() {
                 new SlaveConnectionConfig() { HitRate=10, ConnectionString=Config.ConnectionString2 } ,
                   new SlaveConnectionConfig() { HitRate=10, ConnectionString=Config.ConnectionString2 }
            } 
        });
        db.Aop.OnLogExecuted = (s, p) =>
        {
            Console.WriteLine(db.Ado.Connection.ConnectionString);
        };
        Console.WriteLine("Master:");
        db.Insertable(new Order() { Name = "abc", CustomId = 1, CreateTime = DateTime.Now }).ExecuteCommand();
        Console.WriteLine("Slave:");
        db.Queryable<Order>().First();
        Console.WriteLine("#### MasterSlave End ####");
    }

    private static void SqlSugarClient()
    {
        //Create db
        Console.WriteLine("#### SqlSugarClient Start ####");
        SqlSugarClient db = new SqlSugarClient(new ConnectionConfig()
        {
            DbType = DbType.Dm,
            ConnectionString = Config.ConnectionString,
            InitKeyType = InitKeyType.Attribute,
            IsAutoCloseConnection = true,
            AopEvents = new AopEvents
            {
                OnLogExecuting = (sql, p) =>
                {
                    Console.WriteLine(sql);
                    Console.WriteLine(string.Join(",", p?.Select(it => it.ParameterName + ":" + it.Value)));
                }
            }
        });

        //If no exist create datebase 
        //db.DbMaintenance.CreateDatabase();

        //Use db query
        var dt = db.Ado.GetDataTable("select 1");

        //Create tables
        db.CodeFirst.InitTables(typeof(OrderItem),typeof(Order));
        var id = db.Insertable(new Order() { Name = "order1", CustomId = 1, Price = 0, CreateTime = DateTime.Now }).ExecuteReturnIdentity();

        //Insert data
        db.Insertable(new OrderItem() { OrderId = id, Price = 0, CreateTime=DateTime.Now }).ExecuteCommand();
        Console.WriteLine("#### SqlSugarClient End ####");

    }

    private static void DbContext()
    {
        Console.WriteLine("");
        Console.WriteLine("#### DbContext Start ####");
        var insertObj = new Order { Name = "jack", CreateTime = DateTime.Now };
        var InsertObjs = new Order[] { insertObj };

        DbContext context = new DbContext();

        context.Db.CodeFirst.InitTables<Order, OrderItem,Custom>();//Create Tables
        ;
        var orderDb = context.OrderDb;

        //Select
        var data1 = orderDb.GetById(1);
        var data2 = orderDb.GetList();
        var data3 = orderDb.GetList(it => it.Id == 1);
        var data4 = orderDb.GetSingle(it => it.Id == 1);
        var p = new PageModel() { PageIndex = 1, PageSize = 2 };
        var data5 = orderDb.GetPageList(it => it.Name == "xx", p);
        Console.Write(p.TotalCount);
        var data6 = orderDb.GetPageList(it => it.Name == "xx", p, it => it.Name, OrderByType.Asc);
        Console.Write(p.TotalCount);
        List<IConditionalModel> conModels = new List<IConditionalModel>();
        conModels.Add(new ConditionalModel() { FieldName = "id", ConditionalType = ConditionalType.Equal, FieldValue = "1", FieldValueConvertFunc=it=>Convert.ToInt32(it) });//id=1
        var data7 = orderDb.GetPageList(conModels, p, it => it.Name, OrderByType.Asc);
        orderDb.AsQueryable().Where(x => x.Id == 1).ToList();

        //Insert
        orderDb.Insert(insertObj);
        orderDb.InsertRange(InsertObjs);
        var id = orderDb.InsertReturnIdentity(insertObj);
        orderDb.AsInsertable(insertObj).ExecuteCommand();


        //Delete
        orderDb.Delete(insertObj);
        orderDb.DeleteById(11111);
        orderDb.DeleteById(new int[] { 1111, 2222 });
        orderDb.Delete(it => it.Id == 1111);
        orderDb.AsDeleteable().Where(it => it.Id == 1111).ExecuteCommand();

        //Update
        orderDb.Update(insertObj);
        orderDb.UpdateRange(InsertObjs);
        orderDb.Update(it => new Order() { Name = "a", }, it => it.Id == 1);
        orderDb.AsUpdateable(insertObj).UpdateColumns(it => new { it.Name }).ExecuteCommand();

        //Use Inherit DbContext
        OrderDal dal = new OrderDal();
        var data = dal.GetById(1);
        var list = dal.GetList();

        Console.WriteLine("#### DbContext End ####");
    }

    private static void CustomAttribute()
    {
        Console.WriteLine("");
        Console.WriteLine("#### Custom Attribute Start ####");
        SqlSugarClient db = new SqlSugarClient(new ConnectionConfig()
        {
            ConnectionString = Config.ConnectionString,
            DbType = DbType.Dm,
            IsAutoCloseConnection = true,
            InitKeyType = InitKeyType.Attribute,
            ConfigureExternalServices = new ConfigureExternalServices()
            {
                EntityService = (property, column) =>
                {

                    var attributes = property.GetCustomAttributes(true);//get all attributes 

                    if (attributes.Any(it => it is KeyAttribute))// by attribute set primarykey
                    {
                        column.IsPrimarykey = true;
                    }
                },
                EntityNameService = (type, entity) =>
                {
                    var attributes = type.GetCustomAttributes(true);
                    if (attributes.Any(it => it is TableAttribute))
                    {
                        entity.DbTableName = (attributes.First(it => it is TableAttribute) as TableAttribute).Name;
                    }
                }
            }
        });
        db.CodeFirst.InitTables<AttributeTable>();//Create Table

        db.Insertable(new AttributeTable() { Id = Guid.NewGuid().ToString(), Name = "Name" }).ExecuteCommand();
        var list = db.Queryable<AttributeTable>().ToList();

        Console.WriteLine("#### Custom Attribute End ####");
    }


    private static void SingletonPattern()
    {
        Console.WriteLine("");
        Console.WriteLine("#### Singleton Pattern Start ####");
        Console.WriteLine("Db_Id:" + singleDb.ContextID);
        Console.WriteLine("Db_Id:" + singleDb.ContextID);
        var task = new Task(() =>
        {
            Console.WriteLine("Task DbId:" + singleDb.ContextID);
            new Task(() =>
            {
                Console.WriteLine("_Task_Task DbId:" + singleDb.ContextID);
                Console.WriteLine("_Task_Task DbId:" + singleDb.ContextID);

            }).Start();
            Console.WriteLine("Task DbId:" + singleDb.ContextID);
        });
        task.Start();
        task.Wait();
        System.Threading.Thread.Sleep(500);
        Console.WriteLine(string.Join(",", singleDb.TempItems.Keys));

        Console.WriteLine("#### Singleton Pattern end ####");
    }

    static SqlSugarScope singleDb = new SqlSugarScope(
        new ConnectionConfig()
        {
            ConfigId = 1,
            DbType = DbType.Dm,
            ConnectionString = Config.ConnectionString,
            InitKeyType = InitKeyType.Attribute,
            IsAutoCloseConnection = true,
            AopEvents = new AopEvents()
            {
                OnLogExecuting = (sql, p) => { Console.WriteLine(sql); }
            }
        });


    private static void DistributedTransactionExample()
    {
        Console.WriteLine("");
        Console.WriteLine("#### Distributed TransactionExample Start ####");
        SqlSugarClient db = new SqlSugarClient(new List<ConnectionConfig>()
        {
            new ConnectionConfig(){ ConfigId="1", DbType=DbType.Dm, ConnectionString=Config.ConnectionString,InitKeyType=InitKeyType.Attribute,IsAutoCloseConnection=true },
            new ConnectionConfig(){ ConfigId="2", DbType=DbType.Dm, ConnectionString=Config.ConnectionString2 ,InitKeyType=InitKeyType.Attribute ,IsAutoCloseConnection=true}
        });

        //use db1
        db.CodeFirst.SetStringDefaultLength(200).InitTables(typeof(Order), typeof(OrderItem));//
        db.Insertable(new Order() { Name = "order1", CreateTime = DateTime.Now }).ExecuteCommand();
        Console.WriteLine(db.CurrentConnectionConfig.DbType + ":" + db.Queryable<Order>().Count());

        //use db2
        db.ChangeDatabase("2");
        //db.DbMaintenance.CreateDatabase();//Create Database2
        db.CodeFirst.SetStringDefaultLength(200).InitTables(typeof(Order), typeof(OrderItem));
        db.Insertable(new Order() { Name = "order1", CreateTime = DateTime.Now }).ExecuteCommand();
        Console.WriteLine(db.CurrentConnectionConfig.DbType + ":" + db.Queryable<Order>().Count());

        // Example 1
        Console.WriteLine("Example 1");
        try
        {
            db.BeginTran();

            db.ChangeDatabase("1");//use db1
            db.Deleteable<Order>().ExecuteCommand();
            Console.WriteLine("---Delete all " + db.CurrentConnectionConfig.DbType);
            Console.WriteLine(db.Queryable<Order>().Count());

            db.ChangeDatabase("2");//use db2
            db.Deleteable<Order>().ExecuteCommand();
            Console.WriteLine("---Delete all " + db.CurrentConnectionConfig.DbType);
            Console.WriteLine(db.Queryable<Order>().Count());

            throw new Exception();
            db.CommitTran();
        }
        catch
        {
            db.RollbackTran();
            Console.WriteLine("---Roll back");
            db.ChangeDatabase("1");//use db1
            Console.WriteLine(db.CurrentConnectionConfig.DbType);
            Console.WriteLine(db.Queryable<Order>().Count());

            db.ChangeDatabase("2");//use db2
            Console.WriteLine(db.CurrentConnectionConfig.DbType);
            Console.WriteLine(db.Queryable<Order>().Count());
        }



        // Example 2
        Console.WriteLine("Example 2");

        var result=db.UseTran(() =>
        {

            db.ChangeDatabase("1");//use db1
            db.Deleteable<Order>().ExecuteCommand();
            Console.WriteLine("---Delete all " + db.CurrentConnectionConfig.DbType);
            Console.WriteLine(db.Queryable<Order>().Count());

            db.ChangeDatabase("2");//use db2
            db.Deleteable<Order>().ExecuteCommand();
            Console.WriteLine("---Delete all " + db.CurrentConnectionConfig.DbType);
            Console.WriteLine(db.Queryable<Order>().Count());
            throw new Exception("");

        });
        if (result.IsSuccess == false) {
            Console.WriteLine("---Roll back");
            db.ChangeDatabase("1");//use db1
            Console.WriteLine(db.CurrentConnectionConfig.DbType);
            Console.WriteLine(db.Queryable<Order>().Count());

            db.ChangeDatabase("2");//use db2
            Console.WriteLine(db.CurrentConnectionConfig.DbType);
            Console.WriteLine(db.Queryable<Order>().Count());
        }

        // Example 3
        Console.WriteLine("Example 3");

        var result2 = db.UseTranAsync(() =>
        {

            db.ChangeDatabase("1");//use db1
            db.Deleteable<Order>().ExecuteCommand();
            Console.WriteLine("---Delete all " + db.CurrentConnectionConfig.DbType);
            Console.WriteLine(db.Queryable<Order>().Count());

            db.ChangeDatabase("2");//use db2
            db.Deleteable<Order>().ExecuteCommand();
            Console.WriteLine("---Delete all " + db.CurrentConnectionConfig.DbType);
            Console.WriteLine(db.Queryable<Order>().Count());
            throw new Exception("");

        });
        result2.Wait();
        if (result.IsSuccess == false)
        {
            Console.WriteLine("---Roll back");
            db.ChangeDatabase("1");//use sqlserver
            Console.WriteLine(db.CurrentConnectionConfig.DbType);
            Console.WriteLine(db.Queryable<Order>().Count());

            db.ChangeDatabase("2");//use mysql
            Console.WriteLine(db.CurrentConnectionConfig.DbType);
            Console.WriteLine(db.Queryable<Order>().Count());
        }

        Console.WriteLine("#### Distributed TransactionExample End ####");
    }
}

/// <summary>
/// DbContext Example 1
/// </summary>
public class DbContext
{

    public SqlSugarClient Db;
    public DbContext()
    {
        Db = new SqlSugarClient(new ConnectionConfig()
        {
            ConnectionString = Config.ConnectionString,
            DbType = DbType.Dm,
            IsAutoCloseConnection = true,
            InitKeyType = InitKeyType.Attribute,
            AopEvents = new AopEvents()
            {
                OnLogExecuting = (sql, p) =>
                {
                    Console.WriteLine(sql);
                }
            }
        });
    }
    public SimpleClient<Order> OrderDb => new SimpleClient<Order>(Db);
    public SimpleClient<OrderItem> OrderItemDb => new SimpleClient<OrderItem>(Db);
}


public class OrderDal : DbContext<Order>
{

}
/// <summary>
/// DbContext  Example 2
/// </summary>
/// <typeparam name="T"></typeparam>
public class DbContext<T> where T : class, new()
{

    public SqlSugarClient Db;
    public DbContext()
    {
        Db = new SqlSugarClient(new ConnectionConfig()
        {
            ConnectionString = Config.ConnectionString,
            DbType = DbType.Dm,
            IsAutoCloseConnection = true,
            InitKeyType = InitKeyType.Attribute,
            AopEvents = new AopEvents()
            {
                OnLogExecuting = (sql, p) =>
                {
                    Console.WriteLine(sql);
                }
            }
        });
    }
    public SimpleClient<T> CurrentDb => new SimpleClient<T>(Db);
    public virtual T GetById(int id)
    {
        return CurrentDb.GetById(id);
    }
    public virtual List<T> GetList()
    {
        return CurrentDb.GetList();
    }
    public virtual bool Delete(int id)
    {
        return CurrentDb.DeleteById(id);
    }
}

}

本栏目推荐文章
• Echarts demo
• OPEN SQL取单条数据DEMO
• sqlsugar在使用mysql时，Dbfirst and Codefirst requires system table permissions
• hbase java api demo
• Quartz Demo 任务调度程序简单Demo
• Linux驱动开发笔记（六）：用户层与内核层进行数据传递的原理和Demo
• flink入门1-Flink使用socketTextStream对接netcat完成入门demo
• ASP.NET Core Razor融合JS库Demo
• usb转串口的通信demo
• svelte的一些基础demo

sqlsugar demosqlsugar demo sqlsugar sqlsugar笔记 sqlsugar建议 sqlsugar-c sqlsugar方法 sqlsugar命令wpf sqlsugar数据 sqlsugar mvvm wpf permissions codefirst sqlsugar requires