コード例 #1
0
        public void UnemployPerson(int id)
        {
            using (var connection = DbSQLite.CreateConnection())
            {
                connection.BeginTransaction();
                try
                {
                    connection.CreateTable <Person>();

                    var selectedPerson = connection.Table <Person>().Where(p => p.Id == id).ToList();

                    foreach (var p in selectedPerson)
                    {
                        p.CompanyId = null;
                    }

                    connection.UpdateAll(selectedPerson);

                    connection.Commit();
                }
                catch (Exception)
                {
                    connection.Rollback();
                    throw;
                }
            }
        }
コード例 #2
0
 public List <Company> GetAllCompanies()
 {
     using (var companyConnection = DbSQLite.CreateConnection())
     {
         companyConnection.CreateTable <Company>();
         return(companyConnection.Table <Company>().ToList());
     }
 }
コード例 #3
0
 public List <Person> GetAllPeople()
 {
     using (var personConnection = DbSQLite.CreateConnection())
     {
         personConnection.CreateTable <Person>();
         return(personConnection.Table <Person>().ToList());
     }
 }
コード例 #4
0
 public void RemovePerson(Person person)
 {
     using (var connection = DbSQLite.CreateConnection())
     {
         connection.CreateTable <Person>();
         connection.Delete(person);
     }
 }
コード例 #5
0
 public List <Person> GetEmployees(Company company)
 {
     using (var personConnection = DbSQLite.CreateConnection())
     {
         personConnection.CreateTable <Person>();
         return(personConnection.Table <Person>().Where(c => c.CompanyId == company.Id).ToList());
     }
 }
コード例 #6
0
 public List <Person> GetAllUnemployed()
 {
     using (var personConnection = DbSQLite.CreateConnection())
     {
         personConnection.CreateTable <Person>();
         return(personConnection.Table <Person>().Where(c => c.CompanyId == null).ToList());
     }
 }
コード例 #7
0
        public List <Company> SearchCompany(string searchString)
        {
            using (var connection = DbSQLite.CreateConnection())
            {
                connection.CreateTable <Company>();

                return(connection.Table <Company>()
                       .Where(c => c.CompanyName.ToUpper().Contains(searchString.ToUpper()))
                       .ToList());
            }
        }
コード例 #8
0
 public void AddCompany(Company company)
 {
     // Initialize the SQLite connection
     // The "using" statement takes care of closing the connection"
     using (var connection = DbSQLite.CreateConnection())
     {
         // Create a table of the type Person
         connection.CreateTable <Company>();
         // Insert the person object that was created when the save button was clicked into the SQLite table.
         connection.Insert(company);
     }
 }
コード例 #9
0
        public void RemoveCompany(List <Company> companies)
        {
            using (var connection = DbSQLite.CreateConnection())
            {
                connection.BeginTransaction();
                try
                {
                    connection.CreateTable <Company>();

                    var companiesToBeDeleted = companies;


                    foreach (var company in companiesToBeDeleted)
                    {
                        var companyToBeDeletedList = connection.Table <Company>().Where(c => c.CompanyName == company.CompanyName).ToList();

                        // Unemploy all people in the company
                        foreach (var c in companyToBeDeletedList)
                        {
                            var employedPeopleList = _personRepository.GetEmployees(c);

                            if (employedPeopleList.Count != 0)
                            {
                                foreach (var person in employedPeopleList)
                                {
                                    connection.CreateTable <Person>();

                                    var selectedPerson = connection.Table <Person>().Where(p => p.Id == person.Id).ToList();

                                    foreach (var p in selectedPerson)
                                    {
                                        p.CompanyId = null;
                                    }

                                    connection.UpdateAll(selectedPerson);
                                }
                            }
                            connection.Delete(c);
                        }
                    }

                    connection.Commit();
                }
                catch (Exception)
                {
                    connection.Rollback();
                    throw;
                }
            }
        }
コード例 #10
0
        public List <CompanyCount> AmountOfEmployees()
        {
            using (var connection = DbSQLite.CreateConnection())
            {
                connection.CreateTable <Company>();
                connection.CreateTable <Person>();


                List <CompanyCount> query = connection.Query <CompanyCount>("SELECT Company.CompanyName, Person.CompanyId as Id, COUNT(Person.CompanyId) as NumberOfPersons FROM Company left JOIN Person ON person.CompanyId = Company.Id where Company.CompanyName is not null GROUP BY Company.CompanyName order by Company.CompanyName");



                return(query);
            }
        }
コード例 #11
0
        public bool CompanyExists(Company company)
        {
            using (var connection = DbSQLite.CreateConnection())
            {
                connection.CreateTable <Company>();

                var comp = connection.Table <Company>().SingleOrDefault(c => c.CompanyName.ToLowerInvariant() == company.CompanyName.ToLowerInvariant());

                if (comp == null)
                {
                    return(false);
                }
                else
                {
                    return(true);
                }
            }
        }
コード例 #12
0
        public List <CompanyPerson> GetCompanyPersons()
        {
            using (var connection = DbSQLite.CreateConnection())
            {
                connection.CreateTable <Company>();
                connection.CreateTable <Person>();

                var query =
                    from person in connection.Table <Person>()
                    join company in connection.Table <Company>() on person.CompanyId equals company.Id into companies
                    from company in companies.DefaultIfEmpty()
                    select new CompanyPerson
                {
                    Id          = person.Id,
                    FullName    = person.FullName,
                    CompanyId   = company?.Id,
                    CompanyName = company?.CompanyName
                };

                return(query.OrderBy(c => c.CompanyName).ThenBy(p => p.FullName).ToList());
            }
        }
コード例 #13
0
        public List <CompanyPerson> SearchCompanyPerson(string searchString)
        {
            using (var connection = DbSQLite.CreateConnection())
            {
                connection.CreateTable <Company>();
                connection.CreateTable <Person>();

                var query =
                    from person in connection.Table <Person>()
                    join company in connection.Table <Company>() on person.CompanyId equals company.Id into companies
                    from company in companies.DefaultIfEmpty()
                    select new CompanyPerson
                {
                    Id          = person.Id,
                    FullName    = person.FullName,
                    CompanyId   = company?.Id,
                    CompanyName = company?.CompanyName
                };

                return(query.Where(p => p.FullName.ToLower().Contains(searchString.ToLower())).ToList());
            }
        }