internal PaymentInformation getUserCreditCard(int paymentInfoId)
{
PaymentInformation userCreditCard = new PaymentInformation();
User user = new User();
using (OleDbConnection sqlconn = new OleDbConnection(database))
{
try
{
sqlconn.Open();
OleDbCommand cmd = sqlconn.CreateCommand();
String select = "SELECT [PAYMENT_INFORMATION].payment_information_id, [PAYMENT_INFORMATION].credit_card_type, [PAYMENT_INFORMATION].credit_card_number, [PAYMENT_INFORMATION].card_city, [PAYMENT_INFORMATION].card_state, " +
"[PAYMENT_INFORMATION].card_exp_date, [PAYMENT_INFORMATION].security_code, [USERS].user_id, [USERS].username, [USERS].first_name, [USERS].last_name, [USERS].address, " +
"[USERS].city, [USERS].state, [USERS].zip_code, [USERS].account_creation_date, [USERS].access_level, [USERS].email FROM [PAYMENT_INFORMATION] " +
"INNER JOIN [USERS] ON [USERS].user_id = [PAYMENT_INFORMATION].user_id " +
"WHERE [PAYMENT_INFORMATION].payment_information_id = @paymentInfoId";
cmd.CommandText = select;
cmd.Parameters.Add("paymentInfoId", OleDbType.Integer).Value = paymentInfoId;
cmd.Prepare();
OleDbDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
user.setId((int) reader["user_id"]);
user.setUsername(reader["username"].ToString());
user.setFirstName(reader["first_name"].ToString());
user.setLastName(reader["last_name"].ToString());
user.setAddress(reader["address"].ToString());
user.setCity(reader["city"].ToString());
user.setState(reader["state"].ToString());
user.setZipCode(reader["zip_code"].ToString());
user.setAccountCreationDate(DateTime.Parse(reader["account_creation_date"].ToString()));
user.setEmail(reader["email"].ToString());
userCreditCard.setUser(user);
userCreditCard.setPaymentInformationId((int) reader["payment_information_id"]);
userCreditCard.setCreditCardType(reader["credit_card_type"].ToString());
userCreditCard.setCreditCardNumber(reader["credit_card_number"].ToString());
userCreditCard.setCity(reader["card_city"].ToString());
userCreditCard.setState(reader["card_state"].ToString());
userCreditCard.setCardExpDated(DateTime.Parse(reader["card_exp_date"].ToString()));
userCreditCard.setSecurityCode(reader["security_code"].ToString());
}
return userCreditCard;
}
catch (OleDbException ex)
{
userCreditCard = null;
return userCreditCard;
}
finally
{
sqlconn.Close();
}
}
}
public PaymentInformation(int paymentInformationId, User user, String creditCardType, String creditCardNumber, String city, String state, DateTime cardExpDate, String securityCode)
{
this.paymentInformationId = paymentInformationId;
this.user = user;
this.creditCardType = creditCardType;
this.creditCardNumber = creditCardNumber;
this.city = city;
this.state = state;
this.cardExpDate = cardExpDate;
this.securityCode = securityCode;
}
internal List<PaymentInformation> getUserPaymentInfo(User user)
{
List<PaymentInformation> userPaymentInfo = new List<PaymentInformation>();
using (OleDbConnection sqlconn = new OleDbConnection(database))
{
try
{
sqlconn.Open();
String select = "SELECT * FROM [PAYMENT_INFORMATION] WHERE [user_id] = @userId";
OleDbCommand cmd = new OleDbCommand(select, sqlconn);
cmd.Parameters.Add("userId", OleDbType.Integer).Value = user.getId();
OleDbDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
int paymentInformationId = (int) reader["payment_information_id"];
int userId = (int)reader["user_id"];
String creditCartType = reader["credit_card_type"].ToString();
String creditCardNumber = reader["credit_card_number"].ToString();
String cardCity = reader["card_city"].ToString();
String cardState = reader["card_state"].ToString();
DateTime cardExpDate = DateTime.Parse(reader["card_exp_date"].ToString());
String securityCode = reader["security_code"].ToString();
PaymentInformation paymentInfo = new PaymentInformation(paymentInformationId, user, creditCartType,
creditCardNumber, cardCity, cardState, cardExpDate, securityCode);
userPaymentInfo.Add(paymentInfo);
}
return userPaymentInfo;
}
catch(OleDbException ex)
{
userPaymentInfo = null;
return userPaymentInfo;
}
finally
{
sqlconn.Close();
}
}
}
public User getUserDetails(String username)
{
User user = null;
using (OleDbConnection sqlConn = new OleDbConnection(database))
{
try
{
sqlConn.Open();
String select = "SELECT * FROM [USERS] WHERE [username] = @username";
OleDbCommand cmd = new OleDbCommand(select, sqlConn);
cmd.Parameters.Add("username", OleDbType.VarChar, 255).Value = username;
OleDbDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
int userId = (int) dr["user_id"];
String userName = dr["username"].ToString();
String firstName = dr["first_name"].ToString();
String lastName = dr["last_name"].ToString();
String address = dr["address"].ToString();
String city = dr["city"].ToString();
String state = dr["state"].ToString();
String zipCode = dr["zip_code"].ToString();
DateTime accountCreationDate = (DateTime)dr["account_creation_date"];
int accessLevel = (int) dr["access_level"];
String email = dr["email"].ToString();
user = new User(userId, userName, firstName, lastName, address, city, state, zipCode, accountCreationDate, email);
}
}
catch (OleDbException ex)
{
}
finally
{
sqlConn.Close();
}
return user;
}
}
internal bool updateUser(User user)
{
using (OleDbConnection sqlConn = new OleDbConnection(database))
{
try
{
sqlConn.Open();
String insert = "UPDATE [USERS] " +
"SET [first_name] = @firstName," +
"[last_name] = @lastName," +
"[address] = @address," +
"[city] = @city," +
"[state] = @state," +
"[zip_code] = @zipCode," +
"[email] = @email " +
"WHERE ([user_id] = @userId)";
OleDbCommand cmd = new OleDbCommand(insert, sqlConn);
cmd.Parameters.Add("firstName", OleDbType.VarChar, 255).Value = user.getFirstName();
cmd.Parameters.Add("lastName", OleDbType.VarChar, 255).Value = user.getLastName();
cmd.Parameters.Add("address", OleDbType.VarChar, 255).Value = user.getAddress();
cmd.Parameters.Add("city", OleDbType.VarChar, 255).Value = user.getCity();
cmd.Parameters.Add("state", OleDbType.VarChar, 255).Value = user.getState();
cmd.Parameters.Add("zipCode", OleDbType.VarChar, 255).Value = user.getZipCode();
cmd.Parameters.Add("email", OleDbType.VarChar, 255).Value = user.getEamil();
cmd.Parameters.Add("userId", OleDbType.Integer).Value = user.getId();
int rows = cmd.ExecuteNonQuery();
if (rows == 1)
{
return true;
}
else
{
return false;
}
}
catch (OleDbException ex)
{
return false;
}
finally
{
sqlConn.Close();
}
}
}
public Boolean updateUserDetails(User user)
{
Boolean updated = dataLayer.updateUser(user);
return updated;
}
public void setUser(User user)
{
this.user = user;
}