Code Optimization

Interesting things about software development and code optimization

MS SQL Batch or Bulk UPDATE and all around EF

Dear friends,


Thank you for comming and here I will show you how to make a huge bulk UPDATE in database from your C# code using Entity Framework.

My issue was about calculation prices for +50K products and then updating these product prices all at once.

Before this optimization SQL Update took more than 60 seconds ( using different approaches and of course you even should not think to do it with EF update/save method at all). After implementation of this approach we get less than 10 seconds for updating +50K products (of course it depends on hosting and environment you use). So what is that?


Table-Valued Parameters

yes, may be you have heard about it but have not understand it enough to use or just have not believed that it would really help you.


So everything is really easy - you create table, put all data you need for your update - it is +50K rows and pass it to your stored procedure that knows how to use it, here is an example:

- create table and columns

                            DataTable tblUpdate = new DataTable();
                            tblUpdate.Columns.Add("Id", typeof(int));               
                            tblUpdate.Columns.Add("DeliveryDateId", typeof(int));   
                            tblUpdate.Columns.Add("OldPrice", typeof(decimal));     
                            tblUpdate.Columns.Add("Price", typeof(decimal));        
                            tblUpdate.Columns.Add("ProductCost", typeof(decimal));  
                            tblUpdate.Columns.Add("AdditionalShippingCharge", typeof(decimal)); 
                            tblUpdate.Columns.Add("DisableBuyButton", typeof(bool));
                            tblUpdate.Columns.Add("Published", typeof(bool));       
                            tblUpdate.Columns.Add("AdminComment", typeof(string));  
                            tblUpdate.Columns.Add("UpdatedOnUtc", typeof(DateTime));

- add rows

...
                            object[] row = new object[10];                            
                            ...
                            ...
                            tblUpdate.Rows.Add(row);
                            ...

- now it is time to push it to SQL

                            var dataSettings = Core.Data.DataSettingsManager.LoadSettings();

                            using (System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection(dataSettings.DataConnectionString))
                            {
                                conn.Open();
                                
                                //check if table-valued type is already exists
                                using (System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand(
@"IF TYPE_ID(N'TargetUDT') IS NULL CREATE TYPE [TargetUDT] AS TABLE
(
Id int not null,
DeliveryDateId int not null,
OldPrice decimal(18,4) not null,
Price decimal(18,4) not null,
ProductCost decimal(18,4) not null,
AdditionalShippingCharge decimal(18,4) not null,
DisableBuyButton bit not null,
Published bit not null,
AdminComment nvarchar(max) null,
UpdatedOnUtc datetime not null
);", conn))
                                {
                                    cmd.CommandType = CommandType.Text;
                                    cmd.ExecuteNonQuery();
                                }

//create or update stored procedure using (System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand( @"CREATE OR ALTER PROCEDURE usp_UpdateAllProdPrices (@tvpUpdateProducts [TargetUDT] READONLY) AS BEGIN UPDATE dbo.Product SET Product.DeliveryDateId = ec.DeliveryDateId, Product.OldPrice = ec.OldPrice, Product.Price = ec.Price, Product.ProductCost = ec.ProductCost, Product.AdditionalShippingCharge = ec.AdditionalShippingCharge, Product.DisableBuyButton = ec.DisableBuyButton, Product.Published = ec.Published, Product.AdminComment = ec.AdminComment, Product.UpdatedOnUtc = ec.UpdatedOnUtc FROM @tvpUpdateProducts AS ec INNER JOIN dbo.Product ON dbo.Product.Id = ec.Id END", conn)) { cmd.CommandType = CommandType.Text; cmd.ExecuteNonQuery(); } //run the stored procedure and pass the all data as table-valued parameter using (System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand( @"usp_UpdateAllProdPrices", conn)) { System.Data.SqlClient.SqlParameter tvpParam = cmd.Parameters.AddWithValue("@tvpUpdateProducts", tblUpdate); tvpParam.SqlDbType = SqlDbType.Structured; tvpParam.TypeName = "[TargetUDT]"; cmd.CommandType = System.Data.CommandType.StoredProcedure; cmd.ExecuteNonQuery(); } conn.Close(); }


That's it guys enjoy your super-fast bulk update :)




Thank you and see you :)


1vqHSTrq1GEoEF7QsL8dhmJfRMDVxhv2y



Split by comma but not inside of any parentheses or brackets

Hello friends,


If you are here then you in need of the same as me - split string by comma that is not inside of any brackets or parentheses.

So to do that you need the following piece of code (C# code):

string oval = "Home, Edit, HDMI (4K, ARC), Test, Double 2.0 x1, Multi (F1, (f2-f5, f6, 58), FF, o2), ARG";
string roval = "";
int isOpen = 0;
for (int i = 0i < oval.Lengthi++)
{
    if (isOpen <= 0 && oval[i] == ',')
    {
        roval += ",,";
    }
    else if (isOpen > 0 && (oval[i] == ')' || oval[i] == ']' || oval[i] == '}'))
    {
        isOpen--;
        roval += oval[i];
    }
    else if ((oval[i] == '(' || oval[i] == '[' || oval[i] == '{'))
    {
        isOpen++;
        roval += oval[i];
    }
    else
    {
        roval += oval[i];
    }
}

string[] split = roval.Split(",,"StringSplitOptions.RemoveEmptyEntries);


String you get to split looks like this: 

Home,, Edit,, HDMI (4K, ARC),, Test,, Double 2.0 x1,, Multi (F1, (f2-f5, f6, 58), FF, o2),, ARG

so you need just split it by double comma now.

This is fast, short and clear code that can be easily translated into any other language


Thank you and see you :)


1vqHSTrq1GEoEF7QsL8dhmJfRMDVxhv2y



Flutter - Updating TextField Selection/Caret Problem and comma for thousand separator

Hello friends,


I'm going to show you how I resolved the selection/caret updating issue for Flutter TextField when cursor goes to the very beginning instead of the position you specify.


I was creating a money formating text field and faced this issue as many of us and here is how I resolved it:

  
String odlValue="";

  @override
  void initState() {
    tbPurchasePrice.addListener((){
      var cursor = tbPurchasePrice.selection;
      String val = tbPurchasePrice.text;
      if((odlValue.length < val.length && val.length > 3) || 
          (odlValue.length > val.length && val.length > 4)){
        val = val.replaceAll(",""");
        val = val.substring(0, val.length - 3) + "," + val.substring(val.length - 3);
        int d = val.length - tbPurchasePrice.text.length;
        odlValue = val;
        tbPurchasePrice.text = val;
        Future.delayed(
        Duration(milliseconds: 5),
         () {
              tbPurchasePrice.selection = new TextSelection(
baseOffset: cursor.baseOffset + d, 
               extentOffset: cursor.extentOffset + d, 
               affinity: cursor.affinity, isDirectional: cursor.isDirectional);  
          });
      }
      else if(odlValue.length > val.length && val.length <= 4 && val.indexOf(",") >= 0){
        tbPurchasePrice.text = val.replaceAll(",""");
        odlValue = val;
        Future.delayed(
        Duration(milliseconds: 5),
         () {
              tbPurchasePrice.selection = new TextSelection(
baseOffset: cursor.baseOffset - 1
               extentOffset: cursor.extentOffset - 1
               affinity: cursor.affinity, isDirectional: cursor.isDirectional);  
          });
      }
      odlValue = val;
    });

    super.initState();
  }

.......

child: TextFormField(
       decoration: InputDecoration( prefix: Text(r"$")),
       controller: tbPurchasePrice, 
keyboardType: TextInputType.numberWithOptions(decimal: true),
       style: Theme.of(context).textTheme.headline,
   )
.......


so it puts $ sign on the beginning, then it puts comma in the first thousand place and it use delayed function call to set/update the selection property so it would not go to the beginning of the textfield but to the same place where it has been before text updating or where you need it to be.



Thank you and see you ;)




1vqHSTrq1GEoEF7QsL8dhmJfRMDVxhv2y



Android Xml Layout To Flutter Scaffold Converter - Completely free

Simple but may be useful

Android Xml Layout To Flutter Scaffold Converter - Completely free


If you need to quickly convert most of your Android XML layouts into Dart Scaffold files then you can just try this online free converter. It is not perfect and does not support all Android UI elements and containers but in most cases it will make your life easier and create much DART files for you to start migrating your Java UI to Flutter UI.


Android Xml Layout to Flutter Scaffold Converter


1vqHSTrq1GEoEF7QsL8dhmJfRMDVxhv2y



Dropbox as a service (Windows Service for Windows Server)

Hello friends,


Have you searched for a Dropbox windows service so it would synchronize your files and folders on a specific time and without any logged in windows user? I guess yes and as me it seems that you have not found anything appropriate.

The question is not only about synchronization if we talk about windows server but about performance as well. As you know the Dropbox service use a lot of system resources like CPU, HDD/SSD, RAM while it is running and this is crucial for a server because of any server has to use as less resources as possible at any given time.


So that is why I implemented my own workflow to control dropbox. There are two main points:

- run non-GUI Dropbox instance on a specific time frame only

- if there is any dropbox instance outside of the specified timeframe then kill it immediately.


To run the dropbox without GUI and to start synchronizing files I use the following windows task scheduler:





And here is the windows service we will install and run indefinitely to monitor any dropbox process and kill it:

    public partial class Service1 : ServiceBase
    {
        System.Threading.Timer timer = null;
        System.Threading.Thread tt = null;
        volatile bool stop = false;
        var perod = new Setting();

        public Service1()
        {
            InitializeComponent();
        }

        protected override void OnStart(string[] args)
        {
            perod.StartFrom = new DateTime(DateTime.Now.Year, DateTime.Now.Month, DateTime.Now.Day, 23, 25, 0);
            perod.EndTo = new DateTime(DateTime.Now.Year, DateTime.Now.Month, DateTime.Now.Day, 23, 25, 0).AddHours(1);
            
            tt = new System.Threading.Thread((Action) =>
            {
                if (timer == null)
                    timer = new System.Threading.Timer(new System.Threading.TimerCallback(KillDropbox), this, 5 * 1000, 60 * 1000);

                while (!stop)
                {
                    System.Threading.Thread.Sleep(3000);
                }
            });
            tt.Start();
        }

        void KillDropbox(object o)
        {
            try
            {
                if (DateTime.Now.TimeOfDay >= perod.StartFrom.TimeOfDay && DateTime.Now.TimeOfDay <= perod.EndTo.TimeOfDay)
                    return;

                int pc = 0;
                ReScan:
                var proccs = System.Diagnostics.Process.GetProcesses().Where(p => p.ProcessName.ToLowerInvariant().StartsWith("dropbox"));
                pc = proccs.Count();
                foreach (var proc in proccs)
                {
                    try
                    {
                        proc.Kill();
                        
                        System.Threading.Thread.Sleep(1000);
                        goto ReScan;
                    }
                    catch (Exception eex)
                    {
                    }
                }
            }
            catch(Exception ex)
            {
            }
            
        }

        protected override void OnStop()
        {
            stop = true;

            if (timer != null)
            {
                timer.Dispose();
                timer = null;
            }
            if (tt != null)
            {
                tt.Join(5000);
                tt = null;
            }
        }
    }


So after all we have a scheduler that runs non-GUI dropbox, dropbox synchronizes all configured files and folders and our windows service kills it outside of time frame.


Note: You should select your own time frame that will give dropbox enough time to synchronize everything.


It is also worth to mention that you will have to login to your server from time to time and update and sign into your Dropbox application in other way it will stop synchronization (something special that relates to dropbox itself).


Thank you, hope that was helpful!


1vqHSTrq1GEoEF7QsL8dhmJfRMDVxhv2y



MS SQL Maintenance and Performance

Hello friends,


I want to share my experience with MS SQL maintenance and performance with regards to nonclustered Indexes and Fragmentation.


If you do delete unnecessary rows in tables from time to time then you definitely need to maintenance fragmented indexes.

When you delete a row in a table SQL Server modifies indexes and it leads to fragmentation that leads to slowing down query performances on that table.


To avoid any bottlenecks and be sure your fragmentation is minimal and performance is maximum you need to reorganize indexes or rebuild them.


I use the following piece of stored procedure code whenever I do any clean on a table:


    StoredProcedure

....

    -- Reorganize the NCIX_MyTable 
    -- index on the dbo.MyTable table.   
SELECT @frag = avg_fragmentation_in_percent FROM sys.dm_db_index_physical_stats (DB_ID(N'my_database'), OBJECT_ID(N'dbo.MyTable'), NULL, NULL, NULL) AS a
JOIN sys.indexes AS b ON a.object_id = b.object_id AND a.index_id = b.index_id and [name] = N'NCIX_MyTable';
if @frag>=50 begin --To rebuild a fragmented index ALTER INDEX NCIX_MyTable
ON dbo.MyTable REBUILD WITH (FILLFACTOR = 50); end; if @frag>=25 begin ALTER INDEX NCIX_MyTable ON dbo.MyTable REORGANIZE; end; ....


Conclusion - it keeps your DB size to minimum, removes unnecessary data and keeps sql performance to maximum.


Thank you and see you ;)


