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];                            

- 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))
                                //check if table-valued type is already exists
                                using (System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand(
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;

//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 :)


NopCommerce customization - Message Token

Hello friends,

this time I will show how to add your own message tokens to NopCommerce. For that you need two things: create a message token class and register it with dependencyregistrar.

So here is the code:

    public class FeederOT : IDependencyRegistrar
        public int Order => 1000;

        public void Register(ContainerBuilder builder, ITypeFinder typeFinder, NopConfig config)

    public class OrderMessageToken: Nop.Services.Messages.MessageTokenProvider
        #region Fields

        private readonly CatalogSettings _catalogSettings;
        private readonly CurrencySettings _currencySettings;
        private readonly IActionContextAccessor _actionContextAccessor;
        private readonly IAddressAttributeFormatter _addressAttributeFormatter;
        private readonly ICurrencyService _currencyService;
        private readonly ICustomerAttributeFormatter _customerAttributeFormatter;
        private readonly ICustomerService _customerService;
        private readonly IDateTimeHelper _dateTimeHelper;
        private readonly IDownloadService _downloadService;
        private readonly IEventPublisher _eventPublisher;
        private readonly IGenericAttributeService _genericAttributeService;
        private readonly ILanguageService _languageService;
        private readonly ILocalizationService _localizationService;
        private readonly IOrderService _orderService;
        private readonly IPaymentService _paymentService;
        private readonly IPriceFormatter _priceFormatter;
        private readonly IStoreContext _storeContext;
        private readonly IStoreService _storeService;
        private readonly IUrlHelperFactory _urlHelperFactory;
        private readonly IUrlRecordService _urlRecordService;
        private readonly IVendorAttributeFormatter _vendorAttributeFormatter;
        private readonly IWorkContext _workContext;
        private readonly MessageTemplatesSettings _templatesSettings;
        private readonly PaymentSettings _paymentSettings;
        private readonly StoreInformationSettings _storeInformationSettings;
        private readonly TaxSettings _taxSettings;

        private Dictionary<string, IEnumerable<string>> _allowedTokens;


        #region Ctor

        public OrderMessageToken(CatalogSettings catalogSettings,
            CurrencySettings currencySettings,
            IActionContextAccessor actionContextAccessor,
            IAddressAttributeFormatter addressAttributeFormatter,
            ICurrencyService currencyService,
            ICustomerAttributeFormatter customerAttributeFormatter,
            ICustomerService customerService,
            IDateTimeHelper dateTimeHelper,
            IDownloadService downloadService,
            IEventPublisher eventPublisher,
            IGenericAttributeService genericAttributeService,
            ILanguageService languageService,
            ILocalizationService localizationService,
            IOrderService orderService,
            IPaymentService paymentService,
            IPriceFormatter priceFormatter,
            IStoreContext storeContext,
            IStoreService storeService,
            IUrlHelperFactory urlHelperFactory,
            IUrlRecordService urlRecordService,
            IVendorAttributeFormatter vendorAttributeFormatter,
            IWorkContext workContext,
            MessageTemplatesSettings templatesSettings,
            PaymentSettings paymentSettings,
            StoreInformationSettings storeInformationSettings,
            TaxSettings taxSettings):base(
            this._catalogSettings = catalogSettings;
            this._currencySettings = currencySettings;
            this._actionContextAccessor = actionContextAccessor;
            this._addressAttributeFormatter = addressAttributeFormatter;
            this._currencyService = currencyService;
            this._customerAttributeFormatter = customerAttributeFormatter;
            this._customerService = customerService;
            this._dateTimeHelper = dateTimeHelper;
            this._downloadService = downloadService;
            this._eventPublisher = eventPublisher;
            this._genericAttributeService = genericAttributeService;
            this._languageService = languageService;
            this._localizationService = localizationService;
            this._orderService = orderService;
            this._paymentService = paymentService;
            this._priceFormatter = priceFormatter;
            this._storeContext = storeContext;
            this._storeService = storeService;
            this._urlHelperFactory = urlHelperFactory;
            this._urlRecordService = urlRecordService;
            this._vendorAttributeFormatter = vendorAttributeFormatter;
            this._workContext = workContext;
            this._templatesSettings = templatesSettings;
            this._paymentSettings = paymentSettings;
            this._storeInformationSettings = storeInformationSettings;
            this._taxSettings = taxSettings;


        public override void AddOrderTokens(IList<Token> tokens, Order order, int languageId, int vendorId = 0)
            tokens.Add(new Token("Order.OrderGuid", order.OrderGuid, true));
            base.AddOrderTokens(tokens, order, languageId, vendorId);

So, you create your own class, inherit it from the Nop.Services.Messages.MessageTokenProvider and define your own token inside of the AddOrderTokens method.

After that you register it with the IDependencyRegistrar.

Now you can use your own tokens inside of message templates.

See you there: NopCommerce customization - Price Calculation Service


NopCommerce customization - Plugin

Hello friends,

I'm going to share my experience in NopCommerce customization. I will write about Plugins, Scheduled tasks, Events, Services, and everything you need to know to extend your NopCommerce shop.

This first post will be about NopCommerce plugin for NopCommerce 4.1.

So, to start writing your own plugin for NopCommerce you need to start from the help page of the official documentation

Steps described there are required to start writing your plugin. After that you will just extend it to meet your requirements and here are some useful things:

- Main Plugin class declaration:

public class Feeder : BasePlugin, IMiscPlugin, IAdminMenuPlugin

- Declare all classes you need for work:

public Feeder(IActionContextAccessor actionContextAccessor,
            IDiscountService discountService,
            ILocalizationService localizationService,
            ISettingService settingService,
            IUrlHelperFactory urlHelperFactory,
            IWebHelper webHelper,
            IScheduleTaskService scheduleTaskService)
            this._actionContextAccessor = actionContextAccessor;
            this._discountService = discountService;
            this._localizationService = localizationService;
            this._settingService = settingService;
            this._urlHelperFactory = urlHelperFactory;
            this._webHelper = webHelper;

            this._scheduleTaskService = scheduleTaskService;

- Declare base methods:

        public override string GetConfigurationPageUrl()
            return $"{_webHelper.GetStoreLocation()}Admin/ProductFeederMPlug/Configure";

        public string GetConfigurationUrl(int discountId, int? discountRequirementId)
            return $"{_webHelper.GetStoreLocation()}Admin/ProductFeederMPlug/Configure";

- Adding menu item to the admin menu:

        public void ManageSiteMap(SiteMapNode rootNode)
            var menuItem = new SiteMapNode()
                SystemName = "Product Feeder MPlug",
                Title = "Product Feeder MPlug",
ControllerName = "ProductFeederMPlug",
ActionName = "Setup", Visible = true, IconClass = "fa fa-dot-circle-o", RouteValues = new RouteValueDictionary() { { "area", Web.Framework.AreaNames.Admin } }, }; var pluginNode = rootNode.ChildNodes.FirstOrDefault(x => x.SystemName == "Configuration"); if (pluginNode != null) pluginNode.ChildNodes.Add(menuItem); else rootNode.ChildNodes.Add(menuItem); }

-Installation method:

        public override void Install()
            var task = _scheduleTaskService.GetTaskByType(Services.UpdateStoreTask.TypeName);
            if (task == null)
                _scheduleTaskService.InsertTask(new Core.Domain.Tasks.ScheduleTask()
                    Type = Services.UpdateStoreTask.TypeName,
                    Enabled = true,
                    Name = "MPlug Product Synchronizer",
                    Seconds = 60 * 10,
                    StopOnError = false
                task.Enabled = true;
                task.Seconds = 60 * 10;
                task.StopOnError = false;

            task = _scheduleTaskService.GetTaskByType(Services.UpdateOrderStateTask.TypeName);
            if (task == null)
                _scheduleTaskService.InsertTask(new Core.Domain.Tasks.ScheduleTask()
                    Type = Services.UpdateOrderStateTask.TypeName,
                    Enabled = true,
                    Name = "MPlug Order State Tracker",
Seconds = 60 * 10, StopOnError = false }); } else { task.Enabled = true; task.Seconds = 60 * 15; task.StopOnError = false; _scheduleTaskService.UpdateTask(task); } base.Install(); }

