//
// 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;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlTypes;
using System.Data.SqlClient;
using Trade.BusinessServiceDataContract;
using Trade.IDAL;
using Trade.Utility;



namespace Trade.DALSQLServer
{
    public class Customer : ICustomer
    {
        public Customer()
        {
        }

        //Constructor for internal DAL-DAL calls to use an existing DB connection.
        public Customer(SqlConnection conn, SqlTransaction trans)
        {
            _internalConnection = conn;
            _internalADOTransaction = trans;
        }

        private SqlConnection _internalConnection;
        private SqlTransaction _internalADOTransaction = null;

        //Used only when doing ADO.NET transactions.
        //This will be completely ignored when null, and not attached to a cmd object
        //In SQLHelper 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.
        public void BeginADOTransaction()
        {
            if (_internalConnection.State != ConnectionState.Open)
                _internalConnection.Open();
            _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 SqlConnection(connString);
            if (_internalConnection.State != ConnectionState.Open)
                _internalConnection.Open();
        }

        public void Close()
        {
            if (_internalConnection != null && _internalConnection.State != ConnectionState.Closed)
                _internalConnection.Close();
        }

        //This would be the more efficient way to get holding data with quote price part of the model class/mapping, but do not use to maintain compatibility with Trade 6.1.  While
        //this query is easy with ADO.NET (or JDBC); it would be very problematic with EJB Entity Beans to do efficiently.  
        //private const string SQL_SELECT_HOLDINGS = "SELECT HOLDING.HOLDINGID, HOLDING.QUANTITY, HOLDING.PURCHASEPRICE, HOLDING.PURCHASEDATE, HOLDING.QUOTE_SYMBOL,HOLDING.ACCOUNT_ACCOUNTID, " +
        //                                           "QUOTE.PRICE FROM dbo.ACCOUNT INNER JOIN dbo.HOLDING ON ACCOUNT.ACCOUNTID = HOLDING.ACCOUNT_ACCOUNTID INNER JOIN " +
        //                                           "dbo.QUOTE ON HOLDING.QUOTE_SYMBOL = QUOTE.SYMBOL WHERE (ACCOUNT.PROFILE_USERID = @UserId) ORDER BY HOLDING.HOLDINGID DESC";

