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 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 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 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;
}