Executing plain SQL

To execute a SQL query on the database, the usual APIs like QuerySpec, Linq or the low-level APIs through Adapter or SelfServicing's specific methods are preferred and in most cases will do what you want to do, however in some situations where you need more than these APIs can provide, e.g. you need to execute a hand-tuned SQL statement, you need to execute the SQL statement directly. The Plain SQL API described in this document offers that functionality: it allows you to execute a plain SQL statement with parameters onto the database, like a lowlevel microORM would.

Specifying parameters

A cornerstone of a SQL statement are parameters. The Plain SQL API allows various ways to specify parameters, be it a plain value or a set of values. Additionally you can specify specifics about the parameter, e.g. the direction or type. The Plain SQL API supports both named parameter support and ordered parameter support. The latter is usable for databases which don't support named parameters, like MS Access and IBM DB2. You can use the both systems on databases which do support named parameters.

Parameters are specified with their full name in the SQL statement. For databases which don't use named parameters, a ? is specified. Below is an example of a query with two parameters.

var query = "SELECT * FROM Customers WHERE Country=@country AND City=@city;"

var query = "SELECT * FROM Customers WHERE Country=@p0 AND City=@p1;"

Choosing between ordered and named parameters

To make the Plain API use ordered parameters, pass the values in an object[] instance, with all values in the right order as they appear in the query statement. Each value is then converted into a DbParameter instance containing that value and with a name determined from the index of the value in the object array.

To make the Plain API use named parameters, pass the values in an object with public, non-static properties, with names equal to the parameters specified in query statement. Each property is then converted into a DbParameter containing the value of the property and with a name equal to the property.

An example:

adapter.ExecuteSQL("UPDATE Customers SET Country=@country WHERE CustomerID=@id", 
        new {
            country="Germany", // is the value for the first parameter, will become @country
            id="ALFKI" // is the value for the second parameter, will become @id
        });

adapter.ExecuteSQL("UPDATE Customers SET Country=@p0 WHERE CustomerID=@p1", 
        new object[] {
            "Germany", // is the value for the first parameter, will become @p0
            "ALFKI" // is the value for the second parameter, will become @p1
        });

// On a database which doesn't support named parameters, this will become:
adapter.ExecuteSQL("UPDATE Customers SET Country=? WHERE CustomerID=?", 
        new object[] {
            "Germany", // is the value for the first parameter, will become @p0
            "ALFKI" // is the value for the second parameter, will become @p1
        });

Using named parameters

The following section describes how to use named parameters, which effectively means every non-static, public property in the object passed in as parameter values, will be converted into a DbParameter with the same name as the property. As there's no ordering possible with this system, using named parameters with MS Access and IBM DB2 will result in an ORMQueryConstruction.

Naming pattern

To specify a parameter name in the SQL statement for a value, you can use any valid name for a parameter. The name you specify (without the prefix) has to be the same as the property of the object with values passed to the Plain SQL api method. The runtime will then create DbParameter objects from the non-static, public properties of the object with values, with names using this pattern: parameterprefix + property name. parameterprefix is the database specific prefix, e.g. @ or :. property name is the exact name of the non-static, public property. Casing has to match on databases with case-sensitive collation for SQL statements (e.g. PostgreSQL and Oracle).

Parameter sets

As parameter names have to be specified in the SQL statement, it might be you have an unknown set of values at runtime and therefore don't know the parameter names at development time. To specify a parameter set, e.g. for an IN() clause, you specify a parameter with the name of the property which holds the set of values:

var query = "SELECT * FROM Customers WHERE Country IN(@countries)"

Here, the placeholder parameter @countries will be replaced by a set of parameters created from the IEnumerable<T> property in the object with values which has the name countries. See the following example:

adapter.ExecuteSQL("UPDATE Customers SET Country=@country WHERE Country IN(@countries)", 
        new {
            country="Germany", // is the value for the first parameter, will become @country
            countries=new[] {"ALFKI", "CHOPS", "BLONP"} // first parameter set, will replace @countries.
        });

At runtime the above query will be:

UPDATE Customers SET Country=@country WHERE Country IN(@countries0, @countries1, @countries2)

