A typical an easy approach will be to have a DropDownList with the users on top, and probably a GridView with the permissions available for that user, and a way to add or remove records from the UserPermissions GridView.
But what I would like to do is to provide a more flexible and easy user interface where you could set the permissions by checking/unchecking a box per user, per permission. Sort of a pivot table fashion. And then internally and programmatically, create or
remove the records from the UserPermissions table, accordingly based on the user input.
A GridView something like this:
PermissionID User 1 User 2 User 3
1 TRUE TRUE FALSE
2 TRUE FALSE FALSE
3 TRUE FALSE FALSE
(The TRUE and FALSE values are actually checkboxes.)
The reason of my requirement is that there are going to be very few users (1 to 7 top) and lots of permissions (dozens, even hundreds) so the first approach about selecting the user and then adding or removing records becomes very frustrating and unmanageable.
Also, the User list (User 1, User 2 and User 3) changes (depending on a fourth table: Company) so the GridView should allow dynamically generated columns.
Any suggestions are very welcome and appreciated. If you need more details or information, please let me know.
I think your first solution is the better choice. To me the second solution becomes unmanageable once you start adding users and the list of permissions get into the 100s. I could see easily loosing track of the column\user being edited once you start
scrolling down to set a permission.
I don’t know what permission information you wish to show besides the name so if this was my project and I was only going to show the name then I’d go with a dropdown list for user selection and a CheckBoxList, instead of the Gridview, control for the permissions.
That way you could display the permissions horizontally in columns allowing you to see more of them on the screen without scrolling. Also, if you are able to group permission by category then I would do that and have another dropdown list, under the users
list, that would allow the user to select permissions by category. This, in my opinion, would make your app cleaner.
With this approach, you would load all the permissions into the CheckBoxList by Value(id)\Item(Name). The you get the id's of the selected user, loop through the CheckBoxList Items, and check/select where the Id's match. Then to save the records, I would
delet all of the existing records for the current user and then insert new selected permission id's. This may not be the best approach but it has always worked for men in cases like that.
Not really but thanks. From what I read before, when you use the Pivot operator in SQL Server, you need to know in advanced the number of columns. In my case that's not known and is
dynamically generated base on the company selected. Plus, it seems you need to perform an aggregation, which is not needed in my case.
payini
Member
43 Points
7 Posts
How to do a Pivot-Like GridView with Dinamically Generated Columns base on rows.
Oct 22, 2009 07:09 PM|LINK
Hello All,
After a lot of research and tries I finally gave up and decided to post a question to the experts..... You guys...
Three tables are involved in my problem: Users, Permissions, UserPermissions. (This is a simplified version for simplicity.)
Users Table
UserID Name
1 User 1
2 User 2
3 User 3
Permissions Table
PermissionID Name
1 Permission 1
2 Permission 2
3 Permission 3
UserPermissions Table
UserID PermissionID
1 1
1 2
1 3
2 1
A typical an easy approach will be to have a DropDownList with the users on top, and probably a GridView with the permissions available for that user, and a way to add or remove records from the UserPermissions GridView.
But what I would like to do is to provide a more flexible and easy user interface where you could set the permissions by checking/unchecking a box per user, per permission. Sort of a pivot table fashion. And then internally and programmatically, create or remove the records from the UserPermissions table, accordingly based on the user input.
A GridView something like this:
PermissionID User 1 User 2 User 3
1 TRUE TRUE FALSE
2 TRUE FALSE FALSE
3 TRUE FALSE FALSE
(The TRUE and FALSE values are actually checkboxes.)
The reason of my requirement is that there are going to be very few users (1 to 7 top) and lots of permissions (dozens, even hundreds) so the first approach about selecting the user and then adding or removing records becomes very frustrating and unmanageable.
Also, the User list (User 1, User 2 and User 3) changes (depending on a fourth table: Company) so the GridView should allow dynamically generated columns.
Any suggestions are very welcome and appreciated. If you need more details or information, please let me know.
Thanks,
Gerardo Melendrez
Naom
All-Star
36004 Points
7901 Posts
Re: How to do a Pivot-Like GridView with Dinamically Generated Columns base on rows.
Oct 22, 2009 07:37 PM|LINK
Not an exact answer, but see if you can achieve what you need using sample from http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/169266c9-28ed-4271-b116-c81185a3d445
(Donald Knuth)
Visit my blog
Microsoft Community Contributor 2011-12
justenough
Member
151 Points
60 Posts
Re: How to do a Pivot-Like GridView with Dinamically Generated Columns base on rows.
Oct 22, 2009 07:58 PM|LINK
Gerardo
I think your first solution is the better choice. To me the second solution becomes unmanageable once you start adding users and the list of permissions get into the 100s. I could see easily loosing track of the column\user being edited once you start scrolling down to set a permission.
I don’t know what permission information you wish to show besides the name so if this was my project and I was only going to show the name then I’d go with a dropdown list for user selection and a CheckBoxList, instead of the Gridview, control for the permissions. That way you could display the permissions horizontally in columns allowing you to see more of them on the screen without scrolling. Also, if you are able to group permission by category then I would do that and have another dropdown list, under the users list, that would allow the user to select permissions by category. This, in my opinion, would make your app cleaner.
With this approach, you would load all the permissions into the CheckBoxList by Value(id)\Item(Name). The you get the id's of the selected user, loop through the CheckBoxList Items, and check/select where the Id's match. Then to save the records, I would delet all of the existing records for the current user and then insert new selected permission id's. This may not be the best approach but it has always worked for men in cases like that.
payini
Member
43 Points
7 Posts
Re: How to do a Pivot-Like GridView with Dinamically Generated Columns base on rows.
Oct 22, 2009 08:48 PM|LINK
Naomi,
Not really but thanks. From what I read before, when you use the Pivot operator in SQL Server, you need to know in advanced the number of columns. In my case that's not known and is dynamically generated base on the company selected. Plus, it seems you need to perform an aggregation, which is not needed in my case.
Thanks,
Naom
All-Star
36004 Points
7901 Posts
Re: How to do a Pivot-Like GridView with Dinamically Generated Columns base on rows.
Oct 22, 2009 08:56 PM|LINK
That's not correct. See http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/169266c9-28ed-4271-b116-c81185a3d445 from the SQLUSA answers.
Same thread I already pointed you too, as I see.
Though I like the other answerer reply for your case.
(Donald Knuth)
Visit my blog
Microsoft Community Contributor 2011-12
payini
Member
43 Points
7 Posts
Re: How to do a Pivot-Like GridView with Dinamically Generated Columns base on rows.
Oct 22, 2009 09:01 PM|LINK
Yeah, I looked at the thread, and actually was taking a second look to the Dynamic SQL Examples (Fifth Example.) Thanks.
I was thinking about "justenough" answer as well. That sounds actually right.
I will give that a try.
Thanks,
payini
Member
43 Points
7 Posts
Re: How to do a Pivot-Like GridView with Dinamically Generated Columns base on rows.
Oct 22, 2009 09:50 PM|LINK
I think you are right Jason,
I going to do that. Specially with the ability to select the category since I do have that.
Thanks,
justenough
Member
151 Points
60 Posts
Re: How to do a Pivot-Like GridView with Dinamically Generated Columns base on rows.
Oct 23, 2009 07:22 PM|LINK
let me know if you need any help.
payini
Member
43 Points
7 Posts
Re: How to do a Pivot-Like GridView with Dinamically Generated Columns base on rows.
Oct 23, 2009 08:23 PM|LINK
Thanks, I appreaciate that.
Thanks,