Yogesh Bhadauriya

Ideal SQL Query For Handling Error & Transcation in MS SQL

2012-01-24T04:12:00.001-08:00

BEGIN TRY

BEGIN TRAN

--put queries here

COMMIT;

END TRY
BEGIN CATCH
IF @@TRANCOUNT>0
BEGIN
SELECT @@ERROR,ERROR_MESSAGE ( )
ROLLBACK;
END
END CATCH

any one can use above code to manage transaction as well as error handling in sql server.

Capitalize the First Character of Each Word using queries

2011-05-10T08:00:00.000-07:00

I have found t-sql challenges question where they were asked to provide a solution of oracle INITCAP function in sql.

I was started to find the solution of INITCAP function & found lots of user defined function.

But here I have shared a solution where queries doesn't use any user defined function as well as any variable.

--First of all create one temp table    
DECLARE @t TABLE(ID INT IDENTITY, Data VARCHAR(1000))

--insert the sample data
INSERT INTO @t 
SELECT 'yogesh bhadauriya' 


--use the Common table expression
;with CTE AS
(

    --anchor query where we are finding the first character for replacing with uppercase
    SELECT Cast(STUFF(LOWER(Data), 1, 1, UPPER(LEFT(Data, 1))) As varchar(max)) As Data,
    PATINDEX('%[^a-zA-Z][a-z]%', Data COLLATE Latin1_General_Bin) As Position,ID,Data As OldData
    From @t
    
    Union All
    
   --recursive query
    SELECT Cast(STUFF(CTE.Data, Position, 2, UPPER(SUBSTRING(CTE.Data, CTE.Position, 2))) As Varchar(max)) as Data,
    Cast(PATINDEX('%[^a-zA-Z][a-z]%', CTE.Data COLLATE Latin1_General_Bin) As Int) As Position,CTE.ID,T.Data As OldData
    From @t As T
    Inner Join CTE On CTE.ID=CTE.ID
    where CTE.Data is not null
)
Select ID,OldData As Data,Data As Results
From CTE
where Data is not null
and Position=0


---output
--Id Data    Results
--1 yogesh bhadauriya Yogesh Bhadauriya

User defined function to convert the number into charcter

2011-04-29T07:50:00.000-07:00

How to convert the numeric value into its specific character? If this is your question, than create the following User Defined Function into your database.

--create the following function 
CREATE FUNCTION dbo.udf_Num_ToWords 
(
 @Number Numeric (38, 0) -- Input number with as many as 18 digits

) RETURNS VARCHAR(8000) 
/*
* Converts a integer number as large as 34 digits into the 
* equivalent words.  The first letter is capitalized.
*
* Attribution: Based on NumberToWords by Srinivas Sampath
*        as revised by Nick Barclay
*
* Example:
select dbo.udf_Num_ToWords (1234567890) + CHAR(10)
      +  dbo.udf_Num_ToWords (0) + CHAR(10)
      +  dbo.udf_Num_ToWords (123) + CHAR(10)
select dbo.udf_Num_ToWords(76543210987654321098765432109876543210)
 
DECLARE @i numeric (38,0)
SET @i = 0
WHILE @I <= 1000 BEGIN 
    PRINT convert (char(5), @i)  
            + convert(varchar(255), dbo.udf_Num_ToWords(@i)) 
    SET @I  = @i + 1 
END
*
* Published as the T-SQL UDF of the Week Vol 2 #9 2/17/03
****************************************************************/
AS BEGIN

DECLARE @inputNumber VARCHAR(38)
DECLARE @NumbersTable TABLE (number CHAR(2), word VARCHAR(10))
DECLARE @outputString VARCHAR(8000)
DECLARE @length INT
DECLARE @counter INT
DECLARE @loops INT
DECLARE @position INT
DECLARE @chunk CHAR(3) -- for chunks of 3 numbers
DECLARE @tensones CHAR(2)
DECLARE @hundreds CHAR(1)
DECLARE @tens CHAR(1)
DECLARE @ones CHAR(1)

IF @Number = 0 Return 'Zero'

-- initialize the variables
SELECT @inputNumber = CONVERT(varchar(38), @Number)
     , @outputString = ''
     , @counter = 1
SELECT @length   = LEN(@inputNumber)
     , @position = LEN(@inputNumber) - 2
     , @loops    = LEN(@inputNumber)/3

-- make sure there is an extra loop added for the remaining numbers
IF LEN(@inputNumber) % 3 <> 0 SET @loops = @loops + 1

-- insert data for the numbers and words
INSERT INTO @NumbersTable   SELECT '00', ''
    UNION ALL SELECT '01', 'one'      UNION ALL SELECT '02', 'two'
    UNION ALL SELECT '03', 'three'    UNION ALL SELECT '04', 'four'
    UNION ALL SELECT '05', 'five'     UNION ALL SELECT '06', 'six'
    UNION ALL SELECT '07', 'seven'    UNION ALL SELECT '08', 'eight'
    UNION ALL SELECT '09', 'nine'     UNION ALL SELECT '10', 'ten'
    UNION ALL SELECT '11', 'eleven'   UNION ALL SELECT '12', 'twelve'
    UNION ALL SELECT '13', 'thirteen' UNION ALL SELECT '14', 'fourteen'
    UNION ALL SELECT '15', 'fifteen'  UNION ALL SELECT '16', 'sixteen'
    UNION ALL SELECT '17', 'seventeen' UNION ALL SELECT '18', 'eighteen'
    UNION ALL SELECT '19', 'nineteen' UNION ALL SELECT '20', 'twenty'
    UNION ALL SELECT '30', 'thirty'   UNION ALL SELECT '40', 'forty'
    UNION ALL SELECT '50', 'fifty'    UNION ALL SELECT '60', 'sixty'
    UNION ALL SELECT '70', 'seventy'  UNION ALL SELECT '80', 'eighty'
    UNION ALL SELECT '90', 'ninety'   

WHILE @counter <= @loops BEGIN

 -- get chunks of 3 numbers at a time, padded with leading zeros
 SET @chunk = RIGHT('000' + SUBSTRING(@inputNumber, @position, 3), 3)

 IF @chunk <> '000' BEGIN
  SELECT @tensones = SUBSTRING(@chunk, 2, 2)
       , @hundreds = SUBSTRING(@chunk, 1, 1)
       , @tens = SUBSTRING(@chunk, 2, 1)
       , @ones = SUBSTRING(@chunk, 3, 1)

  -- If twenty or less, use the word directly from @NumbersTable
  IF CONVERT(INT, @tensones) <= 20 OR @Ones='0' BEGIN
   SET @outputString = (SELECT word 
                                      FROM @NumbersTable 
                                      WHERE @tensones = number)
                   + CASE @counter WHEN 1 THEN '' -- No name
                       WHEN 2 THEN ' thousand ' WHEN 3 THEN ' thousand '
                       WHEN 4 THEN ' billion '  WHEN 5 THEN ' trillion '
                       WHEN 6 THEN ' quadrillion ' WHEN 7 THEN ' quintillion '
                       WHEN 8 THEN ' sextillion '  WHEN 9 THEN ' septillion '
                       WHEN 10 THEN ' octillion '  WHEN 11 THEN ' nonillion '
                       WHEN 12 THEN ' decillion '  WHEN 13 THEN ' undecillion '
                       ELSE '' END
                               + @outputString
      END
   ELSE BEGIN -- break down the ones and the tens separately

             SET @outputString = ' ' 
                            + (SELECT word 
                                    FROM @NumbersTable 
                                    WHERE @tens + '0' = number)
              + '-'
                             + (SELECT word 
                                    FROM @NumbersTable 
                                    WHERE '0'+ @ones = number)
                   + CASE @counter WHEN 1 THEN '' -- No name
                       WHEN 2 THEN ' thousand ' WHEN 3 THEN ' million '
                       WHEN 4 THEN ' billion '  WHEN 5 THEN ' trillion '
                       WHEN 6 THEN ' quadrillion ' WHEN 7 THEN ' quintillion '
                       WHEN 8 THEN ' sextillion '  WHEN 9 THEN ' septillion '
                       WHEN 10 THEN ' octillion '  WHEN 11 THEN ' nonillion '
                       WHEN 12 THEN ' decillion '   WHEN 13 THEN ' undecillion '
                       ELSE '' END
                            + @outputString
  END

  -- now get the hundreds
  IF @hundreds <> '0' BEGIN
   SET @outputString  = (SELECT word 
                                      FROM @NumbersTable 
                                      WHERE '0' + @hundreds = number)
                 + ' hundred ' 
                                + @outputString
  END
 END

 SELECT @counter = @counter + 1
      , @position = @position - 3

END

-- Remove any double spaces
SET @outputString = LTRIM(RTRIM(REPLACE(@outputString, '  ', ' ')))
SET @outputstring = UPPER(LEFT(@outputstring, 1)) + SUBSTRING(@outputstring, 2, 8000)


RETURN @outputString -- return the result
END

To check the output of the above User Defined Function, execute the below query into your database.

--now run the following query to get the result

select dbo.udf_Num_ToWords(5800000)

--output
--Five thousand eight hundred thousand

--
Happy Coding to All

Recursive Query To Find All Child Node in SQL server 2005+

2011-04-29T07:44:00.000-07:00

How to find the all child of particular parent using the Recursive Query

--here we are creating the table variable so that we can insert some dummy record
Declare @Table Table
(
 TId int,
 ParentId int,
 Name varchar(10)
)
--inserting some records
--using the union all to insert more than one records
insert into @Table
Select 1,NULL,'ICT'
Union All
Select 2,1,'ICT-M1'
Union All
Select 4,1,'ICT-M2'
Union All
Select 7,2,'ICT-M1U1'
Union All
Select 8,2,'ICT-M1U2'
Union All
Select 9,4,'ICT-M2U1'
Union All
Select 10,4,'ICT-M2U2'
Union All
Select 11,7,'ICT-M1U1P1'
Union All
Select 12,7,'ICT-M1U1P2'
Union All
Select 13,8,'ICT-M1U2P1'
Union All
Select 14,8,'ICT-M1U2P2'
Union All
Select 15,9,'ICT-M2U1P1'
Union All
Select 16,9,'ICT-M2U1P2'
Union All
Select 17,10,'ICT-M2U2P1'
Union All
Select 18,10,'ICT-M2U2P2'

--variable to hold data
Declare @ChildNode varchar(1000)
Set @ChildNode='';

 
--use the standard recursive query
;with [CTE] as 
(
 --anchor query where we are finding the all parents
    select TId,ParentId,Name,CAST(ISNULL(CAST(ParentId as varchar(10)),'0') As Varchar(100)) As ChildNode
    from @Table c where c.[ParentId] is null

    union all

 --recursive query where we are finding the all child according to anchor query parent
    select c.TId,c.ParentId,c.Name,
    CAST( p.ChildNode +','+cast(c.TId as varchar(10) ) As Varchar(100)) As ChildNode
    from [CTE] p, @Table c where c.[ParentId] = p.[TId]    
)
--select the child node as per the id
--Assigin the all Ids into  one variable
select @ChildNode=@ChildNode+','+Cast(TId as varchar(10))
from [CTE]
Cross Apply
dbo.Split(ChildNode,',')
where items=9
order by TId

select SUBSTRING(@ChildNode,2,LEN(@ChildNode))

--outpu
--2,7,8,11,12,13,14


----
--create the tabular function to split the multi valued into table
Create FUNCTION [dbo].[Split](@String varchar(8000), @Delimiter char(1))     
returns @temptable TABLE (items varchar(8000))     
as     
begin     
 declare @idx int     
 declare @slice varchar(8000)     
    
 select @idx = 1     
  if len(@String)<1 or @String is null  return     
    
 while @idx!= 0     
 begin     
  set @idx = charindex(@Delimiter,@String)     
  if @idx!=0     
   set @slice = left(@String,@idx - 1)     
  else     
   set @slice = @String     
  
  if(len(@slice)>0)
   insert into @temptable(Items) values(@slice)     

  set @String = right(@String,len(@String) - @idx)     
  if len(@String) = 0 break     
 end 
return     
end

--

Happy Coding

How to insert multiple record using xml in sql server 2005+

2011-04-15T20:40:00.000-07:00

How to insert the multiple record using the xml into sql server, please check the following example.

DECLARE @idoc int
DECLARE @doc varchar(max)
SET @doc ='
<ROOT>
 <Trans TransId="1" Add="false" Edit="true" Delete="true" View="true" Block="false">   
 </Trans>
 <Trans TransId="2" Add="1" Edit="1" Delete="1" View="1" Block="0">   
 </Trans>
</ROOT>'

--Create an internal representation of the XML document.
EXEC sp_xml_preparedocument @idoc OUTPUT, @doc
-- Execute a SELECT statement that uses the OPENXML rowset provider.


SELECT *
Into #TempTable 
FROM OPENXML(@idoc, '/ROOT/Trans',1)
WITH 
( 
 TransId  varchar(10),
    [Add] bit,
    Edit bit,
    [Delete] bit,
    [View] bit,
    Block bit
)

Select * From #TempTable

drop table #TempTable

--

happy coding

Performance Comparision Between For, While and Foreach Loop

2010-12-14T01:57:00.000-08:00

Today I was posted one post regarding list to datatable conversion. But one of the my senior told me that try to avoid foreach loop.

So that I was googled/binged regarding the same.

Here I am showing you some figure for the same here.

Using the System.Diagnostics.Stopwatch class I ran some tests. 100,000 iterations in a for loop that did nothing inside took me 0.0003745 seconds. This was the code for the loop:

for (int i = 0; i < 100000; i++) ;

The while loop resulted 0.0003641 seconds, which is pretty much the same as the for loop. Here is the code I used:

int i=0;
while (i < 100000)
 i++;

The foreach loop has a slightly different purpose. It is meant for itterating through some collection that implements IEnumerable. It's performance is much slower, my test resulted in 0.0009076 seconds with this code:

int[] test = new int[100000];
foreach (int i in test) ;

foreach creates an instance of an enumerator (returned from GetEnumerator) and that enumerator also keeps state throughout the course of the foreach loop. It then repeatedly calls for the Next() object on the enumerator and runs your code for each object it returns.

So, it seems that 'while' is the fastest looping technique among the three available techniques in C#, for a given processing within the loop. Right?

It varies. "while" and "for" have pretty much the same results. While had a slight advantage, but not one that great.

Im not an expert, but I have the feeling that a while and for loop, once compiled to MSLI, probably are both the exact same thing.

And I wouldn't be surprised if foreach was faster when iterating through objects, since the optimizer can "expect" whats going to happen... So I'd go with foreach being faster if you can use it, and the 2 others being the same thing, if foreach isn't applicable.

