//Erases a bus from the DB given its id
public DataSet deleteBus(BusEN b, int i) // It will delete the index passed in the view
{
string s;
s = ConfigurationManager.ConnectionStrings["DefaultConnection"].ToString(); //String where it's stored the instructions for the connecton for the DB
DataSet virtdb = new DataSet(); //Created the DataSet that is going to be returned with the information asked
SqlConnection c = new SqlConnection(s); //The connection is effectuated
try
{ //The select in SQL language that is processed in the DB which will return all the rows from the table "Bus"
SqlDataAdapter da = new SqlDataAdapter("select * from Bus", c);
da.Fill(virtdb, "bus"); //It introduces the information returned from the select into this virtual DB
DataTable t = new DataTable();
t = virtdb.Tables["bus"]; //Fills it with the information obtained from the select
t.Rows[i].Delete(); //Erases the information related about this bus
SqlCommandBuilder cbuilder = new SqlCommandBuilder(da); //Elaborates the SQL command needed to make the changes
da.Update(virtdb, "bus"); //Updates the DB with the new information added
}
catch (Exception ex)
{
ex.ToString(); //In case of an error it is printed here
Console.WriteLine("ERROR: Delete bus");
}
finally
{
c.Close(); //Closes the connection to the DB
}
return(virtdb); //It returns the virtual DB with all the information asked inside
}
public ArrayList searchBus(BusEN b)
{
ArrayList a = new ArrayList();
string s = ConfigurationManager.ConnectionStrings["DatabaseConnection"].ToString();
SqlConnection c = new SqlConnection(s);
try
{
c.Open();
SqlCommand com = new SqlCommand("Select * from Bus where departureCity = " + b.departureCity + " and destinationCity = " + b.destinationCity, c);
SqlDataReader dr = com.ExecuteReader();
while (dr.Read())
{
a.Add(dr["Id"].ToString());
}
dr.Close();
}
catch (Exception ex)
{
ex.ToString();
Console.WriteLine("ERROR: Show buses");
}
finally
{
c.Close();
}
return(a);
}
public void deleteBus(BusEN b)
{
string s = ConfigurationManager.ConnectionStrings["DatabaseConnection"].ToString();
SqlConnection c = new SqlConnection(s);
try
{
c.Open();
SqlCommand com = new SqlCommand("Delete From Bus Where ID = " + b.Id, c);
com.ExecuteNonQuery();
}
catch (Exception ex)
{
ex.ToString();
Console.WriteLine("ERROR: Delete bus");
}
finally
{
c.Close();
}
}
//Adds a new bus to the DB
public DataSet addBus(BusEN b)
{
string s;
s = ConfigurationManager.ConnectionStrings["DefaultConnection"].ToString(); //String where it's stored the instructions for the connecton for the DB
DataSet virtdb = new DataSet(); //Created the DataSet that is going to be returned with the information asked
SqlConnection c = new SqlConnection(s); //The connection is effectuated
try
{ //The select in SQL language that is processed in the DB which will return all the rows from the table "Bus"
SqlDataAdapter da = new SqlDataAdapter("select * from Bus", c);
da.Fill(virtdb, "bus"); //It introduces the information returned from the select into this virtual DB
DataTable dt = new DataTable(); //Creates a new table
dt = virtdb.Tables["bus"]; //Fills it with the information obtained from the select
DataRow newRow = dt.NewRow(); //Creates a new row
newRow[0] = b.id;
newRow[1] = b.DepartureTime;
newRow[2] = b.ArrivaldTime;
newRow[3] = b.DepartureCity; //Introduces the information of the row
newRow[4] = b.DestinationCity;
newRow[5] = b.Price;
newRow[6] = b.Company;
newRow[7] = b.Extras;
newRow[8] = b.Image;
dt.Rows.Add(newRow);
SqlCommandBuilder cbuilder = new SqlCommandBuilder(da); //Elaborates the SQL command needed to make the changes
da.Update(virtdb, "bus"); //Updates the DB with the new information added
}
catch (Exception ex)
{
ex.ToString(); //In case of an error it is printed here
Console.WriteLine("ERROR: Add bus");
}
finally
{
c.Close(); //Closes the connection to the DB
}
return(virtdb); //It returns the virtual DB with all the information asked inside
}
public void updateBus(BusEN b)
{
string s = ConfigurationManager.ConnectionStrings["DatabaseConnection"].ToString();
SqlConnection c = new SqlConnection(s);
try
{
c.Open();
SqlCommand com = new SqlCommand("Update Bus Set departureDate = '" + b.departureDate + "', arrivalDate = '" + b.arrivalDate + "', departureCity ='" +
b.departureCity + "', destinationCity = '" + b.destinationCity + "', Bonus = '" + b.bonus + "' Where ID = " + b.Id, c);
com.ExecuteNonQuery();
}
catch (Exception ex)
{
ex.ToString();
Console.WriteLine("ERROR: Update bus");
}
finally
{
c.Close();
}
}
public void addBus(BusEN b)
{
string s = ConfigurationManager.ConnectionStrings["DatabaseConnection"].ToString();
SqlConnection c = new SqlConnection(s);
try
{
c.Open();
SqlCommand com = new SqlCommand("Insert Into Bus (Id,departureDate,arrivalDate,departureCity,destinationCity,bonus) VALUES ('" + b.Id + "','" + b.departureDate + "','" + b.arrivalDate + "','" +
b.departureCity + "','" + b.destinationCity + "','" + b.bonus + "')", c);
com.ExecuteNonQuery();
}
catch (Exception ex)
{
ex.ToString();
Console.WriteLine("ERROR: Add bus");
}
finally
{
c.Close();
}
}
// Shows all the information about the buses
public DataSet showBuses(BusEN b)
{
string s;
s = ConfigurationManager.ConnectionStrings["DefaultConnection"].ToString(); //String where it's stored the instructions for the connecton for the DB
DataSet virtdb = new DataSet(); //Created the DataSet that is going to be returned with the information asked
SqlConnection c = new SqlConnection(s); //The connection is effectuated
try
{
SqlDataAdapter da = new SqlDataAdapter("select * from Bus", c); //The select in SQL language that is processed in the DB which will return all the rows from the table "Bus"
da.Fill(virtdb, "bus"); //It introduces the information returned from the select into this virtual DB
}
catch (Exception ex)
{
ex.ToString(); //In case of an error it is printed here
Console.WriteLine("ERROR: show bus");
}
finally
{
c.Close(); //Closes the connection to the DB
}
return(virtdb); //It returns the virtual DB with all the information asked inside
}