Gokuldas Palapatta

Wednesday, 2 March 2016

Call a javascript function on all Postback

Tuesday, 16 February 2016

javascript to capture the browser or tab closed event

/*Refer <script type="text/javascript" src="http://ajax.googleapis.com/ajax/libs/jquery/1.4.4/jquery.min.js"></script> too*/

var validNavigation = false;

function endSession() {
// Browser or broswer tab is closed
// Do here ...
alert("bye");
}

function wireUpEvents() {
/*
* For a list of events that triggers onbeforeunload on IE
* check http://msdn.microsoft.com/en-us/library/ms536907(VS.85).aspx
*/
window.onbeforeunload = function() {
if (!validNavigation) {
endSession();
}
}

// Attach the event keypress to exclude the F5 refresh
$(document).bind('keypress', function(e) {
if (e.keyCode == 116){
validNavigation = true;
}
});

// Attach the event click for all links in the page
$("a").bind("click", function() {
validNavigation = true;
});

// Attach the event submit for all forms in the page
$("form").bind("submit", function() {
validNavigation = true;
});

// Attach the event click for all inputs in the page
$("input[type=submit]").bind("click", function() {
validNavigation = true;
});

}

// Wire up the events as soon as the DOM tree is ready
$(document).ready(function() {
wireUpEvents();
});

Wednesday, 3 February 2016

SP for maintaining same table structure for Main and archive table.

GO

/****** Object: StoredProcedure [dbo].[USP_ManageArchivalTableStrucutures] Script Date: 2/2/2016 11:00:02 AM ******/

SET ANSI_NULLS ON;
GO
SET QUOTED_IDENTIFIER ON;
GO

-- =============================================
-- Author: Gokuldas Palapatta
-- Create date: 2016-02-01 12:32:18.960
-- Description: Help_archiveTables
--
--Table Details
-- CREATE TABLE [dbo].[Archive_Setting](
-- [Id] [int] IDENTITY(1,1) NOT NULL,
-- [SnapShotTable] [varchar](100) NOT NULL,
-- [ArchiveTable] [varchar](100) NOT NULL)
-- =============================================
ALTER PROCEDURE [dbo].[USP_ManageArchivalTableStrucutures]
-- Add the parameters for the stored procedure here
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
--Declaring All variables

DECLARE @Query VARCHAR(MAX) = '';
IF OBJECT_ID('tempdb..#temp1') IS NOT NULL
BEGIN
DROP TABLE #temp1;
END;
IF OBJECT_ID('tempdb..#temp2') IS NOT NULL
BEGIN
DROP TABLE #temp2;
END;
IF OBJECT_ID('tempdb..#temp3') IS NOT NULL
BEGIN
DROP TABLE #temp3;
END;

-- pushing all the details to temptable
SELECT C2.*,
C1.* INTO #TEMP1
FROM INFORMATION_SCHEMA.COLUMNS C2
JOIN ARCHIVE_SETTING C1 ON C2.TABLE_NAME = C1.SNAPSHOTTABLE
OR C2.TABLE_NAME = C1.ARCHIVETABLE;

-- Filtering only changed and new coloumns
SELECT * INTO #TEMP2
FROM(
SELECT DISTINCT COLUMN_NAME,
C2.DATA_TYPE,
C2.CHARACTER_MAXIMUM_LENGTH,
C2.TABLE_NAME,
0 AS ISCHANGED,
1 AS NEW,
ARCHIVETABLE AS ALTERTABLE,
C2.IS_NULLABLE
FROM #TEMP1 C2
WHERE TABLE_NAME = SNAPSHOTTABLE
AND C2.COLUMN_NAME NOT IN(
SELECT COLUMN_NAME
FROM #TEMP1
WHERE TABLE_NAME = ARCHIVETABLE )
UNION ALL
SELECT DISTINCT C2.COLUMN_NAME,
C2.DATA_TYPE,
C2.CHARACTER_MAXIMUM_LENGTH,
C2.TABLE_NAME,
1 AS ISCHANGED,
0 AS NEW,
ARCHIVETABLE AS ALTERTABLE,
C2.IS_NULLABLE
FROM #TEMP1 C2
WHERE TABLE_NAME = SNAPSHOTTABLE
AND C2.COLUMN_NAME IN(
SELECT COLUMN_NAME
FROM #TEMP1 C1
WHERE TABLE_NAME = ARCHIVETABLE
AND C2.COLUMN_NAME = C1.COLUMN_NAME
AND ( C2.IS_NULLABLE <> C1.IS_NULLABLE
OR C1.DATA_TYPE <> C2.DATA_TYPE
OR C1.CHARACTER_MAXIMUM_LENGTH <> C2.CHARACTER_MAXIMUM_LENGTH
))) AS CT;

