Wednesday, February 24, 2010

Reset Identity Column Value in SQL Server

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)

Monday, February 15, 2010

Custom Paging In GridView without objectDataSource

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....

Wednesday, February 10, 2010

.NET 3.5 LANGUAGE ENHANCEMENTS

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.

Saturday, February 6, 2010

NEW DATA TYPES IN SQL SERVER 2008

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.




Thursday, February 4, 2010

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

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:
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



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!

Ideal SQL Query For Handling Error & Transcation in MS SQL

BEGIN TRY BEGIN TRAN --put queries here COMMIT; END TRY BEGIN CATCH IF @@TRANCOUNT>0 BEGIN SELECT @@ERROR,ERRO...