public static void Json()
{
Db.CodeFirst.InitTables <UnitJsonTest>();
Db.CodeFirst.InitTables <UnitJsonTest2>();
Db.DbMaintenance.TruncateTable <UnitJsonTest>();
Db.Insertable(new UnitJsonTest()
{
Order = new Order {
Id = 1, Name = "order1"
}
}).ExecuteCommand();
var list = Db.Queryable <UnitJsonTest>().ToList();
UValidate.Check("order1", list.First().Order.Name, "Json");
Db.Updateable(new UnitJsonTest()
{
Id = 1, Order = new Order {
Id = 2, Name = "order2"
}
}).ExecuteCommand();
list = Db.Queryable <UnitJsonTest>().ToList();
UValidate.Check("order2", list.First().Order.Name, "Json");
var list2 = Db.Queryable <UnitJsonTest>().ToList();
var x = new Order()
{
Name = "a"
};
Db.Updateable <UnitJsonTest2>()
.SetColumns(it => it.Name == "a")
.Where(it => it.Id == 1)
.ExecuteCommand();
}
public static void Updateable()
{
Db.CodeFirst.InitTables(typeof(UnitUser));
Db.DbMaintenance.TruncateTable <UnitUser>();
Db.Insertable(new UnitUser()
{
USER_ID = 1, USER_ACCOUNT = "a", USER_PWD = "b", USER_NAME = "c", PWD_LASTCHTIME = DateTime.Now, PWD_ERRORCOUNT = 1, PWD_LASTERRTIME = DateTime.Now
}).ExecuteCommand();
Db.Updateable(new UnitUser()
{
USER_ID = 1, PWD_LASTERRTIME = null
}).WhereColumns(it => new{ it.PWD_ERRORCOUNT, it.PWD_LASTERRTIME }).ExecuteCommand();
Db.CodeFirst.InitTables(typeof(UnitBoolTest));
var x = new UnitBoolTest();
//Db.Updateable<UnitBoolTest>().SetColumns(it => new UnitBoolTest() { BoolValue = !it.BoolValue }).Where(it=>it.Id==1).ExecuteCommand();
//Db.Updateable<UnitBoolTest>().SetColumns(it => it.BoolValue == !it.BoolValue ).Where(it=>it.Id==1).ExecuteCommand();
Db.Updateable <UnitBoolTest>().SetColumns(it => new UnitBoolTest()
{
BoolValue = x.BoolValue
}).Where(it => it.Id == 1).ExecuteCommand();
Db.Updateable <UnitBoolTest>().SetColumns(it => it.BoolValue == x.BoolValue).Where(it => it.Id == 1).ExecuteCommand();
Db.Updateable <UnitBoolTest>().SetColumns(it => new UnitBoolTest()
{
BoolValue = !x.BoolValue
}).Where(it => it.Id == 1).ExecuteCommand();
//Db.Updateable<UnitBoolTest>().SetColumns(it => it.BoolValue == !x.BoolValue).Where(it => it.Id == 1).ExecuteCommand();
Db.Updateable <UnitBoolTest>(x).ReSetValue(it => it.BoolValue == it.BoolValue).ExecuteCommand();
Db.Updateable <UnitBoolTest>(x).ReSetValue(it => it.BoolValue == true).ExecuteCommand();
//Db.Updateable<UnitBoolTest>(x).ReSetValue(it => it.BoolValue == !it.BoolValue).ExecuteCommand();
Db.Updateable <UnitBoolTest>(x).UpdateColumns(it => new { it.BoolValue }).ExecuteCommand();
UnitSaveDiary saveDiary = new UnitSaveDiary();
saveDiary.ID = 2;
saveDiary.TypeID = 10;
saveDiary.TypeName = "类型100";
saveDiary.Title = "标题1000";
saveDiary.Content = "内容";
saveDiary.Time = DateTime.Now;
saveDiary.IsRemind = false;//无论传false/true 最终执行的结果都是以true执行的
var sql = Db.Updateable <UnitDiary>().SetColumns(it => new UnitDiary()
{
IsRemind = saveDiary.IsRemind,
}).Where(it => it.ID == saveDiary.ID).ToSql();
UValidate.Check(sql.Key, @"UPDATE ""diary"" SET
""isremind"" = @Const0 WHERE ( ""id"" = @ID1 )", "Updateable");
sql = Db.Updateable <UnitDiary>().SetColumns(it => new UnitDiary()
{
TypeID = saveDiary.TypeID,
}).Where(it => it.ID == saveDiary.ID).ToSql();
UValidate.Check(sql.Key, @"UPDATE ""diary"" SET
""typeid"" = @Const0 WHERE ( ""id"" = @ID1 )", "Updateable");
}
public static void Json()
{
Db.CodeFirst.InitTables <UnitJsonTest>();
Db.DbMaintenance.TruncateTable <UnitJsonTest>();
Db.Insertable(new UnitJsonTest()
{
Order = new Order {
Id = 1, Name = "order1"
}
}).ExecuteCommand();
var list = Db.Queryable <UnitJsonTest>().ToList();
UValidate.Check("order1", list.First().Order.Name, "Json");
Db.Updateable(new UnitJsonTest()
{
Id = Db.Queryable <UnitJsonTest>().First().Id, Order = new Order {
Id = 2, Name = "order2"
}
}).ExecuteCommand();
list = Db.Queryable <UnitJsonTest>().ToList();
UValidate.Check("order2", list.First().Order.Name, "Json");
var list2 = Db.Queryable <UnitJsonTest>().ToList();
Db.CodeFirst.InitTables <UnitArray2>();
Db.Insertable(new UnitArray2()
{
MenuIds = new float[] { 1, 2 }
}).ExecuteCommand();
var x = Db.Queryable <UnitArray2>().ToList();
}
private static void q3()
{
RefAsync <int> total = 0;
var count = Db.Queryable <Order>().Count();
Task t = Db.Queryable <Order>().ToJsonPageAsync(1, 2, total);
t.Wait();
UValidate.Check(count, total.Value, "QueryableAsync");
}
public static void Json()
{
Db.CodeFirst.InitTables <UnitJsonTest>();
Db.DbMaintenance.TruncateTable <UnitJsonTest>();
Db.Insertable(new UnitJsonTest()
{
Order = new Order {
Id = 1, Name = "order1"
}
}).ExecuteCommand();
var list = Db.Queryable <UnitJsonTest>().ToList();
UValidate.Check("order1", list.First().Order.Name, "Json");
Db.Updateable(new UnitJsonTest()
{
Id = Db.Queryable <UnitJsonTest>().First().Id, Order = new Order {
Id = 2, Name = "order2"
}
}).ExecuteCommand();
list = Db.Queryable <UnitJsonTest>().ToList();
UValidate.Check("order2", list.First().Order.Name, "Json");
var list2 = Db.Queryable <UnitJsonTest>().ToList();
Db.CodeFirst.InitTables <UnitArray2>();
Db.Insertable(new UnitArray2()
{
MenuIds = new float[] { 1, 2 }
}).ExecuteCommand();
var x = Db.Queryable <UnitArray2>().ToList();
Db.CodeFirst.InitTables <UnitArray311>();
Db.Insertable(new UnitArray311()
{
Text = new string[] { "a", "a" }
}).ExecuteCommand();
Db.Updateable(new List <UnitArray311> {
new UnitArray311()
{
Text = new string[] { "a12", "a2" },
Id = 1
},
new UnitArray311()
{
Text = new string[] { "a1", "a1" },
Id = 2
}
}).ExecuteCommand();
var xxx = Db.Queryable <UnitArray311>().ToList();
Db.CodeFirst.InitTables <UnitUUID1XX>();
Db.Insertable(new UnitUUID1XX()
{
ID = Guid.NewGuid()
}).ExecuteCommand();
var x1 = Db.Queryable <UnitUUID1XX>().ToList();
}
public static void Ado()
{
var task1 = Db.Ado.GetScalarAsync("select 1");
task1.Wait();
UValidate.Check(1, task1.Result, "ado");
var task2 = Db.Ado.GetIntAsync("select 2");
task2.Wait();
UValidate.Check(2, task2.Result, "ado");
var task3 = Db.Ado.GetLongAsync("select 3");
task3.Wait();
UValidate.Check(3, task3.Result, "ado");
var task4 = Db.Ado.GetDataTableAsync("select 4 as id");
task4.Wait();
UValidate.Check(4, task4.Result.Rows[0]["id"], "ado");
var task5 = Db.Ado.GetInt("select @id as id", new { id = 5 });
UValidate.Check(5, task5, "ado");
var task6 = Db.Ado.SqlQuery <dynamic>("select @id as id", new { id = 5 });
UValidate.Check(5, task6[0].id, "ado");
var task7 = Db.Ado.SqlQueryAsync <dynamic>("select @id as id", new { id = 7 });
task7.Wait();
UValidate.Check(7, task7.Result[0].id, "ado");
var task8 = Db.Ado.SqlQueryAsync <dynamic>("select 8 as id");
task8.Wait();
UValidate.Check(8, task8.Result[0].id, "ado");
var task9 = Db.Ado.SqlQuery <Order, OrderItem>("select * from `order`;select * from OrderDetail");
var task10 = Db.Ado.SqlQueryAsync <Order, OrderItem>("select * from `order`;select * from OrderDetail");
task10.Wait();
}
private static void test1()
{
var db = Db;
db.AddQueue("select 11");
db.Queryable <Order>().Where(it => false).AddQueue();
db.AddQueue("select 12");
var list = db.SaveQueues <int, Order, int>();
UValidate.Check(list.Item1[0], "11", "Queue");
UValidate.Check(list.Item2.Count(), 0, "Queue");
UValidate.Check(list.Item3[0], "12", "Queue");
}
public static void Init()
{
var db = NewUnitTest.Db;
var sql = db.Queryable <Order, OrderItem, Custom>((o, i, c) => new JoinQueryInfos(
JoinType.Left, o.Id == i.OrderId,
JoinType.Left, o.CustomId == c.Id
))
.Select <ViewModel>().ToSql().Key;
UValidate.Check(sql, @"SELECT o.[Name] AS [Name],o.[Price] AS [Price],i.[OrderId] AS [OrderItemOrderId],i.[Price] AS [OrderItemPrice],c.[Name] AS [CustomName] FROM [Order] o Left JOIN [OrderDetail] i ON ( [o].[Id] = [i].[OrderId] ) Left JOIN [Custom] c ON ( [o].[CustomId] = [c].[Id] )", "unit");
var list =
db.Queryable <Order, OrderItem, Custom>((o, i, c) => new JoinQueryInfos(
JoinType.Left, o.Id == i.OrderId,
JoinType.Left, o.CustomId == c.Id
))
.Where((o, i, c) => i.ItemId > 0 && c.Id > 0)
.Select <ViewModel>().ToList();
}
public static void Json()
{
Db.CodeFirst.InitTables <UnitJsonTest>();
Db.DbMaintenance.TruncateTable <UnitJsonTest>();
var id = Db.Insertable(new UnitJsonTest()
{
Order = new Order {
Id = 1, Name = "order1"
}
}).ExecuteReturnIdentity();
var list = Db.Queryable <UnitJsonTest>().ToList();
UValidate.Check("order1", list.First().Order.Name, "Json");
Db.Updateable(new UnitJsonTest()
{
Id = id, Order = new Order {
Id = 2, Name = "order2"
}
}).ExecuteCommand();
list = Db.Queryable <UnitJsonTest>().ToList();
UValidate.Check("order2", list.First().Order.Name, "Json");
var list2 = Db.Queryable <UnitJsonTest>().ToList();
}
public static void Filter()
{
var db = Db;
db.QueryFilter.Add(new SqlSugar.TableFilterItem <UnitFilterClass1>(it => it.id > 0));
var s1 = db.Queryable <Order>().ToSql();
if (s1.Key.Contains(">"))
{
throw new Exception("UnitFilter");
}
var s4 = db.Queryable <Order, OrderItem>((o, i) => i.OrderId == o.Id).Select("o.*").ToSql();
if (s4.Key.Contains(">"))
{
throw new Exception("UnitFilter");
}
var s5 = db.Queryable <Order, OrderItem>((o, i) => new JoinQueryInfos(JoinType.Left, o.Id == i.OrderId))
.Select("o.*").ToSql();
if (s5.Key.Contains(">"))
{
throw new Exception("UnitFilter");
}
var s2 = db.Queryable <UnitFilterClass1>().ToSql();
UValidate.Check(s2.Key,
@"SELECT [id],[name] FROM [UnitFilterClass1] WHERE ( [id] > @id0 )", "UnitFilter");
var s3 = db.Queryable <UnitFilterClass1>().Where(it => it.name != null).ToSql();
UValidate.Check(s3.Key,
@"SELECT [id],[name] FROM [UnitFilterClass1] WHERE ( [name] IS NOT NULL ) AND ( [id] > @id1 )", "UnitFilter");
var s6 = db.Queryable <UnitFilterClass1, UnitFilterClass2>((o, i) => i.id == o.id).Select("o.*").ToSql();
UValidate.Check(s6.Key, "SELECT o.* FROM [UnitFilterClass1] o ,[UnitFilterClass2] i WHERE ( [i].[id] = [o].[id] ) AND ( [o].[id] > @id0 )", "UnitFilter");
var s7 = db.Queryable <UnitFilterClass1, UnitFilterClass2>((o, i) => i.id == o.id).Where(o => o.id == 1).Select("o.*").ToSql();
UValidate.Check(s7.Key, "SELECT o.* FROM [UnitFilterClass1] o ,[UnitFilterClass2] i WHERE ( [i].[id] = [o].[id] ) AND ( [o].[id] = @id0 ) AND ( [o].[id] > @id1 )", "UnitFilter");
var s8 = db.Queryable <UnitFilterClass2, UnitFilterClass1>((o, i) => i.id == o.id).Where(o => o.id == 1).Select("o.*").ToSql();
UValidate.Check(s8.Key, "SELECT o.* FROM [UnitFilterClass2] o ,[UnitFilterClass1] i WHERE ( [i].[id] = [o].[id] ) AND ( [o].[id] = @id0 ) AND ( [i].[id] > @id1 )", "UnitFilter");
var s9 = db.Queryable <UnitFilterClass1, UnitFilterClass2>((o, i) => new JoinQueryInfos(JoinType.Left, o.id == i.id))
.Select("o.*").ToSql();
UValidate.Check(s9.Key, "SELECT o.* FROM [UnitFilterClass1] o Left JOIN [UnitFilterClass2] i ON ( [o].[id] = [i].[id] ) WHERE ( [o].[id] > @id0 )", "UnitFilter");
var s10 = db.Queryable <UnitFilterClass1, UnitFilterClass2>((o, i) => new JoinQueryInfos(JoinType.Left, o.id == i.id))
.Where((o, i) => i.id == 0).Select("o.*").ToSql();
UValidate.Check(s10.Key, "SELECT o.* FROM [UnitFilterClass1] o Left JOIN [UnitFilterClass2] i ON ( [o].[id] = [i].[id] ) WHERE ( [i].[id] = @id0 ) AND ( [o].[id] > @id1 )", "UnitFilter");
var s11 = db.Queryable <UnitFilterClass2, UnitFilterClass1>((o, i) => new JoinQueryInfos(JoinType.Left, o.id == i.id))
.Select("o.*").ToSql();
UValidate.Check(s11.Key, "SELECT o.* FROM [UnitFilterClass2] o Left JOIN [UnitFilterClass1] i ON ( [o].[id] = [i].[id] ) WHERE ( [i].[id] > @id0 )", "UnitFilter");
db.QueryFilter.Add(new SqlSugar.TableFilterItem <UnitFilterClass2>(it => it.id == 0));
var s12 = db.Queryable <UnitFilterClass2, UnitFilterClass1>((o, i) => new JoinQueryInfos(JoinType.Left, o.id == i.id))
.Select("o.*").ToSql();
UValidate.Check(s12.Key, "SELECT o.* FROM [UnitFilterClass2] o Left JOIN [UnitFilterClass1] i ON ( [o].[id] = [i].[id] ) WHERE ( [i].[id] > @id0 ) AND ( [o].[id] = @id1 )", "UnitFilter");
var s13 = db.Queryable <UnitFilterClass2, UnitFilterClass1>((o, i) => new JoinQueryInfos(JoinType.Left, o.id == i.id))
.Where(o => o.name == "")
.Select("o.*").ToSql();
UValidate.Check(s13.Key, "SELECT o.* FROM [UnitFilterClass2] o Left JOIN [UnitFilterClass1] i ON ( [o].[id] = [i].[id] ) WHERE ( [o].[name] = @name0 ) AND ( [i].[id] > @id1 ) AND ( [o].[id] = @id2 )", "UnitFilter");
db.QueryFilter.Clear();
db.QueryFilter.Add(new SqlFilterItem()
{
FilterValue = it =>
{
//Writable logic
return(new SqlFilterResult()
{
Sql = " id=@id ", Parameters = new { id = 1 }
});
},
IsJoinQuery = false //单表查询生效
});
var s14 = db.Queryable <Order>().ToPageListAsync(1, 2, 3);
s14.Wait();
}
public static void Queryable()
{
var pageindex = 1;
var pagesize = 10;
var total = 0;
var totalPage = 0;
var list = Db.Queryable <Order>().ToPageList(pageindex, pagesize, ref total, ref totalPage);
//Db.CodeFirst.InitTables(typeof(CarType));
//Db.Updateable<CarType>()
// .SetColumns(it => new CarType { State = SqlSugar.SqlFunc.IIF(it.State == true, false, true) }).Where(it => true)
// .ExecuteCommand();
//Db.CodeFirst.InitTables(typeof(TestTree));
//Db.DbMaintenance.TruncateTable<TestTree>();
//Db.Ado.ExecuteCommand("insert testtree values(hierarchyid::GetRoot(),geography :: STGeomFromText ('POINT(55.9271035250276 -3.29431266523898)',4326),'name')");
//var list2 = Db.Queryable<TestTree>().ToList();
Db.CodeFirst.InitTables <UnitGuidTable>();
Db.Queryable <UnitGuidTable>().Where(it => it.Id.HasValue).ToList();
Db.Queryable <Order>().Where(it => SqlSugar.SqlFunc.Equals(it.CreateTime.Date, it.CreateTime.Date)).ToList();
var sql = Db.Queryable <UnitSelectTest>().Select(it => new UnitSelectTest()
{
DcNull = it.Dc,
Dc = it.Int
}).ToSql().Key;
UValidate.Check(sql, "SELECT `Dc` AS `DcNull` , `Int` AS `Dc` FROM `UnitSelectTest`", "Queryable");
sql = Db.Updateable <UnitSelectTest2>(new UnitSelectTest2()).ToSql().Key;
UValidate.Check(sql, @"UPDATE `UnitSelectTest2` SET
`Dc`=@Dc,`IntNull`=@IntNull WHERE `Int`=@Int", "Queryable");
sql = Db.Queryable <Order>().IgnoreColumns(it => it.CreateTime).ToSql().Key;
UValidate.Check(sql, "SELECT `Id`,`Name`,`Price`,`CustomId` FROM `Order` ", "Queryable");
sql = Db.Queryable <Order>().IgnoreColumns(it => new { it.Id, it.Name }).ToSql().Key;
UValidate.Check(sql, "SELECT `Price`,`CreateTime`,`CustomId` FROM `Order` ", "Queryable");
sql = Db.Queryable <Order>().IgnoreColumns("id").ToSql().Key;
UValidate.Check(sql, "SELECT `Name`,`Price`,`CreateTime`,`CustomId` FROM `Order` ", "Queryable");
var cts = IEnumerbleContains.Data();
var list2 = Db.Queryable <Order>()
.Where(p => /*ids.*/ cts.Select(c => c.Id).Contains(p.Id)).ToList();
var cts2 = IEnumerbleContains.Data().ToList();;
var list3 = Db.Queryable <Order>()
.Where(p => /*ids.*/ cts2.Select(c => c.Id).Contains(p.Id)).ToList();
var list4 = Db.Queryable <Order>()
.Where(p => new List <int> {
1, 2, 3
}.Where(b => b > 1).Contains(p.Id)).ToList();
Db.CodeFirst.InitTables <UnitTest3>();
var list5 = Db.Queryable <UnitTest3>().Where(it => SqlSugar.SqlFunc.ToString(it.Date.Value.Year) == "1").ToList();
var list6 = Db.Queryable <UnitTest3>().Where(it => it.Date.Value.Year == 1).ToList();
var list7 = Db.Queryable <UnitTest3>().Where(it => it.Date.Value.Date == DateTime.Now.Date).ToList();
SaleOrder saleOrderInfo = new SaleOrder();
Db.CodeFirst.InitTables <SaleOrder>();
var result = Db.GetSimpleClient <SaleOrder>().Update(o => new SaleOrder()
{
OrderStatus = 1,
CheckMan = saleOrderInfo.CheckMan,
CheckTime = DateTime.Now
}, o => o.OrderSn == saleOrderInfo.OrderSn && o.OrderStatus != 1);
var task = CustomTest1.GetPurchaseDetailPageAsync(Db);
task.Wait();
}
public static void Updateable()
{
Db.CodeFirst.InitTables(typeof(UnitUser));
Db.DbMaintenance.TruncateTable <UnitUser>();
Db.Insertable(new UnitUser()
{
USER_ID = 1, USER_ACCOUNT = "a", USER_PWD = "b", USER_NAME = "c", PWD_LASTCHTIME = DateTime.Now, PWD_ERRORCOUNT = 1, PWD_LASTERRTIME = DateTime.Now
}).ExecuteCommand();
Db.Updateable(new UnitUser()
{
USER_ID = 1, PWD_LASTERRTIME = null
}).WhereColumns(it => new{ it.PWD_ERRORCOUNT, it.PWD_LASTERRTIME }).ExecuteCommand();
Db.CodeFirst.InitTables(typeof(UnitBoolTest));
var x = new UnitBoolTest();
Db.Updateable <UnitBoolTest>().SetColumns(it => new UnitBoolTest()
{
BoolValue = !it.BoolValue
}).Where(it => it.Id == 1).ExecuteCommand();
Db.Updateable <UnitBoolTest>().SetColumns(it => it.BoolValue == !it.BoolValue).Where(it => it.Id == 1).ExecuteCommand();
Db.Updateable <UnitBoolTest>().SetColumns(it => new UnitBoolTest()
{
BoolValue = x.BoolValue
}).Where(it => it.Id == 1).ExecuteCommand();
Db.Updateable <UnitBoolTest>().SetColumns(it => it.BoolValue == x.BoolValue).Where(it => it.Id == 1).ExecuteCommand();
Db.Updateable <UnitBoolTest>().SetColumns(it => new UnitBoolTest()
{
BoolValue = !x.BoolValue
}).Where(it => it.Id == 1).ExecuteCommand();
Db.Updateable <UnitBoolTest>().SetColumns(it => it.BoolValue == !x.BoolValue).Where(it => it.Id == 1).ExecuteCommand();
Db.Updateable <UnitBoolTest>(x).ReSetValue(it => it.BoolValue == it.BoolValue).ExecuteCommand();
Db.Updateable <UnitBoolTest>(x).ReSetValue(it => it.BoolValue == true).ExecuteCommand();
Db.Updateable <UnitBoolTest>(x).ReSetValue(it => it.BoolValue == !it.BoolValue).ExecuteCommand();
Db.Updateable <UnitBoolTest>(x).UpdateColumns(it => new { it.BoolValue }).ExecuteCommand();
UnitSaveDiary saveDiary = new UnitSaveDiary();
saveDiary.ID = 2;
saveDiary.TypeID = 10;
saveDiary.TypeName = "类型100";
saveDiary.Title = "标题1000";
saveDiary.Content = "内容";
saveDiary.Time = DateTime.Now;
saveDiary.IsRemind = false;//无论传false/true 最终执行的结果都是以true执行的
var sql = Db.Updateable <UnitDiary>().SetColumns(it => new UnitDiary()
{
IsRemind = saveDiary.IsRemind,
}).Where(it => it.ID == saveDiary.ID).ToSql();
UValidate.Check(sql.Key, @"UPDATE `Diary` SET
`IsRemind` = @Const0 WHERE ( `ID` = @ID1 )", "Updateable");
sql = Db.Updateable <UnitDiary>().SetColumns(it => new UnitDiary()
{
TypeID = saveDiary.TypeID,
}).Where(it => it.ID == saveDiary.ID).ToSql();
UValidate.Check(sql.Key, @"UPDATE `Diary` SET
`TypeID` = @Const0 WHERE ( `ID` = @ID1 )", "Updateable");
Db.CodeFirst.InitTables <UnitDSsdfa>();
var dt = DateTime.Now;
Db.Insertable(new UnitDSsdfa()
{
pk = dt, value = 1
}).ExecuteCommand();
var list = new List <UnitDSsdfa>()
{
new UnitDSsdfa()
{
pk = dt, value = 2
}
};
for (int i = 0; i < 100; i++)
{
list.Add(new UnitDSsdfa()
{
pk = dt, value = 2
});
}
var res1 = Db.Updateable(list).WhereColumns(it => it.pk).ExecuteCommand();
}
public static void Queryable()
{
var pageindex = 1;
var pagesize = 10;
var total = 0;
var totalPage = 0;
var list = Db.Queryable <Order>().ToPageList(pageindex, pagesize, ref total, ref totalPage);
//Db.CodeFirst.InitTables(typeof(CarType));
//Db.Updateable<CarType>()
// .SetColumns(it => new CarType { State = SqlSugar.SqlFunc.IIF(it.State == true, false, true) }).Where(it => true)
// .ExecuteCommand();
//Db.CodeFirst.InitTables(typeof(TestTree));
//Db.DbMaintenance.TruncateTable<TestTree>();
//Db.Ado.ExecuteCommand("insert testtree values(hierarchyid::GetRoot(),geography :: STGeomFromText ('POINT(55.9271035250276 -3.29431266523898)',4326),'name')");
//var list2 = Db.Queryable<TestTree>().ToList();
Db.CodeFirst.InitTables <UnitGuidTable>();
Db.Queryable <UnitGuidTable>().Where(it => it.Id.HasValue).ToList();
Db.Queryable <Order>().Where(it => SqlSugar.SqlFunc.Equals(it.CreateTime.Date, it.CreateTime.Date)).ToList();
var sql = Db.Queryable <UnitSelectTest>().Select(it => new UnitSelectTest()
{
DcNull = it.Dc,
Dc = it.Int
}).ToSql().Key;
UValidate.Check(sql, "SELECT [Dc] AS [DcNull] , [Int] AS [Dc] FROM [UnitSelectTest]", "Queryable");
sql = Db.Updateable <UnitSelectTest2>(new UnitSelectTest2()).ToSql().Key;
UValidate.Check(sql, @"UPDATE [UnitSelectTest2] SET
[Dc]=@Dc,[IntNull]=@IntNull WHERE [Int]=@Int", "Queryable");
sql = Db.Queryable <Order>().IgnoreColumns(it => it.CreateTime).ToSql().Key;
UValidate.Check(sql, "SELECT [Id],[Name],[Price],[CustomId] FROM [Order] ", "Queryable");
sql = Db.Queryable <Order>().IgnoreColumns(it => new { it.Id, it.Name }).ToSql().Key;
UValidate.Check(sql, "SELECT [Price],[CreateTime],[CustomId] FROM [Order] ", "Queryable");
sql = Db.Queryable <Order>().IgnoreColumns("id").ToSql().Key;
UValidate.Check(sql, "SELECT [Name],[Price],[CreateTime],[CustomId] FROM [Order] ", "Queryable");
var cts = IEnumerbleContains.Data();
var list2 = Db.Queryable <Order>()
.Where(p => /*ids.*/ cts.Select(c => c.Id).Contains(p.Id)).ToList();
var cts2 = IEnumerbleContains.Data().ToList();;
var list3 = Db.Queryable <Order>()
.Where(p => /*ids.*/ cts2.Select(c => c.Id).Contains(p.Id)).ToList();
var list4 = Db.Queryable <Order>()
.Where(p => new List <int> {
1, 2, 3
}.Where(b => b > 1).Contains(p.Id)).ToList();
Db.CodeFirst.InitTables <UnitTest3>();
var list5 = Db.Queryable <UnitTest3>().Where(it => SqlSugar.SqlFunc.ToString(it.Date.Value.Year) == "1").ToList();
var list6 = Db.Queryable <UnitTest3>().Where(it => it.Date.Value.Year == 1).ToList();
var list7 = Db.Queryable <UnitTest3>().Where(it => it.Date.Value.Date == DateTime.Now.Date).ToList();
SaleOrder saleOrderInfo = new SaleOrder();
Db.CodeFirst.InitTables <SaleOrder>();
var result = Db.GetSimpleClient <SaleOrder>().Update(o => new SaleOrder()
{
OrderStatus = 1,
CheckMan = saleOrderInfo.CheckMan,
CheckTime = DateTime.Now
}, o => o.OrderSn == saleOrderInfo.OrderSn && o.OrderStatus != 1);
var ids = Enumerable.Range(1, 11).ToList();
var list8 = Db.Queryable <Order>().Where(it => SqlFunc.ContainsArrayUseSqlParameters(ids, it.Id)).ToList();
var result2 = Db.Queryable <Unit_SYS_USER>().Where(o => o.XH == UserLoginInfo.XH).Select(o => o.XH).ToSql();
var x = Db.Queryable <BoolTest1>().Select(it => new BoolTest2()
{
a = it.a
}).ToSql();
UValidate.Check(x.Key, "SELECT [a] AS [a] FROM [BoolTest1] ", "Queryable");
x = Db.Queryable <BoolTest2>().Select(it => new BoolTest1()
{
a = it.a.Value
}).ToSql();
UValidate.Check(x.Key, "SELECT [a] AS [a] FROM [BoolTest2] ", "Queryable");
var db = Db;
db.CodeFirst.InitTables <UserInfo, UserIpRuleInfo>();
db.Deleteable <UserInfo>().ExecuteCommand();
db.Deleteable <UserIpRuleInfo>().ExecuteCommand();
db.Insertable(new UserInfo()
{
Id = 1,
Password = "******",
UserName = "******"
}).ExecuteCommand();
db.Insertable(new UserIpRuleInfo()
{
Addtime = DateTime.Now,
UserName = "******",
Id = 11,
UserId = 1,
Description = "xx",
IpRange = "1",
RuleType = 1
}).ExecuteCommand();
var vmList = db.Queryable <UserInfo, UserIpRuleInfo>(
(m1, m2) => m1.Id == m2.UserId
).Where((m1, m2) => m1.Id > 0).Select((m1, m2) => new UserIpRuleInfo()
{
IpRange = m2.IpRange,
Addtime = m2.Addtime,
RuleType = m2.RuleType,
}).ToList();
if (string.IsNullOrEmpty(vmList.First().IpRange))
{
throw new Exception("Queryable");
}
Db.Insertable(new Order()
{
CreateTime = DateTime.Now, CustomId = 1, Name = "a", Price = 1
}).ExecuteCommand();
var sa = Db.SqlQueryable <Order>("SELECT * FroM [ORDER] where id in (@id) ");
sa.AddParameters(new List <SugarParameter>()
{
new SugarParameter("id", new int[] { 1 })
});
int i = 0;
var salist = sa.ToPageList(1, 2, ref i);
db.CodeFirst.InitTables <UnitBytes11>();
db.Insertable(new UnitBytes11()
{
bytes = null, name = "a"
}).ExecuteCommand();
db.Insertable(new UnitBytes11()
{
bytes = new byte[] { 1, 2 }, name = "a"
}).ExecuteCommand();
var bytes = db.Queryable <UnitBytes11>().Select(it => new
{
b = it.bytes,
name = "a"
}).ToList();
var bytes2 = db.Queryable <UnitBytes11>().Select(it => new
{
b = it
}).ToList();
db.CodeFirst.InitTables <BoolTest1>();
db.CodeFirst.InitTables <BoolTest2>();
db.Queryable <BoolTest1>().Where(it => !it.a).ToList();
var test01 = db.Queryable <SaleOrder>().GroupBy(it => new { it.CheckTime.Value.Date })
.Select(it => new { x = it.CheckTime.Value.Date }).ToList();
var q1 = db.Queryable <BoolTest1>();
var x1 = q1.Clone().AS("BoolTest11");
var x2 = q1.Clone().AS("BoolTest12");
var q2 = db.UnionAll(x1, x2).ToSql();
if (!q2.Key.Contains("BoolTest11") || !q2.Key.Contains("BoolTest12"))
{
throw new Exception("unit query error");
}
db.Queryable <Order>().Where(it => SqlFunc.Round(it.Id, 2) == SqlFunc.Abs(it.Id)).ToList();
db.Insertable(new Order()
{
CreateTime = Convert.ToDateTime("2021-1-1"), CustomId = 1, Name = "a", Price = 0
}).ExecuteCommand();
db.Insertable(new Order()
{
CreateTime = Convert.ToDateTime("2021-1-9"), CustomId = 1, Name = "a", Price = 0
}).ExecuteCommand();
db.Insertable(new Order()
{
CreateTime = Convert.ToDateTime("2021-9-11"), CustomId = 1, Name = "a", Price = 0
}).ExecuteCommand();
db.Insertable(new Order()
{
CreateTime = Convert.ToDateTime("2021-11-30"), CustomId = 1, Name = "a", Price = 0
}).ExecuteCommand();
var d1 = db.Queryable <Order>()
.Where(it => it.CreateTime.Day == 1 && it.CreateTime.Year == 2021)
.Select(it => it.CreateTime.ToString("yyyy-MM-dd")).ToList();
Check.Exception(d1.Last() != "2021-01-01", "unit error");
var d11 = db.Queryable <Order>()
.Where(it => it.CreateTime.Day == 9 && it.CreateTime.Year == 2021)
.Select(it => it.CreateTime.ToString("yyyy-MM-dd")).ToList();
Check.Exception(d11.Last() != "2021-01-09", "unit error");
var d111 = db.Queryable <Order>()
.Where(it => it.CreateTime.Day == 11 && it.CreateTime.Year == 2021)
.Select(it => it.CreateTime.ToString("yyyy-MM-dd")).ToList();
Check.Exception(d111.Last() != "2021-09-11", "unit error");
var d1111 = db.Queryable <Order>()
.Where(it => it.CreateTime.Day == 30 && it.CreateTime.Year == 2021)
.Select(it => it.CreateTime.ToString("yyyy-MM-dd")).ToList();
Check.Exception(d1111.Last() != "2021-11-30", "unit error");
var d11111 = db.Queryable <Order>()
.Where(it => it.CreateTime.ToString("yyyy-MM-dd") == "2021-11-30")
.Select(it => it.CreateTime.ToString("yyyy-MM-dd")).ToList();
Check.Exception(d11111.Last() != "2021-11-30", "unit error");
db.CodeFirst.InitTables <UnitEnumadfa>();
db.Insertable(new UnitEnumadfa()).ExecuteCommand();
db.Insertable(new UnitEnumadfa()
{
Type = DbType.Sqlite
}).ExecuteCommand();
var listEnum = db.Queryable <UnitEnumadfa>().ToList();
var d111111 = db.Queryable <Order>()
.Where(it => it.Id == SqlFunc.IF(true).Return(1).End(0))
.ToList();
var d1111111 = db.Queryable <Order>()
.Where(it => it.Id == SqlFunc.IF(it.Id > 0).Return(1).End(0))
.ToList();
var d11111111 = db.Queryable <Order>()
.Where(it => it.Id == (it.Id > 0? (it.Id == 1?11:1):2))
.ToList();
var d111111111 = db.Queryable <Order>()
.Where(it => it.Id == (it.Id > 0 ? (it.Id == 1 ? 11 : (it.Id == 2?2:1)) : 2))
.ToList();
bool?bq = true;
var d1111111111 = db.Queryable <BoolTest1>().Where(it => it.a.Equals(bq.Value)).ToArray();
var d11111111111 = db.Queryable <BoolTest1>().Where(it => SqlFunc.IIF(bq.Value, 1, 2) == 1).ToArray();
var d111111111111 = db.Queryable <BoolTest1>().Select(it => new { x = SqlFunc.IsNull(it.a, false) }).ToArray();
db.CodeFirst.InitTables <SqlSugarDemo.UserEntity, SqlSugarDemo.RoleEntity, SqlSugarDemo.UserRoleEntity>();
var data = new SqlSugarDemo.UserEntity()
{
CardNo = "",
CompanyWX = "",
Credential = "",
EmailAccount = "",
EndDate = DateTime.Now,
FailedLoginPwdCount = 1,
IsChangePassword = true,
IsReal = 1,
LastLoginDate = DateTime.Now,
ManageAccount = Guid.NewGuid(),
ManageOrg = Guid.NewGuid(),
NickName = "",
PhoneAccount = "",
RealName = "",
VerificationLoginPwdDate = DateTime.Now,
SafePhone = "",
Sex = 1,
StartDate = DateTime.Now,
StopLoginTime = DateTime.Now,
UserAccount = "",
UserId = Guid.NewGuid(),
UserType = 1
};
db.Insertable(data).ExecuteCommand();
//var role = new SqlSugarDemo.RoleEntity()
//{
// RoleId=Guid.NewGuid(),
// ManageAccount= Guid.NewGuid(),
// ManageOrg=Guid.NewGuid(),
// OrganizationId=Guid.NewGuid(),
// UnitPrice=1,
// Quantity=1,
// RoleName="",
// RoleType=1,
// SortNum=1
//};
//db.Insertable(role).ExecuteCommand();
//db.Insertable(new SqlSugarDemo.UserRoleEntity()
//{
// RoleId= role.RoleId,
// UserId=data.UserId
//}).ExecuteCommand();
var d1111111111111 = db.Queryable <SqlSugarDemo.UserEntity>()
.Mapper <SqlSugarDemo.UserEntity, SqlSugarDemo.RoleEntity, SqlSugarDemo.UserRoleEntity>(it => ManyToMany.Config(it.UserId, it.RoleId)).InSingle(data.UserId);
}
public static void Filter()
{
var db = Db;
db.QueryFilter.Add(new SqlSugar.TableFilterItem <UnitFilterClass1>(it => it.id > 0));
var s1 = db.Queryable <Order>().ToSql();
if (s1.Key.Contains(">"))
{
throw new Exception("UnitFilter");
}
var s4 = db.Queryable <Order, OrderItem>((o, i) => i.OrderId == o.Id).Select("o.*").ToSql();
if (s4.Key.Contains(">"))
{
throw new Exception("UnitFilter");
}
var s5 = db.Queryable <Order, OrderItem>((o, i) => new JoinQueryInfos(JoinType.Left, o.Id == i.OrderId))
.Select("o.*").ToSql();
if (s5.Key.Contains(">"))
{
throw new Exception("UnitFilter");
}
var s2 = db.Queryable <UnitFilterClass1>().ToSql();
UValidate.Check(s2.Key,
@"SELECT [id],[name] FROM [UnitFilterClass1] WHERE ( [id] > @id0 )", "UnitFilter");
var s3 = db.Queryable <UnitFilterClass1>().Where(it => it.name != null).ToSql();
UValidate.Check(s3.Key,
@"SELECT [id],[name] FROM [UnitFilterClass1] WHERE ( [name] IS NOT NULL ) AND ( [id] > @id1 )", "UnitFilter");
var s6 = db.Queryable <UnitFilterClass1, UnitFilterClass2>((o, i) => i.id == o.id).Select("o.*").ToSql();
UValidate.Check(s6.Key, "SELECT o.* FROM [UnitFilterClass1] o ,[UnitFilterClass2] i WHERE ( [i].[id] = [o].[id] ) AND ( [o].[id] > @id0 )", "UnitFilter");
var s7 = db.Queryable <UnitFilterClass1, UnitFilterClass2>((o, i) => i.id == o.id).Where(o => o.id == 1).Select("o.*").ToSql();
UValidate.Check(s7.Key, "SELECT o.* FROM [UnitFilterClass1] o ,[UnitFilterClass2] i WHERE ( [i].[id] = [o].[id] ) AND ( [o].[id] = @id0 ) AND ( [o].[id] > @id1 )", "UnitFilter");
var s8 = db.Queryable <UnitFilterClass2, UnitFilterClass1>((o, i) => i.id == o.id).Where(o => o.id == 1).Select("o.*").ToSql();
UValidate.Check(s8.Key, "SELECT o.* FROM [UnitFilterClass2] o ,[UnitFilterClass1] i WHERE ( [i].[id] = [o].[id] ) AND ( [o].[id] = @id0 ) AND ( [i].[id] > @id1 )", "UnitFilter");
var s9 = db.Queryable <UnitFilterClass1, UnitFilterClass2>((o, i) => new JoinQueryInfos(JoinType.Left, o.id == i.id))
.Select("o.*").ToSql();
UValidate.Check(s9.Key, "SELECT o.* FROM [UnitFilterClass1] o Left JOIN [UnitFilterClass2] i ON ( [o].[id] = [i].[id] ) WHERE ( [o].[id] > @id0 )", "UnitFilter");
var s10 = db.Queryable <UnitFilterClass1, UnitFilterClass2>((o, i) => new JoinQueryInfos(JoinType.Left, o.id == i.id))
.Where((o, i) => i.id == 0).Select("o.*").ToSql();
UValidate.Check(s10.Key, "SELECT o.* FROM [UnitFilterClass1] o Left JOIN [UnitFilterClass2] i ON ( [o].[id] = [i].[id] ) WHERE ( [i].[id] = @id0 ) AND ( [o].[id] > @id1 )", "UnitFilter");
var s11 = db.Queryable <UnitFilterClass2, UnitFilterClass1>((o, i) => new JoinQueryInfos(JoinType.Left, o.id == i.id))
.Select("o.*").ToSql();
UValidate.Check(s11.Key, "SELECT o.* FROM [UnitFilterClass2] o Left JOIN [UnitFilterClass1] i ON ( [o].[id] = [i].[id] ) WHERE ( [i].[id] > @id0 )", "UnitFilter");
db.QueryFilter.Add(new SqlSugar.TableFilterItem <UnitFilterClass2>(it => it.id == 0));
var s12 = db.Queryable <UnitFilterClass2, UnitFilterClass1>((o, i) => new JoinQueryInfos(JoinType.Left, o.id == i.id))
.Select("o.*").ToSql();
UValidate.Check(s12.Key, "SELECT o.* FROM [UnitFilterClass2] o Left JOIN [UnitFilterClass1] i ON ( [o].[id] = [i].[id] ) WHERE ( [i].[id] > @id0 ) AND ( [o].[id] = @id1 )", "UnitFilter");
var s13 = db.Queryable <UnitFilterClass2, UnitFilterClass1>((o, i) => new JoinQueryInfos(JoinType.Left, o.id == i.id))
.Where(o => o.name == "")
.Select("o.*").ToSql();
UValidate.Check(s13.Key, "SELECT o.* FROM [UnitFilterClass2] o Left JOIN [UnitFilterClass1] i ON ( [o].[id] = [i].[id] ) WHERE ( [o].[name] = @name0 ) AND ( [i].[id] > @id1 ) AND ( [o].[id] = @id2 )", "UnitFilter");
}
public static void Updateable()
{
Db.CodeFirst.InitTables(typeof(UnitUser));
Db.DbMaintenance.TruncateTable <UnitUser>();
Db.Insertable(new UnitUser()
{
USER_ID = 1, USER_ACCOUNT = "a", USER_PWD = "b", USER_NAME = "c", PWD_LASTCHTIME = DateTime.Now, PWD_ERRORCOUNT = 1, PWD_LASTERRTIME = DateTime.Now
}).ExecuteCommand();
Db.Updateable(new UnitUser()
{
USER_ID = 1, PWD_LASTERRTIME = null
}).WhereColumns(it => new{ it.PWD_ERRORCOUNT, it.PWD_LASTERRTIME }).ExecuteCommand();
Db.CodeFirst.InitTables(typeof(UnitBoolTest));
var x = new UnitBoolTest();
//Db.Updateable<UnitBoolTest>().SetColumns(it => new UnitBoolTest() { BoolValue = !it.BoolValue }).Where(it=>it.Id==1).ExecuteCommand();
//Db.Updateable<UnitBoolTest>().SetColumns(it => it.BoolValue == !it.BoolValue ).Where(it=>it.Id==1).ExecuteCommand();
Db.Updateable <UnitBoolTest>().SetColumns(it => new UnitBoolTest()
{
BoolValue = x.BoolValue
}).Where(it => it.Id == 1).ExecuteCommand();
Db.Updateable <UnitBoolTest>().SetColumns(it => it.BoolValue == x.BoolValue).Where(it => it.Id == 1).ExecuteCommand();
Db.Updateable <UnitBoolTest>().SetColumns(it => new UnitBoolTest()
{
BoolValue = !x.BoolValue
}).Where(it => it.Id == 1).ExecuteCommand();
//Db.Updateable<UnitBoolTest>().SetColumns(it => it.BoolValue == !x.BoolValue).Where(it => it.Id == 1).ExecuteCommand();
Db.Updateable <UnitBoolTest>(x).ReSetValue(it => it.BoolValue == it.BoolValue).ExecuteCommand();
Db.Updateable <UnitBoolTest>(x).ReSetValue(it => it.BoolValue == true).ExecuteCommand();
//Db.Updateable<UnitBoolTest>(x).ReSetValue(it => it.BoolValue == !it.BoolValue).ExecuteCommand();
Db.Updateable <UnitBoolTest>(x).UpdateColumns(it => new { it.BoolValue }).ExecuteCommand();
UnitSaveDiary saveDiary = new UnitSaveDiary();
saveDiary.ID = 2;
saveDiary.TypeID = 10;
saveDiary.TypeName = "类型100";
saveDiary.Title = "标题1000";
saveDiary.Content = "内容";
saveDiary.Time = DateTime.Now;
saveDiary.IsRemind = false;//无论传false/true 最终执行的结果都是以true执行的
var sql = Db.Updateable <UnitDiary>().SetColumns(it => new UnitDiary()
{
IsRemind = saveDiary.IsRemind,
}).Where(it => it.ID == saveDiary.ID).ToSql();
UValidate.Check(sql.Key, @"UPDATE ""diary"" SET
""isremind"" = @Const0 WHERE ( ""id"" = @ID1 )", "Updateable");
sql = Db.Updateable <UnitDiary>().SetColumns(it => new UnitDiary()
{
TypeID = saveDiary.TypeID,
}).Where(it => it.ID == saveDiary.ID).ToSql();
UValidate.Check(sql.Key, @"UPDATE ""diary"" SET
""typeid"" = @Const0 WHERE ( ""id"" = @ID1 )", "Updateable");
Db.Updateable <OrderModel>().SetColumns(it => new OrderModel()
{
CreateTime = null
}).Where(it => it.Id == 1).ExecuteCommand();
Db.CodeFirst.InitTables <BoolTest1>();
Db.Updateable <BoolTest1>()
.SetColumns(it => it.a == !it.a)
.Where(it => it.a)
.ExecuteCommand();
Db.Updateable <BoolTest1>()
.SetColumns(it => new BoolTest1()
{
a = !it.a
})
.Where(it => it.a)
.ExecuteCommand();
// Db.CodeFirst.InitTables<UnitPk00121>();
//Db.CodeFirst.InitTables<UnitPk001212>();
//Db.Deleteable<UnitPk00121>().Where(new UnitPk00121() { Id=1, CreateTime=DateTime.Now, Name="a" }).ExecuteCommand();
//Db.Deleteable<UnitPk001212>().Where(new List<UnitPk001212> { new UnitPk001212() { Id = 1, CreateTime = DateTime.Now, Name = "a" } , new UnitPk001212() { Id = 2, CreateTime = DateTime.Now, Name = "11a" } }).ExecuteCommand();
Db.CodeFirst.InitTables <UnitDSsdfa>();
var dt = DateTime.Now;
Db.Insertable(new UnitDSsdfa()
{
pk = dt, value = 1
}).ExecuteCommand();
var list = new List <UnitDSsdfa>()
{
new UnitDSsdfa()
{
pk = Convert.ToDateTime("2022-1-1"), value = 2
}, new UnitDSsdfa()
{
pk = dt, value = 2
}
};
var res1 = Db.Updateable(list).WhereColumns(it => it.pk).ExecuteCommand();
}
public static void Json()
{
Db.CodeFirst.InitTables <UnitJsonTest>();
Db.CodeFirst.InitTables <UnitJsonTest2>();
Db.DbMaintenance.TruncateTable <UnitJsonTest>();
Db.Insertable(new UnitJsonTest()
{
Order = new Order {
Id = 1, Name = "order1"
}
}).ExecuteCommand();
var list = Db.Queryable <UnitJsonTest>().ToList();
UValidate.Check("order1", list.First().Order.Name, "Json");
Db.Updateable(new UnitJsonTest()
{
Id = 1, Order = new Order {
Id = 2, Name = "order2"
}
}).ExecuteCommand();
list = Db.Queryable <UnitJsonTest>().ToList();
UValidate.Check("order2", list.First().Order.Name, "Json");
var list2 = Db.Queryable <UnitJsonTest>().ToList();
var x = new Order()
{
Name = "a"
};
Db.Updateable <UnitJsonTest2>()
.SetColumns(it => it.Name == "a")
.Where(it => it.Id == 1)
.ExecuteCommand();
var list3 = Db.Queryable <UnitJsonTest>().Select(it => new
{
x = it
}).ToList();
if (list3[0].x == null)
{
throw new Exception("unit error");
}
var db = Db;
db.CodeFirst.SetStringDefaultLength(200).InitTables(typeof(SqlSugarSelect.TestModel1));
db.CodeFirst.SetStringDefaultLength(200).InitTables(typeof(SqlSugarSelect.TestModel2));
#region 加入数据
var isadd = !db.Queryable <TestModel1>().Any();
if (isadd)
{
db.Insertable(new SqlSugarSelect.TestModel1
{
Ids = new Guid [] { Guid.NewGuid() },
Titlt = "123"
}).ExecuteCommand();
db.Insertable(new SqlSugarSelect.TestModel2
{
Pid = 1
}).ExecuteCommand();
}
#endregion
#region 实际搜索代码，Bug所在处
var rv = db.Queryable <SqlSugarSelect.TestModel2>()
.LeftJoin <SqlSugarSelect.TestModel1>((a, b) => a.Pid == b.Id)
.Select((a, b) => new { a, b }).ToList();
#endregion
db.CodeFirst.SetStringDefaultLength(2000).InitTables <UnitJsonTestadsga1>();
db.Insertable(new UnitJsonTestadsga1()
{
os = new List <Order>()
}).ExecuteCommand();
db.Insertable(new UnitJsonTestadsga1()
{
os = new List <Order>()
{
new Order()
{
CreateTime = DateTime.Now
}
}
}).ExecuteCommand();
var list10 = db.Queryable <UnitJsonTestadsga1>().Select(it => new { it }).ToList();
}
public static void Updateable()
{
Db.CodeFirst.InitTables(typeof(UnitUser));
Db.DbMaintenance.TruncateTable <UnitUser>();
Db.Insertable(new UnitUser()
{
USER_ID = 1, USER_ACCOUNT = "a", USER_PWD = "b", USER_NAME = "c", PWD_LASTCHTIME = DateTime.Now, PWD_ERRORCOUNT = 1, PWD_LASTERRTIME = DateTime.Now
}).ExecuteCommand();
Db.Updateable(new UnitUser()
{
USER_ID = 1, PWD_LASTERRTIME = null
}).WhereColumns(it => new { it.PWD_ERRORCOUNT, it.PWD_LASTERRTIME }).ExecuteCommand();
Db.CodeFirst.InitTables(typeof(UnitBoolTest));
var x = new UnitBoolTest();
Db.Updateable <UnitBoolTest>().SetColumns(it => new UnitBoolTest()
{
BoolValue = !it.BoolValue
}).Where(it => it.Id == 1).ExecuteCommand();
Db.Updateable <UnitBoolTest>().SetColumns(it => it.BoolValue == !it.BoolValue).Where(it => it.Id == 1).ExecuteCommand();
Db.Updateable <UnitBoolTest>().SetColumns(it => new UnitBoolTest()
{
BoolValue = x.BoolValue
}).Where(it => it.Id == 1).ExecuteCommand();
Db.Updateable <UnitBoolTest>().SetColumns(it => it.BoolValue == x.BoolValue).Where(it => it.Id == 1).ExecuteCommand();
Db.Updateable <UnitBoolTest>().SetColumns(it => new UnitBoolTest()
{
BoolValue = !x.BoolValue
}).Where(it => it.Id == 1).ExecuteCommand();
Db.Updateable <UnitBoolTest>().SetColumns(it => it.BoolValue == !x.BoolValue).Where(it => it.Id == 1).ExecuteCommand();
Db.Updateable <UnitBoolTest>(x).ReSetValue(it => it.BoolValue = it.BoolValue).ExecuteCommand();
Db.Updateable <UnitBoolTest>(x).ReSetValue(it => it.BoolValue = true).ExecuteCommand();
Db.Updateable <UnitBoolTest>(x).ReSetValue(it => it.BoolValue = !it.BoolValue).ExecuteCommand();
Db.Updateable <UnitBoolTest>(x).UpdateColumns(it => new { it.BoolValue }).ExecuteCommand();
UnitSaveDiary saveDiary = new UnitSaveDiary();
saveDiary.ID = 2;
saveDiary.TypeID = 10;
saveDiary.TypeName = "类型100";
saveDiary.Title = "标题1000";
saveDiary.Content = "内容";
saveDiary.Time = DateTime.Now;
saveDiary.IsRemind = false;//无论传false/true 最终执行的结果都是以true执行的
var sql = Db.Updateable <UnitDiary>().SetColumns(it => new UnitDiary()
{
IsRemind = saveDiary.IsRemind,
}).Where(it => it.ID == saveDiary.ID).ToSql();
UValidate.Check(sql.Key, @"UPDATE [Diary] SET
[IsRemind] = @Const0 WHERE ( [ID] = @ID1 )", "Updateable");
sql = Db.Updateable <UnitDiary>().SetColumns(it => new UnitDiary()
{
TypeID = saveDiary.TypeID,
}).Where(it => it.ID == saveDiary.ID).ToSql();
UValidate.Check(sql.Key, @"UPDATE [Diary] SET
[TypeID] = @Const0 WHERE ( [ID] = @ID1 )", "Updateable");
sql = Db.Updateable <UnitDiary>().SetColumns(it => new UnitDiary()
{
TypeID = saveDiary.TypeID,
}).Where(it => it.ID == saveDiary.ID).ToSql();
UValidate.Check(sql.Key, @"UPDATE [Diary] SET
[TypeID] = @Const0 WHERE ( [ID] = @ID1 )", "Updateable");
sql = Db.Updateable <NullTest>().SetColumns(it => new NullTest()
{
p = true
}).Where(it => it.id == 1).ToSql();
UValidate.Check(sql.Key, @"UPDATE [NullTest] SET
[p] = @Const0 WHERE ( [id] = @id1 )", "Updateable");
sql = Db.Updateable <NullTest>().SetColumns(it => new NullTest()
{
p2 = true
}).Where(it => it.id == 1).ToSql();
UValidate.Check(sql.Key, @"UPDATE [NullTest] SET
[p2] = @Const0 WHERE ( [id] = @id1 )", "Updateable");
Db.Updateable <Order>()
.SetColumns(it => it.Name == "a")
.SetColumns(it => it.CreateTime == DateTime.Now)
.SetColumns(it => it.Price == 1).Where(it => it.Id == 1).ExecuteCommand();
Db.Updateable <Order>()
.SetColumns(it => new Order {
Name = "a", CreateTime = DateTime.Now
})
.SetColumns(it => it.Price == 1).Where(it => it.Id == 1).ExecuteCommand();
Db.Updateable <Order>()
.SetColumns(it => new Order {
Name = "a", CreateTime = DateTime.Now
})
.SetColumns(it => new Order()
{
Price = 1
}).Where(it => it.Id == 1).ExecuteCommand();
Db.Updateable <Order>()
.SetColumns(it => new Order {
Name = it.Id > 0 ?"1":"2", CreateTime = DateTime.Now
})
.Where(it => it.Id == 1).ExecuteCommand();
Db.Updateable <Order>()
.SetColumns(it => new Order {
Name = SqlFunc.IsNull(it.Name, "a") + "b", CreateTime = DateTime.Now
})
.Where(it => it.Id == 1).ExecuteCommand();
Db.CodeFirst.InitTables <Unitbluecopy>();
Db.Insertable(new Unitbluecopy()).UseSqlServer().ExecuteBulkCopy();
}
public static void Queryable()
{
var pageindex = 1;
var pagesize = 10;
var total = 0;
var totalPage = 0;
var list = Db.Queryable <Order>().ToPageList(pageindex, pagesize, ref total, ref totalPage);
//Db.CodeFirst.InitTables(typeof(CarType));
//Db.Updateable<CarType>()
// .SetColumns(it => new CarType { State = SqlSugar.SqlFunc.IIF(it.State == true, false, true) }).Where(it => true)
// .ExecuteCommand();
//Db.CodeFirst.InitTables(typeof(TestTree));
//Db.DbMaintenance.TruncateTable<TestTree>();
//Db.Ado.ExecuteCommand("insert testtree values(hierarchyid::GetRoot(),geography :: STGeomFromText ('POINT(55.9271035250276 -3.29431266523898)',4326),'name')");
//var list2 = Db.Queryable<TestTree>().ToList();
Db.CodeFirst.InitTables <UnitGuidTable>();
Db.Queryable <UnitGuidTable>().Where(it => it.Id.HasValue).ToList();
Db.Queryable <Order>().Where(it => SqlSugar.SqlFunc.Equals(it.CreateTime.Date, it.CreateTime.Date)).ToList();
var sql = Db.Queryable <UnitSelectTest>().Select(it => new UnitSelectTest()
{
DcNull = it.Dc,
Dc = it.Int
}).ToSql().Key;
UValidate.Check(sql, "SELECT \"dc\" AS \"dcnull\" , \"int\" AS \"dc\" FROM \"unitselecttest\"", "Queryable");
sql = Db.Updateable <UnitSelectTest2>(new UnitSelectTest2()).ToSql().Key;
UValidate.Check(sql, @"UPDATE ""unitselecttest2"" SET
""dc""=@Dc,""intnull""=@IntNull WHERE ""int""=@Int", "Queryable");
sql = Db.Queryable <Order>().IgnoreColumns(it => it.CreateTime).ToSql().Key;
UValidate.Check(sql, "SELECT \"id\",\"name\",\"price\",\"customid\" FROM \"order\" ", "Queryable");
sql = Db.Queryable <Order>().IgnoreColumns(it => new { it.Id, it.Name }).ToSql().Key;
UValidate.Check(sql, "SELECT \"price\",\"createtime\",\"customid\" FROM \"order\" ", "Queryable");
sql = Db.Queryable <Order>().IgnoreColumns("id").ToSql().Key;
UValidate.Check(sql, "SELECT \"name\",\"price\",\"createtime\",\"customid\" FROM \"order\" ", "Queryable");
var cts = IEnumerbleContains.Data();
var list2 = Db.Queryable <Order>()
.Where(p => /*ids.*/ cts.Select(c => c.Id).Contains(p.Id)).ToList();
var cts2 = IEnumerbleContains.Data().ToList();;
var list3 = Db.Queryable <Order>()
.Where(p => /*ids.*/ cts2.Select(c => c.Id).Contains(p.Id)).ToList();
var list4 = Db.Queryable <Order>()
.Where(p => new List <int> {
1, 2, 3
}.Where(b => b > 1).Contains(p.Id)).ToList();
Db.CodeFirst.InitTables <UnitTest3>();
var list5 = Db.Queryable <UnitTest3>().Where(it => SqlSugar.SqlFunc.ToString(it.Date.Value.Year) == "1").ToList();
var list6 = Db.Queryable <UnitTest3>().Where(it => it.Date.Value.Year == 1).ToList();
var list7 = Db.Queryable <UnitTest3>().Where(it => it.Date.Value.Date == DateTime.Now.Date).ToList();
SaleOrder saleOrderInfo = new SaleOrder();
Db.CodeFirst.InitTables <SaleOrder>();
var result = Db.GetSimpleClient <SaleOrder>().Update(o => new SaleOrder()
{
OrderStatus = 1,
CheckMan = saleOrderInfo.CheckMan,
CheckTime = DateTime.Now
}, o => o.OrderSn == saleOrderInfo.OrderSn && o.OrderStatus != 1);
var list14 = Db.Queryable <Order, Order, Order>((o1, o2, o3) =>
new JoinQueryInfos(JoinType.Inner, o1.Id == o2.Id * 2, JoinType.Inner, o1.Id == o3.Id * 4)
)
.Select((o1, o2, o3) => new
{
id = o1.Id,
x = o1,
x2 = o2,
x3 = o3
}).ToList();
var list15 = Db.Queryable <Order, Order, Order>((o1, o2, o3) =>
new JoinQueryInfos(JoinType.Inner, o1.Id == o2.Id * 2, JoinType.Inner, o1.Id == o3.Id * 4)
)
.Select((o1, o2, o3) => new TestModel1
{
id = o1.Id.SelectAll(),
x = o1,
x2 = o2,
x3 = o3
}).ToList();
}
public static void Queryable()
{
var pageindex = 1;
var pagesize = 10;
var total = 0;
var totalPage = 0;
var list = Db.Queryable <Order>().ToPageList(pageindex, pagesize, ref total, ref totalPage);
//Db.CodeFirst.InitTables(typeof(CarType));
//Db.Updateable<CarType>()
// .SetColumns(it => new CarType { State = SqlSugar.SqlFunc.IIF(it.State == true, false, true) }).Where(it => true)
// .ExecuteCommand();
//Db.CodeFirst.InitTables(typeof(TestTree));
//Db.DbMaintenance.TruncateTable<TestTree>();
//Db.Ado.ExecuteCommand("insert testtree values(hierarchyid::GetRoot(),geography :: STGeomFromText ('POINT(55.9271035250276 -3.29431266523898)',4326),'name')");
//var list2 = Db.Queryable<TestTree>().ToList();
Db.CodeFirst.InitTables <UnitGuidTable>();
Db.Queryable <UnitGuidTable>().Where(it => it.Id.HasValue).ToList();
Db.Queryable <Order>().Where(it => SqlSugar.SqlFunc.Equals(it.CreateTime.Date, it.CreateTime.Date)).ToList();
var sql = Db.Queryable <UnitSelectTest>().Select(it => new UnitSelectTest()
{
DcNull = it.Dc,
Dc = it.Int
}).ToSql().Key;
UValidate.Check(sql, "SELECT [Dc] AS [DcNull] , [Int] AS [Dc] FROM [UnitSelectTest]", "Queryable");
sql = Db.Updateable <UnitSelectTest2>(new UnitSelectTest2()).ToSql().Key;
UValidate.Check(sql, @"UPDATE [UnitSelectTest2] SET
[Dc]=@Dc,[IntNull]=@IntNull WHERE [Int]=@Int", "Queryable");
sql = Db.Queryable <Order>().IgnoreColumns(it => it.CreateTime).ToSql().Key;
UValidate.Check(sql, "SELECT [Id],[Name],[Price],[CustomId] FROM [Order] ", "Queryable");
sql = Db.Queryable <Order>().IgnoreColumns(it => new { it.Id, it.Name }).ToSql().Key;
UValidate.Check(sql, "SELECT [Price],[CreateTime],[CustomId] FROM [Order] ", "Queryable");
sql = Db.Queryable <Order>().IgnoreColumns("id").ToSql().Key;
UValidate.Check(sql, "SELECT [Name],[Price],[CreateTime],[CustomId] FROM [Order] ", "Queryable");
var cts = IEnumerbleContains.Data();
var list2 = Db.Queryable <Order>()
.Where(p => /*ids.*/ cts.Select(c => c.Id).Contains(p.Id)).ToList();
var cts2 = IEnumerbleContains.Data().ToList();;
var list3 = Db.Queryable <Order>()
.Where(p => /*ids.*/ cts2.Select(c => c.Id).Contains(p.Id)).ToList();
var list4 = Db.Queryable <Order>()
.Where(p => new List <int> {
1, 2, 3
}.Where(b => b > 1).Contains(p.Id)).ToList();
Db.CodeFirst.InitTables <UnitTest3>();
var list5 = Db.Queryable <UnitTest3>().Where(it => SqlSugar.SqlFunc.ToString(it.Date.Value.Year) == "1").ToList();
var list6 = Db.Queryable <UnitTest3>().Where(it => it.Date.Value.Year == 1).ToList();
var list7 = Db.Queryable <UnitTest3>().Where(it => it.Date.Value.Date == DateTime.Now.Date).ToList();
SaleOrder saleOrderInfo = new SaleOrder();
Db.CodeFirst.InitTables <SaleOrder>();
var result = Db.GetSimpleClient <SaleOrder>().Update(o => new SaleOrder()
{
OrderStatus = 1,
CheckMan = saleOrderInfo.CheckMan,
CheckTime = DateTime.Now
}, o => o.OrderSn == saleOrderInfo.OrderSn && o.OrderStatus != 1);
var ids = Enumerable.Range(1, 11).ToList();
var list8 = Db.Queryable <Order>().Where(it => SqlFunc.ContainsArrayUseSqlParameters(ids, it.Id)).ToList();
var result2 = Db.Queryable <Unit_SYS_USER>().Where(o => o.XH == UserLoginInfo.XH).Select(o => o.XH).ToSql();
}
public static void Queryable()
{
var pageindex = 1;
var pagesize = 10;
var total = 0;
var totalPage = 0;
var list = Db.Queryable <Order>().ToPageList(pageindex, pagesize, ref total, ref totalPage);
//Db.CodeFirst.InitTables(typeof(CarType));
//Db.Updateable<CarType>()
// .SetColumns(it => new CarType { State = SqlSugar.SqlFunc.IIF(it.State == true, false, true) }).Where(it => true)
// .ExecuteCommand();
//Db.CodeFirst.InitTables(typeof(TestTree));
//Db.DbMaintenance.TruncateTable<TestTree>();
//Db.Ado.ExecuteCommand("insert testtree values(hierarchyid::GetRoot(),geography :: STGeomFromText ('POINT(55.9271035250276 -3.29431266523898)',4326),'name')");
//var list2 = Db.Queryable<TestTree>().ToList();
Db.CodeFirst.InitTables <UnitGuidTable>();
Db.Queryable <UnitGuidTable>().Where(it => it.Id.HasValue).ToList();
Db.Queryable <Order>().Where(it => SqlSugar.SqlFunc.Equals(it.CreateTime.Date, it.CreateTime.Date)).ToList();
var sql = Db.Queryable <UnitSelectTest>().Select(it => new UnitSelectTest()
{
DcNull = it.Dc,
Dc = it.Int
}).ToSql().Key;
UValidate.Check(sql, "SELECT [Dc] AS [DcNull] , [Int] AS [Dc] FROM [UnitSelectTest]", "Queryable");
sql = Db.Updateable <UnitSelectTest2>(new UnitSelectTest2()).ToSql().Key;
UValidate.Check(sql, @"UPDATE [UnitSelectTest2] SET
[Dc]=@Dc,[IntNull]=@IntNull WHERE [Int]=@Int", "Queryable");
sql = Db.Queryable <Order>().IgnoreColumns(it => it.CreateTime).ToSql().Key;
UValidate.Check(sql, "SELECT [Id],[Name],[Price],[CustomId] FROM [Order] ", "Queryable");
sql = Db.Queryable <Order>().IgnoreColumns(it => new { it.Id, it.Name }).ToSql().Key;
UValidate.Check(sql, "SELECT [Price],[CreateTime],[CustomId] FROM [Order] ", "Queryable");
sql = Db.Queryable <Order>().IgnoreColumns("id").ToSql().Key;
UValidate.Check(sql, "SELECT [Name],[Price],[CreateTime],[CustomId] FROM [Order] ", "Queryable");
var cts = IEnumerbleContains.Data();
var list2 = Db.Queryable <Order>()
.Where(p => /*ids.*/ cts.Select(c => c.Id).Contains(p.Id)).ToList();
var cts2 = IEnumerbleContains.Data().ToList();;
var list3 = Db.Queryable <Order>()
.Where(p => /*ids.*/ cts2.Select(c => c.Id).Contains(p.Id)).ToList();
var list4 = Db.Queryable <Order>()
.Where(p => new List <int> {
1, 2, 3
}.Where(b => b > 1).Contains(p.Id)).ToList();
Db.CodeFirst.InitTables <UnitTest3>();
var list5 = Db.Queryable <UnitTest3>().Where(it => SqlSugar.SqlFunc.ToString(it.Date.Value.Year) == "1").ToList();
var list6 = Db.Queryable <UnitTest3>().Where(it => it.Date.Value.Year == 1).ToList();
var list7 = Db.Queryable <UnitTest3>().Where(it => it.Date.Value.Date == DateTime.Now.Date).ToList();
SaleOrder saleOrderInfo = new SaleOrder();
Db.CodeFirst.InitTables <SaleOrder>();
var result = Db.GetSimpleClient <SaleOrder>().Update(o => new SaleOrder()
{
OrderStatus = 1,
CheckMan = saleOrderInfo.CheckMan,
CheckTime = DateTime.Now
}, o => o.OrderSn == saleOrderInfo.OrderSn && o.OrderStatus != 1);
var ids = Enumerable.Range(1, 11).ToList();
var list8 = Db.Queryable <Order>().Where(it => SqlFunc.ContainsArrayUseSqlParameters(ids, it.Id)).ToList();
var result2 = Db.Queryable <Unit_SYS_USER>().Where(o => o.XH == UserLoginInfo.XH).Select(o => o.XH).ToSql();
var x = Db.Queryable <BoolTest1>().Select(it => new BoolTest2()
{
a = it.a
}).ToSql();
UValidate.Check(x.Key, "SELECT [a] AS [a] FROM [BoolTest1] ", "Queryable");
x = Db.Queryable <BoolTest2>().Select(it => new BoolTest1()
{
a = it.a.Value
}).ToSql();
UValidate.Check(x.Key, "SELECT [a] AS [a] FROM [BoolTest2] ", "Queryable");
var db = Db;
db.CodeFirst.InitTables <UserInfo, UserIpRuleInfo>();
db.Deleteable <UserInfo>().ExecuteCommand();
db.Deleteable <UserIpRuleInfo>().ExecuteCommand();
db.Insertable(new UserInfo()
{
Id = 1,
Password = "******",
UserName = "******"
}).ExecuteCommand();
db.Insertable(new UserIpRuleInfo()
{
Addtime = DateTime.Now,
UserName = "******",
Id = 11,
UserId = 1,
Description = "xx",
IpRange = "1",
RuleType = 1
}).ExecuteCommand();
var vmList = db.Queryable <UserInfo, UserIpRuleInfo>(
(m1, m2) => m1.Id == m2.UserId
).Where((m1, m2) => m1.Id > 0).Select((m1, m2) => new UserIpRuleInfo()
{
IpRange = m2.IpRange,
Addtime = m2.Addtime,
RuleType = m2.RuleType,
}).ToList();
if (string.IsNullOrEmpty(vmList.First().IpRange))
{
throw new Exception("Queryable");
}
Db.Insertable(new Order()
{
CreateTime = DateTime.Now, CustomId = 1, Name = "a", Price = 1
}).ExecuteCommand();
var sa = Db.SqlQueryable <Order>("SELECT * FroM [ORDER] where id in (@id) ");
sa.AddParameters(new List <SugarParameter>()
{
new SugarParameter("id", new int[] { 1 })
});
int i = 0;
var salist = sa.ToPageList(1, 2, ref i);
db.CodeFirst.InitTables <UnitBytes11>();
db.Insertable(new UnitBytes11()
{
bytes = null, name = "a"
}).ExecuteCommand();
db.Insertable(new UnitBytes11()
{
bytes = new byte[] { 1, 2 }, name = "a"
}).ExecuteCommand();
var bytes = db.Queryable <UnitBytes11>().Select(it => new
{
b = it.bytes,
name = "a"
}).ToList();
db.CodeFirst.InitTables <BoolTest1>();
db.CodeFirst.InitTables <BoolTest2>();
db.Queryable <BoolTest1>().Where(it => !it.a).ToList();
var test01 = db.Queryable <SaleOrder>().GroupBy(it => new { it.CheckTime.Value.Date })
.Select(it => new { x = it.CheckTime.Value.Date }).ToList();
var q1 = db.Queryable <BoolTest1>();
var x1 = q1.Clone().AS("BoolTest11");
var x2 = q1.Clone().AS("BoolTest12");
var q2 = db.UnionAll(x1, x2).ToSql();
if (!q2.Key.Contains("BoolTest11") || !q2.Key.Contains("BoolTest12"))
{
throw new Exception("unit query error");
}
db.Queryable <Order>().Where(it => SqlFunc.Round(it.Id, 2) == SqlFunc.Abs(it.Id)).ToList();
}
