How to Get Number of Users in Membership Role

[Jonathan Wood]

Greetings,

I'm using ASP.NET membership and I'd like to query the number of users in a
particular role.

I don't want the overhead of returning a dataset and then getting the number
of items in it. I'd like to create a stored procedure for maximum efficiency
that returns the number of users in a particular role.

It appears that role IDs are encrypted in the membership tables so I'm not
really sure where to start. I was hoping this wouldn't be that complicated
as I'm pretty new to this.

Thanks for any tips.

 

[sloan]

Huh?

Try these. A RoleId is a uniqueidentifier


select top 1 * from dbo.aspnet_Roles IsAnonymous

--------------------------------------------------------------------------------------------------------------------------------
---------------------------------------- ---- ----------------------------------------
---- ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
---- ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
---- ------------------ ---- ----------- ---- ----------------------------------------
----

select top 1 * from dbo.aspnet_Users

--------------------------------------------------------------- ----------------------------------------
---- ---------------------------------------- ----

select top 1 * from dbo.aspnet_UsersInRoles

--------------------------------------------------------------- ----------------------------------------
---- ---------------------------------------- ----

declare @RoleId uniqueidentifier

select @RoleId = (select top 1 RoleId from dbo.aspnet_Roles)

print @RoleID

select count(*) as ThisRoleCount from dbo.aspnet_UsersInRoles where RoleId =
@RoleId

declare @RoleName varchar(12)

select @RoleName = (select top 1 RoleName from dbo.aspnet_Roles)

print @RoleName

select count(*) as ThisRoleCount from dbo.aspnet_UsersInRoles link

join dbo.aspnet_Roles roles on link.RoleId = roles.RoleId

where RoleName = @RoleName



select users.UserId , users.UserName from dbo.aspnet_UsersInRoles link

join dbo.aspnet_Roles roles on link.RoleId = roles.RoleId

join dbo.aspnet_Users users on link.UserId = users.UserId

where RoleName = @RoleName

 

[Larry Bud]

Greetings,

I'm using ASP.NET membership and I'd like to query the number of users in a
particular role.

I don't want the overhead of returning a dataset and then getting the number
of items in it. I'd like to create a stored procedure for maximum efficiency
that returns the number of users in a particular role.

It appears that role IDs are encrypted in the membership tables so I'm not
really sure where to start. I was hoping this wouldn't be that complicated
as I'm pretty new to this.

Thanks for any tips.

Very simple. No need for a stored procedure...

Imports System.Web.Security
dim count as integer = roles.GetUsersInRole("rolename").Length

 

[Jonathan Wood]

Larry,

Very simple. No need for a stored procedure...

Imports System.Web.Security
dim count as integer = roles.GetUsersInRole("rolename").Length

Perhaps I'm missing something here.

My understanding is that, not only does this approach load the entire
dataset of users of a particular role into memory, it then makes a second
copy of that data in an in-memory collection, before finally getting the
number of items in that collection. This strikes me as horribly inefficent,
and would not meet my stated objectives.

Thanks.

 

[Larry Bud]

Larry,








Perhaps I'm missing something here.

My understanding is that, not only does this approach load the entire
dataset of users of a particular role into memory, it then makes a second
copy of that data in an in-memory collection, before finally getting the
number of items in that collection. This strikes me as horribly inefficent,
and would not meet my stated objectives.

I cannot comment on how it works, sorry, but I doubt they'd go through
the trouble of making all these functions that are so inefficient as
to be useless.

Maybe a Microsoft guy can chime in.

 

[sloan]

My queries showed you how to get the info, without "full population".

Did they not meet your objective? Did you look at them?

......

I'd still try to use the included methods...How many users/roles do you
have?
I don't think a <100 users/roles is a big deal.....


But my queries show you how to get around the default "full
population".........

 

[Jonathan Wood]

sloan,

My queries showed you how to get the info, without "full population".

Did they not meet your objective? Did you look at them?

I'm sorry, I'm a bit confused. You're responding to Larry but I think you
may be directing your comments at me.

