public List <UserDM> ReadUser(SqlParameter[] parameters, string statement)
{
using (SqlConnection connection = new SqlConnection(connectionString))
{
using (SqlCommand command = new SqlCommand(statement, connection))
{
command.CommandType = CommandType.StoredProcedure;
if (parameters != null)
{
command.Parameters.AddRange(parameters);
}
connection.Open();
SqlDataReader data = command.ExecuteReader();
List <UserDM> users = new List <UserDM>();
while (data.Read())
{
UserDM user = new UserDM();
user.Id = Convert.ToInt32(data["Id"]);
user.Username = data["Username"].ToString();
user.Password = data["Password"].ToString();
user.Role = data["Role"].ToString();
users.Add(user);
}
return(users);
}
}
}
public List <UserDM> Read(SqlParameter[] parameters, string statement)
{
using (SqlConnection connection = new SqlConnection(connectionString))
{
using (SqlCommand command = new SqlCommand(statement, connection))
{
command.CommandType = CommandType.StoredProcedure;
if (parameters != null)
{
command.Parameters.AddRange(parameters);
}
connection.Open();
SqlDataReader data = command.ExecuteReader();
List <UserDM> people = new List <UserDM>();
while (data.Read())
{
UserDM user = new UserDM();
user.UserId = data["UserId"].ToString();
user.UserName = data["UserName"].ToString();
user.Password = data["Password"].ToString();
user.SecLev = data["SecLev"].ToString();
people.Add(user);
}
return(people);
}
}
}
public void DeleteUser(UserDM user)
{
try
{
using (SqlConnection myConnection = new SqlConnection(ConnectionString))
{
using (SqlCommand cmd = new SqlCommand("DeleteUser", myConnection))
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@userName", user.userName);
cmd.Parameters.AddWithValue("@userPassword", user.userPassword);
cmd.Parameters.AddWithValue("@userStreet", user.userStreet);
cmd.Parameters.AddWithValue("@userCity", user.userCity);
cmd.Parameters.AddWithValue("@userState", user.userState);
cmd.Parameters.AddWithValue("@userZipcode", user.userZipcode);
cmd.Parameters.AddWithValue("@userID", user.userID);
cmd.Parameters.AddWithValue("@userID", user.userID);
cmd.Parameters.AddWithValue("@userPosition", user.userPosition);
myConnection.Open();
cmd.ExecuteNonQuery();
}
}
}
catch (Exception e)
{
Console.WriteLine(e);
}
}
public void CreateUser(UserDM user)
{
try
{
//Creating a way of adding new user information to my database
using (SqlConnection myConnection = new SqlConnection(ConnectionString))
{
using (SqlCommand cmd = new SqlCommand("AddUser", myConnection))
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@userFirstName", user.userFirstName);
cmd.Parameters.AddWithValue("@userLastName", user.userLastName);
cmd.Parameters.AddWithValue("@userName", user.userName);
cmd.Parameters.AddWithValue("@userPassword", user.userPassword);
cmd.Parameters.AddWithValue("@userEmail", user.userEmail);
cmd.Parameters.AddWithValue("@userPhoneNumber", user.userPhoneNumber);
cmd.Parameters.AddWithValue("@userStreet", user.userStreet);
cmd.Parameters.AddWithValue("@userCity", user.userCity);
cmd.Parameters.AddWithValue("@userState", user.userState);
cmd.Parameters.AddWithValue("@userZipcode", user.userZipcode);
cmd.Parameters.AddWithValue("@userPosition", user.userPosition);
myConnection.Open();
cmd.ExecuteNonQuery();
}
}
}
catch (Exception e)
{
Console.WriteLine(e);
}
}
public UserDM GetUser(UserDM veriUser)
{
SqlParameter[] parameters = new SqlParameter[] {
new SqlParameter("@UserName", veriUser.UserName)
, new SqlParameter("@Password", veriUser.Password)
};
return(Read(parameters, "GetUser").SingleOrDefault());
}
public void CreateUser(UserDM user)
{
SqlParameter[] parameters = new SqlParameter[] {
new SqlParameter("@UserName", user.UserName)
, new SqlParameter("@Password", user.Password)
, new SqlParameter("@SecLev", user.SecLev)
};
dataWriter.Write(parameters, "CreateUser");
logs.LogError("Event", "An User has been added to the database", "Class:UserDAO, Method:AddUser");
}
public void EditUserById(UserDM user)
{
SqlParameter[] parameters = new SqlParameter[] {
new SqlParameter("@UserName", user.UserName)
, new SqlParameter("@Password", user.Password)
, new SqlParameter("@SecLev", user.SecLev)
, new SqlParameter("@Id", user.UserId)
};
dataWriter.Write(parameters, "UpdateUsers");
logs.LogError("Event", "An User has been updated", "Class:UserDAO, Method: UpdateUser");
}
public UserDM GetUserInfoByNameandPass(string userName, string pass)
{
try
{
using (SqlConnection myConnection = new SqlConnection(ConnectionString))
{
using (SqlCommand cmd = new SqlCommand("GetUserInfoByNameandPass", myConnection))
{
myConnection.Open();
var cust = new UserDM();
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@userName", userName);
cmd.Parameters.AddWithValue(@"userPassword", pass);
using (var reader = cmd.ExecuteReader())
{
if (reader.HasRows)
{
while (reader.Read())
{
if (!reader.IsDBNull(reader.GetOrdinal("userID")))
{
cust.userID = reader.GetInt32(reader.GetOrdinal("userID"));
}
if (!reader.IsDBNull(reader.GetOrdinal("userName")))
{
cust.userName = reader.GetString(reader.GetOrdinal("userName"));
}
if (!reader.IsDBNull(reader.GetOrdinal("userPassword")))
{
cust.userPassword = reader.GetString(reader.GetOrdinal("userPassword"));
}
if (!reader.IsDBNull(reader.GetOrdinal("userPosition")))
{
cust.userPosition = reader.GetString(reader.GetOrdinal("userPosition"));
}
}
}
}
return(cust);
}
}
}
catch (Exception e)
{
return(null);
}
}
public void AddUser(UserDM user)
{
SqlParameter[] parameters = new SqlParameter[]
{
new SqlParameter("@Username", user.Username)
, new SqlParameter("@Password", user.Password)
, new SqlParameter("@Role", user.Role)
};
try
{
dataWriter.Write(parameters, "CreateUser");
logger.LogError("Event", "The user was able to add a user", "Class: UserDAO & Method Name: AddUser");
}
catch (Exception e)
{
logger.LogError("Error", "The user was not able to add a user", "Class: UserDAO & Method Name: AddUser");
}
}
public List <UserDM> DisplayCustomer()
{
List <UserDM> userList = new List <UserDM>();
SqlConnection myConnection = new SqlConnection(ConnectionString);
try
{
using (var conn = new SqlConnection(ConnectionString))
{
myConnection.Open();
using (SqlCommand cmd = new SqlCommand("CustomerList", myConnection))
{
cmd.Connection = myConnection;
cmd.CommandType = CommandType.StoredProcedure;
using (var reader = cmd.ExecuteReader())
{
if (reader.HasRows)
{
while (reader.Read())
{
var cust = new UserDM();
if (!reader.IsDBNull(reader.GetOrdinal("userFirstName")))
{
cust.userFirstName = reader.GetString(reader.GetOrdinal("userFirstName"));
}
if (!reader.IsDBNull(reader.GetOrdinal("userLastName")))
{
cust.userLastName = reader.GetString(reader.GetOrdinal("userLastName"));
}
if (!reader.IsDBNull(reader.GetOrdinal("userName")))
{
cust.userName = reader.GetString(reader.GetOrdinal("userName"));
}
if (!reader.IsDBNull(reader.GetOrdinal("userPassword")))
{
cust.userPassword = reader.GetString(reader.GetOrdinal("userPassword"));
}
if (!reader.IsDBNull(reader.GetOrdinal("userEmail")))
{
cust.userEmail = reader.GetString(reader.GetOrdinal("userEmail"));
}
if (!reader.IsDBNull(reader.GetOrdinal("userPhoneNumber")))
{
cust.userPhoneNumber = reader.GetString(reader.GetOrdinal("userPhoneNumber"));
}
if (!reader.IsDBNull(reader.GetOrdinal("userStreet")))
{
cust.userStreet = reader.GetString(reader.GetOrdinal("userStreet"));
}
if (!reader.IsDBNull(reader.GetOrdinal("userCity")))
{
cust.userCity = reader.GetString(reader.GetOrdinal("userCity"));
}
if (!reader.IsDBNull(reader.GetOrdinal("userState")))
{
cust.userState = reader.GetString(reader.GetOrdinal("userState"));
}
if (!reader.IsDBNull(reader.GetOrdinal("userZipcode")))
{
cust.userZipcode = reader.GetInt32(reader.GetOrdinal("userZipcode"));
}
if (!reader.IsDBNull(reader.GetOrdinal("userID")))
{
cust.userID = reader.GetInt32(reader.GetOrdinal("userID"));
}
if (!reader.IsDBNull(reader.GetOrdinal("userPosition")))
{
cust.userPosition = reader.GetString(reader.GetOrdinal("userPosition"));
}
userList.Add(cust);
}
}
}
}
}
return(userList);
}
catch (Exception e)
{
return(null);
}
}
public UserDM GetUserInfoByUserID(int id)
{
try
{
using (SqlConnection myConnection = new SqlConnection(ConnectionString))
{
using (SqlCommand cmd = new SqlCommand("GetUserInfoByUserID", myConnection))
{
myConnection.Open();
var cust = new UserDM();
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@userID", id);
List <UserDM> userList = new List <UserDM>();
using (var reader = cmd.ExecuteReader())
{
if (reader.HasRows)
{
while (reader.Read())
{
if (!reader.IsDBNull(reader.GetOrdinal("userFirstName")))
{
cust.userFirstName = reader.GetString(reader.GetOrdinal("userFirstName"));
}
if (!reader.IsDBNull(reader.GetOrdinal("userLastName")))
{
cust.userLastName = reader.GetString(reader.GetOrdinal("userLastName"));
}
if (!reader.IsDBNull(reader.GetOrdinal("userName")))
{
cust.userName = reader.GetString(reader.GetOrdinal("userName"));
}
if (!reader.IsDBNull(reader.GetOrdinal("userPassword")))
{
cust.userPassword = reader.GetString(reader.GetOrdinal("userPassword"));
}
if (!reader.IsDBNull(reader.GetOrdinal("userEmail")))
{
cust.userEmail = reader.GetString(reader.GetOrdinal("userEmail"));
}
if (!reader.IsDBNull(reader.GetOrdinal("userPhoneNumber")))
{
cust.userPhoneNumber = reader.GetString(reader.GetOrdinal("userPhoneNumber"));
}
if (!reader.IsDBNull(reader.GetOrdinal("userStreet")))
{
cust.userStreet = reader.GetString(reader.GetOrdinal("userStreet"));
}
if (!reader.IsDBNull(reader.GetOrdinal("userCity")))
{
cust.userCity = reader.GetString(reader.GetOrdinal("userCity"));
}
if (!reader.IsDBNull(reader.GetOrdinal("userState")))
{
cust.userState = reader.GetString(reader.GetOrdinal("userState"));
}
if (!reader.IsDBNull(reader.GetOrdinal("userZipcode")))
{
cust.userZipcode = reader.GetInt32(reader.GetOrdinal("userZipcode"));
}
if (!reader.IsDBNull(reader.GetOrdinal("userID")))
{
cust.userID = reader.GetInt32(reader.GetOrdinal("userID"));
}
if (!reader.IsDBNull(reader.GetOrdinal("userPosition")))
{
cust.userPosition = reader.GetString(reader.GetOrdinal("userPosition"));
}
}
}
}
return(cust);
}
}
}
catch (Exception e)
{
return(null);
}
}
