internal static bool AccountExist(string custEmail) { bool validLogin = false; try { PopCulDatabaseUtil.OpenConnection(); _command = new SqlCommand("SELECT * " + "FROM dbo.account " + "WHERE email=@email", PopCulDatabaseUtil.cnn); _command.Parameters.AddWithValue("@email", custEmail); SqlDataReader reader = _command.ExecuteReader(); if (reader.Read()) { validLogin = true; } } catch (SqlException ex) { Debug.WriteLine("Can't find customer: " + ex.Message); } finally { PopCulDatabaseUtil.CloseConnection(); } return(validLogin); }
internal static string GetAccountNameOfPosts(string accountId) { string accountName = ""; try { PopCulDatabaseUtil.OpenConnection(); _command = new SqlCommand("SELECT a.first_name, a.last_name " + "FROM dbo.item i inner join dbo.account a on i.account_id = a.account_id " + "WHERE a.account_id=@id", PopCulDatabaseUtil.cnn); _command.Parameters.AddWithValue("@id", accountId); SqlDataReader reader = _command.ExecuteReader(); if (reader.Read()) { accountName = reader["first_name"].ToString() + " " + reader["last_name"].ToString(); } } catch (SqlException ex) { Debug.WriteLine("Can't find customer: " + ex.Message); } finally { PopCulDatabaseUtil.CloseConnection(); } return(accountName); }
internal static string ValidateLogin(string custEmail, string custPassword) { string accountId = ""; try { PopCulDatabaseUtil.OpenConnection(); _command = new SqlCommand("SELECT * " + "FROM dbo.account " + "WHERE email=@email and password=@password", PopCulDatabaseUtil.cnn); _command.Parameters.AddWithValue("@email", custEmail); _command.Parameters.AddWithValue("@password", custPassword); SqlDataReader reader = _command.ExecuteReader(); if (reader.Read()) { accountId = reader["account_id"].ToString(); } else { accountId = ""; } } catch (SqlException ex) { Debug.WriteLine("Can't find customer: " + ex.Message); } finally { PopCulDatabaseUtil.CloseConnection(); } return(accountId); }
internal static SqlDataAdapter GetMyAds(string accountId) { PopCulDatabaseUtil.OpenConnection(); string query = $"SELECT * FROM ad_post WHERE account_id = '{accountId}'"; SqlDataAdapter sda = new SqlDataAdapter(query, PopCulDatabaseUtil.cnn); return(sda); }
//Retrieve the search results from the DB //Display the results in browse.aspx internal static List <Item> GetAllItems(string searchKey) { // Reset the list to view new the correct items only next item itemList = new List <Item>(); Message = ""; try { string query = $"SELECT * FROM dbo.item WHERE item_name LIKE '%{searchKey}%'"; PopCulDatabaseUtil.OpenConnection(); SqlDataAdapter sqlDataAdapter = new SqlDataAdapter(query, PopCulDatabaseUtil.cnn); DataTable dataTable = new DataTable(); sqlDataAdapter.Fill(dataTable); if (dataTable.Rows.Count >= 1) { _command = new SqlCommand(query, PopCulDatabaseUtil.cnn); SqlDataReader reader = _command.ExecuteReader(); while (reader.Read()) { Item item = new Item { Id = reader["item_id"].ToString(), Name = reader["item_name"].ToString(), Category = reader["item_category"].ToString(), Description = reader["item_desc"].ToString(), Image = reader["item_img"].ToString(), City = reader["item_city"].ToString(), Phone = reader["item_phone"].ToString(), Price = decimal.Parse(reader["item_price"].ToString()) }; itemList.Add(item); } } else { Message = "No matching items"; } } catch (Exception error) { Message = error.Message; } finally { PopCulDatabaseUtil.CloseConnection(); } return(itemList); }
//Get the item by item ID internal static Item GetItem(string id) { Item item = null; Message = ""; try { string query = $"SELECT * FROM dbo.item WHERE item_id = '{id}'"; PopCulDatabaseUtil.OpenConnection(); SqlDataAdapter sqlDataAdapter = new SqlDataAdapter(query, PopCulDatabaseUtil.cnn); DataTable dataTable = new DataTable(); sqlDataAdapter.Fill(dataTable); if (dataTable.Rows.Count >= 1) { _command = new SqlCommand(query, PopCulDatabaseUtil.cnn); SqlDataReader reader = _command.ExecuteReader(); if (reader.Read()) { item = new Item { Id = reader["item_id"].ToString(), Name = reader["item_name"].ToString(), Category = reader["item_category"].ToString(), Description = reader["item_desc"].ToString(), Image = reader["item_img"].ToString(), City = reader["item_city"].ToString(), Phone = reader["item_phone"].ToString(), Price = decimal.Parse(reader["item_price"].ToString()) }; } } else { Message = "No matching items"; } } catch (Exception error) { Message = error.Message; } finally { PopCulDatabaseUtil.CloseConnection(); } return(item); }
internal static bool RegisterCustomer(Account account) { bool validRegister = false; // Assuming all the customer data is valid after the validations try { // Grab customer's info if it exists otherwise insert PopCulDatabaseUtil.OpenConnection(); _command = new SqlCommand("INSERT INTO " + "dbo.account(username,password,first_name," + "last_name,city,province,postal_code," + "phone,email) " + "VALUES(@username,@password,@first_name," + "@last_name,@city,@province," + "@postal_code,@phone,@email)", PopCulDatabaseUtil.cnn); _command.Parameters.AddWithValue("@username", account.UserName); _command.Parameters.AddWithValue("@password", account.Password); _command.Parameters.AddWithValue("@first_name", account.FirstName); _command.Parameters.AddWithValue("@last_name", account.LastName); _command.Parameters.AddWithValue("@city", account.City); _command.Parameters.AddWithValue("@postal_code", account.PostalCode); _command.Parameters.AddWithValue("@phone", account.Phone); _command.Parameters.AddWithValue("@province", account.Province); _command.Parameters.AddWithValue("@email", account.Email); int rowsAffected = _command.ExecuteNonQuery(); if (rowsAffected == 1) { validRegister = true; } } catch (SqlException ex) { Debug.WriteLine("InsertCustomerError: " + ex.Message); } finally { PopCulDatabaseUtil.CloseConnection(); } return(validRegister); }