public void TestJoin()
{
var sb = new System.Data.SQLite.SQLiteConnectionStringBuilder();
sb.DataSource = "D:\\sqlite.db3";
var connection = new System.Data.SQLite.SQLiteConnection(sb.ConnectionString);
using (IDbContext db = new DbProxyContext(connection, DbContextType.Sqlite))
{
db.Open();
var list0 = db.From <Student, Stuclass>()
.Join((a, b) => a.Id == b.Sid)
.OrderBy((a, b) => a.Id)
.OrderByDescending((a, b) => b.Id)
.Select((a, b) => new
{
a.Id,
a.Name,
Class = b.Name
}).ToList();
var(list1, total1) = db.From <Student, Stuclass>()
.Join((a, b) => a.Id == b.Sid)
.Page(1, 2)
.SelectMany((a, b) => new
{
a.Id,
a.Name,
Class = b.Name
});
var(list2, total2) = db.From <Student, Stuclass>()
.Join((a, b) => a.Id == b.Sid)
.GroupBy((a, b) => b.Name)
.Page(1, 2)
.SelectMany((a, b) => new
{
Class = b.Name,
StuNames = SqlFun.GROUP_CONCAT(a.Name)
});
var list3 = db.From <Student, Stuclass, Stuid>()
.Join((Student a, Stuclass b) => a.Id == b.Sid)
.Join((Student a, Stuid b) => a.Id == b.Sid)
.Select((a, b, c) => new
{
a.Id,
b.Name,
c.IdNum
}).ToList();
}
}
public void TestJoin()
{
var connection = new Npgsql.NpgsqlConnection("Server=127.0.0.1;Port=5432;User Id=postgres;Password=1024;Database=test;");
using (IDbContext db = new DbProxyContext(connection, DbContextType.Postgresql))
{
db.Open();
var list0 = db.From <Student, Stuclass>()
.Join((a, b) => a.Id == b.Sid)
.OrderBy((a, b) => a.Id)
.OrderByDescending((a, b) => b.Id)
.Select((a, b) => new
{
a.Id,
a.Name,
Class = b.Name
}).ToList();
var(list1, total1) = db.From <Student, Stuclass>()
.Join((a, b) => a.Id == b.Sid)
.Page(1, 2)
.SelectMany((a, b) => new
{
a.Id,
a.Name,
Class = b.Name
});
var(list2, total2) = db.From <Student, Stuclass>()
.Join((a, b) => a.Id == b.Sid)
.GroupBy((a, b) => b.Name)
.Page(1, 2)
.SelectMany((a, b) => new
{
Class = b.Name,
Count = SqlFun.COUNT(1L),
//StuNames = SqlFun.GROUP_CONCAT(a.Name)
});
var list3 = db.From <Student, Stuclass, Stuid>()
.Join((Student a, Stuclass b) => a.Id == b.Sid)
.Join((Student a, Stuid b) => a.Id == b.Sid)
.Select((a, b, c) => new
{
a.Id,
b.Name,
c.IdNum
}).ToList();
}
}
public void TestJoin()
{
var connection = new MySql.Data.MySqlClient.MySqlConnection("server=localhost;user id=root;password=1024;database=test;");
using (IDbContext db = new DbProxyContext(connection, DbContextType.Mysql))
{
db.Open();
var list0 = db.From <Student, Stuclass>()
.Join((a, b) => a.Id == b.Sid)
.OrderBy((a, b) => a.Id)
.OrderByDescending((a, b) => b.Id)
.Select((a, b) => new
{
a.Id,
a.Name,
Class = b.Name
}).ToList();
var(list1, total1) = db.From <Student, Stuclass>()
.Join((a, b) => a.Id == b.Sid)
.Page(1, 2)
.SelectMany((a, b) => new
{
a.Id,
a.Name,
Class = b.Name
});
var(list2, total2) = db.From <Student, Stuclass>()
.Join((a, b) => a.Id == b.Sid)
.GroupBy((a, b) => b.Name)
.Page(1, 2)
.SelectMany((a, b) => new
{
Class = b.Name,
StuNames = SqlFun.GROUP_CONCAT(a.Name)
});
var list3 = db.From <Student, Stuclass, Stuid>()
.Join((Student a, Stuclass b) => a.Id == b.Sid)
.Join((Student a, Stuid b) => a.Id == b.Sid)
.Select((a, b, c) => new
{
a.Id,
b.Name,
c.IdNum
}).ToList();
}
}
public void TestSelect()
{
var connection = new Npgsql.NpgsqlConnection("Server=127.0.0.1;Port=5432;User Id=postgres;Password=1024;Database=test;");
using (IDbContext db = new DbProxyContext(connection, DbContextType.Postgresql))
{
db.Open();
var list0 = db.From <Student>().Select(s => SqlFun.CONCAT(s.Name, s.Score)).ToList();
var list1 = db.From <Student>().Select().ToList();
var list2 = db.From <Student>().Where(a => a.IsDelete == false).Select().ToList();
var list3 = db.From <Student>().OrderBy(a => a.Id).OrderByDescending(a => a.Balance).Select().ToList();
var list4 = db.From <Student>().Take(4).Select().ToList();
var list5 = db.From <Student>().Take(4).Skip(2, 2).Select().ToList();
var list6 = db.From <Student>().Select(s => new { s.IsDelete, s.Id, s.Name }).ToList();
var list7 = db.From <Student>().Select(s => new Student {
IsDelete = s.IsDelete, Id = s.Id, Name = s.Name
}).ToList();
}
}
public void TestSelect()
{
var connection = new System.Data.SqlClient.SqlConnection(@"Data Source=DESKTOP-9IS2HA6\SQLEXPRESS;Initial Catalog=test;Persist Security Info=True;User ID=sa;Password=1024");
using (IDbContext db = new DbProxyContext(connection, DbContextType.SqlServer))
{
db.Open();
var list0 = db.From <Student>().Select(s => SqlFun.CONCAT(s.Name, s.Score)).ToList();
var list1 = db.From <Student>().Select().ToList();
var list2 = db.From <Student>().Where(a => a.IsDelete == false).Select().ToList();
var list3 = db.From <Student>().OrderBy(a => a.Id).OrderByDescending(a => a.Balance).Select().ToList();
var list4 = db.From <Student>().Take(4).Select().ToList();
var list5 = db.From <Student>().Take(4).Skip(2, 2).Select().ToList();
var list6 = db.From <Student>().Select(s => new { s.IsDelete, s.Id, s.Name }).ToList();
var list7 = db.From <Student>().Select(s => new Student {
IsDelete = s.IsDelete, Id = s.Id, Name = s.Name
}).ToList();
}
}
public void TestSelect()
{
var connection = new MySql.Data.MySqlClient.MySqlConnection("server=localhost;user id=root;password=1024;database=test;");
using (IDbContext db = new DbProxyContext(connection, DbContextType.Mysql))
{
db.Open();
var list0 = db.From <Student>().Select(s => SqlFun.CONCAT(s.Name, s.Score)).ToList();
var list1 = db.From <Student>().Select().ToList();
var list2 = db.From <Student>().Where(a => a.IsDelete == false).Select().ToList();
var list3 = db.From <Student>().OrderBy(a => a.Id).OrderByDescending(a => a.Balance).Select().ToList();
var list4 = db.From <Student>().Take(4).Select().ToList();
var list5 = db.From <Student>().Take(4).Skip(2, 2).Select().ToList();
var list6 = db.From <Student>().Select(s => new { s.IsDelete, s.Id, s.Name }).ToList();
var list7 = db.From <Student>().Select(s => new Student {
IsDelete = s.IsDelete, Id = s.Id, Name = s.Name
}).ToList();
}
}
public void TestGroup()
{
var connection = new Npgsql.NpgsqlConnection("Server=127.0.0.1;Port=5432;User Id=postgres;Password=1024;Database=test;");
using (IDbContext db = new DbProxyContext(connection, DbContextType.Postgresql))
{
db.Open();
var list = db.From <Student>()
.GroupBy(a => a.Name)
.Having(a => SqlFun.COUNT(1L) > 1)
.OrderByDescending(a => SqlFun.COUNT(1))
.Select(s => new
{
s.Name,
//Names = SqlFun.GROUP_CONCAT(s.Name),
Count = SqlFun.COUNT(1L),
Balance = SqlFun.SUM(s.Balance)
}).ToList();
}
}
public void TestGroup()
{
var connection = new System.Data.SqlClient.SqlConnection(@"Data Source=DESKTOP-9IS2HA6\SQLEXPRESS;Initial Catalog=test;Persist Security Info=True;User ID=sa;Password=1024");
using (IDbContext db = new DbProxyContext(connection, DbContextType.SqlServer))
{
db.Open();
var list = db.From <Student>()
.GroupBy(a => a.Name)
.Having(a => SqlFun.COUNT(1L) > 1)
.OrderByDescending(a => SqlFun.COUNT(1))
.Select(s => new
{
s.Name,
//Names = SqlFun.GROUP_CONCAT(s.Name),
Count = SqlFun.COUNT(1L),
Balance = SqlFun.SUM(s.Balance)
}).ToList();
}
}
public void TestGroup()
{
var connection = new MySql.Data.MySqlClient.MySqlConnection("server=localhost;user id=root;password=1024;database=test;");
using (IDbContext db = new DbProxyContext(connection, DbContextType.Mysql))
{
db.Open();
var list = db.From <Student>()
.GroupBy(a => a.Name)
.Having(a => SqlFun.COUNT(1L) > 1)
.OrderByDescending(a => SqlFun.COUNT(1))
.Select(s => new
{
s.Name,
Names = SqlFun.GROUP_CONCAT(s.Name),
Count = SqlFun.COUNT(1L),
Balance = SqlFun.SUM(s.Balance)
}).ToList();
}
}
public void TestGroup()
{
var sb = new System.Data.SQLite.SQLiteConnectionStringBuilder();
sb.DataSource = "D:\\sqlite.db3";
var connection = new System.Data.SQLite.SQLiteConnection(sb.ConnectionString);
using (IDbContext db = new DbProxyContext(connection, DbContextType.Sqlite))
{
db.Open();
var list = db.From <Student>()
.GroupBy(a => a.Name)
.Having(a => SqlFun.COUNT(1L) > 1)
.OrderByDescending(a => SqlFun.COUNT(1))
.Select(s => new
{
s.Name,
Names = SqlFun.GROUP_CONCAT(s.Name),
Count = SqlFun.COUNT(1L),
Balance = SqlFun.SUM(s.Balance)
}).ToList();
}
}
public void TestUpdate()
{
var sb = new System.Data.SQLite.SQLiteConnectionStringBuilder();
sb.DataSource = "D:\\sqlite.db3";
var connection = new System.Data.SQLite.SQLiteConnection(sb.ConnectionString);
using (IDbContext db = new DbProxyContext(connection, DbContextType.Sqlite))
{
var row = 0;
db.Open();//自动提交
var entity1 = new Student()
{
Id = 22,
Balance = 50,
BirthDay = DateTime.Now,
Score = Grade.E,
IsDelete = true,
Name = "faker",
Version = "test",
CreateTime = DateTime.Now,
};
var entity2 = new Student()
{
Id = 24,
Balance = 50,
BirthDay = DateTime.Now,
Score = Grade.E,
IsDelete = true,
Name = "faker",
Version = "test",
CreateTime = DateTime.Now,
};
//1.根据主键字段更新所有列
row = db.From <Student>().Update(entity1);
//2.忽略指定列
row = db.From <Student>().Filter(s => s.Version).Update(entity2);
//3.根据指定条件更新所有列
var oldVersion = "110";
row = db.From <Student>()
.Where(a => a.Id == 25 && a.Version == oldVersion)
.Update(entity1);
//4.批量修改,不推荐使用，
var students = new List <Student>();
students.Add(entity1);
students.Add(entity2);
row = db.From <Student>().Filter(a => a.CreateTime).Update(students);
//5.更新指定字段
db.From <Student>()
.Update(s => new Student()
{
Id = 26, //默认通过id更新，可以用where来重置默认
Score = Grade.F,
Name = "hihi"
});
//6.动态更新字段
var charat = "f";
db.From <Student>()
.Set(a => a.Balance, 100, 1 > 2) //false
.Set(a => a.IsDelete, true)
.Set(a => a.Balance, a => a.Balance + 200, 1 < 2) //true
.Set(a => a.Name, a => SqlFun.Replace(a.Name, "b", charat)) //true
.Where(a => a.Id == 27)
.Update();
}
}
public void TestUpdate()
{
var connection = new Npgsql.NpgsqlConnection("Server=127.0.0.1;Port=5432;User Id=postgres;Password=1024;Database=test;");
using (IDbContext db = new DbProxyContext(connection, DbContextType.Postgresql))
{
var row = 0;
db.Open();//自动提交
var entity1 = new Student()
{
Id = 22,
Balance = 50,
BirthDay = DateTime.Now,
Score = Grade.E,
IsDelete = true,
Name = "faker",
Version = "test",
CreateTime = DateTime.Now,
};
var entity2 = new Student()
{
Id = 24,
Balance = 50,
BirthDay = DateTime.Now,
Score = Grade.E,
IsDelete = true,
Name = "faker",
Version = "test",
CreateTime = DateTime.Now,
};
//1.根据主键字段更新所有列
row = db.From <Student>().Update(entity1);
//2.忽略指定列
row = db.From <Student>().Filter(s => s.Version).Update(entity2);
//3.根据指定条件更新所有列
var oldVersion = "110";
row = db.From <Student>()
.Where(a => a.Id == 25 && a.Version == oldVersion)
.Update(entity1);
//4.批量修改,不推荐使用，
var students = new List <Student>();
students.Add(entity1);
students.Add(entity2);
row = db.From <Student>().Filter(a => a.CreateTime).Update(students);
//5.更新指定字段
db.From <Student>()
.Update(s => new Student()
{
Id = 26, //默认通过id更新，可以用where来重置默认
Score = Grade.F,
Name = "hihi"
});
//6.动态更新字段
try
{
var charat = "f";
db.From <Student>()
.Set(a => a.Balance, 100, 1 > 2) //false
.Set(a => a.IsDelete, true)
.Set(a => a.Balance, a => a.Balance + SqlFun.MONEY(200), 1 < 2) //true
.Set(a => a.Name, a => SqlFun.Replace(a.Name, "b", charat)) //true
.Where(a => a.Id == 27)
.Update();
}
catch (Exception e)
{
throw;
}
}
}
public void TestPage()
{
var connection = new Npgsql.NpgsqlConnection("Server=127.0.0.1;Port=5432;User Id=postgres;Password=1024;Database=test;");
using (IDbContext db = new DbProxyContext(connection, DbContextType.Postgresql))
{
db.Open();
//第一种方式
var list1 = db.From <Student>().Page(1, 2, out long total1).Select().ToList();
var list2 = db.From <Student>().Page(2, 2, out long total2).Select().ToList();
//第二种方式
var id = 24;
var(list3, total3) = db.From <Student>().Where(a => a.Id > id).Page(1, 2).SelectMany();
var(list4, total4) = db.From <Student>().Where(a => a.Id > id).Page(2, 2).SelectMany();
//分组分页
var(glist1, gtotal1) = db.From <Student>().GroupBy(a => a.Name).Page(1, 2).SelectMany(s => new { s.Name, Count = SqlFun.COUNT(1L) });
var(glist2, gtotal2) = db.From <Student>().GroupBy(a => a.Name).Page(2, 2).SelectMany(s => new { s.Name, Count = SqlFun.COUNT(1L) });
}
}
public void TestPage()
{
var connection = new System.Data.SqlClient.SqlConnection(@"Data Source=DESKTOP-9IS2HA6\SQLEXPRESS;Initial Catalog=test;Persist Security Info=True;User ID=sa;Password=1024");
using (IDbContext db = new DbProxyContext(connection, DbContextType.SqlServer))
{
db.Open();
//第一种方式
var list1 = db.From <Student>().Page(1, 2, out long total1).Select().ToList();
var list2 = db.From <Student>().Page(2, 2, out long total2).Select().ToList();
//第二种方式，只分页不计数的可以通过skip实现
var id = 24;
var(list3, total3) = db.From <Student>().Where(a => a.Id > id).Page(1, 2).SelectMany();
var(list4, total4) = db.From <Student>().Where(a => a.Id > id).Page(2, 2).SelectMany();
//分组分页
var(glist1, gtotal1) = db.From <Student>().GroupBy(a => a.Name).Page(1, 2).SelectMany(s => new { s.Name, Count = SqlFun.COUNT(1L) });
var(glist2, gtotal2) = db.From <Student>().GroupBy(a => a.Name).Page(2, 2).SelectMany(s => new { s.Name, Count = SqlFun.COUNT(1L) });
}
}
public void TestUpdate()
{
var connection = new System.Data.SqlClient.SqlConnection(@"Data Source=DESKTOP-9IS2HA6\SQLEXPRESS;Initial Catalog=test;Persist Security Info=True;User ID=sa;Password=1024");
using (IDbContext db = new DbProxyContext(connection, DbContextType.SqlServer))
{
var row = 0;
db.Open();//自动提交
var entity1 = new Student()
{
Id = 22,
Balance = 50,
BirthDay = DateTime.Now,
Score = Grade.E,
IsDelete = true,
Name = "faker",
Version = "test",
CreateTime = DateTime.Now,
};
var entity2 = new Student()
{
Id = 24,
Balance = 50,
BirthDay = DateTime.Now,
Score = Grade.E,
IsDelete = true,
Name = "faker",
Version = "test",
CreateTime = DateTime.Now,
};
//1.根据主键字段更新所有列
row = db.From <Student>().Update(entity1);
//2.忽略指定列
row = db.From <Student>().Filter(s => s.Version).Update(entity2);
//3.根据指定条件更新所有列
var oldVersion = "110";
row = db.From <Student>()
.Where(a => a.Id == 25 && a.Version == oldVersion)
.Update(entity1);
//4.批量修改,不推荐使用，
var students = new List <Student>();
students.Add(entity1);
students.Add(entity2);
row = db.From <Student>().Filter(a => a.CreateTime).Update(students);
//5.更新指定字段
row = db.From <Student>()
.Update(s => new Student()
{
Id = 26, //默认通过id更新，可以用where来重置默认
Score = Grade.F,
Name = "hihi"
});
//6.动态更新字段
var charat = "f";
row = db.From <Student>()
.Set(a => a.Balance, 100, 1 > 2) //false
.Set(a => a.IsDelete, true)
.Set(a => a.Balance, a => a.Balance + 200, 1 < 2) //true
.Set(a => a.Name, a => SqlFun.Replace(a.Name, "b", charat)) //true
.Where(a => a.Id == 27)
.Update();
}
}
public void TestPage()
{
var sb = new System.Data.SQLite.SQLiteConnectionStringBuilder();
sb.DataSource = "D:\\sqlite.db3";
var connection = new System.Data.SQLite.SQLiteConnection(sb.ConnectionString);
using (IDbContext db = new DbProxyContext(connection, DbContextType.Sqlite))
{
db.Open();
//第一种方式
var list1 = db.From <Student>().Page(1, 2, out long total1).Select().ToList();
var list2 = db.From <Student>().Page(2, 2, out long total2).Select().ToList();
//第二种方式
var id = 24;
var(list3, total3) = db.From <Student>().Where(a => a.Id > id).Page(1, 2).SelectMany();
var(list4, total4) = db.From <Student>().Where(a => a.Id > id).Page(2, 2).SelectMany();
//分组分页
var(glist1, gtotal1) = db.From <Student>().GroupBy(a => a.Name).Page(1, 2).SelectMany(s => new { s.Name, Count = SqlFun.COUNT(1L) });
var(glist2, gtotal2) = db.From <Student>().GroupBy(a => a.Name).Page(2, 2).SelectMany(s => new { s.Name, Count = SqlFun.COUNT(1L) });
}
}
public void TestPage()
{
var connection = new MySql.Data.MySqlClient.MySqlConnection("server=localhost;user id=root;password=1024;database=test;");
using (IDbContext db = new DbProxyContext(connection, DbContextType.Mysql))
{
db.Open();
//第一种方式
var list1 = db.From <Student>().Page(1, 2, out long total1).Select().ToList();
var list2 = db.From <Student>().Page(2, 2, out long total2).Select().ToList();
//第二种方式
var id = 24;
var(list3, total3) = db.From <Student>().Where(a => a.Id > id).Page(1, 2).SelectMany();
var(list4, total4) = db.From <Student>().Where(a => a.Id > id).Page(2, 2).SelectMany();
//分组分页
var(glist1, gtotal1) = db.From <Student>().GroupBy(a => a.Name).Page(1, 2).SelectMany(s => new { s.Name, Count = SqlFun.COUNT(1L) });
var(glist2, gtotal2) = db.From <Student>().GroupBy(a => a.Name).Page(2, 2).SelectMany(s => new { s.Name, Count = SqlFun.COUNT(1L) });
}
}