My logic here is that if you're looping through a collection, and you use a for loop for example, you're going to have to use the index of the collection, which depending on implementation, could be a minor performance hit to "seek" the object, as opposed to going through a well written iterator.

Just an example.So really: I wouldn't care too much about performance of these loops. This isn't C/C++, and when you compile, its not native code (at first). So it is safe to assume that the solution that seems the most efficient "logically" will be so in practice.

For each is slower for a number of reasons. One is it is using the IEnumerable interface, which requires some casting (assuming you aren't using a generic collection). My test above seemed to go along with that as well. A simple for/while/do loop is pretty much as simple as it gets.

As for the MSIL code, let's take a look. This is the MSIL for the for loop:

IL_0000:  nop
  IL_0001:  ldc.i4.0
  IL_0002:  stloc.0
  IL_0003:  br.s       IL_0009
  IL_0005:  ldloc.0
  IL_0006:  ldc.i4.1
  IL_0007:  add
  IL_0008:  stloc.0
  IL_0009:  ldloc.0
  IL_000a:  ldc.i4     0x186a0
  IL_000f:  clt
  IL_0011:  stloc.1
  IL_0012:  ldloc.1
  IL_0013:  brtrue.s   IL_0005

And here it is for the while loop:

IL_0000:  nop
  IL_0001:  ldc.i4.0
  IL_0002:  stloc.0
  IL_0003:  br.s       IL_0009
  IL_0005:  ldloc.0
  IL_0006:  ldc.i4.1
  IL_0007:  add
  IL_0008:  stloc.0
  IL_0009:  ldloc.0
  IL_000a:  ldc.i4     0x186a0
  IL_000f:  clt
  IL_0011:  stloc.1
  IL_0012:  ldloc.1
  IL_0013:  brtrue.s   IL_0005

So yes, they are exactly identical.

Although very handy, C#'s foreach statement is actually quite dangerous. In fact, I may swear off its use entirely. Why? Two reasons: (1) performance, and (2) predictability.

Performance

Iterating through a collection using foreach is slower than with for. I can't remember where I first learned that, perhaps in Patterns & Practices: Improving .Net Application Performance. Maybe it was from personal experience. How much slower? Well, I suppose that depends on your particular circumstances. Here are a few interesting references:

FOREACH Vs. FOR (C#): shows the extra IL code created for a foreach loop
To foreach or not to foreach that is the question: claims it isn't so clear-cut, at least for regular arrays. Not absolutely conclusive.

Predictability

I was looking at the C# Reference entry for foreach today and noticed this for the first time (italics added by me):

The foreach statement is used to iterate through the collection to get the desired information, but should not be used to change the contents of the collection to avoid unpredictable side effects.

What's that all about? Let's take this as an example:

foreach(MyClass myObj in List)

Looking deeper into the C# Language Specification... the iteration variable is supposed to be read-only, though apparently that doesn't stop you from updating a property of an object. Thus for instance it would be illegal to assign a new value to myObj, but not to assign a new value to myObj.MyProperty.
And that's all I can find. Why are there unpredictable side effects? I don't know. But seems best to heed Microsoft's warning.

Conclusion

Some argue that you shouldn't code for performance from the beginning, and therefore go ahead and use foreach whenever you want so long as you don't update the values. In my experience that's hogwash — most of the code I work on goes into environments where performance is extremely important. Besides, writing a for statement requires very little extra coding compared to a foreach statement. Furthermore, if you have a lot going on inside your iteration block, it can be easy to forget and accidentally update the iteration variable inside a foreach loop. Thus do I conclude: just avoid foreach altogether.

Honestly the academically correct answer is "It's Irrelevant". You cant optimize performance by somehow picking "the best loop". If you're doing performance optimizations you should start with a Big O analysis of your algorithms and Profiling. You'd be amazed at how much faster keeping around a dictionary for lookups or using a sorted list + binary search is than looping over a list every time you need to find an object.

Doing so will give you unnoticeable performance increases at the cost of maintainability and programmer time and the effort you're putting in to such small performance boosts is better spent on a proper design and implementation. Let's take a real world example of finding duplicates in a list to illustrate my point:
first let's assume that comparing two items is O(1). We can implement this any number of ways, two of which are:

A) use nested loops with i on the outer loop and j on the inner loop, when list[i] == list[j] push the pair onto a list of duplicates.
B) copy the list to tmpList. quicksort tmpList. iterate over the list with i, when list[i] == list[i+1] push the pair onto the list of duplicates

No amount of optimization can change the fact that A runs in O(n^2) while B runs in O(2*n+n*log(n)).

Convert List to DataTable

2010-12-13T23:21:00.000-08:00

One day I confused to convert the List collection to datatable object. I was wondering how to iterate the columns wise and rows wise in list to make some calculation.

I got the solution which was posted below:

public DataTable ListToDataTable(IEnumerable list)
{
    var dt = new DataTable();

    foreach (var info in typeof(T).GetProperties())
    {
        dt.Columns.Add(new DataColumn(info.Name, info.PropertyType));
    }
    foreach (var t in list)
    {
        var row = dt.NewRow();
        foreach (var info in typeof(T).GetProperties())
        {
            row[info.Name] = info.GetValue(t, null);
        }
        dt.Rows.Add(row);
    }
    return dt;
}

--
Happy coding.

Age calculation with SQL Server

2010-05-19T01:54:00.000-07:00

There seem to be many different methods being suggested to calculate an age in SQLServer. Some are quite complex but most are simply wrong. This is by far the simplest and accurate method that I know.

Declare @Date1 datetime
Declare @Date2 datetime


Select @Date1 = '15Feb1971'Select @Date2 = '08Dec2009'select CASE
WHEN dateadd(year, datediff (year, @Date1, @Date2), @Date1) > @Date2
THEN datediff (year, @Date1, @Date2) - 1
ELSE datediff (year, @Date1, @Date2)END as Age

--

happy coding

[WPF] How to assign a dynamic resource from code-behind ?

2010-04-19T01:24:00.000-07:00

When working on WPF projects, it’s mandatory to assign resources to user interface controls. When you work in XAML, it’s pretty simple: you just need to use the MarkupExtension named StaticResource (or DynamicResource if the resource is going to be modified):

<Button Content="Find Position" Click="Button_Click" Background="{DynamicResource brush}" />

