Esempio n. 1
0
        /// <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();
        }
Esempio n. 2
0
        /// <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;
        }
Esempio n. 3
0
        /// <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;
        }
Esempio n. 4
0
        /// <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();
        }