OrderCollection - how to return only the columns I want

[Mike718NY]

This returns all the columns in the ac_Orders table:

OrderCollection orderIDs = OrderDataSource.LoadForCriteria("dateOrd = getdate()");
R1.DataSource = orderIDs;

But I really only want one, OrderID, like: "SELECT OrderID FROM ac_Orders WHERE dateOrd = getdate()"

How can I specify my own SQL using the "Data Access Layer" in CommerceBuilder?

[mazhar]

You can iterate over returned order collection and then get order id one by one from each order

Code: Select all

List<int> orderIds = new List<int>();
foreach(Order order in orders)
orderIds.Add(order.OrderId);

[Mike718NY]

Don't know if this is ok, but this worked:

string query = "SELECT OrderID FROM ac_Orders WHERE (convert(varchar(10), dateOrd, 101)) = (convert(varchar(10), getdate(), 101))";
Microsoft.Practices.EnterpriseLibrary.Data.Database database = Token.Instance.Database;
System.Data.Common.DbCommand selectCommand = database.GetSqlStringCommand(query);
System.Data.SqlClient.SqlDataReader dr = (System.Data.SqlClient.SqlDataReader)database.ExecuteReader(selectCommand);

[mazhar]

Yep that's fine.

[jmestep]

Don't forget to wrap it in a using so the connection will be closed. Here is from Able wiki, using code from a newer version of Able where it uses CommerceBuilder.data instead of
Microsoft.Practices.EnterpriseLibrary.Data.Database

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);
}