Larry's response did not meet my objective because it required loading a
dataset when all I wanted was the count.

I'm sorry for not responding to your post. Unfortunately, I didn't
understand that well what you were saying. I'm quite new to SQL and wasn't
clear on some things. Then, when I started looking at it, I see that the
membership database includes an aspnet_UsersInRoles_GetUsersInRoles
procedure, which I was toying with modifying to just return the number of
items.

I'd still try to use the included methods...How many users/roles do you
have?
I don't think a <100 users/roles is a big deal.....

I have no reason to think my project will be limited to 100 users per role.

But my queries show you how to get around the default "full
population".........

Yes, I meant to get back to that. I need to look at it some more.

Thanks.

 

[Jonathan Wood]

sloan,

I'm trying to figure this out.

First, I'm not sure what all the -------------- means.

Then, although my knowledge of SQL is very limited, it seems to be doing
much more than simply getting the count.

I guess you are just giving me a couple of samples that do different things.
If so, I appreciate that. The part I see right off is that I need a way to
convert the role name to the role ID. It seems like you kind of show that,
although there is no asp_Roles table.

Thanks.

 

[Jonathan Wood]

Excuse me, there is an aspnet_Roles table. Sorry.

 

[Jonathan Wood]

Based on your code, I came up with the following:

ALTER PROCEDURE dbo.mc_CountUsersInRole
@ApplicationName nvarchar(256),
@RoleName nvarchar(256)
AS
BEGIN
DECLARE @ApplicationId uniqueidentifier
SELECT @ApplicationId = NULL
SELECT @ApplicationId = ApplicationId FROM aspnet_Applications WHERE
LOWER(@ApplicationName) = LoweredApplicationName
IF (@ApplicationId IS NULL)
RETURN(0)

DECLARE @RoleId uniqueidentifier
SELECT @RoleId = NULL
SELECT @RoleId = RoleId FROM aspnet_Roles WHERE LOWER(@RoleName) =
LoweredRoleName
IF (@ApplicationId IS NULL)
RETURN(0)

DECLARE @UsersInRole int
SELECT COUNT(*) AS UsersInRole FROM aspnet_UsersInRole WHERE RoleId =
@RoleId
RETURN @UsersInRole;
END

I'd be interested in anyone's feedback from this, my first stored procedure.

I'm also still trying to determine how I read back the returned value in my
code.

Thanks.

 

[sloan]

Yeah, my examples were based on...sometimes you know the RoleName, sometimes
the RoleId.
I was giving you examples of both.


Corrected version below.

The "mc_" is a weird naming convention as well.


uspCountUsersInRole might be a better option (usp = "user stored procedure")


I've included a second version, which is 1 longer sql statement, but does
the same job.
Google
"derived tables" "Sql Server"
if you want to understand that approach.




ALTER PROCEDURE dbo.mc_CountUsersInRole

@ApplicationName nvarchar(256),

@RoleName nvarchar(256)

AS

BEGIN

DECLARE @ApplicationId uniqueidentifier

SELECT @ApplicationId = NULL

SELECT @ApplicationId = ApplicationId FROM dbo.aspnet_Applications WHERE

LOWER(@ApplicationName) = LoweredApplicationName

IF (@ApplicationId IS NULL)

RETURN(0)

DECLARE @RoleId uniqueidentifier

SELECT @RoleId = NULL

SELECT @RoleId = RoleId FROM dbo.aspnet_Roles roles WHERE LOWER(@RoleName) =
LoweredRoleName

AND roles.ApplicationId = @ApplicationId --<<You need this extra where
condition as well

IF (@RoleId IS NULL)--<<Corrected Here

RETURN(0)

DECLARE @UsersInRole int

SELECT @UsersInRole = COUNT(*) FROM dbo.aspnet_UsersInRole WHERE RoleId =
@RoleId--<<Corrected setting the @UsersInRole variable Here

RETURN @UsersInRole;

END


--------------------ALT VERSION




