Balajiprasad's useful codes: 2024

Thursday, April 25, 2024

Html JS - Handle Browser Back Button on SPA

In Single page applications, we will not have dedicated URLs. So on click of Browser Back button it will navigate us to previous page and we lost our current page changes. We can handle this with native JavaScript functions,

Create Html Page as below to mimic SPA,

On click of each Page button, it can switch the content,

On Each Page navigation we can set the hash URL something like below using history.pushstate or location.hash

Now on click of back button use the hashchange event to detect and open the page accordingly

Here is full code:

<html>

<head>

</head>

<body>

<div id="page1" class="page">Page 1 Content</div>

<div id="page2" class="page">Page 2 Content</div>

<div id="page3" class="page">Page 3 Content</div>

</body>

$(document).ready(function () {

hideAllPages();

});

$(window).on('popstate', (e) => {

e.preventDefault();

var currentLocation = window.location.pathname;

history.push(currentLocation)

});

window.onbeforeunload = function () {

return "Are you sure you want to leave this page?";

};

addEventListener("hashchange", (event) => {

alert("New" + event.newURL);

alert("Old" + event.oldURL);

var currentHash = event.currentTarget.location.hash;

openPage(currentHash.replace("#", ""), true);

});

function hideAllPages() {

$(".page").hide();

}

function openPage(pageId, ignoreHistoryPush = false) {

hideAllPages();

$("#page" + pageId).show();

if (!ignoreHistoryPush) //This is to not add history on everytime

history.pushState({ page: pageId }, pageId, '#' + pageId);
//location.hash = pageId //This way also can set it

}

</script>

</html>

Tuesday, February 20, 2024

SQL - Defined vs Dynamic table column values into JSON

Convert Defined vs Dynamic table column values into JSON using SQL Syntax, using SQL Pivot and Subquery execution achieved to construct JSON,

IF OBJECT_ID('tempdb..#tempDefinedTable1') IS NOT NULL

DROP TABLE #tempDefinedTable1

IF OBJECT_ID('tempdb..#tempDefinedTable2') IS NOT NULL

DROP TABLE #tempDefinedTable2

IF OBJECT_ID('tempdb..#tempAllColumns') IS NOT NULL

drop table #tempAllColumns

IF OBJECT_ID('tempdb..#tempMatchedColumns') IS NOT NULL

drop table #tempMatchedColumns

IF OBJECT_ID('tempdb..#tempUnMatchedColumns') IS NOT NULL

drop table #tempUnMatchedColumns

--create some defined column names as values

create table #tempDefinedTable1 (

definedcolnames varchar(max)

)

insert into #tempDefinedTable1

values ('col1'),('col2')

select * from #tempDefinedTable1

-- create table with defined columns + dynamic columns

create table #tempDefinedTable2 (

id int,

col1 varchar(max),

col2 varchar(max),

dynamicCol1 varchar(max),

dynamicCol2 varchar(max)

)

insert into #tempDefinedTable2

values (1, 'a1', 'b1', 'c1', 'd1'),(2, 'a2', 'b2', 'c2', 'd2')

select * from #tempDefinedTable2

--From the defined+dynamic column list, consolidate columns as values

Select C.* into #tempAllColumns

From #tempDefinedTable2 A

Cross Apply ( values (cast((Select A.* for XML RAW) as xml))) B(XMLData)

Cross Apply (

Select ColumnName = a.value('local-name(.)','varchar(100)')

,ColumnValue = a.value('.','varchar(max)')

,Id

From B.XMLData.nodes('/row') as C1(n)

Cross Apply C1.n.nodes('./@*') as C2(a)

Where a.value('local-name(.)','varchar(100)') not in ('ID','ExcludeOtherCol')

) C

select Id, ColumnName, ColumnValue from #tempAllColumns

--now preparing sql sub query to list defined columns in root level, dynamic columns as nested object

DECLARE @Columns as VARCHAR(MAX)

SELECT @Columns = COALESCE(@Columns + ', ','') + QUOTENAME([ColumnName])

FROM

(SELECT DISTINCT ColumnName FROM #tempAllColumns st

JOIN #tempDefinedTable1 tt on tt.definedcolnames = st.ColumnName

WHERE tt.definedcolnames is not null) AS B

ORDER BY B.ColumnName

select @Columns as DefinedColumns

DECLARE @DynamicColumns as VARCHAR(MAX)

SELECT @DynamicColumns = COALESCE(@DynamicColumns + ', ','') + QUOTENAME([ColumnName])

FROM

(SELECT DISTINCT ColumnName FROM #tempAllColumns st

LEFT JOIN #tempDefinedTable1 tt on tt.definedcolnames = st.ColumnName

WHERE tt.definedcolnames is null) AS B

ORDER BY B.ColumnName

select @DynamicColumns as UndefinedColumns

DECLARE @SQL as VARCHAR(MAX)

SET @SQL = 'SELECT Id,' + @Columns + ',

(SELECT PR1.Id,' + @DynamicColumns + ' FROM (select st1.Id, st1.[ColumnName], st1.ColumnValue from #tempAllColumns st1

LEFT JOIN #tempDefinedTable1 tt1 on tt1.definedcolnames = st1.ColumnName

WHERE tt1.definedcolnames is null) as PD2

PIVOT (

max(ColumnValue)

FOR [ColumnName] IN (' + @DynamicColumns + ')

) AS PR1

WHERE PR1.Id = PR.Id

ORDER BY Id

FOR JSON PATH, INCLUDE_NULL_VALUES) as ExtraFields

FROM

(

select Id,[ColumnName],ColumnValue from #tempAllColumns st

JOIN #tempDefinedTable1 tt on tt.definedcolnames = st.ColumnName

WHERE tt.definedcolnames is not null

) as PD

PIVOT

(

max(ColumnValue)

FOR [ColumnName] IN (' + @Columns + ')

) AS PR

ORDER BY Id

FOR JSON PATH, INCLUDE_NULL_VALUES'

EXEC(@SQL);

Columns:

Output: