Get Table in DataRelation at runtime

I try to explain with an example what i need

I have a Patient and Order tables in db. Patient can have multiple orders through stored procedure i will get patient attributes and orders related to that patient and i will put it in dataset. But patient attributes will be in one Datatab开发者_运维问答le and Order of that patient will be in another Datatable. In front end i need to create a class of this Relation.

like Class Patient { patient attributes list of orders he has done }

---

Assuming you are talking about System.Data.DataRelation class in the .NET Framework...

MSDN has ample information about properties on this class, including the ones you need.

http://msdn.microsoft.com/en-us/library/system.data.datarelation.aspx

using System.Data;

var myRelation = new DataRelation(...your data...);
DataTable parent = myRelation.ParentTable;
DataTable child = myRelation.ChildTable;
ForeignKeyConstraint foreignKey = myRelation.ChildKeyConstraint;
UniqueConstraint uniqueKey = myRelation.ParentKeyConstraint;
DataColumn[] childColumns = myRelation.ChildColumns;
DataColumn[] parentColumns = myRelation.ParentColumns;

etc...

That said, I would recommend elaborating on exactly what you want to do. It isn't clear from your statement " so i need to create a relationship and have to create a class from dataset. If not possible, Is there any alternate way to do that?" what your use case is. That makes it difficult to answer whatever is the core problem you want to solve.

---

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;
        }
    }
}