/*

declare @count int

exec @count = dbo.mc_CountUsersInRole 'App1' , 'Role1'

print @count

exec @count = dbo.mc_CountUsersInRole 'doesnotExist' , 'doesNotExist'

print @count

*/



ALTER PROCEDURE dbo.mc_CountUsersInRole

@ApplicationName nvarchar(256),

@RoleName nvarchar(256)

AS

BEGIN



DECLARE @UsersInRole int

SELECT @UsersInRole = NULL



SELECT @UsersInRole = COUNT(*) FROM dbo.aspnet_UsersInRoles uir

join

(

SELECT RoleId , ApplicationId FROM dbo.aspnet_Roles roles WHERE
LOWER(@RoleName)= roles.LoweredRoleName

) as derived1

on uir.RoleId = derived1.RoleId


join

(

SELECT ApplicationId FROM dbo.aspnet_Applications apps WHERE
LOWER(@ApplicationName) = apps.LoweredApplicationName

) as derived2

on derived1.ApplicationId = derived2.ApplicationId





if(@UsersInRole IS NULL)

begin

select @UsersInRole = 0 --default value

end





RETURN @UsersInRole;

END

 

[Jonathan Wood]

sloan,

Yeah, my examples were based on...sometimes you know the RoleName,
sometimes the RoleId.
I was giving you examples of both.

I see. I was just a little slow figuring out what you gave me.

The "mc_" is a weird naming convention as well.

Well, everything else has an "aspnet_" prefix, "mc" stands for my particular
app.

I've included a second version, which is 1 longer sql statement, but does
the same job.

Thanks. I'll check it out. But your last post actually gave me the shot in
the arm I need and it's actually working now. I feel much better now.

Google
"derived tables" "Sql Server"
if you want to understand that approach.

Will do.

Thanks.

 

[Jonathan Wood]

sloan,

One other things:

SELECT @UsersInRole = COUNT(*) FROM dbo.aspnet_UsersInRole WHERE RoleId =
@RoleId--<<Corrected setting the @UsersInRole variable Here

RETURN @UsersInRole;

This doesn't work for me. But the following does:

SELECT COUNT(*) AS UsersInRole FROM aspnet_UsersInRoles WHERE RoleId =
@RoleId
RETURN @UsersInRole;

Here's how I'm accessing the result. Is there a better way?

public static int GetNumUsersInRole(string role)
{
var conn = new
SqlConnection(ConfigurationManager.ConnectionStrings["ASPNETDB"].ToString());

try
{
var cmd = new
SqlCommand(ConfigurationManager.ConnectionStrings["ASPNETDB"].ToString());
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "mc_CountUsersInRole";
cmd.Parameters.AddWithValue("@RoleName", role);
cmd.Connection = conn;
cmd.Connection.Open();
var reader = cmd.ExecuteReader();
reader.Read();
return (int)reader[0];
}
catch
{
}
// Something went wrong
return 0;
}

 

[Jonathan Wood]

Sloan,

Sorry to email you directly. I was really hoping you could clarify one of
the points you raised in the newsgroup.

I posted a reply in the newsgroup. Here's what it said:

One other things:

SELECT @UsersInRole = COUNT(*) FROM dbo.aspnet_UsersInRole WHERE RoleId =
@RoleId--<<Corrected setting the @UsersInRole variable Here

RETURN @UsersInRole;

This doesn't work for me. But the following does:

SELECT COUNT(*) AS UsersInRole FROM aspnet_UsersInRoles WHERE RoleId =
@RoleId
RETURN @UsersInRole;

Here's how I'm accessing the result. Is there a better way?

public static int GetNumUsersInRole(string role)
{
var conn = new
SqlConnection(ConfigurationManager.ConnectionStrings["ASPNETDB"].ToString());

try
{
var cmd = new
SqlCommand(ConfigurationManager.ConnectionStrings["ASPNETDB"].ToString());
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "mc_CountUsersInRole";
cmd.Parameters.AddWithValue("@RoleName", role);
cmd.Connection = conn;
cmd.Connection.Open();
var reader = cmd.ExecuteReader();
reader.Read();
return (int)reader[0];
}
catch
{
}
// Something went wrong
return 0;
}

 

