asp.net c# concept question

[Howard]

I need to write a web page that outputs a table from database. ( i want to
write my own code, don't want to use the built-in control)
I came up with two ways of doing this (i use c# 2.0)
1.
pesudo code

sqlstringTable = "select category_name from table1"
execute(sqlstringTable)

while(read)
{
output category name

sqlstringTable = "select * from table2"
execute(sqlstringTable2)
while(read)
{
output values of all cell in this row
}
}


2.
sqlstringTable = "select category_name from table1"
execute(sqlstringTable)

while(read)
{
store information to <list>category
}

for (i=0 i<=size of <list>category; i++
{
sqlstringTable = "select * from table2"
execute(sqlstringTable2)
while(read)
{
store to <list>row
}
}

use nested for loops to output <list>category with corresponding <list>row

Method 1 is how i would normally write this. but since im getting into c#
2.0 i want to try out this new feature. did i understand this correctly?
Also any memory used to store <list> will be released after the page is
loaded correct?

Howard

 

[Nick Malik [Microsoft]]

Hello Howard,

Your two methods both involve issuing two queries to the database and
joining the data in your app. That doesn't make any sense at all.
Simply issue a SQL statement that joins the 'category' rows and 'table2'
rows into a single resultset, and then fill your table from that data.
SQL is going to be considerably easier to write the code and considerably
easier to manage the results than either of the two methods you describe.

I don't know what database you have under the covers or what database schema
you have. Assuming your category table has a category name and category id,
and the item table has a category id in it to form the relationship, the
join would look something like this. I added a 'where' clause to show you
where one would go, but your examples did not have one, so feel free to drop
it if this is not needed.

string sSql = "Select ct.category_name, ct.category_id, it.item_name,
it.item_id, it.quantityonhand from category ct inner join item it on
ct.category_id = it.category_id where it.quantityonhand > 0 order by
ct.category_name desc";

(caveat: air code at 11:30pm... check the syntax in the SQL environment of
your choice before coding this into your app)
--
--- Nick Malik [Microsoft]
MCSD, CFPS, Certified Scrummaster
http://blogs.msdn.com/nickmalik

Disclaimer: Opinions expressed in this forum are my own, and not
representative of my employer.
I do not answer questions on behalf of my employer. I'm just a
programmer helping programmers.
--

I need to write a web page that outputs a table from database. ( i want to
write my own code, don't want to use the built-in control)
I came up with two ways of doing this (i use c# 2.0)
1.
pesudo code

sqlstringTable = "select category_name from table1"
execute(sqlstringTable)

while(read)
{
output category name

sqlstringTable = "select * from table2"
execute(sqlstringTable2)
while(read)
{
output values of all cell in this row
}
}


2.
sqlstringTable = "select category_name from table1"
execute(sqlstringTable)

while(read)
{
store information to <list>category
}

for (i=0 i<=size of <list>category; i++
{
sqlstringTable = "select * from table2"
execute(sqlstringTable2)
while(read)
{
store to <list>row
}
}

use nested for loops to output <list>category with corresponding <list>row

Method 1 is how i would normally write this. but since im getting into c#
2.0 i want to try out this new feature. did i understand this correctly?
Also any memory used to store <list> will be released after the page is
loaded correct?

Howard

--
--- Nick Malik [Microsoft]
MCSD, CFPS, Certified Scrummaster
http://blogs.msdn.com/nickmalik

Disclaimer: Opinions expressed in this forum are my own, and not
representative of my employer.
I do not answer questions on behalf of my employer. I'm just a
programmer helping programmers.
--

 

[Karl Seguin [MVP]]

In addition to the ability to do this in a single query, I have concerns
with both approaches as well. With respect to the 2nd approach, using a
generic or some type of collection, there's only a benefit to be had if you
need to manipulate or re-iterate the data during that function. From your
example that doesn't seem to be the case. In your example there's no benefit
to throwing it into a generic as the work is simply thrown away a few
microseconds later. Now if you decide to cache the data, then you're on to
something!

There's also no reason to avoid the pre-built controls. As is, whatever
method you chose to output your data is going to be considerably less
maintanable and clean than using something like a Repeater. A bunch of
Response.Write's, server-side object creation or string concatenation is not
the right way.

Karl