Difference between revisions of "Custom Queries - AC7"
| Line 16: | Line 16: | ||
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. | 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. | ||
| + | |||
| + | [[Category:CommerceBuilder_API]] | ||
Revision as of 16:08, 23 May 2008
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:
Microsoft.Practices.EnterpriseLibrary.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 Microsoft.Practices.EnterpriseLibrary.Data.Database class gives you complete power over 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.