1vqHSTrq1GEoEF7QsL8dhmJfRMDVxhv2y



SSL Time and Rating

Hello,


Today I'm going to share some experience about SSL rating, time, security, performance and why it is better to turn off the RC4 protocol.


Also I did find and would like to share two useful resources that you can use to check your SSL and Website overall performance:

https://www.dotcom-tools.com/website-speed-test.aspx to analyze your website from different world locations

https://www.ssllabs.com/ssltest/analyze.html to analyze your SSL certificate


Using that two tools I did find a few main issues: my IIS server were still using RC4 that is considered non-secure, my DNS resolving time was too long from some points of world and my SSL handshake time was not very fast.


DNS resolving time - is still an issue as it require non-server and non-application actions to be taken to resolve it :(

SSL handshake is not so easy to resolve as well but what I have noticed is that resolving RC4 did speedup overall website loading performance and increase overall security rating.


So first step I would suggest is disabling the RC4 protocol. Lets take a look how to disable it on Windows Server with IIS:

- Open the RegEdit (Win + R >> regedit) and find the following key: HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\SecurityProviders\Schannel\Ciphers


- Right-click on Ciphers >> New >> Key and name it RC4 40/128

Hardening_14.jpg


- Right-click on RC4 40/128 >> New >> DWORD (32-bit) Value and name the value Enabled

Hardening_15.jpg


- Double-click the created Enabled value and make sure that there is zero (0) in Value Data field then click OK

Hardening_16.jpg


- Repeat those steps and create two more keys with the names RC4 56/128 and RC4 128/128 in the Ciphers directory

Hardening_17.jpg


- Close the RegEdit


In my case it was not required to reboot my server so I hope you will see the result immediately as well using the ssllabs web-tool I mentioned before.


This will give your A Rating for your SSL website security and as I noticed it speed up your website overall loading time (including SSL time) by 1.2-1.5 times.




Hope that will help you as well and let me know if you can add some useful info in comments.


Thank you and see you :)




1vqHSTrq1GEoEF7QsL8dhmJfRMDVxhv2y



Top 1 Anti-Spam Contact Form

The Best Anti-Spam Contact Form for Your Website 


Hello friends,

today I'm going to share my experience with implementing the best, in my opinion, anti-spam mechanism for web contact form.

There is well known problem - when you have a contact form on your website then you will get a lot of spam submitted via the form action url on your website and this is very annoying as for me. So I started thinking on how to do it in a way so I would get no spam at all or as less as possible at least.

First thing that came in my mind was that we would need a dynamic and random form action url for the form submission that will return HTTP 404 not found in case user would not pass human-like verification with a captcha or would try to guess the url.

As I work with C#.NET + MVC I started creating such solution with ASP.NET MVC and actually here is what I came up to.

First thing we need is to register our website for google reCAPTCHA v2, get public and secret keys and add reCAPTCHA to an our contact form. Note that in our case we need to render reCAPTCHA by hand and here is the Contact View html:


@section head
{
    <script src="https://www.google.com/recaptcha/api.js?onload=renderRecaptcha&render=explicit" async defer></script>
}

...

    @using (Html.BeginForm("", "", FormMethod.Post, new { id="contactForm" }))
    {
        @Html.AntiForgeryToken()

        ...

        <div id="ReCaptchContainer"></div>

        ...
        
        <input type="submit" value="Send" />
    }

...

@section scripts
{
    <script>
        var your_site_key = '.....-....';
        var canProceed = false;

        var reCaptchaCallback = function (response) {
            //lets go to server, validate a token and return a random url if succeed
            $.ajax({
                method: "POST",
                url: "/Contact/IsReCaptchValid",
                cache: false,
                data: {
                    gRecaptchaResponse: response
                }
            }).done(function (html) {
                if (html != "none") {
                    $("#contactForm").attr("action", html);
                    canProceed = true;
                    $("#submit").removeClass("disabled");
                    $("#submit").addClass("btn-success");
                }
            });
        };

        var renderRecaptcha = function () {
            //lets render our captcha
            grecaptcha.render('ReCaptchContainer', {
                'sitekey': your_site_key,
                'callback': reCaptchaCallback
            });
        };

        $(document).ready(function () {
            $("input[type='submit']").on("click", function (e) {

                ...

                if (canProceed != true) {
                    //CAPTCHA has not been validated yet
                    e.stopPropagation();
                    return false;
                }
            });
        });
    </script>
}

Here we load google api javascript file, run captcha rendering and attach a handler for validation response that we will use to send validation token to our back-end, validate it and return a new dynamic random URL for our <form> to submit data to.

