15
May

Implementing Auto-Logout functionality

   Posted by: Firoz Ansari   in ASP.NET, Web Development

Here is small javascript snippet you will find useful for your web application. It will automatically logout user after specific time of inactivity.

<script type="text/javascript">
var timer;
var wait=10;
document.onkeypress=resetTimer;
document.onmousemove=resetTimer;
function resetTimer()
{
    clearTimeout(timer);
    timer=setTimeout("logout()", 60000*wait);
}
 
function logout()
{
    window.location.href='Login.aspx';
}    
</script>

Code is self explanatory. We are using setTimeout method which will call a function (logout function in above case) after specified delay in milliseconds. With that, we also resetting that again and again for every user movement on page.

You require dropping this code in all pages (except login and unsecured pages) of your application so you might consider placing in master page instead for easy maintenance.

Before login off user from application, you may also like to alert user first. So let’s extend above code further. This gonna be bit tricky as we cannot use simple javascript alert to show message. Instead we will create a DIV layer in the page and use it for showing alert message. Any user interaction will hide this alert message.

Here is modified code:

<html>
<head runat="server">
<title>Default Page</title>
<script type="text/javascript">
var timer1, timer2;
document.onkeypress=resetTimer;
document.onmousemove=resetTimer;
function resetTimer()
{
    document.getElementById('timeoutPopup').style.display='none';
    clearTimeout(timer1);
    clearTimeout(timer2);
 
    // waiting time in minutes
    var wait=10;
 
    // alert user one minute before
    timer1=setTimeout("alertUser()", (60000*wait)-1);
 
    // logout user
    timer2=setTimeout("logout()", 60000*wait);
}
 
function alertUser()
{
    document.getElementById('timeoutPopup').style.display='block';
}
 
function logout()
{
    window.location.href='Login.aspx';
}    
</script>
<style type="text/css">
#timeoutPopup {
    display:none; 
    position:absolute; 
    top:0; right:0; bottom:0; left:0;
    width:30%;
    height:30%;
    margin:auto;
    padding:4px;
    border:solid 1px #999999; 
    background-color:#dddddd;
    text-align:center;
}
</style>
</head>
<body>
    <form id="form1" runat="server">
    <div id="timeoutPopup">
        Your session is about to expire if there is no additional activity.
    </div>
    </form>
</body>
</html>
2 Comments
21
Nov

Generating JSON from TSQL Query

   Posted by: Firoz Ansari   in SQL Server

I was looking for TSQL implementation which can build JSON payload directly from database. After couple of failed search, I decided to create my own stored procedure which can render JSON based for any query passed as parameter. After few keystrokes in SQL Server Management Studio, I have this semi-finish stored procedure which I thought you will find useful.

CREATE PROCEDURE [dbo].[GetJSON] (
@ParameterSQL AS VARCHAR(MAX)
)
AS
BEGIN
 
DECLARE @SQL NVARCHAR(MAX)
DECLARE @XMLString VARCHAR(MAX)
DECLARE @XML XML
DECLARE @Paramlist NVARCHAR(1000)
SET @Paramlist = N'@XML XML OUTPUT'
SET @SQL = 'WITH PrepareTable (XMLString) '
SET @SQL = @SQL + 'AS ( '
SET @SQL = @SQL + @ParameterSQL+ ' FOR XML RAW, TYPE, ELEMENTS '
SET @SQL = @SQL + ') '
SET @SQL = @SQL + 'SELECT @XML = XMLString FROM PrepareTable '
EXEC sp_executesql @SQL, @Paramlist, @XML=@XML OUTPUT
SET @XMLString = CAST(@XML AS VARCHAR(MAX))
 
DECLARE @JSON VARCHAR(MAX)
DECLARE @Row VARCHAR(MAX)
DECLARE @RowStart INT
DECLARE @RowEnd INT
DECLARE @FieldStart INT
DECLARE @FieldEnd INT
DECLARE @KEY VARCHAR(MAX)
DECLARE @Value VARCHAR(MAX)
 
DECLARE @StartRoot VARCHAR(100); SET @StartRoot = '<row>'
DECLARE @EndRoot VARCHAR(100); SET @EndRoot = '</row>'
DECLARE @StartField VARCHAR(100); SET @StartField = '<'
DECLARE @EndField VARCHAR(100); SET @EndField = '>'
 
SET @RowStart = CharIndex(@StartRoot, @XMLString, 0)
SET @JSON = ''
WHILE @RowStart > 0
BEGIN
	SET @RowStart = @RowStart+Len(@StartRoot)
	SET @RowEnd = CharIndex(@EndRoot, @XMLString, @RowStart)
	SET @Row = SubString(@XMLString, @RowStart, @RowEnd-@RowStart)
	SET @JSON = @JSON+'{'
 
	-- for each row
	SET @FieldStart = CharIndex(@StartField, @Row, 0)
	WHILE @FieldStart > 0
	BEGIN
		-- parse node key
		SET @FieldStart = @FieldStart+Len(@StartField)
		SET @FieldEnd = CharIndex(@EndField, @Row, @FieldStart)
		SET @KEY = SubString(@Row, @FieldStart, @FieldEnd-@FieldStart)
		SET @JSON = @JSON+'"'+@KEY+'":'
 
		-- parse node value
		SET @FieldStart = @FieldEnd+1
		SET @FieldEnd = CharIndex('</', @Row, @FieldStart)
		SET @Value = SubString(@Row, @FieldStart, @FieldEnd-@FieldStart)
		SET @JSON = @JSON+'"'+@Value+'",'
 
		SET @FieldStart = @FieldStart+Len(@StartField)
		SET @FieldEnd = CharIndex(@EndField, @Row, @FieldStart)
		SET @FieldStart = CharIndex(@StartField, @Row, @FieldEnd)
	END	
	IF LEN(@JSON)>0 SET @JSON = SubString(@JSON, 0, LEN(@JSON))
	SET @JSON = @JSON+'},'
	--/ for each row
 
	SET @RowStart = CharIndex(@StartRoot, @XMLString, @RowEnd)
