7. Adapdev.Data.Sql
Prev Chapter 2. Adapdev Namespaces Next

7. Adapdev.Data.Sql

The goal of the Adapdev.Data.Sql namespace is to allow for the generation of database specific SQL through a database independent API.

The SQL API has three main pieces

  1. A query object. This can be one of the following:

    • ISelectQuery

    • IInsertQuery

    • IDeleteQuery

    • IUpdateQuery

  2. An ICriteria object. This provides all sql starting from "WHERE..."

  3. A QueryFactory object. This is reponsible for returning the correct query object.

Here is a simple example without any criteria:

ISelectQuery query = QueryFactory.CreateSelectQuery(DbType.ORACLE);

query.SetTable("sometable");
query.AddAll();

Console.WriteLine(query.Statement);

// Output: SELECT * FROM sometable;

Here's the same example, but with criteria included:

ISelectQuery query = QueryFactory.CreateSelectQuery(DbType.ORACLE);

query.SetTable("sometable");
query.AddAll();

ICriteria criteria = query.CreateCriteria();
criteria.AddEqualTo("somecolumn", 1);
query.SetCriteria(criteria);

Console.WriteLine(query.Statement);

// Output: SELECT * FROM sometable WHERE somecolumn = 1;

The generated code will "mutate" based on the target database. Let's revisit the example, but use a DateTime as the value, since the syntax for this varies from database to database.

public string GetSelectStatement(DbType dbType)
{
   ISelectQuery query = QueryFactory.CreateSelectQuery(DbType.ORACLE);

   query.SetTable("sometable");
   query.AddAll();

   ICriteria criteria = query.CreateCriteria();
   criteria.AddEqualTo("somecolumn", DateTime.Now);
   query.SetCriteria(criteria);

   return query.Statement;
}

... // somewhere else in your code

Console.WriteLine(GetSelectStatement(DbType.ACCESS));
// Output: 
SELECT * FROM [sometable] WHERE [somecolumn] = #1/30/2005 1:21:05 PM#;

Console.WriteLine(GetSelectStatement(DbType.ORACLE));
// Output: 
SELECT * FROM sometable WHERE somecolumn = '1/30/2005 1:21:05 PM';

As you can see, the syntax around the column and table names changed, as did the delimiters for the passed in date time value.

7.1. Select Queries

7.1.1. Adding All Columns

ISelectQuery query = QueryFactory.CreateSelectQuery(DbType.SQLSERVER);

query.SetTable("sometable");
query.AddAll();

// Example Output: 
SELECT * FROM [sometable];

7.1.2. Adding Specific Columns

ISelectQuery query = QueryFactory.CreateSelectQuery(DbType.SQLSERVER);

query.SetTable("sometable");
query.Add("somecolumn");
query.Add("somecolumn2");

// Example Output: 
SELECT [somecolumn], [somecolumn2] FROM [sometable];

7.1.3. Adding Multiple Tables with Columns

ISelectQuery query = QueryFactory.CreateSelectQuery(DbType.SQLSERVER);

query.SetTable("sometable");
query.Add("somecolumn");
query.Add("table2","somecolumn2");

// Example Output: 
SELECT [somecolumn], [table2].[somecolumn2] FROM [sometable];

7.1.4. Adding Columns with Aliases

ISelectQuery query = QueryFactory.CreateSelectQuery(DbType.SQLSERVER);

query.SetTable("sometable");
query.Add("column1");
query.AddColumnAlias("table2", "column2", "alias2");
query.AddColumnAlias("column3", "alias3");

// Example Output: 
SELECT  [column1], [table2].[column2] AS alias2,
        [column3] AS alias3 FROM [sometable];

7.1.5. Getting COUNT for All Columns

ISelectQuery query = QueryFactory.CreateSelectQuery(DbType.SQLSERVER);

query.SetTable("sometable");
query.AddCount("column");

// Example Output: 
SELECT  COUNT([column])  FROM [sometable];

7.1.6. Getting COUNT for a Specific Column

ISelectQuery query = QueryFactory.CreateSelectQuery(DbType.SQLSERVER);

query.SetTable("sometable");
query.AddCountAll();

// Example Output: 
SELECT  COUNT(*)  FROM [sometable];

7.1.7. Specifying ORDER BY

By default, ORDER BY is ASC

ISelectQuery query = QueryFactory.CreateSelectQuery(DbType.SQLSERVER);

query.SetTable("sometable");
query.AddAll();
query.AddOrderBy("column1");
query.AddOrderBy("column2");

