Create stored procedure from NopCommerce plugin

Question

I am using nopCommerce 3.50. I have implementing plugin in nopCommerce. I have create stored procedure in SQL server. Now I want to create stored procedure from plugin by code. When plugin is installed, it will create stored procedure.

How can I create stored procedure from plugin in nopCommerce?

Answer 1

This will work.

public class SearchEngineData : ISearchEngineData
{
  private readonly IDbContext _dbContext;        
  public SearchEngineData(IDbContext dbContext){
     _dbContext = dbContext;
  }
  private List<ElasticStoreMapping> GetStoreMappingsForProducts(int[] productIds)
        {
            var pProductIds = _dataProvider.GetParameter();
            pProductIds.ParameterName = "ProductIds";
            pProductIds.Value = productIds == null ? string.Empty : string.Join(",", productIds);
            pProductIds.DbType = DbType.String;

            return _dbContext.SqlQuery<ElasticStoreMapping>($"Exec GetStoreMappingForElastic @ProductIds", pProductIds).ToList();
        }
}

Answer 2

Refer to the stock plugin Nop.Plugin.Shipping.ByWeight

Your ObjectContext will have an Install() method, use that to create your stored procedure. Remove it in the Uninstall method.

    public string CreateDatabaseScript()
    {
        return ((IObjectContextAdapter)this).ObjectContext.CreateDatabaseScript();
    }

    /// <summary>
    /// Install
    /// </summary>
    public void Install()
    {
        //create the table
        var dbScript = CreateDatabaseScript();
        Database.ExecuteSqlCommand(dbScript);
        SaveChanges();
    }

    /// <summary>
    /// Uninstall
    /// </summary>
    public void Uninstall()
    {
        //drop the table
        var tableName = this.GetTableName<ShippingByWeightRecord>();
        //var tableName = "ShippingByWeight";
        this.DropPluginTable(tableName);
    }

Answer 3

Refer to Nop.Service.CAtalog.ProductService

var products = _dbContext.ExecuteStoredProcedureList<Product>(
                    "ProductLoadAllPaged",
                    pCategoryIds,
                    pManufacturerId,
                    pStoreId,
                    pVendorId,
                    pWarehouseId,
                    pParentGroupedProductId,
                    pProductTypeId,
                    pVisibleIndividuallyOnly,
                    pProductTagId,
                    pFeaturedProducts,
                    pPriceMin,
                    pPriceMax,
                    pKeywords,
                    pSearchDescriptions,
                    pSearchSku,
                    pSearchProductTags,
                    pUseFullTextSearch,
                    pFullTextMode,
                    pFilteredSpecs,
                    pLanguageId,
                    pOrderBy,
                    pAllowedCustomerRoleIds,
                    pPageIndex,
                    pPageSize,
                    pShowHidden,
                    pLoadFilterableSpecificationAttributeOptionIds,
                    pFilterableSpecificationAttributeOptionIds,
                    pTotalRecords);

It calls the productloadallpaged stored procedure.