The values specified in the IEnumerable<T> property countries, i.e. the string array with the values "ALFKI", "CHOPS", and "BLONP", are all converted into normal DbParameter objects and their names are added to the query at the place of the placeholder parameter name. The parameter names generated for each value in an IEnumerable<T> argument is: parameterprefix + propertyname + setindex.

The parameterprefix + propertyname part is constructed the same as for other parameters. This is the base name of the parameters in the parameter set. For each value in the IEnumerable<T>, the base name is suffixed with the index in the IEnumerable<T> of the value the parameter is for (0 based). This means the first value in the IEnumerable<T> (in our example above, the string ALFKI) has setindex 0, and will have as base name countries and will get the suffix 0, so the name of the parameter for the value ALFKI will be: @countries0.

Using ordered parameters

The following section describes how to use ordered parameters, which effectively means the order in which parameter values are specified in the object array is the order in which the parameters are specified in the query statement. The order in which the values are specified is used to create parameter names.

Naming pattern

To specify a parameter name in the SQL statement for a value, the name has to follow a specific pattern so the real parameter generated from the specified value has the same name. This pattern is: parameterprefix + p + number. parameterprefix is the database specific prefix, e.g. @ or :. number is a positive number starting with 0: @p0 is the parameter for the first specified value, @p1 for the second specified value and so on.

Parameter sets

As parameter names have to be specified in the SQL statement, it might be you have an unknown set of values at runtime and therefore don't know the parameter names at development time. To specify a parameter set, e.g. for an IN() clause, you specify a placeholder:

var query = "SELECT * FROM Customers WHERE Country IN({0})"

Here, the placeholder {0} will be replaced by a set of parameters created from the IEnumerable<T> argument specified as parameter set. See the following example:

adapter.ExecuteSQL("UPDATE Customers SET Country=@p0 WHERE Country IN({0})", 
        new object[] {
            "Germany", // is the value for the first parameter, will become @p0
            new List<string> {"ALFKI", "CHOPS", "BLONP"} // first parameter set, will replace {0}.
        });

At runtime the above query will be:

UPDATE Customers SET Country=@p0 WHERE Country IN(@p1i0, @p1i1, @p1i2)

The values specified in the IEnumerable<T> argument, here the string array with the values "ALFKI", "CHOPS", and "BLONP", are all converted into normal DbParameter objects and their names are added to the query in the placeholder. The parameter names generated for each value in an IEnumerable<T> argument is: parameterprefix + p + number + i + setindex.

