Block data from Database query

Vote:
 

Hello,

I have a page type which has placeholder to hold list of blocks (of a specific type). These blocks have a property type xhtmlstring.

I need to create a database query to get content id, content name, the xhtmlstring from each block that is on the page.

I followed https://world.episerver.com/blogs/K-Khan-/Dates/2013/4/EpiServer-CMS-6-Useful-SQL-Queries/ and got an idea of the queries and was able to fetch all the properties and data from a page but can't find a way to get blocks data on the pages.

Any inputs on how I can form the query to get this data

#230869
Nov 16, 2020 18:03
Vote:
 

What kind of property do you use on your page,  to reference the blocks? You'll need to parse that property's content.

Another approach would be using tblContentSoftLink. That table contains references to all content linked from your page.

This will give you a list of all content (not only blocks) that are referenced from the page with id = 4.

SELECT *
FROM tblContentSoftLink
WHERE fkOwnerContentID = 4

If all your blocks are of the same type, you could easily filter on ContentTypeId, if you join tblContent.

#230870
Nov 16, 2020 19:30
Vote:
 

I'm interested why this needs to be a direct database query. I'd normally say that if you are having to directly craft your own queries against the Episerver database you're probably doing it wrong ;) Now, of course there will always be some sort of scenarios where this may be the only option. I'd just want to be sure its the last option to try rather than the first...

As you're finding:

  • The CMS database schema is 'deliberately' undocumented
  • The schema is highly normalised, and so producing efficient queries can be difficult
  • Episerver makes no guarantees about how they store their data. That is in effect not part of the pulic API, so any product updates, even minor could introduce breaking changes to your approach / solution that you'd be unaware of 

Why not just use the Episerver API to get the data, which is trivial and then store the output directly in a new database table specific to the task. This could either be operated as a scheduled task or hooked into the content publishing events as necessary.

That way you'll still be able to use a database query to get the results, but you'll be able to use the Episerver API as intended and guard against breaking changes.

#230871
Nov 16, 2020 20:19
Vote:
 

Thanks for the details Mark. I do understand that getting data directly from database is not a good option. We are creating a report to pull this data. However there has been some pressing need from client side for which they wanted to see if I can provide this information as one time activity to them while we simultaneously work on reports.

Using online articles I was able to understand part of database tables and was also able to pull the required data for a page type and all its properties and that gives me block guids but I wasn't sure on how to pull the acutal content.

I am still looking into the query Tomas gave and will update once I have got what i needed.

#231025
Nov 17, 2020 21:28
Vote:
 

Thank you Tomas. Following your information and tying this data with tblProperty and tblPropertyDefinition tables to get the required property information I was able to get what I wanted. 

#231179
Nov 20, 2020 18:00
Tomas Hensrud Gulla - Nov 21, 2020 16:22
Excellent!
* 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.