public void DeletePartFromCart(int iUserID, int iPartID)
{
ExceptionHandling e = new ExceptionHandling();
string dbConnection = System.Configuration.ConfigurationManager.ConnectionStrings["dbConnection"].ConnectionString;
try
{
//establish the connection
using (SqlConnection conn = new SqlConnection(dbConnection))
{
// create command
using (SqlCommand command = new SqlCommand("SP_DELETE_PART_FROM_CART", conn))
{
command.CommandType = System.Data.CommandType.StoredProcedure;
command.CommandTimeout = 30;
conn.Open();
command.Parameters.AddWithValue("@parmUserID", SqlDbType.Int).Value = iUserID;
command.Parameters.AddWithValue("@parmPartID", SqlDbType.Int).Value = iPartID;
command.ExecuteNonQuery();
}
conn.Close();
}
}
catch (Exception ex)
{
e.WriteLogger(ex);
e.InsertLoggerToDB(ex);
}
}
public List <Part> SelectPart(Part iPart)
{
List <Part> partList = new List <Part>();
ExceptionHandling e = new ExceptionHandling();
string dbConnection = System.Configuration.ConfigurationManager.ConnectionStrings["dbConnection"].ConnectionString;
try
{
using (SqlConnection conn = new SqlConnection(dbConnection))
{
using (SqlCommand command = new SqlCommand("SP_SELECT_PART", conn))
{
command.CommandType = System.Data.CommandType.StoredProcedure;
command.CommandTimeout = 30;
conn.Open();
using (SqlDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
Part p = new Part();
p.PartID = (int)reader["PartID"];
p.DirtBikeIDFK = (int)reader["DirtBikeIDFK"];
p.PartName = reader["PartName"].ToString();
p.Picture = reader["Picture"].ToString();
p.Price = (decimal)reader["Price"];
p.PartDescription = reader["PartDescription"].ToString();
p.BrandIDFK = (int)reader["BrandIDFK"];
p.BrandName = reader["BrandName"].ToString();
p.CategoryIDFK = (int)reader["CategoryIDFK"];
partList.Add(p);
}
}
command.ExecuteNonQuery();
}
conn.Close();
}
}
catch (Exception ex)
{
e.WriteLogger(ex);
e.InsertLoggerToDB(ex);
}
return(partList);
}
public List <Part> GetPartsInCart(int iUserID)
{
List <Part> listPartsInCart = new List <Part>();
ExceptionHandling e = new ExceptionHandling();
string dbConnection = System.Configuration.ConfigurationManager.ConnectionStrings["dbConnection"].ConnectionString;
try
{
using (SqlConnection conn = new SqlConnection(dbConnection))
{
using (SqlCommand command = new SqlCommand("SP_SELECT_USERS_CART", conn))
{
command.CommandType = System.Data.CommandType.StoredProcedure;
command.CommandTimeout = 30;
conn.Open();
command.Parameters.AddWithValue("@parmUserID", SqlDbType.VarChar).Value = iUserID;
using (SqlDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
Part p = new Part();
p.PartID = (int)reader["PartIDFK"];
p.Price = (decimal)reader["PartPrice"];
p.PartName = reader["PartName"].ToString();
p.BrandName = reader["PartBrand"].ToString();
p.Picture = reader["Picture"].ToString();
p.PartQuantity = (int)reader["Quantity"];
listPartsInCart.Add(p);
}
}
}
conn.Close();
}
}
catch (Exception ex)
{
e.WriteLogger(ex);
e.InsertLoggerToDB(ex);
}
return(listPartsInCart);
}
public bool AddPart(Part iPart)
{
bool result = false;
ExceptionHandling e = new ExceptionHandling();
string dbConnection = System.Configuration.ConfigurationManager.ConnectionStrings["dbConnection"].ConnectionString;
try
{
//establish the connection
using (SqlConnection conn = new SqlConnection(dbConnection))
{
// create command
using (SqlCommand command = new SqlCommand("SP_INSERT_PART", conn))
{
command.CommandType = System.Data.CommandType.StoredProcedure;
command.CommandTimeout = 30;
conn.Open();
command.Parameters.AddWithValue("@parmDirtBikeIDFK", SqlDbType.Int).Value = iPart.DirtBikeIDFK;
command.Parameters.AddWithValue("@parmPartName", SqlDbType.VarChar).Value = iPart.PartName;
command.Parameters.AddWithValue("@parmPicture", SqlDbType.VarChar).Value = iPart.Picture;
command.Parameters.AddWithValue("@parmPrice", SqlDbType.Decimal).Value = iPart.Price;
command.Parameters.AddWithValue("@parmPartDescription", SqlDbType.VarChar).Value = iPart.PartDescription;
command.Parameters.AddWithValue("@parmBrandIDFK", SqlDbType.Int).Value = iPart.BrandIDFK;
command.Parameters.AddWithValue("@parmCategoryIDFK", SqlDbType.Int).Value = iPart.CategoryIDFK;
command.ExecuteNonQuery();
}
conn.Close();
}
result = true;
}
catch (Exception ex)
{
e.WriteLogger(ex);
e.InsertLoggerToDB(ex);
}
return(result);
}
public List <User> ViewAllUsers(User user)
{
List <User> listUsers = new List <User>();
ExceptionHandling e = new ExceptionHandling();
string dbConnection = System.Configuration.ConfigurationManager.ConnectionStrings["dbConnection"].ConnectionString;
try
{
using (SqlConnection conn = new SqlConnection(dbConnection))
{
using (SqlCommand command = new SqlCommand("SP_SELECT_USER", conn))
{
command.CommandType = System.Data.CommandType.StoredProcedure;
command.CommandTimeout = 30;
conn.Open();
using (SqlDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
User u = new User();
u.UserID = (int)reader["UserID"];
u.FirstName = reader["FirstName"].ToString();
u.LastName = reader["LastName"].ToString();
u.UserName = reader["UserName"].ToString();
u.UserPassword = reader["UserPassword"].ToString();
//u.Salt = reader["Salt"].ToString();
u.Email = reader["Email"].ToString();
u.RoleIDFK = (int)reader["RoleIDFK"];
listUsers.Add(u);
}
}
}
conn.Close();
}
}
catch (Exception ex)
{
e.WriteLogger(ex);
e.InsertLoggerToDB(ex);
}
// Find match of user input from list
return(listUsers.ToList());
}
public List <Error> ViewAllErrors(Error er)
{
List <Error> listErrors = new List <Error>();
ExceptionHandling e = new ExceptionHandling();
string dbConnection = System.Configuration.ConfigurationManager.ConnectionStrings["dbConnection"].ConnectionString;
try
{
using (SqlConnection conn = new SqlConnection(dbConnection))
{
using (SqlCommand command = new SqlCommand("SP_SELECT_ERROR", conn))
{
command.CommandType = System.Data.CommandType.StoredProcedure;
command.CommandTimeout = 30;
conn.Open();
using (SqlDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
Error exc = new Error();
exc.ErrorID = (int)reader["ErrorID"];
exc.ErrorDate = (DateTime)reader["ErrorDate"];
exc.StackTrace = reader["StackTrace"].ToString();
exc.Message = reader["ErrorMessage"].ToString();
listErrors.Add(exc);
}
}
command.ExecuteNonQuery();
}
conn.Close();
}
}
catch (Exception x)
{
e.WriteLogger(x);
e.InsertLoggerToDB(x);
}
return(listErrors);
}
public List <Dirtbike> SelectDirtbike(Dirtbike iDirtbike)
{
List <Dirtbike> dirtbikeList = new List <Dirtbike>();
ExceptionHandling e = new ExceptionHandling();
string dbConnection = System.Configuration.ConfigurationManager.ConnectionStrings["dbConnection"].ConnectionString;
try
{
using (SqlConnection conn = new SqlConnection(dbConnection))
{
using (SqlCommand command = new SqlCommand("SP_SELECT_DIRTBIKE", conn))
{
command.CommandType = System.Data.CommandType.StoredProcedure;
command.CommandTimeout = 30;
conn.Open();
using (SqlDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
Dirtbike db = new Dirtbike();
db.DirtBikeID = (int)reader["DirtBikeID"];
db.Make = reader["Make"].ToString();
db.MakeYear = (int)reader["MakeYear"];
db.Model = reader["Model"].ToString();
dirtbikeList.Add(db);
}
}
command.ExecuteNonQuery();
}
conn.Close();
}
}
catch (Exception ex)
{
e.WriteLogger(ex);
e.InsertLoggerToDB(ex);
}
return(dirtbikeList);
}
public bool updateRole(int UserID, int RoleIDFK)
{
bool result = false;
ExceptionHandling e = new ExceptionHandling();
string dbConnection = System.Configuration.ConfigurationManager.ConnectionStrings["dbConnection"].ConnectionString;
try
{
//establish the connection
using (SqlConnection conn = new SqlConnection(dbConnection))
{
// create command
using (SqlCommand command = new SqlCommand("SP_UPDATE_USER_ROLE", conn))
{
command.CommandType = System.Data.CommandType.StoredProcedure;
command.CommandTimeout = 30;
conn.Open();
command.Parameters.AddWithValue("@parmUserID", SqlDbType.Int).Value = UserID;
command.Parameters.AddWithValue("@parmRoleIDFK", SqlDbType.Int).Value = RoleIDFK;
command.ExecuteNonQuery();
}
conn.Close();
}
result = true;
}
catch (Exception ex)
{
e.WriteLogger(ex);
e.InsertLoggerToDB(ex);
}
return(result);
}
public List <Role> SelectRolesFromTable(Role iRole)
{
List <Role> listRoles = new List <Role>();
ExceptionHandling e = new ExceptionHandling();
string dbConnection = System.Configuration.ConfigurationManager.ConnectionStrings["dbConnection"].ConnectionString;
try
{
using (SqlConnection conn = new SqlConnection(dbConnection))
{
using (SqlCommand command = new SqlCommand("SP_SELECT_ROLE", conn))
{
command.CommandType = System.Data.CommandType.StoredProcedure;
command.CommandTimeout = 30;
conn.Open();
using (SqlDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
Role r = new Role();
r.RoleID = (int)reader["RoleID"];
r.RoleName = reader["RoleName"].ToString();
listRoles.Add(r);
}
}
command.ExecuteNonQuery();
}
conn.Close();
}
}
catch (Exception ex)
{
e.WriteLogger(ex);
e.InsertLoggerToDB(ex);
}
return(listRoles);
}
public bool DeletePart(int PartId)
{
bool result = false;
ExceptionHandling e = new ExceptionHandling();
string dbConnection = System.Configuration.ConfigurationManager.ConnectionStrings["dbConnection"].ConnectionString;
try
{
//establish the connection
using (SqlConnection conn = new SqlConnection(dbConnection))
{
// create command
using (SqlCommand command = new SqlCommand("SP_DELETE_PART", conn))
{
command.CommandType = System.Data.CommandType.StoredProcedure;
command.CommandTimeout = 30;
conn.Open();
command.Parameters.AddWithValue("@parmPartID", SqlDbType.Int).Value = PartId;
command.ExecuteNonQuery();
}
conn.Close();
}
result = true;
}
catch (Exception ex)
{
e.WriteLogger(ex);
e.InsertLoggerToDB(ex);
}
return(result);
}
public void UpdatePart(Part iPart)
{
ExceptionHandling e = new ExceptionHandling();
string dbConnection = System.Configuration.ConfigurationManager.ConnectionStrings["dbConnection"].ConnectionString;
try
{
// Update Part in Part table
using (SqlConnection conn = new SqlConnection(dbConnection))
{
using (SqlCommand command = new SqlCommand("SP_UPDATE_PART", conn))
{
command.CommandType = System.Data.CommandType.StoredProcedure;
command.CommandTimeout = 30;
conn.Open();
command.Parameters.AddWithValue("@parmPartID", SqlDbType.Int).Value = iPart.PartID;
command.Parameters.AddWithValue("@parmDirtBikeIDFK", SqlDbType.Int).Value = iPart.DirtBikeIDFK;
command.Parameters.AddWithValue("@parmPartName", SqlDbType.VarChar).Value = iPart.PartName;
command.Parameters.AddWithValue("@parmPicture", SqlDbType.VarChar).Value = iPart.Picture;
command.Parameters.AddWithValue("@parmPrice", SqlDbType.Decimal).Value = iPart.Price;
command.Parameters.AddWithValue("@parmPartDescription", SqlDbType.VarChar).Value = iPart.PartDescription;
command.Parameters.AddWithValue("@parmBrandIDFK", SqlDbType.Int).Value = iPart.BrandIDFK;
command.Parameters.AddWithValue("@parmCategoryIDFK", SqlDbType.Int).Value = iPart.CategoryIDFK;
command.ExecuteNonQuery();
}
conn.Close();
}
// Update Part in Cart
using (SqlConnection conn = new SqlConnection(dbConnection))
{
using (SqlCommand command = new SqlCommand("SP_UPDATE_PART_INFO_IN_CART", conn))
{
command.CommandType = System.Data.CommandType.StoredProcedure;
command.CommandTimeout = 30;
conn.Open();
command.Parameters.AddWithValue("@parmPartID", SqlDbType.Int).Value = iPart.PartID;
command.Parameters.AddWithValue("@parmPartName", SqlDbType.VarChar).Value = iPart.PartName;
command.Parameters.AddWithValue("@parmPicture", SqlDbType.VarChar).Value = iPart.Picture;
command.Parameters.AddWithValue("@parmPartPrice", SqlDbType.Decimal).Value = iPart.Price;
command.ExecuteNonQuery();
}
conn.Close();
}
}
catch (Exception ex)
{
e.WriteLogger(ex);
e.InsertLoggerToDB(ex);
}
}
public void AddPartToCart(int UserIDFK, int PartIDFK, decimal PartPrice, string PartName, string PartBrand, string Picture)
{
ExceptionHandling e = new ExceptionHandling();
int Quantity = 0;
string dbConnection = System.Configuration.ConfigurationManager.ConnectionStrings["dbConnection"].ConnectionString;
try
{
// Grab User's specific part quantity
// If its 0 then add part to Cart Table in DB
// If its not 0 update part quantity: Quantity + 1
using (SqlConnection conn = new SqlConnection(dbConnection))
{
using (SqlCommand command = new SqlCommand("SP_SELECT_PART_QUANTITY", conn))
{
command.CommandType = System.Data.CommandType.StoredProcedure;
command.CommandTimeout = 30;
conn.Open();
command.Parameters.AddWithValue("@parmUserID", SqlDbType.VarChar).Value = UserIDFK;
command.Parameters.AddWithValue("@parmPartID", SqlDbType.VarChar).Value = PartIDFK;
using (SqlDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
Quantity = (int)reader["Quantity"];
}
}
}
conn.Close();
}
// If part qauntity came back 0
if (Quantity == 0)
{
// Add part values to cart table
using (SqlConnection conn = new SqlConnection(dbConnection))
{
using (SqlCommand command = new SqlCommand("SP_INSERT_TO_CART", conn))
{
command.CommandType = System.Data.CommandType.StoredProcedure;
command.CommandTimeout = 30;
conn.Open();
command.Parameters.AddWithValue("@parmUserIDFK", SqlDbType.Int).Value = UserIDFK;
command.Parameters.AddWithValue("@parmPartIDFK", SqlDbType.Int).Value = PartIDFK;
command.Parameters.AddWithValue("@parmPartPrice", SqlDbType.Decimal).Value = PartPrice;
command.Parameters.AddWithValue("@parmPartName", SqlDbType.VarChar).Value = PartName;
command.Parameters.AddWithValue("@parmPartBrand", SqlDbType.VarChar).Value = PartBrand;
command.Parameters.AddWithValue("@parmPicture", SqlDbType.VarChar).Value = Picture;
command.Parameters.AddWithValue("@parmQuantity", SqlDbType.Int).Value = 1;
command.ExecuteNonQuery();
}
conn.Close();
}
}
// If part quantiy did not came back 0
else
{
using (SqlConnection conn = new SqlConnection(dbConnection))
{
using (SqlCommand command = new SqlCommand("SP_UPDATE_PART_QUANTITY", conn))
{
command.CommandType = System.Data.CommandType.StoredProcedure;
command.CommandTimeout = 30;
conn.Open();
command.Parameters.AddWithValue("@parmUserId", SqlDbType.Int).Value = UserIDFK;
command.Parameters.AddWithValue("@parmPartId", SqlDbType.Int).Value = PartIDFK;
command.Parameters.AddWithValue("@parmQuantity", SqlDbType.Int).Value = 1 + Quantity;
command.ExecuteNonQuery();
}
conn.Close();
}
}
}
catch (Exception ex)
{
e.WriteLogger(ex);
e.InsertLoggerToDB(ex);
}
}
public User CreateNewUser(User user)
{
ExceptionHandling e = new ExceptionHandling();
string dbConnection = System.Configuration.ConfigurationManager.ConnectionStrings["dbConnection"].ConnectionString;
try
{
// Check to see if user name is already exsist in database
string dbUsernames;
List <string> listOfDbUsernames = new List <string>();
using (SqlConnection conn = new SqlConnection(dbConnection))
{
using (SqlCommand command = new SqlCommand("SP_SELECT_USERNAMES_FOR_REGISTRATION", conn))
{
command.CommandType = System.Data.CommandType.StoredProcedure;
command.CommandTimeout = 30;
conn.Open();
using (SqlDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
dbUsernames = reader["UserName"].ToString();
listOfDbUsernames.Add(dbUsernames);
}
}
}
conn.Close();
}
// If username already exsist
if (listOfDbUsernames.Contains(user.UserName))
{
user.UserName = "******";
}
// If username does not exsist already
else
{
// Create salt with DateTime now
// Add salt to user password
// Create StringBuilded (Mutable char string) to append bytes of hashed password to (Did not use String because they are mutable)
user.Salt = DateTime.Now.ToString();
string saltAndPassword = user.UserPassword + user.Salt;
StringBuilder hashed = new StringBuilder();
// Take salted password then Hash with SHA256
using (SHA256 sha256Hash = SHA256.Create())
{
// ComputeHash - returns byte array
byte[] bytes = sha256Hash.ComputeHash(Encoding.UTF8.GetBytes(saltAndPassword));
// Convert byte array to a string
for (int i = 0; i < bytes.Length; i++)
{
hashed.Append(bytes[i].ToString("x2"));
}
hashed.ToString();
}
using (SqlConnection conn = new SqlConnection(dbConnection))
{
using (SqlCommand command = new SqlCommand("SP_INSERT_USER", conn))
{
command.CommandType = System.Data.CommandType.StoredProcedure;
command.CommandTimeout = 30;
conn.Open();
command.Parameters.AddWithValue("@parmFirstName", SqlDbType.VarChar).Value = user.FirstName;
command.Parameters.AddWithValue("@parmLastName", SqlDbType.VarChar).Value = user.LastName;
command.Parameters.AddWithValue("@parmUserName", SqlDbType.VarChar).Value = user.UserName;
command.Parameters.AddWithValue("@parmUserPassword", SqlDbType.VarChar).Value = hashed.ToString();
command.Parameters.AddWithValue("@parmSalt", SqlDbType.VarChar).Value = user.Salt;
command.Parameters.AddWithValue("@parmEmail", SqlDbType.VarChar).Value = user.Email;
command.Parameters.AddWithValue("@parmRoleIDFK", SqlDbType.Int).Value = 3;
command.Parameters.AddWithValue("@parmSavedDirtBikeIDFK", SqlDbType.Int).Value = DBNull.Value;
command.ExecuteNonQuery();
}
conn.Close();
}
}
}
catch (Exception ex)
{
e.WriteLogger(ex);
e.InsertLoggerToDB(ex);
}
return(user);
}
public List <User> LoginUser(User user)
{
List <User> listUser = new List <User>();
ExceptionHandling e = new ExceptionHandling();
// Create StringBuilded (Mutable char string) to append bytes of hashed password to
// (Did not use String because they are not mutable)
// Create empty salt string to store salt from User table for matched username
StringBuilder hashed = new StringBuilder();
string salt = "";
string dbConnection = System.Configuration.ConfigurationManager.ConnectionStrings["dbConnection"].ConnectionString;
try
{
// Get users Salt by entered Username
using (SqlConnection conn = new SqlConnection(dbConnection))
{
using (SqlCommand command = new SqlCommand("SP_SELECT_USERS_SALT", conn))
{
command.CommandType = System.Data.CommandType.StoredProcedure;
command.CommandTimeout = 30;
conn.Open();
command.Parameters.AddWithValue("@parmUserName", SqlDbType.VarChar).Value = user.UserName;
using (SqlDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
User u = new User();
u.Salt = reader["Salt"].ToString();
salt = u.Salt;
}
}
}
conn.Close();
}
// Once salt is recieved add it to User Password and hash it
string saltAndPassword = user.UserPassword + salt;
// Take salted password then Hash with SHA256
using (SHA256 sha256Hash = SHA256.Create())
{
// ComputeHash - returns byte array
byte[] bytes = sha256Hash.ComputeHash(Encoding.UTF8.GetBytes(saltAndPassword));
// Convert byte array to a string
for (int i = 0; i < bytes.Length; i++)
{
hashed.Append(bytes[i].ToString("x2"));// x2 is a string formatter. Prints two hexadecimal digits
}
hashed.ToString();
}
using (SqlConnection conn = new SqlConnection(dbConnection))
{
using (SqlCommand command = new SqlCommand("SP_SELECT_USER", conn))
{
command.CommandType = System.Data.CommandType.StoredProcedure;
command.CommandTimeout = 30;
conn.Open();
using (SqlDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
User u = new User();
u.UserID = (int)reader["UserID"];
u.FirstName = reader["FirstName"].ToString();
u.LastName = reader["LastName"].ToString();
u.UserName = reader["UserName"].ToString();
u.UserPassword = reader["UserPassword"].ToString();
u.Salt = reader["Salt"].ToString();
u.Email = reader["Email"].ToString();
u.RoleIDFK = (int)reader["RoleIDFK"];
if (reader["SavedDirtbikeIDFK"] != DBNull.Value)
{
u.SavedDirtbikeIDFK = (int)reader["SavedDirtbikeIDFK"];
}
else
{
}
listUser.Add(u);
}
}
}
conn.Close();
}
}
catch (Exception ex)
{
e.WriteLogger(ex);
e.InsertLoggerToDB(ex);
}
// Find match of user input from list
return(listUser.Where(singleuser => singleuser.UserName == user.UserName &&
singleuser.UserPassword == hashed.ToString()).ToList());
}