AspDotNetStorefront Shopping Cart ECommerce

We put our trust in McAfee
You should, too

Questions? (602) 490-0243

Products
- Ecommerce Solution + MultiChannel Integration Powerful ecommerce software + integrated multichannel platform feeds products to Google Shopping, Amazon & More!
- Ecommerce Solution Powerful ecommerce software you can customize to fit specific business needs.
- AspDotNetStorefront - MultiStore MSx Features Source Code
- Channel Manager (DotFeed) Integrated Channel Manager About DotFeed Channels Discovery Channels (MiniSites) DotFeed Support Central
- More Stores › Store Add-Ons › License Resellers ›
Hosting
Resources
Community
About Us

SAMPLE SQL STORED PROCS & TABLE FRAGMENTS (We support SQL 2005 or SQL 2008)

CREATE TABLE [dbo].[Customer](
        [CustomerID] [int] IDENTITY(1,1) NOT NULL,
        [CustomerGUID] [uniqueidentifier] ROWGUIDCOL  NOT NULL CONSTRAINT DF_Customer_CustomerGUID DEFAULT(newid()),
        [CustomerLevelID] [int] NOT NULL CONSTRAINT DF_Customer_CustomerLevelID DEFAULT((0)),
        [RegisterDate] [datetime] NOT NULL CONSTRAINT DF_Customer_RegisterDate DEFAULT(getdate()),
        [Email] [nvarchar](100) NOT NULL CONSTRAINT DF_Customer_Email DEFAULT(''),
        [Password] [nvarchar](250) CONSTRAINT DF_Customer_Password DEFAULT(''),
        [SaltKey] int NOT NULL CONSTRAINT DF_Customer_SaltKey DEFAULT((0)),
        [DateOfBirth] [datetime] NULL,
        [Gender] [nvarchar](1) NULL,
        [FirstName] [nvarchar](100) NULL,
        [LastName] [nvarchar](100) NULL,
        [Notes] [ntext] NULL,
        [SkinID] [int] NOT NULL CONSTRAINT DF_Customer_SiteID DEFAULT((1)),
        [Phone] [nvarchar](25) NULL,
        [FAX] [varchar](25) NULL,
        [AffiliateID] [int] NULL,
        [Referrer] [ntext] NULL,
        [CouponCode] [nvarchar](50) NULL,
        [OkToEmail] [tinyint] NOT NULL CONSTRAINT DF_Customer_OkToEmail DEFAULT((1)),
        [IsAdmin] [tinyint] NOT NULL CONSTRAINT DF_Customer_IsAdmin DEFAULT((0)),
        [BillingEqualsShipping] [tinyint] NOT NULL CONSTRAINT DF_Customer_BillingEqualsShipping DEFAULT((0)),
        [LastIPAddress] [nvarchar](20) NULL,
        [OrderNotes] [ntext] NULL,
        [SubscriptionExpiresOn] [datetime] NULL,
        [RTShipRequest] [ntext] NULL,
        [RTShipResponse] [ntext] NULL,
        [OrderOptions] [ntext] NULL,
        [LocaleSetting] [nvarchar](10) NOT NULL CONSTRAINT DF_Customer_LocaleSetting DEFAULT('en-US'),
        [MicroPayBalance] [money] NOT NULL CONSTRAINT DF_Customer_MicroPayBalance DEFAULT((0.0)),
        [RecurringShippingMethodID] [int] NOT NULL CONSTRAINT DF_Customer_RecurringShippingMethodID DEFAULT((1)),
        [RecurringShippingMethod] [ntext] ,
        [BillingAddressID] [int] NULL,
        [ShippingAddressID] [int] NULL,
        ...

if exists (select * from [dbo].sysobjects where id = OBJECT_ID(N'[dbo].[aspdnsf_GetProductsEntity]') and OBJECTPROPERTY(id,N'IsProcedure') = 1)
        DROP PROCEDURE [dbo].[aspdnsf_GetProductsEntity]
        GO
        create proc [dbo].[aspdnsf_GetProductsEntity]
        @categoryID      int = 0,
        @sectionID       int = 0,
        @manufacturerID  int = 0,
        @distributorID   int = 0,
        @genreID   int = 0,
        @vectorID   int = 0,
        @affiliateID     int = 0,
        @ProductTypeID   int = 1,
        @ViewType        bit = 1, -- 0 = all variants, 1 = one variant
        @StatsFirst      tinyint = 1,
        @searchstr       nvarchar(4000) = '',
        @extSearch       tinyint = 1,
        @publishedonly   tinyint = 0,
        @OnSaleOnly      tinyint = 0,
        @SearchIndex	 varchar(2) = '',
        @SortOrder		 varchar(4) = 'ASC', -- ASC or DESC
        @SortBy			 varchar(50) = 'Name' -- name to sort by
        AS
        BEGIN
        SET NOCOUNT ON
        DECLARE @rcount int
        DECLARE @custlevelcount int, @sectioncount int, @affiliatecount int, @categorycount int, @distributorcount int, @genrecount int,  @vectorcount int, @manufacturercount int
        DECLARE @FilterProductsByAffiliate tinyint
        SELECT @FilterProductsByAffiliate = case ConfigValue when 'true' then 1 else 0 end FROM AppConfig WHERE [Name] = 'FilterProductsByAffiliate'
        select @categorycount     = si.rows from sysobjects so with (nolock) join sysindexes si with (nolock) on so.id = si.id where so.id = object_id('productcategory') and si.indid < 2 and type = 'u'
        select @sectioncount      = si.rows from sysobjects so with (nolock) join sysindexes si with (nolock) on so.id = si.id where so.id = object_id('productsection') and si.indid < 2 and type = 'u'
        select @affiliatecount    = si.rows from sysobjects so with (nolock) join sysindexes si with (nolock) on so.id = si.id where so.id = object_id('ProductAffiliate') and si.indid < 2 and type = 'u'
        select @distributorcount  = si.rows from sysobjects so with (nolock) join sysindexes si with (nolock) on so.id = si.id where so.id = object_id('ProductDistributor') and si.indid < 2 and type = 'u'
        select @genrecount  = si.rows from sysobjects so with (nolock) join sysindexes si with (nolock) on so.id = si.id where so.id = object_id('ProductGenre') and si.indid < 2 and type = 'u'
        select @vectorcount  = si.rows from sysobjects so with (nolock) join sysindexes si with (nolock) on so.id = si.id where so.id = object_id('ProductVector') and si.indid < 2 and type = 'u'
        select @manufacturercount = si.rows from sysobjects so with (nolock) join sysindexes si with (nolock) on so.id = si.id where so.id = object_id('ProductManufacturer') and si.indid < 2 and type = 'u'
        SET @searchstr = '%' + @searchstr + '%'
        SET @SearchIndex = @SearchIndex + '%'
        SET @rcount = @@rowcount
        IF @StatsFirst = 1
        SELECT cast(ceiling(@rcount*1.0/1) as int) pages, @rcount ProductCount
        DECLARE @sql nvarchar(4000)
        SET @sql = '
        SELECT
        p.ProductID,
        p.Name,
        pv.VariantID,
        pv.Name AS VariantName,
        p.ProductGUID,
        p.Summary,
        p.Description,
        p.ProductTypeID,
        p.TaxClassID,
        p.SKU,
        p.ManufacturerPartNumber,
        p.XmlPackage,
        p.Published,
        p.Looks,
        p.Notes,
        p.IsAKit,
        p.ShowInProductBrowser,
        p.IsAPack,
        p.PackSize,
        p.IsSystem,
        p.Deleted,
        p.CreatedOn,
        p.ImageFileNameOverride,
        pv.VariantGUID,
        pv.Description AS VariantDescription,
        pv.SKUSuffix,
        pv.ManufacturerPartNumber AS VariantManufacturerPartNumber,
        pv.Price,
        pv.CustomerEntersPrice,
        isnull(pv.SalePrice, 0) SalePrice,
        cast(isnull(pv.Weight,0) as decimal(10,1)) Weight,
        pv.MSRP,
        pv.Cost,
        case p.TrackInventoryBySizeAndColor when 1 then isnull(i.quan, 0) else pv.inventory end Inventory,
        pv.DisplayOrder as VariantDisplayOrder,
        pv.Notes AS VariantNotes,
        pv.IsTaxable,
        pv.IsShipSeparately,
        pv.IsDownload,
        pv.DownloadLocation,
        pv.Published AS VariantPublished,
        pv.SubscriptionInterval,
        pv.RestrictedQuantities,
        pv.MinimumQuantity,
        pv.Deleted AS VariantDeleted,
        pv.CreatedOn AS VariantCreatedOn,
        d.Name AS DistributorName,
        x.Name AS GenreName,
        x2.Name AS SHowName,
        d.DistributorID,
        x.GenreID,
        x2.VectorID,
        m.ManufacturerID,
        m.Name AS ManufacturerName'
        DECLARE @sql1 nvarchar(4000)
        SET @sql1 = '
        FROM Product p with (NOLOCK)
        join
        (
        SELECT distinct p.productid, pv.VariantID
        FROM
        product p with (nolock)
        left join ProductVariant pv             with (NOLOCK) on p.ProductID = pv.ProductID and pv.IsDefault >= '
        SET @sql1 = @sql1 +CONVERT(nvarchar,@ViewType)+'
        left join productcategory pc       with (nolock) on p.ProductID = pc.ProductID
        left join productsection ps        with (nolock) on p.ProductID = ps.ProductID
        left join ProductManufacturer pm   with (nolock) on p.ProductID = pm.ProductID
        left join ProductDistributor pd    with (nolock) on p.ProductID = pd.ProductID
        left join ProductGenre px    with (nolock) on p.ProductID = px.ProductID
        left join ProductVector px2    with (nolock) on p.ProductID = px2.ProductID
        left join ProductAffiliate pa      with (nolock) on p.ProductID = pa.ProductID
        WHERE
        (pc.categoryid = '
        DECLARE @sql2 nvarchar(4000)
        SET @sql2 = ' ' + CONVERT(nvarchar,@categoryID) + ') or (ps.sectionid = '
        SET @sql2 = @sql2 + CONVERT(nvarchar,@sectionID) + ') or (pa.AffiliateID = '
        SET @sql2 = @sql2 + CONVERT(nvarchar,@affiliateID) + ') or (pm.manufacturerid = '
        SET @sql2 = @sql2 + CONVERT(nvarchar,@manufacturerID) + ') or (pd.DistributorID = '
        SET @sql2 = @sql2 + CONVERT(nvarchar,@distributorID) + ') or (px.genreID = '
        SET @sql2 = @sql2 + CONVERT(nvarchar,@genreID) + ') or (px2.vectorID = '
        SET @sql2 = @sql2 + CONVERT(nvarchar,@vectorID) + ')'
        DECLARE @sql3 nvarchar(4000)
        SET @sql3 = '
        and case when isnull(pv.saleprice,0) = 0 then 0 else 1 end >= '+CONVERT(nvarchar,@OnSaleOnly)+'
        and p.published >= '+CONVERT(nvarchar,@publishedonly)+'
        and pv.published >= '+CONVERT(nvarchar,@publishedonly)+'
        and p.Deleted = 0
        and pv.Deleted = 0
        )							   pf on p.ProductID = pf.ProductID
        left join ProductVariant      pv  with (NOLOCK) on p.ProductID = pv.ProductID and pv.IsDefault >= '
        SET @sql3 = @sql3 + CONVERT(nvarchar,@ViewType)+'
        left join ProductManufacturer pm  with (NOLOCK) on p.ProductID = pm.ProductID
        left join Manufacturer         m  with (NOLOCK) on pm.ManufacturerID = m.ManufacturerID
        left join ProductDistributor  pd  with (NOLOCK) on p.ProductID = pd.ProductID
        left join ProductGenre  px  with (NOLOCK) on p.ProductID = px.ProductID
        left join ProductVector  px2  with (NOLOCK) on p.ProductID = px2.ProductID
        left join Distributor          d  with (NOLOCK) on pd.DistributorID = d.DistributorID
        left join Genre          x  with (NOLOCK) on px.GenreID = x.GenreID
        left join Vector          x2  with (NOLOCK) on px2.VectorID = x2.VectorID
        left join (select VariantID, sum(quan) quan from Inventory with (nolock) group by VariantID) i  on pv.VariantID = i.VariantID
        WHERE
        (p.ProductTypeID = '+CONVERT(nvarchar,@ProductTypeID)+ ' or  '+CONVERT(nvarchar,@ProductTypeID)+ ' = 0) and
        (
        p.Name LIKE '''+ @searchstr + '''
        or convert(nvarchar(20),p.productid) LIKE '''+ @searchstr + '''
        or pv.name LIKE '''+ @searchstr + '''
        or p.sku LIKE '''+ @searchstr + '''
        or p.manufacturerpartnumber LIKE '''+ @searchstr + '''
        or pv.manufacturerpartnumber LIKE '''+ @searchstr + '''
        or ('+CONVERT(nvarchar,@extSearch)+' = 1 AND p.Description LIKE '''+ @searchstr + ''')
        or ('+CONVERT(nvarchar,@extSearch)+' = 1 AND p.Summary LIKE '''+ @searchstr + ''')
        )
        and
        p.Name LIKE '''+ @SearchIndex + '''
        ORDER BY '
        DECLARE @sql4 nvarchar(4000)
        IF @SortBy = 'ProductID'
        SET @sql4 = 'P.ProductID'
        ELSE IF @SortBy = 'SKU'
        SET @sql4 = 'SKU'
        ELSE IF @SortBy = 'ManufacturerPartNumber'
        SET @sql4 = 'P.ManufacturerPartNumber'
        ELSE IF @SortBy = 'Inventory'
        SET @sql4 = 'Inventory'
        ELSE
        SET @sql4 = 'P.[Name]'
        IF @SortOrder = 'DESC'
        SET @sql4 = @sql4 + ' DESC'
        ELSE
        SET @sql4 = @sql4 + ' ASC'
        SET @sql4 = @sql4 + ', pv.DisplayOrder'
        EXECUTE(@sql+' '+@sql1+' '+@sql2+' '+@sql3+' '+@sql4)
        IF @StatsFirst <> 1
        SELECT cast(ceiling(@rcount*1.0/1) as int) pages, @rcount ProductCount
        END
        GO

IF  EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'[dbo].[aspdnsf_UpdFeed]') AND type in (N'P', N'PC'))
        DROP PROCEDURE [dbo].[aspdnsf_UpdFeed]
        GO
        create proc [dbo].[aspdnsf_UpdFeed]
        @FeedID int,
        @Name nvarchar(100),
        @DisplayOrder int,
        @XmlPackage nvarchar(100),
        @CanAutoFTP tinyint,
        @FTPUsername nvarchar(100),
        @FTPPassword nvarchar(100),
        @FTPServer nvarchar(100),
        @FTPPort int,
        @FTPFilename nvarchar(100),
        @ExtensionData ntext
        AS
        SET NOCOUNT ON
        IF isnull(@XmlPackage, '') = '' BEGIN
        RAISERROR('XmlPAckage is required', 16, 1)
        RETURN
        END
        IF @CanAutoFTP > 1
        SET @CanAutoFTP = 1
        UPDATE dbo.Feed
        SET
        Name = COALESCE(@Name, Name),
        DisplayOrder = COALESCE(@DisplayOrder, DisplayOrder),
        XmlPackage = COALESCE(@XmlPackage, XmlPackage),
        CanAutoFTP = COALESCE(@CanAutoFTP, CanAutoFTP),
        FTPUsername = COALESCE(@FTPUsername, FTPUsername),
        FTPPassword = COALESCE(@FTPPassword, FTPPassword),
        FTPServer = COALESCE(@FTPServer, FTPServer),
        FTPPort = COALESCE(@FTPPort, FTPPort),
        FTPFilename = COALESCE(@FTPFilename, FTPFilename),
        ExtensionData = COALESCE(@ExtensionData, ExtensionData)
        WHERE FeedID = @FeedID
        GO

MultiChannel Selling

About Us

Community

Customer Service