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