实体类
///<summary>
///
///</summary>
[SplitTable(SplitType._Custom01)]
[SugarTable("CommoditySubTableArea")]
public partial class CommoditySubTableArea
{
/// <summary>
/// 省份
/// </summary>
[SplitField]
public string? Province { get; set; }
/// <summary>
/// Desc:
/// Default:
/// Nullable:False
/// </summary>
[SugarColumn(IsPrimaryKey = true)]
public Guid Id { get; set; }
/// <summary>
/// Desc:
/// Default:
/// Nullable:True
/// </summary>
public long? ProductId { get; set; }
/// <summary>
/// Desc:
/// Default:
/// Nullable:True
/// </summary>
public int? CategoryId { get; set; }
/// <summary>
/// Desc:
/// Default:
/// Nullable:True
/// </summary>
public string? Title { get; set; }
/// <summary>
/// Desc:
/// Default:
/// Nullable:True
/// </summary>
public decimal? Price { get; set; }
/// <summary>
/// Desc:
/// Default:
/// Nullable:True
/// </summary>
public string? Url { get; set; }
/// <summary>
/// Desc:
/// Default:
/// Nullable:True
/// </summary>
public string? ImageUrl { get; set; }
/// <summary>
/// 创建时间
/// </summary>
public DateTime Createtime { get; set; }
}
分表帮助类
public class AreaSubTableService : ISplitTableService
{
//横向分表---以保存到某一些的数据为分表的维度
public static Dictionary<string, string> _AreaDictionary;
static AreaSubTableService()
{
_AreaDictionary = new Dictionary<string, string>()
{
{"Liaoning","辽宁" },
{"Heilongjiang","黑龙江" },
{"Jilin","吉林" },
{"Hebei","河北" },
{"Henan","河南" },
{"Shandong","山东" },
{"Shaanxi","陕西" },
{"ShanxiS","山西" },
{"Jiangsu","江苏" },
{"Zhejiang","浙江" },
{"Fujian","福建" },
{"Guangdong","广东" },
{"Hainan","海南" },
{"Yunnan","云南" },
{"Jiangxi","江西" },
{"Hunan","湖南" },
{"Hubei","湖北" },
{"Sichuan","四川" },
{"Gansu","甘肃" },
{"Guizhou","贵州" }
};
}
/// <summary>
/// 获取所有的数据库中的表
/// </summary>
/// <param name="db"></param>
/// <param name="EntityInfo"></param>
/// <param name="tableInfos"></param>
/// <returns></returns>
/// <exception cref="NotImplementedException"></exception>
public List<SplitTableInfo> GetAllTables(ISqlSugarClient db, EntityInfo EntityInfo, List<DbTableInfo> tableInfos)
{
List<SplitTableInfo> result = new List<SplitTableInfo>();
//var keylist = _AreaDictionary.Select(c => c.Key).ToList();
foreach (var item in tableInfos)
{
if (item.Name.Contains("_Area_")) //区分标识如果不用正则符复杂一些,防止找错表
{
SplitTableInfo data = new SplitTableInfo()
{
TableName = item.Name //要用item.name不要写错了
};
result.Add(data);
}
}
return result.OrderBy(it => it.TableName).ToList();//打断点看一下有没有查出所有分表
}
/// <summary>
///
/// </summary>
/// <param name="db"></param>
/// <param name="entityInfo"></param>
/// <param name="splitType"></param>
/// <param name="entityValue"></param>
/// <returns></returns>
public object GetFieldValue(ISqlSugarClient db, EntityInfo entityInfo, SplitType splitType, object entityValue)
{
var splitColumn = entityInfo.Columns.FirstOrDefault(it => it.PropertyInfo.GetCustomAttribute<SplitFieldAttribute>() != null);
return splitColumn.PropertyInfo.GetValue(entityValue, null);
}
/// <summary>
///
/// </summary>
/// <param name="db"></param>
/// <param name="EntityInfo"></param>
/// <returns></returns>
public string GetTableName(ISqlSugarClient db, EntityInfo EntityInfo)
{
return EntityInfo.DbTableName + "_Area";
}
public string GetTableName(ISqlSugarClient db, EntityInfo EntityInfo, SplitType type)
{
return EntityInfo.DbTableName + "_Area";
}
/// <summary>
/// 确定生成数据库表的时候,表的名称
/// </summary>
/// <param name="db"></param>
/// <param name="entityInfo"></param>
/// <param name="splitType"></param>
/// <param name="fieldValue"></param>
/// <returns></returns>
public string GetTableName(ISqlSugarClient db, EntityInfo entityInfo, SplitType splitType, object fieldValue)
{
KeyValuePair<string, string> keyPar = _AreaDictionary.FirstOrDefault(c => fieldValue.ToString().Contains(c.Value));
return entityInfo.DbTableName + "_Area_" + keyPar.Key; //根据值按首字母
}
}
CRUD
/// <summary>
/// 自定义分表
/// </summary>
public static class CustomSubTableDemo
{
/// <summary>
/// 按年分表
/// </summary>
public static void ShowCustomSubTable()
{
var connectionconfig = new ConnectionConfig()
{
DbType = DbType.SqlServer,
ConnectionString = "Data Source=DESKTOP-VF73RDU;Initial Catalog=CustomerDB;Persist Security Info=True;User ID=sa;Password=sa123",
IsAutoCloseConnection = true
};
using (SqlSugarClient db = new SqlSugarClient(connectionconfig))
{
///自己来制定定义的规则
db.CurrentConnectionConfig.ConfigureExternalServices.SplitTableService = new AreaSubTableService();
db.CodeFirst
.SplitTables()//标识分表
.InitTables<CommoditySubTableArea>(); //程序启动时加这一行,如果一张表没有会初始化一张
var addModel = new CommoditySubTableArea()
{
CategoryId = 1,
Createtime = DateTime.Now,
ImageUrl = $"Images",
Price = 345,
ProductId = 2,
Province = "湖北省",
Title = "这里是一条测试数据",
Url = "URL"
};
db.Insertable(addModel).SplitTable().ExecuteCommand();
List<CommoditySubTableArea> addmodelList = new List<CommoditySubTableArea>();
for (int i = 0; i < 10; i++)
{
foreach (var dic in AreaSubTableService._AreaDictionary)
{
addmodelList.Add(new CommoditySubTableArea()
{
CategoryId = 1,
Createtime = DateTime.Now,
ImageUrl = $"Images_{i}",
Price = 345,
ProductId = i,
Province = dic.Value,
Title = "这里是一条测试数据",
Url = "URL"
});
}
}
db.Insertable(addmodelList).SplitTable().ExecuteCommand();
var listall = db.Queryable<CommoditySubTableArea>().Where(it => it.Province.Contains("湖北")).SplitTable(tas => tas.ContainsTableNames("_Area_")).ToList();
CommoditySubTableArea admodel = new CommoditySubTableArea()
{
CategoryId = 1,
Createtime = DateTime.Now,
ImageUrl = $"Images_",
Price = 345,
ProductId = 34,
Province = "广西",
Title = "这里是一条测试数据",
Url = "URL"
};
db.Insertable(admodel).SplitTable().ExecuteCommand();
List<CommoditySubTableArea> pageList = db.Queryable<CommoditySubTableArea>().SplitTable(t => t.ContainsTableNames("CommoditySubTableArea_Area_")).ToPageList(1, 50);
}
}
}