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 "CrudeDefault", 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 in the Crude Portal DB, sending the ViewID as parameter, and receiving 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.

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

CREATE PROCEDURE [portal].[GetDataViewDataTableCommand]
	@ViewID INT,
	@Draw INT = 1,
	@Start INT = 1,
	@Length INT = 25,
	@SearchValue NVARCHAR(MAX) = NULL,
	@SearchRegEx BIT = 0, -- not yet implemented
	@ColumnsOptions XML = NULL,
	@ColumnsOrder XML = NULL
AS
SET NOCOUNT ON;
DECLARE @DataSource NVARCHAR(100), @TableName NVARCHAR(300), @PK NVARCHAR(300), @Flags INT, @RowReorder NVARCHAR(200)
DECLARE @CMD NVARCHAR(MAX), @SQLColumns NVARCHAR(MAX)

SELECT @DataSource = DataSource, @TableName = MainTable, @PK = Primarykey, @Flags = Flags, @RowReorder = NULLIF(RowReorderColumn, '')
FROM portal.DataView
WHERE ViewID = @ViewID

DECLARE
	@ParametersDeclaration NVARCHAR(MAX) = N'',
	@ParametersFilter NVARCHAR(MAX) = N'',
	@QuickFilter NVARCHAR(MAX) = N'',
	@OrderBy NVARCHAR(MAX) = NULL


