How to add new instance in SQL Server

  1. All program -> SQL Server Installation center
    In windows 8 -> Search -> SQL Server Installation Cepter
  2. From left menu select “Installation->New Sql server standalone installation or add features”
  3. Browse CD or Physical directory of SQL Server
  4. Auto run “Setup support rules” click ok
  5. Product update -> click next
  6. Install setup files -> click next
  7. Setup support rules and click next
  8. Select Installation type -> select “Perform new installation of SQL Server 2012” and click next1
  9. Enter product key and click next
  10. I accept the licence key
  11. Setup rules -> select “All features with default” -> Click next
  12. Feature selection -> Click Next
  13. Instance Configuration -> Named instance -> Type SQLExpress
    And InstanceId -> Type SQLEXPRESS and click next2
  14. Server configuration -> click next
  15. Database engine configuration -> Select windows authentication mode -> Click add current user and click next3
  16. Click next …. Next … next like as usual sql server installation.
  17. After complete installation. Try to enter using new created instance

4

SQL Join with Microsoft SQL Server

What is SQL Joins?

A SQL join clause is used to combine records from two or more tables in a database based on common field between them. It creates a set of rows in a temporary table.

There are different types of joins available in SQL:
• INNER JOIN
• LEFT JOIN
• RIGHT JOIN
• FULL JOIN
• SELF JOIN
• CARTESIAN JOIN/CROSS JOIN

To understand JOINs, let’s consider the following two tables Customers and Orders is as follows

Customers

01-Customer02

Orders

02-Orders02

INNER JOIN
INNER JOIN returns match data between tables. It returns rows when there is at least one match in both the tables.

03-InnerJoin

Syntax:

SELECT Cust.Id, Cust.Name, ord.Amount, ord.[Date]
FROM Customers Cust
INNER JOIN Orders ord
ON Cust.Id = ord.CustomerId;

Output:

Id          Name                                               Amount                                  Date

———– ————————————————– ————————————— ———-

2           Hamidur Rahman                          4000.00                                 2014-01-10

2           Hamidur Rahman                          5000.00                                 2014-01-10

3           Jamilur Rahman                            3000.00                                 2014-02-13

4           Hasan Sarwar                                  600.00                                  2014-03-22

LEFT JOIN/LEFT OUTER JOIN
Left Outer Join returns all the rows from the left table in conjunction with the matching rows from the right table. If there are no columns matching in the right table, it returns NULL values.

04-Left join

Syntax:

SELECT Cust.Id, Cust.Name, ord.Amount, ord.[Date]
FROM Customers Cust
LEFT JOIN Orders ord
ON Cust.Id = ord.CustomerId;

Output:

Id          Name                                               Amount                                  Date

———– ————————————————– ————————————— ———-

1           Mahedee Hasan                               NULL                                    NULL

2           Hamidur Rahman                           4000.00                                 2014-01-10

2           Hamidur Rahman                           5000.00                                 2014-01-10

3           Jamilur Rahman                              3000.00                                 2014-02-13

4           Hasan Sarwar                                  600.00                                  2014-03-22

5           Kamal Hossain                                NULL                                    NULL

6           Imran Khan                                     NULL                                    NULL

7           Abu Sayem                                       NULL                                    NULL

RIGHT JOIN/RIGHT OUTER JOIN
Right outer join returns all the rows from the right table in conjunction with the matching rows from the left table. If there are no columns matching in the left table, it returns NULL values.

05-Right join

Syntax:

SELECT Cust.Id, Cust.Name, ord.Amount, ord.[Date]
FROM Customers Cust
RIGHT JOIN Orders ord
ON Cust.Id = ord.CustomerId;

Output:

Id               Name                                               Amount                                  Date

———– ————————————————– ————————————— ———-

2                 Hamidur Rahman                        4000.00                                 2014-01-10

2                 Hamidur Rahman                        5000.00                                 2014-01-10

3                 Jamilur Rahman                           3000.00                                 2014-02-13

4                 Hasan Sarwar                                600.00                                  2014-03-22

NULL        NULL                                               800.00                                  2014-03-22

FULL JOIN /FULL OUTER JOIN
Full outer join is combines left outer join and right outer join. It returns row from either table when the conditions are met and returns null value when there is no match

06-Full Join

Syntax:

