예제 #1
0
        public static void init()
        {
            var db = CreateDBInstance.GetInstance();


            //by entity
            var t1 = db.Deleteable <Base_User>().Where(new Base_User()
            {
                UserId = "admin30d2c146-3b34-4fdf-be18-f9e066103cb4"
            }).ExecuteCommand();

            //use lock
            var t2 = db.Deleteable <Base_User>().With(SqlWith.RowLock).ExecuteCommand();


            //by primary key
            var t3 = db.Deleteable <Base_User>().In(1).ExecuteCommand();

            //by primary key array
            var t4  = db.Deleteable <Base_User>().In(new int[] { 1, 2 }).ExecuteCommand();
            var t41 = db.Deleteable <Base_User>().In(new int[] { 1, 2 }.Select(it => it)).ExecuteCommand();
            var t42 = db.Deleteable <Base_User>().In(new int[] { 1, 2 }.AsEnumerable()).ExecuteCommand();

            //by expression   id>1 and id==1
            var t5 = db.Deleteable <Base_User>().Where(it => it.Id > 1).Where(it => it.Id == 1).ExecuteCommand();

            var t6 = db.Deleteable <Base_User>().AS("Base_User").Where(it => it.Id > 1).Where(it => it.Id == 1).ExecuteCommandAsync();

            t6.Wait();
        }
예제 #2
0
        public static void Easy()
        {
            var     db     = CreateDBInstance.GetInstance();
            var     dbTime = db.GetDate();
            dynamic getALL = db.Queryable <Base_User>().ToList();

            getALL = db.Queryable <Base_User>().Select <object>("*").ToList();
            getALL = db.Queryable <Base_User>().Select <object>("UserName,Password").ToList();  //不要加引号
            var getRandomList = db.Queryable <Base_User>().OrderBy(it => SqlFunc.GetRandom()).ToList();
            var getAllOrder   = db.Queryable <Base_User>().OrderBy(it => it.Id).OrderBy(it => it.UserId, OrderByType.Desc).ToList().Take(10);
            //var getId = db.Queryable<Student>().Select(it => it.Id).ToList();
            //var getNew = db.Queryable<Student>().Where(it => it.Id == 1).Select(it => new { id = SqlFunc.IIF(it.Id == 0, 1, it.Id), it.Name, it.SchoolId }).ToList();
            //var getAllNoLock = db.Queryable<Student>().With(SqlWith.NoLock).ToList();
            //var getByPrimaryKey = db.Queryable<Student>().InSingle(2);
            //var getSingleOrDefault = db.Queryable<Student>().Where(it => it.Id == 1).Single();
            //var getFirstOrDefault = db.Queryable<Student>().First();
            var getByWhere = db.Queryable <Base_User>().Where(it => it.Id == 17 || it.UserName == "admin").ToList();
            //var getByWhere2 = db.Queryable<Student>().Where(it => it.Id == DateTime.Now.Year).ToList();
            //var getByFuns = db.Queryable<Student>().Where(it => SqlFunc.IsNullOrEmpty(it.Name)).ToList();
            //var sum = db.Queryable<Student>().Select(it => it.SchoolId).ToList();
            //var sum2 = db.Queryable<Student, School>((st, sc) => st.SchoolId == sc.Id).Sum((st, sc) => sc.Id);
            //var isAny = db.Queryable<Student>().Where(it => it.Id == -1).Any();
            //var isAny2 = db.Queryable<Student>().Any(it => it.Id == -1);
            //var count = db.Queryable<Student>().Count(it => it.Id > 0);
            //var date = db.Queryable<Student>().Where(it => it.CreateTime.Value.Date == DateTime.Now.Date).ToList();
            //var getListByRename = db.Queryable<School>().AS("Student").ToList();
            //var in1 = db.Queryable<Student>().In(it => it.Id, new int[] { 1, 2, 3 }).ToList();
            //var in2 = db.Queryable<Student>().In(new int[] { 1, 2, 3 }).ToList();
            //int[] array = new int[] { 1, 2 };
            //var in3 = db.Queryable<Student>().Where(it => SqlFunc.ContainsArray(array, it.Id)).ToList();
            //var group = db.Queryable<Student>().GroupBy(it => it.Id)
            //    .Having(it => SqlFunc.AggregateCount(it.Id) > 10)
            //    .Select(it => new { id = SqlFunc.AggregateCount(it.Id) }).ToList();

            //var between = db.Queryable<Student>().Where(it => SqlFunc.Between(it.Id, 1, 20)).ToList();

            //var getTodayList = db.Queryable<Student>().Where(it => SqlFunc.DateIsSame(it.CreateTime, DateTime.Now)).ToList();

            //var joinSql = db.Queryable("student", "s").OrderBy("id").Select("id,name").ToPageList(1, 2);

            //var getDay1List = db.Queryable<Student>().Where(it => it.CreateTime.Value.Hour == 1).ToList();
            //var getDateAdd = db.Queryable<Student>().Where(it => it.CreateTime.Value.AddDays(1) == DateTime.Now).ToList();
            //var getDateIsSame = db.Queryable<Student>().Where(it => SqlFunc.DateIsSame(DateTime.Now, DateTime.Now, DateType.Hour)).ToList();

            //var getSqlList = db.Queryable<Student>().AS("(select * from student) t").ToList();


            //var getUnionAllList = db.UnionAll(db.Queryable<Student>().Where(it => it.Id == 1), db.Queryable<Student>().Where(it => it.Id == 2)).ToList();

            //var getUnionAllList2 = db.UnionAll(db.Queryable<Student>(), db.Queryable<Student>()).ToList();

            //var test1 = db.Queryable<Student, School>((st, sc) => st.SchoolId == sc.Id).Where(st => st.CreateTime > SqlFunc.GetDate()).Select((st, sc) => SqlFunc.ToInt64(sc.Id)).ToList();
            //var test2 = db.Queryable<Student, School>((st, sc) => st.SchoolId == sc.Id)
            //          .Where(st =>
            //            SqlFunc.IF(st.Id > 1)
            //                 .Return(st.Id)
            //                 .ElseIF(st.Id == 1)
            //                 .Return(st.SchoolId).End(st.Id) == 1).Select(st => st).ToList();
            //var test3 = db.Queryable<DataTestInfo2>().Select(it => it.Bool1).ToSql();
            //var test4 = db.Queryable<DataTestInfo2>().Select(it => new { b = it.Bool1 }).ToSql();
            //DateTime? result = DateTime.Now;
            //var test5 = db.Queryable<Student>().Where(it => it.CreateTime > result.Value.Date).ToList()  ;
            string strJson = JsonConvert.SerializeObject(getByWhere);

            Console.WriteLine(strJson);
        }
