// // Licensed to the Apache Software Foundation (ASF) under one or more // contributor license agreements. See the NOTICE file distributed with // this work for additional information regarding copyright ownership. // The ASF licenses this file to You under the Apache License, Version 2.0 // (the "License"); you may not use this file except in compliance with // the License. You may obtain a copy of the License at // // http://www.apache.org/licenses/LICENSE-2.0 // // Unless required by applicable law or agreed to in writing, software // distributed under the License is distributed on an "AS IS" BASIS, // WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. // See the License for the specific language governing permissions and // limitations under the License. // // .Net StockTrader Sample WCF Application for Benchmarking, Performance Analysis and Design Considerations for Service-Oriented Applications //=============================================================================================== // Customer is part of the SQLServer DAL for StockTrader. This is called from the // BSL to execute commands against the database. It is constructed to use one SqlConnection per // instance. Hence, BSLs that use this DAL should always be instanced properly. // The DAL will work with both ADO.NET and System.Transactions or ServiceComponents/Enterprise // Services attributed transactions [autocomplete]. When using ADO.NET transactions, // The BSL will control the transaction boundaries with calls to dal.BeginTransaction(); // dal.CommitTransaction(); dal.RollbackTransaction(). //=============================================================================================== //====================================================================================================== // Code originally contributed by Microsoft Corporation. // This contribution to the Stonehenge project is limited strictly // to the source code that is submitted in this submission. // Any technology, including underlying platform technology, // that is referenced or required by the submitted source code // is not a part of the contribution. // For example and not by way of limitation, // any systems/Windows libraries (WPF, WCF, ASP.NET etc.) // required to run the submitted source code is not a part of the contribution //====================================================================================================== using System; using System.Collections.Generic; using System.Text; using System.Data; using MySql.Data.MySqlClient; using MySql.Data; using MySql.Data.Types; using Trade.IDAL; using Trade.Utility; using Trade.ConfigServiceDataContract; using Trade.BusinessServiceDataContract; namespace Trade.DALMySQL { public class MarketSummary : IMarketSummary { public MarketSummary() { } //Constructor for internal DAL-DAL calls to use an existing DB connection. public MarketSummary(MySqlConnection conn, MySqlTransaction trans) { _internalConnection = conn; _internalADOTransaction = trans; } //_internalConnection: Used by a DAL instance such that a DAL instance, //associated with a BSL instance, will work off a single connection between BSL calls. private MySqlConnection _internalConnection; //Used only when doing ADO.NET transactions. //This will be completely ignored when null, and not attached to a cmd object //In MySQLHelper unless it has been initialized explicitly in the BSL with a //dal.BeginADOTransaction(). See app config setting in web.config and //Trade.BusinessServiceHost.exe.config "Use System.Transactions Globally" which determines //whether user wants to run with ADO transactions or System.Transactions. The DAL itself //is built to be completely agnostic and will work with either. private MySqlTransaction _internalADOTransaction; //Used only when doing ADO.NET transactions. public void BeginADOTransaction() { _internalADOTransaction = _internalConnection.BeginTransaction(System.Data.IsolationLevel.ReadCommitted); } //Used only when doing ADO.NET transactions. public void RollBackTransaction() { _internalADOTransaction.Rollback(); _internalADOTransaction = null; } //Used only when doing ADO.NET transactions. public void CommitADOTransaction() { _internalADOTransaction.Commit(); _internalADOTransaction = null; } public void Open(string connString) { if (_internalConnection == null) _internalConnection = new MySqlConnection(connString); if (_internalConnection.State != ConnectionState.Open) _internalConnection.Open(); } public void Close() { if (_internalConnection != null && _internalConnection.State != ConnectionState.Closed) _internalConnection.Close(); } private const string MySQL_SELECT_MARKETSUMMARY_GAINERS = "SELECT symbol, companyname, volume, price, open1, low, high, change1 from quote where symbol like 's:%' order by change1 desc"; private const string MySQL_SELECT_MARKETSUMMARY_LOSERS = "SELECT symbol, companyname, volume, price, open1, low, high, change1 from quote where symbol like 's:%' order by change1"; private const string MySQL_SELECT_MARKETSUMMARY_TSIA = "select SUM(price)/count(*) as TSIA from quote where symbol like 's:%'"; private const string MySQL_SELECT_MARKETSUMMARY_OPENTSIA = "select SUM(open1)/count(*) as openTSIA from quote where symbol like 's:%'"; private const string MySQL_SELECT_MARKETSUMMARY_VOLUME = "SELECT SUM(volume) from quote where symbol like 's:%'"; private const string MySQL_SELECT_QUOTE = "SELECT symbol, companyname, volume, price, open1, low, high, change1 from quote where symbol = ?QuoteSymbol"; private const string MySQL_SELECT_QUOTE_NOLOCK = "SELECT symbol, companyname, volume, price, open1, low, high, change1 from quote where symbol = ?QuoteSymbol"; private const string MySQL_UPDATE_STOCKPRICEVOLUME = "UPDATE QUOTE SET PRICE=?Price, Low=?Low, High=?High, Change1 = ?Price - open1, VOLUME=VOLUME+?Quantity WHERE SYMBOL=?QuoteSymbol"; //Parameters private const string PARM_SYMBOL = "?QuoteSymbol"; private const string PARM_PRICE = "?Price"; private const string PARM_LOW = "?Low"; private const string PARM_HIGH = "?High"; private const string PARM_QUANTITY = "?Quantity"; public void updateStockPriceVolume(double Quantity, QuoteDataModel quote) { try { MySqlParameter[] updatestockpriceparm = GetUpdateStockPriceVolumeParameters(); decimal priceChangeFactor = StockTraderUtility.getRandomPriceChangeFactor(quote.price); decimal newprice = quote.price * priceChangeFactor; if (newprice < quote.low) quote.low = newprice; if (newprice > quote.high) quote.high = newprice; updatestockpriceparm[0].Value = (decimal)newprice; updatestockpriceparm[1].Value = (float)Quantity; updatestockpriceparm[2].Value = quote.symbol; updatestockpriceparm[3].Value = quote.low; updatestockpriceparm[4].Value = quote.high; MySQLHelper.ExecuteNonQuery(_internalConnection, _internalADOTransaction, CommandType.Text, MySQL_UPDATE_STOCKPRICEVOLUME, updatestockpriceparm); return; } catch { throw; } } public QuoteDataModel getQuote(string symbol) { try { MySqlParameter parm1 = new MySqlParameter(PARM_SYMBOL, MySqlDbType.VarChar, 10); parm1.Value = symbol; MySqlDataReader rdr = MySQLHelper.ExecuteReaderSingleRowSingleParm(_internalConnection, _internalADOTransaction, CommandType.Text, MySQL_SELECT_QUOTE_NOLOCK, parm1); QuoteDataModel quote = null; if (rdr.HasRows) { rdr.Read(); quote = new QuoteDataModel(rdr.GetString(0), rdr.GetString(1), rdr.GetDouble(2), rdr.GetDecimal(3), rdr.GetDecimal(4), rdr.GetDecimal(5), rdr.GetDecimal(6), rdr.GetDouble(7)); } rdr.Close(); return quote; } catch { throw; } } public QuoteDataModel getQuoteForUpdate(string symbol) { try { MySqlParameter parm1 = new MySqlParameter(PARM_SYMBOL, MySqlDbType.VarChar, 10); parm1.Value = symbol; MySqlDataReader rdr = MySQLHelper.ExecuteReaderSingleRowSingleParm(_internalConnection, _internalADOTransaction, CommandType.Text, MySQL_SELECT_QUOTE, parm1); QuoteDataModel quote = null; if (rdr.HasRows) { rdr.Read(); quote = new QuoteDataModel(rdr.GetString(0), rdr.GetString(1), rdr.GetDouble(2), rdr.GetDecimal(3), rdr.GetDecimal(4), rdr.GetDecimal(5), rdr.GetDecimal(6), rdr.GetDouble(7)); } rdr.Close(); return quote; } catch { throw; } } public MarketSummaryDataModelWS getMarketSummaryData() { try { decimal TSIA = (decimal)MySQLHelper.ExecuteScalarNoParm(_internalConnection, _internalADOTransaction, CommandType.Text, MySQL_SELECT_MARKETSUMMARY_TSIA); decimal openTSIA = (decimal)MySQLHelper.ExecuteScalarNoParm(_internalConnection, _internalADOTransaction, CommandType.Text, MySQL_SELECT_MARKETSUMMARY_OPENTSIA); double totalVolume = (double)MySQLHelper.ExecuteScalarNoParm(_internalConnection, _internalADOTransaction, CommandType.Text, MySQL_SELECT_MARKETSUMMARY_VOLUME); MySqlDataReader rdr = MySQLHelper.ExecuteReaderNoParm(_internalConnection, _internalADOTransaction, CommandType.Text, MySQL_SELECT_MARKETSUMMARY_GAINERS); List topgainers = new List(); List toplosers = new List(); int i = 0; while (rdr.Read() && i++ < 5) { QuoteDataModel quote = new QuoteDataModel(rdr.GetString(0), rdr.GetString(1), rdr.GetDouble(2), rdr.GetDecimal(3), rdr.GetDecimal(4), rdr.GetDecimal(5), rdr.GetDecimal(6), rdr.GetDouble(7)); topgainers.Add(quote); } rdr.Close(); rdr = MySQLHelper.ExecuteReaderNoParm(_internalConnection, _internalADOTransaction, CommandType.Text, MySQL_SELECT_MARKETSUMMARY_LOSERS); i = 0; while (rdr.Read() && i++ < 5) { QuoteDataModel quote = new QuoteDataModel(rdr.GetString(0), rdr.GetString(1), rdr.GetDouble(2), rdr.GetDecimal(3), rdr.GetDecimal(4), rdr.GetDecimal(5), rdr.GetDecimal(6), rdr.GetDouble(7)); toplosers.Add(quote); } rdr.Close(); MarketSummaryDataModelWS marketSummaryData = new MarketSummaryDataModelWS(TSIA, openTSIA, totalVolume, topgainers, toplosers); return marketSummaryData; } catch { throw; } } private static MySqlParameter[] GetUpdateStockPriceVolumeParameters() { // Get the paramters from the cache MySqlParameter[] parms = MySQLHelper.GetCacheParameters(MySQL_UPDATE_STOCKPRICEVOLUME); // If the cache is empty, rebuild the parameters if (parms == null) { parms = new MySqlParameter[] { new MySqlParameter(PARM_PRICE, MySqlDbType.Decimal, 14), new MySqlParameter(PARM_QUANTITY, MySqlDbType.Float), new MySqlParameter(PARM_SYMBOL, MySqlDbType.VarChar, 10), new MySqlParameter(PARM_LOW, MySqlDbType.Decimal, 14), new MySqlParameter(PARM_HIGH, MySqlDbType.Decimal, 14)}; // Add the parametes to the cached MySQLHelper.CacheParameters(MySQL_UPDATE_STOCKPRICEVOLUME, parms); } return parms; } } }