Page 1 of 1
OrderCollection - how to return only the columns I want
Posted: Tue Jul 21, 2009 1:36 pm
by 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?
Re: OrderCollection - how to return only the columns I want
Posted: Wed Jul 22, 2009 3:09 am
by 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);
Re: OrderCollection - how to return only the columns I want
Posted: Wed Jul 22, 2009 7:09 am
by 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);
Re: OrderCollection - how to return only the columns I want
Posted: Wed Jul 22, 2009 7:16 am
by mazhar
Yep that's fine.
Re: OrderCollection - how to return only the columns I want
Posted: Wed Jul 22, 2009 7:16 am
by 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);
}