[sloan]

First, you do need to pass in the ApplicationName.

I believe the same RoleName can exist under more than 1 Application.

So include @ApplicationName in the DotNet code.

.........

The connection string looks correct, as in..how you're reading it.



There are several ways to get the return value.

You need to search
ExecuteScalar
ExecuteNonQuery (but with "out" parameters.
and
RETURN_VALUE

var reader = cmd.ExecuteReader();
reader.Read();
return (int)reader[0];

Since you are not getting a resultset (multiple row). this code will not
work like you want.


Go to this article:
http://aspnet.4guysfromrolla.com/articles/062905-1.aspx

Go toward the end of the article and find
Dim retValParam as New SqlParameter("@RETURN_VALUE", SqlDbType.Int)



and you'll figure out the correct syntax I believe.



PS
I would not "swallow" the exception like youre doing.

write a try/finally block instead

try
{
//your code here
}
finally
{
//you can leave this empty if need be.....
}

Sloan,

Sorry to email you directly. I was really hoping you could clarify one of
the points you raised in the newsgroup.

I posted a reply in the newsgroup. Here's what it said:

One other things:

SELECT @UsersInRole = COUNT(*) FROM dbo.aspnet_UsersInRole WHERE RoleId
= @RoleId--<<Corrected setting the @UsersInRole variable Here

RETURN @UsersInRole;

This doesn't work for me. But the following does:

SELECT COUNT(*) AS UsersInRole FROM aspnet_UsersInRoles WHERE RoleId =
@RoleId
RETURN @UsersInRole;

Here's how I'm accessing the result. Is there a better way?

public static int GetNumUsersInRole(string role)
{
var conn = new
SqlConnection(ConfigurationManager.ConnectionStrings["ASPNETDB"].ToString());

try
{
var cmd = new
SqlCommand(ConfigurationManager.ConnectionStrings["ASPNETDB"].ToString());
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "mc_CountUsersInRole";
cmd.Parameters.AddWithValue("@RoleName", role);
cmd.Connection = conn;
cmd.Connection.Open();
var reader = cmd.ExecuteReader();
reader.Read();
return (int)reader[0];

}
catch
{
}

finally
{
//i would not use an IDataReader..but FYI, if you ever do ... you
need to close it when you're done


if(null!=reader)
{
reader.Close();
}
}

 

[Jonathan Wood]

sloan,

First, you do need to pass in the ApplicationName.

I eliminated it from my stored procedure since there will only be one
application associated with this database.

There are several ways to get the return value.

You need to search
ExecuteScalar
ExecuteNonQuery (but with "out" parameters.
and
RETURN_VALUE

var reader = cmd.ExecuteReader();
reader.Read();
return (int)reader[0];

Since you are not getting a resultset (multiple row). this code will not
work like you want.

I'm really confused with this as it seems to work perfectly. I couldn't find
how to get an int result from ExecuteNonQuery, I will search it for out
parameters.

But what I'm most confused about is the SQL syntax:

======================
SELECT COUNT(*) AS UsersInRole FROM aspnet_UsersInRoles WHERE RoleId =
@RoleId
RETURN @UsersInRole;

SELECT @UsersInRole = COUNT(*) FROM dbo.aspnet_UsersInRole WHERE RoleId =
@RoleId
RETURN @UsersInRole;
======================

Only the second version works for me but you recommend the second. I would
really like to understand that.

Go to this article:
http://aspnet.4guysfromrolla.com/articles/062905-1.aspx

Go toward the end of the article and find
Dim retValParam as New SqlParameter("@RETURN_VALUE", SqlDbType.Int)

Thanks, I'll study that.

I would not "swallow" the exception like youre doing.

write a try/finally block instead

try
{
//your code here
}
finally
{
//you can leave this empty if need be.....
}

There's obviously something else I don't know because I'm not aware of any
advantage to this if the finally block is left empty. Does this serve a
purpose?

Thanks.