Displaying Member info including Roles using Sql Membership Provider

Question

I wish to create a summary table of all users with the database tables setup according to SqlMembershipProvider schema. So far, I have:

<asp:GridView ID="UserAccounts" runat="server" AutoGenerateColumns="False">
    <Columns>
        <asp:BoundField DataField="UserName" HeaderText="UserName" />
        <asp:BoundField DataField="Email" HeaderText="Email" />
        <asp:TemplateField HeaderText="Role">
            <ItemTemplate>
                <asp:TextBox ID="RoleText" ReadOnly="true" runat="server">
                </asp:TextBox>
            </ItemTemplate>
        </asp:TemplateField>
    </Columns>
</asp:GridView>

for the table, and codebehind:

UserAccounts.DataSource = Membership.GetAllUsers();
UserAccounts.DataBind();

To generate the list of users. However, as those of you who are familiar with SQLmembershipprovider, the roles associated with users are housed in multiple separate tables. Therefore, I'm not sure how to retrieve that information and have it display beside each user.

The solution I am currently thinking of is to somehow traverse the table row by row and then calling Roles.GetRolesForUser() on that username to retrieve the information and then binding that to the table. However, I'm not sure of exactly how to do this and if it's even the most efficient method.

I have edited your title. Please see, "[Should questions include “tags” in their titles?](http://meta.stackexchange.com/questions/19190/)", where the consensus is "no, they should not". — John Saunders, Sep 20 '12 at 18:34

score 0 · Answer 1 · answered Sep 20 '12 at 17:24

You can this method and with DataTable

public DataSet CustomGetAllUsers()    {
        DataSet ds = new DataSet();
        DataTable dt = new DataTable();
        dt = ds.Tables.Add("Users");

        MembershipUserCollection muc;
        muc = Membership.GetAllUsers();

        dt.Columns.Add("UserName", Type.GetType("System.String"));
        dt.Columns.Add("Email", Type.GetType("System.String"));
        dt.Columns.Add("CreationDate", Type.GetType("System.DateTime"));

        /* Here is the list of columns returned of the Membership.GetAllUsers() method
         * UserName, Email, PasswordQuestion, Comment, IsApproved
         * IsLockedOut, LastLockoutDate, CreationDate, LastLoginDate
         * LastActivityDate, LastPasswordChangedDate, IsOnline, ProviderName
         */

        foreach(MembershipUser mu in muc) {
            DataRow dr;
            dr = dt.NewRow();
            dr["UserName"] = mu.UserName;
            dr["Email"] = mu.Email;
            dr["CreationDate"] = mu.CreationDate;
            dt.Rows.Add(dr);
        }
        return ds;
    }

Bind :

UserAccounts.DataSource = CustomGetAllUsers().Tables[0] ;
UserAccounts.DataBind();

You can also adapt your ItemTemplate with Eval.DataBinder

This is a good idea. However, I can't help but wonder if there is a way of merging MembershipUserCollection muc with another datatable given the new datatable has one column in common with muc (e.g. username) and other columns created using data from elsewhere? — Tony, Sep 20 '12 at 17:38
Yes you can merge, you add columns in this table and you import data from another source — Aghilas Yakoub, Sep 20 '12 at 17:39

score 0 · Accepted Answer · edited Sep 20 '12 at 18:35

0

Try binding to

    var userRoles = from MembershipUser user in Membership.GetAllUsers()
                    from role in Roles.GetRolesForUser(user.UserName)
                    select new { 
                        UserName = user.UserName, Email = user.Email, Role = role };

or

    var userRoles = from MembershipUser user in Membership.GetAllUsers()
                    let roles = Roles.GetRolesForUser(user.UserName)
                    select new {
                        UserName = user.UserName, 
                        Email = user.Email,
                        Roles = string.Join(", ",  roles) };

edited Sep 20 '12 at 18:35

John Saunders

160,644
26
247
397

answered Sep 20 '12 at 17:38

JamieSee

12,696
2
31
47

Simple and worked great. Thanks. EDIT: Is it possible to add another column from a different database table that can't be accessed as easily as using Roles.GetROlesForUser? Btw is this technique called LINQ? – Tony Sep 20 '12 at 18:18
Yes this is using LINQ. I highly recommend looking at http://code.msdn.microsoft.com/101-LINQ-Samples-3fb9811b for examples of how queries can be crafted. Yes, you can pull things from tables this way as well if you combine it with Entity Framework or LINQ to SQL. Edit your question with more details on what you want to pull and I'll try to look tomorrow. – JamieSee Sep 20 '12 at 23:18

Displaying Member info including Roles using Sql Membership Provider

2 Answers2