mardi 24 septembre 2019

Saving data in SQL with many conditions that retrieves data from SQL to compare with before saving each record I'm looping data from excel

Conditions are if this record already exist, if the email in this record is valid or is in another table. i am looping data from excel sheet by uploading it and looping into records my code is working well and saving in database but the issue is that its taking to much time to finish the job like 4 days for comparing and saving 200k records... i need a better way

var Email = dt.Rows[j][3].ToString();
                List<AllBounced> bounce = cnx.AllBounceds.Where(x => x.Email == Email).ToList();
                List<MainField> mainfield = cnx.MainFields.Where(x => x.Email == data.Email).ToList();
                List<MainField> mainfield2 = cnx.MainFields.Where(x => x.Phone == data.Phone || x.Mobile == data.Mobile).ToList();
                if (data.Email != null)
                {
                    if (IsEmailValid(Email) == true)
                    {
                        if (mainfield.Count != 0)
                        {
                            MainFieldsDuplicate data2 = new MainFieldsDuplicate
                            {
                                Id = Guid.NewGuid(),
                                FirstName = dt.Rows[j][0].ToString(),
                                MiddleName = dt.Rows[j][1].ToString(),
                                LastName = dt.Rows[j][2].ToString(),
                                Email = dt.Rows[j][3].ToString(),
                                Phone = dt.Rows[j][4].ToString(),
                                Mobile = dt.Rows[j][5].ToString(),
                                a= dt.Rows[j][6].ToString(),
                                b= dt.Rows[j][7].ToString(),
                                c= dt.Rows[j][8].ToString(),
                                d= dt.Rows[j][9].ToString(),
                                e= dt.Rows[j][11].ToString(),
                                f= dt.Rows[j][12].ToString(),
                                j= dt.Rows[j][14].ToString(),
                                isDeleted = 0,
                                h= dt.Rows[j][13].ToString(),
                                Date = GetDateTime(dt.Rows[j][10].ToString()),
                            };
                            cnx.MainFieldsDuplicates.Add(data2);
                            cnx.SaveChanges();
                        }
                        else
                        {
                            if (bounce.Count != 0)
                            {
                                data.isBounce = true;
                            }
                            else
                            {
                                data.isBounce = false;
                            }
                            data.isValid = true;
                            cnx.MainFields.Add(data);
                            cnx.SaveChanges();
                        }
                    }

                    else
                    {
                        if (mainfield.Count != 0)
                        {
                            MainFieldsDuplicate data2 = new MainFieldsDuplicate
                            {
                                Id = Guid.NewGuid(),
                                FirstName = dt.Rows[j][0].ToString(),
                                MiddleName = dt.Rows[j][1].ToString(),
                                LastName = dt.Rows[j][2].ToString(),
                                Email = dt.Rows[j][3].ToString(),
                                Phone = dt.Rows[j][4].ToString(),
                                Mobile = dt.Rows[j][5].ToString(),
                                a= dt.Rows[j][6].ToString(),
                                b= dt.Rows[j][7].ToString(),
                                c= dt.Rows[j][8].ToString(),
                                d= dt.Rows[j][9].ToString(),
                                e = dt.Rows[j][11].ToString(),
                                f= dt.Rows[j][12].ToString(),
                                j= dt.Rows[j][14].ToString(),
                                isDeleted = 0,
                                h = dt.Rows[j][13].ToString(),
                                Date = GetDateTime(dt.Rows[j][10].ToString())
                            };
                            cnx.MainFieldsDuplicates.Add(data2);
                            cnx.SaveChanges();
                        }
                        else
                        {
                            if (bounce.Count != 0)
                            {
                                data.isBounce = true;
                            }
                            else
                            {
                                data.isBounce = false;
                            }
                            data.isValid = false;
                            cnx.MainFields.Add(data);
                            cnx.SaveChanges();
                        }
                    }
                }
                else
                {
                    if (mainfield2.Count != 0)
                    {
                        MainFieldsDuplicate data3 = new MainFieldsDuplicate
                        {
                            Id = Guid.NewGuid(),
                            FirstName = dt.Rows[j][0].ToString(),
                            MiddleName = dt.Rows[j][1].ToString(),
                            LastName = dt.Rows[j][2].ToString(),
                            Email = dt.Rows[j][3].ToString(),
                            Phone = dt.Rows[j][4].ToString(),
                            Mobile = dt.Rows[j][5].ToString(),
                            a= dt.Rows[j][6].ToString(),
                            b= dt.Rows[j][7].ToString(),
                            c= dt.Rows[j][8].ToString(),
                            d= dt.Rows[j][9].ToString(),
                            e= dt.Rows[j][11].ToString(),
                            f= dt.Rows[j][12].ToString(),
                            j= dt.Rows[j][14].ToString(),
                            isDeleted = 0,
                            h= dt.Rows[j][13].ToString(),
                            Date = GetDateTime(dt.Rows[j][10].ToString())
                        };
                        cnx.MainFieldsDuplicates.Add(data3);
                        cnx.SaveChanges();
                    }
                    else
                    {
                        cnx.MainFields.Add(data);
                        cnx.SaveChanges();
                    }

Aucun commentaire:

Enregistrer un commentaire