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...) |
|||
| 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: | ||
| − | |||
Microsoft.Practices.EnterpriseLibrary.Data.Database database = Token.Instance.Database; | Microsoft.Practices.EnterpriseLibrary.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"); | ||
| Line 9: | Line 8: | ||
int affiliateCount = (int)database.ExecuteScalar(selectCommand); | int affiliateCount = (int)database.ExecuteScalar(selectCommand); | ||
} | } | ||
| − | |||
The Microsoft.Practices.EnterpriseLibrary.Data.Database class gives you complete power over the database. | The Microsoft.Practices.EnterpriseLibrary.Data.Database class gives you complete power over the database. | ||
| Line 15: | Line 13: | ||
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%'"); | + | |
| − | + | ||
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. | ||
Revision as of 16:05, 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.
