SQL

1.To Add a column: (column  word is not required) but data type+field size required.

ALTER  TABLE CUSTOMER ADD DOB DATETIME

2. To drop a column:Word column is required

ALTER  TABLE CUSTOMER drop column DOB

3. To delete the table completely

DROP TABLE customer

4.To create a new table

CREATE TABLE customer(id char(20),name varchar(20),age int);

5.To insert the data in the  same order as the  field names

INSERT INTO customer values('eoo1','rakesh',23)

6. To insert the data in the   discrete order of the  field names

Insert into customer (name,age)values('raman','45')

7.To view all fields

SELECT * FROM customer

8.To view specific fields

select name,id from customer

9.To  Alter the column field size

ALTER TABLE customer ALTER COLUMN name varchar(100)

10.To Add multiple columns somultaneously

 alter table customer add salary int,designation varchar(10)

11.To Delete  entire row from table

 DELETE FROM CUSTOMER WHERE name='rakesh'

12. Update the null values

UPDATE emp SET designation='Developer' WHERE designation IS NULL and ename='sami'

13.Updating a field value

 UPDATE emp1  SET empid='109' WHERE ename='vinu'

14.Renaming table name

SP_RENAME 'emp','emp1'

15. Renaming table columns

SP_RENAME 'emp1.designation','desig'

16. Logical operator

a)Between:

SELECT  * FROM emp1 WHERE salary between 1000 and 1500

b)Not

SELECT  * FROM emp1 WHERE not ename='rakesh'

c)AND

SELECT  * FROM emp1 WHERE salary=1300 and ename='rakesh'

d)OR

SELECT  * FROM emp1 WHERE ename=’sona’ or ename='rakesh'

17.Not Between

SELECT  * FROM emp1 WHERE salary  not between 1000 and 1200

18.In checks the equality

SELECT  * FROM emp WHERE emp_id IN(101,102)

20. Order By clause(ascending order)

SELECT  *

FROM emp

ORDER  BY ename

21.(Descending order)

SELECT  *

FROM emp1

  ORDER BY ename DESC

22.Agreegate  functions

a) SELECT max(price) max_price FROM books

b) SELECT avg(price) avg_price FROM books

c)SELECT COUNT(title) show FROM books

d) SELECT MIN(price) FROMbooks

e)SELECT sum(price) total_price FROMbooks

23. Common types of constraints include the following:

1. NOT NULL Constraint: Ensures that a column cannot have NULL value.
2. DEFAULT Constraint: Provides a default value for a column when none is specified.
3. UNIQUE Constraint: Ensures that all values in a column are different.
4. CHECK Constraint: Makes sure that all values in a column satisfy certain criteria.
5. Primary Key Constraint: Used to uniquely identify a row in the table.
6. Foreign Key Constraint: Used to ensure referential integrity of the data.

1)NOT NULL Constraint

With create statement

1.create table product(p_id char(12) not null,pname char(10))

2setting not null field after creating table(not done)

2)default(with create statement)

create table product(p_id char(12) ,pname char(10),salary money default 4000 )

b) setting default  after table creation

By default, a column can hold NULL. If you not want to allow NULL value in a column, you will want to place a constraint on this column specifying that NULL is now not an allowable value.

For example, in the following statement,

CREATE TABLE Customer
(SID integer NOT NULL,
Last_Name varchar (30) NOT NULL,
First_Name varchar(30));

Columns "SID" and "Last_Name" cannot include NULL, while "First_Name" can include NULL.

An attempt to execute the following SQL statement,

INSERT INTO Customer (Last_Name, First_Name) values ('Wong','Ken');

will result in an error because this will lead to column "SID" being NULL, which violates the NOT NULL constraint on that column.

2 i)The DEFAULT constraint provides a default value to a column when the INSERT INTO statement does not provide a specific value. 

CREATE TABLE Student

(Student_ID integer ,

Last_Name varchar (30),

First_Name varchar (30),

Score int DEFAULT 80);

ii).To add the default value of a new column in an existing table

 alter table emp1 add score int default 200

3) UNIQUE Constraint

The UNIQUE constraint ensures that all values in a column are distinct.

For example, in the following CREATE TABLE statement,

CREATE TABLE Customer
(SID integer Unique,
Last_Name varchar (30),
First_Name varchar(30));

