How to create a class containing properties from lookup tables?

Question

Some context: this refers to my aspiring desire to create layered web applications with:

• C # ASP.NET Web Forms
• C # POCO Business Objects
• Some kind of DAL ... SQL (or maybe EF4, if I can understand it)

I would not want the answer on which my view layer spoke directly to EF objects, for example.

I have been doing my own web development with C # ASP.NET and SQL for 10 years, but I'm still a newbie when it comes to formal OOAD. I've been pursuing this skill lately, but I'm still new to this and I can't completely wrap my head. I hope someone can explain this in such a way as to bring insight:

Let's say I'm creating a web application that somehow manages People , and my Person object must have properties such as FirstName, LastName, HairColor, EyeColor, Ethnicity, StateOrProvince, etc. m using SQL Server to save ... so common sense will determine that the corresponding fields in the People table are foreign keys:

FirstName varchar(50) LastName varchar(50) HairColor tinyint EyeColor tinyint Ethnicity tinyint StateOrProvince tinyint 

Obviously, this means that I have corresponding search tables for each of these fields (for example, HairColors table, EyeColors table, Ethnicity table, etc.), and each of these search tables has an identifier and a name. Of course, the Name field in these lookup tables will be JOINed with my People data when I want to display something useful about Person.

Some key features of the site:

1.) List people in Gridview (FirstName, LastName, HairColor, EyeColor, Ethnicity, StateOrProvince)

2.) Show individual Character data on a read-only page (FirstName, LastName, HairColor, EyeColor, Ethnicity, StateOrProvince)

3.) Allow the user to update individual Person data on the update page (FirstName, LastName, HairColor, EyeColor, Ethnicity, StateOrProvince)

Case # 1 If I were listing a collection of Person objects in a gridview ... each Person instance would have to display its HairColor, EyeColor, Ethnicity, StateOrProvince properties as strings to make sense (that is, the Name field from the SQL lookup table, not the identifier ) Obviously, my SQL sproc would have several JOINs to provide me with the appropriate string data needed to populate these text properties in each Person instance.

Case # 2 Again, my sproc would have a JOIN to return human readable property names as strings , and I would only display them in read mode. Label controls use something like myPerson.HairColor, myPerson.EyeColor, etc.

Case No. 3 Here I would show a page with drop-down lists for each of these properties (i.e. Value = HairColorId, Text = HairColorName). My immediate instinct here would be to use the identifiers of each property (something like myPerson.HairColorId) to iterate over the DDL elements and select a value that represents the hair color that is currently in the People table for that person. If the user selects something else in any of the DDL properties, I need to pass the corresponding SelectedId values ​​to the UPDATE sproc and change the values ​​in the People table for this particular Person.

So this leads me to the final question:

The best way is to create a Person object so that it contains both an ID and a name for HairColor, EyeColor, Ethnicity, StateOrProvince, so I can subsequently use the Name when displaying , but the identifier to initialize the DDL controls of the update ... and, ultimately processing updates ?

As I was thinking about this ... I came to the conclusion that I need to create classes to represent the properties HairColor, EyeColor, Ethnicity, StateOrProvince.

Then the Person class, and not something like this:

 public class Person { string FirstName { get; set; } string LastName { get; set; } int HairColorId { get; set; } string HairColorName { get; set; } int EyeColorId { get; set; } string EyeColorName { get; set; } int StateOrProvinceId { get; set; } string StateOrProvinceName { get; set; } string StateOrProvinceCode { get; set; } } 

Instead, something like this will be expanded:

 public class HairColor { int Id { get; set; } string Name { get; set; } } public class EyeColor { int Id { get; set; } string Name { get; set; } } public class StateOrProvince { int Id { get; set; } string Name { get; set; } string Code { get; set; } } public class Person { HairColor HairColor { get; set; } EyeColor EyeColor { get; set; } StateOrProvince StateOrProvince { get; set; } public Person() { // how do I initialize a Person from a SQL data row? } } 

But then, if the Person class looks like the one shown above ... what is the best way to initialize it (whether individually or in a collection) from this data string, do I return from the SQL query? I seem to remember that I should not introduce new things into the constructor (ie This.HairColor = new HairColor (dr ["HairColorId"), dr ["HairColorName"];) ... so I wonder like a challenge

 public static IEnumerable<Person> GetPeople() { ... } 

in my BLL can each user fill out his data before he is added to the collection?

Really hope someone can give me a “ha” moment here ...

Answer 1

I think you have the right approach when creating classes for supporting entities (although I would put StateOrProvince in a separate Address object and, possibly, all these features in a separate PersonTraits object).

There are many ways to solve this problem. Without ORM, take a look at Data Mappers (also in Dependent Mapping ), which can be used to map from a database query to an instance of Person . This is the mapping code scheme:

 var row = ... // query database var person = new Person(row["FirstName"], row["LastName"]); person.EyeColor = new EyeColor(row["EyeColorID"], row["EyeColorName"]); ... 

(You can also use some kind of separate Object Builder .)

At any time when you update a person, you also update all supporting information using the identifier of related objects.

UPDATE : ORM, such as EF4, is very efficient and will help you with a lot of repetitive tasks (for example, with the description that I described). It is important to maintain the flexibility of your architecture and to have the constancy of just one layer with the possibility of replacement. Look here for some recommendations. In addition, I found that the book “Domain Driven Design” is really important for understanding this separation and how to model your entities.

Answer 2

I would reflect your search tables as enumerations. Then you get both id and name in one value. If names include characters that cannot be used in an identifier, you can easily create an attribute to handle additional data.

Additional information (for example, a code example, to change in accordance with this, I write in VB, so the conversion to C # will be insignificant):

 Namespace Company.Data Public Enum EyeColor As Int16 Unknown = 0 Brown = 1 Blue = 2 Green = 3 End Enum Public Enum HairColor As Int16 Unknown = 0 Brown = 1 Blond = 2 Red = 3 Pink = 4 End Enum End Namespace Public Class Person Public Property EyeColor As EyeColor = EyeColor.Unknown Public Property HairColor As HairColor = HairColor.Unknown End Class 

Because you use enumerations, individual enumeration values ​​are displayed in the keys of the database lookup table. That way you can get your Display using aPersonObject.HairColor.ToString() and you can get the id with aPersonObject.HairColor

You can get really fancy and use some code templates (mabey T4) to automatically create your listings from the values ​​in the database.

Answer 3

Have you learned the "navigation property" in EF? This will allow you to store identifiers in the main class (e.g. Person) and refer to string properties using navigation properties. For example, you will have:

Person p = [get record from EF data context]

p.state_id will refer to the numeric status identifier, while p.State.Name will be the name of the status bar. EF takes care of loading the specified status record. He can even create them for you automatically if you use the database first and define your foreign keys (there are tools that will convert the database first to a code one)