- Uninstalling:

        public override void Uninstall()
            //do whatever you need to uninstall your plugin

- Adding your own controller - Inherit your plugin controller from the BasePluginController class:

public class ProductFeederMPlugController: BasePluginController

- Declaring views:

        public IActionResult Configure()
            if (!_permissionService.Authorize(StandardPermissionProvider.AccessAdminPanel))
                return AccessDeniedView();

            return View("~/Plugins/ProductFeeder.MPlug/Views/Configure.cshtml");

        public IActionResult Setup()
            if (!_permissionService.Authorize(StandardPermissionProvider.AccessAdminPanel))
                return AccessDeniedView();

            return View("~/Plugins/ProductFeeder.MPlug/Views/Setup.cshtml");

- Store scope detecting:

var storeScope = _storeContext.ActiveStoreScopeConfiguration;

- Store temporary information during session:

_cache.Set<string>("Progress", progress, TimeSpan.FromMinutes(30));

- Declare all instances of classes you need as constructor parameters and store them into fields:

public ProductFeederController(ICustomerService customerService, 
            Nop.Services.Shipping.Date.IDateRangeService dateRangeService,
            ILocalizationService localizationService,
            IPermissionService permissionService,
            ISettingService settingService,
            IStoreContext storeContext,
            Nop.Services.Catalog.ICategoryService categoryService,
            Nop.Services.Catalog.IManufacturerService manufacturerService,
            Nop.Services.Catalog.IProductService productService,
            Nop.Services.Catalog.IProductAttributeService productAttributeService,
            Nop.Services.Catalog.ISpecificationAttributeService specificationAttributeService,
            IGenericAttributeService genericAttributeService,
            Core.Infrastructure.INopFileProvider fileProvider,
            Nop.Services.Media.IPictureService pictureService,
            Nop.Services.Stores.IStoreMappingService storeMappingService,
            Nop.Services.Catalog.IProductTagService productTagService,
            Nop.Services.Seo.IUrlRecordService urlRecordService,
            Microsoft.Extensions.Caching.Memory.IMemoryCache cache)
            this._dateRangeService = dateRangeService;
            this._customerService = customerService;
            this._localizationService = localizationService;
            this._permissionService = permissionService;
            this._settingService = settingService;
            this._storeContext = storeContext;
            this._categoryService = categoryService;
            this._manufacturerService = manufacturerService;
            this._productService = productService;
            this._productAttributeService = productAttributeService;
            this._specificationAttributeService = specificationAttributeService;
            this._genericAttributeService = genericAttributeService;
            this._fileProvider = fileProvider;
            this._pictureService = pictureService;
            this._storeMappingService = storeMappingService;
            this._productTagService = productTagService;
            this._urlRecordService = urlRecordService;
            this._cache = cache;

