Developer forum

Forum » Development » Razor SQL Error

Razor SQL Error

E-mail notifications
Umar Farooq
Reply
Posted on 04/06/2014 09:19:14

Hi guys, I created a razor template for google xml feed and made a function to call product group ID and Product Group name to use into a product list but unfortunatly i get an sql time out error when I select greater amount of products to show in ecom module but when i use less amount of products to show it out puts the feed. So what could be the possible reasons to get this error on greater ammount of products ? Secondly Is there any way to use pure xml instead of html.praising ?  


Replies

 
Mikkel Ricky
Posted on 04/06/2014 09:45:26
Reply

I think you should have a look at this page: https://github.com/dynamicweb/dynamicweb-templates/tree/master/Files/Templates/Designs/RssFeed. It's not a Razor template, but it shows how to create a feed in Dynamicweb.

Best regards,
Mikkel

 
Umar Farooq
Posted on 04/06/2014 10:31:59
Reply

Hi Mikkel, Thanks for your Feed back which resolved one of my issues regarding html praising  but still i am not able to get the ecom:group.ID value and   ecom:group.Name into the product loop. todo this i created a  function which look like this 

@functions{

     string[] getGroup(String ProductID){


string[] output = new string[2];
        using (var command = Dynamicweb.Database.CreateConnection().CreateCommand())
        {
            command.CommandText = "SELECT TOP 1 GroupName, GroupID FROM EcomGroupProductRelation LEFT JOIN EcomGroups ON EcomGroupProductRelation.GroupProductRelationGroupID = EcomGroups.GroupID WHERE GroupProductRelationProductID = @ProductID";
          
   command.Parameters.Add(new System.Data.SqlClient.SqlParameter()
            {
                ParameterName = "ProductID",
                SqlDbType = System.Data.SqlDbType.NVarChar,
                Value = ProductID
            });
            using (var reader = command.ExecuteReader())
            {

   while (reader.Read())
            {
                    output[0] = reader.GetString(0);
                    output[1] = reader.GetString(1);

           }           
            }
        }

return output;
    }

}

which works fine with less amount of products but if you set it to 100 products it throughs Timeout expired error

 

 
Mikkel Ricky
Posted on 04/06/2014 12:12:06
Reply
This post has been marked as an answer

Rather than performing this sql query for each product, you should get the group from the product.

Using template tags, write something like this:

@foreach (var product in GetLoop("Products")) {
  var groups = product.GetLoop("AssociatedGroups");
  // Do something with groups
}

or using the API, write something like

var theProduct = Dynamicweb.eCommerce.Products.Product.GetProductByID(product.GetString("Ecom:Product.ID"), product.GetString("Ecom:Product.VariantID"), product.GetString("Ecom:Product.LanguageID"));
// Do something with theProduct.Groups

Best regards,
Mikkel

 

 

 

 

 

Votes for this answer: 1

 

You must be logged in to post in the forum