Difference between revisions of "Custom Queries - AC7"
From AbleCommerce Wiki
(New page: 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 cu...) |
|||
| (5 intermediate revisions by 2 users not shown) | |||
| 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 8: | ||
int affiliateCount = (int)database.ExecuteScalar(selectCommand); | int affiliateCount = (int)database.ExecuteScalar(selectCommand); | ||
} | } | ||
| − | |||
| − | 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": |
| − | + | AffiliateCollection A_affiliates = AffiliateDataSource.LoadForCriteria("Name LIKE '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. | 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:AbleCommerce 7]] | ||
| + | [[Category:CommerceBuilder_API]] | ||
Latest revision as of 10:33, 15 August 2013
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.
