views:

500

answers:

4

Hello, I have three tables as shown below

Emp
----
empID int 
empName
deptID


empDetails
-----------
empDetailsID int
empID int

empDocuments
--------------
docID
empID
docName
docType

I am creating a entity class so that I can use n-tier architecture to do database transactions etc in C#. I started creating class for the same as shown below

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace employee
{
    class emp
    {
        private int empID;
        private string  empName;
        private int deptID;

        public int EmpID { get; set; }
        public string EmpName { get; set; }
        public int deptID { get; set; }

    }
}

My question is as empDetails and empDocuments are related to emp by empID. How do I have those in my emp class.

I would appreciate if you can direct me to an example.

Thanks

+1  A: 

Have you played with Linq2SQL? You may find your answer there.

Codism
Thanks. I did work with LinQtoSQL but I cannot use LinQ for this project for some strange company policy and the Database being oracle.
acadia
There are other frameworks dealing with database and entities, like hibernate, subsonic, entity framework and etc. You can read some articles about them and see how they handle your situation. After all, eventually, you will be settling down with some solution that address your issue by the existing frameworks.
Codism
A: 

If you use LINQ to Entities, you should be able to create the entities from an Oracle datasource. It's a lot easier than doing them on your own.

Stephen Cleary
+3  A: 

There are three seriously good articles I'd recomend you read to help you down the right path:

http://msdn.microsoft.com/en-us/magazine/dd882522.aspx

http://msdn.microsoft.com/en-us/magazine/ee321569.aspx

http://msdn.microsoft.com/en-us/magazine/ee335715.aspx

They're based on an EF4 solution, but I think they may still be very relevant even if you're not using EF4 or even LINQ. Especially the first two articles. Very well written.

As far as your specific question is concerned, it looks like you'll need to create an entity class for empDetails and empDocuments and then hold a property in emp of type empDetails and a collection (any IEnumerable) of empDocuments. But surely, you can use somesort of prebuilt framework to help you. I think there might be an EF Oracle implementation.

stupid-phil
A: 

Tables which contain a foreign key normally represent a greater entity's details.

Both EmpDetails and EmpDocuments represent a different detail level of your greater entity Emp.

Since you may have many documents and many details for each Emp instance, your details tables shall be gathered as a list inside of your emp class.

public class emp {
    public int Id { get; set; }
    public string Name { get; set; }
    public int DepartmentId { get; set; }
    public IList<empDetail> Details { 
        get {
            return _details;
        }
    }
    private IList<empDetail> _details;
    public IList<empDocument> Documents {
        get {
            return _documents;
        }
    }
    private IList<empDocument> _documents;
}

Using NHibernate, you could simply don't care about your database relational model and have this tool generate your database relational schema automatically using the SchemaExportTool from your class diagram through XML mapping files (Follow this link for an overview).

There are multiple plugins, if we may call them so, to NHibernate such as Fluent NHibernate (FNH), Linq to NHibernate.

Here are some useful links which shall help you get acquainted with it:

  1. NHibernate Reference Documentation
  2. Basic O/R mapping
  3. ISessionFactory Configuration
  4. Speaking of architecture : NHibernate Architecture
  5. Collection Mapping --> Pretty useful for mapping your two empDetail and empDocument collections within your emp entity class.
  6. A Nice Tutorial

A few advantages of using NHibernate:

  1. Never get bothered designing a relational model again;
  2. Don't bother about the underlying datastore, NHibernate supports multiple database engines by simple XML configuration file (no need to recompile the application for any of the underlying databases);
  3. Increase your development by easily from 25% to 50% or over once you master NHibernate;

Otherwise, there is also Microsoft Enterprise Library which I often use in conjunction with NHibernate, or depending on the projects, I may prefer only use EntLib with the different application blocks:

  1. Data Access Application Block;
  2. Exception Handling Application Block;
  3. Logging Application Block;
  4. Security Application Block;
  5. Unity Application Block;

And I may forget some others...

Will Marcouiller