Custom Queries - AC7

There are two main ways to facilitate custom SQL queries. The first is the most direct route, accessing the Database object directly. The current database is always available from the current token instance:

       CommerceBuilder.Data.Database database = Token.Instance.Database;
       string sql = ("SELECT COUNT(*) As RecordCount FROM ac_Affiliates WHERE StoreId = @storeId");
       using (System.Data.Common.DbCommand selectCommand = database.GetSqlStringCommand(sql))
       {
           database.AddInParameter(selectCommand, "@storeId", System.Data.DbType.Int32, Token.Instance.StoreId);
           int affiliateCount = (int)database.ExecuteScalar(selectCommand);
       }

The CommerceBuilder.Data.Database class gives you complete power to pass SQL requests to the database. If your needs are simpler, you may also be able to use the LoadForCriteria method that is present on all of the datasource classes. For example, suppose you only need affiliates with names that start with "A":

       AffiliateCollection A_affiliates = AffiliateDataSource.LoadForCriteria("Name LIKE 'A%'");

This will populate the collection with Affiliate records that have a name starting with A. The criteria that you provide will be used to form the WHERE clause, so you can extend this as much as required with AND, OR, and parenthesis.