Thursday, February 21, 2008

How To Keep Session Live Till The Page Stays Open


One one of my project i was told that the user will keep the browser open and will
talk to their client for long time, which may be 1/2 hour continuous and after that
they will come back and add some item to the page as per the discussion over the
phone. So we have to keep the session live till that period to keep him logged in.
The way to implement this are


1) Add the following code in web.config file

<sessionState timeout="100"></sessionState>Here timeout is in munites and sessionwill stay live upto 180 min i.e 3 hours
(by default the timeout is 20 mins) which was enough for our purpose.


But with this way of implementation we were misusing the valuable server disk space
for all the session variables will stay for that long time. So even if you close
the browser the objects will stay in the server for next 3 Hours. So we implemented
another way


2) In the Body tag of our aspx page i put a div which is having display style attribute='None'
so that the div does not display in the page.


And then put a IFrame inside the div and set another aspx page (KeepSession.aspx)
as the src of the IFrame. The the page in the IFrame i make it auto refresh after
18 mins. In this way though the session is to be expired in 20 mins (by default),
but the KeepSession.aspx page will be reloaded in every 18 mins and the session
will be refreshed and it will start counting the 20 mins expire time from start.
So in this way it will stay alive for ever. Below in the quote in my NewQuote.aspx


::::::::::::::::::::


<div style="display:none">

<iframe id="redresh" src="RefreshSession.aspx" height="0" width="0">

</iframe>
</div>

<form id="form1" runat="server">

<div>YOUR PAGE CODE HERE </div>

</form>

</body>........

KeepSession.aspx Page


<%@ Page Language="C#" AutoEventWireup="true" CodeFile="KeepSession.aspx.cs"
Inherits="KeepSession" %>

<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
<title>Refresh Session</title>

<meta http-equiv="refresh" content="1048" />
<!--Here the content value is the no of secconds
after which the page will be reloaded automatically. Make it less than the session
expire time -->

</head>

<body>

<form id="form1" runat="server

form>

</body>

</html>
Posted by Tarun Ghosh at 10:27 PM 3 comments
Labels: , , , ,

Wednesday, February 6, 2008

How to view all triggers

To view the trigger from a particular database Table
exec sp_helpTrigger @tabname='QTQuotationMaster'

If you want to see all the triggers available in the database then run this
SQL Server 2000:
select * from sysobjects where xtype ='TR'

SQL Server 2005:
Select * from sys.triggers
Posted by Tarun Ghosh at 5:27 PM 0 comments
Labels: , , ,

Wednesday, October 24, 2007

Using text, ntext Data Type SQL Server

In sql server 2000 (or before ver) we uses Text,NText data type for storing very long length of string datatype. Text is suitable for non-unicode (1 byte) string and NText for unicode(2 bytes). we can't manipulate these datatype with the regular dmls, but it requires some other way because of it's storing process which is different from other data types (except Image which is stored in the same way for binary data). These datatypes's values are stored outside the row and a 16 bit pointer is tored which points to the root of the internal pointer which points the fragment of the data stored in different pages.
We need to use READTEXT (to read), WRITETEXT ( to replace ) and UPDATETEXT (to modify) these values.
First create a table and insert some values in it
CREATE TABLE TestNTextDataType ( [Id] [int] NULL , [Name] [Ntext] ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
insert into TestNTextDataType values(1,'This is a test')
insert into TestNTextDataType values(2,'This is second test')
Declare a pointer to the 'Name' Column as
Declare @ptrToCol varbinary(16)
Select @ptrToCol=TextPtr([Name]) from TestNTextDataType where [Id]=1
The @ptrToCol pointer points to the NText value 'This is a test'.

READTEXT { table.columnName @ptrToCol @offset @size } [ HOLDLOCK ]
Reads values from a column, starting from a specified offset and reading the specified number of bytes.
READTEXT TestNTextDataType.[Name] @ptrToCol 5 2
Result: is
The value of offset is 5 and as it is zero based it points to the 6th character 'i' and the length is 2 so the two characters starting from 'i' is returned. If you pass 0 as the size then 4KB of data is read.
Read More

WRITETEXT { table.columnName @ptrToCol } [ WITH LOG ] { data }
Permits minimally logged, interactive updating of an existing column. WRITETEXT overwrites any existing data in the column it affects
WRITETEXT TestNTextDataType.[Name] @ptrToCol 'Updated Test Final'
It will update the 'This is a test' value to the new value 'Updated Test Final'. Remember that the pointer @ptrToCol points to the [Name] column of the row with the id=1 (Where clause).
Read More

UPDATETEXTUPDATETEXT { table.columnName @ptrToCol } { NULL @insertOffset } { NULL @deleteLength } [ WITH LOG ] [ inserted_data]
Updates an existing field. Use UPDATETEXT to change only a part of a text, ntext, or image column in place.
UPDATETEXT TestNTextDataType.[Name] @ptrToCol 8 4 'New String'
Previous value was 'Updated Test Final' insert offset 8 points to 'T' of Test and the delete length 4 deletes the 'Test' and inserts the value 'New String'. So the result value becomes 'Updated New String Final'.
Read More

DataLength(Expression)
To get the length of the column value for Text,NText and Image datatype you have to use DataLength function.
Select DataLength([Name]) Length from TestNTextDataType where [Id]=1
will return 48 as the length of 'Updated New String Final' string because the datatype is NText of the column so every charter will take 2 byte and there are 24 characters. If the datatype was Text then it will return 24. The DATALENGTH of NULL is NULL.

PATINDEX , SET TEXTSIZE, SUBSTRING, TEXTVALID are use useful functions to work with these datatypes.
Note: ntext, text, and image data types will be removed in a future version of Microsoft SQL Server. Avoid using these data types.
Posted by Tarun Ghosh at 4:05 PM 0 comments
Labels: , , , , , , , ,
Subscribe to: Posts (Atom)