Custom SQL Query in Gold

[chemistrudy]

working with Gold R3. I am in need of accessing data in a custom table that I added to the ac database from my .aspx page. For AC7 I found:

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);
}

in the wiki. This appears that it would allow me to do exactly what I need.

How do I go about getting custom data out of the database in Gold without building from the ground up?

Thanks, Rudy

FYI: The custom table stores customer to products to price relationship. We have a group of chemical products (500+/-) that in order to purchase, customers have to have pre-approval (I am setting a custom field to flag the products where this is the case so that if the visibility for the product is "public", pricing and other custom fields will be hidden - unless an authorized customer is logged in). The set of products (4-8) for each customer is different. As well as each customer may have unique pricing for the product (the custom table). Once a user is logged in, they will have a page showing their "authorized" products with their "special" pricing - and they can order right from that page instead of browsing the catalog or searching. (Most of our sales are repeat b2b). I'm open to suggestions about the best way to do this - a custom table seemed like the way to go.

[jmestep]

If you don't have Able's source code to look at, probably the best place to see a pattern of what you can do is in their Admin/Help/SqlPortal.aspx.cs. It is a page that does not have a link in the admin and is not enabled by default because of the danger of someone messing up their database, but it has this routine in it. You can see what namespaces you need to reference and how to return the results of the query.

Code: Select all

protected void ExecuteButton_Click(object sender, EventArgs e)
        {
            if (PortalEnabled)
            {
                DateTime beginTime = DateTime.Now;
                StringBuilder dataTable = new StringBuilder();
                try
                {
                    IList result = NHibernateHelper.CreateSQLQuery(SqlQuery.Text).List();
                    dataTable.Append("<div style=\"overflow:auto\"><table border=\"1\">");
                    foreach (object[] dataRow in result)
                    {
                        int columnCount = dataRow.Length;
                        dataTable.Append("<tr>");
                        for (int i = 0; i < columnCount; i++)
                        {
                            dataTable.Append("<td>" + dataRow[i].ToString() + "</td>");
                        }
                        dataTable.Append("</tr>");
                    }
                    dataTable.Append("</table></div><br />");
                }
                catch { }
                DateTime endTime = DateTime.Now;
                phQueryResult.Controls.Add(new LiteralControl(dataTable.ToString()));
                TimeSpan ts = endTime - beginTime;
                phQueryResult.Controls.Add(new LiteralControl("Query executed in " + Math.Round(ts.TotalMilliseconds, 0) + "ms"));
            }

[chemistrudy]

Judy -- Thank you so much!

The

Code: Select all

IList result = NHibernateHelper.CreateSQLQuery(sql).List();

is just what I was looking for.

For the benefit of others out there: In my case, I know the columns that the query will be returning, so define a table to hold the results and pass to my GridView:

Code: Select all

protected void Page_Load(object sender, EventArgs e)
        {
            string vcId = "";
            IList<CommerceBuilder.Users.UserSetting> usersetting;
            // Create a new table.
            DataTable authProducts = new DataTable();
            // Create the columns.
            authProducts.Columns.Add("CustomerNumber", typeof(string));
            authProducts.Columns.Add("ProductName", typeof(string));
            authProducts.Columns.Add("CustomerReference", typeof(string));
            authProducts.Columns.Add("strDescription", typeof(string));

            //find the right vcId (CustomerNumber)
            usersetting = UserSettingDataSource.LoadForUser(AbleContext.Current.User.Id);
            foreach (UserSetting setting in usersetting)
            {
                if (setting.FieldName == "customer_number")
                {
                    vcId = setting.FieldValue;
                }
            }
            //If the user has a Customernumber
            if (vcId.IsNotNullOrEmpty())
            {
                string sql = "SELECT CustomerNumber, ProductName, CustomerReference, strDescription FROM ac_UserCustProducts_Enerco WHERE CustomerNumber = '";
                sql = sql + vcId + "' ORDER BY ProductName";
                IList result = NHibernateHelper.CreateSQLQuery(sql).List();
                ResultCount.Text = "My Result Count:  " + result.Count.ToString();
                //Add data to the new table.
                foreach (object[] dataRow in result)
                {
                    DataRow tableRow = authProducts.NewRow();
                    tableRow["CustomerNumber"] = dataRow[0].ToString();
                    tableRow["ProductName"] = dataRow[1].ToString();
                    tableRow["CustomerReference"] = dataRow[2].ToString();
                    tableRow["strDescription"] = dataRow[3].ToString();
                    authProducts.Rows.Add(tableRow);
                }

                OrderGrid.DataSource = authProducts;
                OrderGrid.DataBind();
            }
        }

I've got more work to do, but I was stuck getting my data. Thanks again.

Rudy

[jmestep]

Here is another sample for you out of the source code. You might want to use this pattern instead because it passes the values as parameters to Sql server and that way Sql Server can reuse the query execution plan and you won't have to worry about Sql injection. This is delete query, but you get the idea.

Code: Select all

 public void Delete(int orderId, string couponCode)
        {
            NHibernateHelper.CreateSQLQuery("DELETE FROM ac_OrderCoupons WHERE OrderId = :orderId AND CouponCode = :couponCode")
                .SetInt32("orderId", orderId)
                .SetString("couponCode", couponCode)
                .ExecuteUpdate();
        }