Wednesday, March 09, 2011

Output the currency symbol in uCommerce

One thing that's always bugged me about uCommerce is the way the prices are displayed (using the not so inviting ISO codes), this is a simple switch statement to output the (prettier) HTML symbol instead.

<xsl:choose>

    <xsl:when test="@currency = 'GBP'">

        <xsl:text disable-output-escaping="yes">&amp;pound;</xsl:text>

    </xsl:when>

    <xsl:when test="@currency = 'EUR'">

        <xsl:text disable-output-escaping="yes">&amp;euro;</xsl:text>

    </xsl:when>

    <xsl:when test="@currency = 'YEN'">

        <xsl:text disable-output-escaping="yes">&amp;yen;</xsl:text>

    </xsl:when>

    <xsl:otherwise>

        <xsl:text disable-output-escaping="yes">$</xsl:text>

    </xsl:otherwise>

</xsl:choose>

Output the currency symbol in uCommerce

Useful Links:            Comments [2]  Trackback

Tags: eCommerce | Helpful Script | The Site Doctor | uCommerce | Umbraco | XSLT

 Thursday, March 03, 2011

Remove uCommerce Product Definition Field in SQL

Sometimes you need to remove a product definition field from uCommerce e.g. one created in a test environment. Although you can just right click and click "delete" within the administration area, this sometimes doesn't work e.g. when it's a pre-release so this is a simple script which allows you to remove a product definition field from the database.

USE [YourDatabaseName]
GO

BEGIN TRANSACTION

-- Get a list of the current product definitions
SELECT * FROM dbo.uCommerce_ProductDefinition

DECLARE @ProductDefinitionId int, @ProductDefinitionFieldId int
SET @ProductDefinitionId = 23

-- Check that this is the right product definition
SELECT * FROM dbo.uCommerce_ProductDefinition WHERE ProductDefinitionId = @ProductDefinitionId
-- Get a break down of the various fields for the product definition
SELECT * FROM dbo.uCommerce_ProductDefinitionField WHERE ProductDefinitionId = @ProductDefinitionId

-- Set the field id
SET @ProductDefinitionFieldId = 40

-- Check the right field and descriptions will be removed
SELECT * FROM dbo.uCommerce_ProductDefinitionField f INNER JOIN dbo.uCommerce_ProductDefinitionFieldDescription d ON f.ProductDefinitionFieldId = d.ProductDefinitionFieldId WHERE f.ProductDefinitionId = @ProductDefinitionId AND f.ProductDefinitionFieldId = @ProductDefinitionFieldId
SELECT * FROM dbo.uCommerce_ProductDefinitionField f INNER JOIN dbo.uCommerce_ProductProperty p ON f.ProductDefinitionFieldId = p.ProductDefinitionFieldId WHERE f.ProductDefinitionId = @ProductDefinitionId AND f.ProductDefinitionFieldId = @ProductDefinitionFieldId
SELECT * FROM dbo.uCommerce_ProductDefinitionField WHERE ProductDefinitionId = @ProductDefinitionId AND ProductDefinitionFieldId = @ProductDefinitionFieldId

-- Remove any product property definitions
DELETE p FROM dbo.uCommerce_ProductDefinitionField f INNER JOIN dbo.uCommerce_ProductProperty p ON f.ProductDefinitionFieldId = p.ProductDefinitionFieldId WHERE f.ProductDefinitionId = @ProductDefinitionId AND f.ProductDefinitionFieldId = @ProductDefinitionFieldId
-- Remove any associated descriptions
DELETE d FROM dbo.uCommerce_ProductDefinitionField f INNER JOIN dbo.uCommerce_ProductDefinitionFieldDescription d ON f.ProductDefinitionFieldId = d.ProductDefinitionFieldId WHERE f.ProductDefinitionId = @ProductDefinitionId AND f.ProductDefinitionFieldId = @ProductDefinitionFieldId

-- Remove the field itself
DELETE FROM dbo.uCommerce_ProductDefinitionField WHERE ProductDefinitionId = @ProductDefinitionId AND ProductDefinitionFieldId = @ProductDefinitionFieldId

ROLLBACK TRANSACTION
-- When you're happy, uncomment this line
--COMMIT TRANSACTION

Remove uCommerce Product Definition Field in SQL

Useful Links:            Comments [0]  Trackback

Tags: Helpful Script | SQL | The Site Doctor | uCommerce | Umbraco | Web Development

 Friday, February 19, 2010

Search every table and field in a SQL Server Database Updated

As some of my blog posts are a little out of date, I thought I would spend some time updating the most popular ones. As I use this script on a regular basis and there was an error with the original posting, I thought I'd update it with a "corrected" version to get things started.

If you want to see the original script, you can refer to How to search every table and field in a SQL Server Database. This one's just fixed :)

As a stand alone script

