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