Thursday, September 22, 2011

How to get all stored procedures in database ?

 some times we need to know which stored procedure we are using for a single database and for that we need to note down all stored procedure name. Its takes lot of time and passions i am also faced this time of problem so many times . So i decided to looking for a better option so can we get the stored procedures name very easily
 And finally i found this query which returns stored procedure  name from the database.

 Here is the query to get all stored procedures in the database

Select
   Pro.name As ProcedureName
From
   sys.procedures Pro
Inner Join
   sys.schemas Shma
ON
   Pro.schema_id = Shma.schema_id

Friday, September 9, 2011

How to redirect to the login page automatically when session timeout in asp.net C#

 On my last project my client ask me to do some thing so can after certain of time if the website page is in idle mode for a specified time(session timeout) then the current user should automatically redirect to the login page! very interesting. i took this a a challenge and thanks god i able to do this.

Here i am showing  how to redirect to the login page automatically when session timeout in asp.net C#

Write this code in to the masterpage's cs file

 protected override void OnPreRender(EventArgs e)
        {
            base.OnPreRender(e);
            this.HeadContent.Controls.Add(new LiteralControl(
                String.Format("<meta http-equiv='refresh' content='{0};url={1}'>",
                SessionTimeOutWithin * 60, PageToSentAfterSessionTimeOut)));
        }
This will add a metatag into the page header which will refresh the CheckSessionTimeOut.aspx page after SessionTimeOutWithin* 60 seconds when the session timeout occurs.

//This the page to be refresh after sessioin timeout
public string PageToSentAfterSessionTimeOut
        {
            get { return "CheckSessionTimeOut.aspx"; }
        }

//This takes the Session.Timeout from the webconfig or from where the Session.Timeout is being set
 public int SessionTimeOutWithin
        {
            get { return Session.Timeout; }
        }

// Add this within the <system.web> tag in web.config file . Here i am set 1 min as Session timeout

    <sessionState mode="InProc" cookieless="true" timeout="1"></sessionState>

 This will set the session timeout as 1 minute

Mode: means what happens if page is in active mode , InProc means session timeout will increase 1 minute more after the page activity time but if the page is in idle for 1 minute then session timeout will occurs.

How to split values in sql server?

 Here i am showing how to split values in Sql server as Split function did  in c#

Why should we take some string value from a table and split that on our C# programming why not in the SQL programming? so many times we prefer to save a One to Many relation type with a single field in a SQL db table by concatenating the selected item with a separator like"1,4,8,7". But as we know SQL don't have a function like C# has Split(). So many programmer takes that value from db and split it in the C# code. But i am not happy with that as always going for better option and i implement this Function for that and thanks god its works..

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
Create function [dbo].[fnSplit](
    @sInputList VARCHAR(8000) -- List of delimited items
  , @sDelimiter VARCHAR(8000) = ',' -- delimiter that separates items
) RETURNS @List TABLE (item VARCHAR(8000))

BEGIN
DECLARE @sItem VARCHAR(8000)
WHILE CHARINDEX(@sDelimiter,@sInputList,0) <> 0
 BEGIN
 SELECT
  @sItem=RTRIM(LTRIM(SUBSTRING(@sInputList,1,CHARINDEX(@sDelimiter,@sInputList,0)-1))),
  @sInputList=RTRIM(LTRIM(SUBSTRING(@sInputList,CHARINDEX(@sDelimiter,@sInputList,0)+LEN(@sDelimiter),LEN(@sInputList))))

 IF LEN(@sItem) > 0
  INSERT INTO @List SELECT @sItem
 END

IF LEN(@sInputList) > 0
 INSERT INTO @List SELECT @sInputList -- Put the last item in
RETURN
END
--select * from dbo.fnSplit('1,2,34,5', ',') where item=34
--select * from dbo.fnSplit('1,2,34,5', ',')

Hope this will helps you tooooo :)