SELECT Cust.Id, Cust.Name, ord.Amount, ord.[Date]
FROM Customers Cust
FULL JOIN Orders ord
ON Cust.Id = ord.CustomerId;

Output:

Id                    Name                                               Amount                                  Date

———– ————————————————– ————————————— ———-

1                      Mahedee Hasan                             NULL                                    NULL

2                     Hamidur Rahman                          4000.00                                 2014-01-10

2                    Hamidur Rahman                           5000.00                                 2014-01-10

3                    Jamilur Rahman                             3000.00                                 2014-02-13

4                   Hasan Sarwar                                   600.00                                  2014-03-22

5                   Kamal Hossain                                 NULL                                    NULL

6                   Imran Khan                                      NULL                                    NULL

7                    Abu Sayem                                       NULL                                    NULL

NULL          NULL                                                800.00                                  2014-03-22

SELF JOIN
The SQL SELF JOIN is used to join a table to itself as if the table were two tables, temporarily renaming at least one table in the SQL statement.

Syntax:

SELECT A.Id, A.Name, B.Name AS Introducer
FROM Customers A, Customers B
WHERE A.IntroducerId = B.Id

Output:

Id          Name                                               Introducer

———– ————————————————– ————————————————–

1           Mahedee Hasan                             Hamidur Rahman

2           Hamidur Rahman                         Mahedee Hasan

3           Jamilur Rahman                           Hamidur Rahman

4           Hasan Sarwar                                Jamilur Rahman

5           Kamal Hossain                              Hasan Sarwar

6           Imran Khan                                   Kamal Hossain

7           Abu Sayem                                     Imran Khan

CARTESIAN JOIN/CROSS JOIN
The SQL cross join produces a result set which is the number of rows in the first table multiplied by the number of rows in the second table, if no WHERE clause uses in the second table

07-crossjoin

Syntax:

SELECT cust.Id, cust.Name, ord.Amount, ord.[Date]
FROM Customers cust, Orders ord

SELECT cust.Id, cust.Name, ord.Amount, ord.[Date]
FROM Customers cust CROSS JOIN Orders ord

Output:

Id          Name                                               Amount                                  Date

———– ————————————————– ————————————— ———-

1           Mahedee Hasan                            4000.00                                 2014-01-10

2           Hamidur Rahman                       4000.00                                 2014-01-10

3           Jamilur Rahman                          4000.00                                 2014-01-10

4           Hasan Sarwar                                4000.00                                 2014-01-10

5           Kamal Hossain                             4000.00                                 2014-01-10

6           Imran Khan                                  4000.00                                 2014-01-10

7           Abu Sayem                                     4000.00                                 2014-01-10

1           Mahedee Hasan                            5000.00                                 2014-01-10

2           Hamidur Rahman                       5000.00                                 2014-01-10

3           Jamilur Rahman                          5000.00                                 2014-01-10

4           Hasan Sarwar                               5000.00                                 2014-01-10

5           Kamal Hossain                             5000.00                                 2014-01-10

6           Imran Khan                                  5000.00                                 2014-01-10

7           Abu Sayem                                    5000.00                                 2014-01-10

1           Mahedee Hasan                            3000.00                                 2014-02-13

2           Hamidur Rahman                       3000.00                                 2014-02-13

3           Jamilur Rahman                          3000.00                                 2014-02-13

4           Hasan Sarwar                               3000.00                                 2014-02-13

5           Kamal Hossain                             3000.00                                 2014-02-13

6           Imran Khan                                  3000.00                                 2014-02-13

7           Abu Sayem                                    3000.00                                 2014-02-13

1           Mahedee Hasan                            600.00                                  2014-03-22

2           Hamidur Rahman                       600.00                                  2014-03-22

3           Jamilur Rahman                          600.00                                  2014-03-22

4           Hasan Sarwar                               600.00                                  2014-03-22

5           Kamal Hossain                             600.00                                  2014-03-22

6           Imran Khan                                  600.00                                  2014-03-22

7           Abu Sayem                                    600.00                                  2014-03-22

1           Mahedee Hasan                            800.00                                  2014-03-22

2           Hamidur Rahman                        800.00                                  2014-03-22

3           Jamilur Rahman                          800.00                                  2014-03-22

4           Hasan Sarwar                               800.00                                  2014-03-22

5           Kamal Hossain                             800.00                                  2014-03-22

6           Imran Khan                                  800.00                                  2014-03-22

