Insert multiple records in database using parallel.foreach
<p>I am trying to insert around 5000 records in database using foreach loop. It is taking around 10 min which is not acceptable as per the requirement. I also thought about the approach in which first insert the records in a datatable and then converting it into XML pass it to stored procedure which do the insertion. But unfortunately it is not getting fit in my situation. Now i am doing the same thing using parallel.foreach but after inserting 10 records i am getting "Unique constraint violation for the primary key" error msg. As i am new to parallel programming so not getting the solution for it. Below is my code that i have done so for.</p>

<pre><code> 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 =&gt;
        {

            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 =&gt; m.SRC_FUNC_AREA == ExcelFunctionalAreaname).FirstOrDefault().SRC_FUNC_KEY;

            for (int j = 0; j &lt; Ch_Grp_types.Length &amp;&amp; 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 =&gt; 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" &amp;&amp; B.IS_ACTIVE == "Y" &amp;&amp; B.CH_GRP_TYPE_KEY == Key &amp;&amp; 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 &amp;&amp; B.CH_GRP_TYPE_KEY == Key &amp;&amp; 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 =&gt; m.CH_GRP_TYPE_KEY == Key &amp;&amp; m.SRC_FUNC_KEY == srcFuncKey &amp;&amp; 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 &gt; 0)
                            {

                                foreach (var abc in query2)
                                {
                                    if (abc.channelMstKey == objChMst.CHANNEL_MST_KEY &amp;&amp; abc.srcFuncKey == srcFuncKey &amp;&amp; abc.channelGrpTypeKey == Key)
                                    {
                                        if (abc.channelGrpDetKey == objGrpDetail.CH_GRP_DET_KEY &amp;&amp; abc.channelGrpMstKey == objXrefChGrpMst.CH_GRP_MST_KEY)
                                        {
                                            //Reject
                                        }
                                        else
                                        {
                                            //Update
                                            XREF_CH_GRP_DET_TAG obj = bc.XREF_CH_GRP_DET_TAG.Where(m =&gt; 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");
    }
</code></pre>

<p>Getting error when generating the primary key value using the below function :</p>

<pre><code>public static decimal Get_Max_Of_XREF_CH_GRP_DET()
    {
        try
        {
            BarcDataContext bc = new BarcDataContext();
            return bc.XREF_CH_GRP_DET_TAG.Max(m =&gt; m.SR_NO) + 1;

        }
        catch (Exception e)
        {
            return 1;
        }
    }
</code></pre>

<p>Where SR_NO is the primary key in that table.</p>

<p>Any help will be very much appreciated. Thanks in advance.</p>