H
H5N1
Hi there
It seems I've spotted interesting problem in a job I've got to do.
I have 3 tables: meals, employees, and intersecting table:
meals_for_employees with bool field meaning, that given employee wants
a given meal.
employees
-----------------
emp_id
name
meals
--------------
meal_id
name
meals_for_employees
-------------------------------
FK meal_id
FK emp_id
order: boolean
What I'd love to have on a asp.net 2.0 page is this:
| employee 1 | epmloyee 2 | employee 3 | epmloyee 4
| ...
meal1 | 0 | X | 0
| X |
meal2 | X | 0 | X
| 0 |
meal3 | 0 | 0 | 0
| 0 |
meal4 | X | X | 0
| 0 |
the problem is that the number of columns is dynamic, (equals to
"select count(*) from employees") so it cannot be represented with
straight SQL query.
I guess it's called a crosstab query.
My web research from last 15 minutes told me that there are basically 2
ways to solve it:
1) dynamic sql query
2) some people wrote, that Relational DBs are not meant to cope with
such data, and such table should be composed in the presentation tier
from some simpler queries.
in case of 1) outputting would be straightforward (stored proc->table
adapter->object data source->gridview) at least if I knew how to write
dynamic sql in SPRC (any helpful links appreciated) though writing
change values would be a bit harder, wouldn't it?
could someone sketch me a way to write changes back into a db?
in case of 2) I' guess I could compose the table (gridview) from single
columns which would be simple sql queries. but again, what about
editing, how should I instruct gridview to write changes to db
properly?
and third problem - how could I ommit necessity of hitting EDIT link in
each row to be updated? despite, I'd like to check/uncheck all
checkboxes and just hit some SAVE button, which would then save the
whole gridview back into db.
thanks for any suggestions of how would you attack this problem
cheers
HP
It seems I've spotted interesting problem in a job I've got to do.
I have 3 tables: meals, employees, and intersecting table:
meals_for_employees with bool field meaning, that given employee wants
a given meal.
employees
-----------------
emp_id
name
meals
--------------
meal_id
name
meals_for_employees
-------------------------------
FK meal_id
FK emp_id
order: boolean
What I'd love to have on a asp.net 2.0 page is this:
| employee 1 | epmloyee 2 | employee 3 | epmloyee 4
| ...
meal1 | 0 | X | 0
| X |
meal2 | X | 0 | X
| 0 |
meal3 | 0 | 0 | 0
| 0 |
meal4 | X | X | 0
| 0 |
the problem is that the number of columns is dynamic, (equals to
"select count(*) from employees") so it cannot be represented with
straight SQL query.
I guess it's called a crosstab query.
My web research from last 15 minutes told me that there are basically 2
ways to solve it:
1) dynamic sql query
2) some people wrote, that Relational DBs are not meant to cope with
such data, and such table should be composed in the presentation tier
from some simpler queries.
in case of 1) outputting would be straightforward (stored proc->table
adapter->object data source->gridview) at least if I knew how to write
dynamic sql in SPRC (any helpful links appreciated) though writing
change values would be a bit harder, wouldn't it?
could someone sketch me a way to write changes back into a db?
in case of 2) I' guess I could compose the table (gridview) from single
columns which would be simple sql queries. but again, what about
editing, how should I instruct gridview to write changes to db
properly?
and third problem - how could I ommit necessity of hitting EDIT link in
each row to be updated? despite, I'd like to check/uncheck all
checkboxes and just hit some SAVE button, which would then save the
whole gridview back into db.
thanks for any suggestions of how would you attack this problem
cheers
HP