Exemple #1
0
        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();
        }
Exemple #2
0
        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");
        }
Exemple #3
0
        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();
        }
Exemple #4
0
        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");
        }
Exemple #5
0
        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();
        }
Exemple #6
0
        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();
        }
Exemple #7
0
        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");
        }
Exemple #8
0
        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();
        }
Exemple #9
0
        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();
        }
Exemple #10
0
        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();
        }
Exemple #11
0
        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();
        }
Exemple #12
0
        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();
        }
Exemple #13
0
        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);
        }
Exemple #14
0
        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");
        }
Exemple #15
0
        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();
        }
Exemple #16
0
        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();
        }
Exemple #17
0
        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();
        }
Exemple #18
0
        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();
        }
Exemple #19
0
        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();
        }
Exemple #20
0
        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();
        }