public void SportType_CheckDataBeforeSave()
{
//Create a SportType object with data.
aSportType.LoadData(-1, "Weird Sport", 0, "any kind of weird sport", 85,1);
int res = aSportType.CheckData(connString);
Assert.AreEqual(1, res); //Data are ok because the type of sport isn´t in de BD.
//Save the data into BD, and check again.
aSportType = aSportType.SaveData(connString);
Int64 sportTypeIDUsed = aSportType.SportTypeID;
Int64 userIDUsed = aSportType.UserID;
res = aSportType.CheckData(connString);
Assert.AreEqual(-1, res); //Data are not ok, SportTypeName is in de BD.
//Create a SportType object with data:
aSportType.LoadData(-1, "Weird Sport2", -10, "any kind of weird sport", 85,1);
res = aSportType.CheckData(connString);
Assert.AreEqual(-2, res); //Data are not ok, parentSportID does not exist on SportTypes
//Create a SportType object with data:
aSportType.LoadData(-1, "", 0, "any kind of weird sport", 85,1);
res = aSportType.CheckData(connString);
Assert.AreEqual(0, res); //Data are not ok, SportTypeName is empty.
//Create a SportType object with data:
aSportType.LoadData(-1, "Weird Sport2", 0, "any kind of weird sport", -100,1);
res = aSportType.CheckData(connString);
Assert.AreEqual(-3, res); //Data are not ok, userID not in data base.
//Deleting object saved first.
aSportType = aSportType.FindSportTypeByID(connString);
res = aSportType.Delete(connString);
Assert.AreEqual(1, res);
}
public void SportType_FindParent()
{
//Create a SportType object to be the parent
string sportTypeNameUsed = "Deporte Padre";
Int64 parentSportTypeIDUsed = 0;
string memoUsed = "Deporte padre";
Int64 userIDUsed = 85;
SportTypes aSportTypeParent = new SportTypes();
aSportTypeParent.LoadData(-1, sportTypeNameUsed, parentSportTypeIDUsed, memoUsed, userIDUsed,1);
//Save into BD obtaining the sportTypeID
aSportTypeParent = aSportTypeParent.SaveData(connString);
Assert.AreNotEqual(0, aSportTypeParent.SportTypeID); //saving correct data
//Create a SportType object to be the child
sportTypeNameUsed = "Deporte Hijo";
parentSportTypeIDUsed = aSportTypeParent.SportTypeID;
memoUsed = "Deporte Hijo";
userIDUsed = 85;
SportTypes aSportTypeChild = new SportTypes();
aSportTypeChild.LoadData(-1, sportTypeNameUsed, parentSportTypeIDUsed, memoUsed, userIDUsed,1);
//Save into BD obtaining the sportTypeID
aSportTypeChild = aSportTypeChild.SaveData(connString);
Assert.AreNotEqual(0, aSportTypeChild.SportTypeID); //saving correct data
//Try to find the parent sport type.
SportTypes parentSportTypeToFind = new SportTypes();
parentSportTypeToFind = aSportTypeChild.FindParentSportType(connString);
Assert.AreNotEqual(-1, parentSportTypeToFind.SportTypeID); //Found it!!
Assert.AreEqual(aSportTypeParent.SportTypeID, parentSportTypeToFind.SportTypeID);
Assert.AreEqual(aSportTypeParent.SportTypeName, parentSportTypeToFind.SportTypeName);
Assert.AreEqual(aSportTypeParent.ParentSportTypeID, parentSportTypeToFind.ParentSportTypeID,1);
Assert.AreEqual(aSportTypeParent.Memo, parentSportTypeToFind.Memo);
//Deleting object previously saved
int res = aSportTypeParent.Delete(connString);
Assert.AreEqual(1, res);
//Deleting object previously saved
res = aSportTypeChild.Delete(connString);
Assert.AreEqual(1, res);
}
//Update an User object into BD using a connection and a userID, suposses data are checked with checkUserData.
public SportTypes UpdateData(string connString)
{
//Return the user updated. If error return an empty user with userID=-1
SportTypes aSportType = new SportTypes();
using (SqlConnection conn = new SqlConnection(connString))
{
//string pass = userPass;
string query = null;
query = @"UPDATE SportTypes SET SportTypeName=@sportTypeName, ParentSportTypeID=@parentSportTypeID,
Memo=@memo, UserID=@userID, DistForPace=@distForPace
WHERE SportTypeID=@sportTypeID";
using (SqlCommand cmd = new SqlCommand(query, conn))
{
cmd.Parameters.Add(new SqlParameter("@sportTypeID", SqlDbType.BigInt));
cmd.Parameters["@sportTypeID"].Value = sportTypeID;
cmd.Parameters.Add(new SqlParameter("@sportTypeName", SqlDbType.VarChar));
cmd.Parameters["@sportTypeName"].Value = sportTypeName;
cmd.Parameters.Add(new SqlParameter("@parentSportTypeID", SqlDbType.BigInt));
cmd.Parameters["@parentSportTypeID"].Value = parentSportTypeID;
cmd.Parameters.Add(new SqlParameter("@memo", SqlDbType.VarChar));
cmd.Parameters["@memo"].Value = memo;
cmd.Parameters.Add(new SqlParameter("@userID", SqlDbType.BigInt));
cmd.Parameters["@userID"].Value = userID;
cmd.Parameters.Add(new SqlParameter("@distForPace", SqlDbType.Float));
cmd.Parameters["@distForPace"].Value = distForPace;
conn.Open();
int res = cmd.ExecuteNonQuery();
//Find the userID asigned.
if (res > 0)
{
aSportType.LoadData(sportTypeID, sportTypeName, parentSportTypeID, memo, userID, distForPace);
}
}
}
return aSportType;
}
//Save SportType object into BD returning the ID of the object inserted. 0 in case of error
public SportTypes SaveData(string connString)
{
Int64 res = 0;
SportTypes aSportType = new SportTypes();
using (SqlConnection conn = new SqlConnection(connString))
{
string query = @"INSERT INTO SportTypes(SportTypeName, ParentSportTypeID, Memo, UserID, DistForPace)
VALUES(@sportTypeName, @parentSportTypeID, @memo, @userID, @distForPace);
SELECT CAST(SCOPE_IDENTITY() AS bigint)";
using (SqlCommand cmd = new SqlCommand(query, conn))
{
cmd.Parameters.Add(new SqlParameter("@sportTypeName", SqlDbType.VarChar));
cmd.Parameters["@sportTypeName"].Value = sportTypeName;
cmd.Parameters.Add(new SqlParameter("@parentSportTypeID", SqlDbType.BigInt));
cmd.Parameters["@parentSportTypeID"].Value = parentSportTypeID;
cmd.Parameters.Add(new SqlParameter("@memo", SqlDbType.VarChar));
cmd.Parameters["@memo"].Value = memo;
cmd.Parameters.Add(new SqlParameter("@userID", SqlDbType.BigInt));
cmd.Parameters["@userID"].Value = userID;
cmd.Parameters.Add(new SqlParameter("@distForPace", SqlDbType.Float));
cmd.Parameters["@distForPace"].Value = distForPace;
conn.Open();
//res = cmd.ExecuteNonQuery();
res = (Int64)cmd.ExecuteScalar();
if (res > 0)
{
aSportType.LoadData(res, sportTypeName, parentSportTypeID, memo, userID, distForPace);
}
}
}
return aSportType;
}
//Find an SportType object by a given name, and return this object. If not find returns an SportType object with ID=-1
public SportTypes FindSportTypeByName(string connString)
{
SportTypes aSportType = new SportTypes();
using (SqlConnection conn = new SqlConnection(connString))
{
string query = "SELECT * from SportTypes WHERE SportTypeName = @sportTypeName AND UserID=@userID";
using (SqlCommand cmd = new SqlCommand(query, conn))
{
cmd.Parameters.Add(new SqlParameter("@sportTypeName", SqlDbType.VarChar));
cmd.Parameters["@SportTypeName"].Value = sportTypeName;
cmd.Parameters.Add(new SqlParameter("@userID", SqlDbType.BigInt));
cmd.Parameters["@userID"].Value = userID;
conn.Open();
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
aSportType.LoadData(reader.GetInt64(0), reader.GetString(1), reader.GetInt64(2), reader.GetString(3), reader.GetInt64(4),reader.GetDouble(5));
}
reader.Close();
}
}
return aSportType;
}
public SportTypes FindParentSportType(string connString)
{
//Return an object containing the parent SportType.
SportTypes aSportType = new SportTypes();
aSportType.SportTypeID = parentSportTypeID;
aSportType.UserID = userID;
aSportType = aSportType.FindSportTypeByID(connString);
return aSportType;
}
//Check if data into de object is correct to save into de BD
public int CheckData(string connString)
{
// 1: Data is ok
// 0: SportType Name not given
//-1: SportTypeName exist on BD.
//-2: ParentSportID does not exist on BD and is different to 0(root)
SportTypes aST = new SportTypes();
int res = 1;
bool sigue = true;
if ((sportTypeName == "") || (sportTypeName == null))
{
sigue = false;
res = 0; //0: SportType Name not given
}
if (sigue)
{
aST.sportTypeName = sportTypeName;
aST.userID = userID;
aST = aST.FindSportTypeByName(connString);
if (aST.SportTypeID != -1)
{
sigue = false;
res = -1; //-1: SportTypeName exist on BD.
}
}
if (sigue)
{
if (parentSportTypeID != 0)
{
aST.sportTypeID = parentSportTypeID;
aST.userID = userID;
aST = aST.FindSportTypeByID(connString);
if (aST.SportTypeID == -1)
{
sigue = false;
res = -2; //-2: ParentSportID does not exist on BD and is different to 0(root)
}
}
}
return res;
}
private void tsBtnSave_Click(object sender, EventArgs e)
{
//Verify all data is correct.
if (txtParentSportTypeID.Text == "")
txtParentSportTypeID.Text = "0";
SportTypes tempData = new SportTypes(sportTypeIDToUpdate,txtSportTypeName.Text,Convert.ToInt64(txtParentSportTypeID.Text),txtMemo.Text,userIDWorking,Convert.ToDouble(txtDistForPace.Text));
int verifyResult = tempData.CheckData(connString);
//if update and user find, verify must be ok
if ((sportTypeIDToUpdate != -1) && (verifyResult == -1))
verifyResult = 1;
//if data verification is ok, then we can save.
if (verifyResult == 1)
{
int position = 0;
if (sportTypeIDToUpdate == -1)
{ //New user
//Takes position
position = dgvSTypes.RowCount;
//Load data into aUSer
Global.sportTypeUsed.LoadData(-1, txtSportTypeName.Text, Convert.ToInt64(txtParentSportTypeID.Text), txtMemo.Text, userIDWorking, Convert.ToDouble(txtDistForPace.Text));
//save data into BD
Global.sportTypeUsed = Global.sportTypeUsed.SaveData(connString);
}
else
{//Update existing user
//Take actual position
position = dgvSTypes.CurrentRow.Index;
//Load data into aUSer
Global.sportTypeUsed.LoadData(sportTypeIDToUpdate, txtSportTypeName.Text, Convert.ToInt64(txtParentSportTypeID.Text), txtMemo.Text, userIDWorking, Convert.ToDouble(txtDistForPace.Text));
//save data into BD
Global.sportTypeUsed = Global.sportTypeUsed.UpdateData(connString);
//aSportType = aSportType.up
}
//Reload data into grid
LoadDataInGrid(dgvSTypes,userIDWorking);
//Goes to the position
dgvSTypes.CurrentCell = dgvSTypes[0, position];
Global.sportTypeUsed.SportTypeID = Convert.ToInt32(dgvSTypes[0, dgvSTypes.CurrentRow.Index].Value);
Global.sportTypeUsed.UserID = userIDWorking;
//Load data into aUser
Global.sportTypeUsed = Global.sportTypeUsed.FindSportTypeByID(connString);
//Loads data into boxes
LoadDataInBoxes(TrainItLibrary.Global.sportTypeUsed);
//Enable normal mode.
setNormalMode();
//Reload Data into combobox
this.sportTypesTableAdapter1.FillBy(this.trainITDataSet.SportTypes, userIDWorking);
}
else //There is an error in data to save.
{
switch (verifyResult)
{
case 0: //SportTypeName not present
MessageBox.Show("Falta el nombre del tipo de deporte.", "Atención", MessageBoxButtons.OK, MessageBoxIcon.Warning);
txtSportTypeName.Focus();
break;
case -1: //SportTypeName exist on BD.
MessageBox.Show("Ya existe ese tipo de deporte en la base de datos.", "Atención", MessageBoxButtons.OK, MessageBoxIcon.Warning);
txtSportTypeName.Focus();
break;
case -2: //ParentSportID does not exist on BD and is different to 0(root)
MessageBox.Show("La familia a la que pertenece el tipo de deporte no existe en la base de datos.", "Atención", MessageBoxButtons.OK, MessageBoxIcon.Warning);
txtSportTypeName.Focus();
break;
}
}
}
private void LoadDataInBoxes( SportTypes aSportType)
{
txtSportTypeID.Text = aSportType.SportTypeID.ToString();
txtSportTypeName.Text = aSportType.SportTypeName;
txtParentSportTypeID.Text = aSportType.ParentSportTypeID.ToString();
txtMemo.Text = aSportType.Memo;
txtDistForPace.Text = aSportType.DistForPace.ToString();
if (txtParentSportTypeID.Text == "0")
chBxNoFamily.Checked = true;
else
{
chBxNoFamily.Checked = false;
}
//Find the name of the parent sport type
SportTypes parent = new SportTypes();
parent = aSportType.FindParentSportType(connString);
cbxSportTypeName.Text = parent.SportTypeName;
}
public void SportType_Update()
{
//Create a SportType object
string sportTypeNameUsed = "Bicicleta";
Int64 parentSportTypeIDUsed = 0;
string memoUsed = "Deportes de bicicleta";
Int64 userIDUsed = 85;
SportTypes aSportType = new SportTypes();
aSportType.LoadData(-1, sportTypeNameUsed, parentSportTypeIDUsed, memoUsed, userIDUsed,1);
//Save into BD obtaining the sportTypeID
aSportType = aSportType.SaveData(connString);
Assert.AreNotEqual(0, aSportType.SportTypeID);
Assert.AreNotEqual(0, aSportType.SportTypeID); //saving correct data
Int64 sportTypeIDUsed = aSportType.SportTypeID;
//Try to find and check values
aSportType = aSportType.FindSportTypeByName(connString);
Assert.AreNotEqual(-1, aSportType.SportTypeID);
Assert.AreEqual(sportTypeNameUsed, aSportType.SportTypeName);
Assert.AreEqual(parentSportTypeIDUsed, aSportType.ParentSportTypeID);
Assert.AreEqual(memoUsed, aSportType.Memo);
Assert.AreEqual(userIDUsed, aSportType.UserID);
//Update values
sportTypeNameUsed = "Bike Updated";
parentSportTypeIDUsed = 0;
memoUsed = "Deportes de bicicleta updated";
userIDUsed = 85;
aSportType.LoadData(sportTypeIDUsed, sportTypeNameUsed, parentSportTypeIDUsed, memoUsed, userIDUsed,1);
aSportType.UpdateData(connString);
//Try to find and check values
aSportType = aSportType.FindSportTypeByName(connString);
Assert.AreNotEqual(-1, aSportType.SportTypeID);
Assert.AreEqual(sportTypeNameUsed, aSportType.SportTypeName);
Assert.AreEqual(parentSportTypeIDUsed, aSportType.ParentSportTypeID);
Assert.AreEqual(memoUsed, aSportType.Memo);
Assert.AreEqual(userIDUsed, aSportType.UserID);
//Deleting object previously saved
int res = aSportType.Delete(connString);
Assert.AreEqual(1, res);
//Try to find again after deleting
aSportType = aSportType.FindSportTypeByID(connString);
Assert.AreEqual(-1, aSportType.SportTypeID);
}