column "SID" has a unique constraint, and hence cannot include duplicate values. Such constraint does not hold for columns "Last_Name" and "First_Name". So, if the table already contains the following rows:

SID	Last_Name	First_Name
1	Johnson	Stella
	James	Gina
3	Aaron	Ralph

Executing the following SQL statement,

INSERT INTO Customer values ('3','Lee','Grace');

will result in an error because '3' already exists in the SID column, thus trying to insert another row with that value violates the UNIQUE constraint.

Please note that a column that is specified as a primary key must also be unique. At the same time, a column that's unique may or may not be a primary key. In addition, multiple UNIQUE constraints can be defined on a table.

4) Check constraint

a)The CHECK constraint ensures that all values in a column satisfy certain conditions. Once defined, the database will only insert a new row or update an existing row if the new value satisfies the CHECK constraint. The CHECK constraint is used to ensure data quality

For example, in the following CREATE TABLE statement,

CREATE TABLE Customer
(SID integer CHECK (SID > 0),
Last_Name varchar (30),
First_Name varchar(30));

Column "SID" has a constraint -- its value must only include integers greater than 0. So, attempting to execute the following statement,

INSERT INTO Customer values ('-3','Gonzales','Lynn');

will result in an error because the values for SID must be greater than 0.

b)Add check and default constraint to the existing table

5).i)Setting primary key at the time of creating the table

Syntax: CREATE TABLE Customer
(SID integer PRIMARY KEY,
Last_Name varchar(30),
First_Name varchar(30));

Ex:create table book(book_id int,bname char(10),price money,primary key (book_id))

or

ex:create table author(author_id int primary key,aname char(10))

ii)Setting primary key after creating the table

Note: Before using the ALTER TABLE command to add a primary key, you'll need to make sure that the field is defined as 'NOT NULL' -- in other words, NULL cannot be an accepted value for that field.

So, first set the field as not null as below ,then set the primary key

create table emp1(emp_id int not null,ename char(10),salary money);

 alter table emp1 add primary key (emp_id)

6. SQL Foreign Key

A foreign key is a field (or fields) that points to the primary key of another table. The purpose of the foreign key is to ensure referential integrity of the data. In other words, only values that are supposed to appear in the database are permitted.

For example, say we have two tables, a CUSTOMER table that includes all customer data, and an ORDERS table that includes all customer orders. The constraint here is that all orders must be associated with a customer that is already in the CUSTOMER table. In this case, we will place a foreign key on the ORDERS table and have it relate to the primary key of the CUSTOMER table. This way, we can ensure that all orders in the ORDERS table are related to a customer in the CUSTOMER table. In other words, the ORDERS table cannot contain information on a customer that is not in the CUSTOMER table.

The structure of these two tables will be as follows:

Table CUSTOMER

column name	characteristic
SID	Primary Key
Last_Name
First_Name

Table ORDERS

column name	characteristic
Order_ID	Primary Key
Order_Date
Customer_SID	Foreign Key
Amount

In the above example, the Customer_SID column in the ORDERS table is a foreign key pointing to the SID column in the CUSTOMER table.

Below we show examples of how to specify the foreign key when creating the ORDERS table:

MySQL:

CREATE TABLE ORDERS
(Order_ID integer,
Order_Date date,
Customer_SID integer,
Amount double,
Primary Key (Order_ID),
Foreign Key (Customer_SID) references CUSTOMER(SID));

SQL Server:

CREATE TABLE ORDERS
(Order_ID integer primary key,
Order_Date datetime,
Customer_SID integer references CUSTOMER(SID),
Amount double);

Below are examples for specifying a foreign key by altering a table. This assumes that the ORDERS table has been created, and the foreign key has not yet been put in:

SQL Server:

ALTER TABLE ORDERS
ADD FOREIGN KEY (customer_sid) REFERENCES CUSTOMER(SID);

a)

25.To rollback all transactions:

Begin transaction

Create table emp(empid char(12),ename char(14))

Insert into emp values(‘eoo1’,’mona’)

Insert into emp values(‘eoo2’,’riya’)

Rollback transaction

It will undo the  table content again by rolling back all the transaction

26  To rollback few transactions

Begin transaction

Create table emp(empid char(12),ename char(14))

Insert into emp values(‘eoo1’,’mona’)

Insert into emp values(‘eoo2’,’riya’)

Insert into emp values(‘eoo3’,’roma’)

