SubscribeTo in which table is data inserted

Vote:
 

I'm trying to locate which subscriptions users are subscribed to. I'm in a situation where I have to look in the tables.

However I cannot see in which table a given users subscriptions are saved into. 

I have following sql code that shows data for a given user. I want to extend this to also include the subscriptions the users is subscripted to. 

SELECT aspnet_Users.UserName, aspnet_Roles.RoleName
FROM aspnet_Users
JOIN aspnet_UsersInRoles
ON aspnet_UsersInRoles.UserId = aspnet_Users.UserId
JOIN aspnet_Roles
ON aspnet_Roles.RoleId = aspnet_UsersInRoles.RoleId
WHERE 
aspnet_UsersInRoles.RoleId = '70b760b7-100e-4993-b3fa-919116273294'
#146862
Mar 29, 2016 11:28
Vote:
 

I have the impression that subscription data is located in the aspnet_profile table inside the PropertyValuesString, hence I include this table inside my sql string

SELECT aspnet_Users.UserName, aspnet_Roles.RoleName, aspnet_Profile.PropertyValuesString 
FROM aspnet_Users

JOIN aspnet_Profile.UserId
ON aspnet_profile.UserId = aspnet_Users.UserId

JOIN aspnet_UsersInRoles
ON aspnet_UsersInRoles.UserId = aspnet_Users.UserId

JOIN aspnet_Roles
ON aspnet_Roles.RoleId = aspnet_UsersInRoles.RoleId

WHERE 
aspnet_UsersInRoles.RoleId = '70b760b7-100e-4993-b3fa-919116273294'

However the value of the PropertyValuesString is like this

<?xml version="1.0" encoding="utf-16"?>
<GuiSettings xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
  <ShowLanguageMissing>true</ShowLanguageMissing>
  <ShowLanguageFallback>true</ShowLanguageFallback>
  <ShowNew>true</ShowNew>
  <ShowNotPublished>true</ShowNotPublished>
  <ShowNotStarted>true</ShowNotStarted>
  <ShowExpired>true</ShowExpired>
  <ShowReadOnly>true</ShowReadOnly>
  <ShowNotVisibleInMenu>true</ShowNotVisibleInMenu>
  <ShowPageProviderIcon>true</ShowPageProviderIcon>
  <ShowShortcutIcon>true</ShowShortcutIcon>
  <ShowExternalIcon>true</ShowExternalIcon>
  <ShowInactiveIcon>true</ShowInactiveIcon>
  <ShowFetchDataIcon>true</ShowFetchDataIcon>
  <ShowEditingInProgressIcon>true</ShowEditingInProgressIcon>
  <ShowContainerPageIcon>true</ShowContainerPageIcon>
  <SettingsEnabled>false</SettingsEnabled>
</GuiSettings><?xml version="1.0" encoding="utf-16"?>
<ArrayOfString xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
  <string>/Global/Ikoner</string>
  <string>/Global/Ikoner/billetautomater</string>
  <string>/Global/Billeder/Epistore/Produkt - billeder</string>
</ArrayOfString>katrine@dsb.dk<?xml version="1.0" encoding="utf-16"?>
<SubscriptionInfo xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
  <SubscribedPages />
  <LastMessage>2011-02-02T01:47:47.5160506+01:00</LastMessage>
  <Interval>0</Interval>
</SubscriptionInfo>

I cannot see where in this xml subscription values should be located. There is ofcourse <SubscribedPages />, but this is empty, further more if not then how to retrieve the values in this element using sql.

#146863
Edited, Mar 29, 2016 12:00
Vote:
 

Hi,

What kind of subscriptions are we talking about? Episerver Mail?

#146866
Mar 29, 2016 13:32
Vote:
 

Yes it is mail subscriptions. 

Its when you want to subscribe to a specific kind of news

#146869
Mar 29, 2016 14:02
Vote:
 

I'm not that familiar with Episerver Mail, but I'm pretty sure subscriptions are stored in Mail specific tables. Not in the asp.net profile data.

#146870
Mar 29, 2016 14:04
Vote:
 

There is not such thing like mail specific tables. Besides this the xml output also show that there is a section for subscription data

<SubscriptionInfo xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
  <SubscribedPages />
  <LastMessage>2011-02-02T01:47:47.5160506+01:00</LastMessage>
  <Interval>0</Interval>
</SubscriptionInfo>

It only makes sense that subscription related info must be kept within above section. 

#146871
Mar 29, 2016 14:15
Vote:
 

I would recommend downloading the SDK from here http://world.episerver.com/documentation/Items/SDKchm/SDK-documentation-download---EPiServer-75-Mail/ and see if you can find out where the subscribers are stored.
I'm pretty sure they are not stored in the regular asp.net user and profile tables, but rahter in tables with "common" in the name.

Otherwise you're not using Episerver Mail.

#146873
Mar 29, 2016 14:27
Vote:
 

Do you have the Mail section in your global menu http://world.episerver.com/add-ons/episerver-mail/?

#146875
Edited, Mar 29, 2016 14:33
Vote:
 

Hi Johan 

We do not have that (episerver-mail), neither do we have tables with "common" in their name. 

We are using EPiServer v6.

#146876
Mar 29, 2016 15:16
Vote:
 

Ok, then I guess you are using the standard EPiServer Subscription functionality. Then you will either find it in tables in the

<SubscriptionInfo xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
  <SubscribedPages /> <!-- This node till have your information... -->
  <LastMessage>2011-02-02T01:47:47.5160506+01:00</LastMessage>
  <Interval>0</Interval>
</SubscriptionInfo>

like you have above. Or use the API if you don't like to parse xml...(if you got more than 1000 users you need to modify the below allow paging of the profiles...)

int totalRecords = 0;
var allProfiles = ProfileManager.GetAllProfiles(ProfileAuthenticationOption.All, 0, 1000, out totalRecords);

...loop over all profiles and get their subscriptions by
 
foreach (SubscriptionDescriptor subscribedPage in profile.SubscriptionInfo.SubscribedPages)
{
var page = DataFactory.Instance.GetPage(new PageReference(subscribedPage.PageID));
}

where profile is your EPiServer profile...

#146878
Edited, Mar 29, 2016 16:00
Vote:
 

Hi Daniel

Thanks for your suggestion, I'm trying to go that way, since it seem the only possible solution. However what I dont get the Episerver profile. Is this supposed to be in the web.config. This is what I could find there. 

 <profile enabled="true" defaultProvider="SqlProfile" automaticSaveEnabled="true">

How / where should I get the episerver profile?

#146986
Apr 01, 2016 13:22
Vote:
 

That's the configuration for how the profile is stored yes. Do you have access to the source code? Or at least the site where it is running?
If I had been in your seat I would probably have done a custom scheduled job for EPiServer that exports that profile information about subscriptions. For that you don't even need the actual source code of the website and it's reusable if you need to do it again. You do need some coding skills in EPiServer and .NET though

#146989
Edited, Apr 01, 2016 14:45
Vote:
 

Hi Daniel

You code regarding getting the profiles, did the trick. 

var allProfiles = ProfileManager.GetAllProfiles(ProfileAuthenticationOption.All, 0, 1000, out totalRecords);
 
  
foreach (SubscriptionDescriptor subscribedPage in profile.SubscriptionInfo.SubscribedPages)

 Thanks for our help

#147025
Apr 04, 2016 18:20
Vote:
 

Sweet!

#147027
Apr 04, 2016 22:23
This topic was created over six months ago and has been resolved. If you have a similar question, please create a new topic and refer to this one.
* You are NOT allowed to include any hyperlinks in the post because your account hasn't associated to your company. User profile should be updated.