        private const string SQL_SELECT_HOLDINGS = @"SELECT HOLDING.HOLDINGID, HOLDING.QUANTITY, HOLDING.PURCHASEPRICE, HOLDING.PURCHASEDATE, HOLDING.QUOTE_SYMBOL,HOLDING.ACCOUNT_ACCOUNTID 
                                                    from dbo.holding WHERE HOLDING.ACCOUNT_ACCOUNTID = (SELECT ACCOUNTID FROM ACCOUNT WHERE PROFILE_USERID = @UserId) ORDER BY HOLDING.HOLDINGID DESC";
        private const string SQL_SELECT_HOLDING_LOCK = @"Set NOCOUNT ON; SELECT dbo.HOLDING.HOLDINGID, HOLDING.ACCOUNT_ACCOUNTID, HOLDING.QUANTITY, HOLDING.PURCHASEPRICE, HOLDING.PURCHASEDATE,
                                                       HOLDING.QUOTE_SYMBOL FROM dbo.HOLDING WITH (ROWLOCK) INNER JOIN ORDERS ON HOLDING.HOLDINGID = ORDERS.HOLDING_HOLDINGID
                                                       WHERE (ORDERS.ORDERID = @OrderId)";
        private const string SQL_SELECT_HOLDING_NOLOCK = "Set NOCOUNT ON; SELECT HOLDING.ACCOUNT_ACCOUNTID, HOLDING.QUANTITY, HOLDING.PURCHASEPRICE, HOLDING.PURCHASEDATE, HOLDING.QUOTE_SYMBOL FROM HOLDING WITH(NOLOCK) WHERE HOLDING.HOLDINGID=@holdingId AND HOLDING.ACCOUNT_ACCOUNTID = (SELECT ACCOUNTID FROM dbo.ACCOUNT WHERE PROFILE_USERID = @UserId)";
        private const string SQL_SELECT_GET_CUSTOMER_BYUSERID = "Set NOCOUNT ON; SELECT account.ACCOUNTID, account.PROFILE_USERID, account.CREATIONDATE, account.OPENBALANCE, account.LOGOUTCOUNT, account.BALANCE, account.LASTLOGIN, account.LOGINCOUNT FROM account WHERE account.PROFILE_USERID = @UserId";
        private const string SQL_SELECT_CUSTOMERPROFILE_BYUSERID = "Set NOCOUNT ON; SELECT accountprofile.USERID, accountprofile.PASSWORD, accountprofile.FULLNAME, accountprofile.ADDRESS, accountprofile.EMAIL, accountprofile.CREDITCARD FROM dbo.accountprofile WITH (NOLOCK) WHERE accountprofile.USERID = @UserId";
        private const string SQL_SELECT_UPDATE_CUSTOMER_LOGIN = "UPDATE dbo.account WITH (ROWLOCK) SET LOGINCOUNT = (LOGINCOUNT + 1), LASTLOGIN = CURRENT_TIMESTAMP where PROFILE_USERID= @UserId; SELECT account.ACCOUNTID, account.CREATIONDATE, account.OPENBALANCE, account.LOGOUTCOUNT, account.BALANCE, account.LASTLOGIN, account.LOGINCOUNT FROM dbo.account WITH (ROWLOCK) WHERE account.PROFILE_USERID = @UserId";
        private const string SQL_UPDATE_LOGOUT = "UPDATE dbo.account WITH (ROWLOCK) SET LOGOUTCOUNT = (LOGOUTCOUNT + 1) where PROFILE_USERID= @UserId";
        private const string SQL_UPDATE_ACCOUNTPROFILE = "UPDATE dbo.accountprofile WITH (ROWLOCK) SET ADDRESS=@Address, PASSWORD=@Password, EMAIL=@Email, CREDITCARD = @CreditCard, FULLNAME=@FullName WHERE USERID= @UserId";
        private const string SQL_SELECT_CLOSED_ORDERS = "Set NOCOUNT ON; SELECT ORDERID, ORDERTYPE, ORDERSTATUS, COMPLETIONDATE, OPENDATE, QUANTITY, PRICE, ORDERFEE, QUOTE_SYMBOL FROM dbo.orders WHERE ACCOUNT_ACCOUNTID = (select accountid from dbo.account WITH(NOLOCK) where profile_userid =@UserId) AND ORDERSTATUS = 'closed'";
        private const string SQL_UPDATE_CLOSED_ORDERS = "UPDATE dbo.orders SET ORDERSTATUS = 'completed' where ORDERSTATUS = 'closed' AND ACCOUNT_ACCOUNTID = (select accountid from dbo.account WITH (NOLOCK) where profile_userid =@UserId)";
        private const string SQL_SELECT_ORDERS_BY_ID = " o.ORDERID, o.ORDERTYPE, o.ORDERSTATUS, o.OPENDATE, o.COMPLETIONDATE, o.QUANTITY, o.PRICE, o.ORDERFEE, o.QUOTE_SYMBOL from dbo.orders o where o.account_accountid = (select a.accountid from dbo.account a WITH (NOLOCK)  where a.profile_userid = @UserId) ORDER BY o.ORDERID DESC";
        private const string SQL_INSERT_ACCOUNTPROFILE = "INSERT INTO dbo.accountprofile VALUES (@Address, @Password, @UserId, @Email, @CreditCard, @FullName)";
        private const string SQL_INSERT_ACCOUNT = "INSERT INTO dbo.account (CREATIONDATE, OPENBALANCE, LOGOUTCOUNT, BALANCE, LASTLOGIN, LOGINCOUNT, PROFILE_USERID) VALUES (GetDate(), @OpenBalance, @LogoutCount, @Balance, @LastLogin, @LoginCount, @UserId); SELECT ID=@@IDENTITY";
        private const string SQL_DEBIT_ACCOUNT = "UPDATE dbo.ACCOUNT WITH (ROWLOCK) SET BALANCE=(BALANCE-@Debit) WHERE ACCOUNTID=@AccountId";
        private const string SQL_SELECT_CUSTOMERPROFILE_BYSTSID = "Set NOCOUNT ON; SELECT ACCOUNTPROFILE.UserID FROM dbo.ACCOUNT, dbo.ACCOUNTPROFILE, dbo.STSACCOUNTLOOKUP WITH (NOLOCK) WHERE ACCOUNT.PROFILE_USERID = ACCOUNTPROFILE.USERID and ACCOUNT.ACCOUNTID = STSACCOUNTLOOKUP.UserID and STSACCOUNTLOOKUP.StsUniqueIdentifier = @StsUniqueIdentifier and STSACCOUNTLOOKUP.IssuingSts = @IssuingSts";
 
