public void Users_loginTest()
{
//Creates an User with data.
Users aUser = new Users(-1, "name", "", DateTime.Now, "", "", "", "");
//Try to save first
aUser = aUser.LoadData(-1, "Nombre", "Apellido", Convert.ToDateTime("12/04/1982"), "NickToDel", "12345678", "12345678", "*****@*****.**");
aUser = aUser.saveUserData(connString);
Assert.AreNotEqual(-1, aUser.userID); //1=save data without any problem.
//Try to find it and take the userID
//findUserByUserName returns a user if find, if not find return a user wit UserID=-1
string expectedUserName = aUser.userName;
Int64 userIDToDelete = aUser.userID;
aUser = aUser.findUserByUserName(connString, expectedUserName);
Assert.AreEqual(expectedUserName, aUser.userName);
Int64 expectedUserID = aUser.userID;
//Correct login
string givenPassword = "******";
aUser = aUser.verifyPass(aUser.userName, givenPassword, connString);
Assert.AreNotEqual(-1, aUser.userID);
//Uncorrect Login
givenPassword = "";
aUser = aUser.verifyPass(aUser.userName, givenPassword, connString);
Assert.AreEqual(-1, aUser.userID);
//Try to Delete previous saved user.
int res = Users.deleteUserByUserID(connString, userIDToDelete);
Assert.AreEqual(1, res); //Deleted with success
}
public void Users_checkUserData()
{
/*Verify the data to save:
* 1: Data is ok to save.
* -1: userName is missing.
* -2: userName exists in data base
* -3: Password lenght is less than 8 characters.
* -4: Password and Confirm are differents.
*/
//Reg ok.---> Before test check that ther insn´t a userName=userNick
Users aUser = new Users(1, "Nombre", "Apellido", DateTime.Now, "userNick", "12345678", "12345678", "*****@*****.**");
int res=0;
res=aUser.checkUserData(connString);
Assert.AreEqual(1,res);
//User name missing
aUser = aUser.LoadData(1, "Nombre", "Apellido", DateTime.Now, "", "12345678", "12345678", "*****@*****.**");
res=0;
res=aUser.checkUserData(connString);
Assert.AreEqual(-1,res);
//Password is different than confirm, but fails because password lenght less than 8
aUser = aUser.LoadData(1, "Nombre", "Apellido", Convert.ToDateTime("12/04/1982"), "userNick", "", "12345678", "*****@*****.**");
res=0;
res=aUser.checkUserData(connString);
Assert.AreEqual(-3,res);
//Password is different than confirm
aUser = aUser.LoadData(1, "Nombre", "Apellido", Convert.ToDateTime("12/04/1982"), "userNick", "12345678", "", "*****@*****.**");
res=0;
res=aUser.checkUserData(connString);
Assert.AreEqual(-4,res);
//Password lenght less than 8
aUser = aUser.LoadData(1, "Nombre", "Apellido", Convert.ToDateTime("12/04/1982"), "userNick", "", "", "*****@*****.**");
res=0;
res=aUser.checkUserData(connString);
Assert.AreEqual(-3,res);
//User exists
aUser = aUser.LoadData(1, "Nombre", "Apellido", Convert.ToDateTime("12/04/1982"), "admin", "12345678", "12345678", "*****@*****.**");
res = 0;
res = aUser.checkUserData(connString);
Assert.AreEqual(-2, res);
}
//Delete a user using his userID.
public static int deleteUserByUserID(string connString, Int64 aUserID)
{
//Return 1 if deleted,-1 if any problem.
int res = -1;
Users aUser = new Users();
using (SqlConnection conn = new SqlConnection(connString))
{
string query = "delete from Users where UserID = @userID";
using (SqlCommand cmd = new SqlCommand(query, conn))
{
cmd.Parameters.Add(new SqlParameter("@userID", SqlDbType.BigInt));
cmd.Parameters["@userID"].Value = aUserID;
conn.Open();
res=cmd.ExecuteNonQuery();
}
}
return res;
}
//Try to validate password, and then goes into the app.
private void btAccept_Click(object sender, EventArgs e)
{
Boolean sigue = true;
//Takes user ID clicked
userNameGiven = txtUser.Text;
userPassGiven = txtPass.Text;
//Verify if an user has been entered.
if (userNameGiven=="")
{
MessageBox.Show("No has introducido un nombre de ususario", "Atención", MessageBoxButtons.OK, MessageBoxIcon.Error);
sigue = false;
}
//Verify password
if (sigue)
{
Users aUser = new Users(-1, "", "", DateTime.Now, "", "", "", "");
aUser = aUser.verifyPass (userNameGiven, userPassGiven, connString);
userIDGiven = aUser.userID;
if (aUser.userID == -1)
{
sigue = false;
MessageBox.Show("Usuario o contraseña incorrectos", "Atención", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
if (aUser.userID == -2)
{
sigue = false;
MessageBox.Show("No se puede conectar con el servidor de datos");
}
}
//give or not access depending de result of the query
if (sigue)
{
accessGranted = true;
Global.userUsed = Global.userUsed.findUserByUserID(connString, userIDGiven);
Global.userNameWorking = userNameGiven;
Close();
}
}
private void tsBtnNew_Click(object sender, EventArgs e)
{
//Put form on edit mode.
setEditMode();
//Clear all data fields.
clearUserTextBoxes();
userIDToUpdate = -1;
//Set userID to -1;
txtUserID.Text = "";
//Allow to write password
txtUserPass.ReadOnly = false;
txtConfirm.ReadOnly = false;
//Reset user data
aUser = aUser.Reset();
//Put focus on first component
txtFirstName.Focus();
}
private void tsBtnPrevious_Click(object sender, EventArgs e)
{
if (this.dgvUsers.RowCount > 0)
{
if (dgvUsers.CurrentRow.Index > 0)
{
dgvUsers.CurrentCell = dgvUsers[0, dgvUsers.CurrentRow.Index - 1];
//Takes the userID
int userID = Convert.ToInt32(dgvUsers[0, dgvUsers.CurrentRow.Index].Value);
//Find the User and load into aUser
aUser = aUser.findUserByUserID(connString, userID);
//LoadData in boxes
LoadDataInBoxes(aUser);
}
}
}
private void tsBtnDelete_Click(object sender, EventArgs e)
{
if (dgvUsers.RowCount > 0)
{
string message = "Va a borrar el usuario : " + aUser.userName + ".\n" +
"Esto incluye todos los datos asociados a el.\n" +
"¿Esta seguro de hacerlo?";
DialogResult valor = MessageBox.Show(message, "Atención...", MessageBoxButtons.OKCancel, MessageBoxIcon.Question);
if (valor == DialogResult.OK)
{
int position = dgvUsers.CurrentRow.Index;
int totalRegs = dgvUsers.RowCount;
if (position + 1 == totalRegs) position--;
//Delete data.
int res = Users.deleteUserByUserID(connString, aUser.userID);
if (dgvUsers.RowCount > 0)
{
//Update data grid.
LoadDataInGrid(this.dgvUsers);
//Moves to position near deleted
dgvUsers.CurrentCell = dgvUsers[0, position];
//Load data from user in position
int userIDToFind = Convert.ToInt32(dgvUsers[0, position].Value);
aUser = aUser.findUserByUserID(connString, userIDToFind);
}
else
{
aUser = aUser.LoadData(-1, "", "", DateTime.Now, "", "", "", "");
}
//Load Data in boxes
LoadDataInBoxes(aUser);
//Put form in normal mode.
setNormalMode();
userIDToUpdate = -1;
}
}
}
private void tsBtnEdit_Click(object sender, EventArgs e)
{
if (this.dgvUsers.RowCount > 0)
{
//Put form on edit mode.
setEditMode();
//Take de UserID of the reg being updated
userIDToUpdate = Convert.ToInt32(txtUserID.Text);
//Load data for the user being updated
aUser = aUser.findUserByUserID(connString, userIDToUpdate);
//Not allow to update userPass
txtUserPass.ReadOnly = true;
txtConfirm.ReadOnly = true;
//Énables button to change password
btnChangePassword.Enabled = true;
//Put focus on first component
txtFirstName.Focus();
}
}
public void Users_TestFindUserByID()
{
//Creates an User with data.
int userID = 78;
Users aUser = new Users(-1, "", "", DateTime.Now, "", "", "", "");
//findUserByUserID returns a user if find, if not find return a user wit UserID=-1
aUser = aUser.findUserByUserID (connString, userID);
Assert.AreNotEqual(-1, aUser.userID);
Assert.AreEqual(userID, aUser.userID);
}
private void tsBtnCancel_Click(object sender, EventArgs e)
{
//Puts form in Normal mode.
setNormalMode();
clearUserTextBoxes();
userIDToUpdate = -1;
if (dgvUsers.RowCount > 0)
{
//Goes to current reg
dgvUsers.CurrentCell = dgvUsers[0, dgvUsers.CurrentRow.Index];
//Takes user ID clicked
int userID = Convert.ToInt32(dgvUsers[0, dgvUsers.CurrentRow.Index].Value);
//Load data into aUser
aUser = aUser.findUserByUserID(connString, userID);
//Loads data into boxes
LoadDataInBoxes(aUser);
}
}
//Save an User object into BD using a connection, suposses data are checked with checkUserData.
public Users saveUserData(string connString)
{
//Return the user saved. If error return an empty user with userId=-1
Users aUser = new Users();
using (SqlConnection conn = new SqlConnection(connString))
{
string pass = userPass;
string query = String.Format(@"INSERT INTO Users(UserFirstName, UserSecondName, UserBdate, UserName, UserPass, UserMail)
VALUES(@userFirstName,@userSecondName,@userBDate,@userName,PwdEncrypt('{0}'),@userMail)", pass);
using (SqlCommand cmd = new SqlCommand(query, conn))
{
cmd.Parameters.Add(new SqlParameter("@userFirstName", SqlDbType.VarChar));
cmd.Parameters["@userFirstName"].Value = userFirstName;
cmd.Parameters.Add(new SqlParameter("@userSecondName", SqlDbType.VarChar));
cmd.Parameters["@userSecondName"].Value = userSecondName;
cmd.Parameters.Add(new SqlParameter("@userBDate", SqlDbType.Date));
cmd.Parameters["@userBDate"].Value = userBDate;
cmd.Parameters.Add(new SqlParameter("@userName", SqlDbType.VarChar));
cmd.Parameters["@userName"].Value = userName;
cmd.Parameters.Add(new SqlParameter("@userMail", SqlDbType.VarChar));
cmd.Parameters["@userMail"].Value = userMail;
conn.Open();
int res = cmd.ExecuteNonQuery();
//Find the userID asigned.
if (res >= 0)
{
string user = userName;
aUser = aUser.findUserByUserName(connString, user);
}
}
}
return aUser;
}
//verifies a userName/password combination
public Users verifyPass(string userName, string userPass, string connString)
{
//return the user found if login is correct, if not return a user with userID=-1
Users tempUser = new Users();
int count = 0;
//Comprobamos el login.
using (SqlConnection conn = new SqlConnection(connString))
{
string query = String.Format(@"select * from Users where UserName=@userLeido and PWdCompare('{0}',UserPass)=1", userPass);
using (SqlCommand cmd = new SqlCommand(query, conn))
{
cmd.Parameters.Add(new SqlParameter("@userLeido", SqlDbType.VarChar));
cmd.Parameters["@userLeido"].Value = userName;
try
{
conn.Open();
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
count++;
tempUser.userID = reader.GetInt64(0);
tempUser.userFirstName = reader.GetString(1);
tempUser.userSecondName = reader.GetString(2);
tempUser.userBDate = Convert.ToDateTime(reader.GetDateTime(3));
tempUser.userName = reader.GetString(4);
tempUser.userPass = reader.GetSqlBinary(5).ToString();
tempUser.userConfirmPassword = tempUser.userPass;
tempUser.userMail = reader.GetString(6);
}
reader.Close();
}
catch
{
tempUser.userID = -2;
}
}
}
return tempUser;
}
private void dgvUsers_Click(object sender, EventArgs e)
{
if (dgvUsers.RowCount > 0)
{
//Takes user ID clicked
int userID = Convert.ToInt32(dgvUsers[0, dgvUsers.CurrentRow.Index].Value);
//Load data into aUser
aUser = aUser.findUserByUserID(connString, userID);
//Loads data into boxes
LoadDataInBoxes(aUser);
}
}
//Load data into a user object
public Users LoadData(Int64 UserID, string UserFirstName, string UserSecondName, DateTime UserBDate, string UserLoginName,
string UserPassword, string UserConfirmPassword, string UserMail)
{
Users aUser = new Users(UserID, UserFirstName, UserSecondName, UserBDate, UserLoginName, UserPassword, UserConfirmPassword, UserMail);
return aUser;
}
public void Users_TestFindUserByUserName()
{
//Creates an User with data.
string userName = "******"; //Put here the username of a user that exits in the BD.
Users aUser = new Users(-1, "", "", DateTime.Now, "", "", "", "");
//findUserByUserName returns a user if find, if not find return a user wit UserID=-1
aUser = aUser.findUserByUserName(connString, userName);
Assert.AreNotEqual(-1, aUser.userID);
Assert.AreEqual(userName, aUser.userName);
}
public void Users_TestReset()
{
Users aUser = new Users(-1, "Nombre", "Apellido", DateTime.Now, "userNick", "1234", "1234", "*****@*****.**");
aUser = aUser.Reset();
Assert.AreEqual(-1, aUser.userID);
Assert.AreEqual(null, aUser.userFirstName);
Assert.AreEqual(null, aUser.userSecondName);
Assert.AreEqual(DateTime.Now, aUser.userBDate);
Assert.AreEqual(null, aUser.userName);
Assert.AreEqual(null, aUser.userPass);
Assert.AreEqual(null, aUser.userConfirmPassword);
Assert.AreEqual(null, aUser.userMail);
}
public void Users_UpdateTestWithPass()
{
//Creates an User with data.
Users aUser = new Users(-1, "name", "", DateTime.Now, "", "", "", "");
//Try to save first
aUser = aUser.LoadData(-1, "Nombre", "Apellido", Convert.ToDateTime("12/04/1982"), "NickToDel", "12345678", "12345678", "*****@*****.**");
aUser = aUser.saveUserData(connString);
Assert.AreNotEqual(-1, aUser.userID); //1=save data without any problem.
//Try to find it and take the userID
//findUserByUserName returns a user if find, if not find return a user wit UserID=-1
string expectedUserName = aUser.userName;
aUser = aUser.findUserByUserName(connString, expectedUserName);
Assert.AreEqual(expectedUserName, aUser.userName); //User finded
Int64 expectedUserID = aUser.userID;
//Try to Update previous saved user.
string newPass="******";
aUser.userPass = newPass;
aUser.userConfirmPassword = newPass;
aUser = aUser.updateUserData(connString, true);
aUser = aUser.verifyPass(aUser.userName, newPass, connString);
Assert.AreNotEqual(-1, aUser.userID); //Verify if new password was stored correctly
//Try to Delete previous saved user.
Int64 res = Users.deleteUserByUserID(connString, aUser.userID);
Assert.AreEqual(1, res); //Deleted with success
////Try to find it
////findUserByUserName returns a user if find, if not find return a user wit UserID=-1
//expectedUserName = aUser.userName;
//aUser = aUser.findUserByUserName(connString, expectedUserName);
//Assert.AreEqual(-1, aUser.userID); //user not found because was deleted
}
public void Users_UpdateTestWithoutPass()
{
//Creates an User with data.
Users aUser = new Users(-1, "Nombre", "Apellido", Convert.ToDateTime("12/04/1982"), "NickToDel", "12345678", "12345678", "*****@*****.**");
//Try to save first
aUser = aUser.saveUserData(connString);
Assert.AreNotEqual(-1, aUser.userID); //1=save data without any problem.
//Try to find it and take the userID
//findUserByUserName returns a user if find, if not find return a user wit UserID=-1
Users aUserToFind = aUser.findUserByUserName(connString, aUser.userName);
Assert.AreEqual(aUser.userName, aUserToFind.userName);
Int64 expectedUserID = aUser.userID;
//Try to Update previous saved user.
aUser.userSecondName = "Second Name Updated";
aUser = aUser.updateUserData(connString,false);
Assert.AreNotEqual(-1, aUser.userID); //1=update data without any problem.
//Try to find it
//findUserByUserName returns a user if find, if not find return a user wit UserID=-1
aUserToFind = aUser.findUserByUserID(connString,aUser.userID);
Assert.AreEqual(aUser.userName, aUserToFind.userName);
//Verify the update.
Assert.AreEqual(aUser.userSecondName,aUserToFind.userSecondName);
//Try to Delete previous saved user.
int res = Users.deleteUserByUserID(connString, aUser.userID);
Assert.AreEqual(1, res); //Deleted with success
//Try to find it
aUser = aUser.findUserByUserID(connString, aUser.userID);
Assert.AreEqual(-1, aUser.userID); //user not found because was deleted
}
public void Users_TestSaveUser()
{
//Creates anUser with data.
Users aUser = new Users(-1, "Nombre", "Apellido", Convert.ToDateTime("12/04/1982"), "userNick", "12345678", "12345678", "*****@*****.**");
aUser = aUser.saveUserData(connString);
Assert.AreNotEqual(-1, aUser.userID); //1=save data without any problem.
}
//Find an user into de DB. Given a user Name and returns all data into de Users object. If not find return a user with userID=-1.
public Users findUserByUserName(string connString, string userName)
{
Users aUser = new Users(-1, "", "", DateTime.Now, "", "", "", "");
using (SqlConnection conn = new SqlConnection(connString))
{
string query = "select UserID, UserFirstName, UserSecondName, UserBdate, UserName, UserPass, UserMail from Users where UserName = @userName";
using (SqlCommand cmd = new SqlCommand(query, conn))
{
cmd.Parameters.Add(new SqlParameter("@userName", SqlDbType.VarChar));
cmd.Parameters["@userName"].Value = userName;
conn.Open();
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
aUser.userID = reader.GetInt64(0);
aUser.userFirstName = reader.GetString(1);
aUser.userSecondName = reader.GetString(2);
aUser.userBDate = Convert.ToDateTime(reader.GetDateTime(3));
aUser.userName = reader.GetString(4);
aUser.userPass = reader.GetSqlBinary(5).ToString();
aUser.userConfirmPassword = aUser.userPass;
aUser.userMail = reader.GetString(6);
}
reader.Close();
}
}
return aUser;
}
private bool profileChanged(Users users)
{
bool changed = false;
if (txtFirstName.Text != Global.userUsed.userFirstName)
changed = true;
if (!changed)
{
if (txtSecondName.Text == Global.userUsed.userSecondName)
changed = false;
else changed = true;
}
if (!changed)
{
if (txtMail.Text == Global.userUsed.userMail)
changed = false;
else changed = true;
}
if (!changed)
{
if (txtUserName.Text == Global.userUsed.userName)
changed = false;
else changed = true;
}
if ((!changed) && passwordChange)
{
if (txtUserPass.Text == Global.userUsed.userPass)
changed = false;
else changed = true;
}
if (!changed)
{
if (dtpBDate.Value == Global.userUsed.userBDate)
changed = false;
else changed = true;
}
return changed;
}
//Reset the user object
public Users Reset()
{
Users aUser = new Users();
return aUser;
}
private void txtUserName_Validating(object sender, CancelEventArgs e)
{
bool error = false;
//check for username missing
if (txtUserName.Text == "")
{
error = true;
MessageBox.Show("Falta el nombre de usuario para poder acceder al programa.");
}
//Check if user name exist only if was changed
if ((!error) && (txtUserName.Text!=Global.userUsed.userName) )
{
Users aUserFound = new Users();
aUserFound = aUserFound.findUserByUserName(connString, txtUserName.Text);
if (aUserFound.userID != -1)
{
error = true;
MessageBox.Show("Ya existe ese nombre de usuario en la base de datos.");
}
}
if (error)
{
txtUserName.BackColor = Color.Red;
}
else
{
txtUserName.BackColor = SystemColors.Window;
}
}
//Update an User object into BD using a connection and a userID, suposses data are checked with checkUserData.
public Users updateUserData(string connString, Boolean savePassword)
{
//Return the user updated. If error return an empty user with userID=-1
Users aUser = new Users();
using (SqlConnection conn = new SqlConnection(connString))
{
//string pass = userPass;
string query = null;
if (!savePassword)
{
query = @"UPDATE Users SET UserFirstName=@userFirstName, UserSecondName=@userSecondName,
UserBdate=@userBDate, UserName=@userName , UserMail=@userMail
WHERE UserID=@userID";
}
else
{
query = String.Format(@"UPDATE Users SET UserFirstName=@userFirstName, UserSecondName=@userSecondName,
UserBdate=@userBDate, UserName=@userName, UserPass=PwdEncrypt('{0}'), UserMail=@userMail
WHERE UserID=@userID", userPass);
}
using (SqlCommand cmd = new SqlCommand(query, conn))
{
cmd.Parameters.Add(new SqlParameter("@userID", SqlDbType.BigInt));
cmd.Parameters["@userID"].Value = userID;
cmd.Parameters.Add(new SqlParameter("@userFirstName", SqlDbType.VarChar));
cmd.Parameters["@userFirstName"].Value = userFirstName;
cmd.Parameters.Add(new SqlParameter("@userSecondName", SqlDbType.VarChar));
cmd.Parameters["@userSecondName"].Value = userSecondName;
cmd.Parameters.Add(new SqlParameter("@userBDate", SqlDbType.Date));
cmd.Parameters["@userBDate"].Value = userBDate;
cmd.Parameters.Add(new SqlParameter("@userName", SqlDbType.VarChar));
cmd.Parameters["@userName"].Value = userName;
cmd.Parameters.Add(new SqlParameter("@userMail", SqlDbType.VarChar));
cmd.Parameters["@userMail"].Value = userMail;
conn.Open();
int res = cmd.ExecuteNonQuery();
//Find the userID asigned.
if (res > 0)
{
aUser = aUser.findUserByUserID(connString, userID);
}
}
}
return aUser;
}
private void FUsers_Load(object sender, EventArgs e)
{
//Load data into data grid
LoadDataInGrid(dgvUsers);
if (this.dgvUsers.RowCount > 0)
{
//Takes the userID of the first
int userID = Convert.ToInt32(dgvUsers[0, dgvUsers.CurrentRow.Index].Value);
//Find the User and load into aUser
aUser = aUser.findUserByUserID(connString, userID);
//LoadData in boxes
LoadDataInBoxes(aUser);
}
}
private void LoadDataInBoxes(Users aUser)
{
txtUserID.Text = aUser.userID.ToString();
txtFirstName.Text = aUser.userFirstName;
txtSecondName.Text = aUser.userSecondName;
dtpBDate.Value = aUser.userBDate;
txtUserName.Text = aUser.userName;
txtUserPass.Text = aUser.userPass;
txtConfirm.Text = aUser.userConfirmPassword;
txtMail.Text = aUser.userMail;
}
public void Users_TestDeleteUserName()
{
//Creates an User with data.
Users aUser = new Users(-1, "name", "", DateTime.Now, "", "", "", "");
//Try to save first
aUser = aUser.LoadData(-1, "Nombre", "Apellido", Convert.ToDateTime("12/04/1982"), "NickToDel", "12345678", "12345678", "*****@*****.**");
aUser = aUser.saveUserData(connString);
Assert.AreNotEqual(-1, aUser.userID); //1=save data without any problem.
//Try to find it
//findUserByUserName returns a user if find, if not find return a user wit UserID=-1
string expectedUserName = aUser.userName;
aUser = aUser.findUserByUserName(connString, expectedUserName);
Assert.AreNotEqual(-1, aUser.userID); //User finded
//Try to Delete previous saved user.
int res = Users.deleteUserByUserID(connString, aUser.userID);
Assert.AreEqual( 1, res); //Deleted with success
//Try to find it
//findUserByUserName returns a user if find, if not find return a user wit UserID=-1
aUser = aUser.findUserByUserName(connString, expectedUserName);
Assert.AreEqual(-1, aUser.userID); //User not found because was deleted
}
