建一个自定义数据表
[SplitTable(SplitType._Custom01)]
public class PointInfo:BaseEntity
{
public enum DataTypeEnum
{
Electricity,
Voltage,
Temperature,
}
/// <summary>
/// 通道枚举
/// </summary>
public enum ChannelEnum
{
CH1,
CH2,
CH3,
CH4,
}
long taskId;
ChannelEnum channel;
DataTypeEnum dataType;
string? pointName;
string? describle;
double x;
double y;
double z;
double qx;
double qy;
double qz;
[SugarColumn(ColumnName = "point_name", ColumnDescription = "点名称", IsNullable = true)]
public string? PointName { get => pointName; set => pointName = value; }
[SugarColumn(ColumnName = "point_describle", ColumnDescription = "点描述", IsNullable = true)]
public string? Describle { get => describle; set => describle = value; }
[SugarColumn(ColumnName = "pt_x")]
public double X { get => x; set => x = value; }
[SugarColumn(ColumnName = "pt_y")]
public double Y { get => y; set => y = value; }
[SugarColumn(ColumnName = "pt_z")]
public double Z { get => z; set => z = value; }
[SugarColumn(ColumnName = "pt_qx")]
public double Qx { get => qx; set => qx = value; }
[SugarColumn(ColumnName = "pt_qy")]
public double Qy { get => qy; set => qy = value; }
[SugarColumn(ColumnName = "pt_qz")]
public double Qz { get => qz; set => qz = value; }
[SplitField] //分表字段
public long TaskId { get => taskId; set => taskId = value; }
[SugarColumn(ColumnName = "channel")]
public ChannelEnum Channel { get => channel; set => channel = value; }
[SugarColumn(ColumnName = "dataType")]
public DataTypeEnum DataType { get => dataType; set => dataType = value; }
再自建一个自定义的数据服务,用来自定义分表的模式
public class SplitableService : ISplitTableService
{
public List<SplitTableInfo> GetAllTables(ISqlSugarClient db, EntityInfo EntityInfo, List<DbTableInfo> tableInfos)
{
List<SplitTableInfo> result = new List<SplitTableInfo>();
foreach (var item in tableInfos)
{
if (item.Name.Contains("PointInfo_Task"))
{
SplitTableInfo data = new SplitTableInfo();
{
data.TableName= item.Name;
}
result.Add(data);
}
}
return result.OrderBy(it =>it.TableName).ToList();
}
public object GetFieldValue(ISqlSugarClient db, EntityInfo entityInfo, SplitType splitType, object entityValue)
{
var splitColumn = entityInfo.Columns.FirstOrDefault(it =>it.PropertyInfo.GetCustomAttribute<SplitFieldAttribute>() != null);
var value = splitColumn.PropertyInfo.GetValue(entityValue, null);
return value;
}
public string GetTableName(ISqlSugarClient db, EntityInfo EntityInfo)
{
return EntityInfo.DbTableName + "_Task";
}
public string GetTableName(ISqlSugarClient db, EntityInfo EntityInfo, SplitType type)
{
return EntityInfo.DbTableName + "_Task";
}
public string GetTableName(ISqlSugarClient db, EntityInfo entityInfo, SplitType splitType, object fieldValue)
{
return entityInfo.DbTableName + "_Task" + fieldValue;
}
再在一个service库中添加一下代码就行了
public PointService(IMapper _mapper, IPointInfoRespository _pointInfoService)
{
mapper= _mapper;
pointInfoService= _pointInfoService;
DbScoped.Sugar.CurrentConnectionConfig.ConfigureExternalServices.SplitTableService = new SplitableService();
}
数据库增删改查的服务
public RepositoryPointInfo()
{
}
/// <summary>
/// 分表查询当前Task下所有的点,然后选择返回部分的信息
/// </summary>
/// <param name="taskId"></param>
/// <returns></returns>
public async Task<List<PointInfo>> QueryAsyncByTable(long taskId,DataTypeEnum dataTypeEnum , ChannelEnum channel)
{
//查询需要的表格
var tbName = DbScoped.Sugar.Context.SplitHelper<PointInfo>().GetTableName(taskId);
return await DbScoped.Sugar.Context.Queryable<PointInfo>().SplitTable(tas => tas.InTableNames(tbName)).Where(t => t.DataType.Equals(dataTypeEnum) && t.Channel.Equals(channel)).Select(t => new PointInfo() { Id = t.Id, Channel = t.Channel, DataType = t.DataType, TaskId = t.TaskId, X = t.X, CreateTime = t.CreateTime }).OrderBy(t=>t.CreateTime).ToListAsync();
}
/// <summary>
/// 分表查询当前Task下所有的点 分页查询
/// </summary>
/// <param name="taskId"></param>
/// <returns></returns>
public async Task<List<PointInfo>> QueryAsyncByTableByPage(long taskId, DataTypeEnum dataTypeEnum, ChannelEnum channel, int page, int size, RefAsync<int> total)
{
var tbName = DbScoped.Sugar.Context.SplitHelper<PointInfo>().GetTableName(taskId);
//return await DbScoped.Sugar.Context.Queryable<PointInfo>().SplitTable(tas => tas.InTableNames(tbName)).ToPageListAsync(page, size, total);
return await DbScoped.Sugar.Context.Queryable<PointInfo>().SplitTable(tas => tas.InTableNames(tbName)).Where(t => t.DataType.Equals(dataTypeEnum) && t.Channel.Equals(channel)).Select(t => new PointInfo() { Id = t.Id, Channel = t.Channel, DataType = t.DataType, TaskId = t.TaskId, X = t.X, CreateTime = t.CreateTime }).OrderBy(t => t.CreateTime).ToPageListAsync(page, size, total);
}
/// <summary>
/// 分表插入
/// </summary>
/// <param name="entity"></param>
/// <returns></returns>
public async Task<long> CreateAsyncByTable(PointInfo entity)
{
return await DbScoped.Sugar.Context.Insertable(entity).SplitTable().ExecuteReturnSnowflakeIdAsync();
}
/// <summary>
/// 批量插入
/// </summary>
/// <param name="entities"></param>
/// <returns></returns>
public async Task<List<long>> CreateListAsyncByTable(List<PointInfo> entities)
{
if (entities == null)
return new List<long>();
var inster = base.Context.Insertable(entities.ToList()).SplitTable();
if (inster == null)
{
return new List<long>(); ;
}
return await inster.ExecuteReturnSnowflakeIdListAsync();
}
/// <summary>
/// 批量插入
/// </summary>
/// <param name="entities"></param>
/// <returns></returns>
public List<long> CreateListAsyncByTableByMainThread(List<PointInfo> entities)
{
if (entities == null)
return new List<long>();
var inster = base.Context.Insertable(entities).SplitTable();
if (inster == null)
{
return new List<long>(); ;
}
return inster.ExecuteReturnSnowflakeIdList();
}
public async Task<List<PointInfo>> QueryAsyncByTable(long taskId)
{
var tbName = DbScoped.Sugar.Context.SplitHelper<PointInfo>().GetTableName(taskId);
return await DbScoped.Sugar.Context.Queryable<PointInfo>().SplitTable(tas => tas.InTableNames(tbName)).ToListAsync();
}