Complete Item Listing

[vashts1980]

During my integration process, I ran into a situation where there was a problem finding a match between AbleCommerce and the other system. Using C#, I'd like to be able to pull the entire list of items and their variants so I can do a comparison and make sure there's only a one-to-one ratio of matches. I know the code necessary for finding the matches in the other system, but I don't know how I would pull up the entire product listing and variants from within AbleCommerce. My thoughts were to have this run in a code-behind then display the results to a field on the related page.

Along these same lines, I'm also unsure as to how to add a page to the site and have it recognized. Part of my work is going to rely upon a special custom page that will not be available to visitors, and what I need to do here for this particular concern will apply to that work, as well.

[vashts1980]

Ok, I've actually figured out how to get an extra page set up in the system, and I've found the database schema for the version I'm working with. Now... Can someone instead point me to something that describes now to properly set up the database connection so that I can submit my SQL query?

I keep getting the following error message in the logs..
Exception of type 'System.Web.HttpUnhandledException' was thrown.; ExecuteReader: Connection property has not been initialized.

[jmestep]

If you add using for CommerceBuilder.Data, you can get the database by Token.Instance.Database. You don't need to do any connection opening, closing, etc. if you do it that way. You might need to add a using to CommerceBuilder.Common also.

[vashts1980]

Will that work without a codebehind page? I had to switch to having everything operate in an ASPX page without a codebehind. For some reason I got nothing but problems when trying to use one.

[vashts1980]

FYI, I was going with some sample code provided in the wiki that looks like this...

Code: Select all

CommerceBuilder.Data.Database database = Token.Instance.Database;
String sql = ("SQLQUERY");
using (System.Data.Common.DbCommand selectCommand = database.GetSqlStringCommand(sql))
{
    ...SQL results handling
}

The best thing I can think of for handling this is using this...

Code: Select all

System.Data.Common.DbDataReader dbReader = selectCommand.ExecuteReader()

...but that's what causes the error mentioned above.

[jmestep]

You could try putting the code on the aspx page within script tags-kind of like Able does in the checkout payment page. They are .ascx files there, but with no code behind.
<%@ Control Language="C#" ClassName="CheckPaymentForm" EnableViewState="false" %>
<script runat="server">
C# stuff
</script>
Html stuff

[JaminLeather]

You could create your own query, but for pulling a list of all products along with any variants, you could use the existing CommerceBuilder methods. Try something like this...

Code: Select all

...
using CommerceBuilder.Products;
...
        ProductCollection allProducts = CommerceBuilder.Products.ProductDataSource.LoadForStore("Sku");
        foreach (Product selectedProduct in allProducts)  // Loop through all products
        {
            OptionCollection _OptionCollection = selectedProduct.GetOptions();
            foreach (Option _Option in _OptionCollection) // Loop through all product choices
            {
                OptionChoiceCollection _OptionChoice = OptionDataSource.Load(_Option.OptionId).Choices;
                foreach (OptionChoice opt in _OptionChoice) // Loop through each choice
                {
                  // Do whatever you want with each product variant
                }
            }
        }
...

[vashts1980]

Thanks everyone!

Actually doing an SQL query was truly the best way I wanted to go as it made it easy to get exactly what I wanted and how. But I'll keep those AC methods in mind for next time.

I was able to do an item comparison between the two different systems, and it looks like the real problems I had were either items that had typos (or not written to match in both systems) and not properly typing the items for the target system.

This is what ended up working for me, in case anyone is interested...

Code: Select all

CommerceBuilder.Data.Database database = Token.Instance.Database;
StringBuilder sql = new StringBuilder();
sql.Append("SELECT ac_Products.Name,ac_Products.Sku,ac_ProductVariants.VariantName,ac_ProductVariants.Sku FROM ac_Products _
     LEFT JOIN ac_ProductVariants ON ac_Products.ProductId=ac_ProductVariants.ProductId");
System.Data.Common.DbCommand selectCommand = database.GetSqlStringCommand(sql.ToString());
database.AddInParameter(selectCommand, "@storeId", System.Data.DbType.Int32, Token.Instance.StoreId);
System.Data.IDataReader dbResults = database.ExecuteReader(selectCommand);
while (dbResults.Read())
{
       ... do stuff here
}
dbResults.Close();
dbResults.Dispose();