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 ####");
}
