Difference between revisions of "Custom Queries - AC7"
| Line 1: | Line 1: | ||
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: | 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"); | string sql = ("SELECT COUNT(*) As RecordCount FROM ac_Affiliates WHERE StoreId = @storeId"); | ||
using (System.Data.Common.DbCommand selectCommand = database.GetSqlStringCommand(sql)) | using (System.Data.Common.DbCommand selectCommand = database.GetSqlStringCommand(sql)) | ||
| Line 9: | Line 9: | ||
} | } | ||
| − | The | + | 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": |
| − | + | ||
| − | 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%'"); | AffiliateCollection A_affiliates = AffiliateDataSource.LoadForCriteria("Name LIKE 'A%'"); | ||
Revision as of 01:12, 7 July 2009
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.
