// // 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 Order : IOrder // // 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 //====================================================================================================== { public Order() { } //Constructor for internal DAL-DAL calls to use an existing DB connection. public Order(MySqlConnection conn) { _internalConnection = conn; } //_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; //_internalADOTransaction: 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() { if (_internalConnection.State != ConnectionState.Open) _internalConnection.Open(); _internalADOTransaction = _internalConnection.BeginTransaction(System.Data.IsolationLevel.ReadCommitted); } //Used only when explicitly using ADO.NET transactions from the BSL. public void RollBackTransaction() { _internalADOTransaction.Rollback(); _internalADOTransaction = null; } //Used only when explicitly using ADO.NET transactions from the BSL. 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_INSERT_ORDER = "INSERT INTO ORDERS (OPENDATE, ORDERFEE, PRICE, QUOTE_SYMBOL, QUANTITY, ORDERTYPE, ORDERSTATUS, ACCOUNT_ACCOUNTID, HOLDING_HOLDINGID) VALUES (NOW(), ?OrderFee, ?Price, ?QuoteSymbol, ?Quantity, ?OrderType, 'open', ?accountId, ?HoldingId); SELECT LAST_INSERT_ID()"; private const string MySQL_GET_ACCOUNTID = "SELECT ACCOUNTID FROM ACCOUNT WHERE PROFILE_USERID = ?userId"; private const string MySQL_GET_ACCOUNTID_ORDER = "SELECT ACCOUNT_ACCOUNTID FROM ORDERS WHERE ORDERID=?OrderId"; private const string MySQL_INSERT_HOLDING = "INSERT INTO HOLDING (PURCHASEPRICE, QUANTITY, PURCHASEDATE, ACCOUNT_ACCOUNTID, QUOTE_SYMBOL) VALUES (?PurchasePrice, ?Quantity, ?PurchaseDate, ?AccountId, ?QuoteSymbol); SELECT LAST_INSERT_ID()"; private const string MySQL_SELECT_HOLDING = "SELECT HOLDING.HOLDINGID, HOLDING.QUANTITY, HOLDING.PURCHASEPRICE, HOLDING.PURCHASEDATE, HOLDING.QUOTE_SYMBOL,HOLDING.ACCOUNT_ACCOUNTID FROM HOLDING WHERE HOLDINGID= ?HoldingId"; private const string MySQL_DELETE_HOLDING = "DELETE FROM HOLDING WHERE HOLDINGID=?HoldingId"; private const string MySQL_GET_HOLDING_QUANTITY = "SELECT QUANTITY FROM HOLDING WHERE HOLDINGID=?HoldingId"; private const string MySQL_UPDATE_HOLDING = "UPDATE HOLDING SET QUANTITY=QUANTITY-?Quantity WHERE HOLDINGID=?HoldingId"; private const string MySQL_UPDATE_ORDER = "UPDATE ORDERS SET QUANTITY=?Quantity WHERE ORDERID=?OrderId"; private const string MySQL_CLOSE_ORDER = "UPDATE ORDERS SET ORDERSTATUS = ?status, COMPLETIONDATE=NOW(), HOLDING_HOLDINGID=?HoldingId, PRICE=?Price WHERE ORDERID = ?OrderId"; //Parameters private const string PARM_SYMBOL = "?QuoteSymbol"; private const string PARM_USERID = "?userId"; private const string PARM_ORDERSTATUS = "?status"; private const string PARM_QUANTITY = "?Quantity"; private const string PARM_ORDERTYPE = "?OrderType"; private const string PARM_ACCOUNTID = "?accountId"; private const string PARM_ORDERID = "?OrderId"; private const string PARM_HOLDINGID = "?HoldingId"; private const string PARM_ORDERFEE = "?OrderFee"; private const string PARM_PRICE = "?Price"; private const string PARM_PURCHASEPRICE = "?PurchasePrice"; private const string PARM_PURCHASEDATE = "?PurchaseDate"; public QuoteDataModel getQuoteForUpdate(string symbol) { //Cross-DAL calls pass in their own connection if they want to ensure commans are //executed on the same connection and optional ADO transaction. If //_internalADOTransaction is null, as with all DAL classes, it will be ignored. MarketSummary marketsummaryDal = new MarketSummary(_internalConnection, _internalADOTransaction); return marketsummaryDal.getQuoteForUpdate(symbol); } public void updateStockPriceVolume(double quantity, QuoteDataModel quote) { //See note above: want to use existing connection MarketSummary marketSummaryDal = new MarketSummary(_internalConnection, _internalADOTransaction); marketSummaryDal.updateStockPriceVolume(quantity, quote); return; } public HoldingDataModel getHoldingForUpdate(int orderID) { //See note above: want to use existing connection Customer customerDal = new Customer(_internalConnection, _internalADOTransaction); return customerDal.getHoldingForUpdate(orderID); } public void updateAccountBalance(int accountID, decimal total) { //See note above: want to use existing connection Customer customerDal = new Customer(_internalConnection, _internalADOTransaction); customerDal.updateAccountBalance(accountID, total); return; } public OrderDataModel createOrder(string userID, string symbol, string orderType, double quantity, int holdingID) { try { DateTime dt = DateTime.MinValue; int orderid = 0; OrderDataModel order = new OrderDataModel(orderid, orderType, StockTraderUtility.ORDER_STATUS_OPEN, DateTime.Now, DateTime.MinValue, quantity, (decimal)1, StockTraderUtility.getOrderFee(orderType), symbol); order.holdingID = holdingID; MySqlParameter[] parm = new MySqlParameter[] { new MySqlParameter(PARM_USERID, MySqlDbType.VarChar, 20) }; parm[0].Value = userID; order.accountID = Convert.ToInt32(MySQLHelper.ExecuteScalar(_internalConnection, _internalADOTransaction, CommandType.Text, MySQL_GET_ACCOUNTID, parm)); MySqlParameter[] OrderParms = GetCreateOrderParameters(); OrderParms[0].Value = order.orderFee; OrderParms[1].Value = order.price; OrderParms[2].Value = order.symbol; OrderParms[3].Value = (float)order.quantity; OrderParms[4].Value = order.orderType; OrderParms[5].Value = order.accountID; OrderParms[6].Value = holdingID; order.orderID = Convert.ToInt32(MySQLHelper.ExecuteScalar(_internalConnection, _internalADOTransaction, CommandType.Text, MySQL_INSERT_ORDER, OrderParms)); return order; } catch { throw; } } public HoldingDataModel getHolding(int holdingID) { MySqlParameter parm1 = new MySqlParameter(PARM_HOLDINGID, MySqlDbType.Int32, 10); parm1.Value = holdingID; MySqlDataReader rdr = MySQLHelper.ExecuteReaderSingleRowSingleParm(_internalConnection, _internalADOTransaction, CommandType.Text, MySQL_SELECT_HOLDING, parm1); if (rdr.Read()) { HoldingDataModel holding = new HoldingDataModel(rdr.GetInt32(0), rdr.GetDouble(1), rdr.GetDecimal(2), rdr.GetDateTime(3), rdr.GetString(4), rdr.GetInt32(5)); rdr.Close(); return holding; } rdr.Close(); return null; } public int createHolding(OrderDataModel order) { try { MySqlParameter orderParm = new MySqlParameter(PARM_ORDERID, MySqlDbType.Int32, 10); orderParm.Value = order.orderID; order.accountID = Convert.ToInt32(MySQLHelper.ExecuteScalarSingleParm(_internalConnection, _internalADOTransaction, CommandType.Text, MySQL_GET_ACCOUNTID_ORDER, orderParm)); MySqlParameter[] HoldingParms = GetCreateHoldingParameters(); HoldingParms[0].Value = order.price; HoldingParms[1].Value = (float)order.quantity; HoldingParms[2].Value = order.openDate; HoldingParms[3].Value = order.accountID; HoldingParms[4].Value = order.symbol; int holdingid = MySQLHelper.ExecuteScalar(_internalConnection, _internalADOTransaction, CommandType.Text, MySQL_INSERT_HOLDING, HoldingParms); return holdingid; } catch { throw; } } public void updateHolding(int holdingid, double quantity) { try { MySqlParameter[] HoldingParms2 = GetUpdateHoldingParameters(); HoldingParms2[0].Value = holdingid; HoldingParms2[1].Value = quantity; MySQLHelper.ExecuteNonQuery(_internalConnection, _internalADOTransaction, CommandType.Text, MySQL_UPDATE_HOLDING, HoldingParms2); HoldingDataModel holding = new HoldingDataModel(); return; } catch { throw; } } public void deleteHolding(int holdingid) { try { MySqlParameter[] HoldingParms2 = { new MySqlParameter(PARM_HOLDINGID, MySqlDbType.Int32) }; HoldingParms2[0].Value = holdingid; MySQLHelper.ExecuteNonQuery(_internalConnection, _internalADOTransaction, CommandType.Text, MySQL_DELETE_HOLDING, HoldingParms2); return; } catch { throw; } } public void updateOrder(OrderDataModel order) { try { MySqlParameter[] orderparms = GetUpdateOrderParameters(); orderparms[0].Value = order.quantity; orderparms[1].Value = order.orderID; MySQLHelper.ExecuteNonQuery(_internalConnection, _internalADOTransaction, CommandType.Text, MySQL_UPDATE_ORDER, orderparms); } catch { throw; } return; } public void closeOrder(OrderDataModel order) { try { MySqlParameter[] closeorderparm = GetCloseOrdersParameters(); closeorderparm[0].Value = StockTraderUtility.ORDER_STATUS_CLOSED; if (order.orderType.Equals(StockTraderUtility.ORDER_TYPE_SELL)) closeorderparm[1].Value = DBNull.Value; else closeorderparm[1].Value = order.holdingID; closeorderparm[2].Value = order.price; closeorderparm[3].Value = order.orderID; MySQLHelper.ExecuteNonQuery(_internalConnection, _internalADOTransaction, CommandType.Text, MySQL_CLOSE_ORDER, closeorderparm); } catch { throw; } return; } private static MySqlParameter[] GetCreateOrderParameters() { // Get the paramters from the cache MySqlParameter[] parms = MySQLHelper.GetCacheParameters(MySQL_INSERT_ORDER); // If the cache is empty, rebuild the parameters if (parms == null) { parms = new MySqlParameter[] { new MySqlParameter(PARM_ORDERFEE, MySqlDbType.Decimal, 14), new MySqlParameter(PARM_PRICE, MySqlDbType.Decimal, 14), new MySqlParameter(PARM_SYMBOL, MySqlDbType.VarChar, 20), new MySqlParameter(PARM_QUANTITY, MySqlDbType.Float), new MySqlParameter(PARM_ORDERTYPE, MySqlDbType.VarChar,5), new MySqlParameter(PARM_ACCOUNTID, MySqlDbType.Int32, 10), new MySqlParameter(PARM_HOLDINGID, MySqlDbType.Int32,10)}; // Add the parametes to the cached MySQLHelper.CacheParameters(MySQL_INSERT_ORDER, parms); } return parms; } private static MySqlParameter[] GetUpdateOrderParameters() { // Get the paramters from the cache MySqlParameter[] parms = MySQLHelper.GetCacheParameters(MySQL_UPDATE_ORDER); // If the cache is empty, rebuild the parameters if (parms == null) { parms = new MySqlParameter[] {new MySqlParameter(PARM_QUANTITY, MySqlDbType.Float), new MySqlParameter(PARM_ORDERID,SqlDbType.Int)}; MySQLHelper.CacheParameters(MySQL_UPDATE_ORDER, parms); } return parms; } private static MySqlParameter[] GetCreateHoldingParameters() { // Get the paramters from the cache MySqlParameter[] parms = MySQLHelper.GetCacheParameters(MySQL_INSERT_HOLDING); // If the cache is empty, rebuild the parameters if (parms == null) { parms = new MySqlParameter[] { new MySqlParameter(PARM_PURCHASEPRICE, MySqlDbType.Decimal, 14), new MySqlParameter(PARM_QUANTITY, MySqlDbType.Float), new MySqlParameter(PARM_PURCHASEDATE, MySqlDbType.DateTime), new MySqlParameter(PARM_ACCOUNTID, MySqlDbType.Int32), new MySqlParameter(PARM_SYMBOL, MySqlDbType.VarChar,20)}; // Add the parametes to the cached MySQLHelper.CacheParameters(MySQL_INSERT_HOLDING, parms); } return parms; } private static MySqlParameter[] GetUpdateHoldingParameters() { // Get the paramters from the cache MySqlParameter[] parms = MySQLHelper.GetCacheParameters(MySQL_UPDATE_HOLDING); // If the cache is empty, rebuild the parameters if (parms == null) { parms = new MySqlParameter[] {new MySqlParameter(PARM_HOLDINGID, MySqlDbType.Int32), new MySqlParameter(PARM_QUANTITY, MySqlDbType.Float)}; // Add the parametes to the cached MySQLHelper.CacheParameters(MySQL_UPDATE_HOLDING, parms); } return parms; } private static MySqlParameter[] GetCloseOrdersParameters() { // Get the paramters from the cache MySqlParameter[] parms = MySQLHelper.GetCacheParameters(MySQL_CLOSE_ORDER); // If the cache is empty, rebuild the parameters if (parms == null) { parms = new MySqlParameter[] { new MySqlParameter(PARM_ORDERSTATUS, MySqlDbType.VarChar,10), new MySqlParameter(PARM_HOLDINGID, MySqlDbType.Int32), new MySqlParameter(PARM_PRICE, MySqlDbType.Decimal, 14), new MySqlParameter(PARM_ORDERID, MySqlDbType.Int32)}; // Add the parametes to the cached MySQLHelper.CacheParameters(MySQL_CLOSE_ORDER, parms); } return parms; } } }