Raw SQL queries and stored procedures

You can execute any raw SQL query and map the fields to properties of your custom objects.

To illustrate this, we will start by giving an example:

public class QueryResultEntry
{
  public int CustomerID;
  public string Name;
  public int NumOrders;
}

...

// the following statement uses "+" to make things more readable
string sqlQuery = "SELECT c.CustomerID, c.Name, COUNT(*) AS NumOrders"
                  + " FROM tblCustomer c"
                  + " INNER JOIN tblOrder o ON c.CustomerID = o.CustomerID"
                  + " GROUP BY c.CustomerID, c.Name"
                  + " WHERE c.ZipCode=@ZipCode";

QueryResultEntry[] entries = CSDatabase.RunQuery<QueryResultEntry>(sqlQuery, new {ZipCode:"55124"});

In the example above the RunQuery() method will return an array of objects of type QueryResultEntry. This way you can map any SQL query to a typed list of objects.

To execute a query on a database in another context (see the part about specifying your database connection for more information on contexts), you can use the following syntax:

CSDatabase.Context["context"].RunQuery(...)