Here is the server side controller action for reCAPTCHA validation:

        public ActionResult IsReCaptchValid(string gRecaptchaResponse)
        { //check referrer url to see if this request came from our own website
            if (Request.UrlReferrer == null || string.IsNullOrEmpty(Request.UrlReferrer.AbsolutePath) ||
                     !(Request.UrlReferrer.ToString().ToLowerInvariant().StartsWith(Extensions.Extensions.DomainName + "/contact") ||
                     Request.UrlReferrer.ToString().ToLowerInvariant().StartsWith(Extensions.Extensions.DomainName2 + "/contact")))
            {
                return HttpNotFound("Resource was not found");
            }
            string result = "none";
            var captchaResponse = gRecaptchaResponse;
            
            var apiUrl = "https://www.google.com/recaptcha/api/siteverify?secret={0}&response={1}";
            var requestUri = string.Format(apiUrl, secretKey, captchaResponse);
            var request = (System.Net.HttpWebRequest)System.Net.WebRequest.Create(requestUri);
            using (System.Net.WebResponse response = request.GetResponse())
            {
                using (System.IO.StreamReader stream = new System.IO.StreamReader(response.GetResponseStream()))
                {
                    JObject jResponse = JObject.Parse(stream.ReadToEnd());
                    var isSuccess = jResponse.Value<bool>("success");
                    if (isSuccess)
                    { //store token in session to re-validate later
                        Session["g-recaptcha-response"] = gRecaptchaResponse; //generate random url
                        result = "/Contact/" + 
                            ((char)rnd.Next(0x61, 0x7b)) + "" + //random char a-z
                            ((char)rnd.Next(0x61, 0x7b)) + "" +
                            ((char)rnd.Next(0x61, 0x7b)) + "" +
                            ((char)rnd.Next(0x61, 0x7b)) + "" +
                            ((char)rnd.Next(0x61, 0x7b)) + "" +
                            ((char)rnd.Next(0x61, 0x7b)) + "" +
                            ((char)rnd.Next(0x61, 0x7b)) + "" +
                            ((char)rnd.Next(0x61, 0x7b)) + "" ; //store the random url to validate later
                        Session["contactFormAction"] = result;
                    }
                }
            }
            return Content(result);
        }

As you may guessed this will validate google reCAPTCHA token and generate a random url like /Contact/edvbbgrq

Next step will be to accept POST request with all the data, validate and process it.
To be able to accept random urls like /Contact/edvbbgrq in one controller and one action you will have to define some routes, so lets define them now in our RouteConfig.cs:

            //route to accept /Contact/Contact get request and return our contact form/view
            routes.MapRoute(
                name: "Contact1",
                url: "Contact/Contact",
                defaults: new { controller = "Contact", action = "Contact" }
                ); //route to accept /Contact/IsReCaptchValid request and return our random urls
            routes.MapRoute(
                name: "Contact2",
                url: "Contact/IsReCaptchValid",
                defaults: new { controller = "Contact", action = "IsReCaptchValid" }
                ); //route to accept our random urls and submit contact requests
            routes.MapRoute(
                name: "CContact",
                url: "Contact/{*slug}",
                defaults: new { controller = "Contact", action = "CContact", slug = System.Web.Http.RouteParameter.Optional }
                );

And actually final step is implementing our POST action to accept random-url submit requests:

        //to return view Contact in Get and POSTback requests
        public ActionResult CContact(string slug = null)
        {
            return View("Contact");
        }
        [HttpPost]
        [ValidateAntiForgeryToken]
        public ActionResult CContact(string slug, string name, string subject, string message, string email)
        { //check if this request is from our own website
            if (Request.UrlReferrer == null || string.IsNullOrEmpty(Request.UrlReferrer.AbsolutePath) ||
                     !(Request.UrlReferrer.ToString().ToLowerInvariant().StartsWith(Extensions.Extensions.DomainName + "/contact") ||
                     Request.UrlReferrer.ToString().ToLowerInvariant().StartsWith(Extensions.Extensions.DomainName2 + "/contact")))
            {
                return HttpNotFound("Resource was not found");
            }
            //get previously stored random url
            string view = Session["contactFormAction"] as string;
            if (string.IsNullOrEmpty(view))
            {
                return HttpNotFound("Resource was not found");
            }
            view = view.Replace("/Contact/", string.Empty); //get random part from route to compare
            if(RouteData.GetRequiredString("slug") != view)
                return HttpNotFound("Resource was not found"); //just in case let's check the token one more time
            var captchaResponse = Request.Form["g-recaptcha-response"];
            if((string)Session["g-recaptcha-response"]!= captchaResponse)
            {
                return HttpNotFound("Resource was not found");
            }

            ... //do whatever you need to process request ...
            ViewBag.Message = "Thank you, your request has been submitted.";

            return View("Contact");
        }

That's it. Now you have dynamic action form url and this url is generated after human-validation only so it will be impossible or almost impossible at least  for spammers to send spam requests.

Thank you and see you :)



1vqHSTrq1GEoEF7QsL8dhmJfRMDVxhv2y



SSS - Simple Serverless Search for your website

Hello friends,


if you are in need of simple, your own search that will search through all your help documents then this may be a good case for you.

I will show my own simple and fast way if such implementation that I'm sure you will be able to use wherever you need: PHP, Java, Python, .NET, Ruby, Node.JS, etc.


So, lets start and see how it works!


1. First stage will be indexing your help files or DB records. In my case I had html files, so here is my code:

        
        char[] cToRemove = new char[] { '\r', '\n', '\t', ' ', ',', '/', '\\', '~', '–', '.', ':', '\'', '!', ';', '[', ']', '"', '{', '}', '=', '+', '_', ')', '(', '*', '&', '?', '%', '$', '#', '@', '`', '<', '>', '|' };
        char[] cToSplit = new char[] { ' ' };
        string[] toSkip = new string[] 
        {
            "i", "me", "you", "he", "she", "they", "them", "this", "that", "than", "then",  "it", "our", "their", "her", "his", "its", "it's",
            "was", "were", "is","are", "be", "being", "been", "can", "could", "should", "shall", "will", "would", "have", "has", "did", "do", "does",
            "may", "might", "must", "need", "better", "if", "else","also", "same", "now","new", "below", "above",
            "itself", "ourselves", "himself", "herself", "theirselves", "let", "get", "set", "done"
        };

        public ActionResult RunHelpCrawler()
        {

            string root = System.Web.HttpContext.Current.Server.MapPath("~/Views/Help");
            string json = System.Web.HttpContext.Current.Server.MapPath("~/shelp.json");

            AsyncManager.OutstandingOperations.Increment();
            System.Threading.Tasks.Task.Factory.StartNew(() =>
            {
                string suberrors = string.Empty;
                try
                {
                    string path = root;
                    string lastKeywrd = string.Empty;
                    //keywords, url, title
                    List<Models.Search> search = new List<Models.Search>();

                    foreach (string filePath in System.IO.Directory.EnumerateFiles(path))
                    {
                        //exclude everything you need
                        if (filePath.Contains("LeftSideMenu") || filePath.Contains("GetHelpTips"))
                        {
                            continue;
                        }

                        try
                        {
                            string url = Extensions.Extensions.DomainName + "/Help/" + System.IO.Path.GetFileNameWithoutExtension(filePath);
                            url = url.ToLowerInvariant();

                            //we are going to parse HTML to avoid unneeded text. tags, etc
                            var web = new HtmlAgilityPack.HtmlWeb();
                            var doc = web.Load(url);

                            var nH1 = doc.DocumentNode.Descendants("h1")
                             .FirstOrDefault();

                            string title = url;
                            if (nH1 != null)
                            {
                                title = nH1.InnerText;
                            }

                            string text = string.Empty;

                            //our HTMLs have marked sections with the 'shelp' class that we will use for indexing only
                            var nodes = doc.DocumentNode.Descendants()
                             .Single(x => x.Attributes["class"] != null && !string.IsNullOrEmpty(x.Attributes["class"].Value) && x.Attributes["class"].Value.Contains("shelp"))
                             .Descendants()
                            .Where(n =>
                               n.NodeType == HtmlAgilityPack.HtmlNodeType.Text &&
                               n.ParentNode.Name != "script" &&
                               n.ParentNode.Name != "style");
                            text = string.Empty;
                            foreach (var node in nodes)
                            {
                                text += node.InnerText;
                            }

                            foreach (var oc in cToRemove)
                            {
                                text = text.Replace(oc, ' ');
                            }
                            text = text.ToLowerInvariant();
                            //words to skip indexing like: he, she, this, that...
                            string[] keywords = text.Split(cToSplit, StringSplitOptions.RemoveEmptyEntries);
                            foreach (var keyword in keywords)
                            {
                                if (!string.IsNullOrEmpty(keyword) && keyword.Length > 2 && !toSkip.Any(a => a == keyword))
                                {
                                    lastKeywrd = keyword;
                                    Models.Search item = null;

                                    if (item == null)
                                    {
                                        item = search.FirstOrDefault(i => i.UrlTitle.Any(a => a.Item1 == url));
                                        if (item == null)
                                        {
                                            item = new Models.Search() { Keywords = "," + keyword + ",", UrlTitle = new List<Tuple<string, string>>() };
                                            search.Add(item);
                                        }
                                        else
                                        {
                                            if (!item.Keywords.Contains("," + keyword + ","))
                                            {
                                                item.Keywords = item.Keywords.TrimEnd(',') + "," + keyword + ",";
                                            }
                                        }
                                    }
                                    if (!item.UrlTitle.Any(a => a.Item1 == url))
                                    {
                                        item.UrlTitle.Add(new Tuple<string, string>(url, title));
                                    }
                                }
                            }
                            search = search.GroupBy(g => g.Keywords).Select(s => s.First()).ToList();
                        }
                        catch(Exception ex)
                        {
                            //LogException
                        }
                    }

                    using (System.IO.StreamWriter sw = new StreamWriter(json, false))
                    {

                        sw.Write(SimpleJson.SimpleJson.SerializeObject(search));
                    }
                }
                catch (Exception ex)
                {
                    //LogException
                }
            });


            return Content("ok");
        }


