32 messaggi dal 17 febbraio 2015
Ciao a tutti!

Sto cercando di capire come sia possibile da una pagina Razor (core 2.1 - NO MVC! ) di eseguire un file .SQL contenente delle Stored procedure.

Sapete aiutarmi?
11.097 messaggi dal 09 febbraio 2002
Contributi
Ciao,
puoi far vedere un esempio del contenuto del file?
Si tratta di invocazioni a stored procedure (EXEC) oppure dei comandi per crearle (CREATE PROCEDURE)?


Sto cercando di capire come sia possibile da una pagina Razor (core 2.1 - NO MVC! )

Il procedimento è lo stesso sia in Razor Pages che in MVC in quanto questa logica di accesso ai dati andrebbe situata in una classe separata che ha lo scopo di leggere il file, creare una SqlConnection ed eseguire uno o più SqlCommand, in base al contenuto del file. Poi, puoi ricevere un'istanza di questa classe dalla Razor Page o dal Controller MVC sfruttando la dependency injection di ASP.NET Core.
Comunque, un passo alla volta. Per prima cosa vediamo il contenuto del file.

Nel frattempo, se vuoi, lascia un post qui. Sto raccogliendo le opinioni di chi preferisce Razor Pages a MVC.
http://forum.aspitalia.com/forum/post/420918/WebForms-Razor-Pages.aspx

ciao,
Moreno

Enjoy learning and just keep making
32 messaggi dal 17 febbraio 2015
Ciao Moreno,

il modello del file .sql che vorrei eseguire è questo:

CREATE FUNCTION [dbo].[giga_splitstring_to_table]
(
    @string NVARCHAR(MAX),
    @delimiter CHAR(1)
)
RETURNS @output TABLE(
    data NVARCHAR(MAX)
)
BEGIN
    DECLARE @start INT, @end INT
    SELECT @start = 1, @end = CHARINDEX(@delimiter, @string)

    WHILE @start < LEN(@string) + 1 BEGIN
        IF @end = 0 
            SET @end = LEN(@string) + 1

        INSERT INTO @output (data) 
        VALUES(SUBSTRING(@string, @start, @end - @start))
        SET @start = @end + 1
        SET @end = CHARINDEX(@delimiter, @string, @start)
    END
    RETURN
END
GO



CREATE FUNCTION [dbo].[giga_getnotnullnotempty]
(
    @p1 nvarchar(max) = null, 
    @p2 nvarchar(max) = null
)
RETURNS nvarchar(max)
AS
BEGIN
    IF @p1 IS NULL
        return @p2
    IF @p1 =''
        return @p2

    return @p1
END
GO



CREATE FUNCTION [dbo].[giga_getprimarykey_indexname]
(
    @table_name nvarchar(1000) = null
)
RETURNS nvarchar(1000)
AS
BEGIN
  DECLARE @index_name nvarchar(1000)

    SELECT @index_name = i.name
  FROM sys.tables AS tbl
  INNER JOIN sys.indexes AS i ON (i.index_id > 0 and i.is_hypothetical = 0) AND (i.object_id=tbl.object_id)
  WHERE (i.is_unique=1 and i.is_disabled=0) and (tbl.name=@table_name)

    RETURN @index_name
END
GO


CREATE FUNCTION [dbo].[giga_padright]
(
    @source INT, 
    @symbol NVARCHAR(MAX), 
    @length INT
)
RETURNS NVARCHAR(MAX)
AS
BEGIN
    RETURN RIGHT(REPLICATE(@symbol, @length)+ RTRIM(CAST(@source AS NVARCHAR(MAX))), @length)
END
GO