7           Abu Sayem                                    800.00                                  2014-03-22

Additional Information Related JOIN

Left Outer Join Where Null
Left outer join where null returns all the rows from the left table in conjunction without the matching rows from the right table.

08-left Join and Null

Syntax

SELECT Cust.Id, Cust.Name, ord.Amount, ord.[Date]
FROM Customers Cust
LEFT JOIN Orders ord
ON Cust.Id = ord.CustomerId
where ord.CustomerId is null

Output

Id          Name                                               Amount                                  Date

———– ————————————————– ————————————— ———-

1           Mahedee Hasan                              NULL                                    NULL

5           Kamal Hossain                               NULL                                    NULL

6           Imran Khan                                    NULL                                    NULL

7           Abu Sayem                                      NULL                                    NULL

Right Outer Join Where Null
Right outer join where null returns all the rows from the right table in conjunction without the matching rows from the left table.

08-Right Join and Null

Syntax:

SELECT Cust.Id, Cust.Name, ord.Amount, ord.[Date]
FROM Customers Cust
RIGHT JOIN Orders ord
ON Cust.Id = ord.CustomerId
where Cust.Id is null

Output

Id              Name          Amount        Date
———– ————————————————– ————————————— ———-
NULL       NULL          800.00        2014-03-22

Full Outer Join Where Null
Full outer join is combines left outer join and right outer join. It returns row from either table when the conditions are met and returns null value when there is no match

10-full Join and Null

Syntax

SELECT Cust.Id, Cust.Name, ord.Amount, ord.[Date]
FROM Customers Cust
FULL OUTER JOIN Orders ord
ON Cust.Id = ord.CustomerId
WHERE Cust.Id is null
OR ord.CustomerId is null

Output

Id          Name                                                  Amount                                  Date

———– ————————————————– ————————————— ———-

1           Mahedee Hasan                                  NULL                                    NULL

5           Kamal Hossain                                   NULL                                    NULL

6           Imran Khan                                        NULL                                    NULL

7           Abu Sayem                                          NULL                                    NULL

NULL        NULL                                            800.00                                  2014-03-22

Decorator Design Pattern with C#

You who work on design pattern must familiar with Gang of Four (GoF). Design Patterns: Elements of Reusable Object-Oriented Software is a Software Engineering book. The authors of this book are Erich Gamma, Richard Helm, Ralph Johnson and John Vlissides. The authors of this book are often refers to as Gang of Four (GoF). It’s a parody relating to Mao’s Gang of Four. This book describes the recurring solution of common problem in software design. The Gang of Four (GoF) patterns are generally considered the foundation for all other patterns. They are categorized in three groups: Creational, Structural, and Behavioral. Factory Pattern, Abstract Factory Pattern, Singleton Pattern, Builder e.t.c are creational design pattern. Decorator, Adapter, Bridge, Façade, Proxy, Composite e.t.c are structural design pattern. Command, interpreter, strategy, iterator e.t.c are behavioral design pattern.

Decorator Design Pattern
Decorator Design Pattern is a structural design pattern. It is also known as wrapper. It is used to add additional functionality to a particular object during run time without affecting other objects. Responsibility can be adding or removing at run time. Critics says that it uses lot of little object of similar type.

Let’s consider a scenario in which I am going to implement Decorated Design Pattern. We, Bangladeshi are very much cricket loving. A cricket equipment shop declared some package on the occasion of Cricket World Cup 2011. The base package is with a bat and a ball and its cost is 1500 Taka (Bangladeshi Currency). The smart package is with a bat, a ball and 6 stamps i.e base package plus 6 stamps and its cost is 1500 + 600 = 2100 Taka. The special package is with a bat, a ball, 6 stamps and a pair of gloves i.e smart package plus a pair of gloves. It’s cost is 2100 + 500 = 2600 Taka. It looks like the following figure. Actually, the top one wrapped the inner packages.

BlockDiagramDP

Before going to implement the scenario by decorated design pattern, I would like to show you the class diagram. The class diagram is given below.

DP_Class Diagram

Implementation: Let’s implement decorated design pattern by C#.

Step 1: Create interface ICricketInstrument for all package of cricket instrument.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace DecoratorPattern
{
///
/// Interface for Cricket Instrument
/// Developed by: Mahedee
///

public interface ICricketInstrument
{
double Cost { get; }
string Insturments { get; }
}
}

