private void modelToScreen(Brevet brevet)
{
tbDistance.Text = "" + brevet.Distance;
tbDate.Text = "" + brevet.BrevetDate;
tbLocation.Text = "" + brevet.Location;
tbClimbing.Text = "" + brevet.Climbing.ToString();
}
public int InsertBrevet(Brevet brevet)
{
try
{
myDatabase.Open(myConnectionString);
if (brevetExist(brevet.BrevetId) == true)
{
return(1); // row exists
}
String sqlText = String.Format(
@"INSERT INTO Brevet (brevetid, distance, brevetdate, location, climbing)
VALUES ({0}, '{1}', '{2}', '{3}', '{4}')",
brevet.BrevetId, brevet.Distance, brevet.BrevetDate, brevet.Location, brevet.Climbing);
myDatabase.ExecuteQuery(sqlText);
return(0); //OK
}
catch (Exception)
{
return(-1); // ERROR
}
finally
{
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.");
}
}
/// <summary>
/// Updates an existing Department row in the database.
/// </summary>
/// <param name="Brevet"></param>
/// <returns>0 = OK, -1 = error</returns>
public int UpdateBrevet(Brevet brevet)
{
try
{
myDatabase.Open(myConnectionString);
String sqlText = String.Format(
@"UPDATE Brevet
SET distance = '{0}',
brevetDate = '{1}',
location = '{2}',
climbing = '{3}'
WHERE brevetId = {4}",
brevet.Distance,
brevet.BrevetDate,
brevet.Location,
brevet.Climbing,
brevet.BrevetId);
myDatabase.ExecuteUpdate(sqlText);
return(0); // OK
}
catch (Exception)
{
return(-1); // An error occurred
}
finally
{
myDatabase.Close();
}
}
/// <summary>
/// Inserts a single Department row into the database.
/// </summary>
/// <param name="Brevet"></param>
/// <returns>0 = OK, 1 = insert not allowed, -1 = error</returns>
public int InsertBrevet(Brevet brevet)
{
try
{
myDatabase.Open(myConnectionString);
if (brevetExists(brevet.BrevetId) == true)
{
return(1);
}
String sqlText = String.Format(
@"INSERT INTO Brevet (distance, brevetDate, location, climbing, brevetId)
VALUES ({0}, '{1}', '{2}', '{3}', '{4}')",
brevet.Distance,
brevet.BrevetDate,
brevet.Location,
brevet.Climbing,
brevet.BrevetId);
myDatabase.ExecuteUpdate(sqlText);
return(0); // OK
}
catch (Exception)
{
return(-1); // An error occurred
}
finally
{
myDatabase.Close();
}
}
private Brevet screenToModel()
{
Brevet brevet = new Brevet();
brevet.Distance = Convert.ToInt32(tbDistance.Text.Trim());
brevet.BrevetDate = Convert.ToDateTime(tbDate.Text.Trim());
brevet.Location = tbLocation.Text.Trim();
brevet.Climbing = Convert.ToInt32(tbClimbing.Text.Trim());
return(brevet);
}
protected void listBoxClubs_SelectedIndexChanged(object sender, EventArgs e)
{
int brevetId = Convert.ToInt32(listBoxClubs.SelectedValue);
Brevet brevet = brevetDAO.GetBrevetByBrevetId(brevetId);
if (brevet != null)
{
modelToScreen(brevet);
viewStateDetailsDisplayed();
showNoMessage();
}
}
public List <Brevet> GetAllBrevets()
{
myDatabase = new Database();
List <Brevet> brevetList = new List <Brevet>();
try
{
// (5) Open a connection to the database
myDatabase.Open(myConnectionString);
// (6) Construct a SELECT statement
sqlText =
"SELECT brevetId, distance, brevetDate, location, climbing " +
"FROM Brevet ORDER BY distance ASC, brevetDate ASC, location ASC";
// (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
Brevet brevet = new Brevet();
// 8.2 Retrieve column values from the current row in the result set
brevet.BrevetId = (int)resultSet["brevetId"];
brevet.Distance = (int)resultSet["distance"];
brevet.BrevetDate = (DateTime)resultSet["brevetDate"];
brevet.Location = (string)resultSet["location"];
brevet.Climbing = (int)resultSet["climbing"];
brevetList.Add(brevet);
}
// (9) Close the result set
resultSet.Close();
}
catch (Exception)
{
return(null);
}
// (10) Close the database connection
finally
{
myDatabase.Close();
}
return(brevetList);
}
public List <Brevet> GetAllBrevets()
{
myDatabase = new Database();
try
{
// (5) Open a connection to the database
myDatabase.Open(myConnectionString);
List <Brevet> brevetList = new List <Brevet>();
// (6) Construct a SELECT statement
sqlText =
"SELECT DISTINCT brevetId " +
"FROM Brevet";
// (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
Brevet brevet = new Brevet();
// 8.2 Retrieve column values from the current row in the result set
brevet.BrevetId = (int)resultSet["brevetId"];
brevetList.Add(brevet);
}
// (9) Close the result sethttp://localhost:60574/App_Data/
resultSet.Close();
return(brevetList);
}
catch (Exception)
{
return(null);
}
finally
{
// (10) Close the database connection
myDatabase.Close();
}
}
/*
*
* SEARCH BREVET
* */
public List <Brevet> SearchBrevets(String distance, String year, String location)
{
List <Brevet> brevetList = new List <Brevet>();
IDataReader resultSet;
try
{
myDatabase = new Database();
myDatabase.Open(myConnectionString);
string sqlText =
"SELECT brevetid, distance, brevetdate, location, climbing " +
"FROM brevet " +
"WHERE distance = '" + distance +
"' AND brevetdate LIKE '" + year + "%'" +
" AND location = '" + location +
"' ORDER BY brevetdate;";
resultSet = myDatabase.ExecuteQuery(sqlText);
while (resultSet.Read() == true)
{
Brevet brevet = new Brevet();
brevet.BrevetId = (int)resultSet["brevetid"];
brevet.BrevetDate = (DateTime)resultSet["brevetdate"];
brevet.Location = (String)resultSet["location"];
brevet.Climbing = (int)resultSet["climbing"];
brevet.Distance = (int)resultSet["distance"];
brevetList.Add(brevet);
}
resultSet.Close();
return(brevetList);
}
catch (Exception)
{
return(null);
}
finally
{
myDatabase.Close();
}
}
protected void btUpdate_Click(object sender, EventArgs e)
{
Brevet brevet = screenToModel();
int updateOk = brevetDAO.UpdateBrevet(brevet);
if (updateOk == 0) // Update succeeded
{
String selectedValue = listBoxClubs.SelectedValue;
createBrevetList();
listBoxClubs.SelectedValue = selectedValue;
showNoMessage();
}
else
{
showErrorMessage("No record updated. " +
"THE DATABASE IS TEMPORARILY OUT OF USE.");
}
}
/// <summary>
/// Retrieves a single Department row by DepartmentId from the database.
/// </summary>
/// <param name="BrevetId"></param>
/// <returns>A single Department object</returns>
public Brevet GetBrevetByBrevetId(int brevetId)
{
IDataReader resultSet;
try
{
myDatabase.Open(myConnectionString);
String sqlText = String.Format(
@"SELECT brevetId, distance, brevetDate, location, climbing
FROM Brevet
WHERE brevetId = {0}", brevetId);
resultSet = myDatabase.ExecuteQuery(sqlText);
if (resultSet.Read() == true)
{
Brevet brevet = new Brevet();
brevet.BrevetId = (int)resultSet["brevetId"];
brevet.Distance = (int)resultSet["distance"];
brevet.BrevetDate = (DateTime)resultSet["brevetDate"];
brevet.Location = (String)resultSet["location"];
brevet.Climbing = (int)resultSet["climbing"];
resultSet.Close();
return(brevet);
}
else
{
return(null); // Not found
}
}
catch (Exception)
{
return(null); // An error occurred
}
finally
{
myDatabase.Close();
}
}
public List<Brevet> GetAllBrevets()
{
List<Brevet> brevetList = new List<Brevet>();
IDataReader resultSet;
try
{
myDatabase = new Database();
myDatabase.Open(myConnectionString);
string sqlText =
"SELECT brevetid, distance, brevetdate, location, climbing " +
"FROM brevet " +
"ORDER BY brevetdate;";
resultSet = myDatabase.ExecuteQuery(sqlText);
while (resultSet.Read() == true)
{
Brevet brevet = new Brevet();
brevet.BrevetId = (int)resultSet["brevetid"];
brevet.BrevetDate = (DateTime)resultSet["brevetdate"];
brevet.Location = (String)resultSet["location"];
brevet.Climbing = (int)resultSet["climbing"];
brevet.Distance = (int)resultSet["distance"];
brevetList.Add(brevet);
}
resultSet.Close();
return brevetList;
}
catch (Exception)
{
return null;
}
finally
{
myDatabase.Close();
}
}
/// <summary>
/// Retrieves all Department rows in alphabetical order by Department name from the database.
/// </summary>
/// <returns>A List of Departments</returns>
public List <Brevet> GetAllBrevetsOrderedByName()
{
List <Brevet> brevetList = new List <Brevet>();
IDataReader resultSet;
try
{
myDatabase.Open(myConnectionString);
String sqlText =
@"SELECT brevetId, distance, brevetDate, location, climbing
FROM Brevet ORDER BY distance ASC, brevetDate ASC, location ASC";
resultSet = myDatabase.ExecuteQuery(sqlText);
while (resultSet.Read() == true)
{
Brevet brevet = new Brevet();
brevet.BrevetId = (int)resultSet["brevetId"];
brevet.Distance = (int)resultSet["distance"];
brevet.BrevetDate = (DateTime)resultSet["brevetDate"];
brevet.Location = (String)resultSet["location"];
brevet.Climbing = (int)resultSet["climbing"];
brevetList.Add(brevet);
}
resultSet.Close();
return(brevetList);
}
catch (Exception)
{
return(null); // An error occured
}
finally
{
myDatabase.Close();
}
}
protected void btAdd_Click(object sender, EventArgs e)
{
Brevet brevet = screenToModel();
int insertOk = brevetDAO.InsertBrevet(brevet);
if (insertOk == 0) // Insert succeeded
{
createBrevetList();
listBoxClubs.SelectedValue = brevet.BrevetId.ToString();
viewStateDetailsDisplayed();
showNoMessage();
}
else if (insertOk == 1)
{
showErrorMessage("Brevet id " + brevet.BrevetId +
" is already in use. No record inserted into the database.");
tbBrevetId.Focus();
}
else
{
showErrorMessage("No record inserted into the database. " +
"THE DATABASE IS TEMPORARILY OUT OF USE.");
}
}
public int InsertBrevet(Brevet brevet)
{
try
{
myDatabase.Open(myConnectionString);
if (brevetExist(brevet.BrevetId) == true)
{
return 1; // row exists
}
String sqlText = String.Format(
@"INSERT INTO Brevet (brevetid, distance, brevetdate, location, climbing)
VALUES ({0}, '{1}', '{2}', '{3}', '{4}')",
brevet.BrevetId , brevet.Distance, brevet.BrevetDate, brevet.Location, brevet.Climbing);
myDatabase.ExecuteQuery(sqlText);
return 0; //OK
}
catch (Exception)
{
return -1; // ERROR
}
finally
{
myDatabase.Close();
}
}
public Brevet GetBrevetByID(int brevetid)
{
IDataReader resultSet;
try
{
myDatabase.Open(myConnectionString);
String sqlText = String.Format(
@"SELECT brevetid, distance, brevetdate, location, climbing
FROM brevet
WHERE brevet = {0}", brevetid);
resultSet = myDatabase.ExecuteQuery(sqlText);
if (resultSet.Read() == true)
{
Brevet brevet = new Brevet();
brevet.BrevetId = (int)resultSet["brevetid"];
brevet.Distance = (int)resultSet["distance"];
brevet.BrevetDate = (DateTime)resultSet["brevetdate"];
brevet.Location = (String)resultSet["location"];
brevet.Climbing = (int)resultSet["climbing"];
return brevet;
}
else
{
return null;
}
}
catch (Exception)
{
return null;
}
finally
{
myDatabase.Close();
}
}
