public static void Init() { Console.WriteLine(""); Console.WriteLine("#### Insertable Start ####"); SqlSugarClient db = new SqlSugarClient(new ConnectionConfig() { DbType = DbType.MySqlConnector, 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))); } } }); db.CodeFirst.InitTables<TestFAST111>(); db.Fastest<TestFAST111>().BulkCopy(new List<TestFAST111>() { new TestFAST111(){ Date=DateTime.Now, Id=Guid.NewGuid()+"", Sex=1 , X=111,json=new string[]{ "x"} } }); var data = new List<TestFAST111>() { new TestFAST111(){ Date=DateTime.Now, Id=Guid.NewGuid()+"", Sex=2 , X=112,json=new string[]{ "x"} } }; //db.Updateable(data).ExecuteCommand(); db.Fastest<TestFAST111>().BulkUpdate(data); var x = db.Queryable<TestFAST111>().ToList(); }
public static void Init() { Console.WriteLine(""); Console.WriteLine("#### Insertable Start ####"); SqlSugarClient db = new SqlSugarClient(new ConnectionConfig() { DbType = DbType.Oracle, 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))); } } }); //db.DbMaintenance.DropTable("TESTFAST11"); //db.DbMaintenance.TruncateTable<TestFAST11>(); db.CodeFirst.InitTables <TestFAST11>(); //db.Insertable<TestFAST11>(new List<TestFAST11>() { // new TestFAST11(){ Date=DateTime.Now, Id=Guid.NewGuid()+"", Sex=1 , } //}).UseOracle().ExecuteBulkCopy(); //db.Fastest<TestFAST11>().BulkCopy(new List<TestFAST11>() { // new TestFAST11(){ Date=DateTime.Now, Id=Guid.NewGuid()+"", Sex=1 , } //}); //db.Updateable(data).ExecuteCommand(); for (int i = 0; i < 2; i++) { db.Fastest <TestFAST11>().BulkCopy(new List <TestFAST11> { new TestFAST11() { Date = DateTime.Now, Id = Guid.NewGuid() + "", Sex = 1 } }); var x = db.Queryable <TestFAST11>().ToList(); var updaterows = db.Fastest <TestFAST11>().BulkUpdate(x); } }
public static void Init() { Console.WriteLine(""); Console.WriteLine("#### Insertable Start ####"); SqlSugarClient db = new SqlSugarClient(new ConnectionConfig() { DbType = DbType.MySqlConnector, 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))); } } }); var insertObj = new Order() { Id = 1, Name = "order1", Price = 0 }; var updateObjs = new List <Order> { new Order() { Id = 11, Name = "order11", Price = 0 }, new Order() { Id = 12, Name = "order12", Price = 0 } }; var x = db.Insertable(updateObjs).RemoveDataCache().IgnoreColumns(it => it.CreateTime).UseParameter().ExecuteCommand(); //Ignore CreateTime db.Insertable(insertObj).IgnoreColumns(it => new { it.CreateTime }).ExecuteReturnIdentity();//get identity db.Insertable(insertObj).IgnoreColumns("CreateTime").ExecuteReturnIdentity(); //Only insert Name and Price db.Insertable(insertObj).InsertColumns(it => new { it.Name, it.Price }).ExecuteReturnIdentity(); db.Insertable(insertObj).InsertColumns("Name", "Price").ExecuteReturnIdentity(); //ignore null columns db.Insertable(updateObjs).ExecuteCommand();//get change row count //Use Lock db.Insertable(insertObj).With(SqlWith.UpdLock).ExecuteCommand(); db.Deleteable <Order>().ExecuteCommand(); //db.Insertable(new Order() //{ // CreateTime = DateTime.Now, // CustomId = 11, // Name = "11", // Price = 11 //}).UseMySql().ExecuteBulkCopy(); //db.Insertable(new OrderItem() //{ // CreateTime = DateTime.Now, // ItemId = 1, // OrderId = 1, // OrderName = "a", // Price = 11 //}).UseMySql().ExecuteBulkCopy(); var data = db.Queryable <Order>().ToList(); db.Insertable(data).UseMySql().ExecuteBulkCopy(); db.Fastest <Order>().BulkUpdate(data); Console.WriteLine("#### Insertable End ####"); }
public static void Init() { Console.WriteLine(""); Console.WriteLine("#### Insertable Start ####"); SqlSugarClient db = new SqlSugarClient(new ConnectionConfig() { DbType = DbType.SqlServer, 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))); } } }); var insertObj = new Order() { Id = 1, Name = "order1", Price = 0 }; var insertObjs = new List <Order> { new Order() { Id = 11, Name = "XX", Price = 0 }, new Order() { Id = 12, Name = "XX2", Price = 0 } }; var x = db.Insertable(insertObjs).RemoveDataCache().IgnoreColumns(it => it.CreateTime).UseParameter().ExecuteCommand(); //Ignore CreateTime db.Insertable(insertObj).IgnoreColumns(it => new { it.CreateTime }).ExecuteReturnIdentity();//get identity db.Insertable(insertObj).IgnoreColumns("CreateTime").ExecuteReturnIdentity(); //Only insert Name and Price db.Insertable(insertObj).InsertColumns(it => new { it.Name, it.Price }).ExecuteReturnIdentity(); db.Insertable(insertObj).InsertColumns("Name", "Price").ExecuteReturnIdentity(); //ignore null columns db.Insertable(insertObjs).ExecuteCommand();//get change row count //Use Lock db.Insertable(insertObj).With(SqlWith.UpdLock).ExecuteCommand(); insertObjs = new List <Order> { new Order() { Id = 11, Name = "order11", Price = 1 }, new Order() { Id = 12, Name = "order12", Price = 20, CreateTime = DateTime.Now, CustomId = 1 } }; db.Insertable(insertObjs).UseSqlServer().ExecuteBulkCopy(); var dt = db.Queryable <Order>().Take(5).ToDataTable(); dt.TableName = "Order"; db.Insertable(dt).UseSqlServer().ExecuteBulkCopy(); db.CodeFirst.InitTables <RootTable0, TwoItem, TwoItem2, TwoItem3>(); db.CodeFirst.InitTables <ThreeItem2>(); db.DbMaintenance.TruncateTable("RootTable0"); db.DbMaintenance.TruncateTable("TwoItem"); db.DbMaintenance.TruncateTable("TwoItem2"); db.DbMaintenance.TruncateTable("TwoItem3"); db.DbMaintenance.TruncateTable("ThreeItem2"); Console.WriteLine("SubInsert Start"); db.Insertable(new Order() { Name = "订单 1", CustomId = 1, Price = 100, CreateTime = DateTime.Now, Id = 0, Items = new List <OrderItem>() { new OrderItem() { CreateTime = DateTime.Now, OrderId = 0, Price = 1, ItemId = 1 }, new OrderItem() { CreateTime = DateTime.Now, OrderId = 0, Price = 2, ItemId = 2 } } }) .AddSubList(it => it.Items.First().OrderId).ExecuteCommand(); db.Insertable(new List <RootTable0>() { new RootTable0() { Name = "aa", TwoItem2 = new TwoItem2() { Id = "1", ThreeItem2 = new List <ThreeItem2>() { new ThreeItem2() { Name = "a", TwoItem2Id = "1" }, new ThreeItem2() { Id = 2, Name = "a2", TwoItem2Id = "2" } } }, TwoItem = new TwoItem() { Name = "itema", RootId = 2 }, TwoItem3 = new List <TwoItem3>() { new TwoItem3() { Id = 0, Name = "a", Desc = "" }, } }, new RootTable0() { Name = "bb", TwoItem2 = new TwoItem2() { Id = "2" }, TwoItem = new TwoItem() { Name = "itemb", RootId = 2, }, TwoItem3 = new List <TwoItem3>() { new TwoItem3() { Id = 1, Name = "b", Desc = "" }, new TwoItem3() { Id = 2, Name = "b1", Desc = "1" }, } } }) .AddSubList(it => it.TwoItem.RootId) .AddSubList(it => new SubInsertTree() { Expression = it.TwoItem2.RootId, ChildExpression = new List <SubInsertTree>() { new SubInsertTree() { Expression = it.TwoItem2.ThreeItem2.First().TwoItem2Id } } }) .AddSubList(it => it.TwoItem3) .ExecuteCommand(); SubNoIdentity(db); SubIdentity(db); var dict = new Dictionary <string, object>(); dict.Add("name", "1"); dict.Add("CreateTime", DateTime.Now); dict.Add("Price", 1); db.Insertable(dict).AS("[Order]").ExecuteCommand(); db.Fastest <Order>().BulkCopy(insertObjs); Console.WriteLine("#### Insertable End ####"); }
public static void Init() { Console.WriteLine(""); Console.WriteLine("#### CodeFirst Start ####"); SqlSugarClient db = new SqlSugarClient(new ConnectionConfig() { DbType = DbType.SqlServer, ConnectionString = Config.ConnectionString, InitKeyType = InitKeyType.Attribute, IsAutoCloseConnection = true }); db.Aop.OnLogExecuted = (s, p) => { Console.WriteLine(s); Console.WriteLine(string.Join(",", p?.Select(it => it.ParameterName + ":" + it.Value))); }; //初始化分表 db.CodeFirst.SplitTables().InitTables <OrderSpliteTest>(); Console.WriteLine(); //根据最近3个表进行查询 var list = db.Queryable <OrderSpliteTest>() .SplitTable(DateTime.Now.Date.AddYears(-1), DateTime.Now) .ToList(); Console.WriteLine(); //根据时间选出的表进行查询 var list2 = db.Queryable <OrderSpliteTest>().SplitTable(tabs => tabs.Where(it => it.Date >= DateTime.Now.AddYears(-2))).ToList(); Console.WriteLine(); //删除数据只在最近3张表执行操作 var x = db.Deleteable <OrderSpliteTest>().Where(it => it.Pk == Guid.NewGuid()).SplitTable(tabs => tabs.Take(3)).ExecuteCommand(); Console.WriteLine(); var tableName = db.SplitHelper <OrderSpliteTest>().GetTableName(DateTime.Now.AddDays(-111)); var listNull = db.Queryable <OrderSpliteTest>().SplitTable(ta => ta.InTableNames(tableName)).ToList(); var tableName2 = db.SplitHelper(new OrderSpliteTest() { Time = DateTime.Now }).GetTableNames(); var tableName3 = db.SplitHelper(new List <OrderSpliteTest> { new OrderSpliteTest() { Time = DateTime.Now }, new OrderSpliteTest() { Time = DateTime.Now }, new OrderSpliteTest() { Time = DateTime.Now.AddMonths(-10) } }).GetTableNames(); var x2 = db.Updateable <OrderSpliteTest>() .SetColumns(it => it.Name == "a") .Where(it => it.Pk == Guid.NewGuid()) .SplitTable(tabs => tabs.InTableNames(tableName2)) .ExecuteCommand(); Console.WriteLine(); //按日分表 var x3 = db.Insertable(new OrderSpliteTest() { Name = "A" }).SplitTable().ExecuteCommand(); Console.WriteLine(); ////强制分表类型 var x4 = db.Insertable(new OrderSpliteTest() { Name = "A", Time = DateTime.Now.AddDays(-1) }).SplitTable().ExecuteCommand(); //分表支持BulkCopy db.Fastest <OrderSpliteTest>().SplitTable().BulkCopy(new List <OrderSpliteTest> { new OrderSpliteTest() { Pk = Guid.NewGuid(), Name = "a", Time = DateTime.Now }, new OrderSpliteTest() { Pk = Guid.NewGuid(), Name = "a", Time = DateTime.Now }, new OrderSpliteTest() { Pk = Guid.NewGuid(), Name = "a", Time = DateTime.Now.AddMonths(-10) } }); db.Fastest <OrderSpliteTest>().SplitTable().BulkUpdate(db.Queryable <OrderSpliteTest>().SplitTable(it => it).ToList()); db.Fastest <OrderSpliteTest>().SplitTable().BulkUpdate(db.Queryable <OrderSpliteTest>().SplitTable(it => it).ToList(), new string[] { "pk" }, new string[] { "name" }); Console.WriteLine("#### CodeFirst end ####"); }
public static void Init() { Console.WriteLine(""); Console.WriteLine("#### Updateable Start ####"); SqlSugarClient db = new SqlSugarClient(new ConnectionConfig() { DbType = DbType.SqlServer, 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))); } } }); /*** 1.entity or List ***/ var updateObj = new Order() { Id = 1, Name = "order1" }; var updateObjs = new List <Order> { new Order() { Id = 11, Name = "order11" }, new Order() { Id = 12, Name = "order12" } }; //update all columns by primary key var result = db.Updateable(updateObj).ExecuteCommand(); //update single var result2 = db.Updateable(updateObjs).ExecuteCommand(); //update List<Class> //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 /*** 2.by expression ***/ //update name,createtime var result7 = db.Updateable <Order>(it => new Order() { Name = "a", CreateTime = DateTime.Now }).Where(it => it.Id == 11).ExecuteCommand(); var result71 = db.Updateable <Order>().SetColumns(it => new Order() { Name = "a", CreateTime = DateTime.Now }).Where(it => it.Id == 11).ExecuteCommand(); //only update name var result8 = db.Updateable <Order>(it => it.Name == "Name" + "1").Where(it => it.Id == 1).ExecuteCommand(); var result81 = db.Updateable <Order>().SetColumns(it => it.Name == "Name" + "1").Where(it => it.Id == 1).ExecuteCommand(); // /*** 3.by Dictionary ***/ var dt = new Dictionary <string, object>(); dt.Add("id", 1); dt.Add("name", "abc"); dt.Add("createTime", DateTime.Now); var dtList = new List <Dictionary <string, object> >(); dtList.Add(dt); var t66 = db.Updateable(dt).AS("[Order]").WhereColumns("id").ExecuteCommand(); var t666 = db.Updateable(dtList).AS("[Order]").WhereColumns("id").ExecuteCommand(); /*** 4.Other instructions ***/ var caseValue = "1"; //Do not update NULL columns db.Updateable(updateObj).IgnoreColumns(ignoreAllNullColumns: true).ExecuteCommand(); //if 1 update name else if 2 update name,createtime db.Updateable(updateObj) .UpdateColumnsIF(caseValue == "1", it => new { it.Name }) .UpdateColumnsIF(caseValue == "2", it => new { it.Name, it.CreateTime }) .ExecuteCommand(); //Use Lock db.Updateable(updateObj).With(SqlWith.UpdLock).ExecuteCommand(); //Where Sql //db.Updateable(updateObj).Where("id=@x", new { x = "1" }).ExecuteCommand(); var levelCode = "123213123131321"; db.Updateable <Order>(a => a.Name == "a") .Where(a => SqlFunc.StartsWith(a.Name, levelCode)) .AddQueue(); db.SaveQueues(); db.Fastest <Order>().BulkCopy(updateObjs); Console.WriteLine("#### Updateable End ####"); }