DECLARE @SearchStr nvarchar(100)
SET @SearchStr = '## YOUR STRING HERE ##'


    -- Copyright © 2002 Narayana Vyas Kondreddi. All rights reserved.
    -- Purpose: To search all columns of all tables for a given search string
    -- Written by: Narayana Vyas Kondreddi
    -- Site: http://vyaskn.tripod.com
    -- Updated and tested by Tim Gaunt
    -- http://www.thesitedoctor.co.uk
    -- http://blogs.thesitedoctor.co.uk/tim/2010/02/19/Search+Every+Table+And+Field+In+A+SQL+Server+Database+Updated.aspx
    -- Tested on: SQL Server 7.0, SQL Server 2000, SQL Server 2005 and SQL Server 2010
    -- Date modified: 03rd March 2011 19:00 GMT
    CREATE TABLE #Results (ColumnName nvarchar(370), ColumnValue nvarchar(3630))

    SET NOCOUNT ON

    DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128), @SearchStr2 nvarchar(110)
    SET  @TableName = ''
    SET @SearchStr2 = QUOTENAME('%' + @SearchStr + '%','''')

    WHILE @TableName IS NOT NULL
    
    BEGIN
        SET @ColumnName = ''
        SET @TableName = 
        (
            SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))
            FROM     INFORMATION_SCHEMA.TABLES
            WHERE         TABLE_TYPE = 'BASE TABLE'
                AND    QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName
                AND    OBJECTPROPERTY(
                        OBJECT_ID(
                            QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)
                             ), 'IsMSShipped'
                               ) = 0
        )

        WHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL)
            
        BEGIN
            SET @ColumnName =
            (
                SELECT MIN(QUOTENAME(COLUMN_NAME))
                FROM     INFORMATION_SCHEMA.COLUMNS
                WHERE         TABLE_SCHEMA    = PARSENAME(@TableName, 2)
                    AND    TABLE_NAME    = PARSENAME(@TableName, 1)
                    AND    DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar', 'int', 'decimal')
                    AND    QUOTENAME(COLUMN_NAME) > @ColumnName
            )
    
            IF @ColumnName IS NOT NULL
            
            BEGIN
                INSERT INTO #Results
                EXEC
                (
                    'SELECT ''' + @TableName + '.' + @ColumnName + ''', LEFT(' + @ColumnName + ', 3630) FROM ' + @TableName + ' (NOLOCK) ' +
                    ' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2
                )
            END
        END    
    END

    SELECT ColumnName, ColumnValue FROM #Results

DROP TABLE #Results

As a re-usable stored procedure

CREATE PROC SearchAllTables
(
    @SearchStr nvarchar(100)
)
AS

BEGIN

    -- Copyright © 2002 Narayana Vyas Kondreddi. All rights reserved.
    -- Purpose: To search all columns of all tables for a given search string
    -- Written by: Narayana Vyas Kondreddi
    -- Site: http://vyaskn.tripod.com
    -- Updated and tested by Tim Gaunt
    -- http://www.thesitedoctor.co.uk
    -- http://blogs.thesitedoctor.co.uk/tim/2010/02/19/Search+Every+Table+And+Field+In+A+SQL+Server+Database+Updated.aspx
    -- Tested on: SQL Server 7.0, SQL Server 2000, SQL Server 2005 and SQL Server 2010
    -- Date modified: 03rd March 2011 19:00 GMT
    CREATE TABLE #Results (ColumnName nvarchar(370), ColumnValue nvarchar(3630))

    SET NOCOUNT ON

    DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128), @SearchStr2 nvarchar(110)
    SET  @TableName = ''
    SET @SearchStr2 = QUOTENAME('%' + @SearchStr + '%','''')

    WHILE @TableName IS NOT NULL
    
    BEGIN
        SET @ColumnName = ''
        SET @TableName = 
        (
            SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))
            FROM     INFORMATION_SCHEMA.TABLES
            WHERE         TABLE_TYPE = 'BASE TABLE'
                AND    QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName
                AND    OBJECTPROPERTY(
                        OBJECT_ID(
                            QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)
                             ), 'IsMSShipped'
                               ) = 0
        )

        WHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL)
            
        BEGIN
            SET @ColumnName =
            (
                SELECT MIN(QUOTENAME(COLUMN_NAME))
                FROM     INFORMATION_SCHEMA.COLUMNS
                WHERE         TABLE_SCHEMA    = PARSENAME(@TableName, 2)
                    AND    TABLE_NAME    = PARSENAME(@TableName, 1)
                    AND    DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar', 'int', 'decimal')
                    AND    QUOTENAME(COLUMN_NAME) > @ColumnName
            )
    
            IF @ColumnName IS NOT NULL
            
            BEGIN
                INSERT INTO #Results
                EXEC
                (
                    'SELECT ''' + @TableName + '.' + @ColumnName + ''', LEFT(' + @ColumnName + ', 3630) FROM ' + @TableName + ' (NOLOCK) ' +
                    ' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2
                )
            END
        END    
    END

    SELECT ColumnName, ColumnValue FROM #Results
    DROP TABLE #Results
END

Search every table and field in a SQL Server Database Updated

Useful Links:            Comments [6]  Trackback

Tags: Helpful Script | SQL | SQL Server | Stored Procedure