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); }