예제 #3
0
        public static void init()
        {
            var db = CreateDBInstance.GetInstance();

            var updateObj = new Base_User()
            {
                Id = 1, UserId = "lxsh", DepartmentID = "0", Birthday = Convert.ToDateTime("2017-05-21 09:56:12.610")
            };
            var updateObjs = new List <Base_User>()
            {
                updateObj, new Base_User()
                {
                    Id = 2, UserId = "sun", DepartmentID = "0"
                }
            }.ToArray();
            //update reutrn Update Count
            var t1 = db.Updateable(updateObj).ExecuteCommand();

            //Only  update  Name
            updateObj = new Base_User()
            {
                Id = 1, UserId = "admin", Sex = 1, RealName = "李飞", DepartmentID = "10", Birthday = Convert.ToDateTime("2018-05-21 09:56:12.610")
            };
            var t3   = db.Updateable(updateObj).UpdateColumns(it => new { it.Sex, it.RealName }).ExecuteCommand();
            var t3_1 = db.Updateable(updateObj).UpdateColumns(it => it == "DepartmentID").ExecuteCommand();


            //Ignore  Name and TestId
            var t4 = db.Updateable(updateObj).IgnoreColumns(it => new { it.UserName, it.Id }).ExecuteCommand();

            //Ignore  Name and TestId
            var t5 = db.Updateable(updateObj).IgnoreColumns(it => it == "UserName" || it == "Id").With(SqlWith.UpdLock).ExecuteCommand();


            ////Use Lock
            //var t6 = db.Updateable(updateObj).With(SqlWith.UpdLock).ExecuteCommand();

            ////update List<T>
            //var t7 = db.Updateable(updateObjs).ExecuteCommand();

            ////Re Set Value
            //var t8 = db.Updateable(updateObj)
            //    .ReSetValue(it => it.Name == (it.Name + 1)).ExecuteCommand();

            ////Where By Expression
            //var t9 = db.Updateable(updateObj).Where(it => it.Id == 1).ExecuteCommand();

            ////Update By Expression  Where By Expression
            //var t10 = db.Updateable<Student>()
            //    .UpdateColumns(it => new Student() { Name = "a", CreateTime = DateTime.Now })
            //    .Where(it => it.Id == 11).ExecuteCommand();

            ////Rename
            //db.Updateable<School>().AS("Student").UpdateColumns(it => new School() { Name = "jack" }).Where(it => it.Id == 1).ExecuteCommand();
            ////Update Student set Name='jack' Where Id=1

            ////Column is null no update
            //db.Updateable(updateObj).Where(true).ExecuteCommand();

            ////sql
            //db.Updateable(updateObj).Where("id=@x", new { x = "1" }).ExecuteCommand();
            //db.Updateable(updateObj).Where("id", "=", 1).ExecuteCommand();
            //var t12 = db.Updateable<School>().AS("Student").UpdateColumns(it => new School() { Name = "jack" }).Where(it => it.Id == 1).ExecuteCommandAsync();
            //t12.Wait();

            ////update one columns
            //var count = db.Updateable<Student>().UpdateColumns(it => it.SchoolId == it.SchoolId).Where(it => it.Id == it.Id + 1).ExecuteCommand();


            ////update one columns
            //var count2 = db.Updateable<Student>().UpdateColumns(it => it.SchoolId == it.SchoolId + 1).Where(it => it.Id == it.Id + 1).ExecuteCommand();

            //var dt = new Dictionary<string, object>();
            //dt.Add("id", 1);
            //dt.Add("name", null);
            //dt.Add("createTime", DateTime.Now);
            //var t66 = db.Updateable(dt).AS("student").With(SqlWith.UpdLock).ExecuteCommand();
        }