        //Parameters
        private const string PARM_USERID = "@UserId";
        private const string PARM_HOLDINGID = "@holdingId";
        private const string PARM_ORDERID = "@OrderId";
        private const string PARM_ACCOUNTID = "@accountId";
        private const string PARM_PASSWORD = "@Password";
        private const string PARM_FULLNAME = "@FullName";
        private const string PARM_ADDRESS = "@Address";
        private const string PARM_EMAIL = "@Email";
        private const string PARM_CREDITCARD = "@CreditCard";
        private const string PARM_OPENBALANCE = "@OpenBalance";
        private const string PARM_LOGOUTCOUNT = "@LogoutCount";
        private const string PARM_BALANCE = "@Balance";
        private const string PARM_LASTLOGIN = "@LastLogin";
        private const string PARM_LOGINCOUNT = "@LoginCount";
        private const string PARM_TOPORDERS = "@TopOrders";
        private const string PARM_DEBIT = "@Debit";
        private const string PARM_STSUNIQUEID = "@StsUniqueIdentifier";
        private const string PARM_ISSUINGSTS = "@IssuingSts";

        public List<OrderDataModel> getOrders(string userID, bool top, int maxTop, int maxDefault)
        {

            //Here you can configure between two settings: top default to display
            //is MAX_DISPLAY_ORDERS; the upper limit is MAX_DISPLAY_TOP_ORDERS.
            //Set these in Web.Config/Trade.BusinessServiceHost.exe.config; and those will be the toggle 
            //choices for the user in the Account.aspx page.
            try
            {
                string commandText;
                if (top)
                {
                    commandText = "Select Top " + maxTop.ToString() + SQL_SELECT_ORDERS_BY_ID;
                }
                else
                {
                    commandText = "Select Top " + maxDefault.ToString() + SQL_SELECT_ORDERS_BY_ID;
                }
                SqlParameter accountidparm = new SqlParameter(PARM_USERID, SqlDbType.VarChar, 20);
                accountidparm.Value = userID;
                SqlDataReader rdr = SQLServerHelper.ExecuteReaderSingleParm(_internalConnection, _internalADOTransaction, CommandType.Text, commandText, accountidparm);
                List<OrderDataModel> orders = new List<OrderDataModel>();
                while (rdr.Read())
                {
                    int orderid = rdr.GetInt32(0);
                    DateTime openDate = (DateTime)rdr.GetDateTime(3);
                    Object completionDate = null;
                    //can be null
                    try
                    {
                        if (!Convert.IsDBNull(rdr.GetDateTime(4)))
                            completionDate = rdr.GetDateTime(4);
                        else
                            completionDate = DateTime.MinValue;
                    }
                    catch (Exception e)
                    {
                        string message = e.Message;
                        completionDate = DateTime.MinValue;
                    }
                    OrderDataModel order = new OrderDataModel(orderid, rdr.GetString(1), rdr.GetString(2), openDate, (DateTime)completionDate, rdr.GetDouble(5), rdr.GetDecimal(6), rdr.GetDecimal(7), rdr.GetString(8));
                    orders.Add(order);
                }
                rdr.Close();
                return orders;
            }
            catch
            {
                throw;
            }
        }