-- preparing Query based on the changes
SELECT * INTO #Temp3
FROM(
SELECT *,
CASE
WHEN data_type = 'varchar'
OR data_type = 'nvarchar'
OR data_type = 'char'
THEN 'ALTER TABLE ' + AlterTable + '
ALTER COLUMN ' + COLUMN_NAME + ' ' + data_type + ' (' + CAST(CHARACTER_MAXIMUM_LENGTH AS VARCHAR(10)) + ' )' + ' ' + CASE
WHEN IS_NULLABLE = 'YES'
THEN 'null'
ELSE 'Not null'
END
ELSE 'ALTER TABLE ' + AlterTable + '
ALTER COLUMN ' + COLUMN_NAME + ' ' + data_type + ' ' + CASE
WHEN IS_NULLABLE = 'YES'
THEN 'null'
ELSE 'Not null'
END
END AS Query
FROM #temp2
WHERE IsChanged = 1
AND NEW = 0
UNION ALL
SELECT *,
CASE
WHEN data_type = 'varchar'
OR data_type = 'nvarchar'
OR data_type = 'char'
THEN 'ALTER TABLE ' + AlterTable + '
ADD ' + COLUMN_NAME + ' ' + data_type + ' (' + CAST(CHARACTER_MAXIMUM_LENGTH AS VARCHAR(10)) + ' )' + ' ' + CASE
WHEN IS_NULLABLE = 'YES'
THEN 'null'
ELSE 'Not null'
END
ELSE 'ALTER TABLE ' + AlterTable + '
ADD ' + COLUMN_NAME + ' ' + data_type + ' ' + CASE
WHEN IS_NULLABLE = 'YES'
THEN 'null'
ELSE 'Not null'
END
END AS Query
FROM #temp2
WHERE IsChanged = 0
AND NEW = 1 ) AS CX;

-- concardinating Query based
SELECT @Query = COALESCE(@Query + ' ', '') + Query
FROM #Temp3;
EXEC ( @Query );
END;

Monday, 18 January 2016

SP to find a string inside table values in a DB in SQL

-- Pass the search string to the SP, it will return table + column name.

CREATE PROC SearchAllTables

(

@SearchStr nvarchar(100)

)

BEGIN

CREATE TABLE #Results (ColumnName nvarchar(370), ColumnValue nvarchar(3630))

SET NOCOUNT ON

DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128), @SearchStr2 nvarchar(110)

SET @TableName = ''

SET @SearchStr2 = QUOTENAME('%' + @SearchStr + '%','''')

WHILE @TableName IS NOT NULL

BEGIN

SET @ColumnName = ''

SET @TableName =

(

SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))

FROM INFORMATION_SCHEMA.TABLES

WHERE TABLE_TYPE = 'BASE TABLE'

AND QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName

AND OBJECTPROPERTY(

OBJECT_ID(

QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)

), 'IsMSShipped'

) = 0

)

WHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL)

BEGIN

SET @ColumnName =

(

SELECT MIN(QUOTENAME(COLUMN_NAME))

FROM INFORMATION_SCHEMA.COLUMNS

WHERE TABLE_SCHEMA = PARSENAME(@TableName, 2)

AND TABLE_NAME = PARSENAME(@TableName, 1)

AND DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar', 'int', 'decimal')

AND QUOTENAME(COLUMN_NAME) > @ColumnName

)

IF @ColumnName IS NOT NULL

BEGIN

INSERT INTO #Results

EXEC

(

'SELECT ''' + @TableName + '.' + @ColumnName + ''', LEFT(' + @ColumnName + ', 3630)

FROM ' + @TableName + ' (NOLOCK) ' +

' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2

)

END

SELECT ColumnName, ColumnValue FROM #Results

END

Thursday, 10 December 2015

auto-format-entered-date-onkeypress

C# textbox.Attributes.Add("onkeyup", "javascript:return getDate(event);");

JavaScript

function getDate(elem){
var elemVal = elem.value;
var elemId = elem.id;

switch(elemVal.length){
case 2:
elemVal = elemVal+"/";
break;
case 5:
elemVal = elemVal+"/";
break;
}
document.getElementById(elemId).value = elemVal;
}

Sunday, 6 December 2015

Download feature not working within update-panel in asp-net

You cannot return an attachment in an UpdatePanel partial postback, since the results are used by the ScriptManager to update a DIV (not the whole response). The simplest fix for what you're trying to do would be to make your download button as a postback control. That would cause that button to initiate a full postback. Here's the code below to include in your Page_Load

ScriptManager.GetCurrent(this.Page).RegisterPostBackControl(this.lnkDownload);

Thursday, 3 December 2015

Transpose a data table in C#

private DataTable GenerateTransposedTable(DataTable inputTable)
{
DataTable outputTable = new DataTable();

// Add columns by looping rows

// Header row's first column is same as in inputTable
outputTable.Columns.Add(inputTable.Columns[0].ColumnName.ToString());

// Header row's second column onwards, 'inputTable's first column taken
foreach (DataRow inRow in inputTable.Rows)
{
string newColName = inRow[0].ToString();
outputTable.Columns.Add(newColName);
}

// Add rows by looping columns
for (int rCount = 1; rCount <= inputTable.Columns.Count - 1; rCount++)
{
DataRow newRow = outputTable.NewRow();

// First column is inputTable's Header row's second column
newRow[0] = inputTable.Columns[rCount].ColumnName.ToString();
for (int cCount = 0; cCount <= inputTable.Rows.Count - 1; cCount++)
{
string colValue = inputTable.Rows[cCount][rCount].ToString();
newRow[cCount + 1] = colValue;
}
outputTable.Rows.Add(newRow);
}

return outputTable;
}
protected DataTable Transpose(DataTable dt)
{
DataTable inputTable = dt;

dt.Columns.Remove("SlNo");
dt.Columns.Remove("Id#");
dt.Columns["Stage"].ColumnName = "IndexFields";
// Table shown in Figure 1.1

DataTable transposedTable = GenerateTransposedTable(inputTable);
return transposedTable;

}