Save transaction A

Insert into emp values(‘eoo4’,’mina’)

Rollback transaction A

Commit transaction

It will rolling back one transaction

27. To change the length size of the field

alter table emp alter column ename char(10)

28 String functions

1.REVERSE : Returns reverse of a input string.

Syntax: REVERSE(string)

SELECT REVERSE('STRING FUNCTION')->NOITCNUF GNIRTS

2. REPLICATE : Repeats a input string for a specified number of times.

Syntax: REPLICATE (string, integer)

SELECT REPLICATE('FUNCTION', 3)->FUNCTIONFUNCTIONFUNCTION

3PATINDEX : PATINDEX function works very similar to CHARINDEX function.PATINDEX function returns the starting position of the first occurrence of a pattern in a specified string, or zeros if the pttern is not found.

Using PATINDEX function you can search pattern in given string using Wildcard characters(%).The % character must come before and after pattern.

Syntax: PATINDEX('%pattern%',string)

SELECT PATINDEX('%SQL%','Useful SQL String Function')->8

SELECT PATINDEX('Useful%','Useful SQL String Function')->1

SELECT PATINDEX('%Function','Useful SQL String Function')->19

If pattern is not found within given string,PATINDEX returns 0.

4LEFT : Returns left part of a string with the specified number of characters counting from left.LEFT function is used to retrieve portions of the string.

Syntax: LEFT(string,integer)

SELECT LEFT('STRING FUNCTION', 6)->STRING

5.RIGHT : Returns right part of a string with the specified number of characters counting from right.RIGHT function is used to retrieve portions of the string.

Syntax: RIGHT(string,integer)

SELECT RIGHT('STRING FUNCTION', 8)->FUNCTION

6.LTRIM : Returns a string after removing leading blanks on Left side.(Remove left side space or blanks)

Syntax: LTRIM(string)

SELECT LTRIM('   STRING FUNCTION')->STRING FUNCTION

7.RTRIM : Returns a string after removing leading blanks on Right side.(Remove right side space or blanks)

Syntax: RTRIM( string )

SELECT RTRIM('STRING FUNCTION   ')->STRING FUNCTION

8.LOWER : Convert character strings data into lowercase.

Syntax: LOWER(string)

SELECT LOWER('STRING FUNCTION')->string function

9.UPPER : Convert character strings data into Uppercase.

Syntax: UPPER(string)

SELECT UPPER('string function')->STRING FUNCTION

Setting the forein key syntex:

1.create table order2 (o_id char(10) primary key,oname char(10),c_id char(10),foreign key(c_id) references customer(c_id))

or 

create table order1 (o_id char(10) primary key,oname char(10),c_id char(10) references customer(c_id))

setting the foreign key afterwards

ALTER TABLE ORDERS
ADD FOREIGN KEY (customer_sid) REFERENCES CUSTOMER(SID);

Setting the primary  key after wards syntex:

First create not null field then set the primary key

create table customer2 (c_id char(10) not null,cname char(10),desig char(10))

alter table customer2 add primary key (c_id)

setting the check constraint

create table customer2 (c_id int check (c_id>1000),cname char(10),desig char(10),salary money)

setting the unique constraint

create table customer2 (c_id int ,cname char(10) unique,desig char(10),salary money)

Setting the forein key syntex:

1.create table order2 (o_id char(10) primary key,oname char(10),c_id char(10),foreign key(c_id) references customer(c_id))

or 

create table order1 (o_id char(10) primary key,oname char(10),c_id char(10) references customer(c_id))

setting the foreign key afterwards

ALTER TABLE ORDERS
ADD FOREIGN KEY (customer_sid) REFERENCES CUSTOMER(SID);

Or

alter table order1 add constraint ccc foreign key(c_id) references customer(c_id)

Setting the primary  key after wards syntex:

First create not null field then set the primary key

create table customer2 (c_id char(10) not null,cname char(10),desig char(10))

alter table customer2 add primary key (c_id)

or

alter table customer1 add  constraint vvv primary key(c_id)

remove the  primary key

alter table customer1 drop constraint vvv

setting the check constraint

create table customer2 (c_id int check (c_id>1000),cname char(10),desig char(10),salary money)

setting the unique constraint

create table customer2 (c_id int ,cname char(10) unique,desig char(10),salary money)

setting the unique constraint afterwaords

alter table order1 add constraint ddd unique (oname)

