最近遇到一个需求:用户以Excel方法导入数据,所导入的数据需要按某个值拆分成主从表的关系,故数据的存储也要分成主从表存在数据库中;在网找了很久,没有找到更好的办法,于是只好借鉴网上的部分想法做了些加工,以备忘记1
public DataTable dt = new DataTable();
/// <summary>
/// 数据源表结构
/// </summary>
/// <returns></returns>
private DataTable CreateTb()
{
DataTable tb = new DataTable();
tb.Columns.Add("UUID");
tb.Columns.Add("PARENT_UUID");
tb.Columns.Add("MASTER_DESC1");
tb.Columns.Add("MASTER_DESC2");
tb.Columns.Add("SUB_DESC1");
tb.Columns.Add("SUB_DESC2");
tb.Columns.Add("SUB_DESC3");
return tb;
}
/// <summary>
/// 拆分后主表数据结构
/// </summary>
/// <returns></returns>
private DataTable CreateParentTb()
{
DataTable tb = new DataTable();
tb.Columns.Add("UUID");
tb.Columns.Add("PARENT_UUID");
tb.Columns.Add("MASTER_DESC1");
tb.Columns.Add("MASTER_DESC2");
return tb;
}
/// <summary>
/// 拆分后子表结构
/// </summary>
/// <returns></returns>
private DataTable CreateSupTb()
{
DataTable tb = new DataTable();
tb.Columns.Add("UUID");
tb.Columns.Add("PARENT_UUID");
tb.Columns.Add("SUB_DESC1");
tb.Columns.Add("SUB_DESC2");
tb.Columns.Add("SUB_DESC3");
return tb;
}
/// <summary>
/// 数据源(待拆分的数据)
/// </summary>
/// <returns></returns>
private DataTable retDt()
{
DataTable dt = CreateTb();
DataRow row = null;
for (int i = 0; i < 6; i++)
{
int j = 0;
if (i % 2 == 0)//PARENT_UUID 的值,按PARENT_UUIDsss1、PARENT_UUIDsss2分组
j = 0;
else
j = 1;
row = dt.NewRow();
row["UUID"] = System.Guid.NewGuid().ToString();
row["PARENT_UUID"] = "PARENT_UUIDsss" + j.ToString() ;
row["MASTER_DESC1"] = "MASTER_DESC1" + i.ToString();
row["MASTER_DESC2"] = "MASTER_DESC2" + i.ToString();
row["SUB_DESC1"] = "SUB_DESC1" + i.ToString();
row["SUB_DESC2"] = "SUB_DESC2" + i.ToString();
row["SUB_DESC3"] = "SUB_DESC3" + i.ToString();
dt.Rows.Add(row);
}
return dt;
}
/// <summary>
/// 测试
/// </summary>
/// <param name="mesg"></param>
public void SeparateDt(out string mesg)
{
mesg = string.Empty;
DataTable sourceData = retDt();
DataTable parentDt = CreateParentTb();
DataTable subDt = CreateSupTb();
string parentUuid = string.Empty;
string subUuid = string.Empty;
try
{
IEnumerable<IGrouping<string, DataRow>> results = sourceData.Rows.Cast<DataRow>().GroupBy<DataRow, string>
(dr => dr["PARENT_UUID"].ToString());//按PARENT_UUID分组
foreach (IGrouping<string, DataRow> ig in results)
{
//处理主表
DataRow parentRow = parentDt.NewRow();
parentUuid = System.Guid.NewGuid().ToString();
parentRow["UUID"] = parentUuid;
parentRow["PARENT_UUID"] = ig.Key;
parentRow["MASTER_DESC1"] = "MASTER_DESC1".PadRight(20);
parentRow["MASTER_DESC2"] = "MASTER_DESC2".PadRight(20);
parentDt.Rows.Add(parentRow);
//处理子表
foreach (var dr in ig)
{
DataRow subRow = subDt.NewRow();
subUuid = System.Guid.NewGuid().ToString();
subRow["UUID"] = subUuid;
subRow["PARENT_UUID"] = parentUuid;
subRow["SUB_DESC1"] = dr["SUB_DESC1"].ToString();
subRow["SUB_DESC2"] = dr["SUB_DESC2"].ToString();
subRow["SUB_DESC3"] = dr["SUB_DESC3"].ToString();
subDt.Rows.Add(subRow);
}
}
mesg = "主表:" + parentDt.Rows.Count.ToString() + "条记录;" + "子表:" + subDt.Rows.Count.ToString() + "条记录";
}
catch (Exception ex)
{
mesg = "程序有问题:" + ex.Message;
}
}