- Save needed data into settings (DB table):

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

- To avoid complexity of existing model extending - use generic attributes:

_genericAttributeService.SaveAttribute<int>(manuf, Feeder.gAttrID, manuid);

- Add store mapping for entities (in this case for Manufacturer):

_storeMappingService.InsertStoreMapping<Core.Domain.Catalog.Manufacturer>(manuf, storeScope);

- Use repositories to speed-up it work with DB and if you do not need all other logic and events to be occured (use with caution and be aware of it):

var productRepository = Core.Infrastructure.EngineContext.Current.Resolve<Core.Data.IRepository<Core.Domain.Catalog.Product>>();

- Adding a manufacturer:

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
_storeMappingService.InsertStoreMapping<Core.Domain.Catalog.Manufacturer>(manuf, storeScope);

- Adding date ranges / delivery dates:

daterange = new Core.Domain.Shipping.DeliveryDate()
      Name = deliveryText

- Adding product:

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



- Add pictures/images as files:

string iufn = _fileProvider.GetFileName(iurl);
if (!pro.ProductPictures.Any(a => a.Picture.SeoFilename.ToLowerInvariant() == iufn.ToLowerInvariant()))
      Core.Domain.Media.Picture propic;
      _pictureService.StoreInDb = false;
      propic = _pictureService.InsertPicture(data.Item2, "image/jpeg", iufn, pro.Name, pro.Name, true);

      _productService.InsertProductPicture(new Core.Domain.Catalog.ProductPicture()
            Product = pro,
            Picture = propic

- Adding Product Attribute:

pattr = new Core.Domain.Catalog.ProductAttribute()
     Name = name



pro.ProductAttributeMappings.Add(new Core.Domain.Catalog.ProductAttributeMapping()
     AttributeControlType = Core.Domain.Catalog.AttributeControlType.ColorSquares,
     IsRequired = true,
     ProductAttribute = pattr,
     ProductAttributeValues =
          new Core.Domain.Catalog.ProductAttributeValue
                AttributeValueType = Core.Domain.Catalog.AttributeValueType.Simple,
                Name = val, 
                IsPreSelected = true, 

- Adding Product Specification Attribute:

spattr = new Core.Domain.Catalog.SpecificationAttribute
       Name = name
spao = new Core.Domain.Catalog.SpecificationAttributeOption()
       Name = val,
       SpecificationAttribute = spattr

pro.ProductSpecificationAttributes.Add(new Core.Domain.Catalog.ProductSpecificationAttribute()
       AllowFiltering = true,
       ShowOnProductPage = true,
       SpecificationAttributeOption = spao,
       AttributeType = Core.Domain.Catalog.SpecificationAttributeType.Option

- do not forget to update Entity (Product in this case) after all changes:


- Adding Urls/Slugs to entities:

if (string.IsNullOrEmpty(_urlRecordService.GetActiveSlug(p.Id, typeof(Core.Domain.Catalog.Product).Name, 0)))
     slug = _urlRecordService.ValidateSeName(_productService.GetProductById(p.Id), p.Name, p.Name, true);
     _urlRecordService.InsertUrlRecord(new Core.Domain.Seo.UrlRecord
           EntityId = p.Id,
           EntityName = typeof(Core.Domain.Catalog.Product).Name,
           LanguageId = 0,
           IsActive = true,
           Slug = slug

Next post about adding message tokens...