setting the check constraint afterwards

alter table order1 add constraint eee check (o_id>3)

To  remove the  constraints (foreign key)

alter table  order1 drop constraint ddd

if u r not setting the name to a constraint ,then you have to copy the name of the system  generated name  from the table structure and put in the alter table statement for removing the constraint.

SQL LIKE

FirstName	LastName	Email	DOB	Phone
John	Smith	John.Smith@yahoo.com	2/4/1968	626 222-2222
Steven	Goldfish	goldfish@fishhere.net	4/4/1974	323 455-4545
Paula	Brown	pb@herowndomain.org	5/24/1978	416 323-3232
James	Smith	jim@supergig.co.uk	20/10/1980	416 323-8888

The SQL LIKE clause is very useful when you want to specify a search condition within your SQL WHERE clause, based on a part of a column contents. For example if you want to select all customers having FirstName starting with 'J' you need to use the following SQL statement:

SELECT * FROM Customers WHERE FirstName LIKE 'J%'

Here is the result of the SQL statement above:

FirstName	LastName	Email	DOB	Phone
John	Smith	John.Smith@yahoo.com	2/4/1968	626 222-2222
James	Smith	jim@supergig.co.uk	20/10/1980	416 323-8888

If you want to select all Customers with phone numbers starting with '416' you will use this SQL expression:

SELECT * FROM Customers WHERE Phone LIKE '416%'

The '%' is a so called wildcard character and represents any string in our pattern.

You can put the wildcard anywhere in the string following the SQL LIKE clause and you can put as many wildcards as you like too.

Note that different databases use different characters as wildcard characters, for example '%' is a wildcard character for MS SQL Server representing any string, and '*' is the corresponding wildcard character used in MS Access.

Another wildcard character is '_' representing any single character.

The '[]' specifies a range of characters. Have a look at the following SQL statement:

SELECT * FROM Customers WHERE Phone LIKE '[4-6]_6%'

This SQL expression will return all customers satisfying the following conditions:

• The Phone column starts with a digit between 4 and 6 ([4-6])
• Second character in the Phone column can be anything (_)
• The third character in the Phone column is 6 (6)
• The remainder of the Phone column can be any character string (%)

Here is the result of this SQL expression:

FirstName	LastName	Email	DOB	Phone
John	Smith	John.Smith@yahoo.com	2/4/1968	626 222-2222
Paula	Brown	pb@herowndomain.org	5/24/1978	416 323-3232
James	Smith	jim@supergig.co.uk	20/10/1980	416 323-8888

Date functions

1.dateadd() function

(a) SELECT DATEADD(mm,5,getdate()) AS NewDate

o/p

(b)to a table

SELECT DATEADD(mm,5,dob) AS NewDate

FROM emp

o/p

2. DATEDIFF() Function

Definition and Usage

The DATEDIFF() function returns the time between two dates.

Syntax

DATEDIFF ( datepart , startdate , enddate )

Example:

Arguments

datepart

Is the parameter that specifies on which part of the date to calculate the difference. The table lists dateparts and abbreviations recognized by Microsoft® SQL Server™.

Datepart	Abbreviations
Year	yy, yyyy
quarter	qq, q
Month	mm, m
dayofyear	dy, y
Day	dd, d
Week	wk, ww
Hour	hh
minute	mi, n
second	ss, s
millisecond	ms

Example

The following SELECT statement:

1.

SELECT DATEDIFF(dd,'2008-11-29','2008-11-30') AS DiffDate

will result in this:

DiffDate

1

2. SELECT DATEDIFF(mm,'2008-11-29',getdate()) AS DiffDate

2.to a table

SELECT DATEDIFF(yy,'12/4/2008',dom) AS DiffDate from  product1

o/p

4. SELECT DAteNAME(m,'2008-12-15') as "monthname"

5. SELECT DAtepart(d,'2008-12-15') as "monthname"

6. SELECT DAteNAME(w,'2008-12-15') as "dayname"

3. getdate function

SELECT GETDATE() as today;

o/p

4. SQL Year():

SELECT YEAR(GETDATE()) as "Year";

SELECT YEAR('8/14/04') as "Year";

SELECT DAtepart(yy,'2008-12-15') as "year"

o/p

5. SQL Month():

SELECT MONTH(GETDATE()) as "Month";

SELECT MONTH('8/14/04') as "Month";

