Thread: What approach to take?

Hello,

My clients products have more information to be displayed then the ADNSF can handle, so it must be customized.

I was thinking abou tthis problem and I found several solutions to it, but I would like some experts advice which path to take. I would like to keep excel import feature if possible

1. Add additional table that will have FK to products table and display data from that table. This method violates excel import and it need to be fixed.

2. Expand original product table and add desired fields. This way it is easy to pull products information that I need, since they are already there. This method violates excel import as well and it need to be fixed.

3. Use one field in original product table and populate it with data that I need (comma delimited). I need to do some parsing before displaying it on the products page. This does not violate excel import.

What do you think about each solution, and what would you choose?


Thank you for your time.

Depending on how many additional fields you need, you could just use the ExtensionData fields in the product table (there are 5).

Or instead of using one field with comma delimited data, use XML - much more standard and easier to parse.

In all cases, you can easily extend the Excel import routine to support importing of your new data - just enhance import.cs

We added our tenth custom field on Friday and there are no issues so far using that many. USe Webopius way, if you add an additional field then you also will need to include this on your bulk import sheet too.

I cant find import.cs anywhere. I am using 9,3 version.

Import.cs is available with the full source code and is in the ASPDNSFCore package

Originally Posted by webopius

Import.cs is available with the full source code and is in the ASPDNSFCore package

Thanks. I thought that I can't find it.

I see five extension fields in Product table. That would be perfect for me needs, however... what field in excel sheet correspond to those five fields? I have only one column ion my excel called extension data.

Originally Posted by vedran

3. Use one field in original product table and populate it with data that I need (comma delimited). I need to do some parsing before displaying it on the products page. This does not violate excel import.

You've already received some excellent suggestions. But as far as this option goes I find that putting delimited values into any column is just asking for trouble when you try to query the data. From a database theory standpoint this violates normalization rules. However, I've been stuck with data like that and you have to join on the column without some sort of parsing routine to extract the data first...which is very inefficient. It's best to avoid row-by-row parsing with a scalar-value function in a SELECT statement.

For anyone who might be stuck with delimited data within a column, here are some functions that can query the data a little more efficiently.

Code:

/* Create an example test table with a column of delimited values */
/* The test tables only need to be created once of course */

