public void insertAccount(AccountDataModel customer)
{
// Get the parameters from the cache
OracleParameter[] AccountParms = GetCreateAccountParameters();
customer.accountID = Convert.ToInt32(OracleHelper.ExecuteScalarNoParm(_internalConnection, _internalADOTransaction,CommandType.Text,SQL_GET_NEXT_ACCOUNT_SEQ));
try
{
AccountParms[0].Value = customer.accountID;
AccountParms[1].Value = customer.openBalance;
AccountParms[2].Value = customer.logoutCount;
AccountParms[3].Value = customer.balance;
AccountParms[4].Value = customer.lastLogin;
AccountParms[5].Value = customer.loginCount;
AccountParms[6].Value = customer.profileID;
OracleHelper.ExecuteNonQuery(_internalConnection, _internalADOTransaction, CommandType.Text, SQL_INSERT_ACCOUNT, AccountParms);
return;
}
catch
{
throw;
}
}
public AccountDataModel getCustomerByUserID(string userID)
{
try
{
OracleParameter parm1 = new OracleParameter(PARM_USERID, OracleDbType.Varchar2, 20);
parm1.Value = userID;
OracleDataReader rdr = OracleHelper.ExecuteReaderSingleRowSingleParm(_internalConnection, _internalADOTransaction, CommandType.Text, SQL_SELECT_GET_CUSTOMER_BYUSERID, parm1);
if (rdr.Read())
{
AccountDataModel customer = new AccountDataModel(Convert.ToInt32(rdr.GetDecimal(0)), rdr.GetString(1), rdr.GetDateTime(2), rdr.GetDecimal(3), Convert.ToInt32(rdr.GetDecimal(4)), rdr.GetDecimal(5), rdr.GetDateTime(6), Convert.ToInt32(rdr.GetDecimal(7)));
rdr.Close();
return customer;
}
rdr.Close();
return null;
}
catch
{
throw;
}
}
/// <summary>
/// Adds user account data to Account table and also profile data to AccountProfile table.
/// </summary>
/// <param name="userID"></param>
/// <param name="password"></param>
/// <param name="fullname"></param>
/// <param name="address"></param>
/// <param name="email"></param>
/// <param name="creditcard"></param>
/// <param name="openBalance"></param>
/// <returns></returns>
private AccountDataModel addNewRegisteredUser(string userID, string password, string fullname, string address, string email, string creditcard, decimal openBalance)
{
AccountProfileDataModel customerprofile = new AccountProfileDataModel(userID, password, fullname, address, email, creditcard);
dalCustomer.insertAccountProfile(customerprofile, Settings.USE_SALTEDHASH_PASSWORDS);
//Check our acid test conditions here for transactional testing; we want to test part way through
//the register operations from the BSL, to make sure database is never left in state with one
//insert above going through, and the one below not--the entire BSL operation needs to be
//treated as one logical unit of work. Also note the ordering of operations here:
//since trying to register a non-unique userid might be something that happens frequently in the real
//world, lets do the insert that would fail on this condition first (accountprofile);
//rather than wait and do it last.
if (customerprofile.userID.Equals(StockTraderUtility.ACID_TEST_USER))
throw new Exception(StockTraderUtility.EXCEPTION_MESSAGE_ACID_REGISTRATION);
AccountDataModel customer = new AccountDataModel(0, userID, DateTime.Now, (decimal)openBalance, 0, (decimal)openBalance, DateTime.Now, 0);
dalCustomer.insertAccount(customer);
return customer;
}
public AccountDataModel login(string userid, string password, bool useSaltedHash)
{
try
{
OracleParameter parm1 = new OracleParameter(PARM_USERID, OracleDbType.Varchar2, 20);
parm1.Value = userid;
OracleDataReader rdr = OracleHelper.ExecuteReaderSingleRowSingleParm(_internalConnection, _internalADOTransaction, CommandType.Text,SQL_SELECT_CUSTOMERPROFILE_BYUSERID, parm1);
if (rdr.Read())
{
string salt = rdr.GetString(1);
string userPassword = rdr.GetString(2);
rdr.Close();
bool valid = false;
if (useSaltedHash)
{
SaltedHash ver = SaltedHash.Create(salt, userPassword);
valid = ver.Verify(password);
}
else
{
if (password.Equals(userPassword))
valid = true;
}
rdr.Close();
if (valid)
{
OracleParameter profileparm1 = new OracleParameter(PARM_USERID, OracleDbType.Varchar2, 20);
profileparm1.Value = userid;
rdr = OracleHelper.ExecuteReaderSingleRowSingleParm(_internalConnection, _internalADOTransaction, CommandType.Text, SQL_SELECT_CUSTOMER_LOGIN, profileparm1);
rdr.Read();
AccountDataModel customer = new AccountDataModel(Convert.ToInt32(rdr.GetDecimal(0)), userid, rdr.GetDateTime(1), rdr.GetDecimal(2), Convert.ToInt32(rdr.GetDecimal(3)), rdr.GetDecimal(4), rdr.GetDateTime(5), Convert.ToInt32(rdr.GetDecimal(6) + 1));
rdr.Close();
OracleParameter profileparm2 = new OracleParameter(PARM_USERID, OracleDbType.Varchar2, 20);
profileparm2.Value = userid;
OracleHelper.ExecuteNonQuerySingleParm(_internalConnection, _internalADOTransaction, CommandType.Text, SQL_UPDATE_CUSTOMER_LOGIN, profileparm2);
return customer;
}
rdr.Close();
}
return null;
}
catch
{
throw;
}
}
/// <summary>
/// Converts from service data contract model class to a UI Model class for quick HTML display in ASPX pages.
/// </summary>
private AccountDataModel convertAccountDataFromUI(AccountDataUI customer)
{
AccountDataModel serviceLayerCustomer = new AccountDataModel();
serviceLayerCustomer.accountID = (int)customer.accountID;
serviceLayerCustomer.balance = customer.balance;
serviceLayerCustomer.creationDate = customer.creationDate;
serviceLayerCustomer.lastLogin = customer.lastLogin;
serviceLayerCustomer.logoutCount = customer.logoutCount;
serviceLayerCustomer.openBalance = customer.openBalance;
serviceLayerCustomer.profileID = customer.profileID;
return serviceLayerCustomer;
}