Whenever you add new help document just re-run this simple crawler that will re-index everything


It is not important what platform and language you use to generate json, it is important what json structure you will get.

So here is the structure of json you need to generate so the javascript code would recognize and use it properly:

[
    {
        "Keywords""kword1,kword2,kword3",
        "UrlTitle": [
            {
                "Item1""https://www.yourwebsiteurl.com/help/page1",
                "Item2""Title of the page 1"
            }
        ]
    },
    {
        "Keywords""kword1,kword2,kword3",
        "UrlTitle": [
            {
                "Item1""https://www.yourwebsiteurl.com/help/page2",
                "Item2""Title of the page 2"
            }
        ]
    },
    {
        "Keywords""kword1,kword2,kword3",
        "UrlTitle": [
            {
                "Item1""https://www.yourwebsiteurl.com/help/page3",
                "Item2""Title of the page 3"
            }
        ]
    }, ....


2. UI and Javascript parts to allow users using this simple serverless search

UI

		<div class="row">
                    <div class="col-md-12">
                        <div class="input-group">
                            <span class="input-group-addon" id="shlpSearch" style="border: 1px solid #ccc;height: 26px;padding-top: 2px;padding-bottom: 2px;">Search</span>
                            <input type="search" id="hlpSearch" class="form-control" style="max-width:100%;height: 26px;padding: 6px;" title="Search help"
                                   placeholder="How to ...">
                        </div>
                    </div>
                </div>

JS

<script>
	var shelp;
        //load our index json and avoid caching
        $.getJSON("/shelp.json?antc="+new Date().getTime(), function (data) {
            shelp = data;
        });
		
	var prevHtml = "";
        function searchHelp(sh) {
            if (sh != "" && sh.length > 2) {
                if (prevHtml == "") {
                    prevHtml = $(".shelp").html();
                }
                var items = [];
		var lessitems=[];
                var ss = sh.replace(",", " ").split(" ");
				
		$.each(shelp, function (i) {
			var br=0;
			for(var f=0;f<ss.length;f++){
				br += (shelp[i].Keywords.indexOf(ss[f]) >= 0)?1:0;
			}
			$.each(shelp[i].UrlTitle, function (ii) {
				var el = "<a class='label label-default' style='font-size:125%;line-height:2' href='" + shelp[i].UrlTitle[ii].Item1 + "'>" + shelp[i].UrlTitle[ii].Item2 + "</a>";
				//most relevant first
                                if(br==ss.length){
					if ($.inArray(el, items) === -1){
						items.unshift(el);
					}
				}
				else if(br > 0){ //less relevant but containing at least one keyword
					if ($.inArray(el, items) === -1 && $.inArray(el, lessitems) === -1){
						lessitems.push(el);
					}
				}
			});
					
		});
		if(lessitems.length>0){
                        //split less relevant by horizontal line
			items.push("<hr style='margin: 0;padding: 0;'/>");
			$.each(lessitems, function (i) {
				items.push(lessitems[i]);
			});
		}
				
		$(".shelp").css("border", "1px solid");
		$(".shelp").css("box-shadow", "0 0 8px 1px");
                $(".shelp").html(items.join("<br/>"));
            }
            else if (prevHtml != "") {
		$(".shelp").css("border", "");
		$(".shelp").css("box-shadow", "");
                $(".shelp").html(prevHtml);
                prevHtml = "";
            }
        }
		
	$(document).ready(function () {
            //attach event for searching
            $("#hlpSearch").on("keyup mouseup input search touchend", function (e) {
                searchHelp($(this).val());
            });
        });
    </script>

So, we will store previous html and insert a new generated html with search items. First items will be more relevant that contains more than 1 keyword in chain. Horizontal line will split most relevant items from less relevant items.


You can add more specific logic for example to avoid plural forms, add importance of keyword order, etc. but as simple and fastest search this will be more than enough.


Thank you and see you


Updated JavaScript version (2020):

        function searchHelp(sh) {
            if (sh != "" && sh.length > 2) {
                if (prevHtml == "") {
                    prevHtml = $(".shelp").html();
                }
                var topitems = [];
                var items = [];
                var lessitems=[];
                var ss = sh.replace(","" ").match(/[^ ]+/g);
                ss = ss.filter(function(item) { 
                  return item.length > 3;
                });
                
                $.each(shelpfunction (i) {
                    var br=0;
                    for(var f=0;f<ss.length;f++){
                        if(ss[f]!=" " && ss[f]!="" && ss[f].length>3){
                            br += (shelp[i].Keywords.indexOf(ss[f]) >= 0)?1:0;
                        }
                    }
                    
                    $.each(shelp[i].UrlTitlefunction (ii) {
                        var el = "<a class='label label-default' "+ "style='font-size:125%;line-height:2;width:100%;display:inline-flex;'"+ " href='" + shelp[i].UrlTitle[ii].Item1 + "'>" +  shelp[i].UrlTitle[ii].Item2 + "</a>";
                        var inTitle = false;
                        $.each(ssfunction (si) {  inTitle |= shelp[i].UrlTitle[ii].Item2.search(new RegExp(ss[si], "i")) >= 0;  });
                        if (inTitle) {
                            if ($.inArray(eltopitems) === -1){
                                topitems.push(el);
                            }
                        }
                        else if (br == ss.length) {
                            if ($.inArray(elitems) === -1) {
                                items.unshift(el);
                            }
                        }
                        else if(br > 0){
                            if ($.inArray(elitems) === -1 && $.inArray(ellessitems) === -1){
                                lessitems.push(el);
                            }
                        }
                    });
                    
                });
                $.each(topitemsfunction (i) {
                    items.unshift(topitems[i]);
                });
                if(lessitems.length>0){
                    items.push("<hr style='margin: 0;padding: 0;'/>"+ "<span style='font-size: 65%;padding-left: 1%'>less relevant</span>");
                    $.each(lessitemsfunction (i) {
                        items.push(lessitems[i]);
                    });
                }
                
                $(".shelp").css("border""1px solid");
                $(".shelp").css("box-shadow""0 0 8px 1px");
                $(".shelp").html("<div class='ishelp'>" + items.join("<br/>")+"</div>");
            }
            else if (prevHtml != "") {
                $(".shelp").css("border""");
                $(".shelp").css("box-shadow""");
                $(".shelp").html(prevHtml);
                prevHtml = "";
            }
        }


This update will set items that contain keywords in title on top of the list.


Thank you



1vqHSTrq1GEoEF7QsL8dhmJfRMDVxhv2y



NopCommerce customization - Full cycle of product adding in batch

Are you in need of adding a lot +40K of products, attributes, images, slugs, tags, manufacturers in batch?

Then this post should be same useful for you as for me.


In my case I did require to implement a logic for adding more than 40,000 products with attributes, images, etc. feeding data from a 3rd party API. We will add products to all enabled categories and subcategories that are shown on home page/menu.


So less water more sense :) - here is the step by step explanation:



        [AuthorizeAdmin]
        [Area(AreaNames.Admin)]
        public IActionResult GetProgress()
        {
            //to keep progress
            string progress = string.Empty;
            var storeScope = _storeContext.ActiveStoreScopeConfiguration;
            var directory = "~/wwwroot/images/uploaded/";
            
            string curl = string.Empty;
            //Use direct repo access because of cache problem with re-reading data from settings
            Core.Data.IRepository<Core.Domain.Configuration.Setting> _settingRepository = Core.Infrastructure.EngineContext.Current.Resolve<Core.Data.IRepository<Core.Domain.Configuration.Setting>>();

            try
            {
                //get list of rest of categories we should add product for
                string ddcat = _settingRepository.Table.SingleOrDefault(s=>s.Name=="DDCategories" && s.StoreId== storeScope)?.Value;
                if (string.IsNullOrEmpty(ddCat))
                {
                    //seems this is the first run
                    var nopallcats = _categoryService.GetAllCategoriesDisplayedOnHomePage(false).Where(c=>c.Published).Select(s => new Category() { ID = s.Id }).ToList();
                    List<int> finalcategories = new List<int>();
                    foreach (var c1 in nopallcats)
                    {
                        var aa1 = _categoryService.GetAllCategoriesByParentCategoryId((int)c1.ID, true);
                        if (aa1.Count > 0)
                        {
                            foreach (var c2 in aa1)
                            {
                                var aa2 = _categoryService.GetAllCategoriesByParentCategoryId(c2.Id, true);

                                if (aa2.Count > 0 && c2.Published)
                                {
                                    foreach (var c3 in aa2)
                                    {
                                        var aa3 = _categoryService.GetAllCategoriesByParentCategoryId(c3.Id, true);
                                        if (aa3.Count <= 0 && c3.Published)
                                        {
                                            finalcategories.Add(c3.Id);
                                        }
                                        else
                                        {
                                            //we have only 3 level of sub categories
                                            throw new ApplicationException("Too many subcategories");
                                        }
                                    }
                                }
                                else
                                {
                                    finalcategories.Add(c2.Id);
                                }
                            }
                        }
                        else
                        {
                            finalcategories.Add((int)c1.ID);
                        }
                    }

                    nopallcats = null;
                    //store remind categories into the settings
                    _settingService.SetSetting<string>("DDCategories", Newtonsoft.Json.JsonConvert.SerializeObject(finalcategories, Newtonsoft.Json.Formatting.None,
                        new Newtonsoft.Json.JsonSerializerSettings()
                        {
                            ReferenceLoopHandling = Newtonsoft.Json.ReferenceLoopHandling.Ignore
                        }), storeScope, true);

                    progress = string.Format("Categories: {0} --", finalcategories.Count);
                    _cache.Set<string>("Progress", progress, TimeSpan.FromMinutes(30));
                }
                else
                {

                    progress = "Start working on category...";
                    _cache.Set<string>("Progress", progress, TimeSpan.FromMinutes(30));
                    List<int> categories = Newtonsoft.Json.JsonConvert.DeserializeObject<List<int>>(ddCat);
                    int products = 0;
                    //take first category to work on
                    var rcategory = categories.FirstOrDefault();
                    //if category id > 0 then lets work on it or exit otherwise
                    if (rcategory != 0)
                    {
                        try
                        {

                            List<Price> prices = null;

                            string responseText = string.Empty;
                            string json = string.Empty;
                            System.Net.HttpWebRequest r = null;
                            System.Net.WebClient webClient = new System.Net.WebClient();
                            lock (Feeder.fileLock)
                            {
                                string pricefile = _fileProvider.GetFileName("dd_prices.json");
                                pricefile = _fileProvider.Combine(_fileProvider.MapPath(dir2), pricefile);
                                //if price file does not exists or older than 60 mins then get new
                                if (!_fileProvider.FileExists(pricefile) || DateTime.UtcNow.Subtract(_fileProvider.GetLastWriteTimeUtc(pricefile)).TotalMinutes > 60)
                                {
                                    responseText = null;
                                }
                                else
                                {

                                    responseText = _fileProvider.ReadAllText(pricefile, Encoding.UTF8);

                                    prices = Newtonsoft.Json.JsonConvert.DeserializeObject<List<Price>>(responseText);
                                    responseText = progress = "re-read prices from file;";
                                    _cache.Set<string>("Progress", progress, TimeSpan.FromMinutes(30));
                                }
                                if (string.IsNullOrEmpty(responseText) && prices == null)
                                {
                                    curl = Feeder.apiUrl + Feeder.getPrices;
                                    r = (System.Net.HttpWebRequest)System.Net.WebRequest.Create(curl);
                                    r.ContentType = "application/x-www-form-urlencoded";
                                    r.Method = "POST";

                                    json = "key=" + Feeder.apiKey;

                                    using (System.IO.StreamWriter sw = new System.IO.StreamWriter(r.GetRequestStream()))
                                    {
                                        sw.Write(json);
                                        sw.Flush();
                                        sw.Close();
                                    }

                                    using (var reader = new System.IO.StreamReader(r.GetResponse().GetResponseStream()))
                                    {
                                        responseText = reader.ReadToEnd();


                                        dynamic dynJson3 = Newtonsoft.Json.JsonConvert.DeserializeObject(responseText);
                                        responseText = null;
                                        prices = new List<Price>();
                                        foreach (var pr in dynJson3.prices)
                                        {
                                            //get prices from JSON and save to own JSON file
                                            dynamic price = ((Newtonsoft.Json.Linq.JToken)pr).First;
                                            prices.Add(new Price()
                                            {
                                                code = price.sku,
                                                id = price.id,
                                                price = price.price,
                                                RPrice = price.RPrice,
                                                delivery = price.delivery,
                                                deliveryText = price.deliveryText
                                            });
                                        }

                                        _fileProvider.WriteAllText(pricefile, Newtonsoft.Json.JsonConvert.SerializeObject(prices, Newtonsoft.Json.Formatting.None,
                                                new Newtonsoft.Json.JsonSerializerSettings()
                                                {
                                                    ReferenceLoopHandling = Newtonsoft.Json.ReferenceLoopHandling.Ignore
                                                }), Encoding.UTF8);


                                        progress = "prices re-downloaded again;";
                                        _cache.Set<string>("Progress", progress, TimeSpan.FromMinutes(30));
                                    }
                                }
                            }

                            
                            responseText = null;

                            var dateranges = _dateRangeService.GetAllDeliveryDates();

                            ProdTemp pt = null;
                            var category = rcategory;
                            int count = 0;
                            int offset = 0, limit = 100, prodProcessed = 0;
                            var ccc = _categoryService.GetCategoryById(category);

                            string temprod = _settingRepository.Table.SingleOrDefault(s => s.Name == "DDProdcts" && s.StoreId == storeScope)?.Value;
                            //create new or re-read last offset object
                            if (string.IsNullOrEmpty(temprod))
                            {
                                pt = new ProdTemp();
                                pt.StoreID = storeScope;
                            }
                            else
                            {
                                pt = Newtonsoft.Json.JsonConvert.DeserializeObject<ProdTemp>(temprod);
                                limit = pt.Limit;
                                offset = pt.Offset;
                                count = pt.TotalProducts;
                                prodProcessed = pt.ProcessedProducts;
                                storeScope = pt.StoreID.GetValueOrDefault();
                            }


                            curl = Feeder.apiUrl + Feeder.getItems;
                            r = (System.Net.HttpWebRequest)System.Net.WebRequest.Create(curl);
                            r.ContentType = "application/x-www-form-urlencoded";
                            r.Method = "POST";

                            //get 3rd party category id to get products for it
                            var ccatid = _genericAttributeService.GetAttribute<int>(ccc, Feeder.gAttrID, storeScope);

                            json = "key=" + Feeder.apiKey + "&limit=" + limit + "&offset=" + offset + "&modifiedDT=" +
                                DateTime.UtcNow.Date.AddMonths(-6).ToString("yyyy-MM-dd", System.Globalization.CultureInfo.InvariantCulture) +
                                "&brand=" + "&category=" + ccatid;
                            using (System.IO.StreamWriter sw = new System.IO.StreamWriter(r.GetRequestStream()))
                            {
                                sw.Write(json);
                                sw.Flush();
                                sw.Close();
                            }
                            responseText = string.Empty;
                            using (var reader = new System.IO.StreamReader(r.GetResponse().GetResponseStream()))
                            {
                                responseText = reader.ReadToEnd();
                            }

                            dynamic dynJson2 = Newtonsoft.Json.JsonConvert.DeserializeObject(responseText);

                            responseText = null;
                            string swm = string.Empty;

                            count = int.Parse((string)dynJson2.count);

                            progress = string.Format("Categories: {0}, Products: {1}/{2}", ccc.Name + "/" + categories.Count, count, prodProcessed);
                            _cache.Set<string>("Progress", progress, TimeSpan.FromMinutes(30));

                            Core.Domain.Catalog.Product pro = null;
                            dynamic product;
                            Price pprice;

                            var manufRepository = Core.Infrastructure.EngineContext.Current.Resolve<Core.Data.IRepository<Core.Domain.Catalog.Manufacturer>>();
                            var productRepository = Core.Infrastructure.EngineContext.Current.Resolve<Core.Data.IRepository<Core.Domain.Catalog.Product>>();
                            var productTagRepository = Core.Infrastructure.EngineContext.Current.Resolve<Core.Data.IRepository<Core.Domain.Catalog.ProductTag>>();
                            var prodAttrRepo= Core.Infrastructure.EngineContext.Current.Resolve<Core.Data.IRepository<Core.Domain.Catalog.ProductAttribute>>();
                            var spAttrRepository = Core.Infrastructure.EngineContext.Current.Resolve<Core.Data.IRepository<Core.Domain.Catalog.SpecificationAttribute>>();
                            var spOAttrRepository = Core.Infrastructure.EngineContext.Current.Resolve<Core.Data.IRepository<Core.Domain.Catalog.SpecificationAttributeOption>>();

                            var prodAttrMapRepo = Core.Infrastructure.EngineContext.Current.Resolve<Core.Data.IRepository<Core.Domain.Catalog.ProductAttributeMapping>>();
                            var prodAttrValRepo = Core.Infrastructure.EngineContext.Current.Resolve<Core.Data.IRepository<Core.Domain.Catalog.ProductAttributeValue>>();
                            var prodTagMapRepo = Core.Infrastructure.EngineContext.Current.Resolve<Core.Data.IRepository<Core.Domain.Catalog.ProductProductTagMapping>>();
                            var prodCatMapRepo = Core.Infrastructure.EngineContext.Current.Resolve<Core.Data.IRepository<Core.Domain.Catalog.ProductCategory>>();
                            var prodSAttrMapRepo = Core.Infrastructure.EngineContext.Current.Resolve<Core.Data.IRepository<Core.Domain.Catalog.ProductSpecificationAttribute>>();

                            string entityName = typeof(Core.Domain.Catalog.Product).Name;
                            var urlRepository = Core.Infrastructure.EngineContext.Current.Resolve<Core.Data.IRepository<Core.Domain.Seo.UrlRecord>>();
                            //stopwatch to get timing
                            System.Diagnostics.Stopwatch swc = new System.Diagnostics.Stopwatch();
                            double totalTimeSec = 0;
                            foreach (var prodd in dynJson2.items)
                            {

                                totalTimeSec = 0;
                                swm = string.Empty;

                                _cache.Set<string>("Progress", string.Format("Categories: {0}, Products: {1}/{2}", ccc.Name + "/" + categories.Count, count, prodProcessed), TimeSpan.FromMinutes(30));

                                product = ((Newtonsoft.Json.Linq.JToken)prodd).First;

                                int pid = int.Parse((string)Newtonsoft.Json.Linq.JProperty.FromObject(prodd).Name);

                                progress = "pprice | " + string.Format("Categories: {0}, Products: {1}/{2}", ccc.Name + "/" + categories.Count, (string)dynJson2.count, prodProcessed);
                                _cache.Set<string>("Progress", progress, TimeSpan.FromMinutes(30));
                                pprice = prices.SingleOrDefault(p => p.id == pid);

                                if (pprice == null)
                                {
                                    pprice = prices.SingleOrDefault(p => p.code == pid.ToString("000000") + "-02");
                                }
                                string pname = product.name;

                                swc.Start();

                                pro = null;
                                //different products may have same SKUs so take care of it
                                pro = _productService.GetProductsBySku(new string[] { (string)product.article }).SingleOrDefault(sk=> sk.Name== pname && sk.ProductCategories.SingleOrDefault(pc=>pc.CategoryId==category)!=null);

                                swc.Stop();
                                totalTimeSec += swc.Elapsed.TotalSeconds;
                                swm = "GetProductBySku: " + swc.Elapsed.TotalSeconds.ToString("0.##");
                                _cache.Set<string>("Progress", progress + swm, TimeSpan.FromMinutes(30));
                                swc.Reset();
                                //we add products if price exists only
                                if (pprice != null && pro == null)
                                {
                                    decimal cost = pprice.price;
                                    decimal price = Feeder.CalculatePrice(cost, pprice.RPrice);

                                    if(price<cost)
                                    {
                                        price = pprice.RPrice;
                                    }
                                    //define max price for category to filter by later
                                    if (pt.MaxPrice < price)
                                        pt.MaxPrice = price;

                                    //manufacturer
                                    swc.Start();
                                    int manuid = product.brand;
                                    progress = "manufs | " + string.Format("Categories: {0}, Products: {1}/{2}", ccc.Name + "/" + categories.Count, (string)dynJson2.count, prodProcessed);
                                    _cache.Set<string>("Progress", progress + " " + swm, TimeSpan.FromMinutes(30));
                                    string bname = (string)product.brandName;
                                    var manuf = manufRepository.Table.FirstOrDefault(m => m.Name == bname);
                                    if (manuf == null)
                                    {
                                        manuf = new Core.Domain.Catalog.Manufacturer()
                                        {
                                            Name = product.brandName,
                                            CreatedOnUtc = DateTime.UtcNow,
                                            ManufacturerTemplateId = 1,
                                            MetaDescription = product.brandName,
                                            MetaKeywords = product.brandName,
                                            MetaTitle = product.brandName,
                                            PageSize = 20,
                                            Published = true,
                                            UpdatedOnUtc = DateTime.UtcNow
                                        };
                                        _manufacturerService.InsertManufacturer(manuf);
                                        _storeMappingService.InsertStoreMapping<Core.Domain.Catalog.Manufacturer>(manuf, storeScope);
                                        //store 3rd party manufacturer id to use later
                                        _genericAttributeService.SaveAttribute<int>(manuf, Feeder.gAttrID, manuid);
                                        entityName = typeof(Core.Domain.Catalog.Manufacturer).Name;
                                        //add slug if not exists
                                        if (urlRepository.TableNoTracking.SingleOrDefault(u => u.EntityId == manuf.Id && u.EntityName == entityName && u.LanguageId == 0) == null)
                                        {
                                            var slug1 = _urlRecordService.ValidateSeName(manuf, manuf.Name, manuf.Name, true);
                                            _urlRecordService.InsertUrlRecord(new Core.Domain.Seo.UrlRecord
                                            {
                                                EntityId = manuf.Id,
                                                EntityName = entityName,
                                                LanguageId = 0,
                                                IsActive = true,
                                                Slug = slug1
                                            });
                                        }

                                    }
                                    swc.Stop();
                                    totalTimeSec += swc.Elapsed.TotalSeconds;
                                    swm += ", manufs: " + swc.Elapsed.TotalSeconds.ToString("0.##");
                                    swc.Reset();

                                    //date ranges
                                    swc.Start();
                                    Core.Domain.Shipping.DeliveryDate daterange;
                                    progress = "daterange | " + string.Format("Categories: {0}, Products: {1}/{2}", ccc.Name + "/" + categories.Count, (string)dynJson2.count, prodProcessed);
                                    _cache.Set<string>("Progress", progress + " " + swm, TimeSpan.FromMinutes(30));

                                    {
                                        string dt = (string)pprice.deliveryText;
                                        daterange = dateranges.FirstOrDefault(d => d.Name == dt);
                                        if (daterange == null)
                                        {
                                            daterange = new Core.Domain.Shipping.DeliveryDate()
                                            {
                                                Name = pprice.deliveryText
                                            };
                                            _dateRangeService.InsertDeliveryDate(daterange);
                                            dateranges.Add(daterange);
                                        }
                                    }

                                    swc.Stop();
                                    totalTimeSec += swc.Elapsed.TotalSeconds;
                                    swm += ", daterange: " + swc.Elapsed.TotalSeconds.ToString("0.##");
                                    swc.Reset();

                                    //product
                                    swc.Start();
                                    {
                                        //some formatting and length limitation
                                        string shortd = product.descriptionSnort;
                                        if (shortd.Length > 400)
                                        {
                                            shortd = shortd.Substring(0, 399);
                                        }
                                        
                                        if (pname.Length > 400)
                                        {
                                            pname = pname.Substring(0, 399);
                                        }

                                        pro = new Core.Domain.Catalog.Product()
                                        {
                                            ProductType = Core.Domain.Catalog.ProductType.SimpleProduct,
                                            VisibleIndividually = true,
                                            Sku = product.article,
                                            IsShipEnabled = true,
                                            MetaDescription = shortd,
                                            MetaKeywords = shortd,
                                            MetaTitle = pname,
                                            Price = price,
                                            ProductCost = cost,
                                            AdditionalShippingCharge = Feeder.CalculateShipping(price),
                                            CreatedOnUtc = DateTime.UtcNow,
                                            UpdatedOnUtc = ((DateTime)product.modifiedDT).ToUniversalTime(),
                                            Name = pname,
                                            ShortDescription = product.descriptionSnort,
                                            FullDescription = product.description,
                                            BackorderMode = Core.Domain.Catalog.BackorderMode.NoBackorders,
                                            MarkAsNew = true,
                                            AllowBackInStockSubscriptions = true,
                                            AllowCustomerReviews = true,
                                            Published = true,
                                            DeliveryDateId = daterange == null ? 0 : daterange.Id,
                                            LimitedToStores = true, OrderMaximumQuantity=int.MaxValue
                                           ,OrderMinimumQuantity=1, DisableBuyButton = true, ProductTemplateId = 1, ProductManufacturers =
                                        {
                                            new Core.Domain.Catalog.ProductManufacturer() { Manufacturer = manuf }
                                        }
                                            

                                        };
                                        if (!string.IsNullOrEmpty(pro.FullDescription))
                                            pro.FullDescription = pro.FullDescription.Replace("font-size: 1rem;", string.Empty);

                                    }
                                    {
                                        pro.ProductCategories.Add(new Core.Domain.Catalog.ProductCategory()
                                        {
                                            CategoryId = category
                                        });
                                    }

                                    _productService.InsertProduct(pro);

                                    _genericAttributeService.SaveAttribute<int>(pro, Feeder.gAttrID, pid, storeScope);
                                    _genericAttributeService.SaveAttribute<string>(pro, Feeder.gAttrCode, pprice.code, storeScope);

                                    products++;

                                    swc.Stop();
                                    totalTimeSec += swc.Elapsed.TotalSeconds;
                                    swm += ", product: " + swc.Elapsed.TotalSeconds.ToString("0.##");
                                    swc.Reset();

                                    //tags
                                    swc.Start();
                                    progress = "ttag | " + string.Format("Categories: {0}, Products: {1}/{2}", ccc.Name + "/" + categories.Count, (string)dynJson2.count, prodProcessed);
                                    _cache.Set<string>("Progress", progress + " " + swm, TimeSpan.FromMinutes(30));
                                    foreach (var tt in pro.Name.Split(' ', StringSplitOptions.RemoveEmptyEntries))
                                    {
                                        string tag = tt.ToUpperInvariant().Trim().Trim(Feeder.trimChars);
                                        if (!string.IsNullOrEmpty(tag))
                                        {
                                            if (manufRepository.Table.Any(m => m.Name == tag))
                                                continue;

                                            var ttag = productTagRepository.Table.FirstOrDefault(t => t.Name == tag);
                                            if (ttag == null)
                                            {
                                                ttag = new Core.Domain.Catalog.ProductTag() { Name = tag };

                                                _productTagService.InsertProductTag(ttag);

                                                pro.ProductProductTagMappings.Add(new Core.Domain.Catalog.ProductProductTagMapping()
                                                {
                                                    ProductTag = ttag,
                                                });


                                                entityName = typeof(Core.Domain.Catalog.ProductTag).Name;

                                                    if (urlRepository.TableNoTracking.SingleOrDefault(u => u.EntityId == ttag.Id && u.EntityName == entityName && u.LanguageId == 0) == null)
                                                    {
                                                        var slug2 = _urlRecordService.ValidateSeName(ttag, ttag.Name, ttag.Name, true);
                                                        _urlRecordService.InsertUrlRecord(new Core.Domain.Seo.UrlRecord
                                                        {
                                                            EntityId = ttag.Id,
                                                            EntityName = entityName,
                                                            LanguageId = 0,
                                                            IsActive = true,
                                                            Slug = slug2
                                                        });
                                                    }
                                                
                                            }
                                            else if (!prodTagMapRepo.Table.Any(a => a.ProductTagId == ttag.Id && a.ProductId == pro.Id))//!pro.ProductProductTagMappings.Any(a => a.ProductTag.Id == ttag.Id))
                                            {
                                                if (!pro.ProductProductTagMappings.Any(a => a.ProductTag.Id == ttag.Id))
                                                {
                                                    pro.ProductProductTagMappings.Add(new Core.Domain.Catalog.ProductProductTagMapping()
                                                    {
                                                        ProductTag = ttag,
                                                    });
                                                }
                                            }
                                        }
                                    }

                                    _productService.UpdateProduct(pro);

                                    swc.Stop();
                                    totalTimeSec += swc.Elapsed.TotalSeconds;
                                    swm += ", ttag: " + swc.Elapsed.TotalSeconds.ToString("0.##");
                                    swc.Reset();
                                    _cache.Set<string>("Progress", progress + " " + swm, TimeSpan.FromMinutes(30));

                                    //pictures
                                    swc.Start();
                                    AddPictures(product, pro);
                                    _productService.UpdateProduct(pro);


                                    swc.Stop();
                                    totalTimeSec += swc.Elapsed.TotalSeconds;
                                    swm += ", pictures: " + swc.Elapsed.TotalSeconds.ToString("0.##");
                                    swc.Reset();
                                    _cache.Set<string>("Progress", progress + " " + swm, TimeSpan.FromMinutes(30));

                                    bool colDef = true;

                                    //attributes
                                    swc.Start();
                                    Core.Domain.Catalog.ProductAttribute pattr = null;
                                    Core.Domain.Catalog.SpecificationAttribute spattr = null;
                                    Core.Domain.Catalog.SpecificationAttributeOption spao = null;
                                    List<Core.Domain.Catalog.ProductSpecificationAttribute> prodSAttrMapRepo_later = new List<Core.Domain.Catalog.ProductSpecificationAttribute>();

                                    dynamic chr;
                                    Newtonsoft.Json.Linq.JProperty cjp;
                                    string val, name, rgb;
                                    System.Drawing.Color c;
                                    progress = "pattr | " + string.Format("Categories: {0}, Products: {1}/{2}", ccc.Name + "/" + categories.Count, (string)dynJson2.count, prodProcessed);
                                    _cache.Set<string>("Progress", progress + " " + swm, TimeSpan.FromMinutes(30));
                                    string nlo;
                                    string spaovu;
                                    string spaonu;
                                    foreach (var ch in product.characteristics)
                                    {

                                        chr = ((Newtonsoft.Json.Linq.JToken)ch).First;
                                        cjp = Newtonsoft.Json.Linq.JProperty.FromObject(ch) as Newtonsoft.Json.Linq.JProperty;
                                        val = ((Newtonsoft.Json.Linq.JValue)chr).Value as string;
                                        name = cjp.Name.Trim().Trim(Feeder.trimChars);
                                        if (name.IndexOf('(') >= 0 && name.IndexOf(')') < 0)
                                        {
                                            name += ")";
                                        }
                                        if (name.IndexOf('[') >= 0 && name.IndexOf(']') < 0)
                                        {
                                            name += "]";
                                        }
                                        if (name.IndexOf('{') >= 0 && name.IndexOf('}') < 0)
                                        {
                                            name += "}";
                                        }
                                        nlo = name.ToLowerInvariant();
                                        spaovu = val.ToUpperInvariant();
                                        spaonu = name.ToUpperInvariant();

                                        //work on colors
                                        if (nlo == "цвет" || nlo == "color" || nlo == "колір" || (nlo.Contains("цвет ") || nlo.Contains("color ") || nlo.Contains("колір ")))
                                        {
                                            progress = "pattr | " + string.Format("Categories: {0}, Products: {1}/{2}", categories.Count, (string)dynJson2.count, prodProcessed);
                                            pattr = prodAttrRepo.Table.FirstOrDefault(a => a.Name == name);
                                            if (pattr == null)
                                            {
                                                pattr = new Core.Domain.Catalog.ProductAttribute()
                                                {
                                                    Name = name

                                                };

                                                _productAttributeService.InsertProductAttribute(pattr);

                                            }
                                            string[] colors = spaovu.Split(',', StringSplitOptions.RemoveEmptyEntries);

                                            foreach (var sc in colors)
                                            {
                                                rgb = "#ffffff";
                                                var ssc = sc.Replace(" ", string.Empty).Replace("-", string.Empty).Replace("/", string.Empty).Replace("+", string.Empty).ToLowerInvariant();
                                                var sccc = Feeder.colors.SingleOrDefault(s => s.Item2.Contains(ssc));
                                                if (sccc != null)
                                                    rgb = sccc.Item1;
                                                else
                                                {
                                                    c = System.Drawing.Color.FromName(val);

                                                    rgb = "#" + c.R.ToString("X2") + c.G.ToString("X2") + c.B.ToString("X2");
                                                }

                                                if (!prodAttrMapRepo.Table.Any(a => a.ProductAttributeId == pattr.Id && a.ProductId == pro.Id))
                                                    if (!pro.ProductAttributeMappings.Any(a => a.ProductAttribute.Id == pattr.Id
                                                        && a.ProductAttributeValues.Any(aa => aa.Name.ToUpper() == spaovu)))
                                                    {

                                                        pro.ProductAttributeMappings.Add(new Core.Domain.Catalog.ProductAttributeMapping()
                                                        {
                                                            AttributeControlType = Core.Domain.Catalog.AttributeControlType.ColorSquares,
                                                            IsRequired = colors.Length>1,

                                                            ProductAttribute = pattr,
                                                            ProductAttributeValues =
                                            {
                                                new Core.Domain.Catalog.ProductAttributeValue
                                                {
                                                    AttributeValueType = Core.Domain.Catalog.AttributeValueType.Simple,
                                                    Name = sc, IsPreSelected=colDef, ColorSquaresRgb=rgb
                                                }
                                            }

                                                        });
                                                        colDef = false;
                                                    }
                                            }
                                        }
                                        else //work on specification attributes
                                        {
                                            progress = "spattr | " + string.Format("Categories: {0}, Products: {1}/{2}", ccc.Name + "/" + categories.Count, (string)dynJson2.count, prodProcessed);
                                            _cache.Set<string>("Progress", progress, TimeSpan.FromMinutes(30));
                                            spattr = spAttrRepository.Table.FirstOrDefault(a => a.Name == name);
                                            spao = null;
                                            if (spattr == null)
                                            {
                                                spattr = new Core.Domain.Catalog.SpecificationAttribute
                                                {
                                                    Name = name
                                                };
                                                _specificationAttributeService.InsertSpecificationAttribute(spattr);

                                                spao = new Core.Domain.Catalog.SpecificationAttributeOption()
                                                {
                                                    Name = val,
                                                    SpecificationAttribute = spattr
                                                };
                                                _specificationAttributeService.InsertSpecificationAttributeOption(spao);
                                            }
                                            else
                                            {
                                                progress = "spao | " + string.Format("Categories: {0}, Products: {1}/{2}", ccc.Name + "/" + categories.Count, (string)dynJson2.count, prodProcessed);
                                                _cache.Set<string>("Progress", progress, TimeSpan.FromMinutes(30));

                                                spao = spOAttrRepository.Table.FirstOrDefault(s => s.SpecificationAttributeId == spattr.Id && s.Name == val);
                                                if (spao == null)
                                                {
                                                    progress = "!!!" + progress;
                                                    _cache.Set<string>("Progress", progress + " " + swm, TimeSpan.FromMinutes(30));
                                                    
                                                    {
                                                        spao = new Core.Domain.Catalog.SpecificationAttributeOption()
                                                        {
                                                            SpecificationAttribute = spattr,
                                                            Name = val

                                                        };
                                                        _specificationAttributeService.InsertSpecificationAttributeOption(spao);
                                                    }

                                                   
                                                }
                                            }

                                            if (!prodSAttrMapRepo.Table.Any(a => a.SpecificationAttributeOptionId == spao.Id &&
                                             a.ProductId == pro.Id))
                                                if (!pro.ProductSpecificationAttributes.Any(a => a.SpecificationAttributeOptionId == spao.Id
                                                     && a.SpecificationAttributeOption.SpecificationAttributeId == spattr.Id))
                                                {
                                                    pro.ProductSpecificationAttributes.Add(new Core.Domain.Catalog.ProductSpecificationAttribute()
                                                    {
                                                        //disable filtering by SKU or whatever you need
                                                        AllowFiltering = !excludeParams.Any(a => nlo.ToUpper().Contains(a.ToUpper())),
                                                        ShowOnProductPage = nlo.ToUpper() != excludeParams[0].ToUpper() && nlo.ToUpper() != excludeParams[1].ToUpper(),
                                                        SpecificationAttributeOption = spao,
                                                        AttributeType = Core.Domain.Catalog.SpecificationAttributeType.Option
                                                    });
                                            }
                                        }
                                        _productService.UpdateProduct(pro);
                                    }

                                    entityName = typeof(Core.Domain.Catalog.Product).Name;
                                    var slugp = _urlRecordService.ValidateSeName(pro, pro.Name, pro.Name, true);
                                    urlRepository.Insert(new Core.Domain.Seo.UrlRecord
                                    {
                                        EntityId = pro.Id,
                                        EntityName = entityName,
                                        LanguageId = 0,
                                        IsActive = true,
                                        Slug = slugp
                                    });

                                    swc.Stop();
                                    totalTimeSec += swc.Elapsed.TotalSeconds;
                                    swm += ", pattr: " + swc.Elapsed.TotalSeconds.ToString("0.##");
                                    swc.Reset();
                                    _cache.Set<string>("Progress", totalTimeSec.ToString("0.###") + ":" + progress + " " +  swm, TimeSpan.FromMinutes(30));

                                }
                                else if (pro != null)
                                {
                                    //do whatever you need if product already exists
                                }

                                prodProcessed++;
                                offset++;

                                pt.Limit = limit;
                                pt.Offset = offset;
                                pt.ProcessedProducts = prodProcessed;
                                pt.TotalProducts = count;

                                _settingService.SetSetting<string>("DDProdcts", Newtonsoft.Json.JsonConvert.SerializeObject(pt, Newtonsoft.Json.Formatting.None,
                                new Newtonsoft.Json.JsonSerializerSettings()
                                {
                                    ReferenceLoopHandling = Newtonsoft.Json.ReferenceLoopHandling.Ignore
                                }), storeScope, true);
                            }

                            int icount = dynJson2.items is Newtonsoft.Json.Linq.JArray ? ((Newtonsoft.Json.Linq.JArray)dynJson2.items).Count : ((Newtonsoft.Json.Linq.JObject)dynJson2.items).Count;
                            //check if we have done with that category
                            if (dynJson2 == null || icount < 1 || icount < limit)
                            {
                                if (pt.MaxPrice > 0)
                                {
                                    var cat = _categoryService.GetCategoryById(rcategory);
                                    if (pt.MaxPrice > 50000)
                                    {
                                        cat.PriceRanges = "0-9999;10000-24999;25000-49999;50000-";
                                    }
                                    else if (pt.MaxPrice > 20000)
                                    {
                                        cat.PriceRanges = "0-4999;5000-9999;10000-24999;25000-";
                                    }
                                    else if (pt.MaxPrice > 5000)
                                    {
                                        cat.PriceRanges = "0-4999;5000-9999;10000-";
                                    }
                                    else if (pt.MaxPrice > 1000)
                                    {
                                        cat.PriceRanges = "0-999;1000-3999;4000-";
                                    }
                                    else
                                    {
                                        cat.PriceRanges = "0-249;250-499;500-749;750-";
                                    }
                                    _categoryService.UpdateCategory(cat);
                                }
                                //remove category that we have finished
                                categories.Remove(rcategory);
                                // store offset and everything for the next run (I was re-runing it by an ajax calls)
                                pt.Offset = 0;
                                pt.MaxPrice = 0;
                                pt.TotalProducts = 0;
                                pt.ProcessedProducts = 0;
                                _settingService.SetSetting<string>("DDProdcts", Newtonsoft.Json.JsonConvert.SerializeObject(pt, Newtonsoft.Json.Formatting.None,
                                new Newtonsoft.Json.JsonSerializerSettings()
                                {
                                    ReferenceLoopHandling = Newtonsoft.Json.ReferenceLoopHandling.Ignore
                                }), storeScope, true);
                                progress = string.Format("Categories: {0}, Products: {1}/{2} 'tbc", ccc.Name + "/" + categories.Count, (string)dynJson2.count, prodProcessed);
                            }
                            else // seems there are more products so we will continue on the same category
                            {
                                _settingService.SetSetting<string>("DDProdcts", Newtonsoft.Json.JsonConvert.SerializeObject(pt, Newtonsoft.Json.Formatting.None,
                                new Newtonsoft.Json.JsonSerializerSettings()
                                {
                                    ReferenceLoopHandling = Newtonsoft.Json.ReferenceLoopHandling.Ignore
                                }), storeScope, true);
                                progress = string.Format("Categories: {0}, Products: {1}/{2} 'tbc", ccc.Name + "/" + categories.Count, (string)dynJson2.count, prodProcessed);
                            }
                        }
                        catch (System.Net.WebException wex)
                        {
                            //LogException
                        }
                        catch (Exception ex)
                        {
                            //LogException
                        }
                    }

                    _cache.Set<string>("Progress", progress, TimeSpan.FromMinutes(30));
                    //seems we have more categories to work on
                    if (categories != null && categories.Count > 0)
                    {
                        _settingService.SetSetting<string>("DDCategories", Newtonsoft.Json.JsonConvert.SerializeObject(categories, Newtonsoft.Json.Formatting.None,
                        new Newtonsoft.Json.JsonSerializerSettings()
                        {
                            ReferenceLoopHandling = Newtonsoft.Json.ReferenceLoopHandling.Ignore
                        }), storeScope, true);
                    }
                    else //seems we have done with all categories
                    {
                        progress = "Done";
                        _settingService.SetSetting<string>("DDProdcts", string.Empty, storeScope, true);
                        _settingService.SetSetting<string>("DDCategories", string.Empty, storeScope, true);
                    }
                }

            }
            catch(Exception ex)
            {
                //LogException
            }

            return Json(new { data = progress });
        }

