2sxc 9.8 and Visual Query 2 finally have a most-requested data-source which delivers data-items from SQL.
Most Requested DataSource
We heard you - sorry it took so long. We had to refactor a lot of the internals so that we could provide a solution which is future proof. I believe we now have what everybody was waiting for. Here's a brief demo what it can look like:
This example uses SQL to select all portals and pass them on to the template. The query used in this case was:
SELECT TOP (1000) PortalId as EntityId, HomeDirectory as EntityTitle,
[PortalID], [ExpiryDate], [AdministratorRoleId], [GUID],
[HomeDirectory], [CreatedOnDate], [PortalGroupID]
FROM [Portals]
Where ExpiryDate is null
So the view (C#-Razor-MVC or JavaScript) can now use the resulting 61 items.
Using with URL Parameters
Of course a common scenario will be to filter/sort/page the data. We could simply add a filter-component after the data, but that wouldn't be efficient. Better to use Url values as parameters, like this:
SELECT TOP (1000) PortalId as EntityId, HomeDirectory as EntityTitle,
[PortalID],[GUID],[HomeDirectory],[PortalGroupID]
FROM [Portals]
Where PortalID = [QueryString:PortalId]
And just in case you're worring about SQL Injection - the tokens aren't actually resolved as text, they are given to SQL Server as clean query parameter, so we're protecting everyhting.
Using With Other Parameters
You can also configure a query based on information stored somewhere. Here's an example:
This example has a content-item which an admin can edit, which affects the query as follows:
SELECT TOP (1000) PortalId as EntityId, HomeDirectory as EntityTitle,
[PortalID],[GUID],[HomeDirectory],[PortalGroupID]
FROM [Portals]
Where PortalID = [In:SqlSetting:PortalId]
Basically this means that there is an In-Stream by the name of SqlSetting. Of that the property PortalId is used as a configuration.
Using with App-Instance Parameters
But that's not all: often you'll want an editor to add apps (modules) to a page, configure something and based on these settings, affect the query - like this:
In this case, the ModuleDataSource will deliver a content-item which the editor worked on - which is then used in the SqlDataSource as a parameter:
SELECT TOP (1000) PortalId as EntityId, HomeDirectory as EntityTitle,
[PortalID],[GUID],[DefaultLanguage],[HomeDirectory],[PortalGroupID]
FROM [Portals]
Where PortalId = [In:Mod:PortalFilter]
Caching For Performance
If you often need the same data then you don't want to hit the DB all the time. Then you can just add a cache component to buffer the results for minutes, hours or even days:
Try it - and give feedback
Hope you love it. You can try it with the latest 2sxc 9.8 or higher (download). And you can find these sample queries in our queries demo app.
Love from Switzerland,
Daniel