o/p

6. SQL Day():

SELECT DAY(GETDATE()) as "Day";

SELECT DAY('8/14/04') as "Day";

SELECT DAtepart(dd,'2008-12-15') as "day"

SELECT DAtepart(w,'2008-12-15') as "week"

o/p

SQL SELECT INTO

The SQL SELECT INTO statement is used to select data from a SQL database table and to insert it to a different table at the same time.

The general SQL SELECT INTO syntax looks like this:

SELECT Column1, Column2, Column3, INTO Table2 FROM Table1

The list of column names after the SQL SELECT command determines which columns will be copied, and the table name after the SQL INTO keyword specifies to which table to copy those rows.

If we want to make an exact copy of the data in our Customers table, we need the following SQL SELECT INTO statement:

SELECT * INTO Customers_copy FROM Customers

select * into product4 from product1

create table cust(cust_id varchar(4),name1 varchar(50),address varchar(100))

create table ord(o_id varchar(4),cust_id varchar(4),item varchar(10),amount money)

insert into cust values('4','gsdhgj','hjdkhjksd')

insert into ord values('2','2','ghjastyrq',7890)

select * from cust

select * from ord

create trigger t11 on cust

            for delete

as

if (select count(*) from ord join deleted on ord.cust_id=deleted.cust_id)>0

begin

            rollback transaction

            print 'cannot delete'

           

end

delete from cust where cust_id='1'

select * from customer

select datename(weekday,getdate())

select week(getdate())

create trigger t11_chkday

            on customer for update

as

if datename(weekday,getdate()) in ('Thursday')

begin

rollback transaction

print 'can not transact'

end

update customer set cname='llll' where  cname='amar'

create trigger t11_2 on cust for delete

as

begin

delete from ord from ord,deleted where ord.cust_id=deleted.cust_id

print 'done'

end

Change Column Size of a Table

ALTER TABLE tablename ALTER COLUMN columnname datatype

e.g

ALTER TABLE Movie_Master ALTER COLUMN Production varchar(500)

WCF

Developer who has worked with ASP.NET Web Services (ASMX) and WCF always feels that using predecessor was much more easier. It just because WCF configuration is much more complex as compare to ASP.NET Web Service.
WHY ??????
With ASMX, you were able to define a [WebMethod] operation and the runtime automatically provided a default configuration for the underlying communications. When moving to WCF 3.x, on the other hand, developers have to know enough about the various WCF configuration options to define at least one endpoint.
In an effort to make the overall WCF experience just as easy as ASMX, WCF 4 comes with a new “default configuration” model that completely removes the need for any WCF configuration. If you don’t provide any WCF configuration for a particular service, the WCF 4 runtime automatically configures your service with some standard endpoints and default binding/behavior configurations. This makes it much easier to get a WCF service up and running, especially for those who aren’t familiar with the various WCF configuration options.
Let’s discuss some of the standard configuration options that WCF 4 support :
1) Default Endpoints
2) Default Protocol Mapping
3) Default Binding Configurations
4) Default Behavior Configurations
1) Default Endpoints
With WCF 3.X, if you try to host a service without configured endpoints, ServiceHost instance will throw an exception informing you that you need to configure at least one endpoint. With WCF 4, this is no longer the case because the runtime automatically adds one or more ‘default endpoints’ for you.
Now question comes in mind, How this is done by WCF 4?
Answer to Question is like : When Host application calls Open method on ServiceHost instance, it build internal service description from the application configuration file. Than it check the count of configured endpoints. if it is still zero than it will call “AddDefaultEndpoints” public method and method will adds one default endpoint per base address for each service contract implemented by the service.
Clear or Confused ??
Lets take one example to be more clear on it.
If the service implements two service contracts and you configure the host with a single base address, AddDefaultEndpoints will configure the service with two default endpoints (one for each service contract). However, if the service implements two service contracts and the host is configured with two base addresses (one for HTTP and one for TCP), AddDefaultEndpoints will configure the service with four default endpoints.
I Hope now its clear….if still not…please go thru the link provided for more details on it.
-> New Features in WCF 4 that Will Instantly Make You More Productive
(http://www.code-magazine.com/Article.aspx?quickid=1006061)
2) Default Protocol Mapping
In .Net 4.0 framework, default protocol mapping between transport protocol schemes and the built in WCF bindings are as follows :