SET @SQLColumns = N'
SELECT [DT_RowId] = ' + @PK + N', [DT_RowIndex] = ROW_NUMBER() OVER (ORDER BY '

IF @ColumnsOrder IS NOT NULL
BEGIN
	;WITH ColsXML
	AS
	(
		SELECT
			ColIndex = X.value('(@ColIndex)[1]', 'int'),
			ColName = X.value('(@Name)[1]', 'nvarchar(max)'),
			DataSrc = X.value('(@DataSrc)[1]', 'nvarchar(max)'),
			IsRegEx = X.value('(@RegEx)[1]', 'bit'),
			SearchVal = X.value('(text())[1]', 'nvarchar(max)')
		FROM @ColumnsOptions.nodes('/Columns/Column') AS T(X)
		WHERE @RowReorder IS NULL
		UNION ALL
		SELECT
			ColIndex = 0,
			ColName = @RowReorder,
			DataSrc = @RowReorder,
			IsRegEx = 0,
			SearchVal = NULL
		WHERE @RowReorder IS NOT NULL
	)
	SELECT
		@SQLColumns = @SQLColumns + N'
				ISNULL(CONVERT(nvarchar(max), ' + FieldSource + N'), '''') ' + CASE co.ColDir WHEN 'desc' THEN 'DESC' ELSE 'ASC' END + N', '
	FROM  portal.DataViewField AS dvf
	INNER JOIN ColsXML AS c
	ON (@RowReorder IS NULL AND CONVERT(nvarchar,dvf.FieldIdentifier) = c.ColName)
	OR (@RowReorder IS NOT NULL AND dvf.FieldSource = @RowReorder)
	INNER JOIN (
	SELECT
		ColIndex = X.value('(@ColIndex)[1]', 'int'),
		ColDir = X.value('(@Direction)[1]', 'varchar(4)')
	FROM @ColumnsOrder.nodes('/Order/Column') AS T(X)
	) AS co
	ON c.ColIndex = co.ColIndex
	WHERE ViewID = @ViewID

	-- Remove trailing comma
	IF @@ROWCOUNT > 0
		SET @SQLColumns = LEFT(@SQLColumns, LEN(@SQLColumns) - 1)
END
ELSE
	SET @SQLColumns = @SQLColumns + ISNULL(@RowReorder, @PK)

SET @SQLColumns = @SQLColumns + N')'

;WITH ColsXML
AS
(
	SELECT
		ColIndex = X.value('(@ColIndex)[1]', 'int'),
		ColName = X.value('(@Name)[1]', 'nvarchar(1000)'),
		DataSrc = X.value('(@DataSrc)[1]', 'nvarchar(1000)'),
		IsRegEx = X.value('(@RegEx)[1]', 'bit'),
		SearchVal = X.value('(text())[1]', 'nvarchar(max)')
	FROM @ColumnsOptions.nodes('/Columns/Column') AS T(X)
)
SELECT
	@ParametersDeclaration = @ParametersDeclaration +
	CASE
		WHEN SearchVal <> N'' THEN N'
	DECLARE @pFilter' + CONVERT(nvarchar, c.ColIndex) + N' nvarchar(max);
	SET @pFilter' + CONVERT(nvarchar, c.ColIndex) + N' = @ColumnsOptions.value(''(Columns/Column[@ColIndex="' + CONVERT(nvarchar, c.ColIndex) + N'"]/text())[1]'', ''nvarchar(max)'');'
		ELSE N''
	END,
	@ParametersFilter = @ParametersFilter +
	CASE
		WHEN c.SearchVal <> N'' THEN N'
	AND ISNULL(CONVERT(nvarchar(max), ' + FieldSource + N'), '''') LIKE @pFilter' + CONVERT(nvarchar, c.ColIndex)
		ELSE N''
	END,
	@QuickFilter = @QuickFilter +
	CASE
		WHEN @SearchValue IS NOT NULL THEN N'
		OR ISNULL(CONVERT(nvarchar(max), ' + FieldSource + N'), '''') LIKE ''%'' + @SearchValue + ''%'''
		ELSE N''
	END,
	@SQLColumns = @SQLColumns + N',
		' + QUOTENAME(ISNULL(c.DataSrc, 'Field_' + CONVERT(nvarchar,dvf.FieldID))) + N' = ' + CASE WHEN FieldType = 12 THEN N'''''' WHEN FieldType IN (7,8) THEN N'ISNULL(CONVERT(nvarchar(19),' + FieldSource + N', 126), '''')' ELSE N'ISNULL(CONVERT(nvarchar(max), ' + FieldSource + N'), '''')' END
FROM portal.DataViewField AS dvf
LEFT JOIN ColsXML AS c
ON CONVERT(nvarchar,dvf.FieldIdentifier) = c.ColName
WHERE ViewID = @ViewID
--ORDER BY FieldOrder ASC

SET @CMD = N'DECLARE @RTotal INT, @RFiltered INT;
DECLARE @ColumnsOptions XML, @Start INT, @Length INT, @SearchValue NVARCHAR(MAX)
SELECT @ColumnsOptions = ?, @Start = ?, @Length = ?, @SearchValue = ?

SELECT @RTotal = COUNT(*) FROM ' + @TableName + N';
' + @ParametersDeclaration

IF @ParametersFilter <> '' OR @QuickFilter <> ''
BEGIN
	SET @CMD = @CMD + '
	SELECT @RFiltered = COUNT(*) FROM ' + @TableName + N' WHERE 1=1'
	IF @ParametersFilter <> '' SET @CMD = @CMD + @ParametersFilter
	IF @QuickFilter <> '' SET @CMD = @CMD + N'
AND (1=0 ' + @QuickFilter + N')'
END
ELSE
	SET @CMD = @CMD + '
SET @RFiltered = @RTotal;'

SET @CMD = @CMD + N'
SELECT @RTotal AS recordsTotal, @RFiltered AS recordsFiltered

SELECT [JsonData] = ISNULL((SELECT * FROM (' + @SQLColumns + N' FROM ' + @TableName + N' WHERE 1=1'
IF @ParametersFilter <> '' SET @CMD = @CMD + @ParametersFilter
IF @QuickFilter <> '' SET @CMD = @CMD + N'
AND (1=0 ' + @QuickFilter + N')'

SET @CMD = @CMD + N'
) AS q
WHERE [DT_RowIndex] BETWEEN @Start AND @Start + @Length - 1'


SET @CMD = @CMD + N' FOR JSON AUTO), N''[ ]'')'

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

The BL layer receives the Data Source, SQL Command and the DataView Flags as output. It uses DataSource 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.