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