//Inserts a new rider into Brevet_Rider public int InsertRider(BrevetRider rider) { try { // (5) Open a connection to the database myDatabase.Open(myConnectionString); int RiderId = rider.RiderId; int BrevetId = rider.BrevetId; // (6) Construct a SELECT statement sqlText = "INSERT INTO Brevet_Rider (riderId, brevetId, isCompleted, finishingTime ) " + "VALUES('" + RiderId + "', '" + BrevetId + "', '" + 'N' + "', " + '0' + ")"; // (7) Execute the SELECT statement myDatabase.ExecuteUpdate(sqlText); return(0); //OK } catch { return(-1); } finally { // (10) Close the database connection myDatabase.Close(); } }
protected void btRegister_Click(object sender, EventArgs e) { Brevet brevet = screenToModel(); BrevetRider rider = new BrevetRider(); rider.BrevetId = Convert.ToInt32(listBoxBrevets.SelectedValue); rider.RiderId = Convert.ToInt32(riderDAO.GetRiderIdByUsername((string)Session["username"])); int insertOk = brevetDAO.InsertRider(rider); if (insertOk == 0) // Insert succeeded { viewStateDetailsDisplayed(); showMessage(); } else if (insertOk == 1) { showErrorMessage("Brevet id " + brevet.BrevetId + " is already in use. No record inserted into the database."); } else { showErrorMessage("No record inserted into the database. " + "THE DATABASE IS TEMPORARILY OUT OF USE."); } }
public List <BrevetRider> GetBrevetResults(int distance, DateTime year, string location) { try { // (5) Open a connection to the database myDatabase.Open(myConnectionString); List <BrevetRider> brevetRiderList = new List <BrevetRider>(); // (6) Construct a SELECT statement sqlText = "SELECT Brevet_Rider.riderId, Brevet_Rider.brevetId, Rider.givenName, Rider.familyName, isCompleted, finishingTime " + "FROM Brevet_Rider INNER JOIN Brevet ON(Brevet_Rider.brevetId = Brevet.brevetId) JOIN Rider ON(Brevet_Rider.riderId = Rider.riderId) " + "WHERE Brevet.distance = '" + distance + "' AND Brevet.brevetDate = '" + year.ToString("yyyy-MM-dd") + "'" + " AND Brevet.location = '" + location + "' AND isCompleted = 'Y'"; // (7) Execute the SELECT statement resultSet = myDatabase.ExecuteQuery(sqlText); // (8) Process the multiple rows in the result set one by one while (resultSet.Read() == true) // 8.1 Move to the next available row { // true = row available // false = no more rows BrevetRider brevetRider = new BrevetRider(); Rider rider = new Rider(); // 8.2 Retrieve column values from the current row in the result set brevetRider.RiderId = (int)resultSet["riderId"]; brevetRider.BrevetId = (int)resultSet["brevetId"]; brevetRider.IsCompleted = (string)resultSet["isCompleted"]; brevetRider.FinishingTime = (string)resultSet["finishingTime"]; rider.FamilyName = (string)resultSet["familyName"]; rider.GivenName = (string)resultSet["givenName"]; brevetRider.Rider = rider; brevetRiderList.Add(brevetRider); } // (9) Close the result set resultSet.Close(); return(brevetRiderList); } catch (Exception) { return(null); } finally { // (10) Close the database connection myDatabase.Close(); } }