But, how to do the same using code-behind ? The key is to use the method SetResourceReference (http://msdn.microsoft.com/en-us/library/system.windows.frameworkelement.setresourcereference.aspx):

<Window.Resources>
    Key="brush" Color="Red" />
Window.Resources>

this.btn.SetResourceReference(BackgroundProperty,  "brush");

As you can see, it’s really simple to use: you define the resource, you define the control and, in code behind, you call the method SetResourceReference and use the following parameters:

the DependencyProperty on which the resource will be applied
the name of the resource

Happy coding !

Reset Identity Column Value in SQL Server

2010-02-24T03:20:00.000-08:00

If you are using an identity column on your SQL Server tables, you can set the next insert value to whatever value you want. An example is if you wanted to start numbering your ID column at 1000 instead of 1.

It would be wise to first check what the current identify value is. We can use this command to do so:

DBCC CHECKIDENT ('tablename', NORESEED)

For instance, if I wanted to check the next ID value of my orders table, I could use this command:

DBCC CHECKIDENT (orders, NORESEED)

To set the value of the next ID to be 1000, I can use this command:
DBCC CHECKIDENT (orders, RESEED, 999)

Note that the next value will be whatever you reseed with + 1, so in this case I set it to 999 so that the next value will be 1000.

Another thing to note is that you may need to enclose the table name in single quotes or square brackets if you are referencing by a full path, or if your table name has spaces in it. (which it really shouldn’t)

DBCC CHECKIDENT ('databasename.dbo.orders',RESEED, 999)

Custom Paging In GridView without objectDataSource

2010-02-15T05:27:00.000-08:00

All we need to use the Custom paging in grid view without object data source.

Here i am going to explain my code which is used in custom paging of grid view... What you will need to do is used your DataSource SP or query with this paging...

All magic lies in SQL Server 2005 ROWNumber() Function.... Simple SP for this gridview datasource is

Select Row,ID,Name
(
    Select ROW_Number()OVER(ORDER BY ID) As Row,ID,Name 
    from table1
) AS A
Where Row=>@PageIndex*PageSize
and Row<(@PageIndex+1)*PageSize;
--here @PageIndex and @PageSize are passed as parameter as gridview1.PageIndex and gridview1.PageSize

if you are not familiar with RowNumber function than create one temp table use Row as primary key with auto increament number and than use insert select statement.... this will work in all database.....

Follow is the C# code for custom gridview...I m creating new grid view control here

using System; 
using System.Collections.Generic; 
using System.ComponentModel; 
using System.Text; 
using System.Web; 
using System.Web.UI; 
using System.Web.UI.WebControls;  
namespace CustomPagingGridView 
{  
    [DefaultProperty("Text")]  
    [ToolboxData("<{0}:CustomePagingGrid runat=server>CustomePagingGrid>")]  
    public class CustomePagingGrid : GridView 
    {   
        public CustomePagingGrid(): base()
        {}
        
        #region Custom properties       
        // this property is to use to find the total number of record for grid         
        [Browsable(true), Category("NewDynamic")]         
        [Description("Set the virtual item count for this grid")]         
        public int VirtualItemCount         
        {             
            get
            {
                if (ViewState["pgv_vitemcount"] == null)
                    ViewState["pgv_vitemcount"] = -1;                 
                return Convert.ToInt32(ViewState["pgv_vitemcount"]);             
            }             
            set             
            {                 
                ViewState["pgv_vitemcount"] = value;             
            }         
        }         
        
        // this is used to sort the gridview columns        
        [Browsable(true), Category("NewDynamic")] [Description("Get the order by string to use for this grid when sorting event is triggered")] 
        public string OrderBy         
        {             
            get             
            {                 
                if (ViewState["pgv_orderby"] == null)                     
                    ViewState["pgv_orderby"] = string.Empty;                 
                return ViewState["pgv_orderby"].ToString();             
            }             
            protected set             
            {                 
                ViewState["pgv_orderby"] = value;             
            }         
        }            
        
        private int Index         
        {             
            get             
            {                 
                if (ViewState["pgv_index"] == null)                     
                    ViewState["pgv_index"] = 0;                 
                return Convert.ToInt32(ViewState["pgv_index"]);             
            }             
            set             
            {                 
                ViewState["pgv_index"] = value;             
            }         
        }            
        
        public int CurrentPageIndex         
        {             
            get             
            {                 
                if (ViewState["pgv_pageindex"] == null)                     
                    ViewState["pgv_pageindex"] = 0;                 
                return Convert.ToInt32(ViewState["pgv_pageindex"]);             
            }             
            set             
            {                 
                ViewState["pgv_pageindex"] = value;             
            }         
        }            
        
        private int SetCurrentIndex         
        {             
            get             
            {                 
                return CurrentPageIndex;             
            }             
            set             
            {                 
                CurrentPageIndex = value;             
            }         
        }                 
        
        
        // if this property is set to greater than zero means custom paging neede                    
        private bool CustomPaging         
        {             
            get             
            {                 
                return (VirtualItemCount != -1);             
            }         
        }          
        #endregion             
        
        #region Overriding the parent methods           
        public override object DataSource         
        {             
            get             
            {                 
                return base.DataSource;             
            }             
            set             
            {                 
                base.DataSource = value;                 
                // we store the page index here so we dont lost it in databind                 
                CurrentPageIndex = PageIndex;             
            }         
        }     
        
        protected override void OnSorting(GridViewSortEventArgs e) 
        { 
            // We store the direction for each field so that we can work out whether next sort 
            // should be asc or desc order  PageIndex = CurrentPageIndex; SortDirection direction = SortDirection.Ascending;  
            if(ViewState[e.SortExpression]!=null&& (SortDirection)ViewState[e.SortExpression] == SortDirection.Ascending)  
            {   
                direction = SortDirection.Descending; 
            }  
            ViewState[e.SortExpression] = direction;             
            OrderBy = string.Format("{0} {1}", e.SortExpression, (direction == SortDirection.Descending ? "DESC" : ""));             
            base.OnSorting(e);         
        }            
        
        protected override void InitializePager(GridViewRow row, int columnSpan, PagedDataSource pagedDataSource)         
        {             
            // This method is called to initialise the pager on the grid. We intercepted this and override             
            // the values of pagedDataSource to achieve the custom paging using the default pager supplied             
            if (CustomPaging)             
            {                 
                pagedDataSource.AllowCustomPaging = true;                 
                pagedDataSource.VirtualCount = VirtualItemCount;                 
                pagedDataSource.CurrentPageIndex = CurrentPageIndex;             
            }             
            base.InitializePager(row, columnSpan, pagedDataSource);         
        }               
        
        // here we do custom paging         
        protected override void OnPageIndexChanging(GridViewPageEventArgs e)         
        {             
            if (CustomPaging)             
            {                 
                if (this.PagerSettings.Mode == PagerButtons.NumericFirstLast || this.PagerSettings.Mode == PagerButtons.Numeric)                 
                {                     
                    base.OnPageIndexChanging(e);                 
                }                 
                else                 
                {                     
                    if (e.NewPageIndex == -1)                     
                    {                         
                        Index -= 1;                     
                    }                     
                    else if (e.NewPageIndex == 0)
                    {                         
                        Index = 0;                     
                    }                     
                    else if (e.NewPageIndex == ((int)Math.Ceiling((decimal)(VirtualItemCount) / PageSize) - 1))                     
                    {                         
                        Index = ((int)Math.Ceiling((decimal)(VirtualItemCount) / PageSize) - 1);                     
                    }                     
                    else                     
                    {                         
                        Index += 1;                     
                    }                     
                    if (Index < 0)                     
                    { 
                        Index = 0; 
                    }
                    CurrentPageIndex = Index;                     
                    e.NewPageIndex = Index;                     
                    base.OnPageIndexChanging(e);                 
                }             
            }         
        }           
    #endregion     
    } 
}

if u have any doubt,please feel free to ask me....

.NET 3.5 LANGUAGE ENHANCEMENTS

2010-02-10T02:20:00.000-08:00

There are several .NET language enhancements to be introduced with Visual Studio 2008 including implicitly typed variables, extension methods, anonymous types, object initializers, collection initializers and automatic properties. These language enhancements, along with features like generics, are critical to the use of some of the new features, such as LINQ with the ADO.NET Entity Framework. What can be confusing is that these features are often referred to in the same conversation as LINQ. Because of this relation by association, you may be led to believe that these
features are part of LINQ. They are not; they are part of the .NET Framework 3.5 and the VB 9 and C# 3.0 languages. They are very valuable in their own rights as well as playing a huge role for LINQ.

This article will demonstrate and discuss several key language features including:

Automatic Property setters/getters
Object Initializers
Collection Initializers
Extension Methods
Implicitly Typed Variable
Anonymous Type

Automatic Properties

Since creating classes by hand can be monotonous at times, developers use either code generation programs and IDE Add-Ins to assist in creating classes and their properties. Creating properties can be a very redundant process, especially when there is no logic in the getters and setters other than getting and setting the value of the private field. Using public fields would reduce the code required, however public fields do have some drawbacks as they are not supported by some other features such as inherent data binding.

public class Customer
{
private int _customerID;
private string _companyName;
private Address _businessAddress;
private string _phone;
public int CustomerID
{
get { return _customerID; }
set { _customerID = value; }
}
public string CompanyName
{
get { return _companyName; }
set { _companyName = value; }
}
public Address BusinessAddress
{
get { return _businessAddress; }
set { _businessAddress = value; }
}
public string Phone
{
get { return _phone; }
set { _phone = value; }
}
}

how the same result can be achieved through automatic properties with less code than

public class Customer
{
public int CustomerID { get; set; }
public string CompanyName { get; set; }
public Address BusinessAddress { get; set; }
public string Phone { get; set; }
}

Object Initializers

It is often helpful to have a constructor that accepts the key information that can be used to initialize an object. Many code refactoring tools help create constructors like this with .NET 2. However another new feature coming with .NET 3.5, C# 3 and VB 9 is object initialization. Object Initializers allow you to pass in named values for each of the public properties that will then be used to initialize the object.

For example, initializing an instance of the Customer class could be accomplished using the following code:

Customer customer = new Customer();
customer.CustomerID = 101;
customer.CompanyName = "Foo Company";
customer.BusinessAddress = new Address();
customer.Phone = "555-555-1212";

However, by taking advantage of Object Initializers an instance of the Customer class can be created using the following syntax:

Customer customer = new Customer {
CustomerID = 101,
CompanyName = "Foo Company",
BusinessAddress = new Address(),
Phone = "555-555-1212" };

The syntax is to wrap the named parameters and their values with curly braces. Object Initializers allow you to pass in any named public property to the constructor of the class. This is a great feature as it removes the need to create multiple overloaded constructors using different parameter lists to achieve the same goal. While you can currently create your own constructors, Object initializers are nice because you do not have to create multiple overloaded constructors to handle the various combinations of how you might want to initialize the object. To make matters easier, when typing the named parameters the intellisense feature of the IDE will display a list of the named parameters for you. You do not have to pass all of the parameters in and in fact, you can even use a nested object initialize for the BusinessAddress parameter, as shown below.

Customer customer = new Customer
{
CustomerID = 101,
CompanyName = "Foo Company",
BusinessAddress = new Address { City="Somewhere", State="FL" },
Phone = "555-555-1212"
};

Collection Initializers

Initializing collections have always been a bother to me. I never enjoy having to create the collection first and then add the items one by one to the collection in separate statements. (What can I say, I like tidy code.) Like Object Initializers, the new Collection Initializers allow you to create a collection and initialize it with a series of objects in a single statement. The following statement demonstrates how the syntax is very similar to that of the Object Initializers. Initializing a List is accomplished by passing the instances of the Customer objects wrapped inside of curly braces.

List custList = new List 
{ customer1, customer2, customer3 };

Collection Initializers can also be combined with Object Initializers. The result is a slick piece of code that initializes both the objects and the collection in a single statement.

List custList = new List
{
new Customer {ID = 101, CompanyName = "Foo Company"},
new Customer {ID = 102, CompanyName = "Goo Company"},
new Customer {ID = 103, CompanyName = "Hoo Company"}
};

The List and its 3 Customers from this example could also be written without Object Initializers nor Collection Initializers, in several lines of code. The syntax for that could look something like this without using these new features:

Customer customerFoo = new Customer();
customerFoo.ID = 101;
customerFoo.CompanyName = "Foo Company";
Customer customerGoo = new Customer();
customerGoo.ID = 102;
customerGoo.CompanyName = "Goo Company";
Customer customerHoo = new Customer();
customerHoo.ID = 103;
customerHoo.CompanyName = "Hoo Company";
List customerList3 = new List();
customerList3.Add(customerFoo);
customerList3.Add(customerGoo);
customerList3.Add(customerHoo);

Extension Methods

Have you ever looked through the list of intellisense for an object hoping to find a method that handles your specific need only to find that it did not exist? One way you can handle this is to use a new feature called Extension Methods. Extension methods are a new feature that allows you to enhance an existing class by adding a new method to it without modifying the actual code for the class. This is especially useful when using LINQ because several extension methods are available in writing LINQ query expressions.

For example, imagine that you want to cube a number. You might have the length of one side of a cube and you want to know its volume. Since all the sides are the same length, it would be nice to simply have a method that calculates the cube of an integer. You might start by looking at the System.Int32 class to see if it exposes a Cube method, only to find that it does not. One solution for this is to create an extension method for the int class that calculates the Cube of an integer. Extension Methods must be created in a static class and the Extension Method itself must be defined as static. The syntax is pretty straightforward and familiar, except for the this keyword that is passed as the first parameter to the Extension Method. Notice in the code below that I create a static method named Cube that accepts a single parameter. In a static method, preceding the first parameter with the this keyword creates an extension method that applies to the type of that parameter. So in this case, I added an Extension Method called Cube to the int type.

public static class MyExtensions
{
public static int Cube(this int someNumber)
{
return someNumber ^ 3;
}
}

When you create an Extension Method, the method sows up in the intellisense in the IDE, as well. With this new code I can calculate the cube of an integer using the following code sample:

int oneSide = 3;
int theCube = oneSide.Cube(); // Returns 27

As nice as this feature is I do not recommend creating Extension Methods on classes if instead you can create a method for the class yourself. For example, if you wanted to create a method to operate on a Customer class to calculate their credit limit, best practices would be to add this method to the Customer class itself. Creating an Extension method in this case would violate the encapsulation principle by placing the code for the Customer’s credit limit calculation outside of the Customer class.

However, Extension Methods are very useful when you cannot add a method to the class itself, as in the case of creating a Cube method on the int class. Just because you can use a tool, does not mean you should use a tool.

Anonymous Types and Implicitly Typed Variables

When using LINQ to write query expressions, you might want to return information from several classes. It is very likely that you'd only want to return a small set of properties from these classes. However, when you retrieve information from different class sources in this manner, you cannot retrieve a generic list of your class type because you are not retrieving a specific class type. This is where Anonymous Types step in and make things easier because Anonymous Types allow you to create a class structure on the fly.

var dog = new { Breed = "Cocker Spaniel",
Coat = "black", FerocityLevel = 1 };

Notice that the code above creates a new instance of a class that describes a dog. The dog variable will now represent the instance of the class and it will expose the Breed, Coat and Ferocity properties. Using this code I was able to create a structure for my data without having to create a Dog class explicitly. While I would rarely create a class using this feature to represent a Dog, this feature does come in handy when used with LINQ.

When you create an Anonymous Type you need to declare a variable to refer to the object. Since you do not know what type you will be getting (since it is a new and anonymous type), you can declare the variable with the var keyword. This technique is called using an Implicitly Typed Variable. When writing a LINQ query expression, you may return various pieces of information. You could return all of these data bits and create an Anonymous Type to store them. For example, let’s assume you have a List and each Customer has a BusinessAddress property of type Address. In this situation you want to return the CompanyName and the State where the company is located. One way to accomplish this using an Anonymous Type is shown in below.

List customerList = new List
{
new Customer {ID = 101,
CompanyName = "Foo Co",
BusinessAddress = new Address {State="FL"}},
new Customer {ID = 102,
CompanyName = "Goo Co",
BusinessAddress = new Address {State="NY"}},
new Customer {ID = 103,
CompanyName = "Hoo Co",
BusinessAddress = new Address {State="NY"}},
new Customer {ID = 104,
CompanyName = "Koo Co",
BusinessAddress = new Address {State="NY"}}
};
var query = from c in customerList
where c.BusinessAddress.State.Equals("FL")
select new { Name = c.CompanyName,
c.BusinessAddress.State };
foreach (var co in query)
Console.WriteLine(co.Name + " - " + co.State);

Pay particular attention to the select clause in the LINQ query expression. The select clause is creating an instance of an Anonymous Type that will have a Name and a State property. These values come from 2 different objects, the Customer and the Address. Also notice that the properties can be explicitly renamed (CompanyName is renamed to Name) or they can implicitly take on the name as happens with the State property. Anonymous Types are very useful when retrieving data with LINQ.

Summary

There are a lot of new language features coming with .NET 3.5 that both add new functionality and make the using of existing technologies easier. As we have seen in the past, when new technologies have been introduced, such as with generics, they often are the precursors to other technologies. The introduction of Generics allowed us to create strongly typed lists. Now because of those strongly typed lists of objects we will be able to write LINQ query expressions against the strongly typed objects and access their properties explicitly even using intellisense. These new features such as Object Initializers and Anonymous Types are the building blocks of LINQ and other future .NET technologies.

NEW DATA TYPES IN SQL SERVER 2008

2010-02-06T02:38:00.000-08:00

We will take
a look at the following new data types, each of which is available in all editions
of SQL Server 2008: • Date and Time: Four new date and time data types
have been added, making working with time much easier than it ever has in the past.
They include: DATE, TIME, DATETIME2, and DATETIMEOFFSET.
• Spatial: Two new spatial data types have been added--GEOMETRY and GEOGRAPHY--
which you can use to natively store and manipulate location-based information, such
as Global Positioning System (GPS) data. •
HIERARCHYID: The HIERARCHYID data type is used to enable database applications
to model hierarchical tree structures, such as the organization chart of a business.
• FILESTREAM: FILESTREAM is not a data type as
such, but is a variation of the VARBINARY(MAX) data type that allows unstructured
data to be stored in the file system instead of inside the SQL Server database.
Because this option requires a lot of involvement from both the DBA administration
and development side, I will spend more time on this topic than the rest.
Date and Time
In SQL Server 2005 and earlier, SQL Server only offered two date and time data types:
DATETIME and SMALLDATETIME. While they were useful in many cases, they had a lot
of limitations, including:

Both the date value and the
time value are part of both of these data types, and you can’t choose to store one
or the other. This can cause several problems:
It often causes a lot of wasted
storage because you store data you don’t need or want.
It adds unwanted complexity
to many queries because the data types often have to be converted to a different
form to be useful.
It often reduces performance
because WHERE clauses with these data and time data types often have to include
functions to convert them to a more useful form, preventing these queries from using
indexes.
They are not time-zone aware,
which requires extra coding for time-aware applications.
Precision is only .333 seconds,
which is not granular enough for some applications.
The range of supported dates
is not adequate for some applications, and the range does not match the range of
the .NET CLR DATETIME data type, which requires additional conversion code.

To overcome these problems, SQL Server 2008 introduces four new date and time data
types, described in the
following sections. All of these new date and time data types work with SQL Server
2008 date and time functions,
which have been enhanced in order to properly understand the new
In addition, some new date and time functions have been added to take advantage
of the capabilities of
these new data types. The new functions include SYSDATETIME,
TODATETIMEOFFSET, SYSUTCDATETIME,
and DATE.
As you can imagine, the DATE data type only stores a date in the format of YYYY-MM-DD.
It has

a range of 0001-01-01 through
9999-12-32, which should be adequate for most business and

scientific applications. The
accuracy is 1 day, and it only takes 3 bytes to store the date.

        --Sample DATE output
        DECLARE @datevariable as DATE            
        SET @datevariable = getdate()            
        PRINT @datevariable
        Result: 2008-08-15

TIME

TIME is stored in the format:
hh:mm:ss.nnnnnnn, with a range of 00:00:00.0000000 through

23:59:59:9999999 and is accurate
to 100 nanoseconds. Storage depends on the precision and scale
selected, and runs from 3 to 5 bytes.

                    --Sample TIME output                        
                    DECLARE @timevariable as TIME
                    SET @timevariable = getdate()                       
                    PRINT @timevariable                        
                    Result: 14:26:52.3100000

DATETIME2

DATETIME2 is very similar to
the older DATETIME data type, but has a greater range and

precision. The format is YYYY-MM-DD
hh:mm:ss:nnnnnnnm with a range of 0001-01-01

00:00:00.0000000 through 9999-12-31
23:59:59.9999999, with an accuracy of 100 nanoseconds.
depends on the precision and scale selected, and runs from 6 to 8 bytes.

        --Sample DATETIME2 output with a precision of 7
        DECLARE @datetime2variable datetime2(7)
        SET @datetime2variable = Getdate()
        PRINT @datetime2variable
        Result: 2008-08-15 14:27:51.5300000

DATETIMEOFFSET

DATETIMEOFFSET is similar to
DATETIME2, but includes additional information to track the

time zone. The format is YYYY-MM-DD
hh:mm:ss[.nnnnnnn] [+|-]hh:mm with a range of 0001-

01-01 00:00:00.0000000 through
0001-01-01 00:00:00.0000000 through 9999-12-31 23:59:59.9999999.
Universal Time (UTC), with an accuracy of 100 nanoseconds. Storage depends on the
and scale selected, and
runs from 8 to 10 bytes.
zone aware means a time zone identifier is stored as a part of DATETIMEOFFSET column.
time zone identification
is represented by a [-|+] hh:mm designation. A valid time zone falls in
range of -14:00 to +14:00, and
this value is added or subtracted from UTC to obtain the local

--Sample DATETIMEOFFSET
output with a precision of 0

        --Specify a date, time, and time zone
        DECLARE @datetimeoffsetvariable DATETIMEOFFSET(0)
        SET @datetimeoffsetvariable ='2008-10-03 09:00:00 -10:00'
        --Specify a different date, time and time zone
        
        DECLARE @datetimeoffsetvariable1 DATETIMEOFFSET(0)
        SET @datetimeoffsetvariable1= '2008-10-04 18:00:00 +0:00'
        
        --Find the difference in hours between the above dates, times,and timezones
        SELECT DATEDIFF(hh,@datetimeoffsetvariable,@datetimeoffsetvariable1)                    
        
        Result: 23

Spatial
While spatial data has been stored
in many SQL Server databases for many years (using conventional
data types), SQL Server 2008 introduces two specific spatial data types that can
make it easier for developers
to integrate spatial data in their SQL Server-based applications. In addition, by
storing spatial data in
relational tables, it becomes much easier to combine spatial data with other kinds
of business data. For
example, by combining spatial data (such as longitude and latitude) with the

physical address of a
business, applications can be created to map business locations on a map.

The two new spatial data types
in SQL 2008 are:

• GEOMETRY: Used to store
planar (flat-earth) data. It is generally used to store XY

coordinates that represent points,
lines, and polygons in a two-dimensional space. For example

storing XY coordinates in the
GEOMETRY data type can be used to map the exterior of a

building.

• GEOGRAPHY: Used to store
ellipsoidal (round-earth) data. It is used to store latitude and

longitude coordinates that represent
points, lines, and polygons on the earth’s surface. For

example, GPS data that represents
the lay of the land is one example of data that can be stored

in the GEOGRAPHY data type.

GEOMETRY and GEOGRAPHY
data types are implemented as .NET CLR data types. This means
that they can support various properties and methods specific to the data. For example,
a method can be used to
calculate the distance between two GEOMETRY XY coordinates, or the distance
between two GEOGRAPHY latitude
and longitude coordinates. Another example is a method to see
if two spatial objects intersect or not. Methods defined by the Open Geospatial
Consortium standard, and
Microsoft extensions to that standard, can be used. To take full advantage of these
methods, you will have to be
an expert in spatial data.Another
feature of spatial data types is that they support special spatial indexes. Unlike
conventional indexes,
spatial indexes consist of a grid-based hierarchy in which each level of the index
subdivides

the grid sector that is defined
in the level above. But like conventional indexes, the SQL Server query
optimizer can use spatial indexes
to speed up the performance of queries that return spatial data.Spatial
data is an area unfamiliar to many DBAs. If this is a topic you want to learn more
about, you will need a
good math background, otherwise you will get lost very quickly.

HIERARCHYID

While hierarchical tree structures
are commonly used in many applications, SQL Server has, up to
not made it easy to represent and store them in relational tables. In SQL Server
2008, the

HIERARCHYID data type
has been added to help resolve this problem. It is designed to store
that represent the position of
nodes in a hierarchal tree structure.
For example, the HIERARCHYID data type makes it easier to express the following
types of relationships
without requiring multiple parent/child tables and complex joins:

Organizational structures
A set of tasks that make up a larger projects (like a GANTT chart)
File systems (folders and their sub-folders)
A classification of language terms
A bill of materials to assemble or build a product
A graphical representation of links between web pages

Unlike standard data types, the HIERARCHYID data type is a CLR user-defined
type, and it exposes many
methods that allow you to manipulate the date stored within it. For example, there
are methods to get the
current hierarchy level, get the previous level, get the next level, and many more.
In fact, the HIERARCHYID
data type is only used to store hierarchical data; it does not automatically
represent a hierarchical
structure. It is the responsibility of the application to create and assign
HIERARCHYID values in a way that
represents the desired relationship. Think of a
HIERARCHYID data type as a place to store positional nodes of a tree structure,
not as a way to create
the tree structure.

FILESTREAM

SQL Server is great for storing
relational data in a highly structured format, but it has never been
particularly good at storing
unstructured data, such as videos, graphic files, Word documents, Excel
spreadsheets, and so on. In the
past, when developers wanted to use SQL Server to manage such
unstructured data, they essentially had two choices:

Store it in VARBINARY(MAX) columns inside the database
Store the data outside of the database as part of the file system, and include pointers
inside a column that pointed
to the file’s location. This allowed an application that needed access
to the file to find it by looking
up the file’s location from inside a SQL Server table.Neither of these options was
perfect. Storing unstructured data in VARBINARY(MAX) columns
offers less than ideal performance, has a 2 GB size limit, and can dramatically
increase the size of a
database. Likewise, storing unstructured data in the file system requires the DBA
to overcome several difficulties.

For example:

Files have a unique naming system that allows hundreds, if not thousands of files
to be keep track of and
requires very careful management of the folders to store the data.
Security is a problem and often requires using NTFS permissions to keep people from
accessing the files inappropriately.
The DBA has to perform separate backups of the database and the files
Problems can occur when outside files are modified or moved and the database is
not updated to reflect
this.

To help resolve these problems,
SQL Server 2008 has introduced what is called FILESTREAM
storage, essentially a hybrid approach that combines the best features of the previous
two options.

Benefits of FILESTREAM

FILESTREAM storage is
implemented in SQL Server 2008 by storing VARBINARY(MAX) binary
large objects (BLOBs) outside of the database and in the NTFS file system. While
this sounds very similar
to the older method of storing unstructured data in the file system and pointing
to it from a column, it
is much more sophisticated. Instead of a simple link from a column to an outside
file, the SQL Server Database
Engine has been integrated with the NTFS file system for optimum
performance and ease of
administration. For example, FILESTREAM data uses the Windows OS
system cache for caching data
instead of the SQL Server buffer pool. This allows SQL Server to do what it does best: manage structured
data, and allows the Windows OS to do what is does best:
manage large files. In addition, SQL Server handles all of the links between database
columns and the files,
so we don’t have to. In
addition, FILESTREAM storage offers these additional benefits:

Transact-SQL can be used to SELECT, INSERT, UPDATE, DELETE FILESTREAM data.
By default, FILESTREAM data is backed up and restored as part of the database file.
If you want, there is an option available so you can backup a database without the
FILESTREAM data.
The size of the stored data is only limited by the available space of the file system.
Standard VARBINARY(MAX)
data is limited to 2 GB.

Limitations of FILESTREAM

As you might expect, using FILESTREAM
storage is not right for every situation. For example, it is
best used under the following conditions:

When the BLOB file sizes average 1MB or higher.
When fast read access is important to your application.
When applications are being built that use a middle layer for application logic.
When encryption is not required, as it is not supported for FILESTREAM data.
If your application doesn’t meet
the above conditions, then using the standard VARBINARY(MAX) data type might be
your best option. If you
are used to storing binary data inside your database, or outside your database (but
using pointers inside
the database that point to the binary files), then you will find using FILESTREAM
storage to be substantially different.
You will want to thoroughly test your options before
implementing one option or the other, in any new applications you build.

How to Implement FILESTREAM
Storage Enabling SQL Server
to use FILESTREAM data is a multiple-step process, which includes:

Enabling the SQL Server instance to use FILESTREAM data
Enabling a SQL Server database to use FILESTREAM data
Creating FILESTREAM-enabled columns in a table, by specifying the "VARBINARY(MAX)
FILESTREAM" data type.

Base Page For Detecting Session Timeout in ASP.Net/C#

2010-02-04T03:37:00.000-08:00

In this tutorial we will be going over how to create a base page class to handle your sessions. The number one question I get asked time and time again is how to manage sessions, and how to detect if a session has expired. Back in the days before .Net things were a little more complicated when it came to solving this riddle, but with the advent of the .Net Framework 2.0 a new class was introduced, the HttpSessionState Class, which is a member of the System.Web.SessionState Namespace. The new HttpSessionState Class gives us access to session state items and other lifetime management methods.

One of the items in the HttpSessionState class we will be looking at is the IsNewSession Property. This property lets us know whether the current session was created wtih the current request, or if it was an existing session. This is invaluable as we can use it to determine if the users session had expired or timed out. The IsNewSession Property is more robust and advanced then simply checking if the session is null because it takes into account a session timeout as well.

In this tutorial we will create a base page class that we can inherit all our pages from, and in this class we will check the status of the users session in the Page.OnInit Method. The OnOnit Method fires before the Page Load Event, giving us the ability to check the session before the page is actually rendered. So lets get to some code.

The first thing we will need to do, as with any class you create, is to make sure we have a reference to the appropriate Namespaces. For our class we need but 2 Namespaces, the System.Web.UI Namespace and the System Namespace, so lets add them to our class.

NOTE: All Namespace references need to come before the declaration of your class.

using System;
using System.Web.UI;

Now we are going to declare our class, the class in this example is named SessionCheck, and it looks like

public class SessionCheck: System.Web.UI.Page
{

}

You will notice that our base class, which we will be inheriting from, inherits from the System.Web.UI.Page class. Doing this gives us access to all the methods, properties and events of the Page  class. In our base class we will have a single property, this will be the property that will hold the URL we want the user to redirect to if there is a problem with their session. We make this property static  so we can access it without having to create an instance of the class. We dont want to have to do this because we are inheriting from it. 
This is our property


/// 
/// property vcariable for the URL Property
/// 
private static string _url;

/// 
/// property to hold the redirect url we will
/// use if the users session is expired or has
/// timed out.
/// 
public static string URL
{
    get { return _url; }
    set { _url = value; }
}
Now that we have our property out of the way, we will look at the only  of our base class, the OnInit which we will override in order to add our ow functionality. In 
this method we will also initialize our base class, you do that with line


base.OnInit(e);
In our OnInit Method we will first check to
 see if the current session is null. If the session is null we then will
 check the IsNewSession Property to see if 
this session was created in this request. If we determine the session is
 a new session, we will then cal upon the Headers 
Property of the HttpRequest
 Class, which is located in the System.Web
 Namespace.



In our OnInit Method we will first check to
 see if the current session is null. If the session is null we then will
 check the IsNewSession Property to see if 
this session was created in this request. If we determine the session is
 a new session, we will then cal upon the Headers 
Property of the HttpRequest
 Class, which is located in the System.Web
 Namespace.



The Header we are retrieving is the Cookie Header. Once we have this, we will first 
check to see if it's null, if it's not null we will look for the value ASP.Net_SessionId. Now if we make it this far, and
 that cookie exists, we know the session has timed out, so we will then 
redirect the user to our redirect page, which is set with the URL Property. So lets take a look at our new OnInit Method:


override protected void OnInit(EventArgs e)
{
    //initialize our base class (System.Web,UI.Page)
    base.OnInit(e);
    //check to see if the Session is null (doesnt exist)
    if (Context.Session != null)
    {
        //check the IsNewSession value, this will tell us if the session has been reset.
        //IsNewSession will also let us know if the users session has timed out
        if (Session.IsNewSession)
        {
           //now we know it's a new session, so we check to see if a cookie is present
            string cookie = Request.Headers["Cookie"];
            //now we determine if there is a cookie does it contains what we're looking for
            if ((null != cookie) && (cookie.IndexOf("ASP.NET_SessionId") >= 0))
            {
                //since it's a new session but a ASP.Net cookie exist we know
                //the session has expired so we need to redirect them
                Response.Redirect("Default.aspx?timeout=yes&success=no");
            }
        }
    }
}

That's it, we have completed our base class which all our web forms will inherit from, allowing us to keep an eye on the users session. Now that we have the class completed we need to use it. Before it can be affected we need to do 1 of 2 things

Add EnableSessionState = true to the @Page directive on all pages that will inherit from our base class or
Add the following line to the section of our web.config file:

Add EnableSessionState = true to the @Page directive on all pages that will inherit from our base class or
Add the following line to the section of our web.config file

Number 2 on that list will enable session state on all pages in the web. If you dont access session items in each of your pages, this might be overkill. Next we will need to inherit from our base class. Doing this will give our web form the following declaration

public partial class _Default : SessionCheck
{

}

Then in the Page_Load Event we will set the redirect URL for our base class

protected void Page_Load(object sender, EventArgs e)
{
    SessionCheck.URL = "Default.aspx";
}

Now here is the entire base page in its entirety

//   A Base Page class for detecting session time outs
//
//   This program is free software: you can redistribute it and/or modify
//   it under the terms of the GNU General Public License as published by
//   the Free Software Foundation, either version 3 of the License, or
//   (at your option) any later version.
//
//   This program is distributed in the hope that it will be useful,
//   but WITHOUT ANY WARRANTY; without even the implied warranty of
//   MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
//   GNU General Public License for more details.
//
//   You should have received a copy of the GNU General Public License
//   along with this program.  If not, see .
//*****************************************************************************************

using System;
using System.Web.UI;

/// 
/// This is a custom "base page" to inherit from which will be used
/// to check the session status. If the session has expired or is a timeout
/// we will redirect the user to the page we specify. In the page you use
/// to inherit this from you need to set EnableSessionState = True
/// 
public class SessionCheck : System.Web.UI.Page
{
    /// 
    /// property vcariable for the URL Property
    /// 
    private static string _redirectUrl;

    /// 
    /// property to hold the redirect url we will
    /// use if the users session is expired or has
    /// timed out.
    /// 
    public static string RedirectUrl
    {
        get { return _redirectUrl; }
        set { _redirectUrl = value; }
    }

        public SessionCheck()
        {
        _redirectUrl = string.Empty;
        }

    override protected void OnInit(EventArgs e)
    {
        //initialize our base class (System.Web,UI.Page)
        base.OnInit(e);
        //check to see if the Session is null (doesnt exist)
        if (Context.Session != null)
        {
            //check the IsNewSession value, this will tell us if the session has been reset.
            //IsNewSession will also let us know if the users session has timed out
            if (Session.IsNewSession)
            {
               //now we know it's a new session, so we check to see if a cookie is present
                string cookie = Request.Headers["Cookie"];
                //now we determine if there is a cookie does it contains what we're looking for
                if ((null != cookie) && (cookie.IndexOf("ASP.NET_SessionId") >= 0))
                {
                    //since it's a new session but a ASP.Net cookie exist we know
                    //the session has expired so we need to redirect them
                    Response.Redirect("Default.aspx?timeout=yes&success=no");
                }
            }
        }
    }
}

And there you have it, a custom base class that you can use to detect session timeouts. I hope you found this tutorial helpful and useful, and thank you for reading

Happy Coding!

Reading and Writing XML in C#

2010-01-30T01:46:00.000-08:00

In this article, you will see how to read and write XML documents in Microsoft .NET using C# language. First, I will discuss XML .NET Framework Library namespace and classes. Then, you will see how to read and write XML documents. In the end of this article, I will show you how to take advantage of ADO.NET and XML .NET model to read and write XML documents from relational databases and vice versa.

Introduction to Microsoft .NET XML Namespaces and Classes Before start working with XML document
in .NET Framework, It is important to know about .NET namespace and classes provided by .NET Runtime Library. .NET provides five namespace - System.Xml, System.Xml.Schema, System.Xml.Serialization, System.Xml.XPath, and System.Xml.Xsl to support XML classes. The System.Xml namespace contains major XML classes. This namespace contains many classes to read and write XML documents. In this article, we are going to concentrate on reader and write class. These reader and writer classes are used to read and write XMl documents. These classes are - XmlReader, XmlTextReader, XmlValidatingReader, XmlNodeReader, XmlWriter, and XmlTextWriter. As you can see there are four reader and two writer classes. The XmlReader class is an abstract bases classes and contains methods and properties to read a document. The Read method reads a node in the stream. Besides reading functionality, this class also contains methods to navigate through a document nodes. Some of these methods are MoveToAttribute, MoveToFirstAttribute, MoveToContent, MoveToFirstContent, MoveToElement and MoveToNextAttribute. ReadString, ReadInnerXml, ReadOuterXml, and ReadStartElement are more read methods. This class also has a method Skip to skip current node and move to next one. We'll see these methods in our sample example. The XmlTextReader, XmlNodeReader and XmlValidatingReader classes are derived from XmlReader class. As their name explains, they are used to read text, node, and schemas. The XmlWrite class contains functionality to write data to XML documents. This class provides many write method to write XML document items. This class is base class for XmlTextWriter class, which we'll be using in our sample example. The XmlNode class plays an important role. Although, this class represents a single node of XML but that could be the root node of an XML document and could represent the entire file. This class is an abstract base class for many useful classes for inserting, removing, and replacing nodes, navigating through the document. It also contains properties to get a parent or child, name, last child, node type and more. Three major classes derived from XmlNode are XmlDocument, XmlDataDocument and XmlDocumentFragment. XmlDocument class represents an XML document and provides methods and properties to load and save a document. It also provides functionality to add XML items such as attributes, comments, spaces, elements, and new nodes.

The Load and LoadXml methods can be used to load XML documents and Save method to save a document respectively. XmlDocumentFragment class represents a document fragment, which can be used to add to a document. The XmlDataDocument class provides methods and properties to work with ADO.NET data set objects. In spite of above discussed classes, System.Xml namespace contains more classes. Few of them are XmlConvert, XmlLinkedNode, and XmlNodeList. Next namespace in Xml series is System.Xml.Schema.

It classes to work with XML schemas such XmlSchema, XmlSchemaAll, XmlSchemaXPath, XmlSchemaType. The System.Xml.Serialization namespace contains classes that are used to serialize objects into XML format documents or streams. The System.Xml.XPath Namespce contains XPath related classes to use XPath specifications. This namespace has following classes -XPathDocument, XPathExression, XPathNavigator, and XPathNodeIterator. With the help of XpathDocument, XpathNavigator provides a fast
navigation though XML documents. This class contains many Move methods to move through a document. The System.Xml.Xsl namespace contains classes to work with XSL/T transformations. Reading XML Documents In my sample application, I'm using books.xml to read and display its data through XmlTextReader. This file comes with VS.NET samples. You can search this on your machine and change the path of the file in the following line: XmlTextReader textReader = new XmlTextReader("C:\\books.xml"); Or you can use any XML file. The XmlTextReader, XmlNodeReader and XmlValidatingReader classes
are derived from XmlReader class. Besides XmlReader methods and properties, these classes also contain members to read text, node, and schemas respectively. I am using XmlTextReader class to read an XML file. You read a file by passing file name as a parameter in constructor. XmlTextReader textReader = new XmlTextReader("C:\\books.xml"); After creating an instance of XmlTextReader, you call Read method to start reading the document. After read method is called, you can read all information and data stored in a document. XmlReader class has properties such as Name, BaseURI, Depth, LineNumber an so on. List 1 reads a document and displays a node information using these properties. About Sample Example 1 In this sample example, I read an XML file using XmlTextReader and call Read method to read its node one by one until end of file and display the contents to the console output. Sample Example 1.

using System;

using System.Xml;

namespace ReadXml1

{

    class Class1

    {

        static void Main(string[] args)

        {

            // Create an isntance of XmlTextReader and call Read method to read the file

            XmlTextReader textReader = new XmlTextReader("C:\\books.xml");

            textReader.Read();

            // If the node has value

            while (textReader.Read())

            {

                // Move to fist element

                textReader.MoveToElement();

                Console.WriteLine("XmlTextReader Properties Test");

                Console.WriteLine("===================");

                // Read this element's properties and display them on console

                Console.WriteLine("Name:" + textReader.Name);

                Console.WriteLine("Base URI:" + textReader.BaseURI);

                Console.WriteLine("Local Name:" + textReader.LocalName);

                Console.WriteLine("Attribute Count:" + textReader.AttributeCount.ToString());

                Console.WriteLine("Depth:" + textReader.Depth.ToString());

                Console.WriteLine("Line Number:" + textReader.LineNumber.ToString());

                Console.WriteLine("Node Type:" + textReader.NodeType.ToString());

                Console.WriteLine("Attribute Count:" + textReader.Value.ToString());

            }

        }

    }

}

The NodeType property of XmlTextReader is important when you want to know the content type of a document. The XmlNodeType enumeration has a member for each type of XML item such as Attribute, CDATA, Element, Comment, Document, DocumentType, Entity, ProcessInstruction, WhiteSpace and so on. List 2 code sample reads an XML document, finds a node type and writes information at the end with how many node types a document has. About Sample Example 2 In this sample example, I read an XML file using XmlTextReader and call Read method to read its node one by one until end of the file. After reading a node, I check its NodeType property to find the node and write node contents to the console and keep track of number of particular type of nodes. In the end, I display total number of different types of nodes in the document. Sample Example 2.

using System;

using System.Xml;

namespace ReadingXML2

{

    class Class1

    {

        static void Main(string[] args)

        {

            int ws = 0;

            int pi = 0;

            int dc = 0;

            int cc = 0;

            int ac = 0;

            int et = 0;

            int el = 0;

            int xd = 0;

            // Read a document

            XmlTextReader textReader = new XmlTextReader("C:\\books.xml");

            // Read until end of file

            while (textReader.Read())

            {

                XmlNodeType nType = textReader.NodeType;

                // If node type us a declaration

                if (nType == XmlNodeType.XmlDeclaration)

                {

                    Console.WriteLine("Declaration:" + textReader.Name.ToString());

                    xd = xd + 1;

                }

                // if node type is a comment

                if (nType == XmlNodeType.Comment)

                {

                    Console.WriteLine("Comment:" + textReader.Name.ToString());

                    cc = cc + 1;

                }

                // if node type us an attribute

                if (nType == XmlNodeType.Attribute)

                {

                    Console.WriteLine("Attribute:" + textReader.Name.ToString());

                    ac = ac + 1;

                }

                // if node type is an element

                if (nType == XmlNodeType.Element)

                {

                    Console.WriteLine("Element:" + textReader.Name.ToString());

                    el = el + 1;

                }

                // if node type is an entity\

                if (nType == XmlNodeType.Entity)

                {

                    Console.WriteLine("Entity:" + textReader.Name.ToString());

                    et = et + 1;

                }

                // if node type is a Process Instruction

                if (nType == XmlNodeType.Entity)

                {

                    Console.WriteLine("Entity:" + textReader.Name.ToString());

                    pi = pi + 1;

                }

                // if node type a document

                if (nType == XmlNodeType.DocumentType)

                {

                    Console.WriteLine("Document:" + textReader.Name.ToString());

                    dc = dc + 1;

                }

                // if node type is white space

                if (nType == XmlNodeType.Whitespace)

                {

                    Console.WriteLine("WhiteSpace:" + textReader.Name.ToString());

                    ws = ws + 1;

                }

            }

            // Write the summary

            Console.WriteLine("Total Comments:" + cc.ToString());

            Console.WriteLine("Total Attributes:" + ac.ToString());

            Console.WriteLine("Total Elements:" + el.ToString());

            Console.WriteLine("Total Entity:" + et.ToString());

            Console.WriteLine("Total Process Instructions:" + pi.ToString());

            Console.WriteLine("Total Declaration:" + xd.ToString());

            Console.WriteLine("Total DocumentType:" + dc.ToString());

            Console.WriteLine("Total WhiteSpaces:" + ws.ToString());

        }

    }

}

Writing XML Documents XmlWriter class contains the functionality to write to XML documents. It is an abstract base class used through XmlTextWriter and XmlNodeWriter classes. It contains methods and properties to write to XML documents. This class has several Writexxx method to write every type of item of an XML document. For example, WriteNode, WriteString, WriteAttributes, WriteStartElement, and WriteEndElement are some of them. Some of these methods are used in a start and end pair. For example,
to write an element, you need to call WriteStartElement then write a string followed by WriteEndElement.

Besides many methods, this class has three properties. WriteState, XmlLang, and XmlSpace. The WriteState gets and sets the state of the XmlWriter class. Although, it's not possible to describe all the Writexxx methods here, let's see some of them. First thing we need to do is create an instance of XmlTextWriter using its constructor. XmlTextWriter has three overloaded constructors, which can take a string, stream, or a TextWriter as an argument. We'll pass a string (file name) as an argument, which we're going to create in C:\ root. In my sample example, I create a file myXmlFile.xml in C:\\ root directory. // Create a new file in C:\\ dir XmlTextWriter textWriter = new XmlTextWriter("C:\\myXmFile.xml", null) ; After creating an instance, first
thing you call us WriterStartDocument. When you're done writing, you call WriteEndDocument and TextWriter's Close method. textWriter.WriteStartDocument(); textWriter.WriteEndDocument(); textWriter.Close(); The WriteStartDocument and WriteEndDocument methods open and close a document for writing. You must have to open a document before start writing to it. WriteComment method writes comment to a document. It takes only one string type of argument. WriteString method writes a string to a document. With the help of WriteString, WriteStartElement and WriteEndElement methods pair can be used to write an element to a document. The WriteStartAttribute and WriteEndAttribute pair writes an attribute. WriteNode is more write method, which writes an XmlReader to a document as a node of the document. For example, you can use WriteProcessingInstruction and WriteDocType methods to write a ProcessingInstruction and DocType items of a document. //Write the ProcessingInstruction node string PI= "type='text/xsl' href='book.xsl'" textWriter.WriteProcessingInstruction("xml-stylesheet", PI); //'Write the DocumentType node textWriter.WriteDocType("book", Nothing, Nothing, ""); The below sample example summarizes all these methods and creates a new xml document with some items in it such as elements, attributes, strings, comments and so on. See Listing 5-14. In this sample example, we create a new xml file c:\xmlWriterText.xml. In this sample example, We create a new xml file c:\xmlWriterTest.xml using XmlTextWriter: After that, we add comments and elements to the document using Writexxx methods. After that we read our books.xml xml file using XmlTextReader and add its elements to xmlWriterTest.xml using XmlTextWriter. About Sample Example 3 In this sample example, I create a new file myxmlFile.xml using XmlTextWriter and use its various write methods to write XML items. Sample Example 3.

using System;

using System.Xml;

namespace ReadingXML2

{

    class Class1

    {

        static void Main(string[] args)

        {

            // Create a new file in C:\\ dir

            XmlTextWriter textWriter = new XmlTextWriter("C:\\myXmFile.xml", null);

            // Opens the document

            textWriter.WriteStartDocument();

            // Write comments

            textWriter.WriteComment("First Comment XmlTextWriter Sample Example");

            textWriter.WriteComment("myXmlFile.xml in root dir");

            // Write first element

            textWriter.WriteStartElement("Student");

            textWriter.WriteStartElement("r", "RECORD", "urn:record");

            // Write next element

            textWriter.WriteStartElement("Name", "");

            textWriter.WriteString("Student");

            textWriter.WriteEndElement();

            // Write one more element

            textWriter.WriteStartElement("Address", ""); textWriter.WriteString("Colony");

            textWriter.WriteEndElement();

            // WriteChars

            char[] ch = new char[3];

            ch[0] = 'a';

            ch[1] = 'r';

            ch[2] = 'c';

            textWriter.WriteStartElement("Char");

            textWriter.WriteChars(ch, 0, ch.Length);

            textWriter.WriteEndElement();

            // Ends the document.

            textWriter.WriteEndDocument();

            // close writer

            textWriter.Close();

        }

    }

}

Using XmlDocument The XmlDocument class represents an XML document. This class provides similar methods and properties we've discussed earlier in this article. Load and LoadXml are two useful methods of this class. A Load method loads XML data from a string, stream, TextReader or XmlReader. LoadXml method loads XML document from a specified string. Another useful method of this class is Save. Using Save method you can write XML data to a string, stream, TextWriter or XmlWriter. About Sample Example 4 This tiny sample example pretty easy to understand. We call LoadXml method of XmlDocument to load an XML fragment and call Save to save the fragment as an XML file. Sample Example 4.

//Create the XmlDocument. XmlDocument doc = new XmlDocument();
doc.LoadXml(("Tommyex")); //Save the document to a file. doc.Save("C:\\std.xml");

You can also use Save method to display contents on console if you pass Console.Out as a arameter. For example: doc.Save(Console.Out); About Sample Example 5 Here is one example of how to load an XML document using XmlTextReader. In this sample example, we read books.xml file using XmlTextReader and call its Read method. After that we call XmlDocumetn's Load method to load XmlTextReader contents to XmlDocument and call Save method to save the document. Passing Console.Out as a Save method
argument displays data on the console Sample Example 5. XmlDocument doc = new XmlDocument();
//Load the the document with the last book node.
XmlTextReader reader = new XmlTextReader("c:\\books.xml");
reader.Read(); // load reader doc.Load(reader); // Display contents on the console
doc.Save(Console.Out); Writing Data from a database to an XML Document Using XML and ADO.NET mode, reading a database and writing to an XML document and vice versa is not a big deal. In this section of this article, you will see how to read a database table's data and write the contents to an XML document. The DataSet class provides method to read a relational database table and write this table to an XML file.
You use WriteXml method to write a dataset data to an XML file. In this sample example, I have used commonly used Northwind database comes with Office 2000 and later versions. You can use any database you want. Only thing you need to do is just chapter the connection string and SELECT SQ L query. About Sample Example 6 In this sample, I reate a data adapter object and selects all records of Customers table. After that I can fill method to fill a dataset from the data adapter. In this sample example,I have used OldDb data provides. You need to add reference to the Syste.Data.OldDb namespace to use OldDb data adapters in your program. As you can see from Sample Example 6, first I create a connection with northwind database using OldDbConnection. After that I create a data adapter object by passing a SELECT SQL query and connection. Once you have a data adapter, you can fill a dataset object using Fill method of the data adapter. Then you can WriteXml method of DataSet, which creates an XML document and write its contents to the XML document. In our sample, we read Customers table records and write DataSet contents to OutputXml.Xml file in C:\ dir. Sample Example 6.
using System; using System.Xml; using System.Data; using System.Data.OleDb; namespace ReadingXML2 { class Class1 { static void Main(string[] args) { // create a connection OleDbConnection con = new OleDbConnection(); con.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\\Northwind.mdb"; // create a data adapter OleDbDataAdapter da = new OleDbDataAdapter("Select * from Customers", con); // create a new dataset DataSet ds = new DataSet(); // fill dataset da.Fill(ds, "Customers"); // write dataset contents to an xml file by calling WriteXml method ds.WriteXml("C:\\OutputXML.xml"); } } }

.NET Framework Library provides a good support to work with XML documents. The XmlReader,
XmlWriter and their derived classes contains methods and properties to read and write XML documents. With the help of the XmlDocument and XmlDataDocument classes, you can read entire document. The Load and Save method of XmlDocument loads a reader or a file and saves document respectively. ADO.NET provides functionality to read a database and write its contents to the XML document using data providers and a DataSet object.

Main Differences between ASP.NET 3.5 and ASP.NET 4.0

2010-01-29T21:20:00.000-08:00

As we all know, ASP.NET 3.5 has introduced with the following main new features

1) AJAX integration
2) LINQ
3) Automatic Properties
4) Lambda expressions

