public void CreateShoppingCart(shoppingcartDAO shoppingcartToCreate)
{
try
{
using (SqlConnection _connection = new SqlConnection(connectionstring))
{
using (SqlCommand _command = new SqlCommand("Sp_CreateShoppingCart", _connection))
{
_command.CommandType = CommandType.StoredProcedure;
_command.Parameters.AddWithValue("@Albums_ID", shoppingcartToCreate.Albums_ID);
_command.Parameters.AddWithValue("@Clothing_ID", shoppingcartToCreate.Clothing_ID);
_command.Parameters.AddWithValue("@Instruments_ID", shoppingcartToCreate.Instruments_ID);
_command.Parameters.AddWithValue("@User_ID", shoppingcartToCreate.User_ID);
_connection.Open();
_command.ExecuteNonQuery();
_connection.Close();
_connection.Dispose();
}
}
}
catch (Exception _Error)
{
Error_Logger Log = new Error_Logger();
Log.Errorlogger(_Error);
}
}
public List <shoppingcartDAO> GetAllShoppingCarts()
{
List <shoppingcartDAO> _cartList = new List <shoppingcartDAO>();
try
{
using (SqlConnection _connection = new SqlConnection(connectionstring))
{
using (SqlCommand _command = new SqlCommand("Sp_ViewAllShoppingCarts", _connection))
{
_command.CommandType = CommandType.StoredProcedure;
_connection.Open();
using (SqlDataReader _reader = _command.ExecuteReader())
{
while (_reader.Read())
{
shoppingcartDAO _cartToList = new shoppingcartDAO();
_cartToList.ShoppingCart_ID = _reader.GetInt32(0);
_cartToList.Albums_ID = _reader.GetInt32(1);
_cartToList.Clothing_ID = _reader.GetInt32(2);
_cartToList.Instruments_ID = _reader.GetInt32(3);
_cartToList.User_ID = _reader.GetInt32(4);
_cartList.Add(_cartToList);
}
}
}
}
}
catch (Exception _Error)
{
Error_Logger Log = new Error_Logger();
Log.Errorlogger(_Error);
}
return(_cartList);
}
public List <instrumentsDAO> GetAllInstruments()
{
List <instrumentsDAO> _instrumentslist = new List <instrumentsDAO>();
try
{
using (SqlConnection _connection = new SqlConnection(connectionstring))
{
using (SqlCommand _command = new SqlCommand("Sp_ViewInstruments", _connection))
{
_command.CommandType = CommandType.StoredProcedure;
_connection.Open();
using (SqlDataReader _reader = _command.ExecuteReader())
{
while (_reader.Read())
{
instrumentsDAO _instrumentsToList = new instrumentsDAO();
_instrumentsToList.Instruments_ID = _reader.GetInt32(0);
_instrumentsToList.InstrumentName = _reader.GetString(1);
_instrumentsToList.InstrumentDescription = _reader.GetString(2);
_instrumentsToList.InstrumentPrice = _reader.GetDecimal(3);
_instrumentsToList.InstrumentsQuantity = _reader.GetInt32(4);
_instrumentslist.Add(_instrumentsToList);
}
}
}
}
}
catch (Exception _Error)
{
Error_Logger Log = new Error_Logger();
Log.Errorlogger(_Error);
}
return(_instrumentslist);
}
public void CreateMember(bandmembersDAO memberToCreate)
{
try
{
using (SqlConnection _connection = new SqlConnection(connectionstring))
{
using (SqlCommand _command = new SqlCommand("Sp_CreateBandMembers", _connection))
{
_command.CommandType = CommandType.StoredProcedure;
_command.Parameters.AddWithValue("@Name", memberToCreate.MemberName);
_command.Parameters.AddWithValue("@Bio", memberToCreate.MemberBio);
_command.Parameters.AddWithValue("@DateOfBirth", memberToCreate.DateOfBirth);
_command.Parameters.AddWithValue("@BirthLocation", memberToCreate.BirthLocation);
_connection.Open();
_command.ExecuteNonQuery();
_connection.Close();
_connection.Dispose();
}
}
}
catch (Exception _Error)
{
Error_Logger Log = new Error_Logger();
Log.Errorlogger(_Error);
}
}
public bool DeleteMember(bandmembersDAO memberToDelete)
{
bool yes = false;
try
{
using (SqlConnection _connection = new SqlConnection(connectionstring))
{
using (SqlCommand _command = new SqlCommand("Sp_DeleteBandMember", _connection))
{
_command.CommandType = CommandType.StoredProcedure;
_command.Parameters.AddWithValue("@BandMembers_ID", memberToDelete.BandMembers_ID);
_connection.Open();
_command.ExecuteNonQuery();
yes = true;
_connection.Close();
}
}
}
catch (Exception _Error)
{
Error_Logger Log = new Error_Logger();
Log.Errorlogger(_Error);
}
if (yes == true)
{
}
return(yes);
}
public List <bandmembersDAO> GetAllMembers()
{
List <bandmembersDAO> _MemberList = new List <bandmembersDAO>();
try
{
using (SqlConnection _connection = new SqlConnection(connectionstring))
{
using (SqlCommand _command = new SqlCommand("Sp_ViewBandMembers", _connection))
{
_command.CommandType = CommandType.StoredProcedure;
_connection.Open();
using (SqlDataReader _reader = _command.ExecuteReader())
{
while (_reader.Read())
{
bandmembersDAO _memberToList = new bandmembersDAO();
_memberToList.BandMembers_ID = _reader.GetInt32(0);
_memberToList.MemberName = _reader.GetString(1);
_memberToList.MemberBio = _reader.GetString(2);
_memberToList.DateOfBirth = _reader.GetDateTime(3);
_memberToList.BirthLocation = _reader.GetString(4);
_MemberList.Add(_memberToList);
}
}
}
}
}
catch (Exception _Error)
{
Error_Logger Log = new Error_Logger();
Log.Errorlogger(_Error);
}
return(_MemberList);
}
//static string connectionstrings = ConfigurationManager.ConnectionStrings["BasketballDB"].ConnectionString;
public void CreateUser(UserDAO _userCreate)
{
UserDAO _CreateUser = new UserDAO();
try
{
using (SqlConnection _connection = new SqlConnection(connectionstring))
{
using (SqlCommand _command = new SqlCommand("sp_CreateUser", _connection))
{
_command.CommandType = CommandType.StoredProcedure;
_command.Parameters.AddWithValue("@UserName", _userCreate.UserName);
_command.Parameters.AddWithValue("@Password", _userCreate.Password);
_connection.Open();
_command.ExecuteNonQuery();
_connection.Close();
_connection.Dispose();
}
}
}
catch (Exception error)
{
Error_Logger log = new Error_Logger();
log.LogError(error);
}
}
public bool DeleteBMR(int ID)
{
bool success = false;
try
{
//creating connection to the database
using (SqlConnection _connection = new SqlConnection(connectionstring))
{
//this specifies what type of command object for the database
using (SqlCommand _command = new SqlCommand("sp_DeleteBMR", _connection))
{
//this specifies what type of command is being used
_command.CommandType = CommandType.StoredProcedure;
//here is where values are going to be passed to the command
_command.Parameters.AddWithValue("@ID", ID);
//here is where the connection is open
_connection.Open();
//this executes the command
_command.ExecuteNonQuery();
success = true;
_connection.Close();
}
}
}
catch (Exception error)
{
Error_Logger log = new Error_Logger();
log.LogError(error);
}
return(success);
}
public void CreateAlbum(albumDAO albumToCreate)
{
try
{
using (SqlConnection _connection = new SqlConnection(connectionstring))
{
using (SqlCommand _command = new SqlCommand("Sp_CreateAlbum", _connection))
{
_command.CommandType = CommandType.StoredProcedure;
_command.Parameters.AddWithValue("@Name", albumToCreate.AlbumName);
_command.Parameters.AddWithValue("@Description", albumToCreate.AlbumDescription);
_command.Parameters.AddWithValue("@Price", albumToCreate.AlbumPrice);
_command.Parameters.AddWithValue("@YearReleased", albumToCreate.YearReleased);
_command.Parameters.AddWithValue("@NumberOfSongs", albumToCreate.NumberOfSongs);
_command.Parameters.AddWithValue("@AlbumQuantity", albumToCreate.AlbumQuantity);
_connection.Open();
_command.ExecuteNonQuery();
_connection.Close();
_connection.Dispose();
}
}
}
catch (Exception _Error)
{
Error_Logger Log = new Error_Logger();
Log.Errorlogger(_Error);
}
}
public bool deleteUser(UsersDAO userToDelete)
{
bool yes = false;
try
{
//This is creating a connection to the database
using (SqlConnection _connection = new SqlConnection(connectionString))
{
//This specifies what type of command
using (SqlCommand _command = new SqlCommand("SP_User_Delete", _connection))
{
//This specifies what type of command is being used
_command.CommandType = CommandType.StoredProcedure;
//Here is where the Values will be passed to the command
_command.Parameters.AddWithValue("@UserID", userToDelete.UserID);
//Here is where the connection is opened
_connection.Open();
//This will excute the command
_command.ExecuteNonQuery();
yes = true;
_connection.Close();
}
}
}
catch (Exception error)
{
Error_Logger Log = new Error_Logger();
Log.Errorlogger(error);
}
if (yes == true)
{
}
return(yes);
}
public userDAO CreateShoppingCartOnRegister1(userDAO shoppingCartFromRegister1)
{
try
{
using (SqlConnection _connection = new SqlConnection(connectionstring))
{
using (SqlCommand _command = new SqlCommand("Sp_CreateCartOnRegisterPart1", _connection))
{
_command.CommandType = CommandType.StoredProcedure;
_command.Parameters.AddWithValue("Username", shoppingCartFromRegister1.Username);
using (SqlDataReader _reader = _command.ExecuteReader())
{
while (_reader.Read())
{
userDAO _dataToRead = new userDAO();
_dataToRead.User_ID = _reader.GetInt32(0);
_dataToRead.Username = _reader.GetString(1);
_dataToRead.Password = _reader.GetString(2);
_dataToRead.Role_ID = _reader.GetInt32(3);
shoppingCartFromRegister1 = _dataToRead;
}
}
}
}
}
catch (Exception _Error)
{
Error_Logger Log = new Error_Logger();
Log.Errorlogger(_Error);
}
return(shoppingCartFromRegister1);
}
public void Createuser(userDAO userToCreate)
{
try
{
using (SqlConnection _connection = new SqlConnection(connectionstring))
{
using (SqlCommand _command = new SqlCommand("Sp_CreateUser", _connection))
{
_command.CommandType = CommandType.StoredProcedure;
_command.Parameters.AddWithValue("@Username", userToCreate.Username);
_command.Parameters.AddWithValue("@Password", userToCreate.Password);
_command.Parameters.AddWithValue("@Role_ID", userToCreate.Role_ID);
_connection.Open();
_command.ExecuteNonQuery();
_connection.Close();
_connection.Dispose();
}
}
}
catch (Exception _Error)
{
Error_Logger Log = new Error_Logger();
Log.Errorlogger(_Error);
}
}
public List <userDAO> GetAllUsers()
{
List <userDAO> _userlist = new List <userDAO>();
try
{
using (SqlConnection _connection = new SqlConnection(connectionstring))
{
using (SqlCommand _command = new SqlCommand("Sp_ViewUsers", _connection))
{
_command.CommandType = CommandType.StoredProcedure;
_connection.Open();
using (SqlDataReader _reader = _command.ExecuteReader())
{
while (_reader.Read())
{
userDAO _userToList = new userDAO();
_userToList.User_ID = _reader.GetInt32(0);
_userToList.Username = _reader.GetString(1);
_userToList.Password = _reader.GetString(2);
_userToList.Role_ID = _reader.GetInt32(3);
_userlist.Add(_userToList);
}
}
}
}
}
catch (Exception _Error)
{
Error_Logger Log = new Error_Logger();
Log.Errorlogger(_Error);
}
return(_userlist);
}
public List <shoppingcartDAO> GetPrices()
{
List <shoppingcartDAO> _ListPrices = new List <shoppingcartDAO>();
try
{
using (SqlConnection _connection = new SqlConnection(connectionstring))
{
using (SqlCommand _command = new SqlCommand("Sp_PricePull", _connection))
{
_command.CommandType = CommandType.StoredProcedure;
_connection.Open();
using (SqlDataReader _reader = _command.ExecuteReader())
{
while (_reader.Read())
{
shoppingcartDAO _PriceToList = new shoppingcartDAO();
_PriceToList.AlbumPrice = _reader.GetDecimal(0);
_PriceToList.ClothingPrice = _reader.GetDecimal(1);
_PriceToList.InstrumentsPrice = _reader.GetDecimal(2);
_ListPrices.Add(_PriceToList);
}
}
}
}
}
catch (Exception _Error)
{
Error_Logger Log = new Error_Logger();
Log.Errorlogger(_Error);
}
return(_ListPrices);
}
public void CreateInstruments(instrumentsDAO instrumentsToCreate)
{
try
{
using (SqlConnection _connection = new SqlConnection(connectionstring))
{
using (SqlCommand _command = new SqlCommand("Sp_CreateInstrument", _connection))
{
_command.CommandType = CommandType.StoredProcedure;
_command.Parameters.AddWithValue("@Name", instrumentsToCreate.InstrumentName);
_command.Parameters.AddWithValue("@Description", instrumentsToCreate.InstrumentDescription);
_command.Parameters.AddWithValue("@Price", instrumentsToCreate.InstrumentPrice);
_command.Parameters.AddWithValue("@InstrumentsQuantity", instrumentsToCreate.InstrumentsQuantity);
_connection.Open();
_command.ExecuteNonQuery();
_connection.Close();
_connection.Dispose();
}
}
}
catch (Exception _Error)
{
Error_Logger Log = new Error_Logger();
Log.Errorlogger(_Error);
}
}
public void CreateBMR(BMRDAO _BMRCreate)
{
BMRDAO _CreateBMR = new BMRDAO();
try
{
using (SqlConnection _connection = new SqlConnection(connectionstring))
{
using (SqlCommand _command = new SqlCommand("sp_CreateBMR", _connection))
{
_command.CommandType = CommandType.StoredProcedure;
_command.Parameters.AddWithValue("@Height", _BMRCreate.Height);
_command.Parameters.AddWithValue("@Weight", _BMRCreate.Weight);
_command.Parameters.AddWithValue("@Age", _BMRCreate.Age);
_command.Parameters.AddWithValue("@Gender", _BMRCreate.Gender);
_command.Parameters.AddWithValue("@User_ID", _BMRCreate.User_ID);
_command.Parameters.AddWithValue("Result", _BMRCreate.Result);
_connection.Open();
_command.ExecuteNonQuery();
_connection.Close();
_connection.Dispose();
}
}
}
catch (Exception error)
{
Error_Logger log = new Error_Logger();
log.LogError(error);
}
}
public void SendInstrumentID(shoppingcartDAO instrumentidToSend)
{
int SendInstrumentID = new int();
try
{
using (SqlConnection _connection = new SqlConnection(connectionstring))
{
using (SqlCommand _command = new SqlCommand("Sp_SendInstrumentID", _connection))
{
_command.CommandType = CommandType.StoredProcedure;
_command.Parameters.AddWithValue("@Instruments_ID", instrumentidToSend.Instruments_ID);
_command.Parameters.AddWithValue("@User_ID", instrumentidToSend.User_ID);
_connection.Open();
_command.ExecuteNonQuery();
_connection.Close();
}
}
}
catch (Exception _Error)
{
Error_Logger Log = new Error_Logger();
Log.Errorlogger(_Error);
}
}
//CHANGE TO USER
public bool UpdateUser(UserDAO UserToUpdate)
{
bool success = false;
try
{
using (SqlConnection _connection = new SqlConnection(connectionstring))
{
using (SqlCommand _command = new SqlCommand("sp_UpdateUser", _connection))
{
//this specifies what type of command is being used
_command.CommandType = CommandType.StoredProcedure;
//here is where values are going to be passed to the command
_command.Parameters.AddWithValue("@User_ID", UserToUpdate.User_ID);
_command.Parameters.AddWithValue("@UserName", UserToUpdate.UserName);
_command.Parameters.AddWithValue("@Password", UserToUpdate.Password);
_command.Parameters.AddWithValue("@Role_ID", UserToUpdate.Role_ID);
//here is where the connection is open
_connection.Open();
//this executes the command
_command.ExecuteNonQuery();
success = true;
_connection.Close();
}
}
}
catch (Exception error)
{
Error_Logger log = new Error_Logger();
log.LogError(error);
}
return(success);
}
public void CreateClothing(clothingDAO clothingToCreate)
{
try
{
using (SqlConnection _connection = new SqlConnection(connectionstring))
{
using (SqlCommand _command = new SqlCommand("Sp_CreateClothing", _connection))
{
_command.CommandType = CommandType.StoredProcedure;
_command.Parameters.AddWithValue("@TypeOFClothing", clothingToCreate.TypeOFClothing);
_command.Parameters.AddWithValue("@Description", clothingToCreate.ClothingDescription);
_command.Parameters.AddWithValue("@Sizes", clothingToCreate.Sizes);
_command.Parameters.AddWithValue("@Price", clothingToCreate.ClothingPrice);
_command.Parameters.AddWithValue("@Name", clothingToCreate.ClothingName);
_command.Parameters.AddWithValue("@ClothingQuantity", clothingToCreate.ClothingQuantity);
_connection.Open();
_command.ExecuteNonQuery();
_connection.Close();
_connection.Dispose();
}
}
}
catch (Exception _Error)
{
Error_Logger Log = new Error_Logger();
Log.Errorlogger(_Error);
}
}
public void GetClothingID(shoppingcartDAO clothingidToGet)
{
int GetClothingID = new int();
try
{
using (SqlConnection _connection = new SqlConnection(connectionstring))
{
using (SqlCommand _command = new SqlCommand("Sp_GetClothingID", _connection))
{
_command.CommandType = CommandType.StoredProcedure;
_command.Parameters.AddWithValue("@Clothing_ID", clothingidToGet.Clothing_ID);
_command.Parameters.AddWithValue("@User_ID", clothingidToGet.User_ID);
_connection.Open();
_command.ExecuteNonQuery();
_connection.Close();
}
}
}
catch (Exception _Error)
{
Error_Logger Log = new Error_Logger();
Log.Errorlogger(_Error);
}
}
public void createShirt(ShirtsDAO shirtToCreate)
{
try
{
//This is creating a connection to the database
using (SqlConnection _connection = new SqlConnection(connectionString))
{
//This specifies what type of command
using (SqlCommand _command = new SqlCommand("SP_Shirt_Create", _connection))
{
//This specifies what type of command is being used
_command.CommandType = CommandType.StoredProcedure;
//Here is where the Values will be passed to the command
_command.Parameters.AddWithValue("@Size", shirtToCreate.Size);
_command.Parameters.AddWithValue("@Color", shirtToCreate.Color);
_command.Parameters.AddWithValue("@ShirtPrice", shirtToCreate.ShirtPrice);
//Here is where the connection is opened
_connection.Open();
//This will excute the command
_command.ExecuteNonQuery();
_connection.Close();
}
}
}
catch (Exception error)
{
Error_Logger Log = new Error_Logger();
Log.Errorlogger(error);
}
}
public List <BMIDAO> GetBMIByUser_ID(int User_ID)
{
List <BMIDAO> _BMIToGet = new List <BMIDAO>();
try
{ //esablishing the connection for the database
using (SqlConnection _connection = new SqlConnection(connectionstring))
{ //establishing the command to pass to the database and defining the command
using (SqlCommand _command = new SqlCommand("sp_GetBMIByUser_ID", _connection))
{
//this specifies what type of command is being used
_command.CommandType = CommandType.StoredProcedure;
//here is where values are going to be passed to the command
_command.Parameters.AddWithValue("@User_ID", User_ID);
//here is where the connection is open
_connection.Open();
//this executes the command
_command.ExecuteNonQuery();
using (SqlDataReader _reader = _command.ExecuteReader())
{
//loop through the dataset or command and write each element to the _playerToList using the player object class
//while (_reader.Read())
//{
// List
// _BMIToGet.Height = _reader.GetDecimal(0);
// _BMIToGet.Weight = _reader.GetDecimal(1);
// _BMIToGet.User_ID = _reader.GetInt32(2);
// _BMIToGet.ID = _reader.GetInt32(3);
//}
while (_reader.Read())
{
BMIDAO _BMIToList = new BMIDAO()
{
Height = _reader.GetDecimal(0),
Weight = _reader.GetDecimal(1),
User_ID = _reader.GetInt32(2),
ID = _reader.GetInt32(3),
Result = _reader.GetDecimal(4),
};
_BMIToGet.Add(_BMIToList);
}
}
}
}
}
catch (Exception error)
{
Error_Logger log = new Error_Logger();
log.LogError(error);
}
return(_BMIToGet);
}
public void UpdateCart(CartDAO cartToUpdate)
{
try
{
//This is creating a connection to the database
//This specifies what type of command
using (SqlConnection _connection = new SqlConnection(connectionString))
using (SqlCommand _command = new SqlCommand("SP_Cart_Update", _connection))
{
//This specifies what type of command is being used
_command.CommandType = CommandType.StoredProcedure;
//Here is where the Values will be passed to the command
_command.Parameters.AddWithValue("@CartID", cartToUpdate.CartID);
_command.Parameters.AddWithValue("@PantsID", cartToUpdate.PantsID);
_command.Parameters.AddWithValue("@ShirtsID", cartToUpdate.ShirtsID);
_command.Parameters.AddWithValue("@UserID", cartToUpdate.UserID);
_command.Parameters.AddWithValue("@ShirtQuanity", cartToUpdate.ShirtQuanity);
_command.Parameters.AddWithValue("@PantQuanity", cartToUpdate.PantQuanity);
_command.Parameters.AddWithValue("@TotalPrice", cartToUpdate.TotalPrice);
_command.Parameters.AddWithValue("@ShirtPrice", cartToUpdate.ShirtPrice);
_command.Parameters.AddWithValue("@PantPrice", cartToUpdate.PantPrice);
//Here is where the connection is opened
_connection.Open();
using (SqlDataReader _reader = _command.ExecuteReader())
{
while (_reader.Read())
{
cartToUpdate.CartID = _reader.GetInt32(0);
cartToUpdate.PantsID = _reader.GetInt32(1);
cartToUpdate.ShirtsID = _reader.GetInt32(2);
cartToUpdate.UserID = _reader.GetInt32(3);
cartToUpdate.ShirtQuanity = _reader.GetInt32(4);
cartToUpdate.PantQuanity = _reader.GetInt32(5);
cartToUpdate.TotalPrice = _reader.GetInt32(6);
cartToUpdate.ShirtPrice = _reader.GetInt32(7);
cartToUpdate.PantPrice = _reader.GetInt32(8);
}
_connection.Close();
}
//This will excute the command
_command.ExecuteNonQuery();
_connection.Close();
}
}
catch (Exception error)
{
Error_Logger Log = new Error_Logger();
Log.Errorlogger(error);
}
}
public void UpdateUser(UsersDAO userToUpdate)
{
try
{
//This is creating a connection to the database
//This specifies what type of command
using (SqlConnection _connection = new SqlConnection(connectionString))
using (SqlCommand _command = new SqlCommand("SP_User_Update", _connection))
{
//This specifies what type of command is being used
_command.CommandType = CommandType.StoredProcedure;
//Here is where the Values will be passed to the command
_command.Parameters.AddWithValue("@UserID", userToUpdate.UserID);
_command.Parameters.AddWithValue("@Firstname", userToUpdate.Firstname);
_command.Parameters.AddWithValue("@Lastname", userToUpdate.Lastname);
_command.Parameters.AddWithValue("@Address", userToUpdate.Address);
_command.Parameters.AddWithValue("@Zipcode", userToUpdate.Zipcode);
_command.Parameters.AddWithValue("@Phonenumber", userToUpdate.Phonenumber);
_command.Parameters.AddWithValue("@Username", userToUpdate.Username);
_command.Parameters.AddWithValue("@RoleID", userToUpdate.RoleID);
//Here is where the connection is opened
_connection.Open();
using (SqlDataReader _reader = _command.ExecuteReader())
{
while (_reader.Read())
{
userToUpdate.UserID = _reader.GetInt32(0);
userToUpdate.Firstname = _reader.GetString(1);
userToUpdate.Lastname = _reader.GetString(2);
userToUpdate.Address = _reader.GetString(3);
userToUpdate.Zipcode = _reader.GetInt32(4);
userToUpdate.Phonenumber = _reader.GetString(5);
userToUpdate.Username = _reader.GetString(6);
userToUpdate.RoleID = _reader.GetInt32(7);
}
_connection.Close();
}
//This will excute the command
_command.ExecuteNonQuery();
_connection.Close();
}
}
catch (Exception error)
{
Error_Logger Log = new Error_Logger();
Log.Errorlogger(error);
}
}
public List <CartDAO> GetAllCarts()
{//Create a method that will get all my Books and place them in a list named _booklist
List <CartDAO> _cartlist = new List <CartDAO>();
try
{
//Establishing the connection for the database
using (SqlConnection _connection = new SqlConnection(connectionString))
{
using (SqlCommand _command = new SqlCommand("SP_CartView", _connection))
{
//Establishing the command to pass to the database
//and defining the command
_command.CommandType = CommandType.StoredProcedure;
//connect to the database
_connection.Open();
//Open the sql data reader
using (SqlDataReader _reader = _command.ExecuteReader())
{
//Loop through the data sets or command and write each element
//to the _bookToList using the book object
while (_reader.Read())
{
CartDAO _cartToList = new CartDAO();
_cartToList.CartID = _reader.GetInt32(0);
_cartToList.PantsID = _reader.GetInt32(1);
_cartToList.ShirtsID = _reader.GetInt32(2);
_cartToList.UserID = _reader.GetInt32(3);
_cartToList.ShirtQuanity = _reader.GetInt32(4);
_cartToList.PantQuanity = _reader.GetInt32(5);
_cartToList.PantPrice = _reader.GetInt32(6);
_cartToList.ShirtPrice = _reader.GetInt32(7);
_cartToList.TotalPrice = _reader.GetInt32(8);
_cartlist.Add(_cartToList);
}
}
}
}
}
catch (Exception error)
{
Error_Logger Log = new Error_Logger();
Log.Errorlogger(error);
}
return(_cartlist);
}
public List <UsersDAO> GetAllUsers()
{//Create a method that will get all my Books and place them in a list named _booklist
List <UsersDAO> _userlist = new List <UsersDAO>();
try
{
//Establishing the connection for the database
using (SqlConnection _connection = new SqlConnection(connectionString))
{
using (SqlCommand _command = new SqlCommand("SP_User_Read", _connection))
{
//Establishing the command to pass to the database
//and defining the command
_command.CommandType = CommandType.StoredProcedure;
//connect to the database
_connection.Open();
//Open the sql data reader
using (SqlDataReader _reader = _command.ExecuteReader())
{
//Loop through the data sets or command and write each element
//to the _bookToList using the book object
while (_reader.Read())
{
UsersDAO _userToList = new UsersDAO();
_userToList.Firstname = _reader.GetString(0);
_userToList.Lastname = _reader.GetString(1);
_userToList.Address = _reader.GetString(2);
_userToList.Zipcode = _reader.GetInt32(3);
_userToList.Phonenumber = _reader.GetString(4);
_userToList.Password = _reader.GetString(5);
_userToList.Username = _reader.GetString(6);
_userToList.UserID = _reader.GetInt32(7);
_userToList.RoleID = _reader.GetInt32(8);
_userlist.Add(_userToList);
}
}
}
}
}
catch (Exception error)
{
Error_Logger Log = new Error_Logger();
Log.Errorlogger(error);
}
return(_userlist);
}
public WLCDAO GetWLCByUser_ID(int User_ID)
{
WLCDAO _WLCToGet = new WLCDAO();
try
{ //esablishing the connection for the database
using (SqlConnection _connection = new SqlConnection(connectionstring))
{ //establishing the command to pass to the database and defining the command
using (SqlCommand _command = new SqlCommand("sp_GetWLCByUser_ID", _connection))
{
//this specifies what type of command is being used
_command.CommandType = CommandType.StoredProcedure;
//here is where values are going to be passed to the command
_command.Parameters.AddWithValue("@User_ID", User_ID);
//here is where the connection is open
_connection.Open();
//this executes the command
_command.ExecuteNonQuery();
using (SqlDataReader _reader = _command.ExecuteReader())
{
//loop through the dataset or command and write each element to the _playerToList using the player object class
while (_reader.Read())
{
_WLCToGet.Gender = _reader.GetString(0);
_WLCToGet.Age = _reader.GetInt32(1);
_WLCToGet.Height = _reader.GetDecimal(2);
_WLCToGet.Weight = _reader.GetDecimal(3);
_WLCToGet.Goal = _reader.GetDecimal(4);
_WLCToGet.GoalTime = _reader.GetDecimal(5);
_WLCToGet.User_ID = _reader.GetInt32(6);
_WLCToGet.ID = _reader.GetInt32(7);
}
}
}
}
}
catch (Exception error)
{
Error_Logger log = new Error_Logger();
log.LogError(error);
}
return(_WLCToGet);
}
public CartDAO _createCart(CartDAO _cartCreate)
{
CartDAO _createCart = new CartDAO();
try
{
using (SqlConnection _connection = new SqlConnection(connectionString))
{
using (SqlCommand _command = new SqlCommand("SP_Cart_Create", _connection))
{
_command.CommandType = CommandType.StoredProcedure;
_command.Parameters.AddWithValue("@CartID", _cartCreate.CartID);
_command.Parameters.AddWithValue("@PantsID", _cartCreate.PantsID);
_command.Parameters.AddWithValue("@ShirtsID", _cartCreate.ShirtsID);
_command.Parameters.AddWithValue("@UserID", _cartCreate.UserID);
_command.Parameters.AddWithValue("@ShirtQuanity", _cartCreate.ShirtQuanity);
_command.Parameters.AddWithValue("@PantQuanity", _cartCreate.PantQuanity);
_command.Parameters.AddWithValue("@TotalPrice", _cartCreate.TotalPrice);
_command.Parameters.AddWithValue("@ShirtPrice", _cartCreate.ShirtPrice);
_command.Parameters.AddWithValue("@PantPrice", _cartCreate.PantPrice);
_connection.Open();
using (SqlDataReader _reader = _command.ExecuteReader())
{
while (_reader.Read())
{
_createCart.CartID = _reader.GetInt32(0);
_createCart.PantsID = _reader.GetInt32(1);
_createCart.ShirtsID = _reader.GetInt32(2);
_createCart.UserID = _reader.GetInt32(3);
_createCart.ShirtQuanity = _reader.GetInt32(4);
_createCart.PantQuanity = _reader.GetInt32(5);
_createCart.TotalPrice = _reader.GetInt32(6);
_createCart.ShirtPrice = _reader.GetInt32(6);
_createCart.PantPrice = _reader.GetInt32(6);
}
_connection.Close();
}
}
}
}
catch (Exception error)
{
Error_Logger Log = new Error_Logger();
Log.Errorlogger(error);
}
return(_createCart);
}
public CartDAO GetCartById(int CartID)
{//Create a method that will get all my Books and place them in a list named _booklist
CartDAO _cartReturn = new CartDAO();
try
{
//Establishing the connection for the database
using (SqlConnection _connection = new SqlConnection(connectionString))
{
using (SqlCommand _command = new SqlCommand("SP_Get_Cart_By_CartID", _connection))
{
//Establishing the command to pass to the database
//and defining the command
_command.CommandType = CommandType.StoredProcedure;
_command.Parameters.AddWithValue("@CartID", CartID);
//connect to the database
_connection.Open();
//Open the sql data reader
using (SqlDataReader _reader = _command.ExecuteReader())
{
//Loop through the data sets or command and write each element
//to the _bookToList using the book object
while (_reader.Read())
{
_cartReturn.CartID = _reader.GetInt32(0);
_cartReturn.PantsID = _reader.GetInt32(1);
_cartReturn.ShirtsID = _reader.GetInt32(2);
_cartReturn.UserID = _reader.GetInt32(3);
_cartReturn.ShirtQuanity = _reader.GetInt32(4);
_cartReturn.PantQuanity = _reader.GetInt32(4);
_cartReturn.TotalPrice = _reader.GetInt32(5);
_cartReturn.ShirtPrice = _reader.GetInt32(5);
_cartReturn.PantPrice = _reader.GetInt32(5);
}
}
}
}
}
catch (Exception error)
{
Error_Logger Log = new Error_Logger();
Log.Errorlogger(error);
}
return(_cartReturn);
}
public UsersDAO GetUserByID(int userID)
{//Create a method that will get all my Books and place them in a list named _booklist
UsersDAO _userReturn = new UsersDAO();
try
{
//Establishing the connection for the database
using (SqlConnection _connection = new SqlConnection(connectionString))
{
using (SqlCommand _command = new SqlCommand("SP_Get_User_By_UserID", _connection))
{
//Establishing the command to pass to the database
//and defining the command
_command.CommandType = CommandType.StoredProcedure;
_command.Parameters.AddWithValue("@UserID", userID);
//connect to the database
_connection.Open();
//Open the sql data reader
using (SqlDataReader _reader = _command.ExecuteReader())
{
//Loop through the data sets or command and write each element
//to the _bookToList using the book object
while (_reader.Read())
{
_userReturn.UserID = _reader.GetInt32(0);
_userReturn.Firstname = _reader.GetString(1);
_userReturn.Lastname = _reader.GetString(2);
_userReturn.Address = _reader.GetString(3);
_userReturn.Zipcode = _reader.GetInt32(4);
_userReturn.Phonenumber = _reader.GetString(5);
_userReturn.Username = _reader.GetString(6);
_userReturn.RoleID = _reader.GetInt32(7);
}
}
}
}
}
catch (Exception error)
{
Error_Logger Log = new Error_Logger();
Log.Errorlogger(error);
}
return(_userReturn);
}
