/// <summary>
/// NO ACABADA
/// </summary>
/// <param name="persona"></param>
/// <returns></returns>
public int insertPersonaDAL(Persona persona)
{
int i = 0;
SqlCommand miComando = new SqlCommand();
miComando.Parameters.Add("@nombre", System.Data.SqlDbType.VarChar).Value = persona.Nombre;
miComando.Parameters.Add("@apellidos", System.Data.SqlDbType.VarChar).Value = persona.Apellidos;
miComando.Parameters.Add("@fechaNac", System.Data.SqlDbType.DateTime).Value = persona.FechaNac;
miComando.Parameters.Add("@direccion", System.Data.SqlDbType.VarChar).Value = persona.direccion;
miComando.Parameters.Add("@telefono", System.Data.SqlDbType.VarChar).Value = persona.telefono;
try
{
miCon.openConnection();
miComando.CommandText = "Insert Into personas(nombre,apellidos,fechaNac,direccion,telefono) VALUES "
+ "(@nombre,@apellidos,@fechaNac,@direccion,@telefono)";
miComando.Connection = miCon.connection;
i = miComando.ExecuteNonQuery();
}catch (SqlException) {
throw;
}
finally{
miCon.closeConnection();
}
return(i);
}
/// <summary>
/// This method takes a department id as a parameter and
/// delete the database department record with the same id
/// </summary>
/// <param name="id"></param>
public void deleteDepartment(int id)
{
//Opening and closing methods already check for possible exceptions. No need to wrap them around try catch again
//Instantiating necessary objects
connection = new MyConnection();
connection.myCommand = new SqlCommand();
List <Department> DepartmentList = new List <Department>();
connection.openConnection();
//Passing myConnection property to myCommand's own connection property
connection.myCommand.Connection = connection.myConnection;
connection.myCommand.CommandText = "DELETE FROM Departments " +
"WHERE ID = @id";
connection.myCommand.Parameters.Add("@id", System.Data.SqlDbType.Int).Value = id;
//Executing non-query statement and passing the value of rows affected to our handler's property
try
{
this.rowsAffected = connection.myCommand.ExecuteNonQuery();
}catch (InvalidOperationException e)
{
throw e;
}catch (SqlException e)
{
throw e;
}
//Closing connection
this.connection.closeConnection();
}
/// <summary>
/// This method takes a Person object as a parameter and
/// updates the database person record with the same ID
/// </summary>
/// <pre>id attribute </pre>
/// <param name="id"></param>
/// <param name="newPerson"></param>
public void updatePerson(Person newPerson)
{
//Instantiating necessary objects
connection = new MyConnection();
connection.myCommand = new SqlCommand();
List <Person> PersonList = new List <Person>();
//Opening DB connection -> setting SQL sentence as a SqlCommand object -> SQLCommand object executes the code and returns
//a new reader, passed to our own reader -> Reader returns queried information, if any
connection.openConnection();
//Passing myConnection property to myCommand's own connection property
connection.myCommand.Connection = connection.myConnection;
connection.myCommand.CommandText = "UPDATE Persons " +
"SET FirstName = @FirstName, " +
"LastName = @LastName, " +
"Birthdate = @Birthdate, " +
"Email = @Email, " +
"PhoneNumber = @PhoneNumber, " +
"DepartmentID = @DepartmentID " +
"WHERE ID = @ID";
//New department ID would have to be verified
connection.myCommand.Parameters.Add("@ID", System.Data.SqlDbType.Int).Value = newPerson.id;
connection.myCommand.Parameters.Add("@FirstName", System.Data.SqlDbType.NVarChar).Value = newPerson.FirstName;
connection.myCommand.Parameters.Add("@LastName", System.Data.SqlDbType.NVarChar).Value = newPerson.LastName;
connection.myCommand.Parameters.Add("@PhoneNumber", System.Data.SqlDbType.NVarChar).Value = newPerson.PhoneNumber;
connection.myCommand.Parameters.Add("@Email", System.Data.SqlDbType.NVarChar).Value = newPerson.Email;
connection.myCommand.Parameters.Add("@Birthdate", System.Data.SqlDbType.DateTime).Value = newPerson.Birthdate;
connection.myCommand.Parameters.Add("@DepartmentID", System.Data.SqlDbType.Int).Value = newPerson.DepartmentID;
//Executing non-query statement and passing the value of rows affected to our handler's property
try
{
this.rowsAffected = connection.myCommand.ExecuteNonQuery();
}
catch (InvalidOperationException e)
{
throw e;
}
catch (SqlException e)
{
throw e;
}
//Closing connection
this.connection.closeConnection();
}
/// <summary>
/// This method takes a Person object as a parameter
/// and inserts its data as a new database record
/// </summary>
/// <param name="newPerson"></param>
public void insertPerson(Person newPerson)
{
//Opening and closing methods already check for possible exceptions. No need to wrap them around try catch again
//Instantiating necessary objects
connection = new MyConnection();
connection.myCommand = new SqlCommand();
List <Person> PersonList = new List <Person>();
//Opening DB connection -> setting SQL sentence as a SqlCommand object -> SQLCommand object executes the code and returns
//a new reader, passed to our own reader -> Reader returns queried information, if any
connection.openConnection();
//Passing myConnection property to myCommand's own connection property
connection.myCommand.Connection = connection.myConnection;
connection.myCommand.CommandText = "INSERT INTO Persons (FirstName, LastName, Birthdate, Email, PhoneNumber, DepartmentID)" +
" VALUES (@FirstName, " +
" @LastName, " +
" CONVERT(date, @Birthdate, 103), " +
" @Email, " +
" @PhoneNumber, " +
" @DepartmentID)";
//DepartmentID should be verified
connection.myCommand.Parameters.Add("@FirstName", System.Data.SqlDbType.NVarChar).Value = newPerson.FirstName;
connection.myCommand.Parameters.Add("@LastName", System.Data.SqlDbType.NVarChar).Value = newPerson.LastName;
connection.myCommand.Parameters.Add("@Birthdate", System.Data.SqlDbType.Date).Value = newPerson.Birthdate;
connection.myCommand.Parameters.Add("@Email", System.Data.SqlDbType.NVarChar).Value = newPerson.Email;
connection.myCommand.Parameters.Add("@PhoneNumber", System.Data.SqlDbType.NVarChar).Value = newPerson.PhoneNumber;
connection.myCommand.Parameters.Add("@DepartmentID", System.Data.SqlDbType.Int).Value = newPerson.DepartmentID;
//Executing non-query statement andPassing the value of rows affected to our handler's property
try {
this.rowsAffected = connection.myCommand.ExecuteNonQuery();
} catch (InvalidOperationException e)
{
throw e;
}
catch (SqlException e)
{
throw e;
}
//Closing connection
this.connection.closeConnection();
}
public List <Department> getDepartmentList()
{
//Opening and closing methods already check for possible exceptions. No need to wrap them around try catch again
//Instantiating necessary objects
connection = new MyConnection();
connection.myCommand = new SqlCommand();
List <Department> DepartmentList = new List <Department>();
//Opening DB connection -> setting SQL sentence as a SqlCommand object -> SQLCommand object executes the code and returns
//a new reader, passed to our own reader -> Reader returns queried information
connection.openConnection();
//Passing myConnection property to myCommand's own connection property
connection.myCommand.Connection = connection.myConnection;
connection.myCommand.CommandText = "SELECT * FROM Departments";
connection.myReader = connection.myCommand.ExecuteReader();
if (connection.myReader.HasRows)
{
//Read method points to the next record.
//It also returns true if it's pointing to a record and there are more left
while (connection.myReader.Read())
{
Department readDepartment = new Department();
readDepartment.ID = (int)connection.myReader["ID"];
readDepartment.Name = (string)connection.myReader["Name"];
//If the field is null, use this code:
//if (connection.myReader["email"] != System.DBNull.Value)
//{ readPerson.Email = (string) connection.myReader["email"]; }
//Adding retrieved records to our PersonList
DepartmentList.Add(readDepartment);
}
}
//Closing reader and connection
this.connection.myReader.Dispose();
this.connection.closeConnection();
return(DepartmentList);
}
/// <summary>
/// This method takes a Mision object as a parameter and
/// updates the database mision record with the same ID
/// </summary>
/// <pre>id attribute </pre>
/// <param name="newMision"></param>
public void updateMision(Mision newMision)
{
//Instantiating necessary objects
connection = new MyConnection();
connection.myCommand = new SqlCommand();
List <Mision> PersonList = new List <Mision>();
//Opening DB connection -> setting SQL sentence as a SqlCommand object -> SQLCommand object executes the code and returns
//a new reader, passed to our own reader -> Reader returns queried information, if any
connection.openConnection();
//Passing myConnection property to myCommand's own connection property
connection.myCommand.Connection = connection.myConnection;
connection.myCommand.CommandText = "UPDATE Misiones " +
"SET nombre = @nombre, " +
"descripcion = @descripcion, " +
"creditos = @creditos, " +
"completada = @completada " +
"WHERE ID = @ID";
connection.myCommand.Parameters.Add("@ID", System.Data.SqlDbType.Int).Value = newMision.id;
connection.myCommand.Parameters.Add("@nombre", System.Data.SqlDbType.VarChar).Value = newMision.nombre;
connection.myCommand.Parameters.Add("@descripcion", System.Data.SqlDbType.VarChar).Value = newMision.descripcion;
connection.myCommand.Parameters.Add("@creditos", System.Data.SqlDbType.Int).Value = newMision.creditos;
connection.myCommand.Parameters.Add("@completada", System.Data.SqlDbType.Bit).Value = newMision.completada;
//Executing non-query statement and passing the value of rows affected to our handler's property
try
{
this.rowsAffected = connection.myCommand.ExecuteNonQuery();
}
catch (InvalidOperationException e)
{
throw e;
}
catch (SqlException e)
{
throw e;
}
//Closing connection
this.connection.closeConnection();
}
/// <summary>
/// This method takes a Department object as a parameter
/// and inserts its data as a new database record
/// </summary>
/// <param name="newDepartment"></param>
public void insertDepartment(Department newDepartment)
{
//Opening and closing methods already check for possible exceptions. No need to wrap them around try catch again
//Instantiating necessary objects
connection = new MyConnection();
connection.myCommand = new SqlCommand();
List <Department> DepartmentList = new List <Department>();
//Opening DB connection -> setting SQL sentence as a SqlCommand object -> SQLCommand object executes the code and returns
//a new reader, passed to our own reader -> Reader returns queried information, if any
connection.openConnection();
//Passing myConnection property to myCommand's own connection property
connection.myCommand.Connection = connection.myConnection;
connection.myCommand.CommandText = "INSERT INTO Departments (Name) " +
"VALUES @Name";
connection.myCommand.Parameters.Add("@Name", System.Data.SqlDbType.NVarChar).Value = newDepartment.Name;
//Executing non-query statement and passing the value of rows affected to our handler's property
try
{
this.rowsAffected = connection.myCommand.ExecuteNonQuery();
}
catch (InvalidOperationException e)
{
throw e;
}
catch (SqlException e)
{
throw e;
}
//Closing connection
this.connection.closeConnection();
}
/// <summary>
/// This method takes a Department object as a parameter and
/// updates the database department record with the same ID
/// </summary>
/// <param name="id"></param>
/// <param name="newDepartment"></param>
public void updateDepartment(Department newDepartment)
{
//Instantiating necessary objects
connection = new MyConnection();
connection.myCommand = new SqlCommand();
List <Department> DepartmentList = new List <Department>();
//Opening DB connection -> setting SQL sentence as a SqlCommand object -> SQLCommand object executes the code
//The UPDATE SQL statement doesn't return any information, so no reader objects are necessary
connection.openConnection();
//Passing myConnection property to myCommand's own connection property
connection.myCommand.Connection = connection.myConnection;
connection.myCommand.CommandText = "UPDATE Departments " +
"SET Name = @Name " +
"WHERE ID = @ID";
connection.myCommand.Parameters.Add("@Name", System.Data.SqlDbType.NVarChar).Value = newDepartment.Name;
connection.myCommand.Parameters.Add("@ID", System.Data.SqlDbType.Int).Value = newDepartment.ID;
//Executing non-query statement and passing the value of rows affected to our handler's property
try
{
this.rowsAffected = connection.myCommand.ExecuteNonQuery();
}
catch (InvalidOperationException e)
{
throw e;
}
catch (SqlException e)
{
throw e;
}
//Closing connection
this.connection.closeConnection();
}
public Department getDepartment(int id)
{
//Opening and closing methods already check for possible exceptions. No need to wrap them around try catch again
//Instantiating necessary objects
connection = new MyConnection();
connection.myCommand = new SqlCommand();
Department readDepartment = new Department();
//Opening DB connection -> setting SQL sentence as a SqlCommand object -> SQLCommand object executes the code and returns
//a new reader, passed to our own reader -> Reader returns queried information
connection.openConnection();
//Passing myConnection property to myCommand's own connection property
connection.myCommand.Connection = connection.myConnection;
connection.myCommand.CommandText = "SELECT ID, Name FROM Departments WHERE ID = @id";
connection.myCommand.Parameters.Add("@id", System.Data.SqlDbType.Int).Value = id;
connection.myReader = connection.myCommand.ExecuteReader();
//Passing the value of rows affected to our handler's property
this.rowsAffected = connection.myReader.RecordsAffected;
//Gets the only row
connection.myReader.Read();
readDepartment.ID = (int)connection.myReader["ID"];
readDepartment.Name = (string)connection.myReader["Name"];
//Closing reader and connection
this.connection.myReader.Dispose();
this.connection.closeConnection();
return(readDepartment);
}