        public String getProfileIdFromStsIdentifier(string uniqueId, string issuingSts)
        {
            try
            {
                SqlParameter parm1 = new SqlParameter(PARM_STSUNIQUEID, SqlDbType.VarChar, 250);
                parm1.Value = uniqueId;
                SqlParameter parm2 = new SqlParameter(PARM_ISSUINGSTS, SqlDbType.VarChar, 250);
                parm2.Value = issuingSts;

                SqlParameter[] parms = new SqlParameter[] { parm1, parm2 };
                SqlDataReader rdr = SQLServerHelper.ExecuteReaderSingleRow(_internalConnection, _internalADOTransaction, CommandType.Text, SQL_SELECT_CUSTOMERPROFILE_BYSTSID, parms);
                if (rdr.Read())
                {
                    string profileId = rdr.GetString(0);
                    rdr.Close();
                    return profileId;
                }
                return null;
            }
            catch
            {
                throw;
            }
        }

        public AccountDataModel login(string userid, string password)
        {
            try
            {
                SqlParameter parm1 = new SqlParameter(PARM_USERID, SqlDbType.VarChar, 20);
                parm1.Value = userid;
                SqlDataReader rdr = SQLServerHelper.ExecuteReaderSingleRowSingleParm(_internalConnection, _internalADOTransaction, CommandType.Text, SQL_SELECT_CUSTOMERPROFILE_BYUSERID, parm1);
                if (rdr.Read())
                {
                    string userPassword = rdr.GetString(1);
                    rdr.Close();
                    if (userPassword.Equals(password))
                    {
                        SqlParameter profileparm1 = new SqlParameter(PARM_USERID, SqlDbType.VarChar, 20);
                        profileparm1.Value = userid;
                        rdr = SQLServerHelper.ExecuteReaderSingleRowSingleParm(_internalConnection, _internalADOTransaction, CommandType.Text, SQL_SELECT_UPDATE_CUSTOMER_LOGIN, profileparm1);
                        rdr.Read();
                        AccountDataModel customer = new AccountDataModel(rdr.GetInt32(0), userid, rdr.GetDateTime(1), rdr.GetDecimal(2), rdr.GetInt32(3), rdr.GetDecimal(4), rdr.GetDateTime(5), rdr.GetInt32(6) + 1);
                        rdr.Close();
                        return customer;
                    }
                    rdr.Close();
                }
                return null;
            }
            catch
            {
                throw;
            }
        }

        public AccountProfileDataModel getAccountProfileData(string userid)
        {
            try
            {
                SqlParameter parm1 = new SqlParameter(PARM_USERID, SqlDbType.VarChar, 20);
                parm1.Value = userid;
                SqlDataReader rdr = SQLServerHelper.ExecuteReaderSingleRowSingleParm(_internalConnection, _internalADOTransaction, CommandType.Text, SQL_SELECT_CUSTOMERPROFILE_BYUSERID, parm1);
                if (rdr.Read())
                {
                    AccountProfileDataModel customerprofile = new AccountProfileDataModel(rdr.GetString(0), rdr.GetString(1), rdr.GetString(2), rdr.GetString(3), rdr.GetString(4), rdr.GetString(5));
                    rdr.Close();
                    return customerprofile;
                }
                rdr.Close();
                return null;
            }
            catch
            {
                throw;
            }
        }

        public void logOutUser(string userID)
        {
            try
            {
                SqlParameter parm1 = new SqlParameter(PARM_USERID, SqlDbType.VarChar, 20);
                parm1.Value = userID;
                SQLServerHelper.ExecuteNonQuery(_internalConnection, _internalADOTransaction, CommandType.Text, SQL_UPDATE_LOGOUT, parm1);
                return;
            }
            catch
            {
                throw;
            }
        }