<protocolMapping>

<add scheme="http" binding="basicHttpBinding" bindingConfiguration="" />

<add scheme="net.tcp" binding="netTcpBinding" bindingConfiguration=""/>

<add scheme="net.pipe" binding="netNamedPipeBinding" bindingConfiguration=""/>

<add scheme="net.msmq" binding="netMsmqBinding" bindingConfiguration=""/>

</protocolMapping>

You can override these mappings at machine level by adding this section to machine.config file and modify the bindings as per your needs.
If you want to override this mappings at application level than you can override the above section in application/web configfile.
3) Default Binding Configurations
In WCF 3.x , binding can be done like this :

<configuration>

<system.serviceModel>

<bindings>

<basicHttpBinding>

<binding name="BasicBindingMtom" messageEncoding="Mtom"/>

</basicHttpBinding>

</bindings>

<services>

<service name="HelloService">

<endpoint address="mtom" binding="basicHttpBinding"

bindingConfiguration="BasicBindingMtom"

contract="IHello"/>

</service>

</services>

</system.serviceModel>

</configuration>

Here “BasicBindingMtom” binding configuration overrides the defaults for BasicHttpBinding by changing the message encoding to “Mtom”. However this binding will effect only when you apply it to a specific endpoint thru “bindingConfiguration” attribute.
With WCF 4, binding can be done like this :

<basicHttpBinding>

<binding messageEncoding="Mtom"/>

</basicHttpBinding>

No name attribute required. This feature gives you a simple mechanism to define a standard set of binding defaults that you can use across all your services without imposing any complexities of binding configurations.
4) Default Behavior Configurations
With WCF 4, it is possible to define default behavior configurations for services and endpoints.

<behaviors>

<serviceBehaviors>

<behavior>

<serviceMetadata httpGetEnabled="true"/>

</behavior>

</serviceBehaviors>

</behaviors>

Above configuration turns on the metadata for any service that doesn’t come with explicit behavior configuration.
In WCF 4, Behavior configuration also support inheritance model. It means that if application defines a behavior using same name as one already defined in machine.config, the application specific behavior configuration will get merged with machine configuration.
With these new additions in the WCF 4, it will be easier for developers to configure the services. I am sure that many developers will feel relaxed by having these new features in WCF and also start using it. Thats all from my side for this post.

Implementing the Singleton Pattern in C#

The singleton pattern is one of the best-known patterns in software engineering. Essentially, a singleton is a class which only allows a single instance of itself to be created, and usually gives simple access to that instance. Most commonly, singletons don't allow any parameters to be specified when creating the instance - as otherwise a second request for an instance but with a different parameter could be problematic! (If the same instance should be accessed for all requests with the same parameter, the factory pattern is more appropriate.) This article deals only with the situation where no parameters are required. Typically a requirement of singletons is that they are created lazily - i.e. that the instance isn't created until it is first needed.

There are various different ways of implementing the singleton pattern in C#. I shall present them here in reverse order of elegance, starting with the most commonly seen, which is not thread-safe, and working up to a fully lazily-loaded, thread-safe, simple and highly performant version.

All these implementations share four common characteristics, however:

• A single constructor, which is private and parameterless. This prevents other classes from instantiating it (which would be a violation of the pattern). Note that it also prevents subclassing - if a singleton can be subclassed once, it can be subclassed twice, and if each of those subclasses can create an instance, the pattern is violated. The factory pattern can be used if you need a single instance of a base type, but the exact type isn't known until runtime.
• The class is sealed. This is unnecessary, strictly speaking, due to the above point, but may help the JIT to optimise things more.
• A static variable which holds a reference to the single created instance, if any.
• A public static means of getting the reference to the single created instance, creating one if necessary.

Note that all of these implementations also use a public static property Instance as the means of accessing the instance. In all cases, the property could easily be converted to a method, with no impact on thread-safety or performance.

First version - not thread-safe

// Bad code! Do not use!
public sealed class Singleton
{
    private static Singleton instance=null;

    private Singleton()
    {
    }

    public static Singleton Instance
    {
        get
        {
            if (instance==null)
            {
                instance = new Singleton();
            }
            return instance;
        }
    }
}

As hinted at before, the above is not thread-safe. Two different threads could both have evaluated the test if (instance==null) and found it to be true, then both create instances, which violates the singleton pattern. Note that in fact the instance may already have been created before the expression is evaluated, but the memory model doesn't guarantee that the new value of instance will be seen by other threads unless suitable memory barriers have been passed.