// Example Output: 
SELECT  *  FROM [sometable] ORDER BY [column1], [column2] ASC;

You can override this by setting the OrderBy property

ISelectQuery query = QueryFactory.CreateSelectQuery(DbType.SQLSERVER);

query.SetTable("sometable");
query.AddAll();
query.AddOrderBy("column1");
query.AddOrderBy("column2");
query.OrderBy = OrderBy.DESCENDING;

// Example Output: 
SELECT  *  FROM [sometable] ORDER BY [column1], [column2] DESC;

Rather than specifying each column in a separate line, you can pass in multiple columns at once

ISelectQuery query = QueryFactory.CreateSelectQuery(DbType.SQLSERVER);

query.SetTable("sometable");
query.AddAll();
query.AddOrderByColumns("column1", "column2", "column3");

// Example Output: 
SELECT  *  FROM [sometable] ORDER BY [column1], [column2], [column3] ASC;

7.1.8. Specifying Multiple ORDER BYs

You can specify the order per column

ISelectQuery query = QueryFactory.CreateSelectQuery(DbType.SQLSERVER);

query.SetTable("sometable");
query.AddAll();
query.AddOrderBy("column1", OrderBy.ASCENDING);
query.AddOrderBy("column2", OrderBy.DESCENDING);

// Example Output: 
SELECT  *  FROM [sometable] ORDER BY [column1] ASC, [column2] DESC;

7.1.9. Adding JOINs

You can join multiple tables

7.1.9.1. Joining Two Tables (One-To-One, One-To-Many)
ISelectQuery query = QueryFactory.CreateSelectQuery(DbType.SQLSERVER);

query.SetTable("sometable");
query.AddAll();
query.AddJoin("table2", "column1", "column2", JoinType.INNER);

// Example Output: 
SELECT  *  FROM [sometable] 
INNER JOIN [table2] ON [sometable].[column1] = [table2].[column2];
7.1.9.2. Joining Multiple Tables (Many-To-Many)
ISelectQuery query = QueryFactory.CreateSelectQuery(DbType.SQLSERVER);

query.SetTable("sometable");
query.AddAll();
query.AddJoin("table2", "column1", "column2", 
              "table3", "column3", 
              "table4", "column4", JoinType.INNER);

// Example Output: 
SELECT  *  FROM [sometable] 
INNER JOIN [table2] ON [sometable].[column1] = [table2].[column2] 
INNER JOIN [table4] ON [table3].[column3] = [table4].[column4];
7.1.9.3. Specifying the JOIN type (INNER, LEFT, RIGHT, OUTER)

You can specify the join type. The example below does an OUTER join

ISelectQuery query = QueryFactory.CreateSelectQuery(DbType.SQLSERVER);

query.SetTable("sometable");
query.AddAll();
query.AddJoin("table2", "column1", "column2", 
              "table3", "column3", 
              "table4", "column4", JoinType.OUTER);

// Example Output: 
SELECT  *  FROM [sometable] 
FULL OUTER JOIN [table2] ON [sometable].[column1] = [table2].[column2] 
FULL OUTER JOIN [table4] ON [table3].[column3] = [table4].[column4];

7.1.10. Setting Record Limits

ISelectQuery query = QueryFactory.CreateSelectQuery(this._dbType);
query.SetTable("sometable");
query.AddAll();
query.SetLimit(100);

// Example Sql Server Output:
SELECT  TOP 100 *  FROM [sometable];

// Example Oracle Output:
SELECT  *  FROM  sometable  WHERE  ROWNUM <= 100

7.2. Insert Queries

7.2.1. Inserting Columns with Values

You can use the IInsertQuery to generate INSERT statements. The INSERT SQL will automatically handle object types and convert them to the appropriate SQL output (such as dates, text, strings, etc.). It will also change the syntax to match the corresponding database.

IInsertQuery query = QueryFactory.CreateInsertQuery(DbType.SQLSERVER);
query.SetTable("someTable");
query.Add("dateColumn", DateTime.Now);
query.Add("stringColumn", "some text");
query.Add("numericColumn", 12);

// Example Sql Server Output:
INSERT INTO [someTable] 
( [dateColumn], [stringColumn], [numericColumn] ) 
VALUES ( '3/3/2006 10:10:10 AM', 'some text', 12 )