I hope it would be useful for everyone to know about the differences about asp.net 3.5 and its next version asp.net 4.0

Because of space consumption I’ll list only some of them here.

1) Client Data access:

ASP.NET 3.5: There is no direct method to access data from client side. We can go for any of these methods

1) Pagemethods of script manager
2) ICallbackEventHandler interface
3) XMLHttphanlder component

ASP.NET 4.0: In this framework there is an inbuilt feature for this. Following are the methods to implement them.

1) Client data controls
2) Client templates
3) Client data context

i.e we can access the data through client data view & data context objects from client side.

2) Setting Meta keyword and Meta description:

Meta keywords and description are really useful for getting listed in search engine.

ASP.NET 3.5: It has a feature to add meta as following tag

&ltmeta name="keywords" content="These, are, my, keywords" /> 
  &ltmeta name="description" content="This is the description of my page" />

ASP.NET 4.0: Here we can add the keywords and description in Page directives itself as shown below.

< %@ Page Language="C#"  CodeFile="Default.aspx.cs" 
  Inherits="_Default" 
  Keywords="Keyword1,Key2,Key3,etc" 
  Description="description" %>

3) Enableviewstage property for each control

ASP.NET 3.5: this property has two values “True” or “false”

ASP.NET 4.0: ViewStateMode property takes an enumeration that has three values: Enabled, Disabled, and Inherit.
Here inherit is the default value for child controls of a control.

