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 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();
}
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 = 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();
}
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 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 => 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 => 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);
}