public HoldingDataModel getHolding(string userid, int holdingid)
{
try
{
OracleParameter[] holdingidparms = new OracleParameter[]{new OracleParameter(PARM_HOLDINGID, OracleDbType.Int32),
new OracleParameter(PARM_USERID, OracleDbType.Varchar2, 20)};
holdingidparms[0].Value = holdingid;
holdingidparms[1].Value = userid;
OracleDataReader rdr = OracleHelper.ExecuteReaderSingleRow(_internalConnection,_internalADOTransaction, CommandType.Text, SQL_SELECT_HOLDING2, holdingidparms);
while (rdr.Read())
{
HoldingDataModel holding = new HoldingDataModel(holdingid, Convert.ToInt32(rdr.GetDecimal(0)), Convert.ToDouble(rdr.GetDecimal(1)), rdr.GetDecimal(2), rdr.GetDateTime(3), rdr.GetString(4),rdr.GetDecimal(5));
rdr.Close();
return holding;
}
return null;
}
catch
{
throw;
}
}
public List<HoldingDataModel> getHoldings(string userID)
{
try
{
OracleParameter useridparm = new OracleParameter(PARM_USERID, OracleDbType.Varchar2, 20);
useridparm.Value = userID;
OracleDataReader rdr = OracleHelper.ExecuteReaderSingleParm(_internalConnection,_internalADOTransaction, CommandType.Text, SQL_SELECT_HOLDINGS, useridparm);
List<HoldingDataModel> holdings = new List<HoldingDataModel>();
while (rdr.Read())
{
HoldingDataModel holding = new HoldingDataModel(Convert.ToInt32(rdr.GetDecimal(0)), Convert.ToDouble(rdr.GetDecimal(1)),rdr.GetDecimal(2), rdr.GetDateTime(3), rdr.GetString(4), Convert.ToInt32(rdr.GetDecimal(5)),rdr.GetDecimal(6));
holdings.Add(holding);
}
rdr.Close();
return holdings;
}
catch
{
throw;
}
}
public HoldingDataModel getHoldingForUpdate(int orderID)
{
try
{
OracleParameter orderIDparm = new OracleParameter(PARM_ORDERID, OracleDbType.Int32);
orderIDparm.Value = orderID;
OracleDataReader rdr = OracleHelper.ExecuteReaderSingleRowSingleParm(_internalConnection, _internalADOTransaction, CommandType.Text, SQL_SELECT_HOLDING_LOCK, orderIDparm);
while (rdr.Read())
{
HoldingDataModel holding = new HoldingDataModel(Convert.ToInt32(rdr.GetDecimal(0)), Convert.ToInt32(rdr.GetDecimal(1)), Convert.ToDouble(rdr.GetDecimal(2)), rdr.GetDecimal(3), rdr.GetDateTime(4), rdr.GetString(5),rdr.GetDecimal(6));
rdr.Close();
return holding;
}
return null;
}
catch
{
throw;
}
}
/// <summary>
/// Sell the holding.
/// </summary>
/// <param name="order"></param>
/// <param name="holding"></param>
/// <returns></returns>
int sellHolding(OrderDataModel order, HoldingDataModel holding)
{
int holdingid = holding.holdingID;
//There are three distinct business cases here, each needs different treatment:
// a) Quantity requested is less than total shares in the holding -- update holding.
// b) Quantity requested is equal to total shares in the holding -- delete holding.
// c) Quantity requested is greater than total shares in the holding -- delete holding, update order table.
if (order.quantity < holding.quantity)
{
dalOrder.updateHolding(holdingid, order.quantity);
}
else
if (holding.quantity == order.quantity)
{
dalOrder.deleteHolding(holdingid);
}
else
//We now need to back-update the order record quantity to reflect
//fact not all shares originally requested were sold since the holding
//had less shares in it, perhaps due to other orders
//placed against that holding that completed before this one. So we will
//sell the remaining shares, but need to update the final order to reflect this.
if (order.quantity > holding.quantity)
{
dalOrder.deleteHolding(holdingid);
order.quantity = holding.quantity;
dalOrder.updateOrder(order);
}
return holdingid;
}
public HoldingDataModel getHolding(string userid, int holdingid)
{
try
{
SqlParameter[] holdingidparms = new SqlParameter[]{new SqlParameter(PARM_HOLDINGID, SqlDbType.Int),
new SqlParameter(PARM_USERID, SqlDbType.VarChar, 20)};
holdingidparms[0].Value = holdingid;
holdingidparms[1].Value = userid;
SqlDataReader rdr = SQLHelper.ExecuteReaderSingleRow(_internalConnection,_internalADOTransaction, CommandType.Text, SQL_SELECT_HOLDING_NOLOCK, holdingidparms);
while (rdr.Read())
{
HoldingDataModel holding = new HoldingDataModel(holdingid, rdr.GetInt32(0), rdr.GetDouble(1), rdr.GetDecimal(2), rdr.GetDateTime(3), rdr.GetString(4), rdr.GetDecimal(5));
rdr.Close();
return holding;
}
return null;
}
catch
{
throw;
}
}
/// <summary>
/// Business logic to create the order header.
/// The order header is always created synchronously by Trade; its the actual
/// processing of the order that can be done asynchrounously via the WCF service.
/// If, however, this service cannot communicate with the async order processor,
/// the order header is rolled back out of the database since we are wrapped in a tx here
/// either ADO.NET tx or System.TransactionScope as noted above, based on user setting.
/// </summary>
private OrderDataModel createOrder(string orderType, string userID, int holdingID, string symbol, double quantity, ref HoldingDataModel holding)
{
OrderDataModel order;
switch (orderType)
{
case StockTraderUtility.ORDER_TYPE_SELL:
{
holding = dalOrder.getHolding(holdingID);
if (holding == null)
throw new Exception(StockTraderUtility.EXCEPTION_MESSAGE_INVALID_HOLDING_NOT_FOUND);
order = dalOrder.createOrder(userID, holding.quoteID, StockTraderUtility.ORDER_TYPE_SELL, holding.quantity, holdingID);
break;
}
//StockTrader 5 allows users to sell part
//of a holding, not required to sell all shares at once. This business logic
//on the processing side of the pipe is more tricky. Have to check for
//conditions like another order coming in and the shares do not exist anymore
//in the holding, etc. This is not done here--it is done in the ProcessOrder class.
//Here we are merely creatingt he order header as with all orders--note its just the *quantity* variable
//that varies with SELL_ENHANCED case vs. SELL. SELL always uses the total
//number of shares in the holding to sell; here we have obtained from the
//requesting service (for example, the StockTrader Web App), how many shares
//the user actually wants to sell.
case StockTraderUtility.ORDER_TYPE_SELL_ENHANCED:
{
holding = dalOrder.getHolding(holdingID);
if (holding == null)
throw new Exception(StockTraderUtility.EXCEPTION_MESSAGE_INVALID_HOLDING_NOT_FOUND);
//If user requests to sell more shares than in holding, we will just invoke the core
//sell operation which liquidates/sells the entire holding. Seems logical--they will
//get proper notification based on the client order alert in the Web app how many
//were actually sold.
if (quantity > holding.quantity)
{
goto case StockTraderUtility.ORDER_TYPE_SELL;
}
else
{
order = dalOrder.createOrder(userID, holding.quoteID, StockTraderUtility.ORDER_TYPE_SELL, quantity, holdingID);
break;
}
}
case StockTraderUtility.ORDER_TYPE_BUY:
{
//Buys are easier business case! Especially when on unlimited margin accounts :-).
order = dalOrder.createOrder(userID, symbol, StockTraderUtility.ORDER_TYPE_BUY, quantity, -1);
break;
}
default:
throw new Exception(StockTraderUtility.EXCEPTION_MESSAGE_BADORDERTYPE);
}
return order;
}
/// <summary>
/// Business logic to synchrounously process the order within BSL layer, used with OrderMode="Sync."
/// </summary>
/// <param name="order">Order information model class with header info.</param>
/// <param name="holding">Holding model class with holding to sell if applicable.</param>
public void processOrderSync(OrderDataModel order, HoldingDataModel holding)
{
try
{
decimal total = 0;
int holdingid = -1;
QuoteDataModel quote = dalOrder.getQuoteForUpdate(order.symbol);
//Get the latest trading price--this is the money going into (or out of) the users account.
order.price = quote.price;
//Calculate order total, and create/update the Holding. Whole holding
//sells delete the holding, partials sells update the holding with new amount
//(and potentially the order too), and buys create a new holding.
if (order.orderType == StockTraderUtility.ORDER_TYPE_BUY)
{
holdingid = dalOrder.createHolding(order);
total = Convert.ToDecimal(order.quantity) * order.price + order.orderFee;
}
else
if (order.orderType == StockTraderUtility.ORDER_TYPE_SELL)
{
holdingid = sellHolding(order, holding);
total = -1 * Convert.ToDecimal(order.quantity) * order.price + order.orderFee;
}
//Debit/Credit User Account. Note, if we did not want to allow unlimited margin
//trading, we would change the ordering a bit and add the biz logic here to make
//sure the user has enough money to actually buy the shares they asked for!
//Now Update Account Balance.
dalOrder.updateAccountBalance(order.accountID, total);
//Update the stock trading volume and price in the quote table
dalOrder.updateStockPriceVolume(order.quantity, quote);
//Now perform our ACID tx test, if requested based on order type and acid stock symbols
if (order.symbol.Equals(StockTraderUtility.ACID_TEST_BUY) && order.orderType == StockTraderUtility.ORDER_TYPE_BUY)
throw new Exception(StockTraderUtility.EXCEPTION_MESSAGE_ACID_BUY);
else
if (order.symbol.Equals(StockTraderUtility.ACID_TEST_SELL) && order.orderType == StockTraderUtility.ORDER_TYPE_SELL)
throw new Exception(StockTraderUtility.EXCEPTION_MESSAGE_ACID_SELL);
//Finally, close the order
order.orderStatus = StockTraderUtility.ORDER_STATUS_CLOSED;
order.completionDate = DateTime.Now;
order.holdingID = holdingid;
dalOrder.closeOrder(order);
//Done!
return;
}
catch
{
throw;
}
}
/// <summary>
/// This is the business logic for placing orders, handling txs.
/// StockTrader allows the user to select whether to use System.Transactions
/// or use ADO.NET Provider-supplied transactions for order placement (buy, sell); and new user registrations.
/// The best choice for performance will vary based on the backend database being used with the
/// application.
/// </summary>
/// <param name="orderType">Buy or sell type.</param>
/// <param name="userID">User id to create/submit order for.</param>
/// <param name="holdingID">Holding id to sell.</param>
/// <param name="symbol">Stock symbol to buy.</param>
/// <param name="quantity">Shares to buy.</param>
public OrderDataModel placeOrder(string orderType, string userID, int holdingID, string symbol, double quantity)
{
OrderDataModel order = null;
HoldingDataModel holding = new HoldingDataModel();
switch (Settings.TRANSACTION_MODEL)
{
case (StockTraderUtility.TRANSACTION_MODEL_SYSTEMDOTTRANSACTION_TRANSACTION):
{
//This short try/catch block is introduced to deal with idle-timeout on SQL Azure
//connections. It may not be required in the near future, but as of publication
//SQL Azure disconnects idle connections after 30 minutes. While command retry-logic
//in the DAL automatically deals with this, when performing a tx, with the BSL handling
//tx boundaries, we want to go into the tx with known good connections. The try/catch below
//ensures this.
try
{
dalOrder = Trade.DALFactory.Order.Create(Settings.DAL);
dalOrder.Open(Settings.TRADEDB_SQL_CONN_STRING);
dalOrder.getSQLContextInfo();
}
catch { }
finally { dalOrder.Close(); }
System.Transactions.TransactionOptions txOps = new TransactionOptions();
txOps.IsolationLevel = System.Transactions.IsolationLevel.ReadCommitted;
txOps.Timeout = TimeSpan.FromSeconds(Settings.SYSTEMDOTTRANSACTION_TIMEOUT);
//Start our System.Transactions tx with the options set above.
using (TransactionScope tx = new TransactionScope(TransactionScopeOption.Required, txOps))
{
dalOrder.Open(Settings.TRADEDB_SQL_CONN_STRING);
try
{
//Business Step 1: create the order header.
order = createOrder(orderType, userID, holdingID, symbol, quantity, ref holding);
//Business Step 2: Determine which order processing mode to use,
//and either process order right away (sync) and in-process with
//calling ASP.NET Web app; or interface with the
//async WCF Order Processing Service (cooler) via a service-to-service call,
//distributed/remote.
if (Settings.ORDER_PROCESSING_MODE != StockTraderUtility.OPS_INPROCESS)
{
//Fire up our async client; we follow the same model here as with the
//StockTrader Web App in that we do not talk 'directly' to the generated proxy
//for the service; rather we channel all calls through a single
//class that then talks to the service proxy. This will aid in more
//easily knowing where communication and proxy logic sits; and make changes to services
//you might want to interface with vs. littering proxy calls throughout the
//business tier itself.
TradeOrderServiceAsyncClient asyncclient = new TradeOrderServiceAsyncClient(Settings.ORDER_PROCESSING_MODE, new Settings());
asyncclient.processOrderASync(order);
}
else
{
processOrderSync(order, holding);
}
//Commit!
tx.Complete();
return order;
}
//If per chance you are doing step-through debugging through here and are getting a
// "TRANSACTION HAS ABORTED" exception and do not know why,
//it's quite likely you are hitting the 15-second timeout we set in
//ConfigurationSettings for the System.Transaction options so its just doing what we told it to.
//Simply adjust timeout higher, recompile if you need to.
catch
{
throw;
}
finally
{
dalOrder.Close();
}
}
}
//Repeat for ADO.NET transactions config option case.
case (StockTraderUtility.TRANSACTION_MODEL_ADONET_TRANSACTION):
{
if (Settings.ORDER_PROCESSING_MODE == StockTraderUtility.OPS_SELF_HOST_MSMQ)
goto case StockTraderUtility.TRANSACTION_MODEL_SYSTEMDOTTRANSACTION_TRANSACTION;
dalOrder.Open(Settings.TRADEDB_SQL_CONN_STRING);
dalOrder.BeginADOTransaction();
try
{
//Business Step 1: create the order header.
order = createOrder(orderType, userID, holdingID, symbol, quantity, ref holding);
//Business Step 2: Determine which order processing mode to use,
//and either process order right away (sync); or interface with the
//async WCF Order Processing Service (cooler) via a service-to-service call.
if (Settings.ORDER_PROCESSING_MODE != StockTraderUtility.OPS_INPROCESS)
{
//Fire up our async client; we follow the same model here as with the
//StockTrader Web App in that we do not talk 'directly' to the generated proxy
//for the service; rather we channel all calls through a single
//class that then talks to the service proxy. This will aid in more
//easily knowing where communication and proxy logic sits; and make changes to services
//you might want to interface with vs. littering proxy calls throughout the
//business tier itself.
TradeOrderServiceAsyncClient asyncclient = new TradeOrderServiceAsyncClient(Settings.ORDER_PROCESSING_MODE, new Settings());
asyncclient.processOrderASync(order);
dalOrder.CommitADOTransaction();
}
else
{
processOrderSync(order, holding);
}
dalOrder.CommitADOTransaction();
return order;
}
catch
{
dalOrder.RollBackTransaction();
throw;
}
finally
{
dalOrder.Close();
}
}
}
throw new Exception(Settings.ENABLE_GLOBAL_SYSTEM_DOT_TRANSACTIONS_CONFIGSTRING + ": " + StockTraderUtility.EXCEPTION_MESSAGE_INVALID_TXMMODEL_CONFIG + " Repository ConfigKey table.");
}
public void updateHolding(int holdingid, double quantity)
{
try
{
SqlParameter[] HoldingParms2 = GetUpdateHoldingParameters();
HoldingParms2[0].Value = holdingid;
HoldingParms2[1].Value = quantity;
SQLHelper.ExecuteNonQuery(_internalConnection, _internalADOTransaction, CommandType.Text, SQL_UPDATE_HOLDING, HoldingParms2);
HoldingDataModel holding = new HoldingDataModel();
return;
}
catch
{
throw;
}
}
public HoldingDataModel getHolding(int holdingID)
{
SqlParameter parm1 = new SqlParameter(PARM_HOLDINGID, SqlDbType.Int, 10);
parm1.Value = holdingID;
SqlDataReader rdr = SQLHelper.ExecuteReaderSingleRowSingleParm(_internalConnection, _internalADOTransaction, CommandType.Text, SQL_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.GetDecimal(6));
rdr.Close();
return holding;
}
rdr.Close();
return null;
}
public void updateHolding(int holdingid, double quantity)
{
try
{
OracleParameter[] HoldingParms2 = GetUpdateHoldingParameters();
HoldingParms2[0].Value = holdingid;
string sql = String.Format(SQL_UPDATE_HOLDING, quantity.ToString());
OracleHelper.ExecuteNonQuery(_internalConnection, _internalADOTransaction, CommandType.Text, sql, HoldingParms2);
HoldingDataModel holding = new HoldingDataModel();
return;
}
catch
{
throw;
}
}
public HoldingDataModel getHolding(int holdingID)
{
OracleParameter parm1 = new OracleParameter(PARM_HOLDINGID, OracleDbType.Int32, 10);
parm1.Value = holdingID;
OracleDataReader rdr = OracleHelper.ExecuteReaderSingleRowSingleParm(_internalConnection, _internalADOTransaction, CommandType.Text, SQL_SELECT_HOLDING, parm1);
if (rdr.Read())
{
HoldingDataModel holding = new HoldingDataModel(Convert.ToInt32(rdr.GetDecimal(0)), Convert.ToDouble(rdr.GetDecimal(1)), rdr.GetDecimal(2), rdr.GetDateTime(3), rdr.GetString(4), Convert.ToInt32(rdr.GetDecimal(5)), rdr.GetDecimal(6));
rdr.Close();
return holding;
}
rdr.Close();
return null;
}
