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