END
IF LEN(@JSON)>0 SET @JSON = SubString(@JSON, 0, LEN(@JSON))
SET @JSON = '[' + @JSON + ']'
SELECT @JSON
 
END

To use this stored procedure, just pass TSQL query as parameter. Something like this:

EXEC GetJSON 'SELECT * FROM dbo.Employee_TBL'

And this stored procedure will return JSON payload:

[{"UserId":"7C92EB27-DD81-498E-82CE-18192C940328","FirstName":"Bill","LastName":"Gates","Age":"45","LastLogin":"2008-11-02T00:00:00"},{"UserId":"58E75687-8D0F-423A-8A76-4B1D750F62FD","FirstName":"Steve","LastName":"Job","Age":"52","LastLogin":"2008-10-17T00:00:00"}]

Tags: ,

No Comments
15
Nov

Getting list of modified objects in SQL Server

   Posted by: Firoz Ansari   in SQL Server, Uncategorized

Developers always want to have a list of modified tables or stored procedures while check-in the code or wrapping up the development at the end of the day. Many usually keep this list in some text file and they update this file every time they modify any table or stored procedure. Here are some handy sql snippets which can provide you list of the tables, or stored procedure modified.

-- tables modified today
SELECT [name],create_date,modify_date
FROM sys.objects
WHERE modify_date>DATEADD(day,-1,GETDATE())
AND type='U'
-- stored procedures modified today
SELECT [name],create_date,modify_date
FROM sys.objects
WHERE modify_date>DATEADD(day,-1,GETDATE())
AND type='P'

You can further modify above queries to give list something like objects modified in last 7 days.

-- tables modified in last 7 days
SELECT [name],create_date,modify_date
FROM sys.objects
WHERE modify_date>DATEADD(day,-7,GETDATE())
AND type='U'

Tags:

No Comments
5
Apr

Released – TinySQL v2.0

   Posted by: Firoz Ansari   in Code Generation, Programming, SQL Server

I just released TinySQL code generator v2.0 with few bug fixes and a new feature of using separator tag for loop statement. You can now use {sap}{/sap} tags to place your separators inside loop.

Here is the example of using {sap}{/sap} tags in TinySQL template:

** Generate DATA Provider: UPDATE/v2.0
CREATE PROCEDURE [dbo].[usp_Update$table]{loop}
	@$field = $sp_type{sap},{/sap}{/loop}
AS
BEGIN
UPDATE dbo.$table WITH (ROWLOCK)
SET {loop}$field = @$field{sap},{/sap}
	{/loop}
WHERE --TODO
RETURN -1
END

And generated code from above template will be like:

CREATE PROCEDURE [dbo].[usp_UpdateAction]
	@Id = NUMERIC,
	@Action = VARCHAR(250),
	@ProjectId = NUMERIC,
	@IsNextAction = BIT,
	@IsWaitingFor = BIT,
	@WaitingForNotes = VARCHAR(1000),
	@RemindOn = DATETIME,
	@IsDefer = BIT,
	@DeferDate = DATETIME,
	@IsDone = BIT,
	@Sequence = INT
AS
BEGIN
UPDATE dbo.Action WITH (ROWLOCK)
SET Id = @Id,
	Action = @Action,
	ProjectId = @ProjectId,
	IsNextAction = @IsNextAction,
	IsWaitingFor = @IsWaitingFor,
	WaitingForNotes = @WaitingForNotes,
	RemindOn = @RemindOn,
	IsDefer = @IsDefer,
	DeferDate = @DeferDate,
	IsDone = @IsDone,
	Sequence = @Sequence
WHERE --TODO
RETURN -1
END

Download: TinySQL v2.0

You can also read my previous blog to learn more about TinySQL tool.
TinySQL Code Generator

Hope you will find this tool useful.

Tags: , ,

1 Comment
3
Apr

iPhone – Scroll Bottom Bookmarklet

   Posted by: Firoz Ansari   in iPhone

You can easily scroll to the top of the web page by just tapping on top bar (which has signal status, carrier name, time etc.) of iPhone but how about scrolling to the bottom of the page. If you are browsing any long web page then sometime it becomes really annoying to reach bottom of the page.

But from now onwards, you can simply tab on a bookmark and bookmarklet (or favelet) will scroll you to the bottom of the page. You just require adding this javascript code as bookmark:

Javascript Code:
javascript:scroll(0,document.getElementsByTagName(’body’)[0].scrollHeight);

Link:
Scroll Bottom
To add “Scroll Bottom” bookmarklet, just drag above link into Safari’s Bookmarks Bar and then sync to your iPhone through iTune.

Following are some relative articles for bookmarks and syncing to the iPhone:
Syncing iPhone and iPod touch with your computer
iPhone: Using Bookmarks
17 powerful bookmarklets for your iPhone

Tags:

5 Comments
Back to top
Page 1 of 1812345»...Last »