public int UpdateUser(UserDO u)
{
int _result = 0; // assume failture
try
{
using (SqlConnection connection = new SqlConnection(conn))
{
using (SqlCommand command = new SqlCommand("SP_USERS_UPDATE", connection))
{
command.CommandType = CommandType.StoredProcedure;
command.Parameters.AddWithValue("parmUserId", u.UserId);
command.Parameters.AddWithValue("parmLMSId", u.LMSId);
command.Parameters.AddWithValue("parmUsername", u.Username);
command.Parameters.AddWithValue("parmFirstName", u.LastName);
command.Parameters.AddWithValue("parmLastName", u.LastName);
command.Parameters.AddWithValue("parmPassword", u.Password);
command.Parameters.AddWithValue("parmRole", u.Role);
command.Parameters.AddWithValue("parmActive", u.Active);
command.Parameters.AddWithValue("parmGroupId", u.GroupId);
command.Parameters.AddWithValue("parmCourseId", u.CourseId);
connection.Open();
_result = command.ExecuteNonQuery();
}
}
}
catch (Exception error)
{
this.LogError(error);
}
return(_result);
}
//Call to Register the user into the database
public void RegisterUser(UserDO register)
{
//Try(catch) to open a connection and access the server database
try
{
using (SqlConnection sqlConnect = new SqlConnection(connectionString))
using (SqlCommand sqlCommand = new SqlCommand("USER_REGISTER", sqlConnect))
{
sqlCommand.CommandType = CommandType.StoredProcedure;
sqlConnect.Open();
sqlCommand.Parameters.AddWithValue("@UserFirstName", register.UserFirstName);
sqlCommand.Parameters.AddWithValue("@UserLastName", register.UserLastName);
sqlCommand.Parameters.AddWithValue("@Email", register.Email);
sqlCommand.Parameters.AddWithValue("@UserName", register.UserName);
sqlCommand.Parameters.AddWithValue("@Password", register.Password);
sqlCommand.ExecuteNonQuery();
}
}
catch (Exception error)
{
Logger Error = new Logger();
Error.logErrors(error);
}
finally { }
}
//Call to Update a Users data in the server database
public void UpdateUser(UserDO update)
{
//Try(catch) to open a connection and access the server database
try
{
using (SqlConnection sqlConnect = new SqlConnection(connectionString))
using (SqlCommand sqlCommand = new SqlCommand("UPDATE_USER", sqlConnect))
{
sqlCommand.CommandType = CommandType.StoredProcedure;
sqlConnect.Open();
sqlCommand.Parameters.AddWithValue("@UserID", update.UserID);
sqlCommand.Parameters.AddWithValue("@UserFirstName", update.UserFirstName);
sqlCommand.Parameters.AddWithValue("@UserLastName", update.UserLastName);
sqlCommand.Parameters.AddWithValue("@Email", update.Email);
sqlCommand.Parameters.AddWithValue("@UserName", update.UserName);
sqlCommand.Parameters.AddWithValue("@RoleName", update.RoleName);
sqlCommand.Parameters.AddWithValue("@JobTitle", update.JobTitle);
sqlCommand.ExecuteNonQuery();
}
}
catch (Exception error)
{
Logger Error = new Logger();
Error.logErrors(error);
}
finally { }
}
public UserDO GetUserByUsername(string Username)
{
var user = new UserDO();
try
{
using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["Cardfight Vanguard"].ConnectionString))
{
using (SqlCommand viewComm = new SqlCommand("SP_GET_User", conn))
{
viewComm.CommandType = System.Data.CommandType.StoredProcedure;
viewComm.CommandTimeout = 15;
viewComm.Parameters.AddWithValue("Username", Username);
conn.Open();
using (SqlDataReader reader = viewComm.ExecuteReader())
{
while (reader.Read())
{
user.UserID = reader.GetInt32(reader.GetOrdinal("UserID"));
user.Username = (string)reader["Username"];
user.Password = (string)reader["Password"];
user.RoleID = (int)reader["RoleID"];
}
}
}
}
}
catch (Exception)
{
}
return(user);
}
/// <summary>
///
/// </summary>
/// <param name="user"></param>
/// <returns></returns>
public bool UpdateUser(UserDO user)
{
bool _IsSucceeded = false;
try
{
using (SqlConnection connection = new SqlConnection(conn))
{
using (SqlCommand command = new SqlCommand("SP_Update_User", connection))
{
command.CommandType = System.Data.CommandType.StoredProcedure;
command.Parameters.AddWithValue("parmUserID", user.UserID);
command.Parameters.AddWithValue("parmFirstName", user.FirstName);
command.Parameters.AddWithValue("parmLastName", user.LastName);
command.Parameters.AddWithValue("parmUsername", user.Username);
command.Parameters.AddWithValue("parmPassword", user.Password);
connection.Open();
command.ExecuteNonQuery();
_IsSucceeded = true;
}
}
}
catch (Exception error)
{
_IsSucceeded = false;
throw error;
}
return(_IsSucceeded);
}
//Reading all the users in the database table "Users" into a List.
public List <UserDO> UsersList()
{
List <UserDO> userList = new List <UserDO>();
//Try(catch) to open a connection and access the server database
try
{
using (SqlConnection sqlConnect = new SqlConnection(connectionString))
using (SqlCommand sqlCommand = new SqlCommand("VIEW_USERS", sqlConnect))
{
sqlCommand.CommandType = CommandType.StoredProcedure;
sqlConnect.Open();
using (SqlDataReader dataReader = sqlCommand.ExecuteReader())
{
//Reading the data in each column by column name
while (dataReader.Read())
{
//Creates a New List to store the Users in.
UserDO listUsers = new UserDO();
listUsers.UserID = (int)dataReader["UserID"];
listUsers.ProfileID = (int)dataReader["ProfileID"];
listUsers.UserFirstName = (string)dataReader["UserFirstName"];
listUsers.UserLastName = (string)dataReader["UserLastName"];
listUsers.Email = (string)dataReader["Email"];
listUsers.UserName = (string)dataReader["UserName"];
listUsers.RoleName = (string)dataReader["RoleName"];
listUsers.JobTitle = (string)dataReader["JobTitle"];
listUsers.Salary = (decimal)dataReader["Salary"];
userList.Add(listUsers);
}
}
}
}
catch (Exception error)
{
Logger Error = new Logger();
Error.logErrors(error);
}
finally { }
return(userList);
}
/// <summary>
/// DESCRIPTION:
/// </summary>
/// <param name="inUserId"></param>
/// <returns></returns>
public List <UserDO> GetUsers(int inUserId)
{
List <UserDO> _list = new List <UserDO>();
try
{
using (SqlConnection connection = new SqlConnection(conn))
{
using (SqlCommand cmd = new SqlCommand("SP_USERS_SELECT", connection))
{
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("parmUserId", inUserId);
connection.Open();
using (SqlDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
UserDO u = new UserDO();
u.UserId = (Int64)reader["UserId"];
u.LMSId = (Int64)reader["LMSId"];
u.Username = (string)reader["Username"];
u.FirstName = (string)reader["FirstName"];
u.LastName = (string)reader["LastName"];
u.Password = (string)reader["Password"];
u.Role = (string)reader["Role"];
u.Active = reader.GetBoolean(7) ? 1 : 0; // active column index // reader["Active"];
u.GroupId = (Int64)reader["GroupId"];
u.CourseId = (Int64)reader["CourseId"];
_list.Add(u);
}
}
}
}
}
catch (Exception error)
{
this.LogError(error);
}
return(_list);
}
//private static string conn = @"Data Source=DESKTOP-H52G7QL\SQLEXPRESS;Initial Catalog =Cardfight Vanguard; Integrated Security = True";
//SP_Get_Users_All
/// <summary>
///
/// </summary>
/// <returns></returns>
public List <UserDO> getUserall(int UserID)
{
List <UserDO> _listofUsers = new List <UserDO>();
try
{
using (SqlConnection connection = new SqlConnection(conn))
{
using (SqlCommand command = new SqlCommand("SP_GET_User", connection))
{
command.CommandType = System.Data.CommandType.StoredProcedure;
command.Parameters.AddWithValue("@parmUserID", UserID);
connection.Open();
using (SqlDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
UserDO user = new UserDO();
user.UserID = (int)reader["UserID"];
user.FirstName = (string)reader["First Name"];
user.LastName = (string)reader["Last Name"];
user.Username = (string)reader["Username"];
user.Password = (string)reader["Password"];
_listofUsers.Add(user);
}
}
}
}
}
catch (Exception error)
{
throw error;
}
return(_listofUsers);
}
/// <summary>
///
/// </summary>
/// <param name="u"></param>
/// <returns></returns>
public int CreateUser(UserDO u)
{
int _result = 0; // default in failure
try
{
using (SqlConnection connectionSQL = new SqlConnection(conn))
{
using (SqlCommand command = new SqlCommand("SP_USERS_INSERT", connectionSQL))
{
command.CommandType = CommandType.StoredProcedure;
connectionSQL.Open();
command.Parameters.AddWithValue("parmLMSId", u.LMSId);
command.Parameters.AddWithValue("parmUsername", u.Username);
command.Parameters.AddWithValue("parmFirstName", u.FirstName);
command.Parameters.AddWithValue("parmLastName", u.LastName);
command.Parameters.AddWithValue("parmPassword", u.Password);
command.Parameters.AddWithValue("parmRole", u.Role);
command.Parameters.AddWithValue("parmActive", u.Active);
command.Parameters.AddWithValue("parmGroupId", u.GroupId);
command.Parameters.AddWithValue("parmCourseId", u.CourseId);
command.Parameters.AddWithValue("parmCreatedDate", u.CreatedDate);
command.Parameters.AddWithValue("parmCreatedUserId", u.CreatedUserId);
command.Parameters.AddWithValue("parmEmail", u.Email);
_result = (int)command.ExecuteNonQuery();
}
}
}
catch (Exception error)
{
this.LogError(error);
}
finally
{
}
return(_result);
}