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