The parameterprefix + p + number part is constructed the same as for other parameters: if the IEnumerable<T> is e.g. the 2nd argument, like in our example, number will be 1 (as it's 0 based). For each value in the IEnumerable<T>, a suffix is created using i + setindex (0 based), so the first value in the IEnumerable<T> (in our example above, the string ALFKI) has setindex 0, and will get the suffix i0, so the name of the parameter for the value ALFKI will be: @p1i0.

Using this naming pattern, you can also define the parameter names yourself instead of a placeholder. The code below is equal to the one above with the placeholder:

adapter.ExecuteSQL("UPDATE Customers SET Country=@p0 WHERE Country IN(@p1i0, @p1i1, @p1i2)", 
    "Germany", // is the value for the first parameter, will become @p0
    new List<string> {"ALFKI", "CHOPS", "BLONP"} // first parameter set, values will become @p1i0, @p1i1 and @p1i2
    );

Specifying parameter characteristics

Specifying a value for a parameter is done by specifying its literal value as part of the object passed to the method you want to call, be it either a normal object or an object array. The parameter created from this value will get a name created using the aforementioned naming patterns and will be an input parameter. The type of the parameter will be determined from the literal value specified.

This can be limiting, e.g. with string values where you want to specify a dedicated length, string type or e.g. when you want to specify the direction of the parameter. To do this, use a ParameterValue instance instead of the literal value of the parameter. ParameterValue is a class defined in the ORMSupportClasses and which offers the following additional aspects for the parameter besides the value of the parameter: (all are optional, in which case they're ignored and the resulting DbParameter object won't have its equivalent property set to a value)

• DBType. This is the DbType enumeration value to use. Parameter classes set this value based on the specified value, but it can sometimes be the wrong one (as multiple types can be used with a given .NET type, e.g. DateTime or string).
• Length
• Precision
• Scale
• Value
• Direction
• IsNullable

Enum values are converted by the runtime to the underlying value, so it's not required to specify it as an enum: specify the enum typed value as the parameter value, the created DbParameter object will have its value set to the value in the underlying numeric type.

To specify NULL values, it's enough to specify null (Nothing in VB.NET) as the value, as the type and other characteristics aren't important, they're not used by ADO.NET.

The following example specifies a ParameterValue for the second parameter with a specific string type and length.

adapter.ExecuteSQL("UPDATE Customers SET Country=@country WHERE CustomerID=@id", 
        new {
            country = "Germany", // is the value for the first parameter, will become @country
            id = new ParameterValue(value="ALFKI", size=5, dbType=DbType.AnsiString) 
        });

adapter.ExecuteSQL("UPDATE Customers SET Country=? WHERE CustomerID=?", 
        new object[] {
            "Germany", // is the value for the first parameter, will become @p0
            new ParameterValue(value="ALFKI", size=5, dbType=DbType.AnsiString) 
        });

Output parameters

To specify output parameters, use a ParameterValue instance with its Direction set to Output or InputOutput. For output parameters specified like this the value of the parameter after the execution of the actual query will be placed back into the ParameterValue object, in its Value property. If a NULL is returned, Value is null (Nothing in VB.NET).

Example:

var newIDParameter = new ParameterValue() { Direction = ParameterDirection.Output, 
                                            DbType=DbType.Guid};
var result = adapter.ExecuteSQL("SET @id=NEWID();INSERT INTO Table (ID, F1) VALUES (@id, @f1Val)",
                new { id=newIDParameter, f1Val="F1 Value"});
// stores the value of the newIDParameter in the newIDValue variable
var newIDValue = (Guid)newIDParameter.Value;

var newIDParameter = new ParameterValue() { Direction = ParameterDirection.Output, 
                                            DbType=DbType.Guid};
var result = adapter.ExecuteSQL("SET @p0=NEWID();INSERT INTO Table (ID, F1) VALUES (@p0, @p1)",
                new object[] {newIDParameter, "F1 Value"});
// stores the value of the newIDParameter in the newIDValue variable
var newIDValue = (Guid)newIDParameter.Value;

Output parameter values are synced with the ParameterValue instances after a potential resultset has been read.

Executing a statement with ExecuteSQL

Action queries are queries which don't return a resultset but instead perform an action on the database and return the number of successful operations (if applicable). These operations are typically doing insert, delete or update statements. To execute an action query, pass the query string in the right format with the proper parameter names as well as the parameter values to the ExecuteSQL method. The ExecuteSQL method is part of IDao (SelfServicing) and IDataAccessAdapter (Adapter).

The method will return the value returned by the executed statement, which is the value returned by DbCommand.ExecuteNonQuery.

Example with Named parameters

Full example of an action query with parameters and an output parameter:

Guid newId = Guid.Empty;
using(var adapter = new DataAccessAdapter())
{
    var q = @"SELECT @id=NEWID();INSERT INTO MyTable (ID, Name) VALUES (@id, @name);";
    var idVar = new ParameterValue(ParameterDirection.InputOutput, dbType: DbType.Guid);
    var result = adapter.ExecuteSQL(q, new {id=idVar, name="NameValue"});
    newId = (Guid)idVar.Value;
}

var q = @"SELECT @id=NEWID();INSERT INTO MyTable (ID, Name) VALUES (@id, @name);";
var idVar = new ParameterValue(ParameterDirection.InputOutput, dbType: DbType.Guid);
var result = new TypedListDAO().ExecuteSQL(null, q, new {id=idVar, name="NameValue"});
Guid newId = (Guid)idVar.Value;

Example with Ordered parameters

Full example of an action query with parameters and an output parameter:

Guid newId = Guid.Empty;
using(var adapter = new DataAccessAdapter())
{
    var q = @"SELECT @p0=NEWID();INSERT INTO MyTable (ID, Name) VALUES (@p0, @p1);";
    var idVar = new ParameterValue(ParameterDirection.InputOutput, dbType: DbType.Guid);
    var result = adapter.ExecuteSQL(q, new object[] {idVar, "NameValue"});
    newId = (Guid)idVar.Value;
}

var q = @"SELECT @p0=NEWID();INSERT INTO MyTable (ID, Name) VALUES (@p0, @p1);";
var idVar = new ParameterValue(ParameterDirection.InputOutput, dbType: DbType.Guid);
var result = new TypedListDAO().ExecuteSQL(null, q, new object[] {idVar, "NameValue"});
Guid newId = (Guid)idVar.Value;

Fetching a resultset with FetchQuery

Retrieval queries are queries which return a resultset, projected to a type. Other operations can be included in the specified query, however a resultset is expected. To execute a retrieval query, pass the query string in the right format with the proper parameter names as well as the parameter values and an optional PlainSQLFetchAspects instance with additional information to the FetchQuery overload which accepts a string as a query. The generic argument of FetchQuery is the type each row in the resultset will be projected to.

Example:

var results = adapter.FetchQuery<Customer>("SELECT * FROM Customers WHERE Country=@country", 
                                           new { country="USA"});

var results = adapter.FetchQuery<Customer>("SELECT * FROM Customers WHERE Country=@p0", 
                                           new object[] {"USA"});

Here all rows from the table Customers are fetched which have USA as the value for Country and each row is projected to an instance of the class Customer. The engine will project each column to a property of the class with the same name, case-insensitive comparison. This means a property MyProperty in Customer with no equivalent column in the resultset won't receive a value. A column in the resultset with no equivalent property in Customer will be ignored.

Specifying resultset specifics with PlainSQLFetchAspects

PlainSQLFetchAspects is a class which is part of the ORMSupportClasses and which allows you to specify additional specifics for the FetchQuery call. All all optional, in which case their default value is used.

The following aspects are available:

• Offset (0==no offset. Default: 0)
• Limit (0==no limit, all rows are returned. Default: 0)
• CacheResultset (bool. Default: false).
• CacheDuration (Timespan. Only used if CacheResultset is set to true. Default: default Timespan value).
• OverwriteIfPresent (bool, used to force overwrite a cached resultset. Default: false).
• CacheTag (string. optional tag to cache the resultset under. Default: empty string).

See for the caching parameter specifics Resultset caching.

Offset and Limit are used to build a paging query from the specified SQL statement. This means the query must have an order by clause, on some databases, like SQL Server; the engine won't add one manually.

Full example with paging and resultset caching, using named parameters:

List<Customer> result = null;
using(var adapter = new DataAccessAdapter())
{
    adapter.CompatibilityLevel = SqlServerCompatibilityLevel.SqlServer2012;
    // fetch first 5 rows, cache resultset for 10 seconds.
    result = adapter.FetchQuery<Customer>(new PlainSQLFetchAspects(limit: 5, 
                cacheResultset: true, cacheDuration: new TimeSpan(0, 0, 10)), 
                "SELECT * FROM Customers WHERE Country IN (@c1, @c2) ORDER BY CustomerID DESC",
                new {c1="USA", c2="Germany"});
}

CommonDaoBase..SetSqlServerCompatibilityLevel(SqlServerCompatibilityLevel.SqlServer2012);
// fetch first 5 rows, cache resultset for 10 seconds.
List<Customer> result = new TypedListDAO().FetchQuery<Customer>(new PlainSQLFetchAspects(limit: 5, 
                cacheResultset: true, cacheDuration: new TimeSpan(0, 0, 10)), 
                "SELECT * FROM Customers WHERE Country IN (@c1, @c2) ORDER BY CustomerID DESC",
                new {c1="USA", c2="Germany"});

Executing a scalar query with FetchScalarQuery

Scalar queries are queries which return a single, scalar, value. To execute an action query, pass the query string in the right format with the proper parameter names as well as the parameter values to the FetchScalarQuery method. The method and its overloads return the first value returned by the query executed, converted to the type specified as the generic type argument of the method. The FetchScalarQuery method is part of IDao (SelfServicing) and IDataAccessAdapter (Adapter).

Below is a scalar query which calculates the total number of orders from two given customers: one example using named parameters and one using ordered parameters.

Example with Named parameters

using(var adapter = new DataAccessAdapter())
{
    var q = @"SELECT count(*) FROM Orders WHERE CustomerID IN (@id1, @id2)";
    return adapter.FetchScalarQuery<int>(q, new { id1 = "USA", id2 = "Germany" });
}

var q = @"SELECT count(*) FROM Orders WHERE CustomerID IN (@id1, @id2)";
return new TypedListDAO().FetchScalarQuery<int>(q, new { id1 = "USA", id2 = "Germany" });

Example with Ordered parameters

using(var adapter = new DataAccessAdapter())
{
    var q = @"SELECT count(*) FROM Orders WHERE CustomerID IN (@p0, @p1)";
    return adapter.FetchScalarQuery<int>(q, new object[] { "USA", "Germany" });
}

var q = @"SELECT count(*) FROM Orders WHERE CustomerID IN (@p0, @p1)";
return new TypedListDAO().FetchScalarQuery<int>(q, new object[] { "USA", "Germany" });

Transactions

Plain SQL statements can participate in transactions, so you can mix them with regular entity actions, like committing a unit of work or deleting an entity. For Adapter, it's enough to use an IDataAccessAdapter instance which has an active transaction, i.e. started by the method StartTransaction(). After the work in the transaction has been completed, the transaction has to be committed or rolled back, using the methods on the used IDataAccessAdapter instance. See Adapter: Transactions for details on transactions in Adapter.

For SelfServicing, you have to create a Transaction instance first and pass it to ExecuteSQL or FetchQuery. By passing the Transaction instance the methods will perform all work inside that transaction. See SelfServicing: Transactions for details on transactions in SelfServicing.

Example which deletes rows from two different tables in a single transaction, using named parameters:

using(var adapter = new DataAccessAdapter())
{
    try
    {
        adapter.StartTransaction(IsolationLevel.ReadCommitted, "DeleteRows");
        adapter.ExecuteSQL("DELETE FROM [Order Details] WHERE OrderID=@id", new {id=10254});
        adapter.ExecuteSQL("DELETE FROM [Orders] WHERE OrderID=@id", new {id=10254});
        adapter.Commit();
    }
    catch
    {
        adapter.Rollback();
        throw;
    }
}

using(var transaction = new Transaction(IsolationLevel.ReadCommitted, "DeleteRows"))
{
    try
    {
        var dao = new TypedListDAO();
        dao.ExecuteSQL(transaction, "DELETE FROM [Order Details] WHERE OrderID=@id", new {id=10254});
        dao.ExecuteSQL(transaction, "DELETE FROM [Orders] WHERE OrderID=@id", new {id=10254});
        transaction.Commit();
    }
    catch
    {
        transaction.Rollback();
        throw;
    }
}

Execute a stored procedure

To execute a stored procedure, it's enough to simply formulate the call as a SQL statement. The example below executes the stored procedure CustOrdersOrders and passes one parameter, with the value 'CHOPS'. The stored procedure returns a resultset, which is projected to the POCO Order.

var result = adapter.FetchQuery<Order>("EXEC CustOrdersOrders @id", new {id="CHOPS"});

Dealing with SQL injection

The Plain SQL API allows a developer to run any SQL statement onto the database. Even though specifying parameter values is easy and straightforward, it doesn't stop a developer to embed a value directly into the SQL statement instead of using parameters.

What is SQL injection and why are parameters better?

To illustrate why it is very bad to embed values directly into the SQL statement, see the following example:

// this asks the user name from the user.
var userName = GetUserNameFromUser();
// bad! embedding value directly into the query
adapter.ExecuteSQL("DELETE FROM Users WHERE UserName='"+userName+"'");

Say the name filled in by the user is 'Bobby Tables'. This will result in the query:

DELETE FROM Users WHERE UserName='Bobby Tables'

A user who wants to delete all users from the table however can easily do so by specifying as user name ' OR 1=1--. This is a normal string, however the query will now become:

DELETE FROM Users WHERE UserName='' OR 1=1--'

The WHERE clause will resolve to true for all rows and all rows therefore will be deleted. Using a parameter however would solve this, the code would then become:

// this asks the user name from the user.
var userName = GetUserNameFromUser();
// Good! using a parameter
adapter.ExecuteSQL("DELETE FROM Users WHERE UserName=@name", new {name=userName});

The string filled in by the user will be used as the value for the parameter and therefore will be compared with the values in the table rows. If the user fills in ' OR 1=1--, this won't matter, it will simply not match with any value in the table, with the added bonus it will also not be part of the executed SQL statement and therefore can do no harm.

It's therefore crucial to use parameters instead of embedding values into the SQL statement.

How to trace executed Plain SQL queries.

In case you want to monitor whether any plain SQL statement contains embedded values instead of parameters, the framework offers a specific tracer to log all plain SQL queries to a separate output. This tracer is called ORMPlainSQLQueryExecution, and has one level, info (3), which logs the query with parameter values onto the trace output. Enabling this tracer will log all plain SQL queries for all databases onto the single tracer. It's an easy way to know whether your application contains a plain SQL call where values are embedded into the SQL statement.

How to disable the Plain SQL API

In case you simply want to disable the plain SQL API altogether as you can't / don't want to run the risk of SQL injection, you can. The below example shows how to disable the API with a partial class. Calling the methods ExecuteSQL and FetchQuery will then result in an exception. The partial classes have to be part of the VS.NET project specified with the example.

// class has to be part of the generated DbSpecific project. Place it in a separate file, and
// make sure the namespace directive matches the one of the generated DataAccessAdapter class.
public partial class YourProject.DatabaseSpecific.DataAccessAdapter
{
    public override int ExecuteSQL(string sqlQuery, object parameterValues)
    {
        throw new NotSupportedException("The Plain SQL API isn't supported");
    }

    public override List<T> FetchQuery<T>(PlainSQLFetchAspects fetchAspects, string sqlQuery, object parameterValues)
    {
        throw new NotSupportedException("The Plain SQL API isn't supported");
    }

    // if you reference the .NET 4.5+ build of the ORMSupportClasses, also add this override
    public override System.Threading.Tasks.Task<int> ExecuteSQLAsync(System.Threading.CancellationToken cancellationToken, string sqlQuery, object parameterValues)
    {
        throw new NotSupportedException("The Plain SQL API isn't supported");
    }
    
    // if you reference the .NET 4.5+ build of the ORMSupportClasses, also add this override
    public override Task<List<T>> FetchQueryAsync<T>(CancellationToken cancellationToken, PlainSQLFetchAspects fetchAspects, string sqlQuery, object parameterValues)
    {
        throw new NotSupportedException("The Plain SQL API isn't supported");
    }
}

// class has to be part of the generated project. Place it in a separate file, and
// make sure the namespace directive matches the one of the generated CommonDaoBase class (which is in the
// folder 'DaoClasses'). 
public partial class YourProject.CommonDaoBase
{
    public override int ExecuteSQL(ITransaction containingTransaction, string sqlQuery, object parameterValues)
    {
        throw new NotSupportedException("The Plain SQL API isn't supported");
    }

    public override List<T> FetchQuery<T>(PlainSQLFetchAspects fetchAspects, ITransaction containingTransaction, string sqlQuery, object parameterValues)
    {
        throw new NotSupportedException("The Plain SQL API isn't supported");
    }

    // if you reference the .NET 4.5+ build of the ORMSupportClasses, also add this override
    public override Task<int> ExecuteSQLAsync(ITransaction containingTransaction, CancellationToken cancellationToken, string sqlQuery, object parameterValues)
    {
        throw new NotSupportedException("The Plain SQL API isn't supported");
    }

    // if you reference the .NET 4.5+ build of the ORMSupportClasses, also add this override
    public override Task<List<T>> FetchQueryAsync<T>(PlainSQLFetchAspects fetchAspects, ITransaction containingTransaction, CancellationToken cancellationToken, string sqlQuery, object parameterValues)
    {
        throw new NotSupportedException("The Plain SQL API isn't supported");
    }
}

After adding the partial class any call to ExecuteSQL or FetchQuery (and async variants) should result in an exception.