Second version - simple thread-safety

public sealed class Singleton
{
    private static Singleton instance = null;
    private static readonly object padlock = new object();

    Singleton()
    {
    }

    public static Singleton Instance
    {
        get
        {
            lock (padlock)
            {
                if (instance == null)
                {
                    instance = new Singleton();
                }
                return instance;
            }
        }
    }
}

This implementation is thread-safe. The thread takes out a lock on a shared object, and then checks whether or not the instance has been created before creating the instance. This takes care of the memory barrier issue (as locking makes sure that all reads occur logically after the lock acquire, and unlocking makes sure that all writes occur logically before the lock release) and ensures that only one thread will create an instance (as only one thread can be in that part of the code at a time - by the time the second thread enters it,the first thread will have created the instance, so the expression will evaluate to false). Unfortunately, performance suffers as a lock is acquired every time the instance is requested.

Note that instead of locking on typeof(Singleton) as some versions of this implementation do, I lock on the value of a static variable which is private to the class. Locking on objects which other classes can access and lock on (such as the type) risks performance issues and even deadlocks. This is a general style preference of mine - wherever possible, only lock on objects specifically created for the purpose of locking, or which document that they are to be locked on for specific purposes (e.g. for waiting/pulsing a queue). Usually such objects should be private to the class they are used in. This helps to make writing thread-safe applications significantly easier.

Third version - attempted thread-safety using double-check locking

// Bad code! Do not use!
public sealed class Singleton
{
    private static Singleton instance = null;
    private static readonly object padlock = new object();

    Singleton()
    {
    }

    public static Singleton Instance
    {
        get
        {
            if (instance == null)
            {
                lock (padlock)
                {
                    if (instance == null)
                    {
                        instance = new Singleton();
                    }
                }
            }
            return instance;
        }
    }
}

This implementation attempts to be thread-safe without the necessity of taking out a lock every time. Unfortunately, there are four downsides to the pattern:

• It doesn't work in Java. This may seem an odd thing to comment on, but it's worth knowing if you ever need the singleton pattern in Java, and C# programmers may well also be Java programmers. The Java memory model doesn't ensure that the constructor completes before the reference to the new object is assigned to instance. The Java memory model underwent a reworking for version 1.5, but double-check locking is still broken after this without a volatile variable (as in C#).
• Without any memory barriers, it's broken in the ECMA CLI specification too. It's possible that under the .NET 2.0 memory model (which is stronger than the ECMA spec) it's safe, but I'd rather not rely on those stronger semantics, especially if there's any doubt as to the safety. Making the instance variable volatile can make it work, as would explicit memory barrier calls, although in the latter case even experts can't agree exactly which barriers are required. I tend to try to avoid situations where experts don't agree what's right and what's wrong!
• It's easy to get wrong. The pattern needs to be pretty much exactly as above - any significant changes are likely to impact either performance or correctness.
• It still doesn't perform as well as the later implementations.

Fourth version - not quite as lazy, but thread-safe without using locks

public sealed class Singleton
{
    private static readonly Singleton instance = new Singleton();

    // Explicit static constructor to tell C# compiler
    // not to mark type as beforefieldinit
    static Singleton()
    {
    }

    private Singleton()
    {
    }

    public static Singleton Instance
    {
        get
        {
            return instance;
        }
    }
}

As you can see, this is really is extremely simple - but why is it thread-safe and how lazy is it? Well, static constructors in C# are specified to execute only when an instance of the class is created or a static member is referenced, and to execute only once per AppDomain. Given that this check for the type being newly constructed needs to be executed whatever else happens, it will be faster than adding extra checking as in the previous examples. There are a couple of wrinkles, however:

• It's not as lazy as the other implementations. In particular, if you have static members other thanInstance, the first reference to those members will involve creating the instance. This is corrected in the next implementation.
• There are complications if one static constructor invokes another which invokes the first again. Look in the .NET specifications (currently section 9.5.3 of partition II) for more details about the exact nature of type initializers - they're unlikely to bite you, but it's worth being aware of the consequences of static constructors which refer to each other in a cycle.
• The laziness of type initializers is only guaranteed by .NET when the type isn't marked with a special flag called beforefieldinit. Unfortunately, the C# compiler (as provided in the .NET 1.1 runtime, at least) marks all types which don't have a static constructor (i.e. a block which looks like a constructor but is marked static) as beforefieldinit. I now have an article with more details about this issue. Also note that it affects performance, as discussed near the bottom of the page.

