Based on your edited question, I would sit down and ask yourself, do I REALLY need to generate these classes dynamically at runtime ? That seems to be the thrust of your question, but I should think that the use case would be relatively small.
It seems more likely that you just want to create entity classes that reflect what is in your database. For this you might look at an ORM like Nhibernate (and many others), or you could even roll your own. Even strongly typed DataSets are an option, after a fashion.
If you are doing something more one-off, a LINQ to SQL query can return anonymous classes based upon your data - complete with type safety etc.
Bottom line, I'm still not sure exactly what you are trying to do, or why? Is the issue "how do I get data from a database into an entity class", or is the issue "how do I create a 'has-a' relationship between two classes", or ... ?
So the former, then. I would start by re-iterating that there are relationships between classes, and there are relationships between tables. There's no reason that your classes necessarily need to be aware of the relationship in the tables, just of the relationships between themselves
That said, ORMs like Nhibernate will help you maintain the relationship between tables.
In terms of getting the data out of the database and into your entities, a simple approach is below. I won't say this is ideal, or even good, but the core ideas work. I would recommend getting familiar with the basic operations and then reading a lot more (and asking a lot more questions) to zero in on a good approach that works for your application.
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
namespace Sample
{
public class Patient
{
public int PatientId { get; set; }
public string Name { get; set; }
private IEnumerable<Order> _orders;
public List<Order> Orders { get { return new List<Order>(_orders); } }
public static Patient GetById(int patientId)
{
using (var cn = new SqlConnection("your Patient connection string"))
{
cn.Open();
using (SqlCommand cm = cn.CreateCommand())
{
cm.CommandType = CommandType.Text;
cm.CommandText = "SELECT PatientId, Name FROM Patient WHERE PatientId = @PatientId";
cm.Parameters.AddWithValue("@PatientId", patientId);
using (SqlDataReader dr = cm.ExecuteReader())
{
if (!dr.Read())
return null;
return new Patient
{
PatientId = dr.GetInt32(0),
Name = dr.GetString(1),
_orders = Order.GetAllForPatient(patientId)
};
}
}
}
}
public bool Save()
{
// save Patient to Database
foreach (var order in _orders)
order.Save();
return true;
}
}
public class Order
{
public int OrderId { get; set; }
public int PatientId { get; set; }
public string ServiceOrdered { get; set; }
public static IEnumerable<Order> GetAllForPatient(int patientId)
{
var orders = new List<Order>();
using (var cn = new SqlConnection("your Order connection string"))
{
// ... load Orders Here.
}
return orders;
}
public bool Save()
{
//save order to database;
return true;
}
}
}