Step 2: Create a base type for Concrete Cricket Instrument Package and Instrument Package Option

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace DecoratorPattern
{
///
/// Base type for concrete cricket instrument package and decorator(option).
/// Developed by: Mahedee
///

public abstract class CricketInstrument : ICricketInstrument
{
private double cost = 00;
private string instrument = "Cricket Instruments: ";

#region ICricketInstrument Members

public virtual double Cost
{
get { return cost; }
}

public virtual string Insturments
{
get { return instrument; }
}

#endregion
}
}

Step 3: Create a base type for Concrete Cricket Instrument Package Option. It is actually decorator – to decorate Instrument package.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;


namespace DecoratorPattern
{
    /// 
    /// Decorator for concrete package option
    /// 
    public abstract class PackageOption : CricketInstrument
    {
        double cost = 00;
        string instruments = "Abstract Package Option";

        public override double Cost
        {
            get { return cost; }
        }


        public override string Insturments
        {
            get { return instruments; }
        }

    }
}

Step 4: Create base package of Cricket Instrument.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace DecoratorPattern
{
///
/// Base Cricket Insturment Package
///

public class BasePackage : CricketInstrument
{
double cost = 1500;
string instruments = "Ball and Bat";

public override double Cost
{
get { return cost; }
}

public override string Insturments
{
get { return base.Insturments + instruments; }
}

}
}

Step 5: Decorate Smart package for Cricket Instrument

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace DecoratorPattern
{
///
/// Smart Package = Base Package (Bat, Ball) + 6 Stamps
/// Developed by: Mahedee
///

public class SmartPackage : PackageOption
{
double cost = 600;
string instruments = "6 Stamps";
CricketInstrument objCricketInstrument;

public SmartPackage(CricketInstrument objPCricketInstrument)
{
objCricketInstrument = objPCricketInstrument;
}

public override double Cost
{
get { return objCricketInstrument.Cost + cost; }
}

public override string Insturments
{
get { return objCricketInstrument.Insturments + ", " + instruments; }
}
}
}

Step 6: Decorate Special Package for Cricket Instrument.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace DecoratorPattern
{
///
/// Special Package = Base Package (Bat, Ball) + 6 Stamps + 1 Pair - Gloves
/// Developed by: Mahedee
///

public class SpecialPackage : PackageOption
{
double cost = 500;
string instruments = "Gloves - 1 Pair";
CricketInstrument objCricketInstrument;

public SpecialPackage(CricketInstrument objPCricketInstrument)
{
objCricketInstrument = objPCricketInstrument;
}

public override double Cost
{
get { return objCricketInstrument.Cost + cost; }
}

public override string Insturments
{
get { return objCricketInstrument.Insturments + ", " + instruments; }
}

}
}

Step 7: Program class to create base package and decorate other package with option.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace DecoratorPattern
{
public class Program
{
static void Main(string[] args)
{
//Base Insturment class

CricketInstrument objCricketInstruments = new BasePackage();
Console.WriteLine("::Base Package::");
Console.WriteLine(objCricketInstruments.Insturments);
Console.WriteLine("Instrument's Cost: " + objCricketInstruments.Cost);

Console.WriteLine();

//Smart Package
objCricketInstruments = new SmartPackage(objCricketInstruments);
Console.WriteLine("::Smart Package::");
Console.WriteLine(objCricketInstruments.Insturments);
Console.WriteLine("Instrument's Cost: " + objCricketInstruments.Cost);

Console.WriteLine();

//Special Package
objCricketInstruments = new SpecialPackage(objCricketInstruments);
Console.WriteLine("::Special Package::");
Console.WriteLine(objCricketInstruments.Insturments);
Console.WriteLine("Instrument's Cost: " + objCricketInstruments.Cost);

Console.ReadLine();

}
}
}

Install and uninstall windows service from command prompt

Suppose you have created a windows service name “YourServiceName.exe”. Now you want to install or uninstall it from command prompt.

  • To Install Service run the commnad:

C:\Windown\Microsoft.Net\framework\v2.0\installUtil.exe  C:\dirctory\YourServeceName.exe     (with location)

  • To Uninstall Service run the command:

C:\Windown\Microsoft.Net\v2.0\installUtil.exe /u C:\dirctory\YourServeceName.exe         (with location)