Documentation Home

Tip

All examples used are valid against the Microsoft Northwind Sample Database You can run the resulting SQL from the examples against Northwind in order to test and play with the SqlObjects library.

SELECT FROM

SELECT ALL

Using the default behavior

var sql = TSQL

   // * is the default
   .SELECT().FROM("Products").Build()

;

Console.WriteLine(sql);

Output:

SELECT
 *
FROM [Products]

Explicitly call the .STAR() method

var sql = TSQL

   .SELECT()
     .STAR()
   .FROM("Products")
   .Build()

;

Console.WriteLine(sql);

Output:

SELECT
 *
FROM [Products]

COLUMN

Specify a column by name

var sql = TSQL

   .SELECT()
     .COLUMN("ProductID")
     .COLUMN("ProductName")
   .FROM("Products")
   .Build()

;

Console.WriteLine(sql);

Output:

SELECT
 [ProductID],
 [ProductName]
FROM [Products]

Specify a column by name with a multipart identifier table alias

var sql = TSQL

   .SELECT()
     .COLUMN("p", "ProductID")
     .COLUMN("p", "ProductName")
   .FROM("dbo", "Products", "p")
   .Build()

;

Console.WriteLine(sql);

Output:

SELECT
 [p].[ProductID],
 [p].[ProductName]
FROM [dbo].[Products] p

Note

When specifying a multipart identifier table alias, the schema must be included in order to disambiguate the string arguments to the method call.

Specify a column by name with a column alias using .AS()

var sql = TSQL

   .SELECT()
     .COLUMN("ProductID").AS("Id")
     .COLUMN("ProductName")
   .FROM("Products")
   .Build()

;

Console.WriteLine(sql);

Output:

SELECT
 [ProductID] AS [Id],
 [ProductName]
FROM [Products]

Specify a column by name with a multipart identifier table alias and column alias with a third argument.

.COLUMN(string multipartIdentifier, string columnName, string alias)

var sql = TSQL

   .SELECT()
     .COLUMN("p", "ProductID", "Id")
     .COLUMN("p", "ProductName")
   .FROM("dbo", "Products", "p")
   .Build()

;

Console.WriteLine(sql);

Output:

SELECT
 [p].[ProductID] AS [Id],
 [p].[ProductName]
FROM [dbo].[Products] p

Note

When specifying a column alias using the arguments, the multipart identifier table alias must be included in order to disambiguate the string arguments to the method call.

COLUMNS

Provide a collection of column names as IEnumerable<string>

using System;
using System.Collections.Generic;
using KnightMoves.SqlObjects;

var columns = new List<string> { "ProductID", "ProductName" };

var sql = TSQL

     .SELECT()
       .COLUMNS(columns)
     .FROM("dbo", "Products", "p")
     .Build()

;

Console.WriteLine(sql);

Output:

SELECT
 [ProductID],
 [ProductName]
FROM [dbo].[Products] p

Provide a collection of column names as IEnumerable<string> and a common multipart identifier

using System;
using System.Collections.Generic;
using KnightMoves.SqlObjects;

var columns = new List<string> { "ProductID", "ProductName" };

var sql = TSQL

     .SELECT()
       .COLUMNS("p", columns)
     .FROM("dbo", "Products", "p")
     .Build()

;

Console.WriteLine(sql);

Output:

SELECT
 [p].[ProductID],
 [p].[ProductName]
FROM [dbo].[Products] p

Provide a collection of TSQLColumn objects

using System;
using System.Collections.Generic;
using KnightMoves.SqlObjects;
using KnightMoves.SqlObjects.SqlCode.TSQL;

var columns = new List<TSQLColumn>
{
    new TSQLColumn { MultiPartIdentifier = "p", ColumnName = "ProductID" },
    new TSQLColumn { MultiPartIdentifier = "p", ColumnName = "ProductName", Alias = "Name" }
};

var sql = TSQL

     .SELECT()
       .COLUMNS(columns)
     .FROM("dbo", "Products", "p")
     .Build()

;

Console.WriteLine(sql);

Output:

SELECT
 [p].[ProductID],
 [p].[ProductName] AS [Name]
FROM [dbo].[Products] p

DISTINCT

Using .DISTINCT() with .COLUMN() Methods

var sql = TSQL

  .SELECT()
   .DISTINCT()
   .COLUMN("CategoryID")
   .COLUMN("Discontinued")
  .FROM("Products")
  .Build()

;

Console.WriteLine(sql);

Output:

SELECT
 DISTINCT
 [CategoryID],
 [Discontinued]
FROM [Products]

Using .DISTINCT() with an IEnumerable<string> Collection

var columns = new List<string> { "CategoryID", "Discontinued" };

var sql = TSQL

   .SELECT()
     .DISTINCT()
     .COLUMNS(columns)
   .FROM("Products")
   .Build()

;

 Console.WriteLine(sql);

Output:

SELECT
 DISTINCT
 [CategoryID],
 [Discontinued]
FROM [Products]

Using .DISTINCT() with a Collection of TSQLColumn() Objects

var columns = new List<TSQLColumn>
{
      new TSQLColumn { ColumnName = "CategoryID" },
      new TSQLColumn { ColumnName = "Discontinued" }
};

   var sql = TSQL

      .SELECT()
        .DISTINCT()
        .COLUMNS(columns)
      .FROM("Products")
      .Build()

;

 Console.WriteLine(sql);

Output:

SELECT
 DISTINCT
 [CategoryID],
 [Discontinued]
FROM [Products]

TOP

Using .TOP(int) with .STAR()

var sql = TSQL

  .SELECT()
   .TOP(10)
   .STAR()
  .FROM("Products")
  .Build()

;

Console.WriteLine(sql);

Output:

SELECT
 TOP 10
 *
FROM [Products]

Using .TOP(int) with Columns

var sql = TSQL

  .SELECT()
    .TOP(10)
    .COLUMN("ProductID")
    .COLUMN("ProductName")
  .FROM("Products")
  .Build()

;

Console.WriteLine(sql);

Output:

SELECT
 TOP 10
 [ProductID],
 [ProductName]
FROM [Products]

TOP n PERCENT

var sql = TSQL

   .SELECT()
     .TOP(10, isPercent: true)
     .COLUMN("ProductID")
     .COLUMN("ProductName")
   .FROM("Products")
   .Build()

;

 Console.WriteLine(sql);

Output:

SELECT
 TOP 10 PERCENT
 [ProductID],
 [ProductName]
FROM [Products]