4) Setting Client IDs

Some times ClientID property creates head ach for the programmers.

ASP.NET 3.5: We have to use ClientID property to find out the id which is dynamically generated

ASP.NET 4.0: The new ClientIDMode property is introduced to minimize the issues of earlier versions of ASP.NET.

It has following values.

AutoID – Same as ASP.NET 3.5
Static – There won’t be any separate clientid generated at run time
Predictable-These are used particularly in datacontrols. Format is like clientIDrowsuffix with the clientid vlaue
Inherit- This value specifies that a control's ID generation is the same as its parent.

--
happy programming.

.Net Memory Management & Garbage Collection

2010-01-24T22:26:00.000-08:00

The Microsoft .NET common language runtime requires that all resources be allocated from the managed heap. Objects are automatically freed when they are no longer needed by the application.
When a process is initialized, the runtime reserves a contiguous region of address space that initially has no storage allocated for it. This address space region is the managed heap. The heap also maintains a pointer. This pointer indicates where the next object is to be allocated within the heap. Initially, the pointer is set to the base address of the reserved address space region.
Garbage collection in the Microsoft .NET common language runtime environment completely absolves the developer from tracking memory usage and knowing when to free memory. However, you’ll want to understand how it works. So let’s do it.
Topics Covered
We will cover the following topics in the article

