private bool Update(int orderID, string pymntID, string state, string amount, string description, string updatedAt)
{
bool isSuccess = false;
int rowsAffacted = 0;
StringBuilder sqliteQueryUpdate = new StringBuilder();
sqliteQueryUpdate.Append("UPDATE orders ");
sqliteQueryUpdate.Append("SET ");
sqliteQueryUpdate.Append("payment_id = @payment_id, ");
sqliteQueryUpdate.Append("state = @state, ");
sqliteQueryUpdate.Append("amount = @amount, ");
sqliteQueryUpdate.Append("description = @description, ");
sqliteQueryUpdate.Append("updated_at = @updated_at ");
sqliteQueryUpdate.Append("WHERE ");
sqliteQueryUpdate.Append("id = @id");
SQLiteDataAdapter sqliteDataAdapterUpdate = new SQLiteDataAdapter();
sqliteDataAdapterUpdate.UpdateCommand = new SQLiteCommand();
sqliteDataAdapterUpdate.UpdateCommand.Parameters.AddWithValue("@payment_id", pymntID);
sqliteDataAdapterUpdate.UpdateCommand.Parameters.AddWithValue("@state", state);
sqliteDataAdapterUpdate.UpdateCommand.Parameters.AddWithValue("@amount", amount);
sqliteDataAdapterUpdate.UpdateCommand.Parameters.AddWithValue("@description", description);
sqliteDataAdapterUpdate.UpdateCommand.Parameters.AddWithValue("@updated_at", updatedAt);
sqliteDataAdapterUpdate.UpdateCommand.Parameters.AddWithValue("@id", orderID);
dataAccessObject = new DataAccessLayer();
rowsAffacted = dataAccessObject.Update(sqliteQueryUpdate.ToString(), sqliteDataAdapterUpdate);
if (rowsAffacted > 0)
{
isSuccess = true;
}
return isSuccess;
}
private DataTable GetUser(string email)
{
DataTable datTable = new DataTable();
StringBuilder sqliteQuerySelect = new StringBuilder();
sqliteQuerySelect.Append("SELECT ");
sqliteQuerySelect.Append("id, ");
sqliteQuerySelect.Append("email, ");
sqliteQuerySelect.Append("encrypted_password, ");
sqliteQuerySelect.Append("sign_in_count, ");
sqliteQuerySelect.Append("current_sign_in_at, ");
sqliteQuerySelect.Append("last_sign_in_at, ");
sqliteQuerySelect.Append("last_sign_in_ip, ");
sqliteQuerySelect.Append("created_at, ");
sqliteQuerySelect.Append("updated_at, ");
sqliteQuerySelect.Append("credit_card_id, ");
sqliteQuerySelect.Append("credit_card_description ");
sqliteQuerySelect.Append("FROM users ");
sqliteQuerySelect.Append("WHERE email = @email");
SQLiteDataAdapter sqliteDataAdapterSelect = new SQLiteDataAdapter();
sqliteDataAdapterSelect.SelectCommand = new SQLiteCommand();
sqliteDataAdapterSelect.SelectCommand.Parameters.AddWithValue("@email", email);
dataAccessObject = new DataAccessLayer();
datTable = dataAccessObject.Select(sqliteQuerySelect.ToString(), sqliteDataAdapterSelect);
return datTable;
}
private DataTable GetPaymentID(string orderID)
{
DataTable datTable = new DataTable();
string sqliteQuery = "SELECT payment_id FROM orders WHERE id = @id";
SQLiteDataAdapter sqliteDataAdapterSelect = new SQLiteDataAdapter();
sqliteDataAdapterSelect.SelectCommand = new SQLiteCommand();
sqliteDataAdapterSelect.SelectCommand.Parameters.AddWithValue("@id", orderID);
dataAccessObject = new DataAccessLayer();
datTable = dataAccessObject.Select(sqliteQuery, sqliteDataAdapterSelect);
return datTable;
}
private bool Insert(int userID, string pymntID, string state, string amount, string description, string createdAt, string updatedAt)
{
bool isSuccess = false;
int rowsAffacted = 0;
StringBuilder sqliteQueryInsert = new StringBuilder();
sqliteQueryInsert.Append("INSERT INTO orders");
sqliteQueryInsert.Append("(");
sqliteQueryInsert.Append("user_id, ");
sqliteQueryInsert.Append("payment_id, ");
sqliteQueryInsert.Append("state, ");
sqliteQueryInsert.Append("amount, ");
sqliteQueryInsert.Append("description, ");
sqliteQueryInsert.Append("created_at, ");
sqliteQueryInsert.Append("updated_at ");
sqliteQueryInsert.Append(") ");
sqliteQueryInsert.Append("VALUES ");
sqliteQueryInsert.Append("(");
sqliteQueryInsert.Append("@user_id, ");
sqliteQueryInsert.Append("@payment_id, ");
sqliteQueryInsert.Append("@state, ");
sqliteQueryInsert.Append("@amount,");
sqliteQueryInsert.Append("@description, ");
sqliteQueryInsert.Append("@created_at, ");
sqliteQueryInsert.Append("@updated_at ");
sqliteQueryInsert.Append(")");
SQLiteDataAdapter sqliteDataAdapterInsert = new SQLiteDataAdapter();
sqliteDataAdapterInsert.InsertCommand = new SQLiteCommand();
sqliteDataAdapterInsert.InsertCommand.Parameters.AddWithValue("@user_id", userID);
sqliteDataAdapterInsert.InsertCommand.Parameters.AddWithValue("@payment_id", pymntID);
sqliteDataAdapterInsert.InsertCommand.Parameters.AddWithValue("@state", state);
sqliteDataAdapterInsert.InsertCommand.Parameters.AddWithValue("@amount", amount);
sqliteDataAdapterInsert.InsertCommand.Parameters.AddWithValue("@description", description);
sqliteDataAdapterInsert.InsertCommand.Parameters.AddWithValue("@created_at", createdAt);
sqliteDataAdapterInsert.InsertCommand.Parameters.AddWithValue("@updated_at", updatedAt);
dataAccessObject = new DataAccessLayer();
rowsAffacted = dataAccessObject.Insert(sqliteQueryInsert.ToString(), sqliteDataAdapterInsert);
if (rowsAffacted > 0)
{
isSuccess = true;
}
return isSuccess;
}
private DataTable GetOrders(int userID)
{
DataTable datTable = new DataTable();
StringBuilder sqliteQuerySelect = new StringBuilder();
sqliteQuerySelect.Append("SELECT ");
sqliteQuerySelect.Append("id, ");
sqliteQuerySelect.Append("user_id, ");
sqliteQuerySelect.Append("payment_id, ");
sqliteQuerySelect.Append("state, ");
sqliteQuerySelect.Append("amount, ");
sqliteQuerySelect.Append("description, ");
sqliteQuerySelect.Append("created_at, ");
sqliteQuerySelect.Append("updated_at ");
sqliteQuerySelect.Append("FROM orders ");
sqliteQuerySelect.Append("WHERE user_id = @user_id ");
sqliteQuerySelect.Append("ORDER BY updated_at DESC, id DESC");
SQLiteDataAdapter sqliteDataAdapterSelect = new SQLiteDataAdapter();
sqliteDataAdapterSelect.SelectCommand = new SQLiteCommand();
sqliteDataAdapterSelect.SelectCommand.Parameters.AddWithValue("@user_id", userID);
dataAccessObject = new DataAccessLayer();
datTable = dataAccessObject.Select(sqliteQuerySelect.ToString(), sqliteDataAdapterSelect);
return datTable;
}
private bool Insert(SignUpModel model)
{
bool isSuccess = false;
int rowsAffacted = 0;
var email = model.Email.Trim();
var password = model.Password.Trim();
var passwordConfirmation = model.ConfirmPassword.Trim();
var encryptedPassword = Secure.Encrypt(password);
var signInCount = 1;
var dateTimeNow = DateTime.Now.ToString("yyyy-MM-dd hh:mm:ss.FFFFF");
var currentSignInAt = dateTimeNow;
var lastSignInAt = dateTimeNow;
var signInIPAddress = string.Empty;
var currentSignInIP = signInIPAddress;
var lastSignInIP = signInIPAddress;
var createdAt = dateTimeNow;
var updatedAt = dateTimeNow;
CreditCard credCard = CreateCreditCard(model);
var creditCardID = credCard.id;
var creditCardDescription = credCard.number;
StringBuilder sqliteQueryInsert = new StringBuilder();
sqliteQueryInsert.Append("INSERT INTO users");
sqliteQueryInsert.Append("(");
sqliteQueryInsert.Append("email, ");
sqliteQueryInsert.Append("encrypted_password, ");
sqliteQueryInsert.Append("sign_in_count, ");
sqliteQueryInsert.Append("current_sign_in_at,");
sqliteQueryInsert.Append("last_sign_in_at, ");
sqliteQueryInsert.Append("current_sign_in_ip, ");
sqliteQueryInsert.Append("last_sign_in_ip, ");
sqliteQueryInsert.Append("created_at, ");
sqliteQueryInsert.Append("updated_at, ");
sqliteQueryInsert.Append("credit_card_id, ");
sqliteQueryInsert.Append("credit_card_description ");
sqliteQueryInsert.Append(") ");
sqliteQueryInsert.Append("VALUES ");
sqliteQueryInsert.Append("(");
sqliteQueryInsert.Append("@email, ");
sqliteQueryInsert.Append("@encrypted_password, ");
sqliteQueryInsert.Append("@sign_in_count, ");
sqliteQueryInsert.Append("@current_sign_in_at,");
sqliteQueryInsert.Append("@last_sign_in_at, ");
sqliteQueryInsert.Append("@current_sign_in_ip, ");
sqliteQueryInsert.Append("@last_sign_in_ip, ");
sqliteQueryInsert.Append("@created_at, ");
sqliteQueryInsert.Append("@updated_at, ");
sqliteQueryInsert.Append("@credit_card_id, ");
sqliteQueryInsert.Append("@credit_card_description ");
sqliteQueryInsert.Append(")");
SQLiteDataAdapter sqliteDataAdapterInsert = new SQLiteDataAdapter();
sqliteDataAdapterInsert.InsertCommand = new SQLiteCommand();
sqliteDataAdapterInsert.InsertCommand.Parameters.AddWithValue("@email", email);
sqliteDataAdapterInsert.InsertCommand.Parameters.AddWithValue("@encrypted_password", encryptedPassword);
sqliteDataAdapterInsert.InsertCommand.Parameters.AddWithValue("@sign_in_count", signInCount);
sqliteDataAdapterInsert.InsertCommand.Parameters.AddWithValue("@current_sign_in_at", currentSignInAt);
sqliteDataAdapterInsert.InsertCommand.Parameters.AddWithValue("@last_sign_in_at", lastSignInAt);
sqliteDataAdapterInsert.InsertCommand.Parameters.AddWithValue("@current_sign_in_ip", currentSignInIP);
sqliteDataAdapterInsert.InsertCommand.Parameters.AddWithValue("@last_sign_in_ip", lastSignInIP);
sqliteDataAdapterInsert.InsertCommand.Parameters.AddWithValue("@created_at", createdAt);
sqliteDataAdapterInsert.InsertCommand.Parameters.AddWithValue("@updated_at", updatedAt);
sqliteDataAdapterInsert.InsertCommand.Parameters.AddWithValue("@credit_card_id", creditCardID);
sqliteDataAdapterInsert.InsertCommand.Parameters.AddWithValue("@credit_card_description", creditCardDescription);
dataAccessObject = new DataAccessLayer();
rowsAffacted = dataAccessObject.Insert(sqliteQueryInsert.ToString(), sqliteDataAdapterInsert);
if (rowsAffacted > 0)
{
isSuccess = true;
}
return(isSuccess);
}
private bool Insert(int userId, string createdAt, string updatedAt)
{
bool isSuccess = false;
int rowsAffected = 0;
StringBuilder insertQuery = new StringBuilder();
insertQuery.Append("INSERT INTO orders");
insertQuery.Append("(");
insertQuery.Append("user_id, ");
insertQuery.Append("created_at, ");
insertQuery.Append("updated_at ");
insertQuery.Append(") ");
insertQuery.Append("VALUES ");
insertQuery.Append("(");
insertQuery.Append("@user_id, ");
insertQuery.Append("@created_at, ");
insertQuery.Append("@updated_at ");
insertQuery.Append(");");
using (SQLiteCommand commandSQLite = new SQLiteCommand(insertQuery.ToString()))
{
commandSQLite.Parameters.AddWithValue("@user_id", userId);
commandSQLite.Parameters.AddWithValue("@created_at", createdAt);
commandSQLite.Parameters.AddWithValue("@updated_at", updatedAt);
DataAccessLayer dataAccessObject = new DataAccessLayer();
rowsAffected = dataAccessObject.Execute(commandSQLite);
}
if (rowsAffected > 0)
{
isSuccess = true;
}
return isSuccess;
}
private bool Update(int orderId, string payId, string state, string amount, string description, string updatedAt)
{
bool isSuccess = false;
int rowsAffected = 0;
StringBuilder updateQuery = new StringBuilder();
updateQuery.Append("UPDATE orders ");
updateQuery.Append("SET ");
updateQuery.Append("payment_id = @payment_id, ");
updateQuery.Append("state = @state, ");
updateQuery.Append("amount = @amount, ");
updateQuery.Append("description = @description, ");
updateQuery.Append("updated_at = @updated_at ");
updateQuery.Append("WHERE ");
updateQuery.Append("id = @id;");
using (SQLiteCommand commandSQLite = new SQLiteCommand(updateQuery.ToString()))
{
commandSQLite.Parameters.AddWithValue("@payment_id", payId);
commandSQLite.Parameters.AddWithValue("@state", state);
commandSQLite.Parameters.AddWithValue("@amount", amount);
commandSQLite.Parameters.AddWithValue("@description", description);
commandSQLite.Parameters.AddWithValue("@updated_at", updatedAt);
commandSQLite.Parameters.AddWithValue("@id", orderId);
DataAccessLayer dataAccessObject = new DataAccessLayer();
rowsAffected = dataAccessObject.Execute(commandSQLite);
}
if (rowsAffected > 0)
{
isSuccess = true;
}
return isSuccess;
}
private DataTable GetOrders(int userId)
{
DataTable datTable = null;
StringBuilder selectQuery = new StringBuilder();
selectQuery.Append("SELECT ");
selectQuery.Append("DISTINCT id, ");
selectQuery.Append("user_id, ");
selectQuery.Append("payment_id, ");
selectQuery.Append("state, ");
selectQuery.Append("amount, ");
selectQuery.Append("description, ");
selectQuery.Append("created_at, ");
selectQuery.Append("updated_at ");
selectQuery.Append("FROM orders ");
selectQuery.Append("WHERE user_id = @user_id ORDER BY id DESC LIMIT 1;");
using (SQLiteCommand commandSQLite = new SQLiteCommand(selectQuery.ToString()))
{
commandSQLite.Parameters.AddWithValue("@user_id", userId);
DataAccessLayer dataAccessObject = new DataAccessLayer();
datTable = dataAccessObject.Select(commandSQLite);
}
return datTable;
}
private DataTable GetUser(string email)
{
DataTable datTable = null;
StringBuilder selectQuery = new StringBuilder();
selectQuery.Append("SELECT ");
selectQuery.Append("id, ");
selectQuery.Append("email, ");
selectQuery.Append("encrypted_password, ");
selectQuery.Append("sign_in_count, ");
selectQuery.Append("credit_card_id ");
selectQuery.Append("FROM users ");
selectQuery.Append("WHERE email = @email;");
using (SQLiteCommand commandSQLite = new SQLiteCommand(selectQuery.ToString()))
{
commandSQLite.Parameters.AddWithValue("@email", email);
DataAccessLayer dataAccessObject = new DataAccessLayer();
datTable = dataAccessObject.Select(commandSQLite);
}
return datTable;
}
private bool Update(ProfileModel model, string email)
{
bool isSuccess = false;
int rowsAffacted = 0;
var newPassword = model.NewPassword.Trim();
var confirmNewPassword = model.ConfirmPassword.Trim();
var encryptedNewPassword = Secure.Encrypt(newPassword);
var signInCount = 0;
var dateTimeNow = DateTime.Now.ToString("yyyy-MM-dd hh:mm:ss.FFFFF");
var currentSignInAt = dateTimeNow;
var lastSignInAt = string.Empty;
var signInIPAddress = string.Empty;
var currentSignInIP = string.Empty;
var lastSignInIP = string.Empty;
var createdAt = string.Empty;
var updatedAt = dateTimeNow;
DataTable datTable = GetUser(email);
if (datTable != null && datTable.Rows.Count > 0)
{
var distinctRows = from DataRow dRow in datTable.Rows
where dRow.Field<string>("email") == email
select new { column1 = dRow["sign_in_count"], column2 = dRow["last_sign_in_at"], column3 = dRow["last_sign_in_ip"], column4 = dRow["created_at"] };
if (distinctRows != null)
{
foreach (var row in distinctRows)
{
signInCount = Convert.ToInt32(row.column1.ToString());
signInCount++;
DateTime lastSignInAtDateTime = Convert.ToDateTime(row.column2);
lastSignInAt = lastSignInAtDateTime.ToString("yyyy-MM-dd hh:mm:ss.FFFFF");
lastSignInIP = Convert.ToString(row.column3);
DateTime createdAtDateTime = Convert.ToDateTime(row.column4);
createdAt = createdAtDateTime.ToString("yyyy-MM-dd hh:mm:ss.FFFFF");
break;
}
}
CreditCard credCard = CreateCreditCard(model);
var creditCardID = credCard.id;
var creditCardDescription = credCard.number;
StringBuilder sqliteQueryUpdate = new StringBuilder();
sqliteQueryUpdate.Append("UPDATE Users ");
sqliteQueryUpdate.Append("SET ");
sqliteQueryUpdate.Append("encrypted_password = @encrypted_password, ");
sqliteQueryUpdate.Append("sign_in_count = @sign_in_count, ");
sqliteQueryUpdate.Append("current_sign_in_at = @current_sign_in_at, ");
sqliteQueryUpdate.Append("last_sign_in_at = @last_sign_in_at, ");
sqliteQueryUpdate.Append("current_sign_in_ip = @current_sign_in_ip, ");
sqliteQueryUpdate.Append("last_sign_in_ip = @last_sign_in_ip, ");
sqliteQueryUpdate.Append("created_at = @created_at, ");
sqliteQueryUpdate.Append("credit_card_id = @credit_card_id, ");
sqliteQueryUpdate.Append("credit_card_description = @credit_card_description ");
sqliteQueryUpdate.Append("WHERE ");
sqliteQueryUpdate.Append("email = @email");
SQLiteDataAdapter sqliteDataAdapterUpdate = new SQLiteDataAdapter();
sqliteDataAdapterUpdate.UpdateCommand = new SQLiteCommand();
sqliteDataAdapterUpdate.UpdateCommand.Parameters.AddWithValue("@email", email);
sqliteDataAdapterUpdate.UpdateCommand.Parameters.AddWithValue("@encrypted_password", encryptedNewPassword);
sqliteDataAdapterUpdate.UpdateCommand.Parameters.AddWithValue("@sign_in_count", signInCount);
sqliteDataAdapterUpdate.UpdateCommand.Parameters.AddWithValue("@current_sign_in_at", currentSignInAt);
sqliteDataAdapterUpdate.UpdateCommand.Parameters.AddWithValue("@last_sign_in_at", lastSignInAt);
sqliteDataAdapterUpdate.UpdateCommand.Parameters.AddWithValue("@current_sign_in_ip", currentSignInIP);
sqliteDataAdapterUpdate.UpdateCommand.Parameters.AddWithValue("@last_sign_in_ip", lastSignInIP);
sqliteDataAdapterUpdate.UpdateCommand.Parameters.AddWithValue("@created_at", createdAt);
sqliteDataAdapterUpdate.UpdateCommand.Parameters.AddWithValue("@updated_at", updatedAt);
sqliteDataAdapterUpdate.UpdateCommand.Parameters.AddWithValue("@credit_card_id", creditCardID);
sqliteDataAdapterUpdate.UpdateCommand.Parameters.AddWithValue("@credit_card_description", creditCardDescription);
dataAccessObject = new DataAccessLayer();
rowsAffacted = dataAccessObject.Update(sqliteQueryUpdate.ToString(), sqliteDataAdapterUpdate);
}
if (rowsAffacted > 0)
{
isSuccess = true;
}
return isSuccess;
}
private bool Update(string email)
{
bool isSuccess = false;
int rowsAffacted = 0;
int signInCount = 0;
DataTable datTable = GetUser(email);
if (datTable != null && datTable.Rows.Count > 0)
{
var distinctRows = from DataRow dRow in datTable.Rows
where dRow.Field<string>("email") == email
select new { column1 = dRow["sign_in_count"] };
if (distinctRows != null)
{
foreach (var row in distinctRows)
{
signInCount = Convert.ToInt32(row.column1.ToString());
signInCount++;
break;
}
}
StringBuilder sqliteQueryUpdate = new StringBuilder();
sqliteQueryUpdate.Append("UPDATE Users ");
sqliteQueryUpdate.Append("SET ");
sqliteQueryUpdate.Append("sign_in_count = @sign_in_count ");
sqliteQueryUpdate.Append("WHERE ");
sqliteQueryUpdate.Append("email = @email");
SQLiteDataAdapter sqliteDataAdapterUpdate = new SQLiteDataAdapter();
sqliteDataAdapterUpdate.UpdateCommand = new SQLiteCommand();
sqliteDataAdapterUpdate.UpdateCommand.Parameters.AddWithValue("@email", email);
sqliteDataAdapterUpdate.UpdateCommand.Parameters.AddWithValue("@sign_in_count", signInCount);
dataAccessObject = new DataAccessLayer();
rowsAffacted = dataAccessObject.Update(sqliteQueryUpdate.ToString(), sqliteDataAdapterUpdate);
}
if (rowsAffacted > 0)
{
isSuccess = true;
}
return isSuccess;
}
private bool Update(ProfileModel model, string email)
{
bool isSuccess = false;
int rowsAffacted = 0;
var newPassword = model.NewPassword.Trim();
var confirmNewPassword = model.ConfirmPassword.Trim();
var encryptedNewPassword = Secure.Encrypt(newPassword);
var signInCount = 0;
var dateTimeNow = DateTime.Now.ToString("yyyy-MM-dd hh:mm:ss.FFFFF");
var currentSignInAt = dateTimeNow;
var lastSignInAt = string.Empty;
var signInIPAddress = string.Empty;
var currentSignInIP = string.Empty;
var lastSignInIP = string.Empty;
var createdAt = string.Empty;
var updatedAt = dateTimeNow;
DataTable datTable = GetUser(email);
if (datTable != null && datTable.Rows.Count > 0)
{
var distinctRows = from DataRow dRow in datTable.Rows
where dRow.Field <string>("email") == email
select new { column1 = dRow["sign_in_count"], column2 = dRow["last_sign_in_at"], column3 = dRow["last_sign_in_ip"], column4 = dRow["created_at"] };
if (distinctRows != null)
{
foreach (var row in distinctRows)
{
signInCount = Convert.ToInt32(row.column1.ToString());
signInCount++;
DateTime lastSignInAtDateTime = Convert.ToDateTime(row.column2);
lastSignInAt = lastSignInAtDateTime.ToString("yyyy-MM-dd hh:mm:ss.FFFFF");
lastSignInIP = Convert.ToString(row.column3);
DateTime createdAtDateTime = Convert.ToDateTime(row.column4);
createdAt = createdAtDateTime.ToString("yyyy-MM-dd hh:mm:ss.FFFFF");
break;
}
}
CreditCard credCard = CreateCreditCard(model);
var creditCardID = credCard.id;
var creditCardDescription = credCard.number;
StringBuilder sqliteQueryUpdate = new StringBuilder();
sqliteQueryUpdate.Append("UPDATE Users ");
sqliteQueryUpdate.Append("SET ");
sqliteQueryUpdate.Append("encrypted_password = @encrypted_password, ");
sqliteQueryUpdate.Append("sign_in_count = @sign_in_count, ");
sqliteQueryUpdate.Append("current_sign_in_at = @current_sign_in_at, ");
sqliteQueryUpdate.Append("last_sign_in_at = @last_sign_in_at, ");
sqliteQueryUpdate.Append("current_sign_in_ip = @current_sign_in_ip, ");
sqliteQueryUpdate.Append("last_sign_in_ip = @last_sign_in_ip, ");
sqliteQueryUpdate.Append("created_at = @created_at, ");
sqliteQueryUpdate.Append("credit_card_id = @credit_card_id, ");
sqliteQueryUpdate.Append("credit_card_description = @credit_card_description ");
sqliteQueryUpdate.Append("WHERE ");
sqliteQueryUpdate.Append("email = @email");
SQLiteDataAdapter sqliteDataAdapterUpdate = new SQLiteDataAdapter();
sqliteDataAdapterUpdate.UpdateCommand = new SQLiteCommand();
sqliteDataAdapterUpdate.UpdateCommand.Parameters.AddWithValue("@email", email);
sqliteDataAdapterUpdate.UpdateCommand.Parameters.AddWithValue("@encrypted_password", encryptedNewPassword);
sqliteDataAdapterUpdate.UpdateCommand.Parameters.AddWithValue("@sign_in_count", signInCount);
sqliteDataAdapterUpdate.UpdateCommand.Parameters.AddWithValue("@current_sign_in_at", currentSignInAt);
sqliteDataAdapterUpdate.UpdateCommand.Parameters.AddWithValue("@last_sign_in_at", lastSignInAt);
sqliteDataAdapterUpdate.UpdateCommand.Parameters.AddWithValue("@current_sign_in_ip", currentSignInIP);
sqliteDataAdapterUpdate.UpdateCommand.Parameters.AddWithValue("@last_sign_in_ip", lastSignInIP);
sqliteDataAdapterUpdate.UpdateCommand.Parameters.AddWithValue("@created_at", createdAt);
sqliteDataAdapterUpdate.UpdateCommand.Parameters.AddWithValue("@updated_at", updatedAt);
sqliteDataAdapterUpdate.UpdateCommand.Parameters.AddWithValue("@credit_card_id", creditCardID);
sqliteDataAdapterUpdate.UpdateCommand.Parameters.AddWithValue("@credit_card_description", creditCardDescription);
dataAccessObject = new DataAccessLayer();
rowsAffacted = dataAccessObject.Update(sqliteQueryUpdate.ToString(), sqliteDataAdapterUpdate);
}
if (rowsAffacted > 0)
{
isSuccess = true;
}
return(isSuccess);
}
private bool CheckIsExistingUser(SignUpModel model)
{
bool isExistingUser = false;
DataTable datTable = new DataTable();
int rows = 0;
var email = model.Email.Trim();
StringBuilder sqliteQuerySelect = new StringBuilder();
sqliteQuerySelect.Append("SELECT ");
sqliteQuerySelect.Append("count(*) AS NumberOfUsers ");
sqliteQuerySelect.Append("FROM users ");
sqliteQuerySelect.Append("WHERE email = @email");
SQLiteDataAdapter sqliteDataAdapterSelect = new SQLiteDataAdapter();
sqliteDataAdapterSelect.SelectCommand = new SQLiteCommand();
sqliteDataAdapterSelect.SelectCommand.Parameters.AddWithValue("@email", email);
dataAccessObject = new DataAccessLayer();
datTable = dataAccessObject.Select(sqliteQuerySelect.ToString(), sqliteDataAdapterSelect);
if (datTable != null && datTable.Rows.Count > 0)
{
var distinctRows = from DataRow dRow in datTable.Rows
select new { column1 = dRow["NumberOfUsers"] };
if (distinctRows != null)
{
foreach (var row in distinctRows)
{
rows = Convert.ToInt32(row.column1);
break;
}
}
}
if (rows == 1)
{
isExistingUser = true;
}
return isExistingUser;
}
private bool Insert(SignUpModel model)
{
bool isSuccess = false;
int rowsAffacted = 0;
var email = model.Email.Trim();
var password = model.Password.Trim();
var passwordConfirmation = model.ConfirmPassword.Trim();
var encryptedPassword = Secure.Encrypt(password);
var signInCount = 1;
var dateTimeNow = DateTime.Now.ToString("yyyy-MM-dd hh:mm:ss.FFFFF");
var currentSignInAt = dateTimeNow;
var lastSignInAt = dateTimeNow;
var signInIPAddress = string.Empty;
var currentSignInIP = signInIPAddress;
var lastSignInIP = signInIPAddress;
var createdAt = dateTimeNow;
var updatedAt = dateTimeNow;
CreditCard credCard = CreateCreditCard(model);
var creditCardID = credCard.id;
var creditCardDescription = credCard.number;
StringBuilder sqliteQueryInsert = new StringBuilder();
sqliteQueryInsert.Append("INSERT INTO users");
sqliteQueryInsert.Append("(");
sqliteQueryInsert.Append("email, ");
sqliteQueryInsert.Append("encrypted_password, ");
sqliteQueryInsert.Append("sign_in_count, ");
sqliteQueryInsert.Append("current_sign_in_at,");
sqliteQueryInsert.Append("last_sign_in_at, ");
sqliteQueryInsert.Append("current_sign_in_ip, ");
sqliteQueryInsert.Append("last_sign_in_ip, ");
sqliteQueryInsert.Append("created_at, ");
sqliteQueryInsert.Append("updated_at, ");
sqliteQueryInsert.Append("credit_card_id, ");
sqliteQueryInsert.Append("credit_card_description ");
sqliteQueryInsert.Append(") ");
sqliteQueryInsert.Append("VALUES ");
sqliteQueryInsert.Append("(");
sqliteQueryInsert.Append("@email, ");
sqliteQueryInsert.Append("@encrypted_password, ");
sqliteQueryInsert.Append("@sign_in_count, ");
sqliteQueryInsert.Append("@current_sign_in_at,");
sqliteQueryInsert.Append("@last_sign_in_at, ");
sqliteQueryInsert.Append("@current_sign_in_ip, ");
sqliteQueryInsert.Append("@last_sign_in_ip, ");
sqliteQueryInsert.Append("@created_at, ");
sqliteQueryInsert.Append("@updated_at, ");
sqliteQueryInsert.Append("@credit_card_id, ");
sqliteQueryInsert.Append("@credit_card_description ");
sqliteQueryInsert.Append(")");
SQLiteDataAdapter sqliteDataAdapterInsert = new SQLiteDataAdapter();
sqliteDataAdapterInsert.InsertCommand = new SQLiteCommand();
sqliteDataAdapterInsert.InsertCommand.Parameters.AddWithValue("@email", email);
sqliteDataAdapterInsert.InsertCommand.Parameters.AddWithValue("@encrypted_password", encryptedPassword);
sqliteDataAdapterInsert.InsertCommand.Parameters.AddWithValue("@sign_in_count", signInCount);
sqliteDataAdapterInsert.InsertCommand.Parameters.AddWithValue("@current_sign_in_at", currentSignInAt);
sqliteDataAdapterInsert.InsertCommand.Parameters.AddWithValue("@last_sign_in_at", lastSignInAt);
sqliteDataAdapterInsert.InsertCommand.Parameters.AddWithValue("@current_sign_in_ip", currentSignInIP);
sqliteDataAdapterInsert.InsertCommand.Parameters.AddWithValue("@last_sign_in_ip", lastSignInIP);
sqliteDataAdapterInsert.InsertCommand.Parameters.AddWithValue("@created_at", createdAt);
sqliteDataAdapterInsert.InsertCommand.Parameters.AddWithValue("@updated_at", updatedAt);
sqliteDataAdapterInsert.InsertCommand.Parameters.AddWithValue("@credit_card_id", creditCardID);
sqliteDataAdapterInsert.InsertCommand.Parameters.AddWithValue("@credit_card_description", creditCardDescription);
dataAccessObject = new DataAccessLayer();
rowsAffacted = dataAccessObject.Insert(sqliteQueryInsert.ToString(), sqliteDataAdapterInsert);
if (rowsAffacted > 0)
{
isSuccess = true;
}
return isSuccess;
}
private bool Update(string email, string newPassword, string confirmNewPassword,
string newCreditCardType, string newCreditCardNumber, string newCreditCardCVV2, string newCreditCardExpireMonth, string newCreditCardExpireYear)
{
bool isSuccess = false;
int rowsAffected = 0;
var encryptedNewPassword = Secure.Encrypt(newPassword);
var signInCount = 0;
var dateTimeNow = DateTime.Now.ToString("yyyy-MM-dd hh:mm:ss.FFFFF");
var currentSignInAt = dateTimeNow;
// Set last signed in IP Address from database
var lastSignInAt = string.Empty;
// Set first signed in IP Address from database
var signInIPAddress = string.Empty;
// Set current signed in IP Address
var currentSignInIP = string.Empty;
// Set last signed in IP Address from database
var lastSignInIP = string.Empty;
var createdAt = string.Empty;
var updatedAt = dateTimeNow;
DataTable datTable = GetUser(email);
if (datTable != null && datTable.Rows.Count > 0)
{
var distinctRows = (from DataRow dRow in datTable.Rows
where dRow.Field<string>("email") == email
select new
{
column1 = dRow["sign_in_count"],
column2 = dRow["last_sign_in_at"],
column3 = dRow["last_sign_in_ip"],
column4 = dRow["created_at"]
}).Distinct();
if (distinctRows != null)
{
foreach (var row in distinctRows)
{
signInCount = Convert.ToInt32(row.column1.ToString());
signInCount++;
DateTime lastSignInAtDateTime = Convert.ToDateTime(row.column2);
lastSignInAt = lastSignInAtDateTime.ToString("yyyy-MM-dd hh:mm:ss.FFFFF");
lastSignInIP = Convert.ToString(row.column3);
DateTime createdAtDateTime = Convert.ToDateTime(row.column4);
createdAt = createdAtDateTime.ToString("yyyy-MM-dd hh:mm:ss.FFFFF");
break;
}
}
CreditCard credCard = CreateCreditCard(newCreditCardType, newCreditCardNumber, newCreditCardCVV2, newCreditCardExpireMonth, newCreditCardExpireYear);
var creditCardId = credCard.id;
var creditCardDescription = credCard.number;
StringBuilder updateQuery = new StringBuilder();
updateQuery.Append("UPDATE Users ");
updateQuery.Append("SET ");
updateQuery.Append("encrypted_password = @encrypted_password, ");
updateQuery.Append("sign_in_count = @sign_in_count, ");
updateQuery.Append("current_sign_in_at = @current_sign_in_at, ");
updateQuery.Append("last_sign_in_at = @last_sign_in_at, ");
updateQuery.Append("current_sign_in_ip = @current_sign_in_ip, ");
updateQuery.Append("last_sign_in_ip = @last_sign_in_ip, ");
updateQuery.Append("created_at = @created_at, ");
updateQuery.Append("credit_card_id = @credit_card_id, ");
updateQuery.Append("credit_card_description = @credit_card_description ");
updateQuery.Append("WHERE ");
updateQuery.Append("email = @email;");
using (SQLiteCommand commandSQLite = new SQLiteCommand(updateQuery.ToString()))
{
commandSQLite.Parameters.AddWithValue("@email", email);
commandSQLite.Parameters.AddWithValue("@encrypted_password", encryptedNewPassword);
commandSQLite.Parameters.AddWithValue("@sign_in_count", signInCount);
commandSQLite.Parameters.AddWithValue("@current_sign_in_at", currentSignInAt);
commandSQLite.Parameters.AddWithValue("@last_sign_in_at", lastSignInAt);
commandSQLite.Parameters.AddWithValue("@current_sign_in_ip", currentSignInIP);
commandSQLite.Parameters.AddWithValue("@last_sign_in_ip", lastSignInIP);
commandSQLite.Parameters.AddWithValue("@created_at", createdAt);
commandSQLite.Parameters.AddWithValue("@updated_at", updatedAt);
commandSQLite.Parameters.AddWithValue("@credit_card_id", creditCardId);
commandSQLite.Parameters.AddWithValue("@credit_card_description", creditCardDescription);
DataAccessLayer dataAccessObject = new DataAccessLayer();
rowsAffected = dataAccessObject.Execute(commandSQLite);
}
}
if (rowsAffected > 0)
{
isSuccess = true;
}
return isSuccess;
}
private bool Insert(string email, string password, string passwordConfirmation,
string creditCardType, string creditCardNumber, string creditCardCVV2, string creditCardExpireMonth, string creditCardExpireYear)
{
bool isSuccess = false;
int rowsAffected = 0;
var encryptedPassword = Secure.Encrypt(password);
var signInCount = 1;
var dateTimeNow = DateTime.Now.ToString("yyyy-MM-dd hh:mm:ss.FFFFF");
var currentSignInAt = dateTimeNow;
var lastSignInAt = dateTimeNow;
var signInIPAddress = string.Empty;
var currentSignInIP = signInIPAddress;
var lastSignInIP = signInIPAddress;
var createdAt = dateTimeNow;
var updatedAt = dateTimeNow;
CreditCard credCard = CreateCreditCard(creditCardType, creditCardNumber, creditCardCVV2, creditCardExpireMonth, creditCardExpireYear);
var creditCardId = credCard.id;
var creditCardDescription = credCard.number;
StringBuilder insertQuery = new StringBuilder();
insertQuery.Append("INSERT INTO users");
insertQuery.Append("(");
insertQuery.Append("email, ");
insertQuery.Append("encrypted_password, ");
insertQuery.Append("sign_in_count, ");
insertQuery.Append("current_sign_in_at,");
insertQuery.Append("last_sign_in_at, ");
insertQuery.Append("current_sign_in_ip, ");
insertQuery.Append("last_sign_in_ip, ");
insertQuery.Append("created_at, ");
insertQuery.Append("updated_at, ");
insertQuery.Append("credit_card_id, ");
insertQuery.Append("credit_card_description ");
insertQuery.Append(") ");
insertQuery.Append("VALUES ");
insertQuery.Append("(");
insertQuery.Append("@email, ");
insertQuery.Append("@encrypted_password, ");
insertQuery.Append("@sign_in_count, ");
insertQuery.Append("@current_sign_in_at,");
insertQuery.Append("@last_sign_in_at, ");
insertQuery.Append("@current_sign_in_ip, ");
insertQuery.Append("@last_sign_in_ip, ");
insertQuery.Append("@created_at, ");
insertQuery.Append("@updated_at, ");
insertQuery.Append("@credit_card_id, ");
insertQuery.Append("@credit_card_description ");
insertQuery.Append(");");
using (SQLiteCommand commandSQLite = new SQLiteCommand(insertQuery.ToString()))
{
commandSQLite.Parameters.AddWithValue("@email", email);
commandSQLite.Parameters.AddWithValue("@encrypted_password", encryptedPassword);
commandSQLite.Parameters.AddWithValue("@sign_in_count", signInCount);
commandSQLite.Parameters.AddWithValue("@current_sign_in_at", currentSignInAt);
commandSQLite.Parameters.AddWithValue("@last_sign_in_at", lastSignInAt);
commandSQLite.Parameters.AddWithValue("@current_sign_in_ip", currentSignInIP);
commandSQLite.Parameters.AddWithValue("@last_sign_in_ip", lastSignInIP);
commandSQLite.Parameters.AddWithValue("@created_at", createdAt);
commandSQLite.Parameters.AddWithValue("@updated_at", updatedAt);
commandSQLite.Parameters.AddWithValue("@credit_card_id", creditCardId);
commandSQLite.Parameters.AddWithValue("@credit_card_description", creditCardDescription);
DataAccessLayer dataAccessObject = new DataAccessLayer();
rowsAffected = dataAccessObject.Execute(commandSQLite);
}
if (rowsAffected > 0)
{
isSuccess = true;
}
return isSuccess;
}
private bool CheckIsExistingUser(string email)
{
bool isExistingUser = false;
DataTable datTable = null;
int rows = 0;
StringBuilder selectQuery = new StringBuilder();
selectQuery.Append("SELECT ");
selectQuery.Append("count(*) AS NumberOfUsers ");
selectQuery.Append("FROM users ");
selectQuery.Append("WHERE email = @email;");
using (SQLiteCommand commandSQLite = new SQLiteCommand(selectQuery.ToString()))
{
commandSQLite.Parameters.AddWithValue("@email", email);
DataAccessLayer dataAccessObject = new DataAccessLayer();
datTable = dataAccessObject.Select(commandSQLite);
}
if (datTable != null && datTable.Rows.Count > 0)
{
var distinctRows = (from DataRow dRow in datTable.Rows
select new { column1 = dRow["NumberOfUsers"] }).Distinct();
if (distinctRows != null)
{
foreach (var row in distinctRows)
{
rows = Convert.ToInt32(row.column1);
break;
}
}
}
if (rows == 1)
{
isExistingUser = true;
}
return isExistingUser;
}
private DataTable GetPaymentId(string orderId)
{
DataTable datTable = null;
string selectQuery = "SELECT payment_id FROM orders WHERE id = @id;";
using (SQLiteCommand commandSQLite = new SQLiteCommand(selectQuery))
{
commandSQLite.Parameters.AddWithValue("@id", orderId);
DataAccessLayer dataAccessObject = new DataAccessLayer();
datTable = dataAccessObject.Select(commandSQLite);
}
return datTable;
}