// Example Access Output:
INSERT INTO [someTable] 
( [dateColumn], [stringColumn], [numericColumn] ) 
VALUES ( #3/3/2006 10:10:10 AM#, 'some text', 12 )

// Example Oracle Output:
INSERT INTO  someTable  
(  dateColumn ,  stringColumn ,  numericColumn  ) 
VALUES ( '3/3/2006 10:10:10 AM', 'some text', 12 )

7.2.2. Inserting Columns with Null Values

To insert a NULL value, just use the AddNull method and pass in the column name.

IInsertQuery query = QueryFactory.CreateInsertQuery(DbType.SQLSERVER);
query.SetTable("someTable");
query.AddNull("someColumn");
query.AddNull("someColumn2");

// Example Sql Server Output:
INSERT INTO [someTable] 
( [someColumn], [someColumn2] ) 
VALUES ( NULL, NULL ) 

// Example Access Output:
INSERT INTO [someTable] 
( [someColumn], [someColumn2] ) 
VALUES ( NULL, NULL ) 

// Example Oracle Output:
INSERT INTO someTable 
( someColumn, someColumn2 ) 
VALUES ( NULL, NULL )

7.2.3. Inserting Parametized Columns

To use a parametized query, use the standard Add method but don't provide a value. A parametized value will automatically be applied.

IInsertQuery query = QueryFactory.CreateInsertQuery(DbType.SQLSERVER);
query.SetTable("someTable");
query.Add("someColumn");
query.Add("someColumn2");

// Example Sql Server Output:
INSERT INTO [someTable] 
( [someColumn], [someColumn2] ) 
VALUES ( @someColumn, @someColumn2 ) 

// Example Access Output:
INSERT INTO [someTable] 
( [someColumn], [someColumn2] ) 
VALUES ( ?, ? ) 

// Example Oracle Output:
INSERT INTO someTable 
( :someColumn, :someColumn2 ) 
VALUES ( NULL, NULL )

7.3. Update Queries

7.3.1. Updating Columns with Values

You can use the IUpdateQuery to generate UPDATE statements. The UPDATE SQL will automatically handle object types and convert them to the appropriate SQL output (such as dates, text, strings, etc.). It will also change the syntax to match the corresponding database.

IUpdateQuery query = QueryFactory.CreateUpdateQuery(DbType.SQLSERVER);
query.SetTable("someTable");
query.Add("dateColumn", DateTime.Now);
query.Add("stringColumn", "some text");
query.Add("numericColumn", 12);

// Example Sql Server Output:
UPDATE [someTable] 
SET  [dateColumn] = '3/3/2006 10:10:10 AM', 
[stringColumn] = 'some text', 
[numericColumn] = 12

// Example Access Output:
UPDATE [someTable] 
SET  [dateColumn] = #3/3/2006 10:10:10 AM#, 
[stringColumn] = 'some text', 
[numericColumn] = 12

// Example Oracle Output:
UPDATE someTable 
SET  dateColumn = '3/3/2006 10:10:10 AM', 
stringColumn = 'some text', 
numericColumn = 12

7.3.2. Updating Columns with Null Values

To insert a NULL value, just use the AddNull method and pass in the column name.

IUpdateQuery query = QueryFactory.CreateUpdateQuery(DbType.SQLSERVER);
query.SetTable("someTable");
query.AddNull("someColumn");
query.AddNull("someColumn2");

// Example Sql Server Output:
UPDATE [someTable] SET  [someColumn] = NULL, [someColumn2] = NULL

// Example Access Output:
UPDATE [someTable] SET  [someColumn] = NULL, [someColumn2] = NULL

// Example Oracle Output:
UPDATE [someTable] SET  someColumn = NULL, someColumn2 = NULL

7.3.3. Updating Using Parametized Columns

To use a parametized query, use the standard Add method but don't provide a value. A parametized value will automatically be applied.

IUpdateQuery query = QueryFactory.CreateUpdateQuery(DbType.SQLSERVER);
query.SetTable("someTable");
query.Add("someColumn");
query.Add("someColumn2");

// Example Sql Server Output:
UPDATE [someTable] SET  [someColumn] = @someColumn, [someColumn2] = @someColumn2

// Example Access Output:
UPDATE [someTable] SET  [someColumn] = ?, [someColumn2] = ?

// Example Oracle Output:
UPDATE someTable SET  someColumn = :someColumn, someColumn2 = :someColumn2

7.4. Delete Queries

Using the IDeleteQuery is extremely simple

IDeleteQuery query = QueryFactory.CreateDeleteQuery(DbType.SQLSERVER);
query.SetTable("someTable");

// Example Output
DELETE FROM someTable

7.5. Adding Criteria (WHERE...)

The previous sections showed you how to create basic SELECT, INSERT, UPDATE and DELETE statements. Often the most important part though is the WHERE clause. The ICriteria interface is provided to support criteria creation.

Once you've created your query, then create your criteria and add it by using the SetCriteria method.

ISelectQuery query = QueryFactory.CreateSelectQuery(DbType.ACCESS);
query.SetTable("products");
query.AddAll();

ICriteria criteria = query.CreateCriteria();
criteria.AddLike("productName", "%.NET%");

query.SetCriteria(criteria);

Console.WriteLine(query.Statement);

// Output:
SELECT * FROM [products] 
WHERE [productName] LIKE '%.NET%';

The ICriteria interface provides numerous options. Below are several examples:

7.5.1. Between / Not Between

You can use AddBetween to find records with a column value between numbers, dates, etc.

ISelectQuery query = QueryFactory.CreateSelectQuery(DbType.SQLSERVER);
query.SetTable("products");
query.AddAll();

ICriteria criteria = query.CreateCriteria();
criteria.AddBetween("productId", 1, 5);

query.SetCriteria(criteria);

// Output:
SELECT * FROM [products] WHERE [productId] BETWEEN 1 AND 5

To specify the opposite, use the AddNotBetween method

ISelectQuery query = QueryFactory.CreateSelectQuery(DbType.SQLSERVER);
query.SetTable("products");
query.AddAll();

ICriteria criteria = query.CreateCriteria();
criteria.AddNotBetween("productId", 1, 5);

query.SetCriteria(criteria);

// Output:
SELECT * FROM [products] WHERE [productId] NOT BETWEEN 1 AND 5

7.5.2. Equal To / Not Equal To

You can use AddEqualTo to find records with a column value between numbers, dates, etc.

ISelectQuery query = QueryFactory.CreateSelectQuery(DbType.SQLSERVER);
query.SetTable("products");
query.AddAll();

ICriteria criteria = query.CreateCriteria();
criteria.AddEqualTo("productId", 1);

query.SetCriteria(criteria);

// Output:
SELECT * FROM [products] WHERE [productId] = 1

To do the opposite, use the AddNotEqualTo method

ISelectQuery query = QueryFactory.CreateSelectQuery(DbType.SQLSERVER);
query.SetTable("products");
query.AddAll();

ICriteria criteria = query.CreateCriteria();
criteria.AddNotEqualTo("productId", 1);

query.SetCriteria(criteria);

// Output:
SELECT * FROM [products] WHERE [productId] <> 1

Instead of specifying a value, you can use a parametized query

ISelectQuery query = QueryFactory.CreateSelectQuery(DbType.SQLSERVER);
query.SetTable("products");
query.AddAll();

ICriteria criteria = query.CreateCriteria();
criteria.AddNotEqualTo("productId");

query.SetCriteria(criteria);

// Output:
SELECT * FROM [products] WHERE [productId] <> @productId

7.5.3. Exists / Doesn't Exist

You can use AddEqualTo to find records with a column value between numbers, dates, etc.

ISelectQuery query = QueryFactory.CreateSelectQuery(DbType.SQLSERVER);
query.SetTable("products");
query.AddAll();

ICriteria criteria = query.CreateCriteria();
criteria.AddEqualTo("productId", 1);

query.SetCriteria(criteria);

// Output:
SELECT * FROM [products] WHERE [productId] = 1

To do the opposite, use the AddNotEqualTo method

ISelectQuery query = QueryFactory.CreateSelectQuery(DbType.SQLSERVER);
query.SetTable("products");
query.AddAll();

ICriteria criteria = query.CreateCriteria();
criteria.AddNotEqualTo("productId", 1);

query.SetCriteria(criteria);

// Output:
SELECT * FROM [products] WHERE [productId] <> 1

7.5.4. In / Not In

The sql IN keyword allows you to pass in multiple values at once. So, instead of doing this:

SELECT * FROM Products WHERE productId = 1 OR productId = 2 OR productId = 3

You can instead do this:

SELECT * FROM Products WHERE productId IN (1, 2, 3)

You can also use subqueries with the IN statement:

SELECT * FROM Products WHERE productId IN (
                       SELECT productId FROM Orders WHERE customerId = 5)

To do this using the SQL API, there are two options: pass in a collection or a subquery.

IN Using a Collection

ArrayList al = new ArrayList();
al.Add(1);
al.Add(2);
al.Add(3);

ISelectQuery query = QueryFactory.CreateSelectQuery(DbType.SQLSERVER);
query.SetTable("products");
query.AddAll();

ICriteria criteria = CriteriaFactory.CreateCriteria(DbType.SQLSERVER);
criteria.AddIn("productId", al);

query.SetCriteria(criteria);

// Output:
SELECT * FROM products WHERE productId IN (1, 2, 3)

IN Using a Subquery

ISelectQuery subQuery = QueryFactory.CreateSelectQuery(DbType.SQLSERVER);
subQuery.SetTable("orders");
subQuery.Add("productId");


ISelectQuery query = QueryFactory.CreateSelectQuery(DbType.SQLSERVER);
query.SetTable("products");
query.AddAll();

ICriteria criteria = CriteriaFactory.CreateCriteria(DbType.SQLSERVER);
criteria.AddIn("productId", subQuery);

query.SetCriteria(criteria);

// Output:
SELECT * FROM products WHERE productId IN (SELECT productId FROM orders)

To do the opposite, use the AddNotIn method, which also accepts a subquery or collection.

7.5.5. Greater Than / Greater Than or Equal To

A greater than query

ISelectQuery query = QueryFactory.CreateSelectQuery(DbType.SQLSERVER);
query.SetTable("products");
query.AddAll();

ICriteria criteria = query.CreateCriteria();
criteria.AddGreaterThan("productId", 1);

query.SetCriteria(criteria);

// Output:
SELECT * FROM [products] WHERE [productId] > 1

A query for greater than or equal to

ISelectQuery query = QueryFactory.CreateSelectQuery(DbType.SQLSERVER);
query.SetTable("products");
query.AddAll();

ICriteria criteria = query.CreateCriteria();
criteria.AddGreaterThanOrEqualTo("productId", 1);

query.SetCriteria(criteria);

// Output:
SELECT * FROM [products] WHERE [productId] >= 1

7.5.6. Less Than / Less Than or Equal To

A less than query

ISelectQuery query = QueryFactory.CreateSelectQuery(DbType.SQLSERVER);
query.SetTable("products");
query.AddAll();

ICriteria criteria = query.CreateCriteria();
criteria.AddLessThan("productId", 1);

query.SetCriteria(criteria);

// Output:
SELECT * FROM [products] WHERE [productId] < 1

A query for less than or equal to

ISelectQuery query = QueryFactory.CreateSelectQuery(DbType.SQLSERVER);
query.SetTable("products");
query.AddAll();

ICriteria criteria = query.CreateCriteria();
criteria.AddLessThanOrEqualTo("productId", 1);

query.SetCriteria(criteria);

// Output:
SELECT * FROM [products] WHERE [productId] <= 1

7.5.7. Null / Not Null

To check for a NULL value, use the AddIsNull method

ISelectQuery query = QueryFactory.CreateSelectQuery(DbType.SQLSERVER);
query.SetTable("products");
query.AddAll();

ICriteria criteria = query.CreateCriteria();
criteria.AddIsNull("productId");

query.SetCriteria(criteria);

// Output:
SELECT * FROM [products] WHERE [productId] IS NULL

For the opposite, use the AddNotNull method

ISelectQuery query = QueryFactory.CreateSelectQuery(DbType.SQLSERVER);
query.SetTable("products");
query.AddAll();

ICriteria criteria = query.CreateCriteria();
criteria.AddNotNull("supplierId", 1);

query.SetCriteria(criteria);

// Output:
SELECT * FROM [products] WHERE [supplierId] IS NOT NULL

7.6. Adding Multiple Criteria

So far we've demonstrated how to add single criteria. It's possible to add multiple criteria using the AND and OR keywords.

Using AND

ISelectQuery query = QueryFactory.CreateSelectQuery(DbType.SQLSERVER);
query.SetTable("products");
query.AddAll();

ICriteria criteria = query.CreateCriteria();
criteria.AddEqualTo("productId", 1);
criteria.AddAnd();
criteria.AddEqualTo("lastName", "Schmoe");

query.SetCriteria(criteria);

// Output:
SELECT * FROM [products] WHERE [productId] = 1 AND [lastName] = 'Schmoe'

Using OR

ISelectQuery query = QueryFactory.CreateSelectQuery(DbType.SQLSERVER);
query.SetTable("products");
query.AddAll();

ICriteria criteria = query.CreateCriteria();
criteria.AddEqualTo("productId", 1);
criteria.AddOr();
criteria.AddEqualTo("lastName", "Schmoe");

query.SetCriteria(criteria);

// Output:
SELECT * FROM [products] WHERE [productId] = 1 OR [lastName] = 'Schmoe'
Prev Up Next
6. Adapdev.Data Home 8. Adapdev.Data.Schema