CREATE PROCEDURE [dbo].[ProductLoadAllPaged]
(
  @CategoryIds    nvarchar(MAX) = null,  --a list of category IDs (comma-separated list). e.g. 1,2,3
  @ManufacturerId    int = 0,
  @StoreId      int = 0,
  @VendorId      int = 0,
  @WarehouseId    int = 0,
  @ProductTypeId    int = null, --product type identifier, null - load all products
  @VisibleIndividuallyOnly bit = 0,   --0 - load all products , 1 - "visible indivially" only
  @MarkedAsNewOnly  bit = 0,   --0 - load all products , 1 - "marked as new" only
  @ProductTagId    int = 0,
  @FeaturedProducts  bit = null,  --0 featured only , 1 not featured only, null - load all products
  @PriceMin      decimal(18, 4) = null,
  @PriceMax      decimal(18, 4) = null,
  @Keywords      nvarchar(4000) = null,
  @SearchDescriptions bit = 0, --a value indicating whether to search by a specified "keyword" in product descriptions
  @SearchManufacturerPartNumber bit = 0, -- a value indicating whether to search by a specified "keyword" in manufacturer part number
  @SearchSku      bit = 0, --a value indicating whether to search by a specified "keyword" in product SKU
  @SearchProductTags  bit = 0, --a value indicating whether to search by a specified "keyword" in product tags
  @UseFullTextSearch  bit = 0,
  @FullTextMode    int = 0, --0 - using CONTAINS with <prefix_term>, 5 - using CONTAINS and OR with <prefix_term>, 10 - using CONTAINS and AND with <prefix_term>
  @FilteredSpecs    nvarchar(MAX) = null,  --filter by specification attribute options (comma-separated list of IDs). e.g. 14,15,16
  @LanguageId      int = 0,
  @OrderBy      int = 0, --0 - position, 5 - Name: A to Z, 6 - Name: Z to A, 10 - Price: Low to High, 11 - Price: High to Low, 15 - creation date
  @AllowedCustomerRoleIds  nvarchar(MAX) = null,  --a list of customer role IDs (comma-separated list) for which a product should be shown (if a subjet to ACL)
  @PageIndex      int = 0, 
  @PageSize      int = 2147483644,
  @ShowHidden      bit = 0,
  @OverridePublished  bit = null, --null - process "Published" property according to "showHidden" parameter, true - load only "Published" products, false - load only "Unpublished" products
  @LoadFilterableSpecificationAttributeOptionIds bit = 0, --a value indicating whether we should load the specification attribute option identifiers applied to loaded products (all pages)
  @FilterableSpecificationAttributeOptionIds nvarchar(MAX) = null OUTPUT, --the specification attribute option identifiers applied to loaded products (all pages). returned as a comma separated list of identifiers
  @TotalRecords    int = null OUTPUT
)
AS
BEGIN
  
  /* Products that filtered by keywords */
  CREATE TABLE #KeywordProducts
  (
    [ProductId] int NOT NULL
  )

  DECLARE
    @SearchKeywords bit,
    @OriginalKeywords nvarchar(4000),
    @sql nvarchar(max),
    @sql_orderby nvarchar(max)

  SET NOCOUNT ON
  
  --filter by keywords
  SET @Keywords = isnull(@Keywords, '')
  SET @Keywords = rtrim(ltrim(@Keywords))
  SET @OriginalKeywords = @Keywords
  IF ISNULL(@Keywords, '') != ''
  BEGIN
    SET @SearchKeywords = 1
    
    IF @UseFullTextSearch = 1
    BEGIN
      --remove wrong chars (' ")
      SET @Keywords = REPLACE(@Keywords, '''', '')
      SET @Keywords = REPLACE(@Keywords, '"', '')
      
      --full-text search
      IF @FullTextMode = 0 
      BEGIN
        --0 - using CONTAINS with <prefix_term>
        SET @Keywords = ' "' + @Keywords + '*" '
      END
      ELSE
      BEGIN
        --5 - using CONTAINS and OR with <prefix_term>
        --10 - using CONTAINS and AND with <prefix_term>

        --clean multiple spaces
        WHILE CHARINDEX('  ', @Keywords) > 0 
          SET @Keywords = REPLACE(@Keywords, '  ', ' ')

        DECLARE @concat_term nvarchar(100)        
        IF @FullTextMode = 5 --5 - using CONTAINS and OR with <prefix_term>
        BEGIN
          SET @concat_term = 'OR'
        END 
        IF @FullTextMode = 10 --10 - using CONTAINS and AND with <prefix_term>
        BEGIN
          SET @concat_term = 'AND'
        END

        --now let's build search string
        declare @fulltext_keywords nvarchar(4000)
        set @fulltext_keywords = N''
        declare @index int    
    
        set @index = CHARINDEX(' ', @Keywords, 0)

        -- if index = 0, then only one field was passed
        IF(@index = 0)
          set @fulltext_keywords = ' "' + @Keywords + '*" '
        ELSE
        BEGIN    
          DECLARE @first BIT
          SET  @first = 1      
          WHILE @index > 0
          BEGIN
            IF (@first = 0)
              SET @fulltext_keywords = @fulltext_keywords + ' ' + @concat_term + ' '
            ELSE
              SET @first = 0

            SET @fulltext_keywords = @fulltext_keywords + '"' + SUBSTRING(@Keywords, 1, @index - 1) + '*"'          
            SET @Keywords = SUBSTRING(@Keywords, @index + 1, LEN(@Keywords) - @index)            
            SET @index = CHARINDEX(' ', @Keywords, 0)
          end
          
          -- add the last field
          IF LEN(@fulltext_keywords) > 0
            SET @fulltext_keywords = @fulltext_keywords + ' ' + @concat_term + ' ' + '"' + SUBSTRING(@Keywords, 1, LEN(@Keywords)) + '*"'  
        END
        SET @Keywords = @fulltext_keywords
      END
    END
    ELSE
    BEGIN
      --usual search by PATINDEX
      SET @Keywords = '%' + @Keywords + '%'
    END
    --PRINT @Keywords

    --product name
    SET @sql = '
    INSERT INTO #KeywordProducts ([ProductId])
    SELECT p.Id
    FROM Product p with (NOLOCK)
    WHERE '
    IF @UseFullTextSearch = 1
      SET @sql = @sql + 'CONTAINS(p.[Name], @Keywords) '
    ELSE
      SET @sql = @sql + 'PATINDEX(@Keywords, p.[Name]) > 0 '


    --localized product name
    SET @sql = @sql + '
    UNION
    SELECT lp.EntityId
    FROM LocalizedProperty lp with (NOLOCK)
    WHERE
      lp.LocaleKeyGroup = N''Product''
      AND lp.LanguageId = ' + ISNULL(CAST(@LanguageId AS nvarchar(max)), '0') + '
      AND lp.LocaleKey = N''Name'''
    IF @UseFullTextSearch = 1
      SET @sql = @sql + ' AND CONTAINS(lp.[LocaleValue], @Keywords) '
    ELSE
      SET @sql = @sql + ' AND PATINDEX(@Keywords, lp.[LocaleValue]) > 0 '
  

    IF @SearchDescriptions = 1
    BEGIN
      --product short description
      SET @sql = @sql + '
      UNION
      SELECT p.Id
      FROM Product p with (NOLOCK)
      WHERE '
      IF @UseFullTextSearch = 1
        SET @sql = @sql + 'CONTAINS(p.[ShortDescription], @Keywords) '
      ELSE
        SET @sql = @sql + 'PATINDEX(@Keywords, p.[ShortDescription]) > 0 '


      --product full description
      SET @sql = @sql + '
      UNION
      SELECT p.Id
      FROM Product p with (NOLOCK)
      WHERE '
      IF @UseFullTextSearch = 1
        SET @sql = @sql + 'CONTAINS(p.[FullDescription], @Keywords) '
      ELSE
        SET @sql = @sql + 'PATINDEX(@Keywords, p.[FullDescription]) > 0 '



      --localized product short description
      SET @sql = @sql + '
      UNION
      SELECT lp.EntityId
      FROM LocalizedProperty lp with (NOLOCK)
      WHERE
        lp.LocaleKeyGroup = N''Product''
        AND lp.LanguageId = ' + ISNULL(CAST(@LanguageId AS nvarchar(max)), '0') + '
        AND lp.LocaleKey = N''ShortDescription'''
      IF @UseFullTextSearch = 1
        SET @sql = @sql + ' AND CONTAINS(lp.[LocaleValue], @Keywords) '
      ELSE
        SET @sql = @sql + ' AND PATINDEX(@Keywords, lp.[LocaleValue]) > 0 '
        

      --localized product full description
      SET @sql = @sql + '
      UNION
      SELECT lp.EntityId
      FROM LocalizedProperty lp with (NOLOCK)
      WHERE
        lp.LocaleKeyGroup = N''Product''
        AND lp.LanguageId = ' + ISNULL(CAST(@LanguageId AS nvarchar(max)), '0') + '
        AND lp.LocaleKey = N''FullDescription'''
      IF @UseFullTextSearch = 1
        SET @sql = @sql + ' AND CONTAINS(lp.[LocaleValue], @Keywords) '
      ELSE
        SET @sql = @sql + ' AND PATINDEX(@Keywords, lp.[LocaleValue]) > 0 '
    END

    --manufacturer part number (exact match)
    IF @SearchManufacturerPartNumber = 1
    BEGIN
      SET @sql = @sql + '
      UNION
      SELECT p.Id
      FROM Product p with (NOLOCK)
      WHERE p.[ManufacturerPartNumber] = @OriginalKeywords '
    END

    --SKU (exact match)
    IF @SearchSku = 1
    BEGIN
      SET @sql = @sql + '
      UNION
      SELECT p.Id
      FROM Product p with (NOLOCK)
      WHERE p.[Sku] = @OriginalKeywords '
    END

    IF @SearchProductTags = 1
    BEGIN
      --product tags (exact match)
      SET @sql = @sql + '
      UNION
      SELECT pptm.Product_Id
      FROM Product_ProductTag_Mapping pptm with(NOLOCK) INNER JOIN ProductTag pt with(NOLOCK) ON pt.Id = pptm.ProductTag_Id
      WHERE pt.[Name] = @OriginalKeywords '

      --localized product tags
      SET @sql = @sql + '
      UNION
      SELECT pptm.Product_Id
      FROM LocalizedProperty lp with (NOLOCK) INNER JOIN Product_ProductTag_Mapping pptm with(NOLOCK) ON lp.EntityId = pptm.ProductTag_Id
      WHERE
        lp.LocaleKeyGroup = N''ProductTag''
        AND lp.LanguageId = ' + ISNULL(CAST(@LanguageId AS nvarchar(max)), '0') + '
        AND lp.LocaleKey = N''Name''
        AND lp.[LocaleValue] = @OriginalKeywords '
    END

    --PRINT (@sql)
    EXEC sp_executesql @sql, N'@Keywords nvarchar(4000), @OriginalKeywords nvarchar(4000)', @Keywords, @OriginalKeywords

  END
  ELSE
  BEGIN
    SET @SearchKeywords = 0
  END

  --filter by category IDs
  SET @CategoryIds = isnull(@CategoryIds, '')  
  CREATE TABLE #FilteredCategoryIds
  (
    CategoryId int not null
  )
  INSERT INTO #FilteredCategoryIds (CategoryId)
  SELECT CAST(data as int) FROM [giga_splitstring_to_table](@CategoryIds, ',')  
  DECLARE @CategoryIdsCount int  
  SET @CategoryIdsCount = (SELECT COUNT(1) FROM #FilteredCategoryIds)

  --filter by customer role IDs (access control list)
  SET @AllowedCustomerRoleIds = isnull(@AllowedCustomerRoleIds, '')  
  CREATE TABLE #FilteredCustomerRoleIds
  (
    CustomerRoleId int not null
  )
  INSERT INTO #FilteredCustomerRoleIds (CustomerRoleId)
  SELECT CAST(data as int) FROM [giga_splitstring_to_table](@AllowedCustomerRoleIds, ',')
  DECLARE @FilteredCustomerRoleIdsCount int  
  SET @FilteredCustomerRoleIdsCount = (SELECT COUNT(1) FROM #FilteredCustomerRoleIds)
  
  --paging
  DECLARE @PageLowerBound int
  DECLARE @PageUpperBound int
  DECLARE @RowsToReturn int
  SET @RowsToReturn = @PageSize * (@PageIndex + 1)  
  SET @PageLowerBound = @PageSize * @PageIndex
  SET @PageUpperBound = @PageLowerBound + @PageSize + 1
  
  CREATE TABLE #DisplayOrderTmp 
  (
    [Id] int IDENTITY (1, 1) NOT NULL,
    [ProductId] int NOT NULL
  )

  SET @sql = '
  SELECT p.Id
  FROM
    Product p with (NOLOCK)'
  
  IF @CategoryIdsCount > 0
  BEGIN
    SET @sql = @sql + '
    INNER JOIN Product_Category_Mapping pcm with (NOLOCK)
      ON p.Id = pcm.ProductId'
  END
  
  IF @ManufacturerId > 0
  BEGIN
    SET @sql = @sql + '
    INNER JOIN Product_Manufacturer_Mapping pmm with (NOLOCK)
      ON p.Id = pmm.ProductId'
  END
  
  IF ISNULL(@ProductTagId, 0) != 0
  BEGIN
    SET @sql = @sql + '
    INNER JOIN Product_ProductTag_Mapping pptm with (NOLOCK)
      ON p.Id = pptm.Product_Id'
  END
  
  --searching by keywords
  IF @SearchKeywords = 1
  BEGIN
    SET @sql = @sql + '
    JOIN #KeywordProducts kp
      ON  p.Id = kp.ProductId'
  END
  
  SET @sql = @sql + '
  WHERE
    p.Deleted = 0'
  
  --filter by category
  IF @CategoryIdsCount > 0
  BEGIN
    SET @sql = @sql + '
    AND pcm.CategoryId IN ('
    
    SET @sql = @sql + + CAST(@CategoryIds AS nvarchar(max))

    SET @sql = @sql + ')'

    IF @FeaturedProducts IS NOT NULL
    BEGIN
      SET @sql = @sql + '
    AND pcm.IsFeaturedProduct = ' + CAST(@FeaturedProducts AS nvarchar(max))
    END
  END
  
  --filter by manufacturer
  IF @ManufacturerId > 0
  BEGIN
    SET @sql = @sql + '
    AND pmm.ManufacturerId = ' + CAST(@ManufacturerId AS nvarchar(max))
    
    IF @FeaturedProducts IS NOT NULL
    BEGIN
      SET @sql = @sql + '
    AND pmm.IsFeaturedProduct = ' + CAST(@FeaturedProducts AS nvarchar(max))
    END
  END
  
  --filter by vendor
  IF @VendorId > 0
  BEGIN
    SET @sql = @sql + '
    AND p.VendorId = ' + CAST(@VendorId AS nvarchar(max))
  END
  
  --filter by warehouse
  IF @WarehouseId > 0
  BEGIN
    --we should also ensure that 'ManageInventoryMethodId' is set to 'ManageStock' (1)
    --but we skip it in order to prevent hard-coded values (e.g. 1) and for better performance
    SET @sql = @sql + '
    AND  
      (
        (p.UseMultipleWarehouses = 0 AND
          p.WarehouseId = ' + CAST(@WarehouseId AS nvarchar(max)) + ')
        OR
        (p.UseMultipleWarehouses > 0 AND
          EXISTS (SELECT 1 FROM ProductWarehouseInventory [pwi]
          WHERE [pwi].WarehouseId = ' + CAST(@WarehouseId AS nvarchar(max)) + ' AND [pwi].ProductId = p.Id))
      )'
  END
  
  --filter by product type
  IF @ProductTypeId is not null
  BEGIN
    SET @sql = @sql + '
    AND p.ProductTypeId = ' + CAST(@ProductTypeId AS nvarchar(max))
  END
  
  --filter by "visible individually"
  IF @VisibleIndividuallyOnly = 1
  BEGIN
    SET @sql = @sql + '
    AND p.VisibleIndividually = 1'
  END
  
  --filter by "marked as new"
  IF @MarkedAsNewOnly = 1
  BEGIN
    SET @sql = @sql + '
    AND p.MarkAsNew = 1
    AND (getutcdate() BETWEEN ISNULL(p.MarkAsNewStartDateTimeUtc, ''1/1/1900'') and ISNULL(p.MarkAsNewEndDateTimeUtc, ''1/1/2999''))'
  END
  
  --filter by product tag
  IF ISNULL(@ProductTagId, 0) != 0
  BEGIN
    SET @sql = @sql + '
    AND pptm.ProductTag_Id = ' + CAST(@ProductTagId AS nvarchar(max))
  END
  
  --"Published" property
  IF (@OverridePublished is null)
  BEGIN
    --process according to "showHidden"
    IF @ShowHidden = 0
    BEGIN
      SET @sql = @sql + '
      AND p.Published = 1'
    END
  END
  ELSE IF (@OverridePublished = 1)
  BEGIN
    --published only
    SET @sql = @sql + '
    AND p.Published = 1'
  END
  ELSE IF (@OverridePublished = 0)
  BEGIN
    --unpublished only
    SET @sql = @sql + '
    AND p.Published = 0'
  END
  
  --show hidden
  IF @ShowHidden = 0
  BEGIN
    SET @sql = @sql + '
    AND p.Deleted = 0
    AND (getutcdate() BETWEEN ISNULL(p.AvailableStartDateTimeUtc, ''1/1/1900'') and ISNULL(p.AvailableEndDateTimeUtc, ''1/1/2999''))'
  END
  
  --min price
  IF @PriceMin is not null
  BEGIN
    SET @sql = @sql + '
    AND (p.Price >= ' + CAST(@PriceMin AS nvarchar(max)) + ')'
  END
  
  --max price
  IF @PriceMax is not null
  BEGIN
    SET @sql = @sql + '
    AND (p.Price <= ' + CAST(@PriceMax AS nvarchar(max)) + ')'
  END
  
  --show hidden and ACL
  IF  @ShowHidden = 0 and @FilteredCustomerRoleIdsCount > 0
  BEGIN
    SET @sql = @sql + '
    AND (p.SubjectToAcl = 0 OR EXISTS (
      SELECT 1 FROM #FilteredCustomerRoleIds [fcr]
      WHERE
        [fcr].CustomerRoleId IN (
          SELECT [acl].CustomerRoleId
          FROM [AclRecord] acl with (NOLOCK)
          WHERE [acl].EntityId = p.Id AND [acl].EntityName = ''Product''
        )
      ))'
  END
  
  --filter by store
  IF @StoreId > 0
  BEGIN
    SET @sql = @sql + '
    AND (p.LimitedToStores = 0 OR EXISTS (
      SELECT 1 FROM [StoreMapping] sm with (NOLOCK)
      WHERE [sm].EntityId = p.Id AND [sm].EntityName = ''Product'' and [sm].StoreId=' + CAST(@StoreId AS nvarchar(max)) + '
      ))'
  END
  
    --prepare filterable specification attribute option identifier (if requested)
    IF @LoadFilterableSpecificationAttributeOptionIds = 1
  BEGIN    
    CREATE TABLE #FilterableSpecs 
    (
      [SpecificationAttributeOptionId] int NOT NULL
    )
        DECLARE @sql_filterableSpecs nvarchar(max)
        SET @sql_filterableSpecs = '
          INSERT INTO #FilterableSpecs ([SpecificationAttributeOptionId])
          SELECT DISTINCT [psam].SpecificationAttributeOptionId
          FROM [Product_SpecificationAttribute_Mapping] [psam] WITH (NOLOCK)
              WHERE [psam].[AllowFiltering] = 1
              AND [psam].[ProductId] IN (' + @sql + ')'

        EXEC sp_executesql @sql_filterableSpecs

    --build comma separated list of filterable identifiers
    SELECT @FilterableSpecificationAttributeOptionIds = COALESCE(@FilterableSpecificationAttributeOptionIds + ',' , '') + CAST(SpecificationAttributeOptionId as nvarchar(4000))
    FROM #FilterableSpecs

    DROP TABLE #FilterableSpecs
   END

  --filter by specification attribution options
  SET @FilteredSpecs = isnull(@FilteredSpecs, '')  
  CREATE TABLE #FilteredSpecs
  (
    SpecificationAttributeOptionId int not null
  )
  INSERT INTO #FilteredSpecs (SpecificationAttributeOptionId)
  SELECT CAST(data as int) FROM [giga_splitstring_to_table](@FilteredSpecs, ',') 

    CREATE TABLE #FilteredSpecsWithAttributes
  (
        SpecificationAttributeId int not null,
    SpecificationAttributeOptionId int not null
  )
  INSERT INTO #FilteredSpecsWithAttributes (SpecificationAttributeId, SpecificationAttributeOptionId)
  SELECT sao.SpecificationAttributeId, fs.SpecificationAttributeOptionId
    FROM #FilteredSpecs fs INNER JOIN SpecificationAttributeOption sao ON sao.Id = fs.SpecificationAttributeOptionId
    ORDER BY sao.SpecificationAttributeId 

    DECLARE @SpecAttributesCount int  
  SET @SpecAttributesCount = (SELECT COUNT(1) FROM #FilteredSpecsWithAttributes)
  IF @SpecAttributesCount > 0
  BEGIN
    --do it for each specified specification option
    DECLARE @SpecificationAttributeOptionId int
        DECLARE @SpecificationAttributeId int
        DECLARE @LastSpecificationAttributeId int
        SET @LastSpecificationAttributeId = 0
    DECLARE cur_SpecificationAttributeOption CURSOR FOR
    SELECT SpecificationAttributeId, SpecificationAttributeOptionId
    FROM #FilteredSpecsWithAttributes

    OPEN cur_SpecificationAttributeOption
        FOREACH:
            FETCH NEXT FROM cur_SpecificationAttributeOption INTO @SpecificationAttributeId, @SpecificationAttributeOptionId
            IF (@LastSpecificationAttributeId <> 0 AND @SpecificationAttributeId <> @LastSpecificationAttributeId OR @@FETCH_STATUS <> 0) 
          SET @sql = @sql + '
        AND p.Id in (select psam.ProductId from [Product_SpecificationAttribute_Mapping] psam with (NOLOCK) where psam.AllowFiltering = 1 and psam.SpecificationAttributeOptionId IN (SELECT SpecificationAttributeOptionId FROM #FilteredSpecsWithAttributes WHERE SpecificationAttributeId = ' + CAST(@LastSpecificationAttributeId AS nvarchar(max)) + '))'
            SET @LastSpecificationAttributeId = @SpecificationAttributeId
    IF @@FETCH_STATUS = 0 GOTO FOREACH
    CLOSE cur_SpecificationAttributeOption
    DEALLOCATE cur_SpecificationAttributeOption
  END

  --sorting
  SET @sql_orderby = ''  
  IF @OrderBy = 5 /* Name: A to Z */
    SET @sql_orderby = ' p.[Name] ASC'
  ELSE IF @OrderBy = 6 /* Name: Z to A */
    SET @sql_orderby = ' p.[Name] DESC'
  ELSE IF @OrderBy = 10 /* Price: Low to High */
    SET @sql_orderby = ' p.[Price] ASC'
  ELSE IF @OrderBy = 11 /* Price: High to Low */
    SET @sql_orderby = ' p.[Price] DESC'
  ELSE IF @OrderBy = 15 /* creation date */
    SET @sql_orderby = ' p.[CreatedOnUtc] DESC'
  ELSE /* default sorting, 0 (position) */
  BEGIN
    --category position (display order)
    IF @CategoryIdsCount > 0 SET @sql_orderby = ' pcm.DisplayOrder ASC'
    
    --manufacturer position (display order)
    IF @ManufacturerId > 0
    BEGIN
      IF LEN(@sql_orderby) > 0 SET @sql_orderby = @sql_orderby + ', '
      SET @sql_orderby = @sql_orderby + ' pmm.DisplayOrder ASC'
    END
    
    --name
    IF LEN(@sql_orderby) > 0 SET @sql_orderby = @sql_orderby + ', '
    SET @sql_orderby = @sql_orderby + ' p.[Name] ASC'
  END
  
  SET @sql = @sql + '
  ORDER BY' + @sql_orderby
  
    SET @sql = '
    INSERT INTO #DisplayOrderTmp ([ProductId])' + @sql

  --PRINT (@sql)
  EXEC sp_executesql @sql

  DROP TABLE #FilteredCategoryIds
  DROP TABLE #FilteredSpecs
    DROP TABLE #FilteredSpecsWithAttributes
  DROP TABLE #FilteredCustomerRoleIds
  DROP TABLE #KeywordProducts

  CREATE TABLE #PageIndex 
  (
    [IndexId] int IDENTITY (1, 1) NOT NULL,
    [ProductId] int NOT NULL
  )
  INSERT INTO #PageIndex ([ProductId])
  SELECT ProductId
  FROM #DisplayOrderTmp
  GROUP BY ProductId
  ORDER BY min([Id])

  --total records
  SET @TotalRecords = @@rowcount
  
  DROP TABLE #DisplayOrderTmp

  --return products
  SELECT TOP (@RowsToReturn)
    p.*
  FROM
    #PageIndex [pi]
    INNER JOIN Product p with (NOLOCK) on p.Id = [pi].[ProductId]
  WHERE
    [pi].IndexId > @PageLowerBound AND 
    [pi].IndexId < @PageUpperBound
  ORDER BY
    [pi].IndexId
  
  DROP TABLE #PageIndex
END
GO


CREATE PROCEDURE [dbo].[ProductTagCountLoadAll]
(
  @StoreId int
)
AS
BEGIN
  SET NOCOUNT ON
  
  SELECT pt.Id as [ProductTagId], COUNT(p.Id) as [ProductCount]
  FROM ProductTag pt with (NOLOCK)
  LEFT JOIN Product_ProductTag_Mapping pptm with (NOLOCK) ON pt.[Id] = pptm.[ProductTag_Id]
  LEFT JOIN Product p with (NOLOCK) ON pptm.[Product_Id] = p.[Id]
  WHERE
    p.[Deleted] = 0
    AND p.Published = 1
    AND (@StoreId = 0 or (p.LimitedToStores = 0 OR EXISTS (
      SELECT 1 FROM [StoreMapping] sm with (NOLOCK)
      WHERE [sm].EntityId = p.Id AND [sm].EntityName = 'Product' and [sm].StoreId=@StoreId
      )))
  GROUP BY pt.Id
  ORDER BY pt.Id
END
GO

CREATE PROCEDURE [dbo].[FullText_IsSupported]
AS
BEGIN  
  EXEC('
  SELECT CASE SERVERPROPERTY(''IsFullTextInstalled'')
  WHEN 1 THEN 
    CASE DatabaseProperty (DB_NAME(DB_ID()), ''IsFulltextEnabled'')
    WHEN 1 THEN 1
    ELSE 0
    END
  ELSE 0
  END as Value')
END
GO



CREATE PROCEDURE [dbo].[FullText_Enable]
AS
BEGIN
  --create catalog
  EXEC('
  IF NOT EXISTS (SELECT 1 FROM sys.fulltext_catalogs WHERE [name] = ''gigaCommerceFullTextCatalog'')
    CREATE FULLTEXT CATALOG [gigaCommerceFullTextCatalog] AS DEFAULT')
  
  --create indexes
  DECLARE @create_index_text nvarchar(4000)
  SET @create_index_text = '
  IF NOT EXISTS (SELECT 1 FROM sys.fulltext_indexes WHERE object_id = object_id(''[Product]''))
    CREATE FULLTEXT INDEX ON [Product]([Name], [ShortDescription], [FullDescription])
    KEY INDEX [' + dbo.[giga_getprimarykey_indexname] ('Product') +  '] ON [gigaCommerceFullTextCatalog] WITH CHANGE_TRACKING AUTO'
  EXEC(@create_index_text)
  
  SET @create_index_text = '
  IF NOT EXISTS (SELECT 1 FROM sys.fulltext_indexes WHERE object_id = object_id(''[LocalizedProperty]''))
    CREATE FULLTEXT INDEX ON [LocalizedProperty]([LocaleValue])
    KEY INDEX [' + dbo.[giga_getprimarykey_indexname] ('LocalizedProperty') +  '] ON [gigaCommerceFullTextCatalog] WITH CHANGE_TRACKING AUTO'
  EXEC(@create_index_text)

  SET @create_index_text = '
  IF NOT EXISTS (SELECT 1 FROM sys.fulltext_indexes WHERE object_id = object_id(''[ProductTag]''))
    CREATE FULLTEXT INDEX ON [ProductTag]([Name])
    KEY INDEX [' + dbo.[giga_getprimarykey_indexname] ('ProductTag') +  '] ON [gigaCommerceFullTextCatalog] WITH CHANGE_TRACKING AUTO'
  EXEC(@create_index_text)
END
GO



CREATE PROCEDURE [dbo].[FullText_Disable]
AS
BEGIN
  EXEC('
  --drop indexes
  IF EXISTS (SELECT 1 FROM sys.fulltext_indexes WHERE object_id = object_id(''[Product]''))
    DROP FULLTEXT INDEX ON [Product]
  ')

  EXEC('
  IF EXISTS (SELECT 1 FROM sys.fulltext_indexes WHERE object_id = object_id(''[LocalizedProperty]''))
    DROP FULLTEXT INDEX ON [LocalizedProperty]
  ')

  EXEC('
  IF EXISTS (SELECT 1 FROM sys.fulltext_indexes WHERE object_id = object_id(''[ProductTag]''))
    DROP FULLTEXT INDEX ON [ProductTag]
  ')

  --drop catalog
  EXEC('
  IF EXISTS (SELECT 1 FROM sys.fulltext_catalogs WHERE [name] = ''gigaCommerceFullTextCatalog'')
    DROP FULLTEXT CATALOG [gigaCommerceFullTextCatalog]
  ')
END
GO


CREATE PROCEDURE [dbo].[LanguagePackImport]
(
  @LanguageId int,
  @XmlPackage xml,
  @UpdateExistingResources bit
)
AS
BEGIN
  IF EXISTS(SELECT * FROM [Language] WHERE [Id] = @LanguageId)
  BEGIN
    CREATE TABLE #LocaleStringResourceTmp
      (
        [LanguageId] [int] NOT NULL,
        [ResourceName] [nvarchar](200) NOT NULL,
        [ResourceValue] [nvarchar](MAX) NOT NULL
      )

    INSERT INTO #LocaleStringResourceTmp (LanguageId, ResourceName, ResourceValue)
    SELECT  @LanguageId, nref.value('@Name', 'nvarchar(200)'), nref.value('Value[1]', 'nvarchar(MAX)')
    FROM  @XmlPackage.nodes('//Language/LocaleResource') AS R(nref)

    DECLARE @ResourceName nvarchar(200)
    DECLARE @ResourceValue nvarchar(MAX)
    DECLARE cur_localeresource CURSOR FOR
    SELECT LanguageId, ResourceName, ResourceValue
    FROM #LocaleStringResourceTmp
    OPEN cur_localeresource
    FETCH NEXT FROM cur_localeresource INTO @LanguageId, @ResourceName, @ResourceValue
    WHILE @@FETCH_STATUS = 0
    BEGIN
      IF (EXISTS (SELECT 1 FROM [LocaleStringResource] WHERE LanguageId=@LanguageId AND ResourceName=@ResourceName))
      BEGIN
        IF (@UpdateExistingResources = 1)
        BEGIN
          UPDATE [LocaleStringResource]
          SET [ResourceValue]=@ResourceValue
          WHERE LanguageId=@LanguageId AND ResourceName=@ResourceName
        END
      END
      ELSE 
      BEGIN
        INSERT INTO [LocaleStringResource]
        (
          [LanguageId],
          [ResourceName],
          [ResourceValue]
        )
        VALUES
        (
          @LanguageId,
          @ResourceName,
          @ResourceValue
        )
      END
      
      
      FETCH NEXT FROM cur_localeresource INTO @LanguageId, @ResourceName, @ResourceValue
      END
    CLOSE cur_localeresource
    DEALLOCATE cur_localeresource

    DROP TABLE #LocaleStringResourceTmp
  END
END
GO


CREATE PROCEDURE [dbo].[DeleteGuests]
(
  @OnlyWithoutShoppingCart bit = 1,
  @CreatedFromUtc datetime,
  @CreatedToUtc datetime,
  @TotalRecordsDeleted int = null OUTPUT
)
AS
BEGIN
  CREATE TABLE #tmp_guests (CustomerId int)
    
  INSERT #tmp_guests (CustomerId)
  SELECT [Id] FROM [Customer] c with (NOLOCK)
  WHERE
  --created from
  ((@CreatedFromUtc is null) OR (c.[CreatedOnUtc] > @CreatedFromUtc))
  AND
  --created to
  ((@CreatedToUtc is null) OR (c.[CreatedOnUtc] < @CreatedToUtc))
  AND
  --shopping cart items
  ((@OnlyWithoutShoppingCart=0) OR (NOT EXISTS(SELECT 1 FROM [ShoppingCartItem] sci with (NOLOCK) inner join [Customer] with (NOLOCK) on sci.[CustomerId]=c.[Id])))
  AND
  --guests only
  (EXISTS(SELECT 1 FROM [Customer_CustomerRole_Mapping] ccrm with (NOLOCK) inner join [Customer] with (NOLOCK) on ccrm.[Customer_Id]=c.[Id] inner join [CustomerRole] cr with (NOLOCK) on cr.[Id]=ccrm.[CustomerRole_Id] WHERE cr.[SystemName] = N'Guests'))
  AND
  --no orders
  (NOT EXISTS(SELECT 1 FROM [Order] o with (NOLOCK) inner join [Customer] with (NOLOCK) on o.[CustomerId]=c.[Id]))
  AND
  --no blog comments
  (NOT EXISTS(SELECT 1 FROM [BlogComment] bc with (NOLOCK) inner join [Customer] with (NOLOCK) on bc.[CustomerId]=c.[Id]))
  AND
  --no news comments
  (NOT EXISTS(SELECT 1 FROM [NewsComment] nc  with (NOLOCK)inner join [Customer] with (NOLOCK) on nc.[CustomerId]=c.[Id]))
  AND
  --no product reviews
  (NOT EXISTS(SELECT 1 FROM [ProductReview] pr with (NOLOCK) inner join [Customer] with (NOLOCK) on pr.[CustomerId]=c.[Id]))
  AND
  --no product reviews helpfulness
  (NOT EXISTS(SELECT 1 FROM [ProductReviewHelpfulness] prh with (NOLOCK) inner join [Customer] with (NOLOCK) on prh.[CustomerId]=c.[Id]))
  AND
  --no poll voting
  (NOT EXISTS(SELECT 1 FROM [PollVotingRecord] pvr with (NOLOCK) inner join [Customer] with (NOLOCK) on pvr.[CustomerId]=c.[Id]))
  AND
  --no forum topics 
  (NOT EXISTS(SELECT 1 FROM [Forums_Topic] ft with (NOLOCK) inner join [Customer] with (NOLOCK) on ft.[CustomerId]=c.[Id]))
  AND
  --no forum posts 
  (NOT EXISTS(SELECT 1 FROM [Forums_Post] fp with (NOLOCK) inner join [Customer] with (NOLOCK) on fp.[CustomerId]=c.[Id]))
  AND
  --no system accounts
  (c.IsSystemAccount = 0)
  
  --delete guests
  DELETE [Customer]
  WHERE [Id] IN (SELECT [CustomerId] FROM #tmp_guests)
  
  --delete attributes
  DELETE [GenericAttribute]
  WHERE ([EntityId] IN (SELECT [CustomerId] FROM #tmp_guests))
  AND
  ([KeyGroup] = N'Customer')
  
  --total records
  SELECT @TotalRecordsDeleted = COUNT(1) FROM #tmp_guests
  
  DROP TABLE #tmp_guests
END
GO


CREATE PROCEDURE [dbo].[CategoryLoadAllPaged]
(
    @ShowHidden         BIT = 0,
    @Name               NVARCHAR(MAX) = NULL,
    @StoreId            INT = 0,
    @CustomerRoleIds  NVARCHAR(MAX) = NULL,
    @PageIndex      INT = 0,
  @PageSize      INT = 2147483644,
    @TotalRecords    INT = NULL OUTPUT
)
AS
BEGIN
  SET NOCOUNT ON

    --filter by customer role IDs (access control list)
  SET @CustomerRoleIds = ISNULL(@CustomerRoleIds, '')
  CREATE TABLE #FilteredCustomerRoleIds
  (
    CustomerRoleId INT NOT NULL
  )
  INSERT INTO #FilteredCustomerRoleIds (CustomerRoleId)
  SELECT CAST(data AS INT) FROM [giga_splitstring_to_table](@CustomerRoleIds, ',')
  DECLARE @FilteredCustomerRoleIdsCount INT = (SELECT COUNT(1) FROM #FilteredCustomerRoleIds)

    --ordered categories
    CREATE TABLE #OrderedCategoryIds
  (
    [Id] int IDENTITY (1, 1) NOT NULL,
    [CategoryId] int NOT NULL
  )
    
    --get max length of DisplayOrder and Id columns (used for padding Order column)
    DECLARE @lengthId INT = (SELECT LEN(MAX(Id)) FROM [Category])
    DECLARE @lengthOrder INT = (SELECT LEN(MAX(DisplayOrder)) FROM [Category])

    --get category tree
    ;WITH [CategoryTree]
    AS (SELECT [Category].[Id] AS [Id], dbo.[giga_padright] ([Category].[DisplayOrder], '0', @lengthOrder) + '-' + dbo.[giga_padright] ([Category].[Id], '0', @lengthId) AS [Order]
        FROM [Category] WHERE [Category].[ParentCategoryId] = 0
        UNION ALL
        SELECT [Category].[Id] AS [Id], [CategoryTree].[Order] + '|' + dbo.[giga_padright] ([Category].[DisplayOrder], '0', @lengthOrder) + '-' + dbo.[giga_padright] ([Category].[Id], '0', @lengthId) AS [Order]
        FROM [Category]
        INNER JOIN [CategoryTree] ON [CategoryTree].[Id] = [Category].[ParentCategoryId])
    INSERT INTO #OrderedCategoryIds ([CategoryId])
    SELECT [Category].[Id]
    FROM [CategoryTree]
    RIGHT JOIN [Category] ON [CategoryTree].[Id] = [Category].[Id]

    --filter results
    WHERE [Category].[Deleted] = 0
    AND (@ShowHidden = 1 OR [Category].[Published] = 1)
    AND (@Name IS NULL OR @Name = '' OR [Category].[Name] LIKE ('%' + @Name + '%'))
    AND (@ShowHidden = 1 OR @FilteredCustomerRoleIdsCount  = 0 OR [Category].[SubjectToAcl] = 0
        OR EXISTS (SELECT 1 FROM #FilteredCustomerRoleIds [roles] WHERE [roles].[CustomerRoleId] IN
            (SELECT [acl].[CustomerRoleId] FROM [AclRecord] acl WITH (NOLOCK) WHERE [acl].[EntityId] = [Category].[Id] AND [acl].[EntityName] = 'Category')
        )
    )
    AND (@StoreId = 0 OR [Category].[LimitedToStores] = 0
        OR EXISTS (SELECT 1 FROM [StoreMapping] sm WITH (NOLOCK)
      WHERE [sm].[EntityId] = [Category].[Id] AND [sm].[EntityName] = 'Category' AND [sm].[StoreId] = @StoreId
    )
    )
    ORDER BY ISNULL([CategoryTree].[Order], 1)

    --total records
    SET @TotalRecords = @@ROWCOUNT

    --paging
    SELECT [Category].* FROM #OrderedCategoryIds AS [Result] INNER JOIN [Category] ON [Result].[CategoryId] = [Category].[Id]
    WHERE ([Result].[Id] > @PageSize * @PageIndex AND [Result].[Id] <= @PageSize * (@PageIndex + 1))
    ORDER BY [Result].[Id]

    DROP TABLE #FilteredCustomerRoleIds
    DROP TABLE #OrderedCategoryIds
END
GO
11.097 messaggi dal 09 febbraio 2002
Contributi
Ciao,
lo script non può essere eseguito così com'è perché ad esempio contiene le istruzioni GO che non sono parte del linguaggio T-SQL ma hanno senso solo in SQL Server Management Studio.
Comunque, dopo aver letto il file, lo puoi spezzare in varie parti lì dove si trovano le istruzioni GO ed eseguire i comandi individualmente.

Prova a seguire questo, trovi del codice che ti mostra come farlo.
https://social.msdn.microsoft.com/Forums/vstudio/en-US/96c27eff-4b2c-44a9-b5a3-4ef8a42e68a2/executenonquery-and-stored-procedure-creation?forum=csharpgeneral

Inserisci quel codice in una classe, supponiamo chiamata SqlExecutor.
public class SqlExecutor
{
  private readonly IHostingEnvironment env;
  public SqlExecutor(IHostingEnvironment env) {
    this.env = env;
  }
  public void ExecuteSqlScript(string relativeFilePath)
  {
    //Completiamo il percorso relativo
    string fullFilePath = Path.Combine(env.ContentRootPath, relativeFilePath);

    //Qui codice trovato nel post
  }
}


Ora registra questa classe come servizio dal metodo ConfigureServices della classe Startup.
services.AddTransient<SqlExecutor>();


Ora dal codefile della tua Razor Page ricevi il SqlExecutor nel costruttore:
public class MyPageModel : PageModel
{
  private readonly SqlExecutor sqlExecutor;
  public MyPageModel(SqlExecutor sqlExecutor)
  {
    this.sqlExecutor = sqlExecutor;
  }
  public void OnGet()
  {
    //Qui o sull'OnPost o sul metodo che vuoi usi il SqlExecutor,
    //il percorso deve essere relativo alla root dell'applicazione
    sqlExecutor.ExecuteSqlScript("directory/file.sql");
  }
}


ciao,
Moreno
Modificato da BrightSoul il 08 ottobre 2018 23.04 -

Enjoy learning and just keep making

Torna al forum | Feed RSS

ASPItalia.com non è responsabile per il contenuto dei messaggi presenti su questo servizio, non avendo nessun controllo sui messaggi postati nei propri forum, che rappresentano l'espressione del pensiero degli autori.