Why memory matters
.Net Memory and garbage collection
Generational garbage collection
Temporary Objects
Large object heap & Fragmentation
Finalization
Memory problems

1) Why Memory Matters

Insufficient use of memory can impact

Performance
Stability
Scalability
Other applications

Hidden problem in code can cause

Memory leaks
Excessive memory usage
Unnecessary performance overhead

2) .Net Memory and garbage collection

.Net manages memory automatically

Creates objects into memory blocks(heaps)
Destroy objects no longer in use

Allocates objects onto one of two heaps

Small object heap(SOH) – objects < 85k
Large object heap(LOH) – objects >= 85k

You allocate onto the heap whenever you use the “new” keyword in code
Small object heap (SOH)

Allocation of objects < 85k – Contiguous heap – Objects allocated consecutively
Next object pointer is maintained – Objects references held on Stack, Globals, Statics and CPU register
Objects not in use are garbage collected

Figure-1

Next, How GC works in SOH?
GC Collect the objects based on the following rules:

Reclaims memory from “rootless” objects
Runs whenever memory usage reaches certain thresholds
Identifies all objects still “in use”
- Has root reference
- Has an ancestor with a root reference
Compacts the heap
- Copies “rooted” objects over rootless ones
- Resets the next object pointer
Freezes all execution threads during GC
- Every GC runs it hit the performance of your app

Figure-2

3) Generational garbage collection

Optimizing Garbage collection

Newest object usually dies quickly
Oldest object tend to stay alive
GC groups objects into Generations
- Short lived – Gen 0
- Medium – Gen 1
- Long Lived – Gen 2
When an object survives a GC it is promoted to the next generation
GC compacts Gen 0 objects most often
The more the GC runs the bigger the impact on performance

Figure-3

Figure-4

Here object C is no longer referenced by any one so when GC runs it get destroyed & Object D will be moved to the Gen 1 (see figure-5). Now Gen 0 has no object, so when next time when GC runs it will collect object from Gen 1.

Figure-5

Figure-6

Here when GC runs it will move the object D & B to Gen 2 because it has been referenced by Global objects & Static objects.
Figure-7

Here when GC runs for Gen 2 it will find out that object A is no longer referenced by anyone so it will destroy it & frees his memory. Now Gen 2 has only object D & B.
Garbage collector runs when

Gen 0 objects reach ~256k
Gen 1 objects reach ~2Meg
Gen 2 objects reach ~10Meg
System memory is low

Most objects should die in Gen 0.
Impact on performance is very high when Gen 2 run because

Entire small object heap is compacted
Large object heap is collected

4) Temporary objects

Once allocated objects can’t resize on a contiguous heap
Objects such as strings are Immutable
- Can’t be changed, new versions created instead
- Heap fills with temporary objects

Let us take example to understand this scenario.
Figure – 8

After the GC runs all the temporary objects are destroyed.
Figure–9

5) Large object heap & Fragmentation

Large object heap (LOH)

Allocation of object >=85k
Non contiguous heap
- Objects allocated using free space table
Garbage collected when LOH Threshold Is reached
Uses free space table to find where to allocate
Memory can become fragmented

Figure-10

After object B is destroyed free space table will be filled with a memory address which has been available now.
Figure-11

Now when you create new object, GC will check out which memory area is free or available for our new object in LOH. It will check out the Free space table & allocate object where it fit.
Figure-12

6) Object Finalization

Disk, Network, UI resources need safe cleanup after use by .NET classes
Object finalization guarantees cleanup code will be called before collection
Finalizable object survive for at least 1 extra GC & often make it to Gen 2
Finalizable classes have a
- Finalize method(c# or vb.net)
- C++ style destructor (c#)

Here are the guidelines that help you to decide when to use Finalize method:

Only implement Finalize on objects that require finalization. There are performance costs associated with Finalize methods.
If you require a Finalize method, you should consider implementing IDisposable to allow users of your class to avoid the cost of invoking the Finalize method.
Do not make the Finalize method more visible. It should be protected, not public.
An object’s Finalize method should free any external resources that the object owns. Moreover, a Finalize method should release only resources that are held onto by the object. The Finalize method should not reference any other objects.
Do not directly call a Finalize method on an object other than the object’s base class. This is not a valid operation in the C# programming language.
Call the base.Finalize method from an object’s Finalize method.

Note: The base class’s Finalize method is called automatically with the C# and the Managed Extensions for C++ destructor syntax.
Let see one example to understand how the finalization works.
Each figure itself explain what is going on & you can clearly see how the finalization works when GC run.

Figure-13

Figure-14

Figure-15

Figure-16

Figure-17

For more information on Finalization refer the following links:
http://www.object-arts.com/docs/index.html?howdofinalizationandmourningactuallywork_.htm
http://blogs.msdn.com/cbrumme/archive/2004/02/20/77460.aspx
How to minimize overheads
Object size, number of objects, and object lifetime are all factors that impact your application’s allocation profile. While allocations are quick, the efficiency of garbage collection depends (among other things) on the generation being collected. Collecting small objects from Gen 0 is the most efficient form of garbage collection because Gen 0 is the smallest and typically fits in the CPU cache. In contrast, frequent collection of objects from Gen 2 is expensive. To identify when allocations occur, and which generations they occur in, observe your application’s allocation patterns by using an allocation profiler such as the CLR Profiler.
You can minimize overheads by:

Avoid Calling GC.Collect
Consider Using Weak References with Cached Data
Prevent the Promotion of Short-Lived Objects
Set Unneeded Member Variables to Null Before Making Long-Running Calls
Minimize Hidden Allocations
Avoid or Minimize Complex Object Graphs
Avoid Preallocating and Chunking Memory

7) Common Memory Problems

Excessive RAM footprint
- App allocates objects too early or for too long using more memory than needed
- Can affect other app on system
Excessive temporary object allocation
- Garbage collection runs more frequently
- Executing threads freeze during garbage collection
Memory leaks
- Overlooked root references keep objects alive (collections, arrays, session state, delegates/events etc)
- Incorrect or absent finalization can cause resource leaks

References & other useful articles related to this topic:

Link 1
Link 2
Link 3

Hope this help

Backing Up MySQL Database

2010-01-07T04:47:00.000-08:00

Backing Up MySQL Database

MySQL database backup can be accomplished in two ways:

a) Copying the raw mysql database files &
b) Exporting tables to text files

Copying the MySQL database files

MySQL uses the same table format on different platforms, so it's possible to copy MySQL table and index files from one platform and use them on another without any difficulties (assuming, of course, that you're using the same version of MySQL on both platforms).

Exporting tables to text files

The MySQLDump is handy utility that can be used to quickly backup the MySQL Database to the text files. To use the MySQLDump utility it is required to logon to the System running the MySQL Databse. You can use Telnet to remotely logon to the system if you don't have the physical access to the machine.

The syntax for the command is as follows.

mysqldump -u [Username] -p [password] [databasename] > [backupfile.sql]
[username] - this is your database username
[password]- this is the password for your database
[databasename] - the name of your database
[backupfile.sql] - the filename for your database backup

Let's discuss the example of backing up MySQL Database named "accounts" into text file accounts.sql. Here are the scenarios of taking the backup assuming that both user name and password of the database is "admin".

a) Taking the full backup of all the tables including the data.

Use the following command to accomplish this:
mysqldump -u admin -p admin accounts > accounts.sql

b) Taking the backup of table structures only.

Use the following command to accomplish this:
mysqldump -u admin -p admin --no-data accounts > accounts.sql

c) Taking the backup data only.

Use the following command to accomplish this:
mysqldump -u admin -p admin --no-create-info accounts > accounts.sql

Restoring MySQL Database

Restoring the MySQL is very easy job. You can use the following to command to restore the accounts database from accounts.sql backup file.

mysql - u admin -p admin accounts < accounts.sql

In this tutorial you learned how to take the backup of your MySQL Database and restore the same in the event of some database crash or on some other machine.

-----------

Enjoy Programming

Uploading Multiple Files in ASP.NET 2.0

2010-01-03T22:26:00.000-08:00

In ASP.NET We can upload more than one file using the following classes:

HttpFileCollection
HttpPostedFile
Request.Files
System.IO.Path

HttpFileCollection:

HttpFileCollection will provide access to the files uploaded by a client.

HttpPostedFile:

HttpPostedFile will provide access to individual files uploaded by a client. Through this class we can access the content and properties of each individual file, and read and save the files.

Request.Files :

The Request.Files will return collection of all files uploaded by user and store them inside the HttpFileCollection.

Follow these steps mentioned below to do so:

Step 1:

Drag and drop multiple (in our case four) FileUpload controls on to the designer.

Step 2:

Drop a Button control and rename it to “Upload”

<asp:Button ID=”btnUpload” runat=”server” Text=”Upload” />

Step 3:

Double click the Upload Button to add an event hander to the code behind.

protected void btnUpload_Click(object sender, EventArgs e)
{

}

Step 4: Import the System.IO namespace.

using System.IO;

Step 5:

Use the ‘HttpFileCollection’ class to retrieve all the files that are uploaded. Files are encoded and transmitted in the content body using multipart MIME format with an HTTP Content-Type header. ASP.NET extracts
this information from the content body into individual members of an HttpFileCollection.

The code would look as follows:

protected void btnUpload_Click(object sender, EventArgs e)
{
try
{
// Get the HttpFileCollection
HttpFileCollection hfc = Request.Files;
for (int i = 0; i < hfc.Count; i++)
{
HttpPostedFile hpf = hfc[i];
if (hpf.ContentLength > 0)
{
hpf.SaveAs(Server.MapPath(”MyFiles”) + “\\” +
Path.GetFileName(hpf.FileName));

}
}
}
catch (Exception ex)
{
// Handle your exception here
}

}

Some important points to consider while uploading

1.    To save a file to the server, the account associated with ASP.NET must have sufficient permissions on the folder, where the files are being uploaded. This would usually be the ‘ASPNET’ account for Windows XP or a similar OS. In Windows Server 2003, the account used is ‘NETWORKSERVICE’. So you would be required to explicitly grant write permissions to these accounts on the folder.

2.    While uploading the files to a remote server, the default ASPNET user account used by ASP.NET does not have network permissions by default. The solution is to either give the account such permissions or
use impersonation to have it run under a different account that has the permissions.

3.    By default, you can upload no more than 4096 KB (4 MB) of data. However there is a workaround for this limitation. You can change the maximum file size by changing the maxRequestLength attribute of the
httpRuntime element in the web.config file. You can also increase the ‘executionTimeout’. By default it is 110 seconds. I would encourage you to experiment with the other attributes of the httpRuntime element.

<configuration>
<system.web>
<httpRuntime
executionTimeout=”200″
maxRequestLength=”8192″
requestLengthDiskThreshold=”256″
useFullyQualifiedRedirectUrl=”false”
minFreeThreads=”8″
minLocalRequestFreeThreads=”4″
appRequestQueueLimit=”5000″
enableKernelOutputCache=”true”
enableVersionHeader=”true”
requireRootedSaveAsPath=”true”
enable=”true”
shutdownTimeout=”90″
delayNotificationTimeout=”5″
waitChangeNotification=”0″
maxWaitChangeNotification=”0″
enableHeaderChecking=”true”
sendCacheControlHeader=”true”
apartmentThreading=”false”/>
</system.web>
</configuration>

