使用parallel.foreach在数据库中插入多个记录
本文关键字：插入 记录 数据库 parallel foreach 使用 | 更新日期: 2023-09-27 18:04:09
我试图使用foreach循环在数据库中插入约5000条记录。它需要大约10分钟，这是不能接受的要求。我还考虑了一种方法，首先在数据表中插入记录，然后将其转换为XML，将其传递给执行插入的存储过程。但不幸的是，它不适合我的情况。现在我用parallel来做同样的事情。foreach但插入10条记录后，我得到"唯一约束违反主键"错误消息。因为我是新的并行编程，所以没有得到它的解决方案。下面是我的代码，我已经这样做了。
public ActionResult ChannelBulkUpload(HttpPostedFileBase excelFile)
{
bool flag = true;
string path = Server.MapPath("~/Content/UploadFolder/" + excelFile.FileName);
if (System.IO.File.Exists(path))
{
System.IO.File.Delete(path);
}
excelFile.SaveAs(path);
DataTable dt = GetDataTableFromExcel(excelFile, true);
ParallelOptions options = new ParallelOptions
{
MaxDegreeOfParallelism = 4
};
Parallel.ForEach(dt.AsEnumerable(), row =>
{
flag = true;
decimal Key = 0;
string value = "";
decimal channelMstKey = 0;
decimal channelGrpMstKey = 0;
decimal srcFuncKey = 0;
string ExcelMasterChDisplayName = row["MASTER_CHANNEL_DISPLAY_NAME"].ToString();
string ExcelGenreValue = row["GENRE"].ToString();
string ExcelAdsharpValue = row["ADSHARP"].ToString();
string ExcelClusterValue = row["CLUSTER"].ToString();
string ExcelNetworkValue = row["NETWORK"].ToString();
string ExcelBroadCastValue = row["BROADCAST"].ToString();
string ExcelFunctionalAreaname = row["FUNCTIONAL_AREA"].ToString();
string[] Ch_Grp_types = { "GENRE", "ADSHARP", "CLUSTER", "NETWORK", "PLATFORM" };
BarcDataContext bc = new BarcDataContext();
srcFuncKey = bc.REF_SRC_FUNC_AREA.Where(m => m.SRC_FUNC_AREA == ExcelFunctionalAreaname).FirstOrDefault().SRC_FUNC_KEY;
for (int j = 0; j < Ch_Grp_types.Length && flag; j++)
{
if (Ch_Grp_types[j] == "GENRE")
{
Key = 1;
value = ExcelGenreValue;
}
else if (Ch_Grp_types[j] == "NETWORK")
{
Key = 2;
value = ExcelNetworkValue;
}
else if (Ch_Grp_types[j] == "ADSHARP")
{
Key = 3;
value = ExcelAdsharpValue;
}
else if (Ch_Grp_types[j] == "CLUSTER")
{
Key = 4;
value = ExcelClusterValue;
}
else if (Ch_Grp_types[j] == "PLATFORM")
{
Key = 5;
value = ExcelBroadCastValue;
}
DIM_CHANNEL_MST objChMst = bc.DIM_CHANNEL_MST.Where(m => m.CHANNEL_MST_NAME_UPPER == ExcelMasterChDisplayName.ToUpper().Trim()).FirstOrDefault();
if (objChMst == null)
{
flag = false;
}
else
{
if (!string.IsNullOrEmpty(value))
{
var query =
(from A in bc.XREF_CH_GRP_DET_TAG
join B in bc.XREF_CH_GRP_MST_TAG on A.CH_GRP_MST_KEY equals B.CH_GRP_MST_KEY
where A.IS_ACTIVE == "Y" && B.IS_ACTIVE == "Y" && B.CH_GRP_TYPE_KEY == Key && B.CH_GRP_MST_NAME_UPPER == value.ToUpper()
select new XrefChannelGrpDetailTagVM
{
channelGrpDetKey = A.CH_GRP_DET_KEY,
channelGrpMasterNameUpper = B.CH_GRP_MST_NAME_UPPER,
}).Distinct().ToList();
var query2 =
(from A in bc.XREF_CH_GRP_DET_TAG
join B in bc.XREF_CH_GRP_MST_TAG on A.CH_GRP_MST_KEY equals B.CH_GRP_MST_KEY
where A.CHANNEL_MST_KEY == objChMst.CHANNEL_MST_KEY && B.CH_GRP_TYPE_KEY == Key && B.SRC_FUNC_KEY == srcFuncKey
select new XrefChannelGrpDetailTagVM
{
sr_no = A.SR_NO,
channelMstKey = A.CHANNEL_MST_KEY,
channelGrpDetKey = A.CH_GRP_DET_KEY,
channelGrpMstKey = A.CH_GRP_MST_KEY,
srcFuncKey = B.SRC_FUNC_KEY,
channelGrpTypeKey = B.CH_GRP_TYPE_KEY
}).Distinct().ToList();
XREF_CH_GRP_MST_TAG objXrefChGrpMst = bc.XREF_CH_GRP_MST_TAG.Where(m => m.CH_GRP_TYPE_KEY == Key && m.SRC_FUNC_KEY == srcFuncKey && m.CH_GRP_MST_NAME_UPPER == value.ToUpper()).FirstOrDefault();
if (objXrefChGrpMst != null)
{
channelMstKey = objChMst.CHANNEL_MST_KEY;
channelGrpMstKey = objXrefChGrpMst.CH_GRP_MST_KEY;
XREF_CH_GRP_DET_TAG objGrpDetail = new XREF_CH_GRP_DET_TAG();
if (query.Count == 0)
{
objGrpDetail.CH_GRP_DET_KEY = Get_Max_Of_Ch_Grp_Det_Key();
}
else
{
foreach (var detKey in query)
{
if (detKey.channelGrpMasterNameUpper == value.ToUpper())
{
objGrpDetail.CH_GRP_DET_KEY = detKey.channelGrpDetKey;
}
else
{
objGrpDetail.CH_GRP_DET_KEY = Get_Max_Of_Ch_Grp_Det_Key();
}
}
}
if (query2.Count > 0)
{
foreach (var abc in query2)
{
if (abc.channelMstKey == objChMst.CHANNEL_MST_KEY && abc.srcFuncKey == srcFuncKey && abc.channelGrpTypeKey == Key)
{
if (abc.channelGrpDetKey == objGrpDetail.CH_GRP_DET_KEY && abc.channelGrpMstKey == objXrefChGrpMst.CH_GRP_MST_KEY)
{
//Reject
}
else
{
//Update
XREF_CH_GRP_DET_TAG obj = bc.XREF_CH_GRP_DET_TAG.Where(m => m.SR_NO == abc.sr_no).FirstOrDefault();
obj.CH_GRP_DET_KEY = objGrpDetail.CH_GRP_DET_KEY;
obj.CH_GRP_MST_KEY = objXrefChGrpMst.CH_GRP_MST_KEY;
objGrpDetail.CREATE_DATE = DateTime.Now;
objGrpDetail.LAST_UPD_DATE = DateTime.Now;
objGrpDetail.IS_ACTIVE = "Y";
bc.SaveChanges();
}
}
else
{
//Insert
objGrpDetail.CH_GRP_MST_KEY = channelGrpMstKey;
objGrpDetail.CHANNEL_MST_KEY = channelMstKey;
objGrpDetail.SR_NO = Get_Max_Of_XREF_CH_GRP_DET();
objGrpDetail.CREATE_DATE = DateTime.Now;
objGrpDetail.LAST_UPD_DATE = DateTime.Now;
objGrpDetail.IS_ACTIVE = "Y";
bc.XREF_CH_GRP_DET_TAG.Add(objGrpDetail);
bc.SaveChanges();
}
}
}
else
{
objGrpDetail.CH_GRP_MST_KEY = channelGrpMstKey;
objGrpDetail.CHANNEL_MST_KEY = channelMstKey;
objGrpDetail.SR_NO = Get_Max_Of_XREF_CH_GRP_DET();
objGrpDetail.CH_GRP_DET_KEY = objGrpDetail.CH_GRP_DET_KEY;
objGrpDetail.CREATE_DATE = DateTime.Now;
objGrpDetail.LAST_UPD_DATE = DateTime.Now;
objGrpDetail.IS_ACTIVE = "Y";
bc.XREF_CH_GRP_DET_TAG.Add(objGrpDetail);
bc.SaveChanges();
}
}
}
}
}
});
TempData["SuccessMsg"] = "Records uploaded Successfully";
return RedirectToAction("CreateChannel");
}
使用下面的函数生成主键值时出错:
public static decimal Get_Max_Of_XREF_CH_GRP_DET()
{
try
{
BarcDataContext bc = new BarcDataContext();
return bc.XREF_CH_GRP_DET_TAG.Max(m => m.SR_NO) + 1;
}
catch (Exception e)
{
return 1;
}
}
其中SR_NO是该表的主键。
任何帮助都将非常感激。
执行此类插入的最快方法是使用ADO。NET，特别是SQL批量插入。如果你使用SQL Server作为你的数据库，相关代码将是这样的:
DataTable dt = GetDataTableFromExcel(excelFile, true);
using (var copy = new SqlBulkCopy(yourConnectionString) //There are other overloads too
{
BulkCopyTimeout = 10000,
DestinationTableName = dt.TableName,
})
{
foreach (DataColumn column in dt.Columns)
{
copy.ColumnMappings.Add(column.ColumnName, column.ColumnName);
}
copy.WriteToServer(dt);
}
请看我对其他问题的评论
可以使用guid作为表的主键。它将帮助您避免@@IDENTITY的问题。首先，您应该生成新的指南标识符，然后将生成的值插入到