lnskydb

lnskydb是基于dapper的lambda扩展,支持按时间分库分表,也可以自定义分库分表方法

开源地址 https://github.com/liningit/lnskydb

在此非常感谢skychensky其中lambda表达式的解析参考了他的开源项目

下面是用productsalebydayentity作为示例,其中statisticaldate为分库分表字段,如果是对分库分表对象进行数据库操作则必须传入statisticaldate或者设置dbmodel_shuffledtempdate指定是那个库和表

1. 仓储的创建

仓储的创建有两种方式一种是通过repositoryfactory.create<productsalebydayentity>()创建irepository<productsalebydayentity>
还有一种是创建一个仓储类继承repository<productsalebydayentity>

public interface iproductsalebydayrepository : irepository<productsalebydayentity>
{
}
public class productsalebydayrepository : repository<productsalebydayentity>
{
}
//调用的地方可以
iproductsalebydayrepository repository=new productsalebydayrepository();

2. 查询

2.1 根据主键查询

var repository = repositoryfactory.create<productsalebydayentity>();
var entity = repository.get(new productsalebydayentity
{
    dbmodel_shuffledtempdate = new datetime(2019, 01, 01),//这儿表示差19年1月的库和表
    sysno = sysno
});

2.2 根据where条件查询

var sttime = new datetime(2019, 1, 15);
var endtime = new datetime(2019, 2, 11);
var repository = repositoryfactory.create<productsalebydayentity>();
var query = queryfactory.create<productsalebydayentity>(m => m.shopname.contains("测试"));
query.and(m => m.statisticaldate >= sttime);
query.and(m => m.statisticaldate < endtime.date.adddays(1));
query.orderbydescing(m => m.statisticaldate);//如果是查多个库表必须按分库分表的字段降序排列
query.starsize = 20; //可以设置查询行数及开始行数
query.rows = 10;
//分库的传入sttime,endtime会自动根据时间查询符合条件的库和表
var lst = repository.getlist(query, sttime, endtime);

如果可以确定统计时间也可以查指定的库表进行单表查询

var sttime = new datetime(2019, 1, 15);
var endtime = new datetime(2019, 1, 18);
var repository = repositoryfactory.create<productsalebydayentity>();
var query = queryfactory.create<productsalebydayentity>(m => m.shopname.contains("测试"));
query.and(m => m.statisticaldate >= sttime);
query.and(m => m.statisticaldate < endtime.date.adddays(1));
query.dbmodel.dbmodel_shuffledtempdate = new datetime(2019, 01, 01);//这儿表示查19年1月的库和表
query.orderbydescing(m => m.statisticaldate);//单表查询可以随意排序
query.starsize = 20;
query.rows = 10;
var lst= repository.getlist(query);

2.3 分页查询

var sttime = new datetime(2019, 1, 15);
var endtime = new datetime(2019, 2, 11);
var repository = repositoryfactory.create<productsalebydayentity>();
var query = queryfactory.create<productsalebydayentity>(m => m.shopname.contains("测试"));
query.and(m => m.statisticaldate >= sttime);
query.and(m => m.statisticaldate < endtime.date.adddays(1));
query.orderbydescing(m => m.statisticaldate);//如果是查多个库表必须按分库分表的字段降序排列
query.starsize = 20;
query.rows = 10;
//分库的传入sttime,endtime会自动根据时间查询符合条件的库和表
var paging = repository.getpaging(query, sttime, endtime);
var count = paging.totalcount;
var lst = paging.tolist();//或者paging.items

如果可以确定统计时间也可以查指定的库表

var sttime = new datetime(2019, 1, 15);
var endtime = new datetime(2019, 1, 18);
var repository = repositoryfactory.create<productsalebydayentity>();
var query = queryfactory.create<productsalebydayentity>(m => m.shopname.contains("测试"));
query.and(m => m.statisticaldate >= sttime);
query.and(m => m.statisticaldate < endtime.date.adddays(1));
query.dbmodel.dbmodel_shuffledtempdate = new datetime(2019, 01, 01);//这儿表示查19年1月的库和表
query.orderbydescing(m => m.statisticaldate);//单表查询可以随意排序
query.starsize = 20;
query.rows = 10;
var paging= repository.getpaging(query);
var count = paging.totalcount;
var lst = paging.tolist();//或者paging.items