One shortcut you can take with this implementation (and only this one) is to just make instance a public static readonly variable, and get rid of the property entirely. This makes the basic skeleton code absolutely tiny! Many people, however, prefer to have a property in case further action is needed in future, and JIT inlining is likely to make the performance identical. (Note that the static constructor itself is still required if you require laziness.)

Fifth version - fully lazy instantiation

public sealed class Singleton
{
    private Singleton()
    {
    }

    public static Singleton Instance { get { return Nested.instance; } }
       
    private class Nested
    {
        // Explicit static constructor to tell C# compiler
        // not to mark type as beforefieldinit
        static Nested()
        {
        }

        internal static readonly Singleton instance = new Singleton();
    }
}

Here, instantiation is triggered by the first reference to the static member of the nested class, which only occurs in Instance. This means the implementation is fully lazy, but has all the performance benefits of the previous ones. Note that although nested classes have access to the enclosing class's private members, the reverse is not true, hence the need for instance to be internal here. That doesn't raise any other problems, though, as the class itself is private. The code is a bit more complicated in order to make the instantiation lazy, however.

Sixth version - using .NET 4's Lazy<T> type

If you're using .NET 4 (or higher), you can use the System.Lazy<T> type to make the laziness really simple. All you need to do is pass a delegate to the constructor which calls the Singleton constructor - which is done most easily with a lambda expression.

public sealed class Singleton
{
    private static readonly Lazy<Singleton> lazy =
        new Lazy<Singleton>(() => new Singleton());
   
    public static Singleton Instance { get { return lazy.Value; } }

    private Singleton()
    {
    }
}

Performance vs laziness

In many cases, you won't actually require full laziness - unless your class initialization does something particularly time-consuming, or has some side-effect elsewhere, it's probably fine to leave out the explicit static constructor shown above. This can increase performance as it allows the JIT compiler to make a single check (for instance at the start of a method) to ensure that the type has been initialized, and then assume it from then on. If your singleton instance is referenced within a relatively tight loop, this can make a (relatively) significant performance difference. You should decide whether or not fully lazy instantiation is required, and document this decision appropriately within the class. (See below for more on performance, however.)

Exceptions

Sometimes, you need to do work in a singleton constructor which may throw an exception, but might not be fatal to the whole application. Potentially, your application may be able to fix the problem and want to try again. Using type initializers to construct the singleton becomes problematic at this stage. Different runtimes handle this case differently, but I don't know of any which do the desired thing (running the type initializer again), and even if one did, your code would be broken on other runtimes. To avoid these problems, I'd suggest using the second pattern listed on the page - just use a simple lock, and go through the check each time, building the instance in the method/property if it hasn't already been successfully built.

I would be very interested to see a real world application where the difference between using simple locking and using one of the faster solutions actually made a significant performance difference.

Conclusion (modified slightly on January 7th 2006; updated Feb 12th 2011)

There are various different ways of implementing the singleton pattern in C#. A reader has written to me detailing a way he has encapsulated the synchronization aspect, which while I acknowledge may be useful in a few very particular situations (specifically where you want very high performance, and the ability to determine whether or not the singleton has been created, and full laziness regardless of other static members being called). I don't personally see that situation coming up often enough to merit going further with on this page, but please mail me if you're in that situation.

My personal preference is for solution 4: the only time I would normally go away from it is if I needed to be able to call other static methods without triggering initialization, or if I needed to know whether or not the singleton has already been instantiated. I don't remember the last time I was in that situation, assuming I even have. In that case, I'd probably go for solution 2, which is still nice and easy to get right.

Solution 5 is elegant, but trickier than 2 or 4, and as I said above, the benefits it provides seem to only be rarely useful. Solution 6 is a simpler way to achieve laziness, if you're using .NET 4. It also has the advantage that it'sobviously lazy. I currently tend to still use solution 4, simply through habit - but if I were working with inexperienced developers I'd quite possibly go for solution 6 to start with as an easy and universally applicable pattern.

It's simple and performs well. It also allows you to check whether or not the instance has been created yet with the IsValueCreated property, if you need that.