/// <summary>
/// Internal function to get cached parameters
/// </summary>
/// <returns></returns>
private static OracleParameter[] GetOrderParameters()
{
OracleParameter[] parms = OracleHelper.GetCachedParameters(SQL_INSERT_ORDER);
if (parms == null)
{
parms = new OracleParameter[] {
new OracleParameter(PARM_ORDER_ID, OracleType.Number, 10),
new OracleParameter(PARM_USER_ID, OracleType.VarChar, 80),
new OracleParameter(PARM_DATE, OracleType.DateTime),
new OracleParameter(PARM_SHIP_ADDRESS1, OracleType.VarChar, 80),
new OracleParameter(PARM_SHIP_ADDRESS2, OracleType.VarChar, 80),
new OracleParameter(PARM_SHIP_CITY, OracleType.VarChar, 80),
new OracleParameter(PARM_SHIP_STATE, OracleType.VarChar, 80),
new OracleParameter(PARM_SHIP_ZIP, OracleType.VarChar, 50),
new OracleParameter(PARM_SHIP_COUNTRY, OracleType.VarChar, 50),
new OracleParameter(PARM_BILL_ADDRESS1, OracleType.VarChar, 80),
new OracleParameter(PARM_BILL_ADDRESS2, OracleType.VarChar, 80),
new OracleParameter(PARM_BILL_CITY, OracleType.VarChar, 80),
new OracleParameter(PARM_BILL_STATE, OracleType.VarChar, 80),
new OracleParameter(PARM_BILL_ZIP, OracleType.VarChar, 50),
new OracleParameter(PARM_BILL_COUNTRY, OracleType.VarChar, 50),
new OracleParameter(PARM_TOTAL, OracleType.Number),
new OracleParameter(PARM_BILL_FIRST_NAME, OracleType.VarChar, 80),
new OracleParameter(PARM_BILL_LAST_NAME, OracleType.VarChar, 80),
new OracleParameter(PARM_SHIP_FIRST_NAME, OracleType.VarChar, 80),
new OracleParameter(PARM_SHIP_LAST_NAME, OracleType.VarChar, 80),
new OracleParameter(PARM_AUTHORIZATION_NUMBER, OracleType.Int32)
};
OracleHelper.CacheParameters(SQL_INSERT_ORDER, parms);
}
return(parms);
}
/// <summary>
/// Query for products by keywords.
/// The results will include any product where the keyword appears in the category name or product name
/// </summary>
/// <param name="keywords">string array of keywords</param>
/// <returns>A Generic List of ProductInfo</returns>
public IList <ProductInfo> GetProductsBySearch(string[] keywords)
{
IList <ProductInfo> productsBySearch = new List <ProductInfo>();
//Create a new query string
int numKeywords = keywords.Length;
StringBuilder sql = new StringBuilder(SQL_SELECT_PRODUCTS_BY_SEARCH1);
//Add each keyword to the query
for (int i = 0; i < numKeywords; i++)
{
sql.Append(string.Format(SQL_SELECT_PRODUCTS_BY_SEARCH2, PARM_KEYWORD + i));
sql.Append(i + 1 < numKeywords ? SQL_SELECT_PRODUCTS_BY_SEARCH3 : SQL_SELECT_PRODUCTS_BY_SEARCH4);
}
//See if we have a set of cached parameters based on a similar qquery
string sqlProductsBySearch = sql.ToString();
OracleParameter[] parms = OracleHelper.GetCachedParameters(sqlProductsBySearch);
// If the parameters are null build a new set
if (parms == null)
{
parms = new OracleParameter[numKeywords];
for (int i = 0; i < numKeywords; i++)
{
parms[i] = new OracleParameter(PARM_KEYWORD + i, OracleType.VarChar, 80);
}
// Cache the new parameters
OracleHelper.CacheParameters(sqlProductsBySearch, parms);
}
// Bind the new parameters
for (int i = 0; i < numKeywords; i++)
{
parms[i].Value = keywords[i];
}
//Finally execute the query
using (OracleDataReader rdr = OracleHelper.ExecuteReader(OracleHelper.ConnectionStringLocalTransaction, CommandType.Text, sqlProductsBySearch, parms)) {
while (rdr.Read())
{
ProductInfo product = new ProductInfo(rdr.GetString(0), rdr.GetString(1), rdr.GetString(2), rdr.GetString(3), rdr.GetString(4));
productsBySearch.Add(product);
}
}
return(productsBySearch);
}
