static void Main(string[] args)
{
//PostCode pc = new PostCode("KA1 1RE");
//Console.WriteLine(pc.FullCode);
EmployeeMapper em = new EmployeeMapper();
// Get the same employee twice
Employee emp1 = em.GetById(1);
Employee emp2 = em.GetById(1);
// check that the same object is returned by each call
bool sameobject1 = emp1.Equals(emp2);
// Get all hourly paid employees
List<Employee> hpes = em.GetAllHourlyPaid();
bool sameobject2 = emp1.Equals(hpes[0].Supervisor);
bool sameobject3 = hpes[0].Equals(hpes[1].Supervisor);
// Create and store a new hourly paid employee
HourlyPaidEmployee newhpe = new HourlyPaidEmployee();
Address newaddress = new Address("Entity Park", 100, new PostCode("KA1 1BX"));
newhpe.Address = newaddress;
newhpe.Name = "Michael";
newhpe.Username = "******";
newhpe.PhoneNumber = "2222";
newhpe.Supervisor = emp1;
int newID = em.StoreHourlyPaid(newhpe);
Employee newEmp = em.GetById(newID);
// wait for key press before ending
Console.ReadLine();
}
/// <summary>
/// gets all hourly paid employees
/// with associated address, postcode and supervisor objects
/// </summary>
/// <returns>list of employees</returns>
public List<Employee> GetAllHourlyPaid()
{
List<Employee> result = new List<Employee>();
StringBuilder sqlb = new StringBuilder(
"SELECT employeeid,name,username,supervisor, phonenumber, propertyname, propertynumber, postcode ");
sqlb.Append("FROM EMPLOYEES e, ADDRESSES a ");
sqlb.Append("WHERE e.addressID = a.addressID ");
sqlb.Append(@"AND discriminator = 'h'");
string sql = sqlb.ToString();
SqlCeCommand comm = new SqlCeCommand(sql, conn);
if (conn.State == ConnectionState.Closed) conn.Open();
SqlCeDataReader reader = comm.ExecuteReader(CommandBehavior.CloseConnection);
while(reader.Read())
{
Employee emp = null;
int employeeID = reader.GetInt32(0);
string name = reader.GetString(1);
string username = reader.GetString(2);
int supervisor = -1;
if(!reader.IsDBNull(3))
supervisor = reader.GetInt32(3);
string phonenumber = reader.GetString(4);
string propertyname = reader.GetString(5);
int propertynumber = reader.GetInt32(6);
string postcode = reader.GetString(7);
if (identityMap.ContainsKey(employeeID))
{
emp = identityMap[employeeID];
}
else
{
PostCode pc = new PostCode(postcode);
Address ad = new Address(propertyname, propertynumber, pc);
emp = new HourlyPaidEmployee(employeeID, name, username, ad, phonenumber);
emp.Supervisor = GetById(supervisor);
identityMap.Add(employeeID, emp);
}
result.Add(emp);
}
//conn.Close();
return result;
}
static void SetupObjects()
{
// EMPLOYEES
Employee fernando = new SalariedEmployee
{
EmployeeID = 1,
Name = "Fernando",
Username = "******",
PhoneNumber = "9999",
PayGrade = 7
};
Employee felipe = new HourlyPaidEmployee
{
EmployeeID = 2,
Name = "Felipe",
Username = "******",
PhoneNumber = "8888",
Supervisor = fernando
};
Employee nico = new HourlyPaidEmployee
{
EmployeeID = 3,
Name = "Nico",
Username = "******",
PhoneNumber = "7777",
Supervisor = felipe
};
// ADDRESSES
Address ormHouse = new Address
{
PropertyName = "ORM House",
PropertyNumber = 1,
PostCode = "G4 0BA",
Employees = new ArrayList4<Employee> { nico } // db4o activateable collections
};
Address linqTower = new Address
{
PropertyName = "LINQ Tower",
PropertyNumber = 9,
PostCode = "KA1 1XX",
Employees = new ArrayList4<Employee> { fernando, felipe }
};
// SET ADDRESSES FOR EMPLOYEES
fernando.Address = linqTower;
felipe.Address = linqTower;
nico.Address = ormHouse;
// PROJECTS
Project webShop = new Project
{
ProjectID = 1,
ProjectName = "Web Shop",
Employees = new ArrayList4<Employee> { felipe, nico }
};
Project financeSystem = new Project
{
ProjectID = 2,
ProjectName = "Finance System",
Employees = new ArrayList4<Employee> { fernando }
};
Project secret = new Project
{
ProjectID = 3,
ProjectName = "Secret",
Employees = new ArrayList4<Employee> { fernando, felipe }
};
// SET PROJECTS FOR EMPLOYEES
fernando.Projects = new ArrayList4<Project> { financeSystem, secret };
felipe.Projects = new ArrayList4<Project> { webShop, secret };
nico.Projects = new ArrayList4<Project> { webShop };
// OBJECT GRAPH
IList<Employee> employees = new ArrayList4<Employee> { fernando, felipe, nico };
// STORE OBJECT GRAPH
string dbFileName = GetDbFileName();
using (IObjectContainer db = Db4oEmbedded.OpenFile(dbFileName))
{
foreach (Employee emp in employees)
{
db.Store(emp);
}
}
}
/// <summary>
/// gets specified employee with associated address and postcode objects
/// </summary>
/// <param name="id">the employee id</param>
/// <returns>an employee object</returns>
public Employee GetById(int id)
{
Employee result = null;
// check whether target object is already loaded
if (identityMap.ContainsKey(id))
{
result = identityMap[id];
}
else
{
StringBuilder sqlb = new StringBuilder(
"SELECT employeeid,name,username,phonenumber, discriminator, paygrade, propertyname, propertynumber, postcode ");
sqlb.Append("FROM EMPLOYEES e, ADDRESSES a ");
sqlb.Append("WHERE e.addressID = a.addressID ");
sqlb.Append("AND employeeID = ");
sqlb.Append(id.ToString());
string sql = sqlb.ToString();
SqlCeCommand comm = new SqlCeCommand(sql, conn);
if(conn.State==ConnectionState.Closed) conn.Open();
SqlCeDataReader reader = comm.ExecuteReader(CommandBehavior.CloseConnection);
if (reader.Read())
{
int employeeID = reader.GetInt32(0);
string name = reader.GetString(1);
string username = reader.GetString(2);
string phonenumber = reader.GetString(3);
string discriminator = reader.GetString(4);
int paygrade = 0;
if(!reader.IsDBNull(5))
paygrade = reader.GetInt32(5);
string propertyname = reader.GetString(6);
int propertynumber = reader.GetInt32(7);
string postcode = reader.GetString(8);
PostCode pc = new PostCode(postcode);
Address ad = new Address(propertyname, propertynumber, pc);
if (discriminator.Equals("S"))
{
// need to include pay grade in database schema and adjust query
result = new SalariedEmployee(employeeID, name, username, ad, phonenumber, paygrade);
}
else if (discriminator.Equals("H"))
{
result = new HourlyPaidEmployee(employeeID, name, username, ad, phonenumber);
}
else
{
throw new Exception("Invalid employee type");
}
}
//conn.Close();
identityMap.Add(id, result);
}
return result;
}
/// <summary>
/// store an hourly paid employee incuding address/postcode and supervisor ID
/// assumes address has not previously been stored
/// </summary>
/// <param name="emp"></param>
/// <returns></returns>
public int StoreHourlyPaid(HourlyPaidEmployee emp)
{
// store address first (assume we are sure address is not in db)
StringBuilder sqlb = new StringBuilder(
"INSERT INTO Addresses(propertyname, propertynumber, postcode) ");
sqlb.Append(@"VALUES('");
sqlb.Append(emp.Address.PropertyName);
sqlb.Append(@"',");
sqlb.Append(emp.Address.PropertyNumber);
sqlb.Append(@", '");
sqlb.Append(emp.Address.PostCode.FullCode);
sqlb.Append(@"')");
string sql = sqlb.ToString();
SqlCeCommand comm = new SqlCeCommand(sql, conn);
if (conn.State == ConnectionState.Closed) conn.Open();
comm.ExecuteNonQuery();
sql = "SELECT Max(addressID) FROM Addresses";
comm = new SqlCeCommand(sql, conn);
if (conn.State == ConnectionState.Closed) conn.Open();
int addressID = (int)comm.ExecuteScalar();
sqlb = new StringBuilder(
"INSERT INTO Employees(name, phonenumber, username, discriminator, addressID, supervisor) ");
sqlb.Append(@"VALUES('");
sqlb.Append(emp.Name);
sqlb.Append(@"', '");
sqlb.Append(emp.PhoneNumber);
sqlb.Append(@"', '");
sqlb.Append(emp.Username);
sqlb.Append(@"', 'H',");
sqlb.Append(addressID);
sqlb.Append(@", ");
sqlb.Append(emp.Supervisor.EmployeeId);
sqlb.Append(@")");
sql = sqlb.ToString();
comm = new SqlCeCommand(sql, conn);
if (conn.State == ConnectionState.Closed) conn.Open();
comm.ExecuteNonQuery();
sql = "SELECT Max(employeeID) FROM Employees";
comm = new SqlCeCommand(sql, conn);
if (conn.State == ConnectionState.Closed) conn.Open();
int employeeID = (int)comm.ExecuteScalar();
conn.Close();
return employeeID;
}
static void Main(string[] args)
{
#region CONFIGURE
var sessionFactory = Fluently.Configure()
.Database(MsSqlConfiguration.MsSql2008
.ConnectionString(c => c
.FromConnectionStringWithKey("CompanyConnection"))
.ShowSql())
.Mappings(m => m
.FluentMappings.AddFromAssemblyOf<Employee>()
.Conventions.AddFromAssemblyOf<Employee>())
.BuildSessionFactory();
#endregion
#region ADD NEW
// save, then read in a separate session
using (ISession session = sessionFactory.OpenSession())
using (ITransaction transaction = session.BeginTransaction())
{
HourlyPaidEmployee newEmp = new HourlyPaidEmployee
{
Name = "Seb",
Username = "******",
PhoneNumber = "9876",
Address = session.Load<Address>(2) // Load creates a proxy, does not actually query database, so no need for FK fields (would get mapping error anyway - can't map column as property and as FK)
}; // Get actually loads entity
session.Save(newEmp);
transaction.Commit();
}
#endregion
using (ISession session = sessionFactory.OpenSession())
using (ITransaction transaction = session.BeginTransaction())
{
#region GET
// Get an Employee
Employee emp = session.Get<Employee>(2);
Console.WriteLine(emp.Name + ", " + emp.Address.PropertyName);
// Get an Address
Address add = session.Get<Address>(2);
Console.WriteLine(add.PropertyName);
foreach (Employee em in add.Employees)
{
Console.WriteLine(em.Name);
}
#endregion
#region HQL
// HQL queries
var emps1 = session.CreateQuery(
"from Employee as em where em.Address = ?")
.SetEntity(0, session.Load<Address>(2))
.List<Employee>();
var emps2 = session.CreateQuery(
"from Employee as em where em.Address.AddressID = ?")
.SetParameter(0, 2)
.List<Employee>();
#endregion
#region CRITERIA + SQL
Address addr = session.Get<Address>(2);
// Criteria query
var emps3 = session.CreateCriteria<Employee>()
.Add(Restrictions.Eq("Address", addr))
.SetFetchMode("Address", FetchMode.Join)
.List<Employee>();
// SQL query
var emps4 = session.CreateSQLQuery(
"SELECT * FROM Employees WHERE AddressID = :address")
.AddEntity(typeof(Employee))
.SetInt32("address", addr.AddressID)
.List<Employee>();
#endregion
#region LINQ
// LINQ queries (supported in NHibernate 3)
var emps5 = session.Query<Employee>()
.Where(e => e.Address ==
session.Get<Address>(2))
.Fetch(e => e.Address)
.ToList<Employee>();
foreach (Employee em in emps5)
{
Console.WriteLine(em.Name);
Console.WriteLine(em.Address.PropertyName);
}
var emps6 = (from e in session.Query<Employee>()
where e.Address == session.Get<Address>(2)
select e)
.ToList<Employee>();
#endregion
// wait for key press before ending
Console.ReadLine();
}
}
