Interesting CMS solution required

[anthonykallay]

Hi All,

I have been asked to uild an ASP.Net 2.0 CMS in c# for a client that
will display their products, not a problem for the majority! The main
stumbling lock i have run into is that each product has a range of
product codes that have different attributes associated with them, eg:

PRODUCT 1

Product Code Diameter Pack Size
pc1-1 12mm 100
pc1-2 14mm 200
pc1-3 100mm 300

PRODUCT 2

Product Code Width Height Depth
pc2-1 12m 10 12
pc2-2 140m 20 13
pc2-3 100m 30 30

as you can see each product code has any numer of associated
properties, for this reason i cannot just build a full table and
display the data as i normally would. My main method of thinking is to
build a database table that only has three columns ProductCode,
Property, Value

Is this a good idea, can i then display these values suitably grouped
in a table using ASP.Net??

Am i totally on the right/wrong track??, any advice or links to
related articles greatly appreciated as this isnt a problem i have
faced before.. But i am a quick learner

Many thanks in advance

Anthony

 

[Alexey Smirnov]

Hi All,

I have been asked to uild an ASP.Net 2.0 CMS in c# for a client that
will display their products, not a problem for the majority! The main
stumbling lock i have run into is that each product has a range of
product codes that have different attributes associated with them, eg:

PRODUCT 1

Product Code Diameter Pack Size
pc1-1 12mm 100
pc1-2 14mm 200
pc1-3 100mm 300

PRODUCT 2

Product Code Width Height Depth
pc2-1 12m 10 12
pc2-2 140m 20 13
pc2-3 100m 30 30

as you can see each product code has any numer of associated
properties, for this reason i cannot just build a full table and
display the data as i normally would. My main method of thinking is to
build a database table that only has three columns ProductCode,
Property, Value

Is this a good idea, can i then display these values suitably grouped
in a table using ASP.Net??

Am i totally on the right/wrong track??, any advice or links to
related articles greatly appreciated as this isnt a problem i have
faced before.. But i am a quick learner

Many thanks in advance

Anthony

ProductID Product Code
1 pc2-1
2 pc2-2
3 pc2-3

PropertyID Property Name
1 Width
2 Height

ProductID PropertyID Value
1 2 12m
2 2 14m

 

[anthonykallay]

ProductID Product Code
1 pc2-1
2 pc2-2
3 pc2-3

PropertyID Property Name
1 Width
2 Height

ProductID PropertyID Value
1 2 12m
2 2 14m- Hide quoted text -

- Show quoted text -

Hi alexey,

Thanks for your reply, that is similar to what i had in mind although
by using the many tables it will be easier to maintain good data
integrity, do you have any advice how to display this data effectively
in asp.net, how would i group the results together to produce a neat
looking table, would i do the manipulation in a stored procedure or
in .net???

Cheers
Anthony

 

[anthonykallay]

Hi alexey,

Thanks for your reply, that is similar to what i had in mind although
by using the many tables it will be easier to maintain good data
integrity, do you have any advice how to display this data effectively
in asp.net, how would i group the results together to produce a neat
looking table, would i do the manipulation in a stored procedure or
in .net???

Cheers
Anthony- Hide quoted text -

- Show quoted text -

Also could you please tell me what the correct term is for this kind
of data relationship so i can search more effectively on the topic

Cheers
Anthony

 

[Mark Rae]

would i do the manipulation in a stored procedure or in .net???

Generally speaking, you should try to use each layer of your app for what
it's best at...

Therefore, use your data layer for data storage and retrieval, your business
layer for business logic, and your presentation layer for making it all look
good...

 

[Alexey Smirnov]

Hi alexey,

Thanks for your reply, that is similar to what i had in mind although
by using the many tables it will be easier to maintain good data
integrity, do you have any advice how to display this data effectively
in asp.net, how would i group the results together to produce a neat
looking table, would i do the manipulation in a stored procedure or
in .net???

Cheers
Anthony- Hide quoted text -

- Show quoted text -

Of course, all this is a job for a database. It's up to you, if you
will generate sql on the fly or you will use a stored procedures. An
sql wouldn't be that complexed

E.g. for your one-table-approach

SELECT property, value FROM table WHERE productcode='pc2-1'

 

[anthonykallay]

Of course, all this is a job for a database. It's up to you, if you
will generate sql on the fly or you will use a stored procedures. An
sql wouldn't be that complexed

E.g. for your one-table-approach

SELECT property, value FROM table WHERE productcode='pc2-1'

Thanks for all your help with this but the bit i am now struggling
with is displaying the data, after running your query for example i
get

Product Code Property Value
pc2-1 Width 100
pc2-1 Height 50

When what i really want to display is

Product Code Width Height
pc2-1 100 50

How do i go about this final manipulation to make it a readable
table??

Cheers
Anthony

 

[Mark Rae]

Thanks for all your help with this but the bit i am now struggling
with is displaying the data, after running your query for example i
get

Product Code Property Value
pc2-1 Width 100
pc2-1 Height 50

When what i really want to display is

Product Code Width Height
pc2-1 100 50

How do i go about this final manipulation to make it a readable
table??

Aha - well, the term for what you require is a "crosstab query"...

I'm assuming you're using SQL Server, so here's how to do it:
http://www.google.co.uk/search?sour...4GGIH_en-GBGB220GB220&q="SQL+Server"+crosstab

 

[Alexey Smirnov]

When what i really want to display is

Product Code Width Height
pc2-1 100 50

How do i go about this final manipulation to make it a readable
table??

Anthony,

either build a stored procedure with a crosstab query, as Mark said,
or present a data using .NET. I'm not sure which approach will be the
best in your situation, you have to try and decide it. Take a look at
the following example of how it can be done using the ".NET way".

SELECT property, value FROM table WHERE productcode='pc2-1'
-------------------------------

HtmlTable t = new HtmlTable();
HtmlTableRow r1 = new HtmlTableRow();
HtmlTableRow r2 = new HtmlTableRow();

while (mySqlDataReader.Read())
{

HtmlTableCell c1 = new HtmlTableCell();
c1.Controls.Add(new
LiteralControl(mySqlDataReader["property"].ToString()));

HtmlTableCell c2 = new HtmlTableCell();
c2.Controls.Add(new
LiteralControl(mySqlDataReader["value"].ToString()));

r1.Cells.Add(c);
}

t.Rows.Add(r1);
t.Rows.Add(r2);
Place.Controls.Add(t);

It returns: