1、在model.biz的静态构造类中添加分库分表策略
static History()
{
// 添加分库分表策略
Meta.ShardPolicy = new TimeShardPolicy(nameof(CreateTime), Meta.Factory)
{
ConnPolicy = "HDB_{1:yyMM}",
TablePolicy = "History_{1:yyMMdd}",
Step = TimeSpan.FromDays(1),
};
Meta.Modules.Add<UserModule>();
Meta.Modules.Add<TimeModule>();
Meta.Modules.Add<IPModule>();
}
2、构造测试数据
static void TestByDate()
{
XTrace.WriteLine("按时间分表分库,每月一个库,每天一张表");
// 预先准备好各个库的连接字符串,动态增加,也可以在配置文件写好
var start = DateTime.Today;
for (var i = 0; i < 12; i++)
{
var dt = new DateTime(start.Year, i + 1, 1);
var connName = $"HDB_{dt:yyMM}";
DAL.AddConnStr(connName, $"data source=timeData\\{connName}.db", null, "sqlite");
}
// 每月一个库,每天一张表
start = new DateTime(start.Year, 1, 1);
for (var i = 0; i < 365; i++)
{
var dt = start.AddDays(i);
History.Meta.ConnName = $"HDB_{dt:yyMM}";
History.Meta.TableName = $"History_{dt:yyMMdd}";
// 插入一批数据
var list = new List<History>();
for (var n = 0; n < 1000; n++)
{
var entity = new History
{
Category = "交易",
Action = "转账",
CreateUserID = 1234,
CreateTime = dt,
Remark = $"[{Rand.NextString(6)}]向[{Rand.NextString(6)}]转账[¥{Rand.Next(1_000_000) / 100d}]"
};
list.Add(entity);
}
// 批量插入。两种写法等价
//list.BatchInsert();
list.Insert(true);
}
}
3、测试跨库跨表数据查询
static void Search()
{
var start = DateTime.Today;
for (var i = 0; i < 12; i++)
{
var dt = new DateTime(start.Year, i + 1, 1);
var connName = $"HDB_{dt:yyMM}";
DAL.AddConnStr(connName, $"data source=timeData\\{connName}.db", null, "sqlite");
}
//跨表
//var dt1 = new DateTime(start.Year, 1, 1);
//var dt2 = new DateTime(start.Year, 1, 10);
//跨库
var dt1 = Convert.ToDateTime("2021-1-31");
var dt2 = Convert.ToDateTime("2021-3-1");
var exp = new WhereExpression();
exp &= History._.CreateTime.Between(dt1, dt2);
PageParameter pageParameter = new PageParameter
{
PageSize = 3000,
PageIndex = 1,
RetrieveTotalCount = true
};
var list = History.FindAll(exp, pageParameter).OrderBy(x=>x.CreateTime).OrderBy(x=>x.ID);
var allcount = pageParameter.TotalCount;
var re = Newtonsoft.Json.JsonConvert.SerializeObject(list.OrderBy(x=>x.ID));
Console.WriteLine(re);
Console.WriteLine(allcount);
}