/// <summary>
/// 批量更新
/// </summary>
/// <param name="p"></param>
/// <param name="db"></param>
private void Update(Student2 p, SqlSugarClient db)
{
db.UpdateRange(StudentListUpd);
var list = db.Queryable<Student>().ToList();
db.SqlBulkReplace(StudentListUpd2);
list = db.Queryable<Student>().ToList();
}
/// <summary>
/// 测试select new
/// </summary>
/// <param name="p"></param>
/// <param name="db"></param>
private void SelectNew(Student2 p, SqlSugarClient db)
{
//测试用例
var queryable = db.Queryable<Student>().Where(c => c.id < 10)
.Select<V_Student>(c => new V_Student { id = c.id, name = c.name, AreaName = "默认地区", SchoolName = "默认学校", SubjectName = "NET" });
var list = queryable.ToList();
//多表操作将Student转换成V_Student
var queryable2 = db.Queryable<Student>()
.JoinTable<School>((s1, s2) => s1.sch_id == s2.id)
.Where<School>((s1, s2) => s2.id < 10)
.Select<School, V_Student>((s1, s2) => new V_Student() { id = s1.id, name = s1.name, AreaName = "默认地区", SchoolName = s2.name, SubjectName = "NET" });//select new 目前只支持这种写法
var list2 = queryable2.ToList();
//select字符串 转换成V_Student
var list3 = db.Queryable<Student>()
.JoinTable<School>((s1, s2) => s1.sch_id == s2.id)
.Where(s1 => s1.id <= 3)
.Select<V_Student>("s1.*,s2.name SchoolName")
.ToList();
//新容器转换函数的支持 只支持ObjToXXX和Convert.ToXXX
var f1 = db.Queryable<InsertTest>().Select<Student>(it => new Student()
{
name = it.d1.ObjToString(),
id = it.int1.ObjToInt() // 支持ObjToXXX 所有函数
}).ToList();
var f2 = db.Queryable<InsertTest>().Select<Student>(it => new Student()
{
name = Convert.ToString(it.d1),//支持Convet.ToXX所有函数
id = it.int1.ObjToInt(),
sex = Convert.ToString(it.d1),
}).ToList();
var f3 = db.Queryable<InsertTest>()
.JoinTable<InsertTest>((i1, i2) => i1.id == i2.id)
.Select<InsertTest, Student>((i1, i2) => new Student()
{
name = Convert.ToString(i1.d1), //多表查询例子
id = i1.int1.ObjToInt(),
sex = Convert.ToString(i2.d1),
}).ToList();
//Select 外部参数用法
var f4 = db.Queryable<InsertTest>().Where("1=1", new { id = 100 }).Select<Student>(it => new Student()
{
id = "@id".ObjToInt(), //取的是 100 的值
name = "张三",//内部参数可以直接写
sex = it.txt,
sch_id = it.id
}).ToList();
var f6 = db.Queryable<InsertTest>()
.JoinTable<InsertTest>((i1, i2) => i1.id == i2.id)
.Where("1=1", new { id = 100, name = "张三", isOk = true }) //外部传参给@id
.Select<InsertTest, Student>((i1, i2) => new Student()
{
name = "@name".ObjToString(), //多表查询例子
id = "@id".ObjToInt(),
sex = i2.txt,
sch_id = 1,
isOk = "@isOk".ObjToBool()
}).ToList();
try
{
//测试用例
db.Queryable<Student>().Where(c => c.id < 10)
.Select<V_Student>(c => new V_Student { id = c.id, name = _p.name, AreaName = "默认地区", SchoolName = "默认学校", SubjectName = "NET" }).ToList();
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
try
{
//测试用例
db.Queryable<Student>().Where(c => c.id < 10)
.Select<V_Student>(c => new V_Student { id = c.id, name = p.name, AreaName = "默认地区", SchoolName = "默认学校", SubjectName = "NET" }).ToList();
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
try
{
//测试用例
db.Queryable<Student>().Where(c => c.id < 10)
.Select<V_Student>(c => new V_Student { id = c.id, name = Getp().name, AreaName = "默认地区", SchoolName = "默认学校", SubjectName = "NET" }).ToList();
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
try
{
//测试用例
db.Queryable<Student>().Where(c => c.id < 10)
.Select<V_Student>(c => new V_Student { id = c.id, name = Getp2(), AreaName = "默认地区", SchoolName = "默认学校", SubjectName = "NET" }).ToList();
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
try
{
//测试用例
db.Queryable<InsertTest>()
.JoinTable<InsertTest>((i1, i2) => i1.id == i2.id)
.Select<InsertTest, Student>((i1, i2) => new Student()
{
name = Getp2(), //多表查询例子
id = i1.int1.ObjToInt(),
sex = Convert.ToString(i2.d1),
}).ToList();
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
try
{
//测试用例
db.Queryable<InsertTest>()
.JoinTable<InsertTest>((i1, i2) => i1.id == i2.id)
.Select<InsertTest, Student>((i1, i2) => new Student()
{
name = Getp().name, //多表查询例子
id = i1.int1.ObjToInt(),
sex = Convert.ToString(i2.d1),
}).ToList();
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
try
{
//测试用例
db.Queryable<InsertTest>()
.JoinTable<InsertTest>((i1, i2) => i1.id == i2.id)
.Select<InsertTest, Student>((i1, i2) => new Student()
{
name = Getp().name, //多表查询例子
id = i1.int1.ObjToInt(),
sex = Convert.ToString(i2.d1),
}).ToList();
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
try
{
//测试用例
db.Queryable<InsertTest>()
.JoinTable<InsertTest>((i1, i2) => i1.id == i2.id)
.Select<InsertTest, Student>((i1, i2) => new Student()
{
name = Getp().name, //多表查询例子
id = i1.int1.ObjToInt() + 1,
sex = Convert.ToString(i2.d1),
}).ToList();
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
try
{
//测试用例
db.Queryable<InsertTest>()
.JoinTable<InsertTest>((i1, i2) => i1.id == i2.id)
.Select<InsertTest, Student>((i1, i2) => new Student()
{
name = p.name, //多表查询例子
sex = Convert.ToString(i2.d1),
}).ToList();
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
}
/// <summary>
/// 查询测试
/// </summary>
/// <param name="p"></param>
/// <param name="d"></param>
private void Select(Student2 p, SqlSugarClient db)
{
//查询第一条
var select1= db.Queryable<Student>().Single(it => it.id == p.id);
var select2 = db.Queryable<Student>().Single(it => it.id == _p.id);
//查询分页 id3-id5
var select3 = db.Queryable<Student>().OrderBy(it=>it.id).Skip(2).Take(3).ToList();
//查询分页 id4-id6
var select4 = db.Queryable<Student>().OrderBy(it => it.id).ToPageList(2,3).ToList();
//2表join
var join1 = db.Queryable<Student>().JoinTable<School>((st, sc) => st.sch_id == sc.id)
.Select<School,V_Student>((st, sc) => new V_Student() {SchoolName=sc.name,id=st.id, name=st.name}).ToList();
//3表join
var join2 = db.Queryable<Student>()
.JoinTable<School>((st, sc) => st.sch_id == sc.id)
.JoinTable<School,Area>((st,sc,a)=>sc.AreaId==a.id)
.Select<School,Area, V_Student>((st, sc,a) => new V_Student() { SchoolName = sc.name, id = st.id, name = st.name,AreaName=a.name }).ToList();
}
/// <summary>
/// 拉姆达测试
/// </summary>
/// <param name="p"></param>
/// <param name="db"></param>
private void Exp(Student2 p, SqlSugarClient db)
{
//解析拉姆达各种情况组合
//基本
var t1 = db.Queryable<Student>().Where(it => it.id == 1).ToList();
var t2 = db.Queryable<Student>().Where(it => it.id == p.id).ToList();
var t3 = db.Queryable<Student>().Where(it => it.id == _p.id).ToList();
var t4 = db.Queryable<Student>().Where(it => it.id == Getp().id).ToList();
var t5 = db.Queryable<Student>().Where(it => it.id == MyCost.p.id).ToList();
//BOOL
var t11 = db.Queryable<Student2>("Student").Where(it => it.isOk).ToList();
var t21 = db.Queryable<Student2>("Student").Where(it => it.isOk == MyCost.p.isOk).ToList();
var t31 = db.Queryable<Student2>("Student").Where(it => !it.isOk).ToList();
var t41 = db.Queryable<Student2>("Student").Where(it => it.isOk == true).ToList();
var t51 = db.Queryable<Student2>("Student").Where(it => it.isOk == false).ToList();
var t61 = db.Queryable<Student2>("Student").Where(it => it.isOk == _p.isOk).ToList();
var t71 = db.Queryable<Student2>("Student").Where(it => !it.isOk && !p.isOk == it.isOk).ToList();
var t91 = db.Queryable<Student2>("Student").Where(it => _p.isOk == false).ToList();
var t81 = db.Queryable<Student>().Where(it => it.isOk == false).ToList();
var t111 = db.Queryable<Student2>("Student").Where(it => it.isOk && false).ToList();
//length
var c1 = db.Queryable<Student>().Where(c => c.name.Length > 4).ToList();
var c2 = db.Queryable<Student>().Where(c => c.name.Length > _p.name.Length).ToList();
var c3 = db.Queryable<Student>().Where(c => c.name.Length > "aa".Length).ToList();
var c4 = db.Queryable<Student>().Where(c => c.name.Length > Getp().id).ToList();
//Equals
var a1 = db.Queryable<Student>().Where(c => c.name.Equals(null)).ToList();
var x = new InsertTest() { };
var x1 = db.Queryable<Student>().Where(c => c.name.Equals(x.int1)).ToList();
var a2 = db.Queryable<Student>().Where(c => c.name.Equals(p.name)).ToList();
var a4 = db.Queryable<Student>().Where(c => c.name.Equals(Getp().name)).ToList();
//Contains
var s = db.Queryable<Student>().Where(c => c.name.Contains(null)).ToList();
var s0 = new InsertTest() { };
var s1 = db.Queryable<Student>().Where(c => c.name.Contains(x.v1)).ToList();
var s3 = db.Queryable<Student>().Where(c => c.name.Contains(p.name)).ToList();
var s4 = db.Queryable<Student>().Where(c => c.name.Contains(Getp().name)).ToList();
var s5 = db.Queryable<Student>().Where(c => c.name.StartsWith(Getp().name)).ToList();
var s6 = db.Queryable<Student>().Where(c => c.name.EndsWith(Getp().name)).ToList();
//异常处理测试,防止程序中出现未知错误
try
{
var e6 = db.Queryable<Student>().Where(c => Getp().name.StartsWith(c.name)).ToList();
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
try
{
var e6 = db.Queryable<Student>().Where(c => Getp().name.Equals(c.name)).ToList();
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
try
{
var e6 = db.Queryable<Student>().Where(c => c.name.First() != null).ToList();
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
//组合测试
var z = db.Queryable<Student>().Where(c => (c.name.Equals(Getp().name) || c.name == p.name) && true && c.id > 1).ToList();
var z23 = db.Queryable<Student>().Where(c => !string.IsNullOrEmpty(c.name) || (c.id == 1 || c.name.Contains(p.name))).ToList();
var z2 = db.Queryable<Student>().Where(c => !string.IsNullOrEmpty(c.name) || !string.IsNullOrEmpty(c.name)).ToList();
}
/// <summary>
/// 删除测试
/// </summary>
/// <param name="p"></param>
/// <param name="db"></param>
private void Delete(Student2 p, SqlSugarClient db)
{
db.Delete<Student>(it => it.id == 1);
db.Delete<Student>(new Student() { id=2});
db.Delete<Student,int>(3,4);
db.Delete<Student, int>(it=>it.id,5,6);
var list = db.Queryable<Student>().ToList();
}
public void Init()
{
Student2 p = new Student2() { id = 2, name = "张表", isOk = true };
Console.WriteLine("启动Test.Init");
using (var db = SugarDao.GetInstance())
{
//初始化数据
InitData(db);
//查询测试
Select(p,db);
//拉姆达测试
Exp(p, db);
//新容器转换测试
SelectNew(p, db);
//更新测试
Update(p, db);
//删除测试
//Delete(p,db);
}
}