        public AccountDataModel getCustomerByUserID(string userID)
        {
            try
            {
                SqlParameter parm1 = new SqlParameter(PARM_USERID, SqlDbType.VarChar, 20);
                parm1.Value = userID;
                SqlDataReader rdr = SQLServerHelper.ExecuteReaderSingleRowSingleParm(_internalConnection, _internalADOTransaction, CommandType.Text, SQL_SELECT_GET_CUSTOMER_BYUSERID, parm1);
                if (rdr.Read())
                {
                    AccountDataModel customer = new AccountDataModel(rdr.GetInt32(0), rdr.GetString(1), rdr.GetDateTime(2), rdr.GetDecimal(3), rdr.GetInt32(4), rdr.GetDecimal(5), rdr.GetDateTime(6), rdr.GetInt32(7));
                    rdr.Close();
                    return customer;
                }
                rdr.Close();
                return null;
            }
            catch
            {
                throw;
            }
        }

        public List<OrderDataModel> getClosedOrders(string userId)
        {
            try
            {
                SqlParameter useridparm = new SqlParameter(PARM_USERID, SqlDbType.VarChar, 20);
                useridparm.Value = userId;
                SqlDataReader rdr = SQLServerHelper.ExecuteReaderSingleParm(_internalConnection, _internalADOTransaction, CommandType.Text, SQL_SELECT_CLOSED_ORDERS, useridparm);
                List<OrderDataModel> closedorders = new List<OrderDataModel>();
                DateTime completionDate = DateTime.MinValue;
                while (rdr.Read())
                {
                    int orderid = rdr.GetInt32(0);
                    DateTime openDate = (DateTime)rdr.GetDateTime(4);
                    try
                    {
                        completionDate = (DateTime)rdr.GetDateTime(3);
                    }
                    catch (Exception e) { if (e.Message.Equals("Data is Null. This method or property cannot be called on Null values.")) completionDate = DateTime.MinValue; }
                    OrderDataModel order = new OrderDataModel(orderid, rdr.GetString(1), rdr.GetString(2), openDate, completionDate, rdr.GetDouble(5), rdr.GetDecimal(6), rdr.GetDecimal(7), rdr.GetString(8));
                    order.orderStatus = StockTraderUtility.ORDER_STATUS_COMPLETED;
                    closedorders.Add(order);
                }
                if (rdr.HasRows)
                {
                    rdr.Close();
                    useridparm = new SqlParameter(PARM_USERID, SqlDbType.VarChar, 20);
                    useridparm.Value = userId;
                    SQLServerHelper.ExecuteNonQuerySingleParm(_internalConnection, _internalADOTransaction, CommandType.Text, SQL_UPDATE_CLOSED_ORDERS, useridparm);
                }
                else
                    rdr.Close();
                return closedorders;
            }
            catch
            {
                throw;
            }
        }

        public List<HoldingDataModel> getHoldings(string userID)
        {
            try
            {
                SqlParameter useridparm = new SqlParameter(PARM_USERID, SqlDbType.VarChar, 20);
                useridparm.Value = userID;
                SqlDataReader rdr = SQLServerHelper.ExecuteReaderSingleParm(_internalConnection, _internalADOTransaction, CommandType.Text, SQL_SELECT_HOLDINGS, useridparm);
                List<HoldingDataModel> holdings = new List<HoldingDataModel>();
                while (rdr.Read())
                {
                    HoldingDataModel holding = new HoldingDataModel(rdr.GetInt32(0), rdr.GetDouble(1), rdr.GetDecimal(2), rdr.GetDateTime(3), rdr.GetString(4), rdr.GetInt32(5));
                    holdings.Add(holding);
                }
                rdr.Close();
                return holdings;
            }
            catch
            {
                throw;
            }
        }

