DataView Display Workflow

NOTE: this page is work-in-progress and is subject to change frequently

Since a Data View's data can come from a different connection string than that of the CRUDE Portal database itself, the way data is queried is done in a two-step methodology, where the portal BL layer serves as the "middle man" between the Crude Portal DB, and the Data Source DB.

Connection strings are saved in the portal's web.config file, and can be edited either directly or using the standard IIS management console (for added security).

One connection string must exist and named "Default", which will be used for connecting to the Crude Portal DB itself.

The general flow of logic, is therefore as follows:

1. Data View receives the ViewID as a parameter to the BL layer.
2. The BL layer executes a special stored procedure located on the Crude Portal DB, sending the ViewID as parameter, and receives in response the SQL command as it should be executed on the actual data source, together with any additional info as needed.
3. The BL layer uses the information it received to run the SQL command on the actual data source (i.e. DBConnectionString).

Code for the special stored procedure in the Crude Portal DB:

CREATE PROCEDURE [portal].[GetDataViewContentsCommand]
	@ViewID INT
AS
SET NOCOUNT ON;
DECLARE @DataSource NVARCHAR(100), @TableName NVARCHAR(300), @DBWhere NVARCHAR(4000), @PK NVARCHAR(300), @Flags INT
DECLARE @CMD NVARCHAR(MAX)

SELECT @DataSource = DBConnectionString, @TableName = DBTable, @PK = DBPKColumn, @DBWhere = DBWhere, @Flags = Flags
FROM portal.DataView
WHERE ViewID = @ViewID

SET @CMD = N'SELECT [Json] = ISNULL((SELECT "_ItemID" = ' + @PK

SELECT @CMD = @CMD + N', "' + FieldLabel + N'" = ' + CASE WHEN FieldType = 12 THEN N'''''' WHEN DBLOVTable <> '' AND DBLOVTableValueColumn <> '' THEN N'CONVERT(nvarchar(max), ' + DBColumn + N')' ELSE DBColumn END
	+ CASE WHEN DBLOVTable <> '' AND DBLOVTableValueColumn <> '' THEN N',
	 "_resolved_' + FieldLabel + N'" = + STUFF((SELECT N'', '' + labelfield FROM
		(SELECT labelfield = ' + ISNULL(NULLIF(DBLOVLabelColumn, N''), DBLOVTableValueColumn ) + N', valuefield = CONVERT(nvarchar(max), ' + DBLOVTableValueColumn + N')
		 FROM ' + DBLOVTable + N' ' + ISNULL(DBLOVAddition,N'') + N') AS t
			WHERE (t.valuefield = ' + DBColumn + N') OR (t.valuefield IS NULL AND ' + DBColumn + N' IS NULL)
		 FOR XML PATH('''')
		 ), 1, 2, N'''')' ELSE N'' END
FROM portal.DataViewField
WHERE ViewID = @ViewID
ORDER BY FieldOrder ASC

SET @CMD = @CMD 
+ N' FROM ' + @TableName + ISNULL(N' WHERE ' + @DBWhere, N'') + N' FOR JSON AUTO), N''[ ]'')'

SELECT ISNULL(@DataSource, 'Default') AS DataSource, @CMD AS Command, @Flags AS Flags

The BL layer receives the DBConnectionString, SQL Command and the DataView Flags as input. It uses DBConnectionString to retrieve the actual connection string from web.config and then executes the SQL command against that connection string.

To-do:

Using this procedure we should be able to get query info for all required tables: main data and all lov tables, in the following format:

• Data source
• Element name (data / lov_[field id])
• Query

Also, there's no reason not to use this opportunity to get the metadata of the dataview itself.

TBA:

• Advanced Search implementation
• Implementation for stored procedure as source
• Workflow of display logic on the view layer
• Separation of list of values fields: query data and metadata separately and connect them using JS.