Exemplo n.º 1
0
        /// <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);
        }
Exemplo n.º 2
0
        /// <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);
        }