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);
}
}
//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 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);
}
//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 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 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 List <WLCDAO> ViewWLC()
{
List <WLCDAO> _WLCList = new List <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_ViewWLC", _connection))
{
_command.CommandType = CommandType.StoredProcedure;
//connect to the database
_connection.Open();
//open the SQL data reader
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())
{
WLCDAO _userToList = new WLCDAO()
{
Gender = _reader.GetString(0),
Age = _reader.GetInt32(1),
Height = _reader.GetDecimal(2),
Weight = _reader.GetDecimal(3),
Goal = _reader.GetDecimal(4),
GoalTime = _reader.GetDecimal(5),
User_ID = _reader.GetInt32(6),
ID = _reader.GetInt32(7),
Result = _reader.GetDecimal(8),
};
_WLCList.Add(_userToList);
}
}
}
}
}
catch (Exception error)
{
Error_Logger log = new Error_Logger();
log.LogError(error);
}
return(_WLCList);
}
public List <UserDAO> ViewUsers()
{
List <UserDAO> _userlist = new List <UserDAO>();
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_ViewUsers", _connection))
{
_command.CommandType = CommandType.StoredProcedure;
//connect to the database
_connection.Open();
//open the SQL data reader
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())
{
UserDAO _userToList = new UserDAO()
{
User_ID = _reader.GetInt32(0),
UserName = _reader.GetString(1),
Password = _reader.GetString(2),
Role_ID = _reader.GetInt32(3),
};
_userlist.Add(_userToList);
}
}
}
}
}
catch (Exception error)
{
Error_Logger log = new Error_Logger();
log.LogError(error);
}
return(_userlist);
}
public UserDAO LoginUser(UserDAO _userLogin)
{
UserDAO _loginUser = new UserDAO();
try
{
using (SqlConnection _connection = new SqlConnection(connectionstring))
{
using (SqlCommand _command = new SqlCommand("sp_Login", _connection))
{
_command.CommandType = CommandType.StoredProcedure;
_command.Parameters.AddWithValue("@UserName", _userLogin.UserName);
_connection.Open();
using (SqlDataReader _reader = _command.ExecuteReader())
{
while (_reader.Read())
{
_loginUser.User_ID = _reader.GetInt32(0);
_loginUser.UserName = _reader.GetString(1);
_loginUser.Password = _reader.GetString(2);
_loginUser.Role_ID = _reader.GetInt32(3);
}
}
_connection.Close();
}
}
}
catch (Exception error)
{
//Instatiate a new errorlog and name it log
Error_Logger log = new Error_Logger();
//Call the log error method from errorlogger and pass it error value
log.LogError(error);
}
return(_loginUser);
}
public bool UpdateWLC(WLCDAO WLCToUpdate)
{
bool success = false;
try
{
using (SqlConnection _connection = new SqlConnection(connectionstring))
{
using (SqlCommand _command = new SqlCommand("sp_UpdateWLC", _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("@Gender", WLCToUpdate.Gender);
_command.Parameters.AddWithValue("@Age", WLCToUpdate.Age);
_command.Parameters.AddWithValue("@Height", WLCToUpdate.Height);
_command.Parameters.AddWithValue("@Weight", WLCToUpdate.Weight);
_command.Parameters.AddWithValue("@Goal", WLCToUpdate.Goal);
_command.Parameters.AddWithValue("GoalTime", WLCToUpdate.GoalTime);
_command.Parameters.AddWithValue("@Result", WLCToUpdate.Result);
//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);
}