I hope someone can help me here. I am developing a Web Service that selects information from the DNN Database and sends it to a desktop application as a DataSet. Let me keep it simple. I Send the Web Service a Portal Number and it must return ALL Users in THAT Portal along with their RoleNames and Role ID's Comaa Delimited as well as their Email, Telephone and Cell Number. I would ideally like to do this in ONE SQL Statement so if anyone can help, I would really appreciate it. I am confused and have tried for a few days so please, please, ANY help would VERY helpful.

The problem with the simple SQL statement you mention is that it is everything but simple :D

If what you want is to return a resultset containing the user information you mention AND the roles as a single, comma delimited field I dont think that you cant get away with it without using a SQL cursor. You can check http://www.sql-server-helper.com/tips/comma-delimited-output.aspx to get some more information in how you can setup your query to return the roles as commad delimited. Once you have this part done, retrieving profile information is a piece of cake.

Another way would be to exploit dnns to do the dirty job for you. You could for instance, invoke the GetUsers function to retrieve the collection of users as UserInfo classes, where one of its propertys is precisely the Profile. The same UserInfo class contains a collection of roles for that User in the Roles property, which you can also sweep to assemble the list as a single comma delimited string. Once you have all this you can assemble your own data table which you can return in your webservice.

The only problem with the latter approach is that you would make several calls to the db for each user... which can affect performance a bit. If youre not planning to use the webservice too often it shouldnt be a problem.

Cheers,

I implemented similar to what Bryan describes. This query you put in a dataset and then the dataset goes into a webservice you can call. If you want to avoid that data has been changed during the separate sql calls, you need to define a temp table in which you for instance collect first the users, then you do a select on users, then a select on roles. In this way the complete dataset is filled.

I would recommend not to us the properties of DNN since you get very nastly and slow queries to get the data out.

J.