References

There are a number of good resources I referred to, for this article. A few of them are:

http://www.wrox.com/WileyCDA/Section/id-292160.html
http://msdn2.microsoft.com/en-US/library/aa479405.aspx
http://msdn2.microsoft.com/en-us/library/system.web.httpfilecollection.aspx

Some Sql Server Interview Questions

2009-12-30T00:37:00.000-08:00

* Difference between Set and Select
Ans:
1) Set is a ANSI standard for variable assignment.
Select is a Non-ANSI standard when assigning variables.We can assign only one variable at a time

2) We can assign multiple variable at a time.When assigning from a query that returns more than one value, SET will fail with an error.
When assigning from a query that returns more than one value, SELECT will assign the last value returned by the query and hide the fact that the query returned

* What is Network Packet Size in SQL
Ans:
NPS(Network Packet Size) is the size of the TDS (tabular data stream) packets used to communicate between your applications and your relational database engine and default packet size is 4 kilobytes and its manily depends on network packet size configuration option.

* How many different locks in SQL SERVER
Ans:
(1)Intent
(2)shared
(3)Update
(4)Exclusive
(5)Schema
(6)Bulk Update

* Diffrence between temp table and table variable
Ans:
(1)Temp Tables are created in the SQL Server TEMPDB database and therefore require more IO resources and locking. Table Variables and Derived Tables are created in memory.

(2)Temp Tables will generally perform better for large amounts of data that can be worked on using parallelism whereas Table Variables are best used for small amounts of data (I use a rule of thumb of 100 or less rows) where parallelism would not provide a significant performance improvement.

(3)You cannot use a stored procedure to insert data into a Table Variable or Derived Table. For example, the following will work: INSERT INTO #MyTempTable EXEC dbo.GetPolicies_sp whereas the following will generate an error: INSERT INTO @MyTableVariable EXEC dbo.GetPolicies_sp.

(4)Derived Tables can only be created from a SELECT statement but can be used within an Insert, Update, or Delete statement.

(5) In order of scope endurance, Temp Tables extend the furthest in scope, followed by Table Variables, and finally Derived Tables.

* What is Cursor
Ans:
Cursor is a database object used by applications to manipulate data in a set on a row-by-row basis, instead of the typical SQL commands that operate on all the rows in the set at one time. For example, you can use cursor to include a list of all user databases and make multiple operations against each database by passing each database name as a variable

* Write some disadvantage of Cursor
Ans:
Cursor plays there row quite nicely but although there are some disadvantage of Cursor . Because we know cursor doing roundtrip it will make network line busy and also make time consuming methods. First of all select query gernate output and after that cursor goes one by one so roundtrip happen.Another disadvange of cursor are ther are too costly because they require lot of resources and temporary storage so network is quite busy.

* Can you tell me the difference between DELETE and TRUNCATE commands
Ans:
Delete command removes the rows from a table based on the condition that we provide with a WHERE clause. Truncate will actually remove all the rows from a table and there will be no data in the table after we run the truncate command.

How to pad a numeric type column with leading zeros?

2009-12-24T02:40:00.000-08:00

Execute the following Microsoft SQL Server T-SQL example scripts to demonstrate SQL padding of numbers and strings with leading and trailing zeros as well as with other designated characters, and techniques
for removing (trim) leading zeros.

-- SQL Server leading zero - T-SQL padding numbers - lpad tsql - sql pad zero

SELECT ListPrice,

  Padded=RIGHT('0000000000' + CONVERT(VARCHAR,ListPrice), 10)

FROM AdventureWorks2008.Production.Product WHERE ListPrice > 0.0

------------

-- T SQL pad leading zeros - transact sql leading zeros

SELECT ProductID, ReorderPoint = REPLACE(STR(ReorderPoint, 6), SPACE(1), '0')

FROM AdventureWorks2008.Production.Product

/* ProductID            ReorderPoint

      1                 000750

      2                 000750

      3                 000600  */

 ------------

-- SQL Server leading zero - SQL leading zeros - sql server pad leading zero

USE AdventureWorks2008;

DECLARE @Number int = 789

SELECT RIGHT ('000000'+ CAST (@Number AS varchar), 6)

-- 000789

------------

USE AdventureWorks;

-- SQL padding salary pay rate money data type with leading zeroes

-- SQL left pad number - prefix number with zeros - sql server leading zero

-- SQL convert number to text - cast number to varchar string

SELECT EmployeeID,

       Rate,

       PaddedRate = RIGHT(REPLICATE('0',8) + CAST(Rate AS VARCHAR(8)),8)

FROM   HumanResources.EmployeePayHistory
/* Partial results

EmployeeID        Rate              PaddedRate

1                 12.45             00012.45

2                 13.4615           00013.46

3                 43.2692           00043.27

*/


-- SQL zero padding ListPrice money data type - t sql leading zero

-- SQL left pad - T-SQL string concatenation - sql concat

-- SQL convert number to string - pad numeric with zeros

SELECT ProductID,

       ListPrice,

       PaddedListPrice = RIGHT(REPLICATE('0', 8) + CAST(ListPrice AS VARCHAR(8)),8)

FROM Production.Product

/* Results sample

ProductID               ListPrice               PaddedListPrice

965                     742.35                  00742.35

*/


-- SQL month leading zero - sql pad month number with zero

SELECT RIGHT('0' + convert(varchar(2), month(GetDate())), 2)

-- 06
----------

-- trim leading zeros - sql trim leading zeros - remove leading zeros sql

USE AdventureWorks2008;

DECLARE @num  varchar(32)= '00091234560'

SELECT      right(@num, len(@num)+1 - patindex('%[^0]%', @num))

-- 91234560

------------

-- SQL pad numeric data type - SQL pad digits - transact sql leading zeros

-- SQL pad with leading zeroes - append leading zeros - T-SQL top function

-- SQL pad with trailing zeroes - MSSQL append trailing zeros

-- SQL cast money to numeric - cast numeric to string - mssql newid function

SELECT TOP (5)

      ProductName = Name,

      ListPrice = RIGHT(REPLICATE('0', 10)

      + CAST(CAST(ListPrice AS NUMERIC(9,3)) AS VARCHAR) ,10)  

FROM AdventureWorks.Production.Product

ORDER BY NEWID()

/* ProductName                      ListPrice

LL Mountain Frame - Black, 40       000249.790

HL Touring Frame - Yellow, 46       001003.910

Bike Wash - Dissolver               000007.950

Metal Sheet 7                       000000.000

HL Road Frame - Red, 56             001431.500

*/

----------


-- PAD leading zeros function - sql server leading zeros - UDF

USE AdventureWorks2008;

GO

CREATE FUNCTION fnPadNumber

               (@n      DECIMAL(26,2),

                @length TINYINT)

RETURNS VARCHAR(32)

AS

  BEGIN

    RETURN ( replicate('0',@length - len(convert(VARCHAR(32),@n))) +

             convert(VARCHAR(32),@n))

  END

GO


SELECT dbo.fnPadNumber(1234567890.12,16)

-- 0001234567890.12
------------

-- T SQL computed column zero padding - sql generate alphanumeric sequence

USE tempdb; -- SQL Server 2008 T-SQL

CREATE TABLE Celebrity (

  ID           INT    IDENTITY ( 1 , 1 )    PRIMARY KEY,

  CelebrityID  AS 'CEL' + RIGHT('0000' + CAST( ID as varchar),5), -- computed column

  FirstName    VARCHAR(32),

  LastName     VARCHAR(32),

  ModifiedDate DATE    DEFAULT getdate())

GO


INSERT Celebrity

      (FirstName,

       LastName)

VALUES('Jennifer','Aniston'),

      ('Drew','Barrymore'),

      ('Diana','Princess of Wales'),

      ('Tom','Jones'),

      ('Lucille','Ball'),

      ('Frank','Sinatra'),

      ('Elvis','Presley')


SELECT * FROM   Celebrity

GO

-- CelebrityID is zero padded alphanumeric sequence

/* 

ID    CelebrityID       FirstName  LastName          ModifiedDate

1     CEL00001          Jennifer    Aniston          2012-07-04

2     CEL00002          Drew        Barrymore        2012-07-04

3     CEL00003          Diana       Princess of Wales 2012-07-04

4     CEL00004          Tom         Jones             2012-07-04

5     CEL00005          Lucille     Ball              2012-07-04

6     CEL00006          Frank       Sinatra          2012-07-04

7     CEL00007          Elvis       Presley          2012-07-04

*/

-- Cleanup demo

DROP TABLE Celebrity

GO
------------

-- SQL removing leading zeros when no spaces in string - trimming Leading Zeros

USE AdventureWorks2008;

DECLARE @NumberString varchar(16)='000000246'

SELECT REPLACE(LTRIM(REPLACE(@NumberString, '0', ' ')), ' ', '0')

-- 246

------------

-- SQL remove leading zeros - sql trim leading zeros - numeric test

DECLARE @StringWithLeadingZeros    VARCHAR(12) = '000000654321'

SELECT CAST(CAST(@StringWithLeadingZeros AS INT) AS VARCHAR(10))

WHERE ISNUMERIC (@StringWithLeadingZeros)=1

-- 654321

------------

-- LPAD & RPAD string scalar-valued user-defined functions (UDF)

USE AdventureWorks;

GO

-- Left pad string function

CREATE FUNCTION LPAD

               (@SourceString VARCHAR(MAX),

                @FinalLength  INT,

                @PadChar      CHAR(1))

RETURNS VARCHAR(MAX)

AS

  BEGIN

    RETURN

      (SELECT Replicate(@PadChar,@FinalLength - Len(@SourceString)) + @SourceString)

  END

GO


-- T-SQL Test left padding

SELECT LeftPaddedString = dbo.LPAD(Cast(84856 AS VARCHAR),12,'0')

GO

-- 000000084856


-- MSSQL right pad string function

CREATE FUNCTION RPAD

               (@SourceString VARCHAR(MAX),

                @FinalLength  INT,

                @PadChar      CHAR(1))

RETURNS VARCHAR(MAX)

AS

  BEGIN

    RETURN

      (SELECT @SourceString + Replicate(@PadChar,@FinalLength - Len(@SourceString)))

  END

GO


-- Test right padding

SELECT RightPaddedString = dbo.RPAD(Cast(84856 AS VARCHAR),12,'*')

GO

-- 84856*******

----------


-- Padding a money column with leading zeroes - sql convert leading zero

-- SQL convert money data type to string

SELECT PaddedUnitPrice = RIGHT(replicate('0',20) +

                         convert(varchar,UnitPrice,1), 20)

FROM Northwind.dbo.Products

/* Partial results


PaddedUnitPrice

00000000000000018.00

00000000000000019.00

00000000000000010.00

00000000000000022.00

*/


/**************** Zero padding other numeric data ****************/


-- SQL Server 2008 version featuring the LEFT function

-- SQL convert integer to text - convert integer to varchar

USE AdventureWorks2008;

DECLARE @InputNumber int = 522, @OutputLength tinyint = 12

DECLARE @PaddedString char(12)

SET @PaddedString = LEFT( replicate( '0', @OutputLength ),

    @OutputLength - len( @InputNumber ) ) + convert( varchar(12), @InputNumber)

SELECT PaddedNumber=@PaddedString

/* Result


PaddedNumber

000000000522

*/



-- SQL format currency and pad with leading spaces

-- SQL Server lpad to 9 characters

SELECT   TOP (3)  ProductName=Name,

                  Price=   CONVERT(char(9), ListPrice, 1)

FROM Production.Product

WHERE ListPrice > 0.0 ORDER BY Newid()

/*

ProductName                         Price

LL Touring Frame - Blue, 62           333.42

LL Road Seat Assembly                 133.34

Road-250 Red, 58                    2,443.35

*/

------------

-- Padding with zeroes in the middle of string

DECLARE @Number varchar(10)

SET @Number = '99999'

PRINT 'TRK' + REPLICATE('0', 12 - LEN(@Number)) + @Number

SELECT [Zero Padding]='TRK' + REPLICATE('0', 12 - LEN(@Number)) + @Number

-- Result: TRK000000099999


-- Applying the STUFF string function for zero padding

-- SQL convert integer data type to string

DECLARE @SerialNo int, @OutputSize tinyint

SET @OutputSize = 10

SET @SerialNo = 6543

SELECT PaddedSerialNo =  STUFF(replicate('0', @OutputSize),

    @OutputSize - len(@SerialNo)+1, len(@SerialNo), convert(varchar(9),@SerialNo))

-- Result: 0000006543

-- SQL pad integer with 0-s

-- SQL str function - numeric to character conversion

SELECT TOP 5

CAST(replace(str(ProductID,6),' ','0') AS char(6)) AS ProdID

FROM AdventureWorks.Production.Product

ORDER BY Name

/* Results

ProdID

000001

000879

000712

000003

000002

*/


-- SQL pad string with character

-- SQL create function

-- SQL user-defined function

CREATE FUNCTION dbo.fnLeftPadString (

      @Input VARCHAR(255),

      @PadChar CHAR(1),

      @LengthToPad TINYINT

      )

RETURNS VARCHAR(255) AS 

BEGIN

DECLARE @InputLen TINYINT

SET @InputLen = LEN(@Input)

RETURN

   CASE

      WHEN @InputLen < @LengthToPad

      THEN REPLICATE(@PadChar, @LengthToPad - @InputLen) + @Input

      ELSE @Input

   END -- CASE

END -- UDF

GO


-- SQL pad string – left padding - SQL left pad with asterisk

-- SQL check printing - SQL currency formatting

DECLARE @DollarAmount varchar(20)

SET @DollarAmount = '234.40'

SELECT PaddedString='$'+dbo.fnLeftPadString(@DollarAmount, '*', 10)

GO

-- Result: $****234.40


-- SQL currency formatting with asterisk-fill

DECLARE @Amount MONEY

SET @Amount = '3534.40'

SELECT CurrencyFormat = '$' + REPLACE(Convert(char(12),@Amount,1),' ','*')

-- $****3,534.40

SELECT      PaddedProductID =

                  dbo.fnLeftPadString (CONVERT(varchar, ProductID), '0', 6),

            ProductName=Name,

            ListPrice

FROM Production.Product

ORDER BY PaddedProductID

GO