and the add pictures method

        private void AddPictures(dynamic product, Core.Domain.Catalog.Product pro)
        {
            List<Tuple<string, byte[]>> bdata = new List<Tuple<string, byte[]>>();
            var iurls = ((Newtonsoft.Json.Linq.JArray)product.imageFiles).Select(s => (string)s).ToList();

            System.Threading.Tasks.Parallel.ForEach(iurls, iu =>
            {
                try
                {
                    
                    if (!bdata.Any(a => a.Item1 == iu))
                    {
                        System.Net.WebClient webClient = new System.Net.WebClient();
                        bdata.Add(new Tuple<string, byte[]>(iu, webClient.DownloadData(iu)));
                    }
                }
                catch (Exception ex)
                {
                    //LogException
                }
            }
            );
            
            foreach (var iu in iurls)
            {
                try
                {
                    var data = bdata.SingleOrDefault(s => s.Item1 == iu);

                    if (data != null)
                    {
                        string iufn = _fileProvider.GetFileName(iu);
                        string iufnl = iufn.ToLowerInvariant();
                        {
                            {
                                {
                                    
                                    _pictureService.StoreInDb = false;
                                    Core.Domain.Media.Picture propic = _pictureService.InsertPicture(data.Item2, "image/jpeg", iufn, pro.Name, pro.Name, true);

                                    if (propic != null)
                                    {
                                        pro.ProductPictures.Add(new Core.Domain.Catalog.ProductPicture()
                                        {
                                            Picture = propic
                                        });
                                    }
                                }

                            }
                        }
                    }
                }
                catch (Exception ex)
                {
                    //LogException
                }
            }
            bdata.Clear();
        }


This is not really 100% optimized, especially the DB adding/updating part because of using _services instead of _repositories

but seem I had problems using _repos everywhere and not sure why.


Thank you and see you soon 


1vqHSTrq1GEoEF7QsL8dhmJfRMDVxhv2y