        public HoldingDataModel getHoldingForUpdate(int orderID)
        {
            try
            {
                SqlParameter orderIDparm = new SqlParameter(PARM_ORDERID, SqlDbType.Int);
                orderIDparm.Value = orderID;
                SqlDataReader rdr = SQLServerHelper.ExecuteReaderSingleRowSingleParm(_internalConnection, _internalADOTransaction, CommandType.Text, SQL_SELECT_HOLDING_LOCK, orderIDparm);
                while (rdr.Read())
                {
                    HoldingDataModel holding = new HoldingDataModel(rdr.GetInt32(0), rdr.GetInt32(1), rdr.GetDouble(2), rdr.GetDecimal(3), rdr.GetDateTime(4), rdr.GetString(5));
                    rdr.Close();
                    return holding;
                }
                return null;
            }
            catch
            {
                throw;
            }
        }

        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 = SQLServerHelper.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.Close();
                    return holding;
                }
                return null;
            }
            catch
            {
                throw;
            }
        }

        public void updateAccountBalance(int accountID, decimal total)
        {
            try
            {
                // Get the parameters from the cache
                SqlParameter[] accountParms = GetUpdateAccountBalanceParameters();
                accountParms[0].Value = total;
                accountParms[1].Value = accountID;
                SQLServerHelper.ExecuteNonQuery(_internalConnection, _internalADOTransaction, CommandType.Text, SQL_DEBIT_ACCOUNT, accountParms);
            }
            catch
            {
                throw;
            }
            return;
        }

        public void insertAccount(AccountDataModel customer)
        {
            // Get the parameters from the cache
            SqlParameter[] AccountParms = GetCreateAccountParameters();
            try
            {
                AccountParms[0].Value = customer.openBalance;
                AccountParms[1].Value = customer.logoutCount;
                AccountParms[2].Value = customer.balance;
                AccountParms[3].Value = customer.lastLogin;
                AccountParms[4].Value = customer.loginCount;
                AccountParms[5].Value = customer.profileID;
                customer.accountID = Convert.ToInt32(SQLServerHelper.ExecuteScalar(_internalConnection, _internalADOTransaction, CommandType.Text, SQL_INSERT_ACCOUNT, AccountParms));
                return;
            }
            catch
            {
                throw;
            }
        }

        public void insertAccountProfile(AccountProfileDataModel customerprofile)
        {
            // Get the paramters from the cache
            SqlParameter[] ProfileParms = GetCreateAccountProfileParameters();
            try
            {
                ProfileParms[0].Value = customerprofile.address;
                ProfileParms[1].Value = customerprofile.password;
                ProfileParms[2].Value = customerprofile.userID;
                ProfileParms[3].Value = customerprofile.email;
                ProfileParms[4].Value = customerprofile.creditCard;
                ProfileParms[5].Value = customerprofile.fullName;
                SQLServerHelper.ExecuteNonQuery(_internalConnection, _internalADOTransaction, CommandType.Text, SQL_INSERT_ACCOUNTPROFILE, ProfileParms);
            }
            catch
            {
                throw;
            }
        }

        public AccountProfileDataModel update(AccountProfileDataModel customerprofile)
        {
            try
            {
                // Get the paramters from the cache
                SqlParameter[] ProfileParms = GetUpdateAccountProfileParameters();
                ProfileParms[0].Value = customerprofile.address;
                ProfileParms[1].Value = customerprofile.password;
                ProfileParms[2].Value = customerprofile.email;
                ProfileParms[3].Value = customerprofile.creditCard;
                ProfileParms[4].Value = customerprofile.fullName;
                ProfileParms[5].Value = customerprofile.userID;
                SQLServerHelper.ExecuteNonQuery(_internalConnection, _internalADOTransaction, CommandType.Text, SQL_UPDATE_ACCOUNTPROFILE, ProfileParms);
                return customerprofile;
            }
            catch
            {
                throw;
            }
        }

        private static SqlParameter[] GetUpdateAccountBalanceParameters()
        {
            // Get the paramters from the cache
            SqlParameter[] parms = SQLServerHelper.GetCacheParameters(SQL_DEBIT_ACCOUNT);
            // If the cache is empty, rebuild the parameters
            if (parms == null)
            {
                parms = new SqlParameter[] {new SqlParameter(PARM_DEBIT, SqlDbType.Decimal, 14),
                                            new SqlParameter(PARM_ACCOUNTID, SqlDbType.Int)};
                // Add the parameters to the cached
                SQLServerHelper.CacheParameters(SQL_DEBIT_ACCOUNT, parms);
            }
            return parms;
        }

        private static SqlParameter[] GetCreateAccountProfileParameters()
        {
            // Get the parameters from the cache
            SqlParameter[] parms = SQLServerHelper.GetCacheParameters(SQL_INSERT_ACCOUNTPROFILE);
            // If the cache is empty, rebuild the parameters
            if (parms == null)
            {
                parms = new SqlParameter[] {
        			  new SqlParameter(PARM_ADDRESS, SqlDbType.VarChar, StockTraderUtility.ADDRESS_MAX_LENGTH),
		              new SqlParameter(PARM_PASSWORD, SqlDbType.VarChar, StockTraderUtility.PASSWORD_MAX_LENGTH),
                      new SqlParameter(PARM_USERID, SqlDbType.VarChar, StockTraderUtility.USERID_MAX_LENGTH),
                      new SqlParameter(PARM_EMAIL, SqlDbType.VarChar, StockTraderUtility.EMAIL_MAX_LENGTH),
                      new SqlParameter(PARM_CREDITCARD, SqlDbType.VarChar, StockTraderUtility.CREDITCARD_MAX_LENGTH),
                      new SqlParameter(PARM_FULLNAME, SqlDbType.VarChar, StockTraderUtility.FULLNAME_MAX_LENGTH)};

                // Add the parametes to the cached
                SQLServerHelper.CacheParameters(SQL_INSERT_ACCOUNTPROFILE, parms);
            }
            return parms;
        }

        private static SqlParameter[] GetUpdateAccountProfileParameters()
        {
            // Get the parameters from the cache
            SqlParameter[] parms = SQLServerHelper.GetCacheParameters(SQL_UPDATE_ACCOUNTPROFILE);
            // If the cache is empty, rebuild the parameters
            if (parms == null)
            {
                parms = new SqlParameter[] {
            		  new SqlParameter(PARM_ADDRESS, SqlDbType.VarChar, StockTraderUtility.ADDRESS_MAX_LENGTH),
					  new SqlParameter(PARM_PASSWORD, SqlDbType.VarChar, StockTraderUtility.PASSWORD_MAX_LENGTH),
                      new SqlParameter(PARM_EMAIL, SqlDbType.VarChar, StockTraderUtility.EMAIL_MAX_LENGTH),
                      new SqlParameter(PARM_CREDITCARD, SqlDbType.VarChar, StockTraderUtility.CREDITCARD_MAX_LENGTH),
                      new SqlParameter(PARM_FULLNAME, SqlDbType.VarChar, StockTraderUtility.FULLNAME_MAX_LENGTH),
                      new SqlParameter(PARM_USERID, SqlDbType.VarChar, StockTraderUtility.USERID_MAX_LENGTH)};

                // Add the parametes to the cached
                SQLServerHelper.CacheParameters(SQL_UPDATE_ACCOUNTPROFILE, parms);
            }
            return parms;
        }

        private static SqlParameter[] GetCreateAccountParameters()
        {
            // Get the parameters from the cache
            SqlParameter[] parms = SQLServerHelper.GetCacheParameters(SQL_INSERT_ACCOUNT);
            // If the cache is empty, rebuild the parameters
            if (parms == null)
            {
                parms = new SqlParameter[] {
					  new SqlParameter(PARM_OPENBALANCE, SqlDbType.Decimal),
                      new SqlParameter(PARM_LOGOUTCOUNT, SqlDbType.Int),
                      new SqlParameter(PARM_BALANCE, SqlDbType.Decimal),
                      new SqlParameter(PARM_LASTLOGIN, SqlDbType.DateTime),
                      new SqlParameter(PARM_LOGINCOUNT, SqlDbType.Int),
                      new SqlParameter(PARM_USERID, SqlDbType.VarChar, StockTraderUtility.USERID_MAX_LENGTH)};

                // Add the parameters to the cached
                SQLServerHelper.CacheParameters(SQL_INSERT_ACCOUNT, parms);
            }
            return parms;
        }
    }
}