/// <summary>
/// Get a contract by an ID with a specify transaction already opened
/// </summary>
/// <param name="id">ID of contract</param>
/// <param name="transaction">The transaction to use</param>
/// <returns>The contract with this ID</returns>
public Contract GetContractById(int id, SqlTransaction transaction)
{
var contracts = new List<Contract>();
var parameters = new NameValueCollection
{
{"@id", id.ToString()},
};
var c = new Contract();
var persons = new List<Person>();
var departments = new List<Department>();
using (var reader = DBUtils.ExecuteTransactionQuery("SELECT C.id, C.title, C.start, C.[end], C.fileId, C.xmlContent, C.userLogin, C.typeContractName, C.archived " +
"FROM [Contract] C " +
"WHERE C.id = @id"
, transaction, parameters))
{
if (reader.Read())
{
c = BindContract(reader);
}
}
using (var readerAssoc = DBUtils.ExecuteTransactionQuery("SELECT A.roleName, P.id AS personId, P.name, P.firstname " +
"FROM [Association] A " +
"INNER JOIN Person P " +
"ON A.person = P.id " +
"WHERE A.contractId = @id"
, transaction, parameters))
{
while (readerAssoc.Read())
{
var p = new Person
{
Id = (int) readerAssoc["personId"],
Role = (string) readerAssoc["roleName"],
FirstName = (string) readerAssoc["firstname"],
Name = (string) readerAssoc["name"]
};
persons.Add(p);
}
}
using (var readerDestination = DBUtils.ExecuteTransactionQuery("SELECT P.id AS departementId, P.name AS depName, I.id AS institutionId, I.name AS insName" +
" FROM [Destination] D" +
" INNER JOIN [Department] P" +
" ON D.department = P.id" +
" INNER JOIN [Institution] I" +
" ON P.institutionId = I.id" +
" WHERE D.contract = @id"
, transaction, parameters))
{
while (readerDestination.Read())
{
var d = new Department
{
Id = (int) readerDestination["departementId"],
Name = (string) readerDestination["depName"],
InstitutionName = (string) readerDestination["insName"],
InstitutionId = (int) readerDestination["institutionId"]
};
departments.Add(d);
}
}
c.persons = persons;
c.departments = departments;
contracts.Add(c);
return contracts.First();
}
/// <summary>
/// Creates and fill a list of departments belonging to the institution identified by insitutionId.
/// </summary>
/// <param name="institutionId">The institution's identifier.</param>
/// <param name="transaction">The transaction to be used to query the database.</param>
/// <returns>The department's list.</returns>
private static List<Department> GetDepartments(int institutionId, SqlTransaction transaction)
{
var departments = new List<Department>();
var parameters = new NameValueCollection
{
{"@institutionId", institutionId.ToString()}
};
using (var departmentReader = DBUtils.ExecuteTransactionQuery("SELECT id, name FROM [Department] WHERE institutionId = @institutionId and archived=0", transaction, parameters))
{
//Fill departments list
while (departmentReader.Read())
{
var department = new Department
{
Id = (int) departmentReader["id"],
Name = (string) departmentReader["name"]
};
departments.Add(department);
}
}
Logger.Debug("Retrived department of the institution with id {0} in database", institutionId);
return departments;
}
/// <summary>
/// Bind the SQL Result to a Person object
/// </summary>
/// <param name="result">The result of a SQL query</param>
/// <returns>a new instance of Person with the values of the SQL Result</returns>
private static Person BindPerson(SqlResult result)
{
var department = new Department
{
Id = (int) result["departmentId"],
Name = (string) result["departmentName"],
InstitutionName = (string) result["institutionName"],
InstitutionId = (int) result["institutionId"]
};
var person = new Person
{
Id = (int) result["id"],
Name = (string) result["name"],
FirstName = (string) result["firstname"],
Email = (string) result["email"],
Phone = (string) result["phone"],
Archived = (bool) result["archived"],
Department = department
};
return person;
}
/// <summary>
/// Creates an instance of the department identified by id, which is stored in the database.
/// </summary>
/// <param name="id">The department's identifier.</param>
/// <param name="connection">The connection to use</param>
/// <returns>The department's instance.</returns>
public Department GetDepartmentById(int id, SqlConnection connection)
{
var departments = new List<Department>();
var transaction = connection.BeginTransaction(IsolationLevel.ReadCommitted);
var parameters = new NameValueCollection
{
{"@id", id.ToString()},
};
Logger.Debug("Retriving department with id {0} from database", id);
using (var readerDestination = DBUtils.ExecuteTransactionQuery("SELECT D.id AS depId, d.name AS depName, I.id AS institutionId, I.name AS insName, I.city, I.languageName, I.countryName" +
" FROM [Department] D" +
" INNER JOIN [Institution] I" +
" ON D.institutionId = I.id" +
" WHERE D.id = @id"
, transaction, parameters))
{
while (readerDestination.Read())
{
var d = new Department
{
Id = (int) readerDestination["depId"],
Name = (string) readerDestination["depName"],
InstitutionName = (string) readerDestination["insName"],
InstitutionId = (int) readerDestination["institutionId"],
InstitutionCity = (string) readerDestination["city"],
InstitutionCountry = (string) readerDestination["countryName"],
InstitutionLanguage = (string) readerDestination["languageName"]
};
departments.Add(d);
}
}
transaction.Commit();
Logger.Debug("Retrieved department with id {0} from database", id);
return departments.First();
}