/* Partial results


PaddedProductID         ProductName       ListPrice

000757                  Road-450 Red, 48  1457.99

000758                  Road-450 Red, 52  1457.99

000759                  Road-650 Red, 58  782.99

000760                  Road-650 Red, 60  782.99

000761                  Road-650 Red, 62  782.99

*/

----------

-- Generating tracking numbers

-- SQL pad zeroes

WITH cteSequence(SeqNo)

     AS (SELECT 1

         UNION ALL

         SELECT SeqNo + 1

         FROM   cteSequence

         WHERE  SeqNo < 1000000)

SELECT TOP 100 CAST(('TRK' + REPLICATE('0',

               7 - LEN(CAST(SeqNo AS VARCHAR(6)))) +

               CAST(SeqNo AS VARCHAR(6))) AS VARCHAR(10)) AS TrackingNo

FROM   cteSequence

OPTION (MAXRECURSION 0)

GO

/* Partial results


TrackingNo

TRK0000001

TRK0000002

TRK0000003

TRK0000004

TRK0000005

TRK0000006

TRK0000007

*/

----------

-- SQL server pad

-- SQL str function

-- SQL pad integer

-- SQL left pad

SELECT TOP (4) StaffName=LastName+', '+FirstName,

PaddedEmployeeID = REPLACE(STR(EmployeeID, 6, 0), ' ', '0')

FROM HumanResources.Employee e

INNER JOIN Person.Contact c

      ON e.ContactID = c.ContactID

ORDER BY NEWID()

/* Results


StaffName         PaddedEmployeeID

Dyck, Shelley     000214

Hines, Michael    000039

Ford, Jeffrey     000015

Caron, Rob        000168

*/


-- SQL asterisk padding

-- SQL pad with asterisk

-- SQL right pad

SELECT  TOP ( 2 * 2 )

              AddressID

            , City+REPLICATE('*', 20-len(City)) AS City

            , PostalCode

FROM AdventureWorks.Person.[Address]

WHERE LEN(City) <= 20

ORDER by NEWID()

GO

/*

AddressID   City                          PostalCode

13465       Imperial Beach******    91932

23217       Corvallis***********    97330

18548       Milwaukie***********    97222

24893       Goulburn************    2580

*/

------------

------------

-- SQL left pad any size string Alpha with any length string Beta

------------

-- SQL user-defined function - UDF - scalar-valued string function

-- T-SQL varchar(max)

USE AdventureWorks2008;

GO

CREATE FUNCTION fnMaxPad

               (@SourceString  VARCHAR(MAX),

                @PaddingString VARCHAR(MAX),

                @OutputLength  INT)

RETURNS VARCHAR(MAX)

AS

  BEGIN

    DECLARE  @WorkString VARCHAR(MAX) =

      ISNULL(REPLICATE(@PaddingString,@OutputLength - len(ISNULL(@SourceString,0))),

                            '') + @SourceString

    RETURN RIGHT(@WorkString, @OutputLength)

  END

GO


-- Execute UDF

SELECT TOP ( 5 ) [SQL Padding Demo] =

  dbo.fnMaxPad(CONVERT(VARCHAR,FLOOR(ListPrice)),'_/',21)

FROM     Production.Product

ORDER BY NEWID()

GO

/* Results


SQL Padding Demo

_/_/_/_/_/_/_/2443.00

/_/_/_/_/_/_/_/_/0.00

/_/_/_/_/_/_/_/147.00

/_/_/_/_/_/_/_/_/0.00

_/_/_/_/_/_/_/1003.00

*/

------------

-- SQL left pad unicode string Alpha with any length unicode string Beta

-- MSSQL pad international

-- SQL user-defined function - UDF - scalar-value function

-- T-SQL nvarchar(max)

USE AdventureWorks2008;

GO

CREATE FUNCTION fnMaxPadInternational

               (@SourceString  NVARCHAR(MAX),

                @PaddingString NVARCHAR(MAX),

                @OutputLength  INT)

RETURNS NVARCHAR(MAX)

AS

  BEGIN

    DECLARE  @WorkString NVARCHAR(MAX) =

      ISNULL(REPLICATE(@PaddingString,@OutputLength - len(ISNULL(@SourceString,0))),

                            '') + @SourceString

    

    RETURN RIGHT(@WorkString, @OutputLength)

  END

GO


-- Execute UDF

SELECT TOP ( 5 ) [SQL Padding Demo] =

dbo.fnMaxPadInternational(LEFT(Description,6),'_/',21)

FROM     Production.ProductDescription

ORDER BY NEWID()

GO

/* Results


SQL Padding Demo

/_/_/_/_/_/_/_/ล้อ ที่

/_/_/_/_/_/_/_/Roue d

/_/_/_/_/_/_/_/شوكة ط

/_/_/_/_/_/_/_/Each f

/_/_/_/_/_/_/_/Jeu de

*/

------------

Reference from sqlusa.com

How to configure SQL Server 2005 to allow remote connections?

2009-12-19T01:33:00.000-08:00

To configure SQL Server 2005 to allow remote connections, complete all the following steps.
• Enable remote connections on the instance of SQL Server that you want to connect to from a remote computer.
• Turn on the SQL Server Browser service.
• Configure the firewall to allow network traffic that is related to SQL Server and to the SQL Server Browser service.

Enable remote connections for SQL Server 2005 Express

1. Click Start, point to Programs, point to Microsoft SQL Server 2005, point to Configuration Tools, and then click SQL Server Surface Area Configuration.
2. On the SQL Server 2005 Surface Area Configuration page, click Surface Area Configuration for Services and Connections.
3. On the Surface Area Configuration for Services and Connections page, expand Database Engine, click Remote Connections, click Local and remote connections, click the appropriate protocol to enable for your environment, and then click Apply.
Note Click OK when you receive the following message:
Changes to Connection Settings will not take effect until you restart the Database Engine service.
4. On the Surface Area Configuration for Services and Connections page, expand Database Engine, click Service, click Stop. wait until the MSSQLSERVER service stops, and then click Start to restart the MSSQLSERVER service.

Enable the SQL Server Browser service

1.Click Start, point to Programs, point to Microsoft SQL Server 2005, point to Configuration Tools, and then click SQL Server Surface Area Configuration.
2.On the SQL Server 2005 Surface Area Configuration page, click Surface Area Configuration for Services and Connections.
3.On the Surface Area Configuration for Services and Connections page, click SQL Server Browser, click Automatic for Startup type, and then click Apply.
Note When you click the Automatic option, the SQL Server Browser service starts automatically every time that you start Microsoft Windows.
4.Click Start, and then click OK.

Create exceptions in Windows Firewall

SQL Server 2005 uses an instance ID as part of the path when you install its program files. To create an exception for each instance of SQL Server, you must identify the correct instance ID. To obtain an instance ID, follow these steps:
1. Click Start, point to Programs, point to Microsoft SQL Server 2005, point to Configuration Tools, and then click SQL Server Configuration Manager.
2. In SQL Server Configuration Manager, click the SQL Server Browser service in the right pane, right-click the instance name in the main window, and then click Properties.
3. On the SQL Server Browser Properties page, click the Advanced tab, locate the instance ID in the property list, and then click OK.
To open Windows Firewall, click Start, click Run, type firewall.cpl, and then click OK.
Create an exception for SQL Server 2005 in Windows Firewall
To create an exception for SQL Server 2005 in Windows Firewall, follow these steps:
1. In Windows Firewall, click the Exceptions tab, and then click Add Program.
2. In the Add a Program window, click Browse.
3. Click the C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Binn\sqlservr.exe executable program, click Open, and then click OK.
Note The path may be different depending on where SQL Server 2005 is installed. MSSQL.1 is a placeholder for the instance ID that you obtained in step 3 of the previous procedure.
4. Repeat steps 1 through 3 for each instance of SQL Server 2005 that needs an exception.
Create an exception for the SQL Server Browser service in Windows Firewall
To create an exception for the SQL Server Browser service in Windows Firewall, follow these steps:
1. In Windows Firewall, click the Exceptions tab, and then click Add Program.
2. In the Add a Program window, click Browse.
3. Click the C:\Program Files\Microsoft SQL Server\90\Shared\sqlbrowser.exe executable program, click Open, and then click OK.

---
Hope this helps everyone.

Encryption & Decryption : ASCII encoding

2009-12-18T04:34:00.000-08:00

The GetByteCount method determines how many bytes result in encoding a set of Unicode characters, and the GetBytes method performs the actual encoding.

Likewise, the GetCharCount method determines how many characters result in decoding a sequence of bytes, and the GetChars and GetString methods perform the actual decoding.

When selecting the ASCII encoding for your applications, consider the following:

    * The ASCII encoding is usually appropriate for protocols that require ASCII.
    * If your application requires 8-bit encoding, the UTF-8 encoding is recommended over the ASCII encoding. For the characters 0-7F, the results are identical, but use

of UTF-8 avoids data loss by allowing representation of all Unicode characters that are representable. Note that the ASCII encoding has an 8th bit ambiguity that can allow malicious use, but the UTF-8 encoding removes ambiguity about the 8th bit.

    * Previous versions of .NET Framework allowed spoofing by merely ignoring the 8th bit. The current version has been changed so that non-ASCII code points fall back during the decoding of bytes.

——————————————————————
Example of ASCII encoding
——————————————————————

SecurityClass.cs
————————–

public static string EnryptString(string strEncrypted)
{
byte[] b = System.Text.ASCIIEncoding.ASCII.GetBytes(strEncrypted);
string encryptedConnectionString = Convert.ToBase64String(b);
return encryptedConnectionString;
}

public static string DecryptString(string encrString)
{
byte[] b = Convert.FromBase64String(encrString);
string decryptedConnectionString = System.Text.ASCIIEncoding.ASCII.GetString(b);
return decryptedConnectionString;
}

Default.aspx.cs
———————

protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
string Password = “SandeepMRamani”;
lblOriginal.Text = Password;
lblEncryption.Text = SecurityClass.EnryptString(Password);
lblDecryption.Text = SecurityClass.DecryptString(lblEncryption.Text);
}
}

Default.aspx
——————

<div align=”center” style=”font-size:xx-large;”>
<h1> Encryption / Decryption Example </h1>
<hr />

Original String :
<asp:Label ID=”lblOriginal” style=”font-weight:bolder;” runat=”server” Text=”"></asp:Label>
<br />
Encrypted Text :
<asp:Label ID=”lblEncryption” style=”font-weight:bolder;” runat=”server” Text=”"></asp:Label>
<br />
Decrypted Text :
<asp:Label ID=”lblDecryption” style=”font-weight:bolder;” runat=”server” Text=”"></asp:Label>
<hr />
</div>

--

Happy programming

Differences between varchar and nvarchar in SQL Server

2009-12-18T00:21:00.000-08:00

=>SQL Server 2000 and SQL Server 7, a row cannot exceed 8000 bytes in size. This means that a VARBINARY column can only store 8000 bytes (assuming it is the only column in a table), a VARCHAR column can store up to 8000 characters and an NVARCHAR column can store up to 4000 characters (2 bytes per unicode character). This limitation stems from the 8 KB internal page size SQL Server uses to save data to disk.
To store more data in a single column, you needed to use the TEXT, NTEXT, or IMAGE data types (BLOBs) which are stored in a collection of 8 KB data pages that are separate from the data pages that store the other data in the same table. These data pages are arranged in a B-tree structure. BLOBs are hard to work with and manipulate. They cannot be used as variables in a procedure or a function and they cannot be used inside string functions such as REPLACE, CHARINDEX or SUBSTRING. In most cases, you have to use READTEXT, WRITETEXT, and UPDATETEXT commands to manipulate BLOBs.

To solve this problem, Microsoft introduced the VARCHAR(MAX), NVARCHAR(MAX), and VARBINARY(MAX) data types in SQL Server 2005. These data types can hold the same amount of data BLOBs can hold (2 GB) and they are stored in the same type of data pages used for other data types. When data in a MAX data type exceeds 8 KB, an over-flow page is used. SQL Server 2005 automatically assigns an over-flow indicator to the page and knows how to manipulate data rows the same way it manipulates other data types. You can declare variables of MAX data types inside a stored procedure or function and even pass them as variables. You can also use them inside string functions.

The broad range of data types in SQL Server can sometimes throw people through a loop, especially when the data types seem to be highly interchangeable. Two in particular that constantly spark questions are VARCHAR and NVARCHAR: what's the difference between the two, and how important is the difference? VARCHAR is an abbreviation for variable-length character string. It's a string of text characters that can be as large as the page size for the database table holding the column in question. The size for a table page is 8,196 bytes, and no one row in a table can be more than 8,060 characters. This in turn limits the maximum size of a VARCHAR to 8,000 bytes.
The "N" in NVARCHAR means uNicode. Essentially, NVARCHAR is nothing more than a VARCHAR that supports two-byte characters. The most common use for this sort of thing is to store character data that is a mixture of English and non-English symbols — in my case, English and Japanese.
The key difference between the two data types is how they're stored. VARCHAR is stored as regular 8-bit data. But NVARCHAR strings are stored in the database as UTF-16 — 16 bits or two bytes per character, all the time — and converted to whatever codepage is being used by the database connection on output (typically UTF-8). That said, NVARCHAR strings have the same length restrictions as their VARCHAR cousins — 8,000 bytes. However, since NVARCHARs use two bytes for each character, that means a given NVARCHAR can only hold 4,000 characters (not bytes) maximum. So, the amount of storage needed for NVARCHAR entities is going to be twice whatever you'd allocate for a plain old VARCHAR.
Because of this, some people may not want to use NVARCHAR universally, and may want to fall back on VARCHAR — which takes up less space per row — whenever possible.

How to dynamically adjust an iframe’s height?

2009-12-16T22:01:00.000-08:00

I was looking to display content of the other aspx page inside iframe but i wasnt able to adjust the height of the iframe. so try out this solution to resolve it.

Insert iframe on page

   <iframe scrolling='no' frameborder='0' id='frmid' src=’getad.aspx'
            onload='javascript:resizeIframe(this);'>
   </iframe>

Use this javascript to resize iframe based on the height & width of child page

<script language="javascript" type="text/javascript">
 function resizeIframe(obj)
 {
   obj.style.height = obj.contentWindow.document.body.scrollHeight + 'px';
   obj.style.width = obj.contentWindow.document.body.scrollWidth + 'px';
 }
 </script>

What does this code do? When the body of the parent frame loads, it looks up the document element “childframe” which corresponds to the iframe. Then the page calls a function resizeFrame(). The function sets the height of the frame to be the scrollHeight, which effectively removes the scrollbar.

happy programming!