3. 添加

var addentity = new productsalebydayentity()
{
    sysno = guid.newguid(),
    datasource = "测试来源",
    productid = guid.newguid(),               
    shopid = guid.newguid(),
    shopname = "测试店铺",
    productname = "测试商品",
    outproductid = guid.newguid().tostring(),
    importgroupid = guid.newguid(),
    statisticaldate = datetime.now//分库分表字段是必须的
};
var repository = repositoryfactory.create<productsalebydayentity>();
//如果新增主键是自增列会自动赋值自增列值到主键
repository.add(addentity);

4. 更新

4.1 根据主键更新

var updateentity = new productsalebydayentity()
{
    sysno = guid.parse("650bc09c-2b9c-467b-a457-8b4853cc1f0f"),
    datasource = "测试来源修改",
    shopname = "店铺修改",
    statisticaldate = new datetime(2019,01,05),//如果statisticaldate赋值了则根据statisticaldate找库表,然后根据主键更新,statisticaldate也会被更新成所赋的值
    //如果不想更新statisticaldate可以用下面这句话
    // dbmodel_shuffledtempdate=new datetime(2019,01,05),//如果不想更新statisticaldate字段则用这句话来确定是那个库及表
};
var repository = repositoryfactory.create<productsalebydayentity>();
//根据主键更新其他字段
return repository.update(updateentity);

4.2 根据where条件更新

var updateentity = new productsalebydayentity()
{
    datasource = "测试来源修改",
    shopname = "店铺修改where",
    dbmodel_shuffledtempdate = new datetime(2019, 01, 05),//如果用这句话来确定是那个库表
    // statisticaldate = statisticaldate,//如果要更新statisticaldate则可以用这句话替代上面那句话
};
var repository = repositoryfactory.create<productsalebydayentity>();
var where = queryfactory.create<productsalebydayentity>(m => m.shopname == "测试店铺1" && m.statisticaldate > new datetime(2019, 01, 03));//where是更新条件
//注意如果是更新用的是实体类的dbmodel_shuffledtempdate query中的无效
return repository.update(updateentity, where);

5. 删除

5.1 根据主键删除

var deleteentity = new productsalebydayentity()
{
    sysno = guid.parse("650bc09c-2b9c-467b-a457-8b4853cc1f0f"),
    dbmodel_shuffledtempdate = new datetime(2019, 01, 05),//对于分库分表来说dbmodel_shuffledtempdate是必须的用来确认是那个库表
};
var repository = repositoryfactory.create<productsalebydayentity>();
return repository.delete(deleteentity);

5.2 根据where条件删除

var repository = repositoryfactory.create<productsalebydayentity>();
var where = queryfactory.create<productsalebydayentity>();
where.dbmodel.dbmodel_shuffledtempdate = new datetime(2019, 01, 01);
//queryisearch方法表示搜索里面空格表示或+表示且
//如 导入+手工 自动+生成 表示字段必须同时拥有导入和手工或者自动和生成
//生成sql是 and ((datasource like '%导入%' and datasource like '%手工%') or datasource like '%自动%' and datasource like '%生成%')            
where.queryisearch(m => m.datasource, "新+更");
where.queryisearch(m => m.shopname, "批量修改");
//注意如果是更新用的是实体类的dbmodel_shuffledtempdate query中的无效
return repository.delete(where);

6. 多线程处理

对于mvc每次请求都会在请求结束时将数据库连接关闭,如果是新建线程则需要在线程开始调用dbtool.beginthread();
并且在线程结束为止调用dbtool.closeconnections();关闭连接

public class threadtool
{
    public static void queueuserworkitem(action action)
    {
        threadpool.queueuserworkitem(delegate
        {
            dbtool.beginthread();
            try
            {
                action();
            }
            finally
            {
                dbtool.closeconnections();
            }
        });
    }
}
threadtool.queueuserworkitem(threaddo);//调用

7. 实体类t4自动生成

在lnskydb.demo\t4中有可以自动生成实体类的t4模版.
其中dbhelper.ttinclude中的config是配置数据库的
entity.tt是生成实体的t4模版.大家可以根据自己的情况修改

黄山市民网：https://www.huangshanshimin.com/