CREATE TABLE [dbo].[TestStringInput](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[DelimitedStringInput] [VARCHAR](max) NULL,
 CONSTRAINT [PK_TestStringInput] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

/* Insert some test values */

INSERT INTO dbo.TestStringInput (DelimitedStringInput) VALUES ('234,567,890')
INSERT INTO dbo.TestStringInput (DelimitedStringInput) VALUES ('123,456,789')
INSERT INTO dbo.TestStringInput (DelimitedStringInput) VALUES ('123,567,890')
INSERT INTO dbo.TestStringInput (DelimitedStringInput) VALUES ('123,234,456,789,890')
INSERT INTO dbo.TestStringInput (DelimitedStringInput) VALUES ('890,567,234')
GO
    
/* Create another test table with dummy data and keys to join to */  

CREATE TABLE [dbo].[TestJoinOutPut](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[TestValue] [VARCHAR](max) NULL,
 CONSTRAINT [PK_TestJoinOutPut] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

/* Insert some test values */
    
SET IDENTITY_INSERT dbo.TestJoinOutPut ON
INSERT INTO dbo.TestJoinOutPut (ID,TestValue) VALUES (123,'George Washington')
INSERT INTO dbo.TestJoinOutPut (ID,TestValue) VALUES (234,'John Adams')
INSERT INTO dbo.TestJoinOutPut (ID,TestValue) VALUES (456,'Thomas Jefferson')
INSERT INTO dbo.TestJoinOutPut (ID,TestValue) VALUES (567,'James Madison')
INSERT INTO dbo.TestJoinOutPut (ID,TestValue) VALUES (789,'James Monroe')
INSERT INTO dbo.TestJoinOutPut (ID,TestValue) VALUES (890,'Andrew Jackson')
SET IDENTITY_INSERT dbo.TestJoinOutPut OFF
GO

--/* Create a function to join the delimited values */
--/* to the second table's keys as a table          */

CREATE FUNCTION [dbo].[TestJoinTable]
    (
      @strList VARCHAR(50)
    )
RETURNS @RtnValue TABLE 
(
     [ID] INT IDENTITY(1,1)
    ,[KeyValue] INT
    ,[TestValue] VARCHAR(MAX)
    ,PRIMARY KEY (ID)
    ,UNIQUE (ID)
) 
AS 
BEGIN

    DECLARE 
		 @TestValues VARCHAR(MAX)
		,@strXML XML

	SET	@strXML = CONVERT(XML,'<root><item>' + REPLACE(@strList,',','</item><item>')+'</item></root>')

	INSERT INTO @RtnValue (KeyValue, TestValue)
	SELECT 
		  Nodes.VALUE
		 ,testjoin.TestValue
	FROM
		(
		SELECT 
			 X.t.value('.','VARCHAR(MAX)') AS Value 
		FROM 
			@strXML.nodes('/root/item') X (t)
		) AS Nodes
	INNER JOIN 
		dbo.TestJoinOutPut AS testjoin
		ON [Value] = testjoin.ID
	ORDER BY
		testjoin.TestValue	--optional order by
		
	RETURN

END
GO


/* Create a function to join the delimited values */
/* to the second table's keys as a delimited list */

ALTER FUNCTION [dbo].[TestJoinDelimitedList]
    (
      @strList VARCHAR(MAX)
     ,@Delimiter VARCHAR(10)
    )
RETURNS VARCHAR(MAX)
AS 
BEGIN

    DECLARE 
		 @TestValues VARCHAR(MAX)
		,@strXML XML

	SET	@strXML = CONVERT(XML,'<root><item>' + REPLACE(@strList,',','</item><item>')+'</item></root>')

    SELECT 
        @TestValues = COALESCE(@TestValues + @Delimiter,'') + CAST(testjoin.TestValue AS VARCHAR(MAX))
	FROM
		(
		SELECT 
			 X.t.value('.','VARCHAR(MAX)') AS Value 
		FROM 
			@strXML.nodes('/root/item') X (t)
		) AS Nodes
	INNER JOIN 
		dbo.TestJoinOutPut AS testjoin
		ON [Value] = testjoin.ID
	ORDER BY
		testjoin.TestValue	--optional order by
		
    RETURN @TestValues

END
GO


/* Output to a table from a single value input */

	SELECT * FROM dbo.TestJoinTable('890,567,234') 


/* Output to a delimited list by joining to the second table */
	
	SELECT
		dbo.TestJoinDelimitedList(DelimitedStringInput,',') AS JoinedList
	FROM
		dbo.TestStringInput

To deal with larger amounts of data and to avoid looping use a "Tally" or "Numbers" table here are some more complex examples. Or place the data directly into XML as in the final example below.

Code:

* Output ALL delimited values to a single table using a Tally table */


    DECLARE 
		 @strValues VARCHAR(MAX)
		,@Delimiter VARCHAR(10)
		
	DECLARE @Elements TABLE 
        (
        Number INT IDENTITY(1,1)
        ,Value  VARCHAR(MAX)
        )
        
	SET @Delimiter = '|'

    SELECT 
        @strValues = COALESCE(@strValues + @Delimiter,'')
        + CAST(dbo.TestJoinDelimitedList(DelimitedStringInput,',') AS VARCHAR(MAX))
	FROM
		dbo.TestStringInput
		
	SELECT @strValues AS RelatedOutputValues
	
	SET @strValues = ','+REPLACE(@strValues,'|',',') +','

	INSERT INTO @Elements
		(Value)
		SELECT
			SUBSTRING(@strValues,N + 1,CHARINDEX(',',@strValues,N + 1) - N - 1)
		FROM
			dbo.Tally
		WHERE
			N < LEN(@strValues)
			AND SUBSTRING(@strValues,N,1) = ','

	SELECT DISTINCT  -- DISTINCT optional
		 t.ID
		,e.Value
	FROM
		@Elements AS e
	INNER JOIN 
		dbo.TestJoinOutPut AS t
		ON e.Value = t.TestValue
	ORDER BY
		e.Value		-- ORDER BY optional



/* Output ALL delimited values to a 2-dimensional array using a Tally table */


    DECLARE 
		 @strValues VARCHAR(MAX)
		,@Delimiter VARCHAR(10)
		,@strArray VARCHAR(MAX)

	DECLARE @Elements TABLE 
        (
        Number INT IDENTITY(1,1)
        ,Value  VARCHAR(MAX)
        )
    		
	SET @Delimiter = '|'

    SELECT 
        @strValues = COALESCE(@strValues + @Delimiter,'')
        + CAST(DelimitedStringInput AS VARCHAR(MAX))
	FROM
		dbo.TestStringInput

	SELECT @strValues AS StringKeys

	SET @strValues = ','+REPLACE(@strValues,'|',',') +','

	INSERT INTO @Elements
		(Value)
		SELECT
			SUBSTRING(@strValues,N + 1,CHARINDEX(',',@strValues,N + 1) - N - 1)
		FROM
			dbo.Tally
		WHERE
			N < LEN(@strValues)
			AND SUBSTRING(@strValues,N,1) = ','

    SELECT 
        @strArray = COALESCE(@strArray + @Delimiter,'')
        + CAST(CombinedValue AS NVARCHAR(MAX))
	FROM
		(
		SELECT DISTINCT  -- DISTINCT optional
			e.Value+','+t.TestValue AS CombinedValue
		FROM
			@Elements AS e
		INNER JOIN 
			dbo.TestJoinOutPut AS t
			ON e.Value = t.ID
		) AS Result
	ORDER BY
		CombinedValue		-- ORDER BY optional	
				
	SELECT @strArray AS TwoDimensionalArray
	


/* Example output converted to XML */

    DECLARE 
		 @strValues VARCHAR(MAX)
		,@Delimiter VARCHAR(10)
		,@strArray VARCHAR(MAX)
		,@strXML XML

	DECLARE @Elements TABLE 
        (
        Number INT IDENTITY(1,1)
        ,Value  VARCHAR(MAX)
        )
        		
	SET @Delimiter = '|'

    SELECT
        @strValues = COALESCE(@strValues + @Delimiter,'')
        + CAST(DelimitedStringInput AS VARCHAR(MAX))
	FROM
		dbo.TestStringInput

	SET @strValues = ','+REPLACE(@strValues,'|',',') +','

	INSERT INTO @Elements
		(Value)
		SELECT
			SUBSTRING(@strValues,N + 1,CHARINDEX(',',@strValues,N + 1) - N - 1)
		FROM
			dbo.Tally
		WHERE
			N < LEN(@strValues)
			AND SUBSTRING(@strValues,N,1) = ','
    
    SET @Delimiter = ''

    SELECT 
        @strArray = COALESCE(@strArray + @Delimiter,'')
        + CAST(CombinedValue AS NVARCHAR(MAX))
	FROM
		(
		SELECT DISTINCT		-- DISTINCT optional
			'<key>'+e.Value+'</key><value>'+t.TestValue+'</value>' AS CombinedValue
		FROM
			@Elements AS e
		INNER JOIN 
			dbo.TestJoinOutPut AS t
			ON e.Value = t.ID
		) AS Result
				
	SET	@strXML = CONVERT(XML,'<root>'+@strArray+'</root>')

	SELECT @strXML AS XMLOutput