Custom DB Queries / Classes

[KullySupply]

As I've browsed through various posts on this forum I've noticed a lot of people talking about creating custom database queries. This is something I would like to do, but am not sure where to start. I've looked through the AC code, and do not see any example queries. I'm guessing this is because they are included in the Bin .dll files?... which I cannot view.

I was hoping that someone could describe this process and provide some code examples. I've gone through the CommerceBuilder API and am becoming more and more familiar with the various classes. Now I would like to create my own classes (if possible) to customize AC for my company's needs.

I am pretty new to programming... some of this stuff makes sense to me, but certain aspects are completely over my head at the moment. Any help would be greatly appreciated!

-Greg

[jmestep]

Here is some code from the forum that was replicated to the wiki
http://wiki.ablecommerce.com/index.php/ ... cess_Layer

[Logan Rhodehamel]

I posted a new article that might be helpful. It talks about how to add custom queries from .aspx script using either direct database access or our LoadForCriteria methods: http://wiki.ablecommerce.com/index.php/Custom_Queries

[KullySupply]

Thanks for the info!

Although I still feel a little lost and overwhelmed looking through those posts, I think I just have to work my way through it. Hopefully I'll "get it" eventually... I'm a designer, not a programmer, so this stuff doesn't come easy for me!

[m_plugables]

i have written some custom ConLib controls and reports with this approach. These codes can help you. You can download them from here
http://blog.plugables.com/

[KullySupply]

Thanks for those examples!

Hypothetically, if I wanted to select all the products with a particular Sku, what would that code look like?

I tried adding this code to one of my custom ConLibs, but it doesn't work (This code may or may not make any sense, but like I said this is all very new to me!):

Code: Select all

private void getProductsBySku(string prodSku)
{
        string query = "SELECT * FROM ac_Products WHERE Sku=" + prodSku;
        Microsoft.Practices.EnterpriseLibrary.Data.Database database = Token.Instance.Database;
        System.Data.Common.DbCommand selectCommand = database.GetSqlStringCommand(query);
        
        using (IDataReader reader = database.ExecuteReader(selectCommand))
        {
            while (reader.Read())
            {
                // code...
            }
        }
}

[Logan Rhodehamel]

It would probably be easier to use the LoadForCriteria method:

Code: Select all

ProductCollection myProducts = ProductDataSource.LoadForCriteria("SKU='mySku'");
foreach(Product p in myProducts)
{
... do something
}

You could also use LIKE criteria:

Code: Select all

ProductCollection myProducts = ProductDataSource.LoadForCriteria("SKU LIKE 'm%'");

That gets anything with a SKU that starts with "m".

[m_plugables]

The example given by logan is very good. In fact you need to go and write the custom queries and commands for those operations where you do not found support for your desired operation in AbleCommerce code. For example you want to fetch data from two tables like Products and Orders or some very complex query which you can't make work with LoadForCriteria methods etc.
Regarding

using (IDataReader reader = database.ExecuteReader(selectCommand))
{
while (reader.Read())
{
// code...
}
}

I am extending your code to put the products loading support in it and also fixing some problems in your existing code.

Code: Select all

private CommerceBuilder.Products.ProductCollection getProductsBySku(string prodSku)
    {
        string query = "SELECT * FROM ac_Products WHERE Sku=@sku";

        Microsoft.Practices.EnterpriseLibrary.Data.Database database = Token.Instance.Database;
        System.Data.Common.DbCommand selectCommand = database.GetSqlStringCommand(query);
        database.AddInParameter(selectCommand, "@sku", DbType.String, prodSku);
        CommerceBuilder.Products.ProductCollection products = new CommerceBuilder.Products.ProductCollection();
        using (IDataReader reader = database.ExecuteReader(selectCommand))
        {
            while (reader.Read())
            {
                int productId = AlwaysConvert.ToInt(reader["ProductId"]);
                if (productId > 0)
                {
                    CommerceBuilder.Products.Product product = CommerceBuilder.Products.ProductDataSource.Load(productId);
                    products.Add(product);
                }
            }
        }
        return products;
    }

string query = "SELECT * FROM ac_Products WHERE Sku=@sku";

Here you see that i changed Sku=" + prodSku to Sku=@sku that is because if i proceed with your approach then i must provide the sku wrapped in single quotes other wise this query will fail because of invalid syntax. But when you create parameters then these type of things are done by C# it self.

int productId = AlwaysConvert.ToInt(reader["ProductId"]);

For each iteration the data reader will contain a record from the database. I am just reading the ProductId from the reader so that i can load the product using that id.

[KullySupply]

Thanks for all the great info! This has really helped clear things up for me.

I was able to accomplish my goal. Basically, I wanted to pull in products as variants instead of actually using product variants.

For example, I created a product "Relief Valve" and in the "More Details" field (which I don't use) I added three sku numbers separated by a comma. When someone visits that page, my custom ConLib searches for those three products using the sku numbers and adds them to the drop down menu. When one is selected and added to the basket, that specific product is added to the basket (not the "Relief Valve" product - which is really just a placeholder for the variant products because it can't be added to the basket).

I still need to tweak it, but so far, so good. One great thing about doing things this way is that I can change the product page summary, description, image, price, sku number, model number, etc. automatically when the variant is selected. I was only able to change the image, price and sku numbers automatically before because the product variants only have a few input fields available.

I will post a link when we go live. Thanks again!