Custom Query - Dataset as a result

[caldog]

I am using the Token.Instance.Database to execute a custom query in AbleCommerce. The examples on the wiki illustrate getting a scalar result. My question is whether we can get a Dataset returned? I tried it and it appears a Dataset can be returned but I am having trouble getting the results from the Dataset. My code below is:

string sql = ("SELECT CatalogNodeID FROM ac_CatalogNodes WHERE CategoryID IN (SELECT CategoryID FROM ac_Categories WHERE ParentID = 20 and Name = @CategoryID)");

using (System.Data.Common.DbCommand selectCommand = Token.Instance.Database.GetSqlStringCommand(sql))
{
Token.Instance.Database.AddInParameter(selectCommand, "@CategoryID", System.Data.DbType.String, _Category.CategoryId);
System.Data.DataSet dsDiscountedProducts = (System.Data.DataSet)Token.Instance.Database.ExecuteDataSet(selectCommand);
}

Any suggestions on how I loop through the dataset to get the category id's in the dataset?

Thank You,
Calvin

[jmestep]

Since it's a System.Data.Dataset, you should be able find find info searching in Google or MSDN.

[mazhar]

Try something like this with dataset

Code: Select all

string sql = ("SELECT CatalogNodeID FROM ac_CatalogNodes WHERE CategoryID IN (SELECT CategoryID FROM ac_Categories WHERE ParentID = 20 and Name = @CategoryID)");

        using (System.Data.Common.DbCommand selectCommand = Token.Instance.Database.GetSqlStringCommand(sql))
        {
            Token.Instance.Database.AddInParameter(selectCommand, "@CategoryID", System.Data.DbType.String, _Category.CategoryId);
            System.Data.DataSet dsDiscountedProducts = (System.Data.DataSet)Token.Instance.Database.ExecuteDataSet(selectCommand);
            System.Data.DataTable table = dsDiscountedProducts.Tables[0];
            foreach (System.Data.DataRow row in table.Rows)
            {
                int catalogNodeId = AlwaysConvert.ToInt(row["CatalogNodeId"]);
                //Rest of your code here
            }
        }