Monday, January 11, 2010

Static

Static variables are called Class Fields
Static methods are called Class Members
Not static fields are called instance fields or members

What are the things that we can declare as static?
1.data fields
2.member functions
3.properties
4.events

CLASS
A C# class can contain both static and non-static members. When we declare a member with the help of the keyword static, it becomes a static member.

A static member belongs to the class rather than to the objects of the class. Hence static members are also known as class members and non-static members are known as instance members.

In C#, data fields, member functions, properties and events can be declared either as static or non-static. Remember that indexers in C# can't declare as static.

Static classes cannot be instantiated. (I.e. creating an object). If we try to create an object we will get the error, Cannot create an instance of the abstract class or interface 'StaticClassName'

When a class is declared to as static, it is sealed, abstract, and no instance members can be overridden or declared. A static class contains only static members

Since static classes are sealed so they cannot be inherited.

Advantage
No need to create instance.
When to use static class?
Suppose we are using certain methods or properties often we can just call them without creating instance.
It contains only the last updated value. The updated value reflects to all the users.
Static classes can be used when there is no data or behavior in the class that depends on object identity.

static class A
{
static void Foo()
{

A x = null as A; // Cannot declare a variable of static type 'A'
A a; // Cannot declare a variable of static type 'A'
a = new A(); // Cannot create an instance of the static class 'A'

}
}

Static Fields
Static fields can be declared as follows by using the keyword static.
class MyClass
{
public static int x;
public static int y = 20;
}
When we declare a static field inside a class, it can be initialized with a value as shown above.

The static field for a class executes at most once in a given application domain (i.e. when we keep the breakpoint both at a static and non static field and when we refresh the page, the non static field executes but static field didn't). But the value can be changed later in the constructor or anywhere. The last updated value will be maintained there after.

All un-initialized static fields automatically get initialized to their default values when the class is loaded first time.

The C# provides a special type of constructor known as static constructor to initialize the static data members when the class is loaded at first.
// C# static constructor
// Author: rajeshvs@msn.com
using System;
class MyClass
{
public static int x;
public static int y;
static MyClass()
{
x = 100;
Y = 200;
}
}
class MyClient
{
public static void Main()
{
Console.WriteLine("{0},{1},{2}", MyClass.x, MyClass.y);
}
}
Constructor:
Note that static constructor is called when the class is loaded at the first time. However, we can't predict the exact time and order of static constructor execution.

They are called before an instance of the class is created, before a static member is called and before the static constructor of the derived class is called.

Static class can't have instance constructor. (Since we can’t able to instantiate a static class)

A static constructor must be parameter less. (That means there is only one form of static constructor, without any parameters. In other way it is not possible to overload a static constructor).

There is no access modifier require defining a static constructor.

The static constructor for a class executes at most once in a given application domain (i.e. Static constructors are used to perform a particular action that needs performed once only.) The execution of a static constructor is triggered by the first of the following events to occur within an application domain:

1. An instance of the class is created.
2. Any of the static members of the class are referenced.


Static constructor called before the class is referenced for the first time in your program.
It contains only static variables.

Static constructors can't access non-static data members directly.

(A non-static class can have static constructor. When creating instance the static constructor will be called first. Here also the constructor will be called only once till we restart web server.)

Member Functions
Only static members are allowed inside a static class. (I.e. if a class is declared as static all the fields, members should be static.)

We can invoke a static member only through the name of the class.

But a static member function can access only other static members. (I.e. if a member is declared as static all the fields should be static and the method that we are calling from (the caller method) should be static.)
We can’t able to declare a variable as static inside a static method.

An object reference is required for the non-static field, method, or property.

public class foo
{

int x;

static void doSomthingWithX()
{

Console.Write(x.toString);

}
}


Member modifier 'static' must precede the member type and name

void static doSomthingWithX()

Static classes cannot have instance constructors

Cannot declare instance members in a static class

public static partial class StaticClassTest
{

//Static classes cannot have instance constructors
StaticClassTest()
{
}

//cannot declare instance members in a static class
int j = 10;


static int i = 10;

public static int ClassMethod()
{

//The modifier 'static' is not valid for this item
//static int k = 10;

int k = 10;
return i;

return i;
}

//'InstanceMethod': cannot declare instance members in a static class
public int InstanceMethod()
{
return 8;
}

}

The modifier 'static' is not valid for this item
private static void StaticTest()
{
static int i;
i =10;

}

Solution
Whatever we declare inside static method, it is static by default.. So no need to declare a variable as static in static method.

Static Properties
The properties also can be declared as static. The static properties are accessing using the class name. All the rules applicable to a static member are applicable to static properties.
public class PropertyClass
{
private static int x;
public static int X
{
get
{
return x;
}
set
{
x = value;
}
}
}
protected void Page_Load(object sender, EventArgs e)
{
PropertyClass.X = 10; // calls setter
int value = PropertyClass.X; // calls getter
}
Remember that set/get accessor of static property can access only other static members of the class. Also static properties are invoking by using the class name.

Static Members & Inheritance
A derived class can inherit a static member.

But a static member in C# can't be marked as override, virtual or abstract.

However it is possible to hide a base class static method in a derived class by using the keyword new.
public class BaseClassTest
{
public static int i = 10;
public static int get()
{
return i;
}
}

public class ChildClassTest : BaseClassTest
{
public static int j;

//ChildClassTest()
//{
// //cannot be accessed with an instance reference; qualify it with a type name instead
// j = base.i;
//}

//static ChildClassTest()
//{
// //Keyword 'base' is not available in a static method
// j = base.i;
//}

}
class Final : ChildClassTest
{
int p = ChildClassTest.i;
int o = ChildClassTest.get();
int r = ChildClassTest.j;
}

class tester
{
int p = Final.i;
int o = Final.get();
int r = Final.j;
}
Finally remember that it is not possible to use this to reference static methods.
Static Indexers
In C# there is no concept of static indexers, even though static properties are there.
http://www.c-sharpcorner.com/UploadFile/rajeshvs/PropertiesInCS11122005001040AM/PropertiesInCS.aspx

http://www.csharphelp.com/2006/04/c-static-members/

Monday, December 7, 2009

DBCC CHECKIDENT ('table_name', RESEED, new_reseed_value)

The current identity value is set to the new_reseed_value. If no rows have been inserted to the table since it was created, the first row inserted after executing DBCC CHECKIDENT will use new_reseed_value as the identity. Otherwise, the next row inserted will use new_reseed_value + 1. If the value of new_reseed_value is less than the maximum value in the identity column, error message 2627 will be generated on subsequent references to the table.

1. Consider our table having some row. Now we are deleting the table with query

DELETE FROM table_name

DBCC CHECKIDENT ('table_name', RESEED, 0)
The table will generate an identity value form 1 and not from 0.


2. Now we are truncating the table with TRUNCATE TABLE Statement.

TRUNCATE TABLE table_name

DBCC CHECKIDENT ('table_name', RESEED, 0)
The table will generate an identity value form 0.

During INSERT if there is an error, the row will not Inserted but the Identity value increased according to(IDENT_INCR) the Increment.

Wednesday, December 2, 2009

Methods to find table INDEX or Statistics age for better performance

Original Post

 
SELECT OBJECT_NAME(tbl.object_id),
'Index Name' = ind.name ,
'Statistics Date' = STATS_DATE(ind.object_id, ind.index_id)
FROM SYS.INDEXES ind
INNER JOIN SYS.TABLES tbl
ON tbl.OBJECT_ID = ind.object_id
WHERE tbl.TYPE = 'U'

--USE babu;
--GO
--EXEC sp_updatestats --To update all STATISTICS in the DB

Create Parameters

DECLARE @TABLE_NAME VARCHAR(50) = 'PrameterTable'
DECLARE @TableName AS         VARCHAR(200)
DECLARE @ParamNames AS        VARCHAR(8000)
SET @TableName = 'TableTest'
SELECT @ParamNames = (COALESCE(@ParamNames + CHAR(13) + CHAR(10), '') +
       CASE
              WHEN DATA_TYPE = 'bigint'
              THEN '@' + COLUMN_NAME + ' ' + DATA_TYPE
              WHEN DATA_TYPE = 'binary'
              THEN '@' + COLUMN_NAME + ' ' + DATA_TYPE + '(' + CAST(character_maximum_length AS VARCHAR) + ')'
              WHEN DATA_TYPE = 'Bit'
              THEN '@' + COLUMN_NAME + ' ' + DATA_TYPE
              WHEN DATA_TYPE = 'Char'
              THEN '@' + COLUMN_NAME + ' ' + DATA_TYPE + '(' + CAST(character_maximum_length AS VARCHAR) + ')'
              WHEN DATA_TYPE = 'Date'
              THEN '@' + COLUMN_NAME + ' ' + DATA_TYPE
              WHEN DATA_TYPE = 'DateTime'
              THEN '@' + COLUMN_NAME + ' ' + DATA_TYPE
              WHEN DATA_TYPE = 'DateTime2'
              THEN '@' + COLUMN_NAME + ' ' + DATA_TYPE
              WHEN DATA_TYPE = 'DateTimeOffset'
              THEN '@' + COLUMN_NAME + ' ' + DATA_TYPE
              WHEN DATA_TYPE = 'Decimal'
              THEN '@' + COLUMN_NAME + ' ' + DATA_TYPE + '(' + CAST(NUMERIC_precision AS VARCHAR) + ',' + CAST(numeric_scale AS VARCHAR) + ')'
              WHEN DATA_TYPE = 'float'
              THEN '@' + COLUMN_NAME + ' ' + DATA_TYPE + '(' + CAST(NUMERIC_precision AS VARCHAR) + ')'
              WHEN DATA_TYPE = 'geography'
              THEN '@' + COLUMN_NAME + ' ' + DATA_TYPE
              WHEN DATA_TYPE = 'geometry'
              THEN '@' + COLUMN_NAME + ' ' + DATA_TYPE
              WHEN DATA_TYPE = 'hierarchyid'
              THEN '@' + COLUMN_NAME + ' ' + DATA_TYPE
              WHEN DATA_TYPE = 'image'
              THEN '@' + COLUMN_NAME + ' ' + DATA_TYPE
              WHEN DATA_TYPE = 'int'
              THEN '@' + COLUMN_NAME + ' ' + DATA_TYPE --+ '(' + CAST(NUMERIC_precision AS VARCHAR) + ')'
              WHEN DATA_TYPE = 'money'
              THEN '@' + COLUMN_NAME + ' ' + DATA_TYPE --+ '(' + CAST(NUMERIC_precision AS VARCHAR) + ',' + CAST(numeric_scale AS VARCHAR) + ')'
              WHEN DATA_TYPE = 'NCHAR'
              THEN '@' + COLUMN_NAME + ' ' + DATA_TYPE + '(' + CAST(character_maximum_length AS VARCHAR) + ')'
              WHEN DATA_TYPE = 'numeric'
              THEN '@' + COLUMN_NAME + ' ' + DATA_TYPE + '(' + CAST(NUMERIC_precision AS VARCHAR) + ',' + CAST(numeric_scale AS VARCHAR) + ')'
              WHEN DATA_TYPE = 'NVARCHAR'
              THEN '@' + COLUMN_NAME + ' ' + DATA_TYPE + '(' + CAST(character_maximum_length AS VARCHAR) + ')'
              WHEN DATA_TYPE = 'ntext'
              THEN '@' + COLUMN_NAME + ' ' + DATA_TYPE --+ '(' + CAST(character_maximum_length AS VARCHAR) + ')'
              WHEN DATA_TYPE = 'REAL'
              THEN '@' + COLUMN_NAME + ' ' + DATA_TYPE --+ '(' + CAST(NUMERIC_precision AS VARCHAR) + ')'
              WHEN DATA_TYPE = 'smalldatetime'
              THEN '@' + COLUMN_NAME + ' ' + DATA_TYPE
              WHEN DATA_TYPE = 'smallint'
              THEN '@' + COLUMN_NAME + ' ' + DATA_TYPE --+ '(' + CAST(NUMERIC_precision AS VARCHAR) + ')'
              WHEN DATA_TYPE = 'smallmoney'
              THEN '@' + COLUMN_NAME + ' ' + DATA_TYPE --+ '(' + CAST(NUMERIC_precision AS VARCHAR) + ',' + CAST(numeric_scale AS VARCHAR) + ')'
              WHEN DATA_TYPE = 'sql_variant'
              THEN '@' + COLUMN_NAME + ' ' + DATA_TYPE
              WHEN DATA_TYPE = 'text'
              THEN '@' + COLUMN_NAME + ' ' + DATA_TYPE + '(' + CAST(character_maximum_length AS VARCHAR) + ')'
              WHEN DATA_TYPE = 'time'
              THEN '@' + COLUMN_NAME + ' ' + DATA_TYPE
              WHEN DATA_TYPE = 'timestamp'
              THEN '@' + COLUMN_NAME + ' ' + DATA_TYPE
              WHEN DATA_TYPE = 'tinyint'
              THEN '@' + COLUMN_NAME + ' ' + DATA_TYPE + '(' + CAST(NUMERIC_precision AS VARCHAR) + ')'
              WHEN DATA_TYPE = 'uniqueidentifier'
              THEN '@' + COLUMN_NAME + ' ' + DATA_TYPE
              WHEN DATA_TYPE = 'varbinary'
              THEN '@' + COLUMN_NAME + ' ' + DATA_TYPE + '(' + CAST(character_maximum_length AS VARCHAR) + ')'
              WHEN DATA_TYPE = 'varchar'
              THEN '@' + COLUMN_NAME + ' ' + DATA_TYPE + '(' + CAST(character_maximum_length AS VARCHAR) + ')'
              WHEN DATA_TYPE = 'xml'
              THEN '@' + COLUMN_NAME + ' ' + DATA_TYPE
       END )           + ','
FROM   INFORMATION_SCHEMA.Columns
WHERE  TABLE_NAME = @TableName

SET @ParamNames = LEFT(@ParamNames, LEN(@ParamNames)-1)
PRINT @ParamNames


Re Written Query

DECLARE @TABLE_NAME VARCHAR(50) = 'ContractMaster',
@DATA_TYPE  VARCHAR(50) = 'numeric'

SELECT paremeters       = ('@' + column_name + ' ' +
CASE
WHEN data_type   = 'BIGINT'
OR     data_type = 'Bit'
OR     data_type = 'Date'
OR     data_type = 'DateTime'
OR     data_type = 'DateTime2'
OR     data_type = 'DateTimeOffset'
OR     data_type = 'geography'
OR     data_type = 'geometry'
OR     data_type = 'hierarchyid'
OR     data_type = 'image'
OR     data_type = 'smalldatetime'
OR     data_type = 'sql_variant'
OR     data_type = 'time'
OR     data_type = 'timestamp'
OR     data_type = 'uniqueidentifier'
OR     data_type = 'xml'
THEN data_type
WHEN data_type   = 'binary'
OR     data_type = 'Char'
OR     data_type = 'NCHAR'
OR     data_type = 'NVARCHAR'
OR     data_type = 'text'
OR     data_type = 'varbinary'
OR     data_type = 'varchar'
THEN data_type + '(' + CAST(character_maximum_length AS VARCHAR) + ')'
WHEN data_type   = 'Decimal'
OR     data_type = 'money'
OR     data_type = 'numeric'
OR     data_type = 'smallmoney'
THEN data_type + '(' + CAST(numeric_precision AS VARCHAR) + ',' + CAST(numeric_scale AS VARCHAR) + ')'
WHEN data_type   = 'float'
OR     data_type = 'int'
OR     data_type = 'REAL'
OR     data_type = 'smallint'
OR     data_type = 'tinyint'
THEN data_type + '(' + CAST(numeric_precision AS VARCHAR) + ')'
ELSE data_type
END) + ' ,'
FROM   information_schema.columns
WHERE  table_name = @TABLE_NAME
AND
(
CASE
WHEN
(
@DATA_TYPE IS NULL
OR     @DATA_TYPE       = ''
)
THEN 1
END       = 1
OR     data_type = @DATA_TYPE
)


Re Written Query
DECLARE @TableName AS         VARCHAR(200)
DECLARE @TableName AS         VARCHAR(200)
DECLARE @ParamNames AS        VARCHAR(8000)
DECLARE @HorizontalTabChar AS CHAR(1)
DECLARE @NewLineChar AS       CHAR(2)
SET @TableName         = 'TableTest'
SET @HorizontalTabChar = CHAR(9)
SET @NewLineChar       = CHAR(13) + CHAR(10)
SET @ParamNames        = REPLICATE(@HorizontalTabChar, 3)
SELECT @ParamNames     = @ParamNames + '@' + syscolumns.name + ' ' + t.name + CAST( (
                     CASE
                            WHEN COLUMNPROPERTY(OBJECT_ID(@TableName), syscolumns.name, 'Scale') IS NOT NULL
                            THEN '('  + CAST(COLUMNPROPERTY(OBJECT_ID(@TableName), syscolumns.name, 'Precision') AS VARCHAR) + ',' + CAST(COLUMNPROPERTY(OBJECT_ID(@TableName), syscolumns.name, 'Scale')AS VARCHAR) + ')'
                            ELSE '('  + CAST( COLUMNPROPERTY(OBJECT_ID(@TableName), syscolumns.name, 'Precision')AS VARCHAR) +')'
                     END) AS VARCHAR) + ',' + @NewLineChar + REPLICATE(@HorizontalTabChar, 3)
FROM   sysobjects
       JOIN syscolumns
       ON     sysobjects.id = syscolumns.id
       INNER JOIN systypes t
       ON     syscolumns.xusertype = t.xusertype
WHERE  sysobjects.ID               = OBJECT_ID(@TableName)
SET @ParamNames                    = LEFT(@ParamNames, LEN(@ParamNames)-6)
PRINT @ParamNames

Another One Not Much Confident
DECLARE @TABLE_NAME VARCHAR(50) 
SET @TABLE_NAME =  'tabletest'

SELECT '@' + syscolumns.name + ' ' + UPPER(systypes.name) + ' ' +
       CASE
              WHEN syscolumns.prec    IS NULL
              AND    syscolumns.scale IS NULL
              THEN '(' + CAST(syscolumns.length AS VARCHAR) + ')'
              WHEN syscolumns.prec    = 0
              AND    syscolumns.scale = 0
              THEN '(' + CAST(syscolumns.length AS VARCHAR) + ')'
              WHEN syscolumns.prec    > 0
              AND    syscolumns.scale = 0
              THEN ''--CAST(syscolumns.length AS VARCHAR)
              WHEN syscolumns.prec          = 0
              AND    syscolumns.scale IS NULL
              THEN '(' + CAST(syscolumns.length AS VARCHAR) + ')'
              WHEN syscolumns.prec          > 0
              AND    syscolumns.scale IS NULL
              THEN '(' + CAST(syscolumns.length AS VARCHAR) + ')'
              ELSE '(' + CAST(syscolumns.prec AS   VARCHAR) + ' ,' + CAST(syscolumns.scale AS VARCHAR) + ')'
       END
FROM   sysobjects
       JOIN syscolumns
       ON     sysobjects.id = syscolumns.id
       JOIN systypes
       ON     syscolumns.xtype=systypes.xtype
WHERE  sysobjects.xtype       ='U'
AND    sysobjects.name        = @TABLE_NAME

Tuesday, November 17, 2009

Find the tables which does not have an Identity Key column

 
SELECT isc.table_name
FROM information_schema.columns AS isc
INNER JOIN sys.tables
ON SYS.tables.object_id = object_id(isc.TABLE_NAME)
WHERE SYS.tables.type = 'u'
GROUP BY isc.table_name
HAVING MAX(COLUMNPROPERTY( OBJECT_ID(isc.table_name), isc.column_name,'Isidentity')) =0--( To get the table which doesnot contains identity columns')
ORDER BY isc.table_name

Monday, November 16, 2009

Find the index of row in DataTable

string query = "Year = " + "2000" + " AND Period = " + "'1'";

int rowIndex = dtSaveAll.Rows.IndexOf(dtSaveAll.Select(query)(0));

Find all SQL Identity columns

 
SELECT '['+table_schema+'].['+table_name+'] .['+column_name+']'AS SchemaTable,
table_name ,
column_name ,
ordinal_position ,
data_type
FROM information_schema.columns
WHERE columnproperty(object_id(table_schema + '.' + table_name), column_name,'IsIdentity') = 1
ORDER BY table_name


SELECT '['+table_schema+'].['+table_name+'] .['+column_name+']'AS SchemaTable,
table_name ,
column_name ,
ordinal_position ,
data_type ,
Seed_Value AS SeedValue ,
Increment_Value AS IncrementValue ,
ident_current(Object_Name(ic.Object_ID)) AS CurrentValue ,
Last_Value AS LastValue
FROM information_schema.columns AS isc
INNER JOIN sys.identity_columns AS ic
ON ic.object_id = OBJECT_ID('['+isc.table_schema+'].['+isc.table_name+']')
INNER JOIN sys.tables
ON SYS.tables.object_id = ic.object_id
WHERE columnproperty(object_id(isc.table_schema + '.' + table_name), column_name,'IsIdentity') = 1 --( To get the table which contains only identity